MySQL数据库基础

MySQL数据库基础

1、数据库基本概念

1.1 数据库简介

数据库，简而言之可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据运行新增、截取、更新、删除等操作。所谓“数据库”系以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合；一个数据库由多个表空间（Tablespace）构成。

数据库数据存放方式：

• 结构化数据

• 半结构化数据

• 非结构化数据

1.2 数据结构模型

数据库类型的区分主要参照的是数据结构模型，而常用的数据结构模型有很多：

• 层次模型
• 网状模型
• 关系模型
• 面向对象模型
• 半结构化模型

2. 关系型数据库

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列（也就是表格）的形式存储数据，，一组表组成了数据库。通俗来讲这种数据库就是由多张表组成，并且这些表之间存在一定的关系。

关系型数据库的优点：

• 数据以表格的形式存储容易理解
• 支持SQL语言使用方便
• 易于维护
• 事务的一致性。

关系型数据库的缺点：

• 为了维护一致性所付出的巨大代价就是其读写性能比较差；
• 高并发读写效率较低；
• 海量数据的读写效率低；
• 固定的表结构。

关系型数据库举例：MySQL、Oracle、DB2、Microsoft SQL Server、SQLite等。

除了关系型数据库之外的数据库我们统称为：非关系型数据库(NoSQL)

2.1 数据库管理系统

数据库管理系统（Database Management System，简称DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。简单一句话，数据库管理系统是为了我们更方便的使用数据库而诞生的。

管理关系型数据库的系统叫做：关系型数据库管理系统(Relational Database Management System，简称RDBMS)。

MySQL使用关系型数据库管理系统管理数据库。

2.2 RDBMS专业名词

常见的关系型数据库管理系统：

• MySQL：MySQL，MariaDB，Percona-Server
• PostgreSQL：简称为pgsql
• Oracle
• MSSQL（MicroSoft 微软的数据库）或者SQLServer

SQL： Structure Query Language，结构化查询语言

约束： constraint，向数据表提供的数据要遵守的限制

• 主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
  • 一个表只能存在一个
• 惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
  • 一个表可以存在多个
• 外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
• 检查性约束

索引： 将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

2.3 SQL语句

SQL语句有三种类型：

• DDL：Data Defination Language，数据定义语言
• DML：Data Manipulation Language，数据操纵语言
• DCL：Data Control Language，数据控制语言

SQL语句类型	对应操作
DDL	CREATE：创建 DROP：删除 ALTER：修改
DML	INSERT：向表中插入数据 DELETE：删除表中数据 UPDATE：更新表中数据 SELECT：查询表中数据
DCL	GRANT：授权 REVOKE：移除授权

3. mysql安装与配置

mysql安装方式有三种：

• 源代码：编译安装
• 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
• 程序包管理器管理的程序包：
  • rpm：有两种
    • OS Vendor：操作系统发行商提供的
    • 项目官方提供的
  • deb

3.1 rpm安装

8.0和5.7的版本有较大区别，目前yum安装默认是8.0版本，安装5.7的话需要配置yum源，先去官网下载
mysql官网：mysql.com

[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@localhost ~]# ls
anaconda-ks.cfg  mysql57-community-release-el7-10.noarch.rpm
[root@localhost ~]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm 
[root@localhost ~]# ls /etc/yum.repos.d/
localhost.repo  mysql-community.repo  mysql-community-source.repo  redhat.repo

包下载完成后进行安装

# 更改yum文件
[root@localhost ~]# vim /etc/yum.repos.d/mysql-community.repo 
......
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0		//改为0
#gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
//将上方一行删除或者注释掉
......

清除原有的缓存，建立新的缓存

[root@localhost ~]# yum clean all
[root@localhost ~]# yum makecache

到官网去下载MySQL所需的依赖包repo.mysql.com

所需的软件包：

• mysql-community-server
• mysql-community-client
• mysql-community-common
• mysql-community-devel
• mysql-community-libs

