MySQL 数据库

边文凯/sql_study

mysql数据库的使用

https://gitee.com/Kevin_Bian/sql_study

第二章：查询语句

select *
from order_items
where order_id = 6 and quantity * unit_price > 30 ;

select *
from customers
-- where state = 'VA' or state = 'FL' or state = 'GA'
where state  in ('VA','FL','GA' ) ;  # IN运算符简化查询条件

SELECT *
FROM products
WHERE quantity_in_stock IN (49,38,72) ;

SELECT *
FROM customers
-- WHERE points >= 1000 AND points <= 3000
WHERE points BETWEEN 1000 AND 3000;  # BETWEEN 运算符

SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01';

SELECT *
FROM customers
WHERE last_name LIKE '%b%' OR '_a';
-- LIKE运算符   %b%:匹配含有b/B的last_name  %:代表该处占任意数量的字符
-- _a:只有两个字符，第一个随意，但第二个必须是字符a/A，_:表示该处只占一个字符

SELECT *
FROM customers
WHERE last_name REGEXP '^b|D$|caff|[gim]e|a[a-h]';
-- REGEXP正则表达式 ^b(以b/B开头)，d$以d/D结尾 |:或者含有caff字符串 或者[gim]e :含有ge或ie或me

SELECT *
FROM customers
WHERE phone IS NULL ;
-- IS NULL 查询customers表中电话号码为空的客户


SELECT *
FROM customers
ORDER BY first_name DESC ;
-- ORDER BY:排序 DESC:降序

SELECT * ,quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC;
-- 查询order_items表中order_id为2的数据，且将查出的数据根据总价进行降序排序

SELECT *
FROM customers
LIMIT 6,3 ;
-- LIMIT A,B :A偏移量，B读取数据量，这里为跳过前六条数据，然后获取三条记录，即7-9

第三章：连接

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3 ;
-- 查询积分最多的前三个客户信息

SELECT order_id,o.customer_id,first_name,last_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
-- INNER JOIN 表 on 条件 ：通过该条件将同一个数据库中的表与表进行连接
-- 由于orders表和customers表中都有customer_id列 所以这里需要指明是哪个表里的customer_id：o.customer_id
-- 否则会报该错误 Column 'customer_id' in field list is ambiguous
-- orders o ，customers c 给表起别名简化代码

SELECT *
FROM db_bills.billtype b
JOIN customers c
    ON b.id = c.customer_id ;
-- JOIN默认为INNER JOIN
-- 不同数据库之间表连接，只需要给不再当前数据库的表加数据库前缀：db_bills.billtype

SELECT
    oi.order_id,
    oi2.order_id,
    oi.product_id,
    oi2.unit_price
FROM order_items oi
JOIN order_items oi2 ON oi.order_id = oi2.order_id;
-- 自连接：同一张表进行连接，需要起不同别名

SELECT  *
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
JOIN customers c on c.customer_id = o.customer_id ;
-- 这两条sql相等，上面是显示连接，下面是隐式连接语法
SELECT *
FROM orders o ,customers c
WHERE o.customer_id = c.customer_id;

SELECT c.customer_id,c.first_name,o.order_id
FROM  customers c
LEFT OUTER JOIN orders o
    ON c.customer_id = o.customer_id ;
-- 外连接，LEFT/RIGHT OUTER JOIN 左外连接返回第一张表即customers表和查询条件所得到的结果，RIGHT返回第二张表和查询条件返回的结果，OUTER可以省略

USE `sql_hr` ;
SELECT e.employee_id,e.first_name,m.first_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id ;
-- 外自连接

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

SELECT c.customer_id,o.order_id,c.first_name,s.name AS shipper
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
LEFT JOIN shippers s
    ON o.shipper_id = s.shipper_id
ORDER BY  c.customer_id ;
-- 多表外连接

-- 该条sql与上面的sql功能完全相同
SELECT c.customer_id,o.order_id,c.first_name,s.name AS shipper
FROM customers c
LEFT JOIN orders o
    USING (customer_id)  # ON c.customer_id = o.customer_id
LEFT JOIN shippers s
    USING (shipper_id)   # ON o.shipper_id = s.shipper_id
ORDER BY  c.customer_id ;
-- USING 关键字只能用在不同表中列名完全一样的场景中，简化代码

SELECT *
FROM orders o
NATURAL JOIN customers c ;
-- 自然连接，让数据库引擎自己看着办，基于共同的列（有相同名称的列），不推荐使用

SELECT *
FROM customers c
CROSS JOIN  products p;
-- 交叉连接，笛卡尔积的形式，基本不会用到
SELECT *
FROM customers c,products p;
-- 交叉连接隐式语法

SELECT *,'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT *,'Archived'
FROM orders
WHERE order_date < '2019-01-01';
-- UNION合并多段sql查询记录，列名基于第一段sql，这里各个sql语句查询出的列的数量需要相同，否则会报如下错误
-- The used SELECT statements have a different number of columns

第四章：列属性

INSERT INTO customers
VALUES (DEFAULT,'Kevin','Bian','1999-09-09',DEFAULT,'Address','XI\'AN','SX',DEFAULT);
-- 插入一行数据，第一种写法，未指明对应的列名，所以在填值的时候要和表列的数量和列的顺序保持一致。
INSERT INTO customers(FIRST_NAME, LAST_NAME, BIRTH_DATE, ADDRESS, CITY, STATE)
VALUES ('Smith','John','1990-01-01','address','city','CA') ;
-- 这种插入方式我们可以调换插入列的顺序，只需要值和上面列的列顺序对应即可，还可以省略一些列，MySQL会自动添上默认值

INSERT INTO products
VALUES (DEFAULT,'test',66,3.36),
       (DEFAULT,'test',33,3.26),
       (DEFAULT,'test',11,3.16) ;
