MySQL基础

第3章使用MySQL
- 入门命令
第4章检索数据
- 检索单列
- 检索多列
- 检索所有列
- 检索不同的行
- 限制结果
第5章排序检索
- 排序数据
- 按多个列排序
- 指定排序方向
- order by 和 limit 组合使用
第6章过滤数据
- 检查单个值
- 不匹配检查
- 范围值检查
- 空值检查
第7章数据过滤
- 组合where子句
- in操作符
- not操作符
第8章使用通配符进行过滤
- %通配符
- _通配符
第9章用正则表达式进行搜索
- 基本字符匹配
- 进行OR匹配
- 匹配几个字符之一
- 匹配范围
- 匹配特殊字符
- 匹配多个实例
- 定位符
第10章创建可计算字段
- 拼接字段
- 使用别名
- 执行算数计算
第11章使用数据处理函数
- 文本处理函数
- 日期和时间处理函数
- 数值处理函数
第12章汇总数据
- 聚集函数
- 聚集不同值
- 组合聚集函数
第13章分组数据
- 过滤分组
- select子句顺序
第14章使用子查询
- 利用子查询进行过滤
第15章联结表
- 创建联结
第16章创建高级联结
- 使用表别名
- 使用不同的联结
  - 自联结
  - 自然联结
- 外部联结
- 使用带聚集函数的联结
- 使用联结和联结的条件
第17章组合查询
- 创建组合查询
第18章全文本搜索
- 使用全文本搜索
  - 进行全文本搜索
  - 使用查询扩展
第19章插入数据
- 插入完整的行
- insert更安全的写法
- 插入多个行
- 插入检索出的数据
第20章更新和删除数据
- 更新数据
- 删除数据
第21章创建和操纵表
第22章使用视图
- 利用视图简化复杂的联结
- 用视图重新格式化检索出的数据
- 用视图过滤不想要的数据
- 使用视图与计算字段
第23章使用存储过程
- 创建存储过程
第24章使用游标
第25章使用触发器
- 创建触发器
第26章管理事务处理
第28章安全管理
- 管理用户
- 更改密码

第3章使用MySQL

入门命令

# 返回表的每一个字段	
mysql> show columns from customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

# 还可以使用describe语句
mysql> describe customers;
+--------------+-----------+------+-----+---------+----------------+
| Field        | Type      | Null | Key | Default | Extra          |
+--------------+-----------+------+-----+---------+----------------+
| cust_id      | int(11)   | NO   | PRI | NULL    | auto_increment |
| cust_name    | char(50)  | NO   |     | NULL    |                |
| cust_address | char(50)  | YES  |     | NULL    |                |
| cust_city    | char(50)  | YES  |     | NULL    |                |
| cust_state   | char(5)   | YES  |     | NULL    |                |
| cust_zip     | char(10)  | YES  |     | NULL    |                |
| cust_country | char(50)  | YES  |     | NULL    |                |
| cust_contact | char(50)  | YES  |     | NULL    |                |
| cust_email   | char(255) | YES  |     | NULL    |                |
+--------------+-----------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

# 显示创建数据库的语句
mysql> show create database sys;
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| sys      | CREATE DATABASE `sys` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)



# 显示创建数据库表的语句
mysql> show create table customers;
+-----------+----+
| Table     | Create Table                                             
+-----------+----+
| customers | CREATE TABLE `customers` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  `cust_city` char(50) DEFAULT NULL,
  `cust_state` char(5) DEFAULT NULL,
  `cust_zip` char(10) DEFAULT NULL,
  `cust_country` char(50) DEFAULT NULL,
  `cust_contact` char(50) DEFAULT NULL,
  `cust_email` char(255) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10006 DEFAULT CHARSET=utf8 
+-----------+----+
1 row in set (0.00 sec)

第4章检索数据

检索单列

mysql> select prod_name from products;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Detonator      |
| Bird seed      |
| Carrots        |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+
14 rows in set (0.00 sec)

检索多列

