数据库查询

对关系表实施的运算

(1)选择运算

根据给定条件，从二维关系表中将制定的记录挑选出来。

(2)投影运算

把二维关系中指定的列提取出来构成一张新表

(3)连接运算

 select 语句：用于数据查询

一、基本查询

1、选择列--select语句

select 列名1，列名2...

from 表名;

select 学号,姓名,性别,出生日期

from xs;

select *from xs;

给指定列起别名

select 列名 as 别名，列名as 别名...

from 表名;

查询计算列

select 姓名,总学分,总学分+10 as 提高后的总学分

from xs;

消除查询结果中的重复行

selectdistinct 列名

from 表名;

替换查询结果中的数据

select 学号,姓名,

case

when 总学分<50 then '不及格'

when 总学分>=50 and 总学分<60then '及格'

when 总学分>=60 then '优秀'

end as 等级

from xs;

使用聚合函数

selectmax(总学分),min(总学分)

from xs;

max(数值型列) 求这一列的最大值

min(数值型列) 求这一列的最小值

avg(数值型列) 求这一列的平均值

sum(数值型列) 求这一列的总和

count(*) 求的是表格中的记录的行数

count(指定某一列) 求的是指定列不为空的取值的个数（包括重复值）

count(distinct指定某一列) 指定列的取值去掉null值，去掉重复值后的记录的个数

 

2、选择运算---where子句

（1）关系表达式

>  >= <  <=  = <>(不等) !=   <=>(相等关系的比较)

select *

from xs

where 总学分>45;

select *from xs

where 备注<=>null;

select *from xs

where isnull;

或where is not null;

（2）逻辑表达式

select 学号,姓名,总学分

from xs

where 总学分>=40 and 总学分<=50;

select 学号,姓名,总学分

from xs

where 总学分<45 or 总学分>50;

and  &&

or   ||

not!

 

select 学号,姓名,总学分

from xs

wherenot(总学分>=40 and 总学分<=50);

 

select 学号,姓名,总学分

from xs

wherenot(学号='081101');或

select 学号,姓名,总学分

from xs

where !(学号='081101');

 

(3)betweenand

select 学号,姓名,总学分

from xs

where 总学分 between 45 and 50;

select 姓名,出生日期

from xs

where 出生日期 between '1989-01-01'and '1989-12-31';

select 姓名,出生日期

from xs

where 出生日期>'1989-01-01'and 出生日期<'1989-12-31';

 

(4)

select 姓名,总学分

from xs

where 总学分=38 or 总学分=40or 总学分=48;

select 姓名,总学分

from xs

where 总学分 in(38,40,48);

select 姓名,专业名,总学分

from xs

where 专业名='计算机'or 专业名='通信工程';

select 姓名,专业名,总学分

from xs

where 专业名 in('计算机','通信工程');

（5）模糊查询like '模式匹配字符串'

在'模式匹配字符串'中可以：%任意个任意的字符  _一个任意的字符

select 学号,姓名,总学分

from xs

where 姓名 like '王%';

select 学号,姓名

from xs

where 学号 like '__11__';

select 姓名,专业名

from xs

where 姓名 like '%#_%' escape '#';//转义字符

正则表达式

rlike '正则表达式的字符串'

或者 REGEXP'正则表达式的字符串'

select 学号,姓名,专业名

from xs

where 姓名 rlike '^王';

select 学号,姓名,专业名

from xs

where 姓名 rlike '林$';

rlike'.*'

rlike'^08.*08$'

rlike'a+'

rlike'[abc]'

rlike'[a-z]'

rlike'^[a-z]'

3、order by 对查询结果进行相应的排序

不会影响原有的顺序

select 列名1,列名2,...

from 表名

where 查询条件

order by 列名1[,列名2,...]

 

select 学号,姓名,出生日期

from xs

order by 出生日期;

desc 降序   asc  升序

 

select 学号,课程号,成绩

fromxs_kc

