MySQL进阶（一）：DQL其他字段详解，数据库表的约束，表和表之间的关系，数据库设计

--查询语法
       select
		字段列表
	from
		表名列表
	where
		条件列表
	group by
		分组字段
	having
		分组之后的条件
	order by
		排序
	limit
		分页限定

目录

使用SQL语句排序（order by）

使用聚合函数

使用分组和组内过滤(group by，having）

限制查询记录的条数（limit）

数据库备份和还原

数据库表的约束

---

使用SQL语句排序（order by）

通过 ORDER BY 子句，可以将查询出的结果进行排序(排序只是显示方式，不会影响数据库中数据的顺序)

--排序语句
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];--方括号表示可选的字段
ASC: 升序，默认值
DESC: 降序

--1.单列排序：只按某一个字段进行排序
select * from student order by age desc; --eg：查询所有数据，适用年龄降序排序

--2.组合排序：同时对多个字段进行排序，如果第1个字段相等，则按第2个字段排序，依次类推。
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
select * from student order by age desc, math asc; --eg：查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序

使用聚合函数

之前的查询语句都是横向查询，它们都是根据条件 一行一行 的进行判断，而是用聚合函数查询是纵向查询，它是对 一列 的值进行计算，然后返回一个结果值。聚合函数会忽略空值NULL。
五个聚合函数：

-- 使用聚合函数的语法
SELECT 聚合函数(列名) FROM 表名;

-- 查询学生总数
select count(ifnull(id,0)) 总人数 from student; --对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列
-- 查询年龄大于20的总数
select count(*) from student where age>20;
-- 查询数学成绩总分
select sum(math) 总分 from student;
-- 查询数学成绩平均分
select avg(math) 平均分 from student;
-- 查询数学成绩最高分
select max(math) 最高分 from student;
-- 查询数学成绩最低分
select min(math) 最低分 from student;

使用分组和组内过滤(group by，having）

分组查询是指使用 GROUP BY 语句对查询信息进行分组，相同数据作为一组。

GROUP BY分组的方式：将分组字段结果中相同内容作为一组，如按性别将学生分成2组。GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。

-- 语法
SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];


-- 按性别进行分组，求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
-- 查询男女各多少人
select sex, count(*) from student3 group by sex;

-- 查询年龄大于25岁的人,按性别分组,统计每组的人数
select sex, count(*) from student3 where age > 25 group by sex ;

-- 查询年龄大于25岁的人，按性别分组，统计每组的人数，并只显示性别人数大于2的数据
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2; -- 错误写法
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex having COUNT(*) >2; -- 对分组查询的结果再进行过滤

where和having的区别

限制查询记录的条数（limit）

limit语句常用来分页，比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一页显示固定的条数。 假设我们每页显示5条记录的方式来分页。

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];

-- limit语法格式
LIMIT offset,length;  
offset：起始行数，从0开始计数，如果省略，默认就是0
length： 返回的行数，如果不够行数，有多少显示多少

-- 查询学生表中数据，从第3条开始显示，显示6条。
select * from student3 limit 2,6;

数据库备份和还原

在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

（1）备份语句: DOS下(也就是命令行)，未登录的时候。这是一个可执行文件exe，在bin文件夹。

# 备份格式： DOS下，未登录的时候。这是一个可执行文件exe，在bin文件夹
mysqldump -u用户名 -p密码 数据库 > 文件的路径

# 备份day21数据库中的数据到d:\day21.sql文件中
mysqldump -uroot -proot day21 > d:/day21.sql # 导出结果：数据库中的所有表和数据都会导出成SQL语句

（2）还原语句：mysql中（也就是SQL语句），需要登录后才可以操作。

-- 还原格式：mysql中的命令，需要登录后才可以操作
USE 数据库；
SOURCE 导入文件的路径;

-- eg：还原day21数据库中的数据，注意：还原的时候需要先登录MySQL,并选中对应的数据库。
use day21; -- day21数据库里面的表已经全被删除是，要对其进行还原
source d:/day21.sql; -- 将本地备份好的数据还原到day21数据库中

此外还可以使用图形化工具来进行备份还原操作。

数据库表的约束

数据库表约束的作用和种类

约束的作用：对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束的种类：有5种，分别是主键约束、唯一约束、非空约束、外键约束、检查约束。

5种约束详解

1.主键约束

主键的作用：用来唯一标识数据库中的每一条记录。

主键字段的选取：通常不用业务字段作为主键，而是单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键没有含义无所谓，只要不重复，并且非空就可以（即非空not null，唯一）。

创建主键：
       （1）一般在创建表的时候添加主键。primary key表示主键

语法：字段名 字段类型 PRIMARY KEY,

-- eg
create table st5 (
id int primary key, -- id为主键
name varchar(20),
age int
)
desc st5;

       （2）在已有表中添加主键。使用add操作，primary key表示主键

语法：ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
-- eg：
alter table st5 add primary key(id); -- 添加id字段为主键