SQL学习—SELECT

SELECT

我自己认为select就是SQL语言的核心，所以学习好SELECT基本上就掌握了SQL语言

1.SELECT 

SELECT name,genre,year
FROM movies;

2.AS

SELECT name AS 'movie',imdb_rating AS 'IMDb'
FROM movies;

3.DISTINCT

过滤重复值

SELECT DISTINCT genre,year 
FROM movies;

4.WHERE

SELECT * 
FROM movies 
WHERE year > 2014;

5.LIKE 

有点类似于正则

不区分大小写

a% 表示a开头

%a 表示以a结尾

_表示任一字符

SELECT * 
FROM movies
WHERE name LIKE 'Se_en';

SELECT * 
FROM movies
WHERE name LIKE 'The %';

6. Is Null 判断是否为空

SELECT name
FROM movies 
WHERE imdb_rating IS  NULL;

SELECT name
FROM movies 
WHERE imdb_rating IS NOT NULL;

 7.BETWEEN

数字 第二个计算

字符 第二个不算

！不包括J
SELECT *
FROM movies
WHERE name BETWEEN 'A' AND 'J';

!包括1979
SELECT *
FROM movies
WHERE year BETWEEN 1970 AND 1979;

 8.AND 

交集

SELECT * 
FROM movies
WHERE genre="horror"  AND year<1985;

 

9.OR

并集

OR can be used more than once

SELECT *
FROM movies
WHERE genre='romance'
OR	genre='comedy';


!More than once
SELECT *
FROM nomnom
WHERE neighborhood='Midtown'
OR neighborhood='Downtown'
OR neighborhood='Chinatown';

10.ORDER BY 

永远在WHERE后面

DESC 降序 

ASC 升序

SELECT *
FROM movies
ORDER BY name ASC;

SELECT name, year,imdb_rating
FROM movies
WHERE imdb_rating IS NOT NULL
ORDER BY imdb_rating DESC;

11.LIMIT

限制最大返回个数

总是在最后

SELECT *
FROM movies
ORDER BY imdb_rating DESC
LIMIT 3;

其他用法

select * from Customer LIMIT 10;--检索前10行数据，显示1-10条数据
select * from Customer LIMIT 1,10;--检索从第2行开始，累加10条id记录，共显示id为2....11
select * from Customer limit 5,10;--检索从第6行开始向前加10条数据，共显示id为6,7....15
select * from Customer limit 6,10;--检索从第7行开始向前加10条记录，显示id为7,8...16

12.Case

允许我们创建不同的输出

SELECT后面的属性记得要加‘，’因为后面还有一个自创的列

CASE里用WHEN 相当于WHERE

CASE必须以END结尾 END AS可以给一个列名

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