SQL语言基础

SQL:Structured Query Language,1974年Boyce和Chamberlin提出.
数据定义 :CREATE,DROP
数据库查询:SELECT
数据操纵 :INSERT,UPDATE,DELETE
数据控制 :GRANT,REVOKE

一.数据的定义和修改:
1.定义基表
CREATE TABLE 表名 (<列名1 类型[NOT NULL][,列名2 类型[NOT NULL]>…[其他参数]);
关于类型:
INTEGER 全字长的二进制整数.
SMALLINT 半字长的二进制整数.
DECIMAL(p[,q])压缩十进制数,共p位小数点后有q位,15>=p>=q>=0,q=0时可省略.
FLOAT 双字长的浮点数.
CHAR(n) 长度为n的定长字符串.
VARCHAR(n)变长字符串,最大长为n.
2.修改基表
ALTER TABLE 表名 ADD 列名 类型; //增加新列.
3.删除基表
DROP TABLE 表名; //删除基表连同表中记录.
二.视图的定义
1.定义视图
CREATE VIEW 视图名[列名[,列名]…]AS 子查询[WITH CHECK OPTION];
2.视图删除
DROP VIEW 视图名;
三.数据操纵
SELECT [DISTINCT] 选择输出属性表
FROM 要检索的表名
[WHERE 检索应满足的条件]
[GROUP BY 分组属性表(HAVING 检索条件)]
[ORDER BY 排序属性表];
1. 简单查询
(1).不消除重复元组的检索
SELECT TITLE FROM BOOKS
(2).消除重复元组的检索
SELECT DISTINCT TITLE FROM BOOKS
(3).使用”*”全部检索
SELECT * FROM BOOKS
(4).带表达式的检索
SELECT TITLE , PRICE*3 FROM BOOKS WHERE TITLE=’操作系统’
(5).要求排序的检索
SELECT CARD-NO,TITLE,DATA FROM BOOKS
WHERE DATA<1998.7.1 ORDER BY CARD-NO ASCDESC //升或降
2. 含有各种谓词的检索
COMPARISON, IN, LIKE, NULL, QUANTIFIED, EXISTS, BETWEEN
(1).BETWEEN
SELECT TITLE,SORT-NO,LO-NO FROM BOOKS WHERE LO-NO
BETWEEN 4 AND 5等价于
SELECT TITLE,SORT-NO,LO-NO FROM BOOKS WHERE LO-NO
>=4 AND LO-NO<=5
(2).IN 同上例:
SELECT TITLE,SORT-NO,LO-NO FROM BOOKS WHERE LO-NO
IN(4,5) 此例还可写成:
SELECT TITLE,SORT-NO,LO-NO FROM BOOKS WHERE LO-NO
=4 OR LO-NO=5
(3).LIKE:找出姓张的情况
SELECT NAME,CARD-NO,DEPT FROM BOOKS WHERE NAME
LIKE’张%’ //属性名类型一定为CHAR;短线“-”表示此位为一个字符;
“%”表示任意字符串(含空串);其它字符代表自身.
3. 联接查询
(1).等值联接
SELECT BORROWERS.*,LOANS.CARD-NO,LOANS.TITLE
FROM BORROWERS,LOANS
WHERE BORROWERS.CARD-NO=LOANS.CARD-NO
(2).自然联接
(3).带有附加条件的联接查询
SELECT BORROW.ERS*,LOANS.DATE,TITLE
FROM BORROWERS,LOANS
WHERE BORROWERS.CARD-NO=LOANS.CARD-NO
AND LOANS.TITLE=’操作系统’
(4).三个关系的联接
SELECT TITLE,AUTHOR
FROM BORROWERS,LOANS,BOOKS
WHERE NAME=’张平’
4. 复杂检索
(1).简单子查询
SELECT TITLE
FROM LOANS
WHERE LOANS.CARD-NO IN
(SELECT BORROWERS.CARD-NO
FROM BORROWERS
WHERE NAME=’张平’)
(2).多层嵌套子查询
SELECT NAME,DEPT
FROM BORROWERS
WHERE CARD-NO IN
(SELECT CARD-NO IN
FROM LOANS
WHERE BOOKS-NO IN
(SELECT BOOK-NO
FROM BOOKS
WHERE AUTHOR=’王生’))
(3).相关子查询
SELECT NAME FROM BORROWERS
WHERE ‘操作系统’ IN
(SELECT TITLE
FROM LOANS
WHERE CARD-NO=BORROWERS.CARD-NO)
(4).子查询和外层查询引用相同的表
SELECT CARD-NO
FROM LOANS
WHERE TITLE IN
(SELECT TITLE
FROM LOANS
WHERE CARD-NO=980001)
(5).相关子查询和外层查询引用相同的表
SELECT DISTINCT LX.TITLE
FROM LOANS LX
WHERE LX.TITLE IN
(SELECT LY.TITLE
FROM LOANS LY
WHERE LY.CARD-NO=LX.CARD-NO)
(6).使用存在量词的查询
SELECT NAME
FROM BORROWERS BX
WHERE EXISTS
(SELECT *
FROM LOANS
WHERE CARD-NO=BX.CARD-NO
AND TITLE=’操作系统’)
此例同于:
SELECT NAME
FORM BORROWERS
WHERE CARD-NO IN
(SELECT CARD-NO
FROM LOANS
WHERE TITLE=’操作系统’)
(7).使用限量谓词的查询(如:ALL, SOME, ANY)
SELECT NAME
FROM BORROWERS
WHERE CARD-NO<>ALL
(SELECT CARD-NO
FROM LOANS) //表示未借图书人姓名,如CARD-NO=SOME表示借书人姓名.
5. 集函数
COUNT 统计某属性上所有值的个数
SUM 统计指定元组集中某属性上所有值的总和
AVG 统计某属性上所有值的平均值
MAX 统计某属性上所有值的最大值
MIN 统计某属性上所有值的最小值
(1).在SELECT子句中使用集函数
SELECT COUNT(CARD-NO) FORM BORROWERS //求借书人总数
SELECT COUNT(DISTINCT CARD-NO) FROM LOANS //求借了图书的总人数
SELECT SUM(PRICE) FROM BOOKS //求所有图书和总价
SELECT MAX(PRICE) FROM BOOKS //求出最高图书价
SELECT MIN(DATA) FROM LOANS //求借书时间最短的借书日期
(2).在子查询中使用集函数
SELECT TITLE
FORM BOOKS
WHERE PRICE<(SELECT MAX (PRICE)
FROM BOOKS)
AND PRICE>
　　　　　　　　　　　（SELECT MIN(PRICE)
FROM BOOKS)
6. 分组子GROUP BY 的使用
SELECT CARD-NO TITLE
FROM LOANS
GROUP BY CARD-NO
7. HAVING子句的使用
SELECT CARD-NO
FROM LOANS
GROUP BY CARD-NO
HAVING TITLE IN
(SELECT TITLE
FROM LOANS
WHERE CARD-NO=980001)
//HAVING 与 WHERE子句类似，但WHERE后面是对一个元组的限制条件，而HAVING后面是一组元组的限制条件.
8. 更新操作(INSERT, DELETE, UPDATE)
(1).插入语句INSERT
INSERT INTO 关系名[(属性名[,属性名]…)] VALUES(常数[,常数]…) 如：
INSERT INTO BORROWERS（NAME，CARD-NO，DEPT） VALUES（‘李林’，‘980001’，‘计算机系’）
INSERT INTO BORROWERS VALUES（‘李林’，‘980001’，‘计算机系’）
INSERT INTO 关系名 SELECT * FROM 关系名变量名 //插入一批元组
(2).删除语句DELETE
DELETE FROM 关系名 [WHERE 检索条件] 如:
DELETE FROM BORROWERS WHERE NAME=’张平’
DELETE FROM LOANS WHERE DATE<98.7.1
DELETE FROM LOANS WHERE CARD-NO IN
(SELECT CARD-NO
FROM BORROWERS
WHERE NAME=’张平’)
(3).修改语句UPDATE
UPDATE 关系名
SET 属性名=表达式[,属性名=表达式]…
[WHERE 检索条件] 如:
UPDATE BORROWERS SET DEPT=’计算机系’ WHERE NAME=’王三元’
UPDATE LOANS SET LO-NO=3 WHERE LO-NO=5 AND LIKE SORT-NO ‘TP%’
UPDATE LOANS
SET CARD-NO=CARD-NO+100000
WHERE CARD-NO IN
(SELECT CARD-NO
FROM BORROWERS
WHERE DEPT=’计算机系’)
9. 视图的操作
四.SQL数据的安全与授权
GRANT 权力[,权力]…[ON 对象类型 对象名] TO 用户[,用户]…[WITH GRANT OPTION];
REVOKE 权力[,权力]…[ON 对象类型 对象名] FROM 用户[,用户]… //撤消权力.
1.GRANT DELETE ,SELECT ON TABLE BORROWERS TO WANGLING
2.GRANT ALL PRIVILIGES ON TABLE BORROWERS,LOANS,BOOKS TO ZHANGPIN,YANGYON
3.GRANT SELECT ON TABLE BOOKS TO PUBLIC
4.GRANT CREATE ON DATABASE BOOKMANGE TO LIMING
5.GRANT SELECT ON TABLE BOOKS TO U1 WITH GRANT OPTION
6.REVOKE DELETE ON TABLE BORROWERS FROM ZHANGPING
7.REVOKE SELECT ON TABLE BOOKS FROM U1.