爱站程序员基地MySQL 分类
DQL语言：Data Query Language 数据查询语言selectDML语言：Data Manipulate Language 数据操纵语言insert、update、deleteDDL语言：Data Define Language 数据定义语言create、alter、dropDCL语言：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_MANSELECT *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_namefrom employees e,departments dwhere e.department_id = d.department_id;
②为表起别名
SELECT last_name 员工名,department_name 部门名from employees e,departments dwhere e.department_id=d.department_id
③添加筛选条件
案例：查询 工资>5000的工种名和员工名、工资SELECT job_title,last_name,salaryfrom employees e,jobs jwhere e.job_id=j.job_id and salary>5000
④添加分组和筛选
01案例：查询每个部门的员工个数和部门名SELECT count(*) 个数,department_namefrom employees e,departments dwhere e.department_id=d.department_idGROUP BY e.department_id;
⑤排序
01案例：查询每个部门的员工个数和部门名，每个部门人数大于3，按照人数倒序排序SELECT count(*) 个数,department_namefrom departments d,employees ewhere e.department_id=d.department_idGROUP BY e.department_idHAVING count(*)>3ORDER BY COUNT(*) DESC
⑥ 三表连接
案例：查询员工名、部门名和所在城市SELECT last_name,department_name,cityfrom employees e,departments d,locations lwhere e.department_id=d.department_id and d.location_id=l.location_id;
