北京建设招标信息网站,做网站备案是什么意思,企业信用信息公示平台,h5设计报价单明细文章目录一、数据管理技术的三个阶段二、SQL语句学习1. DCL数据控制语言1.1 创建用户1.2 修改用户名1.3 修改密码1.4 删除用户1.5 授权1.6 查看权限1.7 回收权限2. DDL数据定义语言2.1 操作数据库2.2 操作数据表2.3 操作数据3. DQL数据查询语言基本语法3.1 单表查询3.1.1选择表…
文章目录一、数据管理技术的三个阶段二、SQL语句学习1. DCL数据控制语言1.1 创建用户1.2 修改用户名1.3 修改密码1.4 删除用户1.5 授权1.6 查看权限1.7 回收权限2. DDL数据定义语言2.1 操作数据库2.2 操作数据表2.3 操作数据3. DQL数据查询语言基本语法3.1 单表查询3.1.1选择表中的若干列3.1.2 选择表中的若干元组3.1.3 ORDER BY3.1.4 聚合函数3.1.5 GROUP BY3.2 连接查询3.2.1 等值连接3.2.2 非等值连接3.2.3 自身连接3.2.4 外连接3.3 嵌套查询3.3.1 嵌套查询概述3.3.2 不相关子查询与相关子查询非常重要关乎子查询3.3.3 带有IN谓词的子查询3.3.4 带有比较运算符的子查询3.3.5 带有ANYSOME或ALL谓词的子查询3.3.6 带有EXISTS谓词的子查询3.4 集合查询3.5 基于派生表的查询4. 约束4.1 非空约束4.2 唯一约束4.3 主键约束4.4 自动增长约束4.5 外键约束5. mysql中int类型单引号问题6. 空值的处理7. 视图1. 创建视图2. 删除视图3. 查询视图4. 更新视图三、数据类型1. MySQL整数类型2. 小数类型3. 日期和时间类型a. YEAR类型b. TIME类型c. DATE类型d. DATETIME类型e. TIMESTAMP类型4. 字符串类型四、java向MySQL插入当前时间的几种方式方式一方式二五、SQL知识的基本概念1. 码2. 函数依赖3. 六大范式一、数据管理技术的三个阶段 人工管理阶段在计算机出现之前人们主要利用纸张和计算工具如算盘和计算尺来进行数据的记录和计算依靠大脑来管理和利用数据。文件系统阶段将数据存储在计算机的磁盘上。这些数据都以文件的形式存储然后通过文件系统来管理这些文件。 数据库系统阶段相对于文件系统来说数据库系统实现了数据结构化。在文件系统中独立文件内部的数据一般是有结构的但文件之间不存在联系因此整体来说是没有结构的。 数据库系统虽然也常常分成许多单独的数据文件但是它更注意同一数据库中各数据文件之间的相互联系。 二、SQL语句学习
DDL(Data Definition Language)数据定义语言 用来定义数据库对象数据库表列等。关键字create, drop,alter 等DML(Data Manipulation Language)数据操作语言 用来对数据库中表的数据进行增删改。关键字insert, delete, update 等DQL(Data Query Language)数据查询语言 用来查询数据库中表的记录(数据)。关键字select, where 等DCL(Data Control Language)数据控制语言(了解) 用来定义数据库的访问权限和安全级别及创建用户。关键字GRANT REVOKE 等TCL(Transition Language) :事务控制语言用来管理事务
1. DCL数据控制语言
1.1 创建用户
’alian’localhost’ 表示只允许本机登录’alian’’%’ 表示任意地址登录’alian’192.168.0.100’ 表示只允许ip为192.168.0.100的地址登录’alian’‘192.168..’ 表示只允许ip为192.168网段的地址登录
# 格式
create user 用户名IP地址 identified WITH mysql_native_password by 密码;
flush privileges;# 实例
CREATE USER alian% IDENTIFIED WITH mysql_native_password BY 123456;
flush privileges;1.2 修改用户名
rename user 用户名IP地址 to 新用户名IP地址;1.3 修改密码
#切换到mysql库
use mysql;
#更新密码
UPDATE user SET passwordpassword(新密码) WHERE user用户名 AND hostIP地址;
#刷新权限
FLUSH PRIVILEGES;或者
ALTER USER 用户名IP地址 IDENTIFIED WITH mysql_native_password BY 新密码;
flush privileges;或者
#普通用户登录后
SET PASSWORDpassword(新密码);
FLUSH PRIVILEGES;1.4 删除用户
#注意这里的IP地址一个用户可能会有多个
drop user 用户名IP地址;
#比如
drop user Alian192.168.0.100;1.5 授权
grant 权限1, 权限2, 权限3,… ,权限n on 数据库名.表名 to 用户名地址;数据库名.表名 . 表示任意库的任意表不建议mysql.* 表示mysql库的任意表mysql.user 表示mysql库的user表 用户名地址 ’alian’localhost’ 表示只允许本机登录’alian’’%’ 表示任意地址登录’alian’192.168.0.100’ 表示只允许ip为192.168.0.100的地址登录’alian’‘192.168..’ 表示只允许ip为192.168网段的地址登录 实例
#把数据库的所有库的所有权限都给alian,并且是任意ip地址都可以操作
grant all privileges on *.* to alian%;
flush privileges;#把mysql数据库的所有权限都给alian,并且是任意ip地址都可以操作
grant all privileges on mysql.* to alian%;
flush privileges;#把mysql数据库的user表的所有权限都给alian,并且是只能通过192.138.0.10才可以操作
grant all privileges on mysql.user to alian192.138.0.10;
flush privileges;#把mysql数据库的user表的查询插入更新删除的权限都给alian,并且是任意ip地址都可以操作
grant SELECT, INSERT, UPDATE, DELETE on mysql.user to alian%;
flush privileges;1.6 查看权限
show grants for alian%;1.7 回收权限
#格式
revoke 权限1, 权限2…权限n on 数据库名.表名 from 用户名地址;#实例回收用户的更新和删除mysql默认的库数据库的权限
revoke update,delete on mysql.user from alian%;2. DDL数据定义语言
2.1 操作数据库
创建 # 创建数据库判断不存在再创建create database if not exists 数据库名称;# 创建数据库并指定字符集create database 数据库名称 character set 字符集名;查询 * 查询所有数据库的名称:show databases;* 查询某个数据库的字符集:查询某个数据库的创建语句show create database 数据库名称;修改 * 修改数据库的字符集alter database 数据库名称 character set 字符集名称;删除 * 删除数据库drop database 数据库名称;* 判断数据库存在存在再删除drop database if exists 数据库名称;2.2 操作数据表
创建
create table 表名(列名1 数据类型1,列名2 数据类型2,....列名n 数据类型n
);# 数据库类型1. int整数类型 2. double:小数类型3. date:日期只包含年月日yyyy-MM-dd4. datetime:日期包含年月日时分秒 yyyy-MM-dd HH:mm:ss5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss 6. varchar字符串* zhangsan 8个字符 张三 2个字符查询 * 查询某个数据库中所有的表名称show tables;* 查询表结构desc 表名;修改 1. 修改表名alter table 表名 rename to 新的表名;2. 修改表的字符集alter table 表名 character set 字符集名称;3. 添加一列alter table 表名 add 列名 数据类型;4. 修改列名称 类型alter table 表名 change 列名 新列别 新数据类型;alter table 表名 modify 列名 新数据类型;5. 删除列alter table 表名 drop 列名;删除 * drop table 表名;* drop table if exists 表名 ;2.3 操作数据
添加
insert into 表名(列名1,列名2,…列名n) values(值1,值2,…值n);删除
delete from 表名 [where 条件]修改
update 表名 set 列名1 值1, 列名2 值2,… [where 条件];3. DQL数据查询语言
基本语法
select字段列表
from表名列表
where条件列表
group by分组字段
having分组之后的条件
order by 排序
limit分页限定3.1 单表查询
3.1.1选择表中的若干列
# 查询全体学生的学号与姓名。
SELECT Sno,Sname FROM Student; # 查询全体学生的详细记录
SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student;
SELECT * FROM Student; # 查询经过计算的值查全体学生的姓名及其出生年份。
SELECT Sname,2023-Sage FROM Student;# 使用列别名改变查询结果SnameNAME
SELECT Sname NAME FROM Student;
3.1.2 选择表中的若干元组
# 如果没有指定DISTINCT关键词则缺省为ALL
SELECT Sno FROM SC;
SELECT ALL Sno FROM SC;# 指定DISTINCT关键词去掉表中重复的行
SELECT DISTINCT Sno FROM SC;# 1. 比较大小# 查询计算机科学系全体学生的名单。SELECT Sname FROM Student WHERE Sdept‘CS’; # 查询所有年龄在20岁以下的学生姓名及其年龄。SELECT Sname,Sage FROM Student WHERE Sage 20;# 2. 确定范围# 查询年龄在20~23岁包括20岁和23岁之间的学生的姓名、系别和年龄SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23; # 3. 确定集合# 查询既不是计算机科学系、数学系也不是信息系的学生的姓名和性别。SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN (IS,MA’,CS);# 查询计算机科学系CS、数学系MA和信息系IS学生的姓名和性别。SELECT Sname,Ssex FROM Student WHERE Sdept NOT IN (IS,MA,CS);# 4. 字符匹配# 查询学号为201215121的学生的详细情况。SELECT * FROM Student WHERE Sno LIKE ‘201215121;# 查询所有姓刘学生的姓名、学号和性别。SELECT Sname, Sno, Ssex FROM Student WHERE Sname LIKE 刘%;# 5. 涉及空值的查询# 查所有有成绩的学生学号和课程号。SELECT SnoCno FROM SC WHERE Grade IS NOT NULL;# 6. 多重条件查询AND优先级大于OR# 查询计算机系年龄在20岁以下的学生姓名。SELECT Sname FROM Student WHERE Sdept CS AND Sage20;3.1.3 ORDER BY
# 查询选修了3号课程的学生的学号及其成绩查询结果按分数降序排列。
SELECT Sno, Grade FROM SC WHERE Cno 3 ORDER BY Grade DESC;# 查询全体学生情况查询结果按所在系的系号升序排列同一系中的学生按年龄降序排列。
SELECT * FROM Student ORDER BY Sdept, Sage DESC; 3.1.4 聚合函数
聚合函数的计算排序null值
用COUNT([DISTINCT|ALL] 列名)函数和GROUP BY关键字一起来计算不同分组中的记录总数
select o_num count(f_id) from orderitems group by o_num;#SUM([DISTINCT|ALL] 列名)可以与GROUP BY一起使用来计算每个分组的总和
select o_num,sum(quantity) as items_total from orderitems group by o_num;#AVG([DISTINCT|ALL] 列名)可以与GROUP BY一起使用来计算每个分组的平均值
select s_id,AVG(f_price) as avg_price from fruits group by s_id;#MAX([DISTINCT|ALL] 列名)也可以和GROUP BY关键字一起使用求每个分组中的最大值。
select s_id,max(f_price) as max_price from fruits group by s_id;#MIN([DISTINCT|ALL] 列名)也可以和GROUP BY关键字一起使用求出每个分组中的最小值。
select s_id,min(f_price) as min_price from fruits group by s_id;WHERE子句中是不能用聚集函数作为条件表达式
# i查询平均成绩大于等于90分的学生学号和平均成绩,下面的语句是不对的SELECT Sno, AVG(Grade)FROM SCWHERE AVG(Grade)90GROUP BY Sno;# 因为WHERE子句中是不能用聚集函数作为条件表达式正确的查询语句应该是SELECT Sno, AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)90;
3.1.5 GROUP BY
where 在分组之前进行限定如果不满足条件则不参与分组。having在分组之后进行限定如果不满足结果则不会被查询出来where 后不可以跟聚合函数having可以进行聚合函数的判断。
-- 按照性别分组。分别查询男、女同学的平均分SELECT sex , AVG(math) FROM student GROUP BY sex;-- 按照性别分组。分别查询男、女同学的平均分,人数SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;-- 按照性别分组。分别查询男、女同学的平均分,人数 要求分数低于70分的人不参与分组SELECT sex , AVG(math),COUNT(id) FROM student WHERE math 70 GROUP BY sex;-- 按照性别分组。分别查询男、女同学的平均分,人数 要求分数低于70分的人不参与分组,分组之后。人数要大于2个人SELECT sex , AVG(math),COUNT(id) FROM student WHERE math 70 GROUP BY sex HAVING COUNT(id) 2;SELECT sex , AVG(math),COUNT(id) 人数 FROM student WHERE math 70 GROUP BY sex HAVING 人数 2;
3.2 连接查询
3.2.1 等值连接
SQL语句 SELECT Student.*, SC.*FROM Student, SCWHERE Student.Sno SC.Sno;执行结果 执行过程 嵌套循环法NESTED-LOOP 首先在表1中找到第一个元组然后从头开始扫描表2逐一查找满足连接件的元组找到后就将表1中的第一个元组与该元组拼接起来形成结果表中一个元组。 表2全部查找完后再找表1中第二个元组然后再从头开始扫描表2逐一查找满足连接条件的元组找到后就将表1中的第二个元组与该元组拼接起来形成结果表中一个元组。 重复上述操作直到表1中的全部元组都处理完毕 排序合并法SORT-MERGE 常用于连接首先按连接属性对表1和表2排序对表1的第一个元组从头开始扫描表2顺序查找满足连接条件的元组找到后就将表1中的第一个元组与该元组拼接起来形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时对表2的查询不再继续 找到表1的第二条元组然后从刚才的中断点处继续顺序扫描表2查找满足连接条件的元组找到后就将表1中的第一个元组与该元组拼接起来形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时对表2的查询不再继续 重复上述操作直到表1或表2中的全部元组都处理完毕为止 索引连接INDEX-JOIN 对表2按连接字段建立索引对表1中的每个元组依次根据其连接字段值查询表2的索引从中找到满足条件的元组找到后就将表1中的第一个元组与该元组拼接起来形成结果表中一个元组 3.2.2 非等值连接
# 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名。SELECT Student.Sno, SnameFROM Student, SCWHERE Student.SnoSC.Sno AND SC.Cno 2 AND SC.Grade90;执行过程: 先从SC中挑选出Cno2’并且Grade90的元组形成一个中间关系再和Student中满足连接条件的元组进行连接得到最终的结果关系 3.2.3 自身连接
自身连接一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性因此必须使用别名前缀
# 查询每一门课的间接先修课即先修课的先修课SELECT FIRST.Cno, SECOND.CpnoFROM Course FIRST, Course SECONDWHERE FIRST.Cpno SECOND.Cno;3.2.4 外连接
外连接与普通连接的区别
普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体将主体表中不满足连接条件的元组一并输出左外连接列出左边关系中所有的元组右外连接列出右边关系中所有的元组
SQL语句 # LEFT OUT左边的全部输出SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Student LEFT OUT JOIN SC ON (Student.SnoSC.Sno); 执行结果
3.3 嵌套查询
3.3.1 嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询子查询不能使用ORDER BY子句 SQL语句 SELECT Sname /*外层查询/父查询*/FROM StudentWHERE Sno IN( SELECT Sno /*内层查询/子查询*/FROM SCWHERE Cno 2 );3.3.2 不相关子查询与相关子查询非常重要关乎子查询
子查询的查询条件不依赖于父查询 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解子查询的结果用于建立其父查询的查找条件。 子查询的查询条件依赖于父查询 首先取外层查询中表的第一个元组根据它与内层查询相关的属性值处理内层查询若WHERE子句返回值为真则取此元组放入结果表然后再取外层表的下一个元组重复这一过程直至外层表全部检查完为止 3.3.3 带有IN谓词的子查询
# 查询与“刘晨”在同一个系学习的学生。SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept IN(SELECT SdeptFROM StudentWHERE Sname 刘晨 );# 也可以通过自身连接完成查询要求SELECT S1.Sno, S1.Sname,S1.SdeptFROM Student S1,Student S2WHERE S1.Sdept S2.Sdept ANDS2.Sname 刘晨;3.3.4 带有比较运算符的子查询
# 找出每个学生超过他选修课程平均成绩的课程号。SELECT Sno, CnoFROM SC xWHERE Grade (SELECT AVGGrade FROM SC yWHERE y.Snox.Sno);3.3.5 带有ANYSOME或ALL谓词的子查询
# 查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄SELECT Sname,SageFROM StudentWHERE Sage ANY (SELECT SageFROM StudentWHERE Sdept CS )AND Sdept ‘CS ; /*父查询块中的条件 */3.3.6 带有EXISTS谓词的子查询
带有EXISTS谓词的子查询不返回任何数据只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空则外层的WHERE子句返回真值若内层查询结果为空则外层的WHERE子句返回假值
# 查询所有选修了1号课程的学生姓名SELECT SnameFROM StudentWHERE EXISTS(SELECT *FROM SCWHERE SnoStudent.Sno AND Cno 1 );# 本查询涉及Student和SC关系
# 在Student中依次取每个元组的Sno值用此值去检查SC表
# 若SC中存在这样的元组其Sno值等于此Student.Sno值并且其Cno ‘1’则取此Student.Sname送入结果表3.4 集合查询
集合操作的种类并操作UNION、交操作INTERSECT、差操作EXCEPT集合操作限制参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同
# 查询计算机科学系的学生及年龄不大于19岁的学生。
# UNION将多个查询结果合并起来时系统自动去掉重复元组
# UNION ALL将多个查询结果合并起来时保留重复元组 SELECT *FROM StudentWHERE Sdept CSUNIONSELECT *FROM StudentWHERE Sage19;# 查询计算机科学系的学生与年龄不大于19岁的学生 的交集。SELECT *FROM StudentWHERE SdeptCS INTERSECTSELECT *FROM StudentWHERE Sage19 # 查询计算机科学系的学生与年龄不大于19岁的学生的差集。SELECT *FROM StudentWHERE SdeptCSEXCEPTSELECT *FROM StudentWHERE Sage 19;3.5 基于派生表的查询
派生表子查询不仅可以出现在WHERE子句中还可以出现在FROM子句中这时子查询生成的临时派生表Derived Table成为主查询的查询对象
# 找出每个学生超过他自己选修课程平均成绩的课程号 SELECT Sno, CnoFROM SC, (SELECT Sno, Avg(Grade) FROM SC GROUP BY Sno)AS Avg_sc(avg_sno,avg_grade)WHERE SC.Sno Avg_sc.avg_snoAND SC.Grade Avg_sc.avg_grade4. 约束 主键约束primary key 非空约束not null 唯一约束unique 外键约束foreign key 默认值 Default 4.1 非空约束
# 创建表时添加非空约束
CREATE TABLE stu(id INT,NAME VARCHAR(20) NOT NULL -- name为非空
);# 创建表后添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;# 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);4.2 唯一约束
# 创建表时添加唯一约束
CREATE TABLE stu(id INT,phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束);# 在创建表后添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;# 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;4.3 主键约束
# 创建表时添加主键约束
create table stu(id int primary key,-- 给id添加主键约束name varchar(20)
);# 创建完表后添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;# 删除主键错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;4.4 自动增长约束
# 创建表时添加自动增长约束
create table stu(id int primary key auto_increment,-- 给id添加主键约束name varchar(20));# 创建表后添加自动增长约束
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;# 删除自动增长
ALTER TABLE stu MODIFY id INT;4.5 外键约束
在创建数据表时添加约束。 下面语句执行成功之后在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束外键名称为 deptId其依赖于表 tb_dept1 的主键 id。
mysql CREATE TABLE tb_dept1- (- id INT(11) PRIMARY KEY,- name VARCHAR(22) NOT NULL,- location VARCHAR(50)- );mysql CREATE TABLE tb_emp6- (- id INT(11) PRIMARY KEY,- name VARCHAR(25),- deptId INT(11),- salary FLOAT,- CONSTRAINT fk_emp_dept1- FOREIGN KEY(deptId) REFERENCES tb_dept1(id)- );在修改表时添加外键约束
mysql ALTER TABLE tb_emp2- ADD CONSTRAINT fk_tb_dept1- FOREIGN KEY(deptId)- REFERENCES tb_dept1(id);删除外键约束
mysql ALTER TABLE tb_emp2- DROP FOREIGN KEY fk_tb_dept1;5. mysql中int类型单引号问题
今天写mysql的时候发现了一个问题在查询的时候不小心把int类型的id加上引号查询了但是也能查询出来
SELECT * FROM account WHERE aid1;于是查询了很多资料最后得出的结论是mysql会自动转换当你类型为int 但传入的值为varchar 他会把前几个’数字挑出来’ 转换为int 类型来进行查询 如果你的 aid 1 你查询语句是SELECT * FROM 表名 WHERE aid‘1dasd’; 在编译的时候编译器会把 1 挑出来进行转换在进行查询
如果你的 aid 1 你查询语句是SELECT * FROM 表名 WHERE aid‘dasd’; 第一个不为数字所以无法转换查询出来的结果就为空(不会报错)
如果你的 aid 1 你查询语句是SELECT * FROM 表名 WHERE aid‘123da’; 在编译的时候编译器会把 123 挑出来进行转换在进行查询但查询结果为空找不到这个对应的id
如果你的 aid 1 你查询语句是SELECT * FROM 表名 WHERE aid‘da1sd’; 查询结果为空因为编译期是从第一个开始查看是否为数字如果不是不会往后查
6. 空值的处理
规则一 空值与另一个值包括另一个空值的算术运算的结果为空值空值与另一个值包括另一个空值的比较运算的结果为UNKNOWN。 有UNKNOWN后传统二值TRUEFALSE逻辑就扩展成了三值逻辑空值与另一个值包括另一个空值的逻辑运算 7. 视图
概述 虚表是从一个或几个基本表或视图导出的表只存放视图的定义不存放视图对应的数据基表中的数据发生变化从视图中查询出的数据也随之改变 1. 创建视图
# 建立信息系学生的视图。CREATE VIEW IS_StudentAS SELECT Sno,Sname,SageFROM StudentWHERE Sdept IS;WITH CHECK OPTION; # 对视图进行UPDATEINSERT和DELETE操作时要保证 # 更新、插入或删除的行 满足视图定义中的谓词条件即子查询中的条件表达式2. 删除视图
# 如果该视图上还导出了其他视图使用CASCADE级联删除语句把该视图和由它导出的所有视图一起删除
DROP VIEW IS_S1 CASCADE; 3. 查询视图
# 在信息系学生的视图中找出年龄小于20岁的学生。SELECT Sno,SageFROM IS_StudentWHERE Sage20;4. 更新视图
# 将信息系学生视图IS_Student中学号”201215122”的学生姓名改为”刘辰”。
UPDATE IS_Student SET Sname 刘辰 WHERE Sno 201215122 ;三、数据类型
参考文章数据类型
1. MySQL整数类型 2. 小数类型 3. 日期和时间类型 a. YEAR类型
YEAR 类型是一个单字节类型用于表示年在存储时只需要 1 个字节。可以使用各种格式指定 YEAR如下所示 以 4 位字符串或者 4 位数字格式表示的 YEAR范围为 ‘1901’’2155’。输入格式为 ‘YYYY’ 或者 YYYY例如输入 ‘2010’ 或 2010插入数据库的值均为 2010。 以 2 位字符串格式表示的 YEAR范围为 ‘00’ 到 ‘99’。‘00’’69’ 和 ‘70’’99’ 范围的值分别被转换为 20002069 和 19701999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。 以 2 位数字表示的 YEAR范围为 199。199 和 7099 范围的值分别被转换为 20012069 和 19701999 范围的 YEAR 值。注意在这里 0 值将被转换为 0000而不是 2000。 提示两位整数范围与两位字符串范围稍有不同。例如插入 3000 年读者可能会使用数字格式的 0 表示 YEAR实际上插入数据库的值为 0000而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’才可以被正确解释为 3000非法 YEAR值将被转换为 0000。 b. TIME类型
可以使用各种格式指定 TIME 值如下所示。 ‘D HHMMSS’ 格式的字符串。还可以使用这些“非严格”的语法‘HHMMSS’、‘HHMM’、‘D HH’ 或 ‘SS’。这里的 D 表示日可以取 034 之间的值。在插入数据库时D 被转换为小时保存格式为 “D*24HH”。 ‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值假定是有意义的时间。例如‘101112’ 被理解为’101112’但是 ‘106112’ 是不合法的它有一个没有意义的分钟部分在存储时将变为 000000。 提示为 TIME 列分配简写值时应注意如果没有冒号MySQL 解释值时假定最右边的两位表示秒。MySQL 解释 TIME 值为过去的时间而不是当前的时间。例如读者可能认为 ‘1112’ 和 1112 表示 111200即 11 点过 12 分钟但MySQL 将它们解释为 001112即 11 分 12 秒。同样 ‘12’ 和 12 被解释为000012。相反TIME 值中如果使用冒号则肯定被看作当天的时间也就是说‘1112’ 表示 111200而不是 001112。 c. DATE类型
在给 DATE 类型的字段赋值时可以使用字符串类型或者数字类型的数据插入只要符合 DATE 的日期格式即可。如下所示 以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期取值范围为 ‘1000-01-01’’9999-12-3’。例如输入 ‘2015-12-31’ 或者 ‘20151231’插入数据库的日期为2015-12-31。 以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期在这里YY表示两位的年值。MySQL 解释两位年值的规则‘0069’ 范围的年值转换为 ‘20002069’7099’ 范围的年值转换为 ‘19701999’。例如输入 ‘15-12-31’插入数据库的日期为 2015-12-31输入 ‘991231’插入数据库的日期为 1999-12-31。 以 YYMMDD 数字格式表示的日期与前面相似00~69 范围的年值转换为 200020698099 范围的年值转换为 19801999。例如输入 151231插入数据库的日期为 2015-12-31输入 991231插入数据库的日期为 1999-12-31。 使用 CURRENT_DATE 或者 NOW()插入当前系统日期。 提示MySQL 允许“不严格”语法任何标点符号都可以用作日期部分之间的间隔符。例如‘98-11-31’、‘98.11.31’、‘98/11/31’和’981131’ 是等价的这些值也可以正确地插入数据库。 d. DATETIME类型
如下所示。 以 ‘YYYY-MM-DD HHMMSS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期取值范围为 ‘1000-01-01 000000’’9999-12-3 235959’。例如输入 ‘2014-12-31 050505’ 或者 20141231050505’插入数据库的 DATETIME 值都为 2014-12-31 050505。 以 ‘YY-MM-DD HHMMSS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期在这里 YY 表示两位的年值。与前面相同‘0079’ 范围的年值转换为 ‘20002079’‘8099’ 范围的年值转换为 ‘19801999’。例如输入 ‘14-12-31 050505’插入数据库的 DATETIME 为 2014-12-31 050505输入 141231050505插入数据库的 DATETIME 为 2014-12-31 050505。 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如输入 20141231050505插入数据库的 DATETIME 为 2014-12-31 050505输入 140505050505插入数据库的 DATETIME 为 2014-12-31 050505。 提示MySQL 允许“不严格”语法任何标点符号都可用作日期部分或时间部分之间的间隔符。例如‘98-12-31 113045’、‘98.12.31 113035’、‘98/12/31 113045’ 和 ‘981231 113045’ 是等价的这些值都可以正确地插入数据库。 e. TIMESTAMP类型
TIMESTAMP 的显示格式与 DATETIME 相同显示宽度固定在 19 个字符日期格式为 YYYY-MM-DD HHMMSS在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围为 1970-01-01 000001’UTC’2038-01-19 031407’UTC。在插入数据时要保证在合法的取值范围内。
TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下当插入一条记录但并没有指定 TIMESTAMP 这个列值时MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时使用 TIMESTAMP 是方便的另外 TIMESTAMP 在空间上比 DATETIME 更有效。 提示协调世界时英Coordinated Universal Time法Temps Universel Coordonné又称为世界统一时间、世界标准时间、国际协调时间。英文CUT和法文TUC的缩写不同作为妥协简称 UTC。 TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外还有一个最大的区别是 DATETIME 在存储日期数据时按实际输入的格式存储即输入什么就存储什么与时区无关 而 TIMESTAMP 值的存储是以 UTC世界标准时间格式保存的存储时对当前时区进行转换检索时再转换回当前时区。即查询时根据当前时区的不同显示的时间值是不同的。 提示如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值结果值的时间部分被设置为 ‘000000’因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值结果值的时间部分被删除因此DATE 值未包含时间信息。 4. 字符串类型 四、java向MySQL插入当前时间的几种方式
方式一 Date date new Date();//获得系统时间.SimpleDateFormat sdf new SimpleDateFormat( yyyy-MM-dd HH:mm:ss );String nowTime sdf.format(date);Date time sdf.parse( nowTime );方式二 Date date new Date();//得到一个timestamp格式的时间存入mysql中的时间格式为yyyy-MM-dd HH:mm:ssTimestamp timestamp new Timestamp(date.getTime());五、SQL知识的基本概念
1. 码 候选码 若关系中的某一属性组的值能唯一地标识一个元组而其子集不能则称该属性组为候选码。主码 若一个关系中有多个候选码则选定其中一个为主码。主属性 所有候选码的属性称为主属性。不包含在任何候选码中的属性称为非主属性或非码属性。 2. 函数依赖 1. 完全函数依赖 如果我想知道某位学生的某一门课的成绩Grade那我必须得同时知道他的学号Sno和课程号Cno。但如果我只知道一部分信息比如他的Sno或者Cno可以吗答案是不行的此时称Y[Grade]完全依赖于X[Sno,Cno]。 2. 部分函数依赖 如果我想知道某位学生的姓名Sname那我知道他的学号Sno就可以了。也就是说Y[Sname]只函数依赖于X[Sno,Cno]中的子集x[Sno]此时称Y部分函数依赖于X。 3. 传递函数依赖 有一个关系模式S(Sno,Sdept,Mname)如果我知道了一个学生的学号Sno那我就能知道他所在的系Sdept。如果我知道了某一个系Sdept那么我就能知道这个系的系主任的姓名Mname。也就是说我知道了一个学生的学号Sno其实我就知道了他所在系的系主任的姓名Mname。但这个过程中他们是不存在直接函数依赖的我需要通过系名称Sdept作为一个桥梁去把二者联系起来的。 4. 多值依赖 定义 一个关系至少存在三个属性A、B、C才能存在这种关系。对于每一个A值有一组确定的B值和C值并且这组B的值独立于这组C的值。 实例 比如我们建立课程教师和教材的模型我们规定每门课程有对应的一组教师每门课程也有对应的一组教材一门课程使用的教程和教师没有关系。 3. 六大范式
省流
第一范式数据项不可分
第二范式非主属性完全依赖主属性消除了部分函数依赖解决了数据冗余
第三范式非主属性只能完全依赖主属性消除了非主属性依赖于主属性解决了传递依赖
BC范式消除了主属性对候选键的部分依赖
第四范式消除了多值依赖第一范式1NF 所有属性都不可再分即数据项不可分。
第二范式2NF 确保数据库表中的每一列都和主键相关而不能只与主键的某一部分相关 举例比赛表里面包含了球员编号姓名年龄比赛编号比赛时间比赛场地等属性由于单单球员编号和比赛编号都无法确定唯一一条数据因此需要将球员编号和比赛编号联合作为主键 (球员编号比赛编号) -- (姓名年龄比赛时间比赛场地得分)但是这样并不满足第二范式会出现以下问题 数据冗余如果一个球员参加了n场比赛那么球员的姓名和年龄就重复了n次一个比赛也有可能有m个球员参加那比赛的时间和地点就重复了m次。插入异常如果我们想要添加一场新的比赛但是这时球员还没有确定那么就无法插入。删除异常如果我们想要删除某个球员编号会将比赛信息删除掉。更新异常如果我们想要调整某个比赛.的时间那么数据库表中所有关于这个比赛的时间都需要进行调整否则会出现一场比赛时间不同的情况。 因此为了避免上面的问题我们可以将上面球员比赛表设计成三张表这样每张表都符合了第二范式
球员表球员编号姓名年龄比赛表比赛编号比赛场地比赛时间球员比赛关系表球员编号比赛编号得分第三范式3NF 在第二范式的基础上确保数据表中的每一个非主键字段都和主键字段直接相关也就是说要求数据表中的所有非主键字段不能依赖于其他非主键字段字段。即不能存在非主属性A依赖非主属性B非主属性B依赖于主键C的情况通俗来说该规则的意思是所有非主键属性之间不能有依赖关系必须相互独立从而消除非主属性传递依赖候选码。 举例员工信息表员工编号姓名部门编号部门名称 上面的员工信息表是符合第二范式的因为姓名部门编号部门名称都完全依赖员工编号这个主键但是并不符合第三范式因为有非主键字段 部门名称 依赖于非主键字段 部门编号。因此需要将部门编号部门名称再抽取成一张表。 BC范式BCBF 在3NF的基础上消除了主属性对候选键的部分依赖或者传递性依赖 举例存在一个关系表仓库名管理员物品名数量 进行分析可知仓库名物品名或者管理员物品名可以决定数量因此仓库名物品名或者管理员物品名就是这个表的候选键。 存在问题 增加一个仓库但是还没有存放物品由于主键仓库名物品名不能有空值存在因此会插入异常。如果仓库的物品全部卖完了那么会导致仓库名和管理员名称也会随之删除。如果仓库的管理员更换了那么会有多条记录都需要更改。 问题出现原因 主属性仓库名对于候选键管理员物品名有部分依赖这样就导致有可能出现上面的问题因此引入BCNF它在3NF的基础上消除了主属性对候选键的部分依赖或者传递性依赖。 解决问题 将仓库名和管理员拆分出来形成一张表然后仓库名物品名数量形成一张库存表。 第四范式4NF 消除多值依赖 举例有职工表职工编号职工孩子职工选修课程在这张表中每个职工可能有多个孩子也有可能有多个选修课程因此需要进行拆分成两张表职工表一职工编号职工孩子职工表二职工编号职工选修课程。 第五范式5NF 除了第四范式外我们还有更高级的第五范式和域键范式DKNF第五范式处理的是无损连接问题这个范式基本没有实际意义因为无损连接很少出现而且难以察觉。而域键范式试图定义一个终极范式该范式考虑所有的依赖和约束类型但是实用价值也是最小的只存在理论研究中。
