SQL 语句进阶

本文SQL语句基于MySQL。

一、MySQL 的数据类型

SELECT prod_name FROM products LIMIT 5,4;  
#  返回从5行开始的4行，即5～8行。（从0行开始）
SELECT prod_id, prod_name FROM products ORDER BY prod_id DESC, prod_name LIMIT 3;
#  按照 prod_id 降序排列，如果 prod_id 相同，则按 prod_name 升序排列，取前3个
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
#  查询任意以 jet 开头的 prod_name
#  这里'%'可以匹配任意字符或0个字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet_';
#  '_' 总是匹配一个字符，不多不少

二、REGEXP (正则)

SELECT prod_name FROM products WHERE prod_name REGEXP '.000';
#  用正则表达式匹配，'.'是正则表达式中表示任意一个字符
#  因为直接匹配 '000'，任何包含000的串都会匹配出来
#  这里 '.000' 和 '000' 的差别就是 '.000' 最少需要4个字符。
REGEXP:
'1000|2000' 匹配1000或2000
'[123] Ton' ，[123]表示匹配1或2或3，比如 '1 Ton'
'[^123]'，表示匹配除123以外的字符
'[0-9]' '[a-z]' ，匹配一个范围
'\\.' ，匹配特殊字符前加 '\\'
'\\([0-9] sticks?\\)' ， 可以匹配 'TNT (1 stick)','TNT (1 sticks)'
#  '?'表示匹配它前面的任何字符的0次或者1次，{0,1}。
#  '+'表示1次或多次，{1,}
#  '*'表示0次或多次，{0,}
#  '{n}'表示指定数目的匹配
#  '{n,}'表示不少于指定数目的匹配
#  '{n,m}'表示指定范围的匹配
'[0-9]{4}' ，'{4}'要求它前面出现的字符出现4次。
'^[0-9\\.]' ，匹配0-9或者.开头的字符串。
#  '^' 在[]外面时表示开始的定位符， '$' 表示结尾

SELECT Concat(vend_name, '(', vend_country,')') FROM vendors ORDER BY vend_name;
#  Concat() 把多个串拼接成一个串
SELECT Concat(Trim(vend_name), '(', vend_country,')') FROM vendors;
#  Trim() 去除两端的空格
SELECT Concat(Trim(vend_name), '(', vend_country,')') AS vend_title FROM vendors;
#  显示时，此字段别名为 'vend_title'
SELECT quantity*item_prices AS price FROM vendors;
#  计算 quantity乘item_prices 的结果并取别名 price
#  操作符有 '+','-','*','/'

三、文本处理函数：

Upper() #  返回大写字母
Lower() #  返回小写字母
Length() #  串的长度
LOCATE(substr,str) #  返回字符串substr中第一次出现子字符串的位置 str。如果substr不存在返回0
LEFT(str,length); #  返回str的左边length位字符
RIGHT(str,length); #  返回str的右边length位字符
substring(str, pos); substring(str, pos, len) #  截取子串
LTRIM();    #	去除左边的空格
RTRIM();    #	去除右边的空格
SOUNDEX();  #  转换为语音值

四、日期和时间处理函数：

• 日期格式：YYYY-MM-DD
• 时间格式：HH:MM:SS

Date() # 返回日期部分‘2001-01-01’，参数可以使DateTime或varchar
AddDate()  #	增加一个日期（天、周等）
AddTime()  #	增加一个时间（时、分等）
CurDate() # 返回当前日期 '2001-01-01'
CurTime() # 返回当前时间 '02:03:59'
Date_Add()	# 高度灵活的日期运算函数
Date_Format() #	返回一个格式化的日期或时间串
Now()	#   返回当前日期和时间
Time() # 返回时间部分 '02:03:59'
DateDiff() # 返回两日期之差
Year() Month() Day() Hour() Minute() Second() # 返回相应
DayOfWeek() # 返回星期几

SELECT cust_id FROM orders WHERE Date(order_date) BETWEEN '2001-01-01' AND 2001-01-05'';
SELECT cust_id FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
SELECT NOW(); # 显示当前时间

五、数值处理

SIN()	# 正弦
COS()	# 余弦
TAN()	# 正切
ABS()	# 绝对值
SQRT()	# 平方根
MOD()	# 余数
EXP()	# 指数
PI()	# 圆周率
RAND()	# 随机数

六、聚集函数

AVG() # 返回某列的平均数，会忽略 NULL 行。
SUM() # 返回某列值之和
MAX() # 返回某列值最大
MIN() # 返回某列值最小
COUNT() # 返回某列的行数

