Mysql常用命令（2）查询

7、基本查询语句

SELECT 查询列表 
FROM 表
连接类型 JOIN 表2 
ON 连接条件 
WHERE 筛选条件
GROUP BY 分组字段 
HAVING 分组后筛选
ORDER BY 排序字段
LIMIT 起始索引位置, 获取记录数量;

8、函数（WHERE）

1）单行函数

LENGTH：数据长度
    SELECT LENGTH(first_name) FROM users; => 3
CONCAT：拼接字符串
    SELECT CONCAT(first_name,' ',last_name) FROM users; => 'daisy xiao'
UPPER：大写
    SELECT UPPER(first_name) FROM users; =>'DAISY'
LOWER：小写
    SELECT LOWER(first_name) FROM users;=>'daisy'
SUBSTR：截取子串，SUBSTR(列名,位置,长度) 
    SELECT SUBSTR('daisy',1,2);=>'da' # 位置从1开始计算
    SELECT SUBSTR('daisy',2);=>'aisy' # 如果不写长度，则一直截取到最末
SUBSTRING：同上
INSTR：获取子串在数据字串中第一次出现的起始位置，如果没有则返回0。INSTR(列名,子串)
    SELECT INSTR('daisy', 'd');=>1
    SELECT INSTR('daisy', '0');=>0
TRIM：去掉前后空格或字符（但是不包括当中的），如果要替换所有字符则使用replace
    SELECT TRIM('     daisy     ');=>'daisy'
    SELECT TRIM('a' FROM 'aaaDAISYaaa'); =>'DAISY'
LPAD：根据长度截取字符。如果长度不够则用指定字符实现左填充，LPAD(列名,长度,填充字符)
    SELECT LPAD('20180605',4,0)=>'2018';
    SELECT LPAD(1,4,0); =>'0001';
RPD：与上述类似
REPLACE：替换，REPLACE(列名,查找字符串,替换字符串)
    SELECT REPLACE('aabbaacc','a','d');=>'ddbbddcc'
ROUND：四舍五入，如果不写小数点后的位数，则取整。ROUND(列名,小数点后位数) 
    SELECT ROUND(1.567,2); => 1.57
CEIL：向上取整，只能取整
    SELECT CEIL(1.234);=>2
FLOOR：向下取整
    SELECT FLOOR(1.567);=>1
TRUNCATE：截断，不考虑四舍五入
    SELECT TRUNCATE(1.69999,1); => 1.6
MOD：取余，相当于%运算符
    SELECT MOD(10,3) => 1
    SELECT MOD(10,-3) => -1
NOW：当前系统日期+时间
    SELECT NOW(); =>2018-07-03 00:47:50
CURDATE：当前系统日期，不包含时间
    SELECT CURDATE();=>2018-07-03
CURTIME：当前系统时间，不包含日期
    SELECT CURTIME(); =>00:47:50
YEAR：获取时间的年份，
    SELECT YEAR(NOW());=>2018
MONTH：获取数字月份
    SELECT MONTH(NOW());=>7
MONTHNAME：获取英文月份
    SELECT MONTHNAME(NOW());=>July
STR_TO_DATE：将日期格式的字符串转成制定格式的日期
    SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y') => 1999-09-13
    SELECT SELECT * FROM users WHERE birthday = STR_TO_DATE('12-31 1986', '%c-%d %Y');
DATE_FORMAT：将日期转化成字符串
    SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日') =>2018年06月06日
    # Y四位年份，y两位年份，m两位月份，c去零月份，d两位日期，H二十四小时，h十二小时，i两位分钟，s两位秒

IF：if-else效果，类似三元运算
    SELECT first_name, IF(gender=True,'男','女')  AS cgender from users;
    SELECT id, IF(nickname IS NULL, '暂无', nickname ) from users;
IFNULL：第一个值不为空则返回第一个值，否则返回第二个值。可用来排除null对运算的影响。
    SELECT id, salary+IFNULL(allowance,0) FROM users;
CASE：共两种用法
    （1）switch-case效果，等值判断
    CASE 判断语句/值
        WHEN 常量1 THEN 值1
        WHEN 常量2 THEN 语句2; # 使用语句需要加分号
        ELSE 语句或值 # 默认情况
    END
    （2）类似多重if，if-elsif-end，判断区间
    CASE
        WHEN 条件1 THEN 值或语句1
        WHEN 条件2 THEN 值或语句2
        ELSE 值或语句
    END

2）分组函数（经常与GROUP BY一起使用）

SUM：求和
AVG：平均值
MAX：最大值
MIN：最小值
COUNT：计数
    SELECT COUNT(*) FROM users; # 统计总行数
    SELECT COUNT(nickname) FROM users; # 仅统计昵称非空的数量
    SELECT class_id, COUNT(*) FROM students GROUP BY class_id HAVING COUNT(*)>0;
    # 根据班级分组后统计每个班级的人数，HAVING为分组后的筛选条件，非必填
VAR：方差
FIRST：第一个
LAST：最后一个

9、去重、别名、数值运算

