学习总结——基本SQL语句

一、增加

1. 创建表

/* 创建课程表*/
create table Course(
Cno char(4) PRIMARY KEY,
Cname varchar(40) not null,
Cpno char(4),
Ccredit smallint,
foreign key(Cpno) references 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)
);

2. 修改表

/* 学生表中添加入学时间*/
alter table STUDENT add S_enterance date;

/* 给课程名称添加 “唯一” 限制*/
alter table Course add unique(Cname);

/* 将原前定义的年龄类型改为 int*/
alter table STUDENT alter column Sage int;

/* 删除学生表（级联删除）*/
drop table STUDENT cascade;

3. 视图操作

/* 学生表上创建视图*/
create view IS_STUDENT
as 
select Sno,Sname,Sage
from STUDENT
where Sdept='IS';

/* 因为还有其他表依赖于学生表，所以此语句会删除失败*/
drop table STUDENT restrict;

/* 此语句是级联删除，可以强制删除*/
drop table STUDENT cascade;

/* 在视图上查询*/
select * from IS_STUDENT;

4. 索引操作

/* 建立索引*/
create unique index Stusno on STUDENT(Sno);
create unique index Coucno on course(Cno);
create unique index SCno on SC(Sno asc, Cno desc);

/* 修改索引名，（此方法错误）*/
alter index SCno rename to SCSno;
/* 此方法能修改索引名*/
exec sp_rename "SC.SCno", "SCSno", "index";

/* 删除索引*/
drop index Studsname;

二、查询

1. 简单查询

/* 查询学生表中的全部成员*/
select Sno, Sname from STUDENT;

/* 查询学生表中的全部成员*/
select Sname, Sno, Sdept from STUDENT;

/* 查看表中的所有数据*/
select * from STUDENT;

/* 列名可以进行基本运算，可以重命名*/
select Sname, 2022-Sage birth from STUDENT;

/* 可以添加字符串行，简单函数*/
select Sname, 'Year of birth' YearOfBirth, 2022-Sage, lower(Sdept) 
from STUDENT;

/* 选择表中的若干元组*/
select Sno from SC;

/* 使用唯一表示符选择元组*/
select distinct Sno from SC;

2. 条件查询

/* 条件选择*/
select Sname from STUDENT where Sdept='CS';
select Sname, Sage from STUDENT where Sage < 20;
select distinct Sno from SC where Grade < 60;

3. Between and 用法

/* betwwen and 用法*/
select Sname, Sage from STUDENT where Sage between 19 and 20;
select Sname, Sno from STUDENT where Sage not between 19 and 20;

4. In 用法

/* in 用法*/
select Sname, Sdept from STUDENT where Sdept in ('CS', 'IS');
select Sname, Sdept from STUDENT where Sdept not in ('CS', 'IS');

5. Like 用法

/* like 用法*/
select * from STUDENT where Sno like '201201';
select * from STUDENT where Sno = '201201';

select Sname, Sno, Sex from STUDENT where Sname like '刘%';
select Sname, Sno, Sex from STUDENT where Sname like '刘_';
select Sname from STUDENT where Sname like '__三';

/* 转义字符*/
select Cno, Cname from Course where Cname like 'DB\_%' escape '\'; 
select Cno, Cname from Course where Cname like 'DB_%';

6. Null 语句

/* null 语句*/
select Sno, Cno from SC where Grade is null;
select Sno, Cno from SC where Grade is not null;

7. and 和 or 语句

/* and 语句*/
select Sname, Sage from STUDENT where Sdept = 'CS' and Sage <= 20;

/* or 语句*/
select Sname, Sdept from STUDENT where Sdept = 'CS' or Sdept = 'MA';

8. order 语句

/* order 语句*/
select Sno, Grade from SC where Cno = '3' 
order by Grade desc; /* 建立索引时用到升降序*/

select Sno, Grade from SC where Cno = '3' order by Grade asc; 
select * from STUDENT order by Sdept, Sage desc;

9. 聚集函数使用

/*聚集函数*/
select count(*) from STUDENT;
select avg(Grade) from SC where Sno = '201222';
select sum(Grade) average from SC where SC.Sno = '201222';
select max(Grade) from SC;
select min(Grade) from SC;
select sum(Grade) from SC;

select count(distinct Sno) from SC;

select SUM(Ccredit) from SC,Course 
where SC.Sno = '201222' and SC.Cno = Course.Cno;

select * from SC,Course where SC.Cno = Course.Cno;

10. Group 用法

/* group 语句*/
select Cno, count(Sno) from SC group by Cno;
select Sno from SC group by Sno having count(*) > 2;
select Sno, avg(Grade) from SC group by Sno having avg(Grade) >= 90;

11. 多表连接

/* 自然连接*/
select * from STUDENT, SC where STUDENT.Sno = SC.Sno;

