SQL Lesson 3: 条件查询(constraints) (Pt. 2)

我们已经学会了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;
Sorry but the SQLBolt exercises require a more recent browser to run.
Please upgrade to the latest version of Internet Explorer, Chrome, or Firefox!

Otherwise, continue to the next lesson: SQL Lesson 2: Queries with constraints (Pt. 1)
Table(表): movies
练习 do it — 请完成如下任务
我不会做求助!求助!

继续努力 学下一节

你获得一个机会,加微信进群,可以互相学习