SQL
SQL Server
SQL server查询操作技巧大全:https://www.jb51.net/Special/595.htm
视频:https://www.bilibili.com/video/BV1ct411G78W?t=80&p=11
简单增删改
创建表格
专业表:major
mno(专业号)、mname(专业名)
学生表:stu
sno(学号)、sname(姓名)、age(年龄)、sex(性别)、 mno(专业号)
课程表:cou
cno(课程号)、cname(课程名)、ctime(学时)、ccredit(学分)
成绩表:sc
sno(学号)、cno(课程号)、grade(成绩)
create database stuinfo
--创建stuinfo数据库
----------------------------------------------------
use stuinfo
--打开stuinfo数据库
----------------------------------------------------
create table major(
mno int primary key,
--primary key:设置为主键
mname varchar(30)
)
----------------------------------------------------
create table stu(
sno varchar(13) primary key,
sname varchar(30) not null,
age int ,
sex bit,
mno int,
foreign key(mno) references major(mno)
--foreign key:设置为外键,foreign key后面的mno指的是stu表中的mno字段
)
----------------------------------------------------
create table cou(
cno varchar(13) primary key,
cname varchar(30) not null,
ctime int,
ccredit decimal(5,2)
)
----------------------------------------------------
create table sc(
cno varchar(13),
sno varchar(13),
grade decimal(5,2),
primary key(cno,sno),
foreign key(cno) references cou(cno),
foreign key(sno) references stu(sno),
)
----------------------------------------------------
alter table sc add constraint fk_sc foreign key(cno) references cou(cno)
--表建成后,给sc表添加外键
添加数据
select * from major
insert into major(mno,mname) values(1,'计算机科学与技术');
insert into major(mno,mname) values(2,'软件工程');
insert into major(mno,mname) values(3,'网络工程');
insert into major(mno,mname) values(4,'智能科学与技术');
----------------------------------------------------
select * from stu
insert into stu values('2020001','小一',18,0,1);
insert into stu values('2020002','小二',18,1,1);
insert into stu values('2020003','小四',18,1,1);
insert into stu values('2020004','小五',18,1,1);
insert into stu values('2020005','小六',18,0,2);
insert into stu values('2020006','小七',18,1,2);
insert into stu values('2020007','小八',18,0,2);
insert into stu values('2020008','小九',18,1,2);
insert into stu values('2020009','小十',19,0,3);
insert into stu values('20200010','小十',20,0,3);
insert into stu values('20200011','小快',19,0,3);
insert into stu values('20200012','小冬',21,0,3);
insert into stu values('20200013','小宇',19,0,null);
insert into stu values('20200014','小点',19,0,4);
insert into stu values('20200015','彭杰',21,0,4);
insert into stu values('20200016','彭小杰',21,0,4);
----------------------------------------------------
select * from cou
insert into cou values('20201','C语言',32,5);
insert into cou values('20202','C#',32,3);
insert into cou values(