Mysql基本命令用法（二）

wuzhen％1 $ S

一、查询

数据准备，以下以这四张表来做例子

1.学生表
select * from student;
 +------------+-------+--------+---------------------+---------+
 | Num        | Name  | Sex    | birth               | class   |
 +------------+-------+--------+---------------------+---------+
 | 2017052455 | Tom   | male   | 1999-05-07 00:00:00 | class_1 |
 | 2017052456 | Jenny | female | 1998-05-07 00:00:00 | class_3 |
 | 2017052457 | Bob   | male   | 1989-05-17 00:00:00 | class_3 |
 | 2017052458 | Alice | female | 1999-01-07 00:00:00 | class_2 |
 | 2017052459 | Eve   | male   | 1999-05-27 00:00:00 | class_1 |
 | 2017052460 | Anny  | female | 1998-10-27 00:00:00 | class_4 |
 | 2017052461 | Endw  | female | 1999-11-07 00:00:00 | class_2 |
 | 2017052462 | Julie | male   | 1999-08-02 00:00:00 | class_1 |
 | 2017052463 | Luero | female | 1999-01-27 00:00:00 | class_3 |
 +------------+-------+--------+---------------------+---------+
2.课程表
select * from course;;
 +-------+------------------+------------+
 | Num   | Name             | Teacher_ID |
 +-------+------------------+------------+
 | 3-105 | computer science | 1000001    |
 | 3-245 | operator system  | 1000001    |
 | 6-166 | math             | 1000003    |
 | 9-888 | english          | 1000003    |
 +-------+------------------+------------+
3.教师表
select * from teacher;
 +---------+----------+--------+---------------------+--------+--------+
 | Num     | Name     | Sex    | birth               | prof   | depart |
 +---------+----------+--------+---------------------+--------+--------+
 | 1000001 | Mr.Zhang | male   | 1988-05-06 00:00:00 | Doctor | D1     |
 | 1000002 | Mr.Huang | male   | 1978-03-06 00:00:00 | Doctor | D1     |
 | 1000003 | Mr.Liu   | female | 1977-05-06 00:00:00 | Doctor | D2     |
 | 1000004 | Mr.Wu    | male   | 1975-05-16 00:00:00 | Doctor | D3     |
 | 1000005 | Mr.Li    | female | 1976-02-06 00:00:00 | Doctor | D3     |
 | 1000006 | Mr.Kong  | male   | 1987-05-26 00:00:00 | Doctor | D1     |
 | 1000007 | Mr.Yu    | male   | 1982-11-06 00:00:00 | Doctor | D2     |
 +---------+----------+--------+---------------------+--------+--------+
4.成绩表
select * from score;;
 +------------+-----------+--------+
 | Student_ID | Course_ID | degree |
 +------------+-----------+--------+
 | 2017052455 | 3-245     |     96 |
 | 2017052456 | 3-105     |     76 |
 | 2017052457 | 3-245     |     86 |
 | 2017052458 | 6-166     |     88 |
 | 2017052459 | 9-888     |     96 |
 | 2017052460 | 3-105     |     80 |
 | 2017052461 | 3-245     |     82 |
 | 2017052462 | 6-166     |     83 |
 | 2017052463 | 9-888     |     83 |
 +------------+-----------+--------+

1.查询所有记录

select * from student; --查询学生表所有记录

2.查看特定某些列

select Name,Sex from student; --查询名字和性别列

3.查询某列的不重复项有几个

select distinct depart from teacher;  --查询教师所有的不重复depart列
 +--------+
 | depart |
 +--------+
 | D1     |
 | D2     |
 | D3     |
 +--------+

4.查询值在某一区间的所有记录

select * from score where degree between 80 and 85;  --查询score表degree列的值的区间在80到85之间
+------------+-----------+--------+
 | Student_ID | Course_ID | degree |
 +------------+-----------+--------+
 | 2017052460 | 3-105     |     80 |
 | 2017052461 | 3-245     |     82 |
 | 2017052462 | 6-166     |     83 |
 | 2017052463 | 9-888     |     83 |
 +------------+-----------+--------+
select * from score where degree >= 80 and degree <= 85;  --作用同上

5.查询某指定值得所有记录

select * from score where degree in(83,86);   --查询score表degree列值为83或86的记录
+------------+-----------+--------+
 | Student_ID | Course_ID | degree |
 +------------+-----------+--------+
 | 2017052457 | 3-245     |     86 |
 | 2017052462 | 6-166     |     83 |
 | 2017052463 | 9-888     |     83 |
 +------------+-----------+--------+

6.或条件