/* 自身连接*/
select first1.Cno, first1.Cname, second1.Cpno, second1.Cname 
from Course first1, Course second1 
where first1.Cpno = second1.Cno;

/*多表连接*/
select STUDENT.Sno, Sname, Cname, Grade 
from STUDENT, SC, Course 
where STUDENT.Sno = SC.Sno and SC.Cno = Course.Cno;

12. 嵌套查询

/* 嵌套查询*/
select Sname from STUDENT where Sno in (
select Sno from SC where Cno = '2');

/* 不相关查询*/
select Sno, Sname, Sdept from STUDENT where Sdept in (
select Sdept from STUDENT where Sname = '刘晨');

/* 相关子查询*/
select Sno, Sname from STUDENT where Sno in (
select Sno from SC where Cno in (
select Cno from Course where Cname = '信息系统'));

13. 带有比较用算符的查找

/* 带有比较运算符的子查询*/
select Sno, Sname, Sdept from STUDENT where Sdept = (
select Sdept from STUDENT where Sname = '刘晨');

select Sno, Cno from SC x where Grade >= (
select avg(Grade) from SC y where y.Sno = x.Sno);

14. 带有 any 、all 谓词的查询

/* 带有any、all 谓词的子查询*/
select Sname, Sage from STUDENT where Sage < any (
select Sage from STUDENT where Sdept = 'CS') and Sdept != 'CS';

select Sname, Sage from STUDENT where Sage < (
select max(Sage) from STUDENT where Sdept = 'CS') and Sdept != 'CS'

15. 带有 exists 谓词chaxun

/* 带有exists 谓词的子查询*/
select Sname from STUDENT where exists (
select * from SC where Sno = STUDENT.Sno and Cno = '1');

select Sname from STUDENT where not exists (
select * from SC where Sno = STUDENT.Sno and Cno = '1');

select Sno, Sname, Sdept from STUDENT S1 where exists (
select * from STUDENT S2 
where S2.Sdept = S1.Sdept and S2.Sname = '刘晨');

16. 存在两次改写全称量词、蕴含式

/* 存在量词写全称量词*/
select Sname from STUDENT where not exists (
select * from Course where not exists (
select * from SC where Sno = STUDENT.Sno and Cno = Course.Cno));

/* 存在量词改写蕴含式*/
select distinct Sno from SC SCX where not exists (
select * from SC SCY where SCY.Sno = '2012222' and not exists (
select * from SC SCZ where SCZ.Sno = SCX.Sno and SCZ.Cno = SCY.Cno));

17. 集合查询

/* 集合查询*/
select * from STUDENT where Sdept = 'CS' union 
select * from STUDENT where Sage <= 19;

/* 交集*/
select * from STUDENT where Sdept = 'CS' intersect 
select * from STUDENT where Sage <= 19;

/* 差集*/
select * from STUDENT where Sdept = 'CS' except 
select * from STUDENT where Sage <= 19;

select * from STUDENT where Sdept = 'CS' and Sage > 19;

18. 基于派生词查询

/* 基于派生词的查询（from 语句中写 select）*/
select Sname from STUDENT, (select Sno from SC where Cno = '1') 
as SC1 where STUDENT.Sno = SC1.Sno;

三、更新

1. 插入数据

/* 插入数据*/
insert into STUDENT (Sno, Sname, Sex, Sdept, Sage) 
values ('201225', '陈东', '男', 'CS', 18);

/* 不指定参数时要给出全部值*/
insert into SC values ('201222', '1', null);

/* 插入子查询*/
create table Dept_age(Sdept char(15), Avg_age smallint);

insert into Dept_age(Sdept, Avg_age) 
select Sdept, avg(Sage) from STUDENT group by Sdept;

2. 修改数据

/* 修改数据*/
update STUDENT set Sage = 22 where Sno = '201222';
update STUDENT set Sage = Sage + 1;

/* 带子查询的修改语句*/
update SC set Grade = 0 where Sno in (
select Sno from STUDENT where Sdept = 'CS');

四、删除

1. 删除一个元组

/* 删除一个元组*/
delete from STUDENT where Sno = '201202';
delete from STUDENT;
delete from SC where Sno in (
select Sno from STUDENT where Sdept = 'CS');

2. 空值的处理

/* 空值的处理*/
select Sno from SC where Cno = '1' and 
(Grade < 60 or Grade is null);

3. 建立视图

/* 建立视图，以后改动时会检查选项*/
create view IS_STUDENT as 
select Sno, Sname, Sage from STUDENT 
where Sdept = 'CS' with check option;

/* 建立视图*/
create view IS_S1(Sno, Sname, Grade) as 
select STUDENT.Sno, Sname, Grade from STUDENT, SC 
where Sdept = 'IS' and STUDENT.Sno = SC.Sno and SC.Cno = '1';

select * from IS_S1;