高级MySQL数据库管理

一、数据库基本操作

1.1 查看数据库结构-1

■ 查看数据库信息

SHOW   DATABASES

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| myadm              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

1.2 查看数据库结构-2

■ 查看数据库中的表信息

USE 数据库名

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
......
31 rows in set (0.00 sec)

1.3 查看数据库结构-3

■ 显示数据表的结构（字段）

DESCRIBE [数据库名.]表名

###也可以使用“desc user;”
mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
......

二、创建和管理数据库和表

2.1 创建数据库和表

■ DDL语句可用于创建数据库对象，如库、表、索引等
■ 使用DDL语句新建库、表

• 创建数据库 CREATE DATABASE 数据库名
• 创建数据表CREATE TABLE 表名（字段定义......）

mysql> create database auth;
Query OK, 1 row affected (0.00 sec)
mysql> use auth;
Database changed
mysql> 
mysql> create table users (user_name CHAR(16) NOT NULL,user_passwd CHAR(48) DEFAULT'',PRIMARY KEY (user_name));
Query OK, 0 rows affected (0.01 sec)

2.2 管理表中的数据-1

■ DML语句用于对表中的数据进行管理
■ 包括的操作

• INSERT：插入新数据
• UPDATE：更新原有数据
• DELETE：删除不需要的数据

2.3 管理表中的数据-2

■ 想数据表中插入新的数据记录

UPDATE 表名 SET 字段名1=值1[,字段名2=值2]WHERE 条件表达式

mysql> update auth.users set user_passwd=password('123456') where user_name='lisi';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 1
mysql> flush privileges;  ### 刷新下

mysql> update mysql.user set authentication_string=PASSWORD('123456') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1
mysql> flush privileges;  ### 刷新下

2.3 管理表中的数据-3

■ 在数据表中删除指定的数据记录

DELETE FROM 表名 WHERE 条件表达式

mysql> delete from auth.users where user_name='lisi';
Query OK, 0 rows affected (0.01 sec)

■ 不带where条件的语句表示删除表中所有记录

mysql> delete from auth.users;
Query OK, 0 rows affected (0.00 sec)
###不带where子句时需谨慎操作

2.4 管理表中的数据-4

■ DQL是数据查询语句，只有SELECT
■ 用于从数据表中查找符合条件的数据记录
■ 查询时可不指定条件

SELECT 字段名1，字段名2.....FROM 表名

mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| lisi      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| wangwu    | *E710DC2512FCF6F18FE0D652B53290DCB11F3334 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

2.5 管理表中的数据-5

SELECT 字段名1，字段名2......FROM 表名 WHERE 条件表达式

mysql> select user_name,user_passwd from kk.KK where user_name='lisi'; 
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| lisi      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

2.5 数据库高级操作-1

■ 清空表

• DELETE FROM tablename
• TRUNCATE TABLE tablename

mysql> truncate table tmp;

2.6 数据库高级操作-2

■ 临时表

• 临时建立的表，用于保存一些临时数据，不会长期存在

在这里插入代码片

2.7 数据库高级操作-3

■ 克隆表

• LIKE方法

mysql> create table test like mytmp;
mysql> insert into test select * from mytmp;

• SHOW CREATE TABLE方法

mysql> show create table mytmp\G
mysql> create table test(......);
mysql> insert into test select * from mytmp;

三、数据库用户授权

3.1 数据库用户授权-1

■ DCL语句设置用户权限（用户不存在时，则新建用户）

GRANT 权限列表 ON 数据库名 TO 用户名@来源地址 [IDENTIFIED BY '密码']

mysql> GRANT select ON auth.* TO 'xiaoqi'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

3.2 数据库用户授权-2

■ 查看用户的权限

SHOW GRANTS FOR 用户名@来源地址

mysql> SHOW GRANTS FOR 'lisi'@'20.0.0.6';

■ 撤销用户的权限

REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址

mysql>  REVOKE all ON auth.* FROM 'xiaoqi'@'localhost';