day02-mysql-数据查询

MySQL 分类

DQL语言：Data Query Language      数据查询语言
	select
DML语言：Data Manipulate Language 数据操纵语言
	insert、update、delete
DDL语言：Data Define Language      数据定义语言
	create、alter、drop
DCL语言：Data Control Language     数据控制语言
	commit、rollback

MySQL 语法规范

（1）不区分大小写
（2）每句话用;或\g结尾
（3）MySQL的注释:

       单行注释:#
       单行注释：-- 
       多行注释：/*  */

（4）sql语句可以写在一行或多行，各子句一般分行写
（5）关键字不能缩写也不能分行
（6）用缩进提高语句的可读性

1. 基础查询

查询常量

SELECT 10;
SELECT 'alex';

显示表达式

SELECT 2+3;

函数

SELECT VERSION();
SELECT LENGTH('alex');

查询表中的字段

SELECT email '邮箱' from employees;
SELECT email 'youxiang' from employees;

查询表中的多个字段

SELECT * from employees;
SELECT job_id '工号',email '邮箱' from employees;
SELECT job_id 'gonghao',email 'youxiang' from employees;

显示表中所有字段

SELECT *from employees;
SELECT *from employees;

起别名

SELECT job_id 'gonghao' from employees;
SELECT job_id '工号' FROM employees; #方式一
SELECT job_id AS '工号' FROM employees; #方式二

如何去重

SELECT DISTINCT department_id from employees;
SELECT DISTINCT department_id from employees;

2.条件查询

 案例一:查询月薪大于五千的员工信息
SELECT * from employees where salary>5000;

 案例二：查询月薪=12000的员工信息 
SELECT * from employees where salary=12000;

 案例三：查询月薪不等于12000 的员工信息
SELECT * from employees where salary <>12000;

3.按逻辑表达式查询

案例一：查询月薪在5000到12000的员工信息（包含5000和12000）
SELECT * from employees where salary >=5000 and salary<=12000;#方式一：
SELECT * from employees where salary BETWEEN 5000 and 12000;# 方式二：

案例二：查询月薪不在5000到12000的员工工资和姓名
SELECT salary,last_name from employees where NOT (salary>=5000 and salary <=12000);

案例三：查询部门号=90 或 月薪 >1000并且 月薪<15000的员工信息
SELECT * from employees where department_id = 90 or salary>1000 and salary <15000;

生成随机数
SELECT CEIL(RAND()*50);

显示出表employees部门编号在80-100之间 的姓名、工号
SELECT first_name,job_id from employees where department_id BETWEEN 80 AND 100;

显示出表employees的manager_id 是 100,101,110 的员工姓名、工号
SELECT last_name,job_id from employees where manager_id in(100,101,110);

4.模糊查询

like查询

案例1：查询姓名中包含字符 e的员工信息
SELECT * from employees where first_name LIKE '%e%';

案例2：查询姓名中第二个字符为e，第四个字符为a的员工信息
SELECT *from employees WHERE first_name LIKE '_e_a%';

案例3：查询姓名中第三个字符为_的员工信息
SELECT * from employees WHERE first_name LIKE '__\_%';
SELECT * from employees where first_name LIKE '__$_%' ESCAPE '$'; #将$自定义为转义符

between and 查询

案例1：显示出表employees部门编号在80-100之间 的姓名、职位
SELECT last_name,job_id,department_id FROM employees WHERE department_id BETWEEN 80 AND 100;

in 查询

案例1：显示出表employees的manager_id 是 100,101,110 的员工姓名、职位
SELECT last_name,job_id,manager_id FROM employees WHERE manager_id IN(100,101,110);

案例2：查询 job_id 为AD_VP或ST_MAN或SA_MAN
SELECT *from employees where job_id in('AD_VP','ST_MAN,','SA_MAN');

is null / is not null 查询

案例1：查询没有奖金的员工
SELECT * from employees where commission_pct is NOT NULL;

5.排序查询

一、按单个字段进行排序

select * from employees ORDER BY salary desc;
SELECT * from employees ORDER BY salary,employee_id DESC;

== 二、按表达式排序==

案例：按年薪排序
SELECT department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪 from employees ORDER BY salary*12*	(1+IFNULL(commission_pct,0)) DESC;

三、按别名排序

案例：按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;

四、按函数排序

案例：按姓名中的字节长度大小降序
SELECT last_name,LENGTH(last_name) FROM employees ORDER BY LENGTH(last_name) DESC;

6.分组函数

一、普通的分组查询

01 案例：查询各部门的最高工资和部门号
SELECT MAX(salary),department_id from employees GROUP BY department_id;

二、按多个字段分组

案例：查询每个工种、每个部门的平均工资
SELECT avg(salary),job_id,department_id from employees GROUP BY job_id,department_id;

三、分组查询+筛选having

案例1：查询有奖金的，每个部门的最高奖金率
SELECT MAX(commission_pct) 最高奖金率,department_id from employees where commission_pct is not null GROUP BY department_id;

案例2：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号
SELECT department_id,MIN(salary) 最低工资 from employees where last_name LIKE '%e%' GROUP BY department_id HAVING 最低工资  >3000;

案例3：查询电话以“515”开头的，工种号包含字符'T'的,每个工种的平均工资>5000的工种号和平均工资
SELECT job_id,avg(salary) from employees where phone_number like '515%' and job_id LIKE '%T%' GROUP BY job_id having avg(salary) > 5000;
SELECT job_id,AVG(salary) FROM employees WHERE phone_number LIKE '515%' AND job_id LIKE '%T%' GROUP BY job_id HAVING AVG(salary)>5000;

7.等值连接查询

①案例1：查询员工名、部门名

SELECT last_name,department_name
from employees e,departments d
where e.department_id = d.department_id;

②为表起别名

SELECT last_name 员工名,department_name 部门名
from  employees e,departments d
where e.department_id=d.department_id

③添加筛选条件

案例：查询 工资>5000的工种名和员工名、工资
SELECT job_title,last_name,salary
from employees e,jobs j
where e.job_id=j.job_id and salary>5000

④添加分组和筛选

01案例：查询每个部门的员工个数和部门名
SELECT count(*) 个数,department_name
from employees e,departments d
where e.department_id=d.department_id
GROUP BY e.department_id;

⑤排序

01案例：查询每个部门的员工个数和部门名，每个部门人数大于3，按照人数倒序排序
SELECT count(*) 个数,department_name
from departments d,employees e
where e.department_id=d.department_id
GROUP BY e.department_id
HAVING count(*)>3
ORDER BY COUNT(*) DESC

⑥ 三表连接

案例：查询员工名、部门名和所在城市
SELECT last_name,department_name,city
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id;