MySQL必知必会

MySQL

一. 显示数据

• mysql -u root -p 123456 -h localhost -P 3306
  运行mysql，参数-u为用户名，-p为密码，-h为主机名，-P端口号

• USE database01;
  使用database01这个数据库

• SHOW DATABASES;
  显示所有数据库

• SHOW TABLES;
  显示当前数据库下的所有表单

• SHOW COLUMNS FRON students;或
  DESCRIBE students;
  显示students这张表的所有列

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

• SHOW CREATE DATABASE; 或 SHOW CREATE TABLE;
  显示创建的数据库和表单

• HELP SHOW;
  显示允许的SHOW语句

二. 检索数据

• SELECT name , age FROM students;
  检索students表中name列和age列中的每一行

• SELECT * FROM students;
  检索sutdents表中所有列的每一行

• SELECT DISTINCT name，age FROM students;
  分别检索students表中name列和age列的不同的行
  注意：DISTINCT关键字应用于所有列而不仅是前置它的列

• SELECT name FROM students LIMIT 5;
  检索students表中name列的前5行

• SELECT name FROM students LIMIT 5,5;
  检索students表中name列从第5行开始(不包括第5行)的下一个5行的

• 注意：检索出来的第一行为行0，而不是行1，因此LIMIT 1 1 检索出来的是第二行的数据而不是第一行

三. 排序检索数据

• SELECT name FROM studnets ORDER BY name;
  按name列的字母顺序排序students表中的name列

• SELECT id , price , name FROM students ORDER BY price , name;
  先按price列的顺序排序，如果price相等则再按name顺序排序students表中的id,price,name列

• SELECT id , price , name FROM students ORDER BY price DESC;
  按price列的降序排序sutdents表中的id,price,name列。默认为升序(ASC)

• SELECT id, price, name FROM students ORDER BY price DESC, name;
  先按price的降序排序，如果price相同则按name列的升序排列
  注意：DESC只应用到直接位于前面的列，后面的列仍然为默认的升序

• SELECT price FROM products ORDER BY price DESC LIMIT 1;
  检索出price列中最大的值

四. 过滤数据

• SELECT id, price FROM products WHERE price = 2.50;
  检索price=2.50的products表中的id列和price列

• SELECT name, price FROM products WHERE name = 'fuses';
  检索name='fuses’的products表中name列和price列。注意：MySQL在匹配字符串时默认不区分大小写

• SELECT id, name FROM products WHERE id <> 1003; 或 SELECT id, name FROM products WHERE id != 1003;
  检索id不等于1003的products表中的id列和name列

• SELECT name, price FROM products WHERE price BETWEEN 30 AND 50;
  检索price值为30到50之间的products表中的name列和name列

• SELECT name FROM products WHERE price IS NULL;
  检索price列中为NULL的products表中的name列

• 在同时使用ORDER BY和WHERE时，应该让ORDER BY位于WHERE之后，否则将会产生错误

五.数据过滤

• SELECT id, price, name FROM products WHERE id = 1003 AND price <= 10;
  检索id=1003的并且price<=10的products表中的id列，price列和name列

• SELECT id, price, name FROM products WHERE id = 1002 OR id = 1003;
  检索id=1002或者id=1003的products表中id列，price列和name列

• SELECT id, price, name FROM products WHERE id = 1002 OR id = 1003 AND price >= 10;
  AND的优先级大于OR的优先级，因此会先执行后面两个字句，再执行第一个字句。最好是加括号能够更加明显的区别.SELECT id, price, name FROM products WHERE id = 1002 OR (id = 1003 AND price >= 10);

• SELECT name, price FROM products WHERE id IN (1002,1003) ORDER BY name;
  检索所有id为1002与1003的products表中的name, price列并按name字段升序排列。此时与SELECT id, price, name FROM products WHERE id = 1002 OR id = 1003 ORDER BY name;语句的意思一样

• IN操作符的优点：

  • IN操作符的语法更清楚且更直观
  • 在使用IN时，计算的次序更容易管理
  • IN操作符比OR操作符清单执行更快
  • IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句

• SELECT name, price FROM products WHERE id NOT IN (1002,1003) ORDER BY name;
  检索id不为1002和1003的products表中name列，price列并按name列升序排列

