SQL server笔记01(数据查询前内容)

数据库的创建和管理

1.创建数据库

1.create database databasename

2.create database databasename

on (name=databasename_dat,

filename=‘D:\MyCode\SQL\databasename.mdf’)

主文件逻辑文件名和物理文件名

3.create database databasename

on primary(name = student_db,

fliename = ‘D:\MyCode\student.mdf’,

size = 5120KB,

maxsize = unlimited,

filegrowth = 10%)

log on (name = student_log,

filename = ‘D"\MyCode\student.ldf’,

size = 1MB,

maxsize = 10MB,

filegrowth = 128K)

(1) 数据库的主数据文件逻辑文件名为student_db,物理文件名为student.mdf,初始大小为5M,最大文件大小无限制,自动增长为10%.

(2) 事务日志文件逻辑文件名为student_log,物理文件名为student.ldf,初始大小为1MB,最大文件大小为10MB,自动增长量为128KB.

2.修改数据库

1.alter database databasename

add file(name = fileanme,

filename = ‘D:\MyCode\filename.NDF’,

size = 5MB,

maxsize = 30MB,

filename = 30MB,

filegrowth = 2MB)

在一个数据库中添加新数据文件

2.alter database databasename

add filegroup(name = fileanme,

filename = ‘D:\MyCode\filename.NDF’,

size = 5MB,

maxsize = 30MB,

filename = 30MB,

filegrowth = 2MB)

3.向Exercise_db数据库中添加文件组

alter database Exercise_db

add filegroup ExelFG1

Go

将文件Exeldat2 和Exeldat3添加至文件组

alter database Exercise_db2

add file(name = Exeldat2,

filename=‘D:\MyCode\Exel_dat2.ndf’,

size = 2MB,

maxsize = 30MB,

filegrowth = 2MB

),(name = Exeldat3,

filename = ‘D:\MyCode\Exel_dat3.ndf’,

size = 2MB,

maxsize = 30MB,

filegrowth = 2MB)

to fliegroup ExelFG1

GO

将ExelFGL设置为默认文件组

alter database Exercise_db2

modify filegroup ExelFG1 DataULT

GO

3.删除数据库

1. drop database databasename1,databasename2

4.分离数据库

1. sp_detach_db ‘databasename’

5.附加数据库

1. Use master

GO

create database student_db

on (filename = ‘D:\MyCode\stu_info.mdf’)

for attach

表的创建和管理

1.创建表

1.create table student

(sno char(10) primary key,

sname char(20),

gender char(2),

age int ,

depart char(3),

specialty char(50),

check (age between 15 and 25))//使用check关键字设置年龄限制

2. create table course

(cno char(8) primary key,

cname char(20),

pcno char(8),

tno char(4),

foreign key(pcno) references course(cno),

foreign key(tno) references teacher(tno)

);

3.create table score(

sno char(10),

cno char(8),

grade int,

primary key(sno,cno),

foreign key (sno) references student(sno),

foreign key (cno) references course (cno)

);

4.创建临时表#temp_student

(学号 smallint not null,

姓名 varchar(30) not null,

年龄 int not null,

primary key (学号)

)

2.修改表

1.向student表中增加"入学时间"列,其数据类型为字符型

alter table student

ADD enrollment char(10)

2.将student表中的"入学时间"数据类型修改为date

alter table student

alter column enrollment date

3.删除student表中的"入学时间"字段

alter table student drop column enrollment

3.删除表

Drop table table_name [ ,…n ] [;]

4.使用sp_help table_name 查询表的信息

表的完整性管理

1.数据库完整性概述

略

2.T-SQL修改约束

1. 在score表中增加主键(primary key)约束

alter table score

ADD constraint PK_score primary key(sno,cno)

2. 在score表中增加外键(foreign key)约束

alter table score

ADD constraint FK_score1

foreign key(sno) references student(sno)

alter table score

ADD constraint FK_score2

foreign key(cno) references course(cno)

3. 给course表中的cname添加唯一性(unique)约束

