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.