在MariaDB中,WHERE
子句与SELECT
,INSERT
,UPDATE
和DELETE
语句一起使用来选择或更改想要更改的满足指定特定行记录。
它是一个在表名后面出现的语句。
语法
[COMMAND] field,field2,... FROM table_name,table_name2,... WHERE [CONDITION]
注意:
WHERE
子句是一个可选的子句。它可以和AND
,OR
,AND & OR
,LIKE
运算符一起使用。
示例:
我们有一个表students
,里边有一些数据。假设要查询students
表中的student_id
小于6
的所有记录。
SELECT *
FROM Students
WHERE student_id < 6;
执行上面查询语句,得到以下结果 -
MariaDB [testdb]> SELECT *
-> FROM Students
-> WHERE student_id < 6;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 1 | Maxsu | Haikou | 2017-01-07 |
| 3 | JMaster | Beijing | 2016-05-07 |
| 4 | Mahesh | Guangzhou | 2016-06-07 |
| 5 | Kobe | Shanghai | 2016-02-07 |
+------------+--------------+-----------------+----------------+
4 rows in set (0.00 sec)
为了方便演示,这里再插入一条数据 -
INSERT INTO students
(student_name, student_address, admission_date)
VALUES('Maxsu','Sanya','2017-08-08 00:00:00');
当前students
表中的全部数据是 -
MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 1 | Maxsu | Haikou | 2017-01-07 |
| 3 | JMaster | Beijing | 2016-05-07 |
| 4 | Mahesh | Guangzhou | 2016-06-07 |
| 5 | Kobe | Shanghai | 2016-02-07 |
| 6 | Blaba | Shengzhen | 2016-08-07 |
| 7 | Maxsu | Sanya | 2017-08-08 |
+------------+--------------+-----------------+----------------+
6 rows in set (0.00 sec)
现在要查询students
表中的所有列,其中student_name
是Maxsu
,并且student_id
大于1
的行记录。这两个条件都必须满足。参考以下查询语句 -
SELECT *
FROM Students
WHERE student_name = 'Maxsu'
AND student_id >1 ;
执行上面查询语句,得到以下结果 -
MariaDB [testdb]> SELECT *
-> FROM Students
-> WHERE student_name = 'Maxsu'
-> AND student_id >1 ;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 7 | Maxsu | Sanya | 2017-08-08 |
+------------+--------------+-----------------+----------------+
1 row in set (0.03 sec)
现在要查询students
表中的所有列,其中student_name
是Maxsu
,或者student_id
大于5
的行记录。这两个条件满足其中一个即可。参考以下查询语句 -
SELECT *
FROM Students
WHERE student_name = 'Maxsu'
OR student_id >5 ;
执行上面查询语句,得到以下结果 -
MariaDB [testdb]> SELECT *
-> FROM Students
-> WHERE student_name = 'Maxsu'
-> OR student_id >5 ;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 1 | Maxsu | Haikou | 2017-01-07 |
| 6 | Blaba | Shengzhen | 2016-08-07 |
| 7 | Maxsu | Sanya | 2017-08-08 |
+------------+--------------+-----------------+----------------+
3 rows in set (0.00 sec)
假设要查询students
表中的所有列,那些符合student_name
是Ajeet
,student_id
大于或等于1
,或者student_address
是Shanghai
的行记录。
SELECT *
FROM Students
WHERE student_name = 'Maxsu'
AND student_id >=1
OR (student_address = 'Shanghai');
执行上面查询语句,得到以下结果 -
MariaDB [testdb]> SELECT *
-> FROM Students
-> WHERE student_name = 'Maxsu'
-> AND student_id >=1
-> OR (student_address = 'Shanghai');
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 1 | Maxsu | Haikou | 2017-01-07 |
| 5 | Kobe | Shanghai | 2016-02-07 |
| 7 | Maxsu | Sanya | 2017-08-08 |
+------------+--------------+-----------------+----------------+
3 rows in set (0.00 sec)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。
长按识别二维码并关注微信
更方便到期提醒、手机管理