MySQL - 汇总篇

1. 数据库相关概念

2. MySQL概念及安装

3. 基本关键字

3.1 select

1. 查询全部数据：select * from table;
2. 查询单/多列：select column/column1,column2 from table;

3.2 distinct

1. 查询去重：select distinct column from table;

3.3 limit

1. 限制为1条记录：select * from table limit 1;
2. 偏移限制-写法1：select * from table limit 2 , 3;
3. 偏移限制-写法1: select * from table limit 3 offset 2;

3.4 order by

1. 正序排序1：select * from table order by column;
2. 正序排序2: select * from table order by column asc;
3. 倒序排序：select * from table order by column desc;
4. 多列排序：select * from table order by column1 asc,column2 desc;

3.5 where

1. 查询列值为1的记录：select * from table where column = 1;
2. 查询列值为Null的记录：select * from table where column is null;

3.6 not

3.7 between

1. 查询列值在1到10之间的记录：select * from table where column between 1 and 10;
2. 查询列值不在1到10之间的记录：select * from table where column not between 1 and 10;

3.8 in

1. 查询列值在某个结果集的记录：select * from table where column in (1,2);
2. 查询列值不在某个结果集的记录：select * from table where column not in (1,2);

3.9 and

1. 限制多属性查询：select * from table where column1 = 1 and column2 = '计算机';

3.10 or

1. 限制单属性多选择：select * from table where column = 1 or column = 2;
2. 等上写法：select * from table where column in (1,2);
3. 等上写法：select * from table where column between 1 and 2;

3.11 like

1. 左模糊：select * from table where column like '1%';
2. 右模糊：select * from table where column like '%1';
3. 中间模糊：select * from table where column like '1%1';
4. 左右模糊：select * from table where column like '%1%';

3.12 regexp

