数据库练习 10.1

创建表格

 添加数据

create database test1 CHARACTER set utf8;
use test1;
create table student(
sno int,
sname varchar(50),
age int,
sex varchar(20)
)

create table studentcourse(
sno int,
cno int,
scgrade int
)

create table course(
cno int,
cname varchar(50),
cteacher varchar(50)
)


insert into student values(1,'张三',18,'男');
insert into student values(2,'李四',20,'女');
insert into student values(3,'王五',23,'男');
insert into student values(4,'赵六',22,'女');


insert into studentcourse values(1,1,50);
insert into studentcourse values(2,1,66);
insert into studentcourse values(3,1,90);
insert into studentcourse values(1,2,55);
insert into studentcourse values(2,2,68);
insert into studentcourse values(3,2,61);
insert into studentcourse values(4,2,44);
insert into studentcourse values(1,3,90);
insert into studentcourse values(2,3,78);
insert into studentcourse values(3,3,22);
insert into studentcourse values(4,3,55);


insert into course values(1,'java','何昊');
insert into course values(2,'php','李美军');
insert into course values(1,'android','王超');



select student.sname,student.age,student.sex from course INNER JOIN studentcourse on course.cno = studentcourse.cno INNER JOIN student on studentcourse.sno = student.sno where course.cteacher = '何昊' and student.sex='女';



select student.sname from course INNER JOIN studentcourse on studentcourse.cno=course.sno INNER JOIN studentcourse on studentcourse.sno = student.sno where cno not in cteacher = '何昊';

select student.sname from student where not exists(select * from studentcourse,course where studentcourse.cno = course.cno and course.cteacher='何昊' and studentcourse.sno = student.sno);

select DISTINCT student.sname from studentcourse INNER JOIN student on studentcourse.sno = student.sno where scgrade < 60;