SELECT COUNT(cust_email) FROM customers; # 返回customers中有email地址的个数
SELECT COUNT(*) FROM customers; # 返回customers的个数，cust_email为NULL也算

七、数据分组

• GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。
• WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。
• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
• WHERE -> GROUP BY -> ORDER BY -> HAVING;
• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
• NULL 的行会单独分为一组；
• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

SELECT vend_id, COUNT(*) FROM products GROUP BY vend_id; # 显示每个 vend_id 有多少
SELECT vend_id, COUNT(*) AS orders FROM products GROUP BY vend_id HAVING COUNT(*) >= 2; #  过滤数量小于2的
SELECT vend_id, COUNT(*) AS orders FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2; #  过滤数量小于2的

八、子查询

SELECT cust_id FROM orders WHERE order_num IN (
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'); # 先执行子查询，再执行外侧查询，一个两次

SELECT cust_name, cust_state,
(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders 
FROM customers ORDER BY cust_name; # 先执行外侧查询，对每行结果再进行子查询

SELECT (SELECT name FROM Person) name;  # 当子查询为空集时，最后显示为NULL（而非显示空集）。name这里为别名。

九、连接

• 连接可以替换子查询，并且比子查询的效率一般会更快。

SELECT vend_name, prod_name, prod_price FROM vendors, products;
# 笛卡尔积，也叫交叉连接

SELECT vend_name, prod_name, prod_price FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name; # 等值连接，又称内部连接
# 等价于下面的内部连接
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id; 
# 内部连接/等值连接，不保留没有连接到的行，保留重复列。


SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department AND e2.name = "Jim";
# 自连接（自己连接自己），一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。
# 不保留没有连接到的行，保留重复列。

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;
# 自然连接， 内部连接（等值连接）和自然连接的区别：
# 内部连接提供连接的列（有重复列），而自然连接自动连接所有同名列。
# 自然连接不需要使用 where 或者是 on 限定条件，会根据两个表的字段自发选择，即选择字段名及类型都相同的列（可能多个）。

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT JOIN Orders
ON Customers.cust_id = Orders.cust_id; 
# 左外连接，左外连接就是保留左表没有关联的行。右外连接就是保留右表没有关联的行。均保留重复列。
# 全外连接就是保留左右表没有关联的行。

# 左外连接使用 LEFT JOIN 或 LEFT OUTER JOIN
# 右外连接使用 RIGHT JOIN 或 RIGHT OUTER JOIN
# 全外连接使用 FULL JOIN 或 FULL OUTER JOIN

十、组合查询

SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001, 1002); # UNION把两个输出组合成单个结果集。

UNION 会消除重复行
UNION ALL 会保留重复行

SELECT vend_id, prod_id, prod_price FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products
WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_price; # UNION 语句想排序只能在最后一个查询语句加排序，会对整个结果集排序。

十一、视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

• 简化复杂的 SQL 操作，比如复杂的连接；
• 只使用实际表的一部分数据；
• 通过只给用户访问视图的权限，保证数据的安全性；
• 更改数据格式和表示。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

十二、存储过程 （简单、安全、高性能）

存储过程可以看成是对一系列 SQL 操作的批处理，MySQL 5 添加了对存储过程的支持。

使用存储过程的好处：

• 代码封装，简化操作，并防止错误操作（安全性），如需改动只需修改存储过程而使用者无需知道；
• 代码复用；
• 由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 IN（传递给存储过程）、OUT（从存储过程传出） 和 INOUT（对存储过程传入和传出） 三种参数。

给变量赋值都需要用 SELECT INTO 语句。

每次只能给一个变量赋值，不支持集合的操作。

DELIMITER  \\   # 设置 语句结束分隔符 为 `\\` （为避免语句中的';'被误解）

# 定义 myprocedure 存储过程
CREATE PROCEDURE myprocedure(
    IN onnumber INT,
    IN taxable BOOLEAN,
    OUT ret INT
    )  # ret 只能返回int，而不是多个行和列
    BEGIN   # 存储过程体开始
        DECLARE y INT;  # 声明一个INT类型的变量
        -- 这是另一种注释方式
        SELECT SUM(col1)
        FROM mytable
        WHERE col2 = onnumber
        INTO y;
        SELECT y*y INTO ret;
        
        IF taxalbe THEN  // 条件判断语句
            SELECT ret+2 INTO ret;
        END IF;
    END  \\    # 语句结束（发现语句分隔符 '\\'）
    #  存储过程体结束

DELIMITER ;  # 设置 语句结束分隔符 为 `;`

MySQL 语句中的变量必须以@开头，存储过程中的变量不需要。

CALL myprocedure(10005, @ret);  # 调用 myprocedure 存储过程，给出`onnumber=10005`并返回 ret
SELECT @ret;  # 显示 ret

存储过程创建之后，被保留在服务器上以供使用，直至被删除。

SHOW CREATE PROCEDURE myprocedure; # 显示 myprocedure 这个存储过程。
SHOW PROCEDURE STATUS;  # 列出所有存储过程
DROP PROCEDURE myprocedure;  # 删除 myprocedure 这个存储过程，如果不存在这个存储过程会报错。
DROP PROCEDURE IF EXISTS myprocedure;  # 删除存储过程，不存在不报错。

十三、游标

在存储过程中使用游标可以对一个结果集进行移动遍历，MySQL 5 添加了对游标的支持。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

• 声明游标，这个过程没有实际检索出数据，只是定义SELECT语句；
• 打开游标，这个过程用SELECT语句把数据实际检索出来并存储；
• 取出数据，对于填有数据的游标，按需取出各行数据；
• 关闭游标，释放游标使用的所有内存和资源；

FETCH 语句分别访问游标的每一行，每访问一行向前移动内部行指针。

DELIMITER //
CREATE PROCEDURE myprocedure(OUT ret INT)
    BEGIN
        # 变量的定义必须在游标之前
        DECLARE done BOOLEAN DEFAULT 0; # 定义 done，默认值为 0
        # 定义游标
        DECLARE mycursor CURSOR  
        FOR
        SELECT col1 FROM mytable;
        
        # 定义了一个 continue handler，当 SQLSTATE '02000' 这个条件出现时，会执行 set done = 1
        # SQLSTATE '02000' 是一个未找到条件，当repeat由于没有更多行供循环而不能继续时，出现这个条件。
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

        OPEN mycursor;  # 打开游标 

        REPEAT  # 循环
            FETCH mycursor INTO ret;  # 结果放入 ret
            SELECT ret;  # 显示 ret
        UNTIL done END REPEAT;

        CLOSE mycursor; # 关闭游标
    END //
DELIMITER ;

十四、触发器

MySQL 5 添加了对触发器的支持，只有表支持触发器，视图不支持。

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前或之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。因此每个表最多支持6个触发器（三个操作 * (之前 + 之后)）。

在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行。

CREATE TRIGGER mytrigger AFTER INSERT ON mytable  # INSERT 触发器
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col;   # 和上面两条语句等价。

在 DELETE 触发器代码内，可引用一个名为 OLD 的虚拟表，访问被删除的行。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders  # DELETE 触发器
FOR EACH ROW 
BEGIN
    INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END

在 UPDATE 触发器代码内，可引用一个名为 OLD 的虚拟表访问以前的值，可引用一个名为 NEW 的虚拟表访问新更新的值。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

删除触发器

DROP TRIGGER newproduct;

十五、事务处理

InnoDB 支持事务处理，MyISAM 不支持。

基本术语：

• 事务（transaction）指一组 SQL 语句。
• 回退（rollback）指撤销指定 SQL 语句的过程。
• 提交（commit）指将未存储的 SQL 语句结果写入数据库表。
• 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（部分回退）。

CREATE 和 DROP 语句不能回退。

一般的 MySQL 语句都是隐含提交的，即提交操作是自动进行的。但在事务处理块中使用 COMMIT 语句明确的提交。

可以通过 SET autocommit=0 指示MySQL不自动提交（只针对当前的连接而不是服务器）。

START TRANSACTION;   # 标识事务的开始
   ...
   ...
ROLLBACK;  # 事务回退

START TRANSACTION;
// ...
SAVEPOINT delete1; # 创建保留点
// ...
ROLLBACK TO delete1; # 回退到 delete1 保留点
// ...
COMMIT;

十六、事务自动提交与设置隔离级别

设置自动提交状态

show session variables like 'autocommit';
# 查看会话系统autocommit的值
show global variables like 'autocommit';
# 查看全局系统autocommit的值
# Value的值为ON，表示autocommit开启。OFF表示autocommit关闭。

set session autocommit=0;
# 设置会话系统autocommit的值为0,表示关闭autocommit
set global autocommit=0;
# 设置全局系统autocommit的值为0,表示关闭autocommit

设置隔离级别

select @@tx_isolation;
# 查看当前会话的隔离级别
select @@global.tx_isolation;
# 查看全局的隔离级别

set [session | global] transaction isolation level 
[ read uncommitted |
  read committed   |
  repeatable read  |
  serializable
];
# 设置隔离级别