mysql> select prod_id, prod_name, prod_price from products;
+---------+----------------+------------+
| prod_id | prod_name      | prod_price |
+---------+----------------+------------+
| ANV01   | .5 ton anvil   |       5.99 |
| ANV02   | 1 ton anvil    |       9.99 |
| ANV03   | 2 ton anvil    |      14.99 |
| DTNTR   | Detonator      |      13.00 |
| FB      | Bird seed      |      10.00 |
| FC      | Carrots        |       2.50 |
| FU1     | Fuses          |       3.42 |
| JP1000  | JetPack 1000   |      35.00 |
| JP2000  | JetPack 2000   |      55.00 |
| OL1     | Oil can        |       8.99 |
| SAFE    | Safe           |      50.00 |
| SLING   | Sling          |       4.49 |
| TNT1    | TNT (1 stick)  |       2.50 |
| TNT2    | TNT (5 sticks) |      10.00 |
+---------+----------------+------------+
14 rows in set (0.00 sec)

检索所有列

mysql> select * from products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.00 sec)

检索不同的行

mysql> select distinct vend_id from products;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+
4 rows in set (0.00 sec)

限制结果

# 从第0行开始，显示3行
mysql> select distinct vend_id from products limit 3;
# 或者 select distinct vend_id from products limit 0, 3;
# 或者 select distinct vend_id from products limit 3 offset 0;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
+---------+
3 rows in set (0.00 sec)

第5章排序检索

排序数据

mysql> select prod_name from products order by prod_name;
+----------------+
| prod_name      |
+----------------+
| .5 ton anvil   |
| 1 ton anvil    |
| 2 ton anvil    |
| Bird seed      |
| Carrots        |
| Detonator      |
| Fuses          |
| JetPack 1000   |
| JetPack 2000   |
| Oil can        |
| Safe           |
| Sling          |
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

按多个列排序

mysql> select prod_id, prod_price, prod_name from products order by prod_price, prod_name;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
| FU1     |       3.42 | Fuses          |
| SLING   |       4.49 | Sling          |
| ANV01   |       5.99 | .5 ton anvil   |
| OL1     |       8.99 | Oil can        |
| ANV02   |       9.99 | 1 ton anvil    |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| DTNTR   |      13.00 | Detonator      |
| ANV03   |      14.99 | 2 ton anvil    |
| JP1000  |      35.00 | JetPack 1000   |
| SAFE    |      50.00 | Safe           |
| JP2000  |      55.00 | JetPack 2000   |
+---------+------------+----------------+
14 rows in set (0.00 sec)

指定排序方向

mysql> select prod_id, prod_price, prod_name from products order by prod_price desc;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| JP2000  |      55.00 | JetPack 2000   |
| SAFE    |      50.00 | Safe           |
| JP1000  |      35.00 | JetPack 1000   |
| ANV03   |      14.99 | 2 ton anvil    |
| DTNTR   |      13.00 | Detonator      |
| FB      |      10.00 | Bird seed      |
| TNT2    |      10.00 | TNT (5 sticks) |
| ANV02   |       9.99 | 1 ton anvil    |
| OL1     |       8.99 | Oil can        |
| ANV01   |       5.99 | .5 ton anvil   |
| SLING   |       4.49 | Sling          |
| FU1     |       3.42 | Fuses          |
| FC      |       2.50 | Carrots        |
| TNT1    |       2.50 | TNT (1 stick)  |
+---------+------------+----------------+
14 rows in set (0.00 sec)

order by 和 limit 组合使用

mysql> select prod_id, prod_price, prod_name from products order by prod_price desc limit 3 offset 0;
+---------+------------+--------------+
| prod_id | prod_price | prod_name    |
+---------+------------+--------------+
| JP2000  |      55.00 | JetPack 2000 |
| SAFE    |      50.00 | Safe         |
| JP1000  |      35.00 | JetPack 1000 |
+---------+------------+--------------+
3 rows in set (0.00 sec)

第6章过滤数据

检查单个值

mysql> select prod_name, prod_price from products where prod_name='fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+
1 row in set (0.00 sec)

不匹配检查

