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 '司机完单数量';