mysql基础知识汇总

本文自行整理，只做学习记忆之用，若有不当之处请指出

一、数据库三层结构

（1）所谓安装Mysql数据库，就是在主机安装一个数据库管理系统(DBMS),这个管理程序可以管理多个数据库。DBMS(database manage system)

（2）一个数据库中可以创建多个表，以保存数据(信息)。

（3）数据在数据库中的存储方式

二、SQL 语句分类

（1）DDL:数据定义语句[create表，库…]
（2）DML:数据操作语句[增加insert,修改update,删除delete]
（3）DQL:数据查询语句[select]
（4）DCL:数据控制语句[管理数据库：比如用户权限grant revoke]

三、创建数据库

1.语句：

CREATE DATABASE [IF NOT EXISTS] db_name
[DEFAULT]CHARACTER SET charset_nameI 
[DEFAULT]COLLATE collation_name;

2.说明：

（1）CHARACTER SET:指定数据库采用的字符集，如果不指定字符集，8.0以上版本默认utf8mb4
（2）COLLATE:指定数据库字符集的校对规则(常用的utf8mb4_bin[区分大小写]、utf8mb4_general_ci[不区分大小写]、utf8mb4_0900_ai_ci[不区分大小写]，注意8.0以上版本默认是utf8mb4_0900_ai_ci)

四、查看、删除数据库

1.显示数据库语句：

SHOW DATABASES

2.显示数据库创建语句：

SHOW CREATE DATABASE db_name

3.数据库删除语句[一定要慎用]:

DROP DATABASE [IF EXISTS] db_name

注意：用反引号将数据库名或表名包裹起来，可以规避掉关键字

五、备份恢复数据库和表

1.备份数据库(注意：在DOS执行,不要在power shell备份，否则后面恢复时会报错，可以在cmd执行)命令行

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

mysqldump -u root -p -B db01 db02 > D:\\db.sql

2.恢复数据库(注意：进入Mysql命令行再执行)

Source 文件名.sql

 source D:\\db.sql

第二个恢复方法：直接将db.sql的内容复制到查询编辑器执行 

3.备份恢复数据库的表

备份：
mysqldump -u 用户名 -p 数据库 表1 表2 表n > d:\\文件名.sql

恢复方式如上(注意：在哪个数据库下执行的就会导入到哪个数据库)

六、创建表

1.语句

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

2.说明

field:指定列名

datatype:指定列类型(字段类型)

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

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

engine:引擎

3.示例

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

七、Mysql 常用数据类型(列类型)

1.数据类型一览图

2.数值型(整数)的基本使用 

（1）使用规范：在能够满足需求的情况下，尽量选择占用空间小的

（2）示例：

如果没有指定 unsigned , 则 TINYINT 就是有符号 ，如果指定 unsigned , 则 TINYINT 就是无符号 0-255

CREATE TABLE t3 (
id TINYINT
);
 
CREATE TABLE t4 (
id TINYINT UNSIGNED
);

3.数值型(bit)的基本使用

（1）基本使用
create table t05 (num bit(8));

insert into t05(3);

insert into t05 values(65);
（2）细节说明

• bit字段显示时，按照位的方式显示.
• 查询的时候仍然可以按照数值查询
• 如果一个值只有0,1可以考虑使用bit(1),可以节约空间位类型。
• M指定位数，默认值1,范围1-64
• 使用不多

4.数值型(小数)的基本使用

（1）FLOAT/DOUBLE [UNSIGNED]：Float单精度精度，Double双精度.

（2）DECIMAL[M,D] [UNSIGNED]

• 可以支持更加精确的小数位。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。
• 如果D是0,则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10。
• 如果希望小数的精度高，推荐使用decimal

（3）示例

CREATE TABLE t06 (
num1 FLOAT unsigned, 
num2 DOUBLE, 
num3 DECIMAL(30,20)
);

5.字符串的基本使用

（1）CHAR(size)：固定长度字符串最大255字符

（2）VARCHAR(size)：可变长度字符串最大65532字节(utf8编码最大21844字符，gdk编码最大32766字符，1-3个字节用于记录大小)

（3）示例

CREATE TABLE t09 (
`name` CHAR(255)
);
 
CREATE TABLE t10 (
`name` VARCHAR(32766)
) CHARACTER SET gbk;

6.字符串使用细节

（1）细节1

• char(4)//这个4表示字符数(最大255),不是字节数，不管是中文还是字母都是放四个，按字符计算.
• varchar(4)//这个4表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的.

（2）细节2

