ST-6-测试-Mysql学习-4

12. 连接查询【内连接&外连接查询（重点）】

12.1内连接【等值连接】查询（INNER JOIN）

-- 说明：

（1）在进行跨表内连接查询数据时，查询结果只返回符合查询条件的数据；

（2）跨表内连接查询的结果和使用 where 的多表查询结果以及子查询的结果相同。

-- 语法：

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 e.employee_id,d.department_name

FROM hrdb.`employees` AS e

INNER JOIN hrdb.`departments` AS d

ON e.department_id=d.department_id

WHERE e.employee_id=100;

举例:查询在美洲有哪些国家？

SELECT r.region_name,c.country_nameFROM hrdb.`countries` AS c

INNER JOIN hrdb.`regions` AS r

ON r.region_id=c.region_id AND r.region_name="美洲";

举例：查询员工表中 last_name 为 Fay 的员工所在的部门的名称

SELECT e.last_name,d.department_name

FROM hrdb.`employees` AS e

INNER JOIN hrdb.`departments` AS d

ON e.department_id=d.department_id

AND e.last_name="Fay";

举例：查询在城市 Seattle 中工作的人员有哪些(跨三个表查询数据) -- 分析：

-- 跨几个表？employees、departments、locations

-- Seattle 在 locations 表中

-- 人员信息在 employees 表中

-- 找 locations 表和 employees 表的关系

-- employees 表和 departments 表通过字段 department_id 可以建立关系

-- 找 locations 表和 employees 表的关系

-- locations 表和 departments 通过字段 location_id 可以建立关系

-- SQL 语句如下：

SELECT l.city,e.employee_id

FROM hrdb.`locations` AS l

INNER JOIN hrdb.`departments` AS d

ON l.location_id=d.location_idINNER JOIN hrdb.`employees` AS e

ON d.department_id=e.department_id

WHERE l.city="Seattle";

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

方法一：

SELECT j.job_title AS 职位名称,e.employee_id AS 员工编号

FROM hrdb.`jobs` j,hrdb.`employees` e

WHERE j.job_id=e.job_id

AND j.job_title="Marketing Manager";

方法二：

SELECT j.job_title,e.employee_id

FROM hrdb.`jobs` AS j

INNER JOIN hrdb.`employees` AS e

ON j.job_id=e.job_id

WHERE j.job_title="Marketing Manager";

12.2 外连接查询（LEFT JOIN、RIGHT JOIN、FULL JOIN）

包括：左外连接、右外连接、全外连接

12.2.1 左外连接(简称左连接  LEFT JOIN)

概念：使用多表查询时，如果某个表中的数据需要全部显示，

-- 查询时，如果使用左连接进行查询，需要将这个表作为左表，查询完成后，左表的数据都全部显示在结果中，右表【除第一个表以外的表都称为右表】中符合查询条件的数据显示

在查询结果中，-- 右表中不符合查询条件的数据，也显示在结果中，不符合条件的相关字

段的数据以 null 填充。

-- 语法：

SELECT 别名 1.字段名 1，别名 2.字段名 2

FROM 库名.表名 1 [AS] 别名 1

LEFT JOIN 库名.表名 2 [AS] 别名 2

ON 别名 1.字段名 3=别名 2.字段名 3；

-- 解释：left 为左边的意思

举例：查询部门表中哪些部门没有员工,那些部门有员工,查询结果显示部门名称、员工编号

SELECT d.department_name,e.employee_id

FROM hrdb.`departments` d

LEFT JOIN hrdb.`employees` e

ON d.department_id=e.department_id;

举例：查询 locations 表中的城市，有员工的城市显示出来，没有员工的也显示出来，查询

结果显示城市名称、员工编号（左连接）

SELECT l.city,e.employee_id

FROM hrdb.`locations` l

LEFT JOIN hrdb.`departments` d

ON l.location_id=d.`location_id`

