数据库：基础知识总结

目录

一、数据库基础知识

数据库设计三大范式：

数据仓库数据模型：

数据库分类：

SQL语言分类：

二、DDL

操作“数据库”的DDL

操作表的DDL

修改表的DDL

三、DML（增删改）

四、DQL

DQL 查询，单表单查询

DQL 查询数

DQL数据查询语言（1）

DQL数据查询语言（2）

DQL多表查询

select语句总结

五、TCL

1、概述

二、事物特性：

三、TCL语句：

四、事物的并发异常：

五、隔离级别：

六、DCL

---

---

一、数据库基础知识

数据库（DataBase）：磁盘上用来存储和管理数据的文件，按照数据结构来组织、存储和管理数据的仓库；是可组织的、可共享的、统一管理的大量数据的集合。（以文件的方式存储）

数据库设计三大范式：

1.第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，

2.第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，
而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
3.第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

数据仓库数据模型：

星型模型

在数据仓库建模中，星型模型维度建模中的一种选择。星型模型以一个事实表和一组维度表组合而成，并且以事实表为中心，所有的维度表直接与事实表相连。

所有的维度表都直接连接到事实表上，维度表的主键放置在事实表中，作为事实表与维度表连接的外键，因此，维度表和事实表有关联的，然而，维度表与维度表并没有直接相连，因此，维度表之间并没有关联的。

雪花型模型

雪花模型也维度建模中的另一种选择，它对星型模型的扩展；

雪花模型的维度表可以拥有其他的维度表，并且维度表与维度表之间相互关联的。因此，雪花模型相比星型模型更规范一些。但，由于雪花模型需要关联多层的维度表，因此，性能也比星型模型要低，所以一般不很常用

数据库分类：

1、关系型数据库（二维表的方式存储）：Oracle、DB2 、MySQL 、PostgreSQL、SQL Server等

2、非关系数据库：Redis 、MongoDB、HBASE、Graph

SQL语言分类：

1、数据定义语言（DDL）：Data Definition Language，用来定义数据库的对象，如数据表、视图、索引等。

2、数据操纵语言（DML）：Data Manipulation Language，用来在数据库表中更新，增加和删除记录。

3、事务控制语言（TCL）：Transaction Control Language，用来做数据库中的事务管理。

4、数据查询语言（DQL）：Data Query Language，用来查询数据库中的数据。

5、数据控制语言（DCL）：Data Control Language，用来控制数据库的用户权限。

二、DDL

操作“数据库”的DDL

1、指定字符集和排序规则方式创建数据库

create database dsj character set UTF8 collate utf8_bin

2、查看数据库

show databases;    show create database 数据库；

3、修改数据库：只能修改字符集和排序规则，不能修改数据库

alter database 数据库名 character set UTF8 collate utf8_bin

4、删除数据库

drop database 数据库名

5、切换数据库

use 数据库;     select database() 查看正在使用的数据库

操作表的DDL

create table student(
    id int,
    name varchar(200),
    salary double(6,2),
    birthday date
)
1、查看表结构：

desc 表名  或 describe 表名

2、查看创建表的SQL语句：

show create table 表名

3、复制表结构：

create table 新表名 like 旧表名

修改表的DDL

1、添加新列：
alter table 表名 add 列名 类型名
2、修改列类型
alter table 表名 modify 列名 类型名
3、修改类名
alter table 表名 change 旧列名 新列明 类型名
4、删除列
alter table 表名 to 列名字
5、修改表名
rename table 原列 to 新列；
6、修改字符集：
alter table 表名 character set 字符集
删除表:drop table student

三、DML（增删改）

增：

指定字段输入：insert into 表名(字段1,字段2...字段n) values(值1,值2...值n);
不指定字段输入：insert into 表名 values(值1,值2...值n);
蠕虫复制：insert into 表名1 select * from 表2 

改：

不带条件的更新：update 表名 set 字段1=值1[,字段2=值2....字段n=值n]
带条件的更新：update 表名 set 字段1=值1[,字段2=值2....字段n=值n]  where 条件;

删：

带条件的删除：delete from 表名 where 条件;

不带条件的删除：delete from 表名

四、DQL

DQL 查询，单表单查询

1、查询表中全部数据： select * from 表名
2、查询指定列名：select 字段1,字段2 from 表名
3、别名查询：select字段1 as 别名,字段2 as 别名 from 表名 as 表别名
     （as可省略）
4、去除重复值：select distinct 字段1,字段2 from 表名
       （distinct关键字用来去除重复值）
5、查询结果参与计算： select 字段 + 固定值 from 表名
            select 字段1+字段2 from 表名

DQL 查询数

