MySQL基础入门

MySQL基础入门

创建用户

CREATE USER ‘username‘@’host’ IDENTIFIED BY ‘password’;

说明

• username：你将创建的用户名
• host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
• password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器。

例如

1
2
3
4
5

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

MySQL权限

最简单的MySQL权限

​ 最简单也是最高效的，如果解决新手们删库跑路的问题其实也是很简单的，对于正式库只给一个增删改查的权限，或者只给一个查询权限（是不是就解决了删库的可能性？）

创建用户

语法

CREATE USER ‘username‘@’host’ IDENTIFIED BY ‘password’;

• username：你将创建的用户名
• host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
• password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器。

例如

1

CREATE USER 'dev'@'192.168.0.%' IDENTIFIED BY '123456';

一个sql语句就创建一个dev的用户，密码是123456并且只能192.168.0.*的IP才能访问

用户授权

使用root用户，执行

1

grant SELECT on test.* TO 'dev'@'192.168.0.%' WITH GRANT OPTION;

很简单的一句sq，仅仅运行在网段为192.168.0.*的网段进行查询操作。

再执行一条命令：

1

show grants for 'dev'@'192.168.0.%';

深入研究下MySQL权限

用户标识是什么

​ 上面一句简单的SQL堪称完美的解决了程序员新手的删库跑路的问题，但是如果想面试给面试管留下好映像，上面的知识好像还不够，有必要好好深入研究下MySql的权限了。这里有个小的知识点需要先具备，在mysql中的权限不是单纯的赋予给用户的，而是赋予给”用户+IP”的。

​ 比如dev用户是否能登陆，用什么密码登陆，并且能访问什么数据库等都需要加上IP，这样才算一个完整的用户标识，换句话说 ‘dev‘@’192.168.0.168’ 、‘dev‘@’127.0.0.1’与‘dev‘@’localhost’ 这3个是完全不同的用户标识（哪怕你本机的ip就是192.168.0.168）。

用户权限所涉及的表

有了用户标识的概念接下来就可以看权限涉及的表了，这也是面试的时候加分项哦。有几张表需要谨记：

mysql.user，mysql.db，mysql.table_priv，mysql_column_priv

你可以熟悉其中的user表，甚至手动的改过里面的数据（不合规范哦！）

那这些表有什么用，和权限又有什么关系呢？

• User的一行记录代表一个用户标识
• db的一行记录代表对数据库的权限
• table_priv的一行记录代表对表的权限
• column_priv的一行记录代表对某一列的权限

测试

新建一个表account

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `balance` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_balance` (`balance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `account` VALUES ('1', 'lilei', '900');
INSERT INTO `account` VALUES ('2', 'hanmei', '100');
INSERT INTO `account` VALUES ('3', 'lucy', '250');
INSERT INTO hankin.`account` VALUES ('5', 'tom', '0');

mysql其实权限并不事特别low，权限的粒度甚至到了某一列上，举例来说，有个表account表。

对于前面创建的dev用户我不想让他访问balance列,但是id和name列是可以访问的，这样的需求在工作中不是没有。

1

grant select(id,name) on test.account to 'dev'@'192.168.0.%';

在mysql维护工作中，做好权限管理是一个很重要的环节。下面对mysql权限操作进行梳理：

mysql的权限命令是grant，权限撤销的命令时revoke；

grant授权格式

grant 权限列表 on 库.表 to 用户名@’ip’ identified by “密码”;

revoke回收权限格式

revoke 权限列表 on 库.表 from 用户名@’ip’;

下面通过一些例子说明:

用户授权

grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利

1
2
3
4
5
6
7
8
9
10

grant all on *.* to dev@'192.168.0.1' identified by "123456";      
#all等同于all privilege，其中的privileges可以省略

grant all privileges on *.* to dev@'192.168.0.%' identified by "123456";    
#192.168.1.%表示一个网段

grant insert,select,update,delete,drop,create,alter on test.* to dev@'%' identified by "123456";

flush privileges;      
#授权之后，不要忘记更新权限表

查看权限

查看当前用户下所有的权限

1

show grants;

查看指定用户下的所有权限

USAGE是默认的初始状态，表示无任何权限！！
下面表示dev用户在192.168.0.0/254网段登陆本机mysql后，对test库下的所有表有insert, update, alter, delete, create, select的操作权限！

1
2

show grants for dev@'192.168.0.%';
#可以在select user,host,password from mysql.user执行结果中找对应的权限用户信息

例如

1

grant select(id,name) on test.account to 'dev'@'192.168.0.%';

这时候可以在分别看下table_priv，column_priv的数据

revoke撤销权限

revoke跟grant语法差不多，只需要把关键字 “to” 换成 “from” 即可，并且revoke语句中不需要跟密码设置。

注意：revoke可以回收所有权限，也可以回收部分权限。

