MySQL -DQL数据查询语言

最新推荐文章于 2022-11-24 10:31:10 发布

gougege0514

最新推荐文章于 2022-11-24 10:31:10 发布

阅读量1.1k

点赞数 1

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_46074155/article/details/115862503

版权

一、基础查询

语法:
select 查询列表 from 表名;
特点:
1、查询列表可以是:表中的字段、常量值、表达式、函数
2、查询的结果是一个虛拟的表格

#进入数据库

use myemployees;

1.查询表中的单个字段

SELECT   last_name  FROM  employees;

2.查询表中的多个字段

SELECT 
    last_name, salary, email
FROM
    employees;

3.查询表中的所有字段

SELECT * FROM employees;

4.查询常量值

SELECT 100;
SELECT 'john';

5.查询表达式

SELECT 100%98;

6.查询函数

SELECT VERSION();

7.起别名

/*
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来
*/
#方式一:使用as

SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二:使用空格

SELECT last_name 姓,first_name 名 FROM employees;

#案例:查询salary, 显示结果为out put

SELECT salary AS "out put" FROM employees;

8.去重 distinct

#案例:查询员工表中涉及到的所有的部门编号

SELECT DISTINCT department_id FROM employees;

9.+号的作用

java中的+号:
①运算符，两个操作数都为数值型
②连接符，只要有一个操作数为字符串

mysql中的+号:
仅仅只有一个功能:运算符
select 100+90; 两个操作数都为效值型，则做加法运算
select ‘123’+90;其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算
select ‘john’+90; 如果转换失败，则将字符型数值转换成0
select nu11+10; 只要其中一方为null，则结果肯定为null

#案例:查询员工名和姓连接成一个字段，并显示为姓名

SELECT CONCAT('a','b','c') AS 结果;
SELECT
concat(last_name,first_name) AS 姓名
FROM
employees;

10.Test测试

#1.显示表departments的结构，并查询其中的全部数据

DESC departments;
SELECT * FROM departments;

#2.显示出表employees中的全部job_id (不能重复)

SELECT DISTINCT job_id FROM employees;

#3.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT

