MySQL数据库(3)

九、表中数据的操作(重点)
#查询表中的数据(重中之重) --测试时常用
##查询单个表中的数据
-- 结构化查询语言-SQL语言
/*说明： 
 结构化查询语言（Structured Query Language或简称为SQL）,即SQL语言，是一种应用最为广泛的关系数据库语言，该语言定义了操作关系数据库的标准语法，几乎所有的关系数据库管理系统都支持SQL语言。【关系型数据库：mysql、oracle、SQL server、DB2、sybase】
SQL语言分类：
下面的五大分类要能讲出来（面试题）
DDL语言(数据定义语言) - Create、Alter、Drop、truncate
DQL语言(数据查询语言) - Select 查询语句
DML语言(数据操纵语言) -对表中数据 Insert、Update、Delete 
TCL语言(事务控制语言) - Commit(提交)、Rollback(回滚) 事务提交与回滚语句
DCL语言(数据控制语言) - Grant(授权)、Revoke(回收权限) 授予权限与回收权限语句*/
-- Select语句语法如下：
(1)SELECT 字段列表
(2)FROM 数据表
(3)[ where条件 ]
(4)[ GROUP BY 分组字段]
(5)[ having条件]
(6)[ ORDER BY 排序字段 [ ASC | DESC ] ]
(7)[ LIMIT [START],LENGTH]; 
【划重点】：select语句中，各子句出现的顺序不能乱，否则报错
-- 解释：
-- select ：查询的意思
-- 字段列表包括：
-- * 代表查询表中所有字段的值
-- 单个字段：查询所有行对应该字段的值
-- 逗号隔开的多个字段：查询给出行对应的相应字段的值
-- 关系运算符：等于号=，大于号>,小于号<,不等于号!=、<>,大于等于>= 、小于等于<=
-- 数***算符  加+、减-、乘*、除/
-- 逻辑运算符：not 非，and 并且，or 或
-- not 表达式：取表达式计算结果相反的数据
条件1 AND 条件2：查询结果返回的值同时符合条件1和条件2
-- 条件1 or 条件2：查询结果中的数据，如果只符合条件1，那么结果只显示符合条件1相关数据；如果查询结果数据只符合条件2，那么结果中只显示符合条件2的相关数据，如果两个条件都符合，那么结果显示符合两个条件的所有数据。

举例：查询员工表（employees）中所有人员的所有信息
SELECT * FROM hrdb.`employees`;

举例：查询employees表中所有人员的姓、名
SELECT first_name,last_name FROM hrdb.employees;

举例：查询员工表中员工编号为101的员工的员工编号、工资
SELECT employee_id,salary
FROM hrdb.`employees`
WHERE employee_id=101;
#1、 关系运算符 大于号>、小于号<、不等于号!=\<>、大于等于号>=、小于等于号<=的用法
举例：在员工表查询工资大于9000的人员的姓名、工资
SELECT first_name,last_name,salary
FROM hrdb.`employees`
WHERE salary>9000;

举例：查询工资大于等于9000的人员的工资、姓、部门编号
SELECT salary,last_name,department_id
FROM hrdb.`employees`
WHERE salary>=9000;

SELECT `last_name`,`salary`,`department_id`
FROM hrdb.`employees`
WHERE `salary`>9000 OR `salary`=9000;

2、 ###逻辑运算符not(非)|and(并且)|or(或)的用法 
举例：在员工表中，查询工资大于9000，部门编号小于102的人员的员工编号、入职日期、工资、部门编号
SELECT employee_id,hire_date,salary,department_id
FROM hrdb.`employees`
WHERE salary>9000 AND department_id<102;

举例：查询员工表中部门编号分别为60和90的人员的工资、部门编号、员工编号
SELECT salary,department_id,employee_id
FROM hrdb.`employees`
WHERE department_id=60 OR department_id=90;

举例：查询员工表中工资不等于9000的人员的工资、姓、部门编号
方法一：
SELECT salary,last_name,department_id
FROM hrdb.`employees`
WHERE NOT salary=9000; -- not用法
方法二：
SELECT salary,last_name,department_id
FROM hrdb.`employees`
WHERE salary!=9000;
方法三：
SELECT salary,last_name,department_id
FROM hrdb.`employees`
WHERE salary<>9000;