mysql> select vend_id, prod_name from products where vend_id <> 1003;
+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1001 | .5 ton anvil |
|    1001 | 1 ton anvil  |
|    1001 | 2 ton anvil  |
|    1002 | Fuses        |
|    1005 | JetPack 1000 |
|    1005 | JetPack 2000 |
|    1002 | Oil can      |
+---------+--------------+
7 rows in set (0.00 sec)

范围值检查

mysql> select prod_price, prod_name from products where prod_price between 5 and 10;
+------------+----------------+
| prod_price | prod_name      |
+------------+----------------+
|       5.99 | .5 ton anvil   |
|       9.99 | 1 ton anvil    |
|      10.00 | Bird seed      |
|       8.99 | Oil can        |
|      10.00 | TNT (5 sticks) |
+------------+----------------+
5 rows in set (0.00 sec)

空值检查

mysql> select cust_name, cust_email from customers where cust_email is null;
+-------------+------------+
| cust_name   | cust_email |
+-------------+------------+
| Mouse House | NULL       |
| E Fudd      | NULL       |
+-------------+------------+
2 rows in set (0.00 sec)

第7章数据过滤

组合where子句

and操作符

mysql> select prod_name, vend_id, prod_price from products where vend_id = 1003 and prod_price < 5;
+---------------+---------+------------+
| prod_name     | vend_id | prod_price |
+---------------+---------+------------+
| Carrots       |    1003 |       2.50 |
| Sling         |    1003 |       4.49 |
| TNT (1 stick) |    1003 |       2.50 |
+---------------+---------+------------+
3 rows in set (0.01 sec)

or操作符

mysql> select prod_name, vend_id, prod_price from products where vend_id = 1003 or prod_price < 5;
+----------------+---------+------------+
| prod_name      | vend_id | prod_price |
+----------------+---------+------------+
| Detonator      |    1003 |      13.00 |
| Bird seed      |    1003 |      10.00 |
| Carrots        |    1003 |       2.50 |
| Fuses          |    1002 |       3.42 |
| Safe           |    1003 |      50.00 |
| Sling          |    1003 |       4.49 |
| TNT (1 stick)  |    1003 |       2.50 |
| TNT (5 sticks) |    1003 |      10.00 |
+----------------+---------+------------+
8 rows in set (0.00 sec)

计算次序

# and 优先级高于or
# 找出vend_id为1002或1003，并且prod_id大于等于10的产品
mysql> select prod_name, vend_id, prod_price from products where (vend_id = 1003 or vend_id = 1002) and prod_price >= 10;
+----------------+---------+------------+
| prod_name      | vend_id | prod_price |
+----------------+---------+------------+
| Detonator      |    1003 |      13.00 |
| Bird seed      |    1003 |      10.00 |
| Safe           |    1003 |      50.00 |
| TNT (5 sticks) |    1003 |      10.00 |
+----------------+---------+------------+
4 rows in set (0.00 sec)

in操作符

mysql> select prod_name, vend_id, prod_price from products where vend_id in (1002, 1003) order by prod_price desc;
+----------------+---------+------------+
| prod_name      | vend_id | prod_price |
+----------------+---------+------------+
| Safe           |    1003 |      50.00 |
| Detonator      |    1003 |      13.00 |
| Bird seed      |    1003 |      10.00 |
| TNT (5 sticks) |    1003 |      10.00 |
| Oil can        |    1002 |       8.99 |
| Sling          |    1003 |       4.49 |
| Fuses          |    1002 |       3.42 |
| Carrots        |    1003 |       2.50 |
| TNT (1 stick)  |    1003 |       2.50 |
+----------------+---------+------------+
9 rows in set (0.00 sec)

not操作符

mysql> select prod_name, vend_id, prod_price from products where vend_id not in (1002, 1003) order by prod_price desc;
+--------------+---------+------------+
| prod_name    | vend_id | prod_price |
+--------------+---------+------------+
| JetPack 2000 |    1005 |      55.00 |
| JetPack 1000 |    1005 |      35.00 |
| 2 ton anvil  |    1001 |      14.99 |
| 1 ton anvil  |    1001 |       9.99 |
| .5 ton anvil |    1001 |       5.99 |
+--------------+---------+------------+
5 rows in set (0.00 sec)

第8章使用通配符进行过滤

