MySQL必知必会-学习笔记

第1章 了解SQL

1. 数据库是保存有组织的数据的 容器

2. DBMS是数据库软件，我们使用DBMS来访问和操作数据库，常见的DBMS有MySQL、Oracle
   DBMS常见分类为客户端-服务器，即mysql在客户端本地，操作的数据库在远端服务器上

3. 主键规则：

   • 唯一性，任意两行不能具有相同的主键值
   • 每个行都必须具有一个主键值

第2章 MySQL简介

第3章 使用MySQL

1. 连接数据库，本机上以管理员身份打开CMD命令行，输入命令mysql -uroot -p，再输入密码既可以连接数据库

2. 连接远端MySQL，需要远端服务器IP地址、用户名和密码，MySQL端口号，以及MySQL用户名密码

3. 数据库 databases
   数据表 tables
   列 rows
   行 columns
   自增变量 auto_increment

4. 快速查看一张数据表的字段 desc user;

5. 查看服务器错误和警告 show errors; show warnings;

第4章 检索数据

1. 一般不要使用全局检索，性能很慢 select * from user;
   指定要检索的列明，性能更优 select user_name from user;

2. SQL语句对大小写不敏感，可以使用binary设置大小写区分

3. 去重distinct
   对指定列去重，就放在列前面 select distinct user_name from user;

4. 限制结果集行数limit
   输出前5行
   select user_name from user limit 5;
   从第3行开始，输出5行数据（不包含第3行）
   select user_name from user limit 3, 5;

5. 限制结果集行数的另一种写法limit N2 offset N1
   从第3行开始，输出5行数据（不包含第3行）
   select user_name from user limit 5 offset 3;

第5章 排序检索数据

1. order by对指定列进行排序，默认是升序asc

2. 升序排序 select user_name from user order by user_name desc;
   降序排序 select user_name from user order by user_name asc;

3. 结合升降序可以输出最大最小值
   最小值=升序第一行 select distinct user_id from user order by user_id limit 1;
   最小值=降序第一行 select distinct user_id from user order by user_id limit 1;

第6章 过滤数据

1. 普通过滤条件 where
   where子句的位置，同时使用where和order by时，应让order by位于where之后

2. 常见的条件操作符
   = 等于
   <> 不等
   != 不等
   < 小于

   大于
   between A1 and A2 指定两个值之间
   is null 空值检查

第7章 数据过滤

1. 可以使用and和or连接两个或多个where子句，但是这样的性能很低

2. in 操作符
   select user_id from user where user_id in (2001, 2002);
   not 操作符
   select user_id from user where user_id not in (2001, 2002);

第8章 用通配符进行过滤

1. like 模糊查询，谓词
   % 任意多个字符
   _ 任意单个字符

第9章 用正则表达式进行搜索

1. regexp 正则表达式
   . 任意单个字符
   | 或
   [] 匹配多个字符，常见全数字[0-9]，全字母[a-zA-Z]
   \ 转义，注意MySQL中转移使用两个反斜杠
   {n} 指定数目的匹配
   {n,} 不少于指定数目的匹配
   {n,m} 匹配数目的范围
   ^ 文本开头
   $ 文本结尾

2. regexp = %like%， 也就是说like更加精确，regexp很模糊，需要加上很多条件才能查到指定
   比如表列中存在一条s1000，使用like '1000’就查不到，而使用regexp '1000’会查到

3. 字符类
   [:alnum:] 任意字母和数字，等于[a-zA-Z0-9]
   [:alpha:] 任意字符，等于[a-zA-Z]
   [:blank:] 空格和制表
   [:digit:] 任意数字，等于[0-9]

第10章 创建计算字段

1. 拼接字符串函数 concat(str1, str2)

2. 去掉字符串两边空格 trim(str)
   去掉字符串左边空格 Ltrim(str)
   去掉字符串右边空格 Rtrim(str)

3. 算数操作符

   • 假发
   • 减法
   • 乘法
     / 除法
     mod() 取余，比如求5÷2的余数，mod(5,2)

4. 返回当前时间函数now()

第11章 使用数据处理函数

1. 字符串处理函数
   去掉字符串空格函数 trim() Ltrim() Rtrim()
   转换大小写函数 upper() lower()
   拼接字符串函数 concat(str1, str2)
   返回字符串长度函数 length()
   找到字符串的一个子串函数 locate()

2. 日期时间处理函数
   增加一个日期（天、周） addDate()
   增加一个时间（时、分） addTime()
   返回当前日期 curDate()
   返回当前时间 curTime()
   返回当前日期时间 now()
   计算两个日期之差 datediff()

3. 数值处理函数
   返回绝对值 abs()
   返回除操作的余数 mod()
   返回一个随机数 rand()

第12章 汇总数据

