MySQL-DQL（数据查询语言）

数据查询语言(DQL-Data Query Language)
代表关键字：select

MySQL语句执行顺序
在这里插入图片描述

1、基础操作

1.1 启动服务

a.手动启动
我的电脑->右键->管理->服务->mysql->右键启动/启动

b.命令方式
在管理员模式下运行cmd,执行如下操作：

net start mysql --启动服务
net stop mysql --关闭服务

1.2 命令连接数据库（客户端连接数据库）

运行cmd,执行如下操作：

mysql -h 主机名 -u用户名 -p密码

在这里插入图片描述

退出数据库命令：

quit;
exit;

1.3 防止乱码

set names gbk;

注意：为了防止乱码，所以登录成功后，就需要设置字符集。登录成功后，以后的语句都必须添加分号
在这里插入图片描述

1.4 查看所有的数据库

show databases;

在这里插入图片描述

1.5 创建数据库

create database 数据库名字 charset utf8 [collate 校对规则名];

字符集名类似这些：utf8,gbk,gb2312,big5,ascii等。推荐用utf8.

校对规则名：通常都不用写，而是使用所设定字符集的默认校对规则。说明：在定义语法结构的时候，如果加有中括号，代表的意思可以不写
在这里插入图片描述

1.6 查看数据库创建信息

show create database 数据库名;

在这里插入图片描述

1.7 删除数据库

drop database 数据库名;

在这里插入图片描述

1.8 选择某个数据库

一个项目中，具体进行有关数据操作（增删改查）之前，都需要先“选择/进入”该数据库。

use 数据库名;

在这里插入图片描述

2. 数据查询

2.1 基础查询

select 字段 from 表 [where 条件];

案例：

--查询不重复的列
select distinct depart from teacher;
--统计满足条件的数据行数
select count(*) from student where class='95031';
--查询Score表中的最高分的学生学号和课程号。
select * from score order by degree desc limit 0,1;--排序之后，读取数据是从索引0开始，截取第0-1个，但是不包含1
--查询所有的学生信息select id,name,sex,telphone,age from student;--查询出所有的学生信息select * from student;--查询出所有的学生信息select name,telphone from student;--只查出用户名和电话号码
--查询id=1的学生信息select * from student where id=1;
--查询所有的男学生select * from student where sex='男';
--查询年龄大于18的学生信息select * from student where age>18;
--查询性别为'男'且年龄大于20的学生select * from student where sex='男' and age>20;
--查询姓名为张三和里斯的学生select * from student where name='张三' or name='里斯';select * from student where name in('张三','里斯');
--查询年龄在19-23之间，包含19和23select * from student where age>=19 and age<=23;select * from student where age between 19 and 23;
--查询不是张三也不是里斯的所有学生select * from student where name<>'张三' and name<>'里斯';select * from student where name not in('张三','里斯');
--模糊查询，likeselect * from student where name like '小';--where name='小'select * from student where name like '小%';--name以小开头的所有学生信息select * from student where name like '%小';--name以小结尾的所有学生信息select * from student where name like '%小%';--name包含小的所有的学生信息select * from student where name like '%小%大%';--name 既包含小有包含大的所有学生信息
--查询所有以小开头的学生信息select * from student where name like '小%';
--排序查询select * from student order by id;--根据id进行查询，order by 默认是顺序，ascselect * from student order by id asc;select * from student order by id desc;--根据id倒序查询--聚合查询count(),sum(),avg(),max(),min()

2.2 高级查询

2.2.1 高级查询语法概述

一个查询语句语法概述

select 子句

[from 子句]

[where 子句]

[group by 子句]

[having 子句]

[order by 子句]

[limit 子句]

可见，select语句还是比较复杂的——其实是mysql中最复杂的语句
总体说明：
A. 以上中括号中的任一项都可以省略，但如果不省略，就应该按照顺序出现

B. 通常，from后的子句都需要有from子句，having子句需要有group by 子句

C. 这些子句的”执行顺序”,也是按此顺序进行的

2.2.2 查询结果数据及select选项

查询“固定值”

例：

select 1;
select 2,'abc';
select 3,now();
select 1 as '序号';
select 1 as '序号','张三' as '姓名','20221023' as '学号';
select 1 as '学号','张三' as '姓名' union select 2,'里斯' union select 3,'王五';

在这里插入图片描述

说明：
(1) now()，代表的是当前的时间
(2) 这种写法，值就是表头信息
(3) 可以通过as重新命名表头信息

select中可以进行计算

例：

select 1+2;
select 3+4*5,6+round(6.7);

使用distinct消除查询过结果重复行

重复的含义：两行（或两行以上）的数据完全一样

select distinct 字段1,字段2....from 表;

2.3 练习1

--建库create database school charset utf8;
--建学生表create table student (sno varchar(3) not null,sname varchar(4) not null,ssex varchar(2) not null,sbirthday datetime,class varchar(5));
--添加学生数据insert into student values('108','曾华','男','1977-9-1','95033');insert into student values('105','匡明','男','1975-10-2','95031'),('107','王丽','女','1976-1-23','95033'),('101','李军','男','1976-2-20','95033'),('109','王芳','女','1975-2-10','95031'),('103','陆君','男','1974-6-3','95031');
--创建教师表create table teacher (tno varchar(3) not null,tname varchar(4) not null,tsex varchar(2) not null,tbirthday datetime not null,prof varchar(6),depart varchar(10) not null);
--添加教师表数据insert into teacher values ('804','李诚','男','1958-12-2','副教授','计算机系'),('856','张旭','男','1969-3-12','讲师','电子工程系'),('825','王萍','女','1972-5-5','助教','计算机系'),('831','刘冰','女','1977-8-14','助教','电子工程系');
--创建课程表create table course(cno varchar(5) not null,cname varchar(10) not null,tno varchar(10) not null);
--添加课程表数据insert into course values ('3-105','计算机导论','825'),('3-245','操作系统','804'),('6-166','数据电路','856'),('9-888','高等数学',100);
--创建成绩表create table score (sno varchar(3) not null,cno varchar(5) not null,degree decimal(10,1) not null);
--添加成绩表数据insert into score values (103,'3-245',86),(105,'3-245',75),(109,'3-245',68),(103,'3-105',92),(105,'3-105',88),(109,'3-105',76),(101,'3-105',64),(107,'3-105',91),(108,'3-105',78),(101,'6-166',85),(107,'6-106',79),(108,'6-166',81);

语句：

