mysql基础查询

查询方法

mysql基础查询入门级查询查询一列查询多列查询全部列查询不重复的值查询前n行查询从某行开始的n行 排序单一字段排序多字段排序降序混合字段排序 where条件查询算术运算符关系运算符逻辑运算符区间between and列举 in空值查询 分组 本文查询操作只涉及到一个学生信息表， 如下

+-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-------+--------+-------+

入门级查询

查询一列

语法为 select columu_name from table_name; 任何查询语句都以冒号结尾

mysql> select s_name from students; +--------+ | s_name | +--------+ | 曾华 | | 匡明 | | 王丽 | | 李军 | | 王芳 | | 陆军 | | 王尼玛 | | 张全蛋 | | 赵铁柱 |

查询多列

不同列之间用英文逗号隔开

mysql> select s_num, s_name, s_birthday from students; +-------+--------+---------------------+ | s_num | s_name | s_birthday | +-------+--------+---------------------+ | 101 | 曾华 | 1977-09-01 00:00:00 | | 102 | 匡明 | 1975-10-02 00:00:00 | | 103 | 王丽 | 1976-01-23 00:00:00 | | 104 | 李军 | 1976-02-20 00:00:00 | | 105 | 王芳 | 1975-02-10 00:00:00 | | 106 | 陆军 | 1974-06-03 00:00:00 | | 107 | 王尼玛 | 1976-02-20 00:00:00 | | 108 | 张全蛋 | 1975-02-10 00:00:00 | | 109 | 赵铁柱 | 1974-06-03 00:00:00 | +-------+--------+---------------------+

查询全部列

* 表示全选

mysql> select * from students; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+

查询不重复的值

distinct 去掉重复值

mysql> select distinct s_class from students; +---------+ | s_class | +---------+ | 95033 | | 95031 | +---------+

查询前n行

mysql> select * from students limit 5; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+

查询从某行开始的n行

语法 limit length offset start;

mysql> select * from students limit 5 offset 3; --从第三行开始取五行-- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+

排序

单一字段排序

mysql> select * from students order by s_num; --按照s_num 升序 默认升序-- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+

多字段排序

多个排序字段用逗号隔开

mysql> select * from students order by s_class ,s_sex; --先按班级升序，班级相同再按性别升序 +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | +-------+--------+-------+---------------------+---------+

降序

DESC (descending ;降序排列 ，以此递减） ASC (ascending ; 上行,递增,升序）默认

mysql> select * from students order by s_num desc; +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | +-------+--------+-------+---------------------+---------+

混合字段排序

mysql> select * from students order by s_class ASC ,s_sex DESC; --先按班级升序 ，相同按性别降序 +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | +-------+--------+-------+---------------------+---------+

where条件查询

算术运算符

可以对查询结果经行 + - * /四则运算

mysql> select 2*s_num from students; --输出2倍学号-- +---------+ | 2*s_num | +---------+ | 202 | | 204 | | 206 | | 208 | | 210 | | 212 | | 214 | | 216 | | 218 | +---------+

关系运算符

关系运算符包括大于 > , 大于等于 >= ,小于< 等等

mysql> select * from students where s_num > 107; --where 表示当 -- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+

逻辑运算符

逻辑运算符与其他编程语言都类似， 分别为 并 and , 或 or , 非 not .

mysql> select * from students where s_num > 105 and s_class = '95033'; -- 同时成立 and-- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | +-------+--------+-------+---------------------+---------+ mysql> select * from students where s_num > 105 or s_class = '95033'; -- 有一个成立 or-- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+ mysql> select * from students where not s_sex ='男'; -- 否定之后的条件 not-- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | +-------+--------+-------+---------------------+---------+

区间between and

连续区间内取值可以用 between and ,离散的取值可以用 in

mysql> select * from students where s_num between 104 and 107 ; -- 两边都包含， 相当于闭区间-- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |

列举 in

mysql> select * from students where s_name in ('李军','王尼玛'); --括号-- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | +-------+--------+-------+---------------------+---------+

空值查询

IS NULL 查询空值

mysql> insert into students(s_num, s_name, s_sex) values('110','刘表','男'); Query OK, 1 row affected (0.01 sec) mysql> select * from students; -- 插入一条数据，其s_birthday，s_class为空-- +-------+--------+-------+---------------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 110 | 刘表 | 男 | NULL | NULL | +-------+--------+-------+---------------------+---------+ mysql> select * from students where s_birthday IS NULL; +-------+--------+-------+------------+---------+ | s_num | s_name | s_sex | s_birthday | s_class | +-------+--------+-------+------------+---------+ | 110 | 刘表 | 男 | NULL | NULL | +-------+--------+-------+------------+---------+

分组

group by 分组

mysql> select count(*),s_class from students group by s_class; --count() 计数函数-- +----------+---------+ | count(*) | s_class | +----------+---------+ | 4 | 95033 | | 5 | 95031 | | 1 | NULL | +----------+---------+ 3 rows in set (0.00 sec)

本文是mysql最基本的查询语句，每条只有一个方法，旨在掌握透彻基础，在复杂的查询不会轻易写错 。