MySQL查询操作

242030

已于 2023-12-04 14:16:01 修改

阅读量7.8k

点赞数 13

分类专栏：数据库文章标签：数据库

于 2019-10-24 20:57:47 首次发布

本文链接：https://blog.csdn.net/qq_30614345/article/details/100548513

版权

数据库专栏收录该内容

19 篇文章 2 订阅

订阅专栏

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 仓库;