MySQL

#查询t_employee表数据 ，*代表查询所有数据
SELECT * from t_employees;
#使用具体的列名称查询
SELECT EMPLOYEE_ID ,FIRST_NAME,EMAIL,SALARY from t_employees

alias 别名 给结果起个别名

SELECT EMPLOYEE_ID as ‘员工编号’ ,FIRST_NAME as ‘名’,EMAIL as ‘邮箱’,SALARY as ‘月薪资’ from t_employees

可以对某些列（数字型）进行加减乘除

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY*12 from t_employees

+ - * /是真正的除 %用来做求余 (% mysql 是个模糊查询)

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY%12 as ‘年薪’ from t_employees

+

select 1+2;
#去重的方法 DISTINCT
select DISTINCT(DEPARTMENT_ID) from t_employees;

order by 默认是正序，ASC可加可不加，DESC

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees ORDER BY FIRST_NAME ASC ;

按照薪资倒着排

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees ORDER BY SALARY DESC ;

多重排序

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees ORDER BY SALARY DESC ,EMPLOYEE_ID DESC ;

有null的列进行排序 null最小的

SELECT MANAGER_ID from t_employees order by MANAGER_ID desc ;

查询条件 where 等值条件用单等号，不是双等

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY=‘17000’ ;#不好的示范,因为salary是数字型的
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY=17000 ;#用原有的类型

查询薪资>17000的员工

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY>17000;

5000到17000的 包含 多个条件

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY>=5000 AND SALARY<=17000;

使用between and进行范围查询( 包含两边的极限值) 从小到大

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY BETWEEN 5000 AND 17000
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY>5000 AND SALARY<17000;# 之间的，不包含

找薪资大于17000或者first_Name叫Lex的

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY>17000 OR FIRST_NAME=‘Lex’

查询月薪不是17000的 所有员工

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where NOT SALARY=17000 ;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY!=17000 ;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where SALARY<>17000 ;

null值的判断 取等于null的数据

select FIRST_NAME,MANAGER_ID from t_employees where MANAGER_ID is null ;

null值 ，不等的处理

select FIRST_NAME,MANAGER_ID from t_employees where MANAGER_ID is not null ; # ``有值

select EMPLOYEE_ID,FIRST_NAME,COMMISSION_PCT from t_employees where COMMISSION_PCT is not null;

查一个列多个情况时 多个条件拼接

select EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID from t_employees WHERE
DEPARTMENT_ID =‘70’ or DEPARTMENT_ID =‘80’ or DEPARTMENT_ID =‘90’;
#枚举查询
select EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID from t_employees WHERE
DEPARTMENT_ID in (‘70’,‘80’,‘90’);

模糊查询 查L开头的所有的员工 %匹配后续所有长度的

SELECT * from t_employees where FIRST_NAME like ‘L%’;

以L开头的，四个字符的名称

SELECT * from t_employees where FIRST_NAME like ‘L___’; #三个_

姓名以a结尾的员工

SELECT EMPLOYEE_ID,FIRST_NAME from t_employees where FIRST_NAME like ‘%a’;

员工按照薪资分几档

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,
case
when SALARY>10000 then ‘A’
when SALARY>8000 then ‘B’
when SALARY>6000 then ‘C’
when SALARY>4000 then ‘D’
else ‘E’
end as ‘薪资分类’
from t_employees

时间 当前系统时间 这个时间是数据库软件的时间

select sysdate();
select CURDATE();
select CURTIME();
select now();

获取week,YEAR hour ,MINUTE

select week(create_date) from t_date;

获取当前时间的所在年

select year(now());
select minute(now());
select hour(now());

日期间相隔了几天

select DATEDIFF(create_date,now()) from t_date;

当前时间+10天

select ADDDATE(now(),10);

时间比较

select * from t_date where create_date > ‘2019-01-01’
#员工的姓和名拼起来
select concat(FIRST_NAME,’_’,LAST_NAME) as totalName from t_employees;

变大写

select upper(FIRST_NAME) from t_employees;

替换 第二个参数是位置，从1，开始。第三个参数是原来的哪几个字符要替换

select insert(‘这是一个数据库’,3,2,‘JAVA’);

substring ( str,startPosition,number) 第二个参数是位置从1开始，第三个参数是截取几个

select SUBSTRING(‘这是一个数据库’,1,2);

聚合函数，单独使用的聚合使用

所有员工的工资总和

select sum(salary) from t_employees ;

员工的工资平均值

select avg(salary) from t_employees;
#查询员工的最大薪资
select max(salary) from t_employees;

最小薪资

