MySQL数据库必备基操！！！（二）

一、临时表

1、临时表，无法使用show tables查看
2、临时表退出数据库即删除，也可用命令手动删除
3、临时表无法创建外键

mysql> create temporary table ls (id int(4) primary key auto_increment,name int(18) not null);
mysql> show tables;
+--------------+
| Tables_in_py |
+--------------+
| hsh          |
| hsh1         |
| hsh3         |
+--------------+
3 rows in set (0.01 sec)

二、用户授权

mysql> grant select on py.* to 'wangwu'@'%' identified by 'abc123'; #创建用户，只允许访问py库的表
Query OK, 0 rows affected, 1 warning (0.00 sec)
#注释
grant：提权
py.*：数据库py下的所有表，也可以指定表名
%：表示所有有，例“192.168.238.%”,“%.wh.com”

mysql> select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| lisi          | *2517D5E7FDE154859781E63A4F8E06E9804EB170 | localhost |
| wangwu        | *6691484EA6B50DDDE1926A220DA01FA9E575C18A | %         |
+---------------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)
mysql> mysql> grant all privileges on *tiantian'@'%' identified by 'abc123'; #允许用户在所有中断登录MySQL，并拥有所有权限
mysql> flush privileges; #刷新权限
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'tiantian'@'%'; #查看权限
+-----------------------------------------------+
| Grants for tiantian@%                         |
+-----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'tiantian'@'%' |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> REVOKE ALL ON *.* FROM 'tiantian'@'%'; #撤销权限（需要大写）
Query OK, 0 rows affected (0.00 sec)