mysql高级语法

if判断语句

例:select *,if(age>20,’符合’,’不符合’) as judge from stu;满足条件使用“符合”的值

多表连接

内连接：左右连接的交集
select * from talbeA  A inner join tableB B on A.ID=B.ID inner join tableC C on B.NUM=C.NUM;

三表连接另一种写法：
select * from tableA A inner join (tableB B,tableC C) on A.ID=B.ID AND B.NUM=C.NUM;


左连接: 得到的是左边A表的所有数据，B没有数据则置为空;
右连接: 得到的是右边B表的所有数据，A没有数据则置为空;
select A.name,B.* from
test_name A 
left(right) join 
test_detail B
on A.id=B.id;

求并集
select oname,odesc FROM object_a
UNION
select oname,odesc FROM object_b;

另一种写法
select a.S# from (select S#,score from SC where C#='001')a, (select s#,score from SC where c#='002')b Where a.score>b.score and a.s# = b.s#;

 创建数据库视图

语法: create view 视图名称 as select 语句;
相当于多表接连后创建个视图表，可以用查询表的语句执行视图，基表数据修改，视图数据会修改，反过来就不一定。
create view student_score as 
select student.*,score.c_name,score.grade from 
student,score where student.id=score.stu_id;


可使用视图名称进行sql查询
select * from student_score ;

删除视图:drop view student_score;

存储过程--基本语法

基本语法：		

delimiter // #双斜杠表示自己定义的结束符		
create procedure demo2_in(n int) #demo2_in表示自定义的存储过程名,多个参数用逗号隔开，后面不用加分号。
begin # 开头
select * from  a; #编写sql，或者其他语法
end // #结尾，以定义的//结束
delimiter ; #分号不能忘，表示重新把sql的结束符还回来
call demo2_in(190);  #调用存储过程

drop procedure demo2_in; #删除存储过程


例：
delimiter //
create procedure demo2_in(n int)	
begin
select * from actor where actor_id>n;   
insert into students values(a,"变量测试a",100);
end//
delimiter ; 
call demo2_in(190);  

 存储过程 -- if语句

语法：
if 条件 then
执行语句;
else 
执行语句;
end if;

例：
delimiter //
create procedure show_data_all(isAll int)
begin
if  isAll = 1  then
select  *  from  students;
else
select  *  from students limit 3;
end if;
end
//
delimiter ;
call show_data_all(0);调用

存储过程 -- case语句

当有很多if语句时，就可以考虑使用case语句，它是多分支选择语句，类似于if  ,elif.
语法:
case 变量
when 具体数值1 then
执行语句1;
when 具体数值2 then
执行语句2;
when 具体数值3 then
执行语句3;
else
上述值都没有时，执行语句4;
end case;

例：
delimiter //
create procedure show_table(n int)
begin
case   n
when 0 then   #当n=0时
select * from student;
when 1 then	#当n=1时
select * from  score;
else
select * from students_info;
end case;
end
//
delimiter ;
call show_table(1);

存储过程 -- while语句 

语法:
while 条件 do
执行语句
end while;

例：往test_name 表中插入数据

delimiter //
create procedure _while(n int)
begin
declare i int default 0;  #定义一个变量i，默认值为0
while i<n do
insert into test_name values(2000+i,"数据测试");
set i=i+1; #set，让i的赋值生效
end while;    
select * from test_name;
end//
delimiter ;
call  _while(10) ;

 时间函数

select now();
返回结果：
2022-03-08 16:49:18

select UNIX_TIMESTAMP(NOW()); #当前时间时间戳
select UNIX_TIMESTAMP('2022-08-08 00:00:00');
select UNIX_TIMESTAMP('2022-08-08 00:00:00')*1000;
返回结果：
1646729424
1659888000
1659888000000

select FROM_UNIXTIME(1646729424) #时间戳转化为时间格式，FROM_UNIXTIME只支持11位，有超长时需要要除以1000
select FROM_UNIXTIME(1646729424,'%Y-%m-%d %H%i%m')
返回结果：
2022-03-08 16:50:24

