CREATE DATABASE 学生
ON
PRIMARY (NAME =学生_data,
FILENAME='C:\DB\学生_DATA.MDF',
SIZE=15MB,
MAXSIZE=30MB,
FILEGROWTH=20%)
log ON
(NAME = 学生_LOG,
FILENAME='C:\DB\学生_LOG.LDF',
SIZE=3MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
USE 学生
GO
CREATE TABLE STUDENT(SNO CHAR(5) PRIMARY KEY,
SNAME VARCHAR(8),
SSEX CHAR(2) CHECK (SSEX IN ('男','女')),
SAGE INT,
SDEPT VARCHAR(20) CHECK (SDEPT IN('数学系','计算机系','英语系')))
GO
CREATE TABLE COURSE(CNO CHAR(5) PRIMARY KEY,
CNAME VARCHAR(20),
CPNO CHAR(5),
CCREDIT SMALLINT DEFAULT 3)
GO
CREATE TABLE SG(SNO CHAR(5),
CNO CHAR(5),
GRADE SMALLINT,
CONSTRAINT C1 CHECK(GRADE BETWEEN 0 AND 100),
CONSTRAINT C2 PRIMARY KEY (SNO,CNO),
CONSTRAINT C3 FOREIGN KEY(SNO) references STUDENT(SNO),
constraint c4 foreign key(CNO) references COURSE(CNO))
GO
INSERT INTO STUDENT VALUES('1001','LS','男','18','计算机系')
GO
INSERT INTO STUDENT VALUES('1002','ZS','女','20','英语系')
GO
INSERT INTO COURSE VALUES('001','c语言','','')
GO
INSERT INTO COURSE VALUES('002','大学英语','001','4')
GO
INSERT INTO SG VALUES('1001','001','80')
GO
INSERT INTO SG VALUES('1002','002','90')
GO
SELECT STUDENT.SNO,SNAME,SSEX,GRADE
FROM STUDENT,SG
WHERE STUDENT.SNO=SG.SNO
ORDER BY SNAME ASC,GRADE DESC
GO
SELECT COURSE.CNO,MAX(GRADE),AVG(GRADE)
FROM STUDENT,SG,COURSE
WHERE STUDENT.SNO=SG.SNO AND COURSE.CNO=SG.CNO AND SG.CNO='001'
GROUP BY COURSE.CNO
GO
SELECT SDEPT
FROM STUDENT
WHERE SNAME='ZS'
GO
SELECT SNO,SNAME
FROM STUDENT
WHERE SNO=(SELECT SDEPT
FROM STUDENT
WHERE SNAME='ZS')
GO
SELECT STUDENT.*
FROM STUDENT,SG,COURSE
WHERE STUDENT.SNO=SG.SNO AND COURSE.CNO=SG.CNO AND COURSE.CNAME='英语'
GO
UPDATE STUDENT
SET SAGE=SAGE+2
WHERE SDEPT='英语系'
GO
DELETE
FROM SG
WHERE SG.SNO=(SELECT SNO
FROM STUDENT
WHERE SDEPT='英语系')
GO
CREATE VIEW STUDENTCOURSE(SNO,SNAME,CNO,CNAME,GRADE)AS
SELECT STUDENT.SNO,SNAME,COURSE.CNO,CNAME,GRADE
FROM STUDENT,SG,COURSE
WHERE STUDENT.SNO=SG.SNO AND COURSE.CNO=SG.CNO
GO
sql server建库建表插入数据查询代码
原创
©著作权归作者所有:来自51CTO博客作者blotemJ的原创作品,请联系作者获取转载授权,否则将追究法律责任
上一篇:Oracle日期综合练习
下一篇:c语言例题代码大全
![](https://ucenter.51cto.com/images/noavatar_middle.gif)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
SQL数据库入门04:数据查询操作
本文介绍基于Microsoft SQL Server软件,实现数据库表中多种数据查询方法的具体操作~
SQL 数据库 SQL Server MySQL 数据库查询 -
建库 建表 建约束 插入数据 总结
建库 建表 建约束 插入数据 数据库(仓库) 表table(货架) 行 记录 列 字段 主键 表的唯一标示,并且不能为空 外键 创建表之间的关联,如果一个列是外键则在另一个表中必定是主键 sqlserver版本 Express 免费版 Standard &
class database sqlserver table express delete