• char(4)是定长(固定的大小),就是说，即使你插入'aa',也会占用分配的4个字符的空间.
• varchar(4)是变长(变化的大小),就是说，如果你插入了'aa',实际占用空间大小并不是4个字符，而是按照实际占用空间来分配
• 说明：varchar本身还需要占用1-3个字节来记录存放内容长度，实际占用空间=L(实际数据大小)+(1-3)字节

（3）细节3

什么时候使用char,什么时候使用varchar

• 如果数据是定长，推荐使用char,比如密码，邮编，手机号，身份证号码等.
• 如果一个字段的长度是不确定的，我们使用varchar,比如留言，文章

查询速度：char >varchar

（4）细节4

• 在存放文本时，也可以使用Text数据类型.可以将TEXT列视为VARCHAR列，注意Text不能有默认值.大小0-2^16字节
• 如果希望存放更多字符，可以选择MEDIUMTEXT O-2^24或者LONGTEXT 0~2^32

7.日期类型的基本使用

（1）语句

CREATE TABLE birthday6(
t1 DATE,
t2 DATETIME,
t3 TIMESTAMP NOT NULL 
DEFAULTCURRENT TIMESTAMP 
ON UPDATECURRENT_TIMESTAMP 
);
#NOT NULL DEFAULTCURRENT TIMESTAMP ON UPDATECURRENT_TIMESTAMP关键字表示自动更新为当前时间

（2）细节说明

• TimeStamp在Insert和update时，自动更新

（3）示例

CREATE TABLE t14 (
    birthday DATE , -- 生日
    job_time DATETIME, -- 记录年月日 时分秒
    login_time TIMESTAMP
        NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP); -- 登录时间, 如果希望 login_time 列自动更新, 需要配置
SELECT * FROM t14;
INSERT INTO t14(birthday, job_time)
    VALUES('2022-11-11','2022-11-11 10:10:10'); 
-- 如果我们更新 t14 表的某条记录，login_time 列会自动的以当前时间进行更新

八、修改表

1.使用ALTER TABLE语句追加，修改，或删除列的语法

（1）add:添加列

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

（2）modify:修改列的数据长度，数据类型和约束条件

ALTER TABLE tablename MODIFY column datatype [DEFAULT expr];

这里字段的数据长度，数据类型和约束条件，需要进行完整定义，并不是写上新添加的就可以

（3）change:修改列名

alter table table_name change old_name new_name datatype...; --新字段需要完整定义

这里要注意的是你重新命名的新字段需要进行完整定义，并不是改字段名就可以

（4）drop：删除列

ALTER TABLE tablename DROP column;

2.修改表名：Rename table 表名 to 新表名

3.修改表字符集：alter table 表名 character set 字符集

4.查看表的结构：desc 表名

九、删除表

1.truncate

（1）只能操作表，将表中数据全部删除，在功能上和不带where子句的delete语句相同

truncate table table_name;

（2）说明

• 默认情况下，truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。所以使用的系统和事务日志资源少，可以使用reuse storage; truncate语句实际是删除原来的表然后重新建立一个新表
• truncate是DDL语言, 操作立即生效,自动提交，原数据不放到rollback segment中,不能回滚. 由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
• 删除内容、释放空间但不删除表的结构(定义)。

2.drop

（1）drop语句将删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index)

drop table table_name;

（2）说明

• 删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态.
• drop也属于DDL语言，立即执行，执行速度最快
• 删除内容和定义，释放空间。

十、操作表数据：insert、update、delete、select

1.insert插入

（1）语法

INSERT INTOtable_name [(column[,column...])]VALUES(value [,value...]);

（2）说明

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

2.update修改

（1）语法

使用 update 语句修改表中数据

UPDATE tbl_name
SET col_namel=exprl [,col_name2=expr2 ...] 
[WHERE where_definition]

（2）说明

• UPDAE语法可以用新值更新原有表行中的各列。
• SET子句指示要修改哪些列和要给予哪些值。
• WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行(记录)
• 如果需要修改多个字段，可以通过set 字段1=值1,字段2=值2…

3.delete删除

（1）语法

使用 delete 语句删除表中数据

delete from tbl_name [WHERE where_definition]

（2）说明

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

4.select查询

（1）语法

SELECT [DISTINCT] */column1,column2.column3.. FROM table_name;

（2）说明

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

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

SELECT */column|expression,column2|expression,.. FROM tablename;

（4）使用 as 语句给字段起别名(as可以省略)

别名为中文时可以不加单引号，加了也不影响

SELECT column_name as 别名 from 表名；

（5）where 子句中经常使用的运算符

说明：

• like模糊查询中，%表示0到多个任意字符，_表示单个任意字符

（6）使用 order by 子句排序查询结果