六. 用通配符进行过滤

1. 百分号(%)通配符

%表示任何字符出现的次数。包括0次、1次…n次。

SELECT id, name FROM products WHERE name LIKE 'jet%';

检索name列中以jet开头的字符串

SELECT id, name FROM products WHERE name LIKE 's%e';

检索name列中以s开头e结尾的字符串

2. 下划线(_)通配符

下划线(_)只匹配单个字符而不是多个字符

SELECT id, name FROM products WHERE name LIKE '_ton';

检索name列中字符串长度为4的且后面3个字符为ton的字符串

3. 使用通配符的技巧

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

七. 使用正则表达式进行搜索

1. 基本字符匹配

SELECT name FROM products WHERE name REGEXP '1000' ORDER BY name;

检索name列中含有1000这个字符串的行。

SELECT name FROM products WHERE name REGEXP '.000' ORDER BY name;

.是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符。因此，字符串1000和2000都匹配且返回。

• 注意：匹配字符串时不区分大小写，为区分大小写，可使用BINARY关键字。如WHERE name REGEXP BINARY ‘Jet Pack’;

2. 进行OR匹配

搜索两个字符串之一，使用|这个符号

SELECT name FROM prodcts WHERE name REGEXP '1000|2000' ORDER BY name;

检索name列中含有1000和2000的字符串。

3. 匹配几个字符之一

SELECT name FROM products WHERE name REGEXP '[123] Tom' ORDER BY name;

这里，使用正则表达式[123] Ton。[123]定义一组字符，它的意思是匹配1或2或3,因此，1 ton和2 ton都匹配且返回。

4. 匹配范围

[1-3] 匹配1到3的整数。
[a-z] 匹配任意字母字符。

5. 匹配特殊字符

为了检索. - []这些特殊字符，需要进行转义才能进行匹配。
如\\.可以匹配.

SELECT name FROM products WHERE name REGEXP '\\\\.' ORDER BY name;

检索name列中含有.字符的字符串。

6. 匹配字符类

类	说明
[:alnume:]	任意字母和数字(同[a-zA-Z0-9])
[:alpha:]	任意字符(同[a-zA-Z])
[:blank:]	空格和制表(同[\t])
[:cntrl:]	ASCII控制字符(ASCII0到31和127)
[:digit:]	任意数字
[:graph:]	与[:print:]相同,但不包括空格
[:lower:]	任意小写字母(同[a-z])
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符(同[\f\n\r\t\v])
[:upper:]	任意大写字母(同[A-Z])
[:xdigit:]	任意十六进制数字(同[a-fA-F0-9])

7. 匹配多个实例

元字符	说明
*	0个或多个匹配
+	1个或多个匹配(等于{1,})
?	0个或1个匹配(等于{0,1})
{n}	指定数目的匹配
{n,}	不少于指定书目的匹配
{n,m}	匹配数目的范围(m不超过255)

SELECT name FROM products WHERE name REGEXP '[[:digit:]]{4}' ORDER BY name;

匹配name列中连着一起的任意4为数字的字符串

8. 定位符

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

SELECT name FROM products WHERE name REGEXP '^[0-9\\.]' ORDER BY name;

^{匹配串的开始，因此，}[0-9\.]只在.或任意数字为串中第一个字符时才匹配它们。

其他

• ^{的双重用途：}有两种用法。在集合中(用[和]定义)，用它来否定该集合，否则，用来指串的开始处。
• 简单的正则表达式测试：可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0(没有匹配)或1(匹配)。可以用带文字串的REGEXP来测试表达式,并试验它们。相应的语法如下：

SELECT 'hello' REGEXP '[0-9]';

这个例子显然返回0(因为文本hello中没有数字)。

八. 创建计算字段

1. 拼接字段

SELECT Concat(name,'(',country,')') FROM vendors ORDER BY name;

Concat()拼接串，包含name字段左括号country字段右括号。

• RTrim(name) 去掉右边字段的空格
• LTrim(name) 去掉左边字段的空格
• Trim(name) 去掉串左右两边的空格

2. 别名

SELECT Concat(RTrim(name),'(',RTrim(country),')') AS title FROM vendors ORDER BY name; 

将连接起来的字段赋予了title的名字

