SQL

SQL查询语言概览

SQL数据定义

基本类型

表定义

create table 命令的通用形式

CREATE TABLE r
(
    A1 D1,
    A2 D2,
    AN DN,
    <完整性约束1>,
    <完整性约束K>
);

示例:

CREATE TABLE department(
    dept_name VARCHAR(20) NOT NULL,
    building VARCHAR(15), -- 如果不指定 默认为NULL
    budget NUMERIC(12,2) DEFAULT 999, -- 指定默认值
    PRIMARY KEY(dept_name)
);

表更新

DBMS对表更新的约束既复杂又不统一

ALTER TABLE vendors ADD vend_phone CHAR(20); -- 增加字段
ALTER TABLE vendors DROP COLUMN vend_phone; -- 删除字段

SQLite不支持使用ALTER TABLE 语句定义主键跟外键。在使用ALTER TABLE语句应极为小心,使用前应对表进行备份

删除表

DROP TABLE cust_copy;

重命名表

RENAME/sp_rename/ALTER TABLE

完整性约束

SQL查询

SELECT prod_name FROM products; -- 查询单列
SELECT prod_name, prod_price FROM products; -- 查询多列
SELECT * FROM products; -- 检索所有列
SELECT DISTINCT vend_id FROM products; -- 结果去重(DISTINCT作用在整行上,不针对某个具体列)

SQL关键字不区分大小写,但对象名区分,在不同的DBMS跟操作系统区分大小写可能会有不同。

*通配符通常会降低检索性能,但好处就是能匹配未知列。

分页

不同的数据库分页不尽相同

SELECT TOP 5 * FROM products; -- SQL Server
SELECT * FROM products FETCH FIRST 5 ROWS ONLY; -- DB2
SELECT * FROM products WHERE ROWNUM <= 5; -- Oracle
SELECT * FROM products LIMIT 0,5; -- MySQL

排序

SELECT * FROM products ORDER BY prod_name; -- 单列排序
SELECT * FROM products ORDER BY prod_price, prod_name; -- 多列排序
SELECT * FROM products ORDER BY 1,2; -- 使用列的相对位置指定排序 使用这种方式只能针对出现在SELECT中的列进行排序
SELECT * FROM products ORDER BY prod_price DESC; -- 降序排序 默认为ASC升序排序
SELECT * FROM products ORDER BY prod_price DESC, prod_name DESC; -- 多列降序排序需要每列都指定DESC

使用ORDER BY 子句时,应该保证其实SELECT 语句的最后一条子句。

数据过滤

SELECT * FROM products WHERE prod_price=3.49;

WHERE子句操作符

操作符 说明
= or == 等于
<> or != 不等于
< 小于
<= 小于等于
!< 不小于
   | 大于

= | 大于等于 !> | 不大于 BETWEEN | 在两个值之间 IS NULL | 为NULL值

高级数据过滤

SELECT * FROM products WHERE prod_price=3.49 OR prod_price = 5.99; -- 逻辑OR
SELECT * FROM products WHERE prod_price=3.49 AND prod_price < 5.99; -- 逻辑AND
SELECT * FROM products WHERE prod_price IN (3.49, 5.99); -- IN操作符 相比OR更清晰同时有更好的性能 也可以动态包含SELECT语句
SELECT * FROM products WHERE NOT prod_price IN (3.49, 5.99); -- 逻辑NOT

SQL处理AND的优先级比OR高,所以同时使用OR和AND有必要使用括号来明确求值顺序。

通配符过滤

LIKE 关键字:

示例:

SELECT name FROM user WHERE name LIKE 'user%'; --查找用户名以user开头的用户

escape :用来标志逃逸字符

LIKE 'ab\\cd%' escape '\' #匹配所有以ab\cd开头的字符串

SQL1999 中提供了similar to操作,语法类似于正则表达式。

这些通配符匹配在一定程度上会影响性能,这点需要注意。

计算字段

-- 字符串连接
SELECT vend_name + '(' + vend_country + ')' AS name FROM vendors; -- Access SqlServer
SELECT vend_name || '(' || vend_country || ')' AS name FROM vendors; -- DB2 Oracle...
SELECT CONCAT(vend_name,'(',vend_country,')') AS name FROM vendors; -- MySQL

使用AS关键字被视为最佳实践。别名也被称为导出列,Oracle不支持AS关键字

