《MySQL必知必会》学习笔记

使用MySQL

USE 数据库名称;：选择需要使用的数据库

SHOW DATABASES;：返回可用数据库列表

SHOW TABLES;：返回当前数据库中可用表的列表

SHOW COLUMNS FROM 表名;：返回一个表中的内容

SHOW STATUS;：显示服务器状态信息

SHOW GRANTS;：用于显示授予用户的安全权限

SHOW ERRORS, SHOW WARNINGS;：用于显示服务器的错误或警告信息

检索数据

SELECT prod_name FROM products; -- 从表products中检索列prod_name
SELECT prod_id, prod_name, prod_price FROM products; -- 从表products中检索多个列
SELECT * FROM products; --从表products中检索所有列，`*`代表通配符，表示匹配所有列
SELECT DISTINCT vend_id FROM products; -- 返回表中某列不同的行（即对于重复的值只输出一个），如果从多个列中检索不同的行，则会按输出最多的列来输出
SELECT prod_name FROM products LIMIT 5; -- 返回某列中的5行
SELECT prod_name FROM products LIMIT 5, 5; -- 返回从第5行开始的接续5行，前一个数为开始行，若返回的行数超过表中最大行数则返回最多的行
SELECT products.prod_name FROM products; -- 使用表名和列名的形式完全限定某列

排序检索数据

SELECT prod_name FROM products ORDER BY prod_name; --对ORDER BY后面的列数据进行排序，并输出排序后的检索列，此处检索列和排序列一致，也可以使用其他列来排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name; -- 表示先按照prod_price排序，再按照prod_name排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC; -- 按价格降序排序，字串的降序为(Z-A)，默认按升序（A-Z）
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name; -- 表示按照prod_price字段降序排序后再按prod_name字段升序排列（默认），即DESC关键字只对其前面紧邻的关键字起作用
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1; -- 组合使用，表示返回列中的最大值（此处为最贵的物品），通常用于查找最大值和最小值

在排序中大小写字母默认是一样的级别，如A和a顺序应该一样，若确实需要设置大小写不同的顺序，需要请求数据库管理员修改，无法通过ORDER BY实现

过滤数据

在同时使用WHERE和ORDER BY时需要将ORDER BY置于WHERE的后面

SELECT prod_name FROM products WHERE prod_price = 2.5; -- 检索价格为2.5的行，其中=表示条件操作符，后面的过滤字段（WHERE后面的字段）可以和前面的检索字段（SELECT后面的字段）不一样

WHERE的所有条件操作符如下表所示

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个之间

SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses'; -- fuses为字符串，需要使用单引号括起来
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10; --检索价格在5到10之间的数据（包括开始5和结束10），数据可以是数值或日期等
SELECT prod_name FROM products WHERE prod_price IS NULL; -- 用于检查具有空值的列并返回所在的行，空值与0，空格，空字符串不同

数据过滤

多条件过滤数据，操作符：AND, OR, IN, NOT

SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10; -- 过滤同时满足两个条件的数据，两个条件可以是不同字段下的不同条件操作，返回满足条件的行
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003; -- 过滤满足任意一个条件的数据，返回满足条件的行

WHERE中多个条件时的计算次序：当多个AND和OR混合使用时优先处理AND操作符，如需要改变这种优先级，可以采用小括号的形式将需要优先执行的操作符包含

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10; -- 该语句会先处理后面的AND操作符，再处理OR操作符

SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; -- 该语句会先处理括号中的OR操作符，再处理AND操作符

在使用混合操作符的WHERE子句中应该尽量使用小括号对其进行分组，避免产生歧义

SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name; -- IN操作符表示过滤其前面的字段的值在后面括号中的数据，
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name; -- 表示不匹配IN后面清单中的数据，NOT操作符用于否定其后面的任何条件

IN操作符与OR操作符一致，具有的优点如下：

• 在使用长的合法的清单（小括号中的值）时，IN操作符的语法更清楚且更直观
• 在使用IN时，计算次序更容易管理
• IN操作符一般比OR操作符清单执行更快
• IN可以包含其他的SELECT语句，使得能够更动态的建立WHERE子句

用通配符过滤

通配符是用来匹配值的一部分的特数字符，要匹配的字符用单引号包括

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%'; -- %表示匹配任意字符，该语句表示检索prod_name中以jet开头的所有数据，%可以放置在多个位置用于匹配，%不能匹配NULL
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%jet%'; -- 表示匹配包含jet值的行
SELECT prod_name FROM products WHERE prod_name LIKE 's%e'; -- 匹配以s开头和e结尾的数据，%可以匹配0个、一个和多个字符

