数据库第四讲查询笔记

数据库笔记

---

第四讲 数据查询

--创建数据库
CREATE DATABASE StudentSys;
GO
--创建表
USE StudentSys;
CREATE TABLE student                                                  
(
sno CHAR(6) PRIMARY KEY,
sname VARCHAR(8) not null,
ssex CHAR(2) not null,
sage TINYINT not null,
sdept VARCHAR(20)
);
CREATE TABLE course
(
cno CHAR(4) PRIMARY KEY,
cname VARCHAR(30) UNIQUE not null,
cpno CHAR(4),
ccredit TINYINT not null,
FOREIGN KEY (cpno) REFERENCES course(cno)
);
CREATE TABLE sc
(
sno CHAR(6),
cno CHAR(4),
grade TINYINT,
PRIMARY KEY(sno,cno),
FOREIGN KEY (sno) REFERENCES student(sno),
FOREIGN KEY (cno) REFERENCES course(cno)
);

--往student表中插入记录
INSERT INTO student(sno,sname,ssex,sage,sdept)
VALUES('990001','李勇','男',20,'计算机系');
INSERT INTO student(sno,sname,ssex,sage,sdept)
VALUES('990002','刘晨','女',19,'计算机系');
INSERT INTO student(sno,sname,ssex,sage,sdept)
VALUES('990003','王敏','女',18,'数学系');
INSERT INTO student(sno,sname,ssex,sage,sdept)
VALUES('990004','欧阳珊','女',19,'数学系');
INSERT INTO student(sno,sname,ssex,sage,sdept)
VALUES('990005','张立','男',19,'信息系');
INSERT INTO student(sno,sname,ssex,sage,sdept)
VALUES('990006','欧阳小峰','男',20,'信息系');

--往course表中插入记录
INSERT INTO course(cno,cname,cpno,ccredit)
VALUES('C001','数据库',NULL,4);
INSERT INTO course(cno,cname,cpno,ccredit)
VALUES('C002','数学',NULL,2);
INSERT INTO course(cno,cname,cpno,ccredit)
VALUES('C003','信息系统',NULL,4);
INSERT INTO course(cno,cname,cpno,ccredit)
VALUES('C004','操作系统',NULL,3);
INSERT INTO course(cno,cname,cpno,ccredit)
VALUES('C005','数据结构',NULL,4);
INSERT INTO course(cno,cname,cpno,ccredit)
VALUES('C006','数据处理',NULL,2);
INSERT INTO course(cno,cname,cpno,ccredit)
VALUES('C007','DB_Design',NULL,4);
UPDATE course SET cpno='C005' WHERE cno='C001';
UPDATE course SET cpno='C001' WHERE cno='C003';
UPDATE course SET cpno='C006' WHERE cno='C004';
UPDATE course SET cpno='C007' WHERE cno='C005';
UPDATE course SET cpno='C006' WHERE cno='C007';

--往sc表中插入记录
INSERT INTO SC(sno,cno,grade)
VALUES('990001','C001',92);
INSERT INTO SC(sno,cno,grade)
VALUES('990001','C002',93);
INSERT INTO SC(sno,cno,grade)
VALUES('990002','C001',80);
INSERT INTO SC(sno,cno,grade)
VALUES('990002','C002',82);
INSERT INTO SC(sno,cno,grade)
VALUES('990003','C001',70);
INSERT INTO SC(sno,cno,grade)
VALUES('990003','C002',90);
INSERT INTO SC(sno,cno,grade)
VALUES('990003','C003',90);
INSERT INTO SC(sno,cno,grade)
VALUES('990003','C004',82);
INSERT INTO SC(sno,cno,grade)
VALUES('990003','C005',75);
INSERT INTO SC(sno,cno,grade)
VALUES('990003','C006',90);
INSERT INTO SC(sno,cno,grade)
VALUES('990003','C007',88);

---

1.SQL 是什么？

• SQL，指的是结构化查询语言，全称是 Structured Query Language。