select timestamp(date_add(curdate(), interval - 0 day)) # 当天0点
返回结果：
2022-03-08 00:00:00

SELECT date_format(NOW(),'%Y-%m-%d') ;
SELECT curdate();   #得到当天的日期
返回结果：
2022-03-08

select date_sub(curdate(),interval 1 day)#得到昨天的日期
返回结果：
2022-03-07

select timestamp(date_add(curdate(), interval - day(curdate()) + 1 day)) #当月1号0点
返回结果：
2022-03-01 00:00:00

#上个月的第一天
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now())-1 day),interval 1 month)


#上个月的最后一天：
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now()) day),interval 0 month) as date


#这个月的第一天：
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(
day from now())-1 day),interval 0 month)

#获取当月最后一天
select last_day(curdate())；

 修改字段类型

该字段没数据的情况：
alter table student modify(id number(4)); ---将student表中id字段改为number，长度4   
alter table student modify(id number(4),studentName varchar2(100));

有数据的情况：
--修改原字段名  
ALTER TABLE 表名 RENAME COLUMN 字段名 TO 字段名1;
--添加一个和原字段同名的字段  
ALTER TABLE 表名 ADD 字段名 VARCHAR2(30);    
--将原来的数据更新到新字段中,这进里要注意，一定要显示进行数据类型转换（不同于MssQL）  
UPDATE 表名 SET 字段名 = CAST(字段名1 AS VARCHAR2(30));
--删除原来的备份字段  
ALTER TABLE 表名 DROP COLUMN 字段名1;

查看当前用户表字段类型

select TABLE_NAME,column_name,data_type,DATA_LENGTH from user_tab_columns 
where table_name=upper('表名') 

查看表注释和字段注释

select * from user_tab_comments; //查看当前用户下所有表注释
select * from user_col_comments where TABLE_NAME='某表名称';//查看当前用户下某表所有字段注释

拼接字段

SELECT CONCAT('44058219','abc'); # 直接拼接
SELECT CONCAT_WS('-','First','Second','Last'); # 以“-”将所有字符拼接起来

查看当前登录用户所有数据库的所有表名

select * from information_schema.tables;

替换字符

update Scores set date = REPLACE (指定字段,"指定字段的某个符号","修改后的符号");

case语句，根据不同值显示不同内容

select driver_id,FROM_UNIXTIME(online_minute_node/1000,'%Y-%m-%d %H:%i:%s') as "上班时间",
case 
when node_status = -1 then "null"
when node_status = 0 then "停止接单"
when node_status = 100 then "正常听单；计入时长"
when node_status = 101 then "栏外听单"
when node_status = 102 then "城外听单"
when node_status = 103 then "回家单听单"
when node_status = 220 then "已派单；计入时长"
when node_status = 221 then "栏外已派单；"
when node_status = 222 then "城外（栏内城外）已派单"
when node_status = 223 then "回家单已派单"
when node_status = 230 then "开始计费；计入时长" 
else "未知状态" 
end as "上报节点状态"
from driver_online_time_node_49 where driver_id = 7838513

json字段解析后提取值

mysql5.7以上版本

内置函数JSON_EXTRACT(column, '$.key')，column代表json列的列名；key代表json字符串中的某一个key。

SELECT JSON_EXTRACT('{"priceTag":"员工","priceDiscount":"90"}', '$.priceDiscount') AS '定价折扣';

//数组嵌套
//返回值为：[{"cityCode":"440100","inviteeSetting":{"carAssignAward":20,"carAssignCountDays":14,"driverOrderAward":20,"driverOrderCnt":1,"driverOrderCountDays":1}}]

SELECT @json:=`setting_dependence_city_list` from driver_invite_activity where setting_dependence_city_list is not null limit 1; 

SELECT JSON_EXTRACT(@json ,'$[0].inviteeSetting.driverOrderCountDays') AS '司机完单数量';