MySQL必知必会学习笔记（详细）

# 检索单个列
SELECT prod_name FROM products;
# 检索多个列
SELECT prod_id, prod_name, prod_price FROM products;
# 显示所有列 使用通配符 *
SELECT * FROM product;
# 只返回不同（唯一）的行
SELECT DISTINCT vend_id FROM products;
# 返回不多于5行
SELECT prod_name FROM products LIMIT 5;
# 返回不多于5行，偏移1个数据，表示之前有多少个被忽略（第一行为行0）
SELECT prod_name FROM products LIMIT 1, 5; # 等价于LIMIT 5 OFFSET 1;

05 排序 order by

# order by 放在from子句后面，limit放在最后
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

06 过滤数据 where，between and，is null

where，between a and b，空值检查 IS NULL
过滤时选择不具有特定的值时，你不会返回具有NULL值的行，因为NULL是未知具有特殊的含义，数据库不知道是否匹配，在匹配过滤或不匹配过滤时不返回它们。

07 数据过滤 where，and，or，not，in

组合where子句 and，or（注意使用括号明确的分组操作符），not，in

SELECT prod_name, prod_price
FROM products
WHERE (ven_id = 1002 OR ven_id = 1003) AND (prod_price >= 10) # 使用括号扩起来各个条件

SELECT prod_name, prod_price
FROM products
WHERE ven_id IN (1002, 1003)
ORDER BY prod_name;
使用 IN 的优点，1》在使用长的清单时，更清楚更直观；2》计算次序更容易管理；3》一般比OR执行的快；4》可以包含其他SELECT语句
NOT 否定WHERER后面的条件，也可以对IN，BETWEEN，EXISTS子句取反

08 用通配符进行过滤 LIKE %为匹配任意字符，_为匹配一个字符

尽量不要使用通配符，搜索时间长；

SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE '%jet%'

09 用正则表达式搜索 REGEXP

REGEXP，.匹配任意一个字符；正则表达式中的'|'为OR的意思；[abc]匹配几个字符之一；[^abc]否定一个字符集，即匹配除指定字符以外的任何东西；[1-9]匹配范围；匹配特殊字符使用\\（MySQL自己解释一个，正则表达式库解释一个），如【._[]\】以及\\f,换页 \\n,换行 \\r,回车 \\t,制表 \\v纵向制表
LIKE匹配整个列，REGEXP 匹配正则表达式不是匹配整个列，例如 LIKE ‘1000’ 和REXEXP ‘1000’ 不一样，前者不匹配，后者匹配‘01000’，‘ 1000’等
不区分大小写，若区分则在REGEXP后面加BINARY
为了更方便的工作，可以使用预定义的字符集。

使用重复元字符控制匹配字符的数量。定位元字符

可以使用SELECT在不使用数据库的情况下测试正则表达式：SELECT ‘hello’ REGEXP ‘[0-9]’

10 创建计算字段 concat()

concat()拼接串，把多个串拼接在一起；RTrim()去除右侧多余的空格；LTRIM()去掉左边的空格；TRIM()去掉两边的空格
别名使用AS
可以使用SELECT作为计算测试一些东西，比如SELECT NOW();

11 使用数据处理函数文本，日期，数值函数

常用的文本处理函数

常用的日期处理函数和数值处理函数

12 聚合函数 avg，count，max，min，sum

count()可以使用distinct + 列名计算行值不同的数目

13 分组 group by，having

group by对数据进行分组，having对分组进行过滤
group by后面跟with rollup 可以得到每个分组以及每个分组的汇总级别

select vend_id count(1) as num_prod
from products
group by vend_id with rollup

使用顺序：select，from，where，group by，having，order by，limit

14 子查询（嵌套查询）

常见于where子句的in操作符中，in后面跟一个select语句
也可以添加计算字段使用where连接多表

SELECT c1
FROM tb
WHERE c1 in (SELECT c1 FROM …)

SELECT c1,
(SELECT count(*) # 添加计算字段
FROM tb2
WHERE tb2.c1=tb1.c1) AS newname
FROM tb1

15 连接表

from后面跟多个表，在where里面使用两个表的连接条件（等值连接）

16 高级连接

自连接：使用表别名使表自身和自身连接
自然连接：多个表对于相同列进行连接
内连接、外连接：join，left join，right join，full join

17 组合查询 UNION

UNION可以组合多个SELECT语句，要求字段兼容，几乎完成了WHERE条件相同的工作。会自动去除重复的数据，如果要返回所有匹配行使用UNION ALL。在最后使用ORDER BY排序，对所有结果排序，前面所有实际上是一条语句

18 全文本搜索 MATCH AGAINST

MyISAM支持全文本搜索，InonDB不支持
在创建表时使用FULLTEXT创建索引

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit')

SELECT note_text, MATCH(note_text) AGAINST('rabbit') AS rank # 查询所有的
FROM productnotes

SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit' WITH QUERY EXPANSION) # 使用查询扩展

SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit' IN BOOLEAN MODE) # 使用布尔查询

要使用其他布尔查询的操作符

19 插入数据 INSERT INTO

要写插入表的列名，SELECT语句后面的列不要带括号

INSERT INTO tb(c1, c2,…) # 根据值插入
VALUES
(C1,C2,…)

INSERT INTO tb(c1, c2,…) # 从SELECT语句插入
SELECT C1,C2,…

20 更新和删除 UPDATE，DELETE，TRUNCATE

没有where条件更新删除所有行，先试用select查询要删除的数据是否是想要删除的数据

UPDATE tb
SET c1 = newValue,
    c2 = newValue
