欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

MySQL实验室8：查询数据（1）--select语句的基本用法

最编程 2024-10-08 11:33:27
...

MySQL 实验 8：数据查询（1）—— select 语句的基本用法

目录

  • MySQL 实验 8：数据查询（1）—— select 语句的基本用法
    • 一、数据准备
    • 二、SELECT 语句的语法
    • 三、指定查询结果要显示的列名或表达式列表
        • 1、指定要查询的列名
        • 2、指定要查询的表达式
    • 四、为查询结果中的表达式指定别名
    • 五、使用 distinct 参数消除查询结果中的重复行
    • 六、使用 order by 参数对查询结果排序
        • 1、使用列名进行排序
        • 2、使用表达式进行排序
        • 3、使用 select 后面的表达式的序号排序
    • 七、使用 limit 参数限定显示的记录数

MySQL 使用 SELECT 语句查询所需的数据。查询是指根据使用需求，使用不同的查询方式从数据库中获取所需的数据。

一、数据准备

本实验及后续的查询实验所使用的数据表如下：

-- 院系表
create table dept(
    dept_id char(3) primary key,
    dept_name char(50),
    phone char(20),
    address varchar(20)
);

-- 添加数据
insert into dept values('D01','经济学院','0373-3025111','10号教学楼1101');
insert into dept values('D02','管理学院','0373-3025122','12号教学楼602');
insert into dept values('D03','生命科技学院','0373-3025133','8号教学楼204');
insert into dept values('D04','农学院','0373-3025144','7号教学楼612');

-- 学生表
create table stu(
    s_id char(11) primary key,
    s_name char(20),
    gender char(2),
    birth date,
    phone char(20),
    dept_id char(3),
    foreign key(dept_id) references dept(dept_id)
);

-- 添加数据
insert into stu values('20220124001','薛智玲','男','2000-1-15','13637358097','D01');
insert into stu values('20220124002','杨铭华','男','2001-3-6','13637381407','D01');
insert into stu values('20220124003','张从超','女','2003-12-9','13637389129','D01');
insert into stu values('20220124004','孙金航','女','2003-3-7','13637387244','D01');
insert into stu values('20220214001','谭宝名','男','2002-8-5','13637332439','D02');
insert into stu values('20220214002','赵文瀚','女','2001-3-25','13637381710','D02');
insert into stu values('20220214003','裴天馨','女','2003-4-15','13637381374','D02');
insert into stu values('20220325101','董雯林','男','2003-12-27','13637357085','D03');
insert into stu values('20220325102','周宜可','男','2002-7-4','13637333898','D03');
insert into stu values('20220325103','张春晶','男','2001-6-21','13637340698','D03');
insert into stu values('20220410101','夏飞研','女','2002-9-2','13637326693','D04');
insert into stu values('20220410102','聂小龙','男','2002-12-4','13637331432','D04');
insert into stu values('20220410103','赵晨祥','女','2002-5-22','13637388564','D04');
insert into stu values('20220410104','江沐洪','男','2002-2-12','13637345052',NULL);

-- 课程表
create table course(
    c_id char(6) primary key,
    c_name char(50),
    credit decimal(4,2)
);

-- 添加数据
insert into course values('C01001','微观经济学',4);
insert into course values('C01002','宏观经济学',3);
insert into course values('C01003','财务管理',3);
insert into course values('C02101','企业管理概论',3);
insert into course values('C02102','管理学原理',3);
insert into course values('C02103','管理信息系统',4);
insert into course values('C03201','遗传学',3);
insert into course values('C03202','药物学',3);
insert into course values('C03203','药理学',3);
insert into course values('C04111','农业环境学',3);
insert into course values('C04112','土壤肥料学',4);
insert into course values('C04113','作物学',4);

-- 选课表
create table xk(
    s_id char(11),
    c_id char(6),
    score decimal(5,2),
    primary key(s_id,c_id),
    foreign key(s_id) references stu(s_id),
    foreign key(c_id) references course(c_id)
);

-- 添加数据
insert into xk values('20220124001','C01001',61);
insert into xk values('20220124002','C01001',88);
insert into xk values('20220124003','C01001',95);
insert into xk values('20220124004','C01001',81);
insert into xk values('20220124001','C01002',68);
insert into xk values('20220124002','C01002',69);
insert into xk values('20220124001','C01003',92);
insert into xk values('20220124002','C01003',69);
insert into xk values('20220124003','C01003',94);
insert into xk values('20220124004','C01003',96);
insert into xk values('20220214001','C02101',80);
insert into xk values('20220214002','C02101',63);
insert into xk values('20220214003','C02101',72);
insert into xk values('20220214001','C02102',80);
insert into xk values('20220214002','C02102',67);
insert into xk values('20220214003','C02102',96);
insert into xk values('20220214001','C02103',97);
insert into xk values('20220214002','C02103',74);
insert into xk values('20220214003','C02103',94);
insert into xk values('20220325101','C03201',83);
insert into xk values('20220325102','C03201',90);
insert into xk values('20220325103','C03201',65);
insert into xk values('20220325101','C03202',83);
insert into xk values('20220325102','C03202',84);
insert into xk values('20220325103','C03202',77);
insert into xk values('20220325101','C03203',74);
insert into xk values('20220325102','C03203',67);
insert into xk values('20220325103','C03203',66);
insert into xk values('20220410101','C04111',68);
insert into xk values('20220410102','C04111',62);
insert into xk values('20220410103','C04111',64);
insert into xk values('20220410104','C04111',64);
insert into xk values('20220410101','C04112',97);
insert into xk values('20220410102','C04112',78);
insert into xk values('20220410103','C04112',82);
insert into xk values('20220410104','C04112',75);
insert into xk values('20220410101','C04113',83);
insert into xk values('20220410102','C04113',72);
insert into xk values('20220410103','C04113',62);


