--创建数据库
create database pei

--使用数据库
 use pei

 --创建学生信息表
 create table student
 (
 stuNO char(10) unique,--学号
 stuName char(8),--姓名
 stuAge int not null,--年龄
 stuAddress char(15),--地址
 stuSeat int,--排号
 stuSex bit,--性别
 
 )

 --创建学生面试表
 create table studentd
 (
 examNO int,--学号
 stuNO char(15),--排名
 writtenExam int,--笔试
 labExam int,--机试
 )
 
 --查询学生信息表
 select * from student
 --插入学生信息
 insert student(stuNO ,stuName, stuAge,stuAddress,stuSeat,stuSex)
 values('s2501','张秋利',20,'美国硅谷 ',1,1)
 insert student(stuNO ,stuName, stuAge,stuAddress,stuSeat,stuSex)
 values('s2502','李斯文',18,'湖北武汉',2,0)
 insert student(stuNO ,stuName, stuAge,stuAddress,stuSeat,stuSex)
 values('s2503','马文才',22,'湖南长沙',3,1)
 insert student(stuNO ,stuName, stuAge,stuAddress,stuSeat,stuSex)
 values('s2504','欧阳俊雄',21,'湖北武汉',4,0)
 insert student(stuNO ,stuName, stuAge,stuAddress,stuSeat,stuSex)
 values('s2505','梅超风',20,'湖北武汉',5,1)
 insert student(stuNO ,stuName, stuAge,stuAddress,stuSeat,stuSex)
 values('s2506',' 陈旋风',19,'美国硅谷',6,1)
 insert student(stuNO ,stuName, stuAge,stuAddress,stuSeat,stuSex)
 values('s2507','陈风',20,'美国硅谷',7,0)


 --学生分数表
 select * from studentd
 --插入学生分数
 insert studentd(examNO, stuNO, writtenExam,labExam)
 values(1,'s2501',50,50)
 insert studentd(examNO, stuNO, writtenExam,labExam)
 values(2,'s2502',60,65)
 insert studentd(examNO, stuNO, writtenExam,labExam)
 values(3,'s2503',86,85)
 insert studentd(examNO, stuNO, writtenExam,labExam)
 values(4,'s2504',40,80)
 insert studentd(examNO, stuNO, writtenExam,labExam)
 values(5,'s2505',70,90)
 insert studentd(examNO, stuNO, writtenExam,labExam)
 values(6,'s2506',85,90)

 --1.查询学生信息表的所有行所有列， 给每列取上中文名称
 select * from student--查询学生信息表的所有行所有列
 select 斯图诺=stuNO,姓名=stuName,年龄=stuAge,地址=stuAddress,排号=stuSeat,性别=stuSex from student

 --2.查询学生信息表（student）中的姓名，年龄和地址三列的信息
  select stuName,stuAge,stuAddress from student
--3.查询学生分数表（stuexam）中的学号，笔试和机试三列的信息，并为这三列取中文名字
-- 注意：要用三种方法
 select 学号= examNO, 笔试=writtenExam,机试=labExam from studentd

 select examNO as 学号,writtenExam as 笔试,labExam as 机试 from  studentd

 select examNO 学号, writtenExam 笔试,labExam 机试  from  studentd

--4.查询学生信息表（student）中的学号，姓名，地址，以及将：姓名+@+地址 组成新列 “邮箱”
 select 邮箱=stuName+stuAddress from student

--5.查询学生分数表（studentd）中的学生的学号，笔试，机试以及总分这四列的信息
 select examNO,writtenExam,labExam,总分=(writtenExam+labExam) from  studentd






 --1.查询学生的姓名，年龄，笔试成绩和机试成绩
select stuname 姓名,stuage 年龄,writtenexam 笔试成绩 ,labExam 机试成绩 from student
inner join studentd on studentd.stuNO=student.stuNO

--2.查询笔试和机试成绩都在60分以上的学生的学号，姓名，笔试成绩和机试成绩
select * from studentd
select stuname 姓名,examNO 学号,writtenexam 笔试成绩,labexam 机试成绩 from studentd
inner join student on studentd.stuNO=student.stuNO
where writtenexam>=60 and labexam>=60

--3.查询所有学生的学号，姓名，笔试成绩，机试成绩，没有参加考试的学生的成绩以NULL值填充
select stuname 姓名,examNO 学号,writtenexam 笔试成绩,labexam 机试成绩 from studentd
inner join student on studentd.stuNO=student.stuNO

--4.查询年龄在20以上（包括20）的学生的姓名，年龄，笔试成绩和机试成绩，并按笔试成绩降序排列
select stuname 姓名,stuage 年龄,writtenexam 笔试成绩,labexam 机试成绩 from studentd
inner join student on student.stuNO=studentd.stuNO
where stuage>=20
order by writtenexam desc

--5.查询男女生的机试平均分
select stusex 性别 ,avg(labexam) 机试平均分 from student
inner join studentd  on studentd.stuNO=student.stuNO
group by stusex

--6.查询男女生的笔试总分
select stusex 性别  ,sum(writtenexam) 笔试总分 from student
inner join studentd  on studentd.stuNO=student.stuNO
group by stusex