一.DQL语句
1.排序
通过ORDER BY子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC: 升序, 默认是升序
DESC: 降序
1.1 单列排序
单列排序就是使用一个字段排序
例如:
SELECT * FROM student3 ORDER BY age DESC;
1.2 组合排序
组合排序就是先按第一个字段进行排序,如果第一个字段相同,才按第二个字段进行排序,依次类推。
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
2. 聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,它是对一列的值进行计算,然后返回一个结果值。另外聚合函数会忽略空值
五个聚合函数:
count(): 统计指定列记录数,记录为NULL的不统计
sum(): 计算指定列的数值和,如果不是数值类型,那么计算结果为0
max(): 计算指定列的最大值
min(): 计算指定列的最小值
avg(): 计算指定列的平均值,如果不是数值类型,那么计算结果为0
注意: ifnull(expr1,expr2)假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为expr2
聚合函数的使用:写在 SQL语句SELECT后 字段名的地方
SELECT 字段名... FROM 表名;
SELECT COUNT(age) FROM 表名;
3. 分组
分组查询是指使用 GROUP BY语句对查询信息进行分组,相同数据作为一组
SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
SELECT 字段1,字段2... FROM 表名 where 条件 GROUP BY 分组字段 [HAVING 条件];
GROUP BY怎么分组的?
将分组字段结果中相同内容作为一组
GROUP BY将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
分组后聚合函数的作用?不是操作所有数据,而是操作一组数据。
例如:
SELECT SUM(math), sex FROM student3 GROUP BY sex;
注意事项:当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的
总结:
select 字段,聚合函数 from 表名 [where 条件] group by 条件[having 条件] [order by 条件;
4. limit语句
LIMIT是限制的意思,所以LIMIT的作用就是限制查询记录的条数。
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];
LIMIT语法格式:
LIMIT offset,length; 或者limit length;
offset是指偏移量,可以认为是跳过的记录数量,默认为0
length是指需要显示的总记录数
LIMIT的使用场景:分页
比如我们登录京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来。是一页显示固定的条数。
5. 日期和字符串函数
日期函数
- now(); 获取年月日时分秒
- curdate() ; 获取年月日
- curtime(); 获取当前时分秒
- YEAR(date) ;返回date 对应的年份,范围是从1000到9999。
- MONTH(date) ;返回date 对应的月份,范围时从 1 到 12。
- DAYOFMONTH(date) 表示返回date是当月的第几天,1号就返回1,… ,31号就返回31)
- HOUR(time);返回该date或者time的hour值,值范围(0-23)
- MINUTE(time);返回该time的minute值,值范围(0-59)
- SECOND(time);返回该time的minute值,值范围(0-59)
- DATE_FORMAT(date,format);根据format 字符串安排date 值的格式。 将日期以字符串形式输出
- STR_TO_DATE(str,format);这是DATE_FORMAT() 函数的倒转 ,将字符串转换为日期输出
字符串函数
- LENGTH(str):返回字符串的存储长度
- CONCAT(str1,str2,…) ;返回结果为连接参数产生的字符串
- SUBSTRING(str,pos) , SUBSTRING(str,pos,len) ,
- 不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。
- 带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。
- TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str); 默认去除字符串str中的remstr的前后缀
- both 去前后缀
- leading 去前缀
- trailing 去后缀
- REPLACE(str,from_str,to_str) ;返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
- INSERT(str,pos,len,newstr);在指定位置,使用newstr替换指定长度的字符串
二.数据库备份
1. 备份的应用场景
在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补与估量的。
2.source命令备份与还原
备份格式:
mysqldump -u用户名 -p密码 数据库 > 文件的路径
还原格式:
SOURCE 导入文件的路径;
注意:还原的时候需要先登录MySQL,并选中对应的数据库
三.数据库约束
对表中的数据进行进一步的限制,保证数据的正确性、有效性和完整性。
约束种类:
- PRIMARY KEY: 主键(唯一,非空)
- UNIQUE: 唯一(不能重复)
- NOT NULL: 非空(不能为null)
- DEFAULT: 默认
- FOREIGN KEY: 外键(约束多表情况)
1.主键
1.1 主键的作用
用来唯一标识一条记录,每个表都应该有一个主键,并且每个表只能有一个主键。
有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据
主键的特点: 唯一(不能重复),非空(不能为null)
哪个字段应该作为表的主键?
通常不用业务字段作为主键,单独给每张表设计一个id的字段,把id作为主键。主键是给数据库和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
1.2 创建主键
主键:PRIMARY KEY
主键的特点:
- 主键必须包含唯一的值
- 主键列不能包含NULL值
创建主键方式:
在创建表的时候给字段添加主键
字段名 字段类型 PRIMARY KEY
在已有表中添加主键
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
1.3 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
1.4 主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)
例如:
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) ,
age INT
);
默认地AUTO_INCREMENT 的开始值是1,如果希望修改起始值,请使用下列SQL语法
ALTER TABLE 表名 AUTO_INCREMENT=起始值;
DELETE和TRUNCATE的区别
-
DELETE 删除表中的数据,但不重置AUTO_INCREMENT的值。
-
TRUNCATE 摧毁表,重建表,AUTO_INCREMENT重置为1
2. 唯一
在这张表中这个字段的值不能重复
2.1 唯一约束的基本格式
字段名 字段类型 UNIQUE
2.2 实现唯一约束
CREATE TABLE student(
id INT,
NAME VARCHAR(20) UNIQUE
);
3. 非空
这个字段必须设置值,不能是NULL
3.1 非空约束的基本语法格式
字段名 字段类型 NOT NULL
4. 默认值
往表中添加数据时,如果不指定这个字段的数据,就使用默认值
默认值格式
字段名 字段类型 DEFAULT 默认值
如果一个字段设置了非空与唯一约束,该字段与主键的区别
- 一张表中只有一个字段可以设置为主键
- 一张表中可以多个字段非空与唯一约束
- 主键可以自动增长,非空与唯一约束的字段不能自动增长
create table 表名(
字段 字段类型 primary key auto_increment,
字段 字段类型 [约束],
.......
);
5. 外键
5. 1 单表的缺点
缺点:表中出现了很多重复的数据(数据冗余)
5.2 什么是外键约束
一张表中的某个字段引用另一个表的主键
主表: 约束别人
副表/从表: 使用别人的数据,被别人约束
5.3 创建外键
- 新建表时增加外键:
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
关键字解释:
CONSTRAINT – 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES – 主表名(主键字段名) 表示参照主表中的某个字段
- 已有表增加外键:
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);
5.4 删除外键
ALTER TABLE 从表 drop foreign key 外键名称;
5.5 外键的级联
什么是级联操作:
在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
ON UPDATE CASCADE – 级联更新,主键发生更新时,外键也会更新
ON DELETE CASCADE – 级联删除,主键发生删除时,外键也会删除
四.表关系
1. 一对一
两种建表原则:
- 外键唯一:主表的主键和从表的外键(唯一),形成主外键关系,外键唯一UNIQUE
- 外键是主键:主表的主键和从表的主键,形成主外键关系
2. 一对多
一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键
3. 多对多
多对多关系建表原则:
需要创建第三张表(中间表),中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。
五.三范式
1. 什么是范式
范式是指:设计数据库表的规则(Normal Form)
好的数据库设计对数据的存储性能和后期的程序开发,都会产生重要的影响。建立科学的,规范的数据库就需要满足一些规则来优化数据的设计和存储
2. 范式的基本分类
目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。
3. 第一范式
即数据库表的每一列都是不可分割的原子数据项,而不能是集合、数组、记录等非原子数据项。即实体中的某个属性有多个值时,必须拆分为不同的属性。在符合第一范式(1NF)表中每个列的值只能是表的一个属性或一个属性的一部分。简而言之,第一范式每一列不可再拆分,称为原子性。
第一范式:每一列不能再拆分
总结:
如果不遵守第一范式,查询出数据还需要进一步处理(查询不方便)。遵守第一范式,需要什么字段的数据就查询什么数据(方便查询)。
4. 第二范式
在满足第一范式的前提下,表中的每一个字段都完全依赖于主键,不能局部依赖主键
所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之,第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候,才会发生不符合第二范式的情况。比如有一个主键有两个列,不能存在这样的属性,它只依赖于其中一个列,这就是不符合第二范式。
第二范式的特点:
- 一张表只描述一件事情。
- 表中的每一列都完全依赖于主键
总结:
如果不准守第二范式,数据冗余,相同数据无法区分。遵守第二范式减少数据冗余,通过主键区分相同数据。
5. 第三范式
在满足第二范式的前提下,表中的每一列都直接依赖于主键,而不是通过其它的列来间接依赖于主键。
简而言之,第三范式就是所有列不依赖于其它非主键列,也就是在满足2NF的基础上,任何非主列不得传递依赖于主键。所谓传递依赖,指的是如果存在"A → B → C"的决定关系,则C传递依赖于A。因此,满足第三范式的数据库表应该不存在如下依赖关系:主键列 → 非主键列x → 非主键列y
第三范式:从表的外键必须使用主表的主键
总结:
如果不遵守第三范式,可能会有相同数据无法区分,修改数据的时候多张表都需要修改(不方便修改)。遵守第三范式通过id可以区分相同数据,修改数据的时候只需要修改一张表(方便修改)。