mysql查询select的进阶，多表查询，子查询，四种连接查询。_mysql select 连接

我是第二章。
这是第一章。

select特殊功能：

distinct(排重)：select distinct 字段名 from 数据表;

between…and：select * from 数据表 where 字段名 between 值 and 值; 相当于select * from 数据表 where 字段名>字段值 and 字段名<字段值;

in：select * from 数据表 where 字段名 in(85，86，87);

or：select * from 数据表 where 字段名1 = ‘值’ or 字段名2 = ‘值’;(升序查询 asc从小到大)select * from 数据表 order by 字段名 asc; order by：系统默认是升序 desc降序（从大到小）

count(统计)：select count(*) from 数据表 where 字段名 = 值; (聚合函数)

avg(平均)：select avg(字段名) from 数据表 where 字段名 = 值;(聚合函数)

as：起别名

like(%)：模糊查询 %代表任意字段

not like：模糊查询相反

order by(降序查询 desc从大到小)：select * from 数据表 order by 字段名 desc;

group by：需要和 聚合函数（例如：max（），count（），avg（）等）配合使用，使用时至少有一个分组标识字段（例如某一列的列名），分组标识的字段如果有多个相同的，那么搜索结果中只会出现第一次查到的这个字段的记录的信息，可以通过having

having(分组条件)：作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

可以包含聚集函数，但是where不能包含聚集函数

year(sbirthday)：查询出生年份 year(now())：查看当前年份

union：把两个查询的结果连接起来

any：字段中 任意的某条记录

all：字段中 所有的记录

count(*)：函数返回由select语句返回的结果集中的行数

max(字段名)：字段中的最大值

min(字段名)：字段中的最小值

查询字段中的最大最小：

一：（子查询）

select * from 数据表 where 字段名 = (select max(字段名) from 数据表);

1.找到最高分：select max(字段名) from 数据表;

2.根据最高分查询考生信息：select * from 数据表 where 字段名 = (select max(字段名) from 数据表);

二：（排序）

select * from 数据表 order by 字段名 desc limit 0,1;

limit：第一个数字是从多少条开始，第二个数字是查多少条

这个是降序，限制获得第一条，第一条就是最大的，获得最大值

注意：排序有缺陷，如果最大值有两条，她只能查到一条

查询score表中至少有两名学生选修的并以3开头的课程的平均分数

having(分组条件)：having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。

select cno from score group by cno having count(cno)>=2 and cno like '3%'; 
1

%代表的是任意的东西 3%代表以3开头的任意东西

多表查询：

精髓：他们是根据相等的地方进行联系的

两张表：

查询当条件相同时，把数据替换掉

select sname,cno,degree from student,score where student.sno = score.sno;
1

三张表：

select sname,cname,degree from student,course,score where student.sno = score.sno                                                                 
and course.cno = score.cno;
1
2

子查询：

精髓：分开步骤去查

查一个班的学生：

select * from student where class = '95031';
1

查学生的分数：

select * from score where class = '95031';
1

查一个班学生的平均成绩：

select cno,avg(degree)  from score  where sno in (select sno from student where class = '95031') group by cno;
1

查询选修3-105课程成绩高于109号同学3-105成绩的所有同学的记录

select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');
1

查询成绩高于学号为109、课程号为3-105的成绩的记录

select * from* score where degree>(select degree from score where sno='109' and cno='3-105');
1

查询与学号为108和101的同学同年出生的所有学生

查询学号为108和101的学生：

select * from student where sno in(108,101);
1

查询学号为108和101学生的出生年份：

select <u>*year(sbirthday)*</u> from student where sno in(108,101);
1

select * from student where year(sbirthday) in(select year(sbirthday) from student where sno in(108,101));
1

查询张旭教师任课的学生成绩：

教师表中查询tno：select tno from teacher where tname=‘张旭’;

查询他任的课cno：

select cno from cource where tno=(select tno from teacher where tname='张旭');
1

