操作MySQL3 ----- 查询表中数据¶
1. 语法格式¶
SELECT *或字段列表
FROM表名 [WHERE 条件]
[GROUP BY 分组依据]
[HAVING 条件]
[ORDER BY 字段名 [ASC|DESC]]
案例表：stu表
mysql> use school;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| student |
+-------------------+
1 row in set (0.01 sec)
mysql> desc student;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| sno | char(20) | NO | PRI | NULL | |
| sname | varchar(20) | NO | | NULL | |
| sage | tinyint unsigned | NO | | NULL | |
| ssex | enum('0','1') | NO | | 1 | |
| sbirthday | date | YES | | NULL | |
| class | varchar(60) | NO | | NULL | |
+-----------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
2. 查询stu表中所有的数据¶
``select * from student;
mysql> select * from student;
+---------+-------+------+------+------------+--------+
| sno | sname | sage | ssex | sbirthday | class |
+---------+-------+------+------+------------+--------+
| 1035400 | jerry | 20 | 0 | 2003-05-15 | AIO169 |
| 1035401 | Alice | 19 | 0 | 2004-05-16 | AIO169 |
| 1035402 | Bob | 18 | 0 | 2005-05-16 | AIO169 |
| 1035403 | Mary | 21 | 1 | 2002-05-16 | AIO169 |
+---------+-------+------+------+------------+--------+
4 rows in set (0.00 sec)
3. 查询student表中所有的学生学号和姓名¶
select 学号,姓名 from student;
4. 给列重命名¶
select 学号 sno,姓名 sname from student;
select 学号 AS son,姓名 AS sname from student;
5. 显示表中的特定行数¶
格式：
select * from 表名 limits [偏移量,]显示行数;
案例：
a. 只显示stu表中4--8行的数据
select * from student limit 3,5;
b. 只显示product表中33到49行的数据
select * from product limit 32,17;
6. 条件语句¶
| 符号或字符 | 含义 |
|---|---|
> |
大于 |
> |
小于 |
>= |
大于等于 |
<= |
小于等于 |
= |
等于 |
!=或<> |
不等于 |
between 值1 and 值2 |
值1和值2之间包含边界值 |
is null |
列的值为空 |
is not null |
列的值非空 |
like |
用于模糊查询，适配通配符 |
in |
列的值在指定列表内 |
and |
且 |
or |
或 |
not |
非 |
案例：
先建表并插入数据
mysql> drop table tl.tl;
Query OK, 0 rows affected (0.01 sec)
mysql> use tl;
Database changed
mysql> create table tl(
-> name char(20) not null primary key comment '姓名',
-> age tinyint not null comment '年龄');
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tl (name,age)values('aa',20);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tl values ('bb', 24);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tl values ('cc', 21);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tl values ('dd', 23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tl;
+------+-----+
| name | age |
+------+-----+
| aa | 20 |
| bb | 24 |
| cc | 21 |
| dd | 23 |
+------+-----+
4 rows in set (0.00 sec)
a. 查询tl表中年龄age在20到23之间的所有数据
mysql> SELECT * FROM tl WHERE age BETWEEN 20 AND 23;
+------+-----+
| name | age |
+------+-----+
| aa | 20 |
| cc | 21 |
| dd | 23 |
+------+-----+
3 rows in set (0.00 sec)
或
mysql> SELECT * FROM tl WHERE age>=20 AND age<=23;
+------+-----+
| name | age |
+------+-----+
| aa | 20 |
| cc | 21 |
| dd | 23 |
+------+-----+
3 rows in set (0.00 sec)
b. 分别查询stu表中电话号码为空/不为空的所有数据
mysql> use student;
Database changed
mysql> select * from student where 电话号码 is null;
mysql> select * from student where 电话号码 is not null;
c. 查询student表中姓名中包含a的所有信息
mysql> select * from student where `sname` like '%a%';
+---------+-------+------+------+------------+--------+
| sno | sname | sage | ssex | sbirthday | class |
+---------+-------+------+------+------------+--------+
| 1035401 | Alice | 19 | 0 | 2004-05-16 | AIO169 |
| 1035403 | Mary | 21 | 1 | 2002-05-16 | AIO169 |
+---------+-------+------+------+------------+--------+
2 rows in set (0.00 sec)
查询student表中出生年月不在15日的所有学生信息
mysql> SELECT * FROM student WHERE `sbirthday` not like '%-15';
+---------+-------+------+------+------------+--------+
| sno | sname | sage | ssex | sbirthday | class |
+---------+-------+------+------+------------+--------+
| 1035401 | Alice | 19 | 0 | 2004-05-16 | AIO169 |
| 1035402 | Bob | 18 | 0 | 2005-05-16 | AIO169 |
| 1035403 | Mary | 21 | 1 | 2002-05-16 | AIO169 |
+---------+-------+------+------+------------+--------+
3 rows in set (0.00 sec)
d. 查询tl表中年龄是18，23，20的所有人信息
mysql> use tl;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT * FROM tl WHERE `age` in (18,20,23);
+------+-----+
| name | age |
+------+-----+
| aa | 20 |
| dd | 23 |
+------+-----+
2 rows in set (0.00 sec)
* 注：in的本质是or
所以上同：
mysql> SELECT * FROM tl WHERE age=18 or age=20 or age=23;
+------+-----+
| name | age |
+------+-----+
| aa | 20 |
| dd | 23 |
+------+-----+
2 rows in set (0.00 sec)
7. 排序¶
order by 列名 [ASC|DESC]
说明：
ASC为升序也是默认值，DESC为降序
案例：
将tl表中数据按照age降序排序
mysql> SELECT * FROM tl ORDER BY age DESC;
+------+-----+
| name | age |
+------+-----+
| bb | 24 |
| dd | 23 |
| cc | 21 |
| aa | 20 |
+------+-----+
4 rows in set (0.00 sec)
8. 列值去重¶
SELECT DISTINCT 列名 FROM 表名 [where 条件]
案例：
mysql> DELETE FROM tl;
Query OK, 4 rows affected (0.01 sec)
mysql> DESC tl;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(20) | NO | PRI | NULL | |
| age | tinyint | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE tl MODIFY age TINYINT UNSIGNED DEFAULT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tl;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name | char(20) | NO | PRI | NULL | |
| age | tinyint unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE tl ADD CLASS char(20) NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC tl;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name | char(20) | NO | PRI | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| CLASS | char(20) | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> INSERT INTO tl VALUES('aa',20,'2班'),('bb',18,'3班'),('cc',20,'2班'),('dd',19,'2班');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM tl;
+------+------+-------+
| name | age | CLASS |
+------+------+-------+
| aa | 20 | 2班 |
| bb | 18 | 3班 |
| cc | 20 | 2班 |
| dd | 19 | 2班 |
+------+------+-------+
4 rows in set (0.00 sec)
mysql> SELECT DISTINCT CLASS FROM tl;
+-------+
| CLASS |
+-------+
| 2班 |
| 3班 |
+-------+
2 rows in set (0.00 sec)
9. 聚合函数¶
| 函数 | 含义 |
|---|---|
| count() | 统计行数 |
| max() | 最大值 |
| min() | 最小值 |
| sum() | 求和 |
| avg() | 平均值 |
案例：
mysql> SELECT * FROM tl;
+------+------+-------+
| name | age | CLASS |
+------+------+-------+
| aa | 20 | 2班 |
| bb | 18 | 3班 |
| cc | 20 | 2班 |
| dd | 19 | 2班 |
+------+------+-------+
4 rows in set (0.01 sec)
mysql> SELECT count(*) 总行数,max(age) 最大年龄,min(age) 最小年龄,sum(age) 年龄总和,avg(age) 平均年龄 from tl;
+-----------+--------------+--------------+--------------+--------------+
| 总行数 | 最大年龄 | 最小年龄 | 年龄总和 | 平均年龄 |
+-----------+--------------+--------------+--------------+--------------+
| 4 | 20 | 18 | 77 | 19.2500 |
+-----------+--------------+--------------+--------------+--------------+
1 row in set (0.00 sec)