MySQL必知必会
程序员文章站
2022-07-12 17:51:58
...
文章目录
1. Mysql命令行处理
##登录
mysql -u root -p
use database;
##show 命令 通过执行命令help show查看更多
show databases;
show tables;
show columns from tablename; 等价于 describe tablename;
show statuts;
show create database;
show create table;
show grants;（显示授予用户的安全权限）
show errors;
show warnings; --插入数据脚本 source xx.sql;
2. 检索数据
select 语句
- 检索单个列
SELECT prod_name FROM products;
- 检索多个列
SELECT prod_id,prod_name,prod_price FROM products;
- 检索所有的列
SELECT * FROM products;
说明：不建议使用通配符，表模式可能发生变化以及检索不必要的列会降低检索和应用程序的性能
优点：检索出不必要的列
- 检索不同的行
SELECT DISTINCT vend_id FROM products;
说明：DISTINCT 关键字是针对所有的行，不能针对部分
例如以下的例子，只有当vend_id和prod_price相同时才会过滤
SELECT DISTINCT vend_id,prod_price FROM products;
- 限制返回的行数
带一个值得LIMIT:
SELECT prod_name FROM products LIMIT 5;
带两个值得LIMIT；
SELECT prod_name FROM products LIMIT 5,5;
注意：1）检索出来的第一行为行0不是行1 2）行数不够时返回足够的行
等价语法：
SELECT prod_name FROM products LIMIT 5 OFFSET 5;
- 使用完全限定的表名
限定列
SELECT products.prod_name FROM products;
限定表
SELECT products.prod_name FROM crashcourse.products;
3. 排序检索数据
- 按单个列进行排序
SELECT prod_name FROM products ORDER BY prod_name;
- 按多个列排序:仅在前面列出现相同的结果的情况下才会对另一个进行排序
SELECT prod_id,prod_price,prod_name FROM products
ORDER BY prod_price,prod_name;
- 指定关键字进行排序
关键字：DESC、ASC只对前面的列起作用，其他不变；默认情况下是ASC
如果要使用多个列进行降序排序需要每个列都添加关键字DESC
SELECT prod_id,prod_price,prod_name FROM products
ORDER BY prod_price DESC;
SELECT prod_id,prod_price,prod_name FROM products
ORDER BY prod_price DESC,prod_name;
4. 过滤数据
- 使用where语句,order by 要在其之后
SELECT prod_name,prod_price
FROM products
WHERE prod_price = 2.5;
- 比较操作符
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN | 在指定的两个值之间 |
- 检查单个值 MySQL在执行匹配时默认不区分大小写，所以fuses与Fuses匹配
字符串需要用单引号标注；
SELECT prod_name,prod_price
FROM products
WHERE prod_name ='fuses';
- 不匹配查询 <>或者！=
SELECT vend_id,prod_name
FROM products
WHERE vend_id <> 1003;
- 范围值检查 BETWEEN … AND…指定搜索的范围
SELECT vend_id,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
- 空值检查:在选择出不具有特定值的行时，是不会返回NULL值，因为NULL是未知的，具有特殊的含义不是具体的值。
SELECT prod_name
FROM products
WHERE prod_price IS NULL;
SELECt cust_id
FROM cunstomers
WHERE cust_eamil IS NULL;
5. 数据过滤
- AND操作符:满足所有给定条件的行
SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id =1003 AND prod_price <= 10;
- OR操作符：满足任一条件的行
SELECT prod_name,prod_price
FROM prodects
WHERE vend_id = 1002 OR vend_id =1003;
- 组合使用AND和OR需要注意操作符的优先级，建议使用()
SELECT prod_name,prod_price
FROM products
WHERE(vend_id = 1002 OR vend_id =1003) AND prod_price >= 10;
- IN操作符：WHERE 子句中用来指定要匹配值的清单关键字，功能与OR相当
IN操作符一般比OR操作符清单执行更快
IN的最大优点是可以包含其它的SELECT语句，使得能够更动态地建立WHERE子句。
SELECT prod_name,prod_price
FROM products
WHERE vend_id = 1002 OR vend_id =1003
ORDER BY prod_name;
##等价于
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
- NOT操作符：WHERE 子句中用来否定后跟条件的关键字
MySQL支持使用NOT对IN，EXITSTS，BETWEEN进行取反
SELECT prod_name,prod_price
FROM products
WHERE vend_id NOT IN(1002,1003)
ORDER BY prod_name;
6. 用通配符进行过滤
1）LIKE操作符：LIKE+搜索模式进行匹配
通配符：用来匹配值的一部分的特殊字符
搜索模式：由通配符、字面值或两者组合形成的搜索条件
%通配符：表示字符出现任意次数：>=0字符
以某个字符串开头：需要注意为空格，如果检测‘%jet’时jet后面有空格是搜索不出来的
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'jet%';
使用通配符的字符串是区分大小写的:
jet% 和JET 1002是不匹配的
包含某个字符串
%jet%:ejet、ejets、jets、jet都是符合条件的
以某个字符串结尾：
jet%
注意：NULL，即使是使用‘%’也是无法找出NULL行的。
- 下划线（_）通配符：只匹配单个字符串
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '_ton anvil';
2）使用通配符的技巧
- 不要过度使用通配符
- 除非必要否则不要将通配符至于搜索模式的开头，这样查询是最慢的
- 注意通配符的位置，避免引发不必要的错误
7. 用正则表达式进行搜索
- 基本字符匹配
SELECT prod_name
FROM products
WHERE product_name REGEXP '1000'
ORDER BY prod_name;
- REGEXP和LIKE之间的差别
同：
SELECT prod_name
FROM products
WHERE product_name LIKE '%000'
ORDER BY prod_name;
等价于
SELCT prod_name
FROM products
WHERE product_name REGEXP '.000'
ORDER BY prod_name;
. 和 %是一致的表示任意的字符串
异：
SELECT prod_name
FROM products
WHERE product_name LIKE '1000'
ORDER BY prod_name;
等价于
SELECT prod_name
FROM products
WHERE product_name REGEXP '1000'
ORDER BY prod_name;
LIKE搜索出来的结果为空，而REGEXP搜索出来的结果有一行
LIKE匹配整个列值，而REGEXP可以匹配部分字符串
查询结果如下所示：
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '1000'ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT prod_name FROM products WHERE prod_name LIKE '1000'ORDER BY prod_name;
Empty set (0.00 sec)
REGEXP：默认匹配不区分大小写，添加BINARY则区分大小写
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP 'JEtPACk .000';
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP BINARY 'JEtPACk .000';
Empty set (0.00 sec)
- 进行OR 匹配：使用"|"为正则表达式的OR操作符
mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '1000|2000'6
-> ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.00 sec)
- [ ]匹配几个字符之一，[ ]是另一种形式的OR语句
mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '[123] TON';
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.00 sec)
mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '[1|2|3] TON'
-> ;
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
2 rows in set (0.03 sec)
*****需要注意的是使用[]是标识一个子集，在[]中的字符形成或的关系，如果直接使用
|进行则将应用到整个字符串;
mysql> SELECT prod_name
-> FROM products
-> WHERE prod_name REGEXP '1|2|3 TON';
+---------------+
| prod_name |
+---------------+
| 1 ton anvil |
| 2 ton anvil |
| JetPack 1000 |
| JetPack 2000 |
| TNT (1 stick) |
+---------------+
5 rows in set (0.00 sec)
- 可以使用^ 表示NOT [^123]匹配任何除了1，2，3之外的字符串
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '[^123] TON';
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
+--------------+
1 row in set (0.00 sec)
- 匹配范围
[ ]定义一个集合的范围，使用“- ”可以定义一个范围
[123456789]等价于[1-9]；匹配任意的字母字符[a-z] - 匹配特殊字符
上述提及的“- | [] ."都是正则表达式的特殊字符，如果要匹配它们则需要用”\"两个反斜杠来操作
mysql> SELECT vend_name FROM vendors WHERE vend_name REGEXP '.';
+----------------+
| vend_name |
+----------------+
| Anvils R Us |
| LT Supplies |
| ACME |
| Furball Inc. |
| Jet Set |
| Jouets Et Ours |
+----------------+
6 rows in set (0.02 sec)
mysql> SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.';
+--------------+
| vend_name |
+--------------+
| Furball Inc. |
+--------------+
1 row in set (0.00 sec)
\也用来引用元字符
| 元字符 | 说明 |
|---|---|
| \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:] | 任意小写字母 |
| [:print:] | 任意可打印字符 |
| [:punct:] | 既不在[:alnum:]又不在[:cntrl:]中的任意字符 |
| [:space:] | 包括空格在内的任意空白字符（同[\f\n\r\t\v] |
| [:upper:] | 任意大写字母 |
| [:xdigit:] | 任意16进制的数字 |
- 匹配多个实例
| 元字符 | 说明 |
|---|---|
| * | 0或多个匹配 |
| + | 1个或多个匹配 |
| ？ | 0个或1个匹配 |
| {n} | 指定数目的匹配 |
| {n,} | 不少于指定数目的匹配 |
| {n,m} | 匹配数目的范围（m不超过255） |
s? ：表示s出现0次或1次？
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)';
连续出现4个数字：’[[:digit]]{4}’
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}'
等价于：'[0-9][0-9][0-9][0-9]'
- 匹配位置：前面的例子都是匹配串的任意位置，使用定位符可以匹配表达式的固定位置
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]';
从字符串开始位置匹配：即开头为数字或.的列值
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9\\.]';
找出任意位置的符合正则表达式的列值
+----------------+
| prod_name |
+----------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
| JetPack 1000 |
| JetPack 2000 |
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
^:用在集合里面表示not，用在REGEXP串中表示的是字符串开头
| 元字符 | 说明 |
|---|---|
| ^ | 文本的开始 |
| $ | 文本的结尾 |
| [[:<:]] | 词的开始 |
| [[:>:]] | 词的结尾 |
- 简单的正则表达式测试
正则表达式返回值为：0或1，0表示没有匹配 ，1表示有匹配
+------------------------+
| 'hello' REGEXP '[0-9]' |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
8. 计算字段
计算字段是在运行时在SELECT内创建的
- 拼接字段：如果要将多列的检查结果进行拼接形成字符串，使用Concat()函数
SELECT Concat(vend_name,' (',vend_country,' )')
FROM vendors;
- 去空格：
TrimR（）：去字符串的右边空格
TrimL（）：去字符串的左边空格
Trim （）：去字符串的所有空格
- 别名：AS
SELECT Concat(vend_name,' (',vend_country,' )') AS vend_title
FROM vendors;
- 执行算术计算
操作符：+ - * /
mysql> SELECT prod_id , quantity,item_price,quantity*item_price AS '总价'
FROM orderitems WHERE order_num=20005;
+---------+----------+------------+-------+
| prod_id | quantity | item_price | 总价 |
+---------+----------+------------+-------+
| ANV01 | 10 | 5.99 | 59.90 |
| ANV02 | 3 | 9.99 | 29.97 |
| TNT2 | 5 | 10.00 | 50.00 |
| FB | 1 | 10.00 | 10.00 |
+---------+----------+------------+-------+
4 rows in set (0.00 sec)
- 执行测试计算：SELECT 后可以不加FROM
SELECT Trim('abc ');
SELECT 2*3;
SELECT Now();
9. 使用数据处理函数
- 文本处理函数
| 函数 | 说明 |
|---|---|
| Left() | 返回串左边的字符 |
| Right() | 返回串右边的字符 |
| Length() | 返回串的长度 |
| Locate() | 找出串的一个子串 |
| Lower（） | 将串转为小写 |
| Upper() | 将串转为大写 |
| LTrim() | 去掉左边的空格 |
| RTrim() | 去掉右边的空格 |
| SubString（） | 返回子串的字符 |
| Soundex() | 返回串的SOUNDEX值 |
| Soundex()函数： | 为描述其语音表示的字母数字模式的算法，例子如下 |
SELECT cust_name,cust_contact
FROM customers
WHERE cust_contact = 'Y.Lie';
这里的'Y.Lie'可能是输入错误，可能为Y.Lee
mysql> SELECT cust_name,cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y.Lie');
+-------------+--------------+
| cust_name | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee |
+-------------+--------------+
1 row in set (0.00 sec)
- 日期处理函数
| 函数 | 说明 |
|---|---|
| AddDate() | 增加一个日期 |
| AddTime() | 增加一个时间 |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Now() | 返回日期+时间 |
| Date() | 返回日期+时间中的日期 |
| Time() | 返回日期+时间中的时间 |
| Year() | 返回日期中的年份 |
| Month() | 返回日期中的月份 |
| Day() | 返回日期中的天 |
| Hour() | 返回时间中的时 |
| Minute() | 返回时间中的分 |
| Second() | 返回时间中的秒 |
| DateDiff() | 返回两个日期的差 |
| Date_Add() | 高度灵活的日期运算函数 |
| Date_Format() | 返回一个格式化的日期或时间串参考：https://www.w3school.com.cn/sql/func_date_format.asp |
注意：日期格式尽量使用：yy-mm-dd hh:mm:ss
##基本的日期使用
SELECT cust_id,order_num
FROM orders
WHERE order_date = '2005-09-01';
##这里需要注意的是如果已知order_date是日期则可以使用
##为了避免在不知道列是日期+时间的情况，建议使用Date()函数进行比较
##如果是时间则使用:Time()进行比较
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)='09';
- 数值处理函数
| 函数 | 说明 |
|---|---|
| Abs() | 返回绝对值 |
| Cos() | 返回一个角度的余弦值 |
| Tan() | 返回一个角度的正切值 |
| Sin() | 返回一个角度的正弦 |
| Exp() | 返回一个数的指数值 |
| Mod（） | 返回除操作的余数 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sqrt() | 返回一个数据的平方根 |
10. 汇总数据
- 聚集函数
| 函数 | 说明 | 是否忽略NULL | 数据类型 |
|---|---|---|---|
| Avg() | 返回指定条件或所有列的平均数 | 忽略NULL | 数值类型 |
| Sum() | 返回指定条件或所有列的总和 | 忽略NULL | 数值类型 |
| Max() | 返回指定条件或所有列中的最大值 | 忽略NULL | 数值、日期、文本均可 |
| Min() | 返回指定条件或所有列中的最小值 | 忽略NULL | 数值、日期、文本均可 |
| Count(*) | 返回指定条件或所有列的行数 | 不忽略NULL | |
| Count(column) | 返回对特定列中具有的值进行统计 | 忽略NULL | 注意column |
聚集不同的值：使用上述的函数进行统计时重复值是被计算的，如果要忽略重复值则可以在列名前面使用DISTINCT关键字
SELECT Avg(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
SELECT Avg(prod_price) AS avg_price
FROM products
WHEREvend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
组合聚集函数
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;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
| 14 | 2.50 | 55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
11.分组数据
- 创建分组：GROUP BY
SELECT vend_id,count(*) AS num_prods
FROM products
GROUP BY vend_id;
注意点：1)group by后只能跟搜索列或表达式，不能使用聚集函数
2）select 搜索列除聚集函数必须在group by 的搜索列上
- 过滤分组：HAVING
SELECT vend_id,count(*) AS num_prods
FROM products
GROUP BY vend_id
HAVING COUNT(*)>=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1001 | 3 |
| 1002 | 2 |
| 1003 | 7 |
| 1005 | 2 |
+---------+-----------+
HAVING和WHERE的区别：前者是在分组之后进行的条件筛选
WHERE是在分组之前进行的条件筛选
SELECT vend_id ,COUNT(*) AS num_prods
FROM products
WHERE prod_price>=10
GROUP BY vend_id
HAVING COUNT(*)>=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| 1003 | 4 |
| 1005 | 2 |
+---------+-----------+
- 分组和排序
分组排序按照：group by 后的列
order by ：指定列进行排序
SELECT order_sum,SUM(quantity*item_price) AS odertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_prices)>=50;
ORDER BY ordertotal;
- SELECT 语句的执行顺序
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从中检索数据的表 | 仅在从表中使用该数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出的排序顺序 | 否 |
| LIMIT | 要检索的行数 | 否 |
12.使用子查询
- 利用子查询进行过滤
列出订购物品TNT2的所有客户
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'));
- 作为计算字段使用子查询
显示customers表中每个客户的订单总数
1、查询订单总数
SELECT count(*) AS totalorders
FROM orders
GROUP BY cust_id;
字段只能是唯一值
SELECT cust_name,
cust_state,
(SELECT count(*)
FROM orders
WHERE customers.cust_id=orders.cust_id) AS totalorders
FROM customers
GROUP BY cust_name;
+----------------+------------+-------------+
| cust_name | cust_state | totalorders |
+----------------+------------+-------------+
| Coyote Inc. | MI | 2 |
| E Fudd | IL | 1 |
| Mouse House | OH | 0 |
| Wascals | IN | 1 |
| Yosemite Place | AZ | 1 |
+----------------+------------+-------------+
这里需要进行相关子查询：表名.列名，否则会直接默认为查询表
SELECT cust_name,
cust_state,
(SELECT count(*)
FROM orders
WHERE cust_id=cust_id) AS totalorders
FROM customers
GROUP BY cust_name;
+----------------+------------+-------------+
| cust_name | cust_state | totalorders |
+----------------+------------+-------------+
| Coyote Inc. | MI | 5 |
| E Fudd | IL | 5 |
| Mouse House | OH | 5 |
| Wascals | IN | 5 |
| Yosemite Place | AZ | 5 |
+----------------+------------+-------------+
总结:子查询一般用于WHERE的IN操作中或者是作为计算列
13.联结表
- 维护引用完整性
插入表中外键的值只能是另一个表中主键已存在的值，否则报错 - 创建联结：tablename0,tablename1…等值联结
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;
注意：1）完全限定列名：由于两个表中都存在有相同的列名
2)where 语句的重要性：不指定WHERE语句进行联结返回的就是笛卡尔积
- 内连接；同笛卡尔积一样，不过过滤条件更加明显
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;
- 多表联结
列出订购物品TNT2的所有客户：在上面使用的子查询，现在用多表联结来实现
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orders.order_num = orderitems.order_num
AND prod_id = 'TNT2';
14.创建高级联结
- 使用表别名
列别名可以在字段中使用，并返回客户体中，而表别名只能在查询过程中使用并不返回客户机
表别名好处：缩短SQL语句；允许在单条SELECT语句中多次使用相同的表
表别名使用：where 语句；select 语句；order by 子句及语句的其它部分
- 使用不同类型的联结
自联结 pk 子查询
‘DTNTR’：找出商品为“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';
- 外联结
左连接：保留左边的表 右连接：保留右边的表
##检索所有客户及其订单
SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON cunstomers.cust_id = orders.cust_id;
##这里使用的是左连接，则保留所有的客户，不存在订单的保留订单编号为NULL
##可以通过改变FROM中两个表的位置用RIGHT OUTER JOIN实现相同的功能
- 结合聚集函数使用联结
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;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+
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;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------
- 使用联结和联结条件
使用了联结就应该提供适合的联结条件，否则会得出笛卡尔积
15.组合查询
- 组合查询的使用情况
在单个查询中从不同的表返回类似结构的数据
对单个表执行多个查询，按单个查询返回数据
组合查询和where条件的比较：两者可以完成类似的功能，性能参考具体情况 - 创建组合查询
利用UNION来组合多条SELECT语句
查询价格小于等于5或者供应商为1001或1002的产品
##使用UNION来组合两个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);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
8 rows in set (0.06 sec)
##相当于WHERE语句
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price < =5 OR vend_id IN (1001,1002);
- UNION规则：
UNION必须由两条及其以上的SELECT语句组成，语句之间用UNION分隔；SELECT语句的查询结果必须具有相同的列数、聚集函数、表达式；SELECT 语句的查询结果的列数据类型必须兼容，可以不相同但是可进行隐式转换。
- UNION ALL
UNION的返回结果中将重复的行去掉，而要保留重复的行可以使用UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
9 rows in set (0.02 sec)
- 对UNION的结果集进行排序：ORDER BY只能写在最后一个查询语句中并对结果集整体产生作用
SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002)
ORDER BY vend_id;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1001 | ANV03 | 14.99 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1002 | FU1 | 3.42 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
| 1003 | FC | 2.50 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
+---------+---------+------------+
9 rows in set (0.00 sec)
- UNION的应用：简化WEHERE语句，简化从多个表中检索数据的工作
16.插入数据
- 插入完整的行
##不定义插入的列名，可能导致安全性问题，一旦表的结构发生变化就会使得插入失败
INSERT INTO customers VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA',
NULL,
NULL);
##定义要插入的列名
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_concact,
cust_email)
VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA',
NULL,
NULL);
1）插入完整的行需要给每个插入的列一个具体的值，默认情况下不插入表中的列是默认插入NULL值，如果声明NOT NULL而没有插入值将会插入失败。
2）插入数据需要更新检索，一般会降低性能所以可以添加关键字降低插入的优先级
INSERT LOW_PRIORTY INTO ...
- 插入多个行
## 使用多个INSERT INTO语句插入数据
NSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_concact,
cust_email)
VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA',
NULL,
NULL);
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'11213',
'USA');
##只要插入的数据的列名一致，可以采用插入多组数据
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'11213',
'USA'),
('Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA');
##多个数据之间用逗号，隔开
##使用INSERT INTO插入多个数据比多个INSERT INTO插入数据的性能要高
- 插入检索出的数据:INSERT+SELECT
INSERT INTO ....SELECT....
##将检索出来的数据插入到一个表中
INSERT INTO customers(
cust_concact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
)
SELECT cust_concact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
17.更新数据
- 更新特定的行：指明WHERE条件,不指明则更新全部
##语法：UPDATE tablename SET column = newnum WHERE...
UPDATE customers
SET cust_email= '[email protected]'
WHERE cust_id = 1005;
##更新一行中的多个列，列与列名之间用，隔开 只需要一个SET关键字
UPDATE customers
SET cust_email = ' [email protected]',
cust_name = 'huhu'
WHERE cust_id = 10005;
##可以使用SELECT查询的值来更新列的值
UPDATE customers
SET cust_email = (SELECT cust_email FROM custnw WHERE cust_id = 10005)
WHERE cust_id = 1005;
- 更新出错则会回滚到之前的状态，如果出错要继续更新则需要添加关键字IGNORE
UPDATE IGNORE customers....
- 为了删除某个列的值，可以更新为NULL
UPDATE customers SET cust_email = NULL WHERE cust_id = 1005;
- 删除特定的行：指明WHERE语句则是删除特定条件的行，如果不指明则是删除所有的行
##语法
DELETE FROM tablename WHERE ....
##DELETE 是一行一行的删除数据，使用TRUNCATE删除表的数据的效率更高，因为是直接删除表后重新建一个表
##对于强制实施引用完整性的数据库不能够删除或更新成功
##引用完整性：某一个表中列的值只能引用另一个表的列的值
18.创建+操纵表
- 表创建基础
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,
cust_state char(5) NULL,
cust_zip char(10) NULL,
cust_contact char(50) NULL,
cust_country char(50) NULL,
cust_email char(255) NULL
PRIMARY KEY (cust_id)
)ENGINE = InnoDB;
可以使用IF NOT EXSISTS在一个表不存在时创建它
- 使用NULL值
NULL值时没有值或缺值
一个表的列只有允许NULL值和NOT NULL值
NULL值不等于空字符串’’
-主键：主键值必须唯一
CRAETE TABLE orderitems
(
order_num int NOT NULL,
order_iter int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (order_num,order_item)
)ENGINE = InnoDB;
多个列构成主键，这几个列组成的值必须唯一
主键是唯一的，所以不允许是NULL
- AUTO_INCREMENT:自增列
自增列要为唯一的列，在没新增一行时会自动加1
last_insert_id():获取当前的AUTO_INCREMENT的值
使用自增列要注意如果覆盖了就会从当前的值开始
-使用默认值：DEFAULT
CRAETE TABLE orderitems
(
order_num int NOT NULL,
order_iter int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (order_num,order_item)
)ENGINE = InnoDB;
同大多数的DBMS一样，MySQL不支持使用函数作为默认值，使用常量即可
-
MySQL引擎
InnoDB：一个可靠的事务处理引擎，它不支持全文本搜索
MEMORY：数据存储在内存中，速度很快
MyISAM：一个性能极高的引擎，它支持全文搜索 -
更新表
对已经建好的表进行更改
ALTER TABLE vendors
ADD vend_phone CHAR(20);
ALTER TABLE vendors
DROP vend_phone CAHR(20);
更新表添加外键是常用的
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY(order_num) REFERENCES orders (order_num);
-
删除表
DROP TABLE tablename -
重命名表
RENAME tablename1 TO tablename2;
19.使用视图
视图：不包含表中应该有的任何列或数据，它包含的是一个SQL查询；在使用的时候执行得到的结果，本身是不具备数据的
- 使用视图的好处
重用SQL语句;
简化复杂的SQL操作，可以方便重用不需要注重细节
保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限 - 性能问题
视图本身不具备数据，只有在执行的时候获取数据，如果嵌套了很多查询条件将会降低查询的性能 - 视图的规则和限制
1)必须有足够的权限
2）视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图
3)视图不能索引，也不能有关联的触发器或默认值
4）可以使用ORDER BY 但是会被SELECT 中的OREDER BY覆盖
-使用视图
创建视图：CREATE VIEW
查看创建视图：SHOW CREATE VIEW viewname;
使用DROP删除视图：DROP VIEW viewname;
更新视图：CREATE OR REPLACE VIEW…;
案例分析
##1、简化复杂的SQL语句
CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
使用视图
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
MySQL在执行的过程中将WHERE语句添加到指定的VIEW中
##2、用视图重新格式化检索出的数据
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
为了避免每次都创建相同的联结条件，可以使用CREATE VIEW来解决
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
使用视图
SELECT * FROM vendorlocations;
##3、过滤掉NULL行
查询出不是NULL的行
CRAETE VIEW customeremillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;
直接使用视图检索数据
SELECT * FROM customeremaillist;
##4、使用视图与计算字段
CREATE VIEW orderitemsexpand AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems;
在实际使用查询时简化了计算字段
SELECT * FROM orderitemsexpand WHERE order_num = 2005;
- 视图的更新
1)视图一般用于查询语句
2)如果不能正确地确定被更新的基数据，则不能更新视图
视图中包含以下操作不能进行更新
1）GROUP BY
2）联结
3）子查询
4）并
5）聚集函数（sum(),count(),min()
6)DISTINCT
7)导出列（计算列）
20、使用存储过程
-使用存储过程的理由
通过把处理封装在容易使用的单元，简化操作
提高性能：执行过程比执行单独的SQL语句快
安全性：将创建和执行存储过程分隔开
- 调用存储过程
CALL prod_name(var1,var2,var3)
CALL productprinting(@pricelow,@pricehigh,@priceaverage)
- 创建存储过程
语法：
CREATE PROCEDURE pro_name( var1,var2...)
BEGIN
...
END;
##在命令行下创建存储过程，需要重新定义分隔符来避免出现语法错误
mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_name ()
-> BEGIN
-> SELECT Avg(prod_price) AS priceaverage
-> FROM products;
-> END //
Query OK, 0 rows affected (0.32 sec)
mysql> DELIMITER ;
第一个DELIMITER是定义当前的分隔符为//，第二个DELIMITER是指将分隔符还原为 ;
mysql> CALL pro_name();
+--------------+
| priceaverage |
+--------------+
| 16.133571 |
+--------------+
1 row in set (0.27 sec)
Query OK, 0 rows affected (0.29 sec)
- 检查存储过程
显示创建一个存储过程的语句
SHOW CREATE PROCEDURE pro_d;
显示何时，由谁创建等详细信息
SHOW PROCEDURE STATUS LIKE 'ordertotal'
-
删除存储过程
语法:DROP PROCEDURE IF EXISTS pro_name; -
使用参数
变量：内存中一个特定的位置，用来临时存储数据
MySQL支持的参数类型：IN（输入）;OUT（输出）;INOUT(既能输入又能输出)
##OUT参数的使用
mysql> DELIMITER //
mysql> CREATE PROCEDURE product_name(
-> OUT p1 DECIMAL(8,2),
-> OUT p2 DECIMAL(8,2),
-> OUT p3 DECIMAL(8,2)
-> )
-> BEGIN
-> SELECT Min(prod_price) INTO p1 FROM products;
-> SELECT Max(prod_price) INTO p2 FROM products;
-> SELECT Avg(prod_price) INTO p3 FROM products;
-> END //
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
##调用函数
mysql> CALL product_name(@p1,@p2,@p3);
Query OK, 1 row affected, 1 warning (0.04 sec)
##查看变量
mysql> SELECT @p1,@p2,@p3;
+------+-------+-------+
| @p1 | @p2 | @p3 |
+------+-------+-------+
| 2.50 | 55.00 | 16.13 |
+------+-------+-------+
1 row in set (0.00 sec)
## IN参数的使用
mysql> DELIMITER //
mysql> CREATE PROCEDURE pro_d(
-> IN onnumber INT,
-> OUT ototal DECIMAL(8,2)
-> )
-> BEGIN
-> SELECT Sum(item_price*quantity)
-> FROM orderitems
-> WHERE order_num = onnumber
-> INTO ototal;
-> END //
Query OK, 0 rows affected (0.00 sec)
DELIMITER ;
mysql> CALL pro_d(20005,@total);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
| 149.87 |
+--------+
1 row in set (0.00 sec)
- 复杂高级的存储过程：包含业务规则和智能处理
DELIMITER //
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total ,optionally adding tax'
BEGIN
-- declare :声明局部变量
DECLARE total DECIMAL(8,2);
--declare tax percentage
DECLARE taxrate INT DEFAULT 6;
-- get the order total ：赋予局部变量值
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num = onumber
INTO total;
-- Is this taxable? 根据taxable判断是否要赋税
IF taxable THEN
SELECT total+(total/100*taxrate) INTO total;
END IF;
-- finally save to out variable; 将查询的结果存储到OUT参数
SELECT total INTO ototal;
END //
DELIMITER ;
添加注释：COMMENT;
声明过程的局部变量：DECLARE 变量名 变量数据类型 [DEFAULT];
结构：IF ... THEN ...END IF；
存储到输出变量：SELECT ....INTO var;
##调用
CALL ordertotal(20005,1,@total);
CALL ordertotal(20005,0,@total);
SELECT @total;
21、使用游标
MySQL规定游标只能用于存储过程（函数）
-
游标概述
游标：一个存储在MySQL服务器上的数据库查询，表示被该语句检索出来的结果集
作用：需要在检索出来的行中前进或后退一行或多行； -
游标的使用
- 声明游标:定义SELECT 语句用于获取数据
DECLARE cursor_name CURSOR FOR SELECT columnname FROM tablename;
- 打开游标：在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动
OPEN cursor_name;
- 使用游标获取数据：获取单行数据或循环获取数据
FETCH cursor_name INTO var
- 关闭游标：释放游标使用的所有内部内存和资源；
隐蔽关闭：在END之后，MySQL会自动关闭
CLOSE cursor_name;
综合案例：创建一个表，填充数据为订单编号+带税的合计 （参考20）
存储过程+游标+循环+调用其他存储过程
DELIMITER //
CREATE PROCEDURE prod_01 ()
BEGIN
--declare variable
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);
--declare cursor
DECLARE cursor_order_num CURSOR
FOR SELECT order_num FROM orders;
--declare countinue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
--Create table to store order_num+total
CREATE TABLE IF NOT EXISTS order_total(
order_num INT , order_totals DECIMAL(8,2)
);
--open cursor
OPEN cursor_order_num;
--loop
REPEAT
--get order_num from orders
FETCH cursor_order_num INTO o;
--call order_total procedure
CALL ordertotal(o,1,t);
--insert into table
INSERT INTO order_total(order_num,order_totals) VALUES(o,t);
UNTIL done
END REPEAT;
--close cursor
CLOSE cursor_order_num;
END //
DELIMITER ;
mysql> CALL prod_01();
-> //
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM order_total //
+-----------+--------------+
| order_num | order_totals |
+-----------+--------------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+--------------+
6 rows in set (0.00 sec)
小结
1、DECLARE 顺序：变量；游标；句柄
2、游标的使用：4步骤
3、循环语句的使用
22、触发器
在执行以下操作时自动进行的操作：INSERT UPDATE DELETE
- 创建触发器
- 唯一的触发器名
- 触发器关联的表
- 触发器响应的活动
- 触发器何时执行
注意
1）触发器只支持表，不支持视图
2）一个表最多支持6个触发器BEFORE 、 AFTER
3）触发器失败：BEFORE失败则后面语句不执行；SQL或BEFORE失败则AFTER不执行
样例：
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added'
高版本的MySQL不支持返回一个结果集，可以通过变量将值返回
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added ' INTO @test;
SELECT @test;
- 删除触发器
DROP TRIGGER trigger_name;
- 使用触发器
- INSERT 触发器
1)可以使用NEW虚拟表，访问被插入的行
2)BEFORE INSERT 中NEW中的值也被更新，允许更改被插入的值
3)AUTO_INCREMENT:NEW在INSERT执行之前包含0，在INSERT 执行之后包含新的自动生成值
##在插入的时候自动获取AUTO_INCREMENT的值
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num INTO @test1;
mysql> INSERT INTO orders(order_date,cust_id)
-> VALUES(Now(),10001);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT @test1;
+--------+
| @test1 |
+--------+
| 20010 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT order_num FROM orders;
+-----------+
| order_num |
+-----------+
| 20005 |
| 20009 |
| 20010 |
| 20006 |
| 20007 |
| 20008 |
+-----------+
6 rows in set (0.00 sec)
- DELETE 触发器
1)可以使用OLD虚拟表,删除之前的值；
2）其中OLD的值全部为只读，不能更新
CREATE TRIGGER test_01 BEFORE DELETE ON orders
FOR EACH ROW
--多个语句用BEGIN END包围
BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id)
VALUES(OLD.order_num,OLD.order_date,OLD.cust_id)
END;
如果BEFORE失败表示不能存档，则不会删除，这一点优于AFTER DELETE
- UPDATE 触发器
1)可以使用OLD和NEW两个虚拟表
2)NEW可以更新值
##更新供应商的州为大写
CREATE TRIGGER test_02 BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
总结：触发器不支持CALL()操作，所以只能直接编写相关SQL语句；触发器可用于保证数据的一致性（大小写，格式等）；创建审计跟踪
23、事务处理
-
术语
事务：一组SQL语句
回退：指撤回指定SQL语句的过程
提交：指将未存储的SQL语句结果写入数据库
保留点：指事务处理中设置的临时占位符，可以发布回退 -
控制事务处理
管理事务：将SQL语句逻辑分组，规定何时回退，何时不用回退 -
事务的开始：START TRANSACTION;
-
事务的回滚：ROLLBACK;
用在事务开始之后，回滚SQL语句
事务管理的相关操作：INSERT ;DELETE;UPDATE
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM order_total;
+-----------+--------------+
| order_num | order_totals |
+-----------+--------------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+--------------+
6 rows in set (0.00 sec)
mysql> DELETE FROM order_total;
Query OK, 6 rows affected (0.07 sec)
mysql> SELECT * FROM order_total;
Empty set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM order_total;
+-----------+--------------+
| order_num | order_totals |
+-----------+--------------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+--------------+
6 rows in set (0.00 sec)
- 事务的提交
一般的执行SQL语句都是隐式提及
事务处理块，提交不会隐式地进行
START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
- 事务的部分撤回：SAVEPOINT delete1；
每个保留点都取标识它的唯一名字
ROLLBACK TO delete1;
RELEASE SAVEPOINT;
- 更改默认的提交行为
SET autocommit=0;
autocommit标志决定是否自动提交更改；针对每个连接来说
23、全球化和本地化
- 字符集和校对顺序
字符集：字母和符号的集合
编码：某个字符集的内部表示
校对：为规定字符如何比较的指令 - 使用字符集和校对顺序
1)查看支持的字符集以及字符集的描述和默认校对
SHOW CHARACTER SET;
2)查看校对的完整列表
SHOW COLLATION;
3)查看当前数据库服务器所用的字符集和校对
SHOW VARIABLES LIKE 'character%';
SHWO VARIABLES LIKE 'collation%';
mysql> SHOW VARIABLES LIKE 'character%'
-> ;
+--------------------------+---------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Program Files (x86)\MySQL\MySQL Server 5.5\share\charsets\ |
+--------------------------+---------------------------------------------------------------+
8 rows in set (0.29 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
- 给表指定字符集和校对
CREATE TABLE mytable(
column1 INT,
column2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
- 给列指定字符集和校对
CREATE TABLE mytable
(
column1 INT,
column2 VARCHAR(10),
column3 VARCHAR(20) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
COLLATE可用于ORDER BY;GROUP BY;HAVING;聚集函数；别名
24、安全管理
- 访问控制：给用户提供他们所需的权限，例如创建表，删除表…
- 管理用户
1）查看数据库用户
USE mysql;
SELECT user FROM user;
2)创建用户账户
CREATE USER huhu IDENTIFIED BY 'test';
3)重命名用户
RENAME huhu TO huhu1;
4)删除用户
DROP USER huhu1;
- 设置用户访问权限
1）查看用户权限
mysql> SHOW GRANTS FOR huhu1
-> ;
+------------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'huhu1'@'%' IDENTIFIED BY PASSWORD ... |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
可以看到新建的用户没有权限
2)要授予的权限；被授予访问权限的数据库或表；用户名
允许用户对数据库crashcourse的所有表进行SELECT操作
mysql> GRANT SELECT ON crashcourse.* TO huhu1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR huhu1;
+------------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'huhu1'@'%' IDENTIFIED BY PASSWORD '....' |
| GRANT SELECT ON `crashcourse`.* TO 'huhu1'@'%' |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
##撤销权限
mysql> REVOKE SELECT ON crashcourse.* FROM huhu1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR huhu1;
+------------------------------------------------------------------------------------------------------+
| Grants for [email protected]% |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'huhu1'@'%' IDENTIFIED BY PASSWORD '....' |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- GRANT和REVOKE指定的权限设计的层次
1)整个服务器 GRANT ALL ;REVOKE ALL;
2)整个数据库 ON database.*
3）特定表 ON database.table;
4）特定列
5）特定的存储过程
权限有以下相关
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER
- 更改密码
将密码传递到'Password()函数进行加密'
SET PASSWORD FOR huhu1 = Password('test11');
更多知识参考官方文档：https://dev.mysql.com/doc
上一篇： Effective C++ 条款总结