MYSQL基本操作（建库，建表，插入内容，清除内容）以及高级操作（清空表，临时表，克隆表）与数据用户授权

最新推荐文章于 2022-12-05 12:48:04 发布

八橘子

最新推荐文章于 2022-12-05 12:48:04 发布

阅读量2k

点赞数 1

分类专栏： MYSQL数据库应用文章标签： mysql

本文链接：https://blog.csdn.net/Lihuihui006/article/details/109472370

版权

MYSQL数据库应用专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MYSQL基本操作和高级操作以及用户授权

一，数据库基本操作

1，SQL语句概述

（1）SQL语言

Structured Query langguage 的缩写，即结构化查询语言
关系型数据库的标准语言
用于维护管理数据库
+ 包括数据查询，数据更新，访问控制，对象管理等功能

（2）SQL分类

DDL :数据定义语言，用来建立数据库，数据对象和定义字段，如 create, alter,drop
DML: 数据操纵语言，用来插入，删除和修改数据库中的数据，如insert, update, delete
DQL: 数据查询语言，用来查询数据库中的数据，如select
DCL：数据控制语言，用来控制数据库组件的存取许可，存取权限等，如 commit, rollback,grant,revoke

2，基本数据库管理命令

（1），查看已存在数据库信息

show databases；//大部分SQL操作命令必须以；结束
mysql默认的4个数据库：

information_schema: 定义访问数据库元数据的方式。数据库名和表名，列的数据类型，访问权限等。
mysql：核心数据库，负责储存数据用户，权限，关键字等用户自己需要使用的控制和管理信息。
performance_schema:数据库的性能参数，储存引擎等。
sys: sys系统库下包含许多视图，他们以各种方式对performance_schema表进行聚合计算展示。

（2），查看数据库中的表中有哪些表

命令为：
USE mysql; 先使用表
SHOW TABLES; 查看
例如：

mysql> USE mysql;
Database changed
mysql> SHOW tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv               |
省略中间部分
| user                      |
+---------------------------+
28 rows in set (0.00 sec)  //显示有28个表

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

命令为：describe [数据库名.] 表名； //也可使使用“desc user;”
例如：

mysql> USE mysql;
Database changed
mysql> DESCRIBE user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |   
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.00 sec)    //一共43个表

（4) 创建数据库和表(DDL)

DDL语句可用于创建数据库对象，如库，表，索引等
使用DDL语句新建库，表
- 创建数据库 create database 数据库名
- 创建数据表 create table 表名（字段定义… …）
- 删除一个数据表 drop table 库名. 表名；
- 删除一个数据库 drop database 库名;

mysql>  create database aaa;   //创建库
Query OK, 1 row affected (0.01 sec)

mysql> use aaa;    //使用库
Database changed
mysql> create table users (username char(64) not null, password int(24) default '123456',primary key (username));    //创建表，定义字段
Query OK, 0 rows affected (0.03 sec)
mysql> describe users;   //查看表是否创建成功
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| username | char(64) | NO   | PRI | NULL    |       |
| password | int(24)  | YES  |     | 123456  |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)  

mysql> create database bbb;   //重新创建一个库bbb;
Query OK, 1 row affected (0.00 sec)

mysql> use bbb;   //使用库
Database changed
mysql> create table biao2 (name char(64) not null,num int(16),primary key (num));  //创建表并定义表
Query OK, 0 rows affected (0.01 sec)
mysql> describe bbb.biao2;   //查看表创建成功
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(64) | NO   |     | NULL    |       |
| num   | int(16)  | NO   | PRI | 0       |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> drop table bbb.biao2;  //删除表
Query OK, 0 rows affected (0.00 sec)
mysql> describe bbb.biao2;   //查看已不存在
ERROR 1146 (42S02): Table 'bbb.biao2' doesn't exist
mysql> show databases;   //查看库，仍然存在
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| bbb                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database bbb;    //删除上面创建的库bbb
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;    //查看库bbb已经删除成功
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| auth               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.01 sec)

mysql>

（5）管理控制表中的数据（DML）

插入数据记录
insert into 表名（字段1，字段2，...) values(字段1的值，字段2的值，...)
查询数据记录
select * from 表名; 查询全表
select * from 表名 where 字段1='字段1内容'；查询指定字段内容
select 字段1 ，字段2，from 表名 where 字段1=‘字段1的值’；``select 字段1 from 表名；只查看某个字段的值
修改数据记录
update 表名 set 字段1 =字段值1 [字段名2 =字段值2] where 条件表达式 修改某个字段的某个值
修改数据库密码
mysql>update user set authentication_string=password('newpass') where user='root';//5.7下
mysql> update mysql.user set password=PASSWORD(’新密码’) where User=’root’;//5.6下
删除数据库记录
select from 表名 where 字段1=‘字段1的值’；删除表中的某个数据记录
以上所有举例如下：

