MySQL数据库(4)

-- -------------------------------------------------
-- mysql 中查询语句执行顺序：
开始-》From子句》where子句》Group by子句》Having子句》Order BY 子句》select子句》Limit子句》结果   
3、子查询【嵌套查询】
#子查询的概念
子查询是一个嵌套在另一个查询【主查询】内部中的查询，子查询可以达到的查询目的，使用其他多表连接查询同样可以实现，但子查询更容易阅读和编写；
1）、子查询出现在from子句中
-- 说明：把内层的子查询查询结果当成临时表,供外层sql语句再次查询
语法：
SELECT 查询列表1 FROM (SELECT 查询列表2 FROM 库名.表名 WHERE 条件表达式);
-- 外部查询的查询列表需要和子查询的查询列表具有相同字段或者为其子集

举例：查询hrdb数据库employees表工资大于7000的名字包括字母c的员工的first_name,salary
SELECT x.first_name,x.salary
FROM (SELECT first_name,salary FROM hrdb.`employees` WHERE salary>7000) X
WHERE first_name LIKE "%c%";

2）、子查询出现在where子句中
-- 说明：指把内层查询的结果作为外层查询的比较条件.
-- 语法：
SELECT 字段1 FROM 库名.表名
WHERE 字段1 关系运算符(SELECT 字段1 FROM 库名.表名 WHERE 字段2 关系运算符 字段值);
-- 关系运算符指的是大于号、小于号等。

举例：查询员工表中工资比"员工编号为104的员工的工资"低的员工的姓名、
员工编号、工资
SELECT first_name,last_name,employee_id,salary
FROM hrdb.`employees`
WHERE salary<(SELECT salary FROM hrdb.`employees` WHERE employee_id=104);

举例：查询员工表中"员工编号为100的员工所在部门的"部门名称
-- 子查询写法
SELECT department_name
FROM hrdb.`departments` 
WHERE department_id IN(SELECT department_id 
FROM hrdb.`employees` WHERE employee_id=100);
-- 内连接非标准写法
SELECT d.department_name
FROM hrdb.`employees` e,hrdb.`departments` d
WHERE e.`department_id`=d.`department_id`
AND e.`employee_id`=100;

3）、子查询出现在having子句中
-- 语法：
SELECT 查询列表1 FROM 库名.表名
GROUP BY 字段名
HAVING 字段名 比较运算符(select字段名 FROM 库名.表名 WHERE 条件);

举例：查询部门编号大于"first_name为Bruce的员工所在部门的部门编号"
的那些部门的部门编号、部门名称
SELECT department_id,department_name
FROM hrdb.`departments` 
GROUP BY department_id
HAVING department_id>(SELECT department_id FROM hrdb.`employees` 
WHERE first_name="Bruce");

#4)、子查询操作符ALL的用法
-- 说明：
ALL操作符有三种用法：
-- <>ALL(子查询语句)：等价于 NOT IN()
-- >ALL (子查询语句):比子查询中最大的值还要大
-- <ALL (子查询语句):比子查询中最小的值还要小
-- 特别说明：“= All”不存在
语法：
SELECT 字段1 FROM 库名.表名
WHERE 字段1 关系运算符 ALL(SELECT 字段1 FROM 库名.表名 WHERE 字段2 关系运算符 字段值);

举例：查询工资与"job_id为IT_PROG的职位中所有人工资"都不同的人员姓名、工资 (<>ALL 与任何一个都不相等)
SELECT first_name,last_name,salary
FROM hrdb.`employees`
WHERE salary<>ALL(SELECT salary FROM hrdb.`employees` WHERE job_id="IT_PROG");

SELECT first_name,last_name,salary
FROM hrdb.`employees`
WHERE salary NOT IN(SELECT salary FROM hrdb.`employees` WHERE job_id="IT_PROG");

