msyql 基础

数据库默认库

链接

范式理论

• 范式主要有哪些

第一范式(1NF)、第二范式(2NF)、第三范式(3NF)

• 三范式概念

第一范式(1NF)：每个列不可以在拆分

第二范式(2NF)：在第一范式的基础上，非主键列完全依赖于主键，不能是部分依赖于主键

第三范式(3NF)：在第二范式的基础上，非主键列完全只赖于主键，不依赖于其它非主键

• 函数依赖

这里只用通俗的话表达

1. 完全函数依赖

通过AB能得出C，不能只通过A或者B得到C，那么说C完全依赖AB

2. 部分函数依赖

通过AB能得出C，或者只通过A，或者B中的一个就能得到C，那么说C部分依赖AB

3. 传递函数依赖

通过A得到B，通过B得到C，但是直接通过A得不到C，那么说C传递依赖A

• 三范式的区分

1. 第一范式的核心原则是：属性不可切割
2. 第二范式的核心原则是：不能存在“部分函数依赖”
3. 第三范式的核心原则是：不能存在“传递函数依赖”

SQL语言的组成

DDL（Data Definition Language）

数据定义语言。例如，建库，建表语句

DML（Data Manipulation Language）

数据操作语言。例如插入、删除、修改

DQL（Data Query Language）

数据查询语言。例如查询语句

DCL（ Data Control Language）

数据控制语言。用来创建数据库用户，控制数据库的访问权限

DML

删除数据

1）根据条件删除数据

delete from gmall.userinfo where dt = '2021-02-16';

2）清除表中所有数据

truncate table gmall.userinfo;

mysql 约束

主键约束(primary key) pk

重要

创建主键

-- 方式1：在创建表的同时创建主键约束
-- 单列主键
create table emp1
(
    eid    int primary key,
    name   varchar(20),
    deptId int,
    salary double
);

-- 联合主键
create table emp2
(
    name   varchar(20),
    deptId int,
    salary double,
    primary key (name, deptId)
);

-- 方式2：主键约束不仅可以在创建表的同时创建，也可以在修改表时添加
alter table emp1 add primary key（eid);

删除主键

-- 删除单列主键 
alter table emp1 drop primary key;
 
-- 删除联合主键 
alter table emp2 drop primary key;

总结：

1. 每个表最多只允许一个主键
2. 主键约束列不允许重复，也不允许出现空值
3. 唯一标识表中的每一行，其目的是为了提高查询效率

自增长约束(auto_increment)

重要

创建自增长约束

-- 方式1：在创建表的同时创建自增长约束
-- 默认情况
create table t_user1
(
    id   int auto_increment primary key, -- 默认情况初始值是 1，每新增一条记录，字段值自动加 1
    name varchar(20)
);


-- 指定初始值
create table t_user2
(
    id   int primary key auto_increment,
    name varchar(20)
) auto_increment = 77; -- 指定初始值为77

-- 方式2 创建表后，对表添加自增长约束
alter table t_user2 auto_increment=88;

注意事项：

1. 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有索引，以避免序号重复（即为主键或主键的一部分）
2. auto_increment约束的字段必须具备 NOT NULL 属性
3. auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT） 等

删除自增长约束

delete数据之后自动增长从 断点 开始

truncate数据之后从1开始

外键约束(foreign key) fk

重要

创建外键约束

-- 方式1 创建表时，直接指定外键约束
# 主表：
create table dept
(
    deptno varchar(50) primary key comment '部门号',
    name   varchar(50) not null comment '部门名称'
) comment '部门表';

# 从表：
create table emp
(
    eid     varchar(50) primary key,
    ename   varchar(50) comment '姓名',
    dept_id varchar(50) comment '部门ID',
    create table emp
(
    eid     varchar(50) primary key,
    ename   varchar(50) comment '姓名',
    dept_id varchar(50) comment '部门ID',
    constraint emp_fk foreign key (dept_id) references dept (deptno) on update cascade -- 外键约束
) comment '员工表';
-- cascade：主表修改记录时，从表外键字段也会修改（将cascade改为restrict，意思相反）

-- 方式2 创建表后，对表添加外键约束
alter table emp
    add constraint emp_fk foreign key (dept_id) references dept (deptno) on update cascade;
);

删除

1. 删除数据时，需要先删除从表，然后删除主表
2. 删除外键约束

alter table emp
    drop foreign key fk_emp_dept_id

非空约束(not null)

创建非空约束

--f1
create table t_user6
(
    id      int,
    name    varchar(20) not null,
);

--f2
alter table t_user7 modify name varchar(20) not null; 

删除非空约束

alter table t_user7 modify name varchar(20) ;

唯一性约束(unique)

默认约束(default)

零填充约束(zerofill)

mysql 内置函数

• 随机数rand

• 生成随机整数

UPDATE 表名 set 字段 = round(rand() * 具体数值) + 1 where 条件

UPDATE ads_area_topic 
SET payment_day_count = round( rand() * 15 ) + 1 
WHERE
	dt = '2020-06-15';

• mysql 生成随机小数

好像默认保留2小数

UPDATE ads_area_topic 
SET order_day_amount = rand() * 200 
WHERE
	dt = '2020-06-15';

• 时间相减函数: timestampdiff

语法：timestampdiff(args1,date1,date2)

参数简单解释：
date1小，date2大
args：可以是year|month|day|hour|minute|second

select timestampdiff(day,'2020-06-14','2020-06-15');
# 输出1天
select timestampdiff(second ,'2020-06-14 12:12:12','2020-06-14 12:12:13');
# 输出1s

• 日期减少函数: date_sub

select date_sub('2022-01-01', interval 1 month);
# 输出 2021-12-01