mysql 1.42_mysql-查询(DQL)

+

注释：mysql中的+号只有一个作用，就是运算符，没有连接字符串的作用，连接字符串用concat。

select 1+3;两个操作数都是数值型，则做加法运算。select 'wj'+28;select '3'+94；只要其中有一个时字符型，则会试图将字符转换为数值型。如果转换成功，则做加法运算；如果转换失败，则将字符转换为0继续做加法运算。select null+100;如果其中一方为null，则结果肯定为null。

concat(str1,str2,…)

注释：连接多个字符串

select concat('a','b','c');select concat(first_name,last_name) from employees;

条件运算符：>    >= <=

注释：mysql支持两个形式的不等运算符，!=和<>都支持，但是建议<>。

作用：用于在where关键词后，用于条件筛选。

select * from employees where salary<>100;

逻辑运算符：and  or  not

注释：逻辑运算符用于连接条件表达式，可以用括号把条件包起来进行逻辑运算。

and(&&)两个条件都是true，则结果为true。

or(||)两个条件有一个为true，则结果为true。

not(！)条件如果为true，则结果为false。

#查询部门编号不是在90到110之间，或者工资高于15000的员工信息select * from employees where department_id<90 and department_id>110 or salary>15000;select * from employees where not(department_id>=90 and department_id<=110) or salary>15000;

模糊查询：like、between and、in、is null、is not null

like：与like搭配使用的的通配符有(_:匹配任意一个字符，%：匹配任意多个字符，如果想匹配_或%则可以通过转移符号\反斜杠来标明，或者通过一个关键字escape进行转义)

select last_name from employees where last_name like '_\_%'; 或者用escape关键字转义 select last_name from employees where last_name like '_$_%' escape '$';通过escape关键字标识$为转义字符，这样$后面的第一个字符将被转义，_不会再被解读为通配符。

between and：1.使用between and 可以提高语句的简洁度,两边的类型必须一致或者能阴式的转换。

2.包含临界值，也就是如果说between 10 and 20,这个10和20是包含的。

3.临界值不能调换，左边的数要小于等于右边的数，也就是说上面的10和20不能调换，再通俗点即between and完全等价于，大于等于左边的值，小于等于右边的值。

select * from employees where salary between 10000 and 20000;

in：判断某字段的值是否属于in列表中的某一项；in列表的值类型必须一致或可以隐式转换；完全等价于xxx=### or ...；不支持通配符

select job_id from employees where job_id in('it_prot','ad_vp');

is null：=或<>等条件运算符不能判断null值；is null或is not null 可以判断null值

select commission_pct from employees where commission_pct is null;

安全等于：<=>

注释：既可以判断null值，又可以判断普通类型的值，可读性较低，不建议使用，不能与！等搭配使用只能判断是否相等。　　is null：仅仅可以判断null值，可读性高，建议使用。

select * from employees where salary <=> 10000; select commission_pct from employees where commission_pct <=> null;

ifnull(expr1,expr2,…)

注释：判空

案例：查询员工的年薪

错误的：select salary*12*(1+commission_pct) as 年薪 from employees; 错误是因为commission_pct奖金率可能存在空值的情况。 正确的： select salary*12*(1+ifnull(commission_pct,0)) as 年薪 from employees; 如果是null就转化为0

isnull(expr)

注释：判断是否为空，如果表达式或字段是null，则结果为1，否则为0；

select isnull(commission_pct) from employees;最后可以看到结果，如果是null的查询出来的都是1，不是null的查询出来都是0。

length(str)

注释：可以计算一个字符串的长度

查询-排序

注释：　1.order by 字段 asc/desc，排序默认升序排序，支持单个字段，多个字段，表达式，别名，函数。

2.可以对多个字段分别排序，如果第一个相等，会按第二个进行排序。

3.order by 子句一般是放在查询语句的最后面，limit子句除外。

案例：查询员工信息，先按工资升序，再按员工编号降序。

select * from employees order by salary asc,employee_id desc;如果都不写排序方式，直接order by 字段1,字段2，则会按照两个字段升序排序。

字符函数

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

select length('wujing');结果为6个字节。select length('诸葛wujing');结果为12个字节，在utf8下，一个汉字占3个字节。select variables like '%char%';可以查看编码格式。

2.concat()　　拼接字符串

select concat('wu','jing');

3.upper、lower　　大小写转换

select upper('wujing');将小写转换为大写。select lower('WUJING');将大写转换为小写。select concat(upper('wu'),lower('JING'));分别转换为大小写，并进行拼接。