-- 数值计算
SELECT prod_id, quantity * item_price AS total FROM orderitems;
SELECT 2 * 6; -- 当省略子句时,就代表计算这个表达式并展现

函数

SELECT SOUNDEX("meet"), SOUNDEX("meat"); -- SOUNDEX函数将字母描述为字母数字模式 Access和PostgreSQL不支持
函数 作用
LEFT ( 或使用子字符串函数) 返回字符串左边的字符
LENGTH (也使用DATALENGTH或LEN) ) 返回字符串的长度
LOWER ( Access使用LCASE ) 将字符串转换为小写
LTRIM 去掉字符串左边的空格
RICHT (或使用子字符串函数) 返回字符串右边的字符
RTRIM 去掉字符串右边的空格
UPPER ( Access使用UCASE ) 将字符串转换为大写

不同DBMS很不一致 可移植性最差

SELECT YEAR(NOW()); -- MySQL
SELECT to_char(CURRENT_DATE,'YYYY') FROM dual; -- Oracle
函数 作用
ABS 返回一个数的绝对值
COS 返回一个角度的余弦
EXP 返回一个数的指数值
PI 返回圆周率
SIN 返回一个角度的正弦
SQRT 返回一个数的平方根
TAN 返回一个角度的正切

聚合数据

SELECT AVG(prod_price) FROM products; -- 求平均值,AVG函数忽略NULL

SELECT COUNT(cust_email) FROM customers; -- 对列计算,忽略NULL
SELECT COUNT(*) FROM customers; -- 对列计算,不忽略NULL

SELECT MAX(prod_price) FROM products; -- 求最大值 忽略NULL
SELECT MIN(prod_price) FROM products; -- 求最小值 忽略NULL

SELECT SUM(item_price * quantity) FROM orderitems; -- 求和,忽略NULL

SELECT SUM(DISTINCT item_price) FROM orderitems; -- 只对不同的结果进行求和

分组查询

根据后面的列进行分组

SELECT TO_DAYS(create_time),COUNT(1) FROM web_log GROUP BY TO_DAYS(create_time)
-- 查询每天的访问次数

GROUP BY子句的一些规则:

可以包含任意数目的列

子句的每一列都必须是检索列或者有效的表达式(但不能使聚集函数),除了聚集函数,SELECT 中的每一列,GROUP BY子句中都必须有

NULL会被分为一组

GROUP BY必须在WHERE之后 ORDER BY之前

HAVING子句:

WHERE在分组前过滤 HAVING在分组后过滤行,满足HAVING后的条件的分组才会被选择

SELECT TO_DAYS(create_time),COUNT(1) 
FROM web_log GROUP BY TO_DAYS(create_time) HAVING COUNT(1)>1000
-- 查询访问次数1000的那些天

子查询

MySQL4.1后才支持子查询

SELECT username FROM user WHERE user_id IN 
(SELECT user FROM state);
-- 查询发表过动态的用户

作为子查询的SQL只能查询单个列

SELECT cust_name,
  (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id)
FROM customers;
-- 将子查询作为计算字段

联结

联结是一种机制,用来在一条SELECT语句中关联表

SELECT name,instructor.dept_name,building
FROM instructor , department
WHERE instructor.dept_name = department.dept_name; -- 在联结中,特别需要注意列的全限定名

上面的这种联结叫做等值联结,等值联结等同于内联结:

SELECT name,instructor.dept_name,building
FROM instructor INNER JOIN department
ON instructor.dept_name = department.dept_name;

笛卡尔积:

表1:

name age
小明 15
小红 16

表2:

grade school
5 中心小学
6 中心小学

两张表的笛卡尔积是:

name age grade school
小明 15 5 中心小学
小红 16 6 中心小学
小明 15 6 中心小学
小红 16 5 中心小学

笛卡尔积也被称为叉联结(cross join)

联结可以跨多张表:

SELECT * FROM orderitems, products, vendors;

但联结表越多,性能下降越厉害,基于此,许多DBMS都对联结的表数量做了限制,阿里的p3c中也规定联结表的数量不得超过3张

自连接

SELECT * FROM customers AS c1, customers AS c2
WHERE C1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones'; -- 查出与Jim Jones同公司的顾客

许多DBMS处理自联结往往比子查询快得多

自然连接

SELECT name,instructor.dept_name,building
FROM instructor , department
WHERE instructor.dept_name = department.dept_name

上面那条SQL可以简化成下列形式:

