2021-07-20

SQL SERVER学习过程中的笔记代码，每天更新（7.20）

平台：
SQL SERVER 2019 ,
Microsoft SQL Server Management Studio 18
教材与部分代码来源：数据库系统概论（第五版）

代码中注释很全了：

Student.sql

--create table
/*
CREATE TABLE STUDENT(
	SNO CHAR(9) PRIMARY KEY,
	SNAME CHAR(20) UNIQUE,
	SSEX CHAR(2),
	SAGE SMALLINT,
	SDEPT CHAR(20)
);
*/


--insert a tupple
/*
INSERT INTO student
VALUES('201215128','陈东','男',18,'IS');
*/


--delete the tupple
/*
DELETE 
FROM student
WHERE Sno = '201215128';
*/




--add constraint to current column
/*
ALTER TABLE student
ADD PRIMARY KEY(Sno);
*/


--insert more tupples into the table
/*
INSERT INTO student
VALUES('201215121','李勇','男',20,'CS'),
('201215122','刘晨','男',20,'CS'),
('201215123','王敏','女',18,'MA'),
('201215125','张立','男',19,'IS');
*/

/*
ALTER TABLE STUDENT
ADD UNIQUE(SNAME);
*/


--error:违反了 UNIQUE KEY 约束“UQ__student__52723D27F0B36CFD”。不能在对象“dbo.student”中插入重复键。重复键值为 (李勇)。
/*
INSERT INTO student
VALUES('201215130','李勇','女',18,'CS');
*/


--show the table
SELECT * 
FROM student;

Course.sql

/*
CREATE TABLE COURSE(
	CNO CHAR(4),
	CNAME CHAR(40) NOT NULL,
	CPNO CHAR(4),
	CREADIT SMALLINT,
	PRIMARY KEY(CNO),
	FOREIGN KEY(CPNO) REFERENCES COURSE(CNO)
);
*/

/*
INSERT INTO COURSE
VALUES
	('1','数据库','5',4),
	('2','数学',NULL,2),
	('3','信息系统','1',4),
	('4','操作系统','6',3),
	('5','数据结构','7',4),
	('6','数据处理',NULL,2),
	('7','PASCAL语言','6',4);
*/


SELECT *
FROM COURSE;

SC.sql

/*
CREATE TABLE SC(
	SNO CHAR(9),
	CNO CHAR(4),
	GRADE SMALLINT,
	PRIMARY KEY(SNO,CNO) ,
	FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),
	FOREIGN KEY(CNO) REFERENCES COURSE(CNO)
);
*/

/*
INSERT INTO SC
VALUES
	('201215121','1',92),
	('201215121','2',85),
	('201215121','3',88),
	('201215122','2',90),
	('201215122','3',80)
;
*/


--注意drop the constraint 外键约束名不等于属性名，从键列表可知为FK__SC__SNO__300424B4
/*
ALTER TABLE SC
DROP CONSTRAINT FK__SC__SNO__300424B4;
*/
--也可以在创建表的时候就给外键约束名起别名 : CONSTRAINT 约束名 FOREIGN KEY(属性列)  REFERENCES TABLE_NAME(COLUMN NAME)
--也可以通过select语句查询到外键名，从系统表中查出外键约束名,OBJECT_ID()函数中表名即为子表的表名。
--语法：object_id('objectname')或object_id('objectname','type')
--作用：该函数会根据对象名返回指定对象的ID值，可以在sys.objects表中进行验证。
/*
type如下列表：
AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
*/
--代码如下
/*
SELECT name
FROM sys.foreign_key_columns f , sys.objects o
WHERE f.parent_object_id = OBJECT_ID('db.dbo.sc') AND f.constraint_object_id = o.object_id;
*/


--OBJECT_ID函数使用
/*
SELECT OBJECT_ID('DB.DBO.SC')
FROM sys.objects;
*/



--添加外键约束并为外键约束命名
/*
ALTER TABLE SC
ADD CONSTRAINT FK_1 FOREIGN KEY(SNO) REFERENCES STUDENT(SNO);			--创建外键约束的同时对外键约束进行命名，命名为FK_1,方便以后使用 
*/



/*
SELECT *
FROM SC;
*/

之前一周只看教材没有上手码代码，导致第一天使用SSMS出现了很多问题，以后必须结合教材和软件学习。