Sql Server 复习资料总结

46 阅读 0 评论 31 点赞

我是靠谱客的博主冷傲豆芽，最近开发中收集的这篇文章主要介绍Sql Server 复习资料总结，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Sql Server 复习资料总结

/*Sql server 复习创建数据库 */
create database HNIUEAM

on primary

(name='HNIUEAM_primary',filename='f:MydbHNIUEAM.mdf',size=4MB,maxsize=20MB,filegrowth=2MB),

filegroup Old

(name='file1',filename='f:MydbHNIUEAM1.ndf',size=1MB,maxsize=10MB,filegrowth=1MB),

filegroup First

(name='file2',filename='f:MydbHNIUEAM2.ndf',size=1MB,maxsize=10MB,filegrowth=1MB),

filegroup Second

(name='file3',filename='f:MydbHNIUEAM3.ndf',size=1MB,maxsize=10MB,filegrowth=1MB),

filegroup Third

(name='file4',filename='f:MydbHNIUEAM4.ndf',size=1MB,maxsize=10MB,filegrowth=1MB),

filegroup Fourth

(name='file5',filename='f:MydbHNIUEAM5.ndf',size=1MB,maxsize=10MB,filegrowth=1MB)

log on

(name='HNIUEAM_log',filename='f:MydbHNIUEAM.ldf',size=5MB,maxsize=10MB,filegrowth=1MB)

use HNIUEAM
--创建教材信息表

create table T_BookInfo

(Book_id varchar(8) constraint pk_Book_id primary key,

Book_name varchar(50) not null,

Book_isbn varchar(17) not null,

Book_author varchar(20) not null,

Book_publisher varchar(50) not null,

Book_datetime datetime not null,

Book_price float not null,

Book_rkm text)

--创建供应商表

create table T_Supplier

(Supplier_id varchar(8) primary key,

Supplier_name varchar(50) not null,

Supplier_people varchar(8) not null,

Supplier_address varchar(50),

Supplier_phone varchar(15),

Supplier_postcode varchar(6),

Supplier_rkm text)

--创建订单信息表

create table T_Order

(Order_id varchar(8) primary key,

Supplier_id varchar(8) not null,

Order_datetime datetime not null,

Order_status bit not null

)

--创建订单详情表

create table T_OrderDetail

(OrderDet_id varchar(8) primary key,

Order_id varchar(8) not null,

Book_id varchar(8) not null,

OrderDet_num int not null,

OrderDet_status bit not null

)

alter table T_BookInfo drop constraint pk_Book_id

alter table T_BookInfo add constraint un_T_BookInfo unique(Book_isbn)

alter table T_BookInfo alter column Book_rkm text not null

alter table T_BookInfo add constraint ck_Book_price check(Book_price between 10 and 100)

alter table T_Order

add constraint fk_Supplier_id foreign key (Supplier_id) references T_Supplier(Supplier_id)

alter table T_OrderDetail

add constraint fk_Order_id foreign key (Order_id) references T_Order(Order_id)

alter table T_OrderDetail

add constraint fk_Book_id foreign key (Book_id) references T_BookInfo(Book_id)

/*数据库Option*/
--1、怎么用SQL语句获取指定数据库中的所有表名
--Option
select Name FROM SysObjects Where XType='U' orDER BY Name
--2、重命名表
--Option
sp_rename 老表名,新表名
-- 2、重命名

sp_renamedb 老名,新名

--3、删除数据库

drop database 数据库名

--4、查看数据库信息

sp_helpdb 数据库名

--2

/*column option
表的增、删、改、查
*/

sp_helpdb HNIUEAM

select *from T_OrderDetail

--1、修改column name
sp_rename 'T_OrderDetail.Book_id','PhoneID','column'

--2、插入column

--3、增加Column

alter table T_Order add Issh int