LEFT JOIN hrdb.`employees` eON d.department_id=e.department_id;

12.2.2 右外连接(简称右连接  RIGHT JOIN)查询（概念背诵）

-- 右表：进行多表连接查询时，如果使用右连接查询，最后出现的表，称为右表；

-- 说明：进行多表查询时，如果某个表中的数据需要全部显示，

-- 如果使用右连接查询时，需要将这个表作为右表，查询后，右表中的数据全部显示，左

表【除右表以外的表都称为左表】中符合条件的数据显示在查询结果中，左表中不符合条件

的数据，也显示在结果中，但相关数据以 null 填充。

-- 语法：

SELECT 别名 1.字段名 1，别名 2.字段名 2

FROM 库名.表名 1 [AS] 别名 1

RIGHT JOIN 库名.表名 2 [AS] 别名 2

ON 别名 1.字段名 3=别名 2.字段名 3； -- 连接条件

Right：右边的

左右连接区别：

左连接查询结果，左表数据全部显示，右表数据，符合条件的显示，不符合条件的，也显示

在结果中，但显示为 null；右连接与其相反。

举例：查询部门表中哪些部门没有员工,哪些部门有员工，查询结果显示部门名称、员工编号

SELECT d.department_name,e.employee_id

FROM hrdb.`employees` eRIGHT JOIN hrdb.`departments` d

ON d.department_id=e.department_id;

举例：查询所有员工的部门信息，没有部门的员工信息也要显示出来

SELECT d.department_name,e.employee_id

FROM hrdb.`departments` d

RIGHT JOIN hrdb.`employees` e

ON d.department_id=e.department_id;

举例：查询 locations 表中的城市，有员工的城市显示出来，

没有员工的也显示出来，查询结果显示城市名称、员工编号（右连接）

SELECT l.city,e.employee_id

FROM hrdb.`employees` e

RIGHT JOIN hrdb.`departments` d

ON e.department_id = d.department_id

RIGHT JOIN hrdb.`locations` l

ON d.location_id = l.location_id;

12.2.3 全外连接（mysql 没有）

-- oracle 语法：

SELECT 别名 1.字段名 1，别名 2.字段名 2

FROM 库名.表名 1 [AS] 别名 1

FULL JOIN 库名.表名 2 [AS] 别名 2

ON 别名 1.字段名 3=别名 2.字段名 3WHERE 字段名 4=字段值 4; -- full 全部的意思

mysql 语法：-- 使用联合查询关键字将左右连接的查询结果拼接在一起，并过滤掉重复数

据

SELECT 别名 1.字段名 1，别名 2.字段名 2

FROM 库名.表名 1 [AS] 别名 1

RIGHT JOIN 库名.表名 2 [AS] 别名 2

ON 别名 1.字段名 3=别名 2.字段名 3

WHERE 字段名 4=字段值 4;

UNION -- 联合查询

SELECT 别名 1.字段名 1，别名 2.字段名 2

FROM 库名.表名 1 [AS] 别名 1

LEFT JOIN 库名.表名 2 [AS] 别名 2

ON 别名 1.字段名 3=别名 2.字段名 3

WHERE 字段名 4=字段值 4;

###联合查询关键字 UNION 的用法

-- 联合查询说明

举例：查询员工表中员工编号分别为 110 和 115 的人员的工资、入职日期

Select salary,hire_date from hrdb.employees

Where employee_id in(110,115);-- 创建两个表，表结构和数据分别从 employees 复制：

Create table hrdb.temp1 as select * from hrdb.employees;

Create table hrdb.temp2 as select * from hrdb.employees;

SELECT * FROM test1.`temp1` UNION -- 合并重复数据

SELECT * FROM test1.`temp2`;

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

SELECT * FROM test1.`temp2`;

-------------------------------------

13. 表中数据的增、删、改（必须掌握！！！！！！）

13.1.1 往表中插入数据（INSERT INTO）

