MySQL 笔记

2022-09-30 本文已影响0人会爬虫的小蟒蛇

常用数据类型

数字类型

INT
数据范围 -2^32 - 2^32-1 (约10位)
BIGINT
数据范围 -2^63 - 2^63-1 (约10位)
FLOAT

float(m,d) 其中m表示有效位，d表示小数位
m最大值为7 d不能大于m

DOUBLE

double(m,d) 其中m表示有效位，d表示小数位
m最大值为15 d不能大于m

DECIMAL

当有一个非常大的数字，且不希望精度丢失时使用
decimal(m, d), 其中m表示有效位，d表示小数位 m最大值为65

不会产生精度问题的原因是，他本质是字符串

字符类型

CHAR
表示固定长度的字符串，长度为255个字节，约65个汉字
VATCHAR
不定长字符串，长度为0-65535，约1w个汉字
TEXT
长文本类型，最大长度占据64kb
LONGTEXT
极大文本，最大长度占据4GB

时间类型

DATATIME
存进去是什么就是什么
TIMESTAMP
如果当前时区发生更改，timestamp会跟着时区发生更改

常见运算符

div (取商)
mod (取余)
between 与 not between (表示区间， between a and b)
is null 与 is not null (判断是否为null)

常用函数

数字运算

sum (求和)
avg (平均数 )
count (记录数)
max 和 min (对字符串进行操作时，根据ascii码表)

字符运算

char_length (求字符串长度)
format (格式化，用于浮点数小数位截取, format(0.33333, 1) )
left和right (截取字符串， left("abcd", 2) )
trim (去除两端空格)

CRUD

Create

单条插入

insert into table_name(name1, name2...) values(value1, value2....)

多条插入

insert into table_name(name1, name2...) 
  values(value1, value2....),(value1, value2....)

Retrieve

获取所有记录

select name1, name2... from table_name

条件查询(where, and, or)

select * form table_name where 表达式

模糊匹配(like, %, _)

select * form table_name where name like "%a_"
// 存在性能问题，如果记录较多会非常慢

限制返回条数(limit， offset)

查询表达式 limit 取的数量 OFFSET 跳过的数量

过滤重复值(distinct)

select distinct 字段 form table_name

排序(order by)
- 升序（ASC，默认）
- 降序（DESC）

查询表达式 order by name asc

分组(group by)

如果分组后，没有操作语句，默认返回最后一条数据

查询表达式 group by name

Update(更新)

update table_name set name1=value1 where 表达式

delete(删除)

delete from table_name where 表达式

集合操作

交差并补.png

并集 (union)

select name form table1 表达式
    union
select name form table2 表达式
// 子语句 select 必须拥有相同数量的列，且列的数据类型页相同

交集(join, inner join)

select * from
    (select name form table1 表达式) as t1
        join
    (select name form table2 表达式) as t2
        on t1.name = t2.name

差集(left join, right join)
- A 对 B的差集 (left join)
- B 对 A的差集 (right join)

select * from
    (select name form table1 表达式) as t1
        left join
    (select name form table2 表达式) as t2
        on t1.name = t2.name
where t2.name is null

补集

select * from
    (select name form table1 表达式) as t1
        left join
    (select name form table2 表达式) as t2
        on t1.name = t2.name
where t2.name is null
    union
select * from
    (select name form table1 表达式) as t1
        left join
    (select name form table2 表达式) as t2
        on t1.name = t2.name
where t1.name is null
// A与B的补集 = A对B的差集 + B对A的差集

约束

主键约束 primary key

  关系表中记录的**唯一标识**（不为null，不可重复）

create table table_name(
    id int primary key auto_increment,
    name varchar(255), 
)
alter table table_name add primary key (`id`);
alter table table_name drop primary key;

非空约束 NOT NULL

  只能约束程序层面上没有操作，不能约束人行为上对其赋值为空白字符

唯一约束 UNIQUE

  NULL 可以重复，不受此约束影响

外键约束 foreign key

  性能问题：插入数据需要校验

  并发问题：在高并发的事务场景下，使用外键会造成死锁

  迁移问题：触发器、存储过程和外键都很难迁移，成本太高

默认值约束 default

  如果没有对字段赋值，他会自动填充

变量

服务器系统变量

通过@@来调用系统变量

// 列出mysql所有系统变量
show variables
select @@data_format

用户变量

通过@来调用用户变量

# 输出变量 yesterday
select @yesterday

# 对变量yesterday进行赋值
set @yesterday = subdate(current_date, 1)

# 日期格式化
set @yesterday = date_format(@yesterday, "%y/%h/%d")

# 小数的格式化
set @amount = 0.4
set @amount = cast(@amount as decimal(15, 3))
select @amount

局部变量
- 不需要@前缀，但需要事先的变量声明和初始化
```
declare t_date varchar(255);
```

存储过程

简单的认为是SQL中的函数

声明一个存储过程

创建存储过程

create procedure functionName(days int)
begin
  declare t_date varchar(255);
  set t_date = date_format(subdate(current_date, days), `%Y-%m-%d`);
  select 
      department as 部门,
      count(*) as 店铺数,
  from table_name
  where sta_date=t_date
  group by department;
end

调用存储过程

call functionName(1)

删除存储过程

drop procedure functionName

触发器

和存储过程一样，都是嵌入到mysql中的一段程序，区别就是存储过程需要显示调用，而触发器是根据对表的相关操作自动激活执行。

创建触发器

create trigger 触发器名称
before insert  # 触发时机 before[after] [insert, update, delete]
on 表名
for each row
begin
    if new.department not in ("男装事业部", "女装事业部") then
        set new.department = "unknow";
    end if;
end

触发器的应用
```
  多用来检查字段
```
查看触发器

show triggers from 数据库名;

删除触发器

drop trigger 触发器名称；

索引

# 创建索引
CREATE INDEX 索引名 on 表名(字段名1, 字段名2)

# 查看索引
SHOW INDEX FROM 表名

# 删除索引
ALTER TABLE 表名 DROP INDEX 索引名

# 查看是否命中索引
EXPLAIN SELECT * FROM 表名 where 表达式