SQL进阶学习

# 基本命令

# DISTINCT

返回不同的值。

SELECT DISTINCT user_id

FROM order

# SELECT TOP，LIMIT，ROWNUM

指定返回的记录数量。MySQL 支持 LIMIT 子句，Oracle 支持 ROWNUM 子句。

• LIMIT

SELECT *

FROM lottery_code_repository

LIMIT 5

• SELECT TOP

SELECT TOP 2 * 

FROM Customers

WHERE Country='Germany'

查询前百分之五十的记录：

SELECT TOP 50 PERCENT * 

FROM Customers

WHERE Country='Germany'

• ROWNUM

SELECT * 

FROM Customers

WHERE Country='Germany' AND ROWNUM <= 3

# LIKE 运算符

通配符：

• % 表示零个，一个或多个字符
• _ 表示单个字符

# Wildcards 通配符

• [charlist] 定义要匹配的字符的集合和范围
• [^charlist] 或 [!charlist] 定义不匹配字符的集合和范围

SELECT * 

FROM Customers 

WHERE City LIKE '[bsp]%'

MySQL 好像不支持，运行不报错，但是查询结果为空。

# IN 运算符

SELECT *

FROM lottery_code_repository

WHERE lottery_code IN ('BYH0IU', 'BYYOQI', 'BY7W87')

# BETWEEN 运算符

SELECT *

FROM lottery_code_repository

WHERE lottery_code BETWEEN 'BYH0IU' AND 'C7GNIY'

# UNIOM 运算符

SELECT *

FROM lottery_code_repository

WHERE lottery_code IN ('BYH0IU', 'C7GNIY')

UNION 

SELECT *

FROM lottery_code_repository

WHERE lottery_code = 'C7GNIY'

SELECT *

FROM lottery_code_repository

WHERE lottery_code IN ('BYH0IU', 'C7GNIY')

UNION ALL

SELECT *

FROM lottery_code_repository

WHERE lottery_code = 'C7GNIY'

# SELECT INTO

从一个表中复制数据，将数据插入到另一个新表中。

SELECT *

INTO lottery

FROM lottery_code_repository

WHERE lottery_code IN ('BYH0IU', 'C7GNIY')

MySQL 不支持 SELECT INTO，只能先创建表然后插入数据。

CREATE TABLE lottery

(SELECT *

FROM lottery_code_repository

WHERE lottery_code IN ('BYH0IU', 'C7GNIY'))

# INSERT INTO SELECT

从表中复制数据，并将数据插入现有的表中。

INSERT INTO lottery

SELECT *

FROM lottery_code_repository

WHERE lottery_code IN ('BYH0IU', 'C7GNIY')

目标表中的任何现有行都不会受到影响。

# TRUNCATE TABLE 命令

删除现有数据表中的所有数据，不删除表结构。DROP TABLE 会删掉数据和表结构。

# 函数

以下函数适用于 MySQL。

# 文本处理函数

• LEFT(str,len)

  返回字符串左边长度为 len 的子串。

  	SELECT order_name, LEFT(order_name, 3) AS name
  	FROM ad_order

• RIGHT(str,len)

  返回字符串右边长度为 len 的子串。

  	SELECT order_name, RIGHT(order_name, 3) AS name
  	FROM ad_order

• LENGTH(str)

  返回字符串的长度。试试因为数据库设置是 UTF-8 编码，所以一个汉字是 3 个字节，一个数字或字母是一个字节。

  	SELECT order_name, LENGTH(order_name) AS name
  	FROM ad_order

• LOWER(str)

  将字符串转换为小写。

  	SELECT order_name, LOWER(order_name) AS name
  	FROM ad_order

• UPPER(str)

  将字符串转换为大写。

  	SELECT order_name, UPPER(order_name) AS name
  	FROM ad_order

• LTRIM(str)

  去掉字符串左边的空格。

  	SELECT order_name, LTRIM(order_name) AS name
  	FROM ad_order

• RTRIM(str)

  去掉字符串右边的空格。

  	SELECT order_name, RTRIM(order_name) AS name
  	FROM ad_order

• SOUNDEX(str)

  返回字符串的 SOUNDEX 值。SOUNDEX 返回由四个字符组成的代码以评估两个字符串的相似性。

  	SELECT order_name, SOUNDEX(order_name) AS name
  	FROM ad_order

# Date 函数

• NOW()

  返回当前的日期和时间。

  SELECT NOW() AS datetime

• CURDATE()

  返回当前的日期。

  SELECT CURDATE() AS date

• CURTIME()

  返回当前的时间。

  SELECT CURTIME() AS date

• DATE()

  提取日期或日期 / 时间表达式的日期部分。

  SELECT DATE(NOW()) AS date