3. 执行算术计算

SELECT id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE num = 2005;

直接加入四则运算符号即可

4. 测试计算

SELECT提供了测试和实验函数与计算的一个很好的办法。省略FROM语句以便测试。

• SELECT Trim(' abc') ;
  将返回abc
• SELECT 3*2;
  将返回6
• SELECT Now();
  将返回当前日期和时间

九. 使用数据处理函数

1. 文本处理函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。
如 Soundex('Y Lee') = Soundex('Y Lie')， 返回为真，因为这两个串发音相似。

2. 日期和时间处理函数

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

注意：日期的格式必须为yyyy-mm-dd格式

SELECT id, name FROM orders WHERE date = '2005-09-01';

注意：如果date的数据类型为datetime,这种类型存储日期和时间，如果这样写date此时只能匹配2005-09-01 00:00:00的时间，而2005-09-01 00:00:01并不会被返回。如果只想比较日期最好是在date前面加Date()函数，如 SELECT id, name FROM orders WHERE Date(date) = '2005-09-01';

例子：查找2005年9月下的订单

SELECT id, name FROM orders WHERE Date(date) BETWEEN '2005-09-01' AND '2005-09-30';

SELECT id, name FROM orders WHERE Year(date) = 2005 AND Month(date) = 9;

3. 数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
EXp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

十.汇总数据

1. AVG()函数

AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

SELECT AVG(prod_price) AS avg_price FROM products;

返回prod_price的平均值
注意：AVG()只能用于单个列

2. COUNT()函数

利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT的两种使用方式：

• 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值。
• 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

SELECT COUNT(*) AS num_cust FROM customers;

利用COUNT(*)对表中所有行计数

SELECT COUNT(cust_email) AS num_cust FROM customers; 

返回cust_email列中有值得行进行计数
注意：如果指定列名，则指定列的值为空的行被COUNT()函数忽略，但如果COUNT()函数中用的是星好(*)，则不忽略。

3. MAX()函数

MAX()返回指定列中的最大值，MAX要求指定列名。

SELECT MAX(prod_price) AS max_price FROM products;

返回prod_price中最大的值
注意：MAX()用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。

4. MIN()函数

MIN()函数返回指定列的最小值，MIN()要求指定列名。

SELECT MIN(prod_price) AS max_price FROM products;

返回prod_price中最小的值
注意：MIX()用于文本数据时，如果数据按相应的列排序，则MAX()返回最前面的行。

5. SUM()函数

SUM()函数返回指定列值的和(总计)。

SELECT SUM(quantity) AS items_ordered FROM orderitems;

返回quantity这一列值得总和

6. 聚集不同的值

指定包含的值为不同的，则使用DISTINCT参数。

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products;

返回prod_price列中不同值的平均值。
注意：使用DISTINCT必须指定列名，因此不能将DISTINCT用于COUNT(*)
建议：在用聚合函数计算时最好使用别名，便于区分

十一.分组数据

1. 创建分组

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

GROUP BY通过id进行分组。
GROUP BY的一些规定：

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

使用WITH ROLLUP可以汇总级别，如果是数值型的它会对它进行求和,形如：

SELECT id, COUNT(*) AS num_prods FROM products CROUP BY id WITH ROLLUP;

2. 过滤分组

过滤分组使用HAVING子句，HAVING非常类似于WHERE，HAVING支持所有的WHERE操作符，WHERE过滤的是指定的行而不是分组，所以HAVING比WHERE的功能跟强大。

SELECT id, COUNT(*) AS orders FROM products GROUP BY id HAVING COUNT(*) >= 2 

过滤orders >= 2的那些分组
HAVING和WHERE的区别：WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。

SELECT id，COUNT(*) AS num_products FROM products WHERE price >= 10 GROUP BY id HAVING COUNT(*) >= 2;

先将price小于10的过滤之后，在进行分组再过滤。

3.分组和排序

不要忘记ORDER BY：一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

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

先分组，在过滤，最后进行排序。

4.SELECT子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

十二.使用子查询

1. 利用子查询进行过滤

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

子查询总是从内向外处理，在处理上面SELECT语句时，MYSQL实际上执行了两个操作。
首先，先执行：