```sql
SELECT
ifnull(commission_pct,0) AS 奖金率,
commission_pct
FROM
employees;

SELECT 
    CONCAT(employee_id,
            ',',
            first_name,
            ',',
            last_name,
            ',' ,
            email,
            ',',
            phone_number,
            ',',
            job_id,
            ',',
            salary,
            ',',
            IFNULL(commission_pct,0),
            ',',
            manager_id,
            ',',
            department_id,
       
    		',',
            hiredate) AS OUT_PUT


FROM
    employees;

二、条件查询

语法:
select 查询列表
from 表名
where 筛选条件;
分类:
一、按条件表达式筛选
条件运算符:> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符:
&& || !
and or not
三、模糊查询
like
between and
in
is null

一、按条件表达式筛选

#案例1:查询工资>12000的员工信息

SELECT 
    *
FROM
    employees
WHERE
    salary > 12000;

#案例2:查询部门编号不等于90号的员工名和部门编号

SELECT 
    first_name, department_id
FROM
    employees
WHERE
    department_id <> 90;#department_id != 90;

二、按逻辑表达式筛选

#案例1:查询工资在10000到20000之间的员工名、工资以及奖金

SELECT 
    first_name,salary,commission_pct
FROM
    employees
WHERE
    salary >= 10000 AND salary <= 20000;

#案例2:查询部门编号不是在90到110之间，或者工资高于15000的员工信息;

SELECT 
    *
FROM
    employees
WHERE
    department_id < 90
        OR department_id > 100  #NOT(department_id>=90 AND department_id<=110) OR salary>15000;
        OR salary > 15000;

三、模糊查询

/*
like
特点：
between and
is null|is not null
*/

/* 一般和通配符搭配使用
通配符:
% 任意多个字符,包含0个字符
_ 任意单个字符
*/

#案例1:查询员工名中包含字符a的员工信息

SELECT 
    *
FROM
    employees
WHERE
    first_name LIKE '%a%';#%为通配符代表任意

#案例2:查询员工名中第三个字符为e，第五个字符为a的员工名和工资

SELECT 
    last_name, salary
FROM
    employees
WHERE
    last_name LIKE '__e_a%';

#案例3:查询员工名中第二个字符为_的员工名

SELECT 
    last_name
FROM
    employees
WHERE
    last_name LIKE '_\_%';#前面用转义字符\ 或者last_name LIKE '_$_%' ESCAPE '$';

2.between and

/*
①使用between and可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
*/

#案例1:查询员工编号在100到120之间的员工信息

SELECT 
    *
FROM
    employees
WHERE
    employee_id >= 100
        AND employee_id <= 120;
#---------------------------------------------
SELECT 
    *
FROM
    employees
WHERE
    employee_id BETWEEN 100 AND 120;

3.in

/*
含义:判断某字段的值是否属于in列表中的某一项
特点:
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③不支持通配符%等

*/
#案例:查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

SELECT 
    last_name, job_id
FROM
    employees
WHERE
    job_id IN ('IT_PROG' , 'AD_VP', 'AD_PRES');
#------------------------------------------------------
SELECT 
    last_name, job_id
FROM
    employees
WHERE
    job_id = 'IT_PROG' OR job_id = 'AD_VP'
        OR job_id = 'AD_PRES';

4、is null

/*
=或<>不能用于判断null
is nu1l或is not null，可以判断nu1l值

*/
#案例1:查询没有奖金的员工名和奖金率

SELECT 
    last_name, commission_pct
FROM
    employees
WHERE
    commission_pct IS NULL;#不能用commission_pct = null

#案例2:查询有奖金的员工名和奖金率

SELECT 
    last_name, commission_pct
FROM
    employees
WHERE
    commission_pct IS not NULL;

5、[补充] ifnul1函数
功能:判断某字段或表达式是否为null,如果为null返回指定的值，否则返回原本的值

select ifnull (commission_ pct,0) from employees;

6、[补充] isnul1函数
功能:判断某字段或表达式是否为null,如果是，则返回1,否则返回0

7、<=> 安全等于

#案例1:查询没有奖金的员工名和奖金率

SELECT 
    last_name, commission_pct
FROM
    employees
WHERE
    commission_pct <=> NULL;

#案例2:查询工资为12000的员工信息

SELECT 
    *
FROM
    employees
WHERE
    salary <=> 12000;

8、is null 与 <=> 比较

Is NULL:仅仅可以判断NULL值，可读性较高
<=> : 既可以判断NULL值，又可以判断普通的数值，可读性较低

三、排序查询

引入:
select * from employees;
语法:
select 查询列表
from 表
[where 筛选条件]
order by 排序列表 asc(升序)|desc（降序）

特点:
1、asc代表的是升序，desc代表的是降序
如果不写，默认是升序
2、order by子句中可以支持单个子段、多个子段、表达式、函数、别名
3、order by子句一般是放在查询语句的最后面,limit子句除外

#案例1:查询员工信息，要求工资从高到低排序

SELECT * FROM employees ORDER BY salary DESC;#降序
SELECT * FROM employees ORDER BY salary;#默认升序

#案例2:查询部门编号>=90的员工信息，按入职时间的先后进行排序[添加筛选条件]

SELECT 
    *
FROM
    employees
WHERE
    department_id >= 90
ORDER BY hiredate;

#案例3:按年薪的高低显示员工的信息和年薪[按表达式排序]

SELECT 
    *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
    employees
ORDER BY salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC;

#案例4:按年薪的高低显示员工的信息和年薪[order by 后面支持按别名排序 ]

SELECT 
    *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪
FROM
    employees
ORDER BY 年薪 DESC;

#案例5:按姓名的长度显示员工的姓名和工资[按函数排序]

SELECT 
    LENGTH(last_name) AS name_length, last_name, salary
FROM
    employees
ORDER BY name_length DESC;

#案例6:查询员工信息，要求先按工资升序，再按员工编号降序[按多个字段排序]

SELECT 
    *
FROM
    employees
ORDER BY salary ASC , employee_id DESC;

#Test测试

#1.查询员工的姓名和部门号和年薪，按年薪降序按姓名升序

select concat(last_name,first_name) as 姓名,department_id,salary*12*(1+ifnull(commission_pct,0)) as 年薪
from employees
order by 年薪 desc,姓名 asc;

#2.选择工资不在8000到17000的员工的姓名和工资，按工资降序

SELECT 
    CONCAT(last_name, first_name) AS 姓名, salary
FROM
    employees
WHERE
    salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

#3.查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT 
    *
FROM
    employees
WHERE
    email LIKE '%e%'
ORDER BY LENGTH(email) DESC , department_id ASC;
#-------------------------------------------------
SELECT 
    *, LENGTH(email) AS e_length
FROM
    employees
WHERE
    email LIKE '%e%'
ORDER BY e_length DESC , department_id ASC;

四、常见函数

概念:类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处:
1、隐藏了实现细节
2、提高代码的重用性
调用: select 函数名(实参列表)[ from 表] ;
特点:
①叫什么(函数名)
②干什么( 函数功能)
分类:
1、单行函数(一个参数返回一个值)
如:concat、length、 ifnull等
2、分组函数(多个参数返回一个值)
功能:做统计使用，又称为统计函数、聚合函数、组函数

一、字符函数

1.length 获取参数值的字节个数

SELECT LENGTH('john');#4
SELECT LENGTH('张二丰hahaha');#15  urf8字符集下汉字占3个字节 客户端字符集为gbk汉字占两字节
show variables like '%char%';

2.concat拼接字符串

SELECT 
    CONCAT(last_name, ',', first_name) AS 姓名
FROM
    employees;

upper变大写、lower变小写

SELECT UPPER('goutong') ;
SELECT LOWER('goutong') ;

#示例:将姓变大写，名变小写，然后拼接

SELECT 
    CONCAT(LOWER(first_name),',', UPPER(last_name)) AS 姓名
FROM
    employees;

4.substr、substring 截取字符，索引从开始

#截取从指定索引处开始后面的所有字符.

SELECT SUBSTR('今天是个好日子', 5) as outPut;#从5开始

#截取从指定索引开始指定字符长度的字符

SELECT SUBSTR('今天是个好日子', 1,4) as outPut;#从1开始长度为4

#案例:姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT 
    CONCAT(UPPER(SUBSTR(last_name, 1, 1)),
            '_',
            LOWER(SUBSTR(last_name, 2)),'  ',LOWER(SUBSTR(first_name,1)))
FROM
    employees;

instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('我是一个大傻逼', '傻逼') AS out_put;#6
SELECT INSTR('我小可爱是一个小可爱', '小可爱') AS out_put;#2
SELECT INSTR('我小可爱是一个小可爱', '大傻逼') AS out_put;#0

6.trim
#去首尾空格

SELECT TRIM('    大傻吧      ') AS result  ;#大傻吧 
SELECT LENGTH(TRIM('    大傻吧      ')) AS result;#9
#去首位字符
SELECT TRIM('A' FROM 'AAAAAA大傻吧AAAAAA') AS result;#大傻吧

7.lpad用指定的字符实现左填充指定长度，超过右截断

SELECT LPAD('无聊', 10, '★') AS out_put;#★★★★★★★★无聊
SELECT LPAD('无聊', 1, '★') AS out_put;#无

8.rpad用指定的字符实现右填充指定长度

SELECT RPAD('无聊', 10, '★') AS out_put;#无聊★★★★★★★★

9 replace 替换

select replace('我爱吃白菜','白菜','擀面皮') as result;#我爱吃擀面皮

二、数学函数

1.round四舍五入

SELECT ROUND(1.55) ;#2 默认保留整数部分
SELECT ROUND(-1.55) ;#-2
SELECT ROUND(1.567,1) ;#1.6
SELECT ROUND(1.567,2) ;#1.57

2.ceil向上取整,取大于等于它的最小整数

SELECT CEIL(1.001);#2
SELECT CEIL(1.999);#2

3.floor向下取整,取小于等于它的最大整数

SELECT floor(1.001);#1
SELECT floor(1.999);#1
SELECT floor(-1.999);#-2

4.truncate截断

SELECT TRUNCATE (1.69999, 2) ;#1.69

5.mod取余,前数为正结果为正，前数为负，结果为负

SELECT MOD(10,3);#1 
SELECT MOD(10,-3);#1 
SELECT MOD(-10,3);#-1
SELECT MOD(-10,-3);#-1
SELECT 10 % 3;#1

三、日期函数

1.now返回当前系统日期+时间

SELECT NOW() ;

2.curdate返回当前系统日期，不包含时间

SELECT CURDATE() ;

3.curtime返回当前时间，不包含日期]

SELECT CURTIME() ;

4.可以获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) as年;
SELECT YEAR('1998-1-1') AS 年;
SELECT YEAR(hiredate) AS 年 FROM employees;

SELECT MONTH (NOW()) AS 月;
SELECT MONTHNAME (NOW()) AS 月;

str_ to_ date将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%m-%d') AS out_put ;

查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate = '1999-5-14' ;
SELECT * FROM employees WHERE hiredate = STR_TO_DATE ('4-3 1992' , '%c-%d %Y') ;

6.date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW() , '%Y年%m月%d日') AS out_put;

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)

SELECT 
    last_name, DATE_FORMAT(hiredate, '%m月/%d日 %y年')
FROM
    employees
WHERE
    salary IS NOT NULL;

四、其他函数

SELECT VERSION() ;
SELECT DATABASE() ;
SELECT USER() ;

五、流程控制函数
1.if函数: if else的效果

SELECT IF(10 < 5, '大', '小');
SELECT last_name , commission_pct, IF(commission_pct Is NULL, '没奖金，呵呵', '有奖金，嘻嘻') as备注
FROM employees;

2.case1函数的使用相当于switch
/*
java中
switch (变量或表达式) {
case 常量1:语句1;break;
…
default :语句n;break;
mysq1中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;.
when 常量2 then 要显示的值2或语句2; I
else 要显示的值n或语句n;
end
*/

/*案例: 查询员工的工资，要求
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/

select salary as 原工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary 
end as 新工资
from employees;

3.case函数的使用二:类似于多重if
/*
java中:
if(条件1) {
语句1;
}else if(条件2) {
语句2;
}
else {
语句n;
}
mysql中:

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else要显来的值n或语句n
end
*/

#案例:查询员工的工资的情况
/*
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示c级别
否则，显示D级别
*/

SELECT 
    salary,
    CASE
        WHEN salary > 20000 THEN 'A'
        WHEN salary > 15000 THEN 'B'
        WHEN salary > 10000 THEN 'C'
        ELSE 'D'
    END AS 工资等级
FROM
    employees;

Test测试
#1.显示系统时间(注: 日期+时间)

select now();

#2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果(new salary)

SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS 姓名,
    salary AS 'old salary',
    salary * 1.2 AS 'new salary'
FROM
    employees;

#3.将员工的姓名按首字母排序，并写出姓名的长度(LENGTH)

SELECT 
    LENGTH(last_name) AS LENGTH,
    SUBSTR(last_name, 1, 1) AS 首字母,
    last_name
FROM
    employees
ORDER BY 首字母 ASC;

#4.做一个查询，产生下面的结果
/*
<last_name> earns monthly but wants <salary*3> Dream Salary
eg:输出这样的效果：King earns 24000 monthly but wants 72000
*/

SELECT 
    CONCAT(last_name,
            ' earns ',
            salary,
            ' monthly but wants ',
            salary * 3) AS 'dream salary'
FROM
    employees
WHERE
    salary = 24000;

#5.使用CASE-WHEN，按照下面的条件:
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A

SELECT 
    last_name,
    job_id,
    CASE job_id
        WHEN 'AD_PRES' THEN 'A'
        WHEN 'ST_MAN' THEN 'B'
        WHEN 'IT_PROG' THEN 'C'
        WHEN 'SA_REP' THEN 'D'
        WHEN 'ST_CLERK' THEN 'E'
    END AS Grade
FROM
    employees
WHERE
    job_id = 'AD_PRES';

六、分组函数
/*
功能:用作统计使用，又称为聚合函数或统计函数或组函数
分类:
sum求和、avg平均值、max最大值、min最小值、count 计算个数
特点:
1、sum、 avg- 般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略nul1值
3、可以和distinct搭配实现去重的运算|
4、count函数的单独介绍
一般使用count（*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段

*/
1、简单的使用.

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(salary) AS 和,AVG(salary) AS 平均, MAX(salary) AS 最高,MIN(salary) AS 最低, COUNT(salary) AS 个数
FROM employees;

SELECT SUM(salary) AS 和, ROUND (AVG(salary),2) AS 平均, MAX(salary) AS 最高, MIN(salary) AS 最低, COUNT(salary) AS 个数
FROM employees;#平均工资保留两位

2、参数支持类型

SELECT MAX(last_name) ,MIN(last_name) FROM employees ;
SELECT MAX(hiredate) , MIN(hiredate) FROM employees ;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(last_name) FROM employees;

3、和distinct搭配

SELECT SUM(DISTINCT salary) , SUM(salary) FROM employees;
SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees ;

4、count 函数的详细介绍

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;#计算行数
SELECT COUNT(1) FROM employees;#计算行数

5.Test测试

#1.查询公司员工工资的最大值，最小值，平均值，总和

select max(salary) as 最大值,min(salary) as 最小值,round(avg(salary),2) as 平均值,sum(salary) as 总和
from employees;

#2.查询员工表中的最大入职时间和最小入职时间的相差天数(

DIFFRENCE )
select max(hiredate) as 最大入职时间,min(hiredate) as 最小入职时间,datediff(max(hiredate),min(hiredate)) as DIFFRENCE
from employees;

#3.查询部门编号为90的员工个数

SELECT 
    COUNT(*) AS 个数
FROM
    employees
WHERE
    department_id = 90;

五、分组查询

语法:
select 分组函数,列( 要求出现在group by的后面)
from 表.
[where筛选条件]
group by 分组的列表
[order by 子句]
注意:
查询列表必须特殊，要求是分组函数和group by后出现的字段

特点:
1、分组查询中的筛选条件分为两类
①分组前筛选，数据源为原始表，位置放在group by子句的前面，关键字为where；
分组后筛选，数据源为分组后的结果集，位置放在group by子句的后面，关键字为having
②能用分组前筛选的，就优先考虑使用分组前筛选
③分组函数做条件肯定是放在having子句中
2、group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数( 用得较少)
3、排序放最后

#引入:查询每个部门的平均工资

SELECT AVG(salary) FROM employees ;

select AVG(salary),department_id
from employees
group by department_id;

#案例1 :查询每个工种的最高工资

SELECT MAX(salary) ,job_id
FROM employees
GROUP BY job_id;

#案例2:查询每个位置上的部门个数

SELECT COUNT(*) , location_id
FROM departments
GROUP BY location_id;

1.添加分组前筛选条件
#案例1:查询邮箱中包含a字符的，每个部门的平均工资

SELECT AVG(salary) , department_id
FROM employees
where email like '%a%'
GROUP BY department_id;

#案例2:查询有奖金的每个领导手下员工的最高工资

SELECT 
    MAX(salary), manager_id
FROM
    employees
WHERE
    commission_pct IS NOT NULL
GROUP BY manager_id;

2.添加分组后复杂的筛选条件
#案例1:查询哪个部门的员工个数>2
①查询每个部门的员工个数

SELECT 
    COUNT(*), department_id
FROM
    employees
GROUP BY department_id;

②根据①的结果进行筛选，查询哪个部门的员工个数>2

SELECT 
    COUNT(*), department_id
FROM
    employees
GROUP BY department_id
HAVING COUNT(*) > 2;

#案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
①查询每个工种有奖金的员工的最高工资

SELECT 
    MAX(salary), job_id
FROM
    employees
WHERE
    commission_pct IS NOT NULL
GROUP BY job_id;

②根据①结果继续筛选，最高工资>12000

SELECT 
    MAX(salary), job_id
FROM
    employees
WHERE
    commission_pct IS NOT NULL
GROUP BY job_id
HAVING  MAX(salary) > 12000;

#案例3:查询领导编号>102的每个领导手下员工的最低工资>5000的领导编号是哪个，以及其最低工资

①查询领导编号>102的每个领导手下员工的编号，和最低工资

select min(salary),manager_id
from employees
where manager_id>102
group by manager_id;

②查询最低工资>5000

select min(salary),manager_id
from employees
where manager_id>102
group by manager_id
having min(salary)>5000;

3.按表达式或函数分组

#案例:按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

select count(*),length(last_name)
from employees
group by length(last_name)
having count(*) >5;

4.按多个字段分组
#案例:查询每个部门每个工种的员工的平均工资,并且按平均工资的高低显示

SELECT 
    AVG(salary), department_id, job_id
FROM
    employees
GROUP BY department_id , job_id
ORDER BY AVG(salary) DESC;

5.TEST 测试
#1. 查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

SELECT 
    MAX(salary) AS MAX,
    MIN(salary) AS MIN,
    AVG(salary) AS AVG,
    SUM(salary) AS SUM,
    job_id
FROM
    employees
GROUP BY job_id
ORDER BY job_id ASC;

#2、查询员工最高工资和最低工资的差距(DIFFERENCE )

SELECT 
    MAX(salary) AS max,
    MIN(salary) AS min,
    MAX(salary) - MIN(salary) AS DIFFERENCE
FROM
    employees;

#3、查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT 
    MIN(salary), manager_id
FROM
    employees
WHERE
    manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

#4、查询所有部门的编号，员工数量和工资平均值,并按平均工资降序.

SELECT 
    department_id,COUNT(*), AVG(salary)
FROM
    employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

#5、选择具有各个job_id的员工人数.

SELECT 
    COUNT(*), job_id
FROM
    employees
WHERE
    job_id IS NOT NULL
GROUP BY job_id;

六、连接查询

含义:又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象:表1有m行，表2有n行，结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件

按功能分类:
		内连接:
				等值连接
				非等值连接
				自连接
		外连接:
				左外连接
				右外连接
                全外连接
		交叉连接

use girls;
select* from boys;
select * from  beauty;

一.192连接，内连接

1.等值连接

①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④–般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

#案例1:查询女神名和对应的男神名

SELECT 
    name, boyName
FROM
    beauty,
    boys
WHERE
    boyfriend_id =boys.id;

#案例2:查询员工名和对应的部门名

SELECT 
    last_name, department_name
FROM
    employees,
    departments
WHERE
    employees.department_id = departments.department_id;

为表起别名

提高语句的简洁度
区分多个重名的字段
注意:如果为表起了别名，则查询的字段就不能使用原来的表名去限定

#查询员工名、工种号、工种名

SELECT 
    e.last_name, e.job_id, j.job_title
FROM
    employees AS e,
    jobs AS j
WHERE
    e.job_id = j.job_id;

#可以调换表的顺序

SELECT 
    e.last_name, e.job_id, j.job_title
FROM
    jobs AS j,
     employees AS e
WHERE
    e.job_id = j.job_id;

可以加筛选
#案例1:查询有奖金的员工名、部门名

SELECT 
    last_name, department_name,commission_pct
FROM
    employees AS e,
    departments AS d
WHERE
    e.department_id = d.department_id
        AND e.commission_pct IS NOT NULL;

#案例2:查询城市名中第二个字符为o的部门名和城市名

SELECT 
    department_name, city
FROM
    departments,
    locations
WHERE
    departments.location_id = locations.location_id
        AND city LIKE '_o%';

可以加分组
#案例1:查询每个城市的部门个数

SELECT 
    COUNT(*) AS 个数, city
FROM
    departments AS d,
    locations AS l
WHERE
    d.location_id = l.location_id
GROUP BY city;

#案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT 
    department_name, departments.manager_id, MIN(salary)
FROM
    departments,
    employees
WHERE
    departments.department_id = employees.department_id
        AND commission_pct IS NOT NULL
GROUP BY department_name;

可以加排序
#案例:查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT 
    job_title, COUNT(*)
FROM
    jobs,
    employees
WHERE
    jobs.job_id = employees.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

可以实现三表连接
#案例:查询员工名、部门名和所在的城市

SELECT 
    last_name, department_name, city
FROM
    employees,
    departments,
    locations
WHERE
    employees.department_id = departments.department_id
        AND departments.location_id = locations.location_id;

2、非等值连接
#案例1:查询员工的工资和工资级别

SELECT 
    salary, grade_level
FROM
    employees,
    job_grades
WHERE
    salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;

3、自连接
#案例:查询员工名和上级的名称

SELECT e.employee_id, e.last_name, m.employee_id,m.last_name 
FROM employees AS e, employees AS m 
WHERE e.employee_id = m.manager_id;

4.Test 测试1
#一、显示员工表的最大工资，工资平均值

SELECT 
    MAX(salary), AVG(salary)
FROM
    employees;

#二、查询员工表的employee_id, job_id, last_name ，按department_id降序，salary升序

SELECT 
    employee_id, employees.job_id, last_name, department_id, salary
FROM
    employees,
    jobs
WHERE
    employees.job_id = jobs.job_id
ORDER BY department_id DESC , salary ASC;

#三、查询员工表的job_id中包含 a和e的，并且a在e的前面

SELECT DISTINCT
    job_id
FROM
    employees
WHERE
    job_id LIKE '%a%e%';

#四、
已知表student, 里面有 id(学号)，name, gradeId (年级编号)
已知表grade, 里面有 id (年级编号)，name (年级名)
已知表result, 里面有 id, score, studentNo (学号)
要求查询姓名、年级名、成绩

SELECT 
    student.name, grade.name, score
FROM
    student,
    grade,
    result
WHERE
    student.gradeId = grade.id
    and student.id = result.studentNo;

#五、显示当前日期，以及去前后空格，截取子字符串的函数

select now();
select trim( ' s ');
select trim('字符' from '字符串');
select substr (str, startIndex);
select substr (str, startIndex, length) ;

5.TEST测试2
#1.显示所有员工的姓名，部门号和部门名称。

SELECT 
    last_name, departments.department_id, department_name
FROM
    employees,
    departments
WHERE
    departments.department_id = employees.department_id;

#2.查询90号部门员工的job_id和90号部门的location_id

SELECT 
    job_id, location_id
FROM
    employees,
    departments
WHERE
    departments.department_id = employees.department_id
        AND employees.department_id = 90;

#3.选择所有有奖金的员工的last_name ，department_name，location_id，city

SELECT 
    last_name, department_name, departments.location_id, city,commission_pct
FROM
    employees,
    departments,
    locations
WHERE
    departments.location_id = locations.location_id
        AND departments.department_id = employees.department_id
        AND commission_pct IS NOT NULL;

#4.选择city在Toronto工作的员工的last_name ，job_id，department_id，department_name

SELECT 
    last_name,
    job_id,
    departments.department_id,
    department_name,
    city
FROM
    employees,
    departments,
    locations
WHERE
    employees.department_id = departments.department_id
        AND departments.location_id = locations.location_id
        AND city = 'Toronto';

#5.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT 
    department_name,
    job_title,
    MIN(salary),
    employees.job_id,
    departments.department_id
FROM
    employees,
    jobs,
    departments
WHERE
    departments.department_id = employees.department_id
        AND employees.job_id = jobs.job_id
GROUP BY job_title , department_name;

#6.查询每个国家下的部门个数大于2的国家编号

SELECT 
    COUNT(*), country_id
FROM
    locations,
    departments
WHERE
    departments.location_id = locations.location_id
GROUP BY country_id
having count(*) >2;

#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

employees Emp# manager Mgr#
kochhar 101 king 100

SELECT 
    e.last_name AS employees,
    e.employee_id AS 'Emp#',
    m.last_name AS manager,
    m.employee_id AS 'Mgr#'
FROM
    employees AS e,
    employees AS m
WHERE
    e.manager_id = m.employee_id
        AND e.last_name = 'kochhar';

二、sq199语法

语法:
select 查询列表
from 表1 别名 [连接类型 ]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
分类:
内连接(★) : inner
外连接
左外(★) :left [ outer ]
右外(★): right [ outer]
全外: ful1 [ outer] |
交叉连接

一)内连接
/*
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;
分类:
等值
非等值
自连接
特点:
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sq192语法中的等值连接效果是一样的，都是查询多表的交集

等值连接

#1.查询员工名、部门名

SELECT 
    last_name, department_name
FROM
    employees AS e
        INNER JOIN
    departments AS d ON e.department_id = d.department_id;

#2.查询名字中包含e的员工名和工种名(筛选)

sql
SELECT 
    last_name, job_title
FROM
    employees
        INNER JOIN
    jobs ON employees.job_id = jobs.job_id
WHERE

    last_name LIKE '%e%';

#3.查询部门个数>3的城市名和部门个数， (分组+筛选)
#①查询每个城市的部门个数
#②在①结果筛选满足条件的

SELECT 
    city, COUNT(*)
FROM
    locations
        INNER JOIN
    departments ON locations.location_id = departments.location_id
GROUP BY  city
HAVING COUNT(*) > 3;

#4.查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序(排序)

SELECT 
    department_name, COUNT(*)
FROM
    departments
        INNER JOIN
    employees ON departments.department_id = employees.department_id
GROUP BY department_name
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

#5.查询员工名、部门名、工种名，并按部门名降序(添加三表查询)

SELECT 
    last_name, department_name, job_title
FROM
    employees
        INNER JOIN
    departments ON departments.department_id = employees.department_id
        INNER JOIN
    jobs ON employees.job_id = jobs.job_id
ORDER BY department_name DESC;

非等值连接

#查询员工的工资级别

SELECT 
    salary, grade_level
FROM
    employees
        INNER JOIN
    job_grades ON salary BETWEEN lowest_sal AND highest_sal;

#查询每个工资级别的个数>20，并且排序

SELECT 
    COUNT(*), grade_level
FROM
    employees
        INNER JOIN
    job_grades ON salary BETWEEN lowest_sal AND highest_sal
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY COUNT(*) DESC;

自连接

#查询姓名中包含字符k的员工的名字、上级的名字

SELECT 
    e.last_name AS 员工, m.last_name AS 领导
FROM
    employees AS e
        INNER JOIN
    employees AS m ON e.manager_id = m.employee_id
WHERE
    e.last_name LIKE '%k%';

二）、外连接

应用场景:用于查询一个表中有，另一个表没有的记录
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录

2、左外连接，left join 左边的是主表
右外连接，right join 右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果

#1.查询女神表中没有男朋友的女神
#左外连接

SELECT 
    beauty.name, boys.*
FROM
    beauty
        LEFT outer JOIN
    boys ON beauty.boyfriend_id = boys.id
WHERE
    boys.id IS NULL;

#右外连接

SELECT 
    beauty.name, boys.*
FROM
    boys
        RIGHT OUTER JOIN
    beauty ON beauty.boyfriend_id = boys.id
WHERE
    boys.id IS NULL;

#案例1:查询哪个部门没有员工

SELECT 
    department_name, employee_id
FROM
    departments
        LEFT OUTER JOIN
    employees ON departments.department_id = employees.department_id
GROUP BY department_name
HAVING employee_id IS NULL;

#交叉连接实现了笛卡尔积

use girls;
SELECT 
    beauty.*, boys.*
FROM
    beauty
        CROSS JOIN
    boys;

#Test测试
#1、查询编号>3的女神的男朋友信息,I如果有则列出详细，如果没有，用nu1l填充

SELECT 
    beauty.id,beauty.name,boys.*
FROM
    beauty
        LEFT OUTER JOIN
    boys ON beauty.boyfriend_id = boys.id
WHERE
    beauty.id > 3;

#2.查询哪个城市没有部门

select * from locations;
select * from employees;

SELECT 
    city,department_name
FROM
    locations
        LEFT OUTER JOIN
    departments ON locations.location_id = departments.location_id
group by city
having department_name is null;

#3.查询部门名为SAL或IT的员工信息(某部门编号可能该和岗为为null，所以用外连接）

SELECT 
    *
FROM
    departments
        LEFT OUTER JOIN
    employees ON employees.department_id = departments.department_id
WHERE
    departments.department_name IN ('SAL' , 'IT');

七、子查询

含义:
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类:
按子查询出现的位置:
select 后面:
仅仅支持标量子查询
from 后面:
支持表子查询
where或having后面:★
标量子查询（单行）V
列子查询（多行）V
行子查询
exists后面(相关子查询)
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有–列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)

一、 where或having后面

1、
标量子查询( 单行子查询)
2、
列子查询(多行子查询)
3、行子查询(名列名行)
特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

1.标量子查询
#案例1: 谁的工资比Abel高?
①查询Abel的工资

SELECT 
    salary
FROM
    employees
WHERE
    last_name = 'Abel';

②查询员工的信息，满足salary>①结果

SELECT 
    *
FROM
    employees
WHERE
    salary > (SELECT 
            salary
        FROM
            employees
        WHERE
            last_name = 'Abel');

#案例2:返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资

①查询141号员工的job_id

SELECT 
    job_id
FROM
    employees
WHERE
    employee_id = 141;

②查询143号员工的salary

SELECT 
    salary
FROM
    employees
WHERE
    employee_id = 143;

③查询员工的姓名，job_id和工资，要求job_id=①并且salary>②

select last_name,job_id,salary
from employees
where job_id=(SELECT 
    job_id
FROM
    employees
WHERE
    employee_id = 141) and salary>(SELECT 
    salary
FROM
    employees
WHERE
    employee_id = 143);

#案例3:返回公司工资最少的员工的last_name, job_id和salary

①查询公司工资最低工资

SELECT 
    MIN(salary)
FROM
    employees;

②由①查询last_name, job_id和salary

SELECT 
    last_name, job_id, salary
FROM
    employees
WHERE
    salary = (SELECT 
            MIN(salary)
        FROM
            employees);

#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
①查询50号部门最低工资

SELECT 
    MIN(salary)
FROM
    employees
WHERE
    department_id = 50;

②查询每个部门的最低工资

SELECT 
    department_id, MIN(salary)
FROM
    employees
GROUP BY department_id;

③在②中筛选MIN(salary)找大于①的

SELECT 
    department_id, MIN(salary)
FROM
    employees
GROUP BY department_id
HAVING (SELECT 
        MIN(salary)
    FROM
        employees
    WHERE
        department_id = 50);

#非法使用标量子查询

SELECT 
    department_id, MIN(salary)
FROM
    employees
GROUP BY department_id
HAVING (SELECT 
        salary #子查询结果为多行多列，不是单行单列，最终会报错
    FROM
        employees
    WHERE
        department_id = 50);

2.列子查询(多行子查询)
#案例1:返回location_id是1400或1700的部门中的所有员工姓名
①查询location_id是1400或1700的部门id

SELECT DISTINCT
    department_id
FROM
    departments
WHERE
    location_id IN (1400 , 1700);

②查询员工姓名，要求部门id是①列表中的某一个

SELECT 
    last_name
FROM
    employees
WHERE
    department_id IN (SELECT DISTINCT
            department_id
        FROM
            departments
        WHERE
            location_id IN (1400 , 1700));

#案例2:返回其它工种中比job_id为’IT_PROG’ 工种任一工资低的员工的员工号、姓名、job_ id以及salary
①查询job_id为’IT_PROG’ 工种任一工资

SELECT DISTINCT
    salary
FROM
    employees
WHERE
    job_id = 'IT_PROG';

②查询员工号、姓名、job_id以及salary， salary<(①) 的任意一个

SELECT 
    last_name, employee_id, job_id, salary
FROM
    employees
WHERE
    salary < any(SELECT DISTINCT
            salary
        FROM
            employees
        WHERE
            job_id = 'IT_PROG')
        AND job_id <> 'IT_PROG';

或

SELECT 
    last_name, employee_id, job_id, salary
FROM
    employees
WHERE
    salary < (SELECT 
            MAX(salary)
        FROM
            employees
        WHERE
            job_id = 'IT_PROG')
        AND job_id <> 'IT_PROG';

#案例3:返回其它部门中比job_id为’IT_PROG’ 部门所有工资都低的员工的员工号、姓名、job_ id以及salary

SELECT 
    last_name, employee_id, job_id, salary
FROM
    employees
WHERE
    salary < ALL (SELECT 
            salary
        FROM
            employees
        WHERE
            job_id = 'IT_PROG')
        AND job_id <> 'IT_PROG';

#3、行子查询(结果集一行多列或多行多列)
#案例:查询员工编号最小并且工资最高的员工信息

SELECT 
    *
FROM
    employees
WHERE
    (employee_id , salary) = (SELECT 
            MIN(employee_id), MAX(salary)
        FROM
            employees);

①查询最小的员工编号

SELECT 
    MIN(employee_id)
FROM
    employees;

②查询最高工资

SELECT MAX(salary)
FROM employees;

③查询员工信息

SELECT 
    *
FROM
    employees
WHERE
    employee_id = (SELECT 
            MIN(employee_id)
        FROM
            employees)
        AND salary = (SELECT 
            MAX(salary)
        FROM
            employees);

二、select后面

#案例:查询每个部门的员工个数

SELECT d.*, (
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
) '个数'
FROM departments d;

或

SELECT 
    employees.department_id, COUNT(*)
FROM
    employees
       left OUTER JOIN
    departments ON  employees.employee_id =departments.department_id 
GROUP BY employees.department_id;

#案例2:查询员工号=102的部门名

SELECT 
    employee_id, department_name
FROM
    employees
        INNER JOIN
    departments ON employees.department_id = departments.department_id
WHERE
    employee_id = 102;

三、from后面

将子查询结果充当一张表，要求必须起别名

#案例:查询每个部门的平均工资的工资等级

①查询每个部门的平均工资

SELECT 
    departments.department_id, AVG(salary)
FROM
    departments
        INNER JOIN
    employees ON departments.department_id = employees.department_id
GROUP BY departments.department_id;

SELECT * FROM job_grades;

②连接①的结果集和job_grades表，筛选条件平均工资between

lowest_sal and highest_sal
SELECT 
    avg_dep.*, grade_level
FROM
    (SELECT 
        departments.department_id, AVG(salary) AS avg_salary
    FROM
        departments
    INNER JOIN employees ON departments.department_id = employees.department_id
    GROUP BY departments.department_id) AS avg_dep
        INNER JOIN
    job_grades ON avg_dep.avg_salary BETWEEN lowest_sal AND highest_sal;

四、exists后面(相关子查询)

语法:
exists (完整的查询语句)
结果:
1或0

#案例1:查询有员工的部门名

SELECT 
    department_name
FROM
    departments d
WHERE
    EXISTS( SELECT 
            *
        FROM
            employees e
        WHERE
            d.department_id = e.department_id);

SELECT 
    distinct department_name
FROM
    departments d
        INNER JOIN
    employees e ON d.department_id = e.department_id
        AND employee_id IS NOT NULL;

#1.查询和Zlotkey相同部门的员工姓名和工资

①查询zlotkey的部门id

SELECT 
    department_id
FROM
    employees
WHERE
    last_name = 'zlotkey';

②查询部门id=①的员工姓名和工资

SELECT 
    last_name, salary
FROM
    employees
WHERE
    department_id = (SELECT 
            department_id
        FROM
            employees
        WHERE
            last_name = 'zlotkey');

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
①查询公司的平均工资

SELECT 
    AVG(salary)
FROM
    employees;

#②查询工资大于①的员工的员工号，姓名，和工资

SELECT 
    employee_id, last_name, salary
FROM
    employees
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            employees);

#3.查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
①查看各部门的平均工资

SELECT 
    department_id, AVG(salary) AS avg_salary
FROM
    employees
GROUP BY department_id;

#②查询employees表中部门id = ①表id，且工资大于平均工资的信息

SELECT 
    employee_id, last_name, salary, e.department_id
FROM
    employees e
INNER JOIN
    (SELECT department_id, AVG(salary) AS avg_salary
     FROM employees
     GROUP BY department_id) AS avg_dp
ON e.department_id = avg_dp.department_id
where salary > avg_dp.avg_salary;

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
①查询姓名中包含字母u的员工和部门id

select distinct department_id
from employees
where last_name like '%u%';

②查询部门id=①中任意一个的员工的员工号和姓名

select e.employee_id,last_name,e.department_id
from employees as e
inner join (select distinct department_id
			from employees
			where last_name like '%u%') as d_id
on e.department_id = d_id.department_id
order by employee_id asc;

或

SELECT last_name, employee_id
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);

#5.查询在部门的location_id为1700的部门工作的员工的员工号
①查询location_id为1700的部门

select department_id
from departments
where location_id = 1700;

#②查询在①中部门工作的员工和员工号

select last_name,employee_id
from employees
where department_id in (select department_id
                        from departments
						where location_id = 1700);

#6.查询管理者是King的员工姓名和工资
①查询管理者King的员工编号

select employee_id
from employees
where last_name = 'k_ing';

②查询员工的manager_id = ①的员工姓名和工资
select last_name,salary

from employees
where manager_id in(select employee_id
					from employees
					where last_name = 'k_ing');

或者自连接

select e.last_name,e.employee_id,e.manager_id,e.salary,m.last_name
from employees as e,employees as m
where e.manager_id = m.employee_id
and m.last_name = 'k_ing';

#7.查询工资最高的员工的姓名，要求first_ name和last_ name显示为一列，列名为姓名

select concat(first_name,' ',last_name) as 姓名
from employees
where salary=(select max(salary) 
			  from employees);

八、分页查询

应用场景:当要显示的数据，一页显示不全，需要分页提交sq1请求
语法:
select 查询列表
from 表
[ join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段]
limit offset, size;

特点:
①limit语句放在查询语句的最后
②公式
要显示的页数page,每页的条目数size
select 查询列表
from 表
limit (page-1)*size, size;

	(page  size=10 
	1     	0
    2	 	10
	3		20

offset 要显示条目的起始索引(起始索引从0开始)
size 要显示的条目个数

#案例1:查询前五条员工信息

SELECT *  FROM employees LIMIT 0,5;#0为开始索引，5为显示五条。

#案例2:查询第11条----第25条

SELECT *  FROM employees LIMIT 10,15;

#案例3:有奖金的员工信息，并且工资较高的前10名显示出来

SELECT 
    *
FROM
    employees
WHERE
    commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0 , 10;

九、联合查询

union联合合并:将多条查询语句的结果合并成一个结果

语法:
查询语句1
union
查询语句2
union
…

应用场景:要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点:
1、要求多条查询语句的查询列数是一致的!
2、要求多条查询语句的查询的每一列的类型和顺序最好一致.
3.union关键字默认去重，如果使用union all可以包含重复项

#引入的案例:查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;

SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

#案例:查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id, cname, csex FROM t_ca WHERE csex='男'
UNION
SELECT t_id, tName, tGender FROM t_ua WHERE tGender='male' ;

十、查询顺序

查询语句中涉及到的所有的关键字，以及执行先后顺序
select 查询列表
from 表
连接类型 join 表2
on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 偏移，条目数