MySQL

最新推荐文章于 2024-09-05 16:59:36 发布

Honvin Zhou

最新推荐文章于 2024-09-05 16:59:36 发布

阅读量1.7k

点赞数 24

文章标签： mysql

本文链接：https://blog.csdn.net/munanqn/article/details/135091816

版权

MySQL基础

如果安装MySQL过程中出错了，重来

sc delete mysql【删除已经安装好的musql服务，慎重！！！】

MySQL 5.7.44安装

下载 MySQL5.7.44
解压设置环境变量（\bin目录）

在根目录添加 my.ini 文件

[client]
port=3306
default-character-set=utf8
[mysqld]
# 设置为自己MYSQL的安装目录
basedir=E:\MySQL\mysql-5.7.44-winx64\
# 设置为MYSQL的数据自录
datadir=E:\MySQL\mysql-5.7.44-winx64\data\
port=3306
character_set_server=utf8
# 跳过安全检查
skip-grant-tables

管理员身份运行cmd
1. 转到\bin目录
2. mysqld -install
3. mysqld --initialize-insecure --user=mysql产生data目录
启动 mysql 服务

net start mysql
停止 mysql 服务

net stop mysql
进入 mysql 管理终端

mysql -u root -p

修改 root 密码

use mysql;

update user set authentication_string=password('root') where user='root' and Host='localhost';
# 修改 root 密码为 root

flush privileges;
# 刷新权限

quit
# 退出

修改之后注销掉 my.ini 的 skip-grant-tables

连接到Mysql服务（Mysql数据库）的指令

mysql -h 主机IP -P 端口 -u 用户名 -p密码
- 注意：-p密码不要有空格
- -p后面没有写密码，回车会要求输入密码
- 如果没有写 -h 主机，默认本机
- 如果没有些 -P 端口，默认3306
- 实际工作中端口一般会更改

MySQL的三层结构

数据库-普通表的本质：仍然是文件

表的一行称之为一条记录

在java程序中，一行记录往往使用对象表示

SQL语句分类

DDL：数据定义语句[create表，库…]

DML：数据操作语句[增加insert，修改update，删除delete]

DQL：数据查询语句[select】

DCL：数据控制语句【管理数据库：比如用户权限grant，revoke】

数据库基本操作

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [create_specification...]]

create_specification:

[DEFAULT] CHARACTER SET charset_name

[DEFAULT] COLLATE collation_name

CHARACTER SET：指定数据库采用的字符集，如果不指定字符集，默认utf8
COLLATE：指定数据库字符集的校对规则（常用的utf8bin[区分大小写]、utf8generalci[不区分大小写]，注意默认是utf8_generalci）

在创建数据库，表的时候，为了规避关键字，可以使用反引号反引号是~键解决

查看删除数据库

# 显示数据库：
SHOW DATABASES

# 显示数据库创建语句
SHOW CREATE DATABASE db_name

# 数据库删除语句【慎用】
DROP DATABASE [IF EXISTS] db_name

备份恢复数据库

备份数据库（注意：命令行DOS执行）

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql

生成的文件名.sql文件，就是对应的sql语句
恢复数据库（注意：进入MySQL命令行再执行）

Source 文件名.sql
备份恢复库的表

