SQL in MySQL

命令行登录

mysql -u usrname -p pwd -h localhost -P 3306

参数 (基本)

-u/--user
-p/--password

可选

-h/--host
-P/--port

执行sql脚本

未进入mysql命令行

mysql -u usrname -p pwd -h localhost -P 3306 < path/to/script.sql

mysql命令行中
```
source path/to/script.sql
```

SQL Syntax

create/drop创建table/db

--- create/drop db
create database if not exists db_name;
drop database if exists db_name;

--- create/drop table
create table if not exists table_name
(
  -- c_name [type] [attr1 attr2 ...]
	c_name1 int primary key auto_increment,
  c_name2 varchar(50) not null,
  c_name3 Int default 0 unique
);

drop table if exists table_name;

alter更新表

alter table table_name
	ADD c_name_new1 [type] not null after c_name1,
	modify c_name_updated [type] [attrs],
	drop c_name;

添加关系（外键）

create if not exists table table2
(
  id int primary key,
  table1_id int not null,
  foreign key fk_table1_table2 (table1_id)
  	references table1 (id)
  	on update cascade --- no action / set null / restrict
  	on delete no action
);

更新主键/外键

alter table table1
	add primary key (id),
	drop primary key,
	drop foreign key fk_table1_table2,
	add foreign key fk_table1_table2 (table1_id)
  	references table1 (id)
  	on update cascade --- no action / set null / restrict
  	on delete no action;

字符集

show charset;

Schema -> inspector

create/alter database db_name
	character set latin1

SELECT

sql关键词不区分大小写，且无视空格和换行

选择所有属性

select * 
from ...

select的属性中添加运算

select point, point *100 + 10, (point + 10) * 100
from ...

as

alias，如果有空格需要用引号

select 
	point, 
	(point + 10) * 100 as discount
	"abc" as "alias with space char"
from ...

在后续查询中无法直接使用alias

可以使用(select aliasName)来替代
select
	(point * 0.9) as exp1
	(point * 1.1 + 5) as exp2
  (select exp1 - exp2) as exp3
from ...

distinct

去掉查询结果中的重复项

select distinct
	point, 
	(point + 10) * 100 as discount
from ...

WHERE

支持的运算符

=/>/</>=/<=/<> or !=

可比较date, int, varchar

select * 
from usr
where name = 'lzq'

使用`AND/OR/NOT`组合条件

SELECT 
	*, 
	quantity * unit_price as total_price 
FROM order_items
WHERE order_id = 6 AND (quantity * unit_price) > 30
-- 无法使用alias做条件

IN

语义类似python中的in

相当于 where 中多个=的语句 + and的语法糖

典型用例为判断多个离散值，列在小括号的元组中

select *
from customers
where state NOT IN ("va", "fl", "ga")

---
select * from customers
where state <> "va" and state <> "fl" and state <> "ga"

Between

BETWEEN begin AND end
begin和end 不能颠倒

where 中>= + and + <= 的语法糖

SELECT *
FROM customers
WHERE birth_date BETWEEN "1990-01-01" AND "2000-01-01"
---
SELECT *
FROM customers
WHERE birth_date >= "1990-01-01" AND birth_date <= "2000-01-01"

like(字符串匹配)

Pattern matching

感觉不常用，可以被正则表达式完美替代

语法 attr LIKE template

%符号匹配任意长度的字符

example

b%：以b开头的
%b：以b结尾的
%bb%：含两个连续b的

_符号匹配一个字符

example

_y：第二个字母是y的
__y：第三个字母是y的
b_y：b开头，y结尾，共三个字符长

可以组合NOT

SELECT *
FROM customers
WHERE 
	(
    address NOT LIKE "%trail%" OR 
	  address LIKE "%avenue%"
  )
  AND phone LIKE "%9"

REGEXP/RLIKE(正则表达式)

语法：attr (REGEXP|RLIKE) template

直接使用(包含xx)

语义：匹配所有含字符串的项

example

where last_name regexp 'field'
--- 等价于 ---
where last_name rlike 'field'
--- 等价于 mysql方言---
where regexp_like(last_name, 'field')
--- 等价于 ---
where last_name like '%field%'

^以xx开头
- '^filed'：以filed开头
$以xx结尾
- 'field$'：以field$结尾
|组合多个pattern

