使用内联分组需要确定的条件,否则会产生笛卡尔乘积(1*N)。
分组过滤
语法:SELECT 列名 FROM 表名 WHERE 条件 group by 分租列 having 过滤规则;
注意:having对分组后的数据进行过滤
- 分组查询中的筛选条件分为两类
分组前筛选 原始表 group by 子句的前面 where
分组后筛选 分组后的结果集 group by 子句的后面 having
①分组函数做条件肯定是放在having子句中
②能用分组前筛选的尽量使用分组前的
- Group by 子句支持单字段分组,多字段分组(多字段之间用逗号隔开,没有顺序要求)表达式分组
- 也可以添加排序(排序放在整个分组查询寻的最后,除limit之外)
限定查询
关键字:LIMIT offset_start,row_count (限定查询结果的起始行和总数)
语法:select 列名 from 表名 LIMIT 起始行,查询行数;
特点:具有性能优化的特点
Eg. Select * from 表名 LIMIT 0,5;--从第一行开始查询5行
子查询
作为条件判断
语法:select 列名 from表名 where条件 (子查询结果);
Eg. 查询工资大于XX员工的信息
先查询到XX的工资
select 工资字段 from 表名 where XX_name = ‘ XX‘;
查询工资大于XX的员工信息
Select * from 表 where 工资字段 > 6000
整合:
Select * from 表 where 工资字段 > ( select 工资字段 from 表名 where XX_name = ‘ XX‘))
作为枚举查询条件
语法:select 列名 from where 列名 in(子查询结果)
Eg. 查询与名为'King'同一部门的员工信息
#思路:
#1. 先查询 'King' 所在的部门编号(多行单列)
SELECT department_id
FROM t_employees
WHERE last_name = 'King'; //部门编号:80、90
#2. 再查询80、90号部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (80,90);
#3.SQL:合并
SELECT employee_id , first_name , salary , department_id FROM t_employees WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = 'King'); #N行一列
当子查询结果集形式为多行单列是可以使用any 或 all 关键字。
All(高于最大值),any(高于最小值)
Eg. 工资高于60部门所有人的信息
#1.查询 60 部门所有人的工资(多行多列)
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;
#2.查询高于 60 部门所有人的工资的员工信息(高于所有)
select * from t_employees where SALARY > ALL(select SALARY from t_employees WHERE DEPARTMENT_ID=60);(不推荐使用all)
#。查询高于 60 部门的工资的员工信息(高于部分)
select * from t_employees where SALARY > ANY(select SALARY from t_employees WHERE DEPARTMENT_ID=60);(不推荐使用any)
推荐使用:
作为一张临时表
Select 列名 from (子查询结果集) as 别名 where 条件;
注意:子查询作为临时表,需要为其赋予一个临时表名
Eg. 查询员工表中工资排名前 5 名的员工信息
#思路:
#1. 先对所有员工的薪资进行排序(排序后的临时表)
select employee_id , first_name , salary
from t_employees
order by salary desc
#2. 再查询临时表中前5行员工信息
select employee_id , first_name , salary
from (临时表)
limit 0,5;
#SQL:合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary desc) as temp
limit 0,5;
合并查询
语法:select * from 表名1 UNION select * from 表名2;
合并两张表懂结果,去除重复记录
注意:合并结果的两张表,列数的数据类型可以不同。
保留重复数据:union all
连接查询
语法:select 列名 from 表1 连接方式 表2 on 连接条件;
内连接查询(INNER JOIN)
Eg. 查询所有有部门的员工信息(不包括没有部门的员工) SQL 标准
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID
查询所有有部门的员工信息(不包括没有部门的员工) MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID
注意:在使用的时候,有一个隐患就是笛卡尔积(1*n的数据),所以在使用时必须要保证有明确的连接条件
左外连接查询:
主表为左表,右表为副表,要保证大表在前小表在后的连接原则
注意:大表数据无视(on),小表数据会被on所牵制,查询不到null填充
查询所有员工信息,以及所对应的部门名称(没有部门的员工,也在查询结果中,部门名称以NULL 填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
LEFT JOIN t_departments d
ON e.department_id = d.department_id;
右外连接于左外连接查询相反
时间查询
时间函数 | 描述 |
SYSDATE() | 当前系统时间(日、月、年、时、分、秒) |
CURDATE() | 获取当前日期 |
CURTIME() | 获取当前时间 |
WEEK(DATE) | 获取指定日期为一年中的第几周 |
YEAR(DATE) | 获取指定日期的年份 |
HOUR(TIME) | 获取指定时间的小时值 |
MINUTE(TIME) | 获取时间的分钟值 |
DATEDIFF(DATE1,DATE2) | 获取DATE1 和 DATE2 之间相隔的天数 |
ADDDATE(DATE,N) | 计算DATE 加上 N 天后的日期 |
#查询当前时间
SELECT SYSDATE();
#查询当前时间
SELECT NOW();
#获取当前日期
SELECT CURDATE();
#获取当前时间
SELECT CURTIME();
数据库的类型
MySQL支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。
数值
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
INT | 4 字节 | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
DOUBLE | 8 字节 | (-1.797E+308,-2.22E-308) | (0,2.22E-308,1.797E+308) | 双精度浮点数值 |
DOUBLE(M,D) | 8个字节,M表示长度,D表示小数位数 | 同上,受M和D的约束 DOUBLE(5,2) -999.99-999.99 | 同上,受M和D的约束 | 双精度浮点数值 |
DECIMAL(M,D) | DECIMAL(M,D) | 依赖于M和D的值,M最大值为65 | 依赖于M和D的值,M最大值为65 | 小数值 |
日期类型
类型 | 大小 | 范围 | 格式 | 用途 |
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
字符串类型
类型 | 大小 | 用途 |
CHAR | 0-255字符 | 定长字符串 char(10) 10个字符 |
VARCHAR | 0-65535 字节 | 变长字符串 varchar(10) 10个字符 |
BLOB(binary large object) | 0-65535字节 | 二进制形式的长文本数据 |
TEXT | 0-65535字节 | 长文本数据 |