SQL语句基础

数据库

选择数据库

1	USE crashcourse;

展示数据库

1	SHOW DATABASES;

展示表

1	SHOW TABLES;

展示表列

1 2	SHOW COLUMNS FROM customers; DESCRIBE customers;

检索数据

检索列

1 2	SELECT prod_name, prod_name FROM products;

所有列：*

检索不重复的行

1 2	SELECT DISTINCT vend_id FROM products

限制结果（返回前几行或第几行）

1
2
3

SELECT prod_name
FROM products
LIMIT 5,5

LIMIT的两种情况

LIMIT 检索的开始行行数
LIMIT 行数（）从第一行开始

排序数据

1
2
3

SELECT prod_name
FROM products
ORDER BY prod_name

按多个列排序

1
2
3

SELECT prod_name
FROM products
ORDER BY prod_name, prod_price

逆序

1
2
3

SELECT prod_name
FROM products
ORDER BY prod_name DESC

顺序关键字为ASC，顺序逆序关键字只应用到直接位于其前面的列名

过滤数据

1
2
3

SELECT prod_name
FROM products
WHERE prod_name = test

WHERE子句操作符

1	=, <>, !=, <, <=, >, >=, BETWEEN, IS NULL

在过滤数据时，无论匹配过滤还是不匹配过滤都不会返回NULL值的行

组合过滤关键字

1	AND, OR, IN， NOT

AND的优先级比OR高

1	WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10

IN表示范围中的每个条件都可以进行匹配
1
WHERE vend_id IN (1002, 1003)
相当于
1
WHERE vned_id = 1002 OR vwnd_id = 1003
IN可以包含其他SELECT语句

用通配符过滤

通配符：匹配值的一部分的特殊字符

LIKE操作符

%通配符

%表示任何字符出现任意次数

所有以jet开头的产品
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%'

在任何位置包含文本jet的值
...
WHERE prod_name LIKE '%jet%'

区分大小写
%能匹配0、1或多个字符
尾空格会干扰通配符匹配，解决方法：在搜索模式最后附加一个%
%通配符不能匹配NULL

_通配符

用法类似%，但只匹配单个字符

产品名形如 1 ton anvil, e ton_anvil
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil'

通配符搜索的处理较慢，不要过度使用通配符，若其他操作符能做到，就用其他操作符
除非必要，否则不要把通配符用在搜索模式的开始处，搜索起来最慢

用正则表达式进行搜索

REGEXP

REGEXP后所跟的东西作为正则表达式

1
2
3

SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'

.

.在正则表达式中表示匹配任意一个字符

LIKE和REGEXP的区别：
- LIKE匹配整个列（整个串），REGEXP在列值内进行匹配（子串）
- 正则表达式不区别大小写

|

用于OR匹配

1	WHERE prod_name REGEXP '1000\|2000\|3000'

[]

匹配几个字符之一

1	WHERE prod_name REGEXP '[123] Ton'

[-]

匹配范围

1
2
3

WHERE prod_name REGEXP '[1-5]'
等价于
WHERE prod_name REGEXP ‘1|2|3|4|5'

特殊字符

1
2
3

\\.表示查找.
\\-表示查找-
\\\表示查找\

空白元字符

元字符	说明
\ \ f	换页
\ \ n	换行
\ \ r	回车
\ \ t	制表
\ \ v	纵向制表

匹配字符类

类	说明
[:alnum:]	任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]	任意字符（同[a-zA-Z]）
[:blank:]	空格和制表（同[\t]）
[:cntrl:]	ASCII控制字符（ASCII 0到31和127）
[:digit:]	任意数字（同[0-9]）
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母（同[a-z]）
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符（同[\f\n\r\t\v]）
[:upper:]	任意大写字母（同[A-Z]）
[:xdigit:]	任意十六进制数字（同[a-fA-F0-9]）

重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配（等于{1,}）
?	0个或1个匹配（等于{0,1}）
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围（m不超过255）

匹配形如
TNT (1 stick)
TNT (5 sticks)
REGEXP '\\([0-9] sticks?\\)'

1 2	匹配连在一起的4位数字 REGEXP '[[:digit:]]{4}'

定位符

匹配特定位置的文本

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

