有如下MySQL表_【MySQL】数据库常用语句

1、创建数据库和删除数据库(nba是数据库的名称)

创建：CREATE DATEBASE nba;

删除：DROP DATEBASE nba;

注：这个语句创建的数据库默认编码格式为utf8，数据库校对规则为utf8_genera_ci；相当于下面的语句

CREATE DATABASE `nba` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

注：`nba`这个反引号可以打也可以不打。

扩展：utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。

utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感。

2、创建数据表和删除数据表(player是数据表的表名)

创建：

CREATE TABLE player(

player_id int(11) NOT NULL AUTO_INCREMENT,

player_name varchar(255) NOT NULL

)

删除：DROP TABLE player;

3、修改表的结构(如在数据表中添加一个age字段，类型为int(11))

ALTER TABLE player ADD (age int(11));

4、编辑表的字段

4.1、修改表的字段名(如将age字段改成player_age)

ALTER TABLE player change age player_age int(11);

4.2、修改表的字段的数据类型(如将player_age的数据类型设置为float(3,1))

ALTER TABLE player MODIFY COLUMN player_age float(3,1);

5、删除字段(如删除刚才添加的player_age字段)

ALTER TABLE player DROP COLUMN player_age;

6、插入、删除、修改表数据

6.1、插入表数据

INSERT INTO heros_temp(id, name) VALUES('4', '张三');

注：如果是为heros_temp表中所有列指定值，就可以直接写成：INSERT INTO heros_temp VALUES('4', '张三');

6.2、批量插入数据

INSERT INTO heros_temp(id, name) VALUES('5', '张三2'),('6', '张三3'),('7', '张三4');

   

6.3、修改表数据

UPDATE heros_temp SET name='李四' WHERE id='4';

6.4、删除表数据

DELETE FROM heros_temp where id='4';

  

6.5、删除表中所有数据

如有表XJ_test，内容如下：

方式一：DELETE FROM XJ_test;

 

方式二：TRUNCATE TABLE XJ_test;

  

注：TRUNCATE和DELETE的区别：

(1)truncate删除后不记录mysql日志，因此不可以rollback，更不可以恢复数据；而 delete 是可以 rollback ；

(2)效率上 truncate 比 delete快，而且 truncate 删除后将重建索引(新插入数据后id从0开始记起)，而 delete不会删除索引 (新插入的数据将在删除数据的索引后继续增加)

(3)truncate 不会触发任何 DELETE触发器；

(4)delete 操作后返回删除的记录数，而 truncate 返回的是0或者-1(成功则返回0，失败返回-1)；

(5)delete可以与where连用：DELETE FROM XJ_test WHERE id = '4'

7、SELECT查询语句

7.1、查询单列(name是列名，table是表名)

SELECT name FROM table;

7.2、查询多列(name1，name2，name3是列名，table是表名)

SELECT name1,name2,name3 FROM table;

7.3、查询所有列

SELECT * FROM table;

7.4、对查询后的列名起别名(name1，name2，name3是列名；newName1，newName2，newName3是分别起的别名；table是表名)

SELECT name1 AS newName1,name2 AS newName2,name3 AS newName3 FROM table;

7.5、查询常数

SELECT '王者荣耀' as platform, name FROM heros;

查询结果如下：

说明：虚构了一个常数字段platform，并且将其设置为固定值“王者荣耀”。如果常数是个字符串或者英文字母，必须加单引号‘ ’括起来；如果常数是个数字，就可以直接写数字。

7.6、对查询的结果中，去掉重复行(attack_range是列名，heros是表名)

SELECT DISTINCT attack_range FROM heros;

 

7.7、对查询的结果进行排序(ASC是升序排序以及默认排序，DESC是降序排序)

SELECT name,hp_max FROM heros ORDER BY hp_max DESC;

7.8、对查询的结果只显示规定数量的条数(如只显示前5条数据)

SELECT name,hp_max FROM heros ORDER BY hp_max DESC LIMIT 5;