select * from score where cno in(select cno from cource where tno=(select tno from teacher where tname='张旭'));
1

查询某课程的同学数多余5人的教师姓名：

课程数大于五的cno：select cno from score group by cno having count(*)>5;

查询tno：

select tno from course where cno in(select cno from score group by cno having count(*)>5);
1

select tname from teacher where tno in(select tno from course where cno=(select cno from score group by cno having count(*)>5));
1

查看计算机系和电子工程系不同职称的教师的tname和pref

select prof from teacher where depart = '电子工程系' and prof not in(select * from teacher where depart = '计算机系')
1

union

select * from teacher where depart = '计算机系' and prof not in(select prof from teacher where depart = '电子工程系' );
1

查询选修学号为3-105课程且成绩至少高于选修编号3-245的同学的Cno、Sno和Degree，并按Degree从高到底依次排序

select cno,sno,degree from score                                         
where cno='3-105'
and degree><u>**any**</u>(select degree from score where cno='3-245')   
order by degree desc;
1
2
3
4

//从高到低

复制表数据做条件查询：

select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);
1

查询至少有两名男生的班级：

select class from student where ssex = '男' group by class having count(*) > 1;
1

按等级查询：

select sno,cno,grade from score,grade where degree between low and upp;
1

sql的四种链接查询：

内链接：

inner join 或者 join

外连接：

1.左连接：left join 或者 left outer join

2.右连接：right join 或者 right outer join

3.完全外连接：full join 或者 full outer join

person表：id，name，cardId

card表：id，name

person表中的cardId来自于card表中的id

create table person(

​ id int,

​ name varchar(20),

​ cardId int

);

create table card(

​ id int,

​ name varchar(20)

);

insert into card values(1,‘饭卡’);

insert into card values(2,‘建行卡’);

insert into card values(3,‘农行卡’);

insert into card values(4,‘工商卡’);

insert into card values(5,‘邮政卡’);

insert into person values(1,‘张三’,1);

insert into person values(2,‘李四’,3);

insert into person values(3,‘王五’,6);

并没有创建外键，如果创建的话person里面的第三条数据插不进去

inner join查询：(内连接)

select * from person p inner join card c on p.cardId = c.id;
1

select * from person p, card c where c.id = p.cardId;
1

内联查询：其实就是相当于把两个表里面的数据，通过某个字段相对应，把有关系的数据查询出来

mysql> select * from person p, card c where c.id = p.cardId;
±—±-----±-------±—±-------+
| id | name | cardId | id | name |
±—±-----±-------±—±-------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
±—±-----±-------±—±-------+

left join:（左外连接)outer

select * from person left join card on person.cardId = card.id;
1

左外连接：会把左边表里的数据全部取出来，而右边表里的数据有就显示出来，没有就会变成NULL

mysql> select * from person left join card on person.cardId = card.id;
±—±-----±-------±-----±-------+
| id | name | cardId | id | name |
±—±-----±-------±-----±-------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
±—±-----±-------±-----±-------+

right join：(右外连接)outer

select * from person right join card on person.cardId = card.id;
1

左外连接：会把右边表里的数据全部取出来，而左边表里的数据有就显示出来，没有就会变成NULL

mysql> select * from person right join card on person.cardId = card.id;
±-----±-----±-------±—±-------+
| id | name | cardId | id | name |
±-----±-----±-------±—±-------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
±-----±-----±-------±—±-------+

full join：（全外连接）

select * from person full join card on person.cardId = card.id;
1

mysql> select * from person full join card on person.cardId = card.id;
1054 - Unknown column ‘person.cardId’ in ‘on clause’

原因是mysql不支持full join

全外连接我们想要的结果：

select * from person left join card on person.cardId = card.id          
union    
select * from person right join card on person.cardId = card.id;     
1
2
3

±-----±-----±-------±-----±-------+
| id | name | cardId | id | name |
±-----±-----±-------±-----±-------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
±-----±-----±-------±-----±-------+

连接的好处：可以不创建外键

第三章