基础SQL语句总览（库，表，记录）

• 操作数据库

  增：create database db1 charset utf8; 删：drop database db1; 改：alter database db1 charset latin1; 查：show databases;

• 操作表

  增：create table t1 (id int,name char); 删：drop table t1; 改：alter table t1 modify name char(3); alter table t1 change name name1 char(2); 查：show tables;

• 操作记录（表的内容）

  增：insert into t1 values(1,”zhangsan”),(2,”lisi”),(3,”wangwu”); 删：delete from t1 where id=1; 改：update t1 set name=”eagle” where id=3; 查：select * from t1;

1.数据定义

SQL的数据定义功能包含模式、表、视图和索引的创建、删除等操作。

1.1模式的定义与删除

定义模式：

create schema <模式名> authorization <用户名>

模式名如果省略则默认模式名与用户名相同

• 例：创建模式Test，用户名为ZHANG，并在模式下创建表TAB1

create schema Test authorization ZHANG
create table TAB1(col1 smallint,
col2 int,
col3 char(10),
col4 numeric(10,3),
col5 decimal(5,2)
);
删除模式：

drop schema <模式名>

cascade代表级联，表示删除模式下的一切数据库。restrict代表限制，表示如果该模式下有数据库对象（表，视图等）就无法删除模式。

• 例：

drop shcema Test cascade;
该语句删除了模式Test和模式中已经定义的表TAB1

1.2表的定义、删除与修改

1.2.1定义表：

create table <表名> (<列名> <数据类型> [完整性约束条件], <列名> <数据类型> [完整性约束条件], [<表级完整性约束条件>]);

• 例学生表student，课程表course和学生选课表sc。

create table student (sno char(9) primary key,
sname char(20) unique,
ssex char(2),
sage smallint,
sdept char(20));
create table course (cno char(4) primary key,
cname char(40) not null,
cpno char(4), //cpno的含义是先修课
ccredit smallint,
foreign key(cpno) references course(cno)); //表级完整性约束条件，被参照表course， 被参照列cno

注：参照表和被参照表可以是同一张表

create table sc (sno char(9),
cno char(4),
grade smallint,
primary key (sno,cno), //主码由两个属性构成，必须作为表级完整性约束条件定义
foreign key (sno) references student (sno),
foreign key (cno) references course (cno));
数据类型

1.2.2修改表：

alter table <表名> add <新列名> <数据类型> [完整性约束条件]; alter table <表名> drop <列名> [cascade | restrict]; alter table <表名> alter column <列名> <数据类型>; alter table <表明> add <表级完整性约束> ;

• 例：向student表中添加入学时间列，数据类型为日期

alter table student add s_entrance date;

• 例：将年龄由字符型改为整型

alter table student alter column sage int;

• 例：增加课程名称必须取唯一值的约束条件

alter table course add unique(cname)

1.2.3删除表：

drop table <表名> [cascade | restrict]

restrict表示该表的删除不能被其他约束引用，不能有视图，不能有存储过程或函数等。否则不能删除该表。
casecde表示表的删除没有限制条件，相关的依赖对象都会被删除。
默认情况是restrict

1.3视图的定义与删除

1.3.1定义视图：

create view <视图名> [(<列名>)] as <子查询> [with check option];

• 例：建立信息系学生视图

create view is_student
as
select sno,sname,sage
from student
where sdept=’is’;
with check option;
加上with check option子句后，以后对该视图进行插入、修改和查询时关系数据库管理系统会自动加上sdept=’is’条件。

1.3.2删除视图

drop view <视图名> [cascade];

1.3.3查询视图

• 例：在信息系学生的视图中查找年龄小于20的学生

select sno,sage
from is_student
where sage<20;

• 例：在s_g视图查询平均成绩在90分以上的同学

select sno,avg(grade)
from sc
where avg(grade)>=90
group by sno;
上面的写法会报错，因为where子句中是不能以聚集函数作为表达式的，正确的语句是：
select sno,avg(grade)
from sc
group by sno
having avg(grade)>=90;

1.4索引的建立与删除

create [unique] [cluster] index <索引名> on <表名>(<列名>[<次序>]);

• 例：sc表按学号升序和课程号降序建唯一索引

create unique index scno on sc(sno asc,cno desc);

alter index <旧索引名> rename to <新索引名>; drop index <索引名>

2.数据查询

2.1单表查询

2.1.1查询表中若干列

（1）查询指定列

• 例：查询全体学生的学号和姓名

select sno,sname
from student;
（2）查询全部列

• 例：查询全体学生的详细记录

select *
from student;
（3）查询经过计算的值

• 例：查询全体学生姓名及其出生年份（假设当前为2020年）

select sname,2020-sage
from student;

2.1.2选择表中的若干元组

（1）消除取值重复的行
select distinct sno
from sc;
（2）查询满足条件的元组
select sname,sage
from student
where sage<20;

• 查询年龄在20-30岁之间的学生的姓名，系别和年龄

select sname,sdept,sage
from student
where sage between 20 and 30;

• 查询数学系（MA）和信息系（IS）学生的姓名和性别

select sname,sex
from student
where sdept in (‘MA’,’IS’);