4.substr同substring

截取字符串，索引从1开始。

#截取指定索引后面所有字符select substr('怀念大学生活',3);结果为：大学生活。从第三个字符串开始截取，包含第三个字符串。

#截取指定索引处指定字符长度的字符串。select substr('wj小王八蛋',4,2);结果为：王八。

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

select instr('石家庄市裕华区','裕华');结果为：5

6.trim　　将字符串左右去空，或去除字符串左右指定字符。

selct trim('你妹的');结果为：你妹的select trim('j' from 'jjjjjjj你好j吗jjjjjjjj');结果为：你好j吗

7.lpad　　用指定字符实现左填充指定长度

select lpad('火车',5,'啊');结果为：啊啊啊火车。记住是字符，不是字节

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

select rpad('今天',7,'烦')；结果为：今天烦烦烦烦烦

9.replace　　字符替换

select replace('j我有点累了','j','不想了');结果为：不想了我有点累了。这个的三个参数，第一个是原始的字符串，第二个是要替换的字符，第三个是要替换为的字符。

数学函数

1.round　　四舍五入

select round(1.56);结果为：2

select round(1.42);结果为：1

select round(-1.56);结果为：-2。这个先别管正负，直接先四舍五入再加上符号即可，貌似与java中的一个四舍五入有点区别。select round(-1.45);结果为：-1

select round(1.268,2);结果为：1.27。也即小数点后保留两位。

2.ceil　　向上取整，返回>=该参数的最小整数。

select ceil(1.26);结果为：2

select ceil(1.0);结果为：1。记住是>=1.0的最小整数

3.floor　　向下取整，返回<=该参数的最大整数

select floor(1.59);结果为：1

select floor(1.00);结果为：1

select floor(-1.59);结果为：-2

4.truncate j截断

select truncate(1.899,2);结果为：1.89。保留两位，不会进行四舍五入等操作，直接截断。

5.mod 取余

/*mod(a,b); 等价于a-a/b*b 最后的正负总与a被除数一致*/

select mod(10,3);结果为：1。等价于select 10%3;select mod(10,-3);结果为：-1

日期函数

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

select now();结果为：2019-03-30 16:20:17

2.curdate　　当前系统日期，不包括时间

select curdate();结果为：2019-03-30

3.curtime　　当前系统时间，不包括日期

select curtime();结果为：16:27:18

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

select year(now());结果为：2019。传入的可以是完整的日期，也可以是能转换为日期的字符串。select year('2019-03-30');结果为：2019

5.str_to_date　　将日期格式的字符转换为指定格式的日期，如：str_to_date('3-09-2013','%d-%m-%y')

6.date_format　　将日期转换为字符，如：date_format('2018/6/6','Y%年%m月%d日')

其他函数

selectVERSION();select database();select user();

7.datadiff(expr1,expr2)　　获取两个日期的相差的天数(左边的日期减右边的日期，如果左边的大则为正数)

select datediff(MAX(hiredate),min(hiredate)) from employees;这其中的两个表达式不仅可以传入时间类型，也可以传入日期字符串

流程控制函数

1.if(expr1,expr2,expr3)　　if else的效果

select last_name,salary,if(commission_pct is not null,concat('有奖金',commission_pct),'没奖金，呵呵') from employees

2.case函数的使用，switch case的效果

/*java中

switch(){

case 常量1：语句1；break；

case 常量2：语句2；break；

default:语句n;break；

}

mysql中

case 要判断的字段或表达式

when 常量1 then 要显示的值1或表达式1；

when 常量2 then 要显示的值2或表达式2；

...

else 要显示的值n或表达式n；

end;

注意：用的时候如果是跟在select后面作为一个值输出时，then后面不要加分号，而且此时case后面要加某个字段，如果是在存储过程中，则then后可以是一个语句，此时才可以加分号。*/

select salary 原始工资,department_id as部门id,casedepartment_idwhen 30 then salary*1.1

when 40 then salary*1.2

elsesalaryend as新工资from employees;

3.case函数的使用，if else的效果

mysql中格式为：case

when 条件1 then 要显示的值1/语句1；如果是值则没有分号，以下类推。when 条件2 then 要显示的值2/语句2；

...else 要显示的值n/语句n;end案例：查询员工工资级别

如果工资>20000,显示A级别

如果工资>15000,显示B级别

如果工资>10000,显示C级别

否则显示D级别selectlast_name,salary,case

when salary>20000 then 'A'

when salary>15000 then 'B'

when salary>10000 then 'C'

