关系型数据库MySQL(进阶语法)

一、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)

1. 原子性atomicity: 事务的操作要么同时完成 要么同时不完成 ，就是一个不可再分割的整体
2. 一致性consistency 事务前后数据得保证逻辑上的完整性
3. 隔离性Isolation 事务之间是相互独立的 互不影响的
4. 持久性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)

有两个使用场景的，

1. 把select 查询结果做一个快照，形成临时表

   create view v_视图名 as select 语句;
   
   

   把select 语句的结果 使用一个名字进行了定义，下一次使用这个结果的时候 直接使用名字即可

   视图中的数据也是可以修改的，但是是有前提的，前提是视图中的数据来自于原表，并不是通过统计计算得到的，视图的内容修改，原表也会跟随者修改的

2. 控制访问列的权限

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 存储工程名;