[大数据]MySQL实例1

建三个表

create table student(
    -> sid char(3) primary key,
    -> sname varchar(3),
    -> ssex char(1),
    -> sage numeric(2)
    -> );

create table course(
    -> cid char(3) primary key,
    -> cname char(3),
    -> cteachar char(3)
    -> );

create table sc(
    -> sid char(3),
    -> cid char(3),
    -> score numeric(3),
    -> foreign key(sid) references student(sid),
    -> foreign key(cid) references course(cid)
    -> );

student表中插入数据

insert into student values('100','张三','男',18);
insert into student values('101','李四','女',19);
insert into student values('102','王五','男',20);
insert into student values('103','赵六','女',22);
insert into student values('104','白七','男',24);

查看student表中内容

select * from student;

?course表中插入数据

insert into course values('001','高数','张老师');
insert into course values('002','离散','李老师');
insert into course values('003','c语言','董老师');

?查看course表内容

?sc表插入数据

insert into sc values('100','001',98);
insert into sc values('101','002',61);
insert into sc values('100','003',55);
insert into sc values('101','002',77);
insert into sc values('101','003',99);
insert into sc values('102','001',88);
insert into sc values('103','001',77);
insert into sc values('103','003',60);
insert into sc values('104','002',100);

?查看sc表内容

?question1：查询所有学生的学号，姓名，性别，年龄

select sid,sname,ssex,sage from student;

?question2：在学生表中添加“专业”列

alter table student add column smajor varchar(10);

?question3：写出计算并显示高数的最高分，最低分，平均分的命令

select min(score),max(score),avg(score) from sc;

question4：选出即选修了离散数学的，又选修了C语言的

select sid,sname,ssex,sage from student 
    -> where sid in(select sid from sc where cid ='001' and
    -> sid in(select sid from sc where cid='003')
    -> );