%通配符

# %表示任何字符出现任意次数【0、1、多】次
mysql> select prod_name, prod_price from products where prod_name like 'jet%';
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| JetPack 1000 |      35.00 |
| JetPack 2000 |      55.00 |
+--------------+------------+
2 rows in set (0.00 sec)

_通配符

# _通配符和%一样，只是_只匹配一个字符而不是多个字符
mysql> select prod_id, prod_name, prod_price from products where prod_name like '_ ton anvil';
+---------+-------------+------------+
| prod_id | prod_name   | prod_price |
+---------+-------------+------------+
| ANV02   | 1 ton anvil |       9.99 |
| ANV03   | 2 ton anvil |      14.99 |
+---------+-------------+------------+

第9章用正则表达式进行搜索

基本字符匹配

mysql> select prod_name, prod_price from products where prod_name regexp '1000';
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| JetPack 1000 |      35.00 |
+--------------+------------+
1 row in set (0.00 sec)

mysql> select prod_name, prod_price from products where prod_name regexp '.000' order by prod_price desc;
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| JetPack 2000 |      55.00 |
| JetPack 1000 |      35.00 |
+--------------+------------+
2 rows in set (0.00 sec)

进行OR匹配

mysql> select prod_name, prod_price from products where prod_name regexp '1000|2000' order by prod_price desc;
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| JetPack 2000 |      55.00 |
| JetPack 1000 |      35.00 |
+--------------+------------+
2 rows in set (0.00 sec)

匹配几个字符之一

mysql> select prod_name, prod_price from products where prod_name regexp '[123] ton' order by prod_price desc;
+-------------+------------+
| prod_name   | prod_price |
+-------------+------------+
| 2 ton anvil |      14.99 |
| 1 ton anvil |       9.99 |
+-------------+------------+
2 rows in set (0.00 sec)

匹配范围

mysql> select prod_name, prod_price from products where prod_name regexp '[1-5] ton' order by prod_price desc;
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| 2 ton anvil  |      14.99 |
| 1 ton anvil  |       9.99 |
| .5 ton anvil |       5.99 |
+--------------+------------+
3 rows in set (0.00 sec)

匹配特殊字符

mysql> select prod_name, prod_price from products where prod_name regexp '\\.';
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| .5 ton anvil |       5.99 |
+--------------+------------+
1 row in set (0.00 sec)

匹配多个实例

mysql> select prod_name, prod_price from products where prod_name regexp '\\([0-9] sticks?\\)';
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
2 rows in set (0.00 sec)

定位符

mysql> select prod_name, prod_price from products where prod_name regexp '^[0-9\\.]';
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| .5 ton anvil |       5.99 |
| 1 ton anvil  |       9.99 |
| 2 ton anvil  |      14.99 |
+--------------+------------+
3 rows in set (0.00 sec)

第10章创建可计算字段

拼接字段

mysql> select Concat(vend_name, ' (', vend_country, ')') from vendors order by vend_name;
+--------------------------------------------+
| Concat(vend_name, ' (', vend_country, ')') |
+--------------------------------------------+
| ACME (USA)                                 |
| Anvils R Us (USA)                          |
| Furball Inc. (USA)                         |
| Jet Set (England)                          |
| Jouets Et Ours (France)                    |
| LT Supplies (USA)                          |
+--------------------------------------------+
6 rows in set (0.00 sec)

# Trim函数 MySQL除了支持RTrim()（正如刚才所见，它去掉
# 串右边的空格），还支持LTrim()（去掉串左边的空格）以及
# Trim()（去掉串左右两边的空格）。

使用别名

mysql> select Concat(vend_name, ' (', vend_country, ')') as vend_title from vendors order by vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

执行算数计算

mysql> select prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems where order_num='20005';
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_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)

第11章使用数据处理函数

文本处理函数

mysql> select prod_name, Upper(prod_name), prod_price from products where prod_name regexp '.000';
+--------------+------------------+------------+
| prod_name    | Upper(prod_name) | prod_price |
+--------------+------------------+------------+
| JetPack 1000 | JETPACK 1000     |      35.00 |
| JetPack 2000 | JETPACK 2000     |      55.00 |
+--------------+------------------+------------+
2 rows in set (0.00 sec)