• EXTRACT(unit FROM date)

  返回日期 / 时间的单独部分。date 参数是合法的日期表达式，unit 参数参考表 1。

  	SELECT NOW() AS `NOW`, EXTRACT(YEAR FROM NOW()) AS `YEAR`,
  	EXTRACT(MONTH FROM NOW()) AS `MONTH`,
  	EXTRACT(DAY FROM NOW()) AS `DAY`,
  	EXTRACT(HOUR FROM NOW()) AS `HOUR`,
  	EXTRACT(MINUTE FROM NOW()) AS `MINUTE`,
  	EXTRACT(SECOND FROM NOW()) AS `SECOND`,
  	EXTRACT(MICROSECOND FROM NOW()) AS `MICROSECOND`

  	SELECT NOW() `NOW`, EXTRACT(WEEK FROM NOW()) AS `WEEK`,
  	EXTRACT(QUARTER FROM NOW()) AS `QUARTER`

  	SELECT NOW() AS `NOW`, EXTRACT(YEAR_MONTH FROM NOW()) AS `YEAR_MONTH`,
  	EXTRACT(DAY_HOUR FROM NOW()) AS `DAY_HOUR`,
  	EXTRACT(DAY_MINUTE FROM NOW()) AS `DAY_MINUTE`,
  	EXTRACT(DAY_SECOND FROM NOW()) AS `DAY_SECOND`,
  	EXTRACT(DAY_MICROSECOND FROM NOW()) AS `DAY_MICROSECOND`,
  	EXTRACT(HOUR_MINUTE FROM NOW()) AS `HOUR_MINUTE`,
  	EXTRACT(HOUR_SECOND FROM NOW()) AS `HOUR_SECOND`,
  	EXTRACT(HOUR_MICROSECOND FROM NOW()) AS `HOUR_MICROSECOND`,
  	EXTRACT(MINUTE_SECOND FROM NOW()) AS `MINUTE_SECOND`,
  	EXTRACT(MINUTE_MICROSECOND FROM NOW()) AS `MINUTE_MICROSECOND`,
  	EXTRACT(SECOND_MICROSECOND FROM NOW()) AS `SECOND_MICROSECOND`

• DATE_ADD(date,INTERVAL expr unit)

  向日期添加指定的时间间隔。date 参数是合法的日期表达式，expr 参数是添加的时间间隔，unit 参数参考表 1。

  SELECT NOW() AS NOW, DATE_ADD(NOW(), INTERVAL 2 DAY) NOW_ADD

• DATE_SUB(date,INTERVAL expr unit)

  从日期减去指定的时间间隔。date 参数是合法的日期表达式，expr 参数是减去的时间间隔，unit 参数参考表 1。

  SELECT NOW() AS NOW, DATE_SUB(NOW(), INTERVAL 2 DAY) NOW_SUB

• DATEDIFF(expr1,expr2)

  返回两个日期之间的天数。

  SELECT DATEDIFF("2018-10-19 13:34:11", "2018-10-17 13:34:11") AS `DATEDIFF`

• DATE_FORMAT(date,format)

  用不同的格式显示日期 / 时间。date 参数是合法的日期，format 参数规定日期 / 时间的输出格式。可以有的格式参考表 2。

MySQL DATE 数据类型：

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
• YEAR - 格式：YYYY 或 YY

表 1

表 2

表 date

SELECT *

FROM date

WHERE date = '2018-7-30'

SELECT *

FROM date

WHERE datetime = '2018-7-30'

查询结果为空。

SELECT *

FROM date

WHERE datetime = '2018-7-30 15:32:11'

# 数值处理函数

• ABS(X)

  返回一个数的绝对值。

• COS(X)

  返回一个角度的余弦。

• EXP(X)

  返回一个数的指数值。

• PI()

  返回圆周率。

• SIN(X)

  返回一个角度的正弦。

• SQRT(X)

  返回一个数的平方根。

• TAN(X)

  返回一个角度的正切。

# 视图

视图常用应用：

• 重用 SQL 语句。
• 简化复杂的 SQL 操作。在编写查询后，可以方便地重用它而不用关心具体查询细节。
• 使用表的一部分而不是整个表。
• 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的范根权限。
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

# 简化复杂的联结

原始 SQL：

SELECT *

FROM 

(SELECT campaign.`name`, ad_order.order_name, ad_order_item.order_item_name

FROM campaign, ad_order, ad_order_item

WHERE campaign.id = ad_order.campaign_id AND ad_order.id = ad_order_item.order_id) AS campaign_msg

WHERE name = "campaign2"

创建视图：

CREATE VIEW CampaignMsg AS

SELECT campaign.`name`, ad_order.order_name, ad_order_item.order_item_name

FROM campaign, ad_order, ad_order_item

WHERE campaign.id = ad_order.campaign_id AND ad_order.id = ad_order_item.order_id

查询：

SELECT *

FROM CampaignMsg

where name = "campaign2"

# 格式化检索出的数据

原始 SQL：

SELECT RTRIM(id) + '(' + RTRIM(advertiser_id) + ')' as id

FROM campaign 

