“SQL中的各种连接 演示数据”的版本间的差异

来自姬鸿昌的知识库
跳到导航 跳到搜索
（建立内容为“1”的新页面）
 
第1行： 第1行：
−
1
+
=== 演示数据 ===
 +
 
 +
==== 学生表和学生数据 ====
 +
<syntaxhighlight lang="sql">
 +
CREATE TABLE `student` (
 +
  `sid` int(11) NOT NULL AUTO_INCREMENT,
 +
  `snum` int(11) NOT NULL,
 +
  `sname` varchar(20) NOT NULL,
 +
  `sage` tinyint(4) DEFAULT NULL,
 +
  `sclass` smallint(6) NOT NULL,
 +
  PRIMARY KEY (`snum`),
 +
  UNIQUE KEY `student_num` (`sid`)
 +
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 +
 
 +
INSERT INTO `sql`.`student`
 +
(`snum`,
 +
`sname`,
 +
`sage`,
 +
`sclass`)
 +
VALUES
 +
(20201101,'张三',20,150),
 +
(20201102,'李四',18,151),
 +
(20201103,'王五',19,151),
 +
(20201104,'赵六',18,150),
 +
(20201105,'钱七',21,151),
 +
(20201106,'孙八',20,152);
 +
 
 +
</syntaxhighlight>
 +
 
 +
==== 课程表和课程数据 ====
 +
<syntaxhighlight lang="sql">
 +
create table course(
 +
cid int not null auto_increment,
 +
cnum int not null primary key,
 +
cname varchar(20) not null,
 +
unique key course_num (cid)
 +
) engine = innodb auto_increment = 1 default charset = utf8;
 +
 
 +
INSERT INTO `sql`.`course`
 +
(`cnum`,
 +
`cname`)
 +
VALUES
 +
(101, '数据结构'),
 +
(102, '编译原理'),
 +
(103, '计算机网络'),
 +
(105, '计算机组成原理'),
 +
(106, '数据库');
 +
</syntaxhighlight>
 +
 
 +
==== 学生课程关系表 ====
 +
<syntaxhighlight lang="sql">
 +
CREATE TABLE `sc` (
 +
  `scid` int(11) NOT NULL AUTO_INCREMENT,
 +
  `snum` int(11) NOT NULL,
 +
  `cnum` int(11) NOT NULL,
 +
  `grade` smallint(6) DEFAULT NULL,
 +
  PRIMARY KEY (`snum`,`cnum`),
 +
  UNIQUE KEY `scid` (`scid`)
 +
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 +
 
 +
INSERT INTO `sql`.`sc`
 +
(`snum`,
 +
`cnum`,
 +
`grade`)
 +
VALUES
 +
(20201101, 101, 85),
 +
(20201102, 101, 55),
 +
(20201103, 101, 90),
 +
(20201101, 102, 88),
 +
(20201102, 102, 75),
 +
(20201103, 102, 58),
 +
(20201101, 103, 72),
 +
(20201102, 103, 80),
 +
(20201103, 103, 92);
 +
</syntaxhighlight>

2022年11月3日 (四) 14:11的版本

演示数据

学生表和学生数据

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `snum` int(11) NOT NULL,
  `sname` varchar(20) NOT NULL,
  `sage` tinyint(4) DEFAULT NULL,
  `sclass` smallint(6) NOT NULL,
  PRIMARY KEY (`snum`),
  UNIQUE KEY `student_num` (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sql`.`student`
(`snum`,
`sname`,
`sage`,
`sclass`)
VALUES
(20201101,'张三',20,150),
(20201102,'李四',18,151),
(20201103,'王五',19,151),
(20201104,'赵六',18,150),
(20201105,'钱七',21,151),
(20201106,'孙八',20,152);

课程表和课程数据

create table course(
cid int not null auto_increment,
cnum int not null primary key,
cname varchar(20) not null,
unique key course_num (cid)
) engine = innodb auto_increment = 1 default charset = utf8;

INSERT INTO `sql`.`course`
(`cnum`,
`cname`)
VALUES
(101, '数据结构'),
(102, '编译原理'),
(103, '计算机网络'),
(105, '计算机组成原理'),
(106, '数据库');

学生课程关系表

CREATE TABLE `sc` (
  `scid` int(11) NOT NULL AUTO_INCREMENT,
  `snum` int(11) NOT NULL,
  `cnum` int(11) NOT NULL,
  `grade` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`snum`,`cnum`),
  UNIQUE KEY `scid` (`scid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sql`.`sc`
(`snum`,
`cnum`,
`grade`)
VALUES
(20201101, 101, 85),
(20201102, 101, 55),
(20201103, 101, 90),
(20201101, 102, 88),
(20201102, 102, 75),
(20201103, 102, 58),
(20201101, 103, 72),
(20201102, 103, 80),
(20201103, 103, 92);
取自“https://www.jihongchang.top/index.php?title=SQL中的各种连接_演示数据&oldid=3236”