举例：查询工资比job_id为IT_PROG的职位中所有人工资都要高的人员姓名、工资(>ALL 比最大的大)
SELECT first_name,last_name,salary
FROM hrdb.`employees`
WHERE salary>ALL(SELECT salary FROM hrdb.`employees` WHERE job_id="IT_PROG");

举例：查询工资比job_id为IT_PROG的职位中所有人工资都要低的人员姓名、工资(<ALL 比最小的小)
SELECT first_name,last_name,salary
FROM hrdb.`employees`
WHERE salary<ALL(SELECT salary FROM hrdb.`employees` WHERE job_id="IT_PROG");

5）、子查询操作符ANY的用法
-- =ANY[子查询语句]：与子查询中的数据任何一个相等
-- >ANY [子查询语句]: 比子查询中的数据最低的高 -- 比最小的大
-- <ANY[子查询语句]: 比子查询中的数据最高的低  -- 比最大的小
举例：查询工资与job_id为IT_PROG的职位中所有人工资相等的人员姓名、工资(=ANY 与任何一个相等)
SELECT first_name,last_name,salary
FROM hrdb.`employees`
WHERE salary=ANY(SELECT salary FROM hrdb.`employees` WHERE job_id="IT_PROG");

举例：查询工资比job_id为IT_PROG的职位中最低工资高的人员姓名、工资(>ANY 比最低的高)
SELECT first_name,last_name,salary
FROM hrdb.`employees`
WHERE salary>ANY(SELECT salary FROM hrdb.`employees` WHERE job_id="IT_PROG");

举例：查询工资比job_id为IT_PROG的职位中最高工资低的人员姓名、工资(<ANY 比最高的低)
SELECT first_name,last_name,salary
FROM hrdb.`employees`
WHERE salary<ANY(SELECT salary FROM hrdb.`employees` WHERE job_id="IT_PROG");

4、连接查询【内连接&外连接查询（重点）】
4.1 内连接查询【标准写法】
-- 说明：
（1）在进行跨表内连接查询数据时，查询结果只返回符合查询条件的数据；
（2）跨表内连接查询的结果和使用where、having的多表查询结果以及子查询的结果相同。
-- 语法：
SELECT 别名1.字段名1，别名2.字段名2
FROM 库名.表名1  [AS] 别名1
INNER JOIN 库名.表名2 [AS] 别名2  
ON 别名1.字段名3=别名2.字段名3；   
-- 解释：INNER JOIN，在这里是内连接的意思，inner内部的，join连接的意思
-- ON：后面跟查询条件
举例：查询员工表中，员工编号为100的员工所在部门的部门名称，
查询结果显示部门名称
-- 方法一
SELECT department_name
FROM hrdb.`employees` e,hrdb.`departments` d
WHERE e.department_id=d.department_id
AND employee_id=100;
-- 方法二(内连接标准写法)
SELECT department_name
FROM hrdb.`employees` e
INNER JOIN hrdb.`departments` d
ON e.department_id=d.department_id
AND employee_id=100;
-- 方法三
SELECT department_name
FROM hrdb.`employees` e
JOIN hrdb.`departments` d
ON e.department_id=d.department_id
AND employee_id=100;
-- 方法四
SELECT department_name
FROM hrdb.`employees` e
JOIN hrdb.`departments` d
ON e.department_id=d.department_id
WHERE employee_id=100;

举例:查询在美洲有哪些国家？
-- 分析
SELECT r.region_name,c.country_name
FROM hrdb.`regions` r
INNER JOIN hrdb.`countries` c
ON r.region_id=c.region_id
AND r.region_name="美洲";

举例：查询在城市Seattle中工作的人员有哪些(跨三个表查询数据)
-- 分析
-- 城市Seattle locations表
-- 工作的人员 employees 表
-- 关系？ 
SELECT e.employee_id,l.city
FROM hrdb.`locations` l
INNER JOIN hrdb.`departments` d
ON l.location_id=d.location_id
INNER JOIN hrdb.`employees` e
ON d.department_id=e.department_id
AND l.city="seattle";

