SQL 复习

学习来源

https://www.bilibili.com/video/BV1UE41147KC

SELECT

SELECT first_name AS fs FROM custumers;

AS 关键字可以给列起别名，别名如果有空格可以用单引号包裹

`WHERE`

字符串比较用 = 会忽略大小写
date 类型可以用比较符号比较，字面值形如1999-02-02
AND 优先级大于 OR
IN 关键字
- SELECT * FROM cutomers WHERE state IN ('VA', 'GA', 'NY');
在过滤条件中可以使用 ( , , ) 构造临时集合
BETWEEN xxx AND xxx
- SELECT * FROM customers WHERE points BETWEEN 1000 AND 2000;
- 同样适用于 date 类型
LIKE 模糊查询，忽略字母大小写
- SELECT * FROM customers WHERE last_name LIKE 'b%';
- % 匹配任意字符
- _ 匹配单个字符
REGEXP 使用正则表达式查询，忽略字母大小写
- SELECT * FROM customers WHERE last_name REGEXP '^b$';
- 注意如果条件仅是字母，则返回结果会是包含字母的所有结果

`ORDER BY` 排序

....ORDER BY first_name;
....ORDER BY state, first_name; 先按state排序，重复的按first_name二级排序

`LIMIT` 限制数量

SELECT * FROM ... LIMIT 300; 只返回前300条记录
.... LIMIT 6, 3; 跳过前6条，返回之后的3条记录

子句顺序

SELECT
FROM
USE INDEX(idx_xxx) 强制指定使用哪个索引
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

JOIN

`INNER JOIN` (默认)

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

一旦给表起了别名，就不可以再使用原表名了

SELECT order_id, o.customer_id, first_name
FROM orders   o -- 在表后可以起个别名
JOIN cutomers c -- 在表后可以起个别名
    ON o.customer_id = c.customer_id;

跨数据库连接表

直接用数据库名点出来就行

USE sql_store;

SELECT * FROM order_items   oi
JOIN sql_inventory.products p  ON oi.product_id = p.product_id;

自连接

前后要使用不同的别名

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employee e
JOIN employee m
    ON e.reports_to = m.employee_id;

多表连接

SELECT 
    o.order_id,
    o.order_name,
    c.first_name,
    os.name AS status
FROM orders         o
JOIN customers      c
    ON o.customer_id = c.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id;

复合连接操作

SELECT *
FROM order_items         oi
JOIN order_item_notes    oin
    ON oi.order_id = oin.order_id AND oi.product_id = oin.product_id;

隐式连接

不建议使用隐式连接，尽量使用显式连接

SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

`OUTER JOIN`JOIN

LEFT JOIN 左侧的表会返回全部结果
RIGHT JOIN 右侧的表会返回全部结果
上述省略了OUTER 关键字；写全为LEFT/RIGHT OUTER JOIN
尽量使用左连接，减少使用右连接
外连接同样支持多表连接，用法与内链接相同
外连接同样支持自连接
如果连接的两个列名称相同，可以使用USING关键字简化
- .....JOIN customers USING (customer_id)
- 复合连接操作也可简化：...JOIN xxxx USING (customer_id, product_id)

自然连接

数据库自己决定连接哪一列，不推荐使用

SELECT *
FROM orders o
NATURAL JOIN payments p

交叉连接

两个表的笛卡尔积

SELECT *
FROM orders o
CROSS JOIN payments p

简写

SELECT *
FROM orders o, payments p

UNION

合并多段查询的记录

第一段查询的列名将决定往后查询的列名

SELECT order_id, order_date, 'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT order_id, order_date, 'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01'

合并不同表的查询记录，但要求返回列的数量要一致

SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers

INERT

INSERT INTO shippers (name)
VALUES('asd'), ('qwe');

有外键与自增主键的情况在多个表中插入数据

MySQL内置的LAST_INSERT_ID()方法获取最后插入的ID值，只适用于自增主键

LAST_INSERT_ID()表示AUTO_INCREMENT由于最近执行的INSERT语句而成功为列添加的第一个自动生成的值。

LAST_INSERT_ID()如果没有成功插入行，则值保持不变。