1. 聚集函数
   返回某列的平均值 avg()
   返回某列的行数 count()
   返回某列的最大值 max()
   返回某列的最小值 min()
   返回某列的和 sum()

2. 注意聚集函数都会忽略列值为null的行
   即cust_name共有5行，但是其中2行为null，使用count(cust_name)=3

3. 使用count(*)不会忽略null值

4. 可以在聚集函数里使用去重distinct，根据需求
   只统计商品价格的种类，count(distinct score)
   比如分数100 200 300 200 ，count(score)=4， count(distinct score)=3

5. MySQL排名
   排名分三种情况

   • 一种是不管重复分数直接按行号算排名，排名序号为1234567
     select p_age, row_number() over(order by p_age asc) as rank from person;

   • 一种是相同分数占同一排名，但是后一个排名是连续的，排名12223
     select p_age, dense_rank() over(order by p_age asc) as rank from person;

   • 一种是相同分数占同意排名，但是后一个排名不连续，排名12225
     select p_age, rank() over(order by p_age asc) as rank from person;

第13章 分组数据

1. 涉及到分组的字眼一般就是“每个”
   比如每个供应商，group by vend_id
   每个人，group by person_id

2. 如果分组列中包含null值，则将其作为一个单独的分组

3. with rollup 用在group up后面用于统计总数
   select coalesce(p_age, ‘总人数’), p_age, count(*) from person group by p_age with rollup;

4. having用于过滤分组，而where是过滤行
   所有的where都可以使用having来替换

   当having和where同时使用，先执行where对行进行过滤，再执行having对过滤后的分组来过滤

5. 分组和排序
   使用group by分组行，但是输出不是分组的顺序
   使用order by对分组行进行排序即可

6. select子句顺序
   ① select 要返回的列
   ② from 查库
   ③ where 过滤行
   ④ group by 分组
   ⑤ having 过滤分组
   ⑥ order by 排序行
   ⑦ limit 限制行数

第14章 使用子查询

1. 子查询最常用的是in嵌套
   select person_name from person where person_id in
   (select person_id from person where person_id > 2);

第15、16章 联结表

1. 外键，外键为某个表中的一列，它包含另一个表的主键（可伸缩性好）

2. 联结分类

   • 自连接
     用于同一张表中需要查询多次的情况（内部联结要用别名）
     也可以使用where子查询，但是子查询的性能比联结低很多
     SELECT * FROM student s1 ,student s2 where s1.collegeId=s2.collegeId and s1.name = ‘TNT’;

   • 内连接
     表间的主键与外键相连，只取得键值一致的
     SELECT 列名1,列名2… FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

   • 外连接
     左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致
     SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
     左联结时，虽然对应的右表数据为空，但也会显示出来

3. 使用where子查询，但是子查询的性能比联结低很多
   联结的表越多，性能下降的越厉害

第17章 组合查询

1. 复合查询（相当于合集的意思），即在各条语句之间放上关键字union，结果会直接合并

2. 复合查询使用union会自动去除重复的行
   使用 union all就不会去除重复的行，而是返回所有匹配行

3. 使用union时，只能有一条order by排序语句，一般放在最后一条Select子句中

第18章 全文本搜索

1. 全文本搜索fulltext使用索引进行检索，使用match()和against()实现，一般用于搜索引擎应用
   select person_name from person where match(person_name) against(‘a’);

2. 并非所有引擎支持全文本搜索，myISAM支持全文本搜索，而InnoDB不支持

3. 之前使用的like、regexp正则检索数据使用的是全表查询，性能很低
   要想性能高，必须使用索引查询

第19章 插入数据

1. 格式，注意insert语句中values值的顺序必须与前面字段顺序保持一致
   insert into person (person_id, person_name) values(1, ‘xu’)

2. insert语句没有输出

3. 在索引比较多的情况下，insert操作比较耗时

4. 使用批量insert操作，提高性能

5. insert select 将检索出的数据插入到表中，常用于导出数据
   select into person_new (person_id, person_name) select person_id, person_name from person_old;

第20章 更新和删除数据

1. update person set person_name = ‘new’ where person_id = 2;

2. delete from person where person_id = 2;

3. 删除表中指定行一般不要这样做，而是标记数据
   删除和更新操作没有回滚机制，因此要小心
   删除和更新之前要先使用select确认数据

4. 如果想要删除所有行，不要使用delete，而是truncate table
   truncate table的做法是删除表再重新新建一个表，但是速度更快

第21章 创建和操纵表

1. 创建表
   create table person(
   person int not null Auto_increment,
   person_name varchar(30) not null
   )engine=innodb

第22章 视图view

1. 视图是一张虚拟的表，包含动态检索数据的查询
   create view personview as
   select person_name from person where person_id = 2;

// 查看视图
select * from personview;

2. 一般视图仅用于select检索语句

第23章 存储过程procedure

