MySQL：增删改查、临时表、授权相关示例

目录

概念

数据完整性

主键

数据类型

精确数字

近似数字

字符串

二进制字符串

日期和时间

MySQL常用语句示例

SQL结构化查询语言

显示所有数据库

显示所有表

查看指定表的结构

查询指定表的所有列

创建一个数据库

创建表和列

插入数据记录

查询数据记录

修改表中数据

在mysql内修改root用户登录mysql的密码

在终端修改mysql的root用户登录密码

克隆

删除

delete

truncate

临时表

授权

授权用户

授权远程用户

查看指定账户的授权

撤销指定账户授权

Windows通过Navicat连接MySQL服务器

---

概念

数据完整性

数据的正确性

• 数据完整性
  • 每一行必须是唯一的实体（每个实体必须唯一）
• 域完整性
  • 检查每一列是否有效
• 引用完整性
  • 确保所有表中数据的一致性，不允许引用不存在的值
• 用户定义的完整性
  • 指定特定的业务规则

主键

• 用于唯一标识表中的行数据
• 由一个或多个字段组成
• 具有唯一性
• 不允许取空值（NULL）
• 一个表只能有一个主键

数据类型

精确数字

近似数字

字符串

二进制字符串

日期和时间

---

MySQL常用语句示例

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言用于存取数据以及查询、更新和管理关系数据库系统。

SQL结构化查询语言

• DDL数据定义语言
  • create
  • drop（针对数据库对象）
  • alter
• DML数据操纵语言
  • insert
  • update
  • delete（针对表中的记录）
• DQL数据查询语言
  • select
  • show
  • describe
• DCL数据控制语言
  • grant
  • revoke
  • commit（事务）
  • rollback（回滚）

---

首先登录数据库，以便进行下一步实验

[root@localhost ~]# mysql -uroot -p
Enter password: 输入密码

显示所有数据库

在登录进数据库后，可以使用show databases;来列出所有数据库

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

• information_schema：数据库本身所需的信息（库、表信息）
• mysql：程序运行时所需要的数据，主要是用户认证的信息

显示所有表

需要在数据库内输入show tables才能生效

进入mysql数据库
mysql> use mysql    # 不用加分号结尾
mysql> show tables;

查看指定表的结构

查看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                     |      

查询指定表的所有列

这里使用select语句查询user表的所有列，\G表示以垂直方式显示，代替默认的水平方式，水平方式输出会看起来很杂乱

mysql> select * from user\G;

创建一个数据库

create 类型 名称

mysql> create database auth;
Query OK, 1 row affected (0.00 sec)
mysql> use auth;    # 更改当前操作的数据库为auth
Database changed

创建表和列

使用create语句创建名为users表并添加两列数据

DEFAULT ' '：该列默认为空（这里表示密码默认为空）

创建完后，可以使用show tables;查看该表

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.00 sec)
mysql> show tables;
+----------------+
| Tables_in_auth |
+----------------+
| users          |
+----------------+

插入数据记录

在users表的指定列中插入信息

insert语法：insert into 表名 (列名1, 列名2) values (值1, 值2);

mysql>use auth;
mysql>insert into users(user_name, user_passwd) values ('tom', '123456')
mysql>insert into users(user_name, user_passwd) values ('jerry', '654321');

查询数据记录

查询users表中所有列，以及添加where过滤指定列的所有信息

mysql> select * from auth.users;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| jerry     | 654321      |
| tom       | 123456      |
+-----------+-------------+mysql> select user_name, user_passwd from auth.users where user_name='tom';
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| tom       | 123456      |
+-----------+-------------+

修改表中数据

修改auth数据库中users表中的user_name为tom的行中的user_passwd列的值为000，并且使用加密算法加密该值

然后使用select语句查询，可以看到user_passwd列的值被加密了

mysql> update auth.users set user_passwd=password('000') where user_name='tom';
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| jerry     | 654321                                    |
| tom       | *B66FEB4CF5216D971C066EB920EC03B27A5A3F40 |
+-----------+-------------------------------------------+

在mysql内修改root用户登录mysql的密码

更新mysql库下user表中的authentication_string列，调用password对密码进行加密，where条件为root用户

使用flush privileges;命令重新加载授权表，使当前对授权的更改生效，而无需重新启动MySQL服务。

mysql> update mysql.user set authentication_string=password('123456') where user='root';
mysql> flush privileges;
[root@localhost ~]# mysql -uroot -p
Enter password: 输入刚才设置的密码
mysql>
成功登录

在终端修改mysql的root用户登录密码

[root@localhost ~]# mysqladmin -uroot -p123456 password 'pwd123'
[root@localhost data]# mysql -uroot -p
Enter password: 输入密码
mysql>

---

克隆

利用users表克隆一个名为user01的表，但是克隆后的新表是没有内容的

mysql> create table user01 like auth.users;mysql> show tables;
+----------------+
| Tables_in_auth |
+----------------+
| user01         |
| users          |
+----------------+

将users表的内容全部插入到user01新表中

mysql> insert into user01 select * from auth.users;mysql> select * from user01;
+-----------+-------------+
| user_name | user_passwd |
+-----------+-------------+
| tom       | 123456      |
+-----------+-------------+

