原书：MySQL必知必会，(英)Ben Forta 2009年1月
最近感觉SQL技能严重不足，希望较为系统的学习和了解在存储过程等方面的内容。
一、笔记列表
1. 数据库的基本概念
数据库(database)：保存有组织的数据的容器(通常是一个文 件或一组文件)。
表(table)：某种特定类型数据的结构化清单。
模式(schema)：关于数据库和表的布局及特性的信息。表具有一些特性，这些特性定义了数据在表中如何存储，如可以存 储什么样的数据，数据如何分解，各部分信息如何命名，等等。描述表 的这组信息就是所谓的模式，模式可以用来描述数据库中特定的表以及 整个数据库(和其中表的关系)。
列(column)：表中的一个字段。所有表都是由一个或多个列组 成的
数据类型(datatype)：所容许的数据的类型。每个表列都有相 应的数据类型，它限制(或容许)该列中存储的数据。
行(row)：表中的一个记录。如果将表想象为网格，网格中垂直的列为表列，水平行为表行。
主键(primary key)：一列(或一组列)，其值能够唯一区分表中每个行。全国科学技术名词审定委员会审定的key在数据库中的对应名词为“键码”或“码。
主键通常定义在表的一列上，但这并不是必需的，也可以一起使用多个列作为主键。在使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须是唯一的(但单个列的值可以不唯一)。
外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
SQL：SQL(发音为字母S-Q-L或sequel)是结构化查询语言(Structured Query Language)的缩写。SQL是一种专门用来与数据库通信的语言。
SQL(MySQL)语句的关键字顺序select
from
join/left join/right join
on
where
group by
having
order by
limit
where的条件
基本操作符：=、!=、<>、、>=、between
空值：isnull
逻辑操作符：and、or、in、not
通配符：like
使用%：表示任何字符出现任意次数
使用_：只匹配单个字符而不是多个字符
正则表达式：regexp
进行or匹配：使用|来设置多个条件，如regexp"100|200"
匹配几个单一字符：使用[]限定范围,[123]真是表示匹配除123外的数据
匹配正则表达式特殊字符：使用\\转义符，\\f换页,\\n换行,\\r回车,\\t制表,\\v纵向制表
MySQL正则匹配预定义字符
正则表达式重复元字符
定位元字符
创建计算字段
拼接字段：Concat()函数。
去除左右空格：RTrim()、LTrim()
算术计算：+-*/，两个字段间
数据处理函数
Abs():返回一个数的绝对值
Cos():返回一个角度的余弦
Exp():返回一个数的指数值
Mod():返回除操作的余数
Pi():返回圆周率
Rand():返回一个随机数
Sin():返回一个角度的正弦
Sqrt():返回一个数的平方根
Tan():返回一个角度的正切
AddDate():增加一个日期(天、周等)
AddTime():增加一个时间(时、分等)
CurDate():返回当前日期
CurTime():返回当前时间
Date():返回日期时间的日期部分
DateDiff():计算两个日期之差
Date_Add():高度灵活的日期运算函数
Date_Format():返回一个格式化的日期或时间串
Day():返回一个日期的天数部分
DayOfWeek():对于一个日期，返回对应的星期几
Hour():返回一个时间的小时部分
Minute():返回一个时间的分钟部分
Month():返回一个日期的月份部分
Now():返回当前日期和时间
Second():返回一个时间的秒部分
Time():返回一个日期时间的时间部分
Year():返回一个日期的年份部分
left(): 返回串左边的字符
length():返回串的长度
locate():找出串的一个子串
lower():将串转换为小写
ltrim():去掉串左边的空格
right():返回串右边的字符
rtrim():去掉串右边的空格
soundex():返回串的soundex值
substring():返回子串的字符
upper():将串转换为大写
文本处理函数
日和时间处理函数
数据处理函数
聚集函数计算和返回单个值的函数
AVG():返回某列的平均值
COUNT():返回某列的行数
MAX():返回某列的最大值
MIN():返回某列的最小值
SUM():返回某列值之和
分组数据
group by
group by后可以跟with rollup关键字，用于得到每个分组的汇总(每个组的总计)
having和where都是用于过滤，很多where语句可以用having代替。where过滤行(在分组前进行过滤)，having过滤分组(在分组后进行过滤)。
子查询
select*
fromt
whereidin(selectidfromt1where...)
可以使用嵌套多层子查询，但建议不要太多
通常子查询返回单个列并进行匹配，也可以使用多个列
通常子查询与in操作符结合使用，但也可以用于=、<>等操作
子查询也可以出现在计算字段中，在计算字段中关联其他表
自联结、自然联结、外部联结
选出table1含有p_name='abc'的，所有id及其p_name记录
selecta.id,a.name,a.p_name
fromtable1asa,table1asb
wherea.id=b.idandb.p_name='abc'
以下观点不一定正确
自联结：同一个表之间关联进行取数
自然联结：可以理解为两个表直接关联后，所有列直接展示
外部联结：联结包含了那些在相关表中没有关联行的行，这种类型的联结成为外部联结(left / right join)
创建组合查询
使用union操作符组合多条SQL查询
union all和union一个不去重，一个去重
全文本搜索
create table时，使用fulltext()指定启用全文本搜索的字段
不要在导入数据时使用fulltext，导入后再修改表定义fulltext，否则容易造成性能问题
全文本搜索：使用where match("查询的字段") against("关键词")进行数据筛选
全文本搜索和like得出的结果基本一致
使用查询扩展：使用where match("查询的字段") against("关键词" with query expansion)进行查询扩展筛选。【查询扩展貌似是：对查询命中的条目中的文本再去匹配其他行，获取这些文本匹配的结果】
布尔文本搜索： against("关键词 -排除的关键词*" in boolean mode)
插入数据
使用insert语句插入数据
插入完整的行
插入行的一部分
插入多行
插入某些查询的结果
# 可以插入多条记录，括号和逗号隔开
insertintotablename
(columns1,
columns2,
columns3
)
values
('value1',
'value2',
'value3'
),
('value1',
'value2',
'value3'
);
可以省略列名，但是最好不要，容易出错。
可以使用insert low_priority into来降低insert语句的优先级
从一个表中查询某些数据插入到另一个表，不用关心select对应的列名，只需要对应好顺序
insertintotablename
(columns1,
columns2,
columns3
)
select
columns1,
columns2,
columns3
fromtablename2;
更新和删除数据
使用update语句更新表中的数据
更新表中特定行
更新表中所有行
update customers
setcst_name='aaa'
cst_email='qq@saa.com'
wherecst_id='121';
使用delete语句删除数据
从表中删除特定的行
从表中删除所有行
deletefromcustomers
wherecst_id='112'
创建和操纵表
使用create table创建表，必须给出以下信息
新表的名字，在create table之后
表列的名字和定义，用逗号分隔
create table suctomersifnotexists
(
cst_idintnotnullauto_increment,
cst_namechar(50),
cst_emailchar(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;
使用alter table更新表
添加列
alter table table1
add phonechar(20);
删除列
alter table table1
drop column phone;
视图
视图是虚拟的表，视图只是包含使用时动态检索数据的查询。MySQL 5及之后版本才添加对视图的支持
理解：视图可以理解为将一些复杂的sql查询封装成一个表，避免其他查询引用这个部分的内容时写大量的sql，而只需关联这个视图即可
创建和使用视图
使用create view as 语句来创建视图
使用show create view viewname来查看创建视图的语句
使用drop view viewname来删除视图
- 更新视图时，可以先用drop再用create，也可以直接用create or replace view
存储过程
存储过程也是MySQL 5及以上版本才可以使用
存储过程有简单、安全、高性能的特点
存储过程的基本语法：
create procedure procedure_name()
BEGIN
select*fromt;
END;
使用存储过程：
call procedure_name;
删除存储过程：
drop procedure procedure_nameifexists;
可以使用delimiter来定义sql的语句分隔符。主要用于在命令行客户端中执行多条语句的时候使用
# 将结束符定义为：$$
delimiter $$
# 记得在语句结束的时候改回来
delimiter;
使用参数的存储过程
一般存储过程并不现实结果，二是把结果返回给你指定的变量
变量(variable) 内存中一个特定的位置，用来临时存储数据
参数支持IN(传递给存储过程),OUT(从村村过程传出),INOUT(对存储过程传入和传出)
不能通过一个参数返回多个行和列# 创建带参数的存储过程
create procedure procedure_name
(
outp1decimal(8,2),
outp2decimal(8,2)
)
begin
selectmin(price)
intop1
fromproducts;
selectmax(price)
intop2
fromproducts;
end;
# 调用存储过程
call procedure_name
(
@price1,
@price2
)
# 显示结果
select@price1,@price2
使用游标
MySQL游标只能用于存储过程和函数
游标的基本操作
# 使用declare命名游标
create procedure aaa()
begin
declare ordernumbers cursor
for
selectorder_numfromorders;
end;
# 使用open打开游标
open ordernumbers;
# 使用close释放游标
close ordernumbers;
游标和存储过程感觉还需要继续加强
触发器
触发器是MySQL响应delete、insert、update语句而自动执行的一条mysql语句(或位于begin和end语句之间的一组语句)
使用create trigger来创建触发器
create trigger newproduct after insert on products
foreach rowselect'product added';
使用drop trigger删除触发器
事务处理
事务处理可以用来维护数据库的完整性，它保证呈批的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;
二、总结
学习此书最想了解的是存储过程及游标的相关知识，书中略有介绍，对游标的介绍略少。书中各方面的内容虽覆盖全面，但浅尝辄止，要全面掌握仍需继续找其他材料进行学习。另外，书中字符集、帐户安全等相关内容的设定，没去深究，暂时不需要太深入的了解。
718
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
