数据查询与统计-阿里云开发者社区

1. 单表无条件查询

Select 列名，表达式，函数

From 表名

Order by 关键字 desc|asc

（1）查询指定列

select sno,sname,sage

from student;

（2）查询所有列

mysql> select *

-> from sc;

（3）表达式的使用

mysql> select sno,cno,grade * 0.6

-> from sc;

（4）使用别名

mysql> select sno as 学号,cno as 课程号,grade * 0.6 as 期末成绩

-> from sc;

（5）使用函数

select curdate() as 当前日期;

select year(curdate()) as 当前年份;

select month(curdate()) as 当前月份;

select day(curdate()) as 当前日子;

思考：如何查询学生的出生年份？

mysql> select sno,sname,year(curdate())-sage as 出生年份

-> from student;

（6）去掉重复

查找哪些同学选课了？

mysql> select distinct sno

-> from sc;

（7）查找指定行的数据

mysql> select *

-> from student limit 2,3;

（8）排序

mysql> select *

-> from student

-> order by sno desc|asc;

**默认是升序，降序desc

多关键字排序：

举例：查询学生选课信息，并按成绩降序排列，如果成绩相同，则按学号升序排列

mysql> select *

-> from sc

-> order by grade desc,sno;

2. 带条件查询

（1）比较运算符 = > < >= <= <> 或 !=

查找成绩大于90分的记录

mysql> select *

-> from sc

-> where grade >= 90;

（2）区间运算符

x between a and b a <= x <= b

x not between a and b x > b 或 x<a

查找成绩在80—90之间的记录

mysql> select *

-> from sc

-> where grade between 80 and 90;

查找成绩不在80—90之间的记录

mysql> select *

-> from sc

-> where grade not between 80 and 90;

（3）空值判断

查找成绩还没有录入的记录

mysql> select *

-> from sc

-> where grade is null;

查找成绩已经录入的记录

mysql> select *

-> from sc

-> where grade is not null;

（4）逻辑表达式

And 与 or或 not非

举例：查找18岁以上男同学信息

mysql> select *

-> from student

-> where sage > 18 and ssex = '男';

举例：查找18岁以上的或男同学信息

mysql> select *

-> from student

-> where sage > 18 or ssex = '男';

（5）模糊查询

关键字：like

通配符：% 代表任意多个任意字符

_ 代表1个任意字符

举例：查找姓张的同学信息。

mysql> select *

-> from student

-> where sname like '张%';

举例：查找姓张的姓名为2个字的同学信息。

mysql> select *

-> from student

-> where sname like '张_';

举例：查找姓张的姓名为3个字的同学信息。

mysql> select *

-> from student

-> where sname like '张__';

举例：查找手机号以153开头，6结尾的同学信息

mysql> select *

-> from student

-> where tel like '153%6';

举例：查找手机号以153开头，倒数第2位是6的同学信息

mysql> select *

-> from student

-> where tel like '153%6_';

（6）列表运算符

就是or的简写

举例：查找18,20,21岁的同学信息

mysql> select *

-> from student

-> where sage = 18 or sage = 20 or sage = 21;

或

mysql> select *

-> from student

-> where sagein(18,20,21);

举例：查找选修了1号、2号、4号课程的学生学号。

3. 统计与分组

Select

From

Where

Group by

Hving

Order by

（1） 聚合函数

（2）分组 group by 关键字

举例：查找每门课的平均成绩

mysql> select cno,avg(grade)

-> from sc

-> group by cno;

练习：查找每个人的平均成绩

mysql> select sno,avg(grade)

-> from sc

-> group by sno;

练习：查询每个系男、女生人数

mysql> select dno,ssex,count(sno)

-> from student

-> group by dno,ssex;

（3） 筛选having，永远跟着groupby

当条件中含有聚合函数时，用having

举例：查找平均成绩大于80分的课程

mysql> select cno,avg(grade)

-> from sc

-> group by cno

-> having avg(grade) > 80;

练习：查询平均成绩在80分以上的学生学号和平均成绩

Select

From

Where

Group by

Hving

Order by

4. 连接查询

（1）两表连接

from 表1join表2on表1.列 = 表2.列

Select

From表1join表2on表1.列 = 表2.列

Where

Group by

Hving

Order by

from student join sc onstudent.sno = sc.sno

举例：查找刘晨的各科成绩

Select成绩

From sc join student on student.sno = sc.sno

Where sname = 刘晨

练习：输出所有女生的学号、姓名、课程号及成绩

mysql> select student.sno,sname,cno,grade

-> from student join sc on student.sno = sc.sno

-> where ssex = '女';

查询选修数据库课程的学生学号及其成绩

mysql> select sno,grade

-> from sc join course on sc.cno = course.cno

-> where cname = '数据库';

查询计算机系有哪些同学

mysql> select sno,sname

-> from student join dept on student.dno = dept.dno

-> where dname = '计算机系';

（2）多表连接

Student---sc ---course

From student join sc on student.sno = sc.sno

Join course on sc.cno = course.cno

举例：查询选修了数据库课程的名单

学生名单（student）---sc --- 数据库课程（course）

select student.sno,sname

from student join sc on student.sno = sc.sno

join course on sc.cno = course.cno

where cname = '数据库';

练习：查找李勇选修的课程名

select cname

from student join sc on student.sno = sc.sno

join course on sc.cno = course.cno

where sname = '李勇';

4张表的举例：同学选修的课程名（不要求）

计算机系dept—dno---student—sno---sc—cno--course课程名

select cname

from student join sc on student.sno = sc.sno

join course on sc.cno = course.cno

join dept on student.dno = dept.dno

where dname = '计算机系';

（3）外连接

举例：查看所有同学的选课情况

mysql> select student.sno,sname,cno,grade

-> from student left join sc on student.sno = sc.sno;

**join左边表中不满足连接条件的记录也出现

练习：各门课的选修情况。

mysql> select sc.cno,course.cno,grade

-> from sc right join course on sc.cno = course.cno;

5. 子查询

理解什么是子查询

（1）子查询返回单个值

举例1：c01号课程中谁的成绩不低于平均成绩？

select sno

from sc

where cno = 'c01' and grade >= (select avg(grade)

from sc

where cno = 'c01');

举例2：查询和刘晨在同一个系的学生信息。

select *

from student

where sname <> '刘晨' and dno = (select dno

from student

where sname = '刘晨');

（2）子查询返回值列表：可以使用in、not in、any、all操作符

举例1查询选修c03课程的学生平均年龄。

select avg(sage)

from student

where sno in (select sno

from sc

where cno = 'c03');

关于any和all举例：

查询成绩高于3号课任何成绩的选课信息

select*

from sc

where grade >any(select grade

from sc

where sno ='0003');

查询成绩高于3号课所有成绩的选课信息

select*

from sc

where grade > all (select grade

from sc

where sno ='0003');

数据查询与统计

热门文章

最新文章

相关课程

相关电子书

相关实验场景