日期和时间处理函数

mysql> select order_date, cust_id from orders where Date(order_date) = '2005-09-01';
+---------------------+---------+
| order_date          | cust_id |
+---------------------+---------+
| 2005-09-01 00:00:00 |   10001 |
+---------------------+---------+
1 row in set (0.00 sec)

mysql> select order_date, cust_id from orders where Year(order_date) = '2005' and Month(order_date) = '09';
+---------------------+---------+
| order_date          | cust_id |
+---------------------+---------+
| 2005-09-01 00:00:00 |   10001 |
| 2005-09-12 00:00:00 |   10003 |
| 2005-09-30 00:00:00 |   10004 |
+---------------------+---------+
3 rows in set (0.00 sec)

数值处理函数

第12章汇总数据

聚集函数

AVG()

mysql> select AVG(prod_price) as avg_price from products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.01 sec)

mysql> select AVG(prod_price) as avg_price from products where vend_id = '1003';
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.01 sec)

COUNT()

mysql> select COUNT(*) as num_cust from customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> select COUNT(cust_email) as num_cust from customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

MAX()

mysql> select MAX(prod_price) as max_price from products;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+
1 row in set (0.00 sec)

MIN()

mysql> select MIN(prod_price) as max_price from products;
+-----------+
| max_price |
+-----------+
|      2.50 |
+-----------+
1 row in set (0.00 sec)

SUM()

mysql> select SUM(quantity) as ieems_orders from orderitems where order_num = '20005';
+--------------+
| ieems_orders |
+--------------+
|           19 |
+--------------+
1 row in set (0.00 sec)

聚集不同值

以上5个聚集函数都可以如下使用：
对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认
行为）；
只包含不同的值，指定DISTINCT参数。

mysql> select AVG(distinct prod_price) as avg_price from products where vend_id = '1003';
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.00 sec)

组合聚集函数

mysql> select COUNT(*) as num_items, MIN(prod_price) as min_price, MAX(prod_price) as max_price, AVG(prod_price) as avg_price from products;
+-----------+-----------+-----------+-----------+
| num_items | min_price | max_price | avg_price |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)

第13章分组数据

mysql> select vend_id, COUNT(*) as num_prods from products group by vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.00 sec)

mysql> select vend_id, COUNT(*) as num_prods from products group by vend_id with rollup;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+
5 rows in set (0.00 sec)

在具体使用GROUP BY子句前，需要知道一些重要的规定。

 GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，

为数据分组提供更细致的控制。

 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上

进行汇总。换句话说，在建立分组时，指定的所有列都一起计算

（所以不能从个别的列取回数据）。

 GROUP BY子句中列出的每个列都必须是检索列或有效的表达式

（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在

GROUP BY子句中指定相同的表达式。不能使用别名。

 除聚集计算语句外， SELECT语句中的每个列都必须在GROUP BY子

句中给出。

 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列

中有多行NULL值，它们将分为一组。

 GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前。

过滤分组

mysql> select vend_id, COUNT(*) as num_prods from products group by vend_id having COUNT(*) >=3;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1003 |         7 |
+---------+-----------+
2 rows in set (0.00 sec)

mysql> 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 |
+---------+-----------+
2 rows in set (0.00 sec)

mysql> select order_num, SUM(quantity*item_price) as ordertotal
    -> from orderitems
    -> group by order_num
    -> having SUM(quantity*item_price) >= 50;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
+-----------+------------+
4 rows in set (0.00 sec)

mysql> select order_num, SUM(quantity*item_price) as ordertotal
    -> from orderitems
    -> group by order_num
    -> having SUM(quantity*item_price) >= 50
    -> order by ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.00 sec)

select子句顺序

第14章使用子查询

利用子查询进行过滤

mysql> select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.00 sec)

mysql> select cust_id, 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'));
+---------+--------------+
| cust_id | cust_contact |
+---------+--------------+
|   10001 | Y Lee        |
|   10004 | Y Sam        |
+---------+--------------+
2 rows in set (0.00 sec)

