SQL基础知识总结

                  目录

一 数据控制语言（DCL）

二 数据定义语言（DDL）

三 数据操作语言（DML）

四 SQL工具

五 SQL函数

六 制定约束

---

一 数据控制语言（DCL）

可以被授出和撤销的对象包括数据库、表空间、表、视图、索引、包和模式。

1. GRANT语句

语法：

GRANT privilege ON object-type object-name
TO [{USER|GROUP|PUBLIC}] authorization-name
[WITH GRANT OPTION];

2. REVOKE语句

语法：

REVOKE privilege ON object-type object-name
FROM [{USER|GROUP|PUBLIC}] authorization-name;

如撤销Joanna在STAFF表上拥有的所有特权

REVOKE ALL PRIVILEGES ON TABLE staff FROM Joanna;

二 数据定义语言（DDL）

1.CREATE语句

用于创建数据库对象，包括：

缓冲池（Buffer pool）

事件监视器（Event monitor）

函数（Function）

索引（Index）

模式（Schema）

储存过程（Stored procedure）

表（Table）

表空间（Table space）

触发器（Trigger）

视图（View）

如创建一个5列的表，名字叫org

CREATE TABLE org(
  deptnumb SMALLINT NOT NULL,
  deptname VARCHAR(14),
  manager SMALLINT,
  location VARCHAR(13));

2.DECLARE

与CREATE不同的是，DECLARE所创建的是只能在数据库连接期间存在的临时表。声明过的表都可以被引用、修改和删除。表是唯一可以被声明的对象。

如声明一个临时表名为temp1，位于用户现有的一个名叫mytempspace的临时表空间中，表的列名与定义和employee一样

DECLARE GLOBAL TEMPORARY TABLE session.temp1
  LIKE employee
  ON COMMIT PRESERVE ROWS
  NOT LOGGED
  IN mytempspace;

3.ALTER

用来改变现有数据库对象的一些特性，包括缓冲池、模式、表、表空间、触发器和视图。

如在org表中新添加一列state

ALTER TABLE org
ADD state char(2);

4.DROP

删除任何SQL CREATE或DECLARE创建的对象

DROP TABLE 表;

三 数据操作语言（DML）

1.SELECT

用于检索表或视图数据

如检索SAMPLE数据库中所有的STAFF表的数据

SELECT * FROM staff;

• 限制结果集中的行数，使用FETCH FIRST

SELECT * FROM staff 
FETCH FIRST 10 ROWS ONLY;

• 检索特定的列

SELECT name,salary FROM staff;

• 起别名

SELECT name,salary + comm AS pay 
FROM staff;

2.INSERT

向表或视图中添加新行

INSERT INTO staff 
VALUES (1212,’Cerny’,20,’Sales’,3,90000,30000.00);

或者

INSERT INTO staff (id,name,dept,job,years,salary,comm)
     VALUES (1212,’Cerny’,20,’Sales’,3,90000,30000.00);

3.UPDATE

用于改变表或者视图中的数据，可以搭配WHERE。

UPDATE staff
  SET dept = 51,salary = 70000
  WHERE id = 750;

或者

UPDATE staff
  SET (dept,salary) = (51,70000)
  WHERE id = 750;

把staff表中id未750的dept，salary列的数据更新为51和70000。

4.DELETE

用于从表中删除整行数据，可搭配WHERE。

DELETE FROM staff
  WHERE id IN (1212,1213);

删除staff表中id为1212或1213的数据。

四 SQL工具

1.WHERE子句和谓词

使用WHERE子句指定若干选择条件或搜索条件在表或视图中选择某些特定行，搜索条件由一个或多个谓词组成。

如查找薪水超过20000的员工的名字

SELECT name,salary FROM staff
  WHERE salary > 20000;

如非经理人但薪水超过20000的员工的名字，职位和薪水

SELECT name,job,salary FROM staff
  WHERE job <>’Mgr’
  AND salary > 20000;

2.ORDER BY

ORDER BY子句可以根据一列或多列值对结果集进行排序，子句中的列名不一定要在选择的列表中指定。

SELECT name,salary FROM staff
  WHERE salary > 20000
  ORDER BY salary;

其中DESC表示降序，ASC表示升序，默认为升序。

3.连接

（1）连接

一个连接是把两个或者两个以上的表中数据组合在一起的查询。

SELECT  表1列名1，表1列名2，表2列名1，表2列名2
FROM 表1，表2;

返回的是两个表的交叉积，即如果表1有2列，表2有4列，则返回2*4=8列。

（2）内连接

从交叉积中返回满足连接条件的行。

SELECT  表1列名1，表1列名2，表2列名1，表2列名2
FROM 表1 INNER JOIN 表2
ON 表1列名 = 表2列名;

ON表示将要连接的表指定连接条件。

（3）外连接

左连接：LEFT JOIN，返回左边表的所有列名和右边表的匹配值，右边没有的则为NULL，

右连接：RIGHT JOIN，返回右边表的所有列的匹配值，左边表没有的则为NULL，

全连接：FULL JOIN，包括左表、右表中所有匹配的列名。

4.集合运算符