``mysqldump -u 用户名 -p密码数据库表1 表2 表n > 文件名.sql`

MySQL常用数据类型（列类型）

Mysql列类型

日期类型：

CREATE TABLE t4 (
  birthday DATE,
  job_time DATETIME,
  login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ;


SELECT * FROM t4;
INSERT INTO t4(birthday, job_time) VALUES('2023-12-07', '2023-12-07 10:00:00');

NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP：默认填入当前时间，更新时也是当前时间

数据表的管理

创建表

CREATE TABLE table_name
(
	field1 datatype,
    field2 datatype,
    field3 datatype,
)character set 字符集 collate 校对规则 engin 存储引擎

CREATE TABLE `user` (
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE
) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

field:指定列名

datatype：指定列类型（字段类型）

character set：如不指定则为所在数据库字符集

collate：如不指定则为所在数据库校对规则

engine:：引擎（这个涉及内容较多，后面单独讲解）

修改表

添加列

ALTER TABLE tablename
ADD (column datatype [DEFAULT expr] [ , column datatype]...);

修改列

ALTER TABLE tablename
MODIFY (column datatype [DEFAULT expr] [ , column datatype]...);

修改列名：

ALTER TABLE tablename
CHANGE `原列名` `新列名` datatype;

修改表名：
```
RENAME TABLE 表名 to 新表名
```

修改表字符集

ALTER TABLE 表名 CHARACTER SET 字符集;

删除列

ALTER TABLE tablename
DROP (column);
查看表的结构：desc 表名; # 可以查看表的列

删除表

DROP TABLE tablename;

CRUD增删改查

create，read，update，dalete

Insert 语句

使用Insert语句向表中插入数据

INSERT INTO table_name [(column [ , column...])]
VALUES (value [ , vallue...])

insert细节：

插入的数据应与字段的数据类型相同。比如把’abc’添加到int类型会错误
数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。
在values中列出的数据位置必须与被加入的列的排列位置相对应。
字符和日期型数据应包含在单引号中。
列可以插入空值[前提是该字段允许为空]，insert into table value（null）
insert into tab_name（列名…）values (),(),() 形式添加多条记录
如果是给表中的所有字段添加数据，可以不写前面的字段名称
默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错

update 语句

使用update 语句修改表中数据

UPDATE tbl_name
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]

update细节：

UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行（记录），因此提醒一定小心。
如果需要修改多个字段，可以通过set字段1=值1，字段2=值2…

delete 语句

使用delete 语句删除表中数据

DELETE FROM tb_name
[WHERE where_defination]

delete 细节：

如果不使用where子句，将删除表中所有数据。
Delete语句不能删除某一列的值（可使用update设为null或者）使用delete语句仅删除记录，不删除表本身。
如要删除表，使用drop table语句。drop table表名；

select 语句

单表查询

SELECT [DISTINCT] *|{column1, column2, column3...}
FROM tablename;

select 细节

Select指定查询哪些列的数据。
column指定列名。
*号代表查询所有列。
From指定查询哪张表。
DISTINCT可选，指显示结果时，是否去掉重复数据。完全相同才去重。

使用表达式对查询的列进行运算

SELECT [DISTINCT] *|{column1 | expression, column2 | expression, ..}
FROM tablename;

在select语句中可使用as语句取别名

SELECT column_name as 别名 FROM 表名;

在where子句中经常使用同的运算符

between and是闭区间

实例：

-- 查询总分大于200分并且数学成绩小于语文成绩的姓张的学生
SELECT * FROM student 
WHERE (chinese + english + math) > 200 AND math < chinese AND `name` LIKE '张%';

'张%'：表示所有以张开始的名字

使用order by子句排序查询结果
```
SELECT column1, column2, column3..
FROM table
ORDER BY column asc|desc, ...
```
- asc(默认)：升序
- desc：降序
- Orderby指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名。A
- ORDER BY子句应位于SELECT语句的结尾

使用group by 子句对列进行分组

SELECT column1, column2, column3..FROM table
GROUP BY column

使用 having 子句对分组后的结果进行过滤

SELECT column1, column2, column3..FROM table
GROUP BY column having...

查询加强

-- 查询加强
SELECT * FROM emp;
-- ■ 使用where子句
-- 	?如何查找1992.1.1后入职的员工
-- 在mysql中,日期类型可以直接比较, 需要注意格式
SELECT * FROM emp
	WHERE hiredate > '1992.01.01';

-- ■ 如何使用like操作符(模糊)
-- 	%: 表示0到多个任意字符 _: 表示单个任意字符
-- 	?如何显示首字符为S的员工姓名和工资
SELECT ename, sal FROM emp WHERE ename LIKE 'S%';

-- 	?如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename, sal FROM emp WHERE ename LIKE '__O%';

-- ■ 如何显示没有上级的雇员的情况
SELECT * FROM emp WHERE mgr IS NULL;

-- ■ 查询表结构 
DESC emp;


-- 使用order by子句
--   ?如何按照工资的从低到高的顺序[升序]，显示雇员的信息
SELECT * FROM emp ORDER BY sal;
--   ?按照部门号升序而雇员的工资降序排列 , 显示雇员信息
SELECT * FROM emp ORDER BY deptno ASC, sal DESC;

分页查询

基本语法：

SELECT ... LIMIT start, rows

表示从 start+1 行开始取，取出 rows 行，start 从0开始计算。

-- 分页查询
-- 按雇员的id号升序取出， 每页显示3条记录，请分别显示 第1页，第2页，第3页

-- 第1页
SELECT * FROM emp 
	ORDER BY empno
	LIMIT 0, 3

-- 第2页
SELECT * FROM emp 
	ORDER BY empno
	LIMIT 3, 3

-- 第3页
SELECT * FROM emp 
	ORDER BY empno
	LIMIT 6, 3


-- 推导一个公式 
SELECT * FROM emp
	ORDER BY empno 
	LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数

分组函数加强（group by）

-- 增强group by 的使用

-- (1) 显示每种岗位的雇员总数、平均工资。
SELECT COUNT(*), job, AVG(sal) FROM emp GROUP BY job

-- (2) 显示雇员总数，以及获得补助的雇员数。
--  思路: 获得补助的雇员数 就是 comm 列为非null, 就是count(列)，如果该列的值为null, 是
--  不会统计 , SQL 非常灵活，需要我们动脑筋.
SELECT COUNT(*), COUNT(comm) FROM emp;


--  老师的扩展要求：统计没有获得补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL)) FROM emp;


-- (3) 显示管理者的总人数。小技巧:尝试写->修改->尝试[正确的]
SELECT COUNT(DISTINCT mgr) FROM emp;  -- DISTINCT 去重


-- (4) 显示雇员工资的最大差额。
-- 思路： max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) FROM emp;


-- 应用案例：请统计各个部门group by 的平均工资 avg，
-- 并且是大于1000的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0, 2
SELECT deptno, AVG(sal) AS avg_sal  FROM emp
	GROUP BY deptno
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC;
	
SELECT deptno, AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno
	HAVING  avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0, 2

多表查询

多表查询是指基于两个和两个以上的表查询.在实际应用中，查询单个表可能不能满足你的需求。

默认情况下，当两个表查询时，规则如下：

从第一张表中，取出一行和第二张表的每一行进行组合，返回结果【含有两张表的所有列】
一共返回的记录数：第一张表行数 * 第二张表的行数
这样的多表查询默认处理方式返回的结果称为笛卡尔积
解决多表的关键就是要写出正确的过滤条件 where

注意：

当我们需要指定显示某个表的列是，需要 表.列表
多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔积

-- 多表查询
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*
	1. 雇员名,雇员工资 来自 emp表
	2. 部门的名字 来自 dept表
	3. 需求对 emp 和 dept查询  ename,sal,dname,deptno
	4. 当我们需要指定显示某个表的列是，需要 表.列表
*/
SELECT ename, sal, dname FROM emp, dept  -- 默认返回笛卡尔积

SELECT ename, sal, dname FROM emp, dept
	WHERE emp.`deptno` = dept.`deptno`

-- 小技巧：多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为10的部门名、员工名和工资 
SELECT dname, ename, sal FROM emp, dept
	WHERE emp.`deptno` = dept.`deptno` AND emp.`deptno` = 10
	
SELECT dname, ename, sal, emp.`deptno` FROM emp, dept
	WHERE emp.`deptno` = dept.`deptno` 
	HAVING emp.`deptno` = 10

-- ?显示各个员工的姓名，工资，及其工资的级别

-- 思路 姓名，工资 来自 emp 13
--      工资级别 salgrade 5
-- 写sql , 先写一个简单，然后加入过滤条件...
SELECT ename, sal, grade FROM emp, salgrade
	WHERE emp.`sal` BETWEEN salgrade.`losal` AND salgrade.`hisal`

自连接

自连接是指在同一张表的连接查询。即将同一张表看作两张表

-- 多表查询的 自连接

-- 思考题: 显示公司员工名字和他的上级的名字

-- 老韩分析： 员工名字 在emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp表的 mgr 列关联
SELECT worker.`ename` AS '职员名', boss.`ename` AS '上级名' FROM emp AS worker, emp AS boss
	WHERE worker.`mgr` = boss.`empno`

自连接的特点

把同一张表当做两张表使用
需要给表取别名：表名表别名（也可以加 AS）
列名不明确，可以指定列的别名：列名 as 列的别名

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询

单行子查询是指只返回一行数据的子查询语句

-- 单行子查询：如何显示与SMITH同一部门的所有员工?
/*
	1. 先查询到 SMITH的部门号得到
	2. 把上面的select 语句当做一个子查询来使用
*/
SELECT deptno FROM emp WHERE ename = 'SMITH';

-- 下面的答案.	
SELECT * FROM emp WHERE deptno = (
		SELECT deptno FROM emp WHERE ename = 'SMITH'
	);

多行子查询

多行子查询是指返回多行数据的子查询，使用关键字 in

-- 多行子查询:如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含10号部门自己的雇员.

/*
	1. 查询到10号部门有哪些工作
	2. 把上面查询的结果当做子查询使用
*/
SELECT DISTINCT job FROM emp WHERE deptno = 10;	

-- 下面的答案.	
SELECT ename, job, sal, deptno FROM emp
	WHERE job IN(
		SELECT DISTINCT job FROM emp WHERE deptno = 10
	) AND deptno != 10;  -- 或者不等<>

在多行子查询中使用 all 和 any 操作符

-- all 和 any的使用

-- 请思考:显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp
	WHERE sal > ALL(
		SELECT sal FROM emp WHERE deptno = 30
	);

-- 可以这样写
SELECT ename, sal, deptno FROM emp
	WHERE sal > (
		SELECT MAX(sal) FROM emp WHERE deptno = 30
	);


-- 请思考:如何显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno FROM emp
	WHERE sal > ANY(
		SELECT sal FROM emp WHERE deptno = 30
	);
	
SELECT ename, sal, deptno FROM emp
	WHERE sal > (
		SELECT MIN(sal) FROM emp WHERE deptno = 30
	);

子查询当作临时表使用

-- 查询ecshop中各个类别中，价格最高的商品

-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询

select cat_id , max(shop_price) 
	from ecs_goods
	group by cat_id
	
	
-- 这个最后答案	
select goods_id, ecs_goods.cat_id, goods_name, shop_price 
	from (
		SELECT cat_id , MAX(shop_price) as max_price
		FROM ecs_goods
		GROUP BY cat_id
	) temp , ecs_goods
	where  temp.cat_id = ecs_goods.cat_id 
	and temp.max_price = ecs_goods.shop_price

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

-- 多列子查询

-- 请思考如何查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含ALLEN本人)
-- (字段1， 字段2 ...) = (select 字段 1，字段2 from 。。。。)

-- 分析: 1. 得到ALLEN的部门和岗位
SELECT deptno, job FROM emp WHERE ename = 'ALLEN';
	
-- 分析: 2  把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT * FROM emp
	WHERE (deptno, job) = (
		SELECT deptno, job FROM emp WHERE ename = 'ALLEN'
	)AND ename != 'ALLEN';


-- 请查询 和宋佳数学，英语，语文   
-- 成绩 完全相同的学生
SELECT * FROM student
	WHERE (math, english, chinese) = (
		SELECT math, english, chinese FROM student WHERE `name` = '宋佳'
	);

表复制

自我复制数据（蠕虫复制）

有时，为了对某个sgl语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

-- 表的复制
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

CREATE TABLE my_tab01 
	( id INT,
	  `name` VARCHAR(32),
	  sal DOUBLE,
	  job VARCHAR(32),
	  deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;

-- 演示如何自我复制
-- 1. 先把emp 表的记录复制到 my_tab01
INSERT INTO my_tab01 (id, `name`, sal, job, deptno)
	SELECT empno, `ename`, sal, job, deptno FROM emp;
-- 2. 自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;

删除一条表中的重复记录（重复）

-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02, 
-- 2. 让 my_tab02 有重复的记录
CREATE TABLE my_tab02 LIKE emp;
DESC my_tab02;

INSERT INTO my_tab02
	SELECT * FROM my_tab02;
-- 3. 考虑去重 my_tab02的记录
/*
	思路 
	(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样
	(2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
	(3) 清除掉 my_tab02 记录
	(4) 把 my_tmp 表的记录复制到 my_tab02
	(5) drop 掉 临时表my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样
CREATE TABLE my_tmp LIKE my_tab02;

-- (2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
INSERT INTO my_tmp
	SELECT DISTINCT * FROM my_tab02;

-- (3) 清除掉 my_tab02 记录
DELETE FROM my_tab02;

-- (4) 把 my_tmp 表的记录复制到 my_tab02
INSERT INTO my_tab02
	SELECT * FROM my_tmp;

-- (5) drop 掉 临时表my_tmp
DROP TABLE my_tmp;

合并查询

又是在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all

union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。

union ：就是将两个查询结果合并，会去重；

-- 合并查询

SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5

SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union all 就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

-- union  就是将两个查询结果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

mysql表外连接

左外连接（如果左侧的表完全显示就是左外连接）

基本语法：SELECT ... FROM tab1 LEFT JOIN tab2 on 条件，其中，tab1就是左表，tab2就是右表。

-- 使用左连接
-- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空）

SELECT `name`, stu.id, grade
	FROM stu, exam
	WHERE stu.id = exam.id;
	
-- 改成左外连接
SELECT `name`, stu.id, grade
	FROM stu LEFT JOIN exam
	ON stu.id = exam.id;

右外连接（如果右侧的表完全显示就是右外连接）

基本语法：SELECT ... FROM tab1 RIGHT JOIN tab2 on 条件，其中，tab1就是左表，tab2就是右表。

-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
SELECT `name`, stu.id, grade
	FROM stu RIGHT JOIN exam
	ON stu.id = exam.id;

函数

合计/统计函数

count

Count 返回行的总数

SELECT COUNT(*) | COUNT(列名) FROM table_name
[WHERE where_defination]

count(*)和count(列)的区别：

count(*)：返回满足条件的记录的行数
count(列名)：统计满足条件的某列有多少个，但是会排除为null的情况

sum

Sum函数返回满足 where 条件的行的和——一般用在数值列

SELECT SUM(列名) {, SUM(列名)...} FROM tablename
[where where_defination]

注意：

sum进队数值起作用；

对多列求和，“,”不能少

avg

AVG函数返回满足where条件的一列的平均值

SELECT AVG(列名) {, AVG(列名)...} FROM tablename
[WHERE where_defination]

max/min

max/min函数返回满足where条件的一列的最大/最小值

SELECT MAX(列名) FROM tablename
[WHERE where_defination]

字符串相关函数

dual 亚元表, 系统表可以作为测试表使用

索引从1开始

数学函数

RAND([seed]) RAND([seed]) 返回随机数其范围为 0 ≤ v ≤ 1.0

如果使用 rand() 每次返回不同的随机数，在 0 ≤ v ≤ 1.0
如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果seed不变，该随机数也不变了

时间日期相关函数

DATE_ADD()中的interval后面可以是year minute second day等

DATE_SUB()中的interval后面可以是year minute second day等

DATEDIFF(date1，date2)得到的是天数，而且是date1-date2的天数，因此可以取负数

这四个函数的日期类型可以是date，datetime或者timestamp

unix_timestamp() : 返回的是1970-1-1 到现在的秒数

FROM_UNIXTIME() : 可以把一个unix_timestamp 秒数[时间戳]，转成指定格式的日期

意义：在开发中，可以存放一个整数，然后表示时间，通过FROM_UNIXTIME转换
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;

加密函数

-- 演示加密函数和系统函数

-- USER()	查询用户
-- 可以查看登录到mysql的有哪些用户，以及登录的IP
SELECT USER() FROM DUAL;  -- 返回 用户@IP地址

-- DATABASE()	查询当前使用数据库名称
SELECT DATABASE() FROM DUAL;

-- MD5(str)	为字符串算出一个 MD5 32的字符串，常用(用户密码)加密
-- root 密码是 honvin -> 加密md5 -> 在数据库中存放的是加密后的密码
SELECT MD5('honvin') FROM DUAL;  -- d7faa0cda0a6690fc378b2ee740e58a5
SELECT LENGTH(MD5('honvin')) FROM DUAL;

-- 演示用户表，存放密码时，是md5
CREATE TABLE timerring_user
	(id INT , 
	`name` VARCHAR(32) NOT NULL DEFAULT '', 
	pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO timerring_user
	VALUES(100, 'honvin', MD5('honvin'));
SELECT * FROM timerring_user;
SELECT * FROM timerring_user
	WHERE `name` = 'honvin' AND pwd = MD5('honvin');
-- PASSWORD(str) -- 加密函数, MySQL数据库的用户密码就是 PASSWORD函数加密
SELECT PASSWORD('honvin') FROM DUAL;  -- *8EF49CD623ACBFFA57D630ECD695A3DC3EEECDBC
-- select * from mysql.user \G 	从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示 数据库.表 
SELECT * FROM mysql.`user`;

流程控制函数

# 演示流程控制语句
 # IF(expr1,expr2,expr3)	如果expr1为True ,则返回 expr2 否则返回 expr3
 
SELECT 
  IF(FALSE, 'Beijing', 'Shanghai') 
FROM
  DUAL ;

  -- Shanghai

# IFNULL(expr1,expr2)	如果expr1不为空NULL,则返回expr1,否则返回expr2
SELECT IFNULL('Beijing', 'Shanghai') FROM DUAL;  -- Beijing
SELECT IFNULL(NULL, 'Shanghai') FROM DUAL;  -- Shanghai
 # SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; [类似多重分支.]
# 如果expr1 为TRUE,则返回expr2,如果expr2 为t, 返回 expr4, 否则返回 expr5
 
SELECT 
  
  CASE
    
    WHEN TRUE 
    THEN 'jack' 
    WHEN FALSE 
    THEN 'tom' 
    ELSE 'jerry' 
  END ;

  -- jack

-- 1. 查询emp 表, 如果 comm 是null , 则显示0.0
--    老师说明，判断是否为null 要使用 is null, 判断不为空 使用 is not
SELECT ename, IF(comm IS NULL, 0.0, comm) FROM emp;
-- 或者
SELECT ename, IFNULL(comm, 0.0) FROM emp;

-- 2. 如果emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理
--     如果是 SALESMAN 则显示 销售人员，其它正常显示
SELECT 
  ename,
  (SELECT 
    CASE
      WHEN job = 'CLERK' 
      THEN '职员' 
      WHEN job = 'MANAGER' 
      THEN '经理' 
      WHEN job = 'SALESMAN' 
      THEN '销售人员' 
      ELSE job 
    END) AS 'job' 
FROM
  emp ;

MySQL 约束

基本介绍：约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null、unique，primary key，foreignkey和check五种。

主键（primary key）

基本使用：字段名字段类型 primary key
用于唯一的标识表行的数据，当定义主键约束后，该列不能重复

注意细节：
primary key不能重复而且不能为 null。
一张表最多只能有一个主键, 但可以是复合主键(比如 id+name，id和name不能同时相同)
-- 演示复合主键 (id 和 name 做成复合主键)
CREATE TABLE t6
	(id INT , 
	`name` VARCHAR(32), 
	email VARCHAR(32),
	PRIMARY KEY (id, `name`) -- 这里就是复合主键
	);
	
INSERT INTO t6
	VALUES(1, 'tom', 'tom@sohu.com');
INSERT INTO t6
	VALUES(1, 'jack', 'jack@sohu.com');
INSERT INTO t6
	VALUES(1, 'tom', 'xx@sohu.com'); -- 这里就违反了复合主键
SELECT * FROM t6;
主键的指定方式有两种

直接在字段名后指定：字段名 primakry key
在表定义最后写：primary key(列名);
CREATE TABLE t19
	(id INT , 
	`name` VARCHAR(32) PRIMARY KEY, 
	email VARCHAR(32)
	);

CREATE TABLE t20
	(id INT , 
	`name` VARCHAR(32) , 
	email VARCHAR(32),
	PRIMARY KEY(`name`) -- 在表定义最后写 primary key(列名)
	);
使用 desc 表名，可以看到primary key的情况

实际开发中，每个表往往都对设计一个主键

非空（not null）

如果在列上定义了notnull，那么当插入数据时，必须为列提供数据。

字段名字段类型 not null

唯一（unique）

当定义了唯一约束后，该列值是不能重复的。

字段名字段类型 unique

注意细节：

如果没有指定 not null ，则 unique 字段可以右多个null
如果一个列（字段）是 unique not null ，使用效果类似 primary key
一张表可以右多个 unique 字段

外键（foreign key）

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为nul。

学生表的class_id为300的，在班级表中并不存在，所以就会添加失败，这叫外键约束另外，如果学生表的jack和班级表已建立联系再删除班级表的id就会失败，得先删除学生表的jack才行，也叫外键约束。

语法：FOREIGNKEY(本表字段名) REFERENCES 主表名(主键名或unique字段名)
先创建主表，再创建从表

细节说明：

外键指向的表的字段，要求是primarykey或者是unique
表的类型是innodb，这样的表才支持外键
外键字段的类型要和主键字段的类型一致（长度可以不同）
外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]
一旦建立主外键的关系，数据不能随意删除了

检查（check）

用于强制行数据必须满足的条件，假定在sql列上定义了check约束，并要求sql列值在1000～2000之间如果不在1000～2000之间就会提示出错。

提示：oracle和sqlserver均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

-- 演示check的使用
-- mysql5.7目前还不支持check ,只做语法校验，但不会生效
-- 了解 
-- 学习 oracle, sql server, 这两个数据库是真的生效.

-- 测试
CREATE TABLE t7 (
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK (sex IN('man', 'woman')),
	sal DOUBLE CHECK (sal > 1000 AND sal < 2000));
	
-- 添加数据
INSERT INTO t7 VALUES(1, 'jack', 'mid', 1);  -- MySQL 5.7能添加进去

自增长

某列从1开始自动的增长

字段名整形 PRIMARY KEY AUTO_INCREMENT

添加自增长的字段的方式

-- 1
insert into xxx (字段1, 字段2,...) values(null, '值'...)

-- 2
insert into xxx (字段2,...) values('值'...)

-- 3
insert into xxx values(null, '值'...)

使用细节：

一般来说自增长是和primarykey配合使用的
自增长也可以单独使用[但是需要配合一个unique]
自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样使用）
自增长默认从1开始，你也可以通过如下命令修改alter table 表名 auto_increment=xxx;
如果添加数据是，给自增长字段（列）指定的具体值，则以指定的值为准
如果制定了自增长，一般来说，就按照自增长的规则来添加数据

索引

提高数据库的性能，索引是最物美价廉的东西了，不用加内存，不用该程序，不用调sql，查询速度就可能提高百倍千倍。

CREATE INDEX 索引名称 ON 表名(字段名/列名)

CREATE INDEX empno_index ON emp (empno)
-- empno_index 索引名称 
-- ON emp (empno) : 表示在 emp表的 empno列创建索引

注意：

索引占用磁盘空间；
创建一个列的索引之后，如果查询换成另一列，则索引不起作用，需要重建索引。

索引的原理

没有索引时，查询进行全表扫描（即使找到了也会继续往下查询，知道查完全表），查询速度慢
使用索引，会形成一个索引的数据结构，比如二叉树
索引的代价
- 磁盘占用
- 如果对表进行dml（修改update，删除delete，添加insert操作）语句，会对索引进行维护，对速度有影响
在实际项目中，select操作占比90%

索引的类型

主键索引，主键自动的为主索引（类型 Primary key）

create table t1 (
	id int primary key,  -- 主键，同时也是索引，称为主键索引
	`name` varchar(32));

唯一索引（UNIQUE）

create table t2 (
	id int unique,  -- id是唯一的，同时也是索引，称为unique索引
	`name` varchar(32));

普通索引（INDEX）
全文索引（FULLTEXT）[适用于MyISAM]

开发中一般不适用mysql自带的全文索引，而是使用：全文搜索Solr和ElasticSearch（ES）

创建索引

唯一索引

create table t2 (
	id int unique,  -- id是唯一的，同时也是索引，称为unique索引
	`name` varchar(32));

-- 或者
create table t2 (
	id int,
	`name` varchar(32));
create unique index id_index on t2(id);

普通索引
```
create index id_index on table_name(id);