举例：查询职位名称为Marketing Manager的职位，有哪些员工,查询结果显示员工编号、
      职位名称
SELECT employee_id,job_title
FROM hrdb.`jobs` j
INNER JOIN hrdb.`employees` e
ON j.job_id=e.job_id
AND j.job_title="Marketing Manager";

十一、外连接查询
包括：左外连接、右外连接、全外连接
1.左外连接(简称左连接)
-- 左表：在左连接查询中，最先出现的表为左表。
-- 概念：使用多表查询时，如果某个表中的数据需要全部显示，
-- 查询时，如果使用左连接进行查询，需要将这个表作为左表，查询完成后，
-- 左表的数据都全部显示在结果中，右表【除第一个表以外的表都称为右表】
-- 中符合查询条件的数据显示在查询结果中，-- 右表中不符合查询条件的数据，
-- 也显示在结果中，不符合条件的相关字段的数据以null填充。
-- 语法：
SELECT 别名1.字段名1，别名2.字段名2
FROM 库名.表名1  [AS] 别名1
LEFT JOIN 库名.表名2 [AS] 别名2
ON 别名1.字段名3=别名2.字段名3； 
-- 解释：left为左边的意思
-- on后的条件中不能出现where
举例：查询部门表中哪些部门没有员工,那些部门有员工,查询结果显示部门名称
、员工编号
SELECT department_name,employee_id
FROM hrdb.`departments` d
LEFT JOIN hrdb.`employees` e
ON d.department_id=e.department_id;

举例：查询locations表中的城市，有员工的城市显示出来，
没有员工的也显示出来，查询结果显示城市名称、员工编号（左连接）
SELECT city,employee_id
FROM hrdb.`locations` l
LEFT JOIN hrdb.`departments` d
ON l.location_id=d.location_id
LEFT JOIN hrdb.`employees` e
ON d.department_id=e.department_id;

2. 右外连接(简称右连接)查询
-- 右表：进行多表连接查询时，如果使用右连接查询，最后出现的表，称为右表；
-- 右连接概念说明：进行多表查询时，如果某个表中的数据需要全部显示，
-- 如果使用右连接查询时，需要将这个表作为右表，查询后，右表中的数据全部显示，左表【除右表以外的表都称为左表】中符合条件的数据显示在查询结果中，左表中不符合条件的数据，也显示在结果中，但相关数据以null填充。
-- 语法：
SELECT 别名1.字段名1，别名2.字段名2
FROM 库名.表名1  [AS] 别名1
RIGHT JOIN 库名.表名2 [AS] 别名2
ON 别名1.字段名3=别名2.字段名3；   -- 连接条件
-- Right：右边的
/*左右连接区别：
左连接查询结果，左表数据全部显示，右表数据，符合条件的显示，不符合条件的，也显示在结果中，但显示为null；右连接与其相反。
内连接和外连接区别：
内连接只返回符合条件的数据，外连接不但返回符合条件的数据，还返回不符合条件的数据，并且不符合条件的数据显示为null。*/

举例：查询部门表中哪些部门没有员工,哪些部门有员工，查询结果显示部门名称
、员工编号
SELECT d.department_name,e.employee_id
FROM hrdb.`employees` e
RIGHT JOIN hrdb.`departments` d
ON e.department_id=d.department_id;

SELECT department_name,employee_id
FROM hrdb.`departments` d
LEFT JOIN hrdb.`employees` e
ON d.department_id=e.department_id;

4.联合查询关键字UNION的用法

-- union用法举例
举例：查询员工表中员工编号分别为110和115的人员的工资、入职日期
SELECT salary,hire_date FROM hrdb.employees
WHERE employee_id IN(110,115);

SELECT salary,hire_date FROM hrdb.employees
WHERE employee_id=115
UNION
SELECT salary,hire_date FROM hrdb.employees
WHERE employee_id=110;

