数据库 极简概括 30分钟必会 （附上样例）

单表检索

基础语句

as 作为变名
可以省略 （加单引号）

select num,num * 1.1 AS new_num from products;
select num,num * 1.1 ‘new_num’ from products;

where （and or） 条件筛选
订单6中总价大于30的商品

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

in运算符
选出’va’、‘fl’、'ga’三个州的顾客

select *from customers where in state (‘va’,‘fl’,‘ga’);

between
选出积分在1k到3k的顾客

select * from customers between 1000 to 3000;

LIKE 模糊查找
两种通配符：

% 任何个数（包括0个）的字符（用的更多）
_ 单个字符
地址包含 ‘TRAIL’ 或 ‘AVENUE’

select * from customers where address LIKE ‘%TRAIL%’ or address LIKE ‘%AVENUE%’;

order by

acs 升序 可省略
decs 降序

订单2的商品按总价降序排列:

select *, unit_price * quantity as total_price from order_items where oreder_id = 2 order by total_price decs;

LIMIT
限制返回结果的记录数量，“前N个” 或 “跳过M个后的前N个”

找出积分排名前三的死忠粉

select * from customers order by points decs limit 3;

多表检索

内连接

from 表A join 表B on AB的关系

别名 as可省略
通过 product_id 链接 orders_items 和 products:

select * from orders_items oi join products p on
oi.product_id = p.product_id;

跨数据库合并
加上数据库前缀即可 其他都一样

自连接
一个表和自己合并
员工的上级也是员工，所以也在员工表里，

select e.employee_id,
e.first_name,
m.first_name as manager
from employees e join employers m on e.reports_to = m.employee_id

改名为 manager 使得结果表更易于理解

多表连接

FROM A
JOIN B ON AB的关系
JOIN C ON AC的关系
JOIN D ON AD的关系
……

订单表同时链接顾客表和订单状态表，合并为有顾客和状态信息的详细订单表

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.product_id p.name oi. quantity from products p left join order_items oi on p.product_id = oi.product_id;

结果表中除了包括满足连接条件的行外，还包括左表的所有行。
右外连接 可以举一反三。。。

多表外连接 可以举一反三

插入、更新和删除数据

插入单行
INSERT INTO 目标表 （目标列，可选，逗号隔开）
VALUES (目标值，逗号隔开)

INSERT INTO customers – 目标表
VALUES (
DEFAULT,
‘Michael’,
‘Jackson’,
‘1958-08-29’, – DEFAULT/NULL/‘1958-08-29’
DEFAULT,
‘5225 Figueroa Mountain Rd’,
‘Los Olivos’,
‘CA’,
DEFAULT
);

插入多条产品信息

insert into products values(DEFAULT, ‘product1’, 1, 10),
(DEFAULT, ‘product2’, 2, 20),
(DEFAULT, ‘product3’, 3, 30);

DROP TABLE 要删的表名、CREATE TABLE 新表名 AS 子查询

TRUCATE ‘要清空的表名’、INSERT INTO 表名 子查询

INSERT INTO orders_archived
– 不用指明列名，会直接用子查询表里的列名
SELECT * FROM orders
– 子查询，替代原先插入语句中VALUES(……,……),(……,……),…… 的部分
WHERE order_date < ‘2019-01-01’

UPDATE 表
SET 要修改的字段 = 具体值/NULL/DEFAULT/列间数学表达式 （修改多个字段用逗号分隔）
WHERE 行筛选

USE sql_invoicing;

UPDATE invoices SET
payment_total = 100 / 0 / DEFAULT / NULL / 0.5 * invoice_total,
/注意 0.5 * invoice_total 的结果小数部分会被舍弃，
之后讲数据类型会讲到这个问题/
payment_date = ‘2019-01-01’ / DEFAULT / NULL / due_date WHERE invoice_id = 3

让所有非90后顾客的积分增加50点

update customers set points = points + 50 where birth_date < '1990-01-01

汇总数据

GROUP BY
分组操作中的having子语句，是用于在分组后对数据进行过滤的

having与where的区别: having是在分组操作执行后, 对分组后的数据进行过滤. where是在分组操作执行前, 对分组前的数据 只能使用表原始列进行条件过滤
having后面可以使用 聚合函数
where后面不可以使用 聚合函数。

查询每个供应商提供的产品数量，并且产品数量大于2的供应商id SELECT vend_id, COUNT() FROM products GROUP BY vend_id HAVING COUNT() > 2;

子查询
关键：由子查询得到平均工资

SELECT * FROM employees WHERE salary > (
SELECT AVG(salary)
FROM employees )