//下载并安装mysql5.7所需的包
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.35-1.el7.x86_64.rpm \
 http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.35-1.el7.x86_64.rpm \
 http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.35-1.el7.x86_64.rpm \
 http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.35-1.el7.x86_64.rpm \
 http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.35-1.el7.x86_64.rpm
[root@localhost ~]# dnf -y install *.rpm
[root@localhost ~]# rpm -qa | grep mysql
mysql-community-libs-5.7.37-1.el7.x86_64
mysql-community-server-5.7.37-1.el7.x86_64
mysql-community-common-5.7.37-1.el7.x86_64
mysql-community-client-5.7.37-1.el7.x86_64
mysql-community-devel-5.7.37-1.el7.x86_64
mysql57-community-release-el7-10.noarch

3.2 mysql配置

[root@localhost ~]# systemctl disable --now firewalld	//开启服务前关闭防火墙
[root@localhost ~]# getenforce					//关闭selinux
Disabled
[root@localhost ~]# systemctl start mysqld		//启动MySQL服务并设置开机自启
[root@localhost ~]# systemctl enable mysqld

# /var/log/mysqld.log文件内存放着临时密码
[root@localhost ~]# grep 'password' /var/log/mysqld.log 	//查看MySQL密码
2022-04-18T08:10:21.800583Z 1 [Note] A temporary password is generated for root@localhost: pMbdM5xr/?W2
[root@localhost ~]# mysql -uroot -p'pMbdM5xr/?W2' -h127.0.0.1	//登陆

# 修改密钥策略为只需满足长度要求
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

# 设置长度最低为4，此处设置小于4也会按4处理
mysql> set global validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)

# 修改密码
mysql> set password = password('1234');	
Query OK, 0 rows affected, 1 warning (0.00 sec)

4. mysql数据库的操作

mysql数据库命令不分大小写

4.1 DDL操作

mysql> CREATE DATABASE IF NOT EXISTS zyq;	//创建数据库
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;			//查看当前所有的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zyq                |
+--------------------+
5 rows in set (0.00 sec)

mysql> USE zyq;					//进入数据库
Database changed

//创建表
mysql> create table test (id INT NOT NULL,name VARCHAR (100) NOT NULL,age TINYINT);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW TABLES;				//查看当前库所有的表
+---------------+
| Tables_in_zyq |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

mysql> SHOW CHARACTER SET;		//查看支持的所有字符集

mysql> SHOW ENGINES;			//查看当前数据库支持的所有存储引擎

mysql> SHOW TABLES FROM zyq;	//不进入数据库而列出其包含的所有表
+---------------+
| Tables_in_zyq |
+---------------+
| test          |
+---------------+
1 row in set (0.00 sec)

mysql> DESC zyq.test;				//不进入数据库而列出表的结构

mysql> SHOW CREATE TABLE zyq.test;		//查看表的创建命令

mysql> SHOW TABLE STATUS LIKE 'test'\G		//查看表的状态，需要进入库

mysql> HELP CREATE TABLE;				//查看创建表的帮助
Name: 'CREATE TABLE'
Description:
Syntax:
......

mysql> DROP TABLE test;					//删除表
Query OK, 0 rows affected (0.01 sec)

mysql> DROP DATABASE IF EXISTS zyq;		//删除数据库
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;

mysql> ALTER TABLE t1 RENAME t2;		//修改t1表名为t2
Query OK, 0 rows affected (0.01 sec)

4.2 DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作

ORDER BY：排序，默认为升序（ASC）

ORDER 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进行升序排序 并且略过第1个结果取后面的2个结果

truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间
truncate	删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表

insert语句：

mysql> USE zyq;
Database changed

# 创建表，id字段为主键不能为空且自动增值
mysql> create table test (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,name VARCHAR (100) NOT NULL,age TINYINT);
Query OK, 0 rows affected (0.01 sec)

# 增加数据
mysql> INSERT INTO test(id,name,age) VALUE(1,'tom',32);	
Query OK, 1 row affected (0.00 sec)

select语句：