select * from student where Sex='male' or  class = 'class_1';     --查询性别为男的或者是一班的人
+------------+-------+------+---------------------+---------+
 | Num        | Name  | Sex  | birth               | class   |
 +------------+-------+------+---------------------+---------+
 | 2017052455 | Tom   | male | 1999-05-07 00:00:00 | class_1 |
 | 2017052457 | Bob   | male | 1989-05-17 00:00:00 | class_3 |
 | 2017052459 | Eve   | male | 1999-05-27 00:00:00 | class_1 |
 | 2017052462 | Julie | male | 1999-08-02 00:00:00 | class_1 |
 +------------+-------+------+---------------------+---------+

7.升序降序

select * from score order by degree asc;     --按成绩升序排列
+------------+-----------+--------+
 | Student_ID | Course_ID | degree |
 +------------+-----------+--------+
 | 2017052456 | 3-105     |     76 |
 | 2017052460 | 3-105     |     80 |
 | 2017052461 | 3-245     |     82 |
 | 2017052462 | 6-166     |     83 |
 | 2017052463 | 9-888     |     83 |
 | 2017052457 | 3-245     |     86 |
 | 2017052458 | 6-166     |     88 |
 | 2017052455 | 3-245     |     96 |
 | 2017052459 | 9-888     |     96 |
 +------------+-----------+--------+
select * from score order by degree desc;     --按成绩降序排列
 +------------+-----------+--------+
 | Student_ID | Course_ID | degree |
 +------------+-----------+--------+
 | 2017052455 | 3-245     |     96 |
 | 2017052459 | 9-888     |     96 |
 | 2017052458 | 6-166     |     88 |
 | 2017052457 | 3-245     |     86 |
 | 2017052462 | 6-166     |     83 |
 | 2017052463 | 9-888     |     83 |
 | 2017052461 | 3-245     |     82 |
 | 2017052460 | 3-105     |     80 |
 | 2017052456 | 3-105     |     76 |
 +------------+-----------+--------+
select * from score order by Course_ID asc,degree desc;    --按课程编号升序排后再按成绩降序排
 +------------+-----------+--------+
 | Student_ID | Course_ID | degree |
 +------------+-----------+--------+
 | 2017052460 | 3-105     |     80 |
 | 2017052456 | 3-105     |     76 |
 | 2017052455 | 3-245     |     96 |
 | 2017052457 | 3-245     |     86 |
 | 2017052461 | 3-245     |     82 |
 | 2017052458 | 6-166     |     88 |
 | 2017052462 | 6-166     |     83 |
 | 2017052459 | 9-888     |     96 |
 | 2017052463 | 9-888     |     83 |
 +------------+-----------+--------+

8.查询某列拥有相同值的数目

select count(*) from student where class='class_1';     --查询一班的人数
+----------+
 | count(*) |
 +----------+
 |        3 |
 +----------+

9. 查询某列中最大值的那一行的记录

select * from score where degree=(select max(degree) from score); --选择分数最高的那行记录（此法用的是是子查询，先找出分最大的那个degree再找degree所在那行，有两个最高分故两行）
 +------------+-----------+--------+
 | Student_ID | Course_ID | degree |
 +------------+-----------+--------+
 | 2017052455 | 3-245     |     96 |
 | 2017052459 | 9-888     |     96 |
 +------------+-----------+--------+
select * from score order by degree desc limit 0,1;  --先按degree降序排列，再找出从0开始的第一项。
+------------+-----------+--------+
 | Student_ID | Course_ID | degree |
 +------------+-----------+--------+
 | 2017052455 | 3-245     |     96 |
 +------------+-----------+--------+

10. 计算平均值

select Course_ID,avg(degree) from score where Course_ID='3-245'; --算出课程3-245的平均分
 +-----------+-------------+
 | Course_ID | avg(degree) |
 +-----------+-------------+
 | 3-245     |     88.0000 |
 +-----------+-------------+
select Course_ID,avg(degree) from score group by Course_ID; --按课程编号分组再算出各自平均分
+-----------+-------------+
 | Course_ID | avg(degree) |
 +-----------+-------------+
 | 3-105     |     78.0000 |
 | 3-245     |     88.0000 |
 | 6-166     |     85.5000 |
 | 9-888     |     89.5000 |
 +-----------+-------------+

11.查询拥有相同值，且相同次数大于某定值的组

select Course_ID,count() from score group by Course_ID having count(Course_ID)>=2;             --查询课程人数大于2的课程
 +-----------+----------+ 
 | Course_ID | count() |
 +-----------+----------+
 | 3-105     |        2 |
 | 3-245     |        3 |
 | 6-166     |        2 |
 | 9-888     |        2 |
 +-----------+----------+

12.模糊查询

select * from course where Num like '3%';               --查询以3开头的课程（%是通配符）
 +-------+------------------+------------+
 | Num   | Name             | Teacher_ID |
 +-------+------------------+------------+
 | 3-105 | computer science | 1000001    |
 | 3-245 | operator system  | 1000001    |
 +-------+------------------+------------+

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注