MySQL必知必会——代码1

表：某种特定类型数据的结构化清单。由多个列组成。表中的数据是按行来存储的。每一行有一个主键，用来唯一标识自己的一列。
表中的任何列都可以作为主键，只要满足以下条件：
1.任意两行都不具有相同的主键值
2.每个行都必须具有一个主键值（主键列不允许NULL值）
主键的一些好习惯：
1.不更新主键列中的值
2.不重用主键列的值
3.不在主键列中使用可能会更改的值。

什么是SQL？
SQL是结构化查询语言，一种专门用来与数据库通信的语言。

SQL的优点：
1.简单易学
2.灵活，可以进行非常复杂和高级的数据库操作
3.不是某个特定数据库提供商专有的语言

连接到数据库需要以下信息：
1.主机名
2.端口
3.一个合法的用户名
4.用户口令

接着是选择数据库：
use 数据库名
输出：Database changed

返回当前选择的数据库内可用表的列表
SHOW TABLES ;

对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、建信息、默认值以及其他信息
SHOW COLUMNS FROM xxx;

用于显示广泛的服务器状态信息
SHOW STATUS ;

分别用来显示创建特定数据库或表的MySQL语句
SHOW CREATE DATABASE和SHOW CREATE TABLE ;

用来显示授予用户（所有用户或特定用户）的安全权限
SHOW GRANTS ;

用来显示服务器错误或警告消息
SHOW ERRORS 和 SHOW WARNINGS ;

从 products 表中检索一个名为prod_name 的列
SELETE prod_name FROM products ;

与前一个例子一样，这条语句使用 SELECT 语句从表 products中选择数据。
SELETE prod_name，pord_id FROM products ;

添加关键字DISTINCT，删除重复行
SELECT DISTINCT prod_name FROM products ;

只显示前面的5行
SELECT DISTINCT prod_name FROM products LIMIT 5 ;

也可指定要开始的行数和显示的行数
注意：LIMIT 1,1 将检索出第二行。
SELECT DISTINCT prod_name FROM products LIMIT 5,5 ;

因为LIMIT 3,4模糊不清，所以从MySQL 5开始，支持此类说法，表示从第三行还是读取4行
SELECT DISTINCT prod_name FROM products LIMIT 4 OFFSET 3 ;

完全限定的列名，当然表名同理
SELECT products.prod_name FROM products ;

将输出的内容排序
SELETE prod_name FROM products
ORDER BY prod_name;

也支持多列排序
SELETE prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name ;

可以升序当然也可以降序，添加关键字DESC
SELETE prod_name FROM products
ORDER BY prod_name DESC;

但是，如果有多个列要排序呢？
SELETE prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC ,prod_name ;
DESC关键字只应用到直接位于其前面的列名。

指定搜索条件,且搜索条件默认不区分大小写。
SELETE prod_price,prod_name
FROM products
WHERE prod_price=2.0 ;

检索5-10范围
SELETE prod_price,prod_name
FROM products
WHERE prod_price BETWEEN 5 AND 10 ;

空值检查，NULL无值，跟0，空字符串或仅仅包含空格不同
SELETE prod_name
FROM products
WHERE prod_name IS NULL;

SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10 ;

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 ;

计算次序:
AND在计算次序中优先级更高。解决办法是可以用圆括号括起来
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10 ;

IN操作符可以用来指定条件范围，合法值由逗号分隔。且完成于OR相同的功能。优点如下：
1.语法更清楚且直观
2.计算的次序更容易管理
3.执行更快
4.可以包含其他SELECT语言，动态建立WHERE子句
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name DESC;

NOT操作符只有一个功能：否定后面跟的任何条件
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name DESC;

LIKE操作符提示MySQL后跟的搜索模式利用通配符匹配而不是直接相同匹配进行比较
%通配符：表示任何字符出现任意次数
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘jet%’ ;
当然，通配符可以在任意位置使用，并且可以有多个,但不能匹配NULL
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘%anvil%’ ;

_通配符：匹配单个字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘_ ton anvil’ ;

通配符很有用，但是要付出更多的搜索时间。如何巧妙的使用通配符？
1.不要过度使用
2.在确实需要使用的时候使用
3.注意使用位置

MySQL正则表达式：
关键字：REGEXP
跟LIKE的区别：LIKE匹配整个列，而REGEXP在列值内进行匹配
如果要区分大小写，应使用BINARY关键字
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘.000’
ORDER BY prod_name ;

|（OR）匹配：严格匹配（计算空格）
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘1000|2000’
ORDER BY prod_name ;

[]匹配特定的字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[123] Ton’
ORDER BY prod_name ;

可使用-来定义一个范围
SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[1-5] Ton’
ORDER BY prod_name ;

匹配特殊字符，必须用\为前导 \f换页 \n换行 \r回车 \t制表 \v纵向制表
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP ‘\.’
ORDER BY vend_name ;

