【数据库】基本知识与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 条件
左连查询、右连查询的结果去重后进行合并：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 数据类型...);
6）查看所有表：show tables;
7）显示创建表：show create table 表名;
8）显示表的结构：describe(或desc) 表名;