• 查询名字中第二个字为“阳”的学生的姓名和学号

select sname,sno
from student
where sname like ‘_阳%’;

通配符%代表任意长度（长度可以为零）的字符串 通配符_代表任意单个字符

• 查询DB_Design课程的课程号和学分

select cno,credit
from course
where cname like ‘DB_Design’ esacpe ‘\’;

‘\’是换码字符，紧跟在其后的字符不再具有通配符的含义，转义为普通字符

2.1.3order by子句

• 同一系中学生按年龄排序

order by sdept,sage desc;

desc降序 asc升序

2.1.4聚集函数

count(*) 统计元组个数
count( [distinct | all] <列名>) 统计一列中值的个数
sum( [distinct | all] <列名>) 计算一列中值的总和
avg( [distinct | all] <列名>) 计算一列中值的平均值
max( [distinct | all] <列名>) 求一列值中的最大值
min( [distinct | all] <列名>) 求一列值中的最小值

2.1.5 group by子句

group by 子句将查询结果按某一列或多列的值分组，值相等的为一组。

• 求各个课程号及相应的选课人数

select cno,count(sno)
from sc
group by cno;
该语句对查询结果按cno的值分组，值相同的为一组，然后对每一组作用聚集函数count进行计算。
查询结果：

若分组后还要按条件筛选使用having而不能使用where

• 查询成绩大于90分的学生学号和平均成绩

错误：
select sno,avg(grade)
from sc
where avg(grade)>90
group by sno;
正确：
select sno,avg(grade)
from sc
group by sno
having avg(grade)>90;

2.2连接查询

2.2.1等值连接和非等值连接

查询条件连接运算符为‘=’时为等值连接，使用其他运算符为非等值连接。

• 例：查询每个学生及其选课情况

select student.,sc.
from student.sc
where student.sno=sc.sno

• 例：查询选修2号课程且成绩在90分以上的所有学生的学号和姓名

select student.sno,sname
from student,sc
where student.sno=sc.sno and cno=’2’ and sc.grade>90;

2.2.2自身连接

• 例：查询每门课的间接先修课（即先修课的先修课）

分析：在course表中有每门课的先修课信息，而没有先修课的先修课。将course去两个别名：first和second
select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno

2.2.4外连接

• 例：

select student.sno,sname,cno,grade
from student left outer join sc on (student.sno=sc.sno);

• 例：

select student.sno,sname,cno,grade
from student right outer join sc on (student.sno=sc.sno);

左外连接列出左边关系中的所有元组，右外连接列出右边关系中的所有元组。

2.3 嵌套查询

2.3.1带有In谓词的子查询

• 例：查询选修了课程名为“信息系统”的学生学号和姓名

select sno,sname
from student
where sno in
(select sno from sc where cno in
(select cno from course where cname=’信息系统’)
);

2.3.2带有比较运算符的子查询

• 例：找出每个学生超过他自己选修课程平均成绩的课程号

select sno,cno
from sc x
where grade>=(select avg(grade)
from sc y
where y.sno=x.sno);
子查询的查询条件依赖于父查询，是相关查询，否则为不相关子查询。

2.3.3any、all和exists

any 某个值
all 所有值
any和all必须同时使用比较运算符。
exists内层查询结果非空，则外层where子句返回真值，否则返回假值。
not exists内层查询结果为空，则外层where子句返回真值，否则返回假值。

• 例：查询所有选修了1号课的学生姓名

select sname
from student
where exists
(select *
from sc
where sno=student.sno and cno=’1’);

• 例：查询所有没有选修1号课的学生的姓名。

select sname
from student
where not exists
(select *
from sc
where sno=student.sno and cno=’1’);

2.3.4集合查询

并操作（UNION）、交操作（INTERSECT）、差操作（EXCEPT）

• 例：查询计算机科学系的学生及年龄不大于19岁的学生

select
from student
where sdept=’cs’
union
select
from student
where sage<=19;

• 例：查询计算机科学系中年龄不大于19岁的学生

select
from student
where sdept=’cs’
intersect
select
from student
where sage<=19;

• 例：查询计算机科学系中年龄大于19岁的学生

select
from student
where sdept=’cs’
except
select
from student
where sage<=19;

2.3.5基于派生表的查询

• 例：查询所有选修了1号课的学生姓名

slelect sname
from student,(select sno from sc where cno=’1’) as sc1
where student.sno=sc1.sno;

通过from子句生成派生表时as关键词可以省略，但是必须为派生表指定一个别名。

3.数据更新

3.1 插入数据

3.1.1插入元组

insret into <表名> [<属性列>] values(<常量>)

3.1.2插入子查询结果

insert into<表名>[<属性列>] 子查询；

• 例：对每一个系，求学生的平均年龄，并把结果存入新表

首先建一个新表：
create table dept_age
(sdept char(15)
avg_age smallint);
然后对student表按系分组求平均年龄，再把系名和平均年龄存入新表：
insert
into dept_age(sdept,avg_age)
select sdept,avg(age)
from student
group by sdept;

3.2 修改数据

update <表名>

set <列名>=<表达式> [where <条件>]

3.3 删除数据

delete from <表名> [where<条件>]