mysql复习:查询语句详解

1.基本查询语句

select 属性列表 from 表名或视图名 [where 条件表达式] [group by 属性名[having 条件表达式]] order by 属性名 [ascidesc]

如果有groub by 字句，则将结果按照group by子句后的属性进行分组，该属性值相等的记录分为一组。如果在group by 后有having短句，则只有满足having后面的表达式的组才输出。如果有order by则会根据order by 自居后面的属性进行升序或者降序排序。
select 也可以完成负责的多表查询和嵌套查询。

先自己建立了三个表:
(1)student(sno,sname,ssex,sdept)
(2)course(cno,cname,cpno)
(3)sc(sno,cno,grade)

use aa;
create table student 
(
sno int(10) primary key auto_increment,
sname varchar(40),
ssex varchar(40),
sage int(10),
sdept varchar(40)

);
insert into student (sname,ssex,sage,sdept) values('刘敏','女',19,'计算机'),('周松','男',21,'计算机');
insert into student (sname,ssex,sage,sdept) values('张明','男',20,'经贸'),('孟欣','女',21,'信管');
select * from student;


create table course
(
cno int(10) primary key auto_increment,
cname varchar(40),
cpno int(10)
);
insert into course (cname,cpno) values('数据库',4),('操作系统',3),('信息系统',null),('数据结构',2);
select * from course;
create table sc
(
sno int(10),
cno int(10),
grade int(20),
constraint fk_st_sc foreign key (sno)references student(sno),
constraint fk_co_sc foreign key (cno)references course(cno)
);
insert into sc (sno,cno,grade) values(1,1,89);
insert into sc (sno,cno,grade) values(1,2,97);
insert into sc (sno,cno,grade) values(1,3,67);
insert into sc (sno,cno,grade) values(2,1,78),(2,2,90);
select * from sc;

2.查询表中部分字段

select cno,cname from course;

以逗号分隔

3.查询表中所有字段

select  * from student;

不再多说

4.查询经过计算的值

select后面可以是表达式

select sno,sname,year(now())-sage from student;

可以起别名来改变查询的列标题：待指定别名的字段、表达式后加空格。

select sno,sname,year(now())-sage sbir from student;

5.查询表中若干记录

1.清楚取值重复的行

select distinct sdept from student;

关键字 distinct 可以去重

2.查询表中满足条件的记录

（1）比较大小

• 查询全体女生的学号和姓名

select sno,sname from student where ssex='女';

• 查询分数大于80分的学生编号

select distinct sno from sc where grade > 80;

（2）确定范围

• 查询年龄在20-22岁之间的学生的学号、姓名、性别。
  

（3）带in关键字查询

• 查询年龄为18,20,23的学生信息
  

（4）带Like的字符匹配查询

1）% 匹配任何数目字符，甚至包括零字符
2）_ 只能匹配一个字符

• 查询姓张的学生编号和姓名
  
• 查询名字中第二个字符为‘敏’的学生信息
  

（5）查询空值

• 查询先行课为空的值
  

（6）多重条件查询

• 查询选修1号课程并且分数在80分以上的学生编号
  

6.对查询结果进行排序order by

order by 可以根据一列或者多列的结果进行升序（ASC）和降序（DESC）的排序，缺省默认升序

• 查询学生的学号、姓名、年龄，结果按年龄降序排列，若年龄相同，按照学号的降序进行排列
  

7.统计函数和分组记录结果

1.coung()统计行数

两种形式：
1）coung(*)统计符合条件的行数；
2）coung(列名)统计的是列中非空的个数
这两种关键字distinct可以消除重复
1.查询计算机系学生人数

2.查询选修了课程的学生人数

2.avg()统计某列平均值

1.查询计算机系的学生平均年龄

3.sum()统计某列值的和

1.查询不同年龄的总和

4.max()查询某列最大值

查询选修1号课程的最高分

5.min()查询某列最小值

查询1号课程的最低分

8.group by子句

使用group by将查询结果根据某一列或者多列的值进行分组，值相等的为一组。

1.单字段分组

1.统计不同系别的学生人数

按照sdept相同的为一组

2.多字段分组

查询不同性别不同年龄的学生人数。

3.group by与having子句一起使用

having用来对组进行筛选
1.查询平均成绩大于80分的学生编号和平均成绩

4.group by 与group_concat()函数一起使用

group_concat()函数返回一个字符串结果，该结果由分组中的值连接组合而成
1.查询每个学生的各科成绩，成绩显示在一行。

5.group by与rollup

查询每个学生的最高分最低分，并统计所有学生的最高分和最低分

9.limit限制查询结果的数量

limit接受一个或两个参数。第一个参数是偏移量，第二个参数是得到的行数。
1.查询表中前两行的学生信息。

2.查询sc表，返回第2行开始的3行数据

10.连接查询（重要！）

1.内连接查询

where 表名1.列名1 比较运算符 表名2.列名2

1.查询选修了课程的学生学号、姓名、课程号、成绩。

给student定义别名s，即可简化原来的from子句中参加连接的表用‘，’隔开，标准语法是在参加连接的表中加入inner join，连接条件使用on

2.查询每门课程的课程名称及其先行课的课程名称

2.外连接查询

不满足条件的记录也在结果中出现，并且显示null.
1）left join：join左表中所有记录和右表中满足连接条件的记录信息
2）right join:join右表中所有记录和左表中满足连接条件的记录信息

1.查询所有学生的编号、姓名及选课学生的课程号、成绩。
左连接：

右连接：

3.复合条件连接查询（重要！）

1.查询选修了数据库的学号、成绩

2.查询学生的学号、姓名、选修的课程名

student和course表没有联系，所以需要sc进行连接

11.子查询（重要！）

子查询是将一个查询语句嵌套在另一个查询语句中，例如select * from t1 where column1=(select column1 from t2),select column1 from t2为子查询，必须要在括号里，select * from t1为外部查询。子查询常用的操作符ANY(SOME)、ALL、IN、EXISTS

1.any,some,all

检查比较值是否与子查询所返回的全部或一部分进行匹配。
1.查询其他系比计算机系中某一位学生年龄小的学生信息

2.in和not in

带in查询时，内层仅返回一个数列集合。

1.查询选修了1号课程的学生学号和姓名

同理：

2.查询与刘敏同学在同一系学习的学生信息

3.exist和not exist子查询

1.查询选修1号课程的学生信息

执行过程由外向内的方式，遍历外层查询中的student表，将每行的sno传递到子查询中，看他们是否满足子查询的条件，满足条件的行出现在结果中。

2.查询没有选修1号课程的学生信息

12.合并查询union

select ...
union [all|distinct]
select ...
[union[all|distinct]
select...]

因为没有加关键字all，系统自动删除了重复的行,all可以显示符合条件的所有结果。
加上all之后：

13.使用正则查询

^ :以匹配特定字符或者字符串开头的记录
$:以匹配特定字符或者字符串结尾的记录
.:匹配字符串的任意一个字符
[]:匹配字符集合中任意一个字符
[^]:匹配不在字符集合中任意一个字符
s1|s2|s3:匹配s1、s2、s3中任意一个字符串
*：匹配多个该字符，包括0和1个。
+：匹配多个该字符，包括1个。
字符串{N}:字符串至少出现N次
字符串{M,N}:字符串至少出现M次，至多N次。

1.查询学生所在得系以’计’开头得学生记录。

14.综合案例

1.查询年龄小于20岁得女生的学号和姓名

2.查询平均分大于80分的学生姓名
and是用在where后面的
having是用在有例如SUM()这种聚合函数后面的条件 得分情况
解法1：

解法2：

3.选修数据库课程的学生人数