1 2	匹配以数字或.开头的列值 REGEXP '^[0-9\\.]'

计算字段

拼接字段

Concat()拼接两个列，各个串之间用逗号分隔

1
2
3

SELECT Concat(vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name

多数DBMS用+或||来实现拼接，MySQL用Concat()实现

RTrim()用于删除数据右侧多余的空格来整理数据，LTrim()删除左侧多余空格

使用别名

AS赋予别名

1
2
3

SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title
FROM vendors
ORDER BY vend_name

算术运算

可以在SELECT中使用加减乘除

1
2
3

SELECT quantity*item AS expanded_price
FROM orderitems
WHERE order_num = 20005

函数

文本处理函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

日期和时间处理函数

函数	说明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

检索时间时要注意转化

1
2
3

SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01'

若order_date = ‘2005-09-01 11:30:05’则匹配失败

应改为

1
2
3

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01'

检索2005年9月的所有订单

1
2
3

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'

或

1
2
3

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9

数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

汇总数据

聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是*，则不忽略
MAX()、MIN()、SUM()函数忽略值为NULL的行

聚集不同的值

指定DISTINCT参数

1
2
3

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003

DISTINCT必须使用列名，不能用于计算或表达式或*
DISTINCT一般只用于COUNT()，用于MIN()，MAX()无意义

分组数据

分组将数据分为多个逻辑组，便于对每个组进行聚集计算

创建分组

用GROUP BY创建分组

1
2
3

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组

过滤分组

HAVING过滤分组（WHERE过滤行）

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY vend_id
HAING COUNT(*) >= 2

HAVING和WHERE的区别：WHERE在数据分组之前过滤，HAVING在分组后进行过滤;WHERE排除的行不包括在分组中
1
2
3
4
5
SELECT cust_id, COUNT(*) AS orders
FROM orders
WHERE prod_price >= 10
GROUP BY vend_id
HAING COUNT(*) >= 2

子查询

SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num
                  FROM orderitems
                  WHERE prod_id = 'TNT2')

联结表

内部联结

1
2
3

SELECT vend_name, prod_name, prod_price
FROM vendors v INNER JOIN products p
ON v.vend_id = p.vend_id

创建高级联结

自联结

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR'

外部联结

包含在相关表中没有关联行的行：OUT JOIN

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表

LEFT JOIN从左边的表中选择所有行
RIGHT JOIN从右边的表中选择所有行

1
2
3

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON orders.cust_id = customers.cust_id

组合查询

合并结果集

在各语句之间放上关键字UNION

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中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）
UNION从查询结果集中自动去除了重复的行，如果想返回所有匹配行，可使用UNION ALL而不是UNION

排序组合查询

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后

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

全文本搜索

两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持

正则搜索的限制：

性能
难以明确控制

全文本搜索中数据是索引的，所以速度很快

启用全文本搜索支持

在CREATE时FULLTEXT(note_text)

进行全文本搜索

Match()指定被搜索的列，Against()指定要使用的搜索表达式

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit')
可以用LIKE
WHERE note_text LIKE '%rabbit%'

传递给Match()的值必须与FULLTEXT()定义的相同，若指定多个列，就必须列出它们（而且次序正确）
搜索不区分大小写

插入数据

插入完整的行

INSERT INTO customers
VALUES(NULL,
      'Pep E',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL)

没有输出
可以省略都写列，但必须允许为NULL或给出默认值

插入多个行

INSERT INTO customers
VALUES(NULL,
      'Pep E',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL),
      (NULL,
      'Pep E',
      '100 Main Street',
      'Los Angeles',
      'CA',
      '90046',
      'USA',
      NULL,
      NULL),

插入检索的数据

不一定要求列名匹配

更新和删除数据

更新数据

更新时注意不要省略WHERE子句，否则会更新所有行

UPDATE customers
SET cust_email = 'elmer@qq.com'
	cust_name = 'Wynton'
WHERE cust_id = 10005

可以使用子查询，使得能用检索出来的数据更新列数据
IGNORE可以在更新多行时，即使发生错误也能继续更新，否则整个UPDATE操作将被取消
删除某个列的值可以设置其为NULL（若允许）

删除数据

1 2	DELETE FROM customers WHERE cust_id = 10006

