mysql --查询练习

---查询练习；

1. 查询student 表的所有记录；
mysql> select * from student;
+-----+------------+------+---------------------+----------+
| sno | sname      | ssex | sbirthday           | class    |
+-----+------------+------+---------------------+----------+
| 100 | xiaozhan   | 男   | 1991-10-05 00:00:00 | 20110341 |
| 101 | yibo       | 男   | 1997-08-05 00:00:00 | 20110341 |
| 102 | dandan     | 女   | 1993-09-05 00:00:00 | 20110341 |
| 103 | tingting   | 女   | 1993-11-15 00:00:00 | 20110341 |
| 104 | junjie     | 男   | 1994-01-15 00:00:00 | 20110341 |
| 105 | peiyu      | 男   | 1994-07-15 00:00:00 | 20110341 |
| 106 | wangnima   | 男   | 1993-07-15 00:00:00 | 20110341 |
| 107 | zhaotiezhu | 男   | 1993-07-15 00:00:00 | 20110341 |
| 108 | zhaotiezhu | 女   | 1993-07-15 00:00:00 | 20110341 |
| 109 | xudijian   | 男   | 1993-11-15 00:00:00 | 20110341 |
+-----+------------+------+---------------------+----------+
10 rows in set (0.04 sec)

2. 查询student 表中的所有记录的sname,ssex 和class;
mysql> select sname,ssex,class from student;
+------------+------+----------+
| sname      | ssex | class    |
+------------+------+----------+
| xiaozhan   | 男   | 20110341 |
| yibo       | 男   | 20110341 |
| dandan     | 女   | 20110341 |
| tingting   | 女   | 20110341 |
| junjie     | 男   | 20110341 |
| peiyu      | 男   | 20110341 |
| wangnima   | 男   | 20110341 |
| zhaotiezhu | 男   | 20110341 |
| zhaotiezhu | 女   | 20110341 |
| xudijian   | 男   | 20110341 |
+------------+------+----------+
10 rows in set (0.01 sec)


3. 查询教师所有的单位即不重复的depart列；
---distinct 排重
mysql> update teacher set depart="自动化系" where tname="zhangsi";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update teacher set depart="英语系" where tname="lisi";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+-----+----------+------+---------------------+-----------+--------------+
| tno | tname    | tsex | tbirthday           | prof      | depart       |
+-----+----------+------+---------------------+-----------+--------------+
| 101 | zhangsan | 男   | 1958-09-11 00:00:00 | 教授      | 计算机系     |
| 102 | zhangsi  | 男   | 1958-09-11 00:00:00 | 副教授    | 自动化系     |
| 103 | lisi     | 女   | 1978-09-10 00:00:00 | 讲师      | 英语系       |
| 104 | liwu     | 女   | 1988-09-10 00:00:00 | 助教      | 计算机系     |
+-----+----------+------+---------------------+-----------+--------------+
4 rows in set (0.00 sec)

mysql> select distinct depart from teacher;
+--------------+
| depart       |
+--------------+
| 计算机系     |
| 自动化系     |
| 英语系       |
+--------------+
3 rows in set (0.01 sec)

mysql> 


4. 查询score中成绩在80-90 之间的记录；
---查询区间 between ... and ...
mysql> select * from score where degree between 80 and 90;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 100 | 3-108 |     90 |
| 101 | 3-105 |     90 |
| 101 | 3-106 |     90 |
| 101 | 3-108 |     80 |
| 102 | 3-108 |     80 |
| 103 | 3-108 |     80 |
+-----+-------+--------+
6 rows in set (0.06 sec)

直接使用运算符比较：
mysql> select * from score where degree > 80 and degree  < 100;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 100 | 3-105 |     99 |
| 100 | 3-106 |     98 |
| 100 | 3-107 |     98 |
| 100 | 3-108 |     90 |
| 101 | 3-105 |     90 |
| 101 | 3-106 |     90 |
+-----+-------+--------+
6 rows in set (0.00 sec)

mysql> 

5. 查询score 中成绩为80 90或者99 的记录；
---表示或者的查询： in 
mysql> select * from score where degree in (80,90,99);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 100 | 3-105 |     99 |
| 100 | 3-108 |     90 |
| 101 | 3-105 |     90 |
| 101 | 3-106 |     90 |
| 101 | 3-108 |     80 |
| 102 | 3-108 |     80 |
| 103 | 3-108 |     80 |
+-----+-------+--------+
7 rows in set (0.00 sec)

mysql> 

