MySQL练习

最新推荐文章于 2023-04-27 11:33:58 发布

Keep time

最新推荐文章于 2023-04-27 11:33:58 发布

阅读量1.7k

点赞数 1

文章标签： mysql java

本文链接：https://blog.csdn.net/weixin_51985152/article/details/124157961

版权

创建数据库company,在库中创建两个表offices和employees表
查看该库下几个表以及查看两张表结构。
将表employees的mobile字段修改到officeCode字段后面。
将表employees的birth字段改名为employee_birth。
修改sex字段，数据类型为CHAR(1)，非空约束。
删除字段note。
增加字段名favoriate_activity，数据类型为VARCHAR(100)。
删除表offices。
将表employees名称修改为employees_info。

-- 1. 创建数据库company,在库中创建两个表offices和employees表 --
create database company;
use company;
create table offices(
	officeCode int(10) PRIMARY KEY NOT NULL UNIQUE,
	city VARCHAR(50) NOT NULL,
	address VARCHAR(50),
	country VARCHAR(50) NOT NULL,
	postalCode VARCHAR(15) UNIQUE
); 
create table employees(
	employeeNumber int(11) PRIMARY KEY not null unique auto_increment,
	lastName VARCHAR(50) not null,
	firstName VARCHAR(50) not null,
	mobile VARCHAR(25) unique,
	officeCode int(10) not null,
	jobTitle varchar(50) not null,
	birth datetime not null,
	note varchar(255),
	sex varchar(5)
);

-- 2. 查看该库下几个表以及查看两张表结构。 --
show tables;
desc employees;
desc offices;

-- 3. 将表employees的mobile字段修改到officeCode字段后面。 --
alter table employees modify mobile varchar(25) after officeCode;

-- 4. 将表employees的birth字段改名为employee_birth。 --
alter table employees change birth employee_birth datetime;

-- 5. 修改sex字段，数据类型为CHAR(1)，非空约束。 --
alter table employees modify sex char(1) not null;

-- 6. 删除字段note。 --
alter table employees drop note;

-- 7. 增加字段名favoriate_activity，数据类型为VARCHAR(100)。 --
alter table employees add favority_activity varchar(100);

-- 8. 删除表offices。 --
drop table offices;

-- 9. 将表employees名称修改为employees_info。 --
alter table employees rename to employees_info;

创建数据表customers，在c_num字段上添加主键约束和自增约束，在c_birth字段上添加非空约束。
将c_contact字段插入c_birth字段后面。
将c_name字段数据类型改为VARCHAR(70)。
将c_contact字段改名为c_phone。
增加c_gender字段，数据类型为CHAR(1)。
将表名修改为customers_info。
删除字段c_city。
修改数据表的存储引擎为MyISAM。
在Market中创建数据表orders。
创建数据表orders，在o_num字段上添加主键约束和自增约束，在 c_id字段上添加外键约束，关联customers表中的主键c_num。
删除orders表的外键约束，然后删除表customers。

-- 1. 创建数据表customers，在c_num字段上添加主键约束和自增约 束，在c_birth字段上添加非空约束。 --
create database Market;
use Market;
create table customers(
	c_num int(11) primary key not null unique auto_increment,
	c_name varchar(50),
	c_contact varchar(50),
	c_city varchar(50),
	c_birth datetime not null
);

desc customers;

-- 2. 将c_contact字段插入c_birth字段后面。 --
alter table customers modify c_contact varchar(50) after c_birth;

-- 3. 将c_name字段数据类型改为VARCHAR(70)。 --
alter table customers modify c_name varchar(70);

-- 4. 将c_contact字段改名为c_phone。 --
alter table customers change c_contact c_phone varchar(50);

-- 5. 增加c_gender字段，数据类型为CHAR(1)。 --
alter table customers add c_gender char(1);

-- 6. 将表名修改为customers_info。 --
alter table customers rename to customers_info;

-- 7. 删除字段c_city。 --
alter table customers_info drop c_city;

-- 8. 修改数据表的存储引擎为MyISAM。 --
alter table customers_info engine=MyISAM;
alter table orders engine=INNODB;

-- 9. 在Market中创建数据表orders。 --
create table orders(
	o_num int(11) primary key not null unique auto_increment,
	o_date date,
	c_id int(11)
);

-- 10. 创建数据表orders，在o_num字段上添加主键约束和自增约束，在 c_id字段上添加外键约束，关联customers表中的主键c_num。 --
alter table orders modify o_num int(11) auto_increment primary key;
alter table orders add constraint FK_orders_cid foreign key(c_id) REFERENCES customers_info(c_num); -- 引擎不一样，无法创建关联

