SQL server 数据库最新修改

第一章创建数据库

1 用企业管理器创建数据库

（1）在企业管理器中，在控制台的数据库文字上右单击选择，新建数据库，然后给数据库起名字，比如STUDENT,

（2）然后在数据文件选项卡，选择数据文件放置位置；数据库文件名STUDENT_Data.MDF,初始大小1MB，文件增长为20MB，防止文件被数据写满。同样在事务日志选项卡选择日志文件放置位置，初始大小1MB，文件属性为自动增长，最大文件大小为20MB，

2 在查询分析器中用代码创建数据库

CREATE  DATABASE  database_name

 ON PRIMARY

(

    NAME= ,

    FILENAME= ‘ ’

    SIZE=,

    MAXSIZE=,

    FILEGROWTH=

)

案例 在查询分析器中创建数据库

CREATE  DATABASE  JSJX_DB

 ON PRIMARY

(

    NAME='JSJX_DB_DATA',

    FILENAME='E:\\JSJX_DB_DATA.MDF',

    SIZE=5MB,

    MAXSIZE=20MB,

    FILEGROWTH=20%

)

3 查看数据库信息

（1）使用企业管理器  在数据库上右单击选择属性

（2）代码查看 EXEC sp_helpdb ‘jsjx_db’

第二章 管理数据库

1 打开数据库

（1）企业管理器中，控制台---服务器组---服务器----数据库……

（2）查询分析器中 代码use database_name

2 删除数据库

（1）企业管理器中，在控制台的数据库上右单击选择删除

（2）代码DROP DATABASE database_name

3 分离数据库

在要分离数据库上右单击选择  所有任务-----分离数据库

4 附加数据库

在企业管理器的控制台---数据库----右单击选择所有任务-----附加数据库

5 备份数据库：在企业管理器的控制台---数据库----右单击选择所有任务-----备份数据库

6 还原数据库：在企业管理器的控制台---数据库----右单击选择所有任务-----还原数据库

第三章 表的操作

常用数据类型

      (1)定义表结构：给表的每一列取字段名，并确定每一列的数据库型、数据长度、列数据是否可以为空等。

      (2)设置约束：设置约束是为了该列输入值的取值范围，以保证输入数据的正确性和统一性。

      (3)添加数据：表结构建立完成之后，应该向表中输入数据。

“班级”表的结构：

2 创建表

（1）使用企业管理器，在student数据库，展开找到 表 图标，右单击选新建表，然后输入例如如下字段:

输入内容如下

Go

create table dbo.系部

        (

            系部代码 char(2) not null ,

             系部名称 Varchar (30) not null,

             系主任 char(8) 

        )

3修改表结构

（1）在表 图标上双击，然后再在需要的表上右单击选择------设计表比如添加、删除字段，修改原有的字段定义等

（2）代码方式

案例1 在student数据库教师 表中增加家庭住址一列，Varchar类型，允许空

Use student

Go

Alter table 教师

Add 家庭住址 varchar(40)

Go

案例2 在student数据库教师表 家庭住址修改varchar（50），允许空

Use student

Go

Alter table 教师

Alter column 家庭住址 varchar(50)

Go

案例3 在student数据库教师表中删除“家庭住址“一列

Use student

Go

Alter table 教师

     Drop column 家庭住址

Go

4删除表

（1）在需要删除的表上右单击选择 删除，选择全部删除

（2）代码比如

Drop table 系部

Go

5添加数据

（1）双击表 图标，在右侧表名上右单击选择 打开表 –返回所有行

（2）使用代码

例如使用insert 向student库中系部表添加记录

Use student

Go

   Insert 系部

        Values（’03’,’数学系’,’张三’）

Go

6查看表

（1）在表上右单击选择 属性

（2）代码 exec sp_help 表名

第四章 数据库增删改查操作

1 数据的添加

语法：insert [into]  tablename [column_list] values(data values)

案例1 在专业表中添加一行记录：在计算机系部中添加-个电子科学专业

Use student

Go

Insert 专业

      (码,专业名称,系部代码)

       Values

       ('0105','电子科学','01')

Go

案例2省略清单insert语句 在班级表中添加2004级电子商务班

Use student

Go

Insert 专业

      (码,专业名称,系部代码)

       Values

       ('0106','电子系','01')

Go

2 数据的修改  update

案例3 将教学计划 表中码为”0101”的“开课学期”值改为第2学期

Use student

Go

Update 教学计划

Set 开课学期=2

Where 码='0101'

Go

3 数据的删除

案例4  删除“课程注册”表中所有记录

Delete 课程注册  go