通配符_，该通配符用法与%一致，不同的是该符号有且只能匹配一个字符，即不可多余一个也不能没有

通配符使用技巧：

• 不要过度使用，如果其他操作可以实现同样的目的优先使用其他操作
• 除绝对必要外，不要把通配符置于搜索开始处，会导致搜索速度极大变慢
• 注意使用通配符的位置，错误的位置会导致错误的结果输出

正则表达式

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name; -- 在REGEXP后面所跟的东西作为正则表达式，也可以使用.000，'.'表示任意字符，如果要区分大小写匹配，可以在REGEXP后面使用关键字BINARY
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000'; -- '|'表示或的意思，即匹配行中包括1000或2000的数据，可以多次使用该符号，如'| a | b | c |'
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name; -- 表示匹配方括号中任意一个字符与后面字符组合的数据，类似于`[ 1 | 2 | 3 ] Ton`，不同于`1 | 2 | 3`，前者表示一个集合，后者是字符集，会匹配整个字符串，如需要匹配集合以外的数据，可以使用`[^xxx]`，表示匹配`xxx`以外的数据

集合也可以使用连字符要表示，如[0-9]表示匹配0到9中的任意一个数字，[a-z]表示匹配任意一个小写字母、

如果需要匹配特殊字符. | [] - \等，可以使用\\来进行转移，如REGEXP '\\.';：表示匹配包含.的数据，\\也可以用来引用元字符（具有特殊含义的字符）

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

字符类： 预定义的字符集

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

重复元字符： 用于匹配特定的数量，与其他匹配符组合使用，表示在其前面做何种匹配

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

SELECT prod_name FROM products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name; -- `\\(`和`\\)`表示匹配小括号`()`，`[0-9]`表示匹配数字，`?`表示前面有一个`s`或者没有，即可匹配的结果有`stick`和`sticks`
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name; -- `[:digit:]`表示任意数字，表示一个集合，`[[:digit:]]`表示匹配任意数字，`{4}`表示其前面的字符（数字）连续出现4次，该语句表示匹配连接在一起的连续4个数字(使用一个中括号也可以：[:digit:]{4})，功能同下语句
SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;

定位元字符： 用于特定位置的匹配

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

SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\\.]' ORDER BY prod_name; -- 表示匹配以数字或`.`开头的数据

注意'^[]'和'[^]'的区别，前者为文本开头，后者为取反

创建计算字段

Concat()函数：可用于拼接多个字段列之间的数据，如将第i列和第j列以某种形式拼接在一起输出到客户机，在客户机上做拼接操作会浪费大量时间，而在数据库检索时采用拼接操作会更加高效。

SELECT Concat(vend_name, '(',  vend_country, ')') FROM vendors ORDER BY vend_name; -- Concat用于拼接多个字符，输出结果为vend_name、(、vend_country、)拼接的结果，vend_name(vend_country)，拼接时各字符用逗号隔开

RTrim()函数：用于去掉数据右测多余的空格以整理数据，同时还可以使用LTrim()和Trim()来去掉左侧空格或两侧空格

列别名：将拼接的数据赋予别名，以便于客户机可以采用新的列名引用它

SELECT RTrim(vend_name) FROM vendors; -- 表示去掉检索到的vend_name字段数据的右测空格
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name; -- 将拼接后的vend_name(vend_country)数据命名为vend_title

算术计算：对检索出的数据进行算术计算

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

测试计算：可以使用SELECT测试函数与计算，如SELECT 3*2;返回6，SELECT NOW();返回当前时间

MySQL算术操作符包括加减乘除（+，-，*，/），优先顺序与四则运算一致

数据处理函数

文本处理函数：

Upper()函数：将本文转换为大写

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name; -- 将字段vend_name中的文本转换为大写并命名为vend_name_upcase

常用的文本处理函数

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

Soundex()函数用于将文本串转换为描述其语音表示的字母数字模式的算法，可以对串进行发音比较而不是字母比较。

SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y.Lie'; -- 若cust_contact中存在`Y.Lie`，但错误输入不存在的`Y.Lee`时，无法匹配，可以使用Soundex()函数来进行发音匹配，如下
SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y.Lee'); -- 由于采用Soundex()函数，因此可以匹配到`Y.Lie`数据行

常用日期和时间函数，首选日期格式为：yyyy-mm-dd

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

