mosh SQL 视频课程
视频链接：【中英字幕】一口气看完！耗时100天打造SQL数据库零基础入门到精通全套教程_哔哩哔哩_bilibili
大神整理的视频中使用到的数据库：https://pan.baidu.com/s/1KMrI0NqCbWagKI_oK_Rndw?pwd=g5rp 提取码: g5rp
自己老是忘记简书markdown编辑器使用，大神链接一并带上，给自己提个醒：简书markdown编辑器实现代码显示和图表功能 - 简书 (jianshu.com)
学习&练习代码整理（持续更新）：
使用数据表之前检查是否“USE 数据库名”
USE sql_store;
--取别名
SELECT
first_name,
last_name,
points,
(points + 10 )* 100 AS 'discount factor'
FROM customers
-- WHERE customer_id =1
-- ORDER BY first_name
--展示独一无二的值
SELECT
DISTINCT state
FROM customers
-- 练习
USE sql_store;
SELECT
name ,
unit_price ,
unit_price*1.1 AS new_price
FROM products
USE sql_store;
SELECT
*
FROM customers
WHERE points >1000
-- ！=, <>不等于两种形式
SELECT
*
FROM customers
WHERE state != 'VA'
SELECT
*
FROM customers
WHERE birth_date >'1990-01-01'
SELECT
*
FROM customers
WHERE birth_date > '1990-01-01' OR
( points>1000 AND state = 'VA')
SELECT
*
FROM customers
WHERE NOT(birth_date > '1990-01-01' OR points>1000)
SELECT
*
FROM customers
WHERE birth_date <= '1990-01-01' and points <=1000
SELECT *
FROM order_items
WHERE order_id=6 and quantity*unit_price > 30
USE sql_store;
SELECT
*
FROM customers
WHERE state NOT IN ('VA','GA','CA')
SELECT
*
FROM products
WHERE quantity_in_stock IN (49,38,72)
SELECT
*
FROM customers
WHERE points BETWEEN 1000 AND 3000
SELECT
*
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'
USE sql_store;
SELECT
*
FROM customers
WHERE last_name LIKE 'brush%'
--%匹配任意数量的字符
SELECT
*
FROM customers
WHERE last_name LIKE '%b%'
--以b开头，空4个格，以y结尾
-- _表示一个字符
SELECT
*
FROM customers
WHERE last_name LIKE 'b____y'
SELECT
*
FROM customers
-- WHERE address LIKE '%TRAIL%' OR '%AVENUE%'
WHERE phone NOT LIKE '%9'
USE sql_store;
--正则表达式
-- ^123表示以123开头， 123$表示以123结尾
SELECT
*
FROM customers
WHERE last_name REGEXP 'field'
--包含field或者mac
SELECT
*
FROM customers
WHERE last_name REGEXP 'field|mac'
--练习
SELECT
*
FROM customers
-- WHERE first_name LIKE 'elka' or 'ambur'
-- WHERE last_name like '%EY' or '%ON'
-- WHERE last_name LIKE '^MY' or '%se%'
WHERE last_name REGEXP 'b[r|u]'
SELECT
*
FROM customers
WHERE last_name REGEXP 'field$|mac|rose'
--e之前有g i m的情况，ge ie me
SELECT
*
FROM customers
WHERE last_name REGEXP '[gim]e'
SELECT
*
FROM customers
WHERE last_name REGEXP '[a-h]e'
SELECT
*
FROM customers
-- WHERE first_name LIKE 'elka' or 'ambur'
-- WHERE last_name like '%EY' or '%ON'
-- WHERE last_name LIKE '^MY' or '%se%'
WHERE last_name REGEXP 'b[r|u]'
order by
USE sql_store;
-排序
SELECT
*
FROM customers
WHERE phone IS null
SELECT *
FROM customers
ORDER BY first_name DESC
SELECT *
FROM customers
ORDER BY state ,first_name
SELECT first_name,last_name, 10 as points
FROM customers
ORDER BY points ,first_name
SELECT first_name,last_name, 10 as points
FROM customers
ORDER BY 1,2
SELECT * ,quantity*unit_price AS total_price
FROM order_items
WHERE order_id =2
ORDER BY total_price DESC
--只显示6条记录
SELECT *
FROM customers
limit 6
--跳过前6条记录，显示3条；
-- 6为偏移量
SELECT *
FROM customers
limit 6,3
--LIMIT 写到最后
SELECT *
FROM customers
ORDER BY points DESC
limit 3
inner join
USE sql_store;
#表联接
SELECT order_id,orders.customer_id,first_name,last_name
FROM orders
JOIN customers on orders.customer_id = customers.customer_id
SELECT order_id,o.customer_id,first_name,last_name
FROM orders o
JOIN customers c
on o.customer_id = c.customer_id
SELECT order_id,o.product_id ,quantity,o.unit_price
FROM order_items o
JOIN products p
on o.product_id = p.product_id
-- sql_inventory.products数据库同样的products数据表
SELECT *
FROM order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id
--表自身联接
USE sql_hr;
SELECT *
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
-显示重要的几列
SELECT
e.first_name,
e.last_name,
m.first_name
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
SELECT
e.first_name,
e.last_name,
m.first_name AS manger
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
USE sql_store;
--多表连接，实现订单表与 客户表 与订单状态表
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
--练习
USE sql_invoicing;
SELECT
py.payment_id,
py.date,
py.amount,
c.name,
pm.name
FROM payments py
JOIN clients c
ON py.client_id = c.client_id
JOIN payment_methods pm
ON py.payment_method = pm.payment_method_id
USE sql_store;
--多连接条件
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id =oin.order_Id
AND oi.product_id = oin.product_id
--join on 等效 隐式联接，不建议
SELECT *
FROM orders o,customers c
WHERE o.customer_id = c.customer_id