-- union all 用法举例： 
--  创建两个表，表结构和数据分别从employees复制：
CREATE TABLE hrdb.temp1 AS SELECT * FROM hrdb.employees;
CREATE TABLE hrdb.temp2 AS SELECT * FROM hrdb.employees;
SELECT * FROM hrdb.`temp1`
UNION  -- 合并重复数据
SELECT * FROM hrdb.`temp2`;

SELECT * FROM hrdb.`temp1`
UNION ALL -- 不合并重复数据
SELECT * FROM hrdb.`temp2`;

十二、表中数据的增、删、改（必须掌握-面试常被问到！！！！！！）
1、往表中插入数据
-- 语法：
INSERT INTO 库名.表名(字段名1，字段名2) VALUES(字段值1，字段值2);
INSERT INTO test.`goods`(gid,gname) VALUES(1,"lychee");
1.1插入数据时省略字段名
-- 插入的数据的顺序、个数、数据类型要和字段名匹配
INSERT INTO test.`goods` VALUES(2,"apple");
1.2插入数据时同时插入多条数据
INSERT INTO test.`goods` VALUES(3,"banana"),(4,"orange");
1.3 插入的数据来源于其他表
-- 联系准备
CREATE TABLE hrdb.test(id INT,sname VARCHAR(20) ,salary FLOAT(8,2)); 
INSERT INTO hrdb.`test`(id,sname,salary) SELECT employee_id,first_name,salary FROM hrdb.`employees`;
2、修改表中的数据
-- 语法：
UPDATE 库名.表名
SET 字段名1=新字段值1
WHERE 字段名2=字段值2；  -- where条件用来指定被修改的数据，如果不指定条件，将会导致表中所有数据都被修改
-- 解释：
update修改的意思，SET设置的意思，where条件的意思
UPDATE hrdb.`test`
SET salary=8400
WHERE sname="David";
2.1 同时修改多个字段的值
举例：修改hrdb库test表中编号为105的记录，讲姓名修改为zhangsan，工资修改为10000
UPDATE hrdb.`test`
SET sname="zhangsan",salary=10000
WHERE id=105;
2.2 在update语句中使用子查询 
举例：修改hrdb数据库test表中的数据，将Alexander的工资修改为与hrdb库employees表中Bruce的工资相同
UPDATE hrdb.`test`
SET salary=(SELECT salary FROM hrdb.`employees` WHERE first_name="Bruce")
WHERE sname="Alexander";
-- 特殊说明：子查询中的数据，不能来源于本表，否则报错。
2.3 修改用户密码
-- 说明：mysql中，root可以修改别人的密码，也可以修改自己的密码
-- 在mysql修改密码，分为两步：
-- 在mysql中修改指定用户的密码，就是修改mysql库user表中的数据
-- 步骤一、修改密码
-- 步骤二、使新密码生效
举例：修改当前本地用户root的密码为123456
步骤一、修改密码
UPDATE mysql.`user`
SET PASSWORD=PASSWORD("123456")
WHERE USER="root" AND HOST="localhost";
步骤二、使新密码生效
FLUSH PRIVILEGES;

3、删除表中的数据
-- 方法一：
-- 语法：
DELETE FROM 库名.表名 WHERE 字段名1=字段值1；（删除指定数据）
-- 重点说明：删除表中数据时，如果不加where默认删除表中所有数据(清空表)
-- 重点说明：使用delete删除表中数据时，delete后不能跟*号，delete不能和*号搭配使用。
-- delete 删除的意思
-- FROM 库名.表名：删除哪个表中的数据
-- WHERE 字段名1=字段值1：意思是删除字段名1的值为字段值1对应的数据（对应的行）
举例：删除goods表中食品名称为lychee的记录
DELETE FROM test.`goods`
WHERE gname="lychee";
举例：删除hrdb数据库test表中的数据，将编号为100和103的记录删除
DELETE FROM hrdb.`test` WHERE id IN(100,103);
举例：清空temp表中的所有数据
-- 方法一
DELETE FROM hrdb.`temp1`;
-- 方法二
TRUNCATE hrdb.`temp2`; 