1、 查询Student表中的所有记录的Sname、Ssex和Class列。select sname,ssex,class from student;
2、 查询教师所有的单位即不重复的Depart列。select distinct depart from teacher;
3、 查询Student表的所有记录。select * from student;
4、 查询Score表中成绩在60到80之间的所有记录。select * from score where degree>=60 and degree<=80;select * from score where degree between 60 and 80;
5、 查询Score表中成绩为85，86或88的记录。select * from score where degree in (85,86,88);
6、 查询Student表中“95031”班或性别为“女”的同学记录。select * from student where class='95031' or ssex='女';
7、 以Class降序查询Student表的所有记录。select * from student order by class desc;
8、 以Cno升序、Degree降序查询Score表的所有记录。select * from score order by cno asc,degree desc;
9、 查询“95031”班的学生人数。select count(*) from student where class='95031';
10、查询Score表中的最高分的学生学号和课程号。select * from score order by degree desc limit 0,1;--排序之后，读取数据是从索引0开始，截取第0-1个，但是不包含1
11、查询‘3-105’号课程的平均分。select avg(degree) from score where cno='3-105';

函数

.1 知识点：聚合函数

--1.求数量count(参数)：一般参数我都会*，你可以写成具体的字段
--查询一共有多少个学生select count(*) from student;
--查询有多少个女学生select count(*) from student where ssex='女';
--2.avg(参数)求平均值，参数为你要求平均值的字段
--查询所有成绩的平均值select avg(degree) from score;
--查询课程编号为3-105的平均值select avg(degree) from score where cno='3-105';
--sum(参数)求某个字段的和
--查询所有成绩的和select sum(degree) from score;
--查询课程编号为3-105的和select sum(degree) from score where cno='3-105';
--max(参数)/min(参数)：求某个字段的最大值和最小值
--求3-105的最大值和最小值select max(degree),min(degree) from score where cno='3-105';

.2 数据处理函数/单行处理函数

函数	作用
Lower	转换小写
upper	转换大写
substr	取子串（substr(被截取的字符串,起始下标,
截取的长度)）
length	取长度
trim	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
Ifnull	可以将 null 转换成一个具体值

.2.1、lower

查询员工，将员工姓名全部转换成小写

select lower(ename) from emp;

.2.2、upper

查询 job 为 manager 的员工

select * from emp where job=upper('manager');

.2.3、substr

查询姓名以 M 开头所有的员工

select * from emp where substr(ename, 1, 1)=upper('m');

.2.4、length

取得员工姓名长度为 5 的

select length(ename), ename from emp where length(ename)=5;

.2.5、trim

trim 会去首尾空格，不会去除中间的空格

取得工作岗位为 manager 的所有员工

select * from emp where job=trim(upper('manager '));

.2.6、str_to_date (必须严格按照标准输出)

查询 1981-02-20 入职的员工（第一种方法，与数据库的格式匹配上）

select * from emp where HIREDATE='1981-02-20';

查询 1981-02-20 入职的员工（第二种方法，将字符串转换成 date 类型）

select * from emp where HIREDATE=str_to_date('1981-02-20','%Y-%m-%d');
select * from emp where HIREDATE=str_to_date('02-20-1981','%m-%d-%Y');

str_to_date 可以将字符串转换成日期，具体格式 str_to_date (字符串，匹配格式)

.2.7、date_format

查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss

select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp;select date_format(now(),'%Y-%m-%d %H %i %s');
now() 获得当前时间

日期格式的说明

%Y：代表 4 位的年份
%y：代表 2 位的年份
%m：代表月, 格式为(01……12) 
%c：代表月, 格式为(1……12)
%H：代表小时,格式为(00……23) 
%h： 代表小时,格式为(01……12) 
%i： 代表分钟, 格式为(00……59)
%r：代表 时间,格式为 12 小时(hh:mm:ss [AP]M) 
%T：代表 时间,格式为 24 小时(hh:mm:ss)
%S：代表 秒,格式为(00……59) 
%s：代表 秒,格式为(00……59)

.2.8、format

查询员工薪水加入千分位

select empno, ename, Format(sal, 0) from emp;

查询员工薪水加入千分位和保留两位小数

select empno, ename, Format(sal, 2) from emp;

.2.9、round

四舍五入

select round(123.56);

.2.10、rand()

生成随机数

select rand();

随机抽取记录数
select * from emp order by rand() limit 2;
order by 必须写上。

.2.11、case … when … then ……else …end

如果 job 为 MANAGERG 薪水上涨 10%,如果 job 为 SALESMAN 工资上涨 50%

select empno, ename, job, sal, case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then
sal*1.5 end as newsal from emp;

其他的工资不动，需要添加 else

select e.*,sal ,case job when 'salesman' then sal*1.1 when 'clerk' then sal*1.2 else sal end as 
new_sal from emp e;
e.*:取 emp 表所有的字段 , emp as e 是表的别名可以省略 as emp e

.2.12、ifnull

select ifnull(comm,0) from emp;
如果 comm 为 null 就替换为 0
在 SQL 语句当中若有 NULL 值参与数学运算，计算结果一定是 NULL
为了防止计算结果出现 NULL，建议先使用 ifnull 空值处理函数预先处理。
以下 SQL 是计算年薪的：
select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;

数据处理函数又被称为单行处理函数，特点：输入一行输出一行

3. mysql运算符

3.1 算术运算符

+ - * / %

3.2 比较运算符

相等：=

不等于：<> 或 !=

大于：>

大于等于：>=

小于：<

小于等于：<=

3.3 逻辑运算符

逻辑与：&& 或and

逻辑或：|| 或or

逻辑非：！或 not

3.4 其他特殊运算符

like模糊查找运算符：

用于判断某个字符型字段的值是否包含给定的字符。

语法：

xxx字段 like ‘%关键字%’

其中：%表示”任意个数的任意字符”。

还可以使用”_”（下划线）,表示”任意一个字符”.

where name like ‘罗%’ //找出name的第一个字为”罗”的所有，

//但找不出’c罗纳尔多’这个

where name like ‘罗_’ //可以找出：”罗成”,”罗兰”，

//但找不出”c罗”，”罗永浩”

如果不使用”%”或”_”，则like相当于等于(=)。比如：

xxx字段 like ‘关键字’

相当于：

xxx字段=’关键字’

between范围限定运算符：

用于判断某个字段的值是否在给定的两个数据范围之间。

语法：

xxx字段 between 值1 and 值2;

其含义相当于：xxx字段>=值1 and xxx字段<=值2;

