编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE student (
	id int,
	name varchar(255),
	address varchar(255),
	city varchar(255)
);

INSERT INTO student VALUES (1, '刘一', '郑十家隔壁', '河南');
INSERT INTO student VALUES (2, '陈二',  '李四家隔壁', '安徽');
INSERT INTO student VALUES (3, '张三',  '白娘子家隔壁', '杭州');
INSERT INTO student VALUES (4, '李四',  '许仙家隔壁', '杭州');
INSERT INTO student VALUES (5, '王五',  '李四家隔壁', '杭州');
INSERT INTO student VALUES (6, '赵六',  '赵六家隔壁', '杭州');
INSERT INTO student VALUES (7, '孙七',  '张三家隔壁', '杭州');
INSERT INTO student VALUES (8, '周八',  '雷峰塔附近', '杭州');
INSERT INTO student VALUES (9, '吴九',  '孙七家隔壁', '杭州');
INSERT INTO student VALUES (10, '郑十',  '周八家隔壁', '杭州');
 
SELECT * FROM student;
create database stu
default character set utf8;
--我是分割线--
use stu;
--我是分割线--
create table student(
sno char(9) primary key,
sname char(20)unique,
ssex char(2),
sage smallint,
sdept char(20)
);
--我是分割线--
create table course(
cno char(4) primary key,
cname char(40) not null,
cpno char(4),
ccredit smallint,
foreign key(cpno) references course(cno)
);
--我是分割线--
create table sc(
sno char(9) references student(sno),
cno char(4) references course(cno),
grade smallint,
primary key(sno,cno)
);
--我是分割线--
select * from student;
--我是分割线--
insert into student(sno,sname,ssex,sage,sdept)
values('202030001','李永','男',18,'CS');
--我是分割线--
select * from student;
--我是分割线--
insert into student values
('202030002','刘晨','女',17,'CS'),
('202031003','王敏','女',18,'MA'),
('202032002','张力','男',19,'IS');
--我是分割线--
select * from course;
--我是分割线--
insert into course values
('1','数据处理',Null,2),
('2','Python程序设计','1',3),
('3','数据结构','2',4),
('4','操作系统','1',4),
('5','数学',null,5),
('6','数据库','3',4),
('7','信息系统','6',2)
;
--我是分割线--
select * from course;
--我是分割线--
select * from sc;
--我是分割线--
insert into sc values
('202030001','1',92),
('202030001','2',85),
('202030001','3',88),
('202031003','1',90),
('202032002','3',80);
--我是分割线--
select * from sc;
--我是分割线--
select * from student;
--我是分割线--
select sno,sname,ssex,sage,sdept
from student;
--我是分割线--
select *
from student
where sdept='CS';
--我是分割线--
select *
from student
where sdept='MA' or sdept='IS';
--我是分割线--
select * 
from student
where sdept in('MA','IS');
--我是分割线--
select * 
from student
order by sdept desc,sage;
--我是分割线--
select count(*)
from student;
--我是分割线--
select count(distinct sno)
from sc;
--我是分割线--
select avg(grade)
from sc
where cno='1';
--我是分割线--
update student
set sage=17
where sname='张力';
--我是分割线--
update sc
set grade=grade*(1+0.05);
--我是分割线--
select sname,cno,grade
from student,sc
where student.sno=sc.sno and sname='李永';
--我是分割线--
update sc
set grade=grade+2
where sno=(select sno
from student
where sname='李永')
;
--我是分割线--
insert into sc values('20203202','2',null);
--我是分割线--
delete
from sc
where grade is null;
--我是分割线--
或者：
delete
from sc
where sno='202032002' and cno='2';
--我是分割线--
delete 
from sc;