更新和删除时先用SELECT进行测试

创建和操纵表

创建表

CREATE TABLE customers
(
	cust_id			int			NOT NULL AUTO_INCREMENT,
    cust_name		char(50)	NOT NULL,
    cust_address 	char(50)	NULL,
    cust_city 		char(50)	NULL DEFAULT 'Los',
    PRIMARY KEY (cust_id)
)ENGINE=InnoDB

AUTO_INCREMENT表示，本列每当增加一行时自动增量
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）
默认值用DEFAULT指定
ENGINE指定引擎类型

更新表定义

1
2
3

ALTER TABLE vendors
ADD vend_phone CHAR(20)
DROP COLUMN vend_phone

删除表

1	DROP TABLE customers2

重命名表

1	RENAME TABLE customers TO customers2

使用视图

视图常见应用

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

使用视图

视图用CREATE VIEW语句来创建
使用SHOW CREATE VIEW viewname；来查看创建视图的语句
用DROP删除视图，其语法为DROP VIEW viewname;
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE ORREPLACE VIEW

CREATE VIEW productcustomers AS
SELECT cust_name,...
FROM customers,...
WHERE ...

使用视图可以一次性编写基础SQL，根据需要多次使用

利用视图格式化检索出的数据

CREATE VIEW productcustomers AS
SELECT Concat(RTrim(cust_name),...) AS vend_title
FROM customers,...
WHERE ...
ORDER BY ...

利用视图过滤不想要的数据

CREATE VIEW customereamillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL

利用视图与计算字段

…

使用存储过程

优点：简单、安全、高性能

执行存储过程

1
2
3

CALL productpricing(@pricelow
                   	@pricehigh
                   	@priceaverage)

创建存储过程

CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8, 2)
    OUT ph DECIMAL(8, 2)
    OUT pa DECIMAL(8, 2)
)
BEGIN 
	SELECT MIN(prod_price)
	INTO pl
	FROM products;
	SELECT MAX(prod_price)
	INTO ph
	FROM products;
	SELECT AVG(prod_price) 
	INTO pa
	FROM products;
END;

使用游标

使用游标的原因

使用简单的SELECT语句，例如，没有办法得到第一行、下一行或前10行，也不存在每次一行
地处理所有行的简单方法（相对于成批地处理它们）
有时，需要在检索出来的行中前进或后退一行或多行，游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集

创建游标

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

打开和关闭游标

1 2	OPEN ordernumbers CLOSE ordernumbers

使用游标数据

将数据检索到一个o的局部声明的变量中

CREATE PROCEDURE processorders()
BEGIN
	DECLARE o INT;
	
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	OPEN ordernumbers;
	
	FETCH ordernumbers INTO o;
	
	CLOSE ordernumbers;
END;

或

...
REPEAT
	FETCH ordernumbers INTO o;
UNTIL done END REPEAT;
...

加入条件语句的复杂版本

CREATE PROCEDURE processorders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE 0 INT;
	DECLARE t DECIMAL(8,2);
	
	DECLARE ordernumbers CURSOR
	FOR SELECT order_num FROM orders;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	CREATE TABLE IF NOT EXITS ordertotals
	OPEN ordernumbers;
	
	REPEAT
		FETCH orders INTO o;
		CALL ordertotal(o,1,t)
		INSERT INTO ordertotals(order_num, total)
		VALUES(o,t);
		UNTIL done END REPEAT;
		CLOSE ordernumbers;
	END;

使用触发器

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句

DELETE
INSERT
UPDATE

创建触发器

1 2	CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added'

删除触发器

1	DROP TRIGGER newproduct

INSERT触发器

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）
对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值

1 2	CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num

DELETE触发器

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

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN 
	INSERT INTO archive_orders(order_num, order_date, cust_id)
	VALUES(OLD.order_num, OLD.order_date, OLD.cust_id)

UPDATE触发器

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值
在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）
OLD中的值全都是只读的，不能更新

1 2	CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state)

使用事务管理

事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行

标识事务的开始

1	START TRANSACTION

ROLLBACK回退SQL语句

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SSELECT * FROM ordertotals;

使用COMMIT

START TRANSACTION
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

使用保留点

1	SAVEPOINT delete1;

1	ROLLBACK TO delete1;