当初的SqlServer数据库复习笔记

连永胜
1,200 阅读15分钟

数据库用户和权限管理

查看数据库的所有用户

select * from sys.sql_logins

删除用户

drop login login_name

创建用户：

创建登陆：

create login user1 with password='123456',default_database=test2

创建用户：

create user U1 for login user1

授予权限：

grant select on employee to U1
  • 授予用户单个权限
grant select on employee to U1
  • 赋予所有权限： 先创建登录名user2和用户U2,然后执行下面语句
grant all on employee to U2,U3

执行成功，并提示all权限不推荐使用
常用的权限：

收回权限：

和赋予权限类似：

revoke all on employee to U1,U2

注意：不能直接为登录名授权，必须为登录名创建一个用户

create user U1 for login user1
grant select on employee to U1

后面内容可能用到的表结构：

部门数据库

中文：

学生信息数据库：

数据库游标

定义游标：

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL]
FOR select_statement

[ FOR UPDATE [ OF column_name [,...n] ] ]

注意：有标名称写在关键字CURSOR前面，select_statement就是查询语句，用的是for关键字连接的

  • 对一条查询语句定义一个游标
declare c1 cursor 
for 
select emp_no,emp_name,dept,title,salary,addr 
from employee where sex='f'

游标的使用：

利用游标查找所有女业务员的基本情况。

declare c1 cursor 
for 
select emp_no,emp_name,dept,title,salary,addr 
from employee where sex='f' 

open c1
declare @emp_no nvarchar(5),@emp_name nvarchar(10),@dept nvarchar(4),@title nvarchar(6),@salary int,@addr nvarchar(50)
--游标的初始状态指向为空，所以让他先往下走一步
fetch next from c1 into @emp_no,@emp_name,@dept,@title,@salary,@addr

while @@FETCH_STATUS=0--判断fetch语句是否执行成功（前提是执行过）
begin
	--print @emp_no+','+@emp_name+','+@dept+','+@title+','+@salary+','+@addr
	select @emp_no 员工标号,@emp_name 员工姓名,@dept 部门,@title 职称,@salary 工资,@addr 住址
	fetch next from c1 into @emp_no,@emp_name,@dept,@title,@salary,@addr
	
end
close c1
deallocate c1--释放游标

一个小知识点：赋值时，使用select或者set

存储过程

一个简单的介绍：

存储过程就是SQL Server为了实现特定任务，而将一些需要多次调用的固定操作语句编写成程序段，这些程序段存储在服务器上，有数据库服务器通过程序来调用。

创建存储过程（小例子）：

利用存储过程查找“刘刚”的员工编号、订单编号、销售金额。

create proc p1 as
select emp_no,order_no,tot_amt from employee inner join sales on employee.emp_no=sales.sale_id where emp_name='刘刚'
exec p1—执行存储过程

注意：关键字用as

修改和删除存储过程

修改存储过程：

alter proc p1 as
select * from employee

删除存储过程：

drop proc p1

创建带有参数的存储过程

创建带有一个参数的存储过程

create proc p1(@emp_name nvarchar(10)) as
select * from employee where emp_name=@emp_name
exec p1 '刘刚'

创建带有两个参数的存储过程

create proc p2(@emp_name nvarchar(10),@dept nvarchar(4)) as
select * from employee where emp_name=@emp_name and dept=@dept
exec p2 '刘刚','业务'

创建带有返回值的存储过程

create proc p3(@emp_name nvarchar(10),@dept nvarchar(4) output) as
select @dept=dept from employee where emp_name=@emp_name

declare @de nvarchar(4)
exec p3 '刘刚',@de output
print @de

存储过程可以带有通配符，也可以带游标参数

用户自定义函数

关于数据库的go命令(简单的解释)

GO 是SYBASE和SQL Server中用来表示事物结束，提交并确认结果，相当于ORACLE的Commit。 GO不是Transact-SQL语句；而是可为cmdsql和osql实用工具及SQL Server查询分析器识别的命令。 SQL Server实用工具将GO解释为应将当前的Transact-SQL批处理语句发送给SQL Server的信号。 当前批处理语句是自上一GO命令后输入的所有语句，若是第一条GO命令，则是从特殊会话或脚本的开始处到这条GO命令之间的所有语句。

创建标量函数语法：

create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
returns 标量返回值类型
[with {Encryption | Schemabinding }]
[as]
begin
    函数体(即 Transact-SQL 语句)
    return 变量/标量表达式
end

标量函数的一个小例子：

定义：