-- 11. 删除orders表的外键约束，然后删除表customers。 --
alter table orders drop foreign key FK_orders_cid;
drop table customers;

show tables;


-- 练习三 --
-- 创建数据库MySchool --
create database MySchool;
use MySchool;

-- 创建以下数据表 Grade， Student, Subjects, Result --
create table Grade(
	gradeId int primary key auto_increment,
	gradeName varchar(50) not null
);

create table Student(
	studentNo varchar(50) primary key,
	loginPwd varchar(50),
	studentName varchar(50),
	sex char(2),
	gradeId int,
	phone varchar(255),
	address varchar(255),
	bornDate datetime,
	email varchar(50),
	constraint FK_student_gradeid foreign key(gradeId) references Grade(gradeId)
);

create table Subjects(
	subjectId int primary key,
	subjectName varchar(20) not null,
	classHour int not null,
	gradeId int not null
);

create table Result(
	Id int primary key auto_increment,
	studentNo varchar(50) not null,
	subjectId int not null,
	studentResult int,
	examDate datetime not null,
	constraint FK_result_No foreign key(studentNo) references Student(studentNo),
	constraint FK_result_Id foreign key(subjectId) references Subjects(subjectId)
);

练习：数据修改与删除

将地址是西安市的更新为西安科技二路
将S1001的email修改为空的字符串
将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
将S1001 ，课程编号为 2 的成绩提高 5分
将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
经核实 S1004 课程编号为2 的缺考
将计算机网络课程删除掉
数据查询【部分函数后面章节介绍，学习完毕后继续完成】：
1、查询全部一年级的学生信息。
2、查询全部二年级的学生的姓名和电话。
3、查询全部一年级女同学的信息。
4、查询课时超过60的科目信息。
5、查询二年级的科目名称
6、查询二年级男同学的姓名和住址。
7、查询无电子邮件的学生姓名和年级信息。
8、查询出生日期在1992年之后的男学生姓名和年级信息。
9、参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
10、按照出生日期查询一年级的学生信息。
11、按成绩由高到低的次序查询参加编号为1的科目考试信息。
12、查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
13、查询课时最多的科目名称及课时。
14、查询年龄最小的学生所在的年级及姓名。
15、查询考试的最低分出现在哪个科目
16、查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
17、查询年龄超过25周岁的学生信息。
18、查询1月份过生日的学生信息
19、查询今天过生日的学生姓名及所在年级。
20、新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com
21、查询住址为“雁塔区”的学生姓名、电话、住址
22、查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
23、查询电话中含有以“130”开头的学生姓名，住址和电话。
24、查询姓“赵”的学号、姓名和住址。
25、统计一年级女生的总人数。
26、查询李四总成绩
27、学号为s1003的学生所有课总成绩
28、学号为s1003的学生考试的平均分。
29、查询一年级的科目“Mysql”的最高分、最低分、平均分。
30、查询每个年级的总学时数，并按照升序排列。
31、查询每个参加考试的学员的平均分。（Group by 学号）
32、查询每门课程的平均分，并按照降序排列。（group by 课程）
33、查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
34、查询一年级的平均年龄。
35、查询每个年级西安地区的学生人数。
36、查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
37、查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
38、查询学生姓名、所属年级名称及联系电话。
39、查询年级编号为1的科目名称、年级名称及学时。
40、查询参加科目编号为1的考试的学生姓名、分数、考试日期。
41、查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
42、查询所有科目的参考信息（某些科目可能还没有被考试过）
43、查询没有被考过的科目信息。

create database student;
use student;
create table Students(
	studentNo varchar(20) primary key,
	studentName varchar(20),
	loginPassword varchar(50),
	sex varchar(5),
	phone varchar(200),
	address varchar(20) default '宿舍',
	born datetime,
	email varchar(200),
	gradeId int(5)
);

insert into Students values
('s1001','张三','zhagnsan','男','13022225555',default,'1994-01-01',null,1),
('s1002','李四','lisi','男','13266669999',default,'1991-07-07',null,1),
('s1003','张丽','123456','女','13099999999',default,'1992-05-06',null,1),
('s1004','王磊','123456','男','15066668888','西安','1991-06-07',null,1),
('s1005','张丹','zhangdan','女','15036998888',default,'1992-06-07',null,1),
('s1006','李克','123456','男','15022226669','西安市雁塔区','1993-12-01','liliang@126.com',1),
('s1007','李丹','123456','女','15036699965',default,'1992-11-11','201612011419478126com',1),
('s1008','王亮','123456','男','15022223333','西安市','1993-12-02',null,2),
('s1009','赵龙','123456','男','13022229999','西安市','1992-06-07',null,2),
('s1010','徐丹','123456','女','15899996666',default,'1993-05-06',null,2);

