目录
1.数据库约束
1.1null键约束
创建表时,表示某列默认为空。
创建表时,表示某列不能为空。
1.2unique键约束
表示这一列的值是唯一的,不重复的。
由上可知,如果插入两个相同值sn到被unique约束的列里,会出错。
1.3default默认值约束
规定给没有给列赋值时的默认值
1.4primary key 主键约束
主键约束可以看作是unique和not null的结合,它确保了某列(或两个列/多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定记录。
对于整数类型的主键,常配搭自增长auto_increment来使用。插入数据对应字段不给值时,使用最大值+1。
如果上一次插入的是8,那么下次自增之后是以最大的数值8来自增的,就是9。
1.5foreign key 外键约束
foreign key 用来关联其他表的主键或唯一键,保证一个表中的数据匹配另一个表中的值的参照完整性。
详细地说就是如果表B的一个字段(外键)依赖以表A的主键,则称表A为父表,表B为子表。
原则:外键必须依赖于数据库中已经存在的附件的主键,外键可以为空。
作用:建立该表与父表的关联关系。
示例:
创建班级表,id为主键:
创建学生表student,一个学生对应一个班级,一个班级对应多个学生。使用id为主键, classes_id为外键,关联班级表id:
具体用处在后文会提及~~
2.新增
新增就是插入查询结果:
语法:
insert into table_name [(column [, column ... ])] select ...;
案例:创建一张用户表,设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的 学生数据复制进来,可以复制的字段为name、qq_mail
3.查询
3.1聚合查询
常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
函数 | 说明 |
count([distinct]exper) | 返回查询到的数据的 数量 |
sum([distinct]exper) | 返回查询到的数据的 总和,不是数字没有意义 |
avg([distinct]exper) | 返回查询到的数据的 平均值,不是数字没有意义 |
max([distinct]exper) | 返回查询到的数据的 最大值,不是数字没有意义 |
min([distinct]exper) | 返回查询到的数据的 最小值,不是数字没有意义 |
案例:
count:
统计班级共有多少同学
统计班级收集的 qq_mail 有多少个,qq_mail 为 NULL 的数据不会计入结果
sum:
统计数学成绩总分
不及格 < 60 的总分,没有结果,返回 NULL
avg:
统计平均总分
max:
返回英语最高分
min:
返回 > 70 分以上的数学最低分
3.2group by 语句
select 中使用group by子句可以对指定列进行分组查询。需要满足:
1.使用group by进行分组查询时,select指定的字段必须是“分组依据字段”。
2.其他字段若想出现在select中则必须包含在聚合函数中。
语法:
select column1, sum(column2), ... from table group by column1[, column3];
案例:
准备测试表及数据:职员表,有id(主键)、name(姓名)、role(角色)、salary(薪水)
查询每个角色的最高工资、最低工资和平均工资
3.3having 语句
group by 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 where 语句,而需要用 having。
语法:
select column1, sum(column2) from table_name group by column1 [, column3]
having avg(column1) < 数值;
案例:
显示平均工资低于1500的角色和它的平均工资
4.联合查询
前置知识点:
在我们实际的开发应用中,需要使用到的数据来自不同的表,这时候我们就需要进行多表查询。多表查询是对多张表的数据取笛卡尔积:
通过笛卡尔积,我们把两张表汇聚成了一张大表,里面有一些无效的信息,因此我们需要将这些信息进行过滤。
前置操作:
建student、classes、course、score表:
我们先插入数据到student、classes、course、score表,插入数据后结果如下:
4.1内连接:
内连接:仅显示两个表中匹配行,即两表中都有才显示。
语法:
select 字段 from 表1 别名1[inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;
示例:
我们直接查询student表和score表的连接
那么就会产生大量的数据,其中还有许多无用数据:
因此我们就需要加上一些限制条件来过滤无用信息:
案例:
1.查询“许仙”同学的 成绩:
2.查询所有同学的总成绩,及同学的个人信息:
3.查询所有同学的成绩,及同学的个人信息:
三张表联合查询:
建议使用别名,如果不使用别名,如果表1的column与表2的column有重名,那么就有可能辨别不出来:
若不使用重名,也可以使用全名:
4.2外连接
外连接分为左外连接和右外连接:
左外连接:左表有就显示,不论右表。
右外连接:右表有就显示,不论左表。
语法:
左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
右外连接,表2完全显示
select 字段名 from 表名1 right join 表名2 on 连接条件;
案例:
查询所有同学的成绩,及同学的个人信息,如果该同学没有成绩,也需要显示
左外连接:
对应的右外连接:
学生表、成绩表、课程表3张表关联查询
4.3自连接
自连接是指在同一张表连接自身进行查询。
案例:
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
先查询“计算机原理”和“Java”课程的id
再查询成绩表中,“计算机原理”成绩比“Java”成绩 好的信息
也可以使用join on 语句来进行自连接查询
以上查询只显示了成绩信息,并且是分布执行的。现在我们要显示学生及成绩信息,并在一条语句显示:
4.4子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询 。就是将当前查询的结果作为另一个查询的where的选项。
案例:
单行子查询:
返回一行记录的子查询 查询与“不想毕业” 同学的同班同学:
多行子查询:
返回多行记录的子查询 案例:查询“语文”或“英文”课程的成绩信息
使用in
使用not in
可以使用多列包含:
我们先插入重复的分数:score, student_id, course_id列重复:
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),
-- 菩提老祖
(60, 2, 1);
查询重复的分数
使用[not]exists关键字:
exists
not exists
在from子句中使用子查询:子查询语句出现在from子句中。这里要用到数据查询的技巧,把一个 子查询当做一个临时表使用。
查询所有比“中文系2019级3班”平均分高的成绩信息:
我们先获取“中文系2019级3班”的平均分,将其看作临时表
查询成绩表中,比以上临时表平均分高的成绩:
4.5联合查询:
联合查询用来显示多个查询结果的并集,前后查询结果集中,字段要一致。
union 该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中的重复行。
案例:查询id小于3,或者名字为“英文”的课程:
其实用or也不是不行:
union all 和union的区别就是union all不会去掉结果重复的行:
好啦,以上就是本篇的全部内容啦~~