使用UNION、EXCEPT或INTERSECT把两个或两个以上的查询合并成一个查询。

SELECT 列FROM 表
  WHERE 条件
UNION
SELECT 列FROM 表
  WHERE 条件;

5.GROUP BY与HAVING

在结果集中可以使用GROUP BY子句组织行，在使用聚集函数时必须要有GROUP BY语句。聚集函数如SUM、AVG、COUNT。

SELECT 列1,SUM(列2)  FROM 表
  GROUP BY 列1;

通常HAVING子句和GROUP BY子句一起使用可以检索出只满足某个特定条件的组作为结果，HAVING子句可以包含一个或多个谓词。

SELECT 列1,SUM(列2)  FROM 表
  GROUP BY 列1
  HAVING SUM(列2) > 值;

6.连接与子查询的比较

如工作部门为计划部门（’PLANNING’）的所有员工的员工号（deptno）和姓（lastname）

子查询：

SELECT deptno,lastname
  FROM emp
  WHERE workdept IN
        (SELECT deptno
           FROM dept
           WHERE deptname=’PLANNING’);

连接：

SELECT deptno,lastname
  FROM emp,dept
  WHERE workdept = deptno
  AND deptname=’PLANNING’;

内连接：

SELECT deptno,lastname
  FROM emp
INNER JOIN dept
    ON workdept = deptno
WHERE deptname=’PLANNING’;

建议使用连接而不是子查询，子查询的运行效率低些。

7.关系除法

 两个表的关系除法操作返回一个表中的列值，这些列值与另一表中的每个相应行的列值匹配。

SELECT stuname
  FROM students
  WHERE NOT EXISTS
        (SELECT *
           FROM classes
           WHERE NOT EXISTS
             (SELECT *
                FROM scores
                WHERE scores.stuno=students.stuno
                AND scores.classno=classes.classno));

8.CASE表达式

CASE语句根据指定的表达式的值，从多条语句中选择一条来执行

SELECT creator,name
  CASE type
WHEN ‘T’ THEN ‘TABLE’
WHEN ‘G’ THEN ‘TEMP’
WHEN ‘V’ THEN ‘VIEW’
WHEN ‘A’ THEN ‘ALLAS’
         ELSE ‘OTHER’
  END
  FROM SYSIBM.SYSTABLES;

相当于

SELECT creator,name,’TABLE’
  FROM SYSIBM.SYSTABLES
  WHEN type=’T’
UNION ALL
SELECT creator,name,’VIEW’
  FROM SYSIBM.SYSTABLES
  WHEN type=’V’
UNION ALL
SELECT creator,name,’ALLAS’
  FROM SYSIBM.SYSTABLES
  WHEN type=’A’;

CASE表达式的另一个价值的用法是用来进行表的旋转。

五 SQL函数

1.列函数

AVG函数，求列的平均值，

COUNT函数，对列进行计数，

MAX函数，求列的最大值，

MIN函数，求列的最小值，

STDDEV函数，求列的标准均方差，

SUM函数，列的累加和，

2.标量函数

ABS，返回数的绝对值，

HEX，返回值的十六进制表示，

LENGTH，返回自变量中的字节数，

YEAR，抽取日期时间值的年份部分。

六 制定约束

约束是数据库管理程序实施的规则。

唯一约束：确保表中的关键字值是唯一的。检查对组成主关键字的列的任何更改，以确保唯一性。

参考完整性：在插入、更新和删除操作上实现参考约束。所有外部关键值的所有值都有效才是一个数据库的正常状态。

表检查约束：验证更改后的数据有无违反当创建或更改表时制定的条件。

触发器：定义要执行的一组操作，当对制定的表执行更新、删除或插入操作时要调用这组操作。

1.约束的一些基本操作

唯一关键字：是值在所有行中都不重复的一个列或一组列，可将唯一关键字定义为表的主关键字。当唯一关键字被一个外部关键字饮用时，也称为父关键字。

主关键字（主键）：属于表定义一部分的唯一关键字，每个表只能有一个主关键字。

外部关键字（外键）：是一个表中的某一列或一组列，引用同一个表或另一个表的唯一关键字或主关键字。外部关键字用于建立与唯一关键字或主关键字的关系，以便在所有表中实现参考完整性。

组合关键字：有多列的关键字。主关键字和外部关键字均可以是组合关键字。

父关键字：是一个参考约束的主关键字或唯一关键字。

父表：包含一个父关键字的表，该父关键字与同一个表或另一个表中的至少一个外部关键字相关。

父行：是父表中的一行，该表的父关键字值匹配一个从属表中的至少一个外部关键字值。

从属表：是包含一个或多个外部关键字的一个表。从属表也可以是父表。一个表可以是任意多个关系中的一个从属表。

从属行：是从属表中的一行，该表有一个与父关键字值相匹配的非NULL外部关键字值。该外部关键字值表示从属行对父行的引用。

2.唯一约束

使用PRIMARY KEY子句或者UNIQUE子句的CREATE TABLE 或ALTER TABLE语句定义唯一约束。

一个表可以有任意多个唯一约束，但只能定义一个唯一约束为表的主关键字。