6. 查询student 表中性别为女的或者班级是 20110341 班同学的记录；
mysql> select * from student where class="20110341" or ssex="女";
+-----+------------+------+---------------------+----------+
| sno | sname      | ssex | sbirthday           | class    |
+-----+------------+------+---------------------+----------+
| 100 | xiaozhan   | 男   | 1991-10-05 00:00:00 | 20110341 |
| 101 | yibo       | 男   | 1997-08-05 00:00:00 | 20110341 |
| 102 | dandan     | 女   | 1993-09-05 00:00:00 | 0765421  |
| 103 | tingting   | 女   | 1993-11-15 00:00:00 | 20110341 |
| 104 | junjie     | 男   | 1994-01-15 00:00:00 | 20110341 |
| 105 | peiyu      | 男   | 1994-07-15 00:00:00 | 20110341 |
| 107 | zhaotiezhu | 男   | 1993-07-15 00:00:00 | 20110341 |
| 108 | zhaotiezhu | 女   | 1993-07-15 00:00:00 | 20110341 |
+-----+------------+------+---------------------+----------+
8 rows in set (0.00 sec)

查询20110341班级的女生
mysql> select * from student where class="20110341" and  ssex="女";
+-----+------------+------+---------------------+----------+
| sno | sname      | ssex | sbirthday           | class    |
+-----+------------+------+---------------------+----------+
| 103 | tingting   | 女   | 1993-11-15 00:00:00 | 20110341 |
| 108 | zhaotiezhu | 女   | 1993-07-15 00:00:00 | 20110341 |
+-----+------------+------+---------------------+----------+
2 rows in set (0.00 sec)


7. 以cno 升序，degree 为降序查询score中所有记录；
mysql> select * from score order by cno,degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 100 | 3-105 |     99 |
| 101 | 3-105 |     90 |
| 100 | 3-106 |     98 |
| 101 | 3-106 |     90 |
| 100 | 3-107 |     98 |
| 101 | 3-107 |     79 |
| 100 | 3-108 |     90 |
| 101 | 3-108 |     80 |
| 102 | 3-108 |     80 |
| 103 | 3-108 |     80 |
| 105 | 3-108 |     66 |
| 104 | 3-108 |     60 |
+-----+-------+--------+
12 rows in set (0.00 sec)

mysql> 

8. 查询20110341 班级人数；
mysql> select count(*) from student where class="20110341";
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.01 sec)

mysql> 

9.以class 降序查询student 表中的所有记录；


--升序，降序
降序：
mysql> select * from student order by class desc;
+-----+------------+------+---------------------+----------+
| sno | sname      | ssex | sbirthday           | class    |
+-----+------------+------+---------------------+----------+
| 100 | xiaozhan   | 男   | 1991-10-05 00:00:00 | 20110341 |
| 101 | yibo       | 男   | 1997-08-05 00:00:00 | 20110341 |
| 103 | tingting   | 女   | 1993-11-15 00:00:00 | 20110341 |
| 104 | junjie     | 男   | 1994-01-15 00:00:00 | 20110341 |
| 105 | peiyu      | 男   | 1994-07-15 00:00:00 | 20110341 |
| 107 | zhaotiezhu | 男   | 1993-07-15 00:00:00 | 20110341 |
| 108 | zhaotiezhu | 女   | 1993-07-15 00:00:00 | 20110341 |
| 106 | wangnima   | 男   | 1993-07-15 00:00:00 | 0987654  |
| 102 | dandan     | 女   | 1993-09-05 00:00:00 | 0765421  |
| 109 | xudijian   | 男   | 1993-11-15 00:00:00 | 0765421  |
+-----+------------+------+---------------------+----------+
10 rows in set (0.01 sec)

默认升序 asc：
mysql> select * from student order by class;
+-----+------------+------+---------------------+----------+
| sno | sname      | ssex | sbirthday           | class    |
+-----+------------+------+---------------------+----------+
| 102 | dandan     | 女   | 1993-09-05 00:00:00 | 0765421  |
| 109 | xudijian   | 男   | 1993-11-15 00:00:00 | 0765421  |
| 106 | wangnima   | 男   | 1993-07-15 00:00:00 | 0987654  |
| 100 | xiaozhan   | 男   | 1991-10-05 00:00:00 | 20110341 |
| 101 | yibo       | 男   | 1997-08-05 00:00:00 | 20110341 |
| 103 | tingting   | 女   | 1993-11-15 00:00:00 | 20110341 |
| 104 | junjie     | 男   | 1994-01-15 00:00:00 | 20110341 |
| 105 | peiyu      | 男   | 1994-07-15 00:00:00 | 20110341 |
| 107 | zhaotiezhu | 男   | 1993-07-15 00:00:00 | 20110341 |
| 108 | zhaotiezhu | 女   | 1993-07-15 00:00:00 | 20110341 |
+-----+------------+------+---------------------+----------+
10 rows in set (0.00 sec)



10 查询 score表中的最高分数的学号和课程号，（子查询或者排序）。
mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 100 | 3-105 |
+-----+-------+
1 row in set (0.01 sec)

mysql>