可以用OFFSET指定从哪条数据位置开始显示，如这里想显示3条，从“name=程咬金”这一条数据开始显示，“name=程咬金”这条数据位置是2，对应第3条数据。

SELECT name,hp_max FROM heros ORDER BY hp_max DESC  LIMIT 3 OFFSET 2;

8、WHERE条件过滤子句

8.1、查询生命值大于6000的英雄

SELECT name,hp_max FROM heros WHERE hp_max > 6000;

8.2、查询生命值在5339到6811之间的英雄(会取到最小值5339和最大值6811)

SELECT name,hp_max FROM heros WHERE hp_max BETWEEN 5339 and 6811;

8.3、对heros表中的hp_max字段进行空值检查

SELECT name,hp_max FROM heros WHERE hp_max IS NULL;

8.4、假设想要筛选最大生命值大于6000，最大法力大于1700的英雄，然后按照最大生命值和最大法力值之和从高到低进行排序

SELECT name,hp_max,mp_max FROM heros WHERE hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC;

8.5、假设想要查询最大生命值加最大法力值大于8000的英雄，或者最大生命值大于6000并且最大法力值大于1700的英雄(AND和OR同时存在于WHERE子句)

SELECT name,hp_max,mp_max FROM heros WHERE (hp_max+mp_max) > 8000 OR hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC;

注：AND和OR同时存在与WHERE子句时，AND的优先级高于OR优先级

8.6、假设想要查询主要定位或者次要定位是法师或是射手的英雄，同时英雄的上线时间不在2016-01-01到2017-01-01之间

SELECT name,role_main,role_assist,hp_max,mp_max,birthdate FROM heros

WHERE (role_main IN ('法师','射手') OR role_assist IN ('法师','射手'))

AND DATE(birthdate) NOT BETWEEN '2016-01-01' and '2017-01-01'

ORDER BY (hp_max + mp_max) DESC;

注：DATE函数的作用是将字段birthdate转化为日期类型再进行比较。

9、通配符

9.1、通配符%：匹配任意字符串中出现的任意次数。如想要查找英雄名中包含”太“字的英雄有哪些

SELECT name FROM heros WHERE name LIKE '%太%';

9.2、通配符_：匹配单个字符。如想要查找英雄名除了第一个字以外，包含‘太’字的英雄有哪些

SELECT name FROM heros WHERE name LIKE '_%太%';

10、SQL函数：分为算数函数、字符串函数、日期函数、转换函数4类

10.1、算数函数；

ABS()：取绝对值；

MOD()：取余；

ROUND()：四舍五入为指定的小数位数，需要2个参数，分别为字段名称、小数位数。

 

  

10.2、字符串函数

(1)CONCAT()：将多个字符串拼接起来

(2)LENGTH()：计算字段的长度，一个汉字算3个字符，一个数字或字母算1个字符

(3)CHAR_LENGTH()：计算字段的长度，汉字、数字、字母都算1个字符

(4)LOWER()：将字符串中的字符转化为小写

(5)UPPER()：将字符串中的字符转换为大写

(6)REPLACE()：替换函数，由3个参数，分别为：要替换的表达式或字段名、想要查找的被替换字符串、替换成哪个字符串

(7)SUBSTRING()：截取字符串，有3个参数，分别为：待截取的表达式或字段名、开始截取的位置、想要截取的字符串长度

10.3、日期函数

(1)CURRENT_DATE()：系统当前日期

(2)CURRENT_TIME()：系统当前时间，没有具体日期

(3)CURRENT_TIMESTAMP()：系统当前时间，包括具体日期+时间

(4)EXTRACT()：抽取具体的年、月、日、时、分、秒

(5)DATE()：返回时间的日期部分

(6)YEAR()：返回时间的年份部分

(7)MONTH()：返回时间的月份部分

(8)DAY()：返回时间的天数部分

(9)TIME()：返回时间的时间部分

(10)HOUR()：返回时间的小时部分

(11)MINUTE()：返回时间的分钟部分

(12)SECOND()：返回时间的秒部分

10.4、转换函数

