1.关系型数据库介绍
1.1数据结构模型
数据结构模型主要有:
- 层次模型
- 网状模型
- 关系模型
关系模型：(简称二维关系)
二维关系：row, column
数据库管理系统(Database Management System)：
是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。
关系数据库管理系统（Relational Database Management System：RDBMS）是指包括相互联系的逻辑组织和存取这些数据的一套程序 (数据库管理系统软件)。
1.2RDBMS专业名词
常见的关系型数据库管理系统：
- MYSQL：MYSQL, MairaDB, Percona-Server
- PostgreSQL :简称pgsql
- Oracle
- MSSQL
事务：多个操作被当做一个整体对待就称为一个事务。要看一个关系型数据库是否支持事务，需要看其是否支持并满足ACLD测试
ACID：ACID是事务的一个基本标准
- A：Automicity , 原子性
- C：Consistency , 一致性
- I：Isolation , 隔离性
- D：Durability , 持久性
SQL：结构化查询语言(Structured Query Language)是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
约束：constraint ，向数据表提供的数据要遵守的限制,作用是添加、删除。
- 主键约束（Primary Key constraint）：要求主键列数据唯一，并且不允许为空。
- 唯一约束（Unique constraint）：要求该列唯一，允许为空，但只能出现一个空值（NULL）。
- 检查约束（Check constraint）：某列取值范围限制，格式限制等，如有关年龄、邮箱（必须有@）的约束。
- 默认约束（Default constraint）：某列的默认值，如在数据库里有一项数据很多重复，可以设为默认值。
- 外键约束（Foreign Key constraint）：用于在两个表之间建立关系，需要指定引用主表的哪一列。
索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按照特定次序排序存储
关系的基本运算有两类：一类是传统的集合运算（并、差、交等），另一类是专门的关系运算
常用的关系运算：
- 选择：是从二维表中选出符合条件的记录，它是从行的角度对关系进行的运算。
- 投影：是从二维表中选出所需要的列，它是从列的角度对关系进行的运算。
- 连接：是同时涉及到两个二维表的运算，它是将两个关系在给定的属性上满足给定条件的记录连接起来而得到的一个新的关系。
数据抽象方式：
- 物理层：最低层次的抽象，决定数据的存储格式，即RDBMS在磁盘上如何组织文件
- 逻辑层：比物理层次稍高的层次的抽象，描述数据库存储什么数据，以及数据间存在的什么样的关系
- 视图层：最搞层次的抽象，但只描述整个数据库的某个部分。
1.3关系型数据库常见组件
数据库：database
表：table， 由（row）和列（column）组成
索引：index
视图：view
用户：user
权限：privilege
存储过程：procedure
存储函数：function
触发 器：trigger
事件调度器：event scheduler
1.4SQL语句
SQL语句有三种类型：
- DDL：数据定义语言，用来建立数据库、数据库对象和定义列的命令。
包括：create（创建）、drop（删除）、alter（修改） - DML：数据操纵语言，用来操纵数据库中数据的命令。
包括：insert（向表中插入数据）、delete（删除表中数据）、 update（更新表中数据）、select（查询表中数据） - DCL：数据控制语言，用来控制数据库组件。
2.MySQL 安装与配置
2.1mysql安装
mysql安装方式有三种：
- 源代码：编译安装
- 二进制格式的程序包：展开至特定路径，并经过简单配置后使用
- 程序包管理器管理的程序包：
rpm:有两种
1.OS Vendor:操作系统发行商提供的
2.项目官方提供的
deb
配置MySQL的yum源
//安装之前先移除之前的源，以免发生冲突
[root@server ~]# cd /etc/yum.repos.d/
[root@server yum.repos.d]# ls
redhat.repo xxx.repo
[root@server yum.repos.d]# mv * /root
//下载关于MySQL的两个源
[root@server yum.repos.d]# rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
下载过程略...
[root@server yum.repos.d]# ls
mysql-community.repo mysql-community-source.repo
安装MySQL5.7
//查看MySQL的所有包
[root@server yum.repos.d]# yum list all |grep mysql
//安装关于MySQL的服务包、客户包、命令包、工具包，其他的包按需下载
[root@server~]# yum -y install mysql-community-server mysql-community-c lient mysql-community-common mysql-community-devel
安装过程略...
配置MySQL
//启动MySQL服务并确保3306端口已监听
[root@server ~]# systemctl start mysqld
[root@server ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 32 *:21 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 :::22 :::*
LISTEN 0 100 ::1:25 :::*
LISTEN 0 80 :::3306 :::*
//在日志文件中找出自己的临时密码
[root@server ~]# grep "password" /var/log/mysqld.log
2020-04-05T04:16:18.121761Z 1 [Note] A temporary password is generated for root@localhost:
4%i8u%6J1JgN
//此处的临时密码为4%i8u%6J1JgN，这个密码是随机的，所以禁止复制此处的密码
//使用获取到的临时密码登录MySQL
[root@server ~]# mysql -u root -p
Enter password: //此处输入密码，可粘贴复制
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.29
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> //表示已经成功登陆
//修改MySQL登录密码为123
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.10 sec)
mysql> quit
Bye
//为避免MySQL自动升级，这里需要卸载最开始安装的yum源
[root@server ~]# rpm -qa |grep mysql
mysql-community-libs-5.7.29-1.el7.x86_64
mysql-community-libs-compat-5.7.29-1.el7.x86_64
mysql57-community-release-el7-11.noarch
mysql-community-common-5.7.29-1.el7.x86_64
mysql-community-client-5.7.29-1.el7.x86_64
mysql-community-devel-5.7.29-1.el7.x86_64
mysql-community-server-5.7.29-1.el7.x86_64
[root@server ~]# yum -y remove mysql57-community-release-el7-10.noarch
删除过程略...
3.MySQL的程序组成
- 客户端
MySQL：CLI交互式客户端程序
mySQL_secure_installarion：安全初始化，强烈建议安装完以后执行此命令
mysqldump：mysql备份工具
mysqladmin - 服务端
mysqld
3.1mysql工具使用
//语法：mysql [OPTIONS] [database]
常用的选项options:
-u //指定用户名 默认为root 此root为MySQL的管理员
-h //指定登录的服务器主机（用于远程登录）， 无-h时默认为登录（localhost）本机的数据库
-p //指定用户的密码
-P //指定数据库监听的端口,默认端口3306
-V //查看当前使用的MySQL版本
-e //不直接登录MySQL执行sql语句退出后 常用于脚本
//登录本机的数据库的三种方式
[root@client ~]# mysql -u root -p 123 -h localhost
[root@client ~]# mysql -u root -p 123 -h 127.0.0.1
[root@client ~]# mysql -u root -p 123
//例如：不直接登录MySQL查看有哪些数据库
[root@client ~]# mysql -u root -p123 -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@client ~]#
3.2服务器监听的两种socker地址
ip socket：默认监听在tcp的3306端口，支持远程通信
unix sock ：监听在sock文件上（/tmp/mysql.sock, /var/lib/mysql/mysql.sock） server地址只能是：localhost ，127.0.0.1，仅支持本地登录。
server地址只能是：localhost ，127.0.0.1
[root@server ~]# mysql -pabcd123! -S /var/lib/mysql/mysql.sock
4.mysql数据库操作
4.1DDL操作
//查看当前实例有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.11 sec)
//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] ‘DB_NAME’;
//创建数据库virus,如果virus存在则退出，否则创建
mysql> create database if not exists virus;
Query OK, 1 row affected (0.00 sec)
//删除数据库
语法：DROP DATABASE [IF EXISTS] ‘DB_NAME’;
//删除virus这个数据库并查看
mysql> drop database if exists virus;
Query OK, 0 rows affected (0.36 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys | |
+--------------------+
4 rows in set (0.00 sec
4.1.2DML操作
//创建表
//语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) EN
GINE=‘存储引擎类型’;
//在 lzh（需提前创建）数据库，创建表abc,字段分别为id ,name,age
mysql> use lzh;
Database changed
mysql> create table abc (id int not null,name varchar(100),age tinyint);
Query OK, 0 rows affected (0.01 sec)
//查看当前数据库中有哪些表
mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| abc |
+---------------+
1 row in set (0.00 sec)
//查看表结构
mysql> desc abc;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
//删除表
//语法：DROP TABLE [ IF EXISTS ] ‘table_name’;
//删除表abc
mysql> drop table abc;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
4.1.3用户操作
mysql用户账号由两部分组成，如’USERNAME’@‘HOST’,表示此USERNAME只能从此HOST上远程登录。
这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序其值可为：
- ip地址，如：192.168.73.10
- 通配符：
%：匹配任意长度的任意字符，常用于设置允许任何主机登录
_ : 匹配任意单个字符
//数据库用户创建
//语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//创建数据库用户 putao密码为123.com
mysql> create user 'putao'@'127.0.0.1' identified by '123.com';
Query OK, 0 rows affected (0.00 sec)
//使用新创建的用户和密码登录
[root@reserve ~]# mysql -uputao -p123.com -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
//删除数据库用户（退出新创建的用户数据库，在管理员账号中删除）
//语法：DROP USER 'username'@'host';
mysql> drop user 'putao'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)
4.1.4查看命令SHOW
mysql> show character set; //查看支持的所有字符集
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
........
mysql> show engines; //查看当前数据库支持的所有存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> show databases; //查看数据库信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzh |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show tables from lzh; //不进入lzh数据库而列出其包含的所有表
+---------------+
| Tables_in_lzh |
+---------------+
| abc |
+---------------+
1 row in set (0.00 sec)
//查看表结构
语法：DESC [db_name.]table_name;
mysql> desc lzh.abc;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
查看某表的创建命令
//语法：SHOW CREATE TABLE table_name;
mysql> show create table abc;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc | CREATE TABLE `abc` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//查看某表的状态
//语法：SHOW TABLE STATUS LIKE 'table_name'\G
mysql> use lzh; //进入lzh数据库
Database changed
mysql> show table status like 'abc'\G
*************************** 1. row ***************************
Name: abc
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2020-08-07 16:47:59
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
4.1.5获取帮助
//获取命令使用帮助
//语法：HELP keyword; //获取创建表的帮助
mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
... ...
... ...
4.2 DML操作
4.2.1 INSERT语句
DML操作包括增（INSERT）、删(DELETE)、改(UPDATE)、查(SELECT)，均属于针对表的操作
语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value
1,...),(...),...
mysql> use lzh;
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> insert into abc (id,name,age) value (1,'pu',30); //一次插入一条记录
Query OK, 1 row affected (0.01 sec)
mysql> insert into abc (id,name,age) values (2,'li',22),(3,'zi',18),(4,'han',22),(5,'xubo',20),(6,'lizihan',25); //一次插入多条记录
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
4.2.2 SELECT语句
字段column 表示法
| 表示符 | 代表什么？ |
|---|---|
| * | 所有字段 |
| as | 字段别名，如col1 AS alias1当表名很长时用别名代替 |
条件判断语句WHERE
| 操作类型 | 常用操作符 |
|---|---|
| 操作符 | >,<,>=,<=,!= BETWEEN column AND column LIKE:模糊匹配 ，RLIKE:基于正则表达式进行模糊匹配 ，IS NOT NULL :非空， IS NULL:空 |
| 条件逻辑操作 | AND、 OR 、 NOT |
ORDER BY：排序，默认为升序（ASC）
| ORAER BY语句 | 意义 |
|---|---|
| ORDER BY ‘column_name’ | 根据column_name进行升序 |
| ORDER BY 'column_name‘ DESC | 根据column_name进行降序排序 |
| ORDER BY ‘column_name’ LIMIT 2 | 根据column_name进行升序 ,并只取前2个结果 |
| ORDER BY ‘column_name’ LIMIT 1,2 | 根据column_name进行升序 ,并略过第一个结果取后面的2个结果 |
//DML操作之查操作
//语法：SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> use lzh;
Database changed
mysql> select * from abc;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | pu | 30 |
| 2 | li | 22 |
| 3 | zi | 18 |
| 4 | han | 22 |
| 5 | xubo | 20 |
| 6 | lizihan | 25 |
+----+---------+------+
6 rows in set (0.00 sec)
mysql> select name from abc;
+---------+
| name |
+---------+
| pu |
| li |
| zi |
| han |
| xubo |
| lizihan |
+---------+
6 rows in set (0.00 sec)
mysql> select * from abc order by age；
+----+---------+------+
| id | name | age |
+----+---------+------+
| 3 | zi | 18 |
| 5 | xubo | 20 |
| 2 | li | 22 |
| 4 | han | 22 |
| 6 | lizihan | 25 |
| 1 | pu | 30 |
+----+---------+------+
6 rows in set (0.00 sec)
mysql> select * from abc order by age desc;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | pu | 30 |
| 6 | lizihan | 25 |
| 2 | li | 22 |
| 4 | han | 22 |
| 5 | xubo | 20 |
| 3 | zi | 18 |
+----+---------+------+
6 rows in set (0.00 sec)
mysql> select * from abc order by age limit 2;
+----+------+------+
| id | name | age |
+----+------+------+
| 3 | zi | 18 |
| 5 | xubo | 20 |
+----+------+------+
2 rows in set (0.00 sec)
mysql> select * from abc order by age limit 1,2;
+----+------+------+
| id | name | age |
+----+------+------+
| 5 | xubo | 20 |
| 2 | li | 22 |
+----+------+------+
2 rows in set (0.00 sec)
mysql> select * from abc where age >=25;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | pu | 30 |
| 6 | lizihan | 25 |
+----+---------+------+
2 rows in set (0.00 sec)
mysql> select * from abc where age >=25 and name = 'pu';
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | pu | 30 |
+----+------+------+
1 row in set (0.00 sec)
mysql> select * from abc where age BETWEEN 23 and 28; //查找表abc中age在23到28之间
+----+---------+------+
| id | name | age |
+----+---------+------+
| 6 | lizihan | 25 |
+----+---------+------+
1 row in set (0.00 sec)
mysql> select * from abc where age is not null;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | pu | 30 |
| 2 | li | 22 |
| 3 | zi | 18 |
| 4 | han | 22 |
| 5 | xubo | 20 |
| 6 | lizihan | 25 |
+----+---------+------+
6 rows in set (0.00 sec)
mysql> select * from abc where age is null;
Empty set (0.00 sec)
4.2.3 update 语句
//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> select * from abc;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | pu | 30 |
| 2 | li | 22 |
| 3 | zi | 18 |
| 4 | han | 22 |
| 5 | xubo | 20 |
| 6 | lizihan | 25 |
+----+---------+------+
6 rows in set (0.00 sec)
mysql> update abc set age = 27 where name = 'han';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from abc;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | pu | 30 |
| 2 | li | 22 |
| 3 | zi | 18 |
| 4 | han | 27 |
| 5 | xubo | 20 |
| 6 | lizihan | 25 |
+----+---------+------+
6 rows in set (0.00 sec)
4.2.4delete语句
//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> select * from abc;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | pu | 30 |
| 2 | li | 22 |
| 3 | zi | 18 |
| 4 | han | 27 |
| 5 | xubo | 20 |
| 6 | lizihan | 25 |
+----+---------+------+
6 rows in set (0.00 sec)
mysql> delete from abc where id = 6; //删除第7条记录
Query OK, 1 row affected (0.00 sec)
mysql> select * from abc;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | pu | 30 |
| 2 | li | 22 |
| 3 | zi | 18 |
| 4 | han | 27 |
| 5 | xubo | 20 |
+----+------+------+
5 rows in set (0.00 sec)
mysql> delete from abc; //删除整张表的内容
Query OK, 5 row affected (0.00 sec)
4.2.5 trunkcate语句
trunvate 与delete 的区别
| 语句类型 | 特点 |
|---|---|
| delete | 1.delete删除表内容时仅删除内容，但会保留表结构。 2.delete语句每删除一行，并在事务日志中为所删除的每一行记录一项，可以通过回滚事务日志恢复数据。 3.非常占用空间 |
| truncate | 1.删除表中所有数据，且无法恢复表结构、约束和索引等保持不变，新添加的行计数值重置为初始值。2.执行速度不delete快，且使用的系统和事务日志资源少。3.通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。4.对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据，不能用于加入了索引视图的表 |
语法：TRUNCATE table_name;
mysql> select * from abc;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | pu | 30 |
| 2 | li | 22 |
| 3 | zi | 18 |
| 4 | han | 27 |
| 5 | xubo | 20 |
| 7 | bana | 29 |
+----+------+------+
6 rows in set (0.00 sec)
mysql> truncate abc;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from abc;
Empty set (0.00 sec)
4.3 DCL操作
4.3.1创建授权grant
权限类型（priv_type）
| 权限类型 | 代表什么？ |
|---|---|
| ALL | 所有权限 |
| SELECT | 读取内容的权限 |
| INSERT | 插入内容的权限 |
| UPADTE | 更新内容的权限 |
| DELETE | 删除内容的权限 |
指定要操作的对象 db_name.table_name
| 表示方式 | 意义 |
|---|---|
| . | 所有库的所有表 |
| db_name | 指定库的所有表 |
| db_name.table_name | 指定库的指定表 |
WITH ARANT OPTION :被授权的用户可将自己的权限副本转赠给其他用户，说白了就是将自己的权限完全复制给另外一个用户。不建议使用。
语法：GRANT priv_type,... ON [object_type] db_name.table_name TO ‘username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzh |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//授权putao用户在数据库本机上登录访问所有数据库
mysql> grant all on *.* to 'putao'@'localhost' identified by '123.com';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
//授权baix用户在192.168.73.60上远程登录访问lzh数据库的所有表
mysql> grant all on lzh.* to 'baix'@'192.168.73.60' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
客户端验证
[root@client src]# ls /etc/yum.repos.d/
epel.repo mysql-community.repo mysql-community-source.repo redhat.repo xxx.repo
[root@client src]# yum -y install mysql-community-client.x86_64 //只需下载客户端数据库
[root@client ~]# systemctl stop firewalld //服务端客户端均关闭防火墙
[root@client ~]# mysql -ubaix -p123.com -h192.168.73.50
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzh |
+--------------------+
2 rows in set (0.00 sec)
//授权baix用户在任何主机上远程登录访问所有数据库
mysql> grant all on *.* to 'baix'@'%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4.3.2查看授权
//查看当前登录用户的授权信息
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)
//查看指定用户baix的授权信息
mysql> show grants for baix;
+---------------------------------------------------+
| Grants for baix@% |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'baix'@'%' |
| GRANT ALL PRIVILEGES ON `lzh`.`abc` TO 'baix'@'%' |
+---------------------------------------------------+
2 rows in set (0.00 sec)
//查看baix用户在本地的授权信息
mysql> show grants for 'baix'@'localhost';
+-------------------------------------------------------+
| Grants for baix@localhost |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO 'baix'@'localhost' |
| GRANT ALL PRIVILEGES ON `lzh`.* TO 'baix'@'localhost' |
+-------------------------------------------------------+
2 rows in set (0.00 sec)
4.3.3 取消授权REVOKE
//语法：REVOKE priv_type,... ON db_name.table_name FROM'username'@'host';
//取消baix用户从任何主机远程登录的权限
mysql> revoke all on *.* from 'baix'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注意：mysql服务进程启动时会读取mysql库中所有授权表至内存中：
- GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程自动读取授权表，并更新至内存中
- 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表
mysql> flush privileges;
6293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
