SQL Lesson 3: 条件查询(constraints) (Pt. 2)
《SQL必知必会》《SQL基础教程》《Mysql必知必会》领取pdf
入门视频(点我)
我们已经学会了WHERE 语句来筛选数字类型的属性，如果属性是字符串, 我们会用到字符串相关的一些操作符号，其中 LIKE（模糊查询） 和 %（通配符） 是新增的两个.
下面这个表格对字符串操作符有详细的描述:
| Operator（操作符） | Condition（解释） | Example（例子） |
| = | Case sensitive exact string comparison (notice the single equals)完全等于 | col_name = "abc" |
| != or <> | Case sensitive exact string inequality comparison 不等于 | col_name != "abcd" |
| LIKE | Case insensitive exact string comparison 没有用通配符等价于 = | col_name LIKE "ABC" |
| NOT LIKE | Case insensitive exact string inequality comparison 没有用通配符等价于 != | col_name NOT LIKE "ABCD" |
| % | Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符，代表匹配0个以上的字符 | col_name LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS") "%AT%" 代表AT 前后可以有任意字符 |
| _ | Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似，代表1个字符 | col_name LIKE "AN_" (matches "AND", but not "AN") |
| IN (…) | String exists in a list 在列表 | col_name IN ("A", "B", "C") |
| NOT IN (…) | String does not exist in a list 不在列表 | col_name NOT IN ("D", "E", "F") |
小贴士?
在字符串表达式中的字符串需要用引号 " 包含，如果不用引号，SQL会认为是一个属性列的名字，如：col_name = color 表示 col_name和color两个属性一样的行 col_name = "color" 表示 col_name 属性为字符串 "color"的行.
练习
下面再把 WHERE 语法重复一遍，你可以对照上面的表格，来尝试完成本节课的练习.
Select 查询语法复习
SELECT column, another_column, …
FROM mytable
WHERE condition
AND/OR another_condition
AND/OR …;
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 | Finding Nemo | 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 |
| 12 | Cars 2 | John Lasseter | 2011 | 120 |
| 13 | Brave | Brenda Chapman | 2012 | 102 |
| 14 | Monsters University | Dan Scanlon | 2013 | 110 |
xxxxxxxxxx1
--请输入sql2
SELECT * FROM movies;
练习 do it — 请完成如下任务
-
【复杂条件】找到所有
Toy Story系列电影 -
【复杂条件】找到所有
John Lasseter导演的电影 -
【复杂条件】找到所有不是
John Lasseter导演的电影 -
【复杂条件】找到所有电影名为
"WALL-"开头的电影 - 【复杂条件】有一部98年电影中文名《虫虫危机》请给我找出来