in运算符：

用于判断某个字段的值是否在给出的若干个”可选值”范围。

语法：

xxx字段 in (值1，值2…)

其含义是：该字段的值等于所列出的任意一个值，就算满足条件，比如：

籍贯 in (‘北京’,’山东’,’河北’,’江西’);//则某人籍贯为上述4个之一就ok

is运算符：

用于判断一个字段中的是”是否存在”（即有没有）,只有两个写法，如下所示：

where content is null; //不能写成：content=null;

Where content is not null; //不能写成：content !=null;

4. 分组

语法：

…group by 字段1,字段2…

含义：

表示对所取得的数据，以所给定的字段来进行分配。最后的结果就是将数据分成了若干组，每组作为一个”整体”称为一行数据。

特别注意：

分组查询的结果，要理解为，将”若干行原始数据”，分成了若干组，结果是每组为一行数据。

即：一行数据就代表”一组”这个集合概念，而不再是单个概念。

因此：一行中出现的信息，应该是”组的信息”，而不是”个体信息”。

于是，对于分组查询（group by），select中出现的信息，通常就只有两种情况的信息了：

分组本身的字段信息
一组的综合同级信息，主要包括：

A. 计数值：count（字段），表示求出一组中原始数据的行数

B. 最大值：max(字段)，表示求出一组中该字段的最大值

C. 最小值：min(字段)，表示求出一组中该字段的最小值

D. 平均值：avg(字段)，表示求出一组中该字段的平均值

E. 总和值：sum(字段)，表示求出一组中该字段的累加和

案例
--统计每个班有多少个人
select class,count(*) from student group by class;--根据什么字段分组，就只能查询什么字段的数据，以及他的汇总信息
--统计每个班的男女人数
select class,ssex,count(*) from student group by class,ssex;
--统计每个科目有多少个人参与考试，最高分是多少
select cno ,count(*),max(degree) from score group by cno;
--统计科目3开头的，每个科目的总成绩，按总成绩从高到低排序
select cno,sum(degree) from score where cno like '3%' group by cno order by sum(degree) desc;

5. having子句

语法：

having 筛选条件

含义：

having的含义跟where的含义一样，但having是只用于对group by 分组的结果进行的条件筛选。即：having其实是相当于分组之后”有若干行数据”，然后对这些行再筛选

--查询每门科目三人以上参与考试的成绩信息
select cno,count(*) from score group by cno having count(*)>=3;

6. order by 子句

语法：

order by 字段1[asc 或 desc]，字段2[asc或desc],…

含义：

对前面所取得的数据按给定的字段进行排序。

排序方式有：正序asc,倒序desc,如果省略不写，就是asc

7. limit 子句（重要）

作用：将查询结果集的一部分取出来。通常使用在分页查询当中。
百度默认：一页显示10条记录。
分页的作用是为了提高用户的体验，因为一次全部都查出来，用户体验差。可以一页一页翻页看。

完整语法：
limit 起始行号，行数；（起始行号从0开始）

缺省语法：
limit 行数；

说明：

A. limit表示对前面所取得的数据再进行数量上的筛选：取得从某行开始的多少行

B. 行号就是前面所取得数据的”自然顺序号”,从0开始算起——注意不是id,或任何其他实际数据

C. 起始行号可以省略，此时limit后只用一个数字，表示从第0行开始取出多少行

D. limit子句通常用在”翻页”功能上，用于找出”第n页”的数据，其公式为：

limit (n - 1) * pageSize, pageSize：其中pageSize表示每页显示的条数

public static void main(String[] args){// 用户提交过来一个页码，以及每页显示的记录条数int pageNo = 5;// 第5页int pageSize = 10; // 每页显示10条int startIndex = (pageNo - 1) * pageSize;String sql = "select ... limit " + startIndex + ", " + pageSize;
}

8. 联合（union）查询

8.1 联合查询概念

含义：联合查询是指将2个或2个以上的字段数量相同的查询结果,”纵向堆叠”后合并为一个结果

--查询所有学生和教师的姓名、性别
select sname,ssex from student  union select tname,tsex from teacher;--表头信息就是第一张表的字段名

8.2 联合查询语法

语法：

select 查询1

union [all或distinct]

select 查询2

union [all或distinct]

select 查询3

…

[order by 字段 [asc或desc]]

[limit 起始行数，数量];
在这里插入图片描述

说明：

A. 所有单个查询结果应该具有相等的列数

B. 所有单个查询的列类型应该具有一致性（即每个查询的第n列的数据类型一致）

C. 单个查询的列名可以不同，但最终的列名是第一个查询的列名（可以使用别名）

D. union可以带all或distinct参数，如果省略就是distinct,即默认已经消除重复行了

E. 最后的order by或limit是对整个联合之后的结果数据进行排序或数量限定

F. order by子句中的排序字段应该使用第一个查询中的字段名，如果有别名就必须使用别名

G. 可见，假设：

查询1有n1行，m列；

查询2有n2行，m列；

则两个表”联合”之后的结果，有最多n1+n2行，m列
H. union的效率要高一些。对于表连接来说，每连接一次新表，
则匹配的次数满足笛卡尔积，成倍的翻。。。
但是union可以减少匹配的次数。在减少匹配次数的情况下，
还可以完成两个结果集的拼接。

a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是：1000a 连接 b一个结果：10 * 10 --> 100次
a 连接 c一个结果：10 * 10 --> 100次
使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）

I. union在使用的时候有注意事项吗？

//错误的：union在进行结果集合并的时候，要求两个结果集的列数相同。
select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job = 'SALESMAN';// MYSQL可以，oracle语法严格 ，不可以，报错。要求：结果集合并时列和列的数据类型也要一致。
select ename,job from emp where job = 'MANAGER'
union
select ename,sal from emp where job = 'SALESMAN';
+--------+---------+
| ename  | job     |
+--------+---------+
| JONES  | MANAGER |
| BLAKE  | MANAGER |
| CLARK  | MANAGER |
| ALLEN  | 1600    |
| WARD   | 1250    |
| MARTIN | 1250    |
| TURNER | 1500    |
+--------+---------+

9. 连接（join）查询

连接（join）查询是将两个查询的结果以”横向对接”的方式合并起来的结果。

对比：联合查询是将两个查询的结果以“纵向堆叠”的方式合并起来的结果

9.1 连接查询概述

连接查询，是将两个查询（或表）的每一行，以”两两横向对接”的方式，所得到的所有行的结果。即一个表中的某行，跟某一个表中的某行，进行”横向对接”，而得到一个新行。

