目录
一 数据控制语言(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语句定义唯一约束。
一个表可以有任意多个唯一约束,但只能定义一个唯一约束为表的主关键字。