1
2
3
4
5
6
7

revoke all on *.* from dev@'192.168.0.1';                  

revoke all privileges on *.* from dev@'192.168.0.%';              

revoke insert,select,update,delete,drop,create,alter on test.* from dev@'%';

flush privileges;  

注意事项

1. grant, revoke用户权限后，该用户只有重新连接MySQL数据库，权限才能生效。
2. 如果想让授权的用户，也可以将这些权限grant给其他用户，那么授权时需添加选项 “grant option”！
   如下设置后，那么这个wang用户连接mysql后也可以将这些权限授予其他用户。

mysql授权表一共涉及到5个表，分别是user、db、host、tables_priv和columns_priv。
这5张表的内容和用途如下：

• user表：user表列出可以连接服务器的用户及其口令，并且它指定他们有哪种全局（超级用户）权限。在user表启用的任何权限均是全局权限，并适用于所有数据库。例如，如果你启用了DELETE权限，在这里列出的用户可以从任何表中删除记录，所以在你这样做之前要认真考虑。
• db表：db表列出数据库，而用户有权限访问它们。在这里指定的权限适用于一个数据库中的所有表。
• host表：host表与db表结合使用在一个较好层次上控制特定主机对数据库的访问权限，这可能比单独使用db好些。这个表不受GRANT和REVOKE语句的影响，所以，你可能发觉你根本不是用它。
• tables_priv表：tables_priv表指定表级权限，在这里指定的一个权限适用于一个表的所有列。
• columns_priv表：columns_priv表指定列级权限。这里指定的权限适用于一个表的特定列。

1

REVOKE SELECT on test.* from 'dev'@'192.168.3.%';

你使用dev登陆查询试试

你再要查询所有记录？不好意思不让查，而你查询id，name查询又是可以了。

MySQL的角色

准备工作

​ MySql基于”用户+IP”的这种授权模式其实还是挺好用的，但如果你使用Oracle、PostgreSQL、SqlServer你可能会发牢骚，这样对于每个用户都要赋权的方式是不是太麻烦了，如果我用户多呢？有没有角色或者用户组这样的功能呢？在mysql5.7开始才正式支持这个功能，而且连mysql官方把它叫做“Role Like”（不是角色，长得比较像而已）

那在5.7中怎么玩这个不像角色的角色呢？

show variables like “%proxy%”

你得先把check_proxy_users，mysql_native_password_proxy_users这两个变量设置成true才行

1
2
3

set GLOBAL check_proxy_users =1;

set GLOBAL mysql_native_password_proxy_users = 1;

当然，你也可以把这两个配置设置到my.cnf中

创建一个角色

create USER ‘dev_role’;

​ 可能被你发现了，我这里创建得是个user,为了稍微像角色一点点，我给这user取名叫dev_role，而且为了方便也没用使用密码了。

创建2个开发人员账号

1
2
3

create USER 'zhangsan';

create USER 'lisi';

把两个用户加到组里面

这两个用户我也没设置密码，把两个用户加到组里面

1
2
3

grant proxy on 'dev_role' to 'zhangsan';

grant proxy on 'dev_role' to 'lisi';

查看权限

可以看下其中一个用户的权限

GRANT USAGE ON . TO ‘zhangsan‘@’%’

GRANT PROXY ON ‘dev_role‘@’%’ TO ‘zhangsan‘@’%’

注意：如果你是远程链接，你可能会收获一个大大的错误，你没有权限做这一步，这个时候你需要再服务器上执行一条

1

GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION;

给角色dev_role应该有的权限

有了用户了，这用户也归属到了dev_role这角色下面，那接下来要做的就很简单了，根据业务需求给这角色设置权限就好了(需要在服务器上执行)

1

grant select(id,name) on hankin.account to 'dev_role';

测试

现在使用zhangsan用户登陆系统试试

1

select id ,name from hankin.account;

你可能又会问，这种角色的权限是存哪的呢？

SELECT * FROM proxies_priv;

MySQL数据类型

Int类型

有无符号

在项目中使用BIGINT，而且是有符号的。

1
2
3
4
5
6
7

create table test_unsigned(a int unsigned, b int unsigned);

insert into test_unsigned values(1, 2);

select b - a from test_unsigned;

select a - b from test_unsigned;  --运行出错

INT(N)是什么

1
2
3
4
5

create table test_int_n(a int(4) zerofill);

insert into test_int_n values(1);

insert into test_int_n values(123456);

• int(N)中的 N 是显示宽度，不表示存储的数字的长度的上限。
• zerofill 表示当存储的数字长度< N时，用数字0填充左边，直至补满长度N。
• 当存储数字的长度超过N时，按照实际存储的数字显示。

自动增长的面试题

这列语法有错误吗？

1

create table test_auto_increment(a int auto_increment);

以下结果是什么

