文章目录
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.选修数据库课程的学生人数