表结构
mysql> desc stus;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| gradeId | int(11) | YES | MUL | NULL | |
| NAME | varchar(100) | NO | | NULL | |
| age | int(3) | YES | | NULL | |
| bir | date | YES | | NULL | |
| english | int(3) | YES | | NULL | |
| chinese | int(3) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+mysql> select * from stus;
+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME | age | bir | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
| 1 | 1 | 周棋洛 | 18 | 2001-06-01 | 78 | NULL |
| 2 | 2 | 张郁苗 | 18 | 2002-11-07 | 130 | 120 |
| 3 | 1 | 小猪佩奇 | 6 | 2015-06-10 | 34 | NULL |
| 4 | 3 | 猪爸爸 | 8 | 2012-09-12 | 34 | 56 |
| 5 | 3 | 猪妈妈 | 7 | 2012-09-11 | 56 | 78 |
+----+---------+--------------+------+------------+---------+---------+
简单查询
单列查询
select 列名 from 表;
从stus表中查询名字的字段
mysql> select name from stus;
+--------------+
| name |
+--------------+
| 周棋洛 |
| 张郁苗 |
| 小猪佩奇 |
| 猪爸爸 |
| 猪妈妈 |
+--------------+
多列查询
select 列名,列名…… from 表;
从stus表中查询名字和年龄这两个字段
mysql> select name,age from stus;
+--------------+------+
| name | age |
+--------------+------+
| 周棋洛 | 18 |
| 张郁苗 | 18 |
| 小猪佩奇 | 6 |
| 猪爸爸 | 8 |
| 猪妈妈 | 7 |
+--------------+------+
查询所有列 *
select * from 表;
从stus表查询所有字段
mysql> select * from stus;
+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME | age | bir | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
| 1 | 1 | 周棋洛 | 18 | 2001-06-01 | 78 | NULL |
| 2 | 2 | 张郁苗 | 18 | 2002-11-07 | 130 | 120 |
| 3 | 1 | 小猪佩奇 | 6 | 2015-06-10 | 34 | NULL |
| 4 | 3 | 猪爸爸 | 8 | 2012-09-12 | 34 | 56 |
| 5 | 3 | 猪妈妈 | 7 | 2012-09-11 | 56 | 78 |
+----+---------+--------------+------+------------+---------+---------+
distinct去重
distinct关键字可以出去重复出现的内容,注意:不能部分使用distinct
mysql> select distinct age from stus;
+------+
| age |
+------+
| 18 |
| 6 |
| 8 |
| 7 |
+------+
起别名 as
我们在查询时,如果对原有字段名不满意,这时就可以通过as关键字对字段起别名,注意:as是可省略的,如下所示
mysql> select name as "姓名" from stus;
+--------------+
| 姓名 |
+--------------+
| 周棋洛 |
| 张郁苗 |
| 小猪佩奇 |
| 猪爸爸 |
| 猪妈妈 |
+--------------+mysql> select name "姓名" from stus;
+--------------+
| 姓名 |
+--------------+
| 周棋洛 |
| 张郁苗 |
| 小猪佩奇 |
| 猪爸爸 |
| 猪妈妈 |
+--------------+
条件查询
大于判断 >
😀 : 查找stus表中年龄大于18岁的学生姓名
mysql> select name from stus where age > 18;
Empty set (0.00 sec)
小于判断 <
😀 : 查找stus表中年龄小于18岁的学生姓名
mysql> select name from stus where age < 18;
+--------------+
| name |
+--------------+
| 小猪佩奇 |
| 猪爸爸 |
| 猪妈妈 |
+--------------+
大于等于判断 >=
😀 : 查找stus表中年龄大于等于18岁的学生姓名
mysql> select name from stus where age >= 18;
+-----------+
| name |
+-----------+
| 周棋洛 |
| 张郁苗 |
+-----------+
小于等于判断 <=
😀 : 查找stus表中年龄小于等于18岁的学生姓名
mysql> select name from stus where age <= 18;
+--------------+
| name |
+--------------+
| 周棋洛 |
| 张郁苗 |
| 小猪佩奇 |
| 猪爸爸 |
| 猪妈妈 |
+--------------+
不等于判断 <> 或 !=
😀 : 查找stus表中年龄不等于18岁的学生姓名
mysql> select name from stus where age <> 18;
+--------------+
| name |
+--------------+
| 小猪佩奇 |
| 猪爸爸 |
| 猪妈妈 |
+--------------+mysql> select name from stus where age != 18;
+--------------+
| name |
+--------------+
| 小猪佩奇 |
| 猪爸爸 |
| 猪妈妈 |
+--------------+
BETWEEN … AND …包含判断
😀 : 查找stus表中年龄在10~18岁的学生姓名
mysql> select name,age from stus where age between 8 and 20;
+-----------+------+
| name | age |
+-----------+------+
| 周棋洛 | 18 |
| 张郁苗 | 18 |
| 猪爸爸 | 8 |
+-----------+------+
IN(…)
😀 : 查找stus表中年龄为8岁或7岁的学生姓名
mysql> select name,age from stus where age in(7,8);
+-----------+------+
| name | age |
+-----------+------+
| 猪爸爸 | 8 |
| 猪妈妈 | 7 |
+-----------+------+
IS NULL
😀 : 查找stus表中名字为空的学生信息
mysql> select * from stus where name is null;
Empty set (0.02 sec)
IS NOT NULL
😀 : 查找stus表中名字不为空的学生信息
mysql> select * from stus where name is not null;
+----+---------+--------------+------+------------+---------+---------+
| id | gradeId | NAME | age | bir | english | chinese |
+----+---------+--------------+------+------------+---------+---------+
| 1 | 1 | 周棋洛 | 18 | 2001-06-01 | 78 | NULL |
| 2 | 2 | 张郁苗 | 18 | 2002-11-07 | 130 | 120 |
| 3 | 1 | 小猪佩奇 | 6 | 2015-06-10 | 34 | NULL |
| 4 | 3 | 猪爸爸 | 8 | 2012-09-12 | 34 | 56 |
| 5 | 3 | 猪妈妈 | 7 | 2012-09-11 | 56 | 78 |
+----+---------+--------------+------+------------+---------+---------+
AND 或 &&
只有多个条件都成立才会返回真,否则就认为false
😀 : 查找stus表中年龄为18岁的周棋洛的学生信息
mysql> select * from stus where name = "周棋洛" AND age = "18";
+----+---------+-----------+------+------------+---------+---------+
| id | gradeId | NAME | age | bir | english | chinese |
+----+---------+-----------+------+------------+---------+---------+
| 1 | 1 | 周棋洛 | 18 | 2001-06-01 | 78 | NULL |
+----+---------+-----------+------+------------+---------+---------+mysql> select * from stus where name = "周棋洛" && age = "18";
+----+---------+-----------+------+------------+---------+---------+
| id | gradeId | NAME | age | bir | english | chinese |
+----+---------+-----------+------+------------+---------+---------+
| 1 | 1 | 周棋洛 | 18 | 2001-06-01 | 78 | NULL |
+----+---------+-----------+------+------------+---------+---------+
OR 或 ||
只要有一个条件成立就返回真,都不成立返回false
😀 : 查找stus表中名字叫周棋洛或者叫张郁苗的学生信息
mysql> select * from stus where name = "周棋洛" OR name = "张郁苗";
+----+---------+-----------+------+------------+---------+---------+
| id | gradeId | NAME | age | bir | english | chinese |
+----+---------+-----------+------+------------+---------+---------+
| 1 | 1 | 周棋洛 | 18 | 2001-06-01 | 78 | NULL |
| 2 | 2 | 张郁苗 | 18 | 2002-11-07 | 130 | 120 |
+----+---------+-----------+------+------------+---------+---------+mysql> select * from stus where name = "周棋洛" || name = "张郁苗";
+----+---------+-----------+------+------------+---------+---------+
| id | gradeId | NAME | age | bir | english | chinese |
+----+---------+-----------+------+------------+---------+---------+
| 1 | 1 | 周棋洛 | 18 | 2001-06-01 | 78 | NULL |
| 2 | 2 | 张郁苗 | 18 | 2002-11-07 | 130 | 120 |
+----+---------+-----------+------+------------+---------+---------+