-- 或者

alter table table_name add index id_index (id)
```
如何选择：

如果某列的值是不会重复的，则优先考虑使用unique索引，否则使用普通索引

主键索引

create table t1 (
	id int primary key,  -- 主键，同时也是索引，称为主键索引
	`name` varchar(32));
	
-- 或者
create table t1 (
	id int,
	`name` varchar(32));
ALTER TABLE t1 ADD PRIMARY KEY (id);

删除索引

首先查询索引名称

SHOW INDEX FROM table_name
删除索引

DROP INDEX 索引名 ON table_name

删除主键索引比较特别：

ALTER TABLE table_name DROP PRIMARY KEY

修改索引

先删除，再添加新的索引

查询索引

4种方式

-- 1
SHOW INDEX FROM tablename

-- 2
SHOW INDEXES FROM tablename

-- 3
SHOW KEYS FROM tablename

-- 4 不如上面的详细
DESC tablename

总结

在哪些列上适合使用索引？

较频繁的作为查询条件字段应该创建索引

select * from emp where empno =1
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sex=‘男’
更新非常频繁的字段不适合创建索引

select * from emp where logincount =1
不会出现在WHERE子句中字段不该创建索引

事务

事务原理

什么是事务：

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。
事务和锁：

当执行事务操作时（dml语句），mysql会在表上加锁，防止其它用户改表的数据。这对用户来讲是非常重要的。

mysql数据库控制台事务的几个重要操作（基本操作）

start transaction–开始一个事务
savepoint保存点名–设置保存点
rollback to保存点名–回退事务
rollback–回退全部事务
commit–提交事务，所有的操作生效，不能回退。同时将设置的保存点删除

-- 事务的一个重要的概念和具体操作
-- 演示
-- 1. 创建一张测试表
CREATE TABLE t9
	( id INT,
	  `name` VARCHAR(32));
-- 2. 开始事务
START TRANSACTION
-- 3. 设置保存点
SAVEPOINT a
-- 执行dml 操作
INSERT INTO t9 VALUES(100, 'tom');
SELECT * FROM t9;

SAVEPOINT b
-- 执行dml操作
INSERT INTO t9 VALUES(200, 'jack');

-- 回退到 b
ROLLBACK TO b;
-- 继续回退 a
ROLLBACK TO a;
-- 如果这样, 表示直接回退到事务开始的状态.
ROLLBACK

回退事务：在介绍回退事务前，先介绍一下保存点（savepoint)。保存点是事务中的点。用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点当执行回退事务时，通过指定保存点可以回退到指定的点。
提交事务：使用commit语句可以提交事务。当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效]。
事务细节：
1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2. 如果开始一个事务，你没有创建保存点。你可以执行rollback，默认就是回退到你事务开始的状态
3. 你也可以在这个事务中（还没有提交时），创建多个保存点。
  
  比如：
  
  save point aaa;
  
  执行dml；
  
  save point bbb;
4. 你可以在事务没有提交前，选择回退到哪个保存点
5. mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好使
6. 开始一个事务start transaction 或者 set autocommit = off;

隔离级别

事务隔离级别介绍：
1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
2. 如果不考虑隔离性，可能会引发如下问题：
  - 脏读（dirty read）：当一个事务读取另一个事务尚未提交的修改[insert，update， delete]时，产生脏读。
  - 不可重复读（nonrepeatable read）：一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
  - 幻读（phantom read）：查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
3. 事务隔离级别
  
  概念：Mysql隔离级别定义了事务与事务之间的隔离程度。
  - 注意可串行化加锁，会一直等待，只有其他控制台事务提交之后才能执行成功。
4. 查看当前mysql的隔离级别
  
  SELECT @@tx_isolation;
```
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
```
5. 设置隔离级别 READ UNCOMMITTED
  
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
```
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
```
隔离级别指令
1. 查看当前会话隔离级别
  
  SELECT @@tx_isolation;
2. 查看系统当前隔离级别
  
  SELECT @@global.tx_isolation;
3. 设置当前会话隔离级别
  
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
4. 设置系统当前隔离级别
  
  SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
5. mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改（因为该级别可以满足绝大部分项目需求）

事务的ACID特性

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另外一个一致性状态
隔离性（lsolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

存储引擎

基本介绍
1. MySQL的表类型由存储引l擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。
2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM、InnoBDB。
3. 这六种又分为两类，一类是”事务安全型”（transaction-safe），比如：InnoDB；其余都属于第二类，称为”非事务安全型”（non-transaction-safe)[mysiam和 memory]
查看所有的存储引擎

SHOW ENGINES;
主要存储引擎/表类型特点
细节
1. MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求
2. InnoDB存储引l擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引I擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引1。
3. MEMORY存储引I擎使用存在内存中的内容来创建表。
  - 每个MEMORY表只实际对应一个磁盘文件。
  - MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。
  - 但是一旦服务关闭，表中的数据就会丢失掉，表的结构还在。
如何选择存储引擎
1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快
2. 如果需要支持事务，选择InnoDB
3. Memory存储引l擎就是将数据存储在内存中，由于没有磁盘I/O的等待速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态()）
修改存储引擎

ALTER TABLE tablename ENGINE = 引擎名;

视图

看一个需求
基本概念

视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含列，其数据来自对应的真实表（基表）
视图和对应真实表（基本）的关系
总结：
1. 视图时根据基表（可以是多个基表）来创建的，视图是虚拟的表。
2. 视图也有列，数据来自基表
3. 通过视图可以修改基表的数据
4. 基表的改变也会影响到视图的数据

视图的基本使用

create view 视图名 as select 语句
alter view 视图名 asselect 语句 ——更新成新的视图
SHOW CREATEVIEW 视图名
drop view 视图名1，视图名2

-- 视图的使用
-- 创建一个视图emp_view01，只能查询emp表的(empno、ename, job 和 deptno ) 信息

-- 创建视图
CREATE VIEW emp_view01
	AS
	SELECT empno, ename, job, deptno FROM emp;

-- 查看视图
DESC emp_view01;
SELECT * FROM emp_view01;

-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;

视图细节

创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete]
视图中可以再使用视图，数据仍然来自基表，比如从emp_view01 视图中，选出empno和ename做出新视图

-- 视图的细节

-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm) 
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]

-- 修改视图 会影响到基表
UPDATE emp_view01
	SET job = 'MANAGER'
	WHERE empno = 7369;


-- 修改基本表， 会影响到视图
UPDATE emp
	SET job = 'CLERK'
	WHERE empno = 7369;


-- 3. 视图中可以再使用视图 , 比如从emp_view01 视图中，选出empno,和ename做出新视图
CREATE VIEW emp_view02
	AS
	SELECT empno, ename FROM emp_view01;

视图的最佳实践
1. 安全。
  
  一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
2. 性能。
  
  关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接（JOIN）。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
3. 灵活。
  
  如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

基表是多张表的情况

-- 视图的课堂练习
-- 针对 emp ，dept , 和   salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和 薪水级别[即使用三张表，构建一个视图]

/*
	分析: 使用三表联合查询，得到结果
	将得到的结果，构建成视图
*/
CREATE VIEW emp_view03
	AS
	SELECT empno, ename, dname, grade
		FROM emp, dept, salgrade
		WHERE emp.`deptno` = dept.`deptno` AND
		(sal BETWEEN losal AND hisal);
		
SELECT * FROM emp_view03;
DESC emp_view03;

MySQL管理

MySQL用户

我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限，所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用。

mysql中的用户，都存储在系统数据库mysgl中user表中

重要字段说明：
1. host：允许登录的“位置”，localhost表示该用户只允许本机登录。也可以指定ip地址，比如：192.168.1.100
2. user：用户名
3. authentication_string：密码，通过mysql的passwor()函数加密之后的密码。
创建用户

create user '用户名'@'允许登录的位置' identified by '密码'

创建用户，同时指定密码

删除用户

drop user '用户名'@'允许登录的位置'

-- Mysql用户的管理
-- 原因：当我们做项目开发时，可以根据不同的开发人员，赋给他相应的Mysql操作权限
-- 所以，Mysql数据库管理人员(root), 根据需要创建不同的用户，赋给相应的权限，供人员使用

-- 1. 创建新的用户
-- 解读 (1) 'zhw'@'localhost' 表示用户的完整信息 'zhw' 用户名 'localhost' 登录的IP
-- (2) 123456 密码, 但是注意 存放到 mysql.user表时，是password('123456') 加密后的密码
--     *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
CREATE USER 'zhw'@'localhost' IDENTIFIED BY '123456';
SELECT `host`, `user`, `authentication_string` FROM mysql.`user`;

-- 2. 删除用户
DROP USER 'zhw'@'localhost';

不同的数据库用户，操作的库和表不相同
- 用户修改密码
  - 修改自己的密码
    
    set password = password('密码');
  - 修改他人的密码（需要有修改用户密码权限）
    
    set password for '用户名'@'允许登录的位置' = password('密码');
```
--  修改自己的密码, 没问题