else 'D'

end as工资级别from employees;

分组函数

注解：用作统计使用，又称为聚合函数，或统计函数，或组函数。

分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

注意：1.sum、avg用于处理数值型

2.max、min、count可以处理任何类型

3.以上分组函数都忽略null值

4.可以和distinct搭配使用实现去重运算的效果，格式：select count(distinct salary) from employees;

5.count函数的单独介绍，一般使用count(*)作为统计行数。

6.和分组函数一同查询的字段要求是group by后的字段。

分组查询

语法：select分组函数，列(要出现在group by的后面)from表

【where筛选条件】group by分组的列表

【having】分组后条件

【order by子句】

注意：查询列表比较特殊，必须是group by后面出现的字段；并且如果在分组后加条件的话要用having关键字(在分组前加条件或在分组后加条件，就看你的条件是依赖分组前的原始表还是分组后的虚表。)

特点：

数据源

位置

关键字

分组前筛选

原始表

group by 子句前面

where

分组后筛选

分组后的结果集

group by 子句后面

having

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

②能用分组前筛选的，就优先使用分组前筛选。

③grou by子句支持单个字段分组、多个字段分组(多个字段用逗号隔开，没先后顺序)、表达式或函数(用的较少)

④可以添加排序(排序放在整个分组查询的最后)

案例1：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资(分组前后都加条件)select job_id,MAX(salary) from employees where commission_pct is not null group by job_id HAVING MAX(salary)>12000;

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

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

分组查询-按多个字段分组

注释：直接在group by子句中添加多个字段用逗号隔开即可，没有先后顺序要求。

查询每个部门每个工种的平均工资select avg(salary),department_id,job_id from employees group by department_id,job_id;

分组查询-添加排序

查询每个部门每个工种的平均工资，并按工资高低显示select avg(salary),department_id,job_id from employees group by department_id,job_id order by avg(salary) desc;

连接查询(sql92语法)

内连接

等值连接

①多表等值连接的结果为多表的交集部分

②n表连接，至少需要n-1个连接条件

③多表的顺序没有要求

④一般需要为表起别名

⑤可以搭配其他所有子句使用，比如分组、排序、筛选

非等值连接

注释：也即连接的条件是非等值的，是在一个区间中。

#查询员工的工资和工资级别select salary,grade_level from employees e,job_grades j where e.salary between j.lowest_sal and j.highest_sal;

自连接

注释：把本张表当成两张表，甚至更多表使用，自己连接自己。

#查询员工名和上级的名称select e1.last_name 员工名,e2.last_name 上级名 from employees e1,employees e2 where e1.manager_id=e2.employee_id;

连接查询(sql99语法)

语法：select查询列表from表1 别名 【连接类型】join表2 别名on连接条件

【where筛选条件】

【group by分组条件】

【having筛选条件】

【order by排序列表】

分类：

内连接☆：inner外连接：

左外☆：left 【outer】

右外☆：rigth 【outer】

全外：full 【outer】

交叉连接：cross

内连接

等值连接

特点：1、添加排序、分组、筛选2、inner可以省略3、筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读4、innerjoin连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集部分

案例1：查询员工名、部门名select last_name,department_name from employees e inner join departments d on e.department_id=d.department_id;

案例2：查询名字中包含e的员工名和工种名(添加筛选)select last_name,job_title from employees e inner join jobs j on e.job_id=j.job_id where e.last_name like '%e%';

案例3：查询部门个数>3的城市名和部门个数(添加分组+筛选)select city,count(*) 部门个数 from departments d inner join locations l on d.location_id =l.location_id group by city having count(*)>3;

非等值连接

查询员工的工资级别select salary,grade_level from employees e join job_grades g on e.salary between g.lowest_sal and g.highest_sal group by grade_level;

外链接

应用场景：用于查询一个表中有，另一个表中没有的记录

特点：1、外连接的查询结果为主表中的所有记录

如果从表中有和他匹配的，则显示匹配的值

如果从表中没有和他匹配的，则显示null

外连接查询结果=内连接结果+主表中有而从表中没有的记录2、左外连接：left join左边的是主表3、右外连接：right join右边的是主表4、左外连接和右外连接交换两个表的顺序实现的是相同的效果

案例1：查询男朋友不在男生表的女神名select be.name from beauty be left join boys bo on be.boyfriend_id=be.id where bo.id is null;

子查询

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

分类：

按子查询出现的位置：

select后面：

仅仅支持标量子查询

from后面：

支持表子查询

where或having后面：☆