匹配字符类
[: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）

SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘\([0-9] sticks?\)’
ORDER BY prod_name ;

SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘[[:digit:]]{4}’
ORDER BY prod_name ;

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

SELECT prod_name
FROM products
WHERE prod_name REGEXP ‘¹’
ORDER BY prod_name ;

Concat() 拼接字段
SELECT Concat(vend_name,’ (’,vend_country,’)’)
FROM vendors
ORDER BY vend_name ;

RTRIM：删除右侧空格，right trim，同理，LTRIM()去处左边的空格
SELECT Concat(RTRIM(vend_name),’ (’,RTRIM(vend_country),’)’)
FROM vendors
ORDER BY vend_name ;

Concat连接起来的字符串没有列名，客户机没办法引用，可以使用AS关键字赋予别名
SELECT Concat(RTRIM(vend_name),’ (’,RTRIM(vend_country),’)’) AS vend_title
FROM vendors
ORDER BY vend_name ;

orders 表包含收到的所有订单， orderitems 表包含每个订单中的各项物品
SELECT prod_id, quantity, item_price
FROM orderitems
WHERE order_num = 20005 ;

SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005 ;

大多数SQL实现支持以下类型的函数：
1.用于处理文本串(如删除或填充值，转换值为大写或小写)的文本函数
2.用于在数值数据是进行算数操作(如返回绝对值，进行代数运算)的数值函数
3.用于处理日期和时间值并从这些值中提取特定成分(例如，返回两个日期只差，检查日期有效性等)的日期和时间函数
4.返回DBMS正使用的特殊信息(如返回用户登录信息，检查版本细节)的系统函数

UPPER()将文本转换为大写
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name ;

常用的文本处理函数
Left() 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写
LTrim() 去掉串左边的空格
Right() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值，将任何文本串转换为描述其语音表示的字母数字模式的算法
SubString() 返回子串的字符
Upper() 将串转换为大写

Soundex()举例
SELECT cust_name, cust_contact
FROM customers
WHERE SOUNDEX(cust_contact) = SOUNDEX(‘Y Lie’) ;

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

不管是插入或更新表情还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd
如果要的是日期，请使用DATE(),如果数据类型为datetime，则还具有时间值00:00:00，
WHERE order_date='2005-09-01’将会不能匹配 ‘2005-09-01 11:30:00’
SELECT cust_id, order_num
FROM orders
WHERE DATE(order_date) = ‘2005-09-01’ ;

日期范围过滤
SELECT cust_id, order_num
FROM orders
WHERE DATE(order_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’ ;

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() 返回一个角度的正切

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

SELECT AVG(prod_price) AS avg_price
FROM products ;

如果指定列名，则指定列的值为NULL的行被COUNT()函数忽略，但如果使用的是星号()，则不忽略
SELECT COUNT() AS num_cust
FROM customers ;

如果有多行一样的数值，返回最后一行
SELECT MAX(prod_price) AS max_price
FROM products ;

如果有多行一样的数值，返回最前面一行
SELECT MIN(prod_price) AS max_price
FROM products ;

聚集不同值，使用关键字DISTINCT
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003 ;

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM products ;

创建分组： GROUP BY 必须出现在 WHERE 子句之后， ORDER BY 子句之前
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id ;

WITH ROLLUP 关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;

HAVING 和 WHERE 的差别：WHERE 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。
WHERE 排除的行不包括在分组中。这可能会改变计算值，从而影响 HAVING 子句中基于这些值过滤掉的分组。

WHERE过滤所有prod_price>=10的行，然后按vend_id分组，HAVING过滤计数为2或以上的分组
SELECT vend_id, COUNT() AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT() >=2 ;

ORDER BY 与 GROUP BY的区别：
排序产生的输出 分组行。但输出可能不是分组的顺序
任意列都可以使用 只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要 如果与聚集函数一起使用列（或表达式），则必须使用

SELECT order_num, SUM(quantityitem_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantityitem_price) >= 50 ;

SELECT order_num, SUM(quantityitem_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantityitem_price) >= 50
ORDER BY ordertotal ;

子查询
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = ‘TNT2’)) ;
联结语法
SELECT cust_name, cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = ‘TNT2’ ;
联结+别名语法
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = ‘TNT2’ ;

SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name ;

联结：联结是一种机制，用来在一条 SELECT语句中关联表，因此称之为联结。
1.注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
2.保证使用正确的联结条件，否则将返回不正确的数据。
3.应该总是提供联结条件，否则会得出笛卡儿积。
4.在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。
创建联结:
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name ;

笛卡儿积（cartesian product） 由没有联结条件的表关系返回的结果为笛卡儿积

等值联结（equijoin）:基于两个表之间的相等测试。这种联结也称为内部联结
ANSI SQL规范首选 INNER JOIN 语法:
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name ;

自联结：
未使用
SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = ‘DTNTR’) ;
使用
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’ ;

自然联结：
SELECT c.*, o.order_num, o.order_date,
oi.prod_id, oi.quantity, oi.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND oi.order_num = o.order_num
AND prod_id = ‘FB’ ;

外部联结：
在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表
未使用
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id ;
使用
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id ;
SELECT customers.cust_id, orders.order_num
FROM orders RIGHT OUTER JOIN customers
ON orders.cust_id = customers.cust_id ;

聚集函数的联结
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id ;
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id ;

---

1. [:digit:]\. ↩︎