SELECT cust_id, order_num FROM orders WHERE order_date = '2005-09-01'; -- 表示检索到日期为`2005-09-01`的数据，由于日期的数据类型为`datetime`，会包含日期和时间，如果时间会为`00:00:00`，及`2005-09-01 00:00:00`，上述匹配会成功，如果时间不为0的情况下，上述匹配将会失败，更好的做法如下
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01'; -- 仅提取日期时间的日期部分进行匹配
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'; -- 匹配一个月内的数据
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9; -- 功能与上一条语句一样，该语句的优点是不需要记住每月有多少天

数值处理函数

一般主要用于代数、三角或几何运算，常用数值处理函数如下

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

汇总数据

AVG()函数：该函数可以返回某列、某些行的均值，若需要对所有列求均值，需要多次使该函数，该函数忽略NULL行

SELECT AVG(prod_price) AS avg_price FROM products; -- 返回prod_price列的均值并命名为avg_price
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003; -- 返回vend_id特定行(或几行)对应prod_price数据的均值，即返回某些行的均值

COUNT()函数：用于返回表中行数和特定行的数目，COUNT(*)表示统计所有行数，不管行中是否包含空值NULL还是非空值，COUNT(column)表示对特定列中所有值进行统计，忽略NULL值

SELECT COUNT(*) AS num_cust FROM customers; -- 表示返回表customers中的总行数，即客户总数
SELECT COUNT(cust_email) AS num_cust FROM customers; -- 表示统计cust_email列中非空值的行数，即邮件地址不为空的客户总数

MAX()函数：返回列中的最大值，需要指定列名

SELECT MAX(prod_price) AS max_price FROM products; -- 返回prod_price列中的最大值，即最贵的物品的价格，忽略值为NULL的行

MIN()函数：功能与MAX()函数相反，用于与之相同

SUM()函数：返回指定列的和，用法和MAX()函数相同

SELECT SUM(item_price * quantity) AS total_price FROM orderitems WHERE order_num = 20005;

组合聚集函数：组合使用多个函数

SELECT COUNT(*) AS num_items, 
	   MIN(prod_price) AS min_price, 
	   MAX(prod_price) AS max_price,
       AVG(prod_price) AS avg_price FROM products; -- 返回表的总行数，prod_price列的最小值、最大值和均值  

分组数据

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id; -- 可以对vend_id中的数据自动分组，使用GROUP BY子句时需要将其放在WHERE的后面，ORDER BY的前面，如果分组列中有NULL时，空值将会被单独分为一组
SELECT cust_id, COUNT(*) AS num_orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2; -- 返回分组后数量大于等于2的分组，HAVING和WHERE不同，WHERE是先过滤再分组，而HAVING则是先分组再过滤

SELECT子句顺序

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

子查询

适用于MySQL4.1及以后的版本

多个查询语句嵌套使用，用于关系表中多表联合查询，查询结果由内向外逐步返回

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');  --先从表 orderitems 检索满足物品要求的 order_num， 在从表 orders 中检索满足 order_num 要求的 cust_id

相关子查询：涉及外部查询的子查询，列明产生歧义时需要使用完全限定列名，即表名.列名

SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name; --orders.cust_id = customers.cust_id表示从不同表中利用相同的列名检索（由于两个cust_id产生歧义，因此需要使用完全限定列名）

联结表

在多个表中通过字段之间的关系查询，根据关系分表存储具有节省内存资源、管理简单、修改方便的优点

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name; --采用完全限定列名实现关系查询，该方式称为等值查询，也可以使用内联语法代替WHERE子句
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

笛卡尔积：当等值连接不是WHERE子句时，输出数量为两个表行数的乘积

多表联结

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 = 2005;

高级联结

给表起别名的优点：缩短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';

自联结：使用表别名实现表与自身的联结

-- 使用WHERE子句和子查询实现同一个表多次查询
SELECT prod_id , prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DINTR');
-- 使用别名实现自联结，同一个表（products）采用两个别名（p1，p2），此处必须指定是那个别名，若都是用products会产生歧义
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

外部联结

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id; --与内部联结不同的是外部联结包含没有关联的行，使用该语法时必须使用LEFT或RIGHT关键字指定其包含所有行的表，LEFT指定从左边的表（customers）选择所有行

带聚集函数的联结

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; -- 统计每个客户的下单数量，不包括未下单的（0）
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; -- 统计每个客户的下单数量，包括未下单的（0）

组合查询

