MySQL查询操作

<>MySQL查询操作

<>1、建表
CREATE TABLE 学生 ( 学号 char(7) NOT NULL PRIMARY KEY, 姓名 char(8) NOT NULL, 性别 char
(2) , 年龄 tinyint , 入学时间 datetime , 专业 varchar(10) , 年级 char(10) ) ; CREATE TABLE
选课( 学号 char(7) NOT NULL, 课程号 char(4) NOT NULL, 成绩 int check(成绩>=0 and 成绩<=100),
PRIMARY KEY (学号 ASC,课程号 ASC) ); CREATE TABLE 课程 ( 课程号 char(20), 课程名 varchar(20),
学分int, 教师号 int ); CREATE TABLE 教师 ( 教师号 char(20), 教师名 varchar(20), 职称 varchar(
20), 工资 int );
<>2、插入数据
insert into 学生 values('0100111','张三', '女', 22, '2010-09-01','数学','10级'); insert
into 学生 values('0100215','刘玲玲','女',21, '2010-09-01','计算机','10级'); insert into 学生
values('0110102','刘尚宏', '女',21,'2011-09-04','数学','11级'); insert into 学生 values(
'0110205','郑辉' , '女',20 ,'2011-09-04','计算机','11级'); insert into 学生 values(
'0110208','张品逸', '女',22,'2011-09-04', '计算机','11级'); insert into 学生 values(
'0110301','朱一虹', '女',19,'2011-09-04', '网络','12级'); insert into 学生 values(
'0120103','李海' , '女',19,'2012-09-06', '数学','12级' ); insert into 学生 values(
'0120301','张丽娜', '女',18,'2012-09-06', '网络','12级' ); insert into 学生 values(
'0120302','李小红' , '女',20,'2012-09-06', '网络','12级' ); insert into 选课 values(
'0100111', 'C007', 100); insert into 选课 values('0100111', 'C008', 86); insert
into 选课 values('0110102', 'C005', 56); insert into 选课 values('0110102', 'C006',
80); insert into 选课 values('0110205', 'C005', 87); insert into 选课 values(
'0110205', 'C007', 78); insert into 选课 values('0110208', 'C006', 88); insert
into 选课 values('0110208', 'C007', 89); insert into 选课 values('0120301', 'C001',
89); insert into 选课 values('0120301', 'C002', 76); insert into 选课 values(
'0120301', 'C003', 99); insert into 选课 values('0120302', 'C001', 77); insert
into 选课 values('0120302', 'C004', 79); insert into 课程 values('C001','高等数学',4,3);
insert into 课程 values('C002','计算机基础',3,1); insert into 课程 values('C003','网络通信',3
,3); insert into 课程 values('C004','操作系统',3,1); insert into 课程 values('C005',
'数据库',3,1); insert into 课程 values('C006','计算机英语',3,2); insert into 课程 values(
'C007','大学物理',3,4); insert into 课程 values('C008','电子技术',3,4); insert into 教师
values('1','郑浩','教授',6000); insert into 教师 values('2','王伟','副教授',5000); insert
into 教师 values('3','李平','讲师',4000); insert into 教师 values('4','陈亮','副教授',5000);
<>3、查询数据
SELECT * FROM 学生; select * from 选课; select * from 课程; select * from 教师;
<>4、查询操作
--查询学生们有哪些专业,只显示专业列,过滤掉重复行。 select distinct 专业 from 学生; --统计有学生选修的课程门数。 select
COUNT(*) as 课程数 from 课程; --求选修C004课程的学生的平均年龄。 select avg(年龄) as 平均年龄 from 学生,选课
where 学生.学号=选课.学号 and 课程号='C004'; --求学分为3的每门课程的学生平均成绩。 select avg(成绩) as 平均成绩,选课
.课程号 from 选课,课程 where 选课.课程号=课程.课程号 and 学分=3 group by 选课.课程号;
--统计每门课程的学生选修人数,超过三人的课程才能统计。要求输出课程号和选修人数,查询结果按人数降序排列, --若人数相同,按课程号升序排列。 select
count(学号) as 选修人数,课程号 from 选课 group by 课程号 having 选修人数>3 order by 选修人数 desc,课程号
asc; --检索姓王的学生的姓名和年龄。 select 姓名,年龄 from 学生 where 姓名 like '王%'
--在选课表中检索成绩为空值的学生的学号和课程号。 select 学号,课程号 from 选课 where 成绩 is null;
--查询没有学生选修的课的课程号和课程名。 select 课程号,课程名 from 课程 where 课程号 not in (select 课程号 from
选课); --求年龄大于女同学平均年龄的男学生姓名和年龄。 select 姓名,年龄 from 学生 where 性别='男' and 年龄>(select
AVG(年龄) from 学生 where 性别='女') --求年龄大于所有女同学年龄的男学生姓名和年龄。 select 姓名,年龄 from 学生
where 性别='男' and 年龄>all (select 年龄 from 学生 where 性别='女')
--查询所有与张丽娜同年级,同专业,但比王华年龄大的学生的姓名,年龄和性别。 select 姓名,年龄,性别 from 学生 where 专业=(select
专业from 学生 where 姓名='张丽娜') and 年级=(select 年级 from 学生 where 姓名='张丽娜') and 年龄>(
select 年龄 from 学生 where 姓名='张丽娜'); --查询选修课程C002的学生中成绩最高的学生的学号。 select 学号 from 选课
where 课程号='C002' and 成绩=(select MAX(成绩) from 选课 where 课程号='C002')
--检索学生姓名及其所选修课程的课程号和成绩。 select 学生.姓名,选课.课程号,选课.成绩 from 学生,选课 where 学生.学号=选课.学号;
--检索选修4门以上课程的学生平均成绩(不统计不及格的课程),并要求按平均成绩的降序排列出来。 select avg(成绩) as 平均成绩 from 选课
group by 学号 having count(课程号)>4 order by 平均成绩; select 学号,AVG(成绩) as 平均成绩 from 选课
group by 学号 having COUNT(*)>4 order by AVG(成绩) desc; --检索选修两门及以上课程的学生平均成绩只取前5名。
select avg(成绩) as 平均成绩 from 选课 group by 学号 having count(课程号)>1 order by 平均成绩
limit 5; --查询每个学生的总学分。 select 学号,SUM(学分) as 总学分 from 选课,课程 where 选课.课程号=课程.课程号
group by 学号; --查询每位教师所担任的课程,显示课程名,教师名,学时(学分*16)。 select 教师名,课程名,学分*16 as 学分 from
教师,课程 where 教师.教师号=课程.教师号; --教师工资加5%。 update 教师 set 工资=工资*(1+0.05) select *
from 教师;
<>5、建表
CREATE TABLE 商品 ( 商品编号 char(6) NOT NULL PRIMARY KEY, 商品名称 varchar(20) NOT NULL,
单价float , 生产商 varchar(30) ); CREATE TABLE 仓库 ( 仓库编号 char(3) NOT NULL PRIMARY
KEY, 仓库地址 varchar(20) NOT NULL, 电话 varchar(10) , 容量 int ); CREATE TABLE 库存情况 (
仓库编号char(3) NOT NULL, 商品编号 char(6) NOT NULL, 数量 int , PRIMARY KEY (仓库编号 ASC,商品编号
ASC) ); CREATE TABLE 管理员 ( 管理员编号 char(3) NOT NULL PRIMARY KEY, 管理员姓名 varchar(20)
not null, 性别 char(2) , 出生年月 datetime, 仓库编号 char(3) );
<>6、插入数据
insert into 商品 values('bx-179','冰箱',3200,'青岛海尔'); insert into 商品 values(
'bx-340','冰箱',2568,'北京雪花'); insert into 商品 values('ds-001','电视',1580,'四川长虹');
insert into 商品 values('ds-018','电视',2980,'青岛海尔'); insert into 商品 values('ds-580'
,'电视',6899,'南京熊猫'); insert into 商品 values('kt-060','空调',3560,'青岛海尔'); insert
into 商品 values('kt-330','空调',2820,'青岛海信'); insert into 商品 values('xyj-01','洗衣机',
580,'无锡小天鹅'); insert into 商品 values('xyj-30','洗衣机',858,'南京熊猫'); insert into 仓库
values ('001','1号楼105','89123411',78); insert into 仓库 values ('002','1号楼106',
'89123412',89); insert into 仓库 values ('003', '2号楼101','89120007',86); insert
into 仓库 values ('004', '2号楼102', '89120008',90); insert into 仓库 values ('005',
'3号楼104', '89229901',87); insert into 仓库 values ('006', '3号楼108', '89229902',97)
; insert into 库存情况 values('004','bx-179', 5); insert into 库存情况 values('002',
'bx-179', 12); insert into 库存情况 values('003','bx-340', 10); insert into 库存情况
values('001','ds-001', 20); insert into 库存情况 values('003','ds-018', 8); insert
into 库存情况 values('006','ds-018', 10); insert into 库存情况 values('004','ds-018', 12
); insert into 库存情况 values('001','ds-018', 16); insert into 库存情况 values('005',
'ds-018', 20); insert into 库存情况 values('002','ds-580', 15); insert into 库存情况
values('004','kt-060', 9); insert into 库存情况 values('001','kt-060', 13); insert
into 库存情况 values('004','xyj-01', 10); insert into 库存情况 values('003','xyj-30', 21
); insert into 管理员 values('101','张辽','男','1989-02-03','001'); insert into 管理员
values('102','李立平','男','1984-03-07','001'); insert into 管理员 values('103','王辉',
'男','1987-11-05','001' ); insert into 管理员 values('104','郑风豫','男','1989-06-07',
'002' ); insert into 管理员 values('105','常红', '女','1985-09-12', '002'); insert
into 管理员 values('106','明慧林','男','1988-08-16','002' ); insert into 管理员 values(
'107','张可', '男','1990-05-27','003' ); insert into 管理员 values('108','李智', '男',
'1981-03-06','003' ); insert into 管理员 values('109','李明涵','女','1980-12-02', '004'
); insert into 管理员 values('110','张伟', '男','1978-08-19', '004');
<>7、查询数据
select * from 商品; select * from 仓库; select * from 库存情况; select * from 管理员;
<>8、插叙操作
--查询青岛海尔生产的商品信息。 select * from 商品 where 生产商='青岛海尔'; --查询001号仓库储存的商品的编号和数量。
select 库存情况.商品编号,数量 from 库存情况 where 仓库编号='001' --查询所有商品的种类名称。 select distinct
商品名称from 商品; --查询商品的单价在2000到3000之间的商品信息。 select * from 商品 where 单价 between 2000
and 3000; --查询所有商品的信息，其中单价打八折显示。 select 商品编号,商品名称,单价=单价*0.8,生产商 from 商品;
--查询青岛海尔和青岛海信生产的商品的信息。 select * from 商品 where 生产商='青岛海尔' or 生产商='青岛海信';
--查询李立平管理的仓库存储的商品信息。 select 商品.* from 商品,库存情况,管理员 where 管理员姓名='李立平' and 管理员.仓库编号
=库存情况.仓库编号 and 库存情况.商品编号=商品.商品编号; --查询2号楼101仓库的管理员的姓名和年龄。 select 管理员姓名,year(
getdate())-year(出生年月) as 年龄 from 管理员,仓库 where 仓库地址='2号楼101' and 仓库.仓库编号=管理员.仓库编号
--查询不是青岛生产的商品的信息。 select * from 商品 where 生产商 not like '%青岛%'; --查询库存总量最少的仓库的编号。
--方法一 select 仓库编号 from 仓库 where 容量=(select min(容量) from 仓库); --方法二 select 仓库编号
from 仓库 order by 容量 asc limit 1; --查询各生产厂家的商品库存总量。 select 生产商,sum(数量) as 库存总量
from 商品,库存情况 where 商品.商品编号=库存情况.商品编号 group by 生产商; --将2号楼101仓库的管理员的姓名改为"张伟"。
update 管理员 set 管理员姓名='张伟' where 仓库编号=(select 仓库编号 from 仓库 where 仓库地址='2号楼101');
select * from 管理员; --删除四川长虹的产品的库存信息。 delete 库存情况 where 商品编号=(select 商品编号 from 商品
where 生产商='四川长虹'); select * from 库存情况; --查询每个仓库存放的商品品种数。 select 仓库编号,count(商品编号)
as 商品品种数 from 库存情况 group by 仓库编号; --仓库容量增加百分之5。 Update 仓库 set 容量=容量*(1+0.05);
select * from 仓库;

• « 上一篇：Transformer的具体计算过程
• » 下一篇：关于 MsgWaitForMultipleObjects 的理解