类似或运算
```
where last_name regexp '^field|mac|rose'
```
- 组合^filed，mac，rose三个pattern
- 以field开头，或包含mac，或包含rose
[]相邻字符组合
- 括号中包含可能字符
- 使用[a-z]表示a到z范围
- example：
  - ‘[gim]e’：含ge或ie或me
  - ‘f[gim]e’：fge/fie/fme
  - [a-h]：表示a到h: abcdefgh

IS NULL

判读field是否为空

语法：attr IS NULL

类似python：is None

差别：无法用=判断NULL

Order by

查询结果排序

Syntax: ORDER BY field1 (DESC), field2 (DESC), alias, ...

可以通过alias排序
mysql中可以通过表中字段而不是结果中字段排序
默认升序，通过后序DESC来降序排序

Limit

限定查询结果个数

常用于分页功能实现

syntax

LIMIT num
- 返回前num个结果

LIMIT offset, num

跳过前offset个结果，返回num个结果

Example

--- 跳过前6个结果，返回7-9个结果
select *
from ...
LIMIT 6, 3

语句模版

--- 顺序很重要
SELECT ...
FROM ...
(optional) WHERE ...
(optional) ORDER BY ...
(optional) LIMIT ...

内连接 INNER JOIN

语法：(INNER) JOIN tableName ON condition

JOIN默认INNER JOIN，可以省略inner

Example

SELECT orders.customer_id
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id

重名字段必须要显式指明

在FROM和JOIN中给表alias

Syntax:

FROM tableName Alias
Join tableName Alias

语义：

和select中使用as不同，此时表的alias可以在任何地方使用，甚至是select中
使用alias后，之前的名字无法继续使用
- 相当于重命名

example

SELECT p.product_id, p.name, oi.quantity, oi.unit_price
FROM products p
JOIN order_items oi
    ON oi.product_id = p.product_id

跨数据库连接

两张表在不同数据库中时，做join

Syntax : databaseName.TableName

SELECT *
FROM database1.a a
JOIN database2.b b
    ON a.id = b.id

自连接

表和自己做连接

典型场景，table中保存id, name和上级的id

方法：给一个表两个不同的alias，然后做join

SELECT e.first_name as "employee", m.first_name as "manager"
FROM employees e
JOIN employees m 
    ON e.reports_to = m.employee_id

多表连接

连接多张表

方法：多个JOIN语句

SELECT *
FROM Table1 t1
JOIN Table2 t2
	ON t1.id = t2.id
JOIN Table3 t3
	ON t1.id= t3.id
...

复合JOIN语句

有时表会有多个主键(复合主键)

连接复合主键的表

方法：在condition中使用AND

SELECT *
FROM Table1 t1
JOIN Table2 t2
	ON t1.pk1 = t2.pk1 AND t1.pk2 = t2.pk2

MYSQL中隐式JOIN语法

mysql中的dialect

--- sql显式join
SELECT *
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id

--- mysql中隐式join
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

建议使用显式join，因为不会忘记join条件

隐式join忘记条件时会计算笛卡尔积

--- 没有join条件，返回笛卡尔积
SELECT *
FROM orders o, customers c

外连接 OUTER JOIN

内连接时，只有两个表的字段都存在时才会返回

LEFT JOIN

Syntax:

FROM table1

LEFT (OUTER) JOIN table2 ON condition

语义：table1中的所有项都会返回

OUTER关键字可省略

和condition怎么写的没关系

RIGHT JOIN

Syntax:

FROM table1

RIGTHT (OUTER) JOIN table2 ON condition

语义：table2中所有项都会返回

LEFT JOIN的语法糖

多表外链

方法：使用多个OUTER JOIN

note：为了是代码更可读/可理解，尽量统一使用LEFT JOIN

自外连接

方法：同自连接，使用两个alias

JOIN总结

方法	语义	语法
Inner join	连接两表，只返回join条件成功的项目	`JOIN`/`INNER JOIN`
Outer join	连接两表，保留其中一表中所有项目，join条件失败时设置为Null	`LEFT JOIN`: 保留FROM中的表 `RIGHT JOIN`：保留JOIN中的表

Outer join可以视为受限的inner join
LEFT可以视为保留之前的表
RIGHT可以视为保留后面的表

USING(JOIN语法糖)

如果join的两表中使用字段名称相同，可以使用USING代替ON condition

