MySQL数据库：查询

查询所有信息：

mysql> select * from student;
+----+-------+------+--------+-------+
| id | sname | sage | gender | class |
+----+-------+------+--------+-------+
|  1 | 小明  |    7 | 男     | 一班  |
|  2 | 小红  |    8 | NULL   | NULL  |
|  3 | 小一  |    7 | 男     | 二班  |
|  4 | 小二  |    7 | 男     | 二班  |
|  5 | 小程  |    7 | 男     | 二班  |
+----+-------+------+--------+-------+
5 rows in set

查询编号大于3的学生：

mysql> select * from student where id > 4;
+----+-------+------+--------+-------+
| id | sname | sage | gender | class |
+----+-------+------+--------+-------+
|  5 | 小程  |    7 | 男     | 二班  |
+----+-------+------+--------+-------+
1 row in set

查询姓名不是“小一”的学生

mysql> select * from student where sname != "小一";
+----+-------+------+--------+-------+
| id | sname | sage | gender | class |
+----+-------+------+--------+-------+
|  1 | 小明  |    7 | 男     | 一班  |
|  2 | 小红  |    8 | NULL   | NULL  |
|  4 | 小二  |    7 | 男     | 二班  |
|  5 | 小程  |    7 | 男     | 二班  |
+----+-------+------+--------+-------+
4 rows in set

查询没被删除的学生，增加isdelete列1为被删除

mysql> select * from student where isdelete != 1;
+----+-------+------+--------+----------+-------+
| id | sname | sage | gender | isdelete | class |
+----+-------+------+--------+----------+-------+
|  1 | 小明  |    7 | 男     | 0        | 一班  |
|  3 | 小一  |    7 | 男     | 0        | 二班  |
|  4 | 小二  |    7 | 男     | 0        | 二班  |
|  5 | 小程  |    7 | 男     | 0        | 二班  |
+----+-------+------+--------+----------+-------+
4 rows in set

模糊查询：
like
%表示任意多个任意字符
_表示一个任意字符

mysql> select * from student where sname like '%明';
+----+-------+------+--------+----------+-------+
| id | sname | sage | gender | isdelete | class |
+----+-------+------+--------+----------+-------+
|  1 | 小明  |    7 | 男     | 0        | 一班  |
+----+-------+------+--------+----------+-------+
1 row in set

逻辑运算符

and
or
not

mysql> select * from student where id = 1 and sname = '小明';
+----+-------+------+--------+----------+-------+
| id | sname | sage | gender | isdelete | class |
+----+-------+------+--------+----------+-------+
|  1 | 小明  |    7 | 男     | 0        | 一班  |
+----+-------+------+--------+----------+-------+
1 row in set

mysql> select * from student where id = 1 or id = 2;
+----+-------+------+--------+----------+-------+
| id | sname | sage | gender | isdelete | class |
+----+-------+------+--------+----------+-------+
|  1 | 小明  |    7 | 男     | 0        | 一班  |
|  2 | 小红  |    8 | NULL   | 1        | NULL  |
+----+-------+------+--------+----------+-------+
2 rows in set

范围查询

in表示在一个非连续的范围内

mysql> select * from student where id in (1,2,4);
+----+-------+------+--------+----------+-------+
| id | sname | sage | gender | isdelete | class |
+----+-------+------+--------+----------+-------+
|  1 | 小明  |    7 | 男     | 0        | 一班  |
|  2 | 小红  |    8 | NULL   | 1        | NULL  |
|  4 | 小二  |    7 | 男     | 0        | 二班  |
+----+-------+------+--------+----------+-------+
3 rows in set

mysql> select * from student where id not in (1,2,4);
+----+-------+------+--------+----------+-------+
| id | sname | sage | gender | isdelete | class |
+----+-------+------+--------+----------+-------+
|  3 | 小一  |    7 | 男     | 0        | 二班  |
|  5 | 小程  |    7 | 男     | 0        | 二班  |
+----+-------+------+--------+----------+-------+
2 rows in set

空判断

注意：null与’'是不同的
判空is null

mysql> select * from student where class is null;
+----+-------+------+--------+----------+-------+
| id | sname | sage | gender | isdelete | class |
+----+-------+------+--------+----------+-------+
|  2 | 小红  |    8 | NULL   | 1        | NULL  |
+----+-------+------+--------+----------+-------+
1 row in set

优先级

小括号，not，比较运算符，逻辑运算符
and比or先运算，如果同时出现并希望先算or，需要结合()使用