SELECT name,instructor.dept_name,building
FROM instructor NATURAL JOIN department

外连接

左外连接:

select * from user  
left outer join state on user.user_id = state.user;
-- 把user和state进行连接,如果用户没有发表state,则仍保留用户,只是state相关列为NULL

右外连接如上取反

全外连接可以包含两个表中不关联的行,许多DBMS不支持

natural join等价于natural inner join

集合运算

SELECT name FROM student WHERE age = 15
UNION
SELECT name FROM student WHERE age = 16

每个UNION SELECT语句的列都必须相同,类型必须兼容

UNION会自动去除重复行,如果需要保留重复行,则使用UNION ALL

如果UNION语句需要排序,则在最后一条SELECT语句加上ORDER BY子句,ORDER BY作用于所有UNION语句

INTERSECT关键字 用法同上

EXCEPT 关键字 同上

空值

集合比较

SELECT username FROM user
WHERE age > all 
(SELECT age FROM user WHERE sex = '女')
# 查询出年龄大于全部女性年龄的用户

空关系测试

EXIST 关键字: 当改关键字后面的关系非空时返回true,反之返回false 相关子查询:

SELECT user_id FROM user 
WHERE EXISTS (SELECT * FROM state WHERE user = user_id);
# 查询发表过动态的用户ID

重复元组存在性测试

UNIQUE 关键字: 查询是否存在重复的元组

FROM子句中的子查询

SELECT * FROM (SELECT username FROM user) AS T;
# 使用FROM子句子查询,有些数据库要求FROM后面的子查询需要指定一个别名

WITH子句

提供定义临时关系的方法

标量子查询

如果一个子查询的结果只有一个元组,那么可以放在单个值能出现的任何地方:

SELECT username,(SELECT COUNT(1) 
FROM state WHERE state.user = user.user_id) FROM user;
-- 查询每个用户的用户名及其发表的动态条数

数据库的修改

删除

DELETE FROM r
WHERE p

示例:

DELETE FROM user
WHERE username = 'root'
# 删除用户名为root的用户

插入

INSERT INTO user VALUES(1,'username',15);-- 这种方式需要指定全部列,每次插入数据,应尽可能提供所有列名
INSERT INTO user(username,age) VALUES('username',15);-- 这种方式不需要指定全部列,允许为NULL值或者表定义有默认值的列可以被省略
INSERT INTO user SELECT * FROM user;-- 插入查询出来的数据,根据列的位置进行插入,列名可以不匹配
-- 某些SQL实现INTO是可选的
SELECT * INTO cust_copy FROM customers; -- 复制数据到新表
CREATE TABLE cust_copy AS  SELECT * FROM customers; -- MySQL Oracle PostgreSQL的语法

更新

UPDATE r
SET k1=v1,k2=v2,...,kn=vn
WHERE p
UPDATE user
SET username = 'abc'
WHERE username = 'root'

视图

定义:不是逻辑模型的一部分,但是作为虚关系对用户可见

Access 不支持视图 MySQL从5之后才支持视图 SQLite只支持只读视图

视图定义

CREATE VIEW v AS <查询表达式>

创建一个部分用户视图:

CREATE VIEW user_part 
AS
SELECT * FROM user LIMIT 10

如果视图使用了复杂的联结或者对视图进行了嵌套,性能会下降的很厉害,同时视图也不支持创建索引

SQL查询中使用视图

再查询中,视图能出现在关系名可以出现的任何地方

SELECT * FROM user_part

对于视图的使用:

  1. 使用视图隐藏复杂的联结
  2. 使用视图格式化查询的数据
  3. 过滤掉不想要的数据

可以极大简化复杂数据的处理

物化视图

如果用于定义视图的实际关系改变,视图也跟着修改。这样的视图称为物化视图

视图更新

一般来说,满足下列所有条件,则视图是可更新的

事务

定义:事务内的所有语句要么全部执行,要么全部不执行

MySQL:

START TRANSACTION; -- 开始一个事务
DELETE FROM orderitems; -- 执行事务操作
ROLLBACK; -- 回滚事务
COMMIT; -- 提交事务

Oracle:

SET TRANSACTION;
...
COMMIT;

对于没有明确标志事务结束的语句,事务将一直存在。

默认没有开启的事务的执行语句都是隐式提交

为了实现部分回滚的功能,这里引入一个叫做保存点的东西,回滚可以回滚到保存点,放弃回滚点后的所有更改

