mysql必知必会看完看什么_读书笔记：MySQL必知必会

Q&A

1、多个sql查看在数据磁盘位置

show variables like '%datadir%';

2、mysql中char与varchar的区别

char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：

char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．(在检索操作中那些填补出来的空格字符将被去掉)在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节)。

show VARIABLES like "character%%";

mysql数据库检索

库>表>列(数据类型)>行(记录)

不能部分使用 DISTINCT DISTINCT 关键字应用于所有列而

不仅是前置它的列。如果给出 SELECT DISTINCT vend_id,

prod_price ，除非指定的两个列都不同，否则所有行都将被

检索出来。

SELECT vend_id,prod_name from products;

SELECT DISTINCT vend_id from products;

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

将检索出第二行而不是第一行。

SELECT vend_id,prod_name from products LIMIT 2,3;

可以加上表名，库名

SELECT products.vend_id,prod_name from products LIMIT 2,3;

排序

先排序prod_price,前面一样的接着排序prod_name

SELECT prod_id,prod_price,prod_name from products ORDER BY prod_price,prod_name;

ORDER BY 子句以降序(从 Z 到 A )顺序排序。为了进行降序排序，

必须指定 DESC 关键字。DESC 关键字只应用到直接位于其前面的列名。

SELECT prod_id,prod_price,prod_name from products ORDER BY prod_price DESC;

组合运用。

SELECT prod_price,prod_name from products ORDER BY prod_price DESC LIMIT 1;

过滤数据

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

SELECT vend_id,prod_price,prod_name from products WHERE vend_id=1001;

SELECT vend_id,prod_price,prod_name from products WHERE vend_id !=1001 ORDER BY vend_id ;

范围检查

SELECT prod_name,prod_price from products where prod_price BETWEEN 5 and 10;

null 检查用 is null。

数据过滤

MySQL允许给出多个 WHERE 子句。这些子

句可以两种方式使用：以 AND 子句的方式或 OR 子句的方式使用。 and优先级更高

select vend_id,prod_name,prod_price from products where (vend_id=1002 or vend_id=1003) and prod_price >=10;

IN 操作符完成与 OR 相同的功能

in(1001,1003) == 1001 or 1003

MySQL支持使用NOT 对 IN 、 BETWEEN 和

EXISTS子句取反，这与多数其他 DBMS允许使用 NOT 对各种条件

取反有很大的差别。

select vend_id,prod_name from products where vend_id in(1002 ,1003);

select vend_id,prod_name from products where vend_id not in(1002 ,1003);

通配符进行过滤

like

% 表示任何字符出现任意次数。 _一次

select prod_id,prod_name from products where prod_name like 'jet%';

select prod_id,prod_name from products where prod_name like 'j_tPack 1000';

正则表达式

正则：关键字 LIKE 被 REGEXP 替代外，语句看上去非常像使用LIKE 的语句

select prod_name from products where prod_name REGEXP '1000'; #包含即可

select prod_name from products where prod_name like '1000'; #完全匹配

select prod_name from products where prod_name REGEXP '1000|2000'; |类似or

SELECT prod_name from products where prod_name REGEXP '[123] ton anvil'; []里面表示可选项

正则表达式 [1-5] Ton 。 [1-5] 定义了一个范围，这个

表达式意思是匹配 1 到 5 ，因此返回3个匹配行

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

SELECT vend_name from vendors where vend_name REGEXP '\\.';

SELECT prod_name from products where prod_name REGEXP '\\([0-9] sticks?\\)';

select prod_name from products where prod_name REGEXP '^[0-9\\.]';

利用定位符，通过用 ^ 开始每个表达式，用 $ 结束每个表达式，可以使

REGEXP 的作用与 LIKE 一样。

计算字段

Concat() 拼接串

select vend_name,vend_country,CONCAT(vend_name,vend_country) from vendors;

result：

Anvils R UsUSAAnvils R UsUSA

LT SuppliesUSALT SuppliesUSA

ACMEUSAACMEUSA

Furball Inc.USAFurball Inc.USA

Jet SetEnglandJet SetEngland

Jouets Et OursFranceJouets Et OursFrance

加括号

select vend_name,vend_country,CONCAT(vend_name,'(',vend_country,')') from vendors;

Trim 函数 MySQL除了支持 RTrim() (正如刚才所见，它去掉

串右边的空格)，还支持 LTrim() (去掉串左边的空格)以及

Trim() (去掉串左右两边的空格)。

别名便于理解

count(*) AS vend_title