(1) 从customers表中检索客户列表。

(2) 对于检索出的每个客户，统计其在orders表中的订单数目。

mysql> select cust_name,
    -> cust_state,
    -> (select COUNT(*)
    ->  from orders
    ->  where orders.cust_id = customers.cust_id) as orders
    -> from customers
    -> order by cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+
5 rows in set (0.00 sec)

第15章联结表

创建联结

mysql> select vend_name, prod_id, prod_price
    -> from vendors, products
    -> where vendors.vend_id = products.vend_id
    -> order by vend_name, prod_name;
+-------------+---------+------------+
| vend_name   | prod_id | prod_price |
+-------------+---------+------------+
| ACME        | FB      |      10.00 |
| ACME        | FC      |       2.50 |
| ACME        | DTNTR   |      13.00 |
| ACME        | SAFE    |      50.00 |
| ACME        | SLING   |       4.49 |
| ACME        | TNT1    |       2.50 |
| ACME        | TNT2    |      10.00 |
| Anvils R Us | ANV01   |       5.99 |
| Anvils R Us | ANV02   |       9.99 |
| Anvils R Us | ANV03   |      14.99 |
| Jet Set     | JP1000  |      35.00 |
| Jet Set     | JP2000  |      55.00 |
| LT Supplies | FU1     |       3.42 |
| LT Supplies | OL1     |       8.99 |
+-------------+---------+------------+
14 rows in set (0.00 sec)

mysql> select vend_name, prod_id, prod_price
    -> from vendors inner join products
    -> on vendors.vend_id = products.vend_id
    -> order by vend_name, prod_name;
+-------------+---------+------------+
| vend_name   | prod_id | prod_price |
+-------------+---------+------------+
| ACME        | FB      |      10.00 |
| ACME        | FC      |       2.50 |
| ACME        | DTNTR   |      13.00 |
| ACME        | SAFE    |      50.00 |
| ACME        | SLING   |       4.49 |
| ACME        | TNT1    |       2.50 |
| ACME        | TNT2    |      10.00 |
| Anvils R Us | ANV01   |       5.99 |
| Anvils R Us | ANV02   |       9.99 |
| Anvils R Us | ANV03   |      14.99 |
| Jet Set     | JP1000  |      35.00 |
| Jet Set     | JP2000  |      55.00 |
| LT Supplies | FU1     |       3.42 |
| LT Supplies | OL1     |       8.99 |
+-------------+---------+------------+
14 rows in set (0.00 sec)

mysql> 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';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

第16章创建高级联结

使用表别名

# 使用列别名
mysql> select Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') as
    -> vend_title
    -> from vendors
    -> order by vend_name;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

# 使用表别名
mysql> 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';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

使用不同的联结

自联结

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物

品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到

生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

# 使用子查询
mysql> select prod_id, prod_name
    -> from products
    -> where vend_id = (select vend_id
    ->                  from products
    ->                  where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)

# 使用自联结
mysql> 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';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)

自然联结

mysql> 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 oi.prod_id = 'FB';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email
     | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.00 sec)

外部联结

# 内连接
# 检索所有客户及其订单
mysql> select customers.cust_id, orders.order_num
    -> from customers inner join orders
    -> on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

# 外部联结
# 包含那些没有订单的客户
mysql> select customers.cust_id, orders.order_num
    -> from customers left outer join orders
    -> on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)

使用带聚集函数的联结

# 检索所有客户及每个客户所下的订单数
mysql> 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 |
+----------------+---------+---------+
4 rows in set (0.00 sec)

# 包含没有任何订单的客户
mysql> 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 |
+----------------+---------+---------+
5 rows in set (0.00 sec)

使用联结和联结的条件

在总结关于联结的这两章前，有必要汇总一下关于联结及其使用的

某些要点。

 注意所使用的联结类型。一般我们使用内部联结，但使用外部联

结也是有效的。

 保证使用正确的联结条件，否则将返回不正确的数据。

 应该总是提供联结条件，否则会得出笛卡儿积。

 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同

的联结类型。虽然这样做是合法的，一般也很有用，但应该在一

