SQL第2课:具有约束的查询(第1部分)
现在,我们知道了如何从表中选择特定的数据列,但是如果您的表具有一亿行数据,那么读取所有行将是低效率的,甚至是不可能的。
为了过滤返回的某些结果,我们需要WHERE
在查询中使用子句。通过检查特定的列值以确定是否应将其包含在结果中,将该子句应用于数据的每一行。
选择约束查询 SELECT column, another_column, … FROM mytable WHERE condition AND/OR another_condition AND/OR …;
可以通过连接大量AND
或OR
逻辑关键字(即num_wheels >= 4 AND doors <= 2)来构造更复杂的子句。以下是一些有用的运算符,可用于数字数据(即整数或浮点数):
Operator | Condition | SQL Example |
=, !=, < <=, >, >= | i标准数值运算符 | col_name != 4 |
BETWEEN … AND … | 数字在两个值(含)范围内 | col_name BETWEEN 1.5 AND 10.5 |
NOT BETWEEN … AND … | 数字不在两个值(包括两个值)的范围内 | col_name NOT BETWEEN 1 AND 10 |
IN (…) | 列表中存在号码 | col_name IN (2, 4, 6) |
NOT IN (…) | 列表中不存在该号码 | col_name NOT IN (1, 3, 5) |
***你知道吗?***
您可能已经注意到,SQL不需要您将所有大写的关键字都写成大写字母,但是作为一种约定,它可以帮助人们从列名和表名中区分出SQL关键字,并使查询更易于阅读。
练习:
使用正确的约束条件,从电影表中找到以下每个任务所需的信息。(自己动手练习一遍在看答案)
Table: Movies
Id | Title | Director | Year | Length_minutes |
1 | Toy Story | John Lasseter | 1995 | 81 |
2 | A Bug's Life | John Lasseter | 1998 | 95 |
3 | Toy Story 2 | John Lasseter | 1999 | 93 |
4 | Monsters, Inc. | Pete Docter | 2001 | 92 |
5 | Finding Nemo | Andrew Stanton | 2003 | 107 |
6 | The Incredibles | Brad Bird | 2004 | 116 |
7 | Cars | John Lasseter | 2006 | 117 |
8 | Ratatouille | Brad Bird | 2007 | 115 |
9 | WALL-E | Andrew Stanton | 2008 | 104 |
10 | Up | Pete Docter | 2009 | 101 |
11 | Toy Story 3 | Lee Unkrich | 2010 | 103 |
要求:
- Find the movie with a row
id
of 6 - Find the movies released in the
year
s between 2000 and 2010 - Find the movies not released in the
year
s between 2000 and 2010 - Find the first 5 Pixar movies and their release
year
答案:
1.select * from movies where id=6;
2.select * from movies where year between 2000 and 2010;
3.select * from movies where year not between 2000 and 2010;
4.select title,year from movies where id<=5;