数据库 DQL 数据库查询语句
目录
自然连接(等值连接基础上删除重复行 其实就是将要显示的字段都列出来)
DQL:数据查询语言
操作对象：记录(行)
关键字：select
符号约定:
<> 必须
[] 可选
{}或|:必须选其中之一
[,...N]:前面的项可以重复多次
select [all|distinct] <目标表达式> --distinct去掉重复行
from <表名或视图名>
where <条件表达式>
group by <列名1> [having <条件表达式>]
order by <列名2> [asc|desc];
select 字段 [as] <别名> from 表名 where 条件表达式
1.简单单表查询
select Sno from SC; -- 查询所有行的学号 等价于select all Sno from SC;
select distinct Sno from SC; -- 查询所有行学号 去掉重复行 (相当于查询共有多少种学号)
查询结果取别名
select Sno as '学号' from SC; -- 查询结果列名显示为‘学号’
--as可以省略 学号的引号sql server都允许省略 如下
select Sno 学号 from SC;
2.查询条件
| 查询条件 | 谓词 |
|---|---|
| 比较 | =,>,<,>=,<=,!=,<>,!>,!<;not 加上述比较运算符 |
| 确定范围 | between and , not between and |
| 确定集合 | in , not in |
| 字符匹配 | like , not like |
| 空值 | is null , is not null |
| 多重条件（逻辑运算） | and , or , not |
!=与<>都是不等于
2.0全查询与部分查询
select * from 表名;
select * from Student; --查询Student表的所有信息select 字段名1,字段名2... from 表名;
select Sname from Student; -- 查询Student表的Sname信息 (只查询Student表Sname这一列的所有信息)
2.1 比较
select Sname from Student where sdept='CS'; -- 查询‘CS’部门所有学生的姓名
select Sname,Sage from Student where Sage<20; -- 查询学生表所有年龄小于20的学生姓名与年龄
select distinct Sno from SC where Grade<60; -- 查询不及格学生姓名 （可能某学生多门不及格 distinct去重）
2.2范围 between / not between
select * from 表名 where 字段名x between A and B; -- 字段x值在[A,B]之内
select Sname from Student where Sage between 20 and 30; --查询年龄在[20,30]的学生姓名
select Sname from Student where Sage not between 20 and 30; --查询年龄不在[20,30]的学生姓名
2.3集合 in / not in
select * from Student where Sdept in ('CS','MA','IS'); -- 查询'CS','MA','IS'三个部门的学生信息
(in其实是多个or的缩写 上行语句等价于 select * from Student where Sdept='CS' or Sdept='MA' or Sdept='IS'）
select * from Student where Sdept not in ('CS','MA','IS'); -- 查询'CS','MA','IS'三个部门的学生信息
(同理上行语句等价于 select * from Student where Sdept!='CS' and Sdept!='MA' and Sdept!='IS'; )很明显用in或not in比用or and强多了
2.4字符匹配like / not like
-- 模糊匹配
-- 格式：字段名 like "匹配规则";
-- 匹配规则2种：
匹配内容 %
"龙" 值为"龙"
"%龙" 值以"龙"结尾
"龙%" 值以"龙"开头
"%龙%" 值内包含"龙"
匹配个数 '__' 占2个位置 (几个下划线占几个位置)注：sql server字符串只能用单引号扩起来
select * from Student where Sno like '201215121'; -- like后无%_ 而是写了一个确定的值，like相当于=
select * from Student where Sname like '刘%'; -- 查询所有姓刘的学生信息
select * from Student where Sname not like '刘%'; -- 查询所有不姓刘的学生信息select * from Student where Sname like '欧阳_';--查询姓欧阳且全名只有3个汉字的学生信息
select * from Student where Sname like '_阳%';--查询名字中第二个字为阳的学生信息
通配符%与_的转义 escape '换码字符'
select * from Course where Cname like 'DB\_Design' escape '\'; -- 查询DB_Design课程的信息
(escape '换码字符'换码字符临时定义 如这样写也行select * from Course where Cname like 'DB|_Design' escape '|';)select * from Course where Cname like 'DB\_%i__' escape '\';--查询DB_开头 倒数第三个字符为i的课程信息
2.5空值查询 is null / is not null
select Sno from SC where Grade is null;-- 查询成绩为空值(旷考)的学生学号
select * from SC where Grade is not null;--查询所有有成绩的学生SC表信息
2.6多重条件查询and / or
select * from Student where Sdept='CS' and Sage<20; -- 查询计算机系年龄在20岁以下的学生信息
select * from Student where Sdept='CS' or Sdept='MA' or Sdept='IS' -- 查询'CS','MA','IS'三个部门的学生信息
(in其实是多个or的缩写 上行语句等价于 select * from Student where Sdept in ('CS','MA','IS');）
2.7order by子句
select * from SC where Cno=3 order by Grade desc; --查询选修了3号课程的学生成绩信息 按成绩降序排列
select * from Student order by Sdept,Sage desc;--查询全体学生信息，结果按系号升序排列，同一系的按年龄降序排列
(上行Sdept后省略了asc,因为默认就是升序)
3.聚集(合)函数
| count(*) | 统计元组(行)个数 |
| count([distinct|all] <列名>) | 统计一列中值的个数 |
| sum([distinct|all] <列名>) | 计算一列值的总和(此列必须是数值型) |
| avg([distinct|all] <列名>) | 计算一列值的平均值(此列必须是数值型) |
| max([distinct|all] <列名>) | 求一列值中的最大值 |
| min([distinct|all] <列名>) | 求一列值中的最小值 |
3.1count
select count(*) from Student; -- 查询学生总人数
select count(distinct Sno) from SC;--查询选修了课程的学生人数
select count(Sno) from SC;--查询选修了课程的学生人次
3.2avg
select avg(Grade) 平均成绩 from SC where Cno='1';--计算1号课程学生平均成绩
--计算1号课程学生平均成绩 无成绩(Grade为null值)的人次不算 结果为整数，无小数
3.3max
select max(Grade) from SC where Cno='1';--查询选修了一号课程的学生的最高分数
3.4sum
select sum(Ccredit) from SC,Course where Sno='201215122' and SC.Cno=Course.Cno;--此处逻辑比较简单选了就有学分
--查询学生201215122选修课程的总学分 涉及多表查询详见后文 sum()对查询的多行结果的Ccredit列求和
注意：聚集函数不能放在where后面，只能放在select子句后面或者group by的having子句后面（where是对单行一个一个筛选而聚集函数是对查询后的结果进行的操作）
4.group by子句
group by子句将查询结果按某一列或者多列的值分组，值相等的为一组。
对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组，聚集函数将作用于整个查询结果，如前例。分组后聚集函数将作用于每一个组，即每一组都有一个函数值
聚集函数合group by配合使用 就是在group by的基础上对每组作用
4.1.select后接聚集函数
select cno 课程号 from SC;
select cno 课程号 from SC group by Cno;
select cno 课程号,count(Sno) 选课人数 from SC;--报错
select cno 课程号,count(Sno) 选课人数 from SC group by Cno;--查询各个课程的选课人数 结果如下 (改成Count(Cno)也一样)
4.2.having子句接聚集函数
select Sno from SC;
select Sno from SC group by Sno;
select Sno,count(*) from SC group by Sno;
select Sno from SC group by Sno having Count(*)>1;--查询选修了3门以上课程的学生学号 count是在group by的基础上筛选的
4.3where勘误
查询平均成绩大于等于80的学生学号和平均成绩
select Sno,avg(Grade) from SC where avg(Grade)>=80 group by Sno; -- 错误 聚集函数不能放在where后面
select Sno,avg(Grade) from SC group by Sno having avg(Grade)>=80;--正确
5.连接查询(多表查询)
查询同时涉及两个以上的表，关系数据库中最主要的查询，包括等值连接查询、自然连接查询、非等值连接查询、自身连接查询、外连接查询和复合条件连接查询。
5.1等值连接与非等值连接查询
连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词
连接谓词中的列名称为连接字段。连接条件中的各连接字段类型必须是可比的，但名字不必相同。连接运算符为=时为等值连接。使用其他运算符为非等值连接
广义笛卡尔积
多张表无条件的联合查询.没有任何意义(就是全部组合)
select a.*,b.* from a,b;
需要在笛卡尔积基础上加条件，才有意义eg:select Student.*,SC.* from Student,SC;
等值连接
查询每个学生及其选修课程的情况(课程表里有选课和课程信息)
select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;-- sno相等的配对 等值连接
自然连接(等值连接基础上删除重复行 其实就是将要显示的字段都列出来)
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno;
--有歧义的加前缀. 无歧义的不用加
select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and SC.Cno='2' and SC.Grade>90;
--查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
5.2自身连接
一个表与其自己进行连接
select 别名1.字段,别名2.字段 from 表名 别名1,表名 别名2 where 别名1.字段 关系符 别名2.字段
select first.Cno,second.Cpno from Course first,Course second where first.Cpno=second.Cno;
-- 查询每门课程的间接先修课 也即是先修课的先修课
5.3外连接
通常的连接操作中，只有满足连接条件的元组才能作为结果输出，入上例中:Cno=2和6的由于Cpno=null导致没有Cno与其相等，查询结果中就没有输出了。外连接的主体表(左外连接就是左边表 右外连接就是右边表) 无保留全部输出，副表匹配的显示匹配信息，没有匹配的显示null
-- 学生选课情况 未选课的也列出来
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student left outer join SC on (Student.Sno=SC.Sno);(等价于此右外连接:
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from SC right outer join Student on (Student.Sno=SC.Sno);)
5.4多表连接
-- 查询每个学生选的每一门课及其成绩 简单where即可实现3表连接 普通连接无匹配的(未选课的)还是不显示出来
select Student,Sno,Sname,Cname,Grade from Student,SC,Course where Student.Sno=SC.Sno and SC.cno=Course.Cno;
6.嵌套查询(有些可用连接代替)
一个select-from-where语句称为一个查询块，将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询
--查询选修了2号课程的学生姓名
select Sname from Student where Sno in (select Sno from SC where Cno='2');
6.1 带有in谓词的子查询
--查询与刘晨在同一个系学习的学生
select * from Student where Sdept=(select Sdept from Student where Sname='刘晨');
(等价于下行的自身连接
select S1.* from Student s1,Student s2 where S1.Sdept=S2.Sdept and S2.Sname='刘晨';)
6.2 3张表的外连接
--查询选修了课程名为'信息系统'的学生学号和姓名
select Student.Sname,Student.Sno from Student where Sno in
(select Sno from SC where Cno in
(select Cno from Course where Cname='信息系统')
);
--where连接实现
select Student.Sname,Student.Sno from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Course.Cname='信息系统';
有些嵌套查询可以用连接查询运算代替（以上两个都可以），有些不能用连接代替。目前商用数据库管理系统对嵌套查询的优化做得还不够完善所以在实际应用中，能够用连接运算表达的查询尽可能采用连接运算。
子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询。子查询的查询条件依赖于父查询，这类子查询称为相关子查询。
--到书本第105页 未完待续