mysql> insert into users(username,password) values('liming',111111);  //插入字段 方法1
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;   //插入成功之后查看
+----------+----------+
| username | password |
+----------+----------+
| liming   |   111111 |
+----------+----------+
1 row in set (0.00 sec)
mysql> insert into users values ('lisi',null);    //插入字段方法2
Query OK, 1 row affected (0.00 sec)
mysql> select * from users;   //插入成功后查看
+----------+----------+
| username | password |
+----------+----------+
| liming   |   111111 |
| lisi     |     NULL |
| zhangsan |   222222 |
+----------+----------+
3 rows in set (0.00 sec)
mysql> select * from users where username='liming'; //查询某个字段
+----------+----------+
| username | password |
+----------+----------+
| liming   |   111111 |
+----------+----------+
1 row in set (0.00 sec)
mysql> select username,password from users where username='liming';    //查询某一部分
+----------+----------+
| username | password |
+----------+----------+
| liming   |   111111 |
+----------+----------+
1 row in set (0.00 sec)

mysql> update users set password=111131 where  username='liming';   //修改某字段的值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users where username='liming';   //查看已修改成功
+----------+----------+
| username | password |
+----------+----------+
| liming   |   111131 |
+----------+----------+
1 row in set (0.00 sec)

mysql> delete from users where username='lisi';  //删除lisi
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;   //查看已删除
+----------+----------+
| username | password |
+----------+----------+
| liming   |   111131 |
| zhangsan |   222222 |
+----------+----------+
2 rows in set (0.00 sec)

mysql>

3，数据表高级操作

（1）清空表

有两种方式，都只是清空内容，并不会删除表
命令为：

delete from 表名
truncate table 表名

mysql> delete from users;   //清空表
Query OK, 2 rows affected (0.01 sec)

mysql> show tables;  //查看表依然存在
+---------------+
| Tables_in_aaa |
+---------------+
| users         |
+---------------+
1 row in set (0.00 sec)
mysql> select * from users;   //查看已没有内容
Empty set (0.00 sec)

mysql>

(2) 建立临时表

临时建立的表，用于保存一些临时数据，临时表有个特性，就是只在当前连接可见，当前连接下可执行增删改查等操作，当连接关闭后，临时表就会被mysql删除，相关的资源也会被释放。
create temporary table '表名'；
例如：

mysql> create temporary table temp(id int(10) not null auto_increment,name varchar(32) character set utf8 collate utf8_bin not null,level int(10) not null,primary key (id)) engine=InnoDB default charset=utf8;      //建立临时表
Query OK, 0 rows affected (0.00 sec)      

mysql> show tables;  //查看不存在表中
+---------------+
| Tables_in_aaa |
+---------------+
| users         |
+---------------+
1 row in set (0.00 sec)

mysql> 
mysql> insert into temp values(1,'xiaoli',5);  //临时表里面插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from temp;   //查看临时表中的内容
+----+--------+-------+
| id | name   | level |
+----+--------+-------+
|  1 | xiaoli |     5 |
+----+--------+-------+
1 row in set (0.00 sec)