ORDER BY id

创建视图：

CREATE VIEW CampaignMsg AS

SELECT RTRIM(id) + '(' + RTRIM(advertiser_id) + ')' as id

FROM campaign

查询：

SELECT *

FROM CampaignMsg

ORDER BY id

# 过滤数据

创建视图：

CREATE VIEW CampaignMsg AS

SELECT id, `name`

FROM campaign 

WHERE `name` IS NOT NULL

查询：

SELECT *

FROM CampaignMsg

# 简化计算字段

原始 SQL：

SELECT prod_id, qunantity, item_price, quantity*item_price AS expanded_price

FROM orderitem

WHERE order_num = 2018

创建视图：

CREATE VIEW CampaignMsg AS

SELECT prod_id, qunantity, item_price, quantity*item_price AS expanded_price

FROM orderitem

查询：

SELECT *

FROM CampaignMsg

WHERE order_num = 2018

# 存储过程

存储过程就是为以后使用而保存的一条或多条 SQL 语句。

# 简单的存储过程

创建存储过程：

CREATE PROCEDURE count()

BEGIN

	SELECT COUNT(id) AS count

	FROM sys_user;

END;

调用存储过程：

CALL getnumber();

检查存储过程：

SHOW CREATE PROCEDURE getnumber;

删除存储过程：

DROP PROCEDURE getnumber;

DROP PROCEDURE IF EXISTS getnumber;

# 含有参数的存储过程

• IN

  创建存储过程：

  	CREATE PROCEDURE getnumber (IN a INT)
  	BEGIN
  	SELECT id
  	FROM sys_user
  	WHERE role_id = a;
  	END;

  调用存储过程：

  CALL getnumber(2);

• OUT

  创建存储过程：

  	CREATE PROCEDURE getnumber (OUT a INT, OUT b INT)
  	BEGIN
  	SELECT
  	MIN(id) INTO a
  	FROM
  	sys_user;
  	SELECT
  	MAX(id) INTO b
  	FROM
  	sys_user;
  	END;

  调用存储过程：

  	CALL getnumber(@a, @b);
  	SELECT @a, @b;

• IN 和 OUT

  创建存储过程：

  	CREATE PROCEDURE getnumber (IN a INT, OUT b INT)
  	BEGIN
  	SELECT COUNT(id)
  	INTO b
  	FROM sys_user
  	WHERE role_id = a;
  	END;

  调用存储过程：

  	CALL getnumber(2, @b);
  	SELECT @b;

# 复杂的存储过程

创建存储过程：

CREATE PROCEDURE getnumber (IN a BOOLEAN, OUT b INT) COMMENT '复杂的存储过程'

BEGIN

	-- 定义一个变量

	DECLARE c INT;

	SELECT COUNT(id) 

	INTO c

	FROM sys_user;

	-- 	判断

	IF a THEN

		SELECT c + 10

		INTO c;

	END IF;

	SELECT c

	INTO b;

END;

调用存储过程：

CALL getnumber(TRUE, @b);

SELECT @b;

# 游标

游标是一个存储在 MySQL 服务器上的数据库查询，它不是一条 select 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

# 使用游标检索结果集的第一个

创建和使用游标：

CREATE PROCEDURE getnumber(OUT a INT)

BEGIN

	DECLARE usernumbers CURSOR

	FOR 

	SELECT id FROM sys_user;

	OPEN usernumbers;

	FETCH usernumbers INTO a;

	CLOSE usernumbers;

END;

调用存储过程获取游标数据：

CALL getnumber(@a);

SELECT @a;

数据返回第一个 id。

# 使用游标循环检索结果集

创建和使用游标：

CREATE PROCEDURE getnumber(OUT a INT)

BEGIN

	DECLARE done BOOLEAN DEFAULT 0;

	DECLARE usernumbers CURSOR

	FOR 

	SELECT id FROM sys_user;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	OPEN usernumbers;

	REPEAT

		FETCH usernumbers INTO a;

	UNTIL done END REPEAT;

	CLOSE usernumbers;

END;

调用存储过程获取游标数据：

CALL getnumber(@a);

SELECT @a;

数据返回最后一个 id，所以要在循环中处理游标数据，比如插入或更新到另一个张表。

# 综合使用游标

CREATE PROCEDURE getnumber()

BEGIN

	DECLARE done BOOLEAN DEFAULT 0;

	DECLARE o INT;

	DECLARE t DECIMAL(8,2);

	DECLARE usernumbers CURSOR

	FOR 

	SELECT id FROM sys_user;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	CREATE TABLE IF NOT EXISTS user_number(id INT);

	OPEN usernumbers;

	REPEAT

		FETCH usernumbers INTO o;

		INSERT INTO user_number(id) VALUES(o);

	UNTIL done END REPEAT;

	CLOSE usernumbers;

END;

执行游标并查询游标执行结果：

CALL getnumber;

SELECT id

FROM user_number;