alter table course

add constraint UQ_course_cname unique(cname)

4. 给student 表中的gender 添加默认(default)约束

alter table student

add constraint DF_student_gender default (‘男’) for gender

5. 给score 表中的grade 添加检查(check)约束

alter table score

add constraint CK_score_grade check(grade between 0 and 100)

6. 给student表中的gender 添加检查(check)约束

alter table student

add constraint CK_score_grade check(gender in(‘男’,‘女’))

7.删除CK_score_gender约束

alter table student

drop constraint CK_score_gender

3.索引

1.在student表的sname上创建一个聚集索引

create cluster index Stusname

on student (sname)

2.为student,course, score 三个表建立索引

create unique index Stusno

on student(sno)

create unique index Coucno

on course(cno)

create unique index SCno

on score(sno,ASC,cno DESC)

3.将上例中的索引Stusno,course,coucno

drop index student.Stusno,course.coucno

练习

1.创建一个名为MYDB的数据库,存储在D:盘目录下,要求:主数据文件mydb.mdf的初始大小为10MB,自动增长方式为10%,无限制;日志文件mydb_log.ldf的初始大小为2MB,自动增长方式为10MB,最大为100

create database MYDB
on
primary (
	name = mydb,
	filename = 'D:\MyCode\SQL\mydb1.mdf',
	size = 10MB,
	maxsize = unlimited,
	filegrowth = 10%
)
log on (
	name = mydb_log,
	filename = 'D:\MyCode\SQL\mydb_log1.ldf',
	size = 2MB,
	maxsize = 100MB,
	filegrowth = 10MB
)
drop database MYDB

2.在MYDB数据库下创建一个名为STU的表对象,包含属性如下:

sno char(10)

name varchar(10)

sex char(1)

bdate datetime

dept varchar(10)

dormitory varchar(10)

要求:定义必要的约束,包括主键sno,name值不允许为空,且sex取值为0或1

create table STU
(sno char(10),
name varchar(10)not null,
sex char(1),
bdate datetime,
dept varchar(10),
dormitory varchar(10),
primary key (sno),
constraint CK_sex check (sex in (0,1))
)

数据更新

1.插入数据

1.插入一行数据:向department表中插入一行数据,T-SQL语句如下

insert into department(no,name,dean)
values ('001',信息工程学院,null)
< == >
insert into department
values ('001',信息工程学院,null)

2.向表中指定字段插入数据:向score表中插入指定的(学号,课程号)数据

insert into score(sno,cno)
values ('2015874114','08181192')

3.插入多行数据:向score 表中插入两行数据

insert into score(sno,cno)
values ('2015874114','08181192'),('2015874115','08181192')

4.插入子查询:student_Archive_Datay表用来存储已经毕业学生的信息,其结构和student完全一致,将student表中的全部数据插入student_Archive_Datay中

create table student_archive_datay
(
sno char(10),
sname char(20),
gender char(2),
age int ,
depart char(3),
specialty char(50)
)

insert into student_archive_datay
select * from student

5.创建一个和student表相同的表studentNew,两个表的结构和数据相同

select * into studentNew from student

注意:studentNew 表不需要事先创建

2.更新数据

1.将student表中的学号为2015874143的同学年龄改为22

update student
set age = 22
where sno = '2015874143'

2.将teacher 表中工号为0130的老师的年龄+1,职称改为"副教授"

update teacher
set age = age + 1 ,prof = '副教授'
where tno = '0130'

3.用update…set…from…where语句完成以下更新:

有新的选课表score_new(sno char(10),sname char(20),cno char(5),grade int),要求score_new表中学生姓名(sname)字段的值等于student表中相应sname字段的值

update score_new
set score_new.name = student.sname from score_new,student
where score_new.sno = student.sno

3.删除数据

1. 删除teacher表中的全部记录,但保留数据表结构

truncate table teacher
< == >
delete from teacher

2.在course 表中删除课程号为08195371的记录