(1)CAST()：数据类型转换，参数是一个表达式，表达式通过AS关键词分隔了2个参数，分别是原始数据和目标数据类型

 

注：CAST()函数在转换数据类型的时候，不会四舍五入，如果原数值有小数，那么转换为整数类型时会报错。

DECIMAL(a,b)表示精度为a位，即整数加小数加起来最多为a位；b表示小数位数为b位。

(2)COALESCE()：返回第一个非空数或字符串

10.5、SQL函数对数据进行处理一些例子

(1)显示英雄以及他的物攻(对应字段attack_growth)成长，让这个字段精确到小数点后一位，需要使用算数函数里的ROUND函数

SELECT name,ROUND(attack_growth,1) FROM heros;

(2)显示英雄最大生命值的最大值，需要用到MAX函数

SELECT MAX(hp_max) FROM heros;

(3)查询最大生命值最大的是哪个英雄，以及对应的数值

SELECT name,hp_max FROM heros WHERE hp_max = (SELECT MAX(hp_max) FROM heros);

(4)显示英雄的名字，以及他们的名字字数，需要用到CHAR_LENGTH函数

SELECT CHAR_LENGTH(name),name FROM heros;

(5)显示英雄的名字和上线日期(对应字段birthdate)的年份，只显示有上线日期的英雄即可(有些英雄没有上线日期的数据，就不需要显示)，这里需要用到EXTRACT函数

SELECT name,EXTRACT(YEAR FROM birthdate) AS birthdate FROM heros WHERE birthdate is NOT NULL;

(6)显示2016年10月1日之后上线的所有英雄名称和上线日期，这里可以采用DATE函数来判断birthdate的日期是否大于2016-10-01

SELECT name,birthdate FROM heros WHERE DATE(birthdate) > '2016-10-01';

(7)显示2016年10月1日之后上线的英雄的平均最大生命值、平均最大法力值和最高物攻最大值

SELECT AVG(hp_max),AVG(mp_max),MAX(attack_max) FROM heros WHERE DATE(birthdate) > '2016-10-01';

11、聚集函数

COUNT()：总行数

MAX()：最大值

MIN()：最小值

SUM()：求和

AVG()：平均值

例：(1)查询最大生命值大于6000的英雄数量

SELECT COUNT(*) FROM heros WHERE hp_max > 6000;

(2)查询最大生命值大于6000，且次要定位的英雄数量

SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000;

(3)查询射手(主要定位或次要定位是射手)的最大生命值的最大值

SELECT MAX(hp_max) FROM heros WHERE role_main = '射手' or role_assist = '射手';

(4)查询射手(主要定位或次要定位是射手)的英雄数、平均最大生命值、法力最大值的最大值、攻击最大值的最小值、英雄总的防御最大值

SELECT COUNT(*),AVG(hp_max),MAX(mp_max),MIN(attack_max),SUM(defense_max) FROM heros WHERE role_main = '射手' or role_assist = '射手';

(5)查询最小和最大英雄名称

SELECT MIN(CONVERT(name USING gbk)),MAX(CONVERT(name USING gbk)) FROM heros;

注：MIN和MAX函数，如果针对的是英文字母，则按照A-Z的顺序排列，越往后，数值越大。如果针对的是汉字，如本例中的名字，则按照全拼拼音进行排列。需要注意的是，这里需要先把name字段统一转化为gbk类型，使用CONVERT(name USING gbk)。

(6)查询不同的生命最大值的英雄数量

SELECT COUNT(DISTINCT hp_max) FROM heros;

(7)统计不同生命最大值英雄的平均生命最大值，保留小数点后2位

SELECT ROUND(AVG(DISTINCT hp_max),2) FROM heros;

12、分组GROUP BY

例：(1)对英雄的次要定位进行分组，并统计每组的英雄数量

SELECT COUNT(*),role_assist FROM heros GROUP BY role_assist;

注：字段为NULL时，也会被列为一个分组。

(2)对英雄的主要定位、次要定位进行分组，查看这些英雄的数量，并按照这些分组的英雄数量从高到低进行排序

