目录标题
- 一、MYSQL的那些约束你掌握了几种?
- 二、表与表之间的三种关系
- 三、查询
- 3.1 :聚合查询
- 3.2、GROUP BY
- 3.3、HAVING和group by 搭配使用
- 3.4、联合查询
- 3.4.1内连接
- 3.4.2 外连接
- 3.4.3 自连接
- 3.5、子查询
- 3.5.1单行子查询
- 3.5.2多行子查询
- 3.6、合并查询
- 四、结尾
一、MYSQL的那些约束你掌握了几种?
在MYQSL中,有那么一些约束。比如不能为null,或者是必须唯一的。就像身份证一样,每个人的身份证号一定是唯一的,性别可以是男,也可以是女,但不是为null。
下面介绍MYSQL中的6种约束
1、NOT NULL :约束某列不能为空。
-- 创建一个图书表,id不能为空
create table book(id int not null);
2、UNIQUE : 约束某列的某行有唯一值。
-- 创建一个图书表,id唯一,不能重复
create table book(id int unique);
3、DEFAULT : 没有给列赋值时给一个默认值。
-- 创建一个语文书表,默认name为unknown
create table book(name varchar(20) default
'unknown');
4、PRIMARY KEY :NOT NUL和 UNIQUE的结合,即不能为空, 值必须唯一。一般表的ID会设成primary key,这样的好处是为表创立一个唯一的标识。能够更方便的找到表中特殊的信息。一般常与auto_increment连用,表示自增主键,
-- 创建一个图书,每个图书有自己的名称,价格,但序列号是唯一的
create table book(
id int primary key auto_increment,
name varchar(20),
price int );
5、FOREIGN KEY :外键约束。一个表的数据和另一个表匹配,增加耦合性。
-- 创建班级表,
create table class(
id int primary key auto_increment,
name varchar(20)
);
-- 学生表,一个学生对应一个班级,一个班级可以有多个学生
create table student(
id int primary key auto_increment,
name varchar(20),
classes_id int ,
foreign key (classes_id) references class(id)
);
6、CHECK : 保证列中的值符合特定的条件。
-- MySQL使用时不报错,但忽略该约束:
create table student(
id int primary key auto_increment,
name varchar(20),
sex varchar(1),
check (sex='男' or sex='女')
);
二、表与表之间的三种关系
1、一对一关系
eg:人和身份照号码
一个人只能拥有一个身份证号码
2、一对多关系
eg:学生和班级的关系
一个班级可以有多个学生,一个学生只属于一个班级
3、多对多关系
eg:老师和班级的关系
一个班级可以有多个老师,一个老师也可以教多个班。
三、查询
3.1 :聚合查询
聚合查询通过4中聚合函数完成。他们分别是:**
1、count() :返回查询到的数量,和查询类型是不是数字无关。
-- 查询图书表有多少书
select count(*) from book;
2、sum() :返回查询数据的总和,如果数据不是数字就没有意义
-- 查询三年二班有多少学生
select sum(student) from class;
3、avg() : 返回查询的数据的平均数,如果数据不是数字没有意义
-- 查询三年二班学生数学的平均分
select avg(math) from class;
4、max() : 返回查询的数据的最大值,如果数据不是数字没有意义
-- 查询三年二班数学的最高分
select max(math) from class;
5、min(): 返回查询的数据的最小值,如果数据不是数字没有意义
-- 查询三年二班数学的最低分
select min(math) from class;
3.2、GROUP BY
使用group by字句可以对指定列进行分组查询。但是需要满足,select后的
字段必须是分组依据的字段。如果其他字段想出现就必须使用聚合函数。
eg:
准备数据,创建一个学生表,含有学生id,姓名,数学和语文成绩。
create table student(
-- 学生id
id int primary key auto_increment,
-- 学生姓名
student_name varchar(20),
-- 学生数学成绩
math int,
-- 学生语文成绩
chinese int
);
插入数据
insert into student(student_name,math,chinese) values
('张三',66,67),
('李四',44,98),
('王五',88,89);
查看当前表的数据
使用GROUP BY查询,学生的最高、平均、最低数学成绩
3.3、HAVING和group by 搭配使用
在使用group by 字句后,如果还需要条件筛选的话,就不能用where了,需要用having来筛选条件。
-- 查询总成绩大于150的同学
select student_name , sum(math+chinese) from student group by student_name having sum(math+chinses)>150;
结果只有王五同学总分大于150.
3.4、联合查询
上面的查询都是在一张表中进行查询,但实际应用的过程中,经常是在几张表中查询数据,这就用到了联合查询。多张表查询就是对这些表取笛卡尔积,在筛选有用的数据。学习联合查询之前,需要先了解什么笛卡尔积。
总得来说,笛卡尔积就是罗列出两张表所有可能出现的数据。
准备数据:
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;
create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));
create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) ,
classes_id int);
create table course(id int primary key auto_increment, name varchar(20));
create table score(score decimal(3, 1), student_id int, course_id int);
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
3.4.1内连接
语法:
1、select 字段名 from 表1 +别名(可不写) inner join 表2+别名(可不写) on
连接条件 and 其他条件
示例:
查询白素贞同学的成绩
查询所有同学的总成绩:
当我们写一些比较复杂的SQL语句时,非常建议大家不要一次性写完。这样很容易写错,而SQL调试不了,所以在实际写的时候可以像下面这样,一行一行的写。每次写一个条件,这样就很清楚了。
select student.sn,student.name,student.qq_mail,
sum(score.score) from student
join score
on student.id=score.student_id
group by score.student_id;
2、select 字段名 from 表1 +别名(可以不写) ,表2+别名(可以不写) where 连接条件 and 其他条件
示例:
查询白素贞同学的成绩:
查询所有学生的qq邮箱和总成绩:
这两种方式查询的结果相同。
3.4.2 外连接
内连接和外连接的区别,在于对"空值"的区别。如果表里没有"空值"(不是单纯的NULL,而是泛指两张表的数据对不上),那内连接和外连接就没有区别了。
eg:
先删除成绩和学生表,新建一份在加上3个同学。
create table student(id int primary key auto_increment,name varchar(20));
create table score(studentId int ,score int );
insert into student values(null,'张三');
insert into student values(null,'李四');
insert into student values(null,'王五');
先来看一下当前表的数据
select * from student;
再给成绩表中插入三行数据,但是做一点点区别。
insert into score values(1,55);
insert into score values(2,85);
insert into score values(4,91);
这里故意将本该是3的王五的id改成4。
这时候发生了数据不对应的情况。王五同学在分数表中没有成绩,id为4的同学在学生表中,没有信息。
这时候如果我们查询两张表的结果会怎么样呢?
查询每位同学的成绩,姓名信息。
select student.name,score.score from student join
score on student.id=score.studentId;
内连接最后的结果既没有王五同学的成绩和姓名信息,也没有id为4的同学的信息。
内连接相当于对上面两张表取了交集。
外连接相对于内连接来说,分为左外连接和右外连接。在写法上和内连接很相似,只需要在原来的语句中对于join前再加上left或者是right就表示内连接或者外连接了
左外连接:最终结果以join左侧的表为主,尽可能的显示左侧表的信息。
select student.name,score.score from student left
join
score on student.id=score.studentId;
右外连接:最终结果以join右侧的表为主,尽可能的显示右侧表的信息。
select student.name,score.score from student right
join
score on student.id=score.studentId;
3.4.3 自连接
自连接就是同一张表连接自身进行查询。
eg:查询语文成绩比英语成绩高的信息
这次查询和以往不同的是这是针对行和行的查询,之前的例如查询每位同学的成绩,姓名信息。这都是列与列之间的关系。
既然不好解决问题,不妨换个思路,那我就不进行行和行比较了,我想办法转换成列和列比较。
1、先看看它的笛卡尔积长什么样
需要注意的是如果你直接select * from score,score ;查询的话是会报错的。需要你使用as起一个别名。
select * from score as a,score as b;
最终结果有四百行,我这里放一部分。
接下来在进一步筛选有用的信息,挑选出同一个同学的信息
select * from score as a,score as b where a.student_id=b.student_id;
现在缩减成了62行
最后直接一步到位,但是需要先回头看看语文和英语的id,一个是4,另一个是6
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
这样我们在通过课程id筛选
select * from score as a,score as b
where a.student_id=b.student_id and a.course_id=4 and b.course_id=6;
最终结果就只剩下一了。
再来练习一下java比计算机原理分数高的同学的信息,我们这次直接到上图的那一步。
select * from score as a,score as b
where a.student_id=b.student_id and a.course_id=1 and b.course_id=3;
course_id为1的是java的分数,course_id为3的是计算机原理的分数
这样下来,一行不仅包含了java的列,还包含了计算机原理的列。通过自连接就完成了行转列的效果。
来看看最终结果,计算机原理成绩比JAVA高的信息
select * from score as a,score as b
where a.student_id=b.student_id and a.course_id=1 and b.course_id=3 and a.score<b.score;
3.5、子查询
子查询就是其他sql语句中的查询语句。所以也叫做嵌套查询。
3.5.1单行子查询
返回一行子查询的就是单行子查询。
eg:查询和“不想毕业”同学的同班同学。
还是一步步来,先查询不想毕业同学的班级id。
select classes_id from student where name='不想毕业';
不想毕业同学在一班。在通过一班这个信息找他的同学。
select name from student where classes_id=1;
我们用两次查询得到了想要的结果,那试试子查询怎么样。
select name from student where
classes_id=(select classes_id from student where name='不想毕业');
在上面的SQL语句中先执行第二个SQL语句作为前面查询name的条件,这就是子查询。
3.5.2多行子查询
多行子查询返回的是多条记录
eg:查询语文或者英语成绩的信息
1、先查询语文或者英语成绩的课程id
select id from course where name='语文' or name='英文';
2、再在课程表中,根据课程id找对应的信息
select * from score where course_id=4 or course_id=6;
再用多行子查询试试
多行子查询得到的是多条记录,外层查询需要使用in关键字。单行子查询只返回一条记录直接用=即可。
select * from score where course_id in(
select id from course where name='语文' or name='英文');
3.6、合并查询
把多个select查询的结果合并成一个结果就是合并查询。有些像取并集。它包含两个关键字
union : 针对重复的进行去重
union all 不会针对重复的去重
union这有一个前提是两边的查询得到的结果的列是一致的,包括数量和类型。
eg:查询id小于3,或者名字为“英文”的课程
select * from course where id<3 or name ='英文';
使用union查询
select * from course where id<3 union select * from course where name='英文';
效果是一样的。
四、结尾
上面就是这次的所有内容了,祝大家工作顺利,学习顺利。冲冲冲