-- 查看数据
-- 院系表
mysql> select * from dept;
+---------+--------------+--------------+----------------+
| dept_id | dept_name    | phone        | address        |
+---------+--------------+--------------+----------------+
| D01     | 经济学院     | 0373-3025111 | 10号教学楼1101 |
| D02     | 管理学院     | 0373-3025122 | 12号教学楼602  |
| D03     | 生命科技学院 | 0373-3025133 | 8号教学楼204   |
| D04     | 农学院       | 0373-3025144 | 7号教学楼612   |
+---------+--------------+--------------+----------------+
4 rows in set (0.00 sec)

-- 学生表
mysql> select * from stu;
+-------------+--------+--------+------------+-------------+---------+
| s_id        | s_name | gender | birth      | phone       | dept_id |
+-------------+--------+--------+------------+-------------+---------+
| 20220124001 | 薛智玲 | 男     | 2000-01-15 | 13637358097 | D01     |
| 20220124002 | 杨铭华 | 男     | 2001-03-06 | 13637381407 | D01     |
| 20220124003 | 张从超 | 女     | 2003-12-09 | 13637389129 | D01     |
| 20220124004 | 孙金航 | 女     | 2003-03-07 | 13637387244 | D01     |
| 20220214001 | 谭宝名 | 男     | 2002-08-05 | 13637332439 | D02     |
| 20220214002 | 赵文瀚 | 女     | 2001-03-25 | 13637381710 | D02     |
| 20220214003 | 裴天馨 | 女     | 2003-04-15 | 13637381374 | D02     |
| 20220325101 | 董雯林 | 男     | 2003-12-27 | 13637357085 | D03     |
| 20220325102 | 周宜可 | 男     | 2002-07-04 | 13637333898 | D03     |
| 20220325103 | 张春晶 | 男     | 2001-06-21 | 13637340698 | D03     |
| 20220410101 | 夏飞研 | 女     | 2002-09-02 | 13637326693 | D04     |
| 20220410102 | 聂小龙 | 男     | 2002-12-04 | 13637331432 | D04     |
| 20220410103 | 赵晨祥 | 女     | 2002-05-22 | 13637388564 | D04     |
| 20220410104 | 江沐洪 | 男     | 2002-02-12 | 13637345052 | NULL    |
+-------------+--------+--------+------------+-------------+---------+
14 rows in set (0.00 sec)

-- 课程表
mysql> select * from course;
+--------+--------------+--------+
| c_id   | c_name       | credit |
+--------+--------------+--------+
| C01001 | 微观经济学   |   4.00 |
| C01002 | 宏观经济学   |   3.00 |
| C01003 | 财务管理     |   3.00 |
| C02101 | 企业管理概论 |   3.00 |
| C02102 | 管理学原理   |   3.00 |
| C02103 | 管理信息系统 |   4.00 |
| C03201 | 遗传学       |   3.00 |
| C03202 | 药物学       |   3.00 |
| C03203 | 药理学       |   3.00 |
| C04111 | 农业环境学   |   3.00 |
| C04112 | 土壤肥料学   |   4.00 |
| C04113 | 作物学       |   4.00 |
+--------+--------------+--------+
12 rows in set (0.00 sec)

-- 选课表
mysql> select * from xk;
+-------------+--------+-------+
| s_id        | c_id   | score |
+-------------+--------+-------+
| 20220124001 | C01001 | 61.00 |
| 20220124001 | C01002 | 68.00 |
| 20220124001 | C01003 | 92.00 |
| 20220124002 | C01001 | 88.00 |
| 20220124002 | C01002 | 69.00 |
| 20220124002 | C01003 | 69.00 |
| 20220124003 | C01001 | 95.00 |
| 20220124003 | C01003 | 94.00 |
| 20220124004 | C01001 | 81.00 |
| 20220124004 | C01003 | 96.00 |
| 20220214001 | C02101 | 80.00 |
| 20220214001 | C02102 | 80.00 |
| 20220214001 | C02103 | 97.00 |
| 20220214002 | C02101 | 63.00 |
| 20220214002 | C02102 | 67.00 |
| 20220214002 | C02103 | 74.00 |
| 20220214003 | C02101 | 72.00 |
| 20220214003 | C02102 | 96.00 |
| 20220214003 | C02103 | 94.00 |
| 20220325101 | C03201 | 83.00 |
| 20220325101 | C03202 | 83.00 |
| 20220325101 | C03203 | 74.00 |
| 20220325102 | C03201 | 90.00 |
| 20220325102 | C03202 | 84.00 |
| 20220325102 | C03203 | 67.00 |
| 20220325103 
						

							上一篇：							
								[C++] CMake 使用 OpenMP 并行加速 - II.在 CMake 项目中使用 OpenMP							
						

						

							下一篇：							
								QT 上下调用 libusb 库 stm32407

推荐阅读