mysql> SELECT * FROM test;			//查询表内容
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   32 |
+----+------+------+
1 row in set (0.00 sec)

mysql> SELECT name FROM test;		//按字段查询表内容
+------+
| name |
+------+
| tom  |
+------+
1 row in set (0.00 sec)

......

mysql> SELECT * FROM test ORDER BY age;		//按年龄升序显示
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | zhangsan |   20 |
|  7 | zhouba   |   20 |
|  3 | lisi     |   23 |
|  4 | wangwu   |   25 |
|  6 | sunqi    |   26 |
|  5 | zhaoliu  |   28 |
|  1 | tom      |   32 |
+----+----------+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM test ORDER BY age DESC;	//按年龄降序显示
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   32 |
|  5 | zhaoliu  |   28 |
|  6 | sunqi    |   26 |
|  4 | wangwu   |   25 |
|  3 | lisi     |   23 |
|  2 | zhangsan |   20 |
|  7 | zhouba   |   20 |
+----+----------+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM test ORDER BY age limit 2;	//升序排序并只显示前两个结果
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | zhouba   |   20 |
|  2 | zhangsan |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE age >= 25 AND name = 'tom';	//条件查询
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   32 |
+----+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE age BETWEEN 23 AND 28;	//查询年龄23~28的结果
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  3 | lisi    |   23 |
|  4 | wangwu  |   25 |
|  5 | zhaoliu |   28 |
|  6 | sunqi   |   26 |
+----+---------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE age IS NOT NULL;	//查询年龄不包含空值的结果
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | tom      |   32 |
|  2 | zhangsan |   20 |
|  3 | lisi     |   23 |
|  4 | wangwu   |   25 |
|  5 | zhaoliu  |   28 |
|  6 | sunqi    |   26 |
|  7 | zhouba   |   20 |
+----+----------+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE age IS NULL;	//查询年龄为空的结果
Empty set (0.00 sec)

update语句：

mysql> UPDATE test SET age = 27 WHERE name = 'zhouba';	//修改
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

delete语句：

mysql> DELETE FROM test WHERE id = 1;		//删除某条记录
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM test;					//删除整个表的内容
Query OK, 6 rows affected (0.00 sec)

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> DESC test;			//表内容已被删，但表结构仍存在
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

truncate语句：

mysql> TRUNCATE test; 		//删除表内容
Query OK, 0 rows affected (0.01 sec)

4.3 DCL操作

权限类型(priv_type)

权限类型	代表什么？
ALL	所有权限
SELECT	读取内容的权限
INSERT	插入内容的权限
UPDATE	更新内容的权限
DELETE	删除内容的权限

指定要操作的对象db_name.table_name

表示方式	意义
* . *	所有库的所有表
db_name	指定库的所有表
db_name.table_name	指定库的指定表

WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，也就是将自己的权限完全复制给另一个用户。不建议使用。

# 授权zyq用户在数据库本机上登录访问所有数据库
mysql> GRANT ALL ON *.* TO 'zyq'@'localhost' IDENTIFIED BY 'password';

mysql> GRANT ALL ON *.* TO 'zyq'@'127.0.0.1' IDENTIFIED BY 'password';

# 授权用户在192.168.10.102上远程登录访问zyq数据库
mysql> GRANT ALL ON zyq.* TO 'zyq'@'192.168.10.102' IDENTIFIED BY 'password';

# 授权zyq用户在所有位置上远程登录访问所有数据库
mysql> GRANT ALL ON *.* TO 'zyq'@'%' IDENTIFIED BY 'password';

# 查看当前登录用户的授权信息
mysql> SHOW GRANTS;

# 查看指定用户zyq的授权信息
mysql> SHOW GRANTS FOR zyq;
mysql> SHOW GRANTS FOR 'zyq'@'localhost';
mysql> SHOW GRANTS FOR 'zyq'@'127.0.0.1';

# 取消权限
mysql> REVOKE ALL ON *.* FROM 'zyq'@'192.168.10.102';

mysql> FLUSH PRIVILEGES;	//重读授权表