目录
2.4 primary key :主键约束 一条数据的身份标识~~:
1.mysql 增删改查
下面图片可以翻看我上篇文章查看(细说)
2. 数据库约束
2.1 约束类型
2.1 NULL约束
创建表时,可以指定某列不为空:为id的列 不能为null,否则报错
create table student (id int not null, sn int, name varchar(20));
使用指定列插入时,如果没有修改默认值,默认值就是null;
2.2 unique:唯一约束
顾名思义:被unique修饰的列只能出现一次,否则报错
create table student (id int not null, sn int unique, name varchar(20));
2.3 default:默认值约束
指定插入数据时,name列为空,默认值unkown:
create table student (id int not null, sn int unique, name varchar(20) default 'unkown);
2.4 primary key :主键约束 一条数据的身份标识~~:
1️⃣通过这个约束,来指定某个键为主键:不能为null不能重复. 是not null 和uniqu 的结合
2️⃣整数类型的主键,常配搭自增长auto_increment来使用
create table student (id int primary key, name varchar(20));
自增主键 id默认为null,意思让数据库自行发配一个id
2.5 foregin key :外键约束
涉及到两个表之间的约束
外键用于关联其他表的主键或唯一键,语法: foreign key (字段名) references 主表(列)
创建一个班级表
创建一个学生表
学生表id为主键,classes-id为外键 关联班级表id
注意 :插入/或者修改子表中受约束的数据时,确保父表中存在
比如根据上述 要修改\插入学生表中classes-id时要在成绩表必须存在
要删除\修改时先查看在子表中是否被使用,如果使用中则无法删除或者修改
约束是双向的
2.6 CHECK约束(了解)
create table test_user (
id int,
name varchar(20),
sex varchar(1),
check (sex ='男' or sex='女')
);
3. 表的设计
有一对一,一对多,多对多
4. 新增
-- 将学生表中的所有数据复制到用户表
insert into test_user(id, name) select id, name from student;
5. 查询 *重点
5.1 聚合查询
聚合函数
列和列之间的运算
常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
格式基本为:
select 聚合函数(列名) from 表名;
1.count 返回查询到的数据的 数量
2.sum 返回查询到的数据的 总和,不是数字没有意义
3. avg 返回查询到的数据的 平均值,不是数字没有意义
4.max 返回查询到的数据的 最大值,不是数字没有意义
5.min 返回查询到的数据的 最小值,不是数字没有意义
5.1.1 group by 分组
select 列名1, 聚合函数(列名2) from 表名 group by 列名1;
或者多种聚合函数组成
select 列名1, 聚合函数(列名2), 聚合函数(列名2), 聚合函数(列名2) from 表名 group by 列名1;
select 中使用 group by 子句可以对指定列进行分组查询。需要满足:使用 group by 进行分组查
询时,select 指定的字段必须是“分组依据字段”,其他字段若想出现在select 中则必须包含在聚合函
数中
--分组查询score表中 每个角色的成绩之和;
select 列名1, 聚合函数(行名) from 表名 group by 列名1;
5.1.2 having
当使用group by子句分组以后,如果对分组结果再次进行条件筛选,不能使用where语句,需使用having子句;
select 列名1, 聚合函数(行名) from 表名 group by 列名1 having 聚合函数(行名)条件;
select role,max(score) from emp group by role having avg(score)<1500;
1.count
-- 统计班级有多少名同学 为 NULL 的数据不会计入结果
select count(*) from student;
2.sum
-- 统计score的薪资之和 为 NULL 的数据不会计入结果
select sum(score) from score;
3.avg
--统计score薪资的平均值 为 NULL 的数据不会计入结果
select avg(score) from score;
这里复习下前面学的as 别名
as关键字表示别名 可以忽略但最好不要忽略
4.max
--统计score薪资的最高值 为 NULL 的数据不会计入结果
select max(列名) from 表名;
5.min
--统计score薪资的最低值 为 NULL 的数据不会计入结果
select min(score) from score;
6.2 联合查询 (表连接查询)
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积:
而我们可以通过联合查询,使数据更加完整、明确的展现出来
如何通过联合查询查询结果呢?有以下几个步骤:
- 确定那几张表要参与查询,取这几张表的笛卡尔积(全排列):select * from 表1,表2;
- 根据表与表之间的主外键关系,确定连接条件,过滤无效数据(可根据实际情况继续过滤)
- 通过指定列查询,精简结果集
6.2.1内连接
内连接有两种语法形式
内连接就是要求两个表里面都要有的数据,代码如下:
select * from student, class where student.classId = class.id;
这里的结果少了赵六,因为赵六,只在一个表里面存在。
第一步 :获取相关表的笛卡尔积
第一种语法:
select * from 表1,表2
第二种语法:
//直接写join 没有on 则是完整的笛卡尔积
select * from student join class;
第二步: 确定连接条件,过滤无效数据
联合的表之间必然存在主外键关系,通过主外键值确定连接条件。判断子表外键值和主表主键值是否相等即可,保留相等的,过滤不相等的
第一种语法:
因为主外键字段存在于两个表中,所以我们应该使用 表名.字段 的形式,指定哪个表中的哪个字段
select * from student, class where student.class_id = class.id;
第二种语法:
select * from student join class on student.class_id = class.id;
第三步 根据需求进行分组
上图中,我们可以发现表中有好多个无用字段如:class_id、id,我们可以通过 select 表名.列名 的形式,抽取出有用的字段来精简查询结果,
注意:因为存在多个表,所以一定是 select 表名.列名 的形式,明确哪个表中的哪个字段!
可以选择别名的方式
通过 表名.列明 的方式是必要的,但是使得书写的代码量增多,我们可以通过给表起别名的方式简化代码,我们可以在 from 表名 后给表起一个更加简短的别名,这样我们就这样在select和where子句中缩短表名长度:
6.2.2 练习
--查出许仙同学的成绩
6.2.3外连接
注意*
内连接
外连接
-- 左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
使用on表示连接条件
-- 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
全外连接:outer join
6.2.4 自连接
自连接是指在同一张表连接自身进行查询
select * from score as s1, score as s2 where s1.student_id = s2.student_id;
这样筛选之后的就是 id 相等的结果了,如果再加一些条件的话,筛选效果就更明显了
select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id=1;
这样筛选之后的结果就更精确了,运行结果如下:
6.2.5 子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
首先先找到不想毕业同学的班级
select classes_id from student where name = '不想毕业';
这里就是先查询出班级,然后再查询班级为 1 的同学就好了
select name from student where classes_id = 1;
将 SQL 语句合并在一起,也就是子查询:
select name from student where classes_id = (select classes_id from student where name =
'不想毕业');
运行结果:
6.2.6合并查询
在实际应用中,为了合并多个select的执行结果,可以使用集合操作符 union,union all。使用UNION
和UNION ALL时,前后查询的结果集中,字段需要一致。
--union (两个查询结果取并集。可以自动去重)
合并查询:把多个查询语句的结果合并到一起了。通过 union 来实现:把查询的结果放到一起。对两个查询结果取并集。可以自动去重。代码如下
select * from course where name = '英文' union select * from course where id < 3;
也可以使用 or 实现,不过使用 or 的时候必须保证是针对同一个表来进行操作的。代码如下:
select * from coures where name = '英文' or id < 3;
--union all (不会去掉结果集中的重复行)
select * from course where id<3 union all select * from course where name='英文';