#3、关键字in在查询语句中的用法
-- 说明：当查询条件给出的为多个类似的值时，可以使用关键字IN，
-- 不同值之间使用逗号隔开，in后跟数据列表，匹配其中任何一条数据的记录都将显示在查询结果中。
举例：查询员工表中部门编号分别为60和90的人员的工资、部门编号、员工编号
SELECT salary,department_id,employee_id
FROM hrdb.`employees`
WHERE department_id IN(60,90);

举例：查询员工表中职位编号分别为ST_MAN、PU_CLERK、SA_REP的人员的职位编号和工资
-- 方法一
SELECT job_id,salary
FROM hrdb.`employees`
WHERE job_id="ST_MAN" OR job_id="PU_CLERK" OR job_id="SA_REP";

-- 方法二
SELECT job_id,salary
FROM hrdb.`employees`
WHERE job_id IN("ST_MAN","PU_CLERK","SA_REP");

4、关键字between...and...的用法
说明：between...and...意思是在...和...之间
-- between 值1 and 值2，意思是在值1和值2之间

举例：查询工资在3000-6000之间的人员的工资、员工编号、职位编号
-- 方法一
SELECT salary,employee_id,job_id
FROM hrdb.`employees`
WHERE salary>=3000 AND salary<=6000;
-- 方法二
SELECT salary,employee_id,job_id
FROM hrdb.`employees`
WHERE salary BETWEEN 3000 AND 6000;

#5、null关键字在查询语句中的用法
说明：null为空、不确定的意思
-- 说明：一般在查询语句的where条件中进行条件判断，is null代表为空的意思， is not null为非空的意思，不能使用=null

举例：查询部门表中经理编号为空的部门的编号、部门名称、经理编号
SELECT department_id,department_name,manager_id
FROM hrdb.`departments`
WHERE manager_id IS NULL;

举例：查询部门表中经理编号不为空的部门的编号、部门名称、经理编号
SELECT department_id,department_name,manager_id
FROM hrdb.`departments`
WHERE manager_id IS NOT NULL;

#6、聚合函数的用法
-- 说明：聚合函数即分组函数，主要包括：
/*求最大值max()函数
最小值min()函数、
平均值avg()函数
求和sum()函数
统计条数count()函数*/
举例：查询员工表中的最高工资、最低工资、平均工资、工资总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM hrdb.`employees`;

SELECT MAX(salary) AS 最高工资,MIN(salary) AS 最低工资,AVG(salary) AS 平均工资,
SUM(salary) AS 工资之和
FROM hrdb.`employees`;

-- MAX(salary) AS 最高工资：最高工资是函数MAX(salary)的别名
-- AS:是单词alias的简写，这里是别名的意思，别名就是类似于一个人的小名，as可以省略

举例：查询员工表中的所有人员的工资之和以及人员的数量
-- 方法一
SELECT SUM(salary),COUNT(employee_id)
FROM hrdb.employees;
-- 方法二
SELECT SUM(salary),COUNT(*)
FROM hrdb.employees;
-- 方法三
SELECT SUM(salary),COUNT(1)
FROM hrdb.employees;

举例：统计部门表中部门的个数
SELECT COUNT(*) FROM hrdb.`departments`; -- 统计结果为27条
SELECT COUNT(1) FROM hrdb.`departments`;-- 统计结果为27条
SELECT COUNT(manager_id) FROM hrdb.`departments`; -- 统计结果为11条
-- 统计结果只统计到manager_id不为空的记录

#7、分组查询关键字group by用法
-- group：组，分组
-- by：通过...
-- group by...:通过...进行分组
-- 说明：group by 字段名/聚合函数， 意思是先通过给定的【字段名/聚合函数】先进行分组，再对分组后的数据进行分别处理。
-- 查询时，什么时候需要对数据进行分组？
-- 一般查询问题为“每个...”，“各个...”的时候，需要对数据进行分组,比如统计每个部门的平均工资时，就需要分组。

举例：查询员工表中每个部门人员的平均工资，查询结果显示部门编号、平均工资
SELECT department_id,AVG(salary)
FROM hrdb.`employees`
GROUP BY department_id; -- 通过部门编号分组，先分组，再对每个组中的数据，求平均值

举例：查询员工表中每个职位人员的平均工资，查询结果显示职位编号、平均工资
SELECT job_id,AVG(salary)
FROM hrdb.`employees`
GROUP BY job_id;