算术计算

SELECT prod_id,quantity,quantity+3 as add3 from orderitems where order_num = 20005;

数据处理函数

文本处理函数

SELECT vend_name,Upper(vend_name) as big from vendors;

Anvils R UsANVILS R US

LT SuppliesLT SUPPLIES

ACMEACME

Furball Inc.FURBALL INC.

Jet SetJET SET

Jouets Et OursJOUETS ET OURS

日期和时间处理函数

数值处理函数

汇总数据

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

SELECT prod_price from products ORDER BY prod_price;

SELECT AVG(prod_price) as avg_price from products;

COUNT()函数有两种使用方式。优先级超级高

1、 使用 COUNT(*) 对表中行的数目进行计数，不管表列中包含的是空

值( NULL )还是非空值。

2、使用 COUNT(column) 对特定列中具有值的行进行计数，忽略

NULL 值。

select count(*) as num_cust from customers;

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

虽然 MAX() 一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则 MAX() 返回最后一行。

select min(cust_city) as num_cust from customers;

max，min必须有参数

sum()

select sum(quantity) as items_orderd from orderitems where order_num=20005; #个数里面内容求和

select count(quantity) as items_orderd from orderitems where order_num=20005; #记个数

只包含不同的值，指定 DISTINCT 参数。

SELECT avg(prod_price) as sameprice from products;

SELECT avg(DISTINCT prod_price) as onlyprice from products;

组合

分组

GROUP BY 子句和 HAVING 子句

分组结合count()使用就比较安全

select vend_id,count(*) as num_prods from products GROUP BY vend_id;

select vend_id,count(*) as num_prods from products; #错误使用方法

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

过滤分组

WHERE 没有分组的概念。 HAVING 非常类似于 WHERE 。唯一的差别是

WHERE 过滤行，而 HAVING 过滤分组。分组后才能使用

SELECT cust_id,COUNT(*) as orders from orders GROUP BY cust_id HAVING COUNT(*) >=2;

SELECT cust_id,COUNT(*) as orders from orders GROUP BY cust_id;

超级复合语句

select vend_id,count(*),min(prod_price) as num_prods from products where prod_price >=10 GROUP BY vend_id HAVING COUNT(*) >=2;

一般在使用 GROUP BY 子句时，应该也给

出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万

不要仅依赖 GROUP BY 排序数据。

子查询

一个查询结果作为另一个条件

select order_num from orderitems where prod_id = 'TNT2';

select cust_id from orders where order_num in(20005,20007);

组合如下：

select cust_id from orders where order_num in(select order_num from orderitems where prod_id = 'TNT2');

联结表

外键(foreign key) 外键为某个表中的一列，它包含另一个表

的主键值，定义了两个表之间的关系。

简单地说，联结是一种机制，用来在一条 SELECT

语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返

回一组输出。

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;

select vend_name,prod_name,prod_price from vendors,products;

应该保证所有联结都有 WHERE 子句，否

则MySQL将返回比想要的数据多得多的数据，会a*b。

可联结多个表，但是不要联结

不必要的表。联结的表越多，性能下降越厉害。

创建高级联结

别名

select vend_name,prod_name from vendors as v INNER JOIN products as p on v.vend_id=p.vend_id ORDER BY

vend_name,prod_name;

1、自联结，自己取别名(类似子查询，自己细分自己)

自连接(self join)是语句中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。

select p1.prod_id,p2.prod_name from products as p1,products as p2 where p1.vend_id=p2.vend_id AND

p2.prod_id='DTNTR'; #取并集

#对比，少了子查询条件

select p1.prod_id,p2.prod_name from products as p1,products as p2 where p2.prod_id='DTNTR';

#对比

select prod_id,prod_name from products where prod_id='DTNTR';

自然联结

……

外部联结

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; #外部联结

#对比，结果不一样

SELECT customers.cust_id,orders.order_num from customers right OUTER JOIN orders on customers.cust_id=

orders.cust_id;

在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字

指定包括其所有行的表(RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT

指出的是 OUTER JOIN 左边的表，谁是参照物)。上面的例子使用 LEFT OUTER JOIN 从 FROM子句的左边表( customers 表)中选择所有行。为了从右边的表中选择所有行，应该使用 RIGHT OUTER JOIN。

组合查询

union

SELECT vend_id,prod_id FROM products where prod_price <=5;

SELECT vend_id,prod_id from products where vend_id in(1001,1002);

#结果放一起，不并，加

