vlambda博客
学习文章列表

原书：MySQL必知必会，(英)Ben Forta 2009年1月

• 最近感觉SQL技能严重不足，希望较为系统的学习和了解在存储过程等方面的内容。

一、笔记列表

1. 数据库的基本概念

1. 
   

   数据库（database）：保存有组织的数据的容器（通常是一个文 件或一组文件）。

   
   

2. 
   

   表（table）：某种特定类型数据的结构化清单。

   
   

3. 
   

   模式（schema）：关于数据库和表的布局及特性的信息。表具有一些特性，这些特性定义了数据在表中如何存储，如可以存 储什么样的数据，数据如何分解，各部分信息如何命名，等等。描述表 的这组信息就是所谓的模式，模式可以用来描述数据库中特定的表以及 整个数据库（和其中表的关系）。

   
   

4. 
   

   列（column）：表中的一个字段。所有表都是由一个或多个列组 成的

   
   

5. 
   

   数据类型（datatype）：所容许的数据的类型。每个表列都有相 应的数据类型，它限制（或容许）该列中存储的数据。

   
   

6. 
   

   行（row）：表中的一个记录。如果将表想象为网格，网格中垂直的列为表列，水平行为表行。

   
   

7. 
   

   主键（primary key）：一列（或一组列），其值能够唯一区分表中每个行。全国科学技术名词审定委员会审定的key在数据库中的对应名词为“键码”或“码。

   
   

   
   

   
   

1. 主键通常定义在表的一列上，但这并不是必需的，也可以一起使用多个列作为主键。在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的（但单个列的值可以不唯一）。

8. 
   

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

   
   

9. 
   

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

   
   

10. 
    

    SQL(MySQL)语句的关键字顺序

    
    

    
    

• select

• from

• join/left join/right join

• on

• where

• group by

• having

• order by

• limit

11. 
    

    where的条件

    
    

 
   
   
 

  
    
    
  
 基本操作符：=、!=、<>、<、<=、>、>=、between
  
    
    
  
 空值： is null
  
    
    
  
 逻辑操作符： and、or、in、not
  
    
    
  
 通配符：like
  
    
    
  
 使用%：表示任何字符出现任意次数
  
    
    
  
 使用_：只匹配单个字符而不是多个字符
  
    
    
  
 正则表达式：regexp
  
    
    
  
 进行or匹配：使用|来设置多个条件，如 regexp "100|200"
  
    
    
  
 匹配几个单一字符：使用[]限定范围,<a href="#footnote-123"><sup>[123]</sup></a>真是表示匹配除123外的数据
  
    
    
  
 匹配正则表达式特殊字符：使用\\转义符，\\f换页,\\n换行,\\r回车,\\t制表,\\v纵向制表
 
   
   
 

• 
  

  MySQL正则匹配预定义字符

  
  

• 
  

  正则表达式重复元字符

  
  

• 
  

  定位元字符

  
  

1. 
   

   创建计算字段

   
   

1. 拼接字段：Concat()函数。

2. 去除左右空格：RTrim()、LTrim()

3. 算术计算：+-*/，两个字段间

2. 
   

   数据处理函数

   
   

   
   

   
   

1. Abs():返回一个数的绝对值

2. Cos():返回一个角度的余弦

3. Exp():返回一个数的指数值

4. Mod():返回除操作的余数

5. Pi():返回圆周率

6. Rand():返回一个随机数

7. Sin():返回一个角度的正弦

8. Sqrt():返回一个数的平方根

9. Tan():返回一个角度的正切

10. AddDate():增加一个日期（天、周等）

11. AddTime():增加一个时间（时、分等）

12. CurDate():返回当前日期

13. CurTime():返回当前时间

14. Date():返回日期时间的日期部分

15. DateDiff():计算两个日期之差

16. Date_Add():高度灵活的日期运算函数

17. Date_Format():返回一个格式化的日期或时间串

18. Day():返回一个日期的天数部分

19. DayOfWeek():对于一个日期，返回对应的星期几

20. Hour():返回一个时间的小时部分

21. Minute():返回一个时间的分钟部分

22. Month():返回一个日期的月份部分

23. Now():返回当前日期和时间

24. Second():返回一个时间的秒部分

25. Time():返回一个日期时间的时间部分

26. Year():返回一个日期的年份部分

27. left(): 返回串左边的字符

28. length():返回串的长度

29. locate():找出串的一个子串

30. lower():将串转换为小写

31. ltrim():去掉串左边的空格

32. right():返回串右边的字符

33. rtrim():去掉串右边的空格

34. soundex():返回串的soundex值

35. substring():返回子串的字符

36. upper():将串转换为大写

37. 文本处理函数

    
    

    
    

38. 日和时间处理函数

    
    

    
    

39. 数据处理函数

    
    

    
    

    
    

    
    

    
    

3. 
   

   聚集函数

   
   

   
   

   
   

• 计算和返回单个值的函数

1. AVG():返回某列的平均值

2. COUNT():返回某列的行数