use test2
go
create function avgSalary(@sex nvarchar(1))
returns decimal
as
begin

	declare @avgs decimal(10,2)
	select @avgs=avg(salary) from employee where sex=@sex
	return @avgs
end
go

执行：

select dbo.avgSalary('m') as 平均工资

注意：

  • 执行时函数的所有者是不能省的
  • 函数内不能用print语句

内嵌表值函数：

go
create function empInfo(@emp_no nvarchar(5))
returns table
as
return(
select * from employee where emp_no=@emp_no
)
go

执行

select * from employee
select * from dbo.empInfo('e0001')

自定义函数的执行

使用select语句

select dbo.avgSalary('m') as 平均工资

使用exec语句

declare @avg decimal(10,2)
exec @avg=dbo.avgSalary @sex='m'
print @avg

自定义函数的删除

drop function empInfo
drop function avgSalary

数据库ER图基础概念

基本介绍

ER图分为实体、属性、关系三个核心部分。
实体是长方形体现，而属性则是椭圆形，关系为菱形。

ER图的实体（entity）

即数据模型中的数据对象，例如人、学生、音乐都可以作为一个数据对象，用长方体来表示，每个实体都有自己的实体成员（entity member）或者说实体对象（entity instance），例如学生实体里包括张三、李四等，实体成员（entity member）/实体实例（entity instance） 不需要出现在ER图中。

ER图的属性（attribute）

即数据对象所具有的属性，例如学生具有姓名、学号、年级等属性，用椭圆形表示，属性分为唯一属性（ unique attribute）和非唯一属性，唯一属性指的是唯一可用来标识该实体实例或者成员的属性，用下划线表示，一般来讲实体都至少有一个唯一属性。

ER图的关系（relationship）

用来表现数据对象与数据对象之间的联系，例如学生的实体和成绩表的实体之间有一定的联系，每个学生都有自己的成绩表，这就是一种关系，关系用菱形来表示。

简单的例子：

实体：

ER的实体还会细分为弱实体和复合实体：

弱实体：

一个实体必须依赖于另一个实体存在，那么前者是弱实体，后者是强实体，弱实体必须依赖强实体存在，例如上图的学生实体和成绩单实体，成绩单依赖于学生实体而存在，因此学生是强实体，而成绩单是弱实体。
弱实体和强实体的联系必然只有1：N或者1：1，这是由于弱实体完全依赖于强实体，强实体不存在，那么弱实体就不存在，所以弱实体是完全参与联系的，因此弱实体与联系之间的联系也是用的双线菱形。

复合实体：

复合实体也称联合实体或桥接实体，常常用于实现两个或多个实体间的M：N联系，它由每个关联实体的主玛组成，用长方体内加一个菱形来表示。

下图就是一个典型的复合实体，用户和商品两个实体是M：N的关系，中间又订单这个实体联系，因此订单这个实体是一个复合实体，同时如果用户 实体不存在，就没有订单实体的存在，因此对于用户实体来讲订单是弱实体，同理商品实体如果不存在，同样不存在订单实体，因此对商品实体而言订单是弱实体，具体如图：

属性：

ER图的属性还细分为复合属性、多值属性和派生属性、可选属性，同时还有用来表示联系的属性，称为联系属性。

复合属性(composite attribute)：

复合属性是指具有多个属性的组合，例如名字属性，它可以包含姓氏属性和名字属性，如下图：

复合属性也有唯一属性，例如学生的所在班级属性，由于多个年级都有班级，所以单单班级属性是不唯一的，但是和年级组成的复合属性后则可以匹配成唯一属性。

多值属性（multivalued attribute）：

一个实体的某个属性可以有多个不同的取值，例如一本书的分类属性，这本书有多个分类，例如科学、医学等，这个分类就是多值属性， 用双线椭圆表示。

派生属性(derivers attribute)：

是非永久性存于数据库的属性。派生属性的值可以从别的属性值或其他数据（如当前日期）派生出来，用虚线椭圆表示，如下图。
下面的小组人数就是典型的派生属性，随着学生实例的参加的兴趣小组变化，小组人数属性也会变化，一般来讲派生属性不存在于数据库中，而是通过相应的公式进行计算得到，如果要放到数据库中，那么隔一段时间就要进行更新，否则会出现数据错误。

可选属性(optional attribute)：

并不是所有的属性都必须有值，有些属性的可以没有值，这就是可选属性，在椭圆的文字后用（O）来表示，如下图的地址就是一个可选属性。

联系属性：

联系属于用户表示多个实体之间联系所具有的属性，一般来讲M:N的两个实体的联系具有联系属性，在1:1和1：M的实体联系中联系属性并不必要。

