SQL语法学习记录（一）

google搜索前排，就有还不错的在线练习平台，浅学了一下

包括常用的函数，子句和表连接，但是对复杂的连表查询和子查询涉及的较少，需要进一步学习

SQL语言 - 在线练习

笔记：

-- 1. SELECT
SELECT *
FROM labs_sqlexcustominfo 
WHERE party_id="020001234567891"

-- 2. WHERE and AND
SELECT name, zone_num, branch_num, mobile

FROM labs_sqlexcustominfo 
 
WHERE zone_num=0200 AND branch_num=1111


-- 3. WHERE and OR
SELECT name, zone_num, gender_cd, total_asset/10000 as assert

 FROM labs_sqlexcustominfo 
 
 WHERE zone_num='0200' OR zone_num='3000'


-- 4. AND and OR
SELECT name, zone_num, gender_cd, total_asset/10000 as asset

 FROM labs_sqlexcustominfo 
 
 WHERE total_asset >= 1000000 AND (zone_num='0200' OR zone_num='3000')

-- 5. case.. when...then...end
SELECT NAME, 
			CASE WHEN gender_cd='1' THEN '男'
					WHEN gender_cd='2'  THEN '女'
					ELSE '未知'
			END  AS gender
			
 FROM labs_sqlexcustominfo 
 
 WHERE zone_num='0200'

-- 6. other col using case
SELECT party_id, 
			name, 
			total_asset,
			CASE 
						WHEN total_asset<5000 THEN '普通客户'
						WHEN total_asset<50000  THEN '潜力客户'
						WHEN total_asset<200000  THEN '中端客户A'
						WHEN total_asset<1000000  THEN '中端客户B'
						WHEN total_asset<8000000  THEN '高端客户'
						ELSE '私人银行客户'
			END AS level

 FROM labs_sqlexcustominfo 


-- 7. sum
SELECT  SUM(total_asset)/10000 as sum_asset

 FROM labs_sqlexcustominfo 
 
 WHERE zone_num='0200'

-- 8. count
SELECT COUNT(*)  as number

 FROM labs_sqlexcustominfo 
 
 WHERE gender_cd=1 AND zone_num='0200'


-- 9. max, min, avg
SELECT  max(total_asset) as max_asset,
			min(total_asset)  as min_asset,
			avg(total_asset) as avg_asset

 FROM labs_sqlexcustominfo 
 
 WHERE zone_num='0200'

-- 10. strsub 
SELECT substr(name, 1, 1) as family_name

 FROM labs_sqlexcustominfo 
 
 WHERE zone_num='0200'

--  11. group by 1
SELECT zone_num, SUM(total_asset) as asset

 FROM labs_sqlexcustominfo 
 
 GROUP BY zone_num

--  12. case, sum, group by
SELECT CASE WHEN birth_dt<'1980-1-1' THEN '80前' ELSE '80后' END as 年龄段, 
			SUM(CASE WHEN gender_cd='1' THEN 1 ELSE 0 END) AS 男性数量, 
			SUM(CASE WHEN gender_cd='2' THEN 1 ELSE 0 END) AS 女性数量 
			FROM labs_sqlexcustominfo 
			GROUP BY 年龄段

-- 13. 多字段group 
SELECT zone_num, branch_num,
			count(*) as number,
			sum(total_asset),
			avg(total_asset),
			max(total_asset),
			min(total_asset)

 FROM labs_sqlexcustominfo 
 
 GROUP BY  zone_num, branch_num

-- 14. having子句
-- 必须放在group by 后面
SELECT zone_num, branch_num,
			count(*) as number,
			sum(total_asset),
			avg(total_asset),
			max(total_asset),
			min(total_asset)

 FROM labs_sqlexcustominfo 
 
 GROUP BY  zone_num, branch_num
 
 HAVING number>1

-- 15. order by
SELECT *

 FROM labs_sqlexcustominfo 
 
 WHERE zone_num='0200'
 
 ORDER BY total_asset

-- 16. order asc, desc
SELECT zone_num, branch_num, 
			sum(total_asset) as sum_asset

 FROM labs_sqlexcustominfo 
 
 GROUP BY zone_num, branch_num
 
 ORDER BY zone_num asc,
 				sum_asset desc

-- 17. inner join
SELECT a.cust_id, asset, loan, 
			(asset-loan) as net

 FROM labs_sqlexasset as a
 INNER JOIN labs_sqlexloan  as b
 on a.cust_id=b.cust_id 

-- 18. 不等值连接条件
SELECT a.cust_id, asset, loan

 FROM labs_sqlexasset as a
 INNER JOIN labs_sqlexloan  as b
 on a.cust_id=b.cust_id and asset>loan

-- 19. left join
-- order by 1  表示按照第一个字段排序
SELECT a.cust_id, asset, loan
FROM labs_sqlexasset as a
LEFT JOIN labs_sqlexloan as b
ON a.cust_id=b.cust_id
ORDER BY 1

-- 20. 多表left join 
-- coalesec
SELECT a.cust_id, 
			product, 
			asset, 
			coalesce(c.loan, 0)

 FROM labs_sqlexproduct as a
 LEFT JOIN labs_sqlexasset as b
 ON a.cust_id=b.cust_id
 LEFT JOIN labs_sqlexloan as c
 ON a.cust_id=c.cust_id
 
 WHERE product>=3
 
 ORDER BY product DESC

-- 21. union
SELECT cust_id

 FROM labs_sqlexasset as a
 
 UNION
 
 SELECT cust_id
 
 FROM labs_sqlexloan as b
 

-- 22. 嵌套子查询
SELECT a.party_id, 
			a.name, 
			b.asset_cnt,
			b.asset_bal
			
 FROM labs_sqlexcustominfo as a
 
 INNER JOIN (
   SELECT cust_id, count(*) as asset_cnt, 
   				sum(bal)  as asset_bal
   FROM labs_sqlexcustomasset
   WHERE asset_type not in ('三方存管', '保险')
   GROUP BY cust_id
   HAVING asset_cnt>1  
   )  as b
ON a.cust_id=b.cust_id

ORDER BY asset_bal DESC