alter   table   表名   add   列名   varchar(2)        --增加
alter   table   表名   drop   column   列名           --删除
alter   table   表名   alter   column   列名类型     --修改

--example
--add column
select Name from sysobjects where XType='U' order by name

select * from T_BookInfo

alter table T_BookInfo add txtbox char(10) not null

alter table T_BookInfo drop column txtbox

alter table T_BookInfo add lofter bit not null

alter table T_BookInfo alter column lofter nvarchar(10)

                               /*一、数据库的DML*/


--1、插入数据
-- 语法:insert [into] 表名[(字段列表)] values(值列表)

insert into T_Order(Order_id,Supplier_id,Order_datetime) values('abc','ccd','2013-9-3')

--2、获取表的基本字段属性
SELECT syscolumns.name,systypes.name,syscolumns.isnullable, syscolumns.length
FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id('T_OrderDetail') --你的表

--3、单独查询表的递增字段
--单独查询表递增字段
select [name] from syscolumns where id=object_id(N'你的表名') and COLUMNPROPERTY(id,name,'IsIdentity')=1

--4、获取表的主外键
--获取表主外键约束 exec sp_helpconstraint   '你的表名' ;
exec sp_helpconstraint 'T_Order'

USE HNIUEAM

select NAME from sysobjects where XType='U' order by NAME

select *FROM T_Order
SELECT syscolumns.name,systypes.name,syscolumns.isnullable, syscolumns.length
FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = object_id('T_Order') --你的表

/*

defalut check 约束相关

--3.4约束设计
--约束1
alter table T_vehicle_declare
add constraint ck_Inout_port check (Inout_port in ('进港','出港'))
--约束2
alter table T_business_unit
add constraint ck_Business_unit_type
check (Business_unit_type in('国营经济','私营经济','个体经济','集体经济','股份制经济'))
--约束3
alter table T_ship
add constraint ck_Is_new check (Is_new in ('是','否'))
alter table T_ship
add constraint df_Is_new default 0 for Is_new
--约束4
alter table T_vehicle_declare
add constraint df_Declare_date default getdate() for Declare_date
--约束5
alter table T_ship
add constraint df_Checked_capacity default 0 for Checked_capacity
alter table T_ship
add constraint ck_Checked_capacity check (Checked_capacity between 0 and 5000)

--外键3：Ship_code
alter table T_vehicle_declare
add constraint fk_T_vehicle_declare_T_ship
foreign key (Ship_code) references T_ship(Ship_code)

示例复习

/*
@Describe:第B_18测试题
@Student:GongBiao
@Date:2014/10/20
*/

-- 3.1 创建数据库HNIUEAM
CREATE DATABASE HNIUEAM;
USE HNIUEAM;
Go

-- 3.2 创建数据表
/*
T_Student, T_Course, T_Teacher
*/

-- T_Student
CREATE TABLE T_Student(
Student_id VARCHAR(8) PRIMARY KEY,
Student_name VARCHAR(8) NOT NULL,
Student_sex BIT NOT NULL,
Student_age INT NOT NULL
);

-- R_Student_Course(学生选课信息)

CREATE TABLE R_Student_Course(
Student_id VARCHAR(8) NOT NULL,
Course_id VARCHAR(8) NOT NULL,
Student_course_hour DATETIME NOT NULL,
Student_course_score FLOAT NOT NULL
);

-- T_Teacher
CREATE TABLE T_Teacher(
   Teacher_id VARCHAR(8) PRIMARY KEY,
   Teacher_professional VARCHAR(20) NOT NULL,
   Teacher_name VARCHAR(8) NOT NULL,
   Teacher_age INT,
   Teacher_sex BIT

);

-- T_Course(课程基本信息表)
CREATE TABLE T_Course(
   Course_id VARCHAR(8) PRIMARY KEY,
   Course_name VARCHAR(50) NOT NULL,
   Course_classhour INT NOT NULL,
   Course_creditpoints FLOAT NOT NULL
);