select min(salary) from t_employees;

员工总数量

select count(salary) from t_employees;

count 不计算括号内的列的null

select count(*) from t_employees;#性能不好
select count(1) from t_employees;
select count(employee_id) from t_employees;
select count(MANAGER_ID) from t_employees;# 将null值行抹掉再计算

select * from t_employees

分组

取各个部门的总人数

select count(employee_id),DEPARTMENT_ID,EMPLOYEE_ID as ‘没意义’ from t_employees GROUP BY DEPARTMENT_ID

取各个部门的平均工资，最大工资，最小工资

select avg(salary) ,max(salary),min(salary),count(employee_id), DEPARTMENT_ID from t_employees group by DEPARTMENT_ID;

按照部门，岗位进行分组获取工资

select avg(salary),DEPARTMENT_ID,JOB_ID from t_employees group by DEPARTMENT_ID,JOB_ID

查询出部门要求大于一个员工的 ,带分组过滤功能

select count(employee_id),DEPARTMENT_ID from t_employees GROUP BY DEPARTMENT_ID HAVING count(EMPLOYEE_ID)>1

查询每个部门最高的工资 ,先分组再过滤

select DEPARTMENT_ID , max(salary) from t_employees GROUP BY DEPARTMENT_ID having DEPARTMENT_ID in (‘60’,‘70’,‘80’);

先过滤再分组

select DEPARTMENT_ID , max(salary) from t_employees where DEPARTMENT_ID in (‘60’,‘70’,‘80’) GROUP BY DEPARTMENT_ID;

限定查询，分页查询 limit

查一个工资最高的员工

select employee_id,salary from t_employees order by salary desc limit 1;

取工资最高的前五个人(第一页)

select employee_id,salary from t_employees order by salary desc limit 0,5 ;# 第一个是下标，从0开始，代表取第几行数据，第二个是取几个值

取工资最高的前五个人(第二页)

select employee_id,salary from t_employees order by salary desc limit 5,5 ;

select employee_id,salary from t_employees order by salary desc limit 10,5 ;
select employee_id,salary from t_employees order by salary desc limit 2,5 ;

子查询

查询工资大于Bruce的员工

取Bruce得工资

select EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where
SALARY > (select salary from t_employees where FIRST_NAME =‘Bruce’);

查询跟King在同一个部门的员工

返回多行一列，使用in来获取条件

select EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where DEPARTMENT_ID in
(
select DEPARTMENT_ID from t_employees where LAST_NAME =‘King’);

获取工资高于60部门的人的信息(会报错，原因是返回了多行一列)

select EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where
SALARY > (select salary from t_employees where DEPARTMENT_ID =‘60’);

大于所有工资

select EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where
SALARY > all (select salary from t_employees where DEPARTMENT_ID =‘60’);

大于所有2

select EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where
SALARY > (select max(salary) from t_employees where DEPARTMENT_ID =‘60’); #返回单行

大于某一个工资

select EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where
SALARY > any (select salary from t_employees where DEPARTMENT_ID =‘60’);
#大于某一个写法2
select EMPLOYEE_ID,FIRST_NAME,SALARY from t_employees where
SALARY > (select min(salary) from t_employees where DEPARTMENT_ID =‘60’);

获取年薪前五人的信息 (子查询返回的是多行多列的表) 子查询谨慎使用

select t1.EMPLOYEE_ID,t1.FIRST_NAME,t1.YEARSALARY from (
select EMPLOYEE_ID,FIRST_NAME,SALARY*12 as YEARSALARY from t_employees
) as t1
order by t1.YEARSALARY desc limit 0,5
;

表连接查询

笛卡尔积

select * from t_employees
inner join t_departments

查询所有员工所在的部门的相关信息 内连接

select t_employees.employee_id,t_employees.first_name ,t_departments.department_name from t_employees
inner join t_departments on t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID

左外连接

获取所有的员工信息，包含没有部门的

select t_employees.employee_id,t_employees.first_name ,t_departments.department_name from t_employees
left join t_departments on t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID

右外连接 获取所有的部门信息，包含没有员工的部门

select t_employees.employee_id,t_employees.first_name ,t_departments.department_name from t_employees
right join t_departments on t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID;

三表关联 t_employee,t_jobs,t_deparments 获取员工的岗位名称，部门名称

select t_employees.EMPLOYEE_ID,t_jobs.JOB_TITLE,t_departments.DEPARTMENT_NAME from t_employees
inner join t_jobs on t_jobs.JOB_ID = t_employees.JOB_ID
inner join t_departments on t_departments.DEPARTMENT_ID = t_employees.DEPARTMENT_ID