---

删除

delete

使用delete from 表名 where 列名来删除指定行

如果不在结尾加where，是删除指定的表。可以自由搭配

mysql> delete from auth.users where user_name='jerry';mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| tom       | *B66FEB4CF5216D971C066EB920EC03B27A5A3F40 |
+-----------+-------------------------------------------+

truncate

mysql> truncate table user01;mysql> select * from user01;
Empty set (0.00 sec)

• truncate table 表名;
  • 功能：truncate table用于快速且高效地删除表中的所有数据，但保留表的结构（即表定义）。

• • 操作：它是一个DDL（数据定义语言）命令，不是一个事务，并且不能通过回滚来恢复删除的数据。
  • 速度：由于不记录删除的每一行数据，而是直接释放数据页，因此truncate table操作通常比delete from更快。
• delete from 表名;
  • 功能：delete from用于从表中删除指定的行数据。

• • 操作：它是一个DML（数据操作语言）命令，它可以作为一个事务来执行，并且可以通过事务回滚来撤销删除操作。
  • 记录：delete from操作会将每一行删除操作的记录写入事务日志，并且在执行时会触发触发器（如果有的话）。
• 延续id的区别
  • 如果使用delete清除一个表后，添加新内容的id会延续之前的id。
  • 而使用truncate清除一个表后，添加新内容的id会还原，从1开始。

---

临时表

临时表用于临时存放一些数据，如果退出mysql管理系统，再进来就找不到在临时表中存储的数据了

mysql> create temporary table test(id int(10) not null auto_increment, name char(116) character set utf8 collate utf8_bin not null primary key (id));
mysql> insert into test (name) values ('tom');
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+

退出后，再进入。找不到test临时表

mysql> quit[root@localhost data]# mysql -uroot -p
Enter password: 输入密码mysql> select * from test;
ERROR 1046 (3D000): No database selected

---

授权

授权是一组权限的集合，比如授权select命令给某个用户，这里select就是一个角色，只是这个角色的名称叫select

权限是单个的，但是授权这个操作是将一组权限的集合分配给用户

比如使用 grant select on 库名.表名 to '用户名'@'localhost'; 是授予指定用户可以在指定数据库中的指定表中执行select的权限

授权用户

指定tom用户在本机只能使用select语句查询访问auth库中的所有表

mysql> grant select on auth.* to 'tom'@'localhost' identified by '123456';

这里的localhost表示授权本机

根据需求和实际情况也可以写成IP地址或网段，网段的格式在主机部分要写成%（192.168.10.%）或允许所有主机（'root'@'%'）

此时查询mysql库中的user表可以看到刚刚授权的tom用户的授权信息被生成出来了，此时退出mysql

mysql> select * from mysql.user\G;
*************************** 4. row ***************************Host: localhost    # 允许该用户在什么地方登录User: tom    # 账号名# 省略部分信息plugin: mysql_native_password    # 当你对该用户设置密码时，利用该插件对密码进行加密authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9    # 加密过的密码password_expired: Npassword_last_changed: 2024-07-23 22:02:46password_lifetime: NULLaccount_locked: Nmysql> exit

在终端使用tom账户也可以登录了

再使用select语句查询授权过的auth库内的表，可以查询

如果查询别的库的信息，就不行了

[root@localhost ~]# mysql -utom -p123456
mysql>
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| tom       | *B66FEB4CF5216D971C066EB920EC03B27A5A3F40 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)mysql> select * from mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'tom'@'localhost' for table 'user'

授权远程用户

指定授权任何主机（%）都可以使用root用户登录mysql，并且拥有所有库的所有表的访问权限

这里的123456密码，是远程主机登录的密码，而不是本地主机登录的密码

mysql> grant all on *.* to root@'%' identified by '123456';
mysql> flush privileges;

在102主机安装MySQL客户端

[root@localhost ~]# yum -y install mysql
[root@localhost ~]# mysql -uroot -p123456 -h 192.168.10.101 -P 3306
MySQL [(none)]>

查看指定账户的授权

使用show命令查看指定账户的权限

mysql> show grants for 'tom'@'localhost';
+-----------------------------------------------+
| Grants for tom@localhost                      |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'localhost'       |
| GRANT SELECT ON `auth`.* TO 'tom'@'localhost' |
+-----------------------------------------------+

撤销指定账户授权

首先确保是有mysql管理权的用户登录mysql，比如root

如果完成了上方的操作，要执行这里的命令记得exit退出，换成root用户登录进mysql

这里使用revoke语句从'tom'@'localhost'用户上撤销在auth数据库下所有表的所有权限

revoke的语法： revoke 权限 on 库名.表名 from '用户名'@'地址'; 

这里的权限就是你授权的权限，比如select、insert、update、delete等

[root@localhost ~]# mysql -uroot -p
Enter password: 输入密码
mysql> revoke all on auth.* from 'tom'@'localhost';

---

Windows通过Navicat连接MySQL服务器

前提是授权时将地址设为指定IP或%

在计算机管理开启MySQL服务

软件内左上角连接 ——》MySQL

主机IP填为MySQL服务器的IP，密码填为在MySQL服务器设置的

然后可以在左边列表看到连接的MySQL服务器