SELECT COUNT(*) as num,role_main,role_assist FROM heros GROUP BY role_main,role_assist ORDER BY num DESC;

13、分组过滤HAVING

例：(1)按照英雄的主要定位、次要定位进行分组，并且筛选分组数量大于5的组，最后按照分组中的英雄数量数量从高到底进行排序

SELECT COUNT(*) as num,role_main,role_assist FROM heros GROUP BY role_main,role_assist HAVING num > 5 ORDER BY num DESC;

(2)筛选最大生命值大于6000的英雄，按照主要定位、次要定位进行分组，并且显示分组中英雄数量大于5的分组，按照数量从高到底排序

SELECT COUNT(*) as num,role_main,role_assist FROM heros WHERE hp_max > 6000 GROUP BY role_main,role_assist HAVING num > 5 ORDER BY num DESC;

14、子查询

14.1、关联子查询和非关联子查询

例：(1)(非关联子查询)查询最高身高的球员姓名和身高

SELECT player_name,height FROM player WHERE height = (SELECT max(height) FROM player);

(2)(关联子查询)查询每个球队中大于平均身高的球员有哪些，并显示他们的姓名、身高、以及所在球队ID

SELECT player_name,height,team_id FROM player AS a WHERE height > (SELECT AVG(height) FROM player AS b WHERE a.team_id = b.team_id);

14.2、EXISTS子查询

例：查询出场过的球员有哪些，并显示他们的姓名、球员ID、球队ID。(是否出场是通过player_score这张表中的球员出场表现来统计的，如果某个球员

在player_score中有出场记录则代表他出场过)

SELECT player_id,team_id,player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id);

注：NOT EXISTS就是不存在的意思。

14.3、集合比较子查询

IN：判断是否在集合中；

ANY：需要与比较操作符一起使用，与子查询返回的任何值作比较；

ALL：需要与比较操作符一起使用，与子查询返回的所有值作比较；

SOME：实际上是ANY的别名，作用相同，一般常用ANY。

例：(1)(IN)查询出场过的球员有哪些，并显示他们的姓名、球员ID、球队ID

SELECT player_id,team_id,player_name FROM player WHERE player_id IN (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id);

注：IN与EXISTS子查询结果一样，表A指的是player表，表B指的player_score表。如果表A比表B大，那么IN查询效率高，如果表A比表B小，那么EXISTS查询效率高。

(2)(ANY)查询球员表中，比印第安纳步行者(对应的team_id为1002)中任意一个球员身高高的球员信息，显示球员ID、球员姓名、球员身高

SELECT player_id,player_name,height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002);

(3)(ALL)查询球员表中，比印第安纳步行者(对应的team_id为1002)中所有球员身高都高的球员信息，显示球员ID、球员姓名、球员身高

SELECT player_id,player_name,height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002);

14.4、将子查询作为计算字段

例：查询每个球队的球员数，也就是对应team这张表，需要查询相同的team_id在player这张表中所有的球员数量是多少

SELECT team_name,(SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team;

15、SQL92标准和SQL99标准

15.1、【SQL92标准】的5种连接方式

(1)笛卡尔积(相当于SQL99中的交叉连接)：player和team两张表的笛卡尔积

SELECT * FROM player, team;

(2)等值连接(相当于SQL99中的自然连接)：player和team两张表都存在team_id这一列，这两张表等值查询如下

SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id;

(3)非等值连接：player表中有身高height字段，如果想要知道每个球员的身高的级别，可以采用非等值连接查询

SELECT p.player_name, p.height, h.height_level FROM player AS p, height_grades AS h WHERE p.height BETWEEN h.height_lowest AND h.height_highest;

(4.1)左外连接：player表和team表左外连接查询

SELECT * FROM player, team where player.team_id = team.team_id(+);

(4.2)右外连接：player表和team表右外连接查询

SELECT * FROM player, team where player.team_id(+) = team.team_id;

备注：(+)表示哪一张表是从表

外连接是主表显示全部行

MYSQL不支持SQL92的外连接

SQL92标准没有全外连接

(5)自连接：想要查看比布雷克·格里芬高的球员都有谁，以及他们的对应身高

SELECT b.player_name, b.height FROM player as a, player as b WHERE a.player_name = '布雷克-格里芬'  and a.height < b.height;

15.2、【SQL99标准】的6种连接方式

(1)交叉连接(相当于SQL92中的笛卡尔乘积)：player表和team表交叉连接查询

SELECT * FROM player CROSS JOIN team;

(2)自然连接(相当于SQL92中的等值连接)：player表和team表自然连接，存在相同字段team_id

SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team;

(3.1)ON连接：用来指定我们想要的连接条件，可以用来实现自然连接的功能

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team ON player.team_id = team.team_id;

(3.2)ON连接：也可以用来实现非等值连接，如查询球员的身高等级，需要用player和height_grades两张表

SELECT p.player_name, p.height, h.height_level FROM player as p JOIN height_grades as h ON p.height BETWEEN h.height_lowest AND h.height_highest;

(4)USING连接：可以用USING指定数据表里同名字段进行等值连接

SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id);

