mysql

创建数据库

卸载了mysql@5.7

安装了mysql@8.0

理解一下创建数据库，以及表与表之间的关联。

选择语句 select

算术

AS别名

唯一列表 DISTINCT

where使用

=

AND、OR、NOT

比较运算符

算术表达式

IN

BETWEEN

LIKE

LIKE '%y%'：匹配任何字符

_ 单个字符

REGEXP

• ^ 开始
• $ 结束
• | 逻辑或
• [abcd] 包含abcd
• [a-h] 包含a-h的字母

NULL

ORDER BY

列排序

LIMIT

偏移量 + 最大行数

连接 JOIN

JOIN

跨数据库连接

当前数据库和想使用的表不同库时使用数据库名.表名

SELF JOIN

自连接，谁是谁的经理。

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AD manager_name
         FROM employee e
         Join employee m
         ON e.reports_to = m.employee_id;

多表链接

SELECT 
       o.order_id,
       o.order_date,
       c.first_name,
       c.last_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 
       p.date,
       p.invoice_id,
       p.amount,
       c.name,
       pm.name AS method_name
  FROM payments p
  JOIN clients c
    ON p.client_id = c.client_id
  JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id;

复合链接条件

复合主键

SELECT *
  FROM order_items oi
  JOIN order_items_notes oit
   ON oi.product_id = oit.product_id AND oi.order_id = oit.order_id;

隐式链接

JOIN 写成 表,表 WHERE 条件

容易忘记写条件，形成笛卡尔积

外连接

-- 内连接，只能看到有order_id的
SELECT 
       c.customer_id,
       c.first_name,
       o.order_id
  FROM customers c
  JOIN orders o
    ON c.customer_id = o.customer_id
  ORDER BY c.customer_id;

当你的用户没有订单id，但是你也想看到用户的信息。你可以使用外连接。

-- 外连接，能看见没有order_id的
-- LEFT JOIN 留下左表的数据
-- 或者 RIGHT JOIN 具体要看两张表的位置
SELECT 
       c.customer_id,
       c.first_name,
       o.order_id
  FROM customers c
  LEFT JOIN orders o
    ON c.customer_id = o.customer_id
  ORDER BY c.customer_id;

不写就是内连接，写了就是左右连接，可能出现相同的结果。

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

多表外连接

SELECT 
       c.customer_id,
       c.first_name, 
       o.order_id,
       sh.name AS shipper,
  FROM customers c
  LEFT JOIN orders o
    ON c.customer_id = o.customer_id
  LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
  ORDER BY c.customer_id;

尽量不用右连接。

先JOIN，看看是否按照我们所想展示出数据。

自外连接

SELECT 
    e.employee_id,
    e.first_name,
    m.first_name AD manager_name
         FROM employee e
         LEFT Join employee m
         ON e.reports_to = m.employee_id;

USING子句

必须两列的名字都一样的。

SELECT 
       o.order_id,
       c.first_name,
       sh.name AS shipper
  FROM orders p
  JOIN customers c
    USING (customer_id)
  LEFT JOIN shippers sj
    USING (shipper_id)

SELECT *
  FROM order_items oi
  JOIN order_item_notes oin
    USING (product_id,order_id)

USE sql_invoicing;

SELECT 
       p.date,
       c.name AS client,
       p.amount,
       pm.name AS payment_method
  FROM payments p
   JOIN clients c
    USING (client_id)
   JOIN payment_methods pm
    ON p.payment_method = pm.payment_method_id;

自然连接

不推荐～

交叉连接

组合～

SELECT 
       sh.name AS shipper,
       p.name AS product,
  FROM shippers sh
    CROSS JOIN products p
    ORDER BY sh.name

联合

SELECT first_name 
  FROM archived_orders
UNION
SELECT name
  FROM shippers

列需要相同数量。

SELECT 
       c.customer_id,
       c.first_name,
       c.points,
       'Bronze' AS type
  FROM customers c
  WHERE c.points <= 2000
UNION
SELECT 
       c.customer_id,
       c.first_name,
       c.points,
       'Sliver' AS type
  FROM customers c
  WHERE c.points < 2000 AND c.points <= 3000
UNION
SELECT 
       c.customer_id,
       c.first_name,
       c.points,
       'Gold' AS type
  FROM customers c
  WHERE c.points > 3000 AND c.points < 3000
ORDER BY first_name;

列属性

Datatype:varchar和char的区别

PK:primary key

NN:not null

AI:auto increment

Default:默认值

INSERT

基础语句

-- 列和值一一对应
INSERT INTO `表名` ([列集合]) VALUES ([值集合]);

插入多行

INSERT INTO `表名` ([列集合]) VALUES ([值集合]), ([值集合]), ...;

插入分层行

LAST_INSERT_ID

创建表复制

CREATE TABLE order_archived AS 
SELECT * FROM orders;

CREATE TABLE invoices_archived AS
SELECT 
  i.invoice_id,
  c.name,
  i.invoice_date
  FROM invoices i
  JOIN clients c
   ON i.client_id = c.client_id
  WHERE i.payment_date IS NOT NULL;

更新单行

UPDATE `表名` SET ([列名 = 值]) WHERE `条件`；

更新多行

UPDATE customers
SET points = points + 50
WHERE birth_date < '1990-01-01';

在Updates中用子查询

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

删除行

DELETE FROM invoices WHERE invoice_id = 1;

恢复数据库

-- 重新执行命令～

聚合函数

-- 最大值 最小值 平均值 总和 只运行非空值
SELECT MAX(invoice_total) AS highest,
       MIN(invoice_total) AS lowest,
       AVG(invoice_total) AS average,
       SUM(invoice_total * 1.1) AS total_of_invoices，
       COUNT(payment_date) AS count_of_payments,
       COUNT(*) AS total_records
  FROM invoices;

-- DISTINCT 保证不重复

GROUP BY子句

-- 关键字有一定的顺序
SELECT 
  client_id,
  SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
WHERE invoice_date >= '2019-07-01'
-- GROUP BY client_id
GROUP BY state, city
ORDER BY total_sales DESC;

HAVING子句

在分组之后筛选数据

ROLLUP运算符

汇聚总和

编写复杂查询 子查询

子查询

子查询的条件 为在表中查询

IN运算符

NOT IN 不在订单列表里面

子查询vs连接

可读性和查询的摩擦

ALL关键字

ALL和MAX可以相互切换

ANY关键字

大于括号的最小值

相关子查询

没懂

EXISTS运算符

并不会把真正的结果集返回，而是返回是否有没有。

EXISTS 操作符用来判断一个子查询是否返回数据行。

SELECT 子句的子查询

套娃

FROM 子句的子查询

套娃

事务

多次数据库修改为一个原子，要么成功要么失败

原子性、一致性、隔离性、持久性。

创建事务

开启事务，提交事务。

没有提交的事物，会阻塞其他sql的执行。

并发和锁定

表锁/行锁

事务的隔离级别

lost updates

脏读

一个事务读取了尚未被提交的数据。

解决方案：读已提交。

不可重复读 不一致读

读取了两次数据，但是两次数据不一样。

首次读取的是快照。

解决方案：可重复读。

幻读

突然出现的数据没有被查询到。

解决方案，序列化。

读未提交隔离级别

读已提交隔离级别

可重复读隔离级别

序列化隔离级别