#8、使用group by关键字进行多字段分组
举例：查询不同部门中，不同职位的人员的平均工资,查询结果显示部门编号、
      职位编号、平均工资
SELECT department_id,job_id,AVG(salary)
FROM hrdb.`employees`
GROUP BY department_id,job_id;   

举例：查询部门编号为70的部门的人员的工资之和
-- 方法一
SELECT SUM(salary) FROM hrdb.`employees` WHERE department_id=70;
-- 方法二
SELECT SUM(salary),department_id 
FROM hrdb.`employees`
GROUP BY department_id
HAVING department_id=70; -- having用来对分组后的数据进行过滤

9、对group by分组后的结果数据使用having关键字进行过滤
-- 说明：having关键字，可以对分组后的数据进行过滤，类似于where的作用，但又不同于where.
-- having 后的过滤条件，可以跟字段，也可以跟分组函数【avg()等】。

举例：查询员工表中"不同部门"人员的平均工资，查询结果要求只显示
平均工资高于12000的部门编号、平均工资
SELECT department_id,AVG(salary)
FROM hrdb.`employees`
GROUP BY department_id
HAVING AVG(salary)>12000;

举例：查询员工表中不同部门人员的平均工资，查询结果要求只显示
      部门编号低于60的部门编号、平均工资
-- 分析思路：
分组-过滤
SELECT department_id,AVG(salary)
FROM hrdb.`employees`
GROUP BY department_id
HAVING department_id<60;

#10、对查询结果使用order by关键字进行排序
-- 说明：mysql中查询，查询结果默认按升序（从上到下，按从小到大）排列
-- 对查询结果进行升序排列：
-- 方法一：不加排序关键字
-- 方法二：使用关键字 ASC
-- 对查询结果进行降序排列：
-- 使用关键字DESC
-- 排序关键字使用语法：order by 字段名 [asc|desc]，意思是通过给定的字段名将查询结果进行排序（升序或者降序）
-- order，顺序，排序
-- by,通过...的意思
-- order by...意思是通过给定的条件进行排序
-- order by后可以跟:字段名，别名，函数名
举例：查询员工表中所有人员的工资，查询结果按工资高低升序排列，结果显示员工编号、工资
-- 方法一 默认升序排列 
 SELECT employee_id,salary
 FROM hrdb.`employees`
 ORDER BY salary;
-- 方法二 使用升序排列关键字 asc 
SELECT employee_id,salary
FROM hrdb.`employees`
ORDER BY salary ASC;

举例：查询"不同部门"的平均工资，查询结果按"平均工资"高低进行"降序排列"，
查询结果显示部门编号、平均工资
SELECT department_id,AVG(salary)
FROM hrdb.`employees`
GROUP BY department_id
ORDER BY AVG(salary) DESC;

SELECT department_id,AVG(salary) AS a
FROM hrdb.`employees`
GROUP BY department_id
ORDER BY a DESC; -- 函数的别名可以在sql语句中代替对应的函数

#11、查询关键字limit的用法-分页查询
-- 说明：limit限制的意思，在sql语句可以对查询结果进行过滤
-- 在mysql查询结果中，limit m,n,意思是从第m+1条数据开始取数据，连续取n条数据，显示在查询结果中。
-- 在mysql查询结果中，limit 2,1,意思是从第2+1条数据开始取数据，连续取1条数据，显示在查询结果中。

-- limit关键字一般和order by 配合使用
举例：查询员工表中所有人员的工资，查询结果按工资高低降序排列，
结果显示工资前四高的员工编号、工资
SELECT employee_id,salary
FROM hrdb.`employees`
ORDER BY salary DESC
LIMIT 4;

举例：查询员工表中所有人员的工资，查询结果按工资高低降序排列，
结果显示工资第四高的员工编号、工资
SELECT employee_id,salary
FROM hrdb.`employees`
ORDER BY salary DESC
LIMIT 3,1; -- 从第三条数据的下一条数据开始取，只取一条

举例：查询员工表中所有人员的工资，查询结果按工资高低降序排列，
结果显示工资第5名和第6名的员工编号、工资
SELECT employee_id,salary
FROM hrdb.`employees`
ORDER BY salary DESC
LIMIT 4,2;

举例：查询"不同部门"的平均工资，查询结果按平均工资高低进行"降序排列"，
查询结果"显示前4条"数据的部门编号、平均工资
SELECT department_id,AVG(salary)
FROM hrdb.`employees`
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 4; -- 相当于limit 0,4;

