MYSQL查询

参考Jennifer Widom的Pdf
简介：MySql数据库是一种关系型的数据库，SQL语言数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
内容简介：本文将从SQL语言的四大功能DDL，DML,DCL以及其他命令详细讲解SQL语法。

1.DDL （Data Definition Language ）数据库定义语言
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束
DDL不需要commit.

命令	作用	举例
create	创建数据库，表等	create database databasename
drop	删除数据库，表等	drop database databasename
alter	修改数据库,表等	Alter table table_name add column_name column_type [default 默认值]
…	…	…

2.DML （Data Manipulation Language）数据操纵语言
包括select/insert/delete/update。由DBMS提供，用于让用户或程序员使用，实现对数据库中数据的操作。
DML分成交互型DML和嵌入型DML两类。
依据语言的级别，DML又可分成过程性DML和非过程性DML两种。
需要commit.

3.DCL（Data Control Language）数据库控制语言 授权，角色控制等
例如GRANT 授权 /REVOKE 取消授权。

4.TCL（Transaction Control Language）事务控制语言
SAVEPOINT 设置保存点 /ROLLBACK 回滚 /SET TRANSACTION

综上，SQL主要分成四部分：
（1）数据定义。（SQL DDL）用于定义SQL模式、基本表、视图和索引的创建和撤消操作。
（2）数据操纵。（SQL DML）数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
（3）数据控制。包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
（4）嵌入式SQL的使用规定。涉及到SQL语句嵌入在宿主语言程序中使用的规则。
基础的语法结构应该是

select A1,A2,A3...An
from R1,R2,R3...Rn
where condition

下面开始运用几个数据表进行每种语法的实战。提供以下数据集。
需要注意的是sql不区分大小写。结尾分号

college(cName,statem,enrollment) -- 包含大学名字，地区，和录取人数
student(sID,sName,GPA,sizeH) -- 学生编号，高中学校名字，GPA,学校规模
apply(sID,cName,major,decision) -- 学生编号，申请学校名字，申请专业，申请结果

数据集具体如下：
college(cName,statem,enrollment) – 包含大学名字，地区，和录取人数

student(sID,sName,GPA,sizeH) – 学生编号，高中学校名字，GPA,学校规模

apply(sID,cName,major,decision) – 学生编号，申请学校名字，申请专业，申请结果

SQL/Basic SELECT Statement

select

• GPA大于3.7的学生姓名和编号

select sID,sName
from student
where GPA>3.7;

• 选择学生的sName和GPA,满足这些学生高中规模SizeHS<1000,申请了Stanford的CS专业，同时得到录取结果。

select sName,GPA,decision
from student s,apply a
where s.sID = apply.sID
and sizeHS<1000 and cName='Stanford' and major='CS';

• ids,names and gpas,cname,enrollment of students

select s.sid,sname,gpa,c.cname,enrollment
from student s,apply a,college c
where s.sid=a.sid and a.cname=c.cname;

distinct

• 选择有重复和不重复的sID/major(distinct)

select sid,major
from apply;
select distinct sid,major
from apply;

order by

• 以gpa对学生姓名进行排序并选择出gpas
• 降序升序
• 限制输出个数，即输出前几名之类的意思
• 以gpa做第一排序，sname做第二排序

select sname,gpa from student order by gpa desc;
select sname,gpa from student order by gpa asc;
select sname,gpa from student order by gpa desc limit 3;;
select sname,gpa from student order by gpa desc limit 5;
select sname,gpa from student order by gpa,sname;

like predicate

• 输出学生编号和专业，满足申请了含有bio字母的专业（类似生物等）

select sid,major
from apply
where major like '%bio%';

*

• 返回student这个表的所有属性

select *
from student;

Using arithmetic within SQL clauses

在SQL子句中使用算术

• 返回某种加权GPA

select sid,sname,sizeHS,gpa,gpa*(sizeHS/1000.0)
from student;
select sid,sname,sizeHS,gpa,gpa*(sizeHS/1000.0) as scaleGPA
from student;

Self join自连接

• 返回有同样GPA的学生（含自己==自己的情况和不含自己的情况）

select s1.sname,s1.gpa,s2.sname,s2.gpa
from student s1,student s2
where s1.gpa=s2.gpa;

select s1.sname,s1.gpa,s2.sname,s2.gpa
from student s1,student s2
where s1.gpa=s2.gpa and s1.sname<>s2.sname;

Union

select sname from student
union
select cname from college;

select sname from student
union all
select cname from college;

Subqueries in Where

in/not in

• 申请了cs的学生名字

select sid,sname 
from student
where sid in
(select sid
from apply 
where major ='cs');

• 申请了cs但是没有ee的学生gpa

select gpa 
from student
where sid in
(select sid from apply where major='cs')
and sid not in
(select sid from apply where major='ee');

Exists/Not Exists

• 最高GPA学生的GPA和名字

select sname,gpa
from student s1
where not exists
(select *
from student s2
where s2.gpa>s1.gpa);

All/Any

• 最高GPA学生的GPA和名字

select sname,gpa
from student
where gpa>=all(
select gpa 
from student);
select sname,gpa
from student s1
where gpa>all(
select gpa from student s2
where s1.sid<>s2.sid);

From

• 加权gpa与gpa差距大于1的学生

select *
from(select sid,sname,gpa,gpa*(sizeHS/1000.0) as scaledGPA from student) as G
where abs(scaledGPA-GPA)>1.0;

The Join family of operators

内连接

• 选择学生的sName和GPA,满足这些学生高中规模SizeHS<1000,申请了Stanford的CS专业，同时得到录取结果。

select sName,GPA,decision
from student s,apply a
where s.sID = apply.sID
and sizeHS<1000 and cName='Stanford' and major='CS';

也可以用using连接

select sName,GPA,decision
from student join apply 
using(sid)
where sizeHS<1000 and cName='Stanford' and major='CS';

连接超过两个表

select a.sid,sname,gpa,a.cname,enrollment
from apply a,student s,college c
where a.sid=s.sid and a.cname=c.cname;

自然连接

• 选择学生的sName和GPA,满足这些学生高中规模SizeHS<1000,申请了Stanford的CS专业，同时得到录取结果。

select sName,GPA,decision
from student natural join apply 
where sizeHS<1000 and cName='Stanford' and major='CS';

连接超过两个表

select apply.sid,sname,gpa,apply.cname,enrollment
from(apply natural join student)natural join college;

左连接

• 学生的录取信息：姓名/ID/大学名字/申请专业

select sname,sid,cname,major
from student left join apply using(sid);

全连接(笛卡尔集）

• 学生的录取信息：姓名/ID/大学名字/申请专业

select sname,sid,cname,major
from student full join apply using(sid);

描述性计算

sum/avg/min/max

• 返回平均GPA
• 返回总GPA
• 返回最小GPA
• 返回最大GPA
• 计数count

select avg(gpa) from student;
select sum(gpa) from student;
select min(gpa) from student;
select max(gpa) from student;
select count(*) from student;

Group by

• 返回每个大学专业数

select cname,count(distinct sid)
from apply
group by cname;

select cname,sum(distinct sid)
from apply
group by cname;

having

• 查找申请数小于5的大学

select cname,count(*)
from apply 
group by cname
having count(*)<5

NULL

insert into student values(432,'klevin',null,1500)
select sid from student where sname is null;
select count(*)
from student
where sname is null;

INSERT

insert into Table values(A1,A2,..An)
insert into Table values select-statement

Delete

delete from Table where condition

Update

update Table
set attr = Expreesion
where condition