日常积累--数据库基础知识 （MySQL）
MySQL数据库特点
较小的冗余度、较高的的数据独立性和易扩展性，并可为多个用户共享。
DBMS的主要功能
操作对象：表
- 定义：DDL（数据库定义语言）create、alter、drop
- 操作：DML insert、delete、update、select
- 保护：完整性、安全性
- 维护：表结构、数据
数据库系统DBS = DB + DBMS + 应用系统 + DBA + 用户
数据库发展的3个阶段
| 数据库类型 | 网状数据库 | 层次数据库 | 关系数据库 |
| 数据库组织方式 | 网状模型 | 层次模型 | 关系模型 |
| 架构 | 图形结构 | 树形结构 | 简单二维表结构 |
数据管理技术的发展过程
人工管理阶段（20世纪50年代之前）文件管理阶段（20世纪50年代末-60年代中）
数据库管理阶段（20世纪60年代末-现在）
实体-联系模型
实体：客观事物在信息世界中被称为实体
属性：描述实体的数据项
联系：反映事物内部或者事物之间的关系
名词解释
关系：二维表
元组：二维表的行（记录）
属性：二维表的列
域：属性的取值范围
关键字：关系中能唯一区分、确定不同元组的属性和属性组合
主键：唯一、不为空、不重复、针对一个表
外键：针对两个表
关系模式
关系模式：关系名（属性名1，属性名2，...，属性名n）
特点：规范化，属性不可再分割
同一关系中，不允许出现相同的属性名
同一关系中，元组和属性的顺序任意
关系运算
选择：行
投影：列
连接：从两个关系的笛卡尔积中选择属性间满足一定条件的元组，组成新的关系
关系的完整性约束
实体完整性：主键唯一、不重复、不为空
参照完整性（引用完整性）：从表外键参照主表主键
域完整性（用户定义完整性）：列的取值范围
范式（NF）
消除存储异常、减少数据冗余、保证数据的完整性和存储效率
一般为3NF
- 1NF：所有属性均为简单属性，即每个属性不可再分（无重复列）
- 2NF：如果关系R满足1NF，且每一个非主键字段完全依赖于主键，则称满足第二范式
- 3NF：如果关系R满足2NF，且非主键字段之间不存在依赖关系，则称R满足第三范式
E-R图
矩形——实体
椭圆——实体属性
菱形——实体间的联系
MySQL
数据库
查看现有数据库：show databases;
创建数据库：create database <数据库名>；
删除数据库：drop database <数据库名>；
建表
MySQL支持的数值类型：数值型、日期/时间型、字符串（字符）
1.数值型
整数类型 int
小数类型 decimal 格式：decimal(总长度，小数位)
2.时间/日期型
DATE: YYYY-MM-DD
TIME: HH:MM:SS
YEAR:YYYY
DATETIME:YYYYY-MM-DD HH:MM:SS
TIMESTAMP(时间戳):YYYY MM DD HH MM SS
3.字符串
char、varchar、binary、varbinary、blob、text、enum、set
格式：char(字符串长度) 定长
varchar(字符串长度) 变长
4、创建表
属性名1 数据类型 [约束条件],
属性名2 数据类型 [约束条件],
属性名3 数据类型 [约束条件]
）; /* 最后一个属性没有 ，*/
5、删除表
drop table 表名1，表名2，...，表名n；
6、修改表
添加属性（列）:alter table 表名 add 属性名 数据类型； /*alter .. add*/
删除属性：alter table 表名 drop 属性名；/*alter .. drop*/
修改属性：alter table 表名 modify 属性名 数据类型；/*alter .. modify*/
修改字段名：alter table 表名 change 旧字段名 新字段名 数据类型；/*alter .. change*/
显示表结构 desc 表名；
完整性约束
--实体完整性、
参照完整性、
用户自定义完整性约束
1、实体完整性约束
- 添加主键约束（创建表时进行主键约束）
（1）create table 表名（
属性名1 数据类型 primary key，
属性名2 数据类型，
...
）；
（2）create table 表名（
属性名1 数据类型 ，
属性名2 数据类型，
...
constraint 主键约束的名字 primary key（属性名1） /* 联合主键 primary key(属性名1，属性名2)*/
）；
（3）create table 表名（
属性名1 数据类型 ，
属性名2 数据类型，
...
primary key(属性名1) /* 联合主键 primary key(属性名1，属性名2)*/
）；
- 添加主键约束（已经存在的表）
（2）alter table 表名 add primary key (列名); /*联合主键 primary key(列名1，列名2)*/
（3）alter table 表名 add constraint 主键约束名字 primary key(列名); /*联合主键 primary key(列名1，列名2)*/
- 删除主键的约束
2、唯一性约束（unique）
不允许有重复的值，保证数据的唯一性；
可以有空值；
在一个表中，可以有多个唯一约束；
在默认情况下，唯一约束的名字和列名保持一致；
添加唯一约束的列，系统默认给这个列添加一个唯一索引。
- 创建表时创建唯一约束
（1） create table 表名（
列名1 数据类型，
列名2 数据类型，
constraint 唯一约束的名字 unique (列名) /*
）； 多个唯一约束: constraint 唯一约束的名字1 unique (列名1) ,
constraint 唯一约束的名字2 unique (列名2) ,
....
*/
（2） create table 表名（
列名1 数据类型 unique,
列名2 数据类型 ,
...
）;
- 对已经存在的表添加唯一约束
- 删除唯一约束
3、域完整性约束（默认约束、非空约束）
- 默认约束 default
（1）创建表时默认约束
create table 表名（
列名1 数据类型 default '字符串类型或者日期类型的默认值'，
列名2 数据类型 default 数值型默认值，
列名3 数据类型
）；
（2）对已存在的表添加默认约束
alter table 表名 modify 列名 数据类型 default 默认值；
（3）删除默认约束
alter table 表名 modify 列名 数据类型；
- 非空约束 not null
create table 表名（
列名1 数据类型 not null，
列名2 数据类型 not null，
列名3 数据类型
）；
（2）对已存在的表
alter table 表名 modify 列名 数据类型 not null；
（3）删除非空约束
alter table 表名 modify 列名 数据类型；
3、参照完整性约束（primary key）
表的外键值必须在主表中找到，主表中没有的数据，从表不能有操作
若主表被从表参照，主表的记录将不允许删除，若删除数据，需先删除从表中依赖记录的数据
外键构建于一个表的两个字段或两个表的两个字段之间的参照关系 （1）建表时设置 外键约束名：FK_名
create table 表名2（
列名1 数据类型 ，
列名2 数据类型 ，
列名3 数据类型，
constraint 外键约束名 foreign key (从表的列名1) references 主表表名（列名1）
）；
PS：主表的列名和从表的列名可以不一样，但两个列的数据类型和内容必须一致
（2）对已存在的表
alter table 从表表名 add constraint 外键约束的名字 foreign key (从表列名1) references 主表表名（主表列名)；
（3）删除非空约束
alter table 表名 drop foreign key 外键约束的名字；
增删改查（insert、delete、alter、select）
1、Insert
(1) insert into 表名 (列名1，列名2，列名3，...) values (值1，值2，值3，...)；
（2）insert into 表名 values (值1，值2，值3，...)；
（2）insert into 表名 values (值1，值2，值3，...)，(值1，值2，值3，...)，(值1，值2，值3，...)，...；
2、Delete
<di>删除的只是数据，保留表结构，drop删除数据与表结构 </di> (1) delete from 表名；
（2）delete from 表名 where 条件；
3、Update...set...
(1) update 表名 set 列名1 = 值1，列名2 = 值2，... ；
（2）update 表名 set 列名 = 值 where 条件；
4、Select (重点) 😎
(1)select 列名 from 表名；/*执行顺序：先表后列*/
（2）去重查询（distinct）：select distinct 列名 from 表名；
（3）使用别名查询（as）：select 列名1 as '别名1'，列名2 as '别名2' ，... from 表名；
（4）条件查询（where ... and ...）：select 列名 from 表名 where 条件1 and 条件2；
（5）范围 select 列名 from 表名 where 条件
条件：1) [not] between 开始值 and 结束值； /*包含开始值与结束值*/
2) 列名 in （值1，值2，值3，... ）； /*只要满足其中一个就会有查询结果*/
（6）字符通配符（like）
% 表示0个或多个字符
_ 表示一个字符
（7）是否为空查询
is null is not null
（8）聚合函数
select 聚合函数 from 表名
聚合函数：sum avg max min （[distinct <列名>]) /*avg(默认保留4位小数)*/
count（*） /*统计表中元组个数*/
count（[distinct <列名>]) /*统计本列列值个数*/
/*除了 count(*) 之外，其他函数在计算过程中均忽略null值*/
select 列名1，列名2，... ，from 表名 limit [start,] nums ;
/*
start:从第几行开始，可选，不写，则从0开始
nums:总共要几行
*/
（10）数据分组 group by
select 列名，聚合函数 from 表名 group by 列名；
（11）having
select 列名，聚合函数 from 表名 group by 列名 having 条件； /*
having 通常与 group by 子句一起使用，
相当于一个用于组的where条件，
用于制定组的搜索条件，针对组。
having可以包含聚合函数，但where不可以
*/
（12）排序 order by（默认是 升序）
降序：desc 升序：asc
例：1）select * from 表名 order by 列名 desc； 2）select 列名 from 表名 order by 列名1 asc ，列名2 desc;
MySQL函数
(1)字符串函数
length() 返回字符的字节长度
char_length() 返回字符串的字符长度
mid() 从某个位置获取某个长度的字符
mid(列名，开始位置，要截取的长度) /*开始位置最小值为1*/
(2)数学函数
round(参数) 实现四舍五入
2个参数：round(数值，保留的小数位)
1个参数：round(数值) 默认保留整数部分
least() 求最小数字
greatest() 求取最大数字
/*上述两个函数与min(),max()的区别是，这两个函数必须传“具体数值”，例：least(1,2,3,4,5,6)*/
(3)时间日期函数
now() 可求得当前数据库服务器的当前日期时间
current_date() 当前日期
current_time() 当前时间
to_days('日期') 将日期转化为天数
dayofyear('日期') 该年已过了多少天
week('日期') 当前时日是第几周
例：select now();
(4)控制函数
if(参数1，参数2，参数3)； /* 参数1为空，输出参数3，输出参数2*/
ifnull(参数1，参数2)； /*参数1为空，输出参数2，输出参数1*/
表连接
1、内连接
<di>返回的两个表中可以相互匹配的数据*/ </di> （1）select 表名1.列名，表名2.列名 from 表名1，表名2 where 表名1.列名 = 表名2.列名 and 条件1 and 条件2；
（2）select 表名1.列名，表名2.列名 from 表名1 inner join 表名2 on 表名1.列名 = 表名2.列名 and 条件1 and 条件2；
innner join on 等值连接：条件中只包含等号“=”,没有其他符号
非等值连接：条件中除了等号“=”之前，还有其他符号 > < >= <=
2、外连接
<di>返回from子句提到的至少一个表或视图中的所有行*/ </di> 右外连接：对连接条件中右边的表不加限制
（1）左外连接 left outer join : 包含左表所有行和连接所匹配的行，若右表中没有匹配行，则右表相应位置为null;
（2）右外连接 left outer join : 包含右表所有行和连接所匹配的行，若左表中没有匹配行，则左表相应位置为null；
select 表名.列名 from 表名 1 left outer join 表名2 on 表名1.列名 = 表名2.列名；
子查询
select 列名 from 表名 where 列名 in (select 查询)
insert 与 select
/*将查询的结果插入到某一张表中*/
insert into 表名 select查询；
update 与 select
update 表名 set 列名 where 列名 in （select查询）
delete 与 select
delete from 表名 where 列名 in (select 查询)
视图
/*
视图是一张虚表
对视图的更改会影响到实表
*/
create view 视图名 as select 语句；
alter view 视图名 as select语句；
索引
创建索引 create [unoque] [cluster] index 索引名 on 表名（列名）； /*unique 唯一索引 ， cluster 聚簇索引*/ 修改索引 alter index 旧索引名 rename to 新索引名；
删除索引 drop index 索引名；
优点：加快查询速度、保证数据的唯一性、实现表与表之间的参照完整性、可以减少group by、order by、分组和排序的时间
缺点：降低了更新表时的速度，建立索引会占用磁盘空间（索引文件）
SHEIN希音公司福利 280人发布