举例：查询"不同部门"的平均工资，查询结果显示平均工资高于8000的数据，
查询结果按平均工资高低进行"降序排列"，只显示"前4条数据"的部门编号、平均工资
SELECT department_id,AVG(salary) a
FROM hrdb.`employees`
GROUP BY department_id
HAVING a>8000 
ORDER BY a DESC
LIMIT 4;

#12、使用like关键字进行模糊查询
-- 说明：模糊查询，使用查询关键字like，like意思是类似于，像...的意思
-- 模糊查询，支持两种字符匹配符号：
-- 下划线_:下划线可以代替任意单个字符
-- 百分号%：可以代替任意个任意字符(0个、1个或者多个字符)
-- 百分号和下划线可以组合到一起与like关键字配合使用

举例：查询员工表中员工编号第二位为2的员工的编号、姓名
SELECT employee_id,first_name,last_name
FROM hrdb.`employees`
WHERE employee_id LIKE "_2%";

举例：查询员工表中员工编号第三位为2的员工的编号、姓名
SELECT employee_id,first_name,last_name
FROM hrdb.`employees`
WHERE employee_id LIKE "__2%";

举例：查询员工表中first_name第一位为a，第三位为i的员工编号、姓名
SELECT employee_id,first_name,last_name
FROM hrdb.`employees`
WHERE first_name LIKE "a_i%";

举例：查询"不同职位"的平均工资，要求查询"职位编号包括a"，
职位"平均工资高于3000"的职位编号，
查询结果按平均工资高低进行"降序排列"，"只显示前2条"数据的职位编号、平均工资。
-- 分组-》模糊查询-》过滤-》排序-》limit
-- 方法一
SELECT job_id,AVG(salary)
FROM hrdb.`employees`
GROUP BY job_id
HAVING AVG(salary)>3000 AND  job_id LIKE "%a%"
ORDER BY AVG(salary) DESC
LIMIT 2;

-- 方法二
SELECT job_id,AVG(salary)
FROM hrdb.`employees`
WHERE job_id LIKE "%a%"
GROUP BY job_id
HAVING AVG(salary)>3000 
ORDER BY AVG(salary) DESC
LIMIT 2;

#13、查询关键字distinct的用法
-- 说明：distinct意思是有区别的，这里用于在查询时过滤掉重复数据
-- distinct 一般和统计条数函数count()配合使用

举例：查询员工表中有哪些职位分类,要求查询结果不显示重复数据
SELECT DISTINCT job_id FROM hrdb.employees;

举例：查询员工表中不同职位的个数，要求查询结果过滤掉重复数据
SELECT COUNT(DISTINCT job_id) FROM hrdb.`employees`;

举例：查询员工表中不同部门中的不同职位[过滤掉重复数据]，查询结果显示部门编号、职位编号
SELECT DISTINCT department_id,job_id
FROM hrdb.`employees`;
-- 注意:distinct 关键字只能出现在紧靠select关键字的地方
15、多字段排序
###ORDER BY多字段排序
-- 多字段排序，就是排序时，可以同时使用多个字段对查询结果数据先后进行排序，
-- "排序字段"之间使用逗号隔开
-- 语法：
ORDER BY 字段1 [[DESC]|[ASC]] ,字段2 [[DESC]|[ASC]] 

举例：查询员工表中不同员工的薪资，查询结果先按部门编号降序排列，再按薪资高低降序排列
SELECT department_id,salary
FROM hrdb.`employees`
ORDER BY department_id DESC,salary DESC;

十、查询多个表中的数据（必须掌握！！！！！！！-面试笔试常考）
1、笛卡尔积查询：(了解)
从如下两个表中同时查询数据：
SELECT * FROM hrdb.`employees`; -- 107
SELECT * FROM hrdb.`departments`; -- 27
SELECT * FROM hrdb.`employees`,hrdb.`departments`;  -- 2889
SELECT 107*27;


#17、查询的优先级
优先级顺序：
高：()>NOT>AND>OR 低
举例：查询工资低于8000或者高于12000，员工编号大于120的人员的工资、部门编号、员工编号
SELECT salary,department_id,employee_id
FROM hrdb.`employees`
WHERE (salary<8000 OR salary>12000) AND employee_id>120;