-- R_Teacher_Course(教师授课信息表)
CREATE TABLE R_Teacher_Course(
   Teacher_id VARCHAR(8) NOT NULL,
   Course_id VARCHAR(8) NOT NULL,
   Teacher_course_hour DATETIME NOT NULL,
   Teacher_course_place VARCHAR(50) NOT NULL,
   Teacher_course_evaluation TEXT NOT NULL
);

--规范化表名
sp_rename Course_selection,R_Student_Course;

-- 3.3 创建数据表间的关系

ALTER TABLE R_Student_Course
ADD CONSTRAINT fk_Student_id
FOREIGN KEY (Student_id) REFERENCES T_Student(Student_id);

ALTER TABLE R_Student_Course
ADD CONSTRAINT fk_Course_id
FOREIGN KEY (Student_id) REFERENCES T_Course(Course_id);

ALTER TABLE R_Teacher_Course
ADD CONSTRAINT fk_Teacher_Course_id
FOREIGN KEY (Course_id) REFERENCES T_Course(Course_id);

ALTER TABLE R_Teacher_Course
ADD CONSTRAINT fk_Teacher_ID
FOREIGN KEY (Teacher_id) REFERENCES T_Teacher(Teacher_id);

-- 3.4 数据操作
INSERT INTO T_Course
VALUES('KC10001', '数据结构', 72, 4);
SELECT * FROM T_Student;

INSERT INTO T_Student(Student_id, Student_name, Student_sex, Student_age)
VALUES('1225071', '王尼马', 0, 19);
INSERT INTO T_Student(Student_id, Student_name, Student_sex, Student_age)
VALUES('1225072', '李小龙', 0, 18);

INSERT INTO T_Student(Student_id, Student_name, Student_sex, Student_age)
VALUES('1225073', '小红', 1, 21);

INSERT INTO T_Student(Student_id, Student_name, Student_sex, Student_age)
VALUES('1225074', '小莉', 1, 20);

INSERT INTO T_Student(Student_id, Student_name, Student_sex, Student_age)
VALUES('1225075', '李超', 0, 17);

SELECT * FROM T_Teacher;
INSERT INTO T_Teacher
VALUES('X1717171', '科研教授', '王强', 2, 1);

INSERT INTO R_Student_Course
VALUES('1225071', '23755', GETDATE(), 77.3);

INSERT INTO R_Student_Course
VALUES('1225072', '23755', '2010-09-1', 77.3);

INSERT INTO R_Student_Course
VALUES('1225073', '23755', '2010-09-1', 90.6);

INSERT INTO R_Student_Course
VALUES('1225074', '23755', '2010-09-1', 82);

INSERT INTO R_Student_Course
VALUES('1225075', '23755', '2010-09-1', 77);

INSERT INTO T_Course
VALUES('23755', '数据结构', 33, 3);

SELECT * FROM T_Course;

-- 查询选了数据结构的学生

SELECT Student_name FROM T_Student, R_Student_Course WHERE T_Student.Student_id=R_Student_Course.Student_id;

-- 查询老师名称为"王强"老师

SELECT * FROM T_Teacher;
SELECT * FROM R_Teacher_Course;

INSERT INTO R_Teacher_Course
VALUES('X1717171', '23755', GETDATE(), '软件学院6楼', '学生觉得非常有趣');

SELECT * FROM T_Teacher, R_Teacher_Course WHERE T_Teacher.Teacher_id=R_Teacher_Course;

-- 计算所课程的时间为"2010-9-1" 课程名称为"数据结构" 的所有学生的总成绩

SELECT SUM(Student_course_score) AS '总成绩' FROM R_Student_Course WHERE Student_course_hour='2010-9-1';

-- 创建李超的视图

CREATE VIEW Student_view
AS
SELECT Teacher_name ='李超';

ALTER TABLE T_Student;
DROP CONSTRAINT fk_xxxx