使用场景：在单个查询中从不同的表返回类似结构的数据；对单个表执行多个查询，按单个查询返回数据

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); -- 使用关键字UNOIN联合查询，输出结果为两次查询结果的并集

-- 与上述语句同等功能的WHERE子句实现
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001, 1002);

UNION使用规则

• 必须由两条及以上的SELECT组成，语句之间使用关键字UNION分隔
• UNION中每个查询必须包含相同的列、表达试或聚集函数（次序可以不同）
• 列数据类型必须兼容

UNION ALL：单独使用UNION时查询的并集中重复的结果被取消，即只保留一条结果，如果想不取消重复的行可以使用UNION ALL，该工作是WHERE子句完成不了，即WHERE子句总是取消重复的行

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);   -- 返回结果不取消

组合查询结果排序：可以使用ORDER BY子句对查询结果进行排序，但该子句只能存在一个且必须放在最后一个SELECT语句之后

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002) ORDER BY vend_id, prod_price;   -- 使用ORDER BY 排序

全文本搜索

启用全文本搜索：通常在创建表是启用全文本搜索，也可以在后续指定，仅支持MyISAM数据库引擎

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;

进行全文本搜索

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit'); -- match() 用于指定被搜索的列，该值必须和FULLTEXT包含的值一致，如果指定多个列，需要按次序全部列出， Against() 指定要使用的搜索表达试，此处为要搜索的文本
-- 上述例子对应的LIKE子句实现
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';

全文本和LIKE的区别：LIKE检索返回的数据不是按顺序的，而全文本搜索返回按一定等级排序的数据，等级由行中词的数目、唯一词的数目、整个索引中词发总数以及包含该词的行的数目计算，且搜索速度快

扩展查询

SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION); -- 找出关于anvils的注释（不一定包含该词）

布尔文本搜索：检索要匹配的词、要排斥的词（如果某行包含该词则不返回，即使包含其他指定词也如此）、排列提示（指定某些词比其他词更重要，更重要的词等级更高）、表达试分组、其他内容（此种搜索方式可以在不使用FULLTEXT情况下使用）

SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE); -- -rope*指出排除包含rope*的行，即使包含heavy也不返回该行

全文本布尔操作符

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不出现
>	包含且增加等级值
<	包含且减少等级值
()	把词组成句子表达试（允许这些子表达式作为一个组被包含、排除和排列等）
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语（匹配整个短语以便包含或排除这个短语）

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 <carrot' IN BOOLEAN MODE); -- 搜索rabbit和carrot并增加前者等级，降低后者等级
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(combination)' IN BOOLEAN MODE); -- 搜索匹配safe和combination的行并降低后者等级

插入数据

INSERT：用于插入行到数据库表，有如下几种方式：

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

-- 插入单行数据
INSERT INTO customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL); -- 根据表列次序插入数据，在数据为空时用NULL代替，不能不写，该种方式过于依赖次序，是一种不安全的方式，当表中结构改变后，需要大量修改插入语句，应使用如下更为安全的插入语句
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); -- 该方式虽然繁琐，但是更安全，

省略列：以更安全的方式插入数据时，可以省略某些满足如下条件的列

• 列定义为允许NULL值（无或空值）
• 在表的定义中该处默认值，如果在插入时不给定值，将使用默认值

