【数据库】基本知识与MySQL常用操作

来源：http://testingpai.com/article/1637396942163

待整理：https://blog.csdn.net/zj20165149/article/details/104092403

一、基本知识

1、数据（Data）：承载各类软件业务中重要的载体称为数据，数据的类型包括：数值、文本、图片和音频、视频等。相当于存放在表中的数据

2、数据库（DB，DataBase）：存放上述各类数据的场所或仓库，提供持久化，不易丢失，且有合理的组织形式，可管理，共享等。相当于表

3、数据库管理系统（DBMS，Database Management System）：对数据进行管理，提供对数据库的存储管理、安全控制、备份、审计等处理。相当于mysql

4、关系型数据库：传统的数据落地存储模式建立在严格的数据概念模式基础上，数据间有规范化的关系，支持ACID特性，常见的产品有MySQL、Oracle等

5、非关系型数据库：高扩展性和高伸缩性的需求增加，产生了各种的非关系数据库，特点是：非关系型、分布式、扩展方便，但不能满足ACID特性。常见的非关系数据库有：

1）键值型：Memcached、Redis，多用于缓存

2）列分组型：Hbase等，多用于日志和一些博客

3）文档型：MongoDB等，表结构可动态变化，多用于不同结构的日志处理

4）图数据库：Neo4j等，一般做推荐引擎或关系图谱

二、基本概念

1、数据表：也称二维表，基础的数据存储单位，由列（字段）和行（记录）组成

2、字段：规范数据表结构的最小单元，尽量做到不可再分。如下浅蓝色底部分

3、数据类型：描述字段的类型，可以有整型，字符串等

4、主键：唯一的标识一条记录的字段

5、外键：引用其他表数据的字段。如下成绩表中，灰色底部分，学号和课程ID

6、视图：组合一个或多个表输出数据子集，具有隐藏数据复杂性，查询简单等特性

7、函数：类似于程序语言的方法，一般做简单的按规则数据替换或转换

8、存储过程：预编译，可有输入/输出参数，可包含程序流、逻辑处理、事务等操作，用于处理比较复杂的操作，如：数据加工

9、数据完整性：关联表通过外键能找到主表数据，否则为数据不完整

10、数据冗余：重复存储了某些内容，占用存储空间，会带来一定风险

三、范式

1、范式（NF，Noraml Form）,是关系数据库的理论基础。主要用于数据库结构的设计提供规则和指导，使得设计出的数据具有最好的存储性能、更容易被理解、数据完整性更佳。

2、范式一共有6种，一般设计种满足，1NF，2NF，3NF即可。常见的不满足3NF带来的问题有：数据冗余、插入异常、更新异常、删除异常

3、第一范式（1NF）

1）规则：要求数据表中所有的字段都是原子性不可分割的，一般的设计都能满足1NF

2）不满足带来的问题：后续业务处理不方便

4、第二范式（2NF）

1）规则：在1NF的基础上，所有非主键列都完全依赖于主键，而不是部分，能有效减少冗余、保证数据完整性。如下：组合主键学号+课程ID，右图满足2NF

2）不满足带来的问题：数据冗余、插入异常、更新异常、删除异常。如：一个学生有多门课程，就冗余多个姓名；新设立的课程因无学生考试需将学生信息置为空；删除某个学员导致课程被删除

5、第三范式（3NF）

1）在2NF基础上，所有非主键列都直接依赖于主键，不能传递依赖，也能有效减少冗余，保证数据完整性

2、不满足带来的问题：数据冗余、插入异常、删除异常等

三、数据库设计步骤

1、需求分析：识别软件需求中的数据的种类、范围、数量、相互间的交流情况和约束条件等。

2、概念模型设计：当软件系统的用户需求确定后，根据用户需求抽象出常见的对象，以及对象间的关系，称做：E-R（Entiy-Relationship）图。常用实体-关系图表示概念模型。如：一个教学信息系统中，会抽象出老师、班级、课程、学生等实体，并涉及到老师教授课程、老师带领班级、学生所在班级等关系

3、逻辑模型设计：将概念模型细化，细化出概念模型中实体的属性，实体间关系通过哪些属性体现。如：教学信息系统中，老师，包括：ID、姓名、性别等属性特征，通过在课程表上关联老师ID建立起关系

4、物理模型设计：将上述模型的实体、属性、关系，根据实际的数据库产品，落地对应的物理表，并构建起表的字段、主键、外键、约束、默认值、是否可为空、视图等

5、验证设计：运行基于上述构建数据库的业务系统，来验证设计的数据CRUD的正确性、合理性

6、运行和维护：跟随业务需求的不断迭代，数据库需要一直优化和重新设计，保证数据正确性、合理性，同时考虑新旧业务的兼容与扩展

四、实例

基于角色的访问控制（RBAC）

1、需求：针对不同的用户进行功能授权。由于存在不同的用户，不同的部门，岗位等，某一用户有部分功能授权，则是需要“角色”的存在