以下操作为退出之后再次登陆mysql查询临时表已经不存在
mysql> exit
Bye
[root@server1 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
mysql> use aaa;
mysql> select * from temp;
ERROR 1146 (42S02): Table 'aaa.temp' doesn't exist
mysql>

（3）克隆表（两种方法）

LIKE方法（从原表完整复制结构生成test表，再导入数据）
命令为：
create table test like 表名;
insert into test select * from 表名；

mysql> select * from users;   //users表已经存在
+----------+----------+
| username | password |
+----------+----------+
| lisi     |        0 |
| zhangsan |   111111 |
+----------+----------+
2 rows in set (0.00 sec)

mysql> create table test like users;  //克隆表users 名为text
Query OK, 0 rows affected (0.01 sec)

mysql> describe users;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| username | char(64) | NO   | PRI | NULL    |       |
| password | int(24)  | YES  |     | 123456  |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test select * from users;   //将表users里面的内容拷贝到text里面
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> describe test;
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| username | char(64) | NO   | PRI | NULL    |       |
| password | int(24)  | YES  |     | 123456  |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from  text;   //查看内容已被拷贝过来
ERROR 1146 (42S02): Table 'aaa.text' doesn't exist
mysql> select * from  test;
+----------+----------+
| username | password |
+----------+----------+
| lisi     |        0 |
| zhangsan |   111111 |
+----------+----------+
2 rows in set (0.00 sec)
mysql>

SHOW CREATE TABLE方法
show create table ‘表名’\G
create table test1()
insert into test1 select * from 表名；
`

mysql> show create table users\G  //查看要复制的表信息
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `username` char(64) NOT NULL,
  `password` int(24) DEFAULT '123456',
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>  CREATE TABLE `test1` (      //复制新表，并命名为test1
    ->   `username` char(64) NOT NULL,
    ->   `password` int(24) DEFAULT '123456',
    ->   PRIMARY KEY (`username`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> describe test1;  //查看表已经复制成功
+----------+----------+------+-----+---------+-------+
| Field    | Type     | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| username | char(64) | NO   | PRI | NULL    |       |
| password | int(24)  | YES  |     | 123456  |       |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test1 select * from users;   //将原表内容添加到新表中
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from test1;   //查看新表test1已经将内都复制过来。克隆成功
+----------+----------+
| username | password |
+----------+----------+
| lisi     |        0 |
| zhangsan |   111111 |
+----------+----------+
2 rows in set (0.00 sec)

mysql>

二，数据库用户授权

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

mysql> flush privileges;   //刷新
Query OK, 0 rows affected (0.02 sec)

mysql> grant select on aaa.test1 to 'liming'@'localhost'  identified by '000000';   创建执行表test1的权限用户
Query OK, 0 rows affected (0.01 sec)

mysql> exit 
[root@server1 ~]# mysql -u liming -p   // 用liming用户登录进行验证
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use aaa;  // 验证表是有权限的
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> describe test;   // 查看其它表无权限
ERROR 1142 (42000): SELECT command denied to user 'liming'@'localhost' for table 'test'
mysql> select * from test1;   //查看授权的表有权限查看
+----------+----------+
| username | password |
+----------+----------+
| lisi     |        0 |
| zhangsan |   111111 |
+----------+----------+
2 rows in set (0.00 sec)

mysql> select * from test;   //调取其他表中的信息无权限
ERROR 1142 (42000): SELECT command denied to user 'liming'@'localhost' for table 'test'
mysql>

2，查看用户权限

命令为：

show grants for 用户名@来源地址

mysql> show grants for liming@'localhost';   //查看用户liming的权限
+----------------------------------------------------------------------------+
| Grants for liming@localhost                                                |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liming'@'localhost' IDENTIFIED BY PASSWORD <secret> |
| GRANT SELECT ON `aaa`.`test1` TO 'liming'@'localhost'                      |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)    //显示可访问sss表里面的test1

mysql>

此时查看权限时，只能查看所登录的用户的权限, 当前登录的是Liming，所以查看权限只能查看liming的权限

以下操作为在root上查看root，与liming的权限

mysql> show grants for 'root'@'localhost';    //查看root权限
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION         //可以访问所有                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'liming'@'localhost';         //查看liming权限，只能访问aaa下的test1
+---------------------------------------------------------------------------------------------------------------+
| Grants for liming@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liming'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
| GRANT SELECT ON `aaa`.`test1` TO 'liming'@'localhost'                                                         |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>

3，撤销用户权限（DCL)

命令为：
revoke 权限列表 on 数据库名.表名 from 用户

mysql> show grants for 'liming'@'localhost';    // 查看用户liming的权限，上一步中已经将liming用户授权
+---------------------------------------------------------------------------------------------------------------+
| Grants for liming@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liming'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
| GRANT SELECT ON `aaa`.`test1` TO 'liming'@'localhost'                                                         |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke all privileges on aaa.test1 from 'liming'@'localhost';  //撤销liming的授权
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'liming'@'localhost';   //查看liming已无权限
+---------------------------------------------------------------------------------------------------------------+
| Grants for liming@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liming'@'localhost' IDENTIFIED BY PASSWORD '*032197AE5731D4664921A6CCAC7CFCE6A0698693' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

八橘子

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
1
评论
MYSQL基本操作（建库，建表，插入内容，清除内容）以及高级操作（清空表，临时表，克隆表）与数据用户授权

MYSQL基本操作和高级操作以及用户授权一，数据库基本操作1，SQL语句概述（1）SQL语言（2）SQL分类2，基本数据库管理命令（1），查看已存在数据库信息（2），查看数据库中的表中有哪些表（3）显示数据表的结构（字段）（4) 创建数据库和表(DDL)（5）管理控制表中的数据（DML）3，数据表高级操作（1）清空表(2) 建立临时表（3）克隆表（两种方法）二，数据库用户授权1，DCL语句设置用户权限（用户不存在时，则新建用户）2，查看用户权限3，撤销用户权限（DCL)一，数据库基本操作1，SQL语句概
复制链接

扫一扫