order by 课程号 asc,成绩desc;

如果order by子句中的排序列有多个，先按第一个指定列排序，在第一个指定列的值相同的情况下，再按第二个指定列

进行排序。

如果排序列有多个，每一列中需指明是升序还是降序

4、limit子句：限制的是显示的记录的行数，一般是和order by子句一起用。

limit[位置量,]显示记录的行数

select 学号,课程号,成绩

fromxs_kc

where 课程号='101'

order by 成绩 desc;

limit5;//取前五条记录

 

select 学号,课程号,成绩

fromxs_kc

where 课程号='101'

order by 成绩 desc

limit3,3;//前3表示位移量，从第四行记录开始

 

二、高级查询：多表连接查询、子查询、分组汇总

1、多表连接查询

姓名,课程名,成绩

xs   kc    xs_kc

 

(1) 全连接--不是标准呢的sql语句

select 列名1,列名2,...

from 表名1,表名2,...

where 表名1.列名=表名2.列名

查看：学生学号,姓名,选修的课程号,得分

      xs xs_kc xs    xs_kc      xs_kc

selectxs.学号,姓名,课程号,成绩

fromxs,xs_kc

where xs.学号=xs_kc.学号;

 

查看：学号、课程号、课程名、学时

     xs_kc kc xs_kc   kc   kc

select 学号,xs_kc.课程号,课程名,学时

fromxs_kc,kc

wherexs_kc.课程号=kc.课程号;

 

查看：学生姓名,课程名,得分

      xs      kc     xs_kc

select 姓名,课程名,成绩

fromxs,xs_kc,kc

where xs.学号=xs_kc.学号and kc.课程号=xs_kc.课程号;

 

(2)--标准的sql语句，推荐使用

内连接，外连接，自然连接，交叉连接

1）内连接：多个表通过连接条件中的共享列的值进行的比较连接

from 表名1[inner] join biao 表名2 on 连接条件

 

select 列名表

from 表名1 join 表名2

on 连接条件

 

selectxs.学号,姓名,课程号,成绩

from xsjoin xs_kc

using(学号)//on xs.学号=xs_kc.学号; using  共享

 

select 姓名,课程名,成绩

from xsjoin xs_kc

on xs.学号=xs_kc.学号  

join kc

on xs_kc.课程号=kc.课程号;

 

2）外连接：包含来自一张表的所有记录行和另一张的匹配记录行

左外连接 from 表名1 left[outer] join 表名2 on 连接条件

右外连接 from 表名1 right[outer] join 表名2 on 连接条件

查询：所有学生的学号，姓名以及他们所选课程的课程号，成绩

          xs xs_kc  xs                    xs_kc    xs_kc

 

selectxs.学号,姓名,课程号,成绩

from xsleft outer join xs_kc

on xs.学号=xs_kc.学号;

 

查询：学生选修课程的信息（学号、课程号、课程名）,还有没有被学生选修的课程的情况

select 学号,xs_kc.课程号,课程名

fromxs_kc right outer join kc

on xs_kc.课程号=kc.课程号;

 

3）自然连接

 

naturaljoin 自然内连接

naturalleft join

naturalright join

 

selectxs.学号,姓名,课程号,成绩

from xsnatural join xs_kc;

 

4)交叉连接--笛卡尔积

 

selectxs.学号,姓名,课程号,成绩

from xscross join xs_kc

on xs.学号=xs_kc.学号;

 

2、嵌套查询 ：在一个select语句中又包含另一个select语句

 

子查询一般都可以写成多表的连接查询

（1）子查询

select 学号,课程名

fromxs_kc natural join kc

where 课程名='计算机基础';

换一种思路：

 

select 学号

from xs_kc

where 课程号=(

       select 课程号

       from kc

       where 课程名='计算机基础'

)

第一步：执行子查询，101  只执行一次

第二步：执行外部查询，外部查询的查询条件好似依赖与子查询

查询： 所有成绩比平均成绩高的学生的学号，课程号

 