(5.1)左外连接：LEFT JOIN 或 LEFT OUTER JOIN

SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id;

(5.2)右外连接：RIGHT JOIN 或 RIGHT OUTER JOIN

SELECT * FROM player RIGHT JOIN team ON player.team_id = team.team_id;

(5.3)全外连接：FULL JOIN 或 FULL OUTER JOIN

SELECT * FROM player FULL JOIN team ON player.team_id = team.team_id;

备注：MYSQL不支持全外连接

(6)自连接：查看比布雷克·格里芬身高高的球员有哪些

SELECT b.player_name, b.height FROM player as a JOIN player as b ON a.player_name = '布雷克-格里芬' and a.height < b.height;

16、视图

16.1、创建视图

例：想要查询比NBA球员平均身高高的球员有哪些，显示他们的球员ID和身高。假设给该视图起名为player_above_avg_height，那么创建视图语句为：

CREATE VIEW player_above_avg_height AS

SELECT player_id, height

FROM player

WHERE height > (SELECT AVG(height) FROM player)

 

查询该视图：

SELECT * FROM player_above_avg_height;

16.2、嵌套视图

例：创建好一张视图之后，还可以在它的基础上继续创建视图，比如我们想在虚拟player_above_avg_height的基础上，找到比这个表中的球员平均身高高的球员，作为新的视图player_above_above_avg_height，那么语句为：

CREATE VIEW player_above_above_avg_height AS

SELECT player_id, height

FROM player

WHERE height > (SELECT AVG(height) FROM player_above_avg_height);

 

查询该视图：

SELECT * FROM player_above_above_avg_height;

16.3、修改视图

例：如我们想更新视图player_above_avg_height，增加一个player_name字段，可以写成

ALTER VIEW player_above_avg_height AS

SELECT player_id, player_name, height

FROM player

WHERE height > (SELECT AVG(height) FROM player);

查询该视图：

SELECT * FROM player_above_avg_height;

16.4、删除视图

例：如我们想把刚才创建的player_above_above_avg_height视图删掉

DROP VIEW player_above_above_avg_height;

17、存储过程

定义存储过程语法：

修改存储过程：ALTER PROCEDURE;

删除存储过程：DROP PROCEDURE;

调用存储过程：CALL 存储过程名称(@输出参数, '输入参数值')

流控制语句：BEGIN...END，中间每个语句以分号";"结尾

DECLARE，用来声明变量

SET，用来对变量进行赋值

SELECT...INTO，将从数据表中的查询结果存放到变量中，也就是为变量赋值

例1：做1个累加计算，计算1+2+3+...+n等于多少

 

调用存储过程：CALL add_num(50);

例2：创建一个存储类型get_hero_scores，用来查询某一类型英雄中的最大的最大生命值，最小的最大魔法值，以及平均最大攻击值

调用存储过程：

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');

SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

18、事务

18.1、MYSQL中查询当前MYSQL支持的存储引擎有哪些，以及这些存储引擎是否支持事务

命令：show engines;

18.2、事务控制语句

