一、SQL进阶语法
1、常见数据类型
关于数据类型详细内容可在cmd
登录上mysql
平台 查看帮助文档 ? data types;
整型
类型 | 描述 |
---|---|
BIGINT ** | 长整型 占8个字节 |
INT ** | 整型 占4个字节 |
MEDIUMINT | 中等整型 占3个字节 |
smallint | 短整型 占2个字节 |
TINYINT ** | 字节 占1个字节 -128到127 |
定义int类型的时候 int(数字)
数字表示的是数据的宽度 不给这个数字的话 int默认是11位
浮点型
类型 | 描述 |
---|---|
FLOAT | 单精度 4个字节 有效小数的位数8位 |
DOUBLE | 双精度 8个字节 有效小数的位数16位 |
DECIMAL | 精确度更准一些 |
定义的时候 float(m,n)
m
数据的宽度 包含小数在内总共的位数, n
保留小数的位数
float(5, 2)
数据的范围最大是 999.99
字符串类型
类型 | 描述 |
---|---|
CHAR(M) | 定长字符串 M表示单字节字符的个数,【0-255】没给的话默认为1 |
VARCHAR(M) | 变长字符串 M表示单字节字符的个数【0-65535】 如果文本是汉字,最多21844个 |
TEXT | 存放更多的文本信息 |
char
是定长的,给定的M是几,存储的时候就开辟几个字节,如果内容不足M个,存储的时候会在内容末尾追加空格,填充到M
varchar
是变长的,真实开辟的内存大小是 内容字节长度+1
布尔类型
类型 | 描述 |
---|---|
BOOLEAN | 布尔类型,数据是tinyint(1) 0表示False ,非0表示True, 一般我们使用1来表示 |
时间类型
类型 | 描述 |
---|---|
DATE | 描述的是年月日的时间,赋值的是'yyyy-mm-dd' |
DATETIME | 描述的是年月日时分秒的时间 赋值 yyyy-mm-dd HH:MM:SS |
TIMESTAMP | 描述的是时间戳 时间戳一般我们定义为bigint |
2、约束类型
对于字段信息数据的一些限制
主键约束(primary key
)
主键 – 表中数据的唯一标识,要求这个字段的值不能重复,不能为空
添加这个约束的方式
字段名 数据类型 primary key
或者
create table if not exists 表名(
字段名 数据类型,
字段名1 数据类型 约束,
字段名2 数据类型 约束,
primary key(字段名)
);
把一个字段设置为主键之后,主键就具备了索引的特性,通过主键进行查询数据,效率要比其他的键快捷很多
索引的特点可以理解成一本书的目录
自增长auto_increment
是和主键一起来用的,当主键是整数类型的时候,我们可以使用自增长来避开数据重复的问题
字段名 int primary key auto_increment
唯一约束unique
要求这个字段的内容不能重复,但是可以为空(null
)
给字段设置了唯一索引,也相当于设置了索引信息,通过键来查找效率也会提高的
字段名 数据类型 unique
非空约束not null
要求这个字段的内容不能为空
字段名 数据类型 not null
默认值约束default
给这个字段设置一个默认值,添加数据的时候没有给这个字段设置值,就被赋予默认值,赋予的话就使用新值
字段名 数据类型 default 数据值
外键约束(foreign key)
建立表和表之间的关系的
一张表中某个字段要和别的表建立联系,这个字段称为外键
比如 班级表: 班级编号 班级名称
学生表: 学生编号 学生姓名 班级编号
学生表中的班级编号被称为外键,因为它要和班级表去建立联系。
外键的值依赖于被联系的表中的数据的,因此出现了两个概念: 主表
和从表
有外键的表称为从表,另外一张联系表称为主表,因为从表的数据依赖于主表
给外键赋值的时候 数据必须来自于主表被联系的字段的值,否则只能赋予为空(null
)
create table if not exists 表名(
字段名 数据类型,
字段名1 数据类型 约束,
字段名2 数据类型 约束,
字段名3 数据类型,
primary key(字段名),
constraint 约束名 foreign key(字段名3) references 主表(主键字段名)
);
注解约束comment
给字段添加注释,注解字段的含义,方便解读表结构
create table if not exists 表名(
字段名 数据类型,
字段名1 数据类型 约束 comment '含义信息',
字段名2 数据类型 约束,
字段名3 数据类型,
primary key(字段名),
constraint 约束名 foreign key(字段名3) references 主表(主键字段名)
);
3、运算符
3.1 条件判断运算符
运算符 | 描述 |
---|---|
字段名 > 数据值 | 获取字段中大于指定数据的行信息 |
字段名 >= 数据值 | 获取字段中大于或者等于指定数据的行信息 |
字段名 < 数据值 | 获取字段中小于指定数据的行信息 |
字段名 <= 数据值 | 获取字段中小于或者等于指定数据的行信息 |
字段名 = 数据值 | 获取字段中等于指定数据的行信息 |
字段名!=数据值 或者 字段名<>数据值 | 获取字段中不等于指定数据的行信息 |
字段名 in (数据值1,数据值2,数据值n) | 获取字段中包含在指定数据们中的行信息 |
字段名 not in (数据值1,数据值2,数据值n) | 获取字段中不包含在指定数据们中的行信息 |
字段名 between A and B | 获取字段中的数据在区间[A, B]之内的数据行信息 |
字段名 not between A and B | 获取字段中的数据不在区间[A, B]之内的数据行信息 |
字段名 is null | 获取字段中数据为空值的行信息 |
字段名 is not null | 获取字段中数据不为空值的行信息 |
字段名 regexp 正则表达式 | 获取的字段中满足正则表达式的行信息 |
字段名 like 信息 | 信息中可以使用两个占位符 1. _ 匹配任意一个符号2. % 匹配任意个符号 |
3.2 逻辑运算符
运算符 | 描述 |
---|---|
and | 逻辑与 连接并且关系 |
or | 逻辑或 连接或者关系 |
! | 逻辑非 对逻辑结果取反 |
4、常见的函数
4.1 数学函数
abs(-100) 绝对值
floor(29.8) 向下取整
ceil(29.1) 向上取整
round(29.786) 四舍五入
round(29.632, 2) 保留小数位
pow(2, 4) 幂数
rand() `0-1之间的随机数`
pi() 圆周率
greatest(19, 23, 56, 42, 7, 62) 多个数据中的最大值
least(19, 23, 56, 42, 7, 62) 多个数据中的最小值
4.2 字符串函数
char_length(char):字符串长度
left(char,n):获取左边指定n个数的字符 可以理解为以什么开始
right(char,n):获取右边指定n个数的字符
instr(字符串,子串):判断字符串中是否包含指定内容;包含为1,不包含为0
concat(字符串1,字符串2,字符串3):字符串拼接
concat_ws('-',字符串1,字符串2,字符串3):指定拼接符对字符串拼接
replace(字符串,旧子串,新子串):字符串替换
trim( 修饰词 子串 from 字符串):移除字符串两端的内容 修饰词:leading 移除左边 trailing 移除右边
strcmp('a','A'):字符比较,在mysql中忽略大小写,前者大于后者为1,等于为0,小于为-1
substr(字符串,提取的起始位置【从1开始】,长度):提取子串,不指定长度,从指定的起始位置到最后
4.3 日期函数
select now() `年月日时分秒`, curdate() `年月日`, curtime() `时分秒`;
-- 提取时间中的各段信息
select year(now()) `年`,
month(now()) `月`,
day(now()) `日`,
date(now()) `年月日`,
hour(now()) `时`,
minute(now()) `分`,
second(now()) `秒`,
dayofweek(now()) `星期 【星期天是第一天】`,
dayofyear(now()) `一年中的第几天`,
week(now()) `一年中的第几个星期 【默认周日是第一天】`,
week(now(), 1) `一年中的第几个星期 [按中国星期 星期一是第一天]`;
-- 时间戳
select unix_timestamp() `当前时间对应的时间戳`,
unix_timestamp('2022-09-08 12:22:45') `指定时间的时间戳`;
-- 时间戳转化为时间
select from_unixtime(1662610965); -- 2022-09-08 12:22:45
select from_unixtime(1662610965, '%Y-%m-%d'); -- 2022-09-08
-- 时间格式化【date_to_str】 与 反格式化 【str_to_date】
select date_format(now(), '%Y/%m/%d %H:%i:%S'), -- 2022/09/09 14:30:44
str_to_date('2022/09/09 14:30:44', '%Y/%m/%d %H:%i:%S'); -- 2022-09-09 14:30:44
-- 时间差
select datediff(now(), '2022-08-11'); -- 29 差的天数
-- select timestampdiff(差值类型, 小时间, 大时间);
/*
year
month
day
hour
minute
second
week 星期数
quarter 季度
*/
select timestampdiff(quarter, '2022-01-11', now());
-- 获取时间所在季度
select quarter(now()) 季度;
-- 已知一个时间 差值 求另外一个时间
-- date_add(时间, interval 差值表达式 差值类型)
-- date_sub
/*
差值类型
year
month
day
hour
minute
second
week
quarter
year_month 差值表达式 '1-2'
day_hour 差值表达式 '3 5' 3天5小时
day_minute 差值表达式 '3 2:5' 3天2小时5分钟
day_second 差值表达式 '3 2:5:9' 3天2小时5分钟9秒
hour_minute 差值表达式 '2:5' 2小时5分钟
hour_second 差值表达式 '2:5:9' 2小时5分钟9秒
minute_second 差值表达式 '5:9' 5分钟9秒
*/
select date_add(now(), interval 3 day) `三天后`,
date_add(now(), interval '3 5' day_hour) `三天5小时后`;
use school;
select * from tb_student;
-- 本月过生日的
select * from tb_student where month(stu_birth)=month(now());
-- 本季度过生日的
select * from tb_student where quarter(stu_birth)=quarter(now());
-- 本周过生日的
select * from tb_student
where week(concat(year(now()), substr(stu_birth, 5)), 1)=week(now(), 1);
select week('2000-09-21'), week('2030-09-21');
4.4 聚合函数
函数 | 描述 |
---|---|
count | 如果没有分组,统计查询结果有几行,如果有分组的话,统计的是每个组内有几行数据count(*) — 统计的数据中存在着空值,也会被计数在内count(字段名) ---- 如果该字段中存在空值,将不会被统计计数在内 |
sum(字段名) | 如果没有分组的话,统计查询结果中该字段的数据和,如果有分组 统计的该组下对应字段的数据和 |
avg(字段名) | 如果没有分组的话,统计查询结果中该字段的数据平均值,如果有分组 统计的该组下对应字段的数据平均值 |
max(字段名) | 如果没有分组的话,统计查询结果中该字段的数据最大值,如果有分组 统计的该组下对应字段的数据最大值 |
min(字段名) | 如果没有分组的话,统计查询结果中该字段的数据最小值,如果有分组 统计的该组下对应字段的数据最小值 |
group_concat(字段名) | 分组之后 某个字段的数据值 相对于分组字段来说 数据是多个的,想要全部列出,需要使用到group_concat `group_concat(字段名 order by 字段名 asc |
5、事务(transaction
)
执行的sql语句就是所谓的事务,目前来说一条sql语句就是一个事务
事务: 整体形成一个任务,不可再分割的操作称为事务
有些场景的实现是需要多条sql语句协助才能完成一个任务,这种场景就需要我们自己定义事务
比如银行转账的操作:收款方与打款方
事务有4个特性(ACID)
- 原子性
atomicity
: 事务的操作要么同时完成 要么同时不完成 ,就是一个不可再分割的整体 - 一致性
consistency
事务前后数据得保证逻辑上的完整性 - 隔离性
Isolation
事务之间是相互独立的 互不影响的 - 持久性
durability
事务的结果一旦提交是永久保存的
事务的语句
-- 开启一个事务
begin 或者 start transaction
-- 结束事务 并把事务结果提交 【没有问题 提交】
commit;
-- 结束事务 把事务操作的数据回滚到开启事务之前 [事务中一些数据出现偏差 不能生效 回滚]
rollback;
6、索引(index
)
接触到的索引有主键索引和唯一索引,分析字段需求的时候设定的相关约束
为什么使用索引键查询效率要比其他键快,比喻是索引就相当于书籍的目录
索引底层结构B+树,数据的遍历是通过二分法
查找的
sql的执行计划,看到sql语句执行效率等
explain sql语句;
要想提高查询效率的话 尽量使用索引键查询
如何定义一个索引键??
-- 索引键
create index idx_索引名 on 表名(字段名);
-- 查看所有的索引键
show index from 表名;
-- 删除索引键
drop index idx_索引名 on 表名;
写程序的时候 效率提高的同时,空间在损耗 【用空间换时间】
空间是有限的,所以最优解就是占用少量的空间,尽量取提升时间
我们可以采用前缀索引的形式来添加索引
-- 索引键
create index idx_索引名 on 表名(字段名(元素下标)); --- 下标是从1开始的
还有一种索引称为复合索引,就是一个索引名绑着多个字段,复合索引需要遵守的最左侧字段原则: 筛选数据的时候 在逻辑上最左侧字段必须参与筛选,复合索引才有意义,否则起不到索引的作用
-- 索引键
create index idx_索引名 on 表名(字段名, 字段名,...); --- 下标是从1开始的
7、视图(view
)
有两个使用场景的,
-
把select 查询结果做一个快照,形成临时表
create view v_视图名 as select 语句;
把select 语句的结果 使用一个名字进行了定义,下一次使用这个结果的时候 直接使用名字即可
视图中的数据也是可以修改的,但是是有前提的,前提是视图中的数据来自于原表,并不是通过统计计算得到的,视图的内容修改,原表也会跟随者修改的
-
控制访问列的权限
8、自定义函数(function
)
主要是来说自定义函数【了解】
自定义函数的格式
-- 修改语句结束符
delimiter $$
create function 函数名(参数名 数据类型, 参数名 数据类型) returns 返回值类型
begin
函数体
return 返回值;
end $$
-- 定义完成之后改回默认的
delimiter ;
因为函数体中肯定会写SQL语句的,sql语句的结束标记就是;
,定义完函数之后 也要结束定义的语句,到底哪个分号是来结束函数的定义的就不明确了,所以定义函数之前,修改一下sql语句的结束符
-- 删除函数
drop function 函数名;
9、存储过程(stored procedure
)
可以理解成在服务器上保存一组SQL代码,也可以理解成对SQL代码的封装
将SQL语句设置成存储过程,要比一条一条的sql语句执行的性能效率快捷一些
-- 修改语句结束符
delimiter $$
create procedure 存储过程名(参数 数据类型, 参数 数据类型)
begin
存储过程体
end $$
-- 定义完成之后改回默认的
delimiter ;
-- 删除存储过程
drop procedure 存储工程名;