数据库sql期末整理

数据库期末整理——sql语句

关系数据库标准语言SQL

数据定义

模式

创建模式

create schema  S-T authorization ycb;

删除模式

drop schema S-T cascade;

cascade是级联，表示同时把该模式中所有数据库对象全部删除

基本表

创建基本表

create table Student
	(
        Sno CHAR(9) PRIMARY KEY,
        Sname CHAR(20) UNIQUE,
        Sex  CHAR(2)
        Sage SMALLINT
        Sdept CHAR(20)
    );

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)
    );

修改基本表(alter table)

增加属性

alter table Student add S_entrance DATE;

修改属性

alter table Student alter column Sage INT;

删除属性

alter table Student drop Sage CASCADE;

删除基本表

drop table  CASCADE;

索引

建立索引

按学号升序建唯一索引

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;

删除索引

drop index Stusname;

数据查询

一般格式(当板子记住)

select [all|distinct] <目标列表达式>…

from <表名或视图名> [,<表名或视图名>…] | (<select语句>) [AS] <别名>

[where <条件表达式>]

[group by <列名1> [HAVING<条件表达式>] ]

[order by <列名2> [ASC|DESC]]

这种形式实际是后面的基于派生表查询

select * 
from ( select C,D from S )AS K,T;

别名

select Sname AS NAME, 'Year of Birth:' AS BIRTH,2014-Sage AS BIRTHDAY,LOWER(Sdept) DEPARTMENT
from Student;

消除重复元组

select distinct sno
from SC;

where子句常用的查询条件

确定集合

select Sname
from Student
where Sdept NOT IN ('CS','MA','IS');

字符匹配

%代表任意长度(长度可以为0的字符串)

select *
from Student
where Sno LIKE '刘%';

_代表任意单个字符

select Sname
from Student
where Sname LIKE '欧阳_';

转义操作

select Cno,Ccredit
from Course
where Cname LIKE 'DB \_Design' ESCAPE'\';

涉及空值的查询

select SNO,CNO
from SC
where Grade IS NULL;

select SNO,CNO
from SC
where Grade is NOT NULL;

group by子句

对查询结果按一个或多个属性升序(ASC)或降序(DESC),默认升序

分组后聚集函数将作用于每一个组，即每一个组都有一个函数值

查询各个课程号及相应的选课人数

select Cno,Count(Sno)
from SC
group by cno;

执行过程：先对查询结果按CNO相同的值分组，所有具有相同组的元组为一组，然后对每组作用聚集函数COUNT进行计算，以求得该组的学生人数

聚集函数

主要有count,sum,avg,max,min

select avg(grade)
from SC
where Cno='1';

select max(grade)
from SC
where Cno='1';

Having子句

因为where子句中不能作为条件表达式，不能使用聚集函数，所以有了一个Having子句，就是在后面跟聚集函数用的。用来处理分组后还要求按一定的条件筛选组。

select cno
from sc
group by sno
having count(*)>3;

select sno,avg(grade)
from sc
group by sno
having avg(grade)>=90;

连接查询

等值和非等值连接查询

select Student.*,SC.*
from Student,SC
where Student.Sno=SC.SNO;

自身连接

比如一张表上要查两个彼此有关系的属性，一张表上有先行课，想要查先行课的先行课的时候就要把这个表弄成两份

select FIRST.CNO,SECOND.CNO
from Course As FIRST,Course As Second
where FIRST.CNO=SECOND.CNO;

外连接

select Student.Sno,Sname,Ssexm,Sage,Sdept,Cno,Grade
from Student LEFT OUTER JOIN SC ON (Student.SNO=SC.SNO);

多表连接

select Student.Sno,Sname,Cname,Grade
from Student,SC,COURSE
where Student.Sno=SC.Sno AND SC.CNO=COURSE.CNO;

嵌套查询

带IN谓词的子查询

查询与刘晨在一个系学习的学生

select Sno,Sname,Sdept
from Student
where Sdept in
(
	select Sdept
    from Student
    where Sname='刘晨'
);

等价于使用自连接的方式

select * 
from Student as S1,Student as S2
where S1.dept=S2.dept and S1.name='刘晨';

查询选修了课程名为’信息系统’的学生学号和姓名

select Sno,Sname
from Student
where Sno IN(
	select Sno
    from SC
    where Cno in
    (
    	select CNO
        from Course
        where Cname='信息系统'
    )
);

这种方式等价于

select Student.Sno,Sname
from Student,SC,Course
where Student.Sno=SC.SNO AND
	  SC.CNO=Course.Cno AND
	  Course.Cname ='信息系统';

带比较运算符的子查询

可以使用>,<,=,>=,<=,!=或< >等比较运算符

由于一个学生只可能在一个系学习，也就说内查询的结果是一个值，因此这里可以用=代替IN

select Sno,Sname,Sdept
from Student
where Sdept = (
	select Sdept
    from Student
    where Sname='刘晨'
);

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

select Cno
from SC as x
where grade >=(
	select avg(grade)
    from sc y
    where y.sno=x.sno
);

带有ANY（SOME）或ALL的子查询

统计非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

select Sname,Sage
from Student
where Sage < ANY(
	select Sage
    from Studnet
    where Sdept='CS'
);

带EXISTS的子查询

EXISTS代表存在，返回数据为True/False

查询所有选修了1号课程的学生姓名

select Sname
from Student
where EXISTS
(
	Select *
    from SC
    where SNO=Student.SNO AND CNO='1'
);

集合查询

包括UNION,交INTERSECT和差操作EXCEPT

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

select *
from Student
where Sdept='CS'
UNION
Select *
from Student
Where Sage <=19;

该查询实际上是求计算机科学系的所有学生与年龄不大于19岁的学生的并集

基于派生表的查询

子查询不仅可以出现在where子句中,还可以出现在From子句中，这时的子查询生成的临时派生表成为主查询的查询对象。

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

select Sno,Cno
from SC,(select Sno,Avg(Grade) from SC Group by Sno) AS Avg_sc(avg_sno,avg_grade)
where SC.Sno=Avg_sc.avg_sno and SC.Grade>=Avg_sc.avg_grade;

数据更新

数据插入

插入元组数据

insert into Student(Sno,Sname,Ssex,Sdept,Sage)
values('201215128','陈冬','男','IS',18);

字符串常数要用单引号括起来

插入子查询结果数据

insert 
into Dept_age(Sdept,Avg_age)
select Sdept,AVG(Sage)
from Student
Group By Sdept;

修改数据

修改某一个元组的值

将学生201215121的年龄改为22岁

UPDATE Student
Set Sage=22
where Sno='201215121';

修改多个元组的值

UPDATE Student
Set Sage=Sage+1;

带子查询的修改语句

UPDATE SC
SET Grade =0
where SNO IN
(
	select Sno
    from Student
    where Sdept='CS'
);

删除数据

删除某一个元组的值

delete 
from student 
where Sno='201215128';

删除多个元组的值

delete 
from SC;

带子查询的删除语句

delete 
from SC
where sno in
(
	select Sno
    from Student
    where Sdept='CS'
);

空值的处理

• 空值理解成unknown
• 逻辑值有true，false，unknown
  • unknown OR true = true
  • unknown AND false = false
  • NoT unknown = unknown

视图

视图的建立

create view BT_S(Sno,Sname,Sbirth)
AS
select Snno,Sname,2014-Sage
from Student

视图的删除

drop view BT_S [CASCADE]

视图的查询

当成和表上的一样即可

select *
from S_G
where Gavg>=90

视图的更新

UPDATE IS_Student
SET Sname='刘辰'
Where Sno='201215211'