标准SQL语句笔记

发表于 | 浏览 次

标准SQL语句

核心9词

数据查询：SELECT

数据定义：CREATE, DROP, ALTER

数据操纵：INSERT, UPDATE, DELETE

数据控制：GRANT, REVOKE

外模式（视图与部分基本表）、模式（基本表）、内模式（存储文件）

一、数据定义

操作表： CREATE TABLE, DROP TABLE, ALTER TABLE

操作视图：CREATE VIEW, DROP VIEW

操作索引：CREATE INDEX, DROP INDEX

定义基本表

1
2
3
4
CREATE TABLE <表名>
          (<列名><数据类型> [列级完整性约束条件]
           [，<列名> <数据类型> [列级完整性约束条件]...)
           [,<表级完整性约束条件>];
  • Character 字符串：

char(n), varchar(n), varchar(max), text

  • Binary 类型：

bit, binary(n), varbinary(n), varbinary(max), image

Number类型：

tinyint, smallint, int, bigint, decimal(p, s), numeric(p, s), smallmoney, money, float(n), real

  • Date类型：

datetime, datetime2, smalldatetime, date, time, datetimeoffset, timestamp

  • 其他数据类型：

sql_variant, uniquedentifier, xml, cursor(存储用于对数据库操作的指针应用), table

1
2
3
4
5
6
CREATE TABLE Student  
	       (  Sno CHAR(5) NOT NULL UNIQUE,
	         Sname CHAR(20), 
            Ssex CHAR(1), 
            Sage INT, 
            Sdept CHAR(15));

修改基本表

ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIFY子句用于修改原有的列定义。 

1
2
3
4
ALTER TABLE <表名>
	  [ADD <新列名><数据类型>[完整性约束]]
	  [DROP<完整性约束名><完整性约束名>]
	  [MODIFY<列名> <数据类型><数据类型>];
1
2
3
ALTER TABLE Student ADD Scome DATE;
ALTER TABLE student MODIFY Sage SMALLINT;
ALTER TABLE Student Drop UNIQUE(Sname);

删除基本表

基本表定义一旦删除，表中的数据、在此表上建立的索引都将自动被删除掉，而建立在此表上的视图虽仍然保留，但已无法引用。 

1
DROP TABLE<表名>

建立索引

目的：加快查询，在最常查询的列上建立聚簇索引以提高查询效率。

可以在基本表上建立1-多个索引。在一个基本表上最多只能建立一个聚簇索引。 对于经常更新的列不宜建立聚簇索引 。

索引可以建在表的一列或多列上。可在每个<列名>后面指定索引值的排列次序。ASC表示升序，DESC表示降序，缺省值为ASC。

UNIQUE表明建唯一性索引。表明此索引的每一个索引值只对应唯一的数据记录。

CLUSTER表示建聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引。

1
2
CREATE  [UNIQUE]  [CLUSTER] INDEX  <索引名>   
       ON <表名> (<列名>[<次序>] [,<列名>[<次序>]]...);
1
2
3
4
5
CREATE CLUSTER INDEX  Stusname ON  Student(sname)；
为学生．课程数据库中的Student，Couse，Sc三个表建立索引中Student表按学号升序建唯一索引，Couse按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。
CREATE  UNIQUE INDEX  Stusno ON student(Sno);
CREATE  UNIQUE INDEX  Coucno ON Couse(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

删除索引

1
DROP INDEX<索引名>;

二、数据查询

1
2
3
4
5
SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]…
         FROM <表名或视图名>[,<表名或视图名>] ... 
         [WHERE <条件表达式>] 
         [GROUP BY <列名1>[HAVING <条件表达式>]]
         [ORDER BY <列名2> [ASC|DESC]];

含义：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。

如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。

如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。

单表查询

1.查询指定列

2.查询全部列

3.消除取值重复的行（DISTINCT）

4.查询满足条件的元组

比较= > < >= <= != <> !> !<
确定范围 BETWEEN AND, NOT BETWEEN AND
确定集合IN, NOT IN
字符匹配LIKE, NOT LIKE
空值IS NULL, IS NOT NULL
多重条件AND, OR

  • LIKE的问题

谓词LIKE可用来进行字符串的匹配。其语法格式如下：
[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘<换码字符>’]
其含义是查找指定的属性列值与<匹配串>相匹配的元组。<匹配串>可以是一个完整的字符串，也可以含有通配符%和_。
%(百分号) 代表任意长度（长度可以为0）的字符串。
_(下横线) 代表任意单个字符。

如果用户要查询的匹配字符串本身就含有 % 或 _ ，这时就要使用 ESCAPE ‘<换码字符>’ 短语对通配符进行转义了。 

1
2
3
SELECT Cno, Ccredit 
    	FROM Course 
	     WHERE Cname LIKE ’DB\_Design’  ESCAPE ’\’

5.对查询的结果进行排序

6.使用集函数

1
2
3
4
5
6
7
8
9
COUNT([DISTINCT|ALL] *)   统计元组个数 
COUNT([DISTINCT|ALL] <列名>)   统计一列中值的个数 
SUM([DISTINCT|ALL] <列名>)  计算一列值的总和 
AVG([DISTINCT|ALL] <列名>)  计算一列值的平均值
MAX([DISTINCT|ALL] <列名>)  求一列值中的最大值 
MIN([DISTINCT|ALL] <列名>)   求一列值中的最小值

SELECT MAX(Grade)
FROM SC WHERE Cno='1';

7.对查询结果进行分组

GROUP BY子句可以将查询结果表的各行按一列或多列,将值相等的作为一组。 如果分组后还要求按一定的条件对这些组进行筛选条，最终只输出满足指定的组，则可以使用HAVING短语指定筛选条件。 

1
2
3
SELECT Sno
FROM SC
GROUP BY Sno  HAVING COUNT(*)＞3;

连接查询

涉及两个以上的表 ，包括等值连接、非等值连接查询、自身连接查询（自身连接）、外连接查询（外连接）和复合条件连接查询（复合条件连接）。

1.等值连接与非等值连接

比较运算符主要有：=、>、<、>=、<=、!=<=、!= 

1
2
3
4
5
6
7
[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> 
[<表名1>.]<列名1> 
BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3> 

SELECT Student.*, SC.* 
FROM Student, SC 
WHERE Student.Sno=SC.Sno;

2.自身连接

连接操作不仅可以在两个表之间进行，也可以是一个表与其自己进行连接。

1
2
3
SELECT FIRST.Cno,  SECOND. Pcno 
FROM  Course   FIRST,  Course   SECOND 
WHERE   FIRST.Pcno=SECOND.Cno;

3.外连接

通常的连接操作中，只有满足连接条件的元组才能作为结果输出。有时我们想以 Student 表为主体列出每个学生的基本情况及其选课情况，若某个学生没有选课，则只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接（Outer Join）。外连接的运算符通常为 *。有的关系数据库中也用+。 

1
2
3
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade 
FROM Student, SC 
WHERE Student.Sno=SC.Sno(*);

嵌套查询

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

子查询的SELECT语句中不能使用ORDER BY于句，ORDER BY子句只能对最终查询结果排序。

嵌套查询一般由里向外处理。

1
2
3
4
SELECT Sname 
FROM Student
WHERE Sno IN  
SELECT  Sno  FROM  SC WHERE Cno＝’2’;

1.带有IN谓词的子查询

1
2
3
4
5
6
7
查询与刘晨在同一个系学习的学生
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept IN
    (SELECT Sdept
    FROM Student
    WHERE Sname='刘晨')
1
2
3
4
5
6
7
8
9
10
查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno, Sname
FROM Student
WHERE Sno IN
    (SELECT Sno
    FROM SC
    WHERE Cno IN
        (SELECT Cno
        FROM Course
        WHERE Cname='信息系统'))

2.带有比较运算符的子查询

当用户能确切知道内层查询返回的是单值时，可以用>,<,=,>=,<=,<>等比较运算符。 

1
2
3
4
5
6
7
查询与刘晨在同一个系学习的学生
SELECT Sno, Sname, Sdept
FROM Student
WHERE Sdept =
    (SELECT Sdept
    FROM Student
    WHERE Sname='刘晨')

3.带有ANY或ALL谓词的子查询

使用ANY或ALL谓词时则必须同时使用比较运算符。>=ANY >=ALL <=ANY <=ALL =ANY =ALL !=(<>)ANY !=(<>)ALL

可以与集函数进行对应。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
查询其他系中比信息系某一学生年龄小的学生姓名和年龄
SELECT Sname, Sage
From Student
WHERE Sage<ANY(SELECT Sage
            FROM Student
            WHERE Sdept='IS')
		AND Sdept<>'IS'

SELECT Sname, Sage
From Student
WHERE Sage<(SELECT MAX(Sage)
            FROM Student
            WHERE Sdept='IS')
		AND Sdept<>'IS'

三、数据操纵

插入语句

SQL的数据插入语句Insert通常有两种形式。一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多个元组。

1
2
3
4
5
6
7
INSERT
INTO 表名[（列名1 [,列名2,…]）]
VALUES (常量1[,常量2,])；

INSERT
INTO 表名 (列1,[列2,…])
       子查询;

nInto子句中没有出现的属性列，新记录在这些列上将取空值。在表定义时说明了NOT NULL的属性列不能取空值。如果INTO子句没有指明任何列名，则新插入的记录必须在每个属性列上均有值。

修改语句

功能：修改指定表中满足WHERE子句条件的元组。 其中SET子句给出表达式的值用于取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。 

1
2
3
UPDATE 表名，
SET 列名＝表达式[，列名＝表达式]
         [WHERE<条件>];

1.修改某个元素的值

1
UPDATE  Student SET Sage=22 WHERE Sno='95001';

2.修改多个元素的值

1
UPDATE  Student SET Sage=Sage+1;

3.带子查询的修改语句

使用IN条件来构造子句

1
2
3
Update sc
Set grade=0
Where exists (select * from student where student.sno=sc.sno and dept='CS')
1
2
3
4
5
6
UPDATE SC
SET GRADE=0
WHERE 'CS'=
        (SELECT Sdept 
        FROM Student
        WHERE Student.Sno=SC.Sno)

删除数据

从指定表中删除满足WHERE子句条件的所有元组，如果省略WHERE子句，表示删除表中全部元组，但表的定义仍在字典。也就是说，DELETE语句删除的是表中的数据，而不是关于表的定义。 

1
2
DELETE  FROM <表名>
[WHERE<条件>];

1.删除某一个元组的值

1
2
3
DELETE
FROM Student
WHERE Sno='95019';

2.删除多个元组的值

1
2
DELETE
FRDM SC;

3.带子查询的删除语句

1
2
3
4
5
6
DELETE
FROM SC
WHERE 'CS'=
        (SELECT Sdept
        From Student
        WHERE Student.Sno=SC.Sno)

更新操作与数据库的一致性

利用事务(Transaction)的功能实现。

四、视图

视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。

视图是从一个或几个基本表(或视图)导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。

视图一经定义，就可以和基本表一样被查询、被删除，我们也可以在一个视图之上再定义新的视图，但对视图的更新(增加、删除、修改)操作则有一定的限制。

定义视图

1.建立视图

其中子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDER BY子句和DISTINCT短语。

WITH CHECK OPTION表示对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。 

1
2
3
CREATE  VIEW  <视图名>[（<列名>,[<列名>,]…）
AS  <子查询>,
[WITH  CHECK  OPTION]；
1
2
3
4
5
6
7
8
9
10
11
12
13
建立信息系学生的视图
CREATE VIEW IS_Student
    AS
    SELECT Sno, Sname, Sage
    FROM Student
    WHERE Sdept='IS';
建立信息系学生的视图，并要求进修改和插入操作时仍需保证该视图只有信息系学生
CREATE VIEW IS_Student
    AS
    SELECT Sno, Sname, Sage
    FROM Student
    WHERE Sdept='IS'
    WITH CHECK OPTION;

DBMS执行CREATE VIEW语句的结果只是把视图的定义存入数据字典，并不执行其中的SELECT语句。只是在对视图查询时，才按照视图的定义从基本表中将数据查出。

2.删除视图

1
DROP  VIEW  <视图名>;

视图删除后视图的定义将从数据字典中删除。但是由该视图导出的其他视图定义仍在数据字典中，不过该视图已失效。

查询视图

与查询表一致

更新视图

由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。

为防止用户通过视图对数据进行增加、删除、修改时，有意无意地对不属于视图范围内的基本表数据进行操作，可在定义视图时加上WITH CHECK OPTION子句。这样在视图上增删改数据时，DbMS会检查视图定义中的条件，若不满足条件，则拒绝执行该操作。

五、数据控制

SQL中数据控制功能包括事务管理功能和数据保护功能，即数据库的恢复、并发控制；数据库的安全性和完整性控制。 重点说明安全性控制功能。

DBMS必须具有以下功能：

(1) 把授权的决定告知系统，这是由SQL的GRANT和REVOKE语句来完成的。
(2) 把授权的结果存入数据字典。
(3) 当用户提出操作请求时，根据授权情况进行检查，以决定是否执行操作请求。

授权

1
2
3
4
5
将对指定操作对象的指定操作权限授予指定的用户。
GRANT<权限>，[<权限>…
        [ON<对象类型><对象名>]
        TO <用户>[,<用户>]…
        [WITH GRANT OPTION];

收回权限

1
2
3
REVOKE<权限>[,<权限>]…
         [ON<对象类型><对象名>]
         FROM＜用户＞[,＜用户>＝;

其他常用命令（未完待续）

直接清空表 truncate table