3. MAX():返回某列的最大值

4. MIN():返回某列的最小值

5. SUM():返回某列值之和







4. 
   

   分组数据

   
   

   
   

• group by

1. group by后可以跟with rollup关键字，用于得到每个分组的汇总（每个组的总计）

2. having和where都是用于过滤，很多where语句可以用having代替。where过滤行（在分组前进行过滤），having过滤分组（在分组后进行过滤）。

5. 
   

   子查询

   
   

 
   
   
 

  
    
    
  
select *
  
    
    
  
from t
  
    
    
  
where id in (select id from t1 where ...)
 
   
   
 

• 可以使用嵌套多层子查询，但建议不要太多

• 通常子查询返回单个列并进行匹配，也可以使用多个列

• 通常子查询与in操作符结合使用，但也可以用于=、<>等操作

• 子查询也可以出现在计算字段中，在计算字段中关联其他表

1. 自联结、自然联结、外部联结

 
   
   
 

  
    
    
  
选出table1含有p_name='abc'的，所有id及其p_name记录
  
    
    
  
select a.id,a.name,a.p_name
  
    
    
  
from table1 as a, table1 as b
  
    
    
  
where a.id=b.id and b.p_name='abc'
 
   
   
 

以下观点不一定正确

• 自联结：同一个表之间关联进行取数

• 自然联结：可以理解为两个表直接关联后，所有列直接展示

• 外部联结：联结包含了那些在相关表中没有关联行的行，这种类型的联结成为外部联结（left / right join）

1. 创建组合查询

• 使用union操作符组合多条SQL查询

• union all和union一个不去重，一个去重

1. 全文本搜索

• create table时，使用fulltext()指定启用全文本搜索的字段

• 不要在导入数据时使用fulltext，导入后再修改表定义fulltext，否则容易造成性能问题

• 全文本搜索：使用where match("查询的字段") against("关键词")进行数据筛选

• 全文本搜索和like得出的结果基本一致

• 使用查询扩展：使用where match("查询的字段") against("关键词" with query expansion)进行查询扩展筛选。【查询扩展貌似是：对查询命中的条目中的文本再去匹配其他行，获取这些文本匹配的结果】

• 布尔文本搜索： against("关键词 -排除的关键词*" in boolean mode)

1. 插入数据

• 使用insert语句插入数据

• 插入完整的行

• 插入行的一部分

• 插入多行

• 插入某些查询的结果

 
   
   
 

  
    
    
  
# 可以插入多条记录，括号和逗号隔开
  
    
    
  
insert into tablename
  
    
    
  
 (columns1,
  
    
    
  
 columns2,
  
    
    
  
 columns3
  
    
    
  
 )
  
    
    
  
values
  
    
    
  
 ('value1',
  
    
    
  
 'value2',
  
    
    
  
 'value3'
  
    
    
  
 ),
  
    
    
  
 ('value1',
  
    
    
  
 'value2',
  
    
    
  
 'value3'
  
    
    
  
 );
 
   
   
 

• 可以省略列名，但是最好不要，容易出错。

• 可以使用insert low_priority into来降低insert语句的优先级

• 从一个表中查询某些数据插入到另一个表，不用关心select对应的列名，只需要对应好顺序

 
   
   
 

  
    
    
  
insert into tablename
  
    
    
  
 (columns1,
  
    
    
  
 columns2,
  
    
    
  
 columns3
  
    
    
  
 )
  
    
    
  
select 
  
    
    
  
 columns1,
  
    
    
  
 columns2,
  
    
    
  
 columns3
  
    
    
  
from tablename2;
 
   
   
 

1. 更新和删除数据

• 使用update语句更新表中的数据

• 更新表中特定行

• 更新表中所有行

 
   
   
 

  
    
    
  
update customers
  
    
    
  
set cst_name='aaa'
  
    
    
  
 cst_email='[email protected]'
  
    
    
  
where cst_id='121';
 
   
   
 

• 使用delete语句删除数据

• 从表中删除特定的行

• 从表中删除所有行

 
   
   
 

  
    
    
  
delete from customers
  
    
    
  
where cst_id='112'
 
   
   
 

1. 创建和操纵表

• 使用create table创建表，必须给出以下信息

• 新表的名字，在create table之后

• 表列的名字和定义，用逗号分隔

 
   
   
 

  
    
    
  
create table suctomers if not exists
  
    
    
  
(
  
    
    
  
 cst_id int not null auto_increment,
  
    
    
  
 cst_name char(50),
  
    
    
  
 cst_email char(255),
  
    
    
  
 primary key (cst_id)
  
    
    
  
) engine=innodb;
 
   
   
 

• auto_increment： 指定一个自增字段，每个表只能指定一个自增字段

• default： 指定默认值

• engine= innodb 或myisam ： 引擎类型

• InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索

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

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

• 删除表

 
   
   
 

  
    
    
  
drop table customers2;
 
   
   
 

• 重命名表

 
   
   
 

  
    
    
  
