网络安全自学笔记10 - MySQL数据库

一、表设计三范式

1、概念

• 关系型数据库中的关系必须满足一定的要求，既满足不同的范式。范式为设计数据库中表内关系，表与表之间的关系提供了规范和标准，任何按照范式设计的表结构将是最优结构，同时也是可以避免数据冗余，减少数据库的存储空间，减轻维护数据完整性的麻烦。
• 目前关系型数据库有六种范式。满足最低要求的范式是第一范式，在第一范式的基础上进一部满足更多要求的成为第二范式，其余范式以此类推，一般来说数据库满足第三范式就可以了

2、第一范式：确保每列保持原子性

• 第一范式是最基本的范式。如果数据库中的所有字段值都是不可分解的原子值，那就说明该数据满足了第一范式
• 第一范式的合理遵循需要根据系统的实际需求来定，比如某些数据库系统中需要用到“地址”这属性，本来直接将“地址”属性设计成一个数据库字段就行，但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性拆分成省份、城市、详细地址等多个部分进行储存，这样在对地址中某一部分操作的伤害将非常方便，这样设计才算满足数据库的第一范式

3、第二范式：确保每行的唯一性

• 第二范式是在第一范式的基础上建立起来的， 既满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或行必须可以被唯一地区分。既要求每个实体都必须具有主键区分

4、第三范式：确保每列和主键列直接相关，而不是间接

• 第三范式需要确保数据包除外，其他非主键属性不得重复出现在第二张表上
• 比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单建立相关的关系，而不可以在订单表中添加关于客户其他信息的字段。

二、数据库常用操作

1、MySQL数据库的常规操作

• 数据库操作控制是由一个系统软件 —— 数据库管理系统（缩写DBMS，DataBase Management System）。
• 分为四向功能
  • 数据库定义功能：DDL（Data Definition Language），用户通过它可以方便地对数据库中的数据对象进行定义。
  • 数据操纵功能：DML（Data Manipulation Language），供用户实现对数据的基本操作，如插入、删除、修改。
  • 数据查询功能：DQL（Data Query Languasge），供用户对数据的查询操作
  • 数据控制功能：DCL（Data Control Language），主要用于对数据库的用户、角色和权限进行管理和控制

2、MySQL数据库的常用命令

• Linux 安装MySQL数据库

  rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm
  vim /etc/yun.repos.d/mysql-community.repo
  	#修改版本内的 关闭不需要的enabled=0，开启需要的版本的enabled=1
  yum -y install mysql-community-server
  systemctl start mysqld
  grep "password" /var/log/mysqld.log
  

• 命令行链接数据库

  mysql -u root -p
  # 手动输入密码或无密码
  

• 查看目前系统中存在的数据库

  show databases;
  

• 切换到某个数据库

  use world;
  

• 查看当前数据库下面的所有可用的表

  show tables;
  

• 显示mysql数据库中的user表的结构

  desc user;
  

• 退出MySQL命令行

  exit;
  

• 修改root用户的密码

  update user set authentication_string=password('123456') where user='root';
  flush privileges;
  

• 创建一个新用户

  GRANT ALL PRIVILEGES NO *.* TO 'phoon'@'%' IDENTIFIED BY '123456';
  flush privileges;
  

三、DCL（数据控制语言）

1、介绍

• 数据控制语言在SQL语言中，是一种可以对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。由GRANT和REVOKE两个指令组成
• DCL(Data Control Language)是数据库控制语言，是用来设置或更改数据库用户和角色的权限语句，包括 grant，deny，revoke等
• 在默认状态下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人员才有权力执行DCL

2、授权用户权限

• grant 语法 授权用户权限

GRANT <权限类型> [(<列名>)] [,<权限类型> [(<列名>)]] ON <对象> <权限级别> TO <用户>
<用户>格式：<用户名> [IDENTIFIED] BY [PASSWORD]<口令>

• 举例

GRANT * NO *.* TO 'phoon'@'localhost' IDENTIFIED BY '123456';
# *.*表示所有数据库

revoke insert on *.* from 'phoon'@'localhost';
# revoke 取消权限