标量子查询(单行)√

列子查询(多行)√

行子查询

exists后面(相关子查询)

表子查询

按结果集的行列数不同：

标量子查询(结果集只有一行一列)

列子查询(结果集只有一列多行)

行子查询(结果集有一行多列)

表子查询(结果集一般为多行多列)

where或having后面

1、标量子查询(单行子查询)

2、列子查询(多行子查询)

3、行子查询(多列多行)

特点：

①子查询放在小括号内

②子查询一般放到条件右侧

③标量子查询一般搭配着单行操作符使用>、=、<=、<>

列子查询，一般搭配着多行操作符使用in、any、some、all

>any:只要比其中的任意的一个大即可，类似这种。

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

#2.列子查询(多行子查询)★

#案例1：返回location_id是1400或1700的部门中的所有员工姓名

#①查询location_id是1400或1700的部门编号SELECT DISTINCTdepartment_idFROMdepartmentsWHERE location_id IN(1400,1700)

#②查询员工姓名，要求部门号是①列表中的某一个SELECTlast_nameFROMemployeesWHERE department_id <>ALL(SELECT DISTINCTdepartment_idFROMdepartmentsWHERE location_id IN(1400,1700)

);

#3、行子查询(结果集一行多列或多行多列)

#案例：查询员工编号最小并且工资最高的员工信息SELECT *

FROMemployeesWHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary)FROMemployees

);

#①查询最小的员工编号SELECT MIN(employee_id)FROMemployees

#②查询最高工资SELECT MAX(salary)FROMemployees

#③查询员工信息SELECT *

FROMemployeesWHERE employee_id=(SELECT MIN(employee_id)FROMemployees

)AND salary=(SELECT MAX(salary)FROMemployees

);

select后面

/*仅仅支持标量子查询*/#案例：查询每个部门的员工个数SELECT d.*,(SELECT COUNT(*)FROMemployees eWHERE e.department_id =d.`department_id`

) 个数FROMdepartments d;

#案例2：查询员工号=102的部门名SELECT(SELECTdepartment_name,e.department_idFROMdepartments dINNER JOINemployees eON d.department_id=e.department_idWHERE e.employee_id=102) 部门名;

from后面

/*将子查询结果充当一张表，要求必须起别名*/#案例：查询每个部门的平均工资的工资等级

#①查询每个部门的平均工资SELECT AVG(salary),department_idFROMemployeesGROUP BYdepartment_id#②连接①的结果集和job_grades表，筛选条件平均工资between lowest_sal andhighest_salSELECT ag_dep.*,g.`grade_level`FROM(SELECT AVG(salary) ag,department_idFROMemployeesGROUP BYdepartment_id

) ag_depINNER JOINjob_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面(相关子查询)

/*语法：

exists(完整的查询语句)

结果：1或0，如果是1说明有查询结果，如果是0说明没有查询结果。*/

SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000);

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

#in

SELECTdepartment_nameFROMdepartments dWHERE d.`department_id` IN(SELECTdepartment_idFROMemployees

)

#exists

SELECTdepartment_nameFROMdepartments dWHERE EXISTS(SELECT *

FROMemployees eWHERE d.`department_id`=e.`department_id`

);

#案例2：查询没有女朋友的男神信息

#in

SELECT bo.*

FROMboys boWHERE bo.id NOT IN(SELECTboyfriend_idFROMbeauty

)

#exists

SELECT bo.*

FROMboys boWHERE NOT EXISTS(SELECTboyfriend_idFROMbeauty bWHERE bo.`id`=b.`boyfriend_id`

);

分页查询

/*应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

select 查询列表

from

【join type join 表2

on 连接条件

where 筛选条件

group by 分组字段

having 分组后的筛选

order by 排序的字段】

limit 【offset,】size;

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

size 要显示的条目个数

特点：

①limit语句放在查询语句的最后

②公式

要显示的页数 page，每页的条目数size

select 查询列表

from 表

limit (page-1)*size,size;

size=10

page

1 0

2 10

3 20*/#案例1：查询前五条员工信息SELECT * FROM employees LIMIT 0,5;SELECT * FROM employees LIMIT 5;

#案例2：查询第11条——第25条SELECT * FROM employees LIMIT 10,15;

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

*

FROMemployeesWHERE commission_pct IS NOT NULL

ORDER BY salary DESCLIMIT10 ;

联合查询

/*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 FROM t_ca WHERE csex='男'

UNION ALL

SELECT t_id,tname FROM t_ua WHERE tGender='male';