MySQL必知必会

姊妹篇——Hive必知必会(数据仓库)：https://hiszm.blog.csdn.net/article/details/119907136

第一章：数据库基础

基本概念

• 数据库（database）保存有组织的数据的容器（通常是一个文件或一组文件）。
• 表（table）某种特定类型数据的结构化清单。
• 模式（schema）关于数据库和表的布局及特性的信息。
• 列（column）表中的一个字段。所有表都是由一个或多个列组成的。
• 数据类型（datatype）所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。
• 行（row）表中的一个记录
• 主键（primary key）一一列（或一组列），其值能够唯一区分表中每个行。

我的理解可以将 数据库 比作一个书柜 ，然后里面的表 看作一本本书，每本书的名字是不同的。模式 可以看作你在书柜里面摆放书的方式不同，而列 你可以看作书的每一章，数据类型 比作书的页面，是文字还是插图，行就是书的内容，主键，可以看作是找到一段话的方法，常见的是页码。

什么是SQL

SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写。SQL是一种专门用来与数据库通信的语言。

第二章：MySQL 简介

$ mysql -u root -p
1

-u 用户名，-p 输入密码， -h 主机名， -P 端口，注意此时的大小写。
mysql --help 命令行选项和参数列表

连接到数据库需要：主机名（本地为localhost）、端口（如果使用默认端口3306之外的端口）、合法的用户名、用户口令（如果需要）

---

下载create.sql和populate.sql两个sql脚本文件，其中，create.sql包含创建6个数据库表的MySQL语句，populate.sql包含用来填充这些表的INSERT语句。执行下列操作：

-- 创建数据库
CREATE DATABASE testdb；
-- 使用数据库
-- 必须先使用USE打开数据库，才能读取其中的数据。
USE testdb;
-- 执行sql脚本
1
2
3
4
5
6

以上为准备工作。

create.sql

########################
# Create customers table
########################
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_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL ,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

#########################
# Create orderitems table
#########################
CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item 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;


#####################
# Create orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;

#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;

######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;

###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;


#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-84Rh0Lg7-1628496025724)(https://github.com/Jeanhwea/mysql-crash-course/raw/master/mysql_crash_course_ER_diagram.png)]

populate.sql

##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175

第三章：了解数据库和表

• 使用数据库

  use testdb;
  1

• 显示可用的数据库列表

  SHOW DATABASES;
  1

• 获得一个数据库内的表的列表

  SHOW TABLES;
  1

• 用来显示表列

  SHOW COLUMNS FROM customers;
  DESCRIBE customers;
  1
  2

• 其他SHOW语句

  SHOW STATUS -- 用于显示广泛的服务器状态信息
  SHOW CREATE DATABASE -- 显示创建特定数据库的MySQL语句
  SHOW CREATE TABLE -- 显示创建特定表的语句
  SHOW GRANTS -- 显示授予用户（所有用户或特定用户）的安全权限
  SHOW ERRORS -- 显示服务器错误
  SHOW WARNINGS -- 警告信息
  1
  2
  3
  4
  5
  6

第四章：检索数据

SELECT语句

• 检索单个列

  -- 检索products表中的prod_name列
  SELECT prod_name FROM products;
  1
  2

• 检索多个列

  -- 检索products表中的prod_id，prod_name和prod_price列
  SELECT prod_id, prod_name, prod_price FROM products;
  1
  2

• 检索所有列

  -- 检索products表中的所有列
  SELECT * FROM products;
  1
  2

• 检索不同的行

-- DISTINCT关键字必须直接放在列名的前面，不能部分使用DISTINCT，DISTINCT关键字应用于所有列而不仅是前置它的列。
SELECT DISTINCT vend_id FROM products;
1
2

• 限制结果，指定返回前几行

  -- 返回不多于5行
  SELECT prod_name FROM products LIMIT 5;
  -- 返回从第5行开始的5行
  SELECT prod_name FROM products LIMIT 5,5;
  1
  2
  3
  4

---

检索出来的第一行为行0，因此LIMIT 1,1检索出来的是第二行而不是第一行

MySQL 5 支持LIMIT的另一种替代语法
LIMIT 4 OFFSET 3为从行3开始取4行，同LIMIT 3,4

-- 使用完全限定的表名
SELECT products.prod_name FROM products;
SELECT products.prod_name FROM crashcoures.products;
1
2
3

第五章：排序检索数据

-- 排序数据
SELECT prod_name
FROM products
ORDER BY prod_name;
1
2
3
4

-- 按多个列排序
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
1
2
3
4

对于上述例子中的输出，仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

-- 指定排序方向
-- 默认升序排序，降序使用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;
1
2
3
4
5
6
7
8
9

DESC关键字只应用到直接位于其前面的列名。上例中，只对prod_price列指定DESC，对prod_name列不指定。
升序关键字ASC，可省略

找出一列中最高或最低的值

SELECT prod_proce FROM products
ORDER BY prod_price DESC LIMIT 1;
1
2

给出ORDER BY句子时，应保证位于FROM句子之后，如果使用LIMIT，应位于ORDER BY之后。

order by —— limit

第六章：过滤数据

使用WHERE子句

-- 返回prod_price为2.50的行

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50
1
2
3

WHERE子句操作符

-- 检查单个值
-- 返回prod_name为Fuses的一行（匹配时默认不区分大小写）
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';

-- 列出小于10美元的所有产品
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;

-- 列出小于等于10美元的所有产品
SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;

-- 不匹配检查
-- 列出不是1003的所有产品
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
SELECT vend_id, prod_name FROM products WHERE vend_id ！= 1003;

-- 范围值检查
-- 检索价格在5-10美元之间的所有产品
SELECT prod_name, prod_price FROM products
WHERE prod_price BETWEEN 5 AND 10;

-- 空值检查
-- 返回价格为空的所有产品
SELECT prod_name FROM products WHERE prod_price IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

第七章：数据过滤

-- AND操作符
-- 检索由1003制造且价格小于等于10美元的所有产品的名称和价格
SELECT prod_id, prod_price, prod_name FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

-- OR操作符
-- 检索由1002和1003制造的产品的名称和价格
SELECT prod_name, prod_price FROM products
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操作符
-- 用来指定条件范围，取合法值的由逗号分隔的清单全部在圆括号中。
-- IN比OR执行更快，最大的优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

-- NOT操作符
-- 列出除1002，1003之外所有供应商供应的产品
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

第八章：用通配符进行过滤

LIKE操作符

LIKE指示MYSQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

-- 百分号（%）通配符
-- 表示任何字符出现任意次数
-- 例：找出所有jet起头的产品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';

