本文主要是介绍【笔记四】：LIKE，UNION，ORDER BY，AND，OR，IN，BETWEEN，IS NULL，NOT，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
目录
1，LIKE
2，ORDER BY
3，UNION
4，AND
5，OR
6，IN
7，BETWEEN AND
8，IS NULL
9，NOT
1，LIKE
以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：
SELECT field1, field2,...fieldN
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'- %：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
- _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
- []：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
- [^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
- 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询
MariaDB [mydb]> select * from students;
+-------+-----------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+-----------+------+------+----------+------------+
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20003 | 小张 | 男 | 15 | painting | 2005-08-14 |
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
| 20005 | 丽丽 | 女 | 15 | painting | 2005-12-01 |
| 20005 | 李明 | 男 | 16 | music | 2004-08-14 |
| 20006 | 张大明 | 男 | 18 | football | 2002-03-05 |
+-------+-----------+------+------+----------+------------+
7 rows in set (0.001 sec)选择birthday 是03月份的学生
MariaDB [mydb]> select * from students where birthday like '%03%';
+-------+-----------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+-----------+------+------+----------+------------+
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20006 | 张大明 | 男 | 18 | football | 2002-03-05 |
+-------+-----------+------+------+----------+------------+
2 rows in set (0.000 sec)选择name是2个字且最后一个字是‘明’的学生
MariaDB [mydb]> select * from students where name like '_明';
+-------+--------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+--------+------+------+----------+------------+
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20005 | 李明 | 男 | 16 | music | 2004-08-14 |
+-------+--------+------+------+----------+------------+
2 rows in set (0.002 sec)
选择除了name是2个字且最后一个字是‘明’以外的学生
MariaDB [mydb]> select * from students where birthday not like '%03%';
+-------+--------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+--------+------+------+----------+------------+
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20003 | 小张 | 男 | 15 | painting | 2005-08-14 |
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
| 20005 | 丽丽 | 女 | 15 | painting | 2005-12-01 |
| 20005 | 李明 | 男 | 16 | music | 2004-08-14 |
+-------+--------+------+------+----------+------------+
5 rows in set (0.002 sec)2，ORDER BY
按age升序、降序排列
SELECT field, field2,... [or column] FROM table_name, table_name2,...
ORDER BY field, field2,... ASC[or DESC]默认是按升序排列 [ASC]
MariaDB [mydb]> select * from students order by age;
+-------+-----------+------+------+------------+------------+
| id | name | sex | age | interest | birthday |
+-------+-----------+------+------+------------+------------+
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20003 | 小张 | 男 | 15 | basketball | 2005-08-14 |
| 20005 | 丽丽 | 女 | 15 | painting | 2005-12-01 |
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20005 | 李明 | 男 | 16 | music | 2004-08-14 |
| 20006 | 张大明 | 男 | 18 | football | 2002-03-05 |
+-------+-----------+------+------+------------+------------+
7 rows in set (0.000 sec)指定age按降序排列。
MariaDB [mydb]> select * from students order by age desc;
+-------+-----------+------+------+------------+------------+
| id | name | sex | age | interest | birthday |
+-------+-----------+------+------+------------+------------+
| 20006 | 张大明 | 男 | 18 | football | 2002-03-05 |
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20005 | 李明 | 男 | 16 | music | 2004-08-14 |
| 20003 | 小张 | 男 | 15 | basketball | 2005-08-14 |
| 20005 | 丽丽 | 女 | 15 | painting | 2005-12-01 |
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
+-------+-----------+------+------+------------+------------+
7 rows in set (0.000 sec)
先指定age按升序排列如果age相同brithday再按降序排列。
MariaDB [mydb]> select * from students order by age, birthday desc;
+-------+-----------+------+------+------------+------------+
| id | name | sex | age | interest | birthday |
+-------+-----------+------+------+------------+------------+
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20005 | 丽丽 | 女 | 15 | painting | 2005-12-01 |
| 20003 | 小张 | 男 | 15 | basketball | 2005-08-14 |
| 20006 | 李明 | 男 | 16 | music | 2004-08-14 |
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20007 | 张大明 | 男 | 18 | football | 2002-03-05 |
+-------+-----------+------+------+------------+------------+
7 rows in set (0.001 sec)
3，UNION
操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
以下2张表
MariaDB [mydb]> select * from students;
+-------+-----------+------+------+------------+------------+
| id | name | sex | age | interest | birthday |
+-------+-----------+------+------+------------+------------+
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20003 | 小张 | 男 | 15 | basketball | 2005-08-14 |
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
| 20005 | 丽丽 | 女 | 15 | painting | 2005-12-01 |
| 20006 | 李明 | 男 | 16 | music | 2004-08-14 |
| 20007 | 张大明 | 男 | 18 | football | 2002-03-05 |
+-------+-----------+------+------+------------+------------+
7 rows in set (0.002 sec)MariaDB [mydb]> select * from score;
+-------+-----------+------+----------+--------+
| id | name | math | language | sports |
+-------+-----------+------+----------+--------+
| 20001 | 小明 | 81 | 86 | 93 |
| 20002 | 小红 | 86 | 86 | 89 |
| 20003 | 小张 | 77 | 83 | 93 |
| 20004 | 露丝 | 88 | 78 | 65 |
| 20005 | 丽丽 | 92 | 94 | 64 |
| 20006 | 李明 | 75 | 78 | 88 |
| 20007 | 张大明 | 54 | 65 | 95 |
+-------+-----------+------+----------+--------+
7 rows in set (0.000 sec)
MariaDB [mydb]> select id,name from students union select id,name from score;
+-------+-----------+
| id | name |
+-------+-----------+
| 20001 | 小明 |
| 20002 | 小红 |
| 20003 | 小张 |
| 20004 | 露丝 |
| 20005 | 丽丽 |
| 20006 | 李明 |
| 20007 | 张大明 |
+-------+-----------+
7 rows in set (0.002 sec)MariaDB [mydb]> select id,name from students union all select id,name from score;
+-------+-----------+
| id | name |
+-------+-----------+
| 20001 | 小明 |
| 20002 | 小红 |
| 20003 | 小张 |
| 20004 | 露丝 |
| 20005 | 丽丽 |
| 20006 | 李明 |
| 20007 | 张大明 |
| 20001 | 小明 |
| 20002 | 小红 |
| 20003 | 小张 |
| 20004 | 露丝 |
| 20005 | 丽丽 |
| 20006 | 李明 |
| 20007 | 张大明 |
+-------+-----------+
14 rows in set (0.002 sec)4，AND
MariaDB [mydb]> select * from students where age=16 and interest='music';
+-------+--------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+--------+------+------+----------+------------+
| 20006 | 李明 | 男 | 16 | music | 2004-08-14 |
+-------+--------+------+------+----------+------------+
1 row in set (0.001 sec)5，OR
MariaDB [mydb]> select * from students where age=16 or birthday='2007-07-19';
+-------+--------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+--------+------+------+----------+------------+
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
| 20006 | 李明 | 男 | 16 | music | 2004-08-14 |
+-------+--------+------+------+----------+------------+
3 rows in set (0.000 sec)
MariaDB [mydb]> select * from students where (age=16 and birthday='2004-08-14') or birthday='2007-07-19';
+-------+--------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+--------+------+------+----------+------------+
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
| 20006 | 李明 | 男 | 16 | music | 2004-08-14 |
+-------+--------+------+------+----------+------------+
2 rows in set (0.002 sec)6，IN
MariaDB [mydb]> select * from students where age in (13,14);
+-------+--------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+--------+------+------+----------+------------+
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
+-------+--------+------+------+----------+------------+
2 rows in set (0.001 sec)7，BETWEEN AND
MariaDB [mydb]> select * from students where age between 15 and 18;
+-------+-----------+------+------+------------+------------+
| id | name | sex | age | interest | birthday |
+-------+-----------+------+------+------------+------------+
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20003 | 小张 | 男 | 15 | basketball | 2005-08-14 |
| 20005 | 丽丽 | 女 | 15 | painting | 2005-12-01 |
| 20006 | 李明 | 男 | 16 | music | 2004-08-14 |
| 20007 | 张大明 | 男 | 18 | football | 2002-03-05 |
+-------+-----------+------+------+------------+------------+
5 rows in set (0.000 sec)8，IS NULL
首先确保我的表格中的某些数据是null才可以使用
update colin set name='Chen' where age=null;
是修改不了数据的因为age=null 最后的结果是false 条件是不成立的
update colin set name='Chen' where 1=1; 条件成立，但是这种条件没有任何意义
update colin set name='Chen' where age is null; 正确
9，NOT
MariaDB [mydb]> select * from students where not age=15;
+-------+-----------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+-----------+------+------+----------+------------+
| 20001 | 小明 | 男 | 16 | football | 2004-03-05 |
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
| 20006 | 李明 | 男 | 16 | music | 2004-08-14 |
| 20007 | 张大明 | 男 | 18 | football | 2002-03-05 |
+-------+-----------+------+------+----------+------------+
5 rows in set (0.000 sec)
MariaDB [mydb]> select * from students where age not between 15 and 18;
+-------+--------+------+------+----------+------------+
| id | name | sex | age | interest | birthday |
+-------+--------+------+------+----------+------------+
| 20002 | 小红 | 女 | 14 | music | 2006-06-18 |
| 20004 | 露丝 | 女 | 13 | painting | 2007-07-19 |
+-------+--------+------+------+----------+------------+
2 rows in set (0.001 sec)
这篇关于【笔记四】：LIKE，UNION，ORDER BY，AND，OR，IN，BETWEEN，IS NULL，NOT的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
