别否定自己你特别好特别温柔特别值得
目录
一、约束
-- 主键约束
关键字:primary key
特点:主键约束列具有唯一约束和非空约束,因此在主键约束中不允许重复和无值,可以用来定位某行
每个表最多允许一个主键约束,但主键约束可以用单个也能用组合,只能创建一个
创建主键约束时,系统会默认在列和列的组合上建立唯一的索引
1.添加主键约束
在添加字段的最后添加 constraint 约束名 primary key(字段名,.....)
create table e (
sid int,
name varchar,
constraint ys primary key(sid,name) -- 写一个就是单列主键,多个就是联合主键
);
2.修改主键约束
利用DDL表修改操作完成 alter table 表名 add primary key(字段名,······);
3.删除主键约束
利用DDL表修改操作完成 alter table 表名 drop primary key;
-- 自增长约束
关键字; aotu_increment 第一个要写null
特点:只用于数值类的自增长,默认从1开始增长,也就是索引一样,每新增一条纪录,字段自动加1
1.添加自增长
由于必须让索引具有唯一性,经常和primary key搭配使用 primary key auto_increment
2.设置自增长的起始点
aotu_increment=起始点值
在创建表的最后)[表的一些设置里面添加];
利用DDL表修改操作完成,alter table 表名 aotu_increment=起始点值;
-- 搭配使用所以自动定位到主键约束
-- 非空约束
关键字; not null
特点:该列不能是空值,且对该行赋值时一定要对他赋值否则报错
1.添加非空约束
写在创建表的[约束条件]内
2.修改表DDL加约束条件
alter table 表名 modify 字段名 类型 not null;
-- 唯一约束
关键字:unique
特点:值不重复 (null空值不存在重复)
1.添加唯一约束
写在创建表的[约束条件]内
2.修改表DDL加约束条件
alter table 表名 add constraint 约束名 unique (字段名,.....);
3.删除唯一约束
alter table 表名 drop index 唯一约束名; -- 没有名默认列名就是唯一约束名
-- 默认约束
关键字:default 默认值
特点:给该字段列添加默认值,如果没写默认约束默认值就是null -- 手动>默认
1.添加非空约束
写在创建表的[约束条件]内
2.修改表DDL加约束条件
alter table 表名 modify 字段名 类型 default 值;
3.删除默认约束
alter table 表名 modify 字段名 类型 default null;
-- 零填充约束
关键字:zerofill
二、函数
-- 聚合函数
关键字:
- count() 统计该列有多少行 ---null忽略不计 2种情况
- sum() 计算该列的总数 --忽略null
- max() 该列最大值 --忽略null
- min() 该列最小值 --忽略null
- avg() 计算该列平均数 --忽略null(不对此有效除)
特点:聚合关键字可以一起使用用,隔开
例:
select max(price) from product;
select sum(price) from product where category_id='c200';
聚合函数在分组后的运行要搞清楚逻辑
count()
sum()
max()
min()
avg()
-- 合并函数
group_concat() -- 哥路噗
特点:将指定列的所有值合并成一行显示
格式:group_concat([distinct] 字段名 [order by 字段名 desc/asc] [separator '分割符']) 默认分隔符是 ,
-- 数学函数
abs()
特点:取绝对值
ceil() 1.1=2
特点:向上取整
floor() 1.1=1
特点:向下取整
greatest( , , ,....)
特点:取最大值的列
-- max和min只能针对一个列,且返回一个值
-- 而这两个是,,隔开的最大值的列,返回整个列的值
least( , , ,... )
特点:取最小值的列
round( , 保留小数点后几位 ) (2.13344,3)=2.133 (2.14455,3)=2.145
特点:小数四舍五入
truncate( , 保留小数点后几位 ) (2.13344,3)=2.133 (2.14455,3)=2.144
特点:不进行四舍五 入
mod( , ) (5,2)=1
特点:余数
rand() --可以在后面进行数学操作来扩大取值范围 rand()*4 就变成了0-4之间的随机小数
特点:取0-1之间的随机小数
power( x ,y )
特点:取x的y次方
-- 字符串函数
char_length() character_length()
特点:获取字符串长度(个数),汉字字母数值均为一个字符
length()
特点:获取字符串长度,但汉字一个字为3个字符
concat()
特点:无分隔符合并字符串为一行
concat_ws('分隔符' , ...)
特点:合并字符串为一行
-- 拼接函数
关键字: concat_ws( 分隔符 , 字段1 , 字段2 , ·····)
作用:将两个或多个字段里面的值拼接到一个字段
field( '查询的字符串' , ······ )
特点:返回字符串在列表中第一次出现的位置
ltrim()
特点:去除字符串左边的空格
rtrim()
特点:去除字符串右边的空格
trim()
特点:去除字符串所有的空格
mid( , 从第几个开始截取,截取几个) substr( , , )
特点:截取字符串
position( 'A' , 'B' )
特点:获取字符串A在字符串B中出现的位置
replace( , 哪个替换 , 替换什么)
特点:字符串替换
reverse()
特点:字符串反转
right( , 几个 )
特点:返回字符串后几个字符
strcmp( , )
特点:字符串比较,字母按照a-z排序比较,一个一个看,不看全部
upper()
ucase()
特点:小写转大写
lower()
lcase()
特点:大写转小写
-- 日期函数
unix_timestamp()
特点:获取从1970年1月1日0时0分0秒开始到指定时间的毫秒值,不写默认为现在
from_unixtime( , '%y-%m-%d %h:%i:%s' )
特点:将毫秒值转换为具体的时间值
curdate()
current_date()
特点:获取当前时间的年月日
curtime()
current_time()
特点:获取当前时间的时分秒
current_timestamp()
特点:获取当前时间的年月日时分秒
date()
特点:获取年月日
datediff( , )
特点:计算两时间的天数差值
timediff()
特点:计算两时间的秒数差值
date_format( , '%y-%m-%d %h:%i:%s' )
特点:将日期格式改为标准格式
date_sub( , interval 减什么)
特点:对时间进行年月日时分秒的减法
date_add( , interval 加什么)
特点: 对时间进行年月日时分秒的加法
extract(什么 from , )
特点:从时间中获取 (什么)年|月|日|时|分|秒|季
也可以简写
year() month() date() hour() minute() second() quarter()
last_day()
特点:获取给定时间对应月份的最后一天 2021-3-21 = 2021-3-31
makedate( 年 ,天 )
特点:获取给定年并从1月1号加上 给定天
monthname()
特点:获取月份的英文名称
dayname()
特点:获取周几的英文名称
dayofmonth()
特点:获取当月的第几天
dayofweek
特点:获取周几 1表示星期天以此类推
dayofyear()
特点:获取该年的第几天
week()
特点:获取给定日期为几周
now()
特点;获取当前时间的年月日时分秒
-- 控制流函数
一个数据则运行一次,若多个数据(表的字段)那么就运行多次,对应多个结果
if( 条件判断语句 , t值 , f值 )
特点;进行判断,满足条件返回t值,不满足返回f值
ifnull( f值 , t值 )
特点:判断是不是为null如果是null则返回t值,不是null返回f值
nullif( , )
特点:对两者值进行比较,如果相等则返回null,如果不相等返回第一个值
isnull()
特点:判断是不是null,如果是null则为1,不是null则为0
case [( )] when then ... else end
特点:case表示函数开始,end表示结束,当有一个结果成立后就不运行
例1:
select *,case (pay_type)
when 1 then '微信支付'
when 2 then '支付宝支付'
when 3 then '银行卡支付'
else '其他第三方支付' end;
例2:
select *,case
when pay_type=1 then '微信支付'
when pay_type=2 then '支付宝支付'
when pay_type=3 then '银行卡支付'
else '其他第三方支付' end;
-- 窗口函数
格式: 窗口函数 over(partition by 分组字段 order by 排序字段) from·····
排序是精华
序号窗口函数
row_number()
特点:强行对行进行编号,按照1234567....不管是不是并排相同
rank()
特点:对行进行编号,按照12245668..... 出现相同则并排但是会砍掉下一个序号
dense_rank()
特点:对行进行编号,按照1223456678..... 出现相同则并排但是不会砍掉下一个序号
1.开窗聚合函数
sum() avg() max() min()
特点:相比于普通聚合函数,原来的只有一行,而它实现了多行
计算顺序则是是从该组第一行到当前行, -- 前提有排序的情况下
若没有排序,则全部行显示该组的总数,也就是普通聚合函数,然后写满行
当然也可以修改从哪到哪的顺序 rows between 哪 and 哪;
current 当前 preceding 向前 3 preceding 当前行向前3行
分布窗口函数 操作对象是排序的字段
cume_dist()
特点:计算该组排序数据当前行小于等于该值的行数数/该组总行数
percent_rank()
特点:计算当前rank排序值-1/row(改组行数)-1
2,前后窗口函数
lag( 字段 , 第几个 ,默认值)
特点:将字段的当前行前第几个数据放到该行,如果没有(超出表的,则为null,如果设置了默认值则为默认值)
lead(字段 , 第几个 ,默认值)
特点:将字段的当前行后第几个数据放到该行,如果没有(超出表的,则为null,如果设置了默认值则为默认值)
3.头尾窗口函数
-- 如果不指定order by则会排序紊乱,出现错误的结果
first_value(字段)
特点:截至到当前行,将第一个(头)的数据放到该行
last_value(字段)
特点:截至到当前行,将最后一个(尾)的数据放到该行,一般当前行就是最后一个,相当于直接平移过来
4,其他窗口函数
nth_value(字段 ,名次)
特点:获取该组该排序字段当前行从上往下第几名的值,没有(超过了表)即为null
ntile(几组编号)
特点:将该组数据按几组编号 ,多了就重复
三、外键约束
-- 使多表形成联系的约束(外键约束)
关键字:foreign key() references
特点:受到主表主键列的限制,主键与外键形成关系 -- 外键列可以为空
使用格式
1.创表时:constraint 约束名 foreign key(外键列) references 主表名(主键列);
2.创表后:alter table 表名 add constraint 约束名 foreign key(外键列) references(主键列);
通过这个对 alter table 关键字修改表结构更好的理解了
-- 对外键约束的表添加数据注意事项
- 必须先给主表添加数据,再给从表
- 在给从表添加数据时,必须受到主表的限制(它有的你才能有,没有的你不能有,但null可以)
-- 对外键约束的表删除数据注意事项
- 主表的数据被从表依赖时,不能删除,没有依赖则可以删除
- 从表的数据随便删除没事
注意事项:修改和删除时,从表满足限制随便修改和删除,但两边的主表不能随便删除和修改
-- 删除外键约束
格式: alter table 表名 drop foreign key 约束名;
四、多表联合查询
-- 交叉连接查询
格式:select 字段 from 表1,表2,...; -- 表1和其他表哪个先写是表示哪个显示在前面罢了
查询特点: 具有笛卡尔积效应,因此会产生很多多余的数据,因此后期需要筛选一次
-- 内连接查询
关键字: A inner join B on 简写 A join B on -- 当然where也可以 on和where意思一样
特点: 取两个表(on条件设置)相同的地方查询 取两表交集
格式:select 字段名 from A表 inner join B on 。。。。。。。
-- 外连接查询
1.左外连接
左外连接关键字:
A left outer join B on -- 拉夫特
特点:以左边的表(A表)为主要与右边表连接 (就是把b能和a匹配的放查出来,a里有b给不了则为null)
例子:A表: 1 张三 男 B表: 数学 2
2 李四 男 英语 1
3 帅哥 女 地理 2
结果
1 张三 男 英语 1
2 李四 男 数学 2
2 李四 男 地理 2
3 帅哥 女 null null
3.右外连
右外连接关键字:
A right outer join B on
特点:以右边表(B表)为主要与左边表(A表)连接 与left相反原理一样
3.满外连接
满外连接关键字:
union all:将right 与left 直接连接在一起
格式:A left outer join B on union all A right outer join B on
union :将right 与left 连接在一起,--并去重
格式:A left outer join B on union A right outer join B on