-- TRUNCATE:清空的意思

十三、用户及权限管理（非重点）
MySQL用户分类：
-- mysql用户分类角度1(权限不同)
(1) 管理员用户：root,具有最高权限，具有创建用户的权限，可以为其他用户授权
(2) 普通用户：普通用户由root用户创建，权限由root用户分配
-- mysql用户分类角度2(访问主机不同)
(1)本地用户 --  只能在安装mysql的机器上访问mysql数据库的用户,在创建用户时，使用的IP地址为localhost
(2)远程访问用户 -- 在安装mysql的机器以外的机器上访问mysql数据库的相关用户
(2.1) 只能在指定IP对应的机器上访问远程mysql数据库的用户，创建用户时，只能使用指定的具体IP地址：如172.30.70.149所在的机器访问远程数据库
(2.2) 在除安装mysql数据库对应机器以外的任何一台机器上可以访问远程mysql数据库的用户 ，在创建用户时，使用的IP地址为百分号% 
-- 重点说明：
   -- 在mysql中，添加一个可用的新用户，共分为三步：
   步骤一：添加用户【在mysql数据库的user表中添加】
   步骤二：为用户授权 
   步骤三：使授权生效
1、用户创建
-- 只能在mysql所在的机器上创建用户
-- 创建用户时需要使用具有root权限的用户创建用户

语法：
INSERT INTO mysql.`user`(HOST,USER,PASSWORD) VALUES("IP地址","用户名",PASSWORD("密码"));  -- 创建用户
-- PASSWORD() 加密函数
举例：添加[本地用户]test，密码为123456，ip地址为localhost，
为用户test授予所有权限，使该用户可以对指定数据库做所有操作
步骤一：添加用户【在mysql数据库的user表中添加】
INSERT INTO mysql.user(HOST,USER,PASSWORD) VALUES("localhost","test",PASSWORD("123456"));
   步骤二：为用户授权 
GRANT ALL PRIVILEGES ON *.* TO "test"@"localhost" IDENTIFIED BY "123456"; 
   步骤三：使授权生效
FLUSH PRIVILEGES;

举例：创建远程访问用户test1，为该用户在数据库hrdb中的所有表上授予select、insert权限，
使该用户在远程任何一台主机上可以访问数据库，该用户的密码为123456
INSERT INTO mysql.user(HOST,USER,PASSWORD) VALUES("%","test1",PASSWORD("123456"));
GRANT SELECT,INSERT ON hrdb.* TO "test1"@"%" IDENTIFIED BY "123456" WITH GRANT OPTION;
FLUSH PRIVILEGES; -- 相当于重启mysql服务

4、用户权限回收
语法：
REVOKE 权限 ON `数据库`.数据库对象 FROM '用户名'@'数据库所在主机IP'
说明：只有具有root用户权限的用户才有权限回收其他用户的权限
举例：回收用户test1在本地主机上的所有权限
步骤一、查看需要回收权限的用户的权限
(1)查看当前登录用户的权限
SHOW GRANTS;
(2)查看指定用户的权限
SHOW GRANTS FOR 用户名@"IP地址";
步骤二、回收权限
REVOKE SELECT,INSERT ON hrdb.* FROM "test1"@"%";
步骤三、使权限回收生效
FLUSH PRIVILEGES;

-- 查看远程访问用户test1的权限
SHOW GRANTS FOR test1@"%";

-- REVOKE：取消，废除
5、用户删除
-- 语法：
DROP USER 用户名@主机IP地址;
(1)删除主机IP为百分号%的远程用户：
DROP USER 用户名;
DROP USER test1;
(2)删除指定IP地址的远程用户：
DROP USER 用户名@"具体指定IP地址";
(3)删除本地用户
DROP USER 用户名@localhost;
DROP USER pma@localhost;