SELECT order_name FROM orderitems WHERE prod_id = 'TNT2';

此查询返回两个订单号20005和20007；再执行：

SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

2. 作为计算字段使用子查询

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

注意：此时的AS的使用位置。但两张表的某个字段名相同时，进行比较时要使用完全限定列名以保证查询正确性。

十三.联结表

1. 创建联结

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;

vend_name字段属于vendors表，而prod_name和prod_price字段属于products表。因此FROM也是从两张表中取数据。这两个表用WHERE子句联结，因为两个表的vend_id列名相同，因此使用完全限定列名。

2. 内部联结

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

这种语法与上面的语法功能一致。

3. 联结多个表

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;

这里联结了三张表，还可以联结更多的表，但是会影响性能。

十四.创建高级联结

1. 自联结

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'; 

由于是使用的两个相同的表，因此必须要使用表别名，否则会产生歧义。尽量使用自联结而不是子查询，有时候自联结远比处理子查询快得多。

2. 自然联结

SELECT c.*, o.order_name, 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';

自然连接排除多次出现，使每个列只返回一次。

3. 外部联结

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

联结左表中的所有行，也就是customers表，没有数据的为NULL。

4. 使用带聚集函数的联结

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. 使用UNION

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);

UNION可以联结多条SELECT语句。

2. UNION使用规则

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。
• UNION中的每个查询必须包含相同的列、表达式或聚集函数，可以应用不同的表。
• 列数据类型兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型。（例如：不同的数值类型或不同的日期类型）。

3. 包含或取消重复的行

默认UNION是会取消掉重复的行，但是如果需要包含所有行，则可以使用UNION ALL而不是UNION。

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);

4. 对组合查询结果排序

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; 

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

十六.全文本搜索

1. 启用全文本搜索支持

• MYISAM引擎支持全文本搜索，而InnoDB引擎不支持。
• 要使用FULLTEXT()索引单个列或多个列。

2. 进行全文本搜索

SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('rabbit');

搜索note_text列中含有rabbit的行。
Match()指定被搜索的列，Against()指定要使用的搜索表达式。
默认是按照指定的字符串在行中出现的先后排序，出现的在前则等级就高，出现在后则等级低，等级高的排在等级低的后面。

• 使用完整的Match()说明：传递给Match()的值与FULLTEXT()定义中的相同，如果指定多个列，则必须列出它们。
• 搜索不区分大小写：除非使用BINARY方式，否则全文本搜索不区分大小写。
• 排序多个搜索项：如果指定多个搜索项，则包含多数匹配词的那些行具有比包含较少词的那些行高的等级值。

3. 使用查询扩展

SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

它不仅会返回含有anvis的行，还会返回包含与含有anvois行中某些单词一样的行，即使这些行中不包含anvis。

4. 布尔文本搜索

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

将搜索note_text列中含有heavy的行。
即使没有FULLTEXT索引也可以使用。

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

匹配含有heavy词但是不包含以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而不是匹配两个词rabbit和bait。

SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit < carrot' IN BOOLEAN MODE);

匹配rabbit和carrot，增加前者的等级, 降低后者的等级。

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(< combination>)' IN BOOLEAN MODE);

这个搜索匹配词safe和combintion, 降低后者的等级。
在布尔方式中，排列而不排序。

5. 全文本的使用说明

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

十七. 插入数据

1. 插入完整的行

INSERT INTO customers VALUES(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angles',
'CA',
'90046',
'USA',
'NULL',
'NULL');

INSERT语句一般不会产生输出，这种语句虽然简单，但是并不安全，过度依赖于表中列的顺序，不推荐使用。

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 Angles',
'CA',
'90046',
'USA',
'NULL',
'NULL');

以为指定了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序。
提高整体性能：如果SELECT语句更重要，则可以使用INSERT LOW_PRIORITY INTO降低INSERT语句的优先级。

2. 插入多个行

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

第二种会提高INSERT的性能。

3. 插入检索出的数据

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, ccust_name, cust_address, cust_city, cust_state, cust_state, cust_zip, cust_country FROM custnew;

将custnew中的所有数据导入customers，MySQL不关心SELECT返回的列名，它使用的是列的位置，SELECT中的第一列将用来填充表列中的第一列，以此类推。

