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;