SET PASSWORD = PASSWORD('abcdef')

-- 修改其他人的密码， 需要权限

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');  
-- Access denied for user 'zhw'@'localhost' to database 'mysql'
```

MySQL权限

给用户授权

grant 权限列表 on 库.对象名 to ‘用户名’@'登录位置' [identified by‘密码']

说明
回收用户授权

revoke 权限列表 on 库.对象名 from ‘用户名'@'登录位置'；

权限生效指令

如果权限没有生效，可以执行下面命令

FLUSH PRIVILEGES;

-- 演示 用户权限的管理

-- 创建用户 honvin  密码 123 , 从本地登录
CREATE USER 'honvin'@'localhost' IDENTIFIED BY '123';
-- 使用root 用户创建 testdb  ,表 news
CREATE DATABASE testdb;
CREATE TABLE news (
	id INT,
	content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;

-- 给 honvin 分配查看 news 表和 添加news的权限
GRANT SELECT, INSERT
	ON testdb.`news`
	TO 'honvin'@'localhost';
	
-- 可以增加update权限
GRANT UPDATE
	ON testdb.`news`
	TO 'honvin'@'localhost';
	
	
-- 修改 honvin 的密码 abc
SET PASSWORD FOR 'honvin'@'localhost' = PASSWORD('abc');

-- 回收 honvin 用户在 testdb.news 表的所有权限
REVOKE ALL
	ON testdb.`news`
	FROM 'honvin'@'localhost';


-- 删除 honvin
DROP USER 'honvin'@'localhost';

细节

在创建用户的时候，如果不指定Host，则为%，%表示表示所有IP都有连接权限

create user xxx;
你也可以这样指定create user 'xxx'@'192.168.1.%' ，表示xxx用户在192.168.1.*的ip可以登录mysql
在删除用户的时候，如果host不是%，需要明确指定‘用户'@'host值'

-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限 
-- create user  xxx;
CREATE USER jack;

SELECT `host`, `user`, `authentication_string` FROM mysql.`user`;


-- 你也可以这样指定 
-- create user  'xxx'@'192.168.1.%'  表示 xxx用户在 192.168.1.*的ip可以登录mysql
CREATE USER  'smith'@'192.168.1.%'; 


-- 在删除用户的时候，如果 host 不是 %, 需要明确指定  '用户'@'host值'
DROP USER jack;   -- 默认就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%';

MySQL权限

[外链图片转存中…(img-ESU6ivnK-1702983094581)]

给用户授权

grant 权限列表 on 库.对象名 to ‘用户名’@'登录位置' [identified by‘密码']

说明

[外链图片转存中…(img-rFITIXCR-1702983094582)]
回收用户授权

revoke 权限列表 on 库.对象名 from ‘用户名'@'登录位置'；

权限生效指令

如果权限没有生效，可以执行下面命令

FLUSH PRIVILEGES;

-- 演示 用户权限的管理

-- 创建用户 honvin  密码 123 , 从本地登录
CREATE USER 'honvin'@'localhost' IDENTIFIED BY '123';
-- 使用root 用户创建 testdb  ,表 news
CREATE DATABASE testdb;
CREATE TABLE news (
	id INT,
	content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;

-- 给 honvin 分配查看 news 表和 添加news的权限
GRANT SELECT, INSERT
	ON testdb.`news`
	TO 'honvin'@'localhost';
	
-- 可以增加update权限
GRANT UPDATE
	ON testdb.`news`
	TO 'honvin'@'localhost';
	
	
-- 修改 honvin 的密码 abc
SET PASSWORD FOR 'honvin'@'localhost' = PASSWORD('abc');

-- 回收 honvin 用户在 testdb.news 表的所有权限
REVOKE ALL
	ON testdb.`news`
	FROM 'honvin'@'localhost';


-- 删除 honvin
DROP USER 'honvin'@'localhost';

细节

在创建用户的时候，如果不指定Host，则为%，%表示表示所有IP都有连接权限

create user xxx;
你也可以这样指定create user 'xxx'@'192.168.1.%' ，表示xxx用户在192.168.1.*的ip可以登录mysql
在删除用户的时候，如果host不是%，需要明确指定‘用户'@'host值'

-- 说明 用户管理的细节
-- 在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限 
-- create user  xxx;
CREATE USER jack;

SELECT `host`, `user`, `authentication_string` FROM mysql.`user`;


-- 你也可以这样指定 
-- create user  'xxx'@'192.168.1.%'  表示 xxx用户在 192.168.1.*的ip可以登录mysql
CREATE USER  'smith'@'192.168.1.%'; 


-- 在删除用户的时候，如果 host 不是 %, 需要明确指定  '用户'@'host值'
DROP USER jack;   -- 默认就是 DROP USER 'jack'@'%'
DROP USER 'smith'@'192.168.1.%';