案例5 删除“教师”表中没有姓名的记录

Delete 教师 where 姓名 is null 

Go

案例6 删除课程注册表中 姓名为“张斌” 的课程号位“0001”的选课信息

Delete 课程注册表

Where 课程注册表.课程号='0001'and 学号=(select 学号 from 学生 where 姓名 like '张斌')

Go

4 数据查询

（1）查询表中若干列

案例 1  查询“学生”表中全体学生的记录

Select * from 学生 

Go

案例2  查询全体教师的教师编号，姓名，和 职称信息

Select 教师编号,姓名,职称

From 教师

Go

（3）查询表中若干记录

案例3 查询选修了课程的学生号并且取消重复记录

Select distinct 学号 From 课程注册

Go

（4）返回行数

案例4 查询“课程注册”表中的前3条记录

Select top 3* from 课程注册

Go

（5）查询满足条件的元组

Select * from 课程注册 where 成绩>=50

Go

案例6 查询出生日期在1970年至1982年之间的学生姓名、学号和出生日期

Select 姓名,学号,出生日期 from 学生

Where 出生日期 between ‘1970-01-01’ and ‘1982-12-31’

Go 

案例7 查询计算机系、经济管理系的班级名称与班级编号

select 班级代码,班级名称 from 班级 where 系部代码 in('01','02')

案例8 字符匹配  查询“学生”中 姓“周”的同学的信息

Select * from 学生 where 姓名 like '周%'

Go

注意 %代表任意长度字符； _下划线代表任意单个字符 

案例9 查询“教师”表中备注字段为空的班级信息

Select * from 教师 where 备注 is null

Go

案例10 多重条件查询 查询“课程注册”表中课程号为”0001”成绩在70分—79分之间的学生的学号，成绩

Select 学号,成绩 from 课程注册 where 课程号='0001'and 成绩>=70 and 成绩<79

Go

(6) 对查询结果排序

Order by ASC（升序）、Desc（降序）

案例 11 查询选修”0001”号课程的同学的学号，并按成绩降序排列

Select 学号,成绩 from 课程注册 where 课程号='0001'

Order by 成绩 DESC

（7）对数据进行统计

使用集合函数 

Count（[ distinct / all ] *）统计记录个数。

Count（[ distinct / all ] 列名）统计一列中值的个数。

Sum（[ distinct / all ] 列名）计算一列值的总和（此列必须是数值型的）。

Avg（[ distinct / all ] 列名）计算一列值的平均值（此列必须是数值型的）。

Max（[ distinct / all ] 列名）求一列值中的最大值。

Min（[ distinct / all ] 列名）求一列值中的最小值。

案例12 查询“教师”表中教师总数。

Select  count(*)  as  教师总数   from  教师

Go

案例13  查询“课程注册”表中学生的成绩平均分。

Select  avg(成绩) as 平均分   from  课程注册

Go 

案例14 对结果进行分组

 查询“课程注册”表中课程选课人数4人以上的各个课程号和相应的选课人数。代码如下。

Select 课程号,count(*) as 选课人数 from 课程注册

Group  by 课程号

Having count(*)>=4

Go

案例15 Compute对查询的结果集中所有的记录进行汇总统计。

 查询所有学生所有成绩的总和。

Select * from 课程注册   

Order by 学号 

Compute  sum(成绩)

Go

案例16 对每个学生的所有课程的成绩求和，并显示详细记录。

Select   *  from 课程注册   

Order  by  学号

Compute sum(成绩)  by学号

Go

案例17 用查询结果生成新的表

 创建“课程注册”表的一个副本。

Select * into  课程注册副本   from  课程注册

Go

Select *  from 课程注册副本

Go

合并结果集：union

案例18 查询“课程注册”表中0102专业的学生学号及课程成绩大于70分小于79分的学生学号，并按成绩降序排列记录。

Select *  from 课程注册   where   码=’0102’ 

Union 

Select *  from 课程注册  where  成绩>=70  and 成绩<79 

Order by 成绩  desc 

Go

案例19 查询“课程注册”表中选修了0001课程或选修了0002课程的学生，也就是选修了课程0001的学生集合与选修了课程0002的学生集合的并集，且按课程号升序排序。

Select *  from 课程注册   where 课程号='0001' 

Union

Select * from 课程注册  where 课程号='0002' 

Order by 课程号 asc

Go

（8）交叉连接：两个表属性列的和

语法格式：

Select 列表列名  from 表名1 cross  join 表名2 

其中 cross join 为交叉表连接关键字。

案例20 使用上例中的“学生”表、“专业”表，实现交叉查询。

