细枝末节

1. “_”示意任意一个字符
2. “%”示意任意多个任意字符,蕴含0个
3. “=”不能够判断NULL值,”<=>”能够比拟NULL值
4. “<>”不能够判断NULL值
5. 转义字符”\”能够在SQL中应用
6. ESCAPE关键字能够指定转移字符
7. BETWEEN AND蕴含两个临界值,且临界值不能颠倒
8. 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
    name
FROM
    mytable
WHERE
    id > 10;

查问age小于30的用户id —> {“<“}

SELECT
    id
FROM
    mytable
WHERE
    age < 30;

查问name不等于Mike的用户age —> {“<>”}

SELECT
    age
FROM
    mytable
WHERE
    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,
    name
FROM
    mytable
WHERE
    age >= 18
AND
    age <= 30

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

# 第一种写法:
SELECT
    id
FROM
    mytable
WHERE
    salary < 6000
OR
    salary > 8000
OR
    salary < 3000;


# 第二种写法:
SELECT
    id
FROM
    mytable
WHERE
    NOT(salary >= 6000 AND salary <= 8000)
OR
    salary < 3000;

含糊查问

运算符

• LIKE
• BETWEEN AND
• IN
• IS NULL
• IS NOT NULL

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

SELECT
    *
FROM
    mytable
WHERE
    name LIKE '%a%';

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

SELECT
    id,
    age
FROM
    mytable
WHERE
    name LIKE '__c_d%';

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

# 第一种写法:
SELECT
    id
FROM
    mytable
WHERE
    name LIKE '__\_%';


# 第二种写法:
SELECT
    id
FROM
    mytable
WHERE
    name LIKE '__$_%' ESCAPE '$';

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

SELECT
    *
FROM
    mytable
WHERE
    age BETWEEN 18 AND 30;

查问age为18和20的用户信息 —> {“IN”}

SELECT
    *
FROM
    mytable
WHERE
    age IN(18,20);

查问没有奖金的用户信息 —> {“IS NULL”,”<=>”}

# 第一种写法:
SELECT
    *
FROM
    mytable
WHERE
    commission IS NULL;


# 第二种写法:
SELECT
    *
FROM
    mytable
WHERE
    commission <=> NULL;