关系数据库标准语言SQL学习笔记

         定义基本表

修改基本表

删除基本表

数据查询

单表查询

连接查询

插入数据

修改数据

删除数据

---

        关系数据库标准语言SQL，集数据定义语言（DDL），数据操纵语言（DML），

数据控制语言（DCL）功能于一体。

     SQL功能极强，完成核心功能只用了9个动词。

 

定义基本表

CREATE TABLE <表名>

(<列名> <数据类型>[<列级完整性约束条件>]

[,<列名> <数据类型>[<列级完整性约束条件>]]

…

[,<表级完整性约束条件>]);

建立一个学生选课表SC

CREATE TABLE SC (

Sno CHAR(9),

Cno CHAR(4),

Grade SMALLINT，

PRIMARY KEY (Sno,Cno),

 /* 主码由两个属性构成，必须作为表级完整性进行定义*/

 FOREIGN KEY (Cno) REFERENCES Course(Cno)

/* 表级完整性约束条件，Cno是外码，被参照表是Course*/ );

修改基本表

ALTER TABLE <表名>

[ ADD [COLUMN] <新列名> <数据类型> [ 完整性约束] ]

[ ADD <表级完整性约束>]

[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ] [ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]

[ALTER COLUMN <列名><数据类型> ] ;

删除基本表

DROP TABLE <表名>［RESTRICT| CASCADE］;

RESTRICT和CASCADE的区别

RESTRICT：删除表是有限制的。 欲删除的基本表不能被其他表的约束所引用 如果存在依赖该表的对象，则此表不能被删除

CASCADE：删除该表没有限制。 在删除基本表的同时，相关的依赖对象一起删除

数据查询

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …

FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句) [AS]<别名> #linux下别名区分大小写

[ WHERE <条件表达式> ]

[ GROUP BY <列名1> [ HAVING <针对这些列的条件表达式> ] ]

[ ORDER BY <列名2> [ ASC|DESC ] ];

单表查询

检索教师表中所有工资少于1500元或者工资大于2000元 并且是03系的教师姓名?

Select Tname

From Teacher

Where Salary<1500 or Salary>2000 and D#='03 ;

ORDER BY子句

升序：ASC;降序：DESC;缺省值为升序

ORDER BY Grade DESC;

聚集函数

    聚集函数只能出现在select子句和group by中的having子句。不能用在where子句中。

查询学生总人数。

SELECT COUNT(*)

 FROM Student;

查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno)

 FROM SC;

 计算1号课程的学生平均成绩。

SELECT AVG(Grade)

 FROM SC

 WHERE Cno='1';

查询选修1号课程的学生最高分数。

SELECT MAX(Grade)

 FROM SC

 WHERE Cno=‘1’;

查询学生201215012选修课程的总学分数。

SELECT SUM(Ccredit)

 FROM  SC,Course

 WHERE Sno='201215012' AND SC.Cno=Course.Cno;

GROUP BY子句

 求每一个学生的平均成绩

Select S#,AVG(Score)
From sC
Group by S#:

上例是按学号进行分组，即学号相同的元组划到一个组中并求平均值

-分组过滤子句having

若要对集合(即分组)进行条件过滤，即满足条件的集合/分组留下，不满足条件的集合/分组剔除。

示例:求不及格课程超过两门的同学的学号

Select S# From SC

Where Score < 60

Group by S# Having Count(*)>2;

HAVING短语与WHERE子句的区别：

◼作用对象不同

• WHERE子句作用于基表或视图，对分组前的数据过滤，

不可含聚集函数。（用在groupby之前）

• HAVING短语作用于group by的组(选定的列)，对分组后

的数据过滤，可包含聚集函数。（用在groupby之后）

连接查询

Select 列名[[,列名]... ]

From 表名1,表名2,

Where 检索条件;

示例:按'数据库”课成绩由高到低顺序显示所有同学姓名(三表连接)

Select Sname From Student, SC, Course

Where Student.S#=sC.S# and Sc.C#=Course.C#

and Cname='数据库'

Order By Score DESC;

自然连接

等值连接里去掉目标列重复的属性列

自身连接

Select 列名 as 列别名[[,列名 as 列别名]... ]

From表名1  表别名1,表名2  表别名2,

Where 检索条件;

连接运算涉及到重名的问题，如两个表中的属性重名，连接的两个表重名(同一表的连接)等，因此需要使用别名以便区分。

上述定义中的as 可以省略。

