#查询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