第八章 MySQL数据库系统（一）_ylw开源软件-CSDN博客

本文链接：https://blog.csdn.net/ylwh153899/article/details/131387079

MySQL数据库系统：

数据库基础

MySQL的编译安装

MySQL数据库概述

MySQL是开源的关系型数据库服务器软件：
目前由Oracle公司开发和维护
官方站点：http://ww.mysql.com
MySQL的特点：
多线程、多用户
基于C/S（客户端/服务器）架构
简单易用、查询速度快
安全可靠

访问MySQL数据库系统

启动mysqld服务

启动mysqld服务，并查看运行状态

启动mysqld服务：

[root@www mysql-5.1.55]# systemctl  start mariadb  Starting   MySQL.                                            [确定]

查看服务运行状态：

[root@www mysql-5.1.55]# / systemctl status mariadb
 MySQL running (26882)                                      [确定]

查看服务端口，默认为3306

[root@www mysql-5.1.55]# netstat -anpt | grep mysqld
tcp       0    0 0.0.0.0:3306       0.0.0.0:*       LISTEN      26882/mysqld

登录及退出MySQL环境

连接MySQL服务器：
[root@www ~]# mysql -u root -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
……
and you are welcome to modify and redistribute it under the GPL v2 license
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     19075 |
| mysql-bin.000002 |    731182 |
| mysql-bin.000003 |       106 |
+------------------+-----------+
3 rows in set (0.00 sec)

退出MySQL操作环境：
mysql>quit;     
Bye
[root@www ~]#

使用mysql数据库

查看数据库结构
查看数据库列表信息

mysql> USE mysql;
Database changed
mysql> DESCRIBE user;
+-----------------------+------------------------+------+-----+---------+-------+
| Field                 | Type                   | Null | Key | Default | Extra |
+-----------------------+------------------------+------+-----+---------+-------+
| Host                  | char(60)               | NO   | PRI |         |       |
| User                  | char(16)               | NO   | PRI |         |       |
| Password              | char(41)               | NO   |     |         |       |
| Select_priv           | enum('N','Y')          | NO   |     | N       |       |
……
+-----------------------+------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)

数据库分类2019-12-6-1
      数据库通常分为层次式数据库、网络式数据库和关系式数据库三种。
      而不同的数据库是按不同的数据结构来联系和组织的。
      而在当今的互联网中，最常见的数据库模型主要是两种，即关系型数据库和非关系型数据库。

关系型数据库
当前在成熟应用且服务与各种系统的主力数据库还是关系型数据库。
代表：Oracle、SQL Server、MySQL

非关系型数据库
随着时代的进步与发展的需要，非关系型数据库应运而生。
代表：Redis、Mongodb NoSQL数据库在存储速度与灵活性方面有优势，也常用于缓存。

数据库规范化
经过一系列的需求分析，我们可以将客户的需求转换为数据表并确立这些表之间的关系，那么是否我们现在就可以在开发中使用呢？
答案否定的，为什么呢！同一个项目，很多人参与了需求的分析，数据库的设计，不同的人具有不同的想法，不同的部门具有不同的业务需求，我们以此设计的数据库将不可避免的包含大量相同的数据，在结构上也有可能产生冲突，在开发中造成不便。

什么是范式
要设计规范化的数据库，就要求我们根据数据库设计范式――也就是数据库设计的规范原则来做。范式可以指导我们更好地设计数据库的表结构，减少冗余的数据，借此可以提高数据库的存储效率，数据完整性和可扩展性。
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式 1NF-6NF,一般说来，数据库只需满足第三范式(3NF）就行了。

三大范式

第一范式（1NF）
所谓第一范式（1NF）是指在关系模型中，对列添加的一个规范要求，所有的列都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

		员工参与工程工程表
项目号	工程名称	工程地址	员工编号	姓名	月工资	职务
P001	港澳珠大桥	广东珠海	E0001	张三	3000	工人
P001	港澳珠大桥	广东珠海	E0002	李四	3000	工人
P001	港澳珠大桥	广东珠海	E0003	王五	6000	高级技工
P002	南海航天	海南三亚	E0001	张三	3000	工人

第二范式（2NF）
在1NF的基础上，非Key属性必须完全依赖于主键。例如:上表中，一个表描述了工程信息，员工信息等。这样就造成了大量数据的重复。按照第二范式，我们可以将上表拆分成“工程信息表”和“员工信息表” 。
这两个表中，项目号和员工编号就是各自表格的主键。

	员工信息表
员工编号	姓名	月工资	职务
E0001	张三	3000	工人
E0002	李四	3000	工人
E0003	王五	6000	高级技工

第三范式（3NF）
           第三范式是在第二范式基础上，更进一层，第三范式的目标就是确保表中各列与主键列直接相关，而不是间接相关。
            在员工信息表中包含：”员工编号”、”员工名称”、”职务”、”月工资”，而我们知道，月工资是由职务决定，这里”月工资”通过”职务”与员工相关，而不是与主键”员工编号”直接相左关，这不符合第三范式。
            我们需要将员工信息表进一步拆分，如下:
员工信息表：员工编号，姓名，职务
职务表：职务编号，职务，月工资

关系型数据库的典型概念
    数据库 databse：数据的仓库,包含一系列相关表。
    表 table：数据是保存在表内，保存在一个表内的      数据，应该具有相同的数据格式。
    行：行用于记录数据
    记录：行内的数据.
    列：列用于规定数据格式

关系型数据库的典型概念
    字段：数据的某个列
    SQL：用来管理数据的语言。结构化查询语言（SQL，Structured Query Language）
    主键：唯一地标识表中的某一条记录，不能空，不能重复

创建新的数据库
CREATE DATABASE 数据库名

创建auth库：
mysql> CREATE DATABASE auth;
Query OK, 1 row affected (0.01 sec)
mysql> USE auth;

创建新的数据表
CREATE TABLE 表名 (字段定义……)

创建users表：
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)