去重：SELECT DISTINCT 列名 FROM 表名;
   SELECT DISTINCT last_name FROM students;
   SELECT SUM(DISTINCT score) FROM students; # 去重后求和
   SELECT COUNT(DISTINCT score）FROM students; # 去重和计数
别名：AS 可以省略。别名中如果有空格，需要加上双引号。列、表以及查询结果都可以起别名。
   SELECT 列名 AS 别名 FROM 表名;
   SELECT 列名  别名 FROM 表名; 
运算：可以查询运算符的运算结果，需要注意，如果算式中有null值则结果为null 
   SELECT salary+allowance FROM users;

10、条件运算符、逻辑运算符、模糊查询

条件运算符：=、<>、>、<、>=、<=
逻辑运算符：&&、||、!、and、or、not
模糊查询：between..and、like、in、is null、is not null

like：需要与通配符（ %、_ ）配合使用。

SELECT * FROM users WHERE sname like '%a%';       # %表示任意多个字符，包括0个
SELECT * FROM users WHERE sname like '__e_a%';    # _表示单个字符

转译：将“_”写成“\_”，或自定义转译符：
SELECT * FROM users WHERE sname like '_$_%' ESCAPE '$';

<=>：安全等于

salary <=> null 即 salary is null
salary <=> 1200 即 salary = 1200

11、排序

SELECT 查询列表 FROM 表名 ORDER BY 列名 ASC/DESC
默认是ASC，一般放在查询语句最后，LIMIT之前。
支持列名、别名、函数运算、多字段排序。

12、分组查询

SELECT 分组列, 分组函数(列) FROM 表名 GROUP BY 分组列 HAVING 条件;
查询列表只能包括用于分组的列和分组函数
对分组后的数据进行条件约束，应使用HAVING而不是WHERE

# 查询人数大于2的部门id
SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id 
HAVING COUNT(*)>2;

13、分页

SELECT 查询列表 FROM 表 offset, size;
offset 起始索引（从0开始），size 显示条目个数
当offset为0时可以省略。

SELECT * FROM posts LIMIT 0,5;
SELECT * FROM posts LIMIT 5;
# 页数page，每页数目size
SELECT * FROM posts LIMIT (page-1)*size, size;

14、多表查询（外键设置）

SELECT 查询列表 
FROM 表1 别名1 
连接类型 JOIN 表2 别名2 
ON 连接条件 
WHERE 筛选条件

对于较长的表名，可以取别名，非必写。
查询列表中，列名如果出现重复，需要加上表名或表的别名。（如departments.name, employees.name）
1）内连接INNER JOIN
INNER JOIN只取两张表匹配上的数据，数据量最小

# 查询员工名称和上级名称
SELECT e.employee_id, e.first_name, m.employee_id, m.first_name 
FROM employees e 
INNER JOIN employees m 
ON e.manager_id = m.employee_id;

2）左外连接LEFT OUTER JOIN
LEFT OUTER JOIN取左表全部数据，将右表内容对应填充

# 查询没有员工的部门
SELECT d.*, e.employee_id 
FROM departments d 
LEFT OUTER JOIN employees e 
ON e.department_id = d.department_id 
WHERE e.employee_id is null;

3）交叉连接 CROSS JOIN
直接进行笛卡尔乘积，数量最多，总行数=左表的行数*右表的行数

15、子查询

出现在其他语句中的select语句，称为子查询或内查询
通常出现在WHERE或HAVING后，通常放在小括号内，通常放在条件的右侧
子查询执行顺序优先于主查询
1）标量子查询/单行子查询/一行一列
通常配合单行操作符使用，eg：>、<、=、<>、>=、<=等

# 查询比Abel工资高的员工
SELECT first_name 
FROM employees 
WHERE salary > (
    SELECT salary 
    FROM employees 
    WHERE first_name ='Abel'
);

非法使用的情况：子查询结果集不是一行一列，是多行或为空
2）列子查询/多行子查询/多行一列
列通常搭配多行操作符使用，eg：IN/NOT IN，ANY/SOME，ALL
IN：在结果集中；
NOT IN：不在结果集中，即<>ALL
ANY：salary>ANY(结果集)，大于任意一个，即>MIN
SOME：salary>SOME(结果集)，同上
ALL：salary>ALL(结果集)，大于全部，即>MAX

# 查询location_id是1400或1700的部门的员工姓名
SELECT first_name 
FROM employees 
WHERE department_id IN(
    SELECT DISTINCT department_id 
    FROM departments 
    WHERE location_id IN(1400,1700)
);

3）行子查询/一行多列/多行多列

# 查询员工编号最小且工资最高的员工信息
SELECT * FROM employees 
WHERE id = (
    SELECT MIN(id) FROM employees
) AND salary = (
    SELECT MAX(salary) FROM employees
);
# 因为两个条件都是等于条件，可以用行子查询。
SELECT * FROM employees 
WHERE(id, salary)=(
    SELECT MIN(id), MAX(salary) FROM employees
);

4）SELECT后的子查询

# 每个部门的员工个数
SELECT d.*, (
    SELECT COUNT(*) FROM employees e  
    WHERE e.department_id=d.department_id
) AS member_count FROM departments d;

5）FROM后的子查询
将子查询的结果集充当表格，必须起别名

# 查询每个部门的平均工资的工资等级
SELECT avg_dep.*, g.level
FROM(
    SELECT department_id, AVG(salary) avg 
    FROM departments 
    GROUP BY department_id
) avg_dep 
INNER JOIN job_grades g 
ON avg_dep.avg BETWEEN g.lowerst AND g.highest;

6）EXSITS后面的子查询（相关子查询）
EXSITS判断是否存在，即结果集是否有值，有为1，无为0

# 查询有员工的部门的名称
SELECT department_name FROM departments d 
WHERE EXISTS(
    SELECT * FROM employees e 
    WHERE d.department_id=e.department_id
)

16、联合查询 UNION

将多条查询的结果合并为一个结果

# 查询所有中国用户中年龄>12的信息，以及外国用户中年龄>12的信息（两张表）
SELECT id,cname,age FROM chinese WHERE age>12
UNION
SELECT t_id, tName,t_age FROM foreigners WHERE age>12

列数必须相同，列名是第一个的列名
多个查询结果中的每一列的类型和顺序应一致
UNION默认去重，不去重要写UNION ALL