SQL高级教程

1. SQL通配符

通配符通常与 SQL LIKE 操作符一起使用。

通配符	描述
%	代替0个或多个字符
_	代替一个字符
[charlist]	字符列中的任何单一字符
[^charlist] 或 [!charlist]	不在字符列中的任何单一字符

MySQl中使用REGEXP或NOT REGEXP来操作正则表达式

语法：属性名 REGEXP '匹配方式'

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’	book、big
$	匹配文本的结束字符	'st$'	test、resist
.	匹配任何单个字符	'b.t'	bit、bat
*	匹配前面的字符0次或者多次	'f*n'	n、fn、ffn
+	匹配前面的字符1次或者多次	'ba+'	ba、baa
?	匹配前面的字符0次或者1次	'sa?'	s、sa
字符串	匹配包含指定字符的文本	'fa'	fan、afa
[字符集合]	匹配字符集合中的任何一个字符	'[xz]'	包含x或z的字符
[^]	匹配不在括号中的任何字符	'[^abc]'	不含a、b或c的字符串
字符串{n,}	匹配前面的字符串至少n次	'b{2}'	至少两个b
{n,m}	匹配前面的字符串至少n次，至多m次	'b{2,4}'	2-4个b

实例：

SELECT * FROM Websites WHERE url LIKE 'https%';
SELECT * FROM Websites WHERE name LIKE '_oogle';
SELECT * FROM Websites WHERE name LIKE '[!GFs]%'; #非GFs开头
SELECT * FROM Websites WHERE name REGEXP '^[GFs]'; #GFs开头

2. SQL操作符

2.1 LIKE操作符

用于在 WHERE 子句中搜索列中的指定模式。

语法：

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

实例：

SELECT * FROM Websites
WHERE name LIKE '%k';

2.2 IN操作符

允许您在 WHERE 子句中规定多个值。

NOT IN操作符：同理

语法：

SELECT column1, column2, ...
FROM table_name
WHERE column IN (value1, value2, ...);

实例：

SELECT * FROM Websites WHERE name IN ('Google','菜鸟教程');

2.3 BETWEEN操作符

选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

NOT BETWEEN操作符：同理

语法：

SELECT column1, column2, ...
FROM table_name
WHERE column BETWEEN value1 AND value2;

实例：

SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;

2.4 UNION操作符

UNION 操作符合并两个或多个 SELECT 语句的结果。

注意：UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

语法：【UNION 操作符选取不同的值】

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

或【UNION ALL允许重复的值】

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

实例：

SELECT country FROM Websites
UNION 
SELECT country FROM apps ORDER BY country;

3. SQL约束

（1） NOT NULL约束——约束强制列不接受 NULL 值

（2） UNIQUE约束——约束唯一标识数据库表中的每条记录

（3） PRIMARY KEY约束——主键约束【主键特点：唯一，非空，一个表只能有一个主键】

（4） FOREIGN KEY约束——外键约束【一个表中的外键指向另外一个表中的主键】

（5） CHECK约束—— 约束用于限制列中的值的范围

（6） DEFAULT约束——约束用于向列中插入默认值

实例：

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) DEFAULT 'Sandnes',
    UNIQUE(P_Id),
    PRIMARY KEY(P_Id),
    CHECK(P_Id>0)
)

4. 其它

4.1 视图

视图是基于SQL语句的结果集的可视化的表

• 创建视图

语法：

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• 更新视图

语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• 删除视图

语法：DROP VIEW [IF EXISTS] view_name;

4.2 日期

MySQL中内建日期函数

函数	描述
NOW()	返回当前的日期或时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值

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

4.3 NULL值

NULL与0无法比较，它们是不等价的

判断是否为NULL值需要使用 IS NULL 与NOT IS NULL

实例：

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL;

4.4 NULL函数

MySQl中，当值为NULL时转换为0，使用IFNULL或者COALESCE

实例：

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products;
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products;