-- 插入多行数据：可以使用多条INSERT语句插入多行数据，每条语句用分号结束，以下提供一条插入语句插入多行数据写法
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 INTO customers (cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, 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; -- 检索出的数据按照前面插入的字段顺序依次插入，需要保证不能重复的列的值不重复，如cust_id, 否则插入出错，插入的数据行数为custnew中的数据，SELECT语句可以包含WHERE子句来过滤要插入的数据

更新和删除数据

更新（UPDATE）数据两种方式：

• 更新表中特定行
• 更行表中所有行

UPDATE基本组成：

• 要更新的表
• 列名和它们的新值
• 确定要更新行的过滤条件

注意使用WHERE子句，否则将更新整个表

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; -- 更新id为10005客户的邮件地址
UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 10005; -- 更新多个列，使用逗号隔开，如果不加WHERE限制，将会更新所有行
-- 如果在更新多个行时遇到错误则会恢复到修改之前，可以使用IGNORE关键字来忽略其中的某些错误，使得其他的行可以更新成功
UPDATE IGNORE customers...
-- 为了删除某个值可以将其设置为NULL
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;

删除（DELETE）数据两种方式：

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

注意使用WHERE子句，否则将删除整个表中的内容（不是表）

DELETE FROM customers WHERE cust_id = 10006; -- DELETE是删除整行而不是整列，如果需要删除整列可以使用UPDATE语句，如果想删除整个表中的内容可以使用速度更快的TRUNCATE 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;
-- 如果想要在表名不存在时才创建新表，可以使用IF NOT EXISTS
CREATE TABLE tablename IF NOT EXISTS ...
-- 使用 NOT NULL：表示该列在插入数据或更新数据时必须要有值，否则操作将会报错
-- 使用 NULL：默认为NULL，表示该列的值可有可无
-- PRIMARY KEY：指定表的主键，表示该列的值在表（该列中）必须唯一且不能为NULL，若使用多个主键则多个列的组合必须唯一,创建多个主键可以使用逗号分隔开，如PRIMARY KEY (cust_id, cust_name)
-- AUTO_INCREMENT：当表列中只需要值唯一且没有其他意义时，如行编号，每增加一行数据就给一个新的不重复的编号，此时可以使用AUTO_INCREMENT，每个表只允许有一个AUTO_INCREMENT的列（通常作为主键），如果某次使用INSERT插入该列的值（合法）时，后续的自动增量将从手动插入的值开始增加，可以使用LAST_INSERT_ID()函数获得最后一个AUTO_INCREMENT值，如SELECT LAST_INSERT_iD();

-- 指定默认值
CREATE TABLE tablename
(
   	fieldname int NOT NULL DEFAULT 1 -- 在创建表示指定默认值，当不给该值赋值时默认使用1填充该值，MySQL只支持常量不支持函数作为默认值 
) ENGINE=InnoDB;

引擎类型：MySQL中有多种引擎，在使用诸如SELECT语句时在引擎内部处理请求，不同的任务选择不同的引擎可以获得良好的功能和灵活性，在创建表时指定引擎，也可以不指定（即省略ENGINE=语句）而使用默认值（很可能是MyISAM）

• InnoDB：该引擎是一个可靠的事务处理引擎，不支持全文本搜索
• MyISAM：该引擎是一个性能极高的引擎，支持全文本搜索，但不支持事务处理
• MEMORY：该引擎在功能等同于MyISAM，但数据存储在内存而不是磁盘中，所以操作速度很快，适用于临时表

外键不能跨引擎使用

更新表（ALTER TABLE）：在表中存储数据以后不宜再更改表，因此在设计表时需要考虑完整以便于后期不在对表进行大的改动，修改表结构时需要给出如下信息

• 在ALTER TABLE之后给出要更改的表名，表名必须存在，否则将会报错
• 所做更改的列表

ALTER TABLE vendors ADD vend_phone  CHAR(20); -- 在vendors表中添加一个名为vend_phone的列，必须确定其数据类型
ALTER TABLE vendors DROP COLUMN vend_phone; -- 删除vendors表中的vend_phone的列

-- ALTER TABLE 一种常见的用途是定义外键
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);

小心使用ALTER TABLE，在做改动前应先做数据备份

删除表（DROP TABLE）：删除表而不是删除内容

DROP TABLE tablename; -- 删除表没有确认，删除后将永久删除该表，表不存在会报错

重命名表（RENAME TABLE）：

RENAME TABLE customers TO customers1; -- 重命名单个表(需要重命名的表必须存在)
RENAME TABLE customers TO backup_customers,
			vendors TO backup_vendors,
			products TO backup_products;

使用视图

适用于MySQL5及以后的版本

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

视图相当于一个虚拟的表，本身不包含数据，而是包含根据需要检索数据的查询，在视图创建之后可与表基本相同的方式使用它们，视图的规则和限制如下：

• 与表一样，视图必须唯一命名
• 对于可以创建的视图数目没有限制
• 为了创建视图，必须具有足够的访问权限
• 视图可以嵌套，即可以利用从其他视图中检索数据的查询中来构造一个视图、
• ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也包含ORDER BY，那么该视图中的ORDER BY将被覆盖
• 视图不能索引，不能有关联的触发器或默认值
• 视图可以和表一起使用

视图的创建

• 视图用CREATE VIEW语句来创建
• 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
• 用DROP删除视图，语法为DROP VIEW viewname;
• 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW，后者表示如果要更新的视图不存在则创建一个视图，如果存在则替换掉存在的视图

CREATE VIEW productcustomers AS SELECt cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num; -- 返回已订购了任意产品的所有客户的列表
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name; -- 按特定格式检索出的数据，如果需要多次使用这种格式的数据，创建一个视图
CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;  -- 过滤没有电子邮件的用户

