常用数据类型
数字类型
-
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只能约束程序层面上没有操作，不能约束人行为上对其赋值为空白字符 -
唯一约束
UNIQUENULL 可以重复，不受此约束影响 -
外键约束
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 表达式
网友评论