sql语言查询与集合操作

完整sql语句较复杂，但是其基本语法格式可以归纳如下：

select select_list

from table_list

[where search_conditions]

[group by group_by_expression]

[having search_conditions]

[order by order_expression[asc| desc]]

简单查询

例 1：查询Teacher表中全体教师的记录。
select * from Teacher;

例 2：查询全体教师的教师编号，姓名和职称信息。
select 教师编号,姓名,职称信息 from Teacher;

例 3：查询Teacher表中全体教师的姓名及年龄。（函数）
insert into students
values('2350150241','徐耀民','男','1995-12-09','中华人民共和国','235','01502');
select 姓名,年龄 from Teacher;
年龄->ceil(datediff(current_date(),出生日期))
select Sname 姓名,ceil(datediff(current_date(),Sbirth)/365)年龄 from students;

例 4：查询教师所在部门的部门代码。（DISTINCT）
select distinct Dept_ID from Teacher;

例 5：查询SC表中成绩不及格的记录。（关系运算符）
select * from SC where Grade < 60;

例 6：查询Teacher表出身日期在1960-1980年的教师姓名，编号，和出身日期。（BETWEEN AND）
select Tname,Teacher_ID,Tbirth from Teacher where year(Tbirth) between '1960' and '1980'; 

例 7：查询系部代码为 081和082的系部名称和系主任。（IN()）
select Dept_name,Director from Dept where Dept_ID in('081','082');
insert into students
values('2340140110','徐耀明','男','1995-04-09','中华人民共和国','234','01401');
select Sname,Student_ID from Student where Sbirth in('1995-12-09','1995-04-09');

例 8：查询Students表中姓‘李’的学生的信息。（LIKE）
select * from Students where Sname like '李%';
%任意长度的字符,_任意单个字符,[]括号里的任意一个字符,[^]不在括号里的任意一个字符

例 9：查询Course表中课程名为Oracle_12c数据库教程的课程信息。（LIKE \转义）
select * from Course where Cname like 'Oracle\_12c数据库教程';

例 10：查询教师表中学历不为空的教师信息。（IS NOT NULL）
select * from Teacher where Educ is not null;

例 11：查询SC表中课程号为0003，成绩在90-00分之间的学生的学号，成绩。（AND,OR,NOT）
select * from SC where Course_ID = '0003' and Grade <= 90;

例 12：查询SC表中选修课程号为0001并且成绩大于70分的学号，要求输出结果成绩降序排列，当成绩相同时按学号升序排列。（order by）
select * from SC where Course_ID = '0001' and Grade > 70 order by Grade desc, Student_ID asc;

例 13：统计查询学生总人数，以及参加选课的学生的人数。（统计 count,sum,avg,max,min）
count([distinct | all] column_name | *)
sum([distince | all] column_name)
select count(*) from Students;
select count(distinct Student_ID) from Students;

例 14：查询选修0001课程学生的最高分，最低分和平均分。
select max(Grade),min(Grade),avg(Grade) from SC where Course_ID = '0001'; 

例 15：查询每一位学生的选课总数。（group by）
insert into SC 
values('001','a',66),('001','b',67),('002','a',85);
select Student_ID 学号, count(Course_ID) 选课总数 from SC group by Student_ID;

例 16：查询每一位学生选课成绩超过90分的选课门数以及平均分。
select count(Course_ID) 选课门数, avg(Grade) 平均分 from SC where Grade > 90 group by Student_ID;

*：count统计函数会将记录集当前指针向后移动至末尾，所以没使用group by的查询可能只输出一条记录，因为当记录到达末尾将停止查询。
若果使用group by(column_name)，具有相等column_name值的记录将被分为一个组，当前指针移动到该组末尾将转到下一组起点重新移动到末尾。
count不可以直接接在where后面，可以用在子查询的having筛选中。 

连接查询

点击可以查看用到的几张表

交叉连接也称非限制连接，也叫广义笛卡儿积，其连接结果会产生一些没有意义的记录，并且进行该操作非常耗时，因此该运算的实际意义不大。

select table_a.*,table_b.* from table_a,table_b;

等值连接当连接条件中的关系运算符使用“=”时，称其为等值连接。

