sql

查询

2题(null)

id is NULL

3题(逗号问题,union用法)

select w.name, w.population, w.area from world w
	where w.population>=25000000 or w.area>=3000000
 
select w.name, w.population, w.area from world w
    where w.population>=25000000
    union
select w.name, w.population, w.area from world w
    where w.area>=3000000
 

多个查询结果时使用逗号
使用 or 会使索引会失效，在数据量较大的时候查找效率较低

4题(结果的别名,groupby去重,asc)

 
select t.author_id as 'id' from views t 
    where author_id = viewer_id
    group by author_id
    order by t.author_id asc

5题(char_length(content))

 
select t.tweet_id from tweets t 
    where char_length(content)>15
 

连接

1题

-- select b.unique_id, a.name from employees a, employeeUNI b
--     where a.id = b.id
-- 以上错误
 
select employeeUNI.unique_id,employees.name from employees
    left join employeeUNI
        on employees.id = employeeUNI.id

多表连接查询时,使用left join ---on(where) ---

3题(连接后的图,where筛选null)

熟悉左连接后的数据表,on:连接的条件桥梁,后面可跟where条件筛选,用group by去重

• left join 保左全,会出现null值

 
select a.customer_id, count(a.customer_id) as count_no_trans from visits a
    left join transactions b 
    on a.visit_id=b.visit_id
    where transaction_id is NULL
    group by customer_id;
 

4题(自连接,(inner)join ,求交集)

• datediff(a,---,b.---)

 
select a.id from weather a
    join weather b 
    on datediff(a.recordDate,b.recordDate)=1
    where a.temperature>b.temperature
 
 

5 题(round与avg)

 
select a1.machine_id, round(avg(a2.timestamp-a1.timestamp),3) processing_time from activity a1
    join activity a2 
    on a1.machine_id=a2.machine_id
        and a1.process_id = a2.process_id
        and a1.activity_type='start'
        and a2.activity_type='end'
    group by a1.machine_id