十八.更新和删除数据

1. 更新数据

UPDATE customers SET cust_email = 'elmer@fudd.com', cust_name = 'The Fudds' WHERE cust_id = 10005

UPDATE语句总是以要更新的表的名字开始，SET命令用来将新值赋给被更新的列，如果不使用WHERE子句将更新表中的所有列。
IGNORE关键字：如果用UPDATE语句更新时出现错误，则整个UPDATE操作被取消，错误发生前更新的所有行被恢复到它们原来的值。为即使发生错误，也继续进行更新，可使用IGNORE关键字，UPDATE IGNORE customers…

2. 删除数据

DELETE FROM customers WHERE cust_id = 10006;

删除customers表中cust_id = 10006这一行。
DELETE删除整行而不是整列。
更快的删除：如果想从表中删除所有行，不要使用DELETE, 可使用TRUNCATE TABLE语句，它完成相同的工作，单速度更快。

3. 更新和删除的指导原则

下面是UPDATE或DELETE时所遵循的习惯：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
• 保证每个表都有主键。
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
• 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。
  小心使用：MySQL没有撤销按钮，应该非常小心地使用UPDATE和DELETE。

十九.创建和操纵表

1. 创建表

 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; 

cust_id为主键名，且为自增的。如果仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。仅在表名不存在时创建它。
主键和NULL值：主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值得列。允许NULL值的列不能作为作为唯一标识。
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。
确定AUTO_INCREMENT值：可以使用last_insert_id()函数获得最后一个AUTO_INCREMENT的值，如：SELECT last_insert_id()

2. 指定默认值

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

给quantity列包含默认值1，在未给出数量的情况下使用数量1。DEFAULT不允许使用函数，只支持常量。

3. 引擎类型

• InnoDB是一个可靠的事务处理引擎，它不支持全文搜索。
• MEMORY在功能等同于MyISM，但由于数据存储在内存(不是磁盘)中，速度很快（特别适合于临时表）。
• MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
  注意：外键不能跨引擎

3.更新表

ALTER TABLE vendors ADD vend_phone CHAR(20);

这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。

ALTER TABLE vendors DROP COLUMN vend_phone;

用于删除vendors表中vend_phone的列。

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num)
REFERENCES orders (order_num) ON DELETE SET NULL ON UPDATE CASCADE;

给orderitems表中order_num设置成外键对应orders表中的order_num, 该外键的名称为fk_orderitems_orders，方便以后删除外键。
ON DELETE和ON UPDATE后面可以跟①NO ACTION：如果子表引用父表的某个字段值，不允许直接删除父表的值，与RESTRICT作用一样；②SET NULL：表示主表修改，从表对应的外键设置为null；③RESTRICT：如果子表引用父表的某个字段值，不允许直接删除父表的值，与NO ACTION作用一样；④CASCADE：表示主表修改，从表对应的外键做相应的修改

ALTER TABLE orderitems DROP FOREIGN KEY fk_orderitems_orders;

删除orderitems表与orders表的外键约束。

4. 删除表

DROP TABLE customers2;

删除customers2这张表。

5. 重命名表

RENAME TABLE customers2 TO customers;

将customers2表的名字重命名为customers。

二十.使用视图

1. 为什么使用视图

视图：它不包含表中应该有任何列或数据，它包含的是一个SQL查询。

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

2. 视图的规则和限制

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

3. 使用视图

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

4. 代码演示

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;

创建一个名为productcustomers的视图。

SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

从视图中检索特定数据，极大地简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL, 然后根据需要多次使用。

---

CREATE VIEW vendorlocation AS 
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
       AS vend_title
FROM vendors
ORDER BY vend_name;

这条语句创建vendorlocation视图

SELECT * FROM vendorlocations;

5.更新视图

视图是可更新的，但是并非所有视图都是可更新的。
以下操作不能进行视图的更新：

• 分组（使用GROUP BY和HAVING）
• 联结
• 子查询
• 并
• 聚集函数
• DISTINCT
• 导出（计算）列

将视图用于检索：一般，应该将视图用于检索而不用于更新。

二十一. 使用存储过程

1. 为什么要使用存储过程

存储过程：简单来说就是为以后的使用而保存的一条或多条MySQL语句的集合。

