SQL数据库代码语句集锦

1. DESCRIBE shuju; 2. SELECT * FROM sales;
SELECT transaction_date, amount FROM sales; 3. SELECT * FROM sales LIMIT 5;
4. SELECT Product_ID FROM sales;
SELECT MIN(transaction_date), MAX(transaction_date) FROM sales; SELECT MAX(city) FROM member; 8. SELECT * FROM member WHERE city= 'shanghai';
9. SELECT * FROM product WHERE Price>100;
SELECT MAX(city) FROM member;
第 12 课 用 SQL 筛选数据
1. WHERE 语句 SELECT * FROM member WHERE city= 'shanghai'；
2. 操作符 SELECT * FROM product WHERE Price>100; SELECT * FROM member WHERE city>'shanghai';
3. IS NULL SELECT * FROM member WHERE city IS NOT NULL;
4. IN/BETWEEN/LIKE SELECT * FROM sales WHERE city IN ('wuhan','chendu','shanghai'); SELECT * FROM product WHERE Price BETWEEN 20 AND
SELECT * FROM member WHERE city>'shanghai';
10. SELECT * FROM member WHERE city IS NOT NULL;
11. SELECT * FROM sales WHERE city IN ('wuhan','chendu','shanghai'); SELECT * FROM product WHERE Price BETWEEN 20 AND 80;
2. LEFT/RIGHT JOIN 语句 找出购买产品和未购买产品的顾客年龄和性别分布 SELECT m.member_id, m.age, m.gender, s.member_id FROM member m LEFT JOIN sales s ON m.member_id=s.member_id;
SELECT COUNT(city),COUNT(DISTINCT city),COUNT(*)FROM member;
7. SELECT MIN(amount), MAX(amount), AVG(amount), SUM(amount), COUNT(amount) FROM sales;
SELECT MIN(age),MAX(age),AVG(age),SUM(age),COUNT(age) FROM member;
GROUP BY Base_Category,Category ORDER BY total DESC; 3. HAVING 语句与WHERE 语句比较 场景：找出销售金额大于1000的产品大类、小类并按照销售金 额总和从大到小排序 (1) WHERE 语句 SELECT p.Base_category,
4. CASE WHEN 语句 场景：找到发生过购买行为的会员 SELECT m.member_id, m.age, m.gender, CASE WHEN s.member_id IS NOT NULL THEN 'Y' ELSE 'N' END AS Flag FROM member m LEFT JOIN sales s ON m.member_id=s.member_id;
12. SELECT * FROM member WHERE `name` LIKE '张%' AND phone LIKE '%09';
SELECT * FROM member WHERE `name` LIKE ' 张 %' OR phone LIKE '%09';
13. SELECT LEFT (registration_date,10), RIGHT (registration_date,8)FROM member;
p.category, SUM(s.amount)AS total FROM sales s INNER JOIN product p ON s.product_id=p.Product_ID GROUP BY Base_Category,Category ORDER BY total; 场景：将销售的产品按照产品大类、小类汇总销售金额，并按 照销售金额总和从大到小排序 SELECT p.Base_category, p.category, SUM(s.amount)AS total FROM sales s INNER JOIN product p ON s.product_id=p.Product_ID
4. MIN/MAX/SUM/AVG 函数 SELECT MIN(amount), MAX(amount), AVG(amount), SUM(amount), COUNT(amount) FROM sales; SELECT MIN(age), MAX(age), AVG(age), SUM(age), COUNT(age) FROM member; SELECT MIN(transaction_date), MAX(transaction_date) FROM sales;
p.category, SUM(s.amount)AS total FROM sales s INNER JOIN product p ON s.product_id=p.Product_ID WHERE s.amount>1000 GROUP BY Base_Category,Category ORDER BY total DESC; （2）HAVING 语 句 SELECT p.Base_category, p.category, SUM(s.amount)AS total FROM sales s INNER JOIN product p ON s.product_id=p.Product_ID GROUP BY Base_Category,Category HAVING total>1000 ORDER BY total DESC;
SELECT city AS new_city FROM member;
SELECT Product_ID, Amount, Quant,Amount/Quant AS Price FROM sales;
SELECT * FROM sales WHERE transaction_date BETWEEN '2018-10-01' AND '2018-10-07';
SELECT * FROM member WHERE `name` LIKE '张%';
SELECT * FROM member WHERE phone LIKE '%09';
第 11 课 用 SQL 查询数据
1. DESCRIBE 语句 DESCRIBE shuju;
2. SELECT 语句 SELECT * FROM sales; SELECT transaction_date, amount FROM sales; SELECT * FROM sales LIMIT 5; SELECT Product_ID FROM sales; SELECT DISTINCT Product_ID FROM sales;
80; SELECT * FROM sales WHERE transaction_date BETWEEN '2018-10-01' AND '2018-10-07'; SELECT * FROM member WHERE `name` LIKE '张%'; SELECT * FROM member WHERE phone LIKE '%09';
3. COUNT 函数 SELECT COUNT(Member_ID) FROM sales; SELECT COUNT(DISTINCT Member_ID) FROM sales; SELECT COUNT(*)FROM sales; SELECT COUNT(city), COUNT(DISTINCT city), COUNT(*)FROM member;
5. AND/OR SELECT * FROM member WHERE `name` LIKE '张%' AND phone LIKE '%09'; SELECT * FROM member WHERE `name` LIKE ' 张 %' OR phone LIKE '%09';
6. 取出某个特定长度字符 SELECT LEFT (registration_date,10), RIGHT (registration_date,8)FROM member;
第 14 课 用 SQL 聚合数据
1. GRቤተ መጻሕፍቲ ባይዱUP BY 语句 场景：将销售的产品按照产品大类、小类汇总销售金额 SELECT p.Base_category,
p.category,
SUM(s.amount) FROM sales s INNER JOIN product p ON s.product_id=p.Product_ID GROUP BY Base_Category,Category; 2. ORDER BY 语句 场景：将销售的产品按照产品大类、小类汇总销售金额，并按 照销售金额总和从小到大排序 SELECT p.Base_category,
3. FULL OUTER JOIN 语句 分析用户注册城市和商品送货城市的关系 SELECT s.member_id, s.delivery_city, m.member_id, m.city FROM sales s FULL OUTER JOIN member m ON
s.member_id=m.member_id;
SELECT DISTINCT Product_ID FROM sales; 5. SELECT COUNT(Member_ID)FROM sales;
6. SELECT COUNT(DISTINCT Member_ID)FROM sales;
SELECT COUNT(*)FROM sales;
7. 变量命名/重命名 SELECT city AS new_city FROM member;
8. 水平方向（行方向）运算 SELECT Product_ID, Amount, Quant,Amount/Quant AS Price FROM sales;
第 13 课 用 SQL 拼接数据
1. INNER JOIN...ON 语句 找出不同产品大类、小类的销售情况 SELECT s.Product_ID, s.Amount, p.Base_category, p.category FROM sales s INNER JOIN product p ON s.Product_ID=p.Product_ID;