-- 插入多行数据

INSERT INTO orders(customer_id,order_date,status)
values (1,'2021-05-23',3);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,2,3.33),
       (LAST_INSERT_ID(),3,2,3.33);
-- SELECT LAST_INSERT_ID()
-- 多表插入，通过LAST_INSERT_ID来获取最后一次插入的id值，从而用该id值往对应的表中插入相应数据
-- 这里是往订单表中插入记录，通过LAST_INSERT_ID获取最后一次插入的id值，即我们插入的order_id主键值，通过该order_id值在对应的order_items表中添加该order_id的具体信息
-- orders表中有order_id，customer_id等等，每个order_id在order_items表中都有对应的具体信息：产品id，数量，和单价

CREATE TABLE order_archived AS
    SELECT *FROM orders;
-- 创建一张和orders具有相同数据的表，即表的复制，注：这样的copy出的表将不会具有原表的一些列的属性，如主键，自增等等。

use `store`;
INSERT INTO order_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01';
-- Truncate截断表（将数据删除），然后这里我们想往order_archived复制 orders表中order_date < '2019-01-01'的数据
-- 使用SELECT语句作为INSERT语句的子查询

USE `invoicing` ;
CREATE TABLE   invoices_archived AS
    SELECT invoice_id,number,name AS client ,invoice_total,invoice_date,due_date,payment_date,phone
    FROM invoices i
    JOIN clients c ON i.client_id = c.client_id
    WHERE payment_date IS NOT NULL ;
-- 创建一张表，表的数据来源来自invoice表和clients表，且创建出的表中不想要客户id而将其替换为客户姓名，并且只要支付完成的订单。

UPDATE invoices
SET payment_total=8.88 , payment_date='2021-05-23'
WHERE invoice_id = 1 ;
-- 更新一行数据

USE `store`;
UPDATE customers
SET points=points+50
WHERE birth_date <'1990-01-01';
-- 更新一堆符合条件的数据

use `invoicing`;
UPDATE invoices
SET payment_total=8.88 , payment_date='2021-05-23'
WHERE client_id = (SELECT client_id
                   FROM clients
                   WHERE name = 'Vinte') ;
-- 更新子查询，假设我们只知道客户名字，为了在invoices表中更新相应数据，我们首先要到clients表中查找这个客户名字对应的id，所以我们需要进行子查询
UPDATE invoices
SET payment_total=8.88 , payment_date='2021-05-23'
WHERE client_id IN (SELECT client_id
                   FROM clients
                   WHERE state IN ('CA','NY')) ;
-- 当需要更改子查询出来的多条记录时，就不能在使用=，而是要用IN关键字。

UPDATE orders
SET comments = 'gold'
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE points > 3000
    );
-- 练习，将顾客积分超过3000且下过订单的备注改为gold，查询出来符合条件的顾客有多位，所以使用IN关键字

DELETE FROM customers
WHERE customer_id = 13;
-- 删除customer_id = 13的客户

第五章：聚合函数

USE `sql_invoicing`;
SELECT
       MAX(invoice_total) AS highest,
       MIN(invoice_total) AS lowest,
       AVG(invoice_total) AS average,
       SUM(invoice_total) AS total,
       COUNT(invoice_total) AS numbers_of_invoices,
       COUNT(payment_date) AS count_of_payments,
       COUNT(DISTINCT  client_id) AS total_records,
       COUNT(*)  AS total_records
FROM invoices;
-- 聚合函数只运行非空值,要统计所有情况（包括空值），使用*号,DISTINCT去重

SELECT
       'First half of 2019' AS date_range,
       SUM(invoice_total) AS total_sales,
       SUM(payment_total) AS total_payments,
       SUM(invoice_total)-SUM(payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT
       'Second half of 2019',
       SUM(invoice_total),
       SUM(payment_total),
       SUM(invoice_total)-SUM(payment_total)
FROM invoices
WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT
       'Total',
       SUM(invoice_total),
       SUM(payment_total),
       SUM(invoice_total)-SUM(payment_total)
FROM invoices ;
-- 练习题

SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC;
-- 根据用户进行分组

SELECT
    date,pm.name AS payment_menthod,SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id
GROUP BY date ,payment_menthod
ORDER BY date;

SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 ;
-- HAVING字句，在分组之后筛选数据，而WHERE则是在分组之前筛选数据。
-- 但HAVING子句的条件中用到的列必须是SELECT中选择的的，否则报如下错误
-- Unknown column '列名' in 'having clause'

USE `sql_store`;
SELECT state,first_name,SUM(oi.quantity*oi.unit_price) AS total_cost
FROM customers c
JOIN orders o
    USING (customer_id)
JOIN order_items oi
    USING (order_id)
WHERE state = 'VA'
GROUP BY state
HAVING total_cost > 100 ;
-- 练习:从customers表中获取地处Virginia并且消费超过$100的客户

USE `sql_invoicing`;
SELECT name AS payment_method,SUM(amount) AS total
FROM payments p
JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id
GROUP BY name WITH ROLLUP;
-- ROLLUP 进行数据汇总，只汇总有意义的数据
-- 使用ROLLUP运算符时，不能在GROUP BY子句中使用列的别名,只在极个别情况下不能

第六章：编写复杂查询

USE `sql_hr`;
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE salary > (
    SELECT
        AVG(salary)
    FROM employees
    );
-- 复杂查询，从员工表中查询薪水超过平均薪水的员工

USE `sql_invoicing`;
SELECT client_id,name
FROM clients
WHERE client_id  NOT IN (
    SELECT DISTINCT client_id
    FROM invoices
    ) ;
--  查询没有发票的客户， IN运算符

USE `sql_store`;
-- 子查询VS连接查询
SELECT customer_id,first_name,last_name
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_id IN (
        SELECT order_id
        FROM order_items
        WHERE product_id = 3)
    );
