【数据库】基本知识与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;
![](https://img2022.cnblogs.com/blog/1365410/202205/1365410-20220504150746467-1618391289.png)
左连接查询(读取左表全部数据,即使右边没有关联数据):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) 表名;