sql倒序查询语句_SQL丨1.基本查询语句复习

此为自用

查询语句

1.select

SELECT column1,column2 FROM table1;

常用的格式惯例：

大写了SELECT和FROM，而将表名和列名小写；

通常在列名中使用下划线，避免使用空格；

在每个语句末尾添加分号；

SQL不区分大小写。

2.limit

SELECT name,director FROM movies 
LIMIT 15;

3.order by

SELECT name, FROM,film_year,ticket_price FROM movies 
ORDER BY film_year DESC,ticket_price DESC
LIMIT 5;

DESC是倒序

4.where

用在order by之前

SELECT name,film_year FROM movies
WHERE name = '哈利波特'
ORDER BY film_year;

5.派生列

假如表movies里没有列count、country

SELECT film_year,ticket_price,
       film_year/ticket_price AS count
       'England'AS country
FROM movies
WHERE name = '哈利波特'
ORDER BY film_year
LIMIT 5;

逻辑运算符

1.like.

SELECT name FROM movies
WHERE name LIKE '%哈利%';

2.in.

SELECT name,director FROM movies
WHERE name IN ('哈利波特','魔戒');

3.not.

SELECT name FROM movies
WHERE name NOT LIKE '哈利%';

SELECT name,director FROM movies
WHERE name NOT IN ('哈利波特','魔戒');

4.and 与 between

SELECT name, director FROM movies
WHERE film_year = 2000 and name = '哈利波特';

SELECT name,director FROM movies
WHERE film_day NOT BETWEEN 2000-01-01 AND 2010-01-01;

between包含端点值，但对于时间2010-01-01是2010-01-01 0:00:00

5.or.

SELECT name,director FROM movies
WHERE (film_year = 2000 OR film_year = 2010) AND name = '哈利波特'
ORDER BY film_year;