SQL语句（二）

• SELECT

可以用来查看具体的某一列 

SELECT  column1 FROM  celebs；

• AS

对列进行重命名

 SELECT name AS 'Titles' FROM movies;

• DISTINCT

出现的数据只保留一次

SELECT DISTINCT year
FROM movies;

• LIKE

选取以Se开头，en结尾的部分

SQL 用到的正则化符号：'%'， '_';

%:至少一个符号的省略，两头省略

'-':中间省略

select *
from movies
where name LIKE 'Se_en';  

• IS NULL

select name
from movies
where imdb_rating is null; 

• IS NOT NULL

SELECT name FROM movies WHERE imdb_rating IS NOT NULL;

• between  ...and

SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';
select * 
from movies
where year between 1990 and 1999;

BETWEEN two letters is not inclusive of the 2nd letter.

BETWEEN two numbers is inclusive of the 2nd number.

• AND 条件

包含多个筛选，同时成立才输出

select * 
from movies
where year between 1970 and 1979
and imdb_rating >8;

select * 
from movies
where year<1985
and genre='horror';

• or 

满足一个条件即输出

select *
from movies
where year>2014
or genre='action';

select *
from movies
where genre='romance'
or genre='comedy';

• order by ：默认 asc,存在where 时，放在其后面

desc：从高到低

asc:从低到高

select name,year,imdb_rating
from movies
order by imdb_rating desc;

• limit 选取符合筛选条件的有限的行

select *
from movies
order by imdb_rating  desc
limit 3;

• case

SELECT name,
 CASE
  WHEN genre= 'romance' THEN 'Chill'
  WHEN genre='comedy' THEN 'Chill'
  ELSE 'Intense'
 END AS 'Mood'
FROM movies;