(1)START TRANSACTION或者BEGIN：显示开启一个事务

(2)COMMIT：提交事务。提交事务后，对数据库的修改是永久性的

(3)ROLLBACK或者ROLLBACK TO[SAVEPOINT]：回滚事务。指撤销正在进行的所有没有提交的修改，或者将事务回滚导某个保存点

(4)SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点

(5)RELEASE SAVEPOINT：删除某个保存点

(6)SET TRANSACTION：设置事务的隔离级别

18.3、手动提交和自动提交事务

事务分为隐式事务和显示事务。MySQL是隐式事务，默认自动提交，但参数可以修改，变成手动提交。Oracle是显示事务，默认不自动提交，需要手动写COMMIT命令进行提交。

mysql>SET autocommit = 0;  //关闭自动提交

mysql>SET autocommit = 1; //开启自动提交

例1：看下在MYSQL的默认状态下，下面这个事务最后的处理结果是什么：

注：这里两次插入“张飞”是在一个事务里。

例2：那么如果进行下面的操作又会怎么样呢？

注：这里两次插入“张飞”是2个事务，不在一个事务里了，第一次插入“张飞”会自动提交。因为MYSQL是隐式事务，默认autocommit=1，会自动提交。第二次插入”张飞“会报错，进行事务回滚时，第一次插入“张飞”事务已经提交了，表里就有2行数据。

例3：再看下面这段代码，又能发现什么不同呢？

注：前面设置了completion=1，即当提交事务后，相当于在下一行写了一个START TRANSACTION或者BEGIN，这是两次插入“张飞”会被认为是在同一个事务内。

18.4、事务并发处理中的3种可能异常【SQL-92标准】

(1)脏读：读到其他事务还未提交的数据

(2)不可重复读：两次读取结果不同。这是因为有其他事务对这个数据同时进行了修改或删除

(3)幻读：事务1读取N条数据，此时事务2更改或增加M条数据，事务1再次查询发现有N+M条数据，产生了幻读

18.5、4种事务隔离级别

(1)READ UNCOMMITTED：读未提交

(2)READ COMMITTED：读已提交

(3)REPEATABLE READ：可重复读

(4)SERIALIZABLE：可串行化

上面4种隔离级别从低到高。

MYSQL默认的隔离级别是可重复读：REPEATABLE READ

18.5、设置并查看当前事务的隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SHOW VARIABLES LIKE 'transaction_isolation';

18.6、设置并查看当前事务的提交方式

SET autocommit = 0;(关闭自动提交，设为手动提交)

SHOW VARIABLES LIKE 'autocommit';

19、游标

19.1、定义游标

DECLARE cursor_name CURSOR FOR select_statement;

注：这个语法适用于MySQL，SQL Server，DB2，MariaDB

DECLARE cursor_name CURSOR IS select_statement;

注：这个语法适用于Oracle，PostgreSQL

19.2、打开游标

OPEN cursor_name;

19.3、从游标中取得数据

FETCH cursor_name INTO var_name ...

注：使用cursor_name这个游标来读取当前行，并且将数据保存导var_name这个变量中，游标指针指到下一行。如果游标读取的数据行有多个列名，则在INTO关键字后面赋值给多个变量名即可。

19.4、关闭游标

CLOSE cursor_name;

19.5、释放游标

DEALLOCATE cursor_name;

20、

20.1、使用某个库(即切换到某个库)，如使用XJ_test数据库

use XJ_test;

20.2、查看当前连接的数据库

select database();

20.3、查看当前登录数据库的用户

select user();

或者：select current_user();

 

20.4、查看有哪些数据库

show databases;

20.5、查看当前连接的数据库下有哪些表

show tables;

20.6、创建数据库，如创建XJ_test2数据库

CREATE DATABASE `XJ_test2`DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

20.7、创建用户，如创建xj_test用户

CREATE USER 'xj_test'@'%' IDENTIFIED BY '密码';(%表示允许该用户可以从任意远程主机登录)