SELECT *
FROM Table1 t1
JOIN Table2 t2
	ON t1.id = t2.id
	
--- using syntaic sugar
SELECT *
FROM Table1 t1
JOIN Table2 t2
	USING id

只能在字段名称相同情况下使用

Natural JOIN(mysql语法)

数据库引擎决定共同的列来join

找不到时则做笛卡尔积

SELECT *
FROM table1
NATURAL JOIN table2

由于让数据库引擎决定怎么join，故可能产生意料之外的结果

不建议使用

CROSS JOIN(笛卡尔积)

语法：

显式

cross join关键字
```
SELECT *
FROM table1
CROSS JOIN table2
```
隐式

from中列举要cross join的表
```
SELECT *
FROM table1, table2
```

语义：两表所有排列组合

UNION(组合多段查询的结果)

两段结果的行结合

两段结果的列数需要相同
结果显示为第一段查询的名称

SELECT col1, col2
FROM table1
UNION
SELECT cola, colb
FROM table2

典型用例：根据表中值计算结果进行不同的分类处理

INSERT

Syntax:

—- Insert a single record 
INSERT INTO customers(first_name, phone, points) 
VALUES (‘Mosh’, NULL, DEFAULT)

—- Insert multiple single records 
INSERT INTO customers(first_name, phone, points) 
VALUES  
    (‘Mosh’, NULL, DEFAULT), 
    (‘Bob’, ‘1234’, 10)

mysql中嵌套INSERT

使用mysql提供的LAST_INSERT_ID()内置函数获取上次生成的id

通过查询结果创建表/添加数据

Create as

CREATE TABLE tableName AS
--- subquery
SELECT ...

创建的表有相同的数据类型，但不会有列属性(PK, NN, UQ, AI等)

Insert by subquery

INSERT INTO tableName
--- subquery
SELECT ...
FROM ...
WHERE ...

subquery

Update

UPDATE tableName
SET attr1 = v1, attr2 = v2, ...
WHERE ...

组合使用subquery

在where中嵌套子查询

UPDATE orders
SET comments = 'gold custormer'
WHERE customer_id IN
    (
        SELECT customer_id
        FROM customers
        WHERE points > 3000
    );

单列查询结果等价于元组？

DELETE

DELETE FROM tableName
WHERE ...

不加where会delete所有数据

聚合函数

MAX()
MIN()
AVG()
SUM()
COUNT()

函数参数可以是值，或者表达式
- MAX(attr * 1.1)
- COUNT(DISTINCT name)
count(columName)函数中NULL会被跳过，使用count(*)来不跳过

Group by分组

Syntax

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...

在where后，order by前

语义

结果按照 group by中属性的所有可能组合进行分组
使用group by后，select/order by中的属性必须具备以下特点之一，否则会报错
- 在group by属性中
- 聚合函数的结果且计算不依赖于group by中的属性

Group by应该使用select中除聚合函数外的所有属性

having分组后筛选

用于处理group后的数据

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING condition

Having的condition中可以使用select中的alias
- where则不行
Having中只能使用select返回的属性
- 原表中的不能使用

可以理解为结果处理

Mysql ROLLUP汇总

mysql中的dialect，用于在group by后对每组数据汇总

Syntax:

GROUP BY attr1, attr2 WITH ROLLUP

语义：对每个attr1中的attr2总结，之后对所有attr1总结

将多行查询结果转换为一行

ALL

Syntax:

WHERE attr > ALL(1, 2, 3)

语义：

返回attr大于ALL中所有值的结果
$\forall e\in \{S\}$
一般会结合子查询结果使用

ANY/SOME

syntanx:

--- any 和 some 等价
WHERE atrr > ANY(1, 2, 3)

语义

返回attr大于ALL中任意的结果

$\exists e \in \{S\}$

= any 和 in 的语义完全相同

EXIST

Syntax:

WHERE EXISTS (
	--- subquery
)

语义：

只要子查询中产生一条有效结果，就返回true
有时相比IN有更高的效率，因为无需返回子查询结果
- in则会执行并返回整个子查询结果
常和相关子查询一起使用

视图

创建/替换

create or replace view viewName as
(
	--- 子查询
)

一种保存视图的方式是：将创建视图的语句放在一个文件夹中并加入源码控制中

或使用show create view查看，但数据库会对原始语句进行修改（不推荐）

可更新视图