1、比较运算符
在查询条件中，可以使用多种比较运算符来表示查询条件。
=等于、   >大于、   <小于、  <=小于等于、   >=大于等于、  <>或!=不等于
2、逻辑运算符
and(&&) 多个条件同时满足 
or(||) 多个条件其中一个满足 
not(!) 不满足
3、范围
BETWEEN 值1 AND 值2，表示从值1到值2范围，包括开头值又包括结尾值
比如：age BETWEEN 80 AND 100 相当于： age>=80 && age<=100 
4、模糊查询
SELECT * FROM 表名 WHERE 字段名 like '通配符字符串';
满足通配符字符串规则的数据就会显示出来。
MySQL通配符有两个：
%: 表示0个或多个字符(任意个字符)
_: 表示一个字符
5、为空查询
为空查询 is null
查询某个字段为空（null）的数据，不是使用=null（null和任何值都不相等），而是使用is null来进行判断

DQL数据查询语言（1）

排序：

语法：celect 字段名 from 表名 [where 条件] order by 字段名 [asc |Desc] desc：降序
组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推
语法： celect 字段名 from 表名 [where 条件] order by 字段名1 [asc |Desc]，字段名2 [asc |Desc]；

1、单行函数

1、abs(X) :返回 x 的绝对值
2、ceil(x)：返回大于或等于 x 的最小整数（向上取整）
3、floor(x)：返回小于或等于 x 的最大整数（向下取整）
4、rand()：返回 0 到 1 的随机数
5、round(x)：返回离 x 最近的整数(四舍五入取整)

2、字符串函数

(1) concat(s1,s2...sn)：字符串 s1,s2 等多个字符串合并为一个字符串
(2) locate(s1,s)：从字符串 s 中获取 s1 的开始位置
(3) lower (s)：将字符串 s 的所有字母变成小写字母
(4) upper(s)：将字符串转换为大写
(5) replace(s,s1,s2)：将字符串 s2 替代字符串 s 中的字符串 s1
(6) substr(s, start, length)：从字符串 s 的 start 位置截取长度为 length 的子字符串
(7) trim(s)：去掉字符串 s 开始和结尾处的空格
(8) reverse(s)：反转字符串

3、时间日期函数

(1) now()：返回系统当前时间(包括年月日时分秒)
(2) sysdate()：返回系统当前时间(包括年月日时分秒)
(3) curdate()：返回当前日期(年月日)
(4) curtime()：返回当前时间(时分秒)
(5) month(date)：返回参数日期的月份值
(6) week(date)：返回参数日期是一年中的第几个星期
(7) day(date)：返回参数日期的日值
(8) date_add(date,INTERVAL expr type)
在参数日期上增加相应的时间
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

4、流程控制函数

(1) if(condition,expr1,expr2)：conditon条件为true时返回expr1。condition条件为false时，返回expr2。
(2) ifnull(expr1,expr2)：expr1不为null时返回expr1。expr1为null时返回expr2。

5、其他函数

(1) version()：查看数据库版本
(2) user()：查看数据库当前用户
(3) md5(expr)：返回参数md5加密后的值

DQL数据查询语言（2）

1、聚合函数

查询是纵向查询，它是对一列的值进行计算，然后返回一个结果值。
聚合函数会忽略空值。
五个聚合函数：
count： 统计指定列记录数，记录为NULL的不统计 
sum： 计算指定列的数值和，如果不是数值类型，那么计算结果为0
max： 计算指定列的最大值 
min： 计算指定列的最小值 
avg： 计算指定列的平均值，如果不是数值类型，那么计算结果为0

2、分组

select 字段1，字段2...from 表名 group by 分组字段 [having字段]
将分组字段结果中相同内容作为一组 
分组的目的就是为了统计，一般分组会跟聚合函数一起使用。
一个SQL语句中不应该出现两个where子句！
使用having子句解决
where和having的对比
1.having是在分组后对数据进行过滤，where是在分组前对数据进行过滤
2.having语句中可以使用聚合函数，where后面不可以使用聚合函数

3、limit：是限制的意思，所以LIMIT的作用就是限制查询记录的条数

select *|字段列表 [as 别名] from 表名 [where 子句] [group by 子句] [having 子句][order by 子句][limit 子句] 
注意：limit子句要放在SQL语句的最后
limit语法格式
limit offset,length;或者limit length;
offset是指偏移量，可以认为是跳过的记录数量，默认为0，表示从哪开始
length是指需要显示的总记录数，表示显示几条

DQL多表查询

1、多表内外连接

隐式内连接：
select 字段名 from  表1，表2 where 条件

显式内连接：
select * from 表1inner  join 表2 on 条件

左外连接：左表数据全部显示，右表按照连接条件显示有关联的数据
select * from 左表 left outer join 右表 on 条件

右外连接：
select * from 右表 right outer join 左表 on 条件

MySQL不支持全外连接

自连接：左表右表都是同一张表

2、子查询

子查询先执行，查询的结果给主查询当做条件使用