create table Subjects (
	subjectId int(5) primary key auto_increment,
	subjectName varchar(200) not null,
	classHour int(10) not null,
	gradeId int(5)
);

insert into Subjects values 
(null,'MySQL深入',65,1),
(null,'C语言面向过程',45,1),
(null,'计算机基本原理',70,1),
(null,'毛邓概论',61,1),
(null,'英语',55,1),
(null,'jap',40,2),
(null,'数据结构',60,2),
(null,'oracle',65,2),
(null,'计算机网络',50,2);

create table Achievements(
	id int(10) primary key auto_increment,
	studentNo varchar(20),
	subjectId int(5),
	studentScore varchar(10),
	examDate datetime
);

alter table Achievements add constraint fk_Students_Achievements foreign key (studentNo) references Students(studentNo);
alter table Achievements add constraint fk_Subjects_Achievements foreign key (subjectId) references Subjects(subjectId);

insert into Achievements values
(null,'s1001',1,'80','2015-07-01'),
(null,'s1002',1,'40','2015-07-01'),
(null,'s1001',2,'10','2015-07-01'),
(null,'s1002',2,'20','2015-07-01'),
(null,'s1003',1,'60','2015-07-01'),
(null,'s1001',3,'82','2015-07-03'),
(null,'s1001',4,'90','2015-07-03'),
(null,'s1001',5,'75','2015-07-01'),
(null,'s1002',3,'65','2015-07-03'),
(null,'s1002',4,'35','2015-07-03'),
(null,'s1002',5,'87','2015-07-01'),
(null,'s1003',2,'65','2015-07-01'),
(null,'s1003',3,'45','2015-07-03'),
(null,'s1003',4,'92','2015-07-03'),
(null,'s1003',5,'55','2015-07-01'),
(null,'s1004',1,'65','2015-07-01'),
(null,'s1004',2,'85','2015-07-01'),
(null,'s1004',3,'45','2015-07-03');

desc Students;
select * from Students;
select * from Subjects;
select * from Achievements;

-- 1. 将地址是西安市的更新为 西安科技二路
update Students set address = '西安科技二路' where address = '西安市';

-- 2. 将S1001的email修改为空的字符串
update Students set email = '' where studentNo = 'S1001';

-- 3. 将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
update Subjects set subjectName = 'java基础', classHour = 60, gradeId = 2 where subjectId = 2;

-- 4. 将S1001 ，课程编号为 2 的成绩 提高 5分 
update Achievements set studentScore = studentScore + 5 where studentNo = 'S1001' and subjectId = 2;

-- 5. 将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
update Achievements set studentScore = 60,examDate = '2015-10-10' where studentNo = 'S1004' and subjectId = 3;

-- 6. 经核实 S1004 课程编号为2 的 缺考
update Achievements set studentScore = null where studentNo = 'S1004' and subjectId = 2;

-- 7. 将计算机网络课程删除掉
delete from subjects where subjectName = '计算机网络';

-- 查询 --
-- 1、 查询全部一年级的学生信息。
select * from students where gradeId = 1;

-- 2、 查询全部二年级的学生的姓名和电话。
select studentName, phone from students where gradeId = 2;

-- 3、 查询全部一年级女同学的信息。
select * from students where gradeId = 1 and sex = '女';

-- 4、 查询课时超过60的科目信息。
select * from subjects where classHour > 60;

-- 5、 查询二年级的科目名称
select * from subjects where gradeId = 2;

-- 6、 查询二年级男同学的姓名和住址。
select studentName, address from students where gradeId = 2 and sex = '男';

-- 7、 查询无电子邮件的学生姓名和年级信息。
select studentName,gradeId from students where email is null;

-- 8、 查询出生日期在1992年之后的男学生姓名和年级信息。
select studentName,gradeId from students where sex = '男' and born >= '1992-01-01';

-- 9、 参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息
select * from Achievements where examDate = '2015-07-03' and subjectId in (select subjectId from subjects where subjectName = '计算机基本原理');

-- 10、 按照出生日期查询一年级的学生信息。
select * from students where gradeId = 1 order by born;

-- 11、 按成绩由高到低的次序查询参加编号为1的科目考试信息。
select * from Achievements where subjectId = 1 order by studentScore desc;

-- 12、 查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
select * from Achievements where subjectId in (select subjectId from subjects where subjectName = 'MySQL深入') and examDate = '2015-07-01' order by studentScore desc limit 2;

-- 13、 查询课时最多的科目名称及课时。
select subjectName, classHour from subjects order by classHour desc limit 1;

-- 14、 查询年龄最小的学生所在的年级及姓名。
select gradeId, studentName from students order by born desc limit 1;