如过视图建立语句中没有以下函数，则view是可以更新的，即可以进行insert,update,delete操作

distinct
聚合函数
Group by/having
union

防止删除视图中数据

有时候修改数据之后，数据可能不再符合视图的条件于是就不显示了，此为视图默认行为。

语法：创建视图时添加with check option

语义：在试图修改视图中数据时，视图会检查该行为是否会导致视图中数据消失。若会，则组织该操作。

查看建表/视图语句

show create table(view) tablename-or-viewName

列

属性

PK - primary key
NN - not null
UQ - unique
AI - auto increment
default

数据类型

varchar - 变长，设定最长长度
char - 定长，不足则充填空格至长度
int - 整形

详见[MySQL 数据类型](#MySQL 数据类型)

MySQL内置函数

数值型

ROUND(real, precision)
- Real: 小数
- Precision: 保留小数位
- 四舍五入
- round(5.75, 1)返回5.8
- round(5.7)返回6
TRUNCATE(real, precision)
- 截断，不舍入
CEILING(real)
- 返回大于或等于这个数字的最小整数
- $\lceil x \rceil$
FLOOR(real)
- 返回小于于或等于这个数字的最大整数
- $\lfloor x \rfloor$
ABS(x)
- 绝对值
RAND()
- 随机 0-1间数

Mysql numeric functions

MySQL :: MySQL 8.0 Reference Manual :: 12.6 Numeric Functions and Operators

字符串

length
upper/lower
ltrim/rtrim/trim
- 去空格
left(string, #chars)/right(string, #chars)
substring(string, begin, #len)
Locate(char, string)
- 位置，不存在返回0
Replace(string, what-to-replace, substitute)
Concat(str1, …, str)

Msql string functions

获取时间

Now/curdate/curtime
Year/month/day/hour/minite/second
- 都会返回整数
dayname/monthname
- 返回字符串
extract
- SQL标准中一部分，可移植
- 用于代替year/month/…
```
SELECT EXTRACT (YEAR FROM NOW())
```

格式化日期

date_format(date, fstring)

date: 日期
fstring: 格式字符串
- %y：两位数年份
- %Y：四位数年份

msql date format string

类似的还有time_format

时间运算

date_add(now(), interval 1 year)
date_sub
date_diff
time_to_sec
- 转换为到开始时的秒数

OTHERS

替换查询结果中的空值

IFNULL

select
	IFNULL(shipper_id, 'not assigned') as shipper
from orders

coalesce

coalesce(col1, col2, col3, … default)
col1为空则col2，一直找到不为空的值为止

IF条件判断

Syntax:

IF(expression, value1, value2)

语义：

expression为真则返回value1，否则value2

Example:

select
	order_id,
	order_date,
	IF(
  	YEAR(order_date) = YEAR(NOW()),
    'active',
    'archive'
  ) as status
from orders

case分支

Syntax:

CASE
	WHEN exp1 THEN value1
	WHEN exp2 THEN value2
	...
	ELSE value_default
END

存储过程

~~这部分不知道是mysql方言还是sql标准~~

部分是mysql方言

创建procedure

create procedure procedure_name()
begin
	statements;
end;

调用

call procedure_name()

delimiter来改变终止符

删除

drop procedure if exists procedure_name

使用参数

create procedure procedure_name(
	param1 [TYPE1],
  param2 [TYPE2],
  ...
)
begin
	[statements];
end

参数需要指明类型
参数和表列名重名时，需要使用alias，或改变参数名

~~看来sql应该是静态类型语言~~

好像又不是

默认参数(if else)

begin
	if cond then
		[statements];
	else
		[statements];
	end if;
	[statements];
end

--- idiom
where attr = IFNULL(param_attr, attr)

抛出错误

如果参数不合法，可以抛出错误

IF payment_amount <=0 THEN
	SIGNAL SQLSTATE '22003'
		SET MESSAGE_TEXT = "invalid payment amount";
END IF;

Sqlstate error

建议在业务逻辑代码中处理，尽量少在sp中处理

OUT参数

Syntax :

CREATE PROCEDURE out_demo(
	param1 [type],
	OUT param2 [type]
)
BEGIN
	SELECT attr
	INTO param2
	FROM ...
END

参数前加OUT
select后加INTO

语义：

将@param2变量初始化并加入环境
将@param2作为参数调用SP
SP对@param2进行赋值
返回环境中的@param2

变量

用户/session变量

在整个数据库连接中有效。断开连接后清除。

语法

SET @varName = value;
--- 此后@varName在整个连接中有效
SELECT @varName;

变量名以@开头

语义

将varName变量添加到整个连接中

Idiom

通常和OUT结合使用

局部变量

只在单个存储过程中有效。

CREATE PROCEDURE local_var_demo()
BEGIN
	DECLARE var1 [type];
	DECLARE var2 [type] DEFAULT [defaul-val];
	
	SELECT a, b
	INTO var1, var2
	FROM ...
	WHERE ...;
	
	SET var2 = var1 / var2;
	
	SELECT var1, var2;
END

使用declare来声明变量以及类型
- 可以使用default来设定默认值
使用select … into来对变量赋值
使用set来对变量赋值
使用select来返回

函数

相比于存储过程，函数只能返回单个值

创建

CREATE FUNCTION [name](
    param [type]
) RETURNS [type]
--- 函数属性
DETERMINISTIC --- 结果不依赖于数据库中数据
READS SQL DATA --- 包含查询
MODIFIES SQL DATA --- 包含修改
BEGIN
		[body]
    return [value];
END;

删除

DROP FUNCTION IF EXISTS funcName

Mysql触发器 trigger

一段sql代码，在新增、更新或删除时自动触发

一般用于保证数据一致性

创建

CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments --- tableName
	FOR EACH ROW
BEGIN
	 UPDATE invoices
	 SET payment_total = payment_total + NEW.amount
	 WHERE invoice_id = NEW.invoice_id;
END;

命名一般使用 “表名_after(或before)_操作(insert/delete/update)”
目前mysql只支持单行级触发器
使用NEW.attr获取新增行的某属性，使用OLD获取delete的属性

查看

show triggers like "payments%"

删除

drop trigger if exits trigger_name

使用触发器审计(audit)数据

trigger两大用处：保证数据一致性和审计数据(log)

事件EVENTS

一个任务(或一个sql代码块)根据规划执行

show variables like "event%";
set global event_scheduler = ON;

事件依赖于这个变量

创建

CREATE EVENT yearly_delete_state_audit_rows
ON SCHEDULE
	--- AT "2022-12-31" ---执行一次
	EVERY 2 DAY STARTS NOW() ENDS "2022-12-31"
	--- 其他间隔 如YEAR MONTH HOUR等
	--- starts ends都是可选的
DO BEGIN
	[task]
END;

查看/drop/修改alter

show events like "name%";
---
drop events if exists event_name;
--- 修改 ---
alter event ... --- 替换create
alter event event_name DISABLE; ---暂时关闭event
alter event event_name ENABLE; --- 开启event

事务 in Mysql

ACID

原子性
一致性
隔离性：多个事务作用于相同数据时，同时只有一个事务能修改/更新
持久性

创建事务

START TRANSACTION;
[statement1];
[statement2];
...
COMMIT;

有时需要手动回滚事务时，则以ROLLBACK结尾
mysql中会把没有错误的sql封装到事务中（自动提交）。

并发和锁

MYSQL默认行为

当两个事务同时试图修改数据，一个事务会获得控制权，并在事务结束前(COMMIT或rollback)将该行数据上锁。

并发问题

LOST UPDATES

发生于没有使用锁的情况下，对相同数据在同一时间进行更新
后提交的事务覆盖了先提交的事务的执行结果
解决方法：使用锁

脏读

事务A对数据修改后，在其提交前修改后的数据就被另一事务(B)读取
如果事务A回滚了，则事务B读取的数据就是错的
解决方法：使用合适的隔离等级
- mysql中有4个等级，其中read committed让事务只能读取已提交的数据

NON-REPEATING READ

一个事务中多次读相同的数据，由于外部修改了数据，其会返回不同的结果
解决方法：在进入事务时对数据进行快照，事务期间使用快照。

Phantom Read

一个事务在查询时，其数据由于其他事务修改，导致漏掉了符合条件的数据
解决方法：事务期间，防止外界对数据修改/查询

Mysql隔离等级

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HsVV3M7b-1689736377876)(…/…/Library/Application Support/typora-user-images/image-20220111010749499.png)]

mysql默认等级：repeatable read

调整等级

--- 当前等级
show variables like "%isolation%"
--- 给下一个事务设置隔离等级
set transaction isolation level serializable;
--- 给当前连接设置隔离等级
set session transaction isolation level xxx;
--- 设置全局隔离等级，全部顺序执行
set global transaction isolation level xxx;

死锁

mysql会自动检测死锁，并rollback事务。

--- trans1
start transaction;
update table1 ...; --- |<--获取table1的锁
update table2 ...; --- |<--等待table2的锁
commit;
--- trans2
start transaction;
update table2 ...; --- |<--获取table2的锁
update table1 ...; --- |<--等待table1的锁
commit;

MySQL 数据类型

字符串

类型	长度
char(x)	定长
varchar(x)	变长，最长65,535字符(~64KB)
MEDIMTEXT	变长，最多16MB
LONGTEXT	变长，最多4GB
TINYTEXT	变长，255B
TEXT	变长，64KB

最好使用varchar，因为可以被索引

整形

当试图加入超过范围的数时会报错
补零
- INT(4) => 0001
- 只影响显示，不影响存储

小数

decimal–定点数，计算无精度损失
- 用于金额
float/double–浮点数，近似值，范围大，有精度损失
- 用于科学计算

布尔

类型：bool/boolean

值：true(1)/false(0)

枚举/集合类型

ENUM("small", "medium", "big")
SET(...)

避免使用，不易于扩展
可以新建表(look-up table)来存

时间

date
time
datetime 8b
timestamp 4b(up to 2038)
year

BLOBS（二进制）

用于存二进制数据

最好不要在数据库存二进制数据

JSON对象

类型: JSON

创建

update products
set propeties = JSON_OBJECT(
	'weigth', 10,
	'dimensions', JSON_ARRAY(1,2,3),
	'manufacturer', JSON_OBJECT('name', 'sony')
)
where ...

JSON_OBJECT(k1,v1,k2,v2,...)

提取

SELECT JSON_EXTRACT(properties, '$.weight')
from ...

--- alter 1
SELECT properties -> '$.dimensions[0]' from ...

--- alter 2 会去除字符串中引号
select properties ->> '$.manufacturer.name'

更新

update products
set properties = JSON_SET(
	properties,
	'$.weight', 20,
	'$.age', 10
)
where ...

JSON_SET(JSON_OBJ, k1, v1, k2, v2, ...)
- 改变属性值，或添加属性
JSON_REMOVE(JSON_OBJ, k1, k2...)
- 删除属性
二者都会返回一个新的JSON对象

存储引擎

show engines;

alter table xxx
	engine = InnoDB;

常用

MyISAM
- 5.5之前，不支持事务、外键等
InnoDB
- 支持事务、外键

索引indexes

用于加快查询

创建索引表（二叉树）
索引表一般足够小可以存在内存中

代价

数据库更大
在更新数据时需要更新索引，影响性能

Deisgn indexes based on your queries, not your tables

Explain分析sql执行过程

explain select * from tableName use index (indexName);

type
- all，全表扫描
- ref，非全表
- Index：全index扫描
rows：扫描的记录数
Possible_keys：可选的索引
Key：执行使用的索引

创建索引

create index idx_columnName on tableName (columnName)

--- create index idx_state on customers (state)

查看索引

show indexes in tableName;

主键自动创建索引
collation: 排序，A=升序，D=降序
cardinality：索引中唯一值的估算数量
- 先执行analyze table tableName，再查看索引以精确统计
Index_type
- Btree：普通B树索引
- Fulltext：全文索引

聚类索引

主键
每张表最多一个

二级索引

创建时mysql会自动添加主键
创建外键时会自动创建对应列的索引

前缀索引

存储字符串索引需要大量空间，故将整行做为索引是不效率的

create index idx_name on customers (last_name(20))

使用前二十个做索引

如何决定取多少个做索引？

索引目的是尽可能多的区分数据，比如一共1000条数据，我们想让索引中唯一值尽可能毕竟这个值

select
	count(distinct left(last_name, 1)),
	count(distinc left(last_name, 5)),
	count(distinc left(last_name, 10)),
from customers;

分别查看以1，5，10个字符做标识的唯一值数量
选取合适的，数量多，字符数少

复合索引

一张表中有多个索引时，mysql也只会选择一个索引，其余的正常查询

此时可以选择多个列做一个索引

create index idx_state_points on customers (state, points);

实际上，复合索引比单列索引更好

复合索引中列的顺序很重要，应该将常用的、区分度高(cardinality)的放在前面
- 需要根据查询来决定

使用索引优化or查询

拆分为union查询
对每一段查询建立合适的索引

全文索引

用于加速某行字符串匹配，比如将博客的标题、内容存在数据库的列中，用户使用关键字查询

select *
from post
where title like '%react redux%' or 
	body like "%react redux%";

普通方法使用like匹配很长字符串是低效的

创建全文索引

create fulltext index idx_title_body on posts (title, body);

使用全文索引

select *, 	match(title, body) against("react redux")
from posts
where 
	match(title, body) --- 必须包含所有索引的列
  against("react redux");

match(title, body) against("react redux")会返回一个相关性的评分(0-1间)

boolean mode

强制包含或排除指定字符串

in boolean mode
-str1：排除st1
+str2：包含str2
"handling a form"：包含短语

...
match(title, body) against('react -redux +form' in boolean mode);

搜索和react相关、不包含redux，包含form的内容

查看查询代价

show status like "last_query_cost";

会计算排序代价
使用索引优化order by
- 复合索引中，数据会先按第一个索引排序，然后在排序结果中按第二索引排序
Select * 会影响性能，因为索引不能cover所有列
- 故应该给经常查询的列加索引，然后只select哪些列

权限管理

创建用户

create user john --- 任何ip
	--- join@127.0.0.1 ip
	--- john@localhost 主机名
	--- john@abc.com 域名
	--- john@'%.abc.com' 子域名
	identified by "1234";

%通配符用法和字符串中的基本相同

查看用户

--- 方法1
select * from mysql.user;

删除用户

drop user john@localhost;

修改密码

--- 当前用户
set password = '1234';
--- 其他用户
set password john = '1234';

授予权限

读写权限，不能改变表

--- 典型的用户权限
grant select, insert, update, delete, execute
on sql_store.* --- 选择表
to john@localhost;

Admin

--- 典型管理员权限
grant all ---所有权限
on *.* --- 所有数据库中所有表
to admin;

权限列表MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Privileges Provided by MySQL

查看权限

show grants on john;

撤销权限

revoke [privilegs]
on [db].[table]
from userName

一些关于SQL的思考

其实数据库书中有区分查询语言SQL和管理语言SML

现在看来SQL的执行结果可以作为SML的输入
SQL的具体执行结果到底如何，目前尚未看出
单列的SQL查询结果应该等同于元组(1, 2, 3)，可以被用于IN
使用()打包单个值，使其可以被IN/USING/Value等处理

select/from/where都可以使用子查询的结果

感觉mysql中sql的语义还挺复杂的，一点都不会比普通的编程语言简单

SQL语句结构理解

----------------------
------stage-1---------
----------------------
select 
----------------------
------stage-2---------
----------------------
from
where
----------------------
------stage-3---------
----------------------
group by
having
order by

个人理解sql语句分为三部分

select用于定义整个查询的元信息
from/where从磁盘io数据到内存（查询）
group by/having/order by以及select中的函数负责查询结果后处理，包括计算、分组、重命名

Reasons:

where中无法使用select中对列的alias
- 因为select中的alias是在数据查询完成后（第二步）对内存中数据做后处理，而where执行时数据还没从磁盘中io出来
having不会影响整体数据查询的磁盘io量（可通过explain验证），且可以使用select中的alias
- 因为having是在第二步完成后对数据做后处理，因此对查询数据量无影响。同时其可以使用select的alias，说明select对数据重命名发生在查询完成后
- 同理having中也可以使用统计函数
select中的字段数会影响查询效率（一直说要避免使用select *），因此select中的元数据即影响第二步和第三步，而不是仅影响第三步（若如此，无论select什么字段第二步都查表中所有字段并根据select的参数在第三步后处理时才筛选，该种实现方式简洁但效率低下）。

AlohaQing

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL in MySQL

方法语义语法Inner join连接两表，只返回join条件成功的项目JOININNER JOINOuter join连接两表，保留其中一表中所有项目，join条件失败时设置为NullLEFT JOIN: 保留FROM中的表RIGHT JOIN：保留JOIN中的表Outer join可以视为受限的inner joinLEFT可以视为保留之前的表RIGHT可以视为保留后面的表。
复制链接

扫一扫