SELECT DISTINCT c.customer_id,first_name,last_name
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE product_id = 3
ORDER BY customer_id ;
-- 查询点了lettuce（product_id=3）菜品的客户,使用嵌套查询和连表查询两种方式实现
-- 比较代码的易读性和书写难度综合考量使用那种

USE `sql_invoicing`;
SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
    );
SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
);
-- 查询大于三号客户账单的所有账单，ALL和MAX都可以实现上述问题
-- ALL 关键字，当子查询返回的值有多个时，就可以使用ALL关键字，
-- num > All(10,15,23):即num大于ALL中的各个数才能返回TRUE

SELECT  *
FROM clients
WHERE client_id = ANY (
    SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
);
-- ANY关键字，WHERE client_id IN()和WHERE client_id = ANY()等价

USE `sql_hr`;
SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees em
    WHERE e.office_id = em.office_id
    );
-- 相关子查询，数据越多，查询越费时间。
-- 选出薪水超过他所在部门平均薪水的员工,这里子查询只面向的是同一个部门的员工

USE `sql_invoicing`;
SELECT *
FROM invoices i1
WHERE invoice_total > (
    SELECT AVG(invoice_total)
    FROM invoices i2
    WHERE i1.client_id = i2.client_id
    );
-- 获取客户订单金额 大于该客户订单平均值的订单，（一个客户有多个订单，我们只需要那些多个订单中金额大于它订单平均金额的订单）

SELECT DISTINCT c.CLIENT_ID,name,address,city,state,phone
FROM clients c
JOIN invoices i
    USING (client_id);
-- ----------------
SELECT *
FROM clients
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM invoices
    );
-- ------------------
SELECT *
FROM clients
WHERE EXISTS(
    SELECT DISTINCT client_id
    FROM invoices
    WHERE clients.client_id = invoices.client_id
          );
-- EXIST关键词，当数据量巨大时使用EXIST可以提升性能，因为使用IN时子查询会返回结果集，但EXIST不会，注意：EXISTS子查询中需要进行表连接条件
-- 上述三种方法都可以实现获取下过订单的客户的客户

USE `sql_store`;
SELECT *
FROM products  p
WHERE NOT EXISTS(
    SELECT DISTINCT product_id
    FROM order_items
    WHERE p.product_id = order_items.product_id
    );
-- 查询没有被订购过的产品

USE `sql_invoicing`;
SELECT
       client_id,
       name,
       (SELECT SUM(invoice_total)
           FROM invoices
           WHERE c.client_id = client_id) AS total_sales,
       (SELECT AVG(invoice_total)
           FROM invoices) AS average,
       (SELECT total_sales - average) AS difference
FROM clients c ;
-- 在选择语句中进行子查询
-- 子查询不仅可以在WHERE子句中使用，还可以在选择子句以及FROM子句中使用。

SELECT *
FROM (
     SELECT
       client_id,
       name,
       (SELECT SUM(invoice_total)
           FROM invoices
           WHERE c.client_id = client_id) AS total_sales,
       (SELECT AVG(invoice_total)
           FROM invoices) AS average,
       (SELECT total_sales - average) AS difference
    FROM clients c
         ) AS sales_summary
WHERE total_sales IS NOT NULL;
-- 将上一条sql查询出来的表作为该sql语句FROM条件，但需要给上一个表起个别名，否则会报如下错误
-- Every derived table must have its own alias
-- 在查询语句的FROM子句中写子查询，会使我们的主查询变得复杂，所以仅限于一些简单的查询。好的解决方法是使用视图

第七章：函数

-- ===数值处理===
SELECT ROUND(5.73);
SELECT ROUND(5.7368,2);
-- 四舍五入
SELECT CEILING(5.2);
-- 上限函数，res:5
-- 返回大于或等于该数字的最小整数
SELECT FLOOR(5.7);
-- 下限函数，res:5
-- 返回小于或等于该数字的最大整数
SELECT ABS(-5.2);
-- 计算绝对值
SELECT RAND();
-- 生成0-1区间的随机浮点数

-- ===字符串处理===
SELECT LENGTH('Mysql');
-- res；5
SELECT UPPER('Mysql');
-- 转大写，res:MYSQL
SELECT LOWER('Mysql');
-- 转小写，res：mysql
SELECT TRIM('    Mysql     ');
-- 删除所有前导或者尾随空格，可以选择LTRIM或RTRIM;res:Mysql
SELECT LEFT('Mysql',2);
-- 获取该字符串左侧2个字符，res:My
SELECT RIGHT('Mysql',3);
-- 获取该字符串右侧3个字符，res:sql
SELECT SUBSTRING('Mysql',3,1);
-- 截取该字符串3开始的长度为1的字符，res:s
-- 第一个参数：起始位置，第二个参数：截取长度，注意：索引从1开始
SELECT LOCATE('Ys','Mysql');
-- 字符串Ys在Mysql中的位置，忽略大小写。res:2
SELECT REPLACE('Mysql','sql','');
-- 字符串替换，用空串替换Mysql中的sql，res:My
SELECT CONCAT('My','sql');
-- 字符串连接；res:Mysql