使用视图与计算字段

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;  -- 计算每个订单中每种物品的总价

更新视图：通常视图用于检索而不是更新

视图的可以使用INSERT, UPDATE, DELETE对数据进行更新，更新一个视图将更新其基表，如果MySQL不能正确地确定被更新的及数据，则不允许更新视图，即视图定义有如下操作则不能更新视图：

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

使用存储过程

适用于MySQL5及以后的版本，存储过程实际上是一种函数

优点：简单、安全、高性能；缺点：编写复杂、可能没有创建存储过程的权限，只有使用权限

执行存储过程：

CALL productpricing(@pricelow,
                   @pricehigh,
                   @priceaverage);  -- 计算并返回最低、最高和平均价格，productpricing为存储过程名称，括号内为接收的参数
-- 调用后不会显示数据，需要用SELECT语句检索数据
SELECT @pricelow; -- 检索单个值
SELECT @pricelow, @pricehigh, @priceaverage; -- 检索多个值

创建存储过程：

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage FROM products;  -- 创建一个返回平均价格的存储过程
END;

-- 语句说明：
-- CREATE PROCEDURE productpricing()创建一个名为productpricing的存储过程，括号内可以写入参数也可以不写（不写时仍需要加上小括号）
-- BEGIN END用来限定存储过程体

当在命令行创建存储过程时如果在程序内使用;，则需要临时改变语句分隔符，否则出现语法错误

-- 如上述的创建过程内使用`;`分隔符，这在命令行程序中回车后会产生错误，需要做如下修改
DELIMITER //  -- 将结束分隔符临时改为// （只要不是/即可）
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage FROM products; 
END // -- 上面的创建语句使用新的结束分隔符，因此才程序内部使用`;`才能正确执行
DELIMITER ; -- 将结束分隔符修改为`;`

删除存储过程：

DROP PROCEDURE productpricing; -- 删除存储过程不需要加上小括号（），只需要给出存储过程名称即可
DROP PROCEDURE IF EXISTS productpricing; -- 表示存储过程存在时删除，不存在时不报错，上一条语句当存储过程不存在时会报错

使用参数：将存储过程体内的执行结果返回到特定的变量上

CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8, 2),  -- DECIMAL(a, b) a表示数字总长度(整数部分超过a-b位会报错)，b表示数字小数部分长度，若不指定b或b小于2，则自动补为2位，若超过2位则按设置的长度截取
    OUT ph DECIMAL(8, 2),  -- 不能通过一个参数返回多个行和列
    OUT pa DECIMAL(8, 2)   -- OUT用于从存储过程传出一个值，IN用于从外部传递给存储过程，INOUT传入和传出
)
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;
-- 如果是在命令行执行程序，需要临时修改结束分隔符

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

-- 调用存储过程
CALL ordertotal(20005, @total); -- 需要传入一个参数（IN）和列出输出（OUT）结果的参数

智能存储过程：考虑要获得一个与ordertotal一样的订单合计，但是需要针对其中的某些客户增加税收等操作，此时可以按如下步骤执行

• 获得原合计
• 把营业税有条件的添加到合计
• 返回合计（修改后的）

