MySQL必知必会查缺补漏

1、%和_通配符

• % 代表搜索模式中给定位置的0个、1个或多个字符;
  注意1：尾空格可能会干扰通配符匹配。如果它后面有一个或多个空格， 则子句WHERE prod_name LIKE '%anvil’将不会匹配它们，因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数$trim()$去掉首尾空格。
  注意2：虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行。
• _ 只匹配单个字符而不是多个字符;

2、正则表达式

1、 $.$ 匹配任意一个字符

$输入$

select name from product where name REGEXP '.000' ORDER BY name;

$输出$

name
jetpack_1000
jetpack_2000

2、 $|$ 进行OR匹配

$输入$

select name from product where name REGEXP '1000|2000' ORDER BY name;

$输出$

name
jetpack_1000
jetpack_2000

3、$[]$ 匹配几个字符之一

$输入$

SELECT
	name 
FROM
	s_teacher 
WHERE
	NAME REGEXP '[张王]';

$输出$

name
张三老师
王老师

• []是另一种形式的OR语句，事实上，正则表达式$[张王]$为$[张|王]$的缩写,也可以使用后者
• 如果不加[]，那就是匹配整个字符串

4、匹配范围（简化[0123456789]）

$输入$

SELECT
	id
FROM
	s_teacher 
WHERE
	id REGEXP '[0123456789]';
可以替换成
SELECT
	* 
FROM
	s_teacher 
WHERE
	id REGEXP '[0-9]';

$输出$

id
9
10
11
12

$输入$

还可以匹配任意字母
SELECT
	* 
FROM
	s_teacher 
WHERE
	id REGEXP '[a-z]';

5、匹配特殊字符

1、找出包含.字符的值

• 为了匹配特殊字符，必须用\为前导。\-表示查找-，\.表示查找.

$输入$

SELECT
	email
FROM
	s_teacher 
WHERE
	email REGEXP '\\.';

$输出$

email
123@qq.com
123@qq.com
123@qq.com

2、匹配字符类

3、匹配多个实例

4、匹配它前面的任何字符的0次或1次出现

$输入$

select username from `user` where username REGEXP '[a-z]est01?'

$输出$

username
test001
test002
test01

5、匹配连在一起的3位数字

$输入$

select username from `user` where username REGEXP '[:digit:]{3}'
也可以写成
select username from `user` where username REGEXP '[0-9][0-9][0-9]'

$输出$

username
test001
test002

6、定为符

7、以一个数（包括以小数点开始的数）开始的 ^

$输入$

select `password` from `user` where `password` REGEXP '^[0-9\\.]'

$输出$

password
.123456
123
123456

8、简单的正则表达式测试

$输入$

select 'hello' REGEXP '[a-z]';

$输出$
1
REGEXP检查总是返回0（没有匹配）或1（匹配）

3、计算

3、拼接 $Concat()$

oracle则使用 || 实现拼接

$输入$

select CONCAT(username, ',', password) from `user` where `password` REGEXP '^[0-9\\.]';

$输出$

CONCAT(username, ‘,’, password)
test01,.123456
test001,123456
test002,123456

4、清除空格（$LTRIM、RTRIM、TRIM$）

4、函数

1、将文本转大写 $UPPER$

$输入$

select CONCAT(UPPER(username), ',', password) from `user` where `password` REGEXP '^[0-9\\.]';

$输出$

CONCAT(username, ‘,’, password)
TEST01,.123456
TEST001,123456
TEST002,123456

2、常用的文本处理函数

3、$SOUNDEX$ 用法: 匹配所有发音类似于Y.Lie的联系名

SOUNDEX是一个将任何文
本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似
的发音字符和音节，使得能对串进行发音比较而不是字母比较

$输入$

select cust_contact from customers where cust_contact = 'Lie'

$输出$

cust_contact
Lee

4、常用的时间日期函数

5、检索出2005年9月下的所有订单,多种写法

$输入$

