MySQL 表的约束与数据库设计
第1节 数据库的基础语法
1.1 数据库入门
1.1.1 SQL 语句的分类:
- DDL 数据定义语言
- DML 数据操作语言
- DQL 数据查询语言
- DCL 数据控制语言
1.1.2 MySQL 管理数据库
➽ 查看所有数据库
show databases;
➽ 创建数据库
create database 库名;
➽ 查看数据库创建数据的语句:
show create database 库名;
➽ 删除数据库
drop database 库名;
1.2 表的管理表
➽ 查看所有表
show tables;
➽ 创建表(student(id,name,age))
create table student(
id int,
name varchar(20),
age int
)
1.3 查看表结构:
➽ 以 sql 格式返回
show create table 表名;
➽ 以表格格式返回
desc 表名;
1.4 删除表
drop table 表名;
1.5 管理数据:数据增删改
➽ 插入数据
insert into 表名 (列名) values (值);
➽ 修改数据
update 表名 set 列名=值 where 条件
➽ 删除数据
➽ 1. 删除表中的所有数据
delete from 表名 where 条件
➽ 2. 删除所有数据
truncate [table] 表名
1.6 查询数据
➽ 查询所有列
select * from 表名
➽ 查询时指定别名
as 可以省略
➽ 去除重复数据
distinct 例如:select distinct name from student
1.7 条件查询
1) 显示在某一区间的值:80~100 between 80 and 100
2)多个条件中符合 1 个值: in
3) 模糊查询:like % 匹配多个字符 _ 匹配 1 个
第2节 学习目标
- 能够使用 SQL 语句进行排序
- 能够使用聚合函数
- 能够使用 SQL 语句进行分组查询
- 能够完成数据的备份和恢复
- 能够使用 SQL 语句添加主键、外键、唯一、非空约束
- 能够说出多表之间的关系及其建表原则
- 能够理解三大范式
第3节 DQL 查询语句
3.1 排序
通过 ORDER BY 子句,可以将查询出的结果进行排序(排序只是显示方式,不会影响数据库中数据的顺序)
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
ASC: 升序,默认值
DESC: 降序
3.1.1 单列排序
什么是单列排序:
只按某一个字段进行排序,单列排序。
3.1.2 实现不同数据库之间表的复制
– 查询所有数据,使用年龄降序排序
select * from student order by age desc;
3.1.3 组合排序
什么是组合排序?
同时对多个字段进行排序,如果第 1 个字段相等,则按第 2 个字段排序,依次类推。
组合排序的语法:
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 1 [ASC|DESC], 字段名 2 [ASC|DESC];
– 查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序
select * from student order by age desc, math asc;
3.2 聚合函数
之前我们做的查询都是横向查询,它们都是根据条件一行一行的进行判断,而使用聚合函数查询是纵向查询,
它是对一列的值进行计算,然后返回一个结果值。聚合函数会忽略空值 NULL。
3.2.1 五个聚合函数
SQL 中的聚合函数 | 作用 |
---|---|
max(列名) | 求这一列的最大值 |
min(列名) | 求这一列的最小值 |
sum(列名) | 对这一列求总和 |
count(列名) | 统计这一列有多少条记录 |
avg(列名) | 求这一列的平均值 |
3.2.2 语法:
SELECT 聚合函数(列名) FROM 表名;
– 查询学生总数
- select count(id) as 总人数 from student;
- select count(*) as 总人数 from student;
- 我们发现对于 NULL 的记录不会统计,建议如果统计个数则不要使用有可能为 null 的列,但如果需要把 NULL 也统计进去呢?
IFNULL(列名,默认值) 如果列名不为空,返回这列的值。如果为 NULL,则返回默认值。
– 查询 id 字段,如果为 null,则使用 0 代替 - select ifnull(id,0) from student;
我们可以利用 IFNULL()函数,如果记录为 NULL,给个默认值,这样统计的数据就不会遗漏 - select count(ifnull(id,0)) from student;
- – 查询年龄大于 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;
3.3 分组
-
分组查询是指使用 GROUP BY 语句对查询信息进行分组,相同数据作为一组
-
SELECT 字段 1,字段 2… FROM 表名 GROUP BY 分组字段 [HAVING 条件];
GROUP BY 怎么分组的?
将分组字段结果中相同内容作为一组,如按性别将学生分成 2 组。
GROUP BY 将分组字段结果中相同内容作为一组,并且返回每组的第一条数据,所以单独分组没什么用处。
分组的目的就是为了统计,一般分组会跟聚合函数一起使用。
– 按性别进行分组,求男生和女生数学的平均分
select sex, avg(math) from student3 group by sex;
效果如下:
实际上是将每组的 math 求了平均,返回每组统计的结果
注意:当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的
查询男女各多少人
- 查询所有数据,按性别分组。
- 统计每组人数
- select sex, count(*) from student3 gro
查询年龄大于 25 岁的人,按性别分组,统计每组的人数
- 先过滤掉年龄小于 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;
只有分组后人数大于 2 的男
这组数据显示出来
3.3.1 having 与 where 的区别
子名 | 作用 |
---|---|
where 子句 | 1) 对查询结果进行分组前,将不符合 where 条件的行去掉,即在分组之前过滤数据,即先过滤再分组。2) where 后面不可以使用聚合函数 |
having 子句 | 1) having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤。2) having 后面可以使用聚合函数 |