MySQL------存储引擎和用户和授权

9.存储引擎

1.两种引擎

MyISAM和InnoDB

2.两种区别

1.事务： MyISAM不支持事务

2.存储文件: innodb : frm、ibd MyISAM: frm、MYD、MYI

3.数据行锁定: MyISAM不支持

4.全文索引: INNODB不支持，所以MYISAM做select操作速度很快

5.外键约束: MyISAM不支持

3.引擎优缺点:

INNODB

1.可靠性更强，或者业务要求事务时

2.表更新和查询相当频繁，并且表锁定的情况比较大

3.如果你需要大量的修改和插入时。

MYISAM：

1.做很多的数据计算，mysql的底层系统库就是MyISAM

2.修改和插入不频繁，如果执行大量的select,MYISAM比INNODB更加适合

3.没有事务

10.用户和授权

1.创建用户方式

1.创建用户的第一种方式
CREATE USER <'用户名'@'地址'> IDENTIFIED BY ‘密码’;
create user "用户名"@"主机地址localhost" identified by "用户密码";
​
2.创建用户的第二种方式---推荐使用
grant 权限 on 数据库.表 to "用户名"@"主机地址localhost" identified by "用户密码";
-- 权限：SELECT、INSERT、DELETE、UPDATE、ALTER
-- 数据库.表: 可以赋权的数据库及表，* 代表所有 *.* 所有库下的所有表

2.操作

1.查看系统的用户
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
​
2.创建一个有查看所有库及表权限的用户-lj2
grant 权限 on 权限范围 to “用户名”@“主机名” identified by “密码”；
mysql> grant select on *.* to "lj2"@"localhost" identified by "lijian";
Query OK, 0 rows affected, 1 warning (0.00 sec)
​
3.查看系统的用户
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| lj2           | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
​
4.刷新系统表
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)
​
mysql> exit;
Bye
​
5.使用新用户的登陆
C:\Users\24575>mysql -ulj2 -plijian 
​
mysql: [Warning] Using a password on the command line interface can beinsecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fk_table_lls |
| mysql |
| performance_schema |
| sys |
| table_lls_test |
| test |
+--------------------+
7 rows in set (0.00 sec)
​
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_dept |
| tb_employee |
| user |
+----------------+
3 rows in set (0.00 sec)
​
mysql> select * from user;
+---------+-----------+----------+
| user_id | username | password |
+---------+-----------+----------+
| 1      | lijian     | 111 |
| 2      | lijian     | 111 |
| 1000   | lijian111  | 111 |
| 1001   | lijian     | 111 |
| 1002   | zhang3     | 111 |
| 1003   | lisi       | 111 |
| 1004   | wangwu     | 111 |
| 1005   | wangwu     | 111 |
| 1006   | zhao6      | 222 |
| 1007   | lijian     | 111 |
| 1008   | lijian2    | 111111 |
+---------+-----------+----------+
11 rows in set (0.00 sec)
​
6.插入数据失败，没有权限
mysql> insert into user values(null,"lijian2222","1111111"); 
ERROR 1142 (42000): INSERT command denied to user 'lj2'@'localhost' for table
'user'
​
mysql> exit
Bye
​
C:\Users\24575>mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can beinsecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
7.创建一个只能查看test库下所有表的用户lj3
mysql> grant select on test.* to "lj3"@"localhost" identified by "lijian";
Query OK, 0 rows affected, 1 warning (0.00 sec)
​
mysql> flush privileges; -- 属性系统表
Query OK, 0 rows affected (0.00 sec)
​
mysql> exit
Bye
​
8.使用新用户登录
C:\Users\24575>mysql -ulj3 -plijian 
mysql: [Warning] Using a password on the command line interface can be
insecure.