select list_name
from table_nameA [inner] JOIN table_nameB
ON table_nameA.column_name = table_nameB.column_name;

select dept.*,class.*
from dept inner join class on dept.dept_id = class.dept_id;

如果在等值连接中，把目标列中重复的属性列删除，也就是在select后面不要输出重复的列，此时的等值连接可称为 自然连接。

非等值连接当连接条件中的关系运算符使用除“=”以外的其他关系运算符时，这样的内连接称为非等值连接。

在实际的应用开发中，很少用到非等值连接，尤其是单独使用非等值连接，他一般和自然连接查询同时使用。

自连接一个表与其自身之间连接。

例 17：使用教师Teacher表查询与“王鹏飞”在同一个系任课的教师编号，教师姓名和教师的职称，要求不包括“王鹏飞”本人。

select t1.teacher_id,t1.tname,t1.tprofess
from teacher t1 join teacher t2
on t1.dept = t2.dept and t2.tname = '王鹏飞' and t1.name != '王鹏飞';

*：等值连接，非等值连接，自连接统称为 内连接。

内连接只返回在两个表中特定列有匹配值的记录，外连接可以返回匹配的记录还可以返回对应的表中没有匹配值的记录。

左外连接：返回所有的匹配的行并从关键字join左边的列表返回所有不匹配的行。

右外连接：返回所有的匹配的行并从关键字join右边的列表返回所有不匹配的行。

完全外连接：返回两个表中所有匹配的行和不匹配的行。

select list_name
from table_nameA {left | right | full} [outer] join table_nameB 
on table_nameA.column_name = table_nameB.column_name;

子查询

使用in谓词的子查询判断指定列的值是否出现在子查询记录集中

where 表达式 [not] in (子查询)

select teacher_id,tname,profess 
from teacher
where dept_id in (
    select dept_id
    from dept
    where dept_name in('计算机科学与技术','信息管理系')
);

使用比较运算符的子查询的结果为一个单行单列的值，如果返回的不止一个值，整个查询语句将会产生错误

例 17：使用教师Teacher表查询与“王鹏飞”在同一个系任课的教师编号，教师姓名和教师的职称，要求不包括“王鹏飞”本人。

select teacher_id,tname,profess
from teacher
where dept_id = (
    select dept_id 
    from teacher 
    where tname = '王鹏飞'
) and tname != '王鹏飞';

使用any或all的子查询 当子查询返回单值时可以使用比较运算符，但返回多值时要用any或all谓词修饰符

any：表示满足子查询结果的任何一个

all：表示满足子查询的所有结果

ex：where xxx >  any (子查询)，在子查询前面添加谓词

使用exists的子查询有时候只需要考虑判断条件而不需要考虑数据本身，可使用exists

与in相似，通常可以互相转换

ex：where exists (子查询)

例 17：使用教师Teacher表查询与“王鹏飞”在同一个系任课的教师编号，教师姓名和教师的职称，要求不包括“王鹏飞”本人。

select teacher_id,tname,profess
from teacher t1
where exists (
    select * 
    from teacher t2
    where t1.dept_id = t2.dept_id and tname = '王鹏飞'
) and tname != '王鹏飞';

集合操作

使用union操作符

• union结果集的列名与第一个select语句结果集中的列名相同
• 默认情况下union操作符将从结果集删除重复的行
• 使用union，那么各个select语句不能包含order by语句，只能在最后面一个select语句后使用适用于最终的结果集

select student_id,sname
from students
where sname like '张%' sname like '李%';
UNION
select student_id,sname
from students
where sname like '李%' sname like '王%';

UNION[ALL] ：返回各个查询检索出的所有行，all代表包括重复的行，默认不带all，即不包括重复的行

INTERSECT：返回两个查询检索出的共有行

MINUS：返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行

使用INTERSECT操作符

参考使用union操作符

使用MINUS操作符

参考使用union操作符

例 18：统计图书信息表中不同出版社的图书的数目，把统计结果大于或等于 2 的结果

select * from book where p_id in(
    select * from book group by p_id having(count(p_id)>=2);
);

select * from book t1 where exists(
    select p_id from book t2 where t1.p_id = t2.p_id group by p_id having(count(t2.p_id)>=2)
);