-- ===日期函数===
SELECT NOW(),CURDATE(),CURTIME();
-- 2021-05-26 21:04:41  ,  2021-05-26  ,  21:04:41
SELECT YEAR(NOW()),MONTH(NOW()),WEEK(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
-- 2021 , 5  , 21 , 21 ,  7  , 48
SELECT DAYNAME(NOW()),MONTHNAME(NOW()) ;
-- Wednesday  ,  May
SELECT EXTRACT(YEAR FROM NOW());
-- 从now函数返回的日期中提取年份  ，2021

-- ===格式化日期和时间===
SELECT DATE_FORMAT(NOW(),'%y'),DATE_FORMAT(NOW(),'%Y');
-- %y:返回两位数的年份，%Y返回完整年份; 21  ,  2021
SELECT DATE_FORMAT(NOW(),'%m_%Y'),DATE_FORMAT(NOW(),'%M %d %y'),TIME_FORMAT(NOW(),'%h:%i:%s'),DATE_FORMAT(NOW(),'%H:%i:%s');
-- 05_2021 ,  May 26 21  , 09:32:37  ,  21:32:37  ;%H:24小时,%h:12小时

-- ===计算时间和日期===
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY ),DATE_ADD(NOW(),INTERVAL 1 YEAR );
-- 2021-05-27 21:36:27 ,2022-05-27 21:36:27 ;在当前日期上加一天，加一年
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY ),DATE_ADD(NOW(),INTERVAL -1 DAY );
-- 2021-05-25 21:39:37，2021-05-25 21:39:37 ;当前日期减一天，上述语句具有相同效果
SELECT DATEDIFF('2021-05-25 21:39:37','2021-01-01'),DATEDIFF('2021-01-01','2021-05-25 21:39:37');
--   144 ,-144;计算两日期的间隔，不考虑时间间隔
SELECT TIME_TO_SEC('00:00:37');
-- 37;该时间距离00:00:00过去了37秒
SELECT TIME_TO_SEC(NOW())-TIME_TO_SEC('21:39:37');
-- 740，当前时间距离21:39:37过去了740秒

USE `sql_store`;
SELECT *,
       IFNULL(shipper_id,'没有承运人'),
       COALESCE(shipper_id,comments,'没有承运人')
FROM orders ;
-- 关键词IFNULL 和 COALESCE
-- IFNULL如果shipper_id中有空值则替换为“没有承运人”
-- COALESCE如果shipper_id列中有空值则先将其用comments替换，如果comments也为空，则使用“没有承运人”替换

SELECT order_id,
       customer_id,
       order_date,
        IF(YEAR(order_date) = '2019',
            '活跃的',
            '不活跃的') AS status
FROM orders ;
-- IF(条件，为真执行，为假执行)

SELECT   product_id,name,
                 COUNT(*) AS orders,
                 IF(COUNT(*)>1,
                     'Many times',
                     'Once') AS frequency
FROM order_items
JOIN products
    USING (product_id)
GROUP BY product_id;
-- 练习

SELECT
   order_id,
   customer_id,
   order_date,
       CASE
            WHEN YEAR(order_date) = 2019 THEN  '活跃'
            WHEN YEAR(order_date) = 2018 THEN '正常'
            WHEN YEAR(order_date) < 2018 THEN '消极'
            ELSE '未知'
        END AS status
FROM orders
-- 在有多个测试表达式且想要针对每个测试表达式返回不同值的时候使用CASE运算符；IF只能返回两种状态

第八章：视图

USE `sql_invoicing`;
-- ===创建视图===
CREATE VIEW  sales_by_client AS
SELECT
    client_id,
    name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id;
-- 未来很多查询可能多次用到该段查询来写，我们就不需要每次都重写这段查询语句并在每段查询语句上都做一点修改，而是这段查询保存为视图，以供很多地方使用。
-- 视图不存储数据，它是一张虚拟出来的表，如果基础表的内容做出了改表，由其创建的视图的内容也会改变

-- ===删除视图===
DROP VIEW IF EXISTS sales_with_balance;

-- ===创建或替换视图===
CREATE OR REPLACE VIEW  sales_by_client AS
SELECT
    client_id,
    name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id;
-- 如果视图不存在就创建，如果视图存在就替换掉

-- ===可更新视图===
-- 如果视图中没有DISTINCT、聚合函数、GROUP　BY、HAVING、UNION等，则我们的视图是可更新的。
CREATE OR REPLACE VIEW sales_with_balance AS
SELECT *,invoice_total - payment_total AS balance
FROM invoices;
DELETE FROM sales_with_balance
WHERE payment_total = 0;
-- 有时处于安全原因，我们没有权限修改基础表，所以我们想要修改数据的话只能通过视图，但前提是我们的视图是可更新的。

-- ===WITH CHECK OPTION子句===
CREATE OR REPLACE VIEW sales_with_balance AS
SELECT *,invoice_total - payment_total AS balance
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION ;
-- 有时不想让视图的数据通过更新或删除语句删掉的话，可以加上WITH CHECK OPTION
UPDATE sales_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 1;
-- 例如更新数据会使invoice_id=1的行消失，所以会报错，错误如下：
-- CHECK OPTION failed 'sql_invoicing.sales_with_balance'

第九章：存储过程

-- 存储过程是一个包含一堆SQL代码的数据库对象。
-- 优点：在我们的应用代码中，通过使用存储过程来存储和管理SQL代码，更快捷的执行，保证数据安全
DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
    SELECT *
    FROM sales_with_balance
    WHERE balance > 0;
END $$
DELIMITER ;
-- 更改默认分隔符是为了在存储过程中使用“;”分号
-- 因为sql语句结束默认的分隔符为“;”所以为了避免SQL语法错误，需要设置$$作为新的语句结束分隔符，最后在使用DELIMITER改回来

CALL get_invoices_with_balance();
-- 调用存储过程

DROP PROCEDURE IF EXISTS get_invoices_with_balance;
-- 删除存储过程

CREATE PROCEDURE get_invoices_by_client(client_id VARCHAR(10))
BEGIN
    SELECT *
    FROM invoices i
    WHERE client_id = i.client_id;