SELECT column1,column2.column3.. 
FROM  table 
order by column asc|desc,  ...

• Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的别名。
• Asc升序[默认]、Desc降序
• ORDER BY子句应位于SELECT语句的结尾。如果有limit，放在limit前面
• 可通过order by column1 asc,column2 desc形式对多个字段进行排序，写在前面的优先排

（7）使用 limit 分页查询

语法：

select ...limit start,rows

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

公式：LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数

 案例：

-- 第 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;

十一、mysql常用函数

1、合计/统计函数

（一）count

Count返回行的总数

（1）语法

Select count(*)|count(列名) from table_name [WHERE where_definition]

（2）说明

• count(*) 和 count(列) 的区别：count(*) 返回满足条件的记录的行数，count(列):统计满足条件的某列有多少个，但是会排除 为 null 的情况
• count里面也可以写入一个表达式，例如(统计null值的个数，1那里只要是个非空值就会统计进去)：
  SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL)) FROM emp
• 对所统计的列进行去重，需要把distinct写在括号里面，例：

  SELECT COUNT(DISTINCT mgr) FROM emp;

（二）sum

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

Select sum(列名),sum(列名).… from tablename [WHERE where_definition]

注意：sum仅对数值起作用，其他没有意义。

（三）avg

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

Select avg(列名),avg(列名)… from tablename [WHERE where_definition]

（四）max/min

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

Select max(列名) from tablename [WHERE where_definition]

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

SELECT column1,column2.column3.. FROM table group by column1，column2

示例：

-- 显示每个部门的每种岗位的平均工资和最低工资
SELECT job AS 岗位,AVG(sal) AS 平均工资,MAX(sal) AS 最高工资 FROM emp GROUP BY deptno,job

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

SELECT column1,column2.column3.. FROM table group by column having 条件

示例

-- 显示平均工资低于2000的部门号和它的平均工资
SELECT deptno,AVG(sal) AS 平均工资 FROM emp GROUP BY deptno HAVING 平均工资 < 2000

（七） group by，having，order by，limit顺序

如果select语句同时包含有group by,having,limitorder by

那么他们的顺序是group by,having,order by,limit

2、字符串相关函数

案例：

-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp; 
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp; 
-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 是一个系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL; 
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp; 
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp; 
-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp; 
-- LENGTH (string )string 长度[按照字节]
SELECT LENGTH(ename) FROM emp;
 -- REPLACE (str ,search_str ,replace_str )
-- 在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp', 'hsp') FROM DUAL; 
-- SUBSTRING (str , position [,length ])
-- 从 str 的 position 开始(从 1 开始计算),取 length 个字符,length如果省略，默认为取到字符串末尾
-- 从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp; 
-- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' 教育') FROM DUAL;
SELECT RTRIM('教育 ') FROM DUAL;
SELECT TRIM(' 教育 ') FROM DUAL; 
-- 综合: 以首字母小写的方式显示所有员工 emp 表的姓名
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)), SUBSTRING(ename,2)) AS new_name FROM emp

3、数学相关函数

说明：

• rand()返回一个随机浮点值v,范围在0到1之间(即，其范围为O≤v≤1.0)。若已指定一个整数参数N,则它被用作种子值，使每次产生的随机数为同一个数。

案例：

-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL;
 -- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL;
 -- CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
 -- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL; 
-- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECT CONV(16, 16, 10) FROM DUAL; 
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL;
-- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL; 
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL; 
-- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，该随机数也不变了
SELECT RAND() FROM DUAL;

4、时间日期相关函数

说明：

• DATE_ADD()中的interval后面可以是year month day hour minute second 
• DATE_SUB()中的interval后面可以是year month day hour minute second 
• DATEDIFF(date1,date2)得到的是天数，而且是date1-date2的天数，因此可以取负数
• date()、DATE_ADD()、DATE_SUB()、DATEDIFF()这四个函数的日期类型可以是date,datetime或者timestamp
• unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
• FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期。
• 例：FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s')
• 日期类型可以直接使用>、<、=进行比较，但需要注意格式必须相同

案例：

-- 日期时间相关函数
-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL; 
-- CURRENT_TIME ( )当前时间
SELECT CURRENT_TIME() FROM DUAL; 
-- CURRENT_TIMESTAMP ( ) 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL; 
-- 创建测试表 信息表
CREATE TABLE mes(
id INT ,
content VARCHAR(30), 
send_time DATETIME
);
-- 添加一条记录
INSERT INTO mes VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;
SELECT NOW() FROM DUAL; 
-- 上应用实例
-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间. 
SELECT id, content, DATE(send_time) FROM mes; 
-- 查询在 10 分钟内发布的新闻 (两种方法)
SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW()
SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)
-- 求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL; 
-- timediff() 返回相差多少小时多少分钟多少秒
SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL; 
-- YEAR|Month|DAY| DATE (datetime )
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
 -- unix_timestamp() : 返回的是 1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL; 