rename table customers2 to customers;
 
   
   
 

1. 使用alter table更新表

• 添加列

 
   
   
 

  
    
    
  
alter table table1
  
    
    
  
add phone char(20);
 
   
   
 

• 删除列

 
   
   
 

  
    
    
  
alter table table1
  
    
    
  
drop column phone;
 
   
   
 

1. 视图

• 视图是虚拟的表，视图只是包含使用时动态检索数据的查询。MySQL 5及之后版本才添加对视图的支持

• 理解：视图可以理解为将一些复杂的sql查询封装成一个表，避免其他查询引用这个部分的内容时写大量的sql，而只需关联这个视图即可

• 创建和使用视图

• 使用create view as 语句来创建视图

• 使用show create view viewname来查看创建视图的语句

• 使用drop view viewname来删除视图

- 更新视图时，可以先用drop再用create，也可以直接用create or replace view

1. 存储过程

• 存储过程也是MySQL 5及以上版本才可以使用

• 存储过程有简单、安全、高性能的特点

• 存储过程的基本语法：

 
   
   
 

  
    
    
  
create procedure procedure_name()
  
    
    
  
BEGIN
  
    
    
  
 select * from t;
  
    
    
  
END;
 
   
   
 

• 使用存储过程：

 
   
   
 

  
    
    
  
call procedure_name;
 
   
   
 

• 删除存储过程：

 
   
   
 

  
    
    
  
drop procedure procedure_name if exists;
 
   
   
 

• 可以使用delimiter来定义sql的语句分隔符。主要用于在命令行客户端中执行多条语句的时候使用

 
   
   
 

  
    
    
  
# 将结束符定义为：$$
  
    
    
  
delimiter $$
  
    
    
  
# 记得在语句结束的时候改回来
  
    
    
  
delimiter ;
 
   
   
 

• 
  

  使用参数的存储过程

  
  

• 一般存储过程并不现实结果，二是把结果返回给你指定的变量

• 变量（variable） 内存中一个特定的位置，用来临时存储数据

• 参数支持IN（传递给存储过程）,OUT（从村村过程传出）,INOUT（对存储过程传入和传出）

• 不能通过一个参数返回多个行和列

1. # 创建带参数的存储过程

2. create procedure procedure_name

3. (

4. out p1 decimal(8,2),

5. out p2 decimal(8,2)

6. )

7. begin

8. select min(price)

9. into p1

10. from products;

11. select max(price)

12. into p2

13. from products;

14. end;

15. 
    

16. # 调用存储过程

17. call procedure_name

18. (

19. @price1,

20. @price2

21. )

22. 
    

23. # 显示结果

24. select @price1,@price2

1. 使用游标

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

• 游标的基本操作

 
   
   
 

  
    
    
  
# 使用declare命名游标
  
    
    
  
create procedure aaa()
  
    
    
  
begin
  
    
    
  
 declare ordernumbers cursor
  
    
    
  
 for
  
    
    
  
 select order_num from orders;
  
    
    
  
end;
  
    
    
  


  
    
    
  
# 使用open打开游标
  
    
    
  
open ordernumbers;
  
    
    
  


  
    
    
  
# 使用close释放游标
  
    
    
  
close ordernumbers;
 
   
   
 

• 游标和存储过程感觉还需要继续加强

1. 触发器

• 触发器是MySQL响应delete、insert、update语句而自动执行的一条mysql语句（或位于begin和end语句之间的一组语句）

• 使用create trigger来创建触发器

 
   
   
 

  
    
    
  
create trigger newproduct after insert on products
  
    
    
  
for each row select 'product added';
 
   
   
 

• 使用drop trigger删除触发器

1. 事务处理

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

• 事务相关的几个术语

• 事务（transaction） 指一组SQL语句

• 回退（rollback） 指撤销指定SQL语句的过程

• 提交（commit） 指将未存储的SQL语句结果写入数据库表

• 保留点（savepoint） 指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）

• 使用start transaction来创建事务

• 使用rollback来回退/撤销MySQL语句

• rollback只能在一个事务处理内使用，即需要先使用start transaction，才能使用rollback

• rollback可以对insert、update和delete语句起作用，不能对create和drop操作起作用

• 使用commit来提交处理，一般的MySQL语句是直接执行的（隐含提交），但在事务处理块中，需要使用commit进行明确提交

• 使用rollback或commit后，事务会自动关闭

• 保留点：使用savepoint穿件一个占位符

 
   
   
 

  
    
    
  
# 创建保留点
  
    
    
  
savepoint delete1;
  
    
    
  


  
    
    
  
# 回退到保留点
  
    
    
  
rollback to delete1;
 
   
   
 

二、总结

• 学习此书最想了解的是存储过程及游标的相关知识，书中略有介绍，对游标的介绍略少。书中各方面的内容虽覆盖全面，但浅尝辄止，要全面掌握仍需继续找其他材料进行学习。另外，书中字符集、帐户安全等相关内容的设定，没去深究，暂时不需要太深入的了解。