END;
-- 带参数的存储过程，通过传入client_id来返回该客户对应的发票
CALL get_invoices_by_client('1');

-- ======默认参数=========
DROP PROCEDURE IF EXISTS get_client_by_state;
CREATE PROCEDURE get_client_by_state
(
    state varchar(10)
)
BEGIN
    IF state IS NULL THEN
        SELECT * FROM clients;
    ELSE
        SELECT * FROM clients c
        WHERE c.state = state;
    END IF;
END;
CALL get_client_by_state('CA');
-- 如果输入空值则返回所有客户，否则返回对应州的客户

DROP PROCEDURE IF EXISTS get_clients_by_state;
DELIMITER $$
CREATE PROCEDURE get_clients_by_state
    (
        state varchar(10)
    )
BEGIN
    SELECT * FROM clients c
    WHERE c.state = IFNULL(state,c.state);
END $$
DELIMITER ;
-- 优化上一个存储过程，IFNULL(state,c.state)，如果state为空，则该函数返回第二个值。

CALL get_clients_by_state(NULL);

DROP PROCEDURE IF EXISTS get_payments;
DELIMITER $$
CREATE PROCEDURE get_payments
    (
        client_id INT,
        payment_method_id TINYINT
    )
BEGIN
    SELECT * FROM  payments p
    JOIN payment_methods pm ON p.payment_method = pm.payment_method_id
    WHERE
        p.client_id = IFNULL(client_id ,p.client_id) AND
        p.payment_method = IFNULL(payment_method_id,p.payment_method);
END $$
DELIMITER ;
-- 有两个参数client_id和payment_method_id如果两个都为空值则返回全部的付款，
-- 如果client_id不为空则返回对应客户的所有付款，如果client_id为空，payment_method_id不为空则返回该付款方式对应的所有付款
-- 如果两者都不为空，则返回该客户所用的付款方式对应的所有付款
CALL get_payments(5,NULL);

-- =====参数验证====
DROP PROCEDURE IF EXISTS make_payment;
CREATE PROCEDURE make_payment
    (
        invoice_id  INT,
        payment_total DECIMAL(9,2),
        payment_date  DATE
    )
BEGIN
    IF payment_total <= 0 THEN
        SIGNAL SQLSTATE '22003'
        SET MESSAGE_TEXT = '输入的金额无效';
    END IF;
    UPDATE invoices i
    SET i.payment_total = payment_total ,
        i.payment_date = payment_date
    WHERE i.invoice_id = invoice_id ;
END ;
-- DECIMAL(9,2):9是定点精度，2是小数位数。
-- SIGNAL SQLSTATE '22003' :使用SIGNAL 语句从存储的程序（例如存储过程，存储函数，  触发器或事件）向调用者返回错误或警告条件；如果输入的是负数则引发异常，并显示SQLSTATE '22003'的错误消息：Data truncation
-- SET MESSAGE_TEXT = '输入的金额无效';  设置错误描述信息
CALL make_payment(2,-100,'2019-01-01');
-- 报该错：Data truncation: 输入的金额无效

-- ====输出参数======
DROP PROCEDURE IF EXISTS get_unpaid_invoices_for_client;
CREATE PROCEDURE get_unpaid_invoices_for_client
    (
        client_id INT,
        OUT invoices_count INT,
        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;
-- 调用
set @invoices_count = 0;
set @invoices_total = 0;
call get_unpaid_invoices_for_client(3,@invoices_count,@invoices_total);
select @invoices_count,@invoices_total;
-- 只要建立会话，变量就一直存在
-- 不建议使用

-- =======本地变量=========
DROP PROCEDURE IF EXISTS get_risk_factor;
CREATE PROCEDURE get_risk_factor()
BEGIN
    DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9,2);
    DECLARE invoices_count INT;

    SELECT COUNT(*) ,SUM(invoices_total)
    INTO invoices_count,invoices_total
    FROM invoices;

    SET risk_factor = invoices_total / invoices_count * 5;

    SELECT risk_factor;
end;
--  DECLARE:声明变量
-- 本地变量是我们在存储过程或函数内定义的，这些变量只在在客户端会话过程中被保存，一旦存储过程完成执行任务，这些变量就会被清空
CALL get_risk_factor();

-- ======创建自己的函数======
-- 函数和存储过程较为相似，主要区别是函数只能返回单一值
DROP FUNCTION IF EXISTS get_risk_factor_for_client;
CREATE FUNCTION get_risk_factor_for_client
    (
        client_id INT
    )
RETURNS INTEGER  #明确函数返回值的类型。
# 设置函数的属性，每个MySQL至少要具有一个属性
READS SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9,2);
    DECLARE invoices_count INT;

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

    SET risk_factor = invoices_total / invoices_count * 5;

    RETURN  IFNULL(risk_factor,0); # 返回我们要的值
end;
-- 函数属性：
-- DETERMINISTIC （确定性）：如果我们给予这个函数同样的一组值，则它永远返回一样的值
-- READS SQL DATA （读取SQL数据）：函数中会配置选择语句，用以读取一些数据
-- MODIFIES SQL DATA (修改SQL数据) ：函数中有插入、更新或者删除函数

-- 调用函数，和MySQL内置函数一样
SELECT
       client_id,
       name,
       get_risk_factor_for_client(client_id) AS risk_factor
FROM clients ;
-- 小提示：可以加上proc前缀来命名存储过程，加上fn前缀来表示函数

第十章：触发器

DROP TRIGGER IF EXISTS payment_after_insert;
DELIMITER $$
CREATE TRIGGER payment_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 ;
-- 我们在付款表中插入数据后会触发触发器，触发器去执行我们配置的SQL代码
INSERT INTO payments
VALUES (default,3,11,CURDATE(),10,2);
-- 触发器是在插入、更新和删除语句前后自动执行的一堆SQL代码，来增强数据一致性。