备注：若创建一个‘xj_test2’@‘localhost’的用户，则xj_test2用户无法进行远程登录，如这里无法使用Navicat工具远程连接进入数据库。

CREATE USER 'xj_test2'@'localhost' IDENTIFIED BY '密码';

    

20.8、查看数据库有哪些用户

select user,host from mysql.user;

或者：select distinct concat('USER:''',user,'''@''',host,''';')as query from mysql.user;

 

20.9、删除用户，如这里删除xj_test2用户

drop user xj_test2@localhost;

20.10、root用户修改普通用户xj_test的密码(修改完密码后需要刷新权限，才会立即生效)

(1)修改方式1：

update mysql.user set authentication_string=password('新密码')where user='xj_test';

flush privileges;

备注：若同时存在xj_test@localhost和xj_test@%两个用户，则上面修改密码的命令将会同时对这两个用户生效。

 

备注：还发现了一个奇怪的现象，使用select user，host from mysql.user；查询到当用户列表没有xj_test@localhost用户，仅有xj_test@%用户时，使用/opt/XJ/mysql/mysql/bin/mysql -u xj_test -p -S /opt/XJ/mysql/mysql/mysql.sock登录后，通过select user();查询当前用户，显示为xj_test@localhost。未找到解释，先单纯记录下。

若只想修改xj_test@localhost，则命令为：

update mysql.user set authentication_string=password('新密码') where user='xj_test' and host='localhost';

flush privileges;

若只想修改'xj_test'@'%'，则命令为：

update mysql.user set authentication_string=password('新密码') where user='xj_test' and host='%';

flush privileges;

(2)修改方式2：

set password for xj_test@localhost = password('新密码');

flush privileges;

↑↑↑仅对xj_test@localhost用户生效，使用opt/XJ/mysql/mysql/bin/mysql -u xj_test -p -S /opt/XJ/mysql/mysql/mysql.sock登录时输入新密码才能登录。

set password for 'xj_test'@'%' = password('新密码');

flush privileges;

↑↑↑仅对'xj_test'@'%'用户生效，使用Navicat工具连接数据库登录时输入新密码才能登录。

set password for xj_test = password('新密码');

flush privileges;

↑↑↑仅对'xj_test'@'%'用户生效，使用Navicat工具连接数据库登录时输入新密码才能登录。使用opt/XJ/mysql/mysql/bin/mysql -u xj_test -p -S /opt/XJ/mysql/mysql/mysql.sock登录时输入新密码不能登录，使用原密码才能登录。

(3)修改当前用户的密码

set password = password('新密码');

20.11、赋予/删除/查看用户权限

(1)赋予用户权限：如赋予'xj_test'@'%'用户权限

一开始创建新用户'xj_test'@'%'后，该用户无任何权限，Navicat工具登录后是这样：

赋予用户'xj_test'@'%'数据库XJ下的heros表的查询权限，命令如下：

grant select on XJ.heros to 'xj_test'@'%';

flush privileges;

再次使用Navicat工具登录后查看数据库：

(2)删除用户权限：如删除'xj_test'@'%'用户数据库XJ下的heros表的查询权限

revoke select on XJ.heros from 'xj_test'@'%';

flush privileges;

使用Navicat工具登录后查看数据库，发现此时又看不到XJ下的heros表了：

(3)查询用户权限：如查询'xj_test'@'%'用户具有哪些权限

show grants for 'xj_test'@'%';

使用Navicat工具登录后查看数据库，查看如下：

如果对heros_temp表进行查询：可以查询，查询结果如下：

如果对heros_temp表进行插入：可以插入，插入结果如下：

insert into heros_temp VALUES('4', '李四');

如果对heros_temp表进行删除：不可以删除，因为不具有删除权限：

(4)赋予所有权限

grant all privileges on *.* to 'xj_test'@'%';

flush privileges;

再次对heros_temp表删除第四条数据：

20.12、刷新权限

flush privileges;

20.13、用户重命名，如将用户'xj_test'@'%'重命名为‘xj_testNew’@'%'

rename user 'xj_test'@'%' to 'xj_testNew'@'%';

 

------------结束-------------