• 通过把处理封装在容易使用的单元中，简化复杂的操作。
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性。防止错误的发生。
• 简化对变动的管理。
• 提高性能。因为使用存储过程比使用单独的SQL语句要快。

2. 使用存储过程

2.1 创建存储过程

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

CEATE PROCEDURE是创建存储过程的语法格式。BEGIN和END语句用来限定存储过程体。

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

DELIMITER //

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

DELIMITER ;

其中DELIMITER //告诉命令行实用程序//作为新的语句分隔符，可以看到END//最后变了，最后又恢复分隔符为;字符。

2.2 执行存储过程

CALL productpricing();

执行更改刚刚创建的存储过程并显示返回结果。

2.3 删除存储过程

DROP PROCEDURE productpricing;

删除刚刚创建的存储过程。
仅当存在时删除: DROP PROCEDURE IF EXISTS;

2.4 使用参数

CREATE PROCEDURE producpricing(
    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;

此过程接受3个参数，pl最低价格，ph最高价格，pa平均价格。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。MySQL支持IN（传递给存储过程，也就是需要传入一个值）,OUT(从存储过程传出，也就是该值已被算出，到时只需要调用即可)和INOUT（对存储过程传入和传出）。

CALL producpricing(@pricelow,
                   @pricehigh,
                   @priceverage);

要求传递3个参数，因此必须正好传递3个参数。所有MySQL变量都必须以@开始。

SELECT @pricelow, @pricehigh, @priceverage;

以后就可以直接使用SELECT语句返回值。

3. 建立智能存储过程

-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal = order total variable
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    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 precentage
    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 out variable
    SELECT total INTO ototal;

END; 

增加了注释（前面放置–），用DECLARE语句定义了两个局部变量。最后将将局部变量total保存到ototal中
COMMENT关键字：它不是必需的，但如果给出，将在SHOW PROCEDURE STATUS的结果中显示。
执行：

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

4. 检查存储过程

SHOW CREATE PROCEDURE ordertotal;

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

SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'ordertotal';

获得包括何时、由谁创建等详细信息的存储过程列表，也可以指定一个LIKE进行过滤。

二十二.使用游标

1. 使用游标

有时需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。MySQL游标只能用于存储过程。
使用游标涉及几个明确的步骤：

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

2. 创建游标

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

    -- Open the cursor
    OPEN ordernumbers;

    -- Close the cursor
    CLOSE ordernumbers;
END;

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

3. 使用游标检索数据

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 FOR SQLSTATE '02000' SET done=1;

    -- Open the cursor
    OPEN ordernumbers;

    -- Loop through all rows
    REPEAT
        -- Get order number
        FETCH ordernumbers INTO o;

    -- END of loop
    UNTIL done END REPEAT;

    -- Close the cursor
    CLOSE ordernumbers;
END;

循环检索数据，使用FETCH检索当前order_num到声明的名为o的变量中。这个中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNIT done END REPEAT;规定）。为使它起作用，用一个DEFAULT 0（假，不结束）定义变量done。DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;这条语句定义一个CONTINUE HANDLER, 它是在条件出现时被执行的代码。这里，它指出当SQLSTATE’02000’出现时，SET done=1。SQLSTATE '02000是一个未找到条件，当REPATE由于没有更多的行供循环而不能继续时，出现这个条件。

二十三.使用触发器

1. 创建触发器

触发器：需要在某个表发生更改是自动处理。
创建触发器时，需要给出4条信息：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（DELETE、INSERT或UPDATE）
• 触发器何时执行（处理之前BEFORE或之后ALTER）
  保证每个数据库的触发器名唯一：触发器名在每个表中唯一，但不是在数据库中唯一，这表示同一数据库中的两个表可具有相同名字的触发器。
  只有表才支持触发器，视图不支持(临时表也不支持)。

2. 删除触发器

DROP TRIGGER newproduct;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

3. 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;

FOR EACH ROW对每个插入行都执行。当先orders表中插入数据时，会自动返回AUTO_INCREMENT自动增长此时的值。

4. DELETE触发器

• 在DELETE触发器代码内，你可以引用一个名为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

在任意订单被删除前执行此触发器，将要被删除的订单保存在一个名为archive_orders的表中。
多语句触发器；使用BEGIN END块的好处是触发器能容纳多余SQL语句。

5. UPDATE触发器

• 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值。
• 在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE中的值）。
• OLD中的值全部都是只读的，不能更新。

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

该语句会保证插入的字段vend_state全部都是大写的。

二十四.管理事务处理

1. 事务处理

MyISAM引擎不支持事务处理管理，InnoDB引擎支持事务处理管理。
事务处理是一种机制，可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
几个关键词：

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

2. 控制事务处理

标识事务的开始

START TRANSACTION;

2.1 使用ROLLBACK

SELECT * FORM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

START TRANSACTION到ROLLBACK中的语句全部都没有生效。
显然，ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。
那些语句可以回退： 事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。你不能回退CREATE或DROP操作。事务处理快中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

2.2 使用COMMIT

START TRANSACTION 
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM order WHERE order_num = 20010;
COMMIT;

一般的MySQL语句都是直接针对数据库表执行和编写的。这是隐含提交，即提交操作都是自动进行的。
但是，在事务处理块中，提交不会隐含地进行，使用COMMIT进行提交。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，第二条失败，则两条DELETE都不提交。

3. 使用保留点

为了支持回退部分事务处理，必须能在事务处理块中合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点，使用SAVEPOINT创建站位符。

SAVEPOINT delete1;

每个保留点都取标识它的唯一名字，以便回退时，MySQL知道要回退到何处。为了回退到指定的保留点，可以如下进行：

ROLLBACK TO delete1;

释放保留点：保留点在事务处理完成（ROLLBACK或COMMIT）后自动释放。MySQL5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。

4. 更改默认的提交行为

默认的MySQL行为是自动提交所有更改。

SET autocommit = 0;

autocommit标志决定是否自动提交更改，不管有没有COMMIT语句。设置autocommit为0（假）提示MySQL不自动提交更改（知道autocommit被设置为真为止）。

二十五.全球化和本地化

1. 字符集和校对顺序

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

2. 使用字符集合校对顺序

SHOW CHARACTER SET;

这条语句显示所有可用的字符集以及每个字符集的描述和默认校对。
查看所支持校对的完整列表，使用以下语句：

SHOW COLLATION;

此语句显示所有可用的校对，以及它们适用的字符集。

2.1 创建表时指定字符集和校对

CREATE TABLE mytable(
    column1 INT,
    column2 VARCHAR(10)
)DEFAULT CHARCTER SET hebrew
 COLLATE hebrew_general_ci;

此语句创建一个包含两列的表，并且指定一个字符集和一个校对顺序。

• 如果指定CHARCTER SET和COLLATE两者，则使用这些值。
• 如果只指定CHARCTER SET，则使用此字符集及其默认的校对
• 如果既不指定CHARCTER SET, 也不指定COLLATE，则使用数据库默认。

MySQL还允许对每个列设置字符集合校对，在进行排序时也可以设置。
可以使用Cast()或Convert()函数在字符集之间进行转换。

二十六.安全管理

1. 管理用户

USE mysql;
SELECT user FROM use;

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。mysql数据库有一个名为user的表，它包含所有用户账号。

1.1 创建用户账号

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

CREATE USER创建一个用户账号，用IDENTIFIED BY给出了一个口令。

1.2 重新命名用户

RENAME USER ben TO bforta;

1.3 删除用户账号

DROP USER bforta;

MySQL5以后，DROP USER删除用户账号和所有相关的账号权限。

2. 访问权限

SHOW GRANTS FOR bforta;

该语句可以查看用户bforta有哪些权限，输出显示

GRANT USAGE ON *.* TO 'bforta'@'%'

结果表示bforta有一个权限USAGE ON .。USAGE表示根本没有权限。一般用户定义为user@host，这里输出中的%代表默认的主机名。

2.1 GRANT的用法

GRANT SELECT ON crashcourse.* TO bforta;

此语句表示，允许用户bforta对crashcourse数据库中的所有数据具有只读访问权限。
简化多次授权：

GRANT INSERT, SELECT ON crashcourse.* TO bforta;

2.2 REVOKE的用法

REVOKE SELECT ON crashcourse.* TO bforta;

这条语句代表取消刚刚赋予bforta的权限。

2.3 GRANT和REVOKE

GRANT和REVOKE可在几个层次上控制访问权限：

• 整个服务器，使用GRANT ALL和REVOKE ALL;
• 整个数据库，使用ON database.*;
• 特定的表，使用ON database.table;
• 特定的列；
• 特定的存储过程。

3. 更改口令

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

SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。
SET PASSWORD还可以用来设置自己的口令。

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

在不指定用户是，SET PASSWORD更新当前登录用户的口令。

二十七.数据维护

1. 备份数据

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

2. 进行数据库维护

ANALYZE TABLE orders;

该语句可以返回该表的状态信息。

• CHECK TABLE用来针对许多问题对表进行检查。
• CHANGED检查自最后一次检查以来改动过的表。
• EXTENDED执行最彻底的检查
• FAST只检查未正常关闭的表
• MEDIUM检查所有被删除的连接并进行键检验
• QUICK只进行快速扫描
• 如果MyISAM表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE来修复相应的表
• 如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能

3. 诊断启动问题

几个重要的mysqld命令行选型：

• –help显示帮助——一个选项列表
• –safe-mode装载减去某些最佳配置的服务器
• –verbose显示全文本信息
• –version显示版本信息然后退出

4.查看日志文件

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

在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

二十八.改善性能

• MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;）。
• 如果遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。开可以用KILL总结某个特定的进程。
• 总有不止一种方法编写用一条SELECT语句。应该实验联结、并、子查询等，找出最佳的方法。
• 使用SELECT语句和连接它们的UNION语句，代替SELECT语句中的OR条件。
• LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。

二十九. MySQL数据类型

1. 串数据类型

定长串数据类型： CHAR属于定长串类型，它们分配的存储空间与指定的一样多。如CHAR(8)不管你存储内容的长度是多少，它所占空间就是8，如果存储内容长度大于8，则会被截取。
**变长串类型：**变长串存储可变长度的文本，只有指定的数据得到保存。TEXT属于边长串类型
MySQL不允许对变长列进行索引。这会极大地影响性能。

数据类型	说明
CHAR	1~255个字符的定长串。它的长度必须在创建时指定，否则MySQL假定为CHAR(1)
ENUM	接受最多64K个串组成一个预定义集合的某个串
LONGTEXT	与TEXT相同，但最大长度为4GB
MEDIUMTEXT	与TEXT相同，但最大长度16K
SET	接受最多64个串组成的一个预定义集合的零个或多个串
TEXT	最大长度为64K的变长文本
TINYTEXT	与TEXT相同，但最大长度为255字节
VACHAR	长度可变，最多不超过255字节。如果创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串(其中n≤255)

使用引号： 不管使用何种形式的串数据类型，串值都必须括在引号内(通常单引号更好)。

2. 数值数据类型

有符号或无符号： 所有数值类型(除BIT和BOOLEAN外)都可以有符号或无符号。如果不需要存储负值，可以使用UNSIGNED关键字，这样将允许你存储两倍大小的值。

数据类型	说明
BIT	位字段，1~64位
BIGINT	整数值
BOOLEAN	布尔标志，或者为0或者为1
DECIMAL	精度可变的浮点值，DECIMAL(8,2): 8表示整数部分加小数部分的位数，2表示小数部分的位数
DOUBLE	双精度浮点数
FLOAT	单精度浮点数
INT(或INTEGER)	整数值
MEDIUMINT	整数值
REAL	4字节的浮点值
SMALLINT	整数值
TINYINT	整数值

3. 日期和时间数据类型

数据类型	说明
DATE	表示1000~01~01——9999~12~31的日期，格式为YYYY-MM-DD
DATATIME	DATE和TIME的组合
TIMESTAMP	功能和DATETIME相同(但范围较小)
TIME	格式为HH:MM:SS
YEAR	用2位数字表示，范围是70(1970)~69(2069年)，用4位数字表示，范围是1901年~2155年

4. 二进制数据类型

数据类型	说明
BLOB	Blob最大长度为64KB
MEDIUMBLOB	Blob最大长度为16MB
LONGBLOB	Blob最大长度为4GB
TINYBLOB	Blob最大长度为255字节

参考

《MySQL必知必会》