删除指定的数据表
DROP TABLE [数据库名.]表名

删除users表：
mysql> DROP TABLE auth.users;
Query OK, 0 rows affected (0.00 sec)

删除指定的数据库
DROP DATABASE 数据库名

删除auth库：
mysql> DROP DATABASE auth;
Query OK, 0 rows affected (0.01 sec)

插入、查询数据记录

向数据表中插入新的数据记录(请重新建库和表）
INSERT INTO 表名(字段1, 字段2, ……) VALUES(字段1的值, 字段2的值, ……)

插入一条记录：
mysql> use auth;
Database changed
mysql> INSERT INTO users(user_name,user_passwd) VALUES('zhangsan', PASSWORD('123456'));

插入完整记录时可以省略指定字段的部分：
mysql> INSERT INTO users VALUES('lisi', PASSWORD('654321'));
Query OK, 1 row affected (0.00 sec)

从数据表中查找符合条件的数据记录
SELECT 字段名1,字段名2 …… FROM 表名 WHERE 条件表达式

查询auth.users表记录：
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| lisi      | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

查询用户名为zhangsan的记录：
mysql> SELECT user_name,user_passwd FROM auth.users where user_name= 'zhangsan';
+-----------+-------------------------------------------+
| user_name | user_passwd 
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)

修改数据记录

修改、更新数据表中的数据记录 2019-11-29-2
UPDATE 表名 SET 字段名1=值1[,字段名2=值2] WHERE 条件表达式

更改lisi的密码为空：
mysql> UPDATE auth.users SET user_passwd=PASSWORD('') WHERE user_name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| lisi      |                                           |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)

更改root密码为123456：
mysql> UPDATE mysql.user SET password=PASSWORD('123456') WHERE user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

刷新用户授权信息：
mysql> FLUSH PRIVILEGES;

删除数据记录

在数据表中删除指定的数据记录
DELETE FROM 表名 WHERE 条件表达式

用户和密码均为空：
mysql> SELECT user,host,password FROM mysql.user WHERE user='';
+------+-----------+----------+
| user | host      | password |
+------+-----------+----------+
|      | localhost |          |
|      | www       |          |
+------+-----------+----------+
2 rows in set (0.00 sec)

删除空用户：
mysql> DELETE FROM mysql.user WHERE user='';
Query OK, 2 rows affected (0.01 sec)

维护数据库及用户权限

设置用户权限（用户不存在时，则新建用户）
GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY ‘密码’ ]

验证非授权的访问操作：
mysql> GRANT select ON auth.* TO 'xiaoqi'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
[root@www ~]# mysql -u xiaoqi -p
……
mysql> SELECT * FROM auth.users;                                  
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| zhangsan  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.user;                                  
ERROR 1142 (42000): SELECT command denied to user 'xiaoqi'@'localhost' for table 'user'

查看用户的权限
SHOW GRANTS FOR 用户名@域名或IP

查看xiaoqi从主机127.0.0.1访问时的权限：
mysql>  show grants for "xiaoqi"@"localhost"; (请用root重新登录再运行）
+------------------------------------------------------------------------------------+
| Grants for dbuser@192.168.4.19                                                     |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'dbuser'@'192.168.4.19' IDENTIFIED BY PASSWORD
 '*760F60073FD235571A5260444301DB22136ED604'                                       |
| GRANT ALL PRIVILEGES ON `bdqn`.* TO 'dbuser'@'192.168.4.19'                        |
+------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

撤销用户的权限
REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@域名或IP

确认已撤销对auth库的权限：
mysql> REVOKE all ON auth.* FROM 'xiaoqi'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR 'xiaoqi'@'localhost';               
+------------------------------------------------------------------------------------+
| Grants for xiaoqi@localhost                                                        |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoqi'@'localhost' IDENTIFIED BY PASSWORD
 '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'                                       |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

数据库的备份与恢复2-1

数据库的备份与恢复
方法1：可直接备份目录 /var/lib/mysql/
方法2：使用专用备份工具 mysqldump
备份操作(请cd /home/vina再执行以下命令）
mysqldump -u 用户名 -p [密码] [选项] [数据库名] [表名] > /备份路径/备份文件名
常见选项：--all-databases、--opt

备份mysql库的user表：
[root@www ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password:

备份mysql库：
[root@www ~]# mysqldump -u root -p --database auth > auth.sql
Enter password:

备份所有数据库：
[root@www ~]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password:

数据库的备份与恢复2-2

恢复操作
mysql -u root -p [数据库名] < /备份路径/备份文件名

[root@www ~]# mysql -u root -p test < /home/vina/mysql-user.sql
Enter password:
[root@www ~]# mysql -u root -p
Enter password:
……
mysql> USE test
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.01 sec)