SELECT vend_id,prod_id FROM products where prod_price <=5 UNION SELECT vend_id,prod_id from products where vend_id in(1001,1002);完整的select语句

select distinct *

from 表名

where ....

group by ... having ...

order by ...

limit star,count #分页执行顺序为：from 表名

where ....

group by ...

select distinct *

having ...

order by ...

limit star,count

全文本搜索(性能高)

并非所有的引擎都支持本书所描

述的全文本搜索。两个最常使用的引擎为MyISAM 和 InnoDB ，

前者支持全文本搜索，而后者不支持。

为了进行全文本搜索，MySQL根据子句 FULLTEXT(note_text) 的指示对它进行索引。这里的

FULLTEXT 索引单个列，如果需要也可以指定多个列。

SELECT note_text from productnotes where match(note_text) AGAINST('rabbit');

数据插入

更新和删除数据

不要省略 WHERE 子句 在使用 UPDATE 时一定要注意细心。因为

稍不注意，就会更新表中所有行；

不要省略 WHERE 子句 在使用 DELETE 时一定要注意细心。因为

稍不注意，就会错误地删除表中所有行。

MySQL没有撤销(undo)按钮。应该非常小心地

使用 UPDATE 和 DELETE ，否则你会发现自己更新或删除了错误

的数据。

为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，

为不同的任务选择正确的引擎能获得良好的功能和灵活性。

使用视图

视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查

询。视图提供了一种MySQL的 SELECT 语句层次的封装，可用来简化数据

处理以及重新格式化基础数据或保护基础数据。

视图可以隐藏复杂的SQL，保护数据。

视图的创建:

 视图用 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,orderitems

where customers.cust_id = orders.cust_id

and orderitems.order_num = orders.order_num;

#条语句创建一个名为 productcustomers 的视图，它联结三个

表，以返回已订购了任意产品的所有客户的列表。如果执行

SELECT * FROM productcustomers ，将列出订购了任意产品的客户。

select cust_name,cust_contact

from productcustomers where prod_id ='TNT2';

格式化

select vend_name,vend_country,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;

select * FROM vendorlocations;

使用存储过程(函数)

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

-- 存储过程

CREATE PROCEDURE productpricing()

BEGIN

select avg(prod_price) as priceaverage from products;

end;

-- 使用

CALL productpricing();

-- 删除

DROP PROCEDURE productpricing;

使用游标

游标(cursor)是一个存储在MySQL服务器上的数据库查询，

它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

使用触发器

想要某条语句(或某些语句)在事件发生时自动执行

如：

 每当增加一个顾客到某个数据库表时，都检查其电话号码格式是

否正确，州的缩写是否为大写；

 每当订购一个产品时，都从库存数量中减去订购的数量；

 无论何时删除一行，都在某个存档表中保留一个副本。

管理事务处理

MyISAM 和InnoDB是两种最常使用

的引擎。前者不支持明确的事务处理管理，而后者支持。

几个术语：

 事务( transaction )指一组SQL语句；

 回退( rollback )指撤销指定SQL语句的过程；

 提交( commit )指将未存储的SQL语句结果写入数据库表；

 保留点( savepoint )指事务处理中设置的临时占位符(place-

holder)，你可以对它发布回退(与回退整个事务处理不同)。

哪些语句可以回退？ 事务处理用来管理 INSERT 、 UPDATE 和

DELETE 语句。你不能回退 SELECT 语句。(这样做也没有什么意

义。)你不能回退 CREATE 或 DROP 操作。

全球化和本地化

安 全 管 理

访问控制

不应该在日常的MySQL操作中使用 root 。

数据库维护

备份数据

改 善 性 能

1、一段时间后你可能需要调整内存分配、缓冲区大

小等。(为查看当前设置，可使用 SHOW VARIABLES; 和 SHOW

STATUS; 。)

2、总是有不止一种方法编写同一条 SELECT 语句。应该试验联结、并、

子查询等，找出最佳的方法。

3、 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL

语句快。

4、决不要检索比需求还要多的数据。换言之，不要用 SELECT * (除

非你真正需要每个列)。

5、你的 SELECT 语句中有一系列复杂的 OR 条件吗？通过使用多条

SELECT 语句和连接它们的 UNION 语句，你能看到极大的性能改

进。

6、LIKE 很慢。一般来说，最好是使用 FULLTEXT 而不是 LIKE 。

7、最重要的规则就是，每条规则在某些条件下都会被打破，活学活用。

附录

数据类型，保留字……

好书，后面几张看的草率，值得慢慢看，研究！