十四、函数条件查询（非重点）

1、字符大小写转换函数
SELECT first_name,UPPER(first_name) 小写变大写,LOWER(first_name) 大写变小写 FROM hrdb.`employees`;

2、字符控制函数
SELECT first_name,last_name,CONCAT(first_name,".",last_name),SUBSTR(first_name,2,2)
FROM hrdb.`employees`;
-- CONCAT(first_name,".",last_name)：字符串连接

-- SUBSTR(first_name,2,2):截取字符串

SELECT first_name,last_name,CONCAT(E.FIRST_NAME,".",E.LAST_NAME) "Full Name", -- 将fistname和lastname使用点连接起来
       E.JOB_ID,
       TRIM(E.first_name), -- 删除first_name字段值两边的空格
       LENGTH(E.LAST_NAME), -- 获取lastname的字符长度
       LPAD(E.SALARY,11, '*'),  -- 显示11位，不够左边补*号
       RPAD(E.SALARY,12, '$') -- 显示12位，不够右边补$号
FROM HRDB.EMPLOYEES E
WHERE SUBSTRING(JOB_ID, 4) = 'REP';  -- JOB_ID从左边数第四位开始是REP这个字符串

3、数字处理函数
SELECT ROUND(99.878,2) 四舍五入,TRUNCATE(56.788,2) 在指定小数点后保留位数的情况下舍弃多余数字,MOD(15,3) 求余数;
4、日期处理函数
-- 获取系统当前的日期时间
SELECT SYSDATE(),NOW(); 
-- 获取系统的当前时间
SELECT CURTIME(); 
 -- 判断给定的时间日期属于礼拜几
SELECT WEEKDAY(SYSDATE()); 
-- 判断给定的时间日期属于礼拜几，
SELECT WEEKDAY("2018-11-11"); 
-- 判断给定的日期时间属于几月份 
SELECT MONTHNAME("2018-11-11"); 
SELECT MONTHNAME(NOW()); 
 -- 按照指定的格式显示给定的日期时间
SELECT DATE_FORMAT("2018-11-11","%Y/%m/%d");
-- 按照指定的格式显示给定的日期时间
SELECT DATE_FORMAT(NOW(),"%Y-%m-%d %H:%i:%s"); 

十五、使用SQL语句备份/导出数据库数据：
#备份/导出数据库数据：
##使用 sqlyog备份/导出数据库中数据：
###手工操作步骤：
选择一个数据库》在上面右击鼠标》备份/导出》备份数据库，转储到sql...》选择导出路径并命名导出文件》点击导出按钮》确认导出》点击完成

使用SQL语句导出本地数据库数据：
-- 导出本地数据库数据
-- 语法：
mysqldump -u 用户名 -p 库名 >保存目录:\导出后生成的文件名.sql
举例：
mysqldump -u root -p hrdb >c:\hrdb1.sql (退出数据库后执行)
> 重定向符号
-- 导出远程数据库数据
-- 语法：
mysqldump -h ip地址 -u root -p mysql >c:\mysql.sql (退出数据库后执行)
导入数据库数据

使用 sqlyog将数据从导出的sql脚本文件中导入数据库：
手工操作步骤：
在sqlyog左侧目录树中单击鼠标右键》在弹出的右键菜单中选择“执行sql脚本”》选择脚本》执行SQL脚本》完成

使用SQL语句将备份/导出的数据库数据导入数据库：

前提:导入数据库的前提条件是，导入语句中目标库的库名在mysql中存在

-- 将导出的数据库文件中的数据导入到数据库中
-- 语法：
mysql -u 用户名 -p 目标库 <"c:\数据库文件.sql"
mysql -u 用户名 -p 库名 <"c:\数据库文件.sql"
举例：
mysql -u root -p hrdb <"c:\hrdb.sql" (退出数据库后执行)