SQL进阶

自学SQL

sql自学笔记lseeon6---

SQL学习笔记-lesson12

 

count( distinct 字段）可以用

lesson 11第4题：

SELECT Role,case when building is null then "无"

else "有"    end as 有无办公室

,count(Name) as num

FROM employees

group by role,有无办公室

lesson11第5题：

select Role ,

case when Years_employed < 3 then "0-3"

     when Years_employed < 6 then "3-6"

else "6-9" end as 就职年份

,count(name)

from employees

group by Role,就职年份

创建新表

Create table new_table(select *,Domestic_sales+International_sales as total_sales,

Domestic_sales+International_sales/Length_minutes as per_value

from movies inner join boxoffice);

临时表的使用：182. 查找重复的电子邮箱

select Email from

(

  select Email, count(Email) as num

  from Person

  group by Email

) as statistic   #临时表

where num > 1;

having字句

select Email

from Person

group by Email

having count(Email) > 1;

交换性别

动态设置：

UPDATE salary

SET

    sex = CASE sex

        WHEN 'm' THEN 'f'

        ELSE 'm'

    END;

进行编码：

SELECT *,CASE WHEN population< 100 THEN '01'

          WHEN population >= 100 AND population < 200 THEN '02'

          WHEN population >= 200 AND population < 300 THEN '03'

          WHEN population >= 300 THEN '04'

ELSE '其他' END AS FENQU

FROM jinjie.poptbl;

-- 把县编号转换成地区编号(2) ：将CASE 表达式归纳到一处

SELECT CASE pref_name

WHEN '德岛' THEN '四国'

WHEN '香川' THEN '四国'

WHEN '爱媛' THEN '四国'

WHEN '高知' THEN '四国'

WHEN '福冈' THEN '九州'

WHEN '佐贺' THEN '九州'

WHEN '长崎' THEN '九州'

ELSE '其他' END AS district,

SUM(population)

FROM PopTbl

GROUP BY district;