-- Name: ordertotal
-- Parameters: onnumber = order number 
--             taxable  = 0 if not taxable, 1 if taxable
--             ototal   = order total variable
CREATE PROCEDURE ordertotal(
    IN onnumber INT, 
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'  -- COMMENT关键字可有可无，有时将在SHOW PROCEDURE STATUS的结果中显示
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 = onnumber INTO total;
	-- Is this taxable
	IF taxable THEN
		SELECT total + (total / 100 * taxrate) INTO total;
	END IF;
	SELECT total INTO ototal;
END;
-- 调用上述存储过程
CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;

使用游标

适用于MySQL5及以后的版本，用于在检索出来的行中前进或后退一行或多行，是一种交互式应用，MySQL中的游标只能使用在存储过程

• 在使用前必须声明（定义）
• 声明后需要打开游标以供使用
• 对于填有数据的游标，根据需要取出（检索）各行
• 在结束游标使用时，必须关闭游标

创建游标

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR 
	SELECT order_num FROM orders;  -- 声明游标并定义相应的SELECT语句
END;

打开关闭游标

-- 打开游标
OPEN ordernumbers; -- 在处理open语句时执行查询，存储检索出的数据以供浏览和滚动
-- 关闭游标
CLOSE ordernumbers -- close释放游标使用的所有内部内存和资源，因此每个游标不再需要时都应该关闭

游标在关闭后，若不重新打开则无法使用，而声明只需一次，若不手动关闭，在存储过程结束（END）时会自动关闭

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;

使用游标

-- 例1：从游标中检索第一行
CREATE PROCEDURE processorders()
BEGIN 
	DECLARE o INT,
	DECLARE ordernumbers CURSOR
	FOR 
	SELECT order_num FROM orders;
	OPEN ordernumbers;
	FETCH ordernumbers INTO o; -- FETCH用来检索当前行的order_num(自动从第一行开始)到名为o的变量中，即此处将第一行赋给o
	CLOSE ordernumbers;
END;
-- 例2：循环检索数据，从第一行到最后一行
CREATE PROCEDURE processoeders()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8, 2);  
	DECLARE ordernumbers CURSOR  -- 局部变量必须在定义游标之前定义，而句柄必须在游标定义之后定义，否则将会出错
	FOR
	SELECT order_num FROM orders;
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 定义一个CONTINUE HANDLER句柄，当后面的条件出现时执行该行代码，当SQLSTATE ‘02000’出现时执行 SET done = 1，此处的条件指的是未找到一个条件，当REPEAT由于没有更多的行提供给其循环时会出现此条件
	CREATE TABLE IF NOT EXISTS ordertotals(
        order_num INT,
        total DECIMAL(8, 2)
    );
    
    OPEN ordernumbres;
    REPEAT
    	FETCH ordernumbers INTO o;
    	CALL ordertotal(o, 1, t);  -- 调用其他存储过程计算每个订单的带税合计并存储到新建的表中
    	INSERT INTO ordertotals(order_num, total) VALUES(o, t);
    	UNTIL done END REPEAT;
    CLOSE ordernumbers;
END;

触发器

适用于MySQL5及以后的版本，适用于某条语句在事件发生时自动执行的情况

触发器是MySQL响应DELETE, INSERT, UPDATE中任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句），其他的MySQL语句不支持触发器

创建触发器：创建时需要给出4条信息

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（DELETE, INSERT, UPDATE）
• 触发器何时执行（处理之前或之后）

最好保持每个数据库的触发器名唯一，只有表才支持触发器，视图和临时表不支持触发器

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @newpro; -- 创建名为newproduct的新触发器，此处表示在每次对products插入数据行时输出Product added

删除触发器

DROP TRIGGER newproduct;  -- 触发器不能更新和覆盖，修改一个触发器必须删除后重新创建

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 INTO @newder;  -- 当在orders中插入一行数据时会自动分配一个新的订单号并保存到order_num，触发器从NEW.order_num取得这个值并返回，由于每次都会生成新的订单号，因此返回的都是最新的订单号
-- 测试 使用SELECT @newder查看返回值
INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001);

DELETE触发器：该触发器在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;  -- 在任意订单被删除前执行此触发器，使用一条SELECT将OLD中的值保存到一个名为archive_orders的存档表中（需要用与orders相同的列名创建archive_orders表）

UPDATE触发器：该触发器在UPDATE语句之前或之后执行

• 在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问以前的值，引用一个名为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设为大写

管理事务处理

事务处理用来保证成批的MySQL操作要么完全执行要么完全不执行，避免某条语句（或一次操作）执行中途出错而不知情的情况，可以维护数据库的完整性，在InnoDB引擎下支持事务处理，MyISAM不支持

• 事务（transaction）：指一组SQL语句
• 回退（rollback）：指撤销指定SQL语句的过程，不可以回退SELECT, CREATE, DROP操作
• 提交（commit）：指将为存储的SQL语句结果写入数据库
• 保留点（savepoint）：指事务处理中设置的临时占位符（placeholder），可以对其发布回退

使用ROLLBACK：使用该命令来回退（撤销）MySQL语句

SELECT * FROM ordertotals;
START TRANSACTION; -- 标识事务的开始
DELETE FROM ordertotals;  -- 删除表中的所有行
SELECT * FROM ordertotals;
ROLLBACK;  -- 回退START TRANSACTION之后的所有语句，只能在一个事务处理内使用
SELECT * FROM ordertotals;

使用COMMIT：通常MySQL语句是自动执行提交（写或保存）操作的，而在事务处理中，需要进行明确的提交操作

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;  -- 用来提交上述的事务处理操作，如上面的两条删除语句其中一条失败则不会提交，删除失败
-- COMMIT和ROLLBACK语句执行后，事务会自动关闭

