数据库的增删查改进阶
create table student(
    id int not null primary key auto_increment,
    name varchar(10) default 'unknown',
    sn int unique,
    qq_email varchar(10) not null
);

create table student(
    id int primary key auto_increment,
    name varchar(10) default 'unknown',
    sn int unique,
    qq_email varchar(10) not null,
    classes_id int,
    foreign key (classes_id) references classes(id)
);
create table classes(
    id int primary key auto_increment,
    name varchar(10) default 'unknown'
);

create table coures(
    id int primary key,
    name varchar(10) default 'unknown'
);

create table score(
    id int primary key,
    score decimal(3,1),
    student_id int,
    coures_id int,
    foreign key (student_id) references student(id),
    foreign key (coures_id) references coures(id)
);
INSERT INTO student (id, Chinese,English,Math) VALUES
 (1,'唐三藏', 67, 98, 56),
 (2,'孙悟空', 87.5, 78, 77),
 (3,'猪悟能', 88, 98.5, 90),
 (4,'曹孟德', 82, 84, 67),
 (5,'刘玄德', 55.5, 85, 45),
 (6,'孙权', 70, 73, 78.5),
 (7,'宋公明',71,82,93);
  
  INSERT INTO student (id,name,sn,qq_email) VALUES
  (001,'唐三藏',1001,'101@'),
  (002,'孙悟空',1002,'102@'),
  (003,'猪悟能',1003,'103@'),
  (004,'曹孟德',1004,'104@'),
  (005,'刘玄德',1005,'105@'),
  (006,'孙权',1006,'106@'),
  (007,'宋公明',1007,'107@');

create table test_user(
    id int primary key auto_increment,
    name varchar(10) default 'unknown',
    age int comment '年龄',
    email varchar(10),
    sex varchar(1) comment '年龄',
    mobile varchar(20) comment '手机号'
);

INSERT INTO test_user (name ,email) select name , qq_email from student;

create table student_information(
 id int,
    name varchar(10) default 'unknown',
    sn int,
    email varchar(10),
    Chinese decimal(3,1),
    English decimal(3,1),
    Math decimal(3,1)
);
INSERT INTO student_information (id,sn, Chinese,English,Math) VALUES
 (1,001, 67, 98, 56),
 (2,002, 87.5, 78, 77),
 (3,003, 88, 98.5, 90),
 (4,004, 82, 84, 67),
 (5,005, 55.5, 85, 45),
 (6,006, 70, 73, 78.5),
 (7,007,71,82,93);

INSERT INTO  student_information(name,email) select name,qq_email from student;

create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);
insert into emp(name, role, salary) values
 ('马云','服务员',1000.20),
('马化腾','游戏陪玩',2000.99),
('孙悟空','游戏角色',999.11),
('猪无能','游戏角色',333.5),
('沙和尚','游戏角色',700.33),
('隔壁老王','董事长',12000.66);

select role,max(salary),min(salary),avg(salary) from emp group by role;

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1000;

insert into classes(name, coures) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values 
('09982','黑旋风李逵','[email protected]',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','[email protected]',1),
('00054','不想毕业',null,1),
('51234','好好说话','[email protected]',2),
('83223','tellme',null,2),
('09527','老外学中文','[email protected]',2);

insert into coures(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, coures_id) values 
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),-- tellme
(80, 7, 2),(92, 7, 6);

insert into student(sn, name, qq_email, classes_id) values
 ('09982','黑旋风李逵','xuam',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','[email protected]',1),
('00054','不想毕业',null,1),
('51234','好好说话','[email protected]',2),
('83223','tellme',null,2),
('09527','老外学中文','for.com',2);


create table classes(
    id int primary key auto_increment,
    name varchar(20),
    coures varchar(35)
);

create table student(
 id int primary key auto_increment,
 sn varchar(10),
 name varchar(15) default 'unknown',
 qq_mail varchar(30),
 classes_id int,
 foreign key (classes_id) references classes(id)
);

create table coures(
    id int primary key auto_increment,
    name varchar(15) default 'unknown'
);

create table score(
    id int primary key auto_increment,
    score decimal(3,1),
    student_id int,
    coures_id int,
    foreign key (student_id) references student(id),
    foreign key (coures_id) references course(id)
);

create table score(
    id int primary key auto_increment,
    score decimal(3,1),
    student_id int,
    coures_id int,
    foreign key (student_id) references student(id),
    foreign key (coures_id) references coures(id)
);


select * from
student join score on student.id=score.student_id,
coures join score on coures.id=score.coures_id;

select student.id,student.sn,student.name,coures.name
from student join score on student.id=score.student_id,
coures join score coures.id=score.coures_id;

select * from student
join score on student.id=score.student_id
join classes on student.classes_id=classes.id;


select student.id,student.sn,student.qq_mail,score.score,coures.name,coures.coures from student
join score on student.id=score.student_id
join coures on student.coures_id=coures.id;

select * from score
join student on score.student_id=student.id
join coures on score.coures_id=coures.id;

select score.id,score.score,student.sn,student.name,student.qq_mail
,coures.name 所学课程 from score
join student on score.student_id=student.id
join coures on score.coures_id=coures.id;