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