数据库查询练习-简单查询练习及笔记(1)
条件查询 where
1. = != <> > >= <= 算数运算符
2.betwoon ....and... 区间查询
3. in 查询指定字段多个值
4. is null 判断是空
5.and 连接 ..和..
6. or 并且
7. not
1查询表中所有记录
查询student表所有记录
select * from student;
# *表示所有字段
/*
返回如下结果
mysql> select * from student;
+-----+--------+------+---------------------+--------+
| sno | sname | ssex | sbirthday | sclass |
+-----+--------+------+---------------------+--------+
| 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 |
+-----+--------+------+---------------------+--------+
9 rows in set (0.04 sec)
*/
2查询指定字段
查询 student 表中的 sname、ssex 和 sclass 字段的所有行
select sname,ssex,sclass from student;
#select 字段,字段,字段 from 表名;
/*
mysql> select sname,ssex,sclass from student;
+--------+------+--------+
| sname | ssex | sclass |
+--------+------+--------+
| 曾华 | 男 | 95033 |
| 匡明 | 男 | 95031 |
| 王丽 | 女 | 95033 |
| 李军 | 男 | 95033 |
| 王芳 | 女 | 95031 |
| 陆军 | 男 | 95031 |
| 王尼玛 | 男 | 95033 |
| 张全蛋 | 男 | 95031 |
| 赵铁柱 | 男 | 95031 |
+--------+------+--------+
9 rows in set (0.05 sec)
*/
3 distinct: 去重查询
查询 teacher 表中不重复的 depart 列
#未去重查询前
select depart from teacher;
#select 字段 from 表名;
/*
mysql> select depart from teacher;
+------------+
| depart |
+------------+
| 计算机系 |
| 计算机系 |
| 电子工程系 |
| 电子工程系 |
+------------+
4 rows in set (0.06 sec)
*/
select distinct depart from teacher;
#elect distinct 字段名 from 表名;
#去重查询后distinct
/*
mysql> select distinct depart from teacher;
+------------+
| depart |
+------------+
| 计算机系 |
| 电子工程系 |
+------------+
2 rows in set (0.06 sec)
*/
4 查询区间
查询 score 表中成绩在60-80之间的所有行
select * from score where degree between 60 and 80;
# select * from 表名 where 字段名 between 大于多少 and 小于多少;
/*
mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.02 sec)
*/
5 运算符查询
查询 score 表中成绩在60-80之间的所有行
select * from score where degree > 60 and degree <80;
# select * from 表名称 where 字段 > 范围值 and 字段 <范围值;
/*
mysql> select * from score where degree > 60 and degree <80;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
4 rows in set (0.03 sec)
*/
6 表示或者关系的查询(同一字段中查询)
查询 score 表中成绩为 85, 86 或 88 的行
IN: 查询规定中的多个值
not in 表示反选in值
select * from score where degree in(85,86,88);
select * from score where degree not in(85,86,88);
# select * from 表名 where 字段名 in(值1,值2,值3);
/*
mysql> select * from score where degree in(85,86,88);
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
+-----+-------+--------+
3 rows in set (0.03 sec)
*/
7 表示或者关系的查询(多个字段中查询)
查询 student 表中 ‘95031’ 班或性别为 ‘女’ 的所有行
or: 表示或者关系
select * from student where sclass='95031' or ssex='女';
# select * from 表名 where 字段1='字段1下的值' or 字段2='字段2下的值';
/*
mysql> select * from student where sclass='95031' or ssex='女';
+-----+--------+------+---------------------+--------+
| sno | sname | ssex | sbirthday | sclass |
+-----+--------+------+---------------------+--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 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 |
+-----+--------+------+---------------------+--------+
6 rows in set (0.03 sec)
*/
8 升序排列 降序排列
– 以 class 降序的方式查询 student 表的所有行
– DESC: 降序,从高到低
– ASC(默认): 升序,从低到高
# 降序排列
select * from student order by sclass desc;
# select * from 表名 order by 字段名 desc;
/*
mysql> select * from student order by sclass desc;
+-----+--------+------+---------------------+--------+
| sno | sname | ssex | sbirthday | sclass |
+-----+--------+------+---------------------+--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 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 |
+-----+--------+------+---------------------+--------+
9 rows in set (0.03 sec)
*/
# 升序排列(在排序时如果不指定排序规则,默认升序)
select * from student order by sclass asc;
# select * from 表名 order by 字段名 asc;
/*
mysql> select * from student order by sclass asc;
+-----+--------+------+---------------------+--------+
| sno | sname | ssex | sbirthday | sclass |
+-----+--------+------+---------------------+--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 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 |
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
+-----+--------+------+---------------
*/
#不指定排序规则
select * from student order by sclass;
#返回结果与升序排列结果一样
9 一个字段升序 ,一个字段降序
以 cno 升序、degree 降序查询 score 表的所有行
select * from score order by cno asc,degree desc;
# select * from 表名 order by 字段名1 asc,字段名2 desc;
#在查询时先按字段1排序,当字段一一样时,在找字段2排序
/*
mysql> select * from score order by cno asc,degree desc;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 88 |
| 109 | 3-105 | 76 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 109 | 6-166 | 81 |
| 105 | 6-166 | 79 |
+-----+-------+--------+
9 rows in set (0.02 sec)
*/
10 统计
– 查询 “95031” 班的学生人数
– COUNT: 统计
select count(*) from student where sclass='95031';
# select count(*) from 表名 where 统计的字段名='统计的值';
#返回查询到的总和数
/*
mysql> select count(*) from student where sclass='95031';
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.02 sec)
*/
11 查询最大,最小值
max 最大值
min 最小值
select max(degree) from score;
#select max(查询字段) from 查询那个表;
/*
mysql> select max(degree) from score;
+-------------+
| max(degree) |
+-------------+
| 92 |
+-------------+
1 row in set (0.02 sec)
*/
12 子查询
– 查询 score 表中的最高分的学生学号和课程编号(子查询或排序查询)。
– (SELECT MAX(degree) FROM score): 子查询,算出最高分
select sno,cno from score where degree=(select max(degree) from score);
#select 查询字段1,查询字段2 from score where 查询条件字段=查询条件字段对应的值;
#select 查询字段1,查询字段2 from score where 查询条件字段=(select max(字段名) from 表名);
/*
mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.01 sec)
*/
13 分页查询
limit 0,1
select * from score limit 3,5;
#select * from 表名 limit 从第几条开始查(不包括当前),查询几条数据;
/*
mysql> select * from score limit 3,5;
+-----+-------+--------+
| sno | cno | degree |
+-----+-------+--------+
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+-----+-------+--------+
5 rows in set (0.03 sec)
*/
14 查询空数据
字段is null 空
字段is not null;不为空
not 字段 is null;不为空
select * from score where cno is null;
#select * from 表名 where 字段 is null;