工作中使用的sql知识点及查询语法

文章内容，有些写的比较简单，是为了以后工作中再次使用时快速记忆，不明白的知识可以单独查询相关知识点或者下方留言哦。

只修改字段属性时用modify

ALTER TABLE stu MODIFY `name` VARCHAR(20); 

设置默认值

ALTER TABLE stu alter name SET DEFAULT 'test' 

删除默认值

alter table stu alter name DROP DEFAULT 

修改表名

ALTER TABLE stu RENAME to students 

修改字段位置

alter TABLE students MODIFY name varchar(20) AFTER cus_id 

查看创建表的语句

SHOW create table students

复制其他表的数据并插入到该表中

INSERT INTO 表名(id,cus_id,name,show_date,show_time) 
SELECT id,cus_id,name,show_date,show_time from students

完全复制表以及内容

create table 新表 like 源表;     -- 创建一个表
insert into 新表 select * from 源表  -- 复制全部的数据
或者
create table 新表 select * from 旧表 where ......

判断内容并显示 ：IF (xxx=xxx,True, False)

select 
SUM(IF (age between 26 and 30, settle_amount, NULL)) as '26~30',
-- 将年龄是26到30岁之间的人消费的金额进行求和计算
count(IF (age between 26 and 30, TRUE, NULL)) as '26~30',
-- 年龄是26到30岁之间的人进行统计有多少
case 字段名
when '41 then '河南省'
when '31' then '上海市'
. . . . . .
else '未知'
end as '省份'，
name，
age，
from 表名

累加计算(笛卡尔积) -->显示每一天的累加值

   多个表的行数相乘
select 
a.day, sum(b.num) 
from 
tab1 a, tab1 b 
where a.day >= b.day 
group by a.day desc 

多表连接 UNION / UNION ALL的区别

  MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。
  多个 SELECT 语句会删除重复的数据，只显示一个。
  但是如果显示两个字段名，其中有一个字段名的数据不一致，另一个字段的数据一致，也会全部显示，例如：

UNION ALL 不管多表之间的内容重复不重复，全部显示

但是也有特殊情况：

SELECT name, id  from stu WHERE name='xxx' 
	UNION ALL 
SELECT name, sex  FROM cus WHERE name='xxx' GROUP BY `name`

结果字段显示的是：
 1、第一个语句的查询字段，但是第二个查询语句显示的字段不是第一个查询语句的字段，
 2、只有一个name相同，会显示在id的这个字段中，但是值其实是sex的值，去重之后只显示一个name的值

全连接 full join

 完整外部联接返回左表和右表中的所有行。
  1、当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。
  2、如果表之间有匹配行，则整个结果集行包含基表的数据值。

内连接 inner join

取两个表内容的交集部分
select id,name from student1 inner join student2 
on student1.id = student2.id and (student1.name != 'null' or student2.name is not null)

根据身份证获取星座（case … when…end）

(case when substring(number,12,3)>=312 and substring(number,12,3)<=419 then '白羊座'
      when substring(number,12,3)>=420 and substring(number,12,3)<=520 then '金牛座'
      when substring(number,12,3)>=521 and substring(number,12,3)<=621 then '双子座'
      when substring(number,12,3)>=622 and substring(number,12,3)<=722 then '巨蟹座'
      when substring(number,12,3)>=723 and substring(number,12,3)<=822 then '狮子座'
      when substring(number,12,3)>=823 and substring(number,12,3)<=922 then '处女座'
      when substring(number,12,3)>=923 and substring(number,12,3)<=930 then '天枰座'
      when substring(number,11,4)>=1001 and substring(number,11,4)<=1023 then '天枰座'
      when substring(number,11,4)>=1024 and substring(number,11,4)<=1121 then '天蝎座'
      when substring(number,11,4)>=1122 and substring(number,11,4)<=1221 then '射手座'
      when substring(number,11,4)>=1222 and substring(number,11,4)<=1231 then '魔蝎座'
      when substring(number,12,3)>=101 and substring(number,12,3)<=119 then '魔蝎座'
      when substring(number,12,3)>=120 and substring(number,12,3)<=218 then '水瓶座'
      when substring(number,12,3)>=219 and substring(number,12,3)<320 then '双鱼座'
      else '未知' end) as  constellation,--星座

获取两个时间差

  timestampdiff(参数设置, 日期小的, 时间大的)
  参数设置有：year、day、hour、minute、second

最好保持两个时间都是相同的位数，如都是：年-月-日，年-月-日 时：分：秒等，
如果第一个日期有时分秒，第二个日期是年月日，想看的结果是相差的天数，那么相减之后就会少一天
SELECT
	date(create_at), CURDATE(),
	TIMESTAMPDIFF(DAY, date(create_at), CURDATE())
FROM
	big_numbers

SELECT
	create_at, CURDATE(),
	TIMESTAMPDIFF(DAY, create_at, CURDATE())
FROM
	big_numbers
	

窗口函数：取某一用户下多条数据的第一条或最后一条数据 row_number()

需求：查询最早日期的地址以及名称
MySQL错误查法：
SELECT name,MIN(create_at),address FROM big_numbers

  我们会发现，上面的查询时间虽然是最早的，但是明显与对应行的地址不相符，所以我要可以用这种方法查询

我们可以使用 row_number() over()

SELECT 
	name,
	create_at,
	address,
	row_number() over(partition by uid order by create_at) as num
FROM big_numbers 
先将uid分组，然后在根据时间升序（降序用desc），并且依次赋值每次+1，
然后我们在取第一条数据就可以了（where num= 1）

– 写法：

select * from
(SELECT 
	name, create_at, address,
	row_number() over(partition by uid order by create_at) as num
FROM big_numbers) t1 where num =1 -- 取最早日期的数据

partition by：根据uid分组
order by 降序或升序显示

更改类型格式（cast）

cast(字段名 as string/int) -- 将字段类型改为字符串或者数字的整型
number:身份证号（string）
-- 根据身份证获取年龄，变为数字然后相减得出年龄
select name, 2020 - cast (substring( number,  7,  4) as int ) as age from id_cards

时间戳变为日期：FROM_UNIXTIME

FROM_UNIXTIME( 时间戳)
FROM_UNIXTIME( unix_timestamp())  ----->>当前时间

日期变为时间戳：UNIX_TIMESTAMP

unix_timestamp（日期）