如下图所示：
在这里插入图片描述
则他们对接（连接）之后的结果类似这样：

可见，假设：

表1有n1行，m1列；

表2有n2行，m2列；

则表1和表2”连接”之后，就会有：

n1*n2行；

m1+m2列；

连接查询基本形式如下：

select …from 表1 [连接方式] join 表2 [on 连接条件] where…;

可见，连接查询只是作为from子句的“数据源”.

或者说，连接查询是扩大了数据源，从原来的一个表作为数据源，扩大为多个表作为数据源。

连接查询包括以下这些不同形式：

交叉连接，内连接，外连接（分：左外连接，右外连接）。

9.2 交叉连接

语法：

from 表1 [cross] join 表2

说明：

A. 交叉连接其实可以认为是连接查询的”安全版本”,即所有行都无条件地都连接起来了

B. 关键字”cross”可以省略

C. 交叉连接又称为”笛卡尔积”，通常应用价值不大

D. 交叉连接还有一种写法(（多表查询）from 表1, 表2；
在这里插入图片描述

--查询老师姓名、课程、课程编号
select teacher.tname,course.cno,course.cname from teacher join course;--如果，多表查询的时候，字段只出现一次，我们可以不用去写表名,但是出现多次就必须明确是哪个表的。
select tname,cno,cname from teacher join course;
select tno,tname,cno,cname from teacher join course;--这样书写就不能明确是哪个表的tno了，就必须明确是哪个表的字段
select teacher.tno,cno,cname from teacher join course;
select a.tno,tname,cno,cname from teacher a join course;--注意，在取别名的时候，as是可以不写的
select tname,cno,cname from teacher,course;--同时查询这两张表

9.3 内连接（inner join）-取交集

A和B连接，AB两张表没有主次关系，是平等的。

等值连接（连接条件是等量关系）
非等值连接（连接条件是非等量关系）
自连接（一张表看成两张表）

9.3.1 等值连接

语法：

from 表1 [inner] join 表2 on 连接条件——SQL99语法（表连接的条件是独立的，如果还需进一步筛选，再往后继续添加where）
from 表1, 表2 where 连接条件 and 后面加条件 ——SQL92语法（表连接条件和后期要加的筛选条件糅杂在一起，结构不清晰）

说明：

A. 内连接起始是交叉连接的基础上，再通过on条件而筛选出来的部分数据

B. 关键字”inner”可以省略，但建议写上

C. 内连接是应用最广泛的一种连接查询，其本质是根据条件筛选出”有意义的数据”

--查询每门课，由哪个老师来上
select c.cname,t.tname from course c inner join teacher t on c.tno=t.tno;--on后面的条件必须是有关联的，说白了其实就是外键和主键的关联,其实是一种交集
select c.cname,t.tname from course c,teacher t where c.tno=t.tno;--如果直接采用查询多表的情况，这里的条件尽量使用where。where后面的条件必须也是关联性的条件

在这里插入图片描述

9.3.2 非等值连接

案例：找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

mysql> select * from emp;

±------±-------±----------±-----±-----------±--------±--------±-------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
±------±-------±----------±-----±-----------±--------±--------±-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
…

mysql> select * from salgrade;

±------±------±------+
| GRADE | LOSAL | HISAL |
±------±------±------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
±------±------±------+

select e.ename, e.sal, s.grade
fromemp e
joinsalgrade s
one.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。select e.ename, e.sal, s.grade
fromemp e
inner joinsalgrade s
one.sal between s.losal and s.hisal;

±-------±--------±------+
| ename | sal | grade |
±-------±--------±------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
±-------±--------±------+

9.3.3 自连接-可以查询一个表中不同字段数据相比较

自连接不是一种新的连接形式，而只是一个表”自己跟自己连接”，这怎么做到呢？

语法：

from 表1 as a [连接形式] join 表1 as b on a.xx字段1=b.xx字段名

说明：

A. 自连接起始还是两个表连接，只是将一个表用不同的别名，当做两个表。

B. 自连接适用于一个表中的某个字段的值”来源于”当前表的另一个字段的情况。

--查询3-105比3-245成绩好的同学学号
select * from score a join score b on a.sno=b.sno where a.cno='3-105' and b.cno='3-245' and a.degree>b.degree;--假设第一个score就是3-105的，第二个score就是3-245的

9.4 外连接

左外连接
右外连接

将 join 关键字左边或者右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联另一边的表。两张表有主次关系。

9.4.1 左外连接（left join）-以左表基准

语法：

from 表1 left [outer] join 表2 on 连接条件;

说明：

A. 左外连接起始是保证左边表的数据都能够去除的一种连接

B. 左外连接其实是在内连接的基础上，再加上左边表中所有不能满足条件的数据

C. 关键字”outer”可以省略

--查询每门课，由哪个老师来上
select a.cname,b.tname from course a left join teacher b on a.tno=b.tno;--左外连接，如果左表有数据，右表没有数据，这个时候，查询出来的结果，以左表为基准，没有的数据用Null进行填充

取交叉

9.4.2 右外连接（right join）-以右表基准

语法：

from 表1 right [outer] join 表2 on 连接条件；

说明：

A. 右外连接起始是保证右边表的数据能够取出的一种连接

B. 右外连接起始是在内连接的基础上，再加上右边表中所有不饿能满足条件的数据

C. 关键字”outer”可以省略

select a.cname,b.tname from course a right join teacher b on a.tno=b.tno;--以右边的表为基准,如下图所示，因为存在该老师但是她么有要上的课

在这里插入图片描述

10.5 多表连接（3张表、4张表）

语法：

select...fromajoinbona和b的连接条件joincona和c的连接条件joindona和d的连接条件

一条SQL中内连接和外连接可以混合。都可以出现！

案例1：找出每个员工的部门名称以及工资等级，
要求显示员工名、部门名、薪资、薪资等级？

select e.ename,e.sal,d.dname,s.grade
fromemp e
joindept d
on e.deptno = d.deptno
joinsalgrade s
one.sal between s.losal and s.hisal;+--------+---------+------------+-------+
| ename  | sal     | dname      | grade |
+--------+---------+------------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 |
| ALLEN  | 1600.00 | SALES      |     3 |
| WARD   | 1250.00 | SALES      |     2 |
| JONES  | 2975.00 | RESEARCH   |     4 |
| MARTIN | 1250.00 | SALES      |     2 |
| BLAKE  | 2850.00 | SALES      |     4 |
| CLARK  | 2450.00 | ACCOUNTING |     4 |
| SCOTT  | 3000.00 | RESEARCH   |     4 |
| KING   | 5000.00 | ACCOUNTING |     5 |
| TURNER | 1500.00 | SALES      |     3 |
| ADAMS  | 1100.00 | RESEARCH   |     1 |
| JAMES  |  950.00 | SALES      |     1 |
| FORD   | 3000.00 | RESEARCH   |     4 |
| MILLER | 1300.00 | ACCOUNTING |     2 |
+--------+---------+------------+-------+

案例2：找出每个员工的部门名称以及工资等级，还有上级领导，
要求显示员工名、领导名、部门名、薪资、薪资等级？

select e.ename,e.sal,d.dname,s.grade,l.ename
fromemp e
joindept d
on e.deptno = d.deptno
joinsalgrade s
one.sal between s.losal and s.hisal
left joinemp l
one.mgr = l.empno;+--------+---------+------------+-------+-------+
| ename  | sal     | dname      | grade | ename |
+--------+---------+------------+-------+-------+
| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
| WARD   | 1250.00 | SALES      |     2 | BLAKE |
| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
| BLAKE  | 2850.00 | SALES      |     4 | KING  |
| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |
| TURNER | 1500.00 | SALES      |     3 | BLAKE |
| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
| JAMES  |  950.00 | SALES      |     1 | BLAKE |
| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
+--------+---------+------------+-------+-------+

10. 子查询

子查询就是指一个”正常查询语句”中的某个部分（比如select部分，from部分，where部分）又出现了查询的一种查询形式，比如：

select * from xx表名 where price>=(一个子查询语句);

此时，子查询所在上”上层查询”,就被称为主查询。

也可以这么说：子查询是为主查询的某一个部分提供某种数据的查询。

--查询学习李诚老师的课的学生信息
select a.* from student a,score b ,course c,teacher d where a.sno=b.sno and b.cno=c.cno and c.tno=d.tno and d.tname='李诚';
--2.子查询
select * from student where sno in(select sno from score where cno  in (select cno from course where tno =(select tno from teacher where tname='李诚')));

注意：

1.作为子查询结果，必须用小括号包起来

2.如果查询出来的结果是一个值，可以用等号，可以使用比较运算符；如果查询出来的一列只能使用in；如果查询出来的结果是几列几行，就要把它作为一张表来处理了。

10.1、什么是子查询？

select语句中嵌套select语句，被嵌套的select语句称为子查询。

10.2、子查询都可以出现在哪里呢？

select..(select).
from..(select).
where..(select).

10.3、where子句中的子查询

案例：找出比最低工资高的员工姓名和工资？select ename,salfromemp wheresal > min(sal);ERROR 1111 (HY000): Invalid use of group functionwhere子句中不能直接使用分组函数。实现思路：第一步：查询最低工资是多少select min(sal) from emp;+----------+| min(sal) |+----------+|   800.00 |+----------+第二步：找出>800的select ename,sal from emp where sal > 800;第三步：合并select ename,sal from emp where sal > (select min(sal) from emp);+--------+---------+| ename  | sal     |+--------+---------+| ALLEN  | 1600.00 || WARD   | 1250.00 || JONES  | 2975.00 || MARTIN | 1250.00 || BLAKE  | 2850.00 || CLARK  | 2450.00 || SCOTT  | 3000.00 || KING   | 5000.00 || TURNER | 1500.00 || ADAMS  | 1100.00 || JAMES  |  950.00 || FORD   | 3000.00 || MILLER | 1300.00 |+--------+---------+

10.4、from子句中的子查询

注意：from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）案例：找出每个岗位的平均工资的薪资等级。第一步：找出每个岗位的平均工资（按照岗位分组求平均值）select job,avg(sal) from emp group by job;+-----------+-------------+| job       | avgsal      |+-----------+-------------+| ANALYST   | 3000.000000 || CLERK     | 1037.500000 || MANAGER   | 2758.333333 || PRESIDENT | 5000.000000 || SALESMAN  | 1400.000000 |+-----------+-------------+t表第二步：克服心理障碍，把以上的查询结果就当做一张真实存在的表t。mysql> select * from salgrade; s表+-------+-------+-------+| GRADE | LOSAL | HISAL |+-------+-------+-------+|     1 |   700 |  1200 ||     2 |  1201 |  1400 ||     3 |  1401 |  2000 ||     4 |  2001 |  3000 ||     5 |  3001 |  9999 |+-------+-------+-------+
t表和s表进行表连接，条件：t表avg(sal) between s.losal and s.hisal;select t.*, s.gradefrom(select job,avg(sal) as avgsal from emp group by job) tjoinsalgrade sont.avgsal between s.losal and s.hisal;+-----------+-------------+-------+| job       | avgsal      | grade |+-----------+-------------+-------+| CLERK     | 1037.500000 |     1 || SALESMAN  | 1400.000000 |     2 || ANALYST   | 3000.000000 |     4 || MANAGER   | 2758.333333 |     4 || PRESIDENT | 5000.000000 |     5 |+-----------+-------------+-------+

10.5、select后面出现的子查询（这个内容不需要掌握，了解即可！！！）

案例：找出每个员工的部门名称，要求显示员工名，部门名？

select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from emp e;+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+// 错误：ERROR 1242 (21000): Subquery returns more than 1 row
select e.ename,e.deptno,(select dname from dept) as dname
fromemp e;注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果，
多于1条，就报错了。！

11. 练习2

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。select cno,count(*) '人数',avg(degree) '平均分' from score where cno like '3%' group by cno having count(*)>=5;13、查询最低分大于70，最高分小于90的Sno列。select sno from score group by sno having min(degree)>=70 and max(degree)<=90;14、查询所有学生的Sname、Cno和Degree列。select sname,cno,degree from student,score where student.sno=score.sno;15、查询所有学生的Sno、Cname和Degree列。select sno,cname ,degree from score a join course b on a.cno=b.cno;16、查询所有学生的Sname、Cname和Degree列。select sname,cname,degree from student a,course b,score c where a.sno=c.sno and b.cno=c.cno; 17、查询“95033”班所选课程的平均分。第一种：select avg(b.degree) from student a,score b where a.sno=b.sno and a.class='95033';第二种：select avg(degree) from score where sno in (select sno from student where class='95033');19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。select * from student where sno in(select sno from score where cno='3-105' and degree>(select max(degree) from score where sno=109))20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。select * from score a join (select sno from score group by sno order by sum(degree) desc limit 0,1) b on a.sno<>b.sno;21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。select * from score where degree >(select degree from score where sno=109 and cno='3-105');22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。select sno,sname,sbirthday from student where year(sbirthday)=(select year(sbirthday) from student where sno=108);23、查询“张旭“教师任课的学生成绩。select * from score where cno in (select cno from course where  tno in(select tno from teacher where tname='张旭'));24、查询选修某课程的同学人数多于5人的教师姓名。select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>=5));25、查询95033班和95031班全体学生的记录。select * from student where class in('95033','95031');26、查询存在有85分以上成绩的课程Cno.select distinct cno from score where degree>=85;select cno from score where degree>=85 group by cno;27、查询出“计算机系“教师所教课程的成绩表。select * from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'));28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。select tname,prof from teacher where depart in('计算机系','电子工程系');29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。XXXX30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.select * from score a,score b where a.sno=b.sno and a.cno='3-105' and b.cno='3-245' and a.degree>b.degree;31、查询所有教师和同学的name、sex和birthday.select sname,ssex,sbirthday from student union select tname,tsex,tbirthday from teacher;32、查询所有“女”教师和“女”同学的name、sex和birthday.select * from (select sname,ssex,sbirthday from student union select tname,tsex,tbirthday from teacher) a where a.ssex='女';33、查询成绩比该课程平均成绩低的同学的成绩表。方法一：select * from score a where degree<(select avg(degree) from score b where a.cno=b.cno);方法二：select a.* from score a join (select cno,avg(degree) as dd from score group by cno) as b on a.cno=b.cnowhere a.degree<b.dd;34、查询所有任课教师的Tname和Depart.select tname,depart from teacher where tno in(select tno from course where cno in(select cno from score));35  查询所有未讲课的教师的Tname和Depart.select tname,depart from teacher where tno not in(select tno from course where cno in(select cno from score));36、查询至少有2名男生的班号。select class from student where ssex='男' group by class having count(*)>=2;37、查询Student表中不姓“王”的同学记录。select * from student where sname not like '王%';38、查询Student表中每个学生的姓名和年龄。select sname,year(now())-year(sbirthday) as '年龄' from student ;39、查询Student表中最大和最小的Sbirthday日期值。select max(age),min(age) from (select sname,year(now())-year(sbirthday) as age from student) as aa;日期值：select max(Sbirthday) as min_sbirthday,min(Sbirthday) as max_sbirthday from student;40、以班号和年龄从大到小的顺序查询Student表中的全部记录。select * from (select *,year(now())-year(sbirthday) as age from student) as aa order by class desc,age desc;41、查询“男”教师及其所上的课程。select * from course where tno in(select tno from teacher where tsex='男');42、查询最高分同学的Sno、Cno和Degree列。select * from score order by degree desc limit 0,1;43、查询和“李军”同性别的所有同学的Sname.select sname from student where ssex=(select ssex from student where sname='李军');44、查询和“李军”同性别并同班的同学Sname.select sname from student a join (select ssex,class from student where sname='李军') b on a.ssex=b.ssex and a.class=b.class;45、查询所有选修“计算机导论”课程的“男”同学的成绩表select * from score where sno in(select sno from student where ssex='男')and cno in(select cno from course where cname='计算机导论');方法二：select a.* from score a,student b,course c where a.sno=b.sno and a.cno=c.cno and b.ssex='男' and c.cname='计算机导论';