语法：select 字段，... from 表  where 字段  运算符 (子查询)
单行子查询：（子查询的结果返回一行一列，要是用单行运算符）
= > >= < <= != 
多行运算符：（子查询的结果返回多行一列，要是用多行运算符）
in、any、all
子查询用在from后，当做表（通常子查询查回多列）这种子查询必须起别名
多列子查询：
子查询结果是多列的，肯定在from后面当做表来使用
select  查询字段  from（子查询） 表别名 where 条件

例如：删除表ttt中的重复数据，保留ID最大的一行数据

delect from ttt  where id not in (select max(id) from ttt group by name ,age)

3、集合查询

1、集合并：union（上下合并）：对集合进行查询，并去除重复值
      使用union时，两个查询所查询的列的个数和类型必须对应。
2、集合并：union all：对集合进行查询，不去除重复值
3、集合交：intersect：在MySQL不支持intersect  使用natural join实现
4、集合差集：minus：在MySQL不支持intersect  使用not in实现

4、数据备份

1、命令方式备份：mysqldump -u 用户名 -p 数据库 > 文件的路径/文件名.sql
2、命令方式还原：source 导入文件的路径
 

select语句总结

1、书写顺序
select 字段 from 表名 where 条件 group by 字段 having 条件 order by 字段 limit offset,length;
其中，select字段，from表名为 必写，其余均可根据情况而定。
2、执行顺序
from表名 -> where条件 -> group by 字段 -> having 条件 -> select 字段 -> order by 字段 -> limit

五、TCL

1、概述

数据库事务：
目的： 1、失败恢复方法
           2、保持一致性的方法
           3、操作隔离的方法
成功状态下能将数据从一种状态变为另一种状态，并能够持久化
异常情况下能将数据恢复到正常状态要能保持一致性，包含数据的一致性和约束的一致性
并发情况下：并发的操作之间不能产生相互影响

二、事物特性：

ACID ：原子性、一致性、隔离性、持久性

1、原子性（Actomicity）表示一个事务的所有操作都是一个整体，是不可分割的工作单位，要么全部成功要么全部失败
2、一致性（Consistency）事务前后数据的完整性必须保持一致
3、隔离性（Isolation）多个并发事务之间相互隔离
4、持久性（Durability）指一个事务一旦被提交，它对数据库中的数据的改变是永久性的

三、TCL语句：

不写TCL的时，对于每一条SQL数据都会自动开启一个事务,执行结束后自动提交事务

MYSQL中可以有两种方式进行事务的操作：1、自动提交事务。2、手动提交事务。

1、自动提交事务：MySQL的每一条DML(增删改)语句都是一个单独的事务，MySQL默认情况下，在执行每条语句时都会自动开启一个事务，执行完毕自动提交事务。
2、手动提交事务：   start transaction:开启事务     
        commit：提交事务
        rollback：回滚事务

四、事物的并发异常：

1、回滚丢失：此种更新丢失是因为回滚的原因，所以叫做回滚丢失。
2、覆盖丢失：此种更新是因为更新被其他事务覆盖了，所以叫做覆盖更新
3、脏读：一个事务读取了另一个事务尚未提交的数据
4、不可重复读：一个事务对同一行数据执行了两次或以上的查询，结果却不同
5、幻读：幻读和不可重复读有点像，只是针对的一般是数据的数量

五、隔离级别：

隔离级别主要包括：
读未提交（read uncommitted）
读已提交 （read committed）
可重复读 （repeatable read）
串行化     （serializable）
隔离级别越高，数据的效率越低
MySQL数据库InnoDB存储引擎（默认）的默认隔离级别是Repeatable Read

Oracle数据库默认的隔离级别是Read Committed

1、MySQL8以前版本
查看：select @@session.tx_isolation
修改：set @@session.tx_isolation=’隔离级别’
2、MySQL8
查看：select @@transaction_isolation
修改：set session transaction isolation level 隔离级别

六、各种隔离级别能解决哪些并发异常

各种隔离级别能解决哪些并发异常

隔离级别\并发异常	回滚丢失	脏读	不可重复读	覆盖丢失	幻读
读未提交	×	×	×	×	×
读已提交	√	√	×	×	×
可重复读	√	√	√	√	×
串行化	√	√	√	√	√

六、DCL

1、创建用户

语法：create user '用户名'@'主机' identified by 密码

2、授权用户

语法：grant create ,alter,insert,update ......on 数据库名.表 to '用户名@主机'
GRANT` 授权关键字
授予用户的权限，如`SELECT`，`INSERT`，`UPDATE`等。如果要授予所的权限则使用ALL
`数据库名.表名`：该用户可以操作哪个数据库的哪些表。
如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如`*.*

3、查看权限

语法：show grants for  '用户名'@'主机' 

4、删除用户

语法：drop user '用户名'@'主机' 

5、修改管理员密码

语法：mysqladmin -uroot -p password 新密码 -- 新密码不需要加上引号

6、修改普通用户密码

语法：set password for  '用户名'@'主机'  = '新密码'