-- 语法：

INSERT INTO 库名.表名(字段名 1，字段名 2) VALUES(字段值 1，字段值 2);

举例：往表 sstuu 中插入一条数据，编号为 01，姓名为张三

INSERT INTO lianxi.`stu1`(sno,sname) VALUES(01,"张三");

INSERT INTO lianxi.`stu1`(sno,sname) VALUES(02,"李四");

往表中一次性插入多条数据：

INSERT INTO lianxi.`stu1`(sno,sname) VALUES(02,"李四"),(03,"王五"),(04,"赵柳

"); -- INSERT 插入，添加的意思-- INTO：往什么里面

-- VALUES：值的意思

13.1.2 修改表中的数据（UPDATE……SET）（面试题）

-- 语法：

UPDATE 库名.表名

SET 字段名 1=新字段值 1

WHERE 字段名 2=字段值 2； -- where 条件用来指定被修改的数据，如果不指定条件，

将会导致表中所有数据都被修改

-- 解释：

update 修改的意思，SET 设置的意思，where 条件的意思

举例：修改姓名为张三的人的编号由 1 修改为 2

UPDATE lianxi.`stu1` SET sno=3

WHERE sname="李四";

举例：将 sales 表中的 zhangsan 那条数据的省份改为 sichuan

UPDATE hrdb.`sales` SET pro_name="sichuan" WHERE `customer_name`="zhangsan";

举例：修改 test 库 stu1 表中编号为 1 的记录的姓名为 liudehua

UPDATE test.`stu1` SET wname="liudehua"WHERE id=1;

举例：修改 test 库 stu1 表中编号为 3,cno 为 4 的记录的姓名为 zhangxueyou

UPDATE test.`stu1` SET wname="zhangxueyou" WHERE id=3 AND cno=4; -- and 为和，两者都...的意思，and 两边的条件同时成立

举例：将 test 库 stu1 表中所有数据的 cno 都修改为 8

UPDATE test.`stu1` SET cno=8; -- 不加 where 条件时，修改数据会对整个表中的所有记录起作用

举例：修改 test 库 stu1 表的数据，将 zhangxueyou 那条记录的 id 修改为 4，cno 需改

为 9(同时修改多个字段的值)

UPDATE test.`stu1` SET id=4,cno=9

WHERE wname="zhangxueyou";

13.1.3 删除表中的数据(面试题)

-- 方法一：

-- 语法：

DELETE FROM 库名.表名 WHERE 字段名 1=字段值 1；（删除指定数据）

-- 重点说明：删除表中数据时，如果不加 where 默认删除表中所有数据(清空表) -- 重点说明：使用 delete 删除表中数据时，delete 后不能跟*号，delete 不能和*号搭配

使用。

-- delete 删除的意思-- FROM 库名.表名：删除哪个表中的数据

-- WHERE 字段名 1=字段值 1：意思是删除字段名 1 的值为字段值 1 对应的数据（对应的

行）

INSERT INTO school.`classc`(cno,cname,dno)

VALUES(2,"李斯",1),(3,"wangwu",2),(4,"zhaoliu",5),(5,"xaioming",6);

INSERT INTO school.`classc`(cno,cname,dno)

VALUES(6,"李斯",6);

举例：删除 classc 表中的名字为 wangwu 的数据

DELETE FROM school.`classc` WHERE cname="wangwu";

举例：删除姓名为李斯，dno 为 1 的人员

DELETE FROM school.`classc` WHERE cname="李斯" AND dno=1; -- 说明：AND 两边的条件，都满足的数据被删除掉

举例：删除 zhaoliu 和 xiaoming 这两条数据

DELETE FROM school.`classc` WHERE cname="zhaoliu" OR cname="xaioming"; -- or 两边的条件，符合任何一个条件的数据，或者两边条件都符合的数据，均会被删除

SHOW WARNINGS;

举例：删除 cno 分别为 1 和 6 的这两条数据

DELETE FROM school.`classc` WHERE cno=1 OR cno=6;

举例：创建一个表，该表的表结构和数据均复制于表 employees

CREATE TABLE hrdb.emp1 as SELECT * FROM hrdb.`employees`; -- 复制表(复制表结构+数据)

举例：创建一个表，该表的表结构和数据均复制于表 employees

CREATE TABLE hrdb.emp AS SELECT * FROM hrdb.`employees`;

举例：清空 emp 表中的所有数据

方法一：

DELETE FROM hrdb.`emp`;-- 删除表中数据时，如果不加 where 条件，默认删除表中所有数据

方法二：【面试题】

准备数据：

INSERT INTO hrdb.`emp` SELECT * FROM hrdb.`employees`; -- 【重点】将另一个表中的数据全部复制到一个表结构完全相同的表中

清空表 emp 中的数据：

TRUNCATE hrdb.`emp`; -- TRUNCATE 删除的意思，使用 truncate 删除的数据，无法

恢复，不能和 from、where 配合使用

14. 用户及权限管理

14.1 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 表中添加】

步骤二：为用户授权

步骤三：使授权生效

14.2 用户创建

-- 只能在 mysql 所在的机器上创建用户

-- 创建用户时需要使用具有 root 权限的用户创建用户

语法：

INSERT INTO mysql.`user`(HOST,USER,PASSWORD)

VALUES("IP 地址","用户名",PASSWORD("密码")); -- 创建用户   -- PASSWORD() 加密函数

举例：添加[本地用户]test，密码为 123456，ip 地址为 localhost，为用户 test 授予所有权限，使该用户可以对指定数据库做所有操作

(1) -- 创建用户

INSERT INTO mysql.`user`(HOST,USER,PASSWORD)

VALUES("localhost","test",PASSWORD("123456"));

(2) -- 为新创建的用户授权

GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY "123456";

(3) -- 刷新授权表，使被授权的用户具有被授予的权限，相当于重启mysql 服务

FLUSH PRIVILEGES;

-- *.*：句号前的星号*，代表所有库，句号后的星号*，代表每个库中的每个表

-- FLUSH：刷新的意思，PRIVILEGES 在这里是授权表的意思

举例：创建远程访问用户 test1，为该用户在数据库 hrdb 中的所有表上授予 select、insert

权限，使该用户在远程任何一台主机上可以访问数据库，该用户的密码为 123456

INSERT INTO mysql.user(HOST,USER,PASSWORD)

VALUES("%","test1",PASSWORD("123456"));

GRANT SELECT,INSERT ON lianxi.* TO test1@"%" IDENTIFIED BY "123456";

FLUSH PRIVILEGES;

举例：创建远程访问用户 test2，为该用户在所有库的所有表上授予 update、insert、select

权限，使该用户在远程指定 IP 为：172.30.71.176 的主机上可以访问数据库，该用户的密

码为 123456

INSERT INTO mysql.user(HOST,USER,PASSWORD)

VALUES("172.30.66.83","test2",PASSWORD("123456"));

GRANT SELECT,UPDATE,INSERT ON *.* TO test2@"172.30.66.83" IDENTIFIED BY"123456";

FLUSH PRIVILEGES;

14.3权限管理

14.3.1 用户授权

-- mysql 权限分类：

-- 权限列表如下：

-- (1)create：创建库、创建表、创建视图、创建存储过程等

-- (2)drop：删除库、删除表、删除视图、删除存储过程、删除字段等

-- (3)alter:修改库、修改表等

-- (4)insert：往表中插入数据

-- (5)select : 查询表中的数据

-- (6)update：修改表中数据

-- (7)delete:删除表中数据

-- 特殊权限：

-- all privileges:所有权限 -- privilege 权限的意思

-- usage：没有权限，具有该权限的用户只能连接数据库，不能做其他任何操作

14.3.2 授权语法：

GRANT 权限列表 ON 库名.表名 TO 用户名@主机 IP IDENTIFIED BY "密码";

-- 解释：

-- GRANT：授权的意思-- ON 库名.表名：在哪些库的哪些表上授权

-- TO 用户名@主机 IP：为指定主机 IP 对应的用户授权

-- IDENTIFIED BY "密码"：通过什么密码鉴定用户身份（使用什么密码登录）

-- IDENTIFIED 是单词 identify 的过去分词，标志，标识的意思

【重要】：update 和 alter 的区别：update 用来修改表中数据，alter 修改库、表、字段、

数据类型、长度等。

14.3.3 查看用户权限

基本语法：

SHOW GRANTS FOR 用户名@主机 IP; -- 查看指定用户的权限

(1)查看当前登录用户【查看自己的权限】

SHOW GRANTS; -- 查看结果中的 with grant option，意思是该用户具有超级用户权限，可以创建用户，也

可以为其他用户授权

(2)查看本地用户

SHOW GRANTS FOR 用户名@localhost;

(3) 查看远程可以在任意一台主机上访问数据库的用户的权限

SHOW GRANTS FOR 用户名@"%";(4)查看在远程指定 IP 的主机上访问数据的用户的权限：

SHOW GRANTS FOR 用户名@"指定 IP 地址";

SHOW GRANTS FOR test2@"172.30.67.161";

14.3.4 用户权限回收

语法：

REVOKE 权限 ON `数据库`.数据库对象 FROM '用户名'@'数据库所在主机 IP' 说明：root 用户才有权限回收其他用户的权限

举例：回收用户 test 在本地主机上的所有权限

回收权限分为三步：

步骤一、查看将要被回收权限的用户的权限

SHOW GRANTS FOR test@localhost;

步骤二、回收权限

REVOKE ALL PRIVILEGES ON *.* FROM test@localhost;

步骤三、刷新授权表，使回收权限生效

FLUSH PRIVILEGES; -- REVOKE：取消，废除

14.3.5 用户删除

语法：

DROP USER 用户名@主机 IP 地址;(1)删除主机 IP 为百分号%的远程用户：

DROP USER 用户名;

(2)删除指定 IP 地址的远程用户：

DROP USER 用户名@"具体指定 IP 地址";

(3)删除本地用户

DROP USER 用户名@localhost;

14.3.6 修改用户密码

-- 语法：

Update mysql.user

Set password=password(“新密码”)

Where user=”用户名” and host=”IP 地址”；

说明：修改用户密码分为两步：

步骤一、 修改密码

步骤二、使新密码生效

举例：修改 test1 用户的密码为 654321

15.函数条件查询

15.1字符大小写转换函数

SELECT UPPER("SmitH") 将小写字符串变为大写,LOWER("ZhangSan") 将大写字符串变

为小写;

SELECT UPPER("zhangSAN");

SELECT LOWER("ZA");

15.2字符控制函数

SELECT CONCAT(first_name,".",last_name) FROM hrdb.`employees`;

SELECT CONCAT("hello","world"); -- 字符串拼接函数(要求掌握)

SELECT SUBSTR('HelloWorld',2,5),SUBSTRING('HelloWorld',2,5);-- 从第二个字符开

始，连续截取 5 个字符 -- STRING 字符串的意思

-- STRING 是字符串的意思

SELECT LENGTH('HelloWorld'); -- 判断字符长度

SELECT INSTR('HelloWorld', 'Wor'); -- 给定一个大字符串，一个小字符串，判断小字符

串的第一个字母在大串中的位置序号SELECT LPAD('salary',10,'*'); -- 按指定的位数显示数据，长度不够，在左侧补上指定的字符

SELECT RPAD('salary',10,'*'); -- 按指定的位数显示数据，长度不够，在右侧补上指定的字符

SELECT TRIM(' john '); -- 在没有给定其他关键字时，默认去除给定字符串前后的空格

SELECT TRIM(LEADING "c" FROM "ccAAAAccc"); -- 去除给定字符串前面的指定字符

SELECT TRIM(TRAILING "c" FROM "ccAAAAccc"); -- 去除给定字符串后面的指定字符

SELECT TRIM(BOTH "c" FROM "ccAAAAccc"); -- 去除给定字符串两边的指定字符

举例：查询所有员工的名字和姓名，并直接相连。

SELECT UPPER(E.FIRST_NAME) "小写变大写", LOWER(E.FIRST_NAME) "大写变小写", CONCAT(E.FIRST_NAME, '.', E.LAST_NAME) "全名" FROM HRDB.EMPLOYEES E;

SELECT CONCAT(E.FIRST_NAME,".",E.LAST_NAME) "Full Name", E.JOB_ID, TRIM(E.first_name), LENGTH(E.LAST_NAME), LPAD(E.SALARY,11, '*'), RPAD(E.SALARY,12, '$')

FROM HRDB.EMPLOYEES E

WHERE SUBSTRING(JOB_ID, 4) = 'REP'; -- JOB_ID第四位开始是 REP 这个字符串

15.3数字处理函数

SELECT ROUND(99.878,2),TRUNCATE(56.788,2),MOD(15,3);

15.4日期处理函数

SELECT SYSDATE(),NOW(); -- 获取系统当前的日期时间

SELECT CURTIME(); -- 获取系统的当前时间

SELECT WEEKDAY(SYSDATE()); -- 判断给定的时间日期属于礼拜几

SELECT WEEKDAY("2018-11-11"); -- 判断给定的时间日期属于礼拜几

SELECT MONTHNAME("2018-11-11"); -- 判断给定的日期时间属于几月份

SELECT DATE_FORMAT("2018-11-11","%Y:%m:%d"); -- 按照指定的格式显示给定的日期时间

SELECT DATE_FORMAT(NOW(),"%Y-%m-%d %H:%i:%s"); -- 按照指定的格式显示给定的日期时间

SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND); -- 将给定的日期时间增加 1 秒SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY); -- 将给定的日期时间增加 1 天

SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 MONTH); -- 将给定的日期时间增加 1 月

SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL -1 MONTH); -- 将给定的时间减少 1 个月

SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR); -- 在当前给定的时间基础上后退 1 天 10 小时

16.使用 SQL 语句备份/导出数据库数据：

n 备份/导出数据库数据：

l 使用 sqlyog 备份/导出数据库中数据：

手工操作步骤：

选择一个数据库》在上面右击鼠标》备份/导出》备份数据库，转储到 sql...》选择导出路径

并命名导出文件》点击导出按钮》确认导出》点击完成

l 使用 SQL 语句导出本地数据库数据：

-- 导出本地数据库数据

-- 语法：

mysqldump -u 用户名 -p 库名 >保存目录:\导出后生成的文件名.sql

举例：

mysqldump -u root -p hrdb >c:\mysql.sql (退出数据库后执行)

-- 导出远程数据库数据

-- 语法：

mysqldump -h ip 地址 -u root -p mysql >c:\mysql.sql (退出数据库后执行)

导入数据库数据

l 使用 sqlyog 将数据从导出的 sql 脚本文件中导入数据库：

手工操作步骤：

在 sqlyog 左侧目录树中单击鼠标右键》在弹出的右键菜单中选择“执行 sql 脚本”》选择脚本》执行 SQL 脚本》完成

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

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

-- 将导出的数据库文件中的数据导入到数据库中

-- 语法：

mysql -u 用户名 -p 目标库 <"c:\数据库文件.sql" mysql -u 用户名 -p 库名 <"c:\数据库文件.sql" 举例：

mysql -u root -p hrdb <"c:\hrdb.sql" (退出数据库后执行)