WHERE ....  # 可以使用子查询，WHERE id IN (SELECT id FROM...)

DELETE FROM tb
WHERE ...

TRUNCATE TABLE tb  # 删除所有数据（实际上是删除表然后新建一个表，速度比delete快）

21 创建和操纵表CREATE

创建表

CREATE TABLE IF NOT EXISTS tb
( id INT NOT NULL AUTO_INCREMENT, # 自增
name NOT NULL DEFAULT 'AA', # 默认值
address NULL,
PRIMARY KEY (id) # 设置主键约束
)ENGINE=InnoDB

last_insert_id()函数获取最后一个自增AUTO_INCREMENT值

更改表

ALTER TABLE tb # 添加列
ADD c1 char(20),
c2 char(20)

ALTER TABLE tb # 删除列
DROP c1,c2
删除表：DROP TABLE tb
重命名：RENAME tb1 TO tb2，tb3 TO tb4

22 使用视图

视图提供了一种MySQL的select语句层次的封装，可用来简化数据处理，重新格式化基础数据或保护基础数据

## 创建视图
create view as select ...

## 查看创建视图的语句
show create view view_name

## 删除视图
dorp view view_name

## 更新视图
先drop在create
或者
create or replace view

23 使用存储过程

## 调用存储过程
call xxx(@xxx, @xxx);

## 创建存储过程
create procedure xxx()
begin
    select...
end;

## 如果使用命令行，需要修改delimiter，因为命令行解析;为结束
开头使用delimiter //重新定义结束符，最后使用end//

## 删除存储过程
dorp procedure xxx;
dorp procedure xxx if exists;

## 使用参数, in out关键字表示输入输出变量，into表示将结果写入变量
#创建
create procedure xxx(out 变量 变量类型, in 变量 变量类型)
begin
    使用关键字into写入变量
end;

#调用
call xxx(@p1,@p2...)

## 更复杂的存储过程, 使用declare声明变量类型
declare 变量 变量类型;
if 布尔变量 then
    ...
elseif 布尔变量 then
    ...
else
    ...
end if;

## 显示所有存储过程
show procedure status;
show procedure status like 'xxx'; 限制输出，使用过滤

## 显示创建存储过程的语句
show create procedure xxx;

24 使用游标

## 声明游标
declare xxx cursor for ...

## 打开/关闭游标
open xxx;
close xxx;

## 打开游标后检索当前行
fetch xxx into xxx;

## 打开游标后遍历所有行， 注意：declare必须在任意游标之前定义
declare done boolean default 0;
declare continue handler for sqlstate '02000' set done=1; #定义一个continue handler,在条件出现时被执行，'02000'是未找到条件
repeat
    fetch xxx into xxx;
until done end repeat;

25 使用触发器

自动执行的一条语句，delete，insert，update
创建触发器：唯一的触发器名（表中），关联的表，响应的活动，何时执行

创建触发器, 每个表最多支持6个触发器每条insert update delete的之前和之后

create trigger xxx after insert on xxx
for each row select …;

create trigger xxx after insert on xxx
for each row
begin
…
end；

删除触发器, 不能更改或覆盖，必须删除再创建

drop triggle xxx;

insert触发器

可以引用一个NEW的虚拟表访问被插入的行，NEW中的值也可以被更新

delete触发器

可以引用一个OLD的虚拟表访问被删除的行，OLD中的值是只读的

update触发器

可以引用NEW访问更新的值，值也可以更改，OLD访问原来的值，只读

26 管理事务处理

事务管理用来管理insert，update，delete语句，不能回退create，drop

开始事务，回滚事务，提交事务

start transaction
…
rollback; # 只能在一个事务中使用，
commit;

使用保留点, 越多越好，执行rollback或commit后自动释放，release savepoint明确释放

savepoint xxx;
rollback to xxx;
更改默认提交，set autocommit=0

27 全球化和本地化

查看支持的字符集列表：show character set;
查看支持校对的字符集列表：show collation;
查看系统或数据库的字符集和校对：show variables like 'character%'; show variables like 'collation%';
为某个表或某个列指定字符集和校对，不指定使用数据库默认

create table xxx
(
column1 int,
column2 varchar(10) character set xxx collation xxx;
)
default character set xxx
collation xxx;

排序特定的select语句, 或者group by， having，聚合函数，别名等

select xxx order by xxx collate xxx;

28 安全管理

## 创建用户账号, 不指定主机名默认%
create user xxx identified by 'password';
create user xxx identified by password  '散列值';
或者使用insert, grant 一般不建议使用insert

## 重命名用户账号
rename user xxx to xxx;

## 删除用户
drop user xxx;

## 创建用户账号后必须分配访问权限，否则只能登录，看不到数据，也无法执行操作
## 查看赋予用户的权限
show grant for xxx;

## 设置权限
grant xxx on dbname.* to xxx; # 针对某个数据库
revoke xxx on dbname.* from xxx; # 撤销权限

## 更改用户密码
set password for xxx = password('new password');
set password = password('new password'); # 更新当前用户的密码

29 数据库维护

备份数据
数据库维护

检查表键是否正确

analyze table xxx;
check table xxx;
诊断启动问题，mysqld --help, --safe-mode, --verbose, --version
查看日志，data目录
- 错误日志，hostname.err
- 查询日志，hostname.log
- 二进制日志，记录更新过数据的所有语句，hostname-bin
- 缓慢查询日志，记录执行缓慢的任何查询，对数据库需要在何处优化很有用，hostname-slow.log
- flush logs，刷新和重新开始所有日志文件