「数据库」 SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Create table Courses
( Cno  char(4), 
Cname char(40), 
Cpno char(4), 
Ccredit int, 
primary key (Cno) ,  -- 主键
foreign Key (Cpno) REFERENCES Courses(Cno) -- 外键
);

CREATE TABLE Student
(
Sno char(6), 
Sname char(10), 
Ssex  char(1), 
Sage  int, 
Sdept char(2), 
primary key (Sno)
); 

Create table SC
( Sno  char(11), 
Cno char(4), 
Grade  int, 
primary key (Sno , Cno ), 
foreign key(Sno ) references Students (Sno), 
foreign key (Cno ) references Courses (Cno )); 

-- 修改表：在学生表中添加字段Sclass，字符类型，长度20
    Alter table Students  add Sclass char(20)
-- 将年龄的数字类型由字符型改为整型
    Alter table Students alter column Sage int
-- 增加课程名称必须取唯一值的约束条件
    Alter table Courses add unique (Cname)

-- 删除表Students：
    -- Restrict: 表删除有限制条件，不能被其他表的约束所引用
    --  Cascade:把一个基本表的定义连同表上的所有记录、索引以及由此基本表导出的所有视图都删除，并释放相应的存储空间
    Drop table Students cascade

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- 用户可以通过指定别名来改变查询结果的列标题
-- NAME          BIRTH                 BIRTHDAY   DEPARTMENT
-- 李勇        Year of birth:           1999          cs
    select Sname NAME, ‘Year of birth’ BIRTH,
            2020-Sage BIRTHDAY, 
            islower(Sdept) DEPARTMENT
    from   Students;

-- 查询年龄在20岁以下的学生姓名及其年龄
    select   Sname, Sage
    from Students
    where Sage < 20;

-- 查询考试成绩有不及格的学生的学号(注意去重)
	select distinct sno 
	from SC
	where Grade<60

-- 查询年龄在20~23岁之间的学生姓名、所在系，年龄
-- 与between…and…相对的谓词是not between…and…
    select Sname, Sdept, Sage
    from Students
    where Sage between 20 and 23;

-- 查询信息系、数学系和计算机系学生的姓名和性别
-- 与in相对的谓词是Not in
    select Sname, Ssex
    from Students
    where Sdept in (‘IS’, ‘MA’, ‘CS’);

-- 查询所有姓刘的学生的姓名、学号和性别(not like 可以表示相反的意思)
    select Sname, Sno, Ssex
    from Students
    where Sname like ‘刘%’；

-- 查询姓欧阳且全名为三个汉字的学生姓名
    select Sname
    from Students
    where Sname like ‘欧阳__’;

-- 查询DB_Design课程号和学分(用escape表示转移了下划线)
    select Cno, Ccredit
    from Courses
    where Cname like ‘DB\_Design’ escape ‘\’;

-- 查询缺考学生的学号和课号(is 反义词是 is not ,null 不可以用等号)
    select Sno, Cno
    from SC
    where Grade is null;

-- 查询计算机系年龄在20岁以下的学生的姓名(and 可替换成 or ,and 优先级大于 or 可通过加括号来划定优先级 )
    select Sname
    from Students
    where Sdept=‘CS’  and Sage<20;

-- 查询选修了3号课程的学生学号和成绩，要求查询结果按成绩降序排列(升序是 asc)
    select Sno, Grade
    from SC
    where Cno=‘3’ 
    order by Grade desc;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- 参加集合操作的各查询结果的列数必须相同，对应项的数据类型也必须相同
-- 在union, intersect, except中
-- 缺省情况下：消除重复元组
-- 在union, intersect, 或except后加上all，保留重复元组
    select *
    from Students
    where Sdept=‘CS’
    Union -- 表示'或'
    -- intersect 表示'且'
    -- except  表示 '差'
    select *
    from Students
    where Sage <= 19;

-- 在 where 中使用比较运算符来作为连接两个表的条件
-- 但是可能会产生 两列内容一样的情况,可以手动修改 select 以去掉重复的列
    select Students.*, SC.*
    from Students, SC
    where Students.Sno = SC.Sno;

-- 查询租借过船只的水手姓名和租借的日期
    select Sailors.name, Reserve.date
    from Sailors, Reserve
    where Sailors.Sid = Reserve.Sid;

-- 查询每门课的课号及其间接先修课的课号
    select C1.Cno, C2.Cpno
    from Courses C1, Courses C2
    where C1.Cpno=C2.Cno; 

-- 外连接:在一般连接中,只有满足条件的元组才可以作为结果输出, 外连接则不同(左、右外连接)，把舍弃的元组保留在结果关系中，而在其他属性上填空值。
-- 内连接(是学生,也是雇员)
Students INNER JOIN Employee 
        on  ( Students.name = Employee.name )

-- 左外连接(是学生,可以不是雇员)
Students LEFT OUTER JOIN Employee

-- 全外连接(可以不是学生，可以不是雇员)
Students FULL OUTER JOIN Employee

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- 一个查询块可以放在另一个查询块的where子句或having短语的条件中
-- 甚至是SELECT子句和FROM子句中

-- 不相关子查询求解方法由里向外
-- 相关子查询求解方法由外向里

-- 使用 in 来链接(适合子查询结果是多值)(感觉这样提高了可读性)
-- 不知道这个 in 有没有反义词
    select *
    from Students
    where Sdept in (select Sdept
                    from Students
                    where Sname=‘刘晨’)；
-- 和上文等价
    select S2.*
    from Students S1, Students S2,
    where S1.Sname = ‘刘晨’ and
          S1.Sdept = S2.Sdept;

-- 使用比较运算符,要求子查询结果是单值
    select *
    from Students
    where Sage =   (select Sage 
                    from Students
                    where Sno=‘20140001’);

-- 带有 any 或 all 的子查询必须和比较运算符同用
-- 查询比信息系某一学生年龄小的其他系学生姓名和年龄
    select Sname, Sage
    from Students
    where Sage <any (select Sage
                    from Students
                    where Sdept=‘IS’)
                    and Sdept != ‘IS’ ;                 
-- 等价,使用集函数效率高
    select Sname, Sage
    from Students
    where Sage < (select max(Sage)
                  from Students
                  where Sdept=‘IS’)
          and Sdept!=‘IS’ ;                               

1
2
3
4
5
6
7
8
9
10
11
12
-- 相关子查询
-- exists 加上子查询 返回值是 true 或 false

-- 查询所有选修了1号课程的学生的姓名(没有选修的 写作 not exists )
-- 计算顺序是从外向内,Students会被内层的子查询使用
    select Sname
    from Students
    where exists  ( select *
                    from SC
                    where Sno=Students.Sno 
                          and     Cno=‘1’);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
count sum avg max min

-- 查找最年长的水手的年龄和名字
Select S.sname, S.age
From Sailors S
Where S.age=(select max(S2.age) from Sailors S2)

-- 查询选修了课程的学生人数
    select count (distinct Sno)
    from SC;

-- 平均值
-- 求选修1号课程的学生的平均成绩
    select avg(Grade)
    from SC
    where Cno=‘1’;

-- 分组
-- 将查询结果按一列或多列值分组, 目的是将集函数作用到组上
-- 求选课人数超过10人的课程号及其人数
    select Cno, count(Sno)
    from SC
    group by Cno
    having count(Sno)>10; -- 组内条件

-- 在18岁以上水手中,对于每个rating级别中最少有两个水手以上的组中最年轻水手的年龄
    SELECT S.rating, Min(S.age)
    FROM Sailors S
    WHERE S.age>18
    GROUP BY S.rating
    HAVING COUNT(*)>1

-- 修改
update   Students  set  Sage = 22 
update   Students  set  Sage = Sage + 1;