DROP TRIGGER IF EXISTS payment_after_delete;
CREATE TRIGGER payment_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;
-- 练习
DELETE
FROM payments
WHERE payment_id = 10;

-- ====查看触发器======
SHOW TRIGGERS ;
-- 全部的触发器
SHOW TRIGGERS LIKE 'PAYMENTS%' ;
-- 查看返回名称中是payments开头的触发器


-- =====使用触发器进行审计======
DROP TABLE IF EXISTS payments_audit;
CREATE TABLE payments_audit
    (
        client_id INT,
        date DATE,
        amount DECIMAL(9,2),
        action_type varchar(50),
        action_date datetime
)engine = INNODB default charset = utf8;
-- 创建一个表用来记录被更改的数据

-- 在我们原来触发器的基础上加入记录数据变化的SQL
DROP TRIGGER IF EXISTS payment_after_insert;
DELIMITER $$
CREATE TRIGGER payment_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;

    INSERT INTO payments_audit
    VALUES (NEW.client_id,NEW.date,NEW.amount,'Insert',NOW());
end $$
DELIMITER ;

DROP TRIGGER IF EXISTS payment_after_delete;
CREATE TRIGGER payment_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;

    INSERT INTO payments_audit
    VALUES (OLD.client_id,OLD.date,OLD.amount,'Delete',NOW());
end;

-- =====事件======
SHOW VARIABLES LIKE 'EVENT%';  #查询MySql系统变量
SET GLOBAL EVENT_SCHEDULER = ON;  #开启事件调度器

DROP EVENT IF EXISTS yearly_delete_stale_audit_rows;
CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
-- 接下来提供事件执行的计划，你想多久执行这个任务，执行一次还是定期执行
--    AT '2020-06-01' # 只执行一次用AT，只在2020-06-01这天执行一次
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2025-01-01' #执行多次，在该日期内每年都执行一次
DO BEGIN
    DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR ;
end;
-- 将审计表中记录的数据超过一年的进行删除。

SHOW EVENTS;
-- 修改事件
ALTER EVENT yearly_delete_stale_audit_rows
-- DISABLE #关闭该事件
ENABLE ;#开启该事件

第十一章：事务

-- ACID
SHOW VARIABLES LIKE 'AUTOCOMMIT';
-- 要么都成功，要么都失败

USE `sql_store`;
-- ===创建事务===
START TRANSACTION ;

INSERT INTO orders(CUSTOMER_ID, ORDER_DATE, STATUS)
VALUES (2,CURDATE(),1);

INSERT INTO order_items
VALUES (LAST_INSERT_ID(),2,3.11);

COMMIT;
-- ROLLBACK ;
-- order_items列插入出错后，orders列插入也将失败

-- ===并发和锁定===
START TRANSACTION;
UPDATE customers
SET points = points + 10
WHERE customer_id = 1;
COMMIT;
-- 如果一个事务试图修改一行或多行，他将给这些行上锁，这个锁可以防止其他事务修改这些行，直到第一个事务完成，其他事务才能去操作这些行
-- 模拟并发，在两个不同的console（会话中）中创建事务分别对customer_id = 1的顾客进行更改，第一个事务开启并进行更改但不执行COMMIT，然后让第二个事务开启，接着执行更新操作，会看到执行超时，然后报如下错误
-- Lock wait timeout exceeded; try restarting transaction

-- ===并发问题===
-- ①丢失更新：当两个事务更新相同的数据并没有上锁时，较完提交的事务会覆盖较早事务所做的更改，一般情况下，Mysql数据库会自动给事务上锁。
-- ②脏读：一个事务读取了未被提交的数据。 为此我们要设置事务隔离级别，这样事务修改但未提交的数据不会被其他事务读取
-- ③不可重复读：在一次事务执行过程中读取某个数据两次，但得到了不同的结果。为此要增加事务隔离级别，确保数据更改对其他事务不可见
-- 注：只有增删改操作才会锁数据，读数据不会上锁。
-- ④幻读：我们的查询中缺失了数据，因为别的事务正在修改数据，但我们没有意识到事务的修改。为此需要我们的事务按序列化执行

-- ===事务隔离级别===
-- ①读未提交：不能解决任何并发问题
-- ②读已提交：可以解决脏读问题
-- ③可重复读：可以解决除幻读之外的其他并发问题，Mysql默认的事务隔离级别。
-- ④序列化：可以解决上述四种并发问题
-- 注：隔离级别越高，需要用到更多的锁和资源，会损害性能和可扩展性，但也意味着更少的并发问题。

SHOW VARIABLES LIKE 'tx_isolation';
-- REPEATABLE-READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 为下一个事务设置隔离级别为序列化

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 为该会话中的所有事务设置隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 为所有会话中的所有事务设置隔离级别

-- ===读未提交===
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT points
FROM customers
WHERE customer_id = 1;
-- 在我们更新customer_id = 1的客户积分points = 20但未提交的情况下，我们的查询语句会读到该客户的points为20，但表中此时还是原来的数据2273，出现了脏读
-- 最低事务隔离等级，在这一级别可能会遇到所有的并发问题

-- ===读已提交===
SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
START TRANSACTION ;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT ;
-- 解决了脏读问题，但是又出现不可重复读的问题

-- ===可重复读===
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
START TRANSACTION ;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT ;
-- 使我们读取的数据具有一致性，但还不能解决幻读问题