起测试它们前，分别测试每个联结。这将使故障排除更为简单。

第17章组合查询

创建组合查询

# where查询
mysql> select vend_id, prod_id, prod_price
    -> from products
    -> where prod_price <= 5 or vend_id in (1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)

# union
mysql> 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.00 sec)

UNION与WHERE 本章开始时说过，UNION几乎总是完成与多个

WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成

WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全

部出现（包括重复行），则必须使用UNION ALL而不是WHERE。

# 排序union查询
mysql> 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;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | FC      |       2.50 |
|    1003 | TNT1    |       2.50 |
|    1003 | SLING   |       4.49 |
+---------+---------+------------+
8 rows in set (0.00 sec)

第18章全文本搜索

使用全文本搜索

进行全文本搜索

mysql> select note_text
    -> from productnotes
    -> where Match(note_text) Against('rabbit');
+-----------------------------------------------------------------------------------------------------------------------+
| note_text
|
+-----------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
|
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select note_text, Match(note_text) Against('rabbit') as rank
    -> from productnotes;

使用查询扩展

# 未使用查询扩展
mysql> select note_text
    -> from productnotes
    -> where Match(note_text) Against('anvils');
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text
                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

# 使用扩展
mysql> select note_text
    -> from productnotes
    -> where Match(note_text) Against('anvils' with query expansion);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text
                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. |
| Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.                       |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
| Please note that no returns will be accepted if safe opened using explosives.
                                   |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.
                                   |
| Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.
            |
| Matches not included, recommend purchase of matches or detonator (item DTNTR).
                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

这次返回了7行。第一行包含词anvils，因此等级最高。第二

行与anvils无关，但因为它包含第一行中的两个词（customer

和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它

们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为

第三。第三行确实也没有涉及anvils（按它们的产品名）。

第19章插入数据

插入完整的行

mysql> insert into customers 
values(null, 'Prp E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA',null, null);
Query OK, 1 row affected (0.06 sec)

insert更安全的写法

mysql> insert into customers(cust_name,
    ->                          cust_address,
    ->                          cust_city,
    ->                          cust_state,
    ->                          cust_zip,
    ->                          cust_country,
    ->                          cust_contact,
    ->                          cust_email)
    ->                  values('Pep E. LaPew',
    ->                          '100 main street',
    ->                          'Los Angeles',
    ->                          'CA',
    ->                          '90046',
    ->                          'USA',
    ->                          null,
    ->                          null);
Query OK, 1 row affected (0.04 sec)

插入多个行

其中单条INSERT语句有多组值，每组值用一对圆括号括起来，

用逗号分隔。

插入检索出的数据

insert select

第20章更新和删除数据

更新数据

# id为10005的顾客增加Email
mysql> update customers
    -> set cust_email = 'emler@fudd.com'
    -> where cust_id = '10005';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

删除数据

mysql> delete from customers
    -> where cust_id = '10006';
Query OK, 1 row affected (0.04 sec)

**更快的删除如果想从表中删除所有行，不要使用DELETE。 **

可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更

快（TRUNCATE实际是删除原来的表并重新创建一个表，而不

是逐行删除表中的数据）。

**下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯。 **

 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE

子句的UPDATE或DELETE语句。

 保证每个表都有主键（如果忘记这个内容，请参阅第15章），尽可能

像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。

 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进

行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不

正确。

 使用强制实施引用完整性的数据库（关于这个内容，请参阅第15

章），这样MySQL将不允许删除具有与其他表相关联的数据的行。

第21章创建和操纵表

第22章使用视图

利用视图简化复杂的联结

mysql> 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;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from productcustomers;
+----------------+--------------+---------+
| cust_name      | cust_contact | prod_id |
+----------------+--------------+---------+
| Coyote Inc.    | Y Lee        | ANV01   |
| Coyote Inc.    | Y Lee        | ANV02   |
| Coyote Inc.    | Y Lee        | TNT2    |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | FB      |
| Coyote Inc.    | Y Lee        | OL1     |
| Coyote Inc.    | Y Lee        | SLING   |
| Coyote Inc.    | Y Lee        | ANV03   |
| Wascals        | Jim Jones    | JP2000  |
| Yosemite Place | Y Sam        | TNT2    |
| E Fudd         | E Fudd       | FC      |
+----------------+--------------+---------+
11 rows in set (0.00 sec)

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