Select 学号,姓名,性别,学生.系部代码,学生.码,专业.码,专业名称,专业.系部代码

From 学生  cross  join 专业

（9）自然连接

等值连接：用=作为连接条件或连接谓词时候

自然连接：在等值连接中，把目标列中重复的列删除。

用等值连接方法连接 学生 表和 专业 表，观察效果

Select 学号,姓名,性别,学生.系部代码, 学生. 码，专业.码,专业名称，专业.系部代码From 学生 inner join 专业 on 学生.码=专业.码

案例21 自然连接“学生”表和“专业”表。

Select 学号,姓名,性别,学生.系部代码,专业.码,专业名称

From 学生 join 专业 on 学生.码=专业.码

（10）自身连接查询：一个表与自己进行连接

案例22 使用“教师任课”表，查询至少为两个专业开设课程的教师编号和码。

Select first.教师编号,second.码

From 教师任课  as  first join 教师任课 as second 

On  first.教师编号=second.教师编号

And first.码!=second.码

（11）

左外连接是结果表中除了包含满足连接条件的记录外，还包括左表中不满足连接条件的记录。

左外连接语法格式

Select 列表列名 

From 表名1  left [outer] join 表名2

On  表名1.列名=表名2.列名

案例23 将“学生”表左外连接“专业”表。

Select 学号,姓名,性别,学生.系部代码,学生.码,专业.码,专业.专业名称,专业.系部代码

From 学生 left outer join 专业

On 学生.码=专业.码

（12）右外连接  是结果表中除了包含满足连接条件的记录外，还包括右表中不满足连接条件的记录。

案例24 将“学生”表右外连接“专业”表。

Select 学号,姓名,性别,学生.系部代码,学生.码,专业.码,专业. 专业名称,专业.系部代码

From 学生 right outer join 专业

On 学生.码=专业.码

（13）完全外连接是结果表中除了包含满足连接条件的记录外，还包括2个表中不满足连接条件的记录。

案例25 将“学生”表完全外连接“专业”表

Select 学号,姓名,性别,学生.系部代码,学生.码,专业.码,专业.专业名称,专业.系部代码

From 学生 full outer join 专业

On 学生.码=专业.码

（14）复合连接： on中连接有多个连接条件

案例26 使用学生表，课程表和课程注册表，查询成绩在70分-80分之间（含70分和80分）的学生的学号，姓名，码，选修课的课程号，课程名以及对应的成绩。

Select  s.学号,s.姓名,s.码,c.课程号,cn.课程名,c.成绩

From  学生 as s join 课程注册 as c 

On s.学号=c.学号  and c.成绩>=70  and c.成绩<=80 

Join 课程  as  cn

On c.课程号=cn.课程号

（15）带有比较运算符的子查询

案例27 使用教师 表，查询与“王钢”同在一个系的教师基本信息

Select 教师编号,姓名,性别,学历,职称 from 教师

Where 系部代码= (select 系部代码 from 教师 where 姓名='王钢')

（16）带有exists运算符的子查询

使用教师 表，查询与“王钢”同在一个系的教师基本信息

Select 教师编号，姓名，性别，学历，职称 from 教师 as  T1

Where exists （select * from 教师 as T2 where T2.系部代码 and  T2.姓名=’王钢’）

第五章 约束

约束定义了关于允许什么数据进入数据库的规则。使用约束的目的是为了防止列出现非法数据，以保证数据库中数据的一致性和完整性。

1 常见约束

（1）NOT NULL指定不接受 NULL 值的列

（2）CHECK约束对放入列中的值进行，以强制执行域的完整性

（3）UNIQUE约束在列集内强制执行值的唯一性

（4）primary key 约束列 这些列唯一标识表中的行

（5）Foreign Key 约束标识表之间的关系

（6）default约束为列填入默认值

2 创建主键约束

（1）用企业管理器创建主键约束 在“学生”表，右单击选择“设计表”，在需要的字段上右单击选择 设置主键

（2）使用SQL语句创建主键

案例1  在student数据库中，建立一个“教材”表，包括教材代码、教材名称、书号、出版社、版本、价格六个字段，并将教材代码设置为主键。

Create table 教材

(

教材代码 int primary key,

教材名称 char(50),

书号 char(50), 

出版社 char(50),

版本 char(50),

价格 char(50)

)

Go

案例2 如果在创建”教材”表的时段没有指定主键，那么在创建好后的“教材”表中，将教材代码设置为主键。

Alter table 教材

Add  constraint ps_jc 

Primary key clustered (教材代码)

Go

3 创建唯一约束