-- 例：使用多个通配符,匹配任何位置包含anvil的值，不论它之前或之后出现什么字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';

-- 例：找出s起头e结尾的所有产品
SELECT prod_name FROM products WHERE prod_name LIKE 's%e';
1
2
3
4
5
6
7
8
9
10

%可以匹配0个字符，%代表搜索模式中给定位置的0个、1个或多个字符
尾空格可能会干扰通配符，例如，在保存词anvil时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil'将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模 式最后附加一个%。一个更好的办法是使用函数去掉首尾空格。

-- 下划线(_)通配符
-- 只匹配单个字符而不是多个字符
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
1
2
3

使用技巧

• 不要过度使用通配符，如果其他操作符能够达到目的应该使用其他操作符
• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索的开始处。
  把通配符置于搜索模式的开始处搜索起来是最慢的。
• 仔细注意通配符的位置

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

使用MySQL正则表达式

基本字符匹配

-- 例：检索prod_name包含文本1000的所有行
-- REGEXP后所跟的东西作为正则表达式处理
SELECT prod_name FROM products WHERE prod_name REGEXP '1000'
ORDER BY prod_name;

-- `.`表示匹配任意一个字符
SELECT prod_name FROM products WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
1
2
3
4
5
6
7
8

• LIKE和REGEXP的区别：
  LIKE '1000’匹配整个列值，等于’1000’时才会返回相应行，而REGEXP '1000’在列值内进行匹配，如果包含’1000’则会返回相应行。

-- 区分大小写
-- 使用关键字BINARY,例如
WHERE prod_name REGEXP BINARY 'JetPack .000';
1
2
3

进行OR匹配

-- `|`为正则表达式的OR操作符，表示匹配其中之一
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;


-- 可以给出两个以上的OR条件
`1000|2000|3000`
1
2
3
4
5
6
7

匹配几个字符之一

-- `[]`表示匹配[]中的任意一个字符，例如`[123]`是`[1|2|3]`的缩写
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
-- output
+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
+-------------+

-- 和直接使用OR的区别：
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name

-- 匹配的是1 OR 2 OR 3 Ton，应该使用'[1|2|3] Ton'
-- output
+-------------+
| prod_name   |
+-------------+
|1 ton anvil  |
|2 ton anvil  |
|JetPack 1000 |
|JetPack 2000 |
|TNT (1 stick)|
+-------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

字符集合也可以被否定，为否定一个字集，在集合的开始处放置^，例如[^123]匹配除这些字符的任何东西

匹配范围

-- `[0123456789]`可以写成`[0-9]`，其他范围如`[a-z]`
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name

-- output
+-------------+
|  prod_name  |
+-------------+
| .5 ton anvil|
| 1 ton anvil |
| 2 ton anvil |
+-------------+
1
2
3
4
5
6
7
8
9
10
11
12

匹配特殊字符

-- 匹配'.'字符，如果使用
SELECT vend_name FROM vendors WHERE vend_name REGEXP '.'
ORDER BY vend_name;

-- output
+---------------+
|   vend_name   |
+---------------+
| ACME          |
| Anvils R Us   |
| Furball Inc.  |
| Jet Set       |
| Jouets Et Ours|
| LT Supplies   |
+---------------+

-- 因为'.'为匹配任意字符，因此匹配特殊字符，必须用'\\'为前导

SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

-- output
+---------------+
|   vend_name   |
+---------------+
| Furball Inc.  |
+---------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

正则表达式中具有特殊意义的所有字符都要通过这种方式转义
\\也用来引用元字符

为了匹配\本身，需要使用\\\

匹配字符类

匹配多个实例

例：

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name
-- output
+---------------+
|   prod_name   |
+---------------+
| TNT (1 stick) |
| TNT (5 sticks)|
+---------------+

-- '\\('匹配'('
'[0-9]'匹配任意数字
'stick?'匹配'stick'和'sticks'
'\\)'匹配')'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

例：匹配连在一起的4位数字

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
-- output
+---------------+
|   prod_name   |
+---------------+
| JetPack 1000  |
| JetPack 2000  |
+---------------+
-- 也可以写成 '[0-9][0-9][0-9][0-9]'
1
2
3
4
5
6
7
8
9
10

定位符

例：找出以一个数（包括小数点开头）开始的所有产品

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
-- output
+---------------+
|   prod_name   |
+---------------+
| .5 ton anvil  |
| 1 ton anvil   |
| 2 ton anvil   |
+---------------+
1
2
3
4
5
6
7
8
9
10

第十章：创建计算字段

计算字段

应用程序需要的数据需要通过从数据库中检索出转换、计算或格式化过的数据，而不是检索出数据，然后再在客户机应用程序或报告程序中重新格式化。

字段：基本上与列的意思相同，经常互换使用，不过数据库一般称为列，而属于字段通常用在计算字段的连接上。

拼接字段

拼接：将值联结到一起构成单个值

在SELECT语句中，可使用Concat()函数来拼接两个列。Concat()函数需要一个或多个指定的串，各个串之间用逗号分隔。

SELECT Concat(vend_name, ' (',vend_country,')') FROM vendors
ORDER BY vend_name;
#output
+-----------------------------------------+
| Concat(vendname,' (',vend_country,')') |
+-----------------------------------------+
| ACME (USA)                              |
| Anvils R Us (USA)                       |
| Furball Inc. (USA)                      |
| Jet Set (England)                       |
| Jouets Et Ours (France)                 |
| LT Supplies (USA)                       |
+-----------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13

使用 RTrim()函数可以删除右侧多余的空格来整理数据，例：

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;
1
2
3

使用别名

拼接的结果只是一个值，未命名。可以用AS关键字赋予别名

常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它，在原来的名字含混或容易误解时扩充它等等。
别名有时也称为导出列（derived column）