• 权限类型

  • 授予数据库权限时

    • SELECT：表示授予用户可以使用选择语句访问特定数据库中所有表和视图的权限。

    • INSERT：表示授予用户可以使用INSERT语句向特定数据库中的所有表添加数据行的权限。

    • DELETE：表示授予用户可以使用DELETE语句删除特定数据库中所有表的数据行的权限。

    • UPDATE：表示授予用户可以使用UPDATE语句更新特定数据库中所有数据表的值的权限

    • REFERENCES：表示授予用户可以创建指向特定数据库中的表外键的权限

    • CREATE：表示授权用户可以使用CREATE TABLE语句在特定的数据库中创建新表权限。

    • ALTER：表示授权用户可以使用ALTER TABLE语句修改特定数据库中所有数据表的权限。

    • SHOW VIEW：表示授权用户可以查看特定数据库中已有视图的视图定义的权限

    • CREATE ROUTINE：表示授权用户可以为特定的数据库创建存储过程和存储函数的权限

    • ALTER ROUTINE：表示授权用户可以更新和删除数据库已有的存储过程和存储函数权限

    • INDEX：表示授权用户可以在特定数据库中的所有表上定义和删除索引的权限

    • DROP：表示授权用户可以删除特定数据库中所有表和视图的权限

    • CREATE TEMPORARY TABLES：表示授权用户可以在特定数据库中创建临时表的权限。

    • CRETE VIEW：表示授权用户可以在特定数据库中创建新的视图的权限

    • EXECUTE ROUTING：表示授权用户可以调用特定数据库的存储过程和函数的权限

    • LOCK TABLES：表示授权用户可以锁定特定数据库的已有数据表的权限

    • ALL或ALL PRIVILEGES：表示以上所有权限

  • 授予表权限时

    • SELECT：授予用户可以使用SELECT语句进行访问特定表的权限
    • INSERT：授予用户可以使用INSERT语句向一个特定表中添加数据行的权限
    • DELETE：授予用户可以使用DELETE语句从一个特定表中删除数据行的权限
    • DROP：授予用户可以删除数据表的权限
    • UPDATE：授予用户可以使用UPDATE语句更新特定数据表的权限
    • ALTER：授予用户可以使用ALTER TABLE语句修改数据表的权限
    • REFERENCES：授予用户可以创建一个外键来参照特定数据表的权限
    • CREATET：授予用户可以使用特定的名字创建一个数据表的权限
    • INDEX：授予用户可以在表上定义索引的权限
    • ALL或ALL PRIVILEGES：所有的权限名

四、DDL（数据库模式定义语言）

1、介绍

• 数据库定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的显示世界实体的语言

2、数据库操作

• 创建数据库

  create database school;
  

• 删除数据库

  drop database school;
  

3、数据表操作

• 创建数据表

  reate table <表名>
  (id int(11) primary key, # 字段名 数据类型 主键约束
  name varchar(25),
  deptOP int(11),
  salary float
  # primary key(id) 另一种写法
  );
  
  

4、修改数据表

• 修改数据表名

  alter table <旧表名> rename [to]<新表明>;
  alter table stu1 rename stu2;
  

• 修改字段的数据类型

  alter table <表名> modify <字段名><数据类型>;
  alter table stu1 modify name varchar(30);
  

• 修改字段名

  alter table <表名> change <旧字新段名> <数据类型>
  alter table stu1 change name firstname varchar(30);
  

• 添加字段

  alter table stu1 add managerID int(10);
  alter table stu1 add managerID int(10) not null;  #添加有完整性约束的字段
  alter table stu1 add managerID int(10) first; # 在表的第一列添加字段
  alter table stu1 add managerID int(10) after name; #在表的指定列后面加入字段
  

• 删除字段

  alter table <表名> drop <字段名>
  alter table stu1 drop name;
  

• 删除数据库

  drop table [if exists] stu1,stu2l;
  # if exists 判断是否存在
  

五、DML（数据库操纵语言）

1、介绍

• 数据操作语言是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句，通常是数据库专用编程语言中的一个子集
• 例如在信息软件产业标准得到SQL语言中，以INSER、UPDATE、DELETE三种指令为核心，分别代表插入、更新、删除

2、insert 插入数据

• 语法

  insert into <表明> (列1,列2,...) values(值1,值2,...));
  

• 举例

  inser into stu1 (id,name,age,info) values (1,'phoon',21,'lawyer');
  inser into stu1 (id,name,age,info) values (1,'phoon',21,'lawyer'),(2,'zhangsan',21,'cook');
  

3、update 更新数据

• 语法

  update <表明> set 列1 = 值1, 列2 = 值2, ...where(条件)
  

• 举例

  # 更新id为11的记录
  update stu1 set age=15,name='liming' where id=11;
  # 更新age值为19~22的记录
  update stu1 set info='student' where age between 19 and 22;
  

delete 删除数据

• 语法

  delete from <表明> [where <条件>];
  

• 举例

# 删除id值为11的记录
delete from stu1 where id=11;
# 删除age值为19~22的记录
delete from stu1 where age between 19 and 22;
# 删除表中所有数据
delete from stu1;

六、DQL （数据查询语言）

1、介绍

• 数据库管理系统的一个重要功能就是数据查询，数据查询不只是简单返回数据库中存储的数据，还可以根据需要对数据进行筛选，以及确定以什么样的个数显示。

2、select 查询语句

• 语法

  # 查询表中所有字段
  select * from stu1;
  

• 查询条件

# where 查询stu1表中country字段等于CN的数据
select * FROM stu1 where country='CN';
# ORDER BY DESC 查询stu1表并按alexa字段排序
select * from stu1 ORDER BY alexa DESC;
# SELECT DISTINCT 去掉重复值
SELECT DISTINCT country FROM Websites;