delete from course
where cno ='08195371'

数据库的创建和管理

1.创建数据库

1.create database databasename

2.create database databasename

on (name=databasename_dat,

filename=‘D:\MyCode\SQL\databasename.mdf’)

主文件逻辑文件名和物理文件名

3.create database databasename

on primary(name = student_db,

fliename = ‘D:\MyCode\student.mdf’,

size = 5120KB,

maxsize = unlimited,

filegrowth = 10%)

log on (name = student_log,

filename = ‘D"\MyCode\student.ldf’,

size = 1MB,

maxsize = 10MB,

filegrowth = 128K)

(1) 数据库的主数据文件逻辑文件名为student_db,物理文件名为student.mdf,初始大小为5M,最大文件大小无限制,自动增长为10%.

(2) 事务日志文件逻辑文件名为student_log,物理文件名为student.ldf,初始大小为1MB,最大文件大小为10MB,自动增长量为128KB.

2.修改数据库

1.alter database databasename

add file(name = fileanme,

filename = ‘D:\MyCode\filename.NDF’,

size = 5MB,

maxsize = 30MB,

filename = 30MB,

filegrowth = 2MB)

在一个数据库中添加新数据文件

2.alter database databasename

add filegroup(name = fileanme,

filename = ‘D:\MyCode\filename.NDF’,

size = 5MB,

maxsize = 30MB,

filename = 30MB,

filegrowth = 2MB)

3.向Exercise_db数据库中添加文件组

alter database Exercise_db

add filegroup ExelFG1

Go

将文件Exeldat2 和Exeldat3添加至文件组

alter database Exercise_db2

add file(name = Exeldat2,

filename=‘D:\MyCode\Exel_dat2.ndf’,

size = 2MB,

maxsize = 30MB,

filegrowth = 2MB

),(name = Exeldat3,

filename = ‘D:\MyCode\Exel_dat3.ndf’,

size = 2MB,

maxsize = 30MB,

filegrowth = 2MB)

to fliegroup ExelFG1

GO

将ExelFGL设置为默认文件组

alter database Exercise_db2

modify filegroup ExelFG1 DataULT

GO

3.删除数据库

1. drop database databasename1,databasename2

4.分离数据库

1. sp_detach_db ‘databasename’

5.附加数据库

1. Use master

GO

create database student_db

on (filename = ‘D:\MyCode\stu_info.mdf’)

for attach

表的创建和管理

1.创建表

1.create table student

(sno char(10) primary key,

sname char(20),

gender char(2),

age int ,

depart char(3),

specialty char(50),

check (age between 15 and 25))//使用check关键字设置年龄限制

2. create table course

(cno char(8) primary key,

cname char(20),

pcno char(8),

tno char(4),

foreign key(pcno) references course(cno),

foreign key(tno) references teacher(tno)

);

3.create table score(

sno char(10),

cno char(8),

grade int,

primary key(sno,cno),

foreign key (sno) references student(sno),

foreign key (cno) references course (cno)

);

4.创建临时表#temp_student

(学号 smallint not null,

姓名 varchar(30) not null,

年龄 int not null,

primary key (学号)

)

2.修改表

1.向student表中增加"入学时间"列,其数据类型为字符型

alter table student

ADD enrollment char(10)

2.将student表中的"入学时间"数据类型修改为date

alter table student

alter column enrollment date

3.删除student表中的"入学时间"字段

alter table student drop column enrollment

3.删除表

Drop table table_name [ ,…n ] [;]

4.使用sp_help table_name 查询表的信息

表的完整性管理

1.数据库完整性概述

略

2.T-SQL修改约束

1. 在score表中增加主键(primary key)约束

alter table score

ADD constraint PK_score primary key(sno,cno)

2. 在score表中增加外键(foreign key)约束

alter table score

ADD constraint FK_score1

foreign key(sno) references student(sno)

alter table score

ADD constraint FK_score2

foreign key(cno) references course(cno)

3. 给course表中的cname添加唯一性(unique)约束

