数据库sql

数据库sql查询时间

date(create_time)='2021-01-25' 会算当前日期

created_time BETWEEN '2021-07-01' AND '2021-07-30'

create_time > '2021-10-1' and create_time < '2021-12-30'  // 如果日期后面有时分秒，这样格式不会算当前日期

EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。
year --> 代表单独取年份 MONTH–>代表单独取月份 data–>代表单独取日份
EXTRACT(year FROM create_time)

同一张表中，查询出来的数量能当作条件使用
select (2-1) from a
SELECT ((SELECT COUNT() FROM corp_external_contact WHERE corp_id = ‘wwfd4ecb2b0e96b13d’ AND date(create_time) = ‘2021-01-27’) - (SELECT COUNT() FROM corp_external_contact WHERE corp_id = ‘wwfd4ecb2b0e96b13d’ AND date(create_time) = ‘2021-01-28’)) as a FROM corp_external_contact GROUP BY a

我们如何仅仅选取在 “Address” 列中带有 NULL 值的记录呢？
我们必须使用 IS NULL 操作符：

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

当一张表中的字段的value值显示的是数字，when后面是数字then后面是备注的信息
renovation_status int 改厕意愿: 1.愿意改厕 2.不愿改厕 3.不用改厕
1
2
3

SELECT householder,case renovation_status 
when '1' then '愿意改厕'
when '2' then '不愿意改厕'
else '不用改厕' end renovation_status
FROM resident_household
愿意改厕
不愿改厕 
不用改厕			

 select userid,case
    -> when salary<=1000 then 'low'
    -> when salary=2000 then 'med'
    -> when salary>=3000 then 'high'
    -> else '无效值' end salary_grade
    -> from salary_tab;

只有两个注解的话
renovation_status 的值为 TRUE，则返回值为 愿意改厕
renovation_status 的值为FALSE，则返回值为 不愿改厕

SELECT IF(renovation_status='1','愿意改厕','不愿改厕' )
FROM resident_household
'愿意改厕'
'不愿改厕'

COUNT() 函数返回匹配指定条件的行数：

count(distinct job)–distinct(去重）

SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name;

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

有条件查询，这样可以查询多个 用于统计数量

select deptno,
count(1) 总人数,
count(case when job ='SALESMAN' then '1' end) 销售人数, （1为固定值）
count(case when job ='MANAGER' then '1' end) 主管人数
from emp
group by deptno;--如果不group，会认为所有数据是一组，返回一个数据
SELECT count(case when household_type != '7' then '1' end) 总户数,count(*),count(case when household_type in (1,2,3,7) then '1' end) 改厕基数 FROM renovation

count( CASE WHEN toilet_type = '68' or toilet_status = '9' THEN '1' END ) 无害化厕所总数

in 后面可以接查询语句
SELECT CODE FROM department_area WHERE pcode in (select code 
FROM department_area WHERE pcode='420204000000' GROUP BY code)

金额分转元，并保留2位小数
truncate(sum(acc.money)/100,2	

COUNT 里面加时间查询

COUNT(CASE WHEN company_id = '10' and DATE(created_time) ='2022-07-13' THEN 1	END ) 今日

查询年龄并保留2为小数

SELECT COUNT(case when a >='70' then '1' end ) '70',
COUNT(case when a <= '17' then '1' end ) '17',
COUNT(*),
cast(((COUNT(case when a >='70' then '1' end ) /(COUNT(*)) ) *100) as decimal(18,2)) as 70占比,
cast((COUNT(case when a <= '17' then '1' end ) / COUNT(*) *100) as decimal(18,2)) as 17占比
FROM (SELECT  TIMESTAMPDIFF(YEAR, birthday, CURDATE()) a FROM resident_identity) tj

count函数相除，并保留2位小数

SELECT COUNT(case when gender ='男' then '1' end ) 男,COUNT(case when gender ='女' then '1' end ) 女,
cast( ( COUNT(case when gender ='男' then '1' end ) / COUNT(*) * 100 ) as decimal(18,2)) 男占比,
cast( ( COUNT(case when gender ='女' then '1' end ) / COUNT(*) * 100 ) as decimal(18,2)) 女占比
FROM resident_identity 

count函数去重时多条件查询

1.
SELECT 
count(DISTINCT userid, CASE WHEN sex = 3 THEN 1 END) 未知,
FROM today_discount_log WHERE time BETWEEN '2023-05-04' AND '2023-05-10' 
2.
SELECT 
count(DISTINCT CASE WHEN sex = 1 THEN userid END) 男
FROM today_discount_log WHERE time BETWEEN '2023-05-04' AND '2023-05-10' 

查询数据为空或者空字符串的写法

SELECT *  FROM book where id is null and id = ''

字符串查询

select * from dede_archives where FIND_IN_SET('1',title) 

多表删除

表不能用别名
DELETE resident_identity,resident_household FROM  resident_identity  INNER JOIN resident_household  
ON resident_identity.household_id = resident_household.id WHERE  resident_household.village_code = '411224001001' AND resident_household.household_type = '7' 

多表查询

SELECT SUM(other)其他用地面积 FROM land_acreage  a 
INNER JOIN farm  b ON a.farm_id = b.id AND b.city_code = 420100000000 
INNER JOIN farm_status c  ON a.farm_id = c.farm_id  

查询交集之外的数据
用左连接，并且加上左表没有右表唯一的字段做条件

SELECT * FROM resident a LEFT JOIN web_user b ON a.householder_identity = b.identity WHERE openid IS NULL