SQL syntax-LOG1

SQL-LOG

-->创建数据库
go
create database stuDB on primary(
 name = 'stuDB_data',     --主数据库的逻辑名
 filename = 'e:\stuDB_data.mdf',   --主数据库的物理路径
 size = 5mb,        --主数据库的初始大小
 maxsize = 100mb,      --主数据库的最大空间
 filegrowth = 15%      --主数据库的文件增长比
)

log on (
 name = 'stuDB_log',      --数据库日志的逻辑名
 filename = 'e:\stuDB_log.ldf',   --数据库日志的物理路径
 size = 5mb,        --数据库日志的初始大小
 filegrowth = 15%      --数据库日志文件的增长比
)
go

-->数据库删除操作
go
drop database stuDB
go

---------------------------------------------------------------------------------

 

 

--------------------------------表格基本处理部分---------------------------------

-->选择数据库
use stuDB

go

-->创建stuInfo表格
IF EXISTS(SELECT * FROM sysobjects WHERE name='stuInfo') DROP TABLE stuInfo;
CREATE TABLE stuInfo(
 stuNO INT IDENTITY(100001,1) NOT NULL PRIMARY KEY ,   -- 学号
 stuName VARCHAR(45),          --  姓名
 stuAge TINYINT,            -- 年龄
 stuID NUMERIC(18,0),          --  ×××号
 stuAddreass TEXT,           --  家庭地址
 stuSeat int             -- 座位号
)

go

-->向stuInfo表格插入数据
go
INSERT INTO stuInfo VALUES('杨荣凯',21,152127199008250012,'内蒙古×××县',1);
INSERT INTO stuInfo VALUES('王冰',22,988127199008250012,'湖北黄石',2);
INSERT INTO stuInfo VALUES('胡兵',23,254136199008250012,'湖北孝感',3);
INSERT INTO stuInfo VALUES('吕巧燕',20,785412199008250012,'湖北宜昌',4);
go

-->查询stuInfo表格数据
SELECT * FROM stuInfo ;

-->更改stuInfo表格数据
UPDATE stuInfo SET stuName='吕巧艳' WHERE stuID=785412199008250012;

-->删除stuInfo表格数据
DELETE FROM stuInfo WHERE stuNO=100005;

go

 

 

-->创建stuMark表格
IF EXISTS(SELECT * FROM sysobjects WHERE name='stuMark')
DROP TABLE stuMark;
CREATE TABLE stuMark(
 examNO CHAR(6) PRIMARY KEY,    -- 考号
 labExam NUMERIC(4,1),     -- 机试成绩
 writtenExam NUMERIC(4,1)    -- 笔试成绩
)
go

-->查询stuMark表格数据
SELECT * FROM stuMark;

-->添加stuMark表格数据
INSERT INTO stuMark VALUES('WP0001',100,100);
INSERT INTO stuMark VALUES('WP0002',33,44);
INSERT INTO stuMark VALUES('WP0003',69,55.5);
INSERT INTO stuMark VALUES('WP0004',79.9,33.3);

-->更新stuMark数据
UPDATE stuMark SET writtenExam=60 WHERE examNO='WP0004';

-->删除stuMark表格数据
DELETE FROM stuMark WHERE examNO='WP0005';

----------------------------------------------------------------------------------------

 

 

-------------------------------------字段处理部分----------------------------------------
-->以下操作无数据时可以任意转换，有数据时，只可以同类型小单位向大单位转换　反之不能

-->修改stuInfo表格中stuSeat类型
ALTER TABLE stuInfo ALTER COLUMN stuSeat TINYINT;
ALTER TABLE stuInfo Alter COLUMN stuSeat INT;

-->在stuInfo表格中的myUser字段
ALTER TABLE stuInfo ADD myUser int;

-->在stuInfo表格中删除myUser字段
ALTER TABLE stuInfo DROP COLUMN myUser;

-----------------------------------------------------------------------------------------

 

 

 

--------------------------------------约束处理部分---------------------------------------

-->向stuMark表格中labExam和writtenExam字段中分别增添一个check约束
ALTER TABLE stuMark ADD CONSTRAINT CK_LABEXAM CHECK (labExam BETWEEN 0 AND 100);
ALTER TABLE stuMark ADD CONSTRAINT CK_WRITTENEXAM CHECK (writtenExam BETWEEN 0 AND 100);
-->测验
INSERT INTO stuMark VALUES('WP0005',101,4);
INSERT INTO stuMark VALUES('WP0005',4,101);
SELECT * FROM stuMark;

-->向stuInfo表格中stuSeat字段中添加一个唯一键约束
ALTER TABLE stuInfo ADD CONSTRAINT UK_STUSEAT UNIQUE (stuSeat);
-->测试
INSERT INTO stuInfo VALUES('王华',21,988127199008250011,'湖北仙桃',1);
SELECT * FROM stuInfo;

-->向stuMark添加外间使stuMark表变成从表
-->1.因为成绩表与学号一对一的关系，所以要创建一个字段用于和学生信息表关联
ALTER TABLE stuMark ADD stuNO INT;
-->2.创建外键
ALTER TABLE stuMark ADD CONSTRAINT FK_STUINFO_STUNO FOREIGN KEY (stuNO) REFERENCES stuInfo (stuNO);
-->3.测试
INSERT INTO stuMark VALUES('WP0001',100,100,100001); -->成功
INSERT INTO stuMark VALUES('WP0001',100,100,1);   -->失败

-->额外　级联删除
-->1.删除外键
ALTER TABLE stuMark DROP CONSTRAINT FK_STUINFO_STUNO;
-->2.创建级联删除
ALTER TABLE stuMark ADD CONSTRAINT FK_STUINFO_STUNO FOREIGN KEY (stuNO) REFERENCES stuInfo (stuNO) ON DELETE CASCADE;
-->3.测试
DELETE FROM stuInfo WHERE stuNO=100001;  --要删除主表数据 才有级联效果
SELECT * FROM stuInfo;
SELECT * FROM stuMark;

-->向stuInfo表格中添加默认约束
-->1.创建一个国家字段
ALTER TABLE stuInfo ADD stuCountry VARCHAR(50);
-->2.设置默认约束
ALTER TABLE stuInfo ADD CONSTRAINT DF_STUINFO DEFAULT '中国' FOR stuCountry;
-->3.测试
INSERT INTO stuInfo(stuName,stuAge,stuID,stuAddreass,stuSeat) VALUES ('天天',21,123321157854674354,'天津',2);
SELECT * FROM stuInfo;

-----------------------------------------------------------------------------------------