（1）在表 上右单击选择“设计表“，然后在要设置唯一约束字段上右单击选择---索引/键，单击新建按钮，给起名字，在从列名下拉框中选择字段名称，最好选择”创建unique”复选框和”约束’单选框。

（2）使用sql 语句创建唯一约束

案例3 在student 表中“教材”表中，将“书号”字段创建一个唯一约束。

Alter table 教材 

Add  constraint uk_sh

Unique (书号)

Go

4 创建检查约束

（1）在表 上右单击选择“设计表“，然后在要设置check约束字段上右单击选择 check约束，单击新建按钮，输入约束名，比如（[学分]>=1 and [学分]<=7）.

（2）代码方式：

案例4 利用sql语句创建“课程”表，并且在创建的同时，创建检查约束，是学分字段被约束在1至7之间。

Create  table 课程

(

课程号  int primary  key,

课程名 char(50),

学分 char(50),

Constraint 学分 check(学分 between 1 and 7 )

)

Go

5创建默认约束

(1) 在表 上右单击选择“设计表“，然后在要设置默认字段上比如性别字段。在下方默认值处输入”男”

(2)案例5 在student数据库中新建“学生注册”表，并将注册时间设置为当前日期。

Create table 学生注册

(

注册编码  int primary key,

学号 char(50),

注册时间 datetime default  getdate(),

学期 char(50)

)

Go

案例6 在student 数据库中的“教师”表中，为学历字段创建默认值为“本科”的默认约束。

Alter table 教师

Add constraint df_xl

Default ‘本科’ for 学历

Go

6 创建外键约束

（1）比如教师 表，右单击选择 设计表，选择要设置外键约束的字段，右单击选择 关系，单击新建按钮，然后在主键表中 选择主键表，比如系部表，在外键列表中选择 教师表。然后分别主键表和外键表中选择系部代码字段。

（2）用sql语句创建外键约束

案例7 在student数据库中位“班级”表，为码字段创建一个外键约束，从而保证输入有效的码。

Alter table 班级

Add constraint fk_zydm

Foreign  key  (码)

References 专业(码)

Go

7查看约束的定语

（1）在表上右单击选择 设计表，在右单击选择属性查看。

（2）代码  案例8 使用系统存储过程查看student 数据库中定义的入学时间（名称为ck_rxsj）的约束信息和文本信息。

查询约束信息

Exec sp_help  ck_rxsj 

Go

查询约束信息

Exec sp_helptext  ck_rxsj 

Go

8删除约束

案例9 删除“课程”表中的入学时间（ck_rxsj）。

Alter table 学生

Drop constraint ck_rxsj

Go

第六章 Sql server安全管理与权限分配

1 两种验证方式：一种是windows验证方式，另一种windows和sql server混合验证方式

在控制台中展开服务器组---在服务器中右单击选择属性，在安全性选项卡中修改

2 sql server中超级登录账号sa， 查看登录账号：在控制台中找到服务器的树形目录中找到 安全性----登录，可以看到登录用户

3 创建登录账号：在控制台中服务器---安全性---登录上右单击选择 新建登录。起名字，然后设置密码，和要管理的数据库，在服务器角色选项卡中选择角色，另外还可以设置要访问和管理的数据库名

sysadmin 可以在 SQL Server 中执行任何活动。 

serveradmin 可以设置服务器范围的配置选项，关闭服务器。 

setupadmin 可以管理链接服务器和启动过程。 

securityadmin 可以管理登录和 CREATE DATABASE 权限，还可以读取错误日志和更改密码。 

processadmin 可以管理在 SQL Server 中运行的进程。 

dbcreator 可以创建、更改和除去数据库。 

diskadmin 可以管理磁盘文件。 

bulkadmin 可以执行 BULK INSERT 语句。

服务器角色是负责管理与维护SQL server 组

4 禁止登录账号

在控制台展开找到在目标服务器 下面展开 找到安全性 然后单击登录，在禁止的账号上右单击 选择 属性，然后再常规选项中选择拒绝访问、

5 删除登录账号

在控制台展开找到在目标服务器 下面展开 找到安全性 然后单击登录，在删除的账号上右单击选择 删除

6创建数据库用户

在控制台中 展开数据库，找到数据库的 用户 一项，右单击选择 新建数据库用户，起名字，选择角色，确定。

7删除数据库的用户

展开控制台---某数据库-----用户 ，找到要删除用户，右单击选择删除。

8设置数据库用户账号权限

在企业管理器的右窗格中用户账号右单击选择 属性，打开数据库用户属性 对话框， 单击权限按钮设置权限

9 数据库角色

.