单表检索
基础语句
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 )