编辑代码

drop database if exists keDB;
create database keDB;
use keDB;
drop table if exists class;
create table class(
classno int primary key auto_increment,      -- 班级编号
classname VARCHAR(20)unique,                 -- 班级名称
number varchar (1024)               -- 班级人数
)auto_increment=1;
insert into class VALUES(151102,'企业信息化一班','60');
insert into class VALUES(151103,'企业信息化二班','72');
insert into class VALUES(151104,'GIS班','34');
insert into class VALUES(151105,'网络营销班','38');
insert into class VALUES(151106,'移动互联网一班','65');
insert into class VALUES(151107,'移动互联网二班','68');
select * from class;

drop table if exists Student;
CREATE TABLE Student(
stuno int primary key auto_increment,                              -- 学生序号
stuname char(8),                        -- 学生姓名
sex char(2),                            -- 学生性别
age int,                                -- 学生年龄
address varchar(20),                    -- 家庭住址
classno char(8)                         -- 班级序号
)auto_increment=1;
insert into Student VALUES(101,'彬彬','男','18','郑州','151102');
insert into Student VALUES(102,'彭少帆','男','18','郑州','151102');
insert into Student VALUES(103,'曾敏鑫','男','20','开封','151102');
insert into Student VALUES(104,'张晶晶','女','17','洛阳','151103');
insert into Student VALUES(105,'曹业成','男','18','','151103');
insert into Student VALUES(106,'甘磊','男','19','郑州','151103');
insert into Student VALUES(107,'林小文','女','18','','151104');
insert into Student VALUES(108,'梁亮','男','18','','151105');
insert into Student VALUES(109,'陈燕珊','女','21','郑州','151106');
insert into Student VALUES(110,'韩霞','女','17','洛阳','151106');
insert into Student VALUES(111,'李小丽','女','18','郑州','151106');
insert into Student VALUES(112,'张芳','女','20','洛阳','151107');
insert into Student VALUES(113,'吴燕妮','女','19','郑州','151107');
select * from Student;

drop table if exists Coures;
CREATE TABLE Course(
couno char(3),                  -- 课程编号
couname char(30),               -- 课程名称
period int,                     -- 课程课时
teacher char(20)                -- 任课教师姓名
)auto_increment=1;
insert into Course VALUES(001,'sqlserver2008数据库基础','32','徐人凤');
insert into Course VALUES(002,'java编辑技术开发','96','陈伟斌');
insert into Course VALUES(003,'android编辑技术基础','88','李涛');
insert into Course VALUES(004,'ERP原理','24','郑星');
insert into Course VALUES(005,'PHP网站开发','72','李云婷');
insert into Course VALUES(006,'winform基础','96','叶燕');
insert into Course VALUES(007,'photoshop基础','60','郑星');
insert into Course VALUES(008,'Javascript技术应用','36','李涛');

select * from Course; 
-- 1、显示年龄小于 18 岁和大于 20 岁的学生信息。
select * from Student where age<18 and age>20;
-- 2、显示学生表中的前 10 个的学生信息

-- 3、查询名字当中第二个字为‘小’的学生信息。
select * from Student where stuname like '_小';
-- 4、查询班级表中班级的总人数。
select number,count(number) from class group by number;
-- 5、查询家庭住址在洛阳并且性别为女的学生信息。
select * from Student where address='洛阳' and sex='女';
-- 6、删除学生表中年龄为18岁的男学生。
delete from Student where age=18 and sex='男';
-- 7、将学生表中所有年龄大于等于21岁或者小于18岁的男生年龄修改为18岁。

-- 8、按班级人数升序排列班级表
select * from class order by number asc;
-- 9、按班级统计各男生和女生各有多少人？

-- 10、找出学生表中城市为空的记录
select * from Student where address = '';
-- 11、计算学生的最大年龄差为多少
select max(age)-min(age) from Student;
-- 12、统计该班级一共有多少个课时

-- 13、将课程按课时多少升序排列
select * from Course order by period asc;
-- 14、统计每个老师带多少门课程，总课时为多少？

-- 15、在学生表中将班级信息用文字替换

-- 16、在班级表中添加条记录，为‘AI2103班，50人’，班级序号依次递增

-- 17、查询没有学生信息的班级名称级人数

-- 18、查询学生为郑州男生的最小年龄
select min(age) from Student where address='郑州';
-- 19、查询课时量排名3-5为的老师的姓名及课时量

-- 20、新建‘班级排课表’