12. 练习3

-- 1、查询“001”课程比“002”课程成绩高的所有学生的学号；
##方法1
select a.sno from (select * from sc where Cno='001') a
join (select * from sc where Cno='002') b on a.sno=b.sno
where a.score>b.score;
##方法2.自连接
select a.sno from sc a,sc b where a.sno=b.sno and a.cno='001' 
and b.cno='002' and a.score>b.score;-- 2、查询平均成绩大于60分的同学的学号和平均成绩；
select sno,avg(score) '平均成绩' from sc group by sno having avg(score)>=60;-- 3、查询所有同学的学号、姓名、选课数、总成绩；
select a.sno,a.sname,b.c1 '选课数',b.s1 '总成绩' from student as a 
join (select sno,count(*) c1,sum(score) s1 from sc group by sno ) as b
on a.sno=b.sno;-- 4、查询姓“李”的老师的个数；
select count(*) from teacher where teachername like '李%';-- 5、查询没学过“叶平”老师课的同学的学号、姓名；
select sno,sname from student where sno not in(
select sno from sc where cno in (
select cno from course where tno =(
select tno from teacher where teacherName='叶平')));-- 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select sno,sname from student where sno in(
select sno from sc where cno in ('001','002') group by sno having count(*)=2);-- 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select sno,sname from student where sno in(
select sno from sc where cno in (
select cno from course where tno =(
select tno from teacher where teacherName='叶平')));-- 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select sno,sname from student where sno in(
select a.sno from sc a,sc b where a.sno=b.sno and a.cno='001' 
and b.cno='002' and a.score>b.score);-- 9、查询所有课程成绩小于60分的同学的学号、姓名；
select sno,sname from student where sno in(
select sno from sc group by sno having max(score)<60);-- 10、查询没有学全所有课的同学的学号、姓名；
## 课要是course里面的课
## 数量要是course里面全部课程的数量
## 逆向思维，先选出选全课的学生学号，再取反
select sno,sname from student where sno in (
select sno from sc where cno in (select cno from course) group by sno 
having count(*)=(select count(*) from course));-- 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select sno,sname from student where sno in(
select sno from sc where cno in (
select cno from sc where sno='1001'));-- 12、查询学过学号为“1001”同学所有课的其他同学学号和姓名；
select sno,sname from student where sno in(
select sno from sc where cno in (select cno from sc where sno='1001')
group by sno having count(*)=(select count(cno) from sc where sno='1001'));-- 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
select *  from sc a 
join (select cno,avg(score) s1 from sc where cno in(
select cno from course where tno =(
select tno from teacher where teachername='叶平')) group by cno ) b 
on a.cno=b.cno;update sc a inner join (select cno,avg(score) s1 from sc where cno in(
select cno from course where tno =(
select tno from teacher where teachername='叶平')) group by cno ) b on a.cno=b.cno
set a.score=b.s1;-- 14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
## 筛选课程之前的数量要对的上
## 筛选后的课程要一样，且数量一样
select sno,sname from student where sno in(
select sno from sc where sno in(
select sno from sc group by sno having count(*)=(select count(*) from sc where sno='1002'))
and cno in (select cno from sc where sno='1002') group by sno having count(*)=(select count(*) from sc where sno='1002'));-- 15、删除学习“叶平”老师课的SC表记录；
-- ????????????????????????删除先不做-- 16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、
-- 2、003号课的平均成绩；
insert into sc 
select sno,'003',(select avg(score) from sc where cno='003') from student where sno not in(
select sno from sc where cno='003');-- 17、按平均成绩从高到低显示所有学生的、“企业管理”、“马克思”、“UML”三门的课程成绩，
-- 按如下形式显示： 学生ID,企业管理,马克思,UML,有效课程数,有效平均分
select bb.sno '学号',bb.s1 '企业管理',bb.s2 '马克思',bb.s3 'UML',bb.yx '有效课程数',
case when yx=3 then (bb.s1+bb.s2+bb.s3)/3 
when yx=1 then bb.s1+bb.s2+bb.s3
when yx=2 then (bb.s1+bb.s2+bb.s3)/2
end as '有效平均分'
from (select *,
case when aa.s1<>0 and aa.s2<>0 and aa.s3<>0 then 3 
when aa.s1=0 and aa.s2=0 and aa.s3=0 then 0 
when aa.s1<>0 and aa.s2=0 and aa.s3=0 then 1
when aa.s1=0 and aa.s2<>0 and aa.s3=0 then 1
when aa.s1=0 and aa.s2=0 and aa.s3<>0 then 1
else 2
end as yx
from (
select a.sno,
case when b.score is null then 0 else b.score end s1,
case when c.score is null then 0 else c.score end s2,
case when d.score is null then 0 else d.score end s3 from student a 
left join (select sno,score from sc where cno =(select cno from course where cname='企业管理')) b
on a.sno=b.sno
left join (select sno,score from sc where cno =(select cno from course where cname='马克思')) c
on a.sno=c.sno
left join (select sno,score from sc where cno =(select cno from course where cname='UML')) d
on a.sno=d.sno) aa)bb-- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cno,max(score) '最高分',min(score) '最低分' from sc group by cno;-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
select aa.cno '学号',aa.a1 '平均分',bb.bfs '及格率%' from (select cno,avg(score) a1 from sc group by cno) aa join (select a.cno,(a.jg/b.zs)*100 as bfs from (select cno,count(*) jg from sc where score>=60 group by cno) a join (select cno,count(*) zs from sc group by cno) b on a.cno=b.cno) bb on aa.cno=bb.cno order by aa.a1,bb.bfs desc;-- 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）
select cno,avg(score) avgs,count(*) from score where cno in('001','002','003','004') group by cno # 各科平均成绩和总人数
select cno,count(*) pc from score where score>=60 group by cno # 各科及格人数
select cno,count(*) tc from score group by cno # 各科总人数
select a.cno,pc/tc*100 pr from (select cno,count(*) pc from score where score>=60 group by cno) a join (select cno,count(*) tc from score group by cno) b on a.cno=b.cno # 及格率
# 耗时很长，大概要一分钟。。。
select * from (
(select aa.cno,avgs,pr from (
(select cno,avg(score) avgs from score group by cno having cno in('001','002','003','004')) aa 
join (select a.cno,pc/tc*100 pr from (select cno,count(*) pc from score where score>=60 group by cno) a join (select cno,count(*) tc from score group by cno) b on a.cno=b.cno) bb on aa.cno=bb.cno)) x1
join
(select aa.cno,avgs,pr from 
((select cno,avg(score) avgs from score group by cno having cno in('001','002','003','004') and cno='002') aa join (select a.cno,pc/tc*100 pr from (select cno,count(*) pc from score where score>=60 group by cno) a join (select cno,count(*) tc from score group by cno) b on a.cno=b.cno) bb on aa.cno=bb.cno)) x2
join
(select aa.cno,avgs,pr from 
((select cno,avg(score) avgs from score group by cno having cno in('001','002','003','004') and cno='003') aa join (select a.cno,pc/tc*100 pr from (select cno,count(*) pc from score where score>=60 group by cno) a join (select cno,count(*) tc from score group by cno) b on a.cno=b.cno) bb on aa.cno=bb.cno)) x3
join
(select aa.cno,avgs,pr from 
((select cno,avg(score) avgs from score group by cno having cno in('001','002','003','004') and cno='004') aa join (select a.cno,pc/tc*100 pr from (select cno,count(*) pc from score where score>=60 group by cno) a join (select cno,count(*) tc from score group by cno) b on a.cno=b.cno) bb on aa.cno=bb.cno)) x4
)# 改进，算和及格率的时候不要先算好，放外层一起算效率高
select * from ( 
select aa.cno as '学号' ,aa.avgs as '平均分',bb.pc as '及格人数',bb.pc/aa.tc*100 as '及格率' from (
select cno,avg(score) as avgs,count(*) as tc from score where cno in('001','002','003','004') group by cno) as aa
join (select cno,count(*) as pc from score where cno in ('001','002','003','004') and score>=60 group by cno) as bb on aa.cno=bb.cno where aa.cno ='001') as x1
join
(
select aa.cno as '学号' ,aa.avgs as '平均分',bb.pc as '及格人数',bb.pc/aa.tc*100 as '及格率' from (
select cno,avg(score) as avgs,count(*) as tc from score where cno in('001','002','003','004') group by cno) as aa
join (select cno,count(*) as pc from score where cno in ('001','002','003','004') and score>=60 group by cno) as bb on aa.cno=bb.cno where aa.cno ='002') as x2
join
(
select aa.cno as '学号' ,aa.avgs as '平均分',bb.pc as '及格人数',bb.pc/aa.tc*100 as '及格率' from (
select cno,avg(score) as avgs,count(*) as tc from score where cno in('001','002','003','004') group by cno) as aa
join (select cno,count(*) as pc from score where cno in ('001','002','003','004') and score>=60 group by cno) as bb on aa.cno=bb.cno where aa.cno ='003') as x3
join
(
select aa.cno as '学号' ,aa.avgs as '平均分',bb.pc as '及格人数',bb.pc/aa.tc*100 as '及格率' from (
select cno,avg(score) as avgs,count(*) as tc from score where cno in('001','002','003','004') group by cno) as aa
join (select cno,count(*) as pc from score where cno in ('001','002','003','004') and score>=60 group by cno) as bb on aa.cno=bb.cno where aa.cno ='004') as x4-- 21、查询不同老师所教不同课程平均分从高到低显示（有歧义）
select cno,avg(score) from score group by cno having cno in (select cno from course) order by avg(score) desc;-- 22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
-- [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
select * from (select * from sc where cno='001' order by score desc limit 0,2) a
union select * from (select * from sc where cno='002' order by score desc limit 0,2) b
union select * from (select * from sc where cno='003' order by score desc limit 0,2) c
union select * from (select * from sc where cno='004' order by score desc limit 0,2) d;-- 23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
# 列印各科成绩
select a.sno,a.cno,b.cname,a.score from(
select sno,cno,score from score where cno in (select cno from course)) a join (select cno,cname from course) b on a.cno=b.cnoselect a.cno,a.cname,a1.c1 '[100-85]',a2.c2 '[85-70]',a3.c3 '[70-60]',a4.c4 '[60-0]' from course a
left join (select cno,count(*) c1 from score where score between 85 and 100 group by cno) a1 on a.cno=a1.cno
left join (select cno,count(*) c2 from score where score between 70 and 85 group by cno) a2 on a.cno=a2.cno
left join (select cno,count(*) c3 from score where score between 60 and 70 group by cno) a3 on a.cno=a3.cno
left join (select cno,count(*) c4 from score where score between 0 and 60 group by cno) a4 on a.cno=a4.cno
# 用case when去除所有null数据
select a.cno,a.cname,
case when a1.c1 is null then 0 else a1.c1 end '[100-85]',
case when a2.c2 is null then 0 else a2.c2 end '[85-70]',
case when a3.c3 is null then 0 else a3.c3 end '[70-60]',
case when a4.c4 is null then 0 else a4.c4 end '[60-0]'
from course a
left join (select cno,count(*) c1 from score where score between 85 and 100 group by cno) a1 on a.cno=a1.cno
left join (select cno,count(*) c2 from score where score between 70 and 85 group by cno) a2 on a.cno=a2.cno
left join (select cno,count(*) c3 from score where score between 60 and 70 group by cno) a3 on a.cno=a3.cno
left join (select cno,count(*) c4 from score where score between 0 and 60 group by cno) a4 on a.cno=a4.cno-- 24、查询学生平均成绩及其名次
select sno,avg(score) a1,sum(score) s1 from sc group by sno order by sum(score) desc;-- 25、查询各科成绩前三名的记录:(不考虑成绩并列情况)。
-- ***************??????????-- 26、查询每门课程被选修的学生数
select cno ,count(*) from sc group by cno;-- 27、查询出只选修了一门课程的全部学生的学号和姓名
select sno,sname from student where sno in(
select sno from score group by sno having count(*)=1);# 上面的写法可能有问题，因为当成绩表中的学生选了课程表中没有的课程，也会查出来
select a.sno,sname from student a
join (select sno,cno from score where cno in (select cno from course) group by sno having count(*)=1) b on a.sno=b.sno;-- 28、查询男生、女生人数
select ssex,count(*) from student group by ssex;-- 29、查询姓“张”的学生名单
select * from student where sname like '张%';-- 30、查询同名同性学生名单，并统计同名人数
# 自连接
select a.sname,count(*) '同名同性人数'
from student a join student b on a.sname=b.sname and a.ssex=b.ssex and a.sno<>b.sno;
# 分组查询
select sname,ssex,count(*) from student group by sname,ssex having count(*)>1;-- 31、2005年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * from student where year(now())-sage=2005;-- 32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select cno,avg(score) from score group by cno having cno in (select cno from course) order by score asc,cno desc;-- 33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.sno,a.sname,b.avgs from student a
join (select sno,avg(score) avgs from score group by sno having avg(score)>80) b on a.sno=b.sno-- 34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
# 方法一
select a.sname,b.score from student a
join score b on a.sno=b.sno 
join course c on b.cno=c.cno
where c.cname='数据库' and b.score<60;
# 方法二
select a.sname,b.score from student a join (select sno,score from score where cno in (select cno from course where cname='数据库') and score<60) b on a.sno=b.sno-- 35、查询所有学生的选课情况；
select cno ,count(*) from score group by cno order by count(*) desc;-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select a.sname,c.cname,b.score from student a 
join score b on a.sno=b.sno
join course c on b.cno=c.cno
where a.sno in(select sno from score group by sno having min(score)>70);-- 37、查询不及格的课程，并按课程号从大到小排列
select cno,score from score where cno in (select cno from course) and score<60 order by cno desc;-- 39、求选了课程的学生人数
select count(*) from (select sno from score group by sno) a;-- 40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select a.sname,c.cno,b.score from student a 
join score b on a.sno=b.sno
join (select cno,max(score) m1 from score where cno in(
select cno from course where tno =(
select tno from teacher where tname='叶平')) group by cno) c on b.cno=c.cno and b.score=c.m1;-- 41、查询各个课程及相应的选修人数
select a.cno,b.cname,c1 from(
select cno,count(*) c1 from score  where cno in (select cno  from course) group by cno) a
join (select cno,cname from course) b on a.cno=b.cno;-- 42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select * from score where score in(
select score from score group by score having count(*)>1) order by sno;-- 43、查询每门功成绩最好的前两名
# 思路：先查询每门功课（course）
select co.cno,co.CName ,(select sno from score scwhere sc.cno=co.cno order by sc.score desc limit 0,1
) no1,(select sno from score scwhere sc.cno=co.cno order by sc.score desc limit 1,1
) no2 from course co;-- 44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
-- ？？？？？？？？？？？？-- 45、检索至少选修两门课程的学生学号
select sno,count(*) from score a join (select cno from course) b on a.cno=b.cno group by sno having count(*)>=2-- 46、查询全部学生都选修的课程的课程号和课程名
select cno,cname from course where cno in (
select cno from (select * from score where cno in(select cno from course) and sno in (select sno from student)) a group by cno having count(*)=(select count(*) from student));-- 47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select sname from student where sno not in(
select sno from sc where cno in(
select cno from course where tno =(select tno from teacher where teachername='叶平')));-- 48、查询两门以上不及格课程的同学的学号及其平均成
select sno,avg(score) from score where sno in(select sno from score where score<60 group by sno having count(*)>=2) group by sno;-- 49、检索“004”课程分数小于60，按分数降序排列的同学学号
select sno,score from score where cno='004' and score<60 order by score desc;-- 50、删除“1002”同学的“001”课程的成绩
delete from score where sno='1002' and cno='001';