select cust_id, order_num from orders where Date(order_data) between '2005-09-01' and '2005-09-31'
另一种写法
select cust_id, order_num from orders where Year(order_data) = 2005 and Month(order_date) = 9

6、常用的数值处理函数

5、聚集函数

1、$AVG()$ 求平均值

$输入$

select AVG(weight) from user;

$输出$

AVG(weight)
65.75

NULL值 AVG()函数忽略列值为NULL的行

2、$COUNT()$ 计数

NULL值 如果指定列名，则指定列的值为空的行被COUNT()
函数忽略，但如果COUNT()函数中用的是星号（*），则不忽
略。

3、$MAX()$

NULL值 MAX()函数忽略列值为NULL的行。

对非数值数据使用MAX(),MySQL允许将它用来返回任意列中的最大
值，包括返回文本列中的最大值。在用于文本数据时，如果数
据按相应的列排序，则MAX()返回最后一行。例如MAX(update_date)返回最后更新时间.

4、$MIN()$

5、$SUM()$ 求和

NULL值 SUM()函数忽略列值为NULL的行。

6、分组（group by）

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

1、WITH ROLLUP 分组后的汇总

$输入$

select height, count(*) from user group by height WITH ROLLUP

$输出$

height	count(*)
1.75	3
1.76	1
	4

2、过滤分组 $having$

WHERE过滤行，而HAVING过滤分组

$输入$

select height, count(*) from user group by height HAVING height > 1.75

$输出$

height	count(*)
1.76	1

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

7、联结

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

1、内部联结

$输入$

select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id order by vend_name, prod_name

$输入$

select vend_name, prod_name, prod_price from vendors inner join products on vendors.vend_id = products.vend_id order by vend_name, prod_name

ANSI SQL规范首选INNER JOIN语法。
$性能考虑$： MySQL在运行时关联指定的每个表以处理联结。
这种处理可能是非常耗费资源的，因此应该仔细，不要联结
不必要的表。联结的表越多，性能下降越厉害。

2、内联和子查询的性能

$输入$

使用子查询：
select prod_id, prod_name from products where vend_id in (select vend_id from products where prod_id = 'DTN')
使用内联：
select p1.prod_id, p1.prod_name from products p1, products p2 where p1.vend_id = p2.vend_id and p2.prod_id='DTN'

用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一
下两种方法，以确定哪一种的性能更好。

3、左外和右外

left outer join 和 right outer join

8、组合 $union$ 和 $unionall$

UNION规则
1、UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
2、UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
3、列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

UNION从查询结果集中自动去除了重复的行。如果想返回所有匹配行，可使用UNION ALL而不是UNION。

9、全文本搜索

并非所有引擎都支持全文本搜索 两个最常使用的引擎为MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

$创建表输入$