关系：

ER图中关联关系有三种：

1对1（1:1） ：

1对1关系是指对于实体集A与实体集B，A中的每一个实体至多与B中一个实体有关系；反之，在实体集B中的每个实体至多与实体集A中一个实体有关系。

1对多（1:N） ：

1对多关系是指实体集A与实体集B中至少有N(N>0)个实体有关系；并且实体集B中每一个实体至多与实体集A中一个实体有关系。

多对多（M:N） ：

多对多关系是指实体集A中的每一个实体与实体集B中至少有M(M>0)个实体有关系，并且实体集B中的每一个实体与实体集A中的至少N（N>0）个实体有关系。

表示

在E-R图中，用“->”表示指向参与联系集中的“一”方实体集， “——”表示参与联系集中的“多”方实体集。

形式化关系查询语言

符号：

选择：

查询年龄小于20岁的学生。

σSage < 20(Student)

类似这个效果：

Select * from Student where Sage<20

查找所有1992年及以后出生的女学生情况

σyear(birthday)>=1992∧sex='女'(Student)

∧类似于and

投影：

1.查找所有学生的姓名和民族

∏：投影，类似于

Select studentName,nation from Student

2.括号里的内容可以是用σ符号选出来的内容: 查找所有“蒙古族”学生的姓名和籍贯：

∏studentName, native(σnation=‘蒙古族’(Student))

连接：

简单复习下数据库基础

数据库的基本数据类型：

  • char(n). 用户指定长度的字符串，n为字符串长度
  • varchar(n). 可变长度的字符串，用户指定最大长度.
  • varchar(max) 可变长度的字符串。最多 1,073,741,824 个字符。
  • text 可变长度的字符串。最多 2GB 字符数据。
  • int. 整型，4B
  • smallint. 小整数类型，2B
  • numeric(p,d). 定点数，精度由用户指定，decimal(p,d)和这个类似
  • float(n). 精度至少为n位的浮点数，n由用户指定
  • Datetime 日期/时间型，8B
  • Bit 允许 0、1 或 NULL
  • binary(n) 固定长度的二进制数据。最多 8,000 字节。
  • varbinary(n) 可变长度的二进制数据。最多 8,000 字节。
  • varbinary(max) 可变长度的二进制数据。最多 2GB 字节。
  • image 可变长度的二进制数据。最多 2GB。

数据库的定义和删除

  • 创建：CREATE DATABASE <数据库名>
  • 删除：DROP DATABASE <数据库名>

###表的定义和删除：

表的定义：

 CREATE TABLE <表名>
      （<列名1> <数据类型> [<列1的完整性约束>][，
          <列名2> <数据类型> [<列2的完整性约束>]，
                 … …  ，
          <列名n> <数据类型> [<列n的完整性约束>]，
     [<表的完整性约束>]]）；

基本表的修改：

增加列（新增一列的值为空值）：

ALTER TABLE <tableName>
     ADD <columnName> <dataType>

删除列：

ALTER TABLE <tableName>
     DROP column < columnName >

修改列的数据类型：

ALTER TABLE <tableName>
     ALTER COLUMN <columnName> <newDataType>

注意：在删除列以前必须先删除基于该列的所有索引和约束
注意：对列定义的修改只适用于修改列的类型和长度，列名不能修改。当表中已有数据时，只能增加列的长度，不能缩短列的长度。

约束

  • NULL：指出该列可以为空值。
  • NOT NULL：指出该列不能为空值。每一个表中至少应有一个列的可选项为NOT NULL。
  • PRIMARY KEY：指出该列名为表的主键。
  • CHECK (<值的约束条件>)：表检验CHECK约束

索引

创建索引

CREATE [UNIQUE ] [CLUSTER ] INDEX ＜索引名＞ 
&emsp;&emsp;ON&emsp;＜表名＞&emsp;&emsp;（＜列名＞［＜次序＞］［，＜列名＞［＜次序＞］］．．．）

删除索引

DROP INDEX  索引名

查询

基本语法：

Select <列名>,<列名>,<列名>… from 表名 [where 筛选语句]

限制结果集返回行数

如果SELECT语句返回的结果集的行数非常多，那么可以使用TOP选项限制其返回的行数。TOP选项的基本格式为:

[ TOP expression [ PERCENT ] [ WITH TIES ] ]

指示只能从查询结果集返回指定的第一组行或指定的百分比数目的行。expression可以是指定数目或百分比数目的行。若带PERCENT关键字，则表示返回结果集的前expression%行。

