---查询练习； 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>