-- FROM_UNIXTIME() : 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的，表示年月日，%H、%i、%s表示时分秒
SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;

5、加密和系统函数

案例演示：

1.USER() 查询用户：可以查看登录到 mysql 的有哪些用户，以及登录的 IP

SELECT USER() FROM DUAL;

2. DATABASE()查询当前使用数据库名称

SELECT DATABASE();

3.- MD5(str) 为字符串算出一个 MD5的 32位的字符串，常用(用户密码)于加密 

SELECT MD5('hsp') FROM DUAL;

4.PASSWORD(str) 加密函数：8.0版本以下的MySQL 数据库的用户密码就是 PASSWORD 函数加密 

但是mysql8.0版本去掉了password函数，可以使用sha1(str)或者sha(str)代替,sha1等同于sha

SELECT SHA1('123456') FROM DUAL;

 mysql8.0版本数据库用户密码似乎(不确定)使用的是SHA2(str, hash_length)加密，hash_length支持的值224, 256, 384, 512, or 0。0等同于256.

SELECT SHA2('123456',256) FROM DUAL;

6、流程控制函数

 案例：

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, CASE
WHEN job = 'CLERK' THEN '职员' 
WHEN job = 'MANAGER' THEN '经理' 
WHEN job = 'SALESMAN' THEN '销售人员' 
ELSE job END AS 'job' 
FROM emp;

方式二：

SELECT ename, CASE job
WHEN 'CLERK' THEN '职员' 
WHEN 'MANAGER' THEN '经理' 
WHEN 'SALESMAN' THEN '销售人员' 
ELSE job END AS 'job' 
FROM emp

十二、多表查询

介绍：多表查询是指基于两个和两个以上的表查询.在实际应用中，查询单个表可能不能满足你的需求,就需要使用到多表查询

1.基本使用

语法：select * from table1,table2

在不加任何限制条件时：对两个表进行查询，规则

• 从第一张表中，取出一行和第二张表的每一行进行组合，返回结果(含有两张表的所有列)
• 一共返回的记录数 第一张表的行数*第二张表的行数
• 这样的多表查询默认处理返回的结果，称为笛卡尔集
• 所有多表查询的关键就是要写出正确的过滤条件
• 当需要指定显示某个表的某个列，形式：表名 .列名

2.自连接

自连接是指在同一张表的连接查询[将同一张表看做两张表]。

说明：

• 把同一张表当做两张表使用
• 需要给表取别名，形式：表名 as 表别名(as可省略)
• 列名不明确，可以指定列的别名，形式：列名 as 列的别名(as可省略)

案例：

显示公司员工名字和他的上级的名字

SELECT 员工.ename AS 员工名,上级.ename AS 上级名 
FROM emp 员工,emp 上级 
WHERE 员工.mgr = 上级.empno

3.子查询 

（一）什么是子查询

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

（二）单行子查询

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

案例：

显示与 SMITH 同一部门的所有员工

SELECT * FROM emp 
WHERE deptno = (
SELECT deptno FROM emp 
WHERE ename = 'SMITH'
)

（三）多行子查询

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

案例：

查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10自己的.

SELECT ename,job,sal,deptno FROM emp 
WHERE job IN (
SELECT job FROM emp 
WHERE deptno = 10
) 
AND deptno != 10

扩展：可以将子查询的结果当做一张临时表使用

（四）在多行子查询中使用 all 操作符

all表示所有

案例：

显示工资比部门 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
)

（五）在多行子查询中使用 any 操作符

any表示任意一个

案例：

显示工资比部门 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
)

（六）多列子查询 

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

多字段比较形式：(字段1,字段2…)=(select字段1,字段2 from。。。。)

案例：

查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)

SELECT * FROM emp 
WHERE (deptno,job) = (
SELECT deptno,job FROM emp 
WHERE ename = 'allen'
) 
AND ename != 'allen'

4.外连接 

使用场景：前面的多表查询，是利用where子句对两张表或者多张表，形成的迪卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不会显示。如果希望将没有匹配的也显示出来，就需要用到外连接（比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
）

外连接分为：左外连接和右外连接

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

语法：select..from 表1 left join 表2 on 条件(表1:就是左表,表2:就是右表)

案例：

列出部门名称和这些部门的员工名称和工作， 同时要求 显示出那些没有员工的部门。