1. 存储过程相当于mysql中的函数
   指定分隔符为//
   delimiter //

创建存储过程
create procedure personpre(
out pl decimal(8,2),
out ph decimal(8,2)
)
begin
select avg(person_price) as priceavg
from person;
end;

// 调用存储过程
call personpre(@pl, @ph);

显示存储过程的结果
select @pl;
select @ph;

2. decimal(4,2)指的是列可以存储2位小数的8位数，因此，amount列的范围是从-99.99到99.99

3. 显示存储过程
   show create procedure;
   show procedure status like ‘personpre’;

第24章 游标cursor

1. 游标用于处理select返回的结果集，在使用游标后，应用程序可以根据需要滚动或浏览其中的数据

2. 游标只能用于存储过程中

create procedure personpre(
out pl decimal(8,2),
out ph decimal(8,2)
)
begin
– 声明游标
declare person_cursor cursor for
select avg(person_price) as priceavg
from person;

– 打开游标
open person_cursor;

– 将其中一行赋值给变量O
fetch person_cursor into O;

– 关闭游标
close person_cursor;
end;

第25章 使用触发器trigger

1. 触发器，即在某个表发生更改时自动处理，适用于delete、insert、update

2. 创建触发器

create trigger person_trigger after insert on person for each row select ‘person added’;

在person表中执行插入操作后显示文本’person added’

3. 触发器的几个参数
   • 触发器的名称
   • 触发器关联的表
   • 触发时机（delete、insert、update）
   • 触发何时执行（before、after）
   • 触发影响范围（each row）
   • 触发动作

第26章 事务处理

1. 并非所有引擎都支持事务处理

   • myisam不支持事务处理
   • innoDB支持事务处理（所以常用innoDB）

2. 所谓事务处理，就是保证成批的mysql语句要么完全执行，要么完全不执行

3. 相关名词

   • 事务 指一组SQL语句
   • 回退 撤销SQL的过程
   • 提交 将未存储的SQL语句结果写入数据库表
   • 保留点 指事务处理中设置的临时占位符（placeholder），你可以对它发布回退

4. 默认的mysql行为是自动提交所有更改，也就是说任何时候你执行一条MySQL语句，该语句都会立即生效，为了指示MySQL不自动提交更改，需要设置
   set autocommit = 0;

第27章 全球化和本地化

第28章 安全管理

第29章 数据库维护

1. 备份数据mysqldump
   mysqldump -uroot -p test > /backup/mysqldump/test.db

   备份前最好使用flush tables 保证所有数据都被写到磁盘中

2. 数据库维护
   analyze table person; 用于检查表键是否正确

   CHECK TABLE 针对许多问题对表进行检查

3. 如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能

4. 数据库日志文件

   • 错误日志 它包含启动和关闭问题以及任意关键错误的细节
     日志通常名为hostname.err，位于data目录中

   • 查询日志 它记录所有MySQL活动，在诊断问题时非常有用。
     此日志文件可能会很快地变得非常大，因此需要对他定期删除
     日志通常名为hostname.log，位于data目录中

   • 二进制日志 它记录更新过数据（或者可能更新过数据）的所有
     语句，此日志通常名为hostname-bin，位于data目录内

   • 缓慢查询日志 即慢查询，此日志记录执行缓慢的任何查询
     这个日志在确定数据库何处需要优化很有用。
     此日志通常名为hostname-slow.log ， 位于data目录中

5. 使用日志时，可用flush logs 来刷新和重新开始所有日志文件

第30章 改善性能

1. 在诊断应用的滞缓现象和性能现象时，数据库查询性能是最常见的原因

2. mysql默认的设置该开始使用没有问题，但过一段时间后你可能需要调整内存分配、缓冲区大小等
   （为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;）

3. 当遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程
   （以及它们的线程ID和执行时间，kill终结掉某些线程）。

4. 总是有不止一种方法编写同一条SELECT语句。
   应该试验联结、并、子查询等，找出最佳的方法。
   理论上性能从高到低排序：联结 > 并 > 子查询

5. 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句

6. 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL
   语句快（但是存储过程需要DBA来创建，一般人没权限）

7. 总是要使用正确的数据类型
   （比如数据库是字符串类型，而你在SQL中使用数字类型，经过SQL弱类型转换是可以成功执行，但是没有用到索引检索，性能较差）

8. 决不要检索比需求还要多的数据。换言之，不要用SELECT *

9. 在导入数据时，应该关闭自动提交。
   你可能还想删除索引，然后在导入完成后再重建它们

10. 检索必须保证使用了索引而不是全表查询，使用explain语句查看是否使用索引

11. 定义索引列，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句
    （如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象）

12. 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。
    如果你有一些表，它们收集数据且不经常被搜索，则不要索引它们

13. like和regexp都是全表查询，性能很差，要用索引检索