START TRANSACTION;
SAVEPOINT p1;
DELETE FROM orderitems;
ROLLBACK TO p1;
COMMIT;

游标

在检索出来的行中前进或者后退。

Access不支持 MySQL5之后才支持 SQLite的游标称为步骤。

相较而言,游标对对于Web应用用处不大

完整性约束

完整性约束防止的是对数据的意外破坏。

单个关系上的约束

NOT NULL约束

 CREATE TABLE `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `password` varchar(32) NOT NULL,
  `user_info` int(11) NOT NULL,
  `permission` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  `last_login` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=293 DEFAULT CHARSET=utf8

表示禁止在该属性上插入NULL值

UNIQUE 约束

被该约束修饰的属性在单个关系上是唯一的,由于NULL != NULL ,所以一个关系上允许存在多个NULL值

CHECK 子句

check(p) 指定一个谓词P,只有当该谓词满足时,数据库才允许插入

参照完整性

一个关系中给定属性集上的取值在另一关系的特定属性集的取值中出现,这种情况称为参照完整性

CREATE TABLE test(
user_id INT,
FOREIGN KEY (user_id) REFERENCES user(user_id)
);

test表中的user_id参照user表的user_id

事务中对完整性约束的违反

如果事务中的某条SQL语句违反了完整性约束,则会马上进行检查。有些DBS支持将initially deferred加入到约束中,这样完整性约束检查就会在事务结束的时候进行。

复杂CHECK条件与断言

比如CHECK后面的谓词可以使用子查询:

CREATE TABLE test(
user_id INT 
CHECK(user_id IN( SELECT user.user_id FROM user))
)

这样在插入test表时,只有在user表中出现的user_id才被允许插入,但是大多数数据库还不支持 断言:

CREATE ASSERTION <name> CHECK <p>

任何在断言中涉及到的关系发生变动,都会触发断言。

SQL中的数据类型与模式

SQL中的日期和时间类型

与时间相关的函数:

默认值

CREATE TABLE test(
  user_id INT DEFAULT 0
);

当user_id未指定时,默认为0

创建索引

CREATE INDEX index_1 ON test(id)

大对象类型

用户定义的类型

CREATE TABLE 的扩展

创建两个模式相同的表:

CREATE TABLE test1 LIKE test

从查询中创建表:

CREATE TABLE test2 AS 
(
SELECT * FROM test
)
WITH DATA;
# mysql不支持

模式、目录与环境

当代数据库提供了三层结构的关系命名机制,最顶层由目录构成,每个目录当中可以包含模式,目录 == 数据库。 默认目录和模式是为每个连接建立的SQL环境的一部分。

授权

权限的授予与收回

GRANT <权限列表>
ON <关系或视图>
TO <用户或角色列表>
GRANT SELECT ON department TO user1
# 授予user1查询department表的权限

public:代表当前系统的所有用户以及未来用户

REVOKE <权限列表>
ON <关系名或视图名>
FROM <用户/角色列表>
REVOKE SELECT ON department FROM user1
# 收回user1的查询权限

角色

创建角色:

CREATE ROLE <角色名>
GRANT admin to user1;
# 将admin角色授予user1

视图的授权

同上

模式的授权

GRANT REFERENCES (dept_name) ON department TO user1
# 允许user1创建这样的关系:它能参照department的dept_name

权限的转移

在授权语句最后加上 WITH GRANT OPTION 即允许用户可将权限授予给其他用户

权限的收回

默认情况下,多数DBS都会级联收回用户的权限 如果在收回语句最后加上 RESTRICT关键字,可以防止级联收回

存储过程

存储过程可以看成是对一系列 SQL 操作的批处理

Access 与 SQLite 不支持,MySQL5之后才支持

不同DBMS存储和调用存储过程的方式都很不一致

使用程序设计语言访问数据库

JDBC

一段经典的JDBC代码:

// 加载驱动
 Class.forName("com.mysql.jdbc.Driver");
 // 获取连接
 Connection connection =
         DriverManager.getConnection("jdbc:mysql:///test","root","Root@@715711877");
 // 执行SQL

ResultSet resultSet = connection.prepareStatement("SELECT * FROM test").executeQuery();

//取回结果集
while (resultSet.next()){
    System.out.println(resultSet.getInt("id")+"|"
            +resultSet.getString("name"));
}
connection.close();