SELECT dname, ename, job
FROM dept 
LEFT JOIN emp
ON dept.deptno = emp.deptno

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

语法：select..from 表1 right join 表2 on条件(表1:就是左表,表2:就是右表)

案例：

列出部门名称和这些部门的员工名称和工作， 同时要求 显示出那些没有员工的部门。

SELECT dname, ename, job
FROM emp 
RIGHT JOIN dept
ON dept.deptno = emp.deptno

十三、表复制

自我复制数据(蠕虫复制)：有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。

1.复制步骤

（1）创建一个新表

CREATE TABLE my_tab01(
 id INT,
 `name` VARCHAR(32),
 sal DOUBLE,
 job VARCHAR(32),
 deptno INT
)

（2）把另外一张表的记录复制到新表上 

INSERT INTO my_tab01
(id, `name`, sal, job,deptno)
SELECT 
empno, ename, sal, job, deptno 
FROM emp;

（3）自我复制

INSERT INTO my_tab01
SELECT * FROM my_tab01;

2.如何删除掉一张表的重复记录

步骤如下：

准备工作

创建一张表 my_tab02, 让 my_tab02 有重复的记录(可使用like关键字创建，复制已有表的表结构)

-- 这个语句 把 emp 表的结构，复制到 my_tab02
CREATE TABLE my_tab02 LIKE emp;

复制数据 

INSERT INTO my_tab02 SELECT * FROM emp;
 
INSERT INTO my_tab02 SELECT * FROM my_tab02;

开始去重，步骤如下

(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

1.union all

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

select ename,sal,job from emp where sal>2500 
union all
Select ename,sal,job from emp where job='MANAGER';

2.union

该操作与union all相似，但是会自动去掉结果集中的重复行

select ename,sal,job from emp where sal>2500
union
select ename,sal,job from emp wherejob='manager';

十五、约束

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

1.primary key(主键)

语法：字段名 字段类型 primary key

说明：

（1）primary key不能重复而且不能为null。

（2）一张表最多只能有一个主键，但可以是复合主键

（3）主键的指定方式有两种

• 直接在字段类型后指定：字段名 字段类型 primakry key
• 在表定义最后写primary key(列名);

（4）使用desc 表名，可以看到primary key的情况.

（5）在实际开发中，每个表往往都会设计一个主键.

使用案例：

基本使用(两种指定方式)

--第一种方式
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(列名)
);

演示复合主键 (id 和 name 做成复合主键)

CREATE TABLE t18(
id INT , 
`name` VARCHAR(32), 
email VARCHAR(32), 
PRIMARY KEY (id, `name`) -- 这里就是复合主键
);

2.not null(非空)

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

语法：字段名 字段类型 not null

3.unique(唯一)

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

语法：字段名 字段类型 unique

说明：

• 如果没有指定not null,则unique字段可以有多个null
• 一张表可以有多个unique字段

4.foreign key(外键)

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

语法：FOREIGN KEY (本表字段名) REFERENCES 主表名 (主键名 或 unique字段名)

说明：

（1）外键指向的表的字段，要求是primary key或者是unique

（2）表引擎的类型需要是innodb,这样的表才支持外键

（3）外键字段的类型要和主键字段的类型一致(长度可以不同)

（4）外键字段的值，必须在主键字段中出现过，或者外键字段的值为null(前提该字段允许为null)

（5）一旦建立主外键的关系，主表里的数据就不能随意删除了。需要先删除从表里的数据再删除主表里的数据

案例：

-- 创建 主表 my_class
CREATE TABLE my_class (
id INT PRIMARY KEY , -- 班级编号
`name` VARCHAR(32) NOT NULL DEFAULT ''); 
 
-- 创建 从表 my_stu
CREATE TABLE my_stu (
id INT PRIMARY KEY , -- 学生编号
`name` VARCHAR(32) NOT NULL DEFAULT '', 
class_id INT , -- 学生所在班级的编号
-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id)
)

5.check

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

注意：oracle和sql server均支持check,但是mysql5.7目前还不支持check(语法上是支持的，但不会生效),mysql8.0.16才开始支持

基本语法：列名 类型 check (条件)

案例：

CREATE TABLE t23 (
id INT PRIMARY KEY, 
`name` VARCHAR(32) , 
sex VARCHAR(6) CHECK (sex IN('man','woman')), 
sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);

说明：在mysql中实现check的功能，一般是在程序中控制，或者通过触发器完成。

十六、自增长

使用场景：在某张表中，存在一个id列(整数),我们希望在添加记录的时候，该列从1开始，自动的增长，就需要用到自增长

语法：字段名 整型 primary key auto_increment

如果一个字段设置了自增长，可通过如下方式添加 自增长的字段