1. 查询包含1的结果集：select * from table where column regexp '1';
2. 查询包含1%的结果集：`select * from table where column regexp '1.'

3.13 as

1. 别名：select t.column as new_name from table as t;
2. 简写：select t.column new_name from table t;

3.14 group by

1. 按列分组：select column count(column) from table group by column;

3.15 having

1. 分组并取count大于等于2的：select column count(column) from table group by column having count(*) >= 2;

3.16 insert

1. 按照表中的列顺序插入：insert into table values(xxx,xxx,xxx...);
2. 自定义列及顺序插入：insert into table(id,name,age...) values(null,'test',18...)
3. 多行插入：insert into table values(xxx,xxx,xxx...),(xxx,xxx,xxx...)

3.17 update

1. 更新指定数据：update table set column1 = 1 where column2 = 2
2. 更新多列：update table set column1 = 1,column2 = 2 where column3 = 3

3.18 delete

1. 删除指定记录：delete from table where column1 = 1

4. 函数和运算符

4.1 运算符

4.2 函数

• 字符串函数
• 数值函数
• 日期与时间函数

4.2.1 字符串函数

4.2.2 数值函数

4.3.3 日期与时间函数

4.4.4 流程控制函数

• case、then、when、else、end

mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULLSQL

• IF(expr1,expr2,expr3)

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'sql

• IFNULL(expr1,expr2)

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'sql

• NULLIF(expr1,expr2)

mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1sql

5. 高级部分

5.1 子查询（subquery）

select column from table where column = (select column from table where column = 1)sql

select column1 (select count(*) from table2 where table1.column1 = table2.column1) from table1sql

5.2 连表查询（join）

select product.product_name, orders.amount from orders, products where orders.product_id = products.idsql

• 内连接

select product.product_name, orders.amount from orders inner join products on orders.product_id = products.idsql

• 外连接

• 自连接

select name, subject from teachers where subject = (select subject from teachers where id = 1);sql

select t1.name, t1.subject from teachers t1, teachers t2 where t1.subject = t2.subject and t2.id = 1;sql

5.3 组合查询（union）

select name, age, level from teachers where age <= 30 union select name, age, level from teachers where level >= 3sql

5.4 全文索引（fulltext index）

手册参考：
- [官方手册8.0 - 全文索引功能](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html)
- [官方手册8.0 - InnoDB全文索引](https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html)plaintext

• 全文索引的创建与删除

CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200),
       FULLTEXT idx (opening_line)
       ) ENGINE=InnoDB;sql

CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);sql

-- 此方法添加的索引名即为列名
ALTER TABLE opening_lines
ADD FULLTEXT(opening_line)sql

drop index idx on opening_linessql

alter table opening_lines drop index idxsql

• 使用全文索引

select opening_line from opening_lines where match(opening_line) against('xxx')sql

select opening_line, match(opening_line) against('xxx') as weight from opening_linessql

SELECT * FROM articles WHERE MATCH (title,body)
    AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);sql

select opening_line from opening_lines where match(opening_line) against('xxx' with query expansion)sql

• ngram 全文解析器

1. 命令行参数配置：mysqld --ngram_token_size=2
2. 配置文件：

[mysqld]
ngram_token_size=2ini

CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body) WITH PARSER ngram
    ) ENGINE=InnoDB CHARACTER SET utf8mb4;

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;sql

mysql> USE test;

mysql> CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body) WITH PARSER ngram
    ) ENGINE=InnoDB CHARACTER SET utf8mb4;

mysql> SET NAMES utf8mb4;

INSERT INTO articles (title,body) VALUES
    ('数据库管理','在本教程中我将向你展示如何管理数据库'),
    ('数据库应用开发','学习开发数据库应用程序');

mysql> SET GLOBAL innodb_ft_aux_table="test/articles";

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;sql

5.5 视图（view）

1. 重用sql
2. 简化sql
3. 保护数据，只能操作表的特定部分

# 创建视图
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products);
# 查询视图
SELECT * FROM [Products Above Average Price];
# 查看视图创建过程
SHOW CREATE VIEW [Products Above Average Price];
# 更新视图
CREATE OR REPLACE VIEW [视图名] AS
SELECT column_name(s)
FROM table_name
WHERE condition;
# 删除视图
DROP VIEW [视图名];sql

5.6 存储过程（Stored Procedure）

• MySQL 存储过程 | 菜鸟教程
• CREATE PROCEDURE and CREATE FUNCTION Statements | MySQL

# 创建存储过程
CREATE PROCEDURE two_sum(
	in num1 int, # 入参
	in num2 int,
	out sum int # 出参
)
begin
	select num1+num2 into sum;
end;
# 查看存储过程创建过程
show create procedure two_sum;
# 调用存储过程
call two_sum(1,1,@ans);
# 查询存储过程返回的变量
select @ans; # 2
# 删除存储过程
drop procedure two_sum;
# 修改存储过程只能改变存储过程的特征，不能修改过程的参数以及过程体。
ALTER PROCEDURE proc_name [characteristic ...]

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}sql

# 临时修改为//
delimiter //
# 修改为正常情况
delimiter ;sql

5.7 游标（cursor）

create procedure testcursor(out res int)
begin

	-- 定义本地变量done
	declare done boolean default 0;

	-- 创建游标
	declare mycursor cursor
	for
	select id from gowow_admin;


	-- '02000'事件(未找到条件，fetch循环到最后触发)触发设置done变量
	declare continue handler for sqlstate '02000' set done = 1;

	-- 打开游标
	open mycursor;

	-- 循环
	repeat
		-- 使用游标
		fetch mycursor into res;
	until done end repeat;

	-- 关闭游标
	close mycursor;
end;sql

5.8 触发器（trigger）

# 创建触发器 MySQL5之后不允许返回结果 所以使用变量接收
create trigger mytrigger after insert on [表名]
for each row select "my trigger is staring" into @res;
# 删除触发器
drop trigger mytrigger;

# 触发器不能被更新sql

5.9 事务（transaction）

start transaction;
# 一些操作
...
savepoint sp1;
...
rollback to sp1;
...
rollback;
commit;sql

6. 一些提示与建议

6.1 select语句的关键字顺序

7. 常见SQL语句

7.1 数据库相关

# 创建数据库
create database [数据库名] character set [字符编码];
# 查看数据库
show databases;
# 查看数据库创建详情
show create database [数据库名字];
# 修改数据库编码
alter database [数据库名] character set [字符编码];
# 切换当前数据库
use [数据库名];
# 删除数据库
drop database [数据库名];sql

7.2 表相关

# 创建表格
create table [表名]
(
	id int not null auto_increment,
    name varchar(255) not null,
    email varchar(255) null
    enable tinyint default 1
    primary key (id)
) engine = InnoDB;
# 新增列
alter table [表名] add age int(1);
# 删除列
alter table [表名] drop column age;
# 重命名表
rename table [旧表名] to [新表名];
# 删除表
drop table [表名];sql

7.3 用户权限相关

# 切换到mysql数据库
use mysql;
# 查看所有用户
select user from user;
# 新建用户
create user 'test' identified by 'password';
# 绑定主机
# create user 'test'@`localhost` identified by 'password';
# 重命名用户
rename user `test` to `test2`;
# 修改密码
alter user root identified by '123456';
# 修改当前用户密码
set password = '123456';
# 修改其他密码
set password for test2 = 'password';
#查看权限
show grants for `test2`; # 默认有个usage权限，其实就是无权限
# 赋予全数据表查询权限
grant select on learningmysql.* to test2;
# 撤销权限
revoke select on learningmysql.* from test2;
# 删除用户
drop user 'test2';
# 更多参考：https://dev.mysql.com/doc/refman/8.0/en/grant.htmlsql

7.4 字符集相关

# 查看字符集
show character set;
# 查看校对顺序
show collation;sql