mysql> create view vendorlocations as
    -> select Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') as vend_title
    -> from vendors
    -> order by vend_name;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from vendorlocations;
+-------------------------+
| vend_title              |
+-------------------------+
| ACME (USA)              |
| Anvils R Us (USA)       |
| Furball Inc. (USA)      |
| Jet Set (England)       |
| Jouets Et Ours (France) |
| LT Supplies (USA)       |
+-------------------------+
6 rows in set (0.00 sec)

用视图过滤不想要的数据

mysql> create view customeremaillist as
    -> select cust_id, cust_name, cust_email
    -> from customers
    -> where cust_email is not null;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from customeremaillist;
+---------+----------------+---------------------+
| cust_id | cust_name      | cust_email          |
+---------+----------------+---------------------+
|   10001 | Coyote Inc.    | ylee@coyote.com     |
|   10003 | Wascals        | rabbit@wascally.com |
|   10004 | Yosemite Place | sam@yosemite.com    |
|   10005 | E Fudd         | emler@fudd.com      |
+---------+----------------+---------------------+
4 rows in set (0.00 sec)

使用视图与计算字段

mysql> create view orderitemsexpanded as
    -> select order_num, prod_id, quantity, item_price, quantity*item_price as expanded_price
    -> from orderitems;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from orderitemsexpanded
    -> where order_num = '20005';
+-----------+---------+----------+------------+----------------+
| order_num | prod_id | quantity | item_price | expanded_price |
+-----------+---------+----------+------------+----------------+
|     20005 | ANV01   |       10 |       5.99 |          59.90 |
|     20005 | ANV02   |        3 |       9.99 |          29.97 |
|     20005 | TNT2    |        5 |      10.00 |          50.00 |
|     20005 | FB      |        1 |      10.00 |          10.00 |
+-----------+---------+----------+------------+----------------+
4 rows in set (0.00 sec)

第23章使用存储过程

创建存储过程

# 在客户端执行切换分隔符
mysql> delimiter //
mysql> 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//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call productpricing(@pricelow, @pricehigh, @priceavg);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select @pricelow, @pricehigh, @priceavg;
+-----------+------------+-----------+
| @pricelow | @pricehigh | @priceavg |
+-----------+------------+-----------+
|      2.50 |      55.00 |     16.13 |
+-----------+------------+-----------+
1 row in set (0.00 sec)

第24章使用游标

第25章使用触发器

创建触发器

mysql> create trigger newproduct after insert on products
    -> for each row select 'Product added' into @ee;
Query OK, 0 rows affected (0.06 sec)

mysql> select @ee;
+------+
| @ee  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> desc products;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| prod_id    | char(10)     | NO   | PRI | NULL    |       |
| vend_id    | int(11)      | NO   | MUL | NULL    |       |
| prod_name  | char(255)    | NO   |     | NULL    |       |
| prod_price | decimal(8,2) | NO   |     | NULL    |       |
| prod_desc  | text         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> insert products(prod_id, vend_id, prod_name, prod_price)
    ->                                  values('20009', '1002', 'ysxu', 2.56);
Query OK, 1 row affected (0.04 sec)

mysql> select @ee;
+---------------+
| @ee           |
+---------------+
| Product added |
+---------------+
1 row in set (0.00 sec)

第26章管理事务处理

第28章安全管理

管理用户

创建用户账号

create user [用户名] identified by [用户密码]

用户重命名

rename user [旧用户名] to [新用户名]

删除用户账号

drop user [用户名]

设置访问权限

# 查看用户的权限
show grants for [用户名] //完整用户名：'用户名'@'主机'

# 设置访问权限
grant all on *.* to [用户名]
all-所有权限
*.*-所有数据库下所有表

#grant的反操作是revoke

更改密码

set password for [用户名] = Password('密码')
# 设置当前用户的密码
set password = Passwprd('密码')

posted @ 2019-11-06 13:33 Cava425 阅读(173) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Cava425