一、数据库约束
1.约束类型
1.NOT NULL: 指示某列不能存储NULL值
-- 创建一个学生表
mysql> create table student
-> (id int not null,
-> sn int unique,
-> name varchar(20) default 'unknown',
-> qq_mail varchar(20)
-> );
-- 插入id为null
mysql> insert into student values(null,01,null,null);
报错:
前面我们对id的约束条件为not null,所以id不能为null。
2.UNIQUE: 可以是一个或者多个字段,保证任意两条数据,唯一约束的字段不能重复
-- 插入一个学生学号为01
mysql> insert into student values(null,01,null,null);
-- 插入第二个学生学号仍为01
mysql> insert into student values(2,01,null,null);
报错:
原因是前边规定了sn的约束条件为unique,有唯一值。
3.DEFAULT: 规定没有给列赋值时的默认值
-- 没有给name列赋值
mysql> insert into student(id,sn,qq_mail) values(3,02,null);
-- 给name列赋值为空
mysql> insert into student values(4,03,null,null);
结果:
插入时,不指定该字段,才能插入默认值
如果指定了,即使null,也会真实的插入null
4.PRIMARY KEY: NOT NULL和UNIQUE的结合,确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速的找到表中的一个特定的记录
应用场景:
用来标识数据库中数据
数据是在不同设备中流传的,一般来说,web开发,数据是在
1.用户浏览器
2.Java程序
3.数据库
在做修改、删除、查询时,就需要知道是哪条数据
如果主键为整型数字,可以指定为auto_increment表示从1开始自增,但是插入数据的时候,不要制定插主键字段
-- 新创建一个班级表
mysql> create table classes (
-> id int primary key auto_increment,
-> name varchar(20),
-> `desc` varchar(50)
-> );
-- 不指定id,插入两个
mysql> insert into classes(name) values('图图');
mysql> insert into classes(name) values('小美');
显示结果:
5.FOREIGN KEY: 保证一个表中的数据匹配另一个表中的值的参照完整性
外键用于关联其他表的主键或唯一值,语法:
foreign key (字段名) references 主表(列)
一对一、一对多、多对多关联时,需要使用外键约束
关联的字段名可以使用主键(最多的方式),其实也可以使用其他字段(如unique字段)
外键的值可以重复:1.一对一关系外键的值就是唯一的
2.一对多的关系外键的值就是重复的表的直接关系只有一对一和一对多、多对多的关系,是在中间表产生的间接关系
6.CHECK: 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句
二、表的设计
三大范式
1.一对一
2.一对多
名称上虽然是一对多,但是理解上要从两方面看待:
1.一个班级关联多个学生
2.一个学生关联一个班级
-- 向classes中插入
mysql> insert into classes(name,`desc`) values
-> ('羽毛球俱乐部',null),
-> ('舞蹈社',null);
-- 创建一个学生表,其中包含对classes的外键
mysql> create table student (
-> id int primary key auto_increment,
-> sn int unique,
-> name varchar(20),
-> qq_mail varchar(100),
-> classes_id int,
-> foreign key(classes_id) references classes(id)
-> );
mysql> insert into student(sn,name,qq_mail,classes_id) values
-> (11,'图图',123,4),
-> (22,'小美',234,5),
-> (33,'壮壮',345,4),
-> (44,'王子',456,4),
-> (55,'小豆丁',567,5);
显示结果:
3.多对多
在一定的业务场景下,两张表产生了多对多的关联关系:
例如学生和课程之间通过考试就建立起多对多的关系
在每次考试发生以后,总共产生了:1x、1y、2x、2y四条数据
设计考试成绩表:
多对多关系,以中间表来记录关系
中间表中包含的关系:
1.id为1的一个学生,有多个课程及成绩
2.id为x的课程,有多个学生
注意:两张表,m:n关联,不直接在两张表产生关联关系。
-- 创建一个课程表
mysql> create table course(
-> id int primary key auto_increment,
-> name varchar(50),
-> `desc` varchar(50)
-> );
-- 插入
mysql> insert into course(name,`desc`) values
-> ('高等数学','难'),
-> ('大学英语','较难');
-- 创建一个考试表,其中有两个外键,一个学生id,一个课程id
mysql> create table exam (
-> id int primary key auto_increment,
-> score decimal(4,2),
-> student_id int,
-> foreign key (student_id) references student(id),
-> course_id int,
-> foreign key (course_id) references course(id)
-> );
-- 插入
mysql> insert into exam(score,student_id,course_id) values
-> (98,6,2),
-> (78,6,3),
-> (32,7,2),
-> (45,7,3),
-> (78,8,2),
-> (90.8,8,3),
-> (35,9,2),
-> (67,9,3),
-> (12,10,2),
-> (99,10,3);
结果:
三、新增
插入查询的结果
语法:
insert into table_name [(column [,column ...])] select ...
-- 再创建一个考试表
mysql> create table exam2 (
-> id int primary key auto_increment,
-> name varchar(20),
-> score decimal(6,2)
-> );
-- 将exam_result中的那么和计算出的总分插入到新创建的考试表中
mysql> insert into exam2(name,score) select name,chinese+math+english from exam_result;
结果:
注意:查询结果不一定等于表字段
四、查询
1.聚合查询
1.聚合函数
所谓的聚合,是根据结果集多条数据,计算汇总为一个数据
函数 | 说明 |
---|---|
count([distinct] expr) | 返回查询到的数据的数量 |
sum([distinct] expr) | 返回查询到的数据的总和 ,不是数字没有意义 |
avg([distinct] expr) | 返回查询到的数据的平均值 ,不是数字没有意义 |
max([distinct] expr) | 返回查询到的数据的最大值 ,不是数字没有意义 |
min([distinct] expr) | 返回查询到的数据的最小值 ,不是数字没有意义 |
1.count
-- 返回列表中有多少个学生
mysql> select count(*) from student;
-- 返回列表中有多少个0
mysql> select count(0) from student;
如下为列表:
结果:
为什么count(0)也是5呢?
原因是之前我们在查询列表时,如果将查询字段写完0,那么该列都会显示为0
所以这里的count(0)是先把列置为0,再计算0的个数,所以和count(*)一样。
注意:
1.count(*)和count(0)、count(1)的结果是一样的
2.select * from 表和select 0 from 表 ,数据和数量的结果都是一样的
2.sum
-- 统计数学成绩的总分
mysql> select sum(math) from exam_result;
-- 统计语文成绩小于60的学生,没有结果,返回NULL
mysql> select id,name,chinese from exam_result where chinese<60;
运行结果:
3.avg
-- 统计平均总分,给其起别名为avgsum
mysql> select avg(math+chinese+english) avgsum from exam_result;
运行结果:
4.max
-- 返回英语最高分
mysql> select id,name,max(english) from exam_result;
5.min
-- 返回70分以上的数学最低分
mysql> select min(math) from exam_result where math>70;
运行结果:
2.GROUP BY语句
语法:
select 查询字段 from 表 group by 分组字段1,分组字段2…
作用:
将分组字段相同的一条或多条数据,合并为一条
注意:
1.如果使用了分组,查询字段只能是分组依据字段(分组字段,聚合函数(任意字段)),其实,可以理解为,分组前后,数据数量一样,其他字段也可以使用(如按unique字段分组,按主键字段分组)
2.如果查询字段有分组后合并的字段,mysql里边不会报错,只显示第一条(此时可能就不是我们想要的结果)
准备测试表及数据:
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);
-- 查询每个角色的最高工资\最低工资和平均工资
mysql> select role,max(salary),min(salary),avg(salary) from emp group by role;
除了role字段,其他的其实都是合并了不存在的,只是取了第一条.
3.HAVING
GROUP BY子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用WHERE语句,而需要用HAVING
语法:
group by 分组字段 having 条件
注意:
1.分组前也可以使用条件过滤,表示先过滤,结果集再进行分组
2.having后的条件,使用的字段必须为有效的分组后的查询字段
-- 把id>3的按照角色进行分组
mysql> select role,sum(salary),avg(salary) from emp where id>3 group by role;
-- 分组后过滤出平均值>10000的
mysql> select role,sum(salary),avg(salary) from emp where id>3 group by role having avg(salary)>10000;
运行结果:
步骤:
1.select * from emp where id>3
2.以上结果集,按照role字段使用group by分组
3.分组后数据在使用avg聚合函数(有效的分组后查询字段)来过滤
2.联合查询
然而实际开发中往往数据来自不同的表,所以需要多表联合查询.多表查询是对多张表的数据取笛卡尔积:
具体要使用哪些表来关联,依赖查询字段(要获取什么字段数据,这些字段在哪些表,就关联哪些表)
语法:
(两张表关联) select * from 表1,表2 (表也可以使用别名 语法: 表名 [as] 别名)
注意:表使用了别名,查询字段也需要是表的别名,该表的字段名.
对于select * from a,b结果集来说
查询字段/结果集字段,为省略的a*,b*
数据为:
1.遍历a表的所有数据
2.a表的m条数据中,每一条数据,关联b表n条数据的每一条数据,产生m*n条件
关联班级表和学生表:
表的别名+字段别名结合使用(几乎都要使用)
准备测试表和数据:
要显示该班级关联的学生,除了将学生表关联班级表作笛卡尔积外,还需要使用一对多关联条件:主外键关联(班级表主键(classes.id)关联学生表的外键(student.classes_id))
1.内连接
语法:
select…from 表1 别名1 join 表2 别名2 on 连接条件 and 其他条件
select…from 表1 别名1 , 表2 别名2 where 连接条件 and 其他条件
select…from表1 别名1 join 表2 别名2 on 连接条件 where 其他条件
上面一个连接就是用的内连接的第二种形式
第一种:
关联查询可以使用两张或者以上的表来关联:
-- 查询许仙同学的成绩,包括课程名称
mysql> select
-> stu.id,
-> stu.name,
-> sco.score,
-> sco.course_id,
-> cou.name course_name
-> from
-> student stu
-> join score sco on stu.id=sco.student_id
-> join course cou on sco.course_id=cou.id
-> and stu.name='许仙';
说明:内连接,查询结果集,必须严格保证所有的条件(包含连接条件)
2.外连接
语法:
左外连接left join 左边的表是外表(表1完全显示)
select 查询字段 from 表1 别名1 left join 表2 别名2 on 连接条件 where 其他条件
外表:表1
右外连接right join 右边的表是外表(表2完全显示)
select 查询字段 from 表1 别名1 right join 表2 别名2 on 连接条件 where 其他条件
外表:表2
注意:
如果外表有的数据,在另一个表没有关联的数据,也会显示(只是说连接条件不满足时,还会使用外表全部数据)
但是其他条件还是会生效
简单地说,就是按其他条件严格过滤后,外表数据全部显示
说明:
对外连接来说,查询结果集:
1.其他条件:完全满足
2.连接条件:(1)满足 或者(2)外表的数据
3.自连接
关联查询的两张表,是同一张表,就是自连接
使用场景:
4.子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
(1)使用在比较操作符后:需要子查询结果集返回1个字段,且只有1条数据
语法:select…from 表 where 字段1=(select 某个字段 from 表)
(2)使用在in/out后:需要子查询返回一个字段,可以有多条数据
语法:select…from 表 where 字段 in (select 某个字段 from 表)
数据库常考的一种题:根据一个或多个字段去重
说明:去重是delect 操作重复数据,只保留一条,这个操作比较复杂
先学习如何查询重复数据
1.group by 去重的字段
2.having count(0)>1
3.in(子查询)查询重复的数据
where exists(子查询)
mysql> select *
-> from score sco
-> where exists(
-> select sco.id
-> from course cou
-> where (name='语文'or name='英文')
-> and cou.id = sco.course_id);
(3)子查询,作为一张不存在的虚拟的临时表,可以关联真实的表来查询
语法:
select * from (select… from 表)tmp
注意:临时表,必须给别名
最简单的,一个可以执行sql,可以直接包裹起来作为临时表
(1)前面加 “select * from(”
(2)后边加 “)临时表别名”
临时表也可以当做一张虚拟表,来关联查询,但是注意,临时表关联的字段是结果集字段.
5.合并查询
在实际应用中,为了合并多个select的执行结果,可以试用集合操作符union,union all.使用union和union all时,前后查询的结果集中,字段需要一致
1.Union
作用:多个结果集,根据查询字段去重后,取并集
注意:是结果集合并,两个结果集,可以是不同的表数据
建议:查询字段,数量,顺序,结果集字段名,保持一致
2.Union all
作用:和union相比,不去重