超详细！！！MySQL基本操作命令——DDL、DML、DQL、DCL

一、数据库基本操作命令

1.1：mysql查看数据库结构

• 查看数据库结构

• 创建及删除库和表

• 管理表的记录

1.2：查看数据库信息

• show databases

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

1.3：查看数据库中的数据表信息

• use 数据库名
• show tables

mysql> use mysql;						'进入mysql数据库中'
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
...省略内容

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

• describe [数据库名.]表名

mysql> describe user;				'也可以使用desc user；'
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |

二：SQL操作管理命令

2.1：SQL语言概述

• SQL语言
  1.是Structured Query Language的缩写，及结构化查询语言
  2.是关系型数据库的标准语言
  3.用于维护管理数据库，如数据查询，数据更新，访问控制，对象管理等功能
• SQL分类
  DDL：数据定义语言
  DML：数据操纵语言
  DQL：数据查询语言
  DCL：数据控制语言

2.2：DDL操作命令

2.2.1：创建数据库和表

• DDL语句用于创建数据库对象，如库，表，索引等

• 使用DDL语句新建库、表

2.2.2：DDL语句创建库、表的命令

• 创建数据库：create database 数据库名
• 创建数据表：create table 表名（字段定义…）

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed

mysql> create table info (id int(3) not null primary key auto_increment,name varchar(10) not null,score decimal(5,2),address varchar(50) default '未知');
Query OK, 0 rows affected (0.02 sec)
'info表名'，'not null设置不为空'，'primary key主键'，'auto_increment自动增长'

2.2.3：DDL语句删除库，表的命令

• 删除指定的数据表：drop table [数据库名.]表名

• 删除指定的数据库：drop database 数据库名

  mysql> drop table info;			'删除表'
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> drop database shcool;			'删除库'
  Query OK, 0 rows affected (0.00 sec)

2.3：DML操作命令

2.3.1：DML语句的作用

• DML语句用于对表中的数据进行管理
• 包括以下操作
  • insert：插入新数据
  • update：更新原有数据
  • delete：删除不需要的数据

2.3.2：向数据表中插入新的数据记录命令

mysql> use school							'进入school数据库'
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from info;					'查看表中数据'
Empty set (0.00 sec)

mysql> show tables;								'查看表'
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> desc info;									'查看表结构'
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(3)       | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10)  | NO   |     | NULL    |                |
| score   | decimal(5,2) | YES  |     | NULL    |                |
| address | varchar(50)  | YES  |     | 未知    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into info (id,name,score,address) values (1,'zhangsan',88.5,'nanjing');	'插入新的数据记录'
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 88.50 | nanjing |
+----+----------+-------+---------+
1 row in set (0.00 sec)

mysql> insert into info values (2,'lisi',90,'beijing'),(3,'wangwu',77,'hangzhou');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
'如果不加字段名，默认是所有字段，并且是按照从左往右'
mysql> select * from info;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | zhangsan | 88.50 | nanjing  |
|  2 | lisi     | 90.00 | beijing  |
|  3 | wangwu   | 77.00 | hangzhou |
+----+----------+-------+----------+
3 rows in set (0.00 sec)

mysql> select * from info where score > 80;		'筛选出80分以上的数据'
+----+----------+-------+---------+
| id | name     | score | address |
+----+----------+-------+---------+
|  1 | zhangsan | 88.50 | nanjing |
|  2 | lisi     | 90.00 | beijing |
+----+----------+-------+---------+
2 rows in set (0.00 sec)

mysql> create table tmp as select * from info where score > 80; '筛选出80分以上的并导入进新的表中'
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

2.3.3：向表中添加字段

mysql> alter table sibiao add (id int(3) not null primary key auto_increment);

2.3.4：修改，更新数据表中的数据记录的命令

• update 表名 set 字段名 1=值1[，字段名2=值2] where条件表达式

  mysql> update info set score=66 where name='zhangsan';	'修改张三的分数为66'
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> select * from info;
  +----+----------+-------+----------+
  | id | name     | score | address  |
  +----+----------+-------+----------+
  |  1 | zhangsan | 66.00 | nanjing  |
  |  2 | lisi     | 90.00 | beijing  |
  |  3 | wangwu   | 77.00 | hangzhou |
  +----+----------+-------+----------+
  3 rows in set (0.00 sec)
  
  mysql> update info set name='zhaoliu';	'修改所有id的name'

2.3.5：在数据表中删除指定的数据记录命令

• delete from 表名 where 条件表达式

 mysql> select * from info;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | zhangsan | 66.00 | nanjing  |
|  2 | lisi     | 90.00 | beijing  |
|  3 | wangwu   | 77.00 | hangzhou |
+----+----------+-------+----------+
3 rows in set (0.00 sec)

mysql> delete from info where score >= 90;
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | zhangsan | 66.00 | nanjing  |
|  3 | wangwu   | 77.00 | hangzhou |
+----+----------+-------+----------+
2 rows in set (0.00 sec)

• 不带where条件的语句表示删除表中所有记录（高危操作）

mysql> delete from info;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from info;
Empty set (0.00 sec)

2.3.6：查看表结构命令

mysql> desc info;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(3)       | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10)  | NO   |     | NULL    |                |
| score   | decimal(5,2) | YES  |     | NULL    |                |
| address | varchar(50)  | YES  |     | 未知    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

2.3.7：修改用户登录mysql的密码

mysql> update mysql.user set authentication_string=password('123456') where user='root';
[root@localhost ~]# vim /etc/my.cnf
skip-grant-tables   '在[mysqld]下添加'
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p				'输入密码123456'

2.4：DQL操作命令

2.4.1：DQL语句的作用

• DQL是数据查询语句，只有一条：select
• 用于从数据表中查找符合条件的数据记录

2.4.2：不指定条件查询命令

• select字段名1，字段名2…from表名

mysql> select name from info;
+----------+
| name     |
+----------+
| zhangsan |
| wangwu   |
+----------+
2 rows in set (0.00 sec)

mysql> select name,score from info;
+----------+-------+
| name     | score |
+----------+-------+
| zhangsan | 66.00 |
| wangwu   | 77.00 |
+----------+-------+
2 rows in set (0.00 sec)

2.4.3：指定条件查询的命令

• select字段名1，字段名2…from表名 WHERE 条件表达式

mysql> select name from info where id=1 or id=3;
+----------+
| name     |
+----------+
| zhangsan |
| wangwu   |
+----------+
2 rows in set (0.00 sec)

2.5：DCL操作命令

2.5.1：DCL语句的作用

• 设置或查看用户的权限，或者创建用户

2.5.2：设置用户权限的命令

• 若用户已存在，则更改用户密码
• 若用户不存在，则新建用户
• grant 权限列表 on 数据库名.表名 to 用户名@来源地址 identified by ‘密码′

 mysql> grant all privileges on *.* to 'root'@'%' identified by 'abc123' with grant option;
'all privileges：所有权限，%：所有终端'
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.5.3：查看用户权限的命令

• show grants for 用户名@来源地址

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

2.5.4：撤销用户权限的命令

• revoke 权限列表 on 数据库名.表名 from 用户名@来源地址

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke all on *.* from 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'root'@'localhost';
+--------------------------------------------------------------+
| Grants for root@localhost                                    |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'localhost' WITH GRANT OPTION   |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)