MySQL数据库三大类型和高级查询

使用内联分组需要确定的条件，否则会产生笛卡尔乘积（1*N）。

分组过滤

语法：SELECT 列名 FROM 表名 WHERE 条件 group by 分租列 having 过滤规则；

注意：having对分组后的数据进行过滤

1. 分组查询中的筛选条件分为两类

分组前筛选          原始表           group by 子句的前面  where

分组后筛选         分组后的结果集    group by 子句的后面  having

①分组函数做条件肯定是放在having子句中

②能用分组前筛选的尽量使用分组前的

1. Group by 子句支持单字段分组，多字段分组（多字段之间用逗号隔开，没有顺序要求）表达式分组
2. 也可以添加排序（排序放在整个分组查询寻的最后，除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字节	长文本数据