-- 15、 查询考试的最低分出现在哪个科目
select subjectName from subjects where subjectId = (select subjectId from Achievements order by studentScore limit 1);

-- 16、 查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
select * from Achievements where studentNo = 'S1001' order by examDate;

-- 17、 查询年龄超过25周岁的学生信息。
select * from students where (YEAR(NOW()) - YEAR(born)) > 25;

-- 18、 查询1月份过生日的学生信息
select * from students where MONTH(born) = 1;

-- 19、 查询今天过生日的学生姓名及所在年级。
select studentName,gradeId from students where MONTH(born) = MONTH(NOW()) and DAY(born) = DAY(NOW());

-- 20、 新生入学，为其分配一个Email地址，规则如下：S1+当前日期 +@bd.com

-- 21、 查询住址为“雁塔区”的学生姓名、电话、住址
select studentName,phone,address from students where address like '%雁塔区%';

-- 22、 查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
select subjectName,classHour,gradeId from subjects where subjectName like '%计算机%' order by gradeId;

-- 23、 查询电话中含有以“130”开头的学生姓名，住址和电话。
select studentName,address,phone from students where phone like '130%';

-- 24、 查询姓“赵”的学号、姓名和住址。
select studentNo,studentName,address from students where studentName like '赵%';

-- 25、 统计一年级女生的总人数。
select count(studentNo) '总人数' from students where gradeId = 1 and sex = '女';

-- 26、 查询李四总成绩
select sum(studentScore) '总成绩' from Achievements where studentNo = (select studentNo from students where studentName = '李四');

-- 27、 学号为s1003的学生所有课总成绩
select sum(studentScore) as '总成绩' from Achievements where studentNo = 'S1003';

-- 28、 学号为s1003的学生考试的平均分。
select avg(studentScore) '平均分' from Achievements where studentNo = 'S1003';

-- 29、 查询一年级的科目“Mysql”的最高分、最低分、平均分。
select max(studentScore) '最高分', min(studentScore) '最低分',avg(studentScore) '最低分' from Achievements where subjectId = (select subjectId from subjects where subjectName = 'MySQL深入');

-- 30、 查询每个年级的总学时数，并按照升序排列。
select sum(classHour) '总学时',gradeId from subjects group by gradeId order by sum(classHour);

-- 31、 查询每个参加考试的学员的平均分。（Group by 学号）
select studentNo, avg(studentScore) '平均分' from Achievements GROUP BY studentNo;

-- 32、 查询每门课程的平均分，并按照降序排列。（group by 课程）
select avg(studentScore) '平均分', subjectId from Achievements group by subjectId order by avg(studentScore) desc;

-- 33、 查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）
select sum(studentScore) '总分', studentNo from Achievements group by studentNo order by '总分' desc;

-- 34、 查询一年级的平均年龄。
select avg(YEAR(NOW()) - YEAR(born)) '平均年龄' from students where gradeId = 1;

-- 35、 查询每个年级西安地区的学生人数。
select count(studentName) '学生人数', gradeId from students where address like '%西安%' group by gradeId;

-- 36、 查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
select avg(studentScore) '平均分', studentNo from Achievements GROUP BY studentNo HAVING avg(studentScore) >= 60 order by '平均分' desc; 

-- 37、 查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。
select studentNo, count(studentScore) '不及格次数' from Achievements where studentScore < 60 group by studentNo having count(studentScore) >= 1;

-- 38、 查询学生姓名、所属年级名称及联系电话。
select studentName,gradeId,phone from students;

-- 39、 查询年级编号为1的科目名称、年级名称及学时。
select subjectName,gradeId,classHour from subjects where gradeId = 1;

-- 40、 查询参加科目编号为1的考试的学生姓名、分数、考试日期。
select studentNo,studentScore,examDate from Achievements where subjectId = 1;

-- 41、 查询学号为s1001的学生参加的考试科目名称、分数、考试日期。
select subjectId,studentScore,examDate from Achievements where studentNo = 's1001';

-- 42、 查询所有科目的参考信息（某些科目可能还没有被考试过）


-- 43、 查询没有被考过的科目信息。

Keep time

关注

1
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
MySQL练习

创建数据库company,在库中创建两个表offices和employees表查看该库下几个表以及查看两张表结构。将表employees的mobile字段修改到officeCode字段后面。将表employees的birth字段改名为employee_birth。修改sex字段，数据类型为CHAR(1)，非空约束。删除字段note。增加字段名favoriate_activity，数据类型为VARCHAR(100)。删除表offices。将表employees名称修改为employees_i.
复制链接

扫一扫

MySQL练习

“相关推荐”对你有帮助么？