CREATE TABLE `auth_` (
  `auth_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '授权ID：',
  `user_group` varchar(64) DEFAULT NULL COMMENT '用户组：',
  `mod_name` varchar(64) DEFAULT NULL COMMENT '模块名：',
  `table_name` varchar(64) DEFAULT NULL COMMENT '表名：',
  `page_title` varchar(255) DEFAULT NULL COMMENT '页面标题：',
  `path` varchar(255) DEFAULT NULL COMMENT '路由路径：',
  `position` varchar(32) DEFAULT NULL COMMENT '位置：',
  `mode` varchar(32) NOT NULL DEFAULT '_blank' COMMENT '跳转方式：',
  `add` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否可增加：',
  `del` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否可删除：',
  `set` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否可修改：',
  `get` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '是否可查看：',
  `field_add` varchar(500) DEFAULT NULL COMMENT '添加字段：',
  `field_set` varchar(500) DEFAULT NULL COMMENT '修改字段：',
  `field_get` varchar(500) DEFAULT NULL COMMENT '查询字段：',
  `table_nav_name` varchar(255) DEFAULT NULL COMMENT '跨表导航名称：',
  `table_nav` varchar(255) DEFAULT NULL COMMENT '跨表导航：',
  `option` text COMMENT '配置：',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间：',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间：',
  PRIMARY KEY (`auth_id`) USING BTREE,
	FULLTEXT(field_add)
) ENGINE=MyISAM COMMENT='定制授权';

不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然
不是很多，但毕竟要花时间。如果正在导入数据到一个新表，
此时不应该启用FULLTEXT索引。应该首先导入所有数据，然
后再修改表，定义FULLTEXT。这样有助于更快地导入数据

$输入$

select field_add from auth_ where MATCH(field_add) against('full_name')

$输出$

field_add
full_name,gender
full_name,gender
full_name,gender
full_name,gender
upload_attachments,complaint_content,complaint_type,order_number,complaint_about_vehicle_license_plate,full_name,user,complaint_platform
upload_attachments,complaint_content,complaint_type,order_number,complaint_about_vehicle_license_plate,full_name,user,complaint_platform
upload_attachments,complaint_content,complaint_type,order_number,complaint_about_vehicle_license_plate,full_name,user,complaint_platform
user,full_name,complaint_platform,complaint_about_vehicle_license_plate,order_number,complaint_type,upload_attachments,date,complaint_progress,complaint_content,complaint_response

1、全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。

$输入$

select field_add, MATCH(field_add) against('full_name') from auth_;

$输出$

field_add	field_add
full_name,gender	0.49934080243110657
full_name,gender	0.49934080243110657
complaint_platform	0
…

10、TRUNCATE

更快的删除 如果想从表中删除所有行，不要使用DELETE。
可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更
快（TRUNCATE实际是删除原来的表并重新创建一个表，而不
是逐行删除表中的数据）。

11、理解NULL

不要把NULL值与空串相混淆。NULL值是没有值，
它不是空串。如果指定’'（两个单引号，其间没有字符），这
在NOT NULL列中是允许的。空串是一个有效的值，它不是无
值。NULL值用关键字NULL而不是空串指定。

主键和NULL值 主键为其值唯一标识表中每个
行的列。主键中只能使用不允许NULL值的列。允许NULL值的
列不能作为唯一标识。

12、确定AUTO_INCREMENT值

可使用last_insert_id()函数获得这个值，如下所示：

select last_insert_id()

此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于
后续的MySQL语句。

13、使用默认值而不是NULL值

许多数据库开发人员使用默认
值而不是NULL列，特别是对用于计算或数据分组的列更是如
此。

14、引擎类型

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

15、外键不能跨引擎

混用引擎类型有一个大缺陷。外键（用于
强制实施引用完整性，如第1章所述）不能跨引擎，即使用一
个引擎的表不能引用具有使用不同引擎的表的外键。

16、视图

为什么使用视图
1、重用SQL语句
2、简化复杂的SQL操作
3、使用表的组成部分而不是整个表
4、保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5、更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

性能问题：如果你用多个联结
和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能
下降得很厉害。因此，在部署使用了大量视图的应用前，应
该进行测试

1、创建视图

$输入$

create view productcustomers as
	select cust_name, cust_contact, prod_id 
	from customers, orders, orderitems
	where customers.cust_id=orders.cust_id 
		and orders.order_num=orderitems.order_num

2、使用视图

$输入$

select cust_name, cust_contact from productcustomers where prod_id='TNT2'

$输出$

cust_name	cust_contact

3、视图的更新

视图是可更新的

但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

• 分组
• 联结
• 子查询
• 并
• 聚集函数
• distinct
• 计算

将视图用于检索： 一般，应该将视图用于检索（SELECT语句）
而不用于更新（INSERT、UPDATE和DELETE）。

17、存储过程

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

1、简化复杂的操作。通过把处理封装在容易使用的单元中
2、防止错误保证了数据的一致性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。
3、安全性，简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
4、提高性能。因为使用存储过程比使用单独的SQL语句要快
5、存储过程可以使用它们来编写功能更强更灵活的代码

2、创建存储过程

$输入$

CREATE PROCEDURE authmodname()
BEGIN
	select mod_name from `auth` GROUP BY mod_name;
END;

3、执行存储过程

$输入$

CALL authmodname();

$输出$

field_add
普通用户
普通管理员
我的投诉
投诉平台分类
我要联系
我的收藏
评论
新闻
新闻分类

4、删除存储过程

$输入$

DROP PROCEDURE authmodname;

如果指定的过程不存在，则DROP PROCEDURE
将产生一个错误。当过程存在想删除它时（如果过程不存在也
不产生错误）可使用DROP PROCEDURE IF EXISTS。

5、使用参数接收返回值

$输入$

CREATE PROCEDURE productpricing(
	out pl decimal(8,2),
	out ph decimal(8,2),
	out pa decimal(8,2),
)
BEGIN
	select min(prod_price) into pl from products;
	select max(prod_price) into ph from products;
	select avg(prod_price) into pa from products;
END;

$调用输入$

CALL productpricing(@pricelow, @pricehign, @priceabg);
在调用时，这条语句并不显示任何数据。

$检索输入$

select @pricelow, @pricehign, @priceabg;

$输出$

pricelow	pricehign	priceabg
55.00	1.50	16.133

6、接收参数

$输入$

CREATE PROCEDURE productpricing(
	in number int,
	out total decimal(8,2)
)
BEGIN
	select sum(item_price * quantity) from products where order_num > number into total;
END;

$调用输入$

CALL productpricing(100, @total);
在调用时，这条语句并不显示任何数据。

$检索输入$

select @total;

$输出$

total
150

7、建立智能存储过程

CREATE PROCEDURE ordertotal(
	in inumber int,
	in itaxable boolean,
	out ototal decimal(8,2)
) comment '根据税率计算总金额'
BEGIN
	declare total decimal(8, 2);
	// 默认税率
	declare taxable int default 6;
	select sum(item_price * quantity) from orderitems where order_num > number into total;	
	if itaxable then 
		select total + (total/100*taxable) into total;
	end if;
	select total into ototal;
END;

$调用输入$

CALL ordertotal(1001,1,@total);
默认0：false 1：true

$输出$

total
158

8、显示存储过程的$create$语句

show create procedure ordertotal;

show procedure status like 'ordertotal';

9、游标

1、取出数据添加入新表

$输入$

新建表
CREATE TABLE IF NOT EXISTS contrycode (contrycode VARCHAR(255));

创建存储过程
DROP PROCEDURE collect_country;

CREATE PROCEDURE collect_country()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE country_code VARCHAR(255);

    DECLARE countries CURSOR FOR
    SELECT CountryCode FROM `city` GROUP BY CountryCode;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    OPEN countries;

    REPEAT
        FETCH countries INTO country_code;
        INSERT INTO contrycode (contrycode) VALUES (country_code);
    UNTIL done END REPEAT;

    CLOSE countries;
END;

$调用输入$

CALL collect_country();

$查看新表$

18、触发器

1、insert

对于orders的每次插入使用这个触发器将总是返回新的订单号。

$输入$

create trigger neworder after insert on orders
for each row select new.order_num;

$测试$

insert into orders(order_date, cust_id) values (now(), 10001)

$输出$

order_num
20010

2、update

保证名称的缩写总是大写

• 在update触发器代码内，可以饮用一个名为OLD的虚拟表，访问被更新的行。引用一个名为NEW的虚拟表访问新更新的值

$输入$

create trigger updatevendor before update on vendors
for each row 
set NEW.vend_state = upper(NEW.vend_state);
每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。

3、delete

将要被删除的行到一个存档表中

• 在delete触发器代码内，可以饮用一个名为OLD的虚拟表，访问被删除的行。

$输入$

create trigger delorder 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;

4、删除触发器

$输入$

drop trigger newproduct;

触发器不能更新或者覆盖。

19、mysql数据备份

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

为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。