（1）insert into xxx(字段1,字段2……) values(null,'值'…);

（2）insert into xxx(字段2……) values('值1','值2’…);

（3）insert into xxx values(null,'值1',.…)

细节说明：

（1）一般来说自增长是和primary key配合使用的

（2）自增长也可以单独使用（但是需要配合一个unique）

（3）自增长修饰的字段为整数型的(虽然小数也可以但是很少这样使用)

（4）自增长默认从1开始，你也可以通过如下命令修改：

alter table 表名 auto increment = 新的开始值；

（5）如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则来添加数据.

案例：

-- 创建表
CREATE TABLE t24(
id INT PRIMARY KEY AUTO_INCREMENT, 
email VARCHAR(32) NOT NULL DEFAULT '', 
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
 
-- 测试自增长的使用
INSERT INTO t24
VALUES(NULL, 'tom@qq.com', 'tom');
 
INSERT INTO t24 (email, `name`) 
VALUES('wwj@sohu.com', 'hsp');

十七、索引

1.介绍

使用场景：说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql,查询(select)速度就可能提高百倍干倍。

原理：会对添加了索引的字段形成一个索引的数据结构，比如二叉树

索引的代价：

（1）磁盘占用空间
（2）会影响dml(update delete insert)语句的效率

2.索引的类型

（1）主键索引，主键自动的为主索引(类型Primary key)
（2）唯一索引(UNIQUE)
（3）普通索引(INDEX)
（4）全文索引(FULLTEXT)(适用于MylSAM存储引擎)
一般开发，不使用mysql自带的全文索引，而是使用：全文搜索 Solr和ElasticSearch(ES)

3.索引的使用：

（1）添加索引

create [UNIQUE] index index_name on tbl_name(col_name [(length)][ASC|DESC],……);
 
alter table table_name ADD INDEX [index name] (col_name…)

示例：

-- 添加唯一索引方式 1
CREATE UNIQUE INDEX id_index ON t25 (id); 
 
-- 添加唯一索引方式 2
ALTER TABLE t25 ADD UNIQUE(id);
 
-- 添加普通索引方式 1
CREATE INDEX id_index ON t25 (id);
 
-- 添加普通索引方式 2
ALTER TABLE t25 ADD INDEX id_index (id)

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

（2）添加主键(索引)

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

示例： 

ALTER TABLE t26 ADD PRIMARY KEY (id)

（3）删除索引

DROP INDEX index_name ON tbl_name
 
alter table table_name drop INDEX index_name;

 示例：

--方式一
DROP INDEX id_index ON t25
 
--方式二
alter table t25 drop index id_index

（4）删除主键索引比较特别：

alter table tb drop primary key;

示例：

ALTER TABLE t26 DROP PRIMARY KEY

（5）查询索引(三种方式)

show index(es) from table_name;
 
show keys from table_name;
 
desc table_Name;

示例：

-- 1. 方式
SHOW INDEX FROM t25
 
-- 2. 方式
SHOW INDEXES FROM t25
 
-- 3. 方式
SHOW KEYS FROM t25
 
-- 4 方式
DESC t25

4.哪些列上适合使用索引

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

（2）唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sex ='男‘

（3）更新非常频繁的字段不适合创建索引

（4）不会出现在WHERE子句(即不会作为查询条件)中字段不该创建索引

十八、事务

1.事务介绍

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

2.事务和锁

（1）介绍：当执行事务操作时(dml语句),mysql会在表上加锁，防止其它用户改表的数据.

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

• start transaction --开始一个事务
• savepoint 保存点名 --设置保存点
• rollback to 保存点名 --回退事务
• rollback  --回退全部事务
• commit --提交事务，所有的操作生效，不能回退

案例：

-- 1. 创建一张测试表
CREATE TABLE t27
( id INT, `name` VARCHAR(32)); 
 
-- 2. 开始事务
START TRANSACTION;
 
-- 3. 设置保存点
SAVEPOINT a;
 
-- 执行 dml 操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;
 
SAVEPOINT b;
 
-- 执行 dml 操作
INSERT INTO t27 VALUES(200, 'jack');
 
-- 回退到 b
ROLLBACK TO b;
 
-- 继续回退 a
ROLLBACK TO a;
 
-- 直接回退到事务开始的状态. 
ROLLBACK;
 
COMMIT;

3.回退事务

介绍：在介绍回退事务前，先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务，当结束事务时(commit),会自动的删除该事务所定义的所有保存点.当执行回退事务时，通过指定保存点可以回退到指定的点

注意：当回退事务时，如果中间存在其他的保存点，这些保存点会被删除

4.提交事务

介绍：使用commit语句可以提交事务.当执行了commit语句子后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用commit语句结束事务子后，其它会话(其他连接)将可以查看到事务变化后的新数据(所有数据就正式生效.)

5.事务细节说明

（1）如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚

（2）如果开始一个事务，没有创建保存点.可以执行rollback,默认回退到事务开始的状态.

（3）你也可以在这个事务中(还没有提交时),创建多个保存点.比如：

savepoint aaa;

执行dml；

savepoint bbb;

（4）你可以在事务没有提交前，选择回退到哪个保存点.

（5）mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好使.

（6）开始一个事务start transaction、set autocommit=off;

6.事务隔离级别

（一）事务隔离级别介绍

（1）多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

（2）如果不考虑隔离性，可能会引发如下问题：

• 脏读：当一个事务读取另一个事务尚末提交的改变(update,insert,delete)时，产生脏读
• 不可重复读：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
• 幻读：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读。
• 理解：脏读,读取到另一个事务未提交数据，不可重复读，和脏读区别是读取了另一个事务已经提交的数据。 脏读和不可重复读 都强调是同一条数据的变化。幻读，读取到另一个事务操作的数据，重点在于数据的条数变化！
• 理解：脏读其实包含了不可重复读和幻读，因为没提交的改变都被读取到了，那提交了的肯定会被读取到。也就是说只要发生了脏读，就一定会发生不可重复读或幻读

（二）事务隔离级别一览

概念：Mysql隔离级别定义了事务与事务之间的隔离程度。

说明：

• V可能出现X不会出现
• 注意：可重复度只能避免查询状态下的幻读，对于添加或删除操作，仍然会出现幻读

比如：假设对一个表进行操作，id是主键，事务a中添加id为400的记录并提交，并且b事务想添加id为400的记录，是添加不了的，因为主键是唯一，id为400的记录已经在事务a中添加了，而事务b查询又显示没有，就像幻觉一样

• Serializable事务a对表进行操作时 在检测到有事务b在对表进行操作并且没有提交,事务a就会卡住，直到事务b提交，事务a才会继续执行，此时事务a如果进行查询是可以查询到事务b对表进行的改变的。
• 值得注意的是，不可重复读和幻读的定义都是同一查询进行多次后 结果返回不一致，Serializable事务没有发生多次查询不一致的情况，只是单纯被卡在外面，进不去了

（三）设置事务隔离级别

（1）查看当前会话隔离级别

mysql8以下版本：select @@tx.isolation;

mysql8以上版本：select @@transaction.isolation;

（2）查看系统当前隔离级别

mysql8以下版本：select @@global.tx_isolation;

mysql8以上版本：select @@global.transaction_isolation;

（3）设置当前会话隔离级别
set session transaction isolation level repeatable read;

（4）设置系统当前隔离级别
set global transaction isolation level repeatable read;

（5）mysql默认的事务隔离级别是repeatable read,一般情况下，没有特殊要求，没有必要修改(因为该级别可以满足绝大部分项目需求)

（6）可修改默认隔离级别：全局修改，修改my.ini配置文件，在最后加上
transaction-isolation =REPEATABLE-READ

(可选参数有：READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE.)

7.事务的 ACDI 特性

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

（2）一致性(Consistency)：事务必须使数据库从一个一致性状态变换到另外一个一致性状态

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

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

十九、mysql 表类型和存储引擎

1.基本介绍

（1）MySQL的表类型由存储引擎(Storage Engines)决定，主要包括MylSAM、innoDB、Memory等。

（2）MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG MYISAM、MYISAM、InnoDB。

（3）这六种又分为两类，一类是”事务安全型”(transaction-safe),比如：InnoDB;其余都属于第二类，称为”非事务安全型”(non-transaction-safe)[mysiam和memory]。

显示当前数据库支持的存储引擎

show engines;

2.主要的存储引擎/表类型特点

3.MyISAM、InnoDB、MEMORY详解

（1）MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求

（2）InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

（3）MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MySQL服务关闭，表中的数据就会丢失掉，表的结构还在。

4.如何选择表的存储引擎

（1）如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择，速度快

（2）如果需要支持事务，选择InnoDB。

（3）Memory存储引擎就是将数据存储在内存中，由于没有磁盘l/O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法:用户的在线状态())

5.修改存储引擎

ALTER TABLE 表名 ENGINE = 储存引擎；

示例：

ALTER TABLE `account` ENGINE = MYISAM;

二十、视图(view)

1.基本概念

介绍：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，但视图本身并不储存数据，其数据来自对应的真实表(基表)

说明：

• 视图是根据基表(可以是多个基表)来创建的，视图是虚拟的表
• 视图也有列，数据来自基表
• 通过视图可以修改基表的数据
• 基表的改变，也会影响到视图的数据

2.视图的基本使用

（1）create view 视图名 as select语句 --新建一个视图

CREATE VIEW emp_view01 AS
SELECT empno, ename, job, deptno FROM emp;

（2）desc 视图名 -- 查看视图

DESC emp_view01

（3）alter view 视图名 as select语句 --更新成新的视图，相当于重新定义了一个视图

ALTER VIEW emp_view01 AS
SELECT empno, ename, job, deptno FROM emp;

（4）SHOW CREATE VIEW 视图名 --查看创建视图的指令

SHOW CREATE VIEW emp_view01

（5）drop view 视图名1,视图名2 --删除视图

DROP VIEW emp_view01;

3.视图细节说明

（1）创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式：视图名.frm)没有数据文件(数据文件以.ibd为后缀)