1
2
3
4
5
6
7

insert into test_auto_increment values(NULL);

insert into test_auto_increment values(0);

insert into test_auto_increment values(-1);

insert into test_auto_increment values(null),(100),(null),(10),(null)

总结：插入null索引会自动增加，插入0，索引也会增加但不是0，插入负数成功

字符类型

排序规则

1
2
3
4
5
6
7
8
9
10
11

select 'a' = 'A';  -- 查询结果：1

create table test_ci (a varchar(10), key(a));

insert into test_ci values('a');

insert into test_ci values('A');

select * from test_ci where a = 'a';   -- 结果是什么？两条数据都查询成功

set names utf8mb4 collate utf8mb4_bin;

时间类型

datatime与timestamp区别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

create table test_time(a timestamp, b datetime);

insert into test_time values (now(), now());

select * from test_time;

查询结果：2019-06-20 22:04:59 2019-06-20 22:04:59

select @@time_zone;

查询结果：SYSTEM

set time_zone='+00:00';

select * from test_time;

查询结果：2019-06-20 14:04:59 2019-06-20 22:04:59

JSON类型

JSON入门

新建表

1
2
3
4
5

create table json_user (
     uid int auto_increment,
     data json,
     primary key(uid)
 );

插入数据

1
2
3

insert into json_user values (null, '{ "name":"lison","age":18,"address":"enjoy" }' );
 
insert into json_user values (null,'{"name":"james","age":28,"mail":"james@163.com"}');

查询结果

1
2
3

1 {"age": 18, "name": "张三", "address": "陕西西安"}

2 {"age": 28, "mail": "lisi@163.com", "name": "lisi"}

JSON函数

json_extract抽取

1

select json_extract('[10, 20, [30, 40]]', '$[1]');

1

select json_extract(data, '$.name'), json_extract(data, '$.address')  from test.json_user;

JSON_OBJEC

将对象转为json

1

select json_object("name", "隔壁老王", "email", "老王.sina.com", "age",35);

1

insert into test.json_user values ( null, json_object("name", "隔壁老王", "email", "老王.sina.com", "age",35) );

json_insert

插入数据 新增不存在的值

语法：JSON_INSERT(json_doc, path, val[, path, val] …)

• json_doc 是JSON字符串
• path 是要插入数据或更新值的元素的路径。
• val 是新的值.

1
2
3

set @json = '{ "a": 1, "b": [2, 3]}';

select json_insert(@json, '$.a', 10, '$.c', '[true, false]');

我们发现 a 存在没有插入 c 不存在 插入了

1

update test.json_user set data = json_insert(data, "$.address", "你家隔壁") where uid = 3;

json_merge

合并数据并返回

1

select json_merge('{"name": "enjoy"}', '{"id": 47}');

1

select json_merge(json_extract(data, '$.name'), json_extract(data, '$.address')) from test.json_user where uid = 3;

JSON索引

JSON 类型数据本身 无法直接 创建索引，需要将需要索引的 JSON数据 重新 生成虚拟列(Virtual Columns) 之后，对 该列 进行 索引

1
2
3
4
5
6
7
8
9

create table test_inex_1(
    data json,
    gen_col varchar(10) generated always as (json_extract(data, '$.name')),
    index idx (gen_col)
 
);
insert into test_inex_1(data) values ('{"name":"张三", "age":18, "address":"上海"}');
insert into test_inex_1(data) values ('{"name":"李四", "age":28, "address":"北京"}');
select * from test_inex_1;

疑问：这条sql查询的结果是？

1
2

select json_extract(data,"$.name") as username from test_inex_1 where gen_col="张三";
执行结果：Empty set (0.00 sec)

因为数据带有双引号 所以需要在外面包一个单引号

1

select json_extract(data,"$.name") as username from test_inex_1 where gen_col='"张三"';

我们用执行计划看看有没有走索引

1

explain select json_extract(data,"$.name") as username from test_inex_1 where gen_col='"张三"';

我们看到是走了索引

示例

经查阅官方文档，建立虚拟列，这个列查询的时候不需要加上“”符号

1
2
3
4
5
6
7

create table test_index_2 (
    data json,
    gen_col varchar(10) generated always as ( json_unquote( json_extract(data, "$.name"))),
    key idx(gen_col)
);
insert into test_index_2(data) values ('{"name":"张三", "age":18, "address":"上海"}');
insert into test_index_2(data) values ('{"name":"李四", "age":28, "address":"南京"}');

查询下结果

1
2

select json_extract(data,"$.name") as username from test_index_2 where gen_col="张三";
查询结果：”king” 带有双引号。

答案：通过使用JSON_UNQUOTE来解决这个问题

1

SELECT json_extract(data,"$.name"),JSON_UNQUOTE(data->'$.name') from test_index_2 where gen_col="张三";