【例】 对PXSCJ数据库的XSB表选择姓名、专业和总学分，返回结果集的前6行。

SELECT TOP 6 姓名,专业,总学分
	FROM XSB

查询条件：

换码字符

如果查询字串中本身要包含%和_，必须使用“ESCAPE <换码字符>”短语，对通配符进行转义处理。

[例] 在班级Class表中查询班级名称中含有“08_”符号的班级名称

 SELECT className
   FROM Class
   WHERE className LIKE '%08\_%' ESCAPE '\'

“ESCAPE ‘\’”表示\为换码字符。 紧跟在\符号后的_不是通配符，而是普通的用户要查询的符号

结果排序：

SELECT Sno,Grade 
FROM SC 
WHERE Cno=‘3’ ORDER BY Grade DESC

SELECT *
FROM  Student
ORDER BY Sdept, Sage DESC

缺省按升序(ASC)排序，按降序排序，必须指明DESC选项

当排序列含空值时

  • ASC：排序列为空值的元组最后显示
  • DESC：排序列为空值的元组最先显示

集函数（聚合函数）

  • COUNT([DISTINCT|ALL]*)
  • COUNT([DISTINCT|ALL]<列名>) 计算列数
  • SUM ([DISTINCT|ALL] <列名>) 计算总和
  • AVG ([DISTINCT|ALL] <列名>) 计算平均数
  • MAX ([DISTINCT|ALL] <列名>) 最大值
  • MIN ([DISTINCT|ALL] <列名>) 最小值

[例] 查询学生总人数。

SELECT COUNT(*) FROM  Student

[例] 查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno) FROM SC

分组：

SELECT Cno,COUNT(Sno)
FROM SC 
GROUP BY Cno

注意：分组查询的结果只能包含分组依据和聚集函数

查询选修了3门以上课程的学生学号：

SELECT Sno
 FROM SC 
GROUP BY Sno 
HAVING COUNT(*)>3

WHERE和HAVING的区别在于作用对象不同： WHERE作用于基本表或视图，从中选择满足条件的元组， HAVING作用于子组，从中选择满足条件的组。

连接查询：

SELECT Student.* ,  SC.*  
FROM Student,SC  
WHERE Student.Sno=SC.Sno
  • 内连接。指定了INNER关键字的连接是内连接，内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。
  • 外连接。指定了OUTER关键字的为外连接，外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下三种。
    • 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行；
    • 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；
    • 完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。 其中的OUTER关键字均可省略。

嵌套查询：

  • 一个SELECT-FROM-WHERE语句称为一个查询块
  • 嵌套查询:嵌套两个以上的查询块
SELECT Sname		       /*外层查询/父查询*/
    FROM Student
    WHERE Sno IN
        （SELECT Sno            /*内层查询/子查询*/
             FROM SC
             WHERE Cno= ‘ 2 ’）；

带有比较运算符的查询：

SELECT Sno，Sname，Sdept
     FROM    Student
     WHERE Sdept   =
                   (SELECT Sdept
                    FROM    Student
                    WHERE Sname= ‘刘晨’)

带有ANY（SOME）或ALL谓词的子查询

在比较运算符中，=ANY等价于IN谓词，!=ALL等价于NOT IN谓词

相关子查询：

［例］找出每个学生超过他选修课程平均成绩的课程号。

 SELECT Sno， Cno
    FROM  SC  x
    WHERE Grade >=(SELECT AVG(Grade) 
		                          FROM  SC  y
                                       WHERE y.Sno=x.Sno);

带有EXISTS谓词的子查询

  • EXISTS( … )->true
  • EXISTS( )->False
  • NOT EXISTS( )->true
  • NOT EXISTS( …)->false

[例]查询所有选修了1号课程的学生姓名。

     SELECT Sname
     FROM Student
     WHERE EXISTS
                   (SELECT *
                    FROM SC
                    WHERE Sno=Student.Sno AND Cno= ' 1 ')；

数据更新：

插入数据：

语法：

INSERT  
INTO 〈表名〉[〈属性列1〉[，〈属性列2〉…)] 
VALUES (<常量1>[,<常量2>]…)

例如：

INSERT  
INTO SC（Sno，Cno）
VALUES （’95020‘，’1‘）

修改数据

语法：

UPDATE <表名>  
SET 〈列名〉=<表达式>[,〈列名〉=<表达式>]… 
[WHERE <条件>]

删除数据

语法：

DELETE  FROM  <表名>
[WHERE <条件>]
avatar
文章
阅读
粉丝