-- ===序列化===
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
START TRANSACTION ;
SELECT * FROM customers WHERE state = 'VA';
COMMIT;
-- 设置序列化隔离级别后，开启我们的事务，但是不执行查询，然后我们去执行另一个事务更新我们的数据，
-- 但是并不执行COMMIT，接着回来执行我们的查询，此时我们的查询将超时并报错，只有我们将我们更改数据事务进行提交后，才能执行查询。
-- [40001][1205] Lock wait timeout exceeded; try restarting transaction
-- 序列化解决了所有并发问题，因为所有的事务都是一个接一个按顺序执行的，但是会大大降低我们的效率。

-- ===死锁===
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customer_id = '1';
UPDATE orders SET status = 1 WHERE order_id = 5;
COMMIT;
-- 如果需要“修改”一条数据，首先数据库管理系统会在上面加锁，以保证在同一时间只有一个事务能进行修改操作。锁定(Locking)发生在当一个事务获得对某一资源的“锁”时，这时，其他的事务就不能更改这个资源了

-- 当我们开启第一个事务，并执行了更新customer_id = '1'的客户state='VA'后qu开启第二个个事务，然后执行更新order_id = 5的客户信息，此时一切安好，
-- 接着我们回来执行第一个事务更新order_id = 5的客户信息（此时由于数据库默认锁的存在，导致我们更新该语句会超时），接着我们去执行第二个事务更新customer_id = '1'的客户state='VA'，
-- 此时我们的数据库就陷入了死锁。报如下错误：
-- Deadlock found when trying to get lock; try restarting transaction

-- 模拟并发

-- ===并发和锁定===
USE `sql_store`;
START TRANSACTION;
UPDATE customers
SET points = points + 10
WHERE customer_id = 1;
COMMIT;

-- ===读未提交===
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customer_id = 1;
ROLLBACK;
COMMIT;

-- ===读已提交===
START TRANSACTION;
UPDATE customers
SET points = 30
WHERE customer_id = 1;
COMMIT;

-- ===可重复读===
START TRANSACTION;
UPDATE customers
SET points = 40
WHERE customer_id = 1;
COMMIT;

-- ===序列化===
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customer_id = 1;
COMMIT;

-- ===死锁===
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 5;
UPDATE customers SET state = 'VA' WHERE customer_id = '1';
COMMIT;

第十二章：数据类型

-- ===strings===
-- CHAR():存储固定长度的字符串。
-- VARCHAR() ：用以存储可变长度字符串。varchar最大长度65535（64KB）
-- MEDIUMTEXT :文本串，能存储约1600万个字符（16MB），一般用来存json文件或vsc文件
-- LONGTEXT ：长文本串，可存储4GB的文本数据 （4GB），一般用来存储多年来的日志文件
-- TINYTEXT ：微文本类型，可存储255个字符。
-- TEXT :文本类型，可存储65535个字符，和varchar一样（64KB）
-- 这些类型都支持国际字符
-- 注：一个英文字符占一个字节，而一个中文占3个字节，

-- ===Integers===
-- TINYINT :微整型  1B  [-128,127]
-- UNSIGNED TINYINT :无符号微整型  1B  [0，255]
-- SMALLINT :小整型 2B  [-32768,32767]
-- MEDIUMINT :中整型 3B  [-8M,8M-1]([-8388608,8388607])
-- INT :整型 4B [-2147483648,2147483647]
-- BIGINT:大整型 8B [-2^63,2^63-1]
-- 选择能够满足自己需求的最小的范围

-- ===定点和浮点===
-- DECIMAL(p,s) :小数型 存储定点数 p：精度(明确了最大位数，介于1-65) s:小数位数
-- DEC / NUMERIC / FIXED 这是DECIMAL的同义词
-- ------------------
-- FLOAT : 浮点型 4B
-- DOUBLE :双精度  8B

-- ===布尔型===
-- BOOL : TRUE FALSE
-- BOOLEAN :TRUE(1) FALSE(0)

-- ===枚举和集合==
-- ENUM(固定值)
-- enum可以在一列选取一个enum中设定的值
-- 不推荐使用，尽量通过建表来实现和ENUM()相似的功能
-- SET(……)
-- set使我们可以在一列存储多个set中设定的值

-- ===日期和时间===
-- DATE :存储一个没有时间成分的日期
-- TIME :存储一个时间值
-- DATETIME ：日期时间 8B
-- TIMESTAMP  ：时间戳 4B 可以存储2038年之前的日期
-- YEAR ：四位数的年份

-- ===BLOB类型===
-- 如图像、视频、PDF、word等文件，几乎囊括了所有二进制数据
-- TINYBLOB : 255B 最大存储255B的二进制数据
-- BLOB ：65KB
-- MEDIUMBLOB : 16MB
-- LONGBLOB : 4GB
-- 一般最好不要把文件存在数据库中，因为关系型数据库是为了处理结构化关系数据库设计的，而非二进制数据。
-- 此外会增加我们的数据库的大小，弱化数据库备份能力，还会出现性能问题，此外在数据库读取或存储图像还得写额外的代码

-- ===JSON类型===
-- 键值对：键是字符串类型，值为Object（任意）类型
-- MySQL数据库版本要在8.0以上才支持JSON格式

alter table products
	add properties json not null;

UPDATE products
SET properties = '
{
  "dimensions ": [1,2,3],
  "weight": 10,
  "manufacturer": { "name" : "sony" }
}'
WHERE product_id = 1;
-- 上下两种sql功能相同，下面这个sql使用了函数
UPDATE products
SET properties = JSON_OBJECT(
    'weight', 10,
    'dimensions' , JSON_ARRAY(1,2,3),
    'manufacturer', JSON_OBJECT ( 'name' , 'sony' )
)
WHERE product_id = 1;

