MYSQL数据库建表及查询

学生表student（S#,Sname,Sage,Ssex）学号，姓名，年龄，性别
（注：有特殊符号时例如#时用键盘1旁边的`的包住！）

create table student(
`S#` int(10)  not null auto_increment,
Sname char(10),
Sage int(10),
Ssex char(10),
primary key(`S#`)
)

教师表teacher（T#,Tname）教师编号，教师名字
课程表course（C#,Cname,T#）课程编号，课程名称，教师编号
成绩表SC（S#,C#,score）学号，课程编号，成绩
外键格式：（alter table + 表名1 add ）constraint + 外键名字 + foreign key(列名1) references 表名2(列名2)；
示例： constraint fk foreign key (bookTypeId) references t_bookType(id)

create table teachert(
`T#` int primary key not null auto_increment,
Tname char(10)
)

create table course(
`C#` int primary key not null auto_increment,
Cname char(10),
`T#` int,
constraint `fk` foreign key (`T#`) references `teachert` (`T#`)
)

create table sc(
`S#` int,
`C#` int,
score int,
constraint `fk1` foreign key (`S#`) references student(`S#`),
constraint `fk2` foreign key (`C#`) references course(`C#`)
)

insert into student values (default,‘张三’,20,‘男’)
insert into student values (default,‘李四’,‘21’,‘女’)
insert into student values (default,‘王五’,22,‘男’)
insert into student values (default,‘赵六’,‘23’,‘女’)
nsert into teachert values (default,‘牛顿’)
insert into teachert values (default,‘爱因斯坦’)

insert into course values (default,‘JAVA’,1)
insert into course values (default,‘C++’,2)
insert into course values (default,‘ORACLE’,1)

insert into sc values (1,1,50)
insert into sc values (1,2,80)
insert into sc values (1,3,90)
insert into sc values (2,1,55)
insert into sc values (2,2,40)
insert into sc values (2,3,68)
insert into sc values (3,1,89)
insert into sc values (3,2,90)
insert into sc values (3,3,95)
insert into sc values (4,1,65)
insert into sc values (4,2,66)
insert into sc values (4,3,80)

1，查询1课程比2课程成绩高的所有学生的学号
select a.S# from (select s#,score from SC where C#=‘1’) a,
(select s#,score from SC where C#=‘2’) b
where a.score>b.score and a.s#=b.s#;

2、查询课程编号为2的成绩比课程编号1的课程低的所有同学的学号、姓名

3、查询所有的课程成绩小于60的同学的学号、姓名
select S#,Sname from Student
where S# not in (select s.S# from Student s join SC on S.S#=SC.S# and score>60);

4、删除学习‘’牛顿‘’老师课的SC表记录
delete sc from course c join sc on c.C#=sc.C# join teachert t on c.T#=t.T# where t.Tname=‘牛顿’

4、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号为3的课程同学的、2号课的平均成绩