（2）视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]

（3）视图中可以再使用视图，数据仍然来自基表

CREATE VIEW emp_view02 AS
SELECT empno, ename FROM emp_view01

4.什么时候使用视图

（1）安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。

（2）性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时，数据库查询通常会用到连接(JOIN)。这样做不但麻烦，效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。

（3）灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

二十一、Mysql 用户管理

1.Mysql 用户

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

查看所有用户：

select * from mysql.user

其中user表的重要字段说明：

（1）host:允许登录的“位置”:localhost表示该用户只允许本机登录，也可以指定ip地址，比如：192.168.1.100
（2）user:用户名；
（3）authentication_string:密码，是通过mysql的password()函数加密之后的密码。

2.创建用户

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

说明：创建用户，同时指定密码

案例：

CREATE USER 'weijun'@'localhost' IDENTIFIED BY '123'

3.删除用户

drop user ‘用户名’@’允许登录位置;

案例：

drop user 'weijun'@'localhost'

说明：

• 在创建用户的时候，如果不指定Host,则为%,%表示表示所有IP都有连接权限create user xxx;
• 也可以这样指定create user 'xxx'@'192.168.1.%’表示xxx用户在192.168.1.*的ip可以登录mysql
• 在删除用户的时候，如果host不是%,需要明确指定‘用户'@'host值

