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，刷新和重新开始所有日志文件

30 改善性能

查看配置：show variables，show status
查看活动进程：show processlist，使用kill终结某个特定进程
使用explain解析查询语句
有的操作支持delayed关键字

posted on 2020-12-17 21:31 Bingmous 阅读(114) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？

	# 检索单个列
	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;

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

	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;

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

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

	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

	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) # 使用布尔查询
	# 要使用其他布尔查询的操作符

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

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

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

	DELETE FROM tb
	WHERE ...

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

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

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

	ALTER TABLE tb # 删除列
	DROP c1,c2

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

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

	## 删除视图
	dorp view view_name

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

	## 调用存储过程
	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;

	## 声明游标
	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;

	## 创建触发器, 每个表最多支持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访问原来的值，只读

	## 开始事务，回滚事务，提交事务
	start transaction
	...
	rollback; # 只能在一个事务中使用，
	commit;

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

	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;

	## 创建用户账号, 不指定主机名默认%
	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'); # 更新当前用户的密码

	## 检查表键是否正确
	analyze table xxx;
	check table xxx;

bingmous

导航

统计

公告

搜索

常用链接

最新随笔

我的标签

积分与排名

随笔分类 (106)

阅读排行榜