select 学号,课程号

fromxs_kc

where 成绩>(

       select avg(成绩)

       from xs_kc

)

子查询只做一次 75.2

 

 

（2）子查询的结果不是单列单行，而是单列多行。

解决办法：in

= ><  all或any子查询的结果集

选修了206的课程的学生的姓名

 

xs:学号，姓名

xs_kc:学号，课程号

 

select 姓名

from xsjoin xs_kc

on xs.学号=xs_kc.学号

where 课程号='206';

 

select 姓名

from xs

where 学号 in (select 学号from xs_kc where 课程号='206');

先执行子查询 ，也只执行一次

 

select 姓名

from xs

where 学号 not in (select 学号 from xs_kc where 课程号='206')

 

select 姓名

from xs

where 学号 = any (select 学号from xs_kc where 课程号='206');

 

select 姓名

from xs

where 学号 <>all (select 学号 from xs_kc where 课程号='206');

 

where 列名> all(子查询)    (1,8,9) 列的取值>9  查找的是比最大值还要大的记录行

<=all(子查询) (1,8,9)   列的取值<=1  查找的是比最小值还要小的记录行

>=all(子查询)

<=all(子查询)

=all(子查询)

<>all(子查询)

>any(子查询) (1,8,9) 查找的是比最小值大的记录行

<any(子查询) (1,8,9) 查找的是比最大值小的记录行

>any(子查询)

<any(子查询)

=any(子查询)

<>any(子查询) (1,8,9)

 

select 出生日期

from xs

where 专业名='计算机基础';

 

select 学号,姓名,专业名,出生日期

from xs

where 出生日期< all(

select 出生日期

from xs

where 专业名='计算机'

);

 

select 学号,姓名,专业名,出生日期

from xs

where 出生日期> all(

select 出生日期

from xs

where 专业名='计算机'

);

 

(3) whereexists(子查询)

如果子查询的结果集是非空，该判定为true，否则，该判定的false

 

查询: 选修206课程的学生姓名

select 姓名

from xs

whereexists (select * from sx_kc where 学号=xs.学号 and 课程号='206');

 

selectcount(*)

from xs;

3.分组group by

默认按升序显示分组的信息，可以使用desc来指明显示分组信息按降序

select 专业名,count(*)

from xs

group by 专业名;//分组

 

查询：每个学生的总成绩

查询：每门功课的平均分

 

select 学号,sum(成绩)

fromxs_kc

group by 学号;

 

select 课程号,avg(成绩)

fromxs_kc

group by 课程号;

 

select 课程号,学号,avg(成绩)

fromxs_kc

group by 课程号;

 

带有with rollup的分组汇总在原有的分组汇总的结果上又增加了汇总的信息。

select 专业名,性别,count(*)

from xs

group by 专业名,性别with rollup;

 

selectxs_kc.课程号,课程名,avg(成绩)

from kcjoin xs_kc

on kc.课程号=xs_kc.课程号

group by 课程号;

 

having子句，having条件：对分组汇总的信息进行筛选

selectxs_kc.课程号,课程名,avg(成绩)

from kcjoin xs_kc

on kc.课程号=xs_kc.课程号

group by 课程号

havingavg(成绩)>77.0;

 

where子句

having子句

对表中的记录按where中设定的条件进行筛选，然后，进行分组汇总，最后用having对分组汇总的结果再进行筛选

 

出生时间是：1989-10以后出生的学生按专业分组，统计每个专业的学生人数，将人数>3人的信息显示出来

select 专业名,count(*)

from xs

where 出生日期>'1989-10-01'

group by 专业名

havingcount(*)>3;

 

union

select 学号,姓名

from xs

where 专业名='计算机'

union

select 课程号,课程名

from kc

where 学分>4

order by1 desc

limit 5;

 

select 专业名,

 

引用一张表

（1）use数据库

select...

from 表名;

(2)

select...

from 数据库.表名;