alter table course

add constraint UQ_course_cname unique(cname)

4. 给student 表中的gender 添加默认(default)约束

alter table student

add constraint DF_student_gender default (‘男’) for gender

5. 给score 表中的grade 添加检查(check)约束

alter table score

add constraint CK_score_grade check(grade between 0 and 100)

6. 给student表中的gender 添加检查(check)约束

alter table student

add constraint CK_score_grade check(gender in(‘男’,‘女’))

7.删除CK_score_gender约束

alter table student

drop constraint CK_score_gender

3.索引

1.在student表的sname上创建一个聚集索引

create cluster index Stusname

on student (sname)

2.为student,course, score 三个表建立索引

create unique index Stusno

on student(sno)

create unique index Coucno

on course(cno)

create unique index SCno

on score(sno,ASC,cno DESC)

3.将上例中的索引Stusno,course,coucno

drop index student.Stusno,course.coucno

练习

1.创建一个名为MYDB的数据库,存储在D:盘目录下,要求:主数据文件mydb.mdf的初始大小为10MB,自动增长方式为10%,无限制;日志文件mydb_log.ldf的初始大小为2MB,自动增长方式为10MB,最大为100

create database MYDB
on
primary (
	name = mydb,
	filename = 'D:\MyCode\SQL\mydb1.mdf',
	size = 10MB,
	maxsize = unlimited,
	filegrowth = 10%
)
log on (
	name = mydb_log,
	filename = 'D:\MyCode\SQL\mydb_log1.ldf',
	size = 2MB,
	maxsize = 100MB,
	filegrowth = 10MB
)
drop database MYDB

2.在MYDB数据库下创建一个名为STU的表对象,包含属性如下:

sno char(10)

name varchar(10)

sex char(1)

bdate datetime

dept varchar(10)

dormitory varchar(10)

要求:定义必要的约束,包括主键sno,name值不允许为空,且sex取值为0或1

create table STU
(sno char(10),
name varchar(10)not null,
sex char(1),
bdate datetime,
dept varchar(10),
dormitory varchar(10),
primary key (sno),
constraint CK_sex check (sex in (0,1))
)

数据更新

1.插入数据

1.插入一行数据:向department表中插入一行数据,T-SQL语句如下

insert into department(no,name,dean)
values ('001',信息工程学院,null)
< == >
insert into department
values ('001',信息工程学院,null)

2.向表中指定字段插入数据:向score表中插入指定的(学号,课程号)数据

insert into score(sno,cno)
values ('2015874114','08181192')

3.插入多行数据:向score 表中插入两行数据

insert into score(sno,cno)
values ('2015874114','08181192'),('2015874115','08181192')

4.插入子查询:student_Archive_Datay表用来存储已经毕业学生的信息,其结构和student完全一致,将student表中的全部数据插入student_Archive_Datay中

create table student_archive_datay
(
sno char(10),
sname char(20),
gender char(2),
age int ,
depart char(3),
specialty char(50)
)

insert into student_archive_datay
select * from student

5.创建一个和student表相同的表studentNew,两个表的结构和数据相同

select * into studentNew from student

注意:studentNew 表不需要事先创建

2.更新数据

1.将student表中的学号为2015874143的同学年龄改为22

update student
set age = 22
where sno = '2015874143'

2.将teacher 表中工号为0130的老师的年龄+1,职称改为"副教授"

update teacher
set age = age + 1 ,prof = '副教授'
where tno = '0130'

3.用update…set…from…where语句完成以下更新:

有新的选课表score_new(sno char(10),sname char(20),cno char(5),grade int),要求score_new表中学生姓名(sname)字段的值等于student表中相应sname字段的值

update score_new
set score_new.name = student.sname from score_new,student
where score_new.sno = student.sno

3.删除数据

1. 删除teacher表中的全部记录,但保留数据表结构

truncate table teacher
< == >
delete from teacher

2.在course 表中删除课程号为08195371的记录

delete from course
where cno ='08195371'