2、E-R图

3、物理模型

1）一般的设计过程中，针对1对多，会拆成主-外键关系；针对多对多，会添加一个中间表

2）小型数据库设计，可直接使用Excel

3）中大型项目数据库设计，可使用PowerDesigner、PDMan等工具

五、MySQL数据库的常用操作

1、增 --insert into

insert into 表名 (字段) values (数据)（数据）

insert into 表名 values (数据) （数据）

insert into 表名 (列名，列名....) select (列名，列名...) from 表

如：INSERT INTO `user`(username) VALUES (2);

INSERT INTO `user`(username,password,nick_name,age,sex,address) VALUES ('tst6','123456','test12','18','男','address'),('tst7','123456','test12','18','男','address');

2、改 --update

update 表名 set 字段 = value where 条件

UPDATE `user` SET username= 'update' WHERE id='102';

3、删 --delete、drop

drop database 数据库名 --删除数据库

drop table 表名 --删除表

delete from 表名 --删除表中的数据

delete from 表名 where 条件1 and(or) 条件2....; --按条件删除表中的数据

DELETE FROM `user` WHERE id='102';

4、查 --select

1）单表查询

select * from 表名 where 条件 --查询所有字段列信息

select 字段名 from 表名 where 条件 --查询指定字段列的信息

select 字段名 from 表名 limit 条件 --查询条件信息

2）多表查询

参考链接：https://www.cnblogs.com/bypp/p/8618382.html

关联查询：select 字段名1，....from 表名1，表名2，.....where 关联条件表达式 and 过滤条件表达式...；

SELECT * FROM lin_user_group,lin_group_permission WHERE lin_user_group.user_id = lin_group_permission.group_id;

select * from user_actor,actor_lover where user_actor.id = actor_lover.u_id;

等值查询：select 字段名1，... from 表名1 inner join 表名2 on 关联条件表达式 and 过滤条件表达式...；

select * from user_actor inner join actor_lover on user_actor.id = actor_lover.u_id;

左连接查询(读取左表全部数据，即使右边没有关联数据)：select 字段名1，...from 表名1 left join 表名2 on 关联条件表达式 and 过滤表达式...;

select * from user_actor left join actor_lover on user_actor.id = actor_lover.u_id;

右连接查询(以右表为基础，读取右表全部数据，即使左边没有关联数据)：select 字段名1，...from 表名1 right join 表名2 on 关联表达式 and 过滤表达式...;

select * from user_actor right join actor_lover on user_actor.id = actor_lover.u_id;

5、高级查询

查询结果进行排序：order by

select * from table where 条件 order by字段(需要排序的字段) asc\desc

select * from table order by 列1 asc,列2 desc...(根据多列进行排序，若列1数据相同，则根据列2进行排序)

select * from user_actor right join actor_lover on user_actor.id = actor_lover.u_id order by actor_lover.id desc;

条件在数据集\子查询中：in

select * from table where 条件字段 in 数据集\子查询

根据条件模糊查询：like

select * from table where 条件字段 like '%_值'（%--任意字符、_--每个下划线代表一个字符）

根据某字段进行分组查询：group by

select * from table where 条件 group by having

查询条件在某个区间之间：between and

select * from table where 条件字段 between ... and ...

查询结果进行去重：distinct

select distinct (去重字段) from table where 条件

分页：limit

select * from table where 条件 limit m,n

select * from table where 条件 limit n offset m (偏移m个数据，查询n条)

左连查询、右连查询的结果去重后进行合并：union

select * from table1 left join table2 on 关联条件 union(union all 为不去重将结果全部合并)

6、数据库查询常用函数

1）数值相关函数

max() -- 最大值
min() -- 最小值
avg() -- 平均值
sum() -- 求和
count() -- 计数
median() -- 中位数

2）日期相关函数

sysdate() -- 当前日期时间
curdate() -- 当前日期
curtime() -- 当前时间
year() -- 获取日期的年份
month() -- 获取日期的月份
date_add(日期，interval 变更值单位) -- 变更日期

3）字符串相关函数

concat() -- 拼接字符串
length() -- 获取字符串长度
substr() -- 截取字符串

7、SQL执行顺序

select ... from ... where ... group by ... having ... order by ...

FROM left_table --> ON join_condition --> join_type JOIN right_table --> WHERE where_condition --> GROUP BY group_by_list --> HAVING having_condition --> SELECT --> DISTINCT select_list --> ORDER BY order_by_condition --> LIMIT limit_number

8、常用命令

1）Windows环境下，打开cmd窗口，输入 mysql -uroot -p，输入密码后，即可进入mysql

2）查询数据库：show databases;

3）创建数据库：create databse ××(数据库名)

4）显示数据库信息：show create database ××(数据库名)

5）创建表：create table 表名（字段名1 数据类型,字段名2 数据类型,字段名3 数据类型...);