SELECT Concat(RTrim(vend_name),' (',RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
#output
+----------------------------+
| vend_name                  |
+----------------------------+
| ACME (USA)                 |
| Anvils R Us (USA)          |
| Furball Inc. (USA)         |
| Jet Set (England)          |
| Jouets Et Ours (France)    |
| LT Supplies (USA)          |
+----------------------------+
#指示SQL创建一个包含指定计算的名为vend_title的计算字段
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

执行算术计算

例：汇总物品的价格（单价乘以订购数量）

SELECT prod_id,
	   quantity,
       item_price,
       quantity * item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
#output
+---------+----------+------------+----------------+
| 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 |
+---------+----------+------------+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT Now() 利用 Now()函数返回当前日期和时间

第十一章：使用数据处理函数

函数没有SQL的可移植性强

使用函数

大多数SQL实现支持以下类型的函数

• 用于处理文本串的文本函数
• 在数值数据上进行算术操作的数值函数
• 处理日期和时间值并从这些值中提取特定成分的日期和时间函数
• 返回DBMS正是用的特殊信息的系统函数

文本处理函数

常用的文本处理函数

SOUNDEX是一个将任何文本转换为描述其语音表示的字母数字模式的算法，使得能对串进行发音比较而不是字母比较。MySQL提供对SOUNDEX的支持。

例：联系人Y.Lie输入错误为Y.Lee，使用SOUNDEX检索，匹配发音类似于Y.Lie的联系名

SELECT cust_name, cust_contact FROM customers
WHERE Soundex(cust_contact）= Soundex('Y Lie');
#output
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1
2
3
4
5
6
7
8

日期和时间处理函数

MySQL使用的日期格式

日期必须为格式yyyy-mm-dd
支持2位数字的年份，MySQL处理00-69为2000-2069，70-99为1970-1999，但使用4为数字年份更可靠。
例：

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

order_date类型为datetime，样例表中的值全部具有时间值00：00：00，但是如果order_date的值为2005-09-01 11：30：05则上面的WHERE order_date = '2005-09-11’不会检索出这一行，因此必须使用Date()函数。

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

例：检索出2005年9月下的所有订单

SELECT cust_id, order_num FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
#BETWEEN把2005-09-01和2005-09-30定义为一个要匹配的日期范围。
#另一种方法
SELECT cust_id, order_num FROM orders
WHERE Year(roder_date) = 2005 AND Month(order_date) = 9;
1
2
3
4
5
6

数值处理函数

第十二章：汇总数据

AVG()函数

例：返回products表中所有产品的平均价格

SELECT AVG(prod_price) AS avg_price FROM products;
1

例：返回特定供应商所提供产品的平均价格

SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
1
2
3

COUNT()函数

例：返回customer表中客户的总数

SELECT COUNT(*) AS num_cust FROM customers;
1

例：只对具有电子邮件地址的客户计数

SELECT COUNT(cust_email) AS num_cust
FROM customers;
1
2

MAX()函数

例：返回products表中最贵的物品价格

SELECT MAX(prod_price) AS max_price
FROM products;
1
2

对非数值数据使用MAX()
MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。MAX()函数忽略列值为NULL的行。

MIN()函数

例：

SELECT MIN(prod_price) AS min_price FROM products;
1

SUM()函数

返回指定列值的和（总计）
例：检索所订购物品的总数

SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 20005;
1
2
3

例：合计计算值，合计每项物品item_price*quantity，得出订单总金额

SELECT SUM(item_price*quantity) AS total_price
FORM orderitems
WHERE order_num = 20005;
1
2
3

聚集不同值（适用于5.0.3后的版本）

上述五个聚集函数都可以如下使用：

• 对所有的行执行计算，指定ALL参数或不给参数（ALL为默认）
• 只包含不同的值，指定DISTINCT参数

例：

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
1
2
3

注意：如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT）， 否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

组合聚集函数

SELECT语句可根据需要包含多个聚集函数

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;
#output
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.50 | 16.133571 |
+-----------+-----------+-----------+-----------+
1
2
3
4
5
6
7
8
9
10
11

第十三章：分组数据

创建分组

例：根据vend_id分组，对每个分组分别计算总数

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

#output
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12
13

• GROUP BY 子句可以包含任意数目的列，使得能对分组进行嵌套，为数据分组提供更细致的控制
• 如果GROUP BY子句中中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。【详细信息，见下表】

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：

SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
1
2
3

过滤分组

WHERE指定的是行，不是分组，WHERE没有分组的概念

使用HAVING过滤分组

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
#output
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1
2
3
4
5
6
7
8
9
10

WHERE不起作用，因为过滤是基于分组聚集值而不是特定行值的。

例：列出具有2个（含）以上、价格为10（含）以上的产品的供应商

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >=2
#output
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
1
2
3
4
5
6
7
8
9
10
11
12

分组和排序

例：检索总计订单价格大于等于50的订单的订单号和总计订单价格

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertital;
1
2
3
4
5

SELECT子句顺序

SELECT子句及其顺序

上述子句使用时必须遵循该顺序

这个是书写的时候需要注意的，但是实际数据库在运行的时候是这样的吗？

• 先执行from关键字后面的语句，明确数据的来源，它是从哪张表取来的。

• 接着执行where关键字后面的语句，对数据进行筛选。

• 再接着执行group by后面的语句，对数据进行分组分类。

• 然后执行select后面的语句，也就是对处理好的数据，具体要取哪一部分。

• 最后执行order by后面的语句，对最终的结果进行排序。

第十四章：使用子查询

要求4.1以上版本

例：列出订购物品TNT2的所有客户

1. 检索包含物品TNT2的所有订单的编号
2. 检索具有前一步骤列出的订单编号的所有客户的ID
3. 检索前一步骤返回的所有客户ID的客户信息

#(1)
SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';
#output
+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+
#(2)
SELECT cust_id FROM orders WHERE order_num IN (20005,20007);
+-----------+
|  cust_id  |
+-----------+
|     10001 |
|     10004 |
+-----------+

#(1)+(2)
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

#(3)
SELECT clust_name. cust_contact FROM customers WHERE cust_id IN (10001, 10004)

#(1)+(2)+(3)
SELECT cust_name, cust_contact FROM customers
WHERE cust_id IN(SELECT cust_id FROM orders
				 WHERE order_name IN(SELECT order_num FROM orderitems
                 					 WHERE prod_id ='TNT2'));
#output
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

在WHERE子句中使用子查询应保证SELECT语句有与WHERE子句中相同数目的列。

作为计算字段使用子查询

需要显示customers表中每个客户的订单总数，订单与相应的客户ID存储在orders表中

1. 从customers表中检索客户列表
2. 对于检索出的每个客户，统计其在orders表中的订单数目

# 对客户10001的订单进行计数
SELECT COUNT (*) AS orders FROM orders WHERE cust_id = 10001;
# 为了对每个客户执行COUNT(*)计算，应该将COUNT(*)作为一个子查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders
                              WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers ORDER BY cust_name;
1
2
3
4
5
6

相关子查询：涉及外部查询的子查询
在任何时候只要列明可能有多义性，就必须使用这种语法（表明和列名由一个句点分隔）

第十五章：联结表

连结

关系表

例如：两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商应具有唯一的标识，称为主键（primary key）。products表只存储产品信息，除了存储供应商ID之外不存储其他的供应商信息。vendors表的主键又叫products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

例：定义外键

ALTER TABLE orderitems
ADD CONSTRAINT fk_irderitems_orders
FOREIGN KEY (order_num) REFERENCES orders(order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_irderitems_products
FOREIGN KEY (prod_id) REFERENCES products(prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_irderitems_customers
FOREIGN KEY (cust_id) REFERENCES customers(cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_irderitems_vendors
FOREIGN KEY (vend_id) REFERENCES vendors(vend_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在使用关系表时，仅在关系列中插入合法的数据非常重要，如果在products表中插入拥有非法供应商ID（即没有在vendors表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。

创建连结

我们把tableA看作左表，把tableB看成右表，

那么INNER JOIN是选出两张表都存在的记录：

LEFT OUTER JOIN是选出左表存在的记录：

RIGHT OUTER JOIN是选出右表存在的记录：

FULL OUTER JOIN则是选出左右表都存在的记录：

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
3

两个表用WHERE子句联结

笛卡儿积：由没有连结条件的表关系返回的结果为笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

内部连结

等值联结：基于两个表之间的相等测试，也叫内部连结

可以使用另一种语法来明确指定联结的类型

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
1
2
3

FROM 子句的组成部分，以INNER JOIN指定，联结条件用ON子句

联结多个表

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
#显示编号为20005的订单中的物品。订单物品存储在orderitems表中，按每个产品的ID存储。
它引用products表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商
1
2
3
4
5
6
7

例：返回订购产品INT2的客户列表

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';
1
2
3
4
5

第十六章：创建高级联结

使用表别名

SELECT Concat(RTrim(vend_name),'('Rtrim(vend_country),')') AS vend_title
FROM vendors ORDER BY vend_name;
1
2

别名除了用于列名和计算字段外，SQL还允许给表起别名，这样做有两个主要理由：

• 缩短SQL语句
• 允许在单条SELECT语句中多次使用相同的表。

例：

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';
1
2
3
4
5

使用不同类型的联结

自联结

例：如果某物品（ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

#子查询
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
#output
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
#使用自联结
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';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（内部联结）返回所有数据，甚至相同的列多次出现，自然联结排除多次出现，使每个列只返回一次。

自然联结是这样一种联结，其中你只能选择那些唯一的列，这一版是通过使用通配符，对所有其他表的列使用明确的字集来完成的。

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';
1
2
3
4
5

这个例子中，通配符只对第一个表使用所有其他列明确列出，所以没有重复的列被检索出来。

外部联结

有时候需要包含没有关联的那些行

例：需要联结来完成以下工作

• 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户
• 列出所有产品及订购数量，包括没人订购的产品
• 计算平均销售规模，包括那些至今尚未下订单的客户

#内部联结
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;
1
2
3
4
5
6
7
8
9

外部联结语法类似。可以检索所有客户，包括没有订单的客户。

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表。（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表。）上面的例子中从customers中选择所有的行。

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON orders.cust_id = customers.cust_id;
1
2
3

使用带聚集函数的联结

例：检索所有客户及每个客户所下的订单数

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;
1
2
3
4

此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY子句按客户分组数据，因此，函数调用COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回。

聚集函数也可以方便地与其他联结一起使用。例：

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
2
3
4

这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。

使用联结和联结条件

• 注意所使用的联结类型，一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡尔积。
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。应该在一起测试他们前分别测试每个联结。

第十七章：组合查询

组合查询

MySQL允许执行多个查询并将结果作为单个查询结果返回。
两种情况：

• 在单个查询中从不同的表返回类似结构的数据
• 对单个表执行多个查询，按单个查询返回数据

创建组合查询

使用UNION

给出每条SELECT语句，在各条语句之间放上关键字UNION
例：需要价格小于等于5的所有物品的一个列表，并且包含供应商1001和1002生产的所有物品

#单条语句
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5;

SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

#组合上述语句
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);

#等于
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001, 1002);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

UNION规则

• UNION 必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
• UNION中的每个查询必须包含先沟通的列、表达式或聚集函数
• 列数据类型必须兼容：类型不必完全向东，但必须是DBMS可以隐含地转换的类型

包含或取消重复的行

UNION从查询结果集中自动去除了重复的行，如果需要返回所有行，可以使用UNION ALL

对组合查询结果排序

使用ORDER BY子句排序，只能使用一条ORDER BY子句，必须在最后一条SELECT语句之后。

第十八章：全文本搜索

并非所有引擎都支持全文本搜索

mysql 5.6.4之前只有 Myisam支持，5.6.4之后则Myisam和innodb都支持，不过mysql中的全文索引目前只支持英文(不支持中文)

其实根本原因是因为英文检索是用空格来对分词进行分隔，而中文肯定不能用空格来分隔，只能通过语义进行分词，用空格的话是肯定检索不出某些词汇的。

良心的是，在Mysql 5.7版本时，MySQL内置了ngram全文检索插件，用来支持中文分词，但是仅对MyISAM和InnoDB引擎有效。

MyISAM支持，InnoDB不支持

LIKE、正则表达式的限制

• 性能：由于被搜索行不断增加，这些搜索可能非常耗时
• 明确控制：很难明确控制匹配什么和不匹配什么
• 智能化的结果：不能提供一种智能化的选择结果的方法，例如：一个特殊词的搜索将会返回包含该词的所有行而不区分包含单个匹配的行和多个匹配的行。

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。
在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索。

启用全文本搜索支持

一般在创建表时启用全文本搜索
CREATE TABLE语句接收FULLTEXT子句，它给出被索引列的一个逗号分隔的列表
例：

CREATE TABLE productnotes
(
	note_id int NOT NULL AUTO_INCREMENT,
    pord_id char(10) NOT NULL,
    note_date datetime NOT NULL.
    note_text text NULL,
    PRIMARY KEY(note_id),
    FULLTEXT(note_text)
)ENGINE=MyISAM;
1
2
3
4
5
6
7
8
9

可以在创建表时指定FULLTEXT或在稍后指定（所有数据必须立即索引）
不要在导入数据时使用FULLTEXT 应线导入所有数据再修改表，定义FULLTEXT

进行全文本搜索

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

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
1
2
3

传递个Match()的值必须与FULLTEXT()定义中的相同。

除非使用BINARY方式，否则全文本搜索不区分大小写

全文本搜索对结果排序，具有较高等级的行先返回

SELECT note_text,
Match(note_text) Against('rabbit') AS rank
FROM productnotes;
1
2
3

显示所有行及他们的等级

使用扩展查询

（MySQL 4.1.1及更高版本）
例如找出所有提到anvils的注释，和与搜索有关的其他行，即使它们不包含这个词

#不使用扩展查询
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils');
+------------------------------------------------------------------------------+
| note_text                                                                    |
+------------------------------------------------------------------------------+
| Multiple custoer returns, anvils failing to drop fast enough or falling      |
| backwords on purchaser, Recomend that customer considers using havier        |
| anvils.                                                                      |
+------------------------------------------------------------------------------+
#使用扩展查询
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
+------------------------------------------------------------------------------+
| note_text                                                                    |
+------------------------------------------------------------------------------+
| Multiple custoer returns, anvils failing to drop fast enough or falling      |
| backwords on purchaser, Recomend that customer considers using havier        |
| anvils.                                                                      |
| Customer complaint: Sticks not individually wrapped, too easy to mistakenly  |
| detonate all at once. Recommend individual wrapping.                         |
| Customer compliant: Not heavy enouth to generate flying stars around heardof |
| victim. If veing 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 include, recomend purchase of matches or detonator (item DTNTR)  |
+------------------------------------------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

返回7行，第一行包含anvils，因此等级最高，第二行与anvils无关，但包含第一行中的两个次，因此被检索出来。

布尔文本搜索

可以提供如下内容的细节：

• 要匹配的词
• 要排斥的次
• 排列提示
• 表达式分组
• 另外一些内容

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
1
2
3

例：匹配包含heavy但不包含任意以rope开始的词的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
1
2
3

*MySQL4.x版本中使用-ropes而不是-rope **

全文本布尔操作符

例：

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
#匹配包含词rabbit和bait的行。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
#匹配包含rabbit和bait中的至少一个词的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
#匹配rabbit bait而不是匹配两个词

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit <bait' IN BOOLEAN MODE);
#匹配rabbit和carrot，增加前者的等级，降低后者的等级

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
#匹配词safe和combination，降低后者的等级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

全文本搜索的使用说明

• 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为 那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
• MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
• 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。 因此，MySQL规定了一条50%规则，如果一个词出现在50%以上 的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
• 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词 或者不出现，或者至少出现在50%的行中）。
• 忽略词中的单引号。例如，don’t索引为dont。
• 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文 本搜索结果。
• 如前所述，仅在MyISAM数据库引擎中支持全文本搜索。

第十九章：插入数据

INSERT

• 插入完整的行
• 插入行的一部分
• 插入多行
• 插入某些查询的结果

插入完整的行

INSERT INTO Customers
VALUES(NULL,
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angles',
    'CA',
    '90046',
    'USA',
    NULL,
    NULL);
1
2
3
4
5
6
7
8
9
10

语法简单但不安全。更安全的方法为：

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);
#下面的INSERT语句填充所有列（与前面的一样），但以一种不同的次序填充。
#因为给出了列名，所以插入结果仍然正确：
INSERT INTO customers(cust_name,
    cust_contact,
    cust_email,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
VALUES('Pep E. LaPew',
    NULL,
    NULL,
    '100 Main Street',
    'Los Angles',
    'CA',
    '90046',
    'USA');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

不管哪种INSSERT语法，都必须给出VALUES的正确数目，如果不提供列名，则必须给每个表提供一个值。

如果提供列名，则必须对每个列出的列值给出一个值。

列名被明确列出时，可以省略列,如果表的定义允许则可以省略列

• 该列定义为允许NULL值（无值或空值）
• 在表定义中给出默认值。

插入多个行

INSERT INTO customers(cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
VALUES('Pep E. LaPew',
    '100 Main Street'
    'Los Angeles',
    'CA',
    '90046',
    'USA');
INSERT INTO customers(cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
VALUES('M. Martian',
    '42 Galaxy Way'
    'New York',
    'NY',
    '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 Angeles',
    'CA',
    '90046',
    'USA')，

    ('M. Martian',
    '42 Galaxy Way'
    'New York',
    'NY',
    '11213',
    'USA');

#单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

插入检索出的数据

INSERT INTO customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
FROM custnew;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

第二十章：更新和删除数据

更新数据

UPDATE

• 更新表中特定行
• 更新表中所有行
  例：客户10005更新电子邮件

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
1
2
3

例：更新多个列

UPDARTE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
1
2
3
4

在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间 用逗号分隔（最后一列之后不用逗号）。在此例子中，更新客户10005的cust_name和cust_email列。

IGNORE关键字:如果用UPDATE语句更新多行，并且在更新这些 行中的一行或多行时出一个现错误，则整个UPDATE操作被取消 （错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：UPDATE IGNORE customers…

为了删除某列的值，可以设置为NULL

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
1
2
3

删除数据

使用DELETE语句

• 从表中删除特定的行
• 从表中删除所有的行

DELETE FROM customers
WHERE cust_id = 10006;
1
2

更新和删除的指导原则

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

• 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
• 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
• 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

第二十一章：创建和操纵表

创建表

创建表基础

CREATE TABLE

• 新表的名字，在关键字CREATE TABLE之后给出
• 表列的名字和定义，用逗号分隔。
  例：

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_country char(50) NULL,
    cust_contact char(50) NULL,
    cust_email char(255) NULL,
    PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
1
2
3
4
5
6
7
8
9
10
11
12
13

在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。

如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

CREATE TABLE IF NOT EXISTS 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_country char(50) NULL,
    cust_contact char(50) NULL,
    cust_email char(255) NULL,
    PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
1
2
3
4
5
6
7
8
9
10
11
12
13

使用NULL值

每个表列或者是NULL列或者是NOT NULL列，这种状态在创建时由表的定义规定
例：

CREATE TABLE orders
(
    order_num int NOT NULL AUTO_INCREMENT,
    order_date datetime NOT NULL,
    cust_id int NOT NULL,
    PRIMARY KEY (order_num)
) ENGINE-InnoDB;
1
2
3
4
5
6
7

例：混合了NULL和NOT NULL列的表

CREATE TABLE vendors
(
    vend_id int NOT NULL AUTO_INCREMENT,
    vend_name char(50) NOT NULL,
    vend_address char(50) NULL,
    vend_city char(50) NULL,
    vend_state char(5) NULL,
    vend_zip char(10) NULL,
    vend_country char(50) NULL,
    PRIMARY KEY(vend_id)
) ENGINE = InnoDB;
1
2
3
4
5
6
7
8
9
10
11

主键

主键值必须唯一。可以由一个或者多个。

• 如果主键使用单个列，则它的值必须唯一。
• 如果使用多个列，则这些列的组合值必须唯一。
• PRIMARY KEY (order_num, order_item)

例：创建多个列组成的主键

CREATE TABLE orderitems
(
    order_num int NOT NULL,
    order_item 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;
1
2
3
4
5
6
7
8
9

使用AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次 执行一个INSERT操作时，MySQL自动对该列增量（从而才有这个关键字AUTO_INCREMENT），给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。

覆盖AUTO_INCREMENT:如果一个列被指定为AUTO_INCREMENT，则它需要使用特殊的值吗？你可以简单地INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。

后续的增量将开始使用该手工插入的值。

指定默认值

CREATE TABLE orderitems
(
    order_num int NOT NUL,
    order_item 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;
1
2
3
4
5
6
7
8
9

MySQL不允许使用函数作为默认值，只支持常量

引擎类型

• InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；【后面版本已经支持了~】

• MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘） 中，速度很快（特别适合于临时表）；

• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

• 引擎类型可以混用。

  外键不能跨引擎 混用引擎类型有一个大缺陷。

  外键（用于强制实施引用完整性）不能跨引擎，

  即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

使用ALTER TABLE更改表的结构，必须给出以下信息：

• 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
• 所做更改的列表。

例：

ALTER TABLE vendors
ADD vend_phone CHAR(20);
1
2

例：删除刚增加的列

ALTER TABLE vendors
DROP COLUMN vend_phone;
1
2

为了对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

• 用新的列布局创建一个新表；
• 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
• 检验包含所需数据的新表；
• 重命名旧表（如果确定，可以删除它）；
• 用旧表原来的名字重命名新表；
• 根据需要，重新创建触发器、存储过程、索引和外键。

使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

删除表

DROP TABLE customers2;
1

重命名表

RENAME TABLE customers2 TO customers;

#对多个表重命名
RENAME TABLE backup_customers TO customers,
	         backup_vendors TO vendors,
             backup_products TO products;
1
2
3
4
5
6

补充

MySQL的注释方法
一共有三种，分别为

#单行注释可以使用"#"
-- 单行注释也可以使用"--"，注意与注释之间有空格
/*
用于多行注释
*/
1
2
3
4
5

第二十二章：使用视图（适用于MySQL 5及之后的版本）

视图

例：

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';
#此查询用来检索订购了某个特定产品的客户。假如把整个查询包装成一个名为productcusotmers的虚拟表，则
SELECT cust_name, cust_contact FROM productcustomers
WHERE prod_id = 'TNT2';
1
2
3
4
5
6
7

productcustomers是一个视图。

为什么使用视图

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

视图的规则和限制

• 与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
• 对于可以创建的视图数目没有限制。
• 为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
• ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
• 视图不能索引，也不能有关联的触发器或默认值。
• 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。

使用视图

• 视图用CREATE VIEW语句来创建。
• 使用SHOW CREATE VIEW viewname；来查看创建视图的语句
• 用DROP删除视图，其语法为DROP VIEW viewname;
• 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

利用视图简化复杂的联结

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderietms
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
1
2
3
4
5

创建可重用的视图：创建不受特定数据限制的视图是一种好办法。

例如，上面创建的视图返回生产所有产品的客户而不仅仅是生产TNT2的客户。扩展视图的范围不仅使得它能被重用，而且甚至更有用。这样做不需要创建和维护多个类似视图。

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

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
1
2
3

现在，假如经常需要这个格式的结果。不必在每次需要时执行联结，创建一个视图，每次需要时使用它即可。为把此语句转换为视图，可按如下进行：

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
1
2
3
4

这条语句使用与以前的SELECT语句相同的查询创建视图。为了检索出以创建所有邮件标签的数据，可如下进行：

SELECT *
FROM vendorlocations;
1
2

用视图过滤不想要的数据

例：排除没有电子邮件地址的用户

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

SELECT * FROM customeremaillist;
1
2
3
4
5
6

使用视图与计算字段

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
#将其转换为视图
CREATE VIEW orderitemsexpanded AS
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems;

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;
1
2
3
4
5
6
7
8
9
10
11

更新视图

视图的数据能否更新视情况而定。

通常，视图是可更新的（即，可以对它们使用INSERT、UPDATE和DELETE）。

更新一个视图将更新其基表（可以回忆一下，视图本身没有数据）。如果你对视图增加或删除行，实际上是对其基表增加或删除行。

但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。

这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

• 分组（使用GROUP BY和HAVING）；
• 联结；
• 子查询；
• 并；
• 聚集函数（Min()、Count()、Sum()等）；
• DISTINCT；
• 导出（计算）列。

第二十三章：存储过程（适用于MySQL5及之后的版本）

什么是存储过程？

我的理解，存储过程就是将一条或多条MySQL语句进行封装成一个函数。

为什么使用存储过程？

• 通过把处理封装在容易使用的单元中，简化复杂的操作（正如前面例子所述）。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。 这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能 性就越大。防止错误保证了数据的一致性。
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
  这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码（在下一章的例子中可以看到。）
  换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将SQL代码转换为存储过程前，也必须知道它的一些缺陷。

怎么使用存储过程？

创建存储过程

例：返回产品平均价格的存储过程

CREATE PROCEDURE priductpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
    FROM priducts;
END;
1
2
3
4
5

使用存储过程：

CALL priductpricing();
1

---

---

• mysql命令行客户机的分隔符【可以改，但没必要】

默认的MySQL语句分隔符为’;’。mysql命令行实用程序也使用’;'作为语句分隔符。

如果命令行实用程序要解释存储过程自身内的’;'字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误。
解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

DELIMTER//

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
    FROM products;
END//

DELIMITER ;


-- 其中，DELIMITER//告诉命令行实用程序使用//作为新的语句结束分隔符，可以看到标志存储过程结束的END定义为END//而不是END;。
-- 这样，存储过程体内的;仍然保持不动，并且正确地传递给数据库引擎。
-- 最后，为恢复为原来的语句分隔符，可使用DELIMITER ;。
-- 除\符号外，任何字符都可以用作语句分隔符。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

---

---

删除存储过程

DROP PROCEDURE productpricing;
1

仅当存在时删除使用DROP PROCEDURE IF EXISTS

使用参数

CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT Min(prid_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT AVG(prod_price)
    INTO pa
    FROM prodcts;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

此存储过程接受3个参数：

pl存储产品最低价格，

ph存储产品最高价格，

pa存储产品平均价格。

DECIMAL(P,D);

• P是表示有效数字数的精度。 P范围为1〜65。
• D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。

每个参数必须具有指定的类型，这里使用十进制值。

关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。

MySQL支持IN（传递给存储过程）、OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。

存储过程的代码位于BEGIN和END语句内，如前所见，它们是一系列SELECT语句，用来检索值，然后保存到相应的变量（通过指定INTO关键字）。

为调用此修改过的存储过程，必须指定3个变量名，如下所示：

CALL productpricing(@pricelow,
					@pricehigh,
                    @priceaverage);
#为了显示检索出的产品价格，可如下进行
SELECT @priceaverage；
#为了获得3个值，可以使用如下语句
SELECT @pricehigh, @pricelow, @priceaverage;
1
2
3
4
5
6
7

例：使用IN和OUT参数，ordertotal接收订单号并返回该订单的合计。

CREATE PROCEDURE ordertotal(
	IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;
END;

#调用:必须传递两个参数，1为订单号，2为包含计算出来的合计的变量名
CALL ordertotal(20005, @total);

#显示合计
SELECT @total;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

• 建立只能存储过程

例：获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客

• 获得合计
• 把营业税有条件地添加到合计
• 返回合计

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable

CREATE PROCEDURE ordertotal(
	IN onumber TNT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax'
BEGIN
    -- DECLARE variable for total
    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?
    IF taxable THEN
    	-- Yes, so add taxrate to the total
        SELECT total +(total/100*taxrate) INTO total;
    END IF;

    -- And finally, save to our variable
    SELECT total INTO ototal;
END;

-- 试验
CALL ordertotal(20005, 0, @total);
SELECT @total;

CALL ordertotal(20005, 1, @total);
SELECT @total;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

此存储过程有很大的变动。

首先，增加了注释（前面放置–）。在存储过程复杂性增加时，这样做特别重要。添加了另外一个参数taxable，它是一个布尔值（如果要增加税则为真，否则为假）。

在存储过程体中，用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型，它也支持可选的默认值（这个例子中的taxrate的默 认被设置为6%）。

SELECT语句已经改变，因此其结果存储到total（局部变量）而不是ototal。IF语句检查taxable是否为真，如果为真，则用另一SELECT语句增加营业税到局部变量total。最后，用另一SELECT语句将total（它增加或许不增加营业税）保存到ototal。

本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示

• 检查存储过程

显示用来创建一个存储过程的CREATE语句，使用

SHOW CREATE PROCEDURE ordertotal;
1

为获得包括何时、由谁创建等详细信息的存储过程列表，使用SHOW PRODUCEDURE STATUS
SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出，可使用LIKE指定一个过滤模式:

SHOW PROCEDURE STATUS LIKE 'ordertotal';
1

第二十四章：使用游标（适用于MySQL5及以上版本）

有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。

在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

MySQL游标只能用于存储过程（和函数）

使用游标

使用游标涉及几个明确的步骤。

• 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
• 一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
• 对于填有数据的游标，根据需要取出（检索）各行。
• 在结束游标使用时，必须关闭游标。

创建游标

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;
1
2
3
4
5
6

这个存储过程并没有做很多事情，DECLARE语句用来定义和命名游标，这里为ordernumbers。存储过程处理完成后，游标就消失（因为它局限于存储过程）。

打开和关闭游标

--打开游标
OPEN ordernumbers;
--处理完成后，应当使用下句关闭游标
CLOSE ordernumbers;
1
2
3
4

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭
MySQL会在达到END语句时自动关闭它

CREATE PROCEDURE processorders()
BEGIN
	-- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELCET order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

    --Close the cursor
    CLOSE ordernumbers;

END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

这个存储过程声明、打开和关闭一个游标，但对检索出的数据什么也没做。

使用游标数据

例：从游标中检索单个行

CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
    DECLARE o INT;

    -- Declare the cursor
    DECLARE orderumbers CURSOR
    FRO
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

	-- Get order number
    FETCH ordernumbers INTO o;

	-- Close the cursor
    CLOSE ordernumbers;

END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

其中FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。

例：循环检索数据，从第一行到最后一行

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    --Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

	--Declare continue handler
    DECLARE CONTINUE HANDLER FRO SQLSTATE '02000' SET done=1;

	--Open the cursor
    OPEN ordernumbers;

	--Loop through all rows
    REPEAT
    	--Get order numbers
        FETCH ordernumbers INTO o;
    --End of loop
    UNTIL done END REPEAT;
    --CLose the cursor
    CLOSE ordernumbers;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是，这个例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定）。为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。

使用以下语句将done在结束时设置为真：
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1

这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000’出现时，SET done=1。SQLSTATE '02000’是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件。

例：

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    --Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR SELECT order_num FROM orders;
    --Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    --Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
    	(order_num INT, total DECIMAL(8,2));
    --Open the cursor
    OPEN ordernumbers;
    --Loop through all rows
    REPEAT
    	--Get order number
        FETCH ordernumbers INTO o;
        --Get the total for this order
        CALL ordertoal(o, 1, t);
        --Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total)
        VALUES(o,t);
    --End of loop
    UNTIL done END REPEAT;
    --Close the cursor
    CLOSE ordernumbers;
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

在这个例子中，增加了另一个名为t的变量（存储每个订单的合计）。此存储过程还在运行中创建了一个新表（如果它不存在的话），名为ordertotals。这个表将保存存储过程生成的结果。FETCH像以前一样取每个order_num，然后用CALL执行另一个存储过程来计算每个订单的带税的合计（结果存储到t）。最后，用INSERT保存每个订单的订单号和合计。
次存储过程不返回数据
可以用SELECT * FROM ordertotals;查看该表

第二十五章：使用触发器

想要某条语句（或某些语句）在事件发生时自动执行
触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句

• DELETE
• INSERT
• UPDATE
  其他MySQL语句不支持触发器

创建触发器

需要

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动
• 触发器合适执行

使用CREATE TRIGGER语句创建。例：

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

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。

只有表支持触发器，视图不支持。

每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE前后）单一触发器不能与多个事件或多个表关联。

删除触发器

DROP TRIGGER newproduct;
1

使用触发器

INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

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

例：确定新值生成

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

此代码创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两 点：

• 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
• OLD中的值全都是只读的，不能更新。
  例：使用OLD保存将要被删除的行到一个存档表中：

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
1
2
3
4
5
6

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中（为实际使用这个例子，你需要用与orders相同的列创建一个名为archive_orders的表）。

UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：

• 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新 更新的值；
• 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
• OLD中的值全都是只读的，不能更新。
  下面的例子保证州名缩写总是大写（不管UPDATE语句中给出的是大写还是小写）：

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

每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。

关于触发器的进一步介绍

• 与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
• 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。
• 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
• 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。
• 遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

第二十六章：管理事务处理

事务处理

InnoDB支持事务处理

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

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

事务处理过程：

1. 检查数据库中是否存在相应的客户，如果不存在，添加他/她。
2. 提交客户信息。
3. 检索客户的ID。
4. 添加一行到orders表。
5. 如果在添加行到orders表时出现故障，回退。
6. 检索orders表中赋予的新订单ID。
7. 对于订购的每项物品，添加新行到orderitems表。
8. 如果在添加新行到orderitems时出现故障，回退所有添加的orderitems行和orders行。
9. 提交订单信息。

在使用事务和事务处理时，有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语：

• 事务（transaction）指一组SQL语句；
• 回退（rollback）指撤销指定SQL语句的过程；
• 提交（commit）指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。

控制事务处理

使用ROLLBACK

ROLLBACK撤回MySQL语句

SELECT * FROM ordertitals;
START TRANSCITION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
1
2
3
4
5
6

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动 进行的。
但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句，如下所示：

START TRAMSCATION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
1
2
3
4

在这个例子中，从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

使用保留点

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符
SAVEPOINT

SAVEPOINT delete1;
ROLLBACK TO delete1;
1
2

每个保留点都取标识它的唯一名字,以便在回退时，MySQL直到要回退到何处。

更改默认的提交行为

默认MySQL行为是自动提交所有更改，为指示MySQL不自动提交更改，需要使用

SET autocommit=0;
1

第二十七章：全球化和本地化

使用字符集和校对顺序

• 字符集为字母和符号的集合；
• 编码为某个字符集成员的内部表示；
• 校对为规定字符如何比较的指令。

--查看所支持的字符集完整列表
SHOW CHARECTER SET;
--查看所支持校对的完整列表
SHOW COLLATION；
--确定所用的字符集和校对
SHOW VARIABLSE LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
1
2
3
4
5
6
7

为了给表指定字符集和校对，可以使用带子句的CREATE TABLE

CREATE TABLE mytable
(
	columnn1 INT,
    columnn2 VARCHAR(10)
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;
1
2
3
4
5
6

除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们

CREATE TABLE mytable
(
	columnn1 INT,
    columnn2 VARCHAR(10)
    column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
  COLLATE hebrew_general_ci;
1
2
3
4
5
6
7

校对在对用ORDER BY子句检索出来的数据排序时起重要的作用。如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行：

SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;
1
2

此SELECT使用COLLATE指定一个备用的校对顺序（在这个例子中，为区分大小写的校对）
最后，值得注意的是，如果绝对需要，串可以在字符集之间进行转换。为此，使用Cast()或Convert()函数。

第二十八章：安全管理

访问控制

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权

考虑以下内容：

• 多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；
• 某些用户需要读表，但可能不需要更新表；
• 你可能想允许用户添加数据，但不允许他们删除数据；
• 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；
• 你可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；
• 你可能想根据用户登录的地点限制对某些功能的访问。

不过在现实世界的日常工作中，决不能使用root。应该创建一系列的账号，有的用于管理，有的供用户使用，有的供开发人员使用，等等。

管理用户

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般不需要直接访问mysql数据库和表，但有时需要直接访问。需要直接访问它的时机之一是在需要获得所有用户账号列表时。为此，可使用以下代码：

USE mysql;
SELECT user FROM user;
1
2

创建用户账号

CREATE USER ben IDENTIFIED BY 'p@ssw0rd';
1

删除用户账号

DROP USER bforta;
1

MySQL 5y以前的版本需要先用REVOKE删除与账号相关的权限，再用DROP USER删除账号

设置访问权限

查看账号的权限

SHOW GRANTS FOR bforta
-- output
+---------------------------------------------+
| Grants for bforta@%                         |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'          |
+---------------------------------------------+

-- 输出结果显示用户bforta有一个权限USAGE ON *.*。USAGE表示根本没有权限，所以，此结果表示在任意数据库和任意表上对任何东西没有权限。
1
2
3
4
5
6
7
8
9

为设置权限，使用GRANT语句。GRANT要求你至少给出以下信息：

• 要授予的权限；
• 被授予访问权限的数据库或表；
• 用户名。

例：

GRANT SELECT ON crashcourse.* TO beforta;

-- 此GRANT允许用户在crashcourse.*（crashcourse数据库的所有表）上使用SELECT。通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。


SHOW GRANTS FRO bforta;
+--------------------------------------------------+
| Grants for bforta@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO 'bforta'@'%'               |
| GRANT SELECT ON 'charshcourse'.* TO 'bforta'@'%' |
+--------------------------------------------------+
1
2
3
4
5
6
7
8
9
10
11
12

撤销特定权限 REVOKE

REVOKE SELECT ON crashcourse.* FROM bforta
1

这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。被撤销的访问权限必须存在，否则会出错

更改口令

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');
1

设置自己的口令

SET PASSWORD = Password('n3w p@$$w0rd');
1

不指定用户名时SET PASSWORD更新当前登陆用户的口令

第二十九章：数据库维护

备份数据

• 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
• 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
• 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORETABLE来复原。

进行数据维护

ANALYZE TABLE检查表键是否正确

ANALYZE TABLE orders;
--output
+-----------------------+-----------+-----------+-----------+
| Table                 | Op        | Msg_type  | Msg_text  |
+-----------------------+-----------+-----------+-----------+
| crashcourse.orders    | analyze   | status    | OK        |
+-----------------------+-----------+-----------+-----------+
1
2
3
4
5
6
7

CHECK TABLE用来针对许多问题对表进行检查。在MyISAM表上还对索引进行检查。CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表。EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表，MEDIUM检查所有被删除的链接并进行键检验，QUICK只进行快速扫描。如下所示，CHECK TABLE发现和修复问题：

CHECK TABLE orders, orderitems;
1

诊断启动问题

-- help 显示帮助
-- safe-mode 装在减去某些最佳配置的服务器
-- verbose 显示全文本消息
-- version显示版本信息然后推出
1
2
3
4

查看日志文件

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下几种。

• 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用 --log-error命令行选项更改。
• 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用 --log命令行选项更改。
• 二进制日志。它记录更新过数据（或者可能更新过数据）的所有 语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用–log-bin命令行选项更改。注意，这个日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志。
• 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这 个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ，位于 data 目录中。此名字可以用–log-slow-queries命令行选项更改。

第三十章：改善性能

• 首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。
• 一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
• MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;。）
• MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
• 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
• 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
• 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。
• 应该总是使用正确的数据类型。
• 决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）。
• 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
• 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括 FULLTEXT索引），然后在导入完成后再重建它们。
• 必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的 WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
• 当SELECT语句中有一系列复杂的OR条件时，使用多条SELECT语句和连接它们的UNION语句，可以极大地改进性能。
• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
• LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
• 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
• 最重要的规则就是，每条规则在某些条件下都会被打破。

https://hiszm.cn/
扫描上方公众号l:孙中明 回复 3002 获取 MySQL必知必会 电子版本
1
2
3
4