(个人总结更新,mysql)常用SQL语句介绍:
①博主习惯小写;②test_table:数据库表;③column_name:字段;④str:字符串等类型数据
1、创建库表
create table test_table (colnum_name1 varchar(50),colnum_name2 datatime)
2、删除库表
drop table test_table
3、select 查询字段数据:
select * from test_table
select column_name1,column_name2 from test_table
4、where 筛选数据:
select * from test_table where column_name='str'
select * from test_table where column_name!='str'
5、筛选为空/不为空的记录:
为空的记录:select * from test_table where column_name is null
不为空的记录:select * from test_table where column_name is not null
6、delete 删除数据记录:
delete from test_table where column_name='str'
7、insert 增加数据记录:
insert into test_table (column_name1,column_name2) values('str1','str2')
8、update 修改字段数据:
update test_table set column_name1='str1' where column_name2='str2'
update test_table set column_name1='str1',column_name2='str2' where column_name3='str3'
9、replace 替换字段数据中某个字符串:
update test_table set column_name=replace(column_name,'str1','str2')
10、join 数据库表关联:
select a.*,b.* from test_table1 left join test_table2 on table1.column_name1=table2.column_name2
select a.*,b.* from test_table1 right join test_table2 on table1.column_name1=table2.column_name2
select a.*,b.* from test_table1 inner join test_table2 on table1.column_name1=table2.column_name2
11、and/or/in条件查询:
交叉条件(and):select * from test_table where column_name1='str1' and column_name2='str2'
并列条件(or):select * from test_table where column_name1='str1' and column_name2='str2'
多条件(in):select * from test_table where column_name in('str1','str2')
12、distinct 字段数据去重:
select distinct column_name from test_table
13、group by 按字段数据分组:
select * from test_table group by column_name
分组求和:select c_type,sum(c_total) as c_total from test_table group by c_type
分组计数:select c_type,count(*) as c_cnt from test_table group by c_type
分组计数(字段数据不为空):select count(column_name1) as column_name1from test_table group by column_name2
14、order by 按字段数据排序:
select * from test_table order by column_name(顺序/升序)
select * from test_table order by column_name desc(逆序/降序)
优先级问题: where 优先于 group by,group by 优先于 order by
15、获取日期(下列时间都可用于where条件,最后又一个where条件演示):
按年月日查询的:
当前时间:select now()
当前日期:select date(now())
当前年月:select left(now(),7)
当前月份:select month(now())
当前年份:select year(now())
当前日期前7天:select date_add(now(),interval -7 day)
当前日期前30天:select date_add(now(),interval -30 day)
当前日期前一个月(详细时间):select date_add(now(),interval-1 month)
当前日期前一个月(年月日):select date(date_add(now(),interval-1 month))
当前日期前一个月(年月):select left(date_add(now(),interval-1 month),7)
当前日期前一个月(月份):select month(date_add(now(),interval-1 month))
当前日期前一个月(月份):select month(date_add(now(),interval-1 month))
当前日期三个月数据:
select c_id,c_time from test_table where c_time <date(now()) and c_time >date(date_add(now(),interval-3 month))
日期比较(日期加减):
日期比较(大-小):select datediff(date(now()),mid("DQSC_20190714002754",6,8))
按年月查询的:
参数为年月,如“2019-06”,
获取上一个月“2019-05”:select left(date(date_add('${para}-01',interval-1 month)),7)
按博主自己测试的总结,sql的年月增减是按照完整的年月日来执行的,如果需要按年月查询时,需要将年月改为年月日的模式(在年月后再拼接日期如“-01”),才可以进行年月的增减,以下是一个例子,其他场景大家可以根据自己的情况做延伸。
select * from (select '5月数据' as name,'2019-05' as sj union all
select '6月数据' as name,'2019-06' as sj union all
select '7月数据' as name,'2019-07' as sj) as test
where sj=left(date(date_add('${para}-01',interval-1 month)),7)
16、带参数的sql查询:
默认:select * from test_table
参数名:para
参数查询(夹杂了单引号和双引号,可复制到文本编辑器查看):
①等于条件查询:select * from test_table where column_name ='${para}'
②参数为空查询所有:select * from test_table where 1=1 ${if(len(para)==0," "," and column_name='"+para+"'")}
③ in条件查询:select * from test_table where column_name in ('${replace(para,",","','")}')
④ in条件为空查询所有:select * from test_table where 1=1 ${if(len(para)==0,"","and column_name in ("+concatenate("'",replace(para,",","','"),"'")+") ")}
17、length 长度
select column_name,len(column_name) as str_len from test_table
select * from test_table where len(column_name)>7
18、group_concat 分组后组内数据拼接
select group_concat(c_province,c_city) from tablename group by c_province,c_city
分组后组内数据排序拼接:
select group_concat(c_province,c_city order by c_province) from tablename group by c_province,c_city
select group_concat(c_province,c_city order by c_province desc) from tablename group by c_province,c_city
19、索引:
创建索引:create index id from tablename(c_id)
创建多字段索引:create index id from tablename(c_id,c_name)
查看索引:show index from tablename
删除索引:drop index id on tablename
20、查看版本信息
SHOW VARIABLES LIKE '%version%'