CDA Level 1 数据分析师：3 数据库的应用-part2

1. 数据库查询

1. Select 语句操作符

算数操作符：+  -   *  /
比较操作符：=	 > 	<	 <=  	>=   != 		<>
聚合类函数： AVG	SUM 	MAX	MIN 	COUNT

SELECT :
	SELECT 字段 FROM 数据源 WHERE 条件 GROUP BY 选择组条件  ORDER BY 排序要求

SELECT * FROM XXX;

-- 查询北京的数据
SELECT * FROM XXX WHERE city_name = '北京';

-- 查询不同月份PM2.5的最大值；GROUP BY 就是透视表的分组；max 是计算值；SELECT是需要显示的值
SELECT month_key, max(pm25) FROM XXX GROUP BY month_key;

-- 降序查询不同城市PM10的平均值
SELECT city_name, avg(pm10) FROM XXX GROUP BY city_name order by avg(pm10) desc;

-- 对大气质量表进行有选择的查询
SELECT city_name, avg(pm25), avg(pm10) from Monthly_Indicator where pm25>50
group by city_name, month_key having city_name <>'北京'
order by avg(pm25) desc;
# having 一定要GROUP BY 并用，having 和 where 是形同作用的

# 多表查询
-- join区分左右


-- 内连接：inner join 合并时只取交集，key有重复值时，会相乘的关系
SELECT 学员信息表.*， 学员成绩表.*,
FROM 学员信息表 inner join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号

-- 左连接：
SELECT 学员信息表.*， 学员成绩表.*,
FROM 学员信息表 left join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号

-- 右连接
SELECT 学员信息表.*， 学员成绩表.*,
FROM 学员信息表 right join 学员成绩表 on 学员信息表.学号 = 学员成绩表.学号

# 联合查询
-- 纵向合并
SELECT T1.* FROM T1
UNION
SELECT T2.* FROM T2
-- 使用UNION 如果有重复的行就会删除重复的内容
-- 使用UNION ALL 不会删除重复行

# 全连接,获得左右连接的所有内容
SELECT * FROM T1 LEFT JOIN ON key1 = key2
UNION
SELECT * FROM T1 RIGHT JOIN ON key1 = key2

2. 查询操作符与子查询

1. 操作符

1. AND:  SELECT * FROM fruits WHERE sid =101  AND fid ='a1'
2. OR :SELECT * FROM fruits WHERE fname ="apple" OR fname ="orange"
3. IN: SELECT * FROM fruits WHERE fname IN ("apple", "orange")
4. BETWEEN:SELECT * FROM fruits WHERE fprice BETWEEN 10 AND 20
包括10和20
5. LIKE: SELECT * FROM fruits WHERE fid LIKE "b_"
％的话不论多少个字符，_是个数限制
6. IS NULL: SELECT * FROM fruits WHERE fname IS NULL
7. DISTINCT: SELECT DISTINCT sid FROM fruit   查询所有不重复的sid

2. 子查询

类似于excel的函数嵌套
1. ANY
SELECT fid FROM fruits WHERE fid = ANY
(select fid FROM fruit WHERE fprice BETWEEN 10 AND 20)
2. ALL
SELECT  FROM fruits WHERE fprice > ALL
(select fprice from fruits where fprice <=20)
查询所有fprice 大于20元的水果记录
3. EXISTS
SELECT * FROM fruits WHERE EXISTS
(select * from fruits where fprice > 30)
当EXISTS后面子查询存在，则执行主查询
4. AS
SELECT f.* fruits as f
表名和字段名都可被代替，且AS可省略
5. LIMIT
SELECT * FROM fruits
ORDER BY fprice desc
LIMIT 3

3. 函数

1. 函数
ABS
BIN
EXP
RAND
LOG
MOD  取余
CELING
ROUND
SQRT
COUNCAT(s1,s2)
LEFT(str, x)    保留的数量
RIGHT()
LENGTH(str)字符长度
NOW()
DATE()
TIME()

2. 逻辑函数
    1. 空值函数  IFNULL
       SELECT ename, sal + ifnull(comm, 0) 实发工资 FROM emp
       工资=基本工资sal + 奖金    如果是空就写0
    2. IF(expr1, expr2, expr3)
    3. 逻辑表达式：
    SELECT ename, sal, 
    if(sal>=3000, "高", if(sal<=1500, "低", "中")) 工资级别 FROM emp
    该条命令会显示三列数据，ename, sal, 工资级别 ，其中工资级别是利用IF函数计算出来的
    SELECT ename, sal, 
    case when sal >= 3000 then "高"
    when sal <= 1500 then "低" 
    else "中" end 工资级别 FROM emp

3. 其他函数：开窗函数
    1. over()
        聚合函数是分组聚合，开窗函数聚合不分组, 会多一列       
        聚合函数最后结果是一个结果
        开窗函数每一行都有这个结果
        SELECT *, avg(sal) over() 平均工资 FROM emp
    2. partition by
        聚合函数：SELECT deptno, avg(sal) FROM emp group by deptno
        开窗函数：SELECT *,avg(sal) over (partition by deptno) 平均工资 FROM emp
4. E-R图 联系图
    流程一般是先创建表，再从本地导入数据，再进行查找