INSERT INTO orders (customer_id, order_date, status)
VALUES(1, '2019-01-02', 1);

INSERT INTO order_items
VALUES(LAST_INSERT_ID(), 1, 1, 2.9),(LAST_INSERT_ID(), 2, 1, 3.9);

复制一整张表

复制一整张表的内容到一张新表，但是新表结构不会有主键和自增

CREATE TABLE orders_new AS
SELECT * FROM orders;

使用子查询复制数据

INSERT INTO orders_new 
SELECT * FROM orders where .....

UPDATE

UPDATE invoices
SET payment_total = 10, payment_date = '2019-01-04'
WHERE invoice_id = 1;

更新多条记录

UPDATE invoices
SET payment_total = 10, payment_date = '2019-01-04'
WHERE client_id in (3, 4);

使用子查询更新记录，子查询要用()包裹

UPDATE invoices
SET payment_total = 10, payment_date = '2019-01-04'
WHERE client_id = 
        (SELECT client_id FROM clients WHERE state = 'CA')

UPDATE invoices
SET payment_total = 10, payment_date = '2019-01-04'
WHERE client_id IN 
        (SELECT client_id FROM clients WHERE state IN ('CA', 'NY'))

DELETE

删除数据

DELETE FROM invoices WHERE ....

支持子查询

DELETE FROM invoices WHERE client_id IN (SELECT client_id FROM ....)

聚合函数

MAX()
MIN()
AVG()
SUM()
COUNT()， COUNT(DISTINCT col)

列中空值会被忽略

GROUP BY

分组数据

SELECT 
    client_id, 
    SUM(invoice_total) AS total
FROM invoices
WHERE invoice_date > '2019-01-02'
GROUP BY client_id
ORDER BY total

使用GROUP BY对多列进行分组，则会将两列连起来看作一个分组

只能用于SELECT关键字后面的列

SELECT 
    state,
    city,
    SUM(invoice_total) AS total
FROM invoices
JOIN clients USING (client_id)
GROUP BY state, city

HAVING

针对分组后的数据再过滤，支持AND OR

注意：使用聚合函数后生成的新列只能用HAVING过滤，并且只能用于SELECT关键字后面的列

SELECT 
    client_id, 
    SUM(invoice_total) AS total
FROM invoices
GROUP BY client_id
HAVING total > 500

WITH ROLLUP

仅支持MySQL

在最后一行，统计聚合函数列的记录的总和

使用WITH ROLLUP时，GROUP BY关键字不能接列别名，只能使用真实列名

SELECT 
    client_id, 
    SUM(invoice_total) AS total
FROM invoices
GROUP BY client_id WITH ROLLUP

复杂SQL编写

子查询 vs JOIN，耗时相同应选择可读性更好的

SELECT DISTINCT customer_id, first_name
FROM customers   c
JOIN orders      o  USING(customer_id)
JOIN order_items oi USING(order_id)
WHERE oi.product_id = 3

ALL

SELECT * 
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

返回大于client_id为3的invoice_total最大值的所有记录

这里我们先求了一下满足条件的最大值，然后找大于最大值的记录

我们可以用ALL关键字改写，ALL总是可以与MAX()互换

SELECT * 
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

改写思路是，先得到所有满足条件的值，在找大于所有这些值的记录

相当于

SELECT * 
FROM invoices
WHERE invoice_total > ALL (100, 200, 300,...)

ANY

IN可以与= ANY互换

SELECT * FROM clients
WHERE client_id IN (
    SELECT client_id FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

等价于

SELECT * FROM clients
WHERE client_id = ANY (
    SELECT client_id FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
)

EXISTS

考虑场景：找出收据的顾客

子查询返回一个数据集，外查询看有多少记录在这个数据集中

SELECT * FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id FROM invoices
)

用EXISTS重写

SELECT * FROM clients c
WHERE client_id EXISTS (
    SELECT client_id FROM invoices
    WHERE client_id = c.client_id
)

我们在这里使用相关子查询，但子查询并没有真正返回数据集，而是每条记录都看一下表中是否有记录满足条件，返回一个TRUE或者FALSE给WHERE

如何选择IN还是EXISTS：当子查询返回的数据量很大，EXISTS效率更高

