细枝末节

"_"示意任意一个字符
"%"示意任意多个任意字符,蕴含0个
"="不能够判断NULL值,"<=>"能够比拟NULL值
"<>"不能够判断NULL值
转义字符"\"能够在SQL中应用
ESCAPE关键字能够指定转移字符
BETWEEN AND蕴含两个临界值,且临界值不能颠倒
IN列表中的值类型必须统一

语法

SELECT   字段名FROM    表名WHERE    条件

执行程序

FROM 表名 ----> WHERE 条件 ----> SELECT 字段名

按条件表达式筛选

简略条件运算符

大于 &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &gt
小于 &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp <
等于 &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp = &nbsp &nbsp &nbsp &nbsp <=>
不等于 &nbsp &nbsp &nbsp &nbsp <> &nbsp &nbsp &nbsp !=
大于等于 &nbsp &nbsp &gt=
小于等于 &nbsp &nbsp <=

查问id大于10的用户名 ---> {">"}

SELECT    nameFROM    mytableWHERE    id > 10;

查问age小于30的用户id ---> {"<"}

SELECT    idFROM    mytableWHERE    age < 30;

查问name不等于Mike的用户age ---> {"<>"}

SELECT    ageFROM    mytableWHERE    name <> 'Mike';

按逻辑表达式筛选

逻辑运算符

与 &nbsp &nbsp & &nbsp &nbsp and
或 &nbsp &nbsp | &nbsp &nbsp or
非 &nbsp &nbsp ! &nbsp &nbsp not

查问age在18-30之间的用户的id和name ---> {"AND"}

SELECT    id,    nameFROM    mytableWHERE    age >= 18AND    age <= 30

查问薪水不在6000-8000之间,或者薪水小于3000的用户id ---> {"OR","NOT"}

# 第一种写法:SELECT    idFROM    mytableWHERE    salary < 6000OR    salary > 8000OR    salary < 3000;# 第二种写法:SELECT    idFROM    mytableWHERE    NOT(salary >= 6000 AND salary <= 8000)OR    salary < 3000;

含糊查问

运算符

LIKE
BETWEEN AND
IN
IS NULL
IS NOT NULL

查问名字中蕴含字母a的用户信息 ---> {"LIKE","%"}

SELECT    *FROM    mytableWHERE    name LIKE '%a%';

查问用户名第三个字符为c,第5个为d的用户id和age ---> {"_"}

SELECT    id,    ageFROM    mytableWHERE    name LIKE '__c_d%';

查问用户名第三个字符为"_"的用户id ---> {"\","ESCAPE"}

# 第一种写法:SELECT    idFROM    mytableWHERE    name LIKE '__\_%';# 第二种写法:SELECT    idFROM    mytableWHERE    name LIKE '__$_%' ESCAPE '$';

查问age在18-30之间的用户信息 ---> {"BETWEEN AND"}

SELECT    *FROM    mytableWHERE    age BETWEEN 18 AND 30;

查问age为18和20的用户信息 ---> {"IN"}

SELECT    *FROM    mytableWHERE    age IN(18,20);

查问没有奖金的用户信息 ---> {"IS NULL","<=>"}

# 第一种写法:SELECT    *FROM    mytableWHERE    commission IS NULL;# 第二种写法:SELECT    *FROM    mytableWHERE    commission <=> NULL;