4.用户修改密码

mysql8.0版本之前的方式：

修改自己的密码：

set password =password('密码');

修改他人的密码(需要有修改用户密码权限): 

set password for '用户名'@'登录位置' =password('密码');

案例：

set password = password('123456');
 
set password for 'weijun'@'localhost' = password('123456');

 mysql8.0版本之后的方式：

use mysql;
alter user 'root'@'localhost' identified by '123456';
flush privileges;

5.mysql 中的权限

6.给用户授权

基本语法：

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

说明：
（1）权限列表，多个权限用逗号分开

grant select on ……
grant select,delete,create on ……
grant all [privileges] on ……//表示赋予该用户在该对象上的所有权限

（2）特别说明
*.*:代表本系统中的所有数据库的所有对象(表，视图，存储过程)库.

*:表示某个数据库中的所有数据对象(表，视图，存储过程等)
（3）identified by可以省略，也可以写出.

• 如果用户存在，就是修改该用户的密码。
• 如果该用户不存在，就是创建该用户!

7.回收用户授权

基本语法：

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

8.权限生效指令

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

基本语法：FLUSH PRIVILEGES;

9.Mysql 用户管理细节说明

SQLyog快捷键

• 注释的快捷键 shift+ctrl+c 
• 注销注释 shift+ctrl+r

Navicat快捷键

• 注释：ctrl+/

案例表mysql语句

CREATE TABLE student(
id INT NOT NULL DEFAULT 1, NAME VARCHAR(20) NOT NULL DEFAULT '', chinese FLOAT NOT NULL DEFAULT 0.0, english FLOAT NOT NULL DEFAULT 0.0, math FLOAT NOT NULL DEFAULT 0.0
);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩寒',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT "", loc VARCHAR(13) NOT NULL DEFAULT ""
);
 
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');
SELECT * FROM dept; -- 员工表
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利 奖金*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);-- 添加测试数据
INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30), (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30), (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20), (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30), 
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30), (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10), (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20), (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10), (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30), (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30), (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20), (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
SELECT * FROM emp; -- 工资级别
#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*工资级别*/
losal DECIMAL(17,2) NOT NULL, /* 该级别的最低工资 */
hisal DECIMAL(17,2) NOT NULL /* 该级别的最高工资*/
);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
SELECT * FROM salgrade;
SELECT * FROM dept;
SELECT * FROM emp;