使用保留点：在复杂的事务处理中可能需要实现部分事务的提交和回退，此时需要设置一个占位符用于知晓回退到那个位置，该占位符也称为保留点

-- 创建占位符
SAVEPOINT delete1; -- 每个保留点都有其唯一的名字，便于回退时知道回退到何处
-- 回退到刚才创建的delete1
ROLLBACK TO delete1;
-- 释放保留点：在ROLLBACK和COMMIT后自动释放，也可以使用RELEASE SAVEPOINT明确释放保留点
-- 保留点可以任意多的设置，越多越能灵活的进行回退

更改默认提交：针对每个连接可以使用SET autocommit = 0;设置不自动提交更改，而是使用COMMIT手动提交。

安全管理

MySQL服务器的安全基础：应该给用户有限且合适的使用权限，如某些用户只能有读写权限、删除、更新权限等，以防止用户在使用过程中因误操作而导致数据库异常，应该尽可能少用root登录，仅在绝对需要时才使用它

管理用户

USE mysql;
SELECT user FROM user; -- 查看当前用户列表，在名为mysql的数据库中存在一个user表，保存所有用户的账户

创建账号

CREATE USER ben IDENTIFIED BY 'p@$$word'; -- 创建一个账号并给出一个登录口令，也可以不用给出口令（IDENTIFIED BY 'p@$$word'）
RENAME user ben TO bforta; -- 重命名一个用户

删除账号

DROP USER bforta; -- 删除一个账号及其有关权限，MySQL5之前的该语句只能删除账号而不能删除其有关权限，需要先使用REVOKE删除与账号有关的权限，然后再删除账号

设置访问权限（GRANT）：新设置的用户没有访问权限，只能登录MySQL，不能执行任何数据库操作，使用GRANT语句设置权限需要给出以下信息

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

SHOW GRANTS FOR bforta; -- 查看赋予用户的权限
GRANT SELECT ON crashcourse.* TO bforta; -- crashcourse（crashcourse.* ）所有表的SELECT权限，用户具对该数据库中的所有数据具有只读权限
REVOKE SELECT ON crashcourse.* FROM bforta; -- 撤销上述语句赋予的SELECT权限，撤销权限时必须具备该权限，否则将报错

-- 多次授权：可以使用逗号分隔需要授权的权限
GRANT SELECT, INSERT ON crashcourse.* TO bforta;

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

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

更改口令

SET PASSWORD FOR bforta = Password('n3w p@$$word'); -- 使用SET PASSWORD更改口令，口令必须传到Password()函数内进行加密
SET PASSWORD = Password(`n3w p@$$word`); -- 不指定用户名时用于设置当前登录用户的口令

改善性能

• 应该多试验联结、并、子集等操作来实现复杂查询语句，找出最佳的方法
• 执行存储过程比按单条语句执行要快
• 应该注意使用的数据类型是否正确
• 不检索比需求多的数据，即少用SELECT *检索所有数据（除非需要）
• LIKE很慢，通常使用FULLTEXT而不是LIKE

MySQL数据类型

串数据类型：存储常用的串数据类型，如名字、地址、电话号码和邮编等

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

任何形式的串数据类型，串值都必须包括在引号内（最好为单引号）

数字型数据使用的基本规则：若数值要用于计算（如求和、均值等）则使用数值数据类型，如果作为字符串使用，则应该保存在串数据类型中（可能只包含数字，如电话和邮编等）

数值数据类型：存储数值，UNSIGNED关键字允许存储不带该关键字时两倍大小的值（除BIT和BOOLEAN外均可用UNSIGNED）

数据类型	说明
BIT	位字段，1~64位
BIGINT	整数值
BOOLEAN(BOOL)	布尔标志，为0或1
DOUBLE	双精度浮点值
FLOAT	单精度浮点值
INT(INTEGER)	整数值，支持-2147483648~2147483647（UNSIGNED：0~4294967285）
MEDIUMINT	整数值，支持-8388608~8388607（UNSIGNED：0~16777215）
REAL	4字节浮点值
SMALLINT	整数值，支持 -32768~32767（UNSIGNED：0~65535）
TINYINT	整数值，支持 -128~127（UNSIGNED：0~255）
DECIMAL(DEC)	精度可变浮点值

数值类型数据不需要括在引号内，对于货币数值，通常使用DECIMAL(8, 2)

日期和时间数据类型：存储日期和时间值

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

二进制数据类型：可存储任何数据（包括二进制信息），如图像、多媒体和字处理文档等

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