SELECT product_id,JSON_EXTRACT(properties,'$.weight' )
FROM products
WHERE product_id = 1;
-- 上下两种写法功能一样，只不过上边的sql使用了官方的函数
SELECT product_id, properties->'$.weight'
FROM products
WHERE product_id = 1;

SELECT product_id, properties ->> '$.manufacturer.name'
FROM products
WHERE product_id = 1;
-- ->返回"sony"(带引号的)
-- ->>返回sony

-- JSON_SET()：重新设置属性
-- JSON_REMOVE():用以删除一个或多个属性

第十三章：数据库设计

-- 数据建模
-- 概念模型：实体关系图
-- 逻辑模型：抽象的数据模型，能清楚的显示我们的实体及关系架构。实体中属性的数据类型，实体间的关系（一对一、一对多、多对多）
-- 实体模型：比逻辑模型更进一步，逻辑模型中不需要管主键、外键问题是逻辑模型通过特定数据库技术的实现
-- 注：概念模型并不能为我们提供存储数据的结构，它只代表业务实体及其关系；逻辑模型增添了更多细节

-- 主键：唯一标识表中记录的列
-- 外键：在一张表中引用了另一张表的主键的列
-- 外键约束：当作为外键时，主键所在的表的信息发生变化作为外键的那个表的对应记录应该做出的变化：更新（级联，拒绝，置空），删除（不变、拒绝、级联）
-- 标准化：审查我们的设计，防止数据冗余或重复，有七条规则即七范式。

-- 第一范式：要求一行中的每一个单元格都应该有单一值，且不能出现重复值，为了满足第一范式，我们可能需要链接表。
-- 链接表：将多对多关系通过添加一张表变为两个一对多关系，一般情况下链接表只有多对多关系的两张表中的主键两列。
-- 第二范式：一张表中的每一列都应该是在描述该表代表的实体。如果有一列描述的不是该实体，我们应该去除它，并将它放入一张单独的表中。
-- 第三范式：表中的列不应派生自其他列，如结余是由订单总额减去付款总额得到的、first_name,last_name以及full_name

-- 先从逻辑或者概念模型入手，不要直接开始创建表，但也不要什么都建模，具体视业务需求，项目背景而定，一切从简

-- 创建数据库
CREATE DATABASE  IF NOT EXISTS sql_store2;
-- 创建数据表
USE sql_store2;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
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 -- UNIQUE:唯一
);
-- 声明为主键后可以省略NOT NULL，因为主键一定不为空
-- 修改表
ALTER TABLE customers
    ADD last_name varchar(50) NOT NULL AFTER first_name,
    MODIFY COLUMN first_name VARCHAR(55) DEFAULT '',
    DROP points;


CREATE TABLE 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
);
-- 设置外键约束 首先给外键取名：fk_orders_customers（取名规则：fk_+外键表名+主键列的名称），接着，在括号中，列出我们想要添加这个外键的列，
-- 然后告诉Mysql，这一列引用了顾客表中的customer_id列，下来我们要指定更新和删除行为，是级联它们还是拒绝它们等等。

创建索引

 EXPLAIN SELECT customer_id FROM customers WHERE state = 'VA';

 CREATE INDEX  idx_points ON customers (state) ;

查看索引

 ANALYZE TABLE customers;  -- 分析这张表
 
 SHOW INDEXES IN customers;  -- 查看这张表的所有索引

 -- ===前缀索引===
 CREATE INDEX idx_lastname ON customers(last_name(5))
 -- 根据last_name 的前五个字符来创建索引
 
 -- ===全文索引===
 use sql_blog;
 SELECT * FROM posts;
 
 CREATE FULLTEXT INDEX idx_title_body ON posts (title,body);
 
 SELECT *,MATCH(title,body) AGAINST('react redux') AS relevance
 FROM posts
 WHERE MATCH(title,body) AGAINST('react -redux + form' IN BOOLEAN MODE)
     OR MATCH(title,body) AGAINST('"what should I"');
 -- 使用MATCH()、AGAINST()这两个内置函数来支持全文索引
 -- MATCH()里必须要传入我们创建全文索引时的列，这里是title和body
 -- AGAINST()这里传入我们要搜索的内容，这样就可以返回所有标题或者正文中包含这两个关键字的文章，这些单词可以按任何顺序排列，也可以被一个或多个单词分割
 -- MATCH(title,body) AGAINST('react redux') AS relevance 会返回一个搜索的相关性得分，介于0-1之间的浮点数
 -- 全文搜索有两种模式，一种是自然语言模式，另一种是布尔模式（这个模式可以使用正则表达式来包括或排除某些单词）和搜索引擎一致

 1. insert into :插入数据，数据库会检查主键（PrimaryKey）h，如果出现重复会报错；
 
 2. replace into:插入替换数据，表中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
 3. insert ignore:如果已存在，就不会进行插入，跳过这个

 -- 对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
 
 insert IGNORE into actor
 VALUES(3,'ED','CHASE','2006-02-15 12:34"33');

 -- 对于表titles_test插入如下数据,如果主键已存在，则替换
 REPLACE INTO titles_test
  VALUES(5, 10005 ,'Senior Engineer','1986-06-26', '9999-01-01') ;

 INSERT INTO titles_test
     VALUES(5, 10001 ,'Senior Engineer', '1986-06-26', '9999-01-01')
     ON DUPLICATE KEY UPDATE emp_no = 10005;

 select t.date ,round(sum(
     case t.type
     when 'completed' then 0
     when 'no_completed' then 1
     end
       )/count(t.type),3)
 from (
     select date,type
     from email
     join user as u1 on (email.send_id = u1.id and u1.is_blacklist = 0)
     join user as u2 on (email.receive_id = u2.id and u2.is_blacklist = 0)
 )as t
 group by date
 order by date;