十、查询多个表中的数据（必须掌握！！！！！！！-面试笔试常考）
1、笛卡尔积查询：(了解)
从如下两个表中同时查询数据：
SELECT * FROM hrdb.`employees`; -- 107
SELECT * FROM hrdb.`departments`; -- 27
SELECT * FROM hrdb.`employees`,hrdb.`departments`;  -- 2889
SELECT 27*107;
2、内连接查询【非标准写法】

##通过连接条件查询两个表的数据结果示例如下：（理解）


·-- 跨表查询步骤：
步骤一：分析需要查询的数据和已知数据分布在哪些表中
步骤二：分析需要查询的数据所在表和所给条件相关数据所在表之间有哪些联系（找两个表的相同字段，存在，即表示两个表有直接关系）
直接关系：两个表之间有数据引用(两个表有相同字段)
间接关系：两个表之间通过其他表有数据引用关系（两两之间有共同字段）
步骤三：结合已知条件，写查询需要的SQL语句
#### 别名(alias) 
-- 说明：在sql语句中，表的别名可以代替对应表，字段的别名可以代替对应字段，
-- 函数的别名可以代替对应函数
-- 别名作用：可以减少sql语句编写的工作量，标识数据来源

举例：查看员工表中，员工编号为100的员工所在的部门的部门名称
SELECT e.employee_id,d.department_name
FROM hrdb.`employees` e,hrdb.`departments` d
WHERE e.department_id=d.department_id AND e.employee_id=100;

举例：查询职位表中，职位名称为Accounting Manager的职位，
有哪些员工,查询结果显示职位名称、员工编号

employees>job_id<jobs

SELECT j.job_title,e.employee_id
FROM hrdb.`jobs` j,hrdb.`employees` e
WHERE j.job_id=e.job_id AND j.job_title="Accounting Manager";

举例：查询员工表中last_name为Fay的员工所在的部门的名称
-- 已知数据Fay在employees
-- 待求数据部门的名称在部门表departments
-- `department_id`
SELECT d.department_name,e.last_name
FROM hrdb.`employees` e,hrdb.`departments` d
WHERE e.department_id=d.department_id
AND e.last_name="Fay";

举例：查询在城市Seattle中工作的人员有哪些,查询结果显示城市名称、
员工姓名(跨三个表查询数据)
SELECT l.city,e.first_name,e.last_name 
FROM locations l,hrdb.`departments` d,hrdb.`employees` e
WHERE l.location_id=d.location_id AND d.department_id=e.department_id
AND l.city="Seattle";

举例：查询在United States of America工作的员工有哪些人
-- 思路
-- 已知数据：United States of America
-- 美国在哪个表？countries
-- 待求得数据：employee_id
-- 员工编号在哪个表？employees
-- 关系 ？countries>country_id>locations>location_id>departments>department_id>employees

SELECT e.employee_id,e.first_name,e.last_name,c.country_name
FROM hrdb.`countries` c,hrdb.`locations` l,hrdb.`departments` d,hrdb.`employees` e
WHERE c.country_id=l.country_id AND l.location_id=d.location_id 
AND d.department_id =e.department_id
AND c.country_name="United States of America";

举例：求工资总和比"部门名称为Finance的部门的工资总和"高的，那些部门的部门编号、工资总和，
查询结果以工资总和高低降序排列，取前两条数据
SELECT department_id,SUM(salary)
FROM hrdb.`employees`
GROUP BY department_id
HAVING SUM(salary)>(SELECT SUM(e.salary) FROM hrdb.`departments` d,hrdb.`employees` e
WHERE e.department_id=d.department_id AND d.department_name="Finance")
ORDER BY SUM(salary) DESC
LIMIT 2;

步骤一、查询出部门名称为Finance的部门的工资总和
SELECT SUM(e.salary) FROM hrdb.`departments` d,hrdb.`employees` e
WHERE e.department_id=d.department_id AND d.department_name="Finance"
步骤二、求出最终结果

SELECT department_id,SUM(salary)
FROM hrdb.`employees`
GROUP BY department_id
HAVING SUM(salary)>51600
ORDER BY SUM(salary) DESC
LIMIT 2;

SELECT SUM(salary),d.department_id
FROM hrdb.`departments` d,hrdb.`employees` e
WHERE d.`department_id`=e.`department_id`
GROUP BY department_id
HAVING SUM(salary)>SUM(d.`department_name`="Finance")
ORDER BY SUM(salary) DESC
LIMIT 2;