SELECT中的相关子查询

注意：此处的invoice_avg是一个第三列计算出来的常数

SELECT invoice_id,
       invoice_total,
       (SELECT AVG(invoice_total) FROM invoices) AS invoice_avg
       invoice_total - (SELECT invoice_avg) AS different
FROM invoices

SELECT 
    client_id,
    name,
    (SELECT SUM(invoice_total)
     FROM invoices
     WHERE client_id = c.client_id) AS total_sales,
    (SELECT AVG(invoice_total) FROM invoices) AS avg_sales,
    (SELECT total_sales - avg_sales) AS differencs
FROM clients c

FROM中的相关子查询

将一个大语句变成FROM的“表”，但会影响可读性，可用视图优化

注意：必须给子查询一个别名

SELECT * 
FROM (
    SELECT 
        client_id,
        name,
        (SELECT SUM(invoice_total)
        FROM invoices
        WHERE client_id = c.client_id) AS total_sales,
        (SELECT AVG(invoice_total) FROM invoices) AS avg_sales,
        (SELECT total_sales - avg_sales) AS differencs
    FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL

常用函数

数值函数

ROUND() 取整，
- ROUND(5,33, 1) 参数：取整的数值，保留几位小数(可选参数)
TRUNCATE() 截取部分
- TRUNCATE(5,33, 1) 参数：截取的数值，保留几位小数(可选参数)
CEILING() 向上取整
- CEILING(5.3)
FLOOR() 向下取整
- FLOOR(5.3)
ABS() 取绝对值
- ABS(-5.3)
RAND() 取0-1之间的随机小数，无参数

字符串函数

LENGTH(str)
UPPER(str)
LOWER(str)
TRIM(str) 清除两侧空格
LTRIM(str) 清除左侧空格
RTRIM(str) 清除右侧空格
LEFT(str, int) 截取前几个字符
RIGHT(str, int) 截取后几个字符
SUBSTRING(str, start, length) 截取字符串，start从1开始计算，length可选
LOCATE(str1, str2) 找str1在str2出现的第一个位置
- 位置从1开始，忽略大小写
- 找不到返回 0
REPLACE(str1, str2, str3) 把str1中的str2替换成str3
CONCAT(str1, str2) 连接两个字符串

日期函数

NOW() 当前日期+时间
CURDATE() 当前日期
CURTIME() 当前时间
YEAR(date) 返回date的年
MONTH(date) 返回date的月
DAY(date) 返回date的日
HOUR(date) 返回date的小时
MINUTE(date) 返回date的分
SECOND(date) 返回date的秒
DAYNAME(date) 返回date的英文星期
MONTHNAME(date) 返回date的英文月份

日期格式化

DATE_FORMAT(date, 'format-string') 日期格式化
- DATE_FORMAT(NOW(), '%M %d %Y')

日期计算

DATE_ADD(date, INTERVAL int DAY/MONTH/YEAR) 往后计算
DATE_SUB(date, INTERVAL int DAY/MONTH/YEAR) 往前计算
DATEDIFF(date, date) 计算日期间隔，以天为单位，忽略时间
- DATEDIFF('2019-02-06', '2019-02-07')
TIME_TO_SEC('默认时间格式') 计算从零点开始经过了多少秒
- TIME_TO_SEC('9:00')

其他

IFNULL(col, str) (用于SELECT紧接的列)如果col列的记录为空，则替换为str
COALCASE(col1, col2, str) (用于SELECT紧接的列)如果col1的记录为空，则替换为col2的值，如果还为空，则替换为str
IF(exp, first, second) 若exp为真则返回first，否则second

SELECT
    order_id,
    IF(
        YEAR(order_date) = YEAR(NOW()),
        'Active',
        'Archive')
FROM orders;

CASE WHEN switch case

CASE
    WHEN exp THEN res1
    WHEN exp2 THEN res2
    ELSE res3
END

SELECT 
    order_id,
    CASE
        WHEN YEAR(order_date) = YEAR(NOW())     THEN 'Active'
        WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN 'Last Year'
        WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN 'Archive'
        ELSE 'ELSE'
    END AS Category
FROM orders

VIEW

VIEW 可以将查询结果暂存为一张虚拟表，用以替代复杂的子查询

但注意：VIEW 并不真正存储数据

创建视图

CREATE VIEW sales_by_client AS
SELECT 
    c.client_id,
    c.name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices USING(client_id)
GROUP BY client_id, name;

SELECT * FROM sales_by_client;

CREATE VIEW clients_balance AS
SELECT 
    c.client_id,
    c.name,
    SUM(invoice_total - payment_total) AS balance
FROM clients c
JOIN invoices i USING(client_id)
GROUP BY client_id, name;

更改或删除视图

删除视图

DROP VIEW sales_by_client;

修改视图 CREATE OR REPLACE

CREATE OR REPLACE VIEW clients_balance AS
SELECT 
    c.client_id,
    c.name,
    SUM(invoice_total - payment_total) AS balance
FROM clients c
JOIN invoices i USING(client_id)
GROUP BY client_id, name;

推荐将VIEW代码保存为sql文件

可更新视图

如果创建的视图代码中没有以下内容，则该视图称为可更新视图

DISTINCT
聚合函数
GROUP BY
HAVING
UNION
位于选择列表中的子查询
FROM子句中包含多个表
SELECT语句中引用了不可更新视图
WHERE子句中的子查询，引用FROM子句中的表

可更新视图意味着你可以对视图进行删改操作，这样也会影响到原表数据

`WITH OPTION CHECK`

当我们对可更新视图做UPDATE时，修改后的行可能不符合视图要求，即新行不会出现在视图的结果中

如果我们不想这样的事情发生，可以在创建视图时加上WITH OPTION CHECK，它会直接让这样的操作报错

CREATE OR REPLACE VIEW invoice_with_balance AS
SELECT 
    invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_total AS balance,
    invoice_date,
    due_date,
    payment_date
FROM inovices
WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION

此时若我们想执行update语句

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2;

可知修改后的结果不再视图的查询条件中，此时会报错拒绝执行update语句

视图的其他优点

视图可以对基础表提供一层抽象，若基础表改动则仅需改动视图而不需要更改查询
视图可以限制对基础表的访问

存储过程

创建存储过程

DELIMITER $$ -- 需要临时将分割符改成$$保证这个部分是一个整体
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;
END$$
DELIMITER ; -- 结束后改回原来

DELIMITER $$ 
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
    SELECT * FROM clients c
    WHERE c.state = state;
END$$
DELIMITER ;

DELIMITER $$ 
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
    SELECT * FROM clients c
    WHERE c.state = IFNULL(state, c.state); -- 如果参数为NULL，则查询所有
END$$
DELIMITER ;

DELIMITER $$ 
CREATE PROCEDURE get_payments(
    client_id INT,
    payment_method_id TINYINT
)
BEGIN
    SELECT * FROM payments
    WHERE p.client_id = IFNULL(client_id, p.client_id) AND
          p.payment_method = IFNULL(payment_method_id, p.payment_method);
END$$
DELIMITER ;

调用

CALL get_clients();

删除

DROP PROCEDURE IF EXISTS get_clients;

参数验证

DELIMITER $$ 
CREATE PROCEDURE make_payment(
    invoice_id INT,
    payment_amount DECIMAL(9, 2),
    payment_date DATE
)
BEGIN
    IF payment_amount <= 0 THEN
        SIGNAL SQLSTATE '22003' -- 抛出代码为22003的错误
            SET MESSAGE_TEXT = 'Invalid payment amount';
    END IF;

    UPDATE invoices i
    SET i.payment_total = payment_amount,
        i.payment_date = payment_date
    WHERE i.invoice_id = invoice_id;
END$$
DELIMITER ;

输出参数

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client(
    client_id INT,
    OUT invoices_count TINYINT,
    OUT invoices_total DECIMAL(9, 2)
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = client_id AND payment_total = 0;
END $$
DELIMITER ;

调用语句

-- 定义变量
SET @invoices_count = 0;
SET @invoices_total = 0;

call get_unpaid_invoices_for_client(3, @invoices_count, @invoices_total);

select @invoices_count, @invoices_total;

变量

User or session variable
- 变量在整个会话存活
- SET @count = 0
Local vriable
- 存储过程本地变量
- DECLARE count INT DEFAULT 0


DELIMITER $$
CREATE PROCEDURE get_risk_factor()
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoice_total DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoice_count INT;

    SELECT COUNT(*), SUM(invoice_total)
    INTO invoice_total, invoice_count
    FROM invoices;

    SET risk_factor = invoice_total / invoice_count * 5;

    SELECT risk_factor;
END $$
DELIMITER ;

函数

与存储过程不同，函数只能返回单一值

函数属性，属性可以多个

DETERMINISTIC
READS SQL DATA
MODIFIES SQL DATA


DELIMITER $$
CREATE FUNCTION get_risk_factor(
    client_id INT
)
RETURNS INTEGER
-- 函数属性
READS SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoice_total DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoice_count INT;

    SELECT COUNT(*), SUM(invoice_total)
    INTO invoice_total, invoice_count
    FROM invoices i
    WHERE i.client_id = client_id;

    SET risk_factor = invoice_total / invoice_count * 5;

    RETURN IFNULL(risk_factor, 0);
END $$
DELIMITER ;

触发器

创建

AFTER / BEFORE
INSERT / UPDATE / DELETE
NEW / OLD

DELIMITER $$

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

DELIMITER ;

DELIMITER $$

CREATE TRIGGER payments_after_delete
    AFTER DELETE ON payments
    FOR EACH ROW
BEGIN
    UPDATE invoices
    SET payment_total = payment_total - OLD.amount
    WHERE invoice_id = OLD.invoice_id;
END $$

DELIMITER ;

查看触发器

SHOW TRIGGERS;

删除触发器

DROP TRIGGER IF EXISTS trigger_name;

EVENT

略

事务

START TRANSACTION;

XXXXX

COMMIT;

数据类型

String 类型

CHAR(x)
VARCHAR(x) max 65535 characters (~64KB)
MEDIUMTEXT max 16MB
LONGTEXT max 4GB
TINYTEXT max 255 bytes
Text (类型不能走索引) max 64KB

国际语言

英文字母占1个字节
欧洲与中东字母占2个字节
中文、亚洲文字占3个字节

整数类型

TINYINT 1B [-128, 127]
UNSIGNED TINYINT 1B [0, 255]
SMALLINT 2B [-32K, 32K]
MEDIUMINT 3B [-8M, 8M]
INT 4B [-2B, 2B]
BIGINT 8B [-9Z, 9Z]

定点数与浮点数

DECIMAL(p, s)
DEC
NUMERIC
FIXED
FLOAT
DOUBLE

bool

BOOL
BOOLEAN
1 = TRUE; 0 = FALSE

日期

DATE
TIME
DATETIME 8B
TIMESTAMP 4B (up to 2038)
YEAR

BLOB

用于存储大型二进制数据

TINYBLOB 255B
BLOB 65KB
MEDIUMBLOB 16MB
LONGBLOB 4GB

JSON

MySQL 8之后支持JSON类型

UPDATE products
SET properties = '
{
    "asd": 1
}
'
WHERE product_id = 1;

UPDATE products
SET properties = JSON_OBJECT(
    'weight', 10, 
    'dimen', JSON_ARRAY(1, 2, 3),
    'manufacturer', JSON_OBJECT('name', 'sony')
)
WHERE product_id = 1;

访问JSON

SELECT product_id, JSON_EXTRACT(properties, '$.weight')
FROM products
WHERE prodect_id = 1;

或

SELECT product_id, properties -> '$.weight'
FROM products
WHERE prodect_id = 1;

SELECT product_id, properties -> '$.dimen[0]'
FROM products
WHERE prodect_id = 1;

SELECT product_id, properties -> '$.manufacturer.name'
-- 这样结果会带着""，即"sony"
FROM products
WHERE prodect_id = 1;
SELECT product_id, properties ->> '$.manufacturer.name'
-- 使用双箭头结果会去掉""，即sony
FROM products
WHERE prodect_id = 1;

修改JSON列中的部分属性，或新增属性

UPDATE products
SET properties = JSON_SET(
    properties,
    '$.weight', 20, 
    '$.age', 10
)
WHERE product_id = 1;

删除JSON中的某个属性

UPDATE products
SET properties = JSON_REMVOE(
    properties,
    '$.weight'
)
WHERE product_id = 1;

表结构相关语句

创建数据库

CREATE DATABASE IF NOT EXISTS db_name;

删除数据库

DROP DATABASE IF EXISTS db_name;

创建表

CREATE TABLE IF NOT EXISTS customers
(
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name  VARCHAR(50) NOT NULL,
    points      INT NOT NULL DEFAULT 0,
    email       VARCHAR(255) NOT NULL UNIQUE
);

删除表

DROP TABLE IF EXISTS db_name;

更改表

ALTER TABLE customers
    ADD last_name VARCHAR(50) NOT NULL AFTER first_name,
    ADD city      VARCHAR(50) NOT NULL,
    MODIFY first_name VARCHAR(55) DEFAULT '',
    DROP points;

创建外键关系

CREATE TABLE IF NOT EXISTS orders
(
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY fk_orders_customers (customer_id)
        REFERENCES customers (customer_id)
        ON UPDATE CASCADE   -- 级联更新
        ON DELETE NO ACTION -- 拒绝删除
);

更改主键外键

ALTER TABLE orders
    ADD PRIMARY KEY (order_id),
    DROP PRIMARY KEY,
    DROP FOREIGN KEY fk_orders_customers,
    FOREIGN KEY fk_orders_customers (customer_id)
        REFERENCES customers (customer_id)
        ON UPDATE CASCADE   -- 级联更新
        ON DELETE NO ACTION -- 拒绝删除
;

索引

使用EXPLAIN关键字查看代码的效率

EXPLAIN SELECT customer_id FROM customers WHERE state='CA'

type 列的值代表了查询的方式
- const system：代表mysql能对查询的某部分进行优化并将其转化成一个常量
- eq_ref 代表primary key 或 unique key 索引的所有部分被连接使用
- ref 代表使用普通索引或者唯一性索引的部分前缀
- fulltext
- ref_or_null 类似ref，但是可以搜索值为NULL的行
- index_merge 使用了索引合并的优化方法
- unique_subquery
- index_subquery
- range 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行
- index 和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些
- ALL 代表全表查询
rows 列代表了查询了多少行
possible_keys 可能用到的索引
key 实际用到的索引

创建索引

我们应依据查询的频繁程度建立相应的索引

CREATE INDEX idx_state ON customer(state);

每当创建一个二级索引(普通索引)时，MySQL会自动将主键包含在二级索引中

比如在state列上创建的索引，MySQL会隐含主键列和state列

查看索引

SHOW INDEXES IN customers;

key_name 列
- PRIMARY 聚集索引，建立主键时MySQL会自动建立的索引
- fk_xxxx MySQL为外键自动建立的索引
column_name 列，索引建立的列
collation 列，排序方式
- A 升序
- B 降序

重新分析表，这样SHOW IDNEXES会更加精确

ANALYZE TABLE customers;

在FROM和WHERE子句之间可以USE INDEX(idx_xxx)强制指定使用哪个索引

前缀索引

对字符串列的索引不需要针对一条数据的所有内容，我们可以只针对前几个字符建立索引以减小索引的大小，从而加快索引速度

CREATE INDEX idx_lastname ON customers (last_name(10));
-- char和varchar长度指定是可选的，但是TEXT和BLOB必须有

前缀长度的选择

SELECT 
    COUNT(DISTINCT LEFT(last_name, 1)),
    COUNT(DISTINCT LEFT(last_name, 5)),
    COUNT(DISTINCT LEFT(last_name, 10))
FROM customers;
-- 查看不同前缀长度唯一值的数量
-- 1-25；5-996；10-996，所以长度为5比较合适

全文索引

用于搜索字符串列的索引

CREATE FULLTEXT INDEX idx_title_body ON posts(title, body);

如何使用全文索引

SELECT * FROM posts
WHERE MATCH(title, body) AGAINST('react redux');
-- AGAISNT函数接受一个字符串，内容一空格分割的多个目标字符串

-- 这两个函数会产生一个相关性的0~1的浮点数，我们可以select看一下
SELECT *, MATCH(title, body) AGAINST('react redux')
FROM posts
WHERE MATCH(title, body) AGAINST('react redux');

全文索引有两种模式，一个默认的自然语言模式，二是布尔模式

布尔模式

SELECT * FROM posts
WHERE MATCH(title, body) AGAINST('react -redux' IN BOOLEAN MODE);
-- 在redux前面加上负号代表我们的搜索要排除redux单词

SELECT * FROM posts
WHERE MATCH(title, body) AGAINST('"Handling a form"' IN BOOLEAN MODE);
-- 使用双引号括起来，代表我们要精确匹配双引号中的文字

复合索引

CREATE INDEX idx_state_points ON customer(state, points);

用于多条件查询

SELECT customer_id FROM customers
WHERE state = 'CA' AND points > 100;

复合索引最多支持16列，但在4到6列之间就能达到很好的性能，但还是应依据实际情况

复合索引中列的顺序问题

越频繁使用的列应越靠前
一列中唯一值越多的越靠前
- 在字符串的查询中，也应考虑前缀的唯一程度，比如姓氏的前缀重复度较高，不应放在最靠前的顺序
=条件的约束性更强，所以可以更好的缩小范围
一切以实际出发，可以COUNT(DISTINCE xxx)看一下唯一值的多少，再EXPAIN测试一下哪一种方案更好

MySQL的复合索引遵循了最左前缀原则，当建立索引(index_A, index_B, index_C)时：

使用索引有效的字段为：
- 'index_A’
- 'index_A,index_C’
- 'index_A,index_B’
- 'index_A,index_B,index_C’；
使用索引失效的字段为：
- 'index_B’
- 'index_C’
- 'index_C,index_B’；

删除索引

DROP INDEX idx_state ON customers;

索引失效

SELECT * 时索引失效
查询时，采用is null条件时，不能利用到索引，只能全表扫描
在WHERE子句中对列作运算
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
- 要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
对于多列索引，不是使用的第一部分，则不会使用索引
like查询以%开头
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
如果mysql估计使用全表扫描要比使用索引快,则不使用索引

使用索引加速排序

在有索引的列上使用ORDER BY可以使用索引加速排序
在没有索引的列上使用ORDER BY，MySQL会使用耗时的外部排序

我们可以查看系统变量last_query_cost看上次查询的成本

SHOW STATUS LIKE 'last_query_cost';
-- 对于order by语句，不使用索引的情况下，成本将贵十倍

关于排序的方向

如果仅有一列，则方向相同相反都走索引
如果是复合索引，则每一列的排序方向都要与每一列的索引排序方向保持相同或相反
- 例如如果第一列相同，而第二列相反，则不会使用索引

聚集索引

只读索引，不读表

在加索引时，先看WHERE子句中最频繁的列，再看ORDER BY子句中的列，最后看SELECT的列

安全与权限

创建用户

-- 没有连接限制
CREATE USER user_name IDENTIFIED BY 'password'

-- 令该用户可以访问该ip地址或域名(但不能访问子域名)
CREATE USER user_name@ip_address/domain.com IDENTIFIED BY 'password'

-- 令该用户可以访问该域名的子域名
CREATE USER user_name@'$.domain.com' IDENTIFIED BY 'password'

查看用户

SELECT * FROM mysql.user;

删除用户

DROP USER user_name;
-- or
DROP USER user_name@domain.com;

修改密码

SET PASSWORD FOR user_name = 'password';

-- 为当前登陆用户更改密码
SET PASSWORD = 'password';

授予权限

没有更改表结构权利的用户

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON some_database.*
TO user_name;

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON some_database.some_table
TO user_name;

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON some_database.*
TO user_name@domain.com;

管理员

GRANT ALL
ON some_database.*
TO user_name;

GRANT ALL
ON *.*
TO user_name;

GRANT ALL
ON some_database.*
TO user_name@domain.com;

查看权限

SHOW GRANTS FOR user_name;

-- 查看当前用户的权限
SHOW GRANTS;

撤销权限

REVOKE SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON some_database.*
FROM user_name;

郭佬笔记