示例:求有薪水差额的任意两位教师

Select T.Tname as Teacher1, T2.Tname as Teacher2

From Teacher T1, Teacher T2

Where T1.Salary> T2.Salary;

用连接查询查找和problem_id为 1026 的题目属于同一个比赛的题目信息，结果按problem_id升序排序。
SELECT DISTINCT problem.*
FROM problem,contest_problem A,contest_problem B
WHERE A.problem_id=1026 and B.contest_id=A.contest_id and B.problem_id=problem.problem_id
ORDER BY problem_id ASC

外连接

Select 列名 [[,列名]... ]

From 表名1 [NATURAL]

[INNER |{ LEFT |RIGHT |FULL} [OUTER]]

JOIN 表名2ON (连接条件|Using(Colname {, colname ...}))

[Where 检索条件]…;

多表连接

查询每个学生的学号、姓名、选修的课程名及成绩

SELECT Student.Sno, Sname, Cname, Grade

FROM Student,SC, Course /*多表连接*/

WHEREStudent.Sno = SC.Sno

ANDSC.Cno =Course.Cno;

#必须有等值连接条件，否则将出现笛卡尔积

SQL语句的执行顺序一般为：

from子句

where 子句

group by 子句

having 子句

order by 子句

select 子句

嵌套查询

一个SELECT-FROM-WHERE语句称为一个查询块

将一个查询块嵌套在另一个查询块的WHERE子句或 HAVING短语的条件中的查询称为嵌套查询

SELECT Sname                        /*外层查询/父查询*/

FROM Student

WHERE Sno IN

        ( SELECT Sno                /*内层查询/子查询*/

        FROM SC

        WHERE Cno= ' 2 ');

带有IN谓词的子查询

查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno,Sname
FROM    Student                         
WHERE Sno IN
    (SELECT Sno
    FROM    SC                         
    WHERE  Cno IN
      (SELECT Cno
      FROM Course           
      WHERE Cname= '信息系统'                      
    )
);


SELECT Sno,Sname
FROM    Student,SC,Course
WHERE Course.Cname= '信息系统 '  AND
      Course.Cno = SC.Cno AND
      SC.Sno = Student.Sno;

带有比较运算符的子查询

找出每个学生超过他选修课程平均成绩的课程号
SELECT Sno, Cno
 FROM    SC  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    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 ';

带有EXISTS谓词的子查询

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑 真值“true”或逻辑假值“false”。

若内层查询结果非空，则外层的WHERE子句返回真值

若内层查询结果为空，则外层的WHERE子句返回假值

NOT EXISTS返回结果相反

查询选修了全部课程的学生姓名
双重否定：没有一门课程没有选修的学生姓名
NOTEXISTS 1 (遍历所有课程)不存在（没选修）的一门课程(等价于上一句)
NOTEXISTS 2 不存在该生选修某课程（等价于没选修）
SELECT Sname
    FROM Student
    WHERE NOT EXISTS#“没有一门课程”
        (SELECT *
        FROM Course
        WHERE NOT EXISTS#”没有修”#遍历完Course表，全选修则为F
            (SELECT *
            FROM SC
            WHERE Sno=Student.Sno#外层循环代入
            AND Cno=Course.Cno) #内层循环
);

查询至少选修了学生201215122选修的全部课程的学生号码
SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
    (SELECT*
    FROM SC SCY
    WHERE SCY.Sno='201215122' AND NOT EXISTS
        (SELECT*
        FROM SC SCZ
        WHERE SCZ.Sno=SCX.Sno AND
            SCZ.Cno=SCY.Cno));

插入数据

INSERT INTO <表名> [(<属性列1>[,<属性列2 >…)]

VALUES (<常量1> [,<常量2>]… );

INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) 
VALUES ('201215128','陈冬','男','IS',18);

修改数据

Update 表名

Set 列名=表达式|(子查询)

[[，列名=表达式|(子查询)]...]

[Where 条件表达式】;>

如果Where条件省略，则更新所有的元组

将所有计算机系的教师工资上调10%

Update Teacher

Set Salary = Salary* 1.1

Where D# in

(Select D# From Dept Where Dname ='计算机’);

删除数据

DELETE     FROM     <表名>

 [WHERE <条件>]

删除计算机科学系所有学生的选课记录。

DELETE
FROM  SC
WHERE  Sno  IN
(SELECT Sno
FROM   Student
WHERE  Sdept= 'CS') ;