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.