系统数据库和用户数据库,要求所有非主属性都

2019-10-29 18:10栏目:网络数据
TAG:

数据库范式理论

范式理论是为了建立冗余较小结构合理的数据库所遵循的规则。关系数据库中的关系必须满足不同的范式。目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、BCNF、第四范式(4NF)、第五范式(5NF)

1、进入sqlserver management studio

Microsoft SQL Server2008复习提高

第一范式

对象的任意属性不能被拆分,每个属性有且仅有一个值,即没有重复的行,没有重复的列。

2、选择安全性->登录名->右键新建登录名

一.Microsoft SQL Server 系统的体系结构

第二范式

在第一范式的基础上,要求所有非主属性都与主属性完全相关。假设属性1和属性2为主属性,属性3为非主属性,如果属性1或者属性2能唯一确定属性3,则不符合2NF,只有(属性1+属性2)能唯一确定属性3(有助于数据库基础性操作的实现)才符合2NF

3、在常规里输入用户名和密码

1.Microsoft SQL Server2008由4个主要的部分组成,即4个服务:

第三范式

在第二范式的基础上,要求除主键外其他字段不相关,不存在依赖性。比如一张表中非主属性1,2,3,属性1=属性2-属性3,则该表不满足第三范式。(不要在数据库中存储可以简单计算得出的数据)

4、在"用户映射"里“映射到此登录名的用户”选择该用户可以操作的数据库

  数据库引擎、分析服务、报表服务、集成服务。如下图:

BCNF

在第三范式基础上,要求表中所有字段(包括主键)都互不相关,不存在依赖性。即主属性不依赖于主属性。

5、在“数据库角色成员身份”里选择"db_datareader",

 

第四范式

表内不存在多对多关系。如果A和B是1:N的关系,A和C是1:N的关系,B和C互相独立,则不满足第四范式。

6、这样一个只读用户就创建完成了。

2.Microsoft SQL Server2008提供了两种类型的数据库:系统数据库和用户数据库。

第五范式

在第四范式的基础上,可以分解成更小的表。从最终结构重新建立原始结构。

 

系统数据库包括:master、model、msdb、Resource和tempdb数据库;

Transact-SQL行构造器

例:用INSERT语句一次性插入多行数据

CREATE TABLE a(
    Column1 NVARCHAR(max),
    Column2 NVARCHAR(max)
);
Go
INSERT INTO a VALUES(‘1’,’1’),(‘2’,’2’),(‘3’,’3’);

服务器角色参考 (可以对数据库的操作)

用户实例数据库包括:AdventureWorks、AdventureWorksDW、AdventureWorksDW2008、AdventureWorksLT、AdventureWorksLT2008等数据库。

用存储过程新建登录名和用户名

创建登录名huyan1,密码111111,默认数据库test,切换到test数据库下,创建登录名huyan1在test数据库中的用户hy1

EXECUTE sp_addlogin ‘huyan1’,’111111’,’test’
USE test
EXEC sp_adduser 'huyan1','hy1'

或使用sp_grantdbaccess创建一个与登录名相同的数据库用户名。

EXEC sp_addlogin 'hy2';
GO
USE test
EXEC sp_grantdbaccess 'hy2'

注:仅创建登录名而没有创建数据库用户名,该登录名无法正常登录数据库。可以使用sql语句创建数据库用户名,或者右键登录名—属性—用户映射中勾选数据库。创建和删除数据库用户名语句必须在该数据库下执行。用户名hy1默认权限public。

删除新建的登录名:

EXECUTE sp_droplogin ‘huyan1’

删除新建的用户名:

USE test;
EXECUTE sp_dropuser ‘hy1’

bulkadmin 可以运行 bulk insert 语句  bulk insert 详细 

Master数据库是最重要的系统数据库,它记录了服务器配置信息、登录帐户信息、数据库文件信息、SQL Server初始化信息等。

用户权限

dbcreator   创建,修改,删除,还原任何数据库

model:模板数据库,简化数据库的厨师创建和管理操作。

主要语句

USE test;
GRANT SELECT,UPDATE,DELETE
ON Customers
TO huyan1

REVOKE SELECT,UPDATE,DELETE
ON Customers
TO huyan1

注: GRANT语句必须在目标数据库下执行。

diskadmin   管理磁盘文件

msdb他是于SQLServerAgent服务有关的数据库。该系统记录了有关作业、警报、操作员、调度信息,这些信息可以用作自动化系统操作。

授予用户权限的前提

(1)创建登录名huyan1,密码111111,默认数据库test

EXCUTE sp_addlogin ‘huyan1’,’111111’,’test’

(2)在huyan1登录名的目标数据库test下创建用户hy

USE test;
CREATE USER hy FOR LOGIN huyan1 WITH DEFAULT_SCHEMA=test;

或者右键点击huyan1登录名,在属性—用户映射中勾选test数据库,系统会自动在test数据库下添加huyan1的用户

processadmin   可以终止在数据库引擎实例中运行的程序

Tempdb 是一个临时数据库,用于存储查询过程中的中间数据和结果。

授予权限

USE test;
GRANT SELECT,UPDATE,DELETE
ON Customers
TO hy

注:这里的hy指的是数据库用户名而非登录名。

securityadmin   可以管理登录名及其属性,具有grant,deny,和revoke服务器和数据库级别权限,还可以重置sql server 登录名的密码

3.OLTP和OLAP

撤销权限

撤销用户hy在Customers表中的SELECT,UPDATE,DELETE权限

USE test;
REVOKE SELECT,UPDATE,DELETE
ON Customers
TO hy

serveradmin     可以更改服务器范围的配置选项和关闭服务器

OLTP是传统的关系型数据库的主要应用,主要是基本的、日常的事务处理,例如银行交易。OLAP是数据仓库系统的主要应用,支持复杂的分析操作,侧重决策支持,并且提供直观易懂的查询结果.

拒绝权限

拒绝用户hy在Customers表中的DELETE权限

USE test;
DENY DELETE
ON Customers
TO hy

注:REVOKE和DENY的区别在于,用户权限被DENY后无法通过其组或角色成员身份继承该权限,而权限被REVOKE后还可以通过继承和授予得到。

setupadmin     可以添加和删除链接服务器,并对可以执行某些系统执行存储过程(如,sp_serveroption)

4.数据库对象类型主要包括了:

创建角色并分配给用户

在test数据库下,用存储过程创建角色p_test,该角色的所有者为hy。为p_test角色授予Customers表的所有权限。用存储过程为角色p_test分配成员用户hy。

USE test
EXEC sp_addrole ‘p_test’,’hy’;
GRANT ALL ON Customers TO p_test;
EXEC sp_addrolemember ‘p_test’,’hy’;

注:角色所有者hy并不是角色成员。用EXEC sp_addrole ‘p_test’,’hy’语句创建角色p_test并设置所有者为hy,并不代表hy是p_test的成员,拥有p_test角色的权限。

为角色p_test移除成员hy

EXEC sp_droprolemember ‘p_test’,’hy’;

sysadmin         在sql server中进行任何活动,该觉得的权限跨越所有其他固定服务器角色,默认情况下,windows builtinadmin组(本地管理员组)的所有成员都是sysadmin固定服务器角色的成员

数据库关系图、表、视图、同义词、可编程性、Service Broker、存储和安全性等。

服务器角色

bulkadmin:允许运行BULK INSERT语句,用于从文本中大量插入数据到数据库中
dbcreator:允许创建,修改,删除和还原任何数据库,适用于助理DBA和开发人员
diskadmin:允许管理磁盘文件,比如镜像数据库和添加备份设备,适用于助理DBA
processadmin:允许多任务化的管理,可以通过多个进程做多件事情,也可以删除进程
securityadmin:安全管理员,管理登录名及其属性
serveradmin:服务管理员,更改服务器的配置选项和关闭服务器
setupadmin:管理链接服务器,控制启动的存储过程
sysadmin:有权执行任何任务,仅适用于数据库管理员

 

5.SQL Server Profilter(照相)

数据库角色

用户映射参考  (可以对数据库的数据进行操作)

它主要用于从服务器中捕获Sqlserver2008事件的工具。

标准角色

允许用户适用单一的权限来创建角色。如创建一个叫User的角色,允许用户INSERT,SELECT,UPDATE数据库中的指定表,不允许其他任务。

db_owner       可以执行数据库中技术所有动作的用户

6.数据库引擎优化顾问:

应用程序角色

允许用户为特定应用程序创建密码保护

db_accessadmin 可以添加,删除用户的用户

这个工具可以帮助用户分析工作负荷、提出创建高效率索引的建议等功能。他就是一个优化大师,尽可能的提高系统效率。

预定义数据库角色

这些角色是内置的,不能被更改权限
(1) db_owner:可以做其他角色能做的所有事情,还可以做一些管理性操作
(2) db_accessadmin:可以通过添加或删除用户指定谁可以访问数据库
(3) db_securityadmin:可以修改角色成员身份和管理权限
(4) db_dlladmin:可以在数据库中运行所有DLL命令,创建修改和删除数据库对象而不必浏览其数据
(5) db_backupoperator:备份数据库
(6) db_datareader:读取所有用户所有表中的数据
(7) db_datawriter:可以添加更改删除所有用户所有表中的数据
(8) db_denydatareader:不能读取任何用户任何表中的数据
(9) db_denydatawriter:不能对任何用户任何表中的数据做添加更改删除的操作
(10) public:每个数据库用户都属于public角色,未对用户授予权限时该用户将继承public权限。该角色不能被删除。

db_datareader 可以查看所有数据库中用户表内数据的用户

7.数据库管理员DBA

系统数据库

db_datawrite 可以添加,修改,删除所有数据库用户表内数据的用户

职责:保证系统正常高效的运行。

master数据库

存储了登录名和用户ID所属角色,系统配置,数据库名和初始化信息,是最重要的数据库。

db_ddladmin 可以在数据库中执行ddl操作的用户,DDL(Data Definition Language)数据表的创建以及管理

 

model数据库

存储了创建数据库时的一些预定义标准如数据库初始大小,特定信息集等,是tempdb数据库的基础。

db_securityadmin 可以管理数据库中与安全权限有关所有动作的用户

二.管理安全性

tempdb数据库

临时性数据库,在sql server2008启动时新建,在sql server2008关闭时丢失。主要存储用户建立的临时表和临时存储过程。

db_backoperator 可以备份数据库的用户(可以发布dbcc和checkPoint语句,这两个语句一般在备份前使用

  1. 管理登录名

msdb数据库

给sql server2008提供必要的信息来运行作业。

db_denydatareader 不能看到数据库中任何数据的用户

包括:创建登录名、设置密码策略、查看登录名信息及修改和删除登录名等。

数据库文件和日志

db_denydatawrite 不能修改数据库中任何数据的用户

登录策略:windows身份验证和sqlserver身份验证登录。

主数据文件

一个数据库对应一个主数据文件,扩展名为.mdf,包含数据库的启动信息并指其他文件,用户数据和对象可存储在主数据文件中,也可以存储在辅助数据文件中。

比如:create login lin   from windows go//创建windows登录名

辅助数据文件

可选的,由用户定义存储用户数据的文件,扩展名为.ndf,当数据库超过单个Windows文件的最大限制,可使用辅助数据文件将数据分散到多个磁盘上,使数据库能够继续增长。

      create login lin with password=‘sa’//创建sqlserver登录名

事务日志文件

保存了用于恢复数据库的日志信息,每个数据库必须至少有1个日志文件。扩展名为.ldf。

  1. 管理数据库用户

数据库快照(database snapshot)

数据库快照是源数据库的静态只读视图,与源数据库位于同一服务器实例上,与源数据库在事务上一致,源数据库更新时,数据库快照也将更新。一个数据库可以存在多个数据库快照。

数据库用户是数据库级的主体,是登录名在数据库中的映射,实在数据库中执行操作和活动的行动者。

优点

(1) 用于报告目的。客户端可以查询数据库快照,以便利用创建快照时的数据编写报表。
(2) 用于保存历史数据以生成报表。数据库快照保留了某个时间点的历史数据,方便用户日后对该时间点的历史数据生成报表。
(3) 通过带有镜像数据库的数据库快照来访问镜像数据库,释放主体数据库上的资源。
(4) 使数据免受管理失误带来的影响
(5) 如果源数据库上出现用户错误,可以将数据库恢复到创建数据库快照时的状态。
(6) 管理测试数据库。在第一轮测试开始前,对测试数据库创建数据库快照。在测试结束后可利用数据库快照将数据库恢复到测试前的状态,以便重新开始测试。

注:数据库快照与源数据库相关,且无法对脱机和损坏的数据库进行恢复,不能替代备份和还原。所有恢复模式都支持数据库快照。

在某个A数据库中创建对应sa登录名的用户lin:

数据库快照的限制

(1) 数据库快照存在期间,不能对源数据库进行分离,还原和删除操作,但可以备份。
(2) 每次更新源数据库时都会对快照进行“写入时复制”操作,源数据库的I/O性能受到影响。
(3) 不能从源数据库或任何快照中删除文件。
(4) 必须与源数据库在同一个服务器实例上创建和保存。
(5) 依赖于源数据库,但不是冗余存储,无法防止磁盘错误或其他类型的损坏。
(6) 快照更新时用尽磁盘空间或遇到其他错误,则成为可以快照,必须删除。
(7) 快照只读,无法升级,因此升级后不可用。
(8) 无法对model数据库,tempdb数据库,master数据库创建快照。
(9) 不能附加,分离,备份,还原数据库快照,不能对数据库快照中的文件进行删除。
创建数据库快照的唯一方式是使用Transact-SQL语句。且Microsoft SQL Server Management Studio不支持数据库快照。

use A

创建数据库快照

CREATE DATABASE test_snapshot_201806271505 ON
(
    NAME=test,
    FILENAME='C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAtest_snapshot_201806271505.ss'
)AS SNAPSHOT OF test;
GO

上述代码中,test_snapshot_201806271505是快照名称,NAME=test中的test是源数据库的逻辑名称,可右键数据库—属性—文件查看。FILENAME中填写的是快照的稀疏文件存储路径,其中test_snapshot_201806271505.ss是稀疏文件的名称,最后一个test是源数据库名称(并非逻辑名称)。

注:实际在SQL Server Management Studio中运行上述语句创建数据库快照,并未运行成功。
消息1844,级别16,状态1,第1 行
Express Edition with Advanced Services (64-bit) 不支持Database Snapshot。

create user lin from login sa  [with default_schema=架构名] go

恢复数据库快照

USE master
RESTORE DATABASE test FROM
DATABASE_SNAPSHOT='test_snapshot_201806271505';
GO

上述代码中,test是源数据库名称,test_snapshot_201806271505是快照名称。

3.管理架构

删除数据库快照

DROP DATABASE test_snapshot_201806271505;

注:用数据库快照恢复数据库时,源数据库不能存在多个快照,也不能存在任何只读或压缩文件组,创建快照时联机现在脱机的文件。

架构是形成单个命名空间的数据库实体的集合。

创建和修改数据库

如果一个用户没有制定架构名,则默认为dbo架构。

创建数据库

CREATE DATABASE 教务管理系统
ON(
    NAME='教务管理系统_DATA',
    FILENAME='E:教务管理系统_DATA.mdf',
    SIZE=5MB,
    MAXSIZE=10MB,
    FILEGROWTH=5%
)
LOG ON(
    NAME='教务管理系统_LOG',
    FILENAME='E:教务管理系统_LOG.ldf',
    SIZE=2MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB
)

1)创建架构的sql:   create schema 架构名

修改数据库名称

ALTER DATABASE 教务管理系统 MODIFY NAME=database_name;

或者使用存储过程

EXEC sp_renamedb 'database_name','School_MIS';

2)创建某个架构属于某个用户:create schema 架构名 authorization 用户名

修改数据库大小

可以通过为数据库新增一个次要数据库文件实现增大数据库容量

ALTER DATABASE School_MIS
ADD FILE(
    NAME='School_MIS1',
    FILENAME='E:School_MIS1.mdf',
    SIZE=3MB,
    MAXSIZE=10MB,
    FILEGROWTH=10%
)

上述代码中将新增的次要文件命名为School_MIS1,存储路径为E:School_MIS1.mdf,初始大小为3MB,增长的最大限制为10MB,自动增长的增量为10%

3)创建某个架构属于某个用户同时创建表: create schema 架构名 authorization 用户名 create table 表名 (………)

查看数据库状态

(1) 通过查询master表中的sys.databases视图的state_desc列值来查看数据库状态,查询条件是数据库名称name

SELECT state_desc FROM sys.databases
WHERE name='test'

(2) 通过使用DATABASEPROPERTYEX()函数的STATUS属性来查看状态

USE test
GO
SELECT DATABASEPROPERTYEX('test','STATUS') AS '当前数据库状态';

SQL Server 2008提供了多种方法来查看数据库信息,如使用master数据库中的sys.database_files查看数据库文件信息,sys.filegroups查看数据库组的信息,sys.maste_files查看数据库文件基本信息和状态信息。除了目录视图和函数,还可以通过存储过程sp_spaceused查看数据库使用和保留的空间。

USE test
GO
EXEC sp_spaceused;

使用sp_helpdb查看数据库基本信息

EXEC sp_helpdb 'test';

4.数据库角色(权限)

分离和附加数据库

数据库角色是数据库级别的主体,也是数据库用户的集合。

分离数据库

将数据库从SQL Server实例中删除,但使数据库在其数据文件和事务日志文件中保持不变,之后就可以使用这些文件将数据库附加到任意SQL Server实例。
下列情况不能分离数据库:
已复制并发布的数据库不能分离,如果数据库已发布,必须通过运行sp_replicationdboption禁用发布后才能分离。
如果数据库中存在快照,必须删除所有快照才能进行分离。
该数据库正在某个数据库镜像会话中进行镜像。
数据库处于可以状态不能分离。
数据库是系统数据库不能进行分离。

1)创建简单的角色:create role 角色名

分离数据库命令

USE master
EXEC sp_detach_db 'test';

2)创建带所有者的角色:create role 角色名 authorization 用户名

附加数据库

利用分离出的数据文件和事务日志文件将数据库附加到其他服务器实例。可以附加复制的和分离的数据库。

3)在角色中添加成员:sp_addrolemember(‘lin’,…)

附加数据库命令

5.管理权限(放权和释权)

无日志附加数据库命令
CREATE DATABASE test ON(
    FILENAME='C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAtest.mdf' 
)FOR ATTACH_REBUILD_LOG;

授权:grand语句

多个.mdf,.ndf,.ldf文件附加数据库命令
CREATE DATABASE School_MIS ON(
FILENAME='E:教务管理系统_DATA.mdf'),
(FILENAME='E:School_MIS1.mdf'),
(FILENAME='E:test_data.ndf'
)LOG ON(
FILENAME='E:教务管理系统_LOG.ldf'
)FOR ATTACH;

释权:revoke语句

存储过程附加数据库命令
EXEC sp_attach_db @dbname='School_MIS',
@filename1='E:教务管理系统_DATA.mdf',
@filename2='E:School_MIS1.mdf',
@filename3='E:test_data.ndf',
@filename4='E:教务管理系统_LOG.ldf';

拒绝:deny语句 通过继承他人权限则无法收回权限,便可以用deny拒绝。

1)  将创建数据库的权限授予某个lin用户:grant create table to lin [with grant option 表示可以将此权限转给他人]

2)  将lin在某张表的查询权限回收:revoke select on student from lin go

  1. Microsoft SQL Server2008内置的加密机制

对称加密和非对称加密两种。密钥分为公钥和私钥。

对称加密:

图片 1

 

三.管理数据库

1.数据库文件和文件组的特征:

数据库文件分为:主数据库.mdf、次数据库.ndf、日志文件.ldf;

1)主数据库.mdf包含数据库的启动信息,并指向数据库中的其他文件。用

户数据和对象可存储在此文件中,也可以存储在次要数据文件中。

每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是 .mdf。

2) 次要数据文件是可选的,由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上,次要文件可用于将数据分散到多个磁盘上。另外,如果数据库超过了单个Windows 文件的最大大小,可以使用次要数据文件,这样数据库就能继续增长。次要数据文件的建议文件扩展名是 .ndf。

3)日志文件

文件组:是文件的集合。

注意事项:①一个文件或者文件组只能用于一个数据库,不能用于多个数②一个文件只能是某一个文件组的成员,不能是多个文件组的成员③数据库的数据信息和日志信息不能放在同一个文件或文件组中,应该分开④日志文件永远不能是任何文件组的一部分。

  1. 管理单位:管理的最小物理单位是以页为单位的,每一个页的大小为8KB.extend 64kb。
  2. 定义数据库: create database 数据库名

修改数据库: alter database数据库名

  1. 扩大数据库方法:

1)通过增加新的文件来扩大数据库。

例如:alter database 数据库名  add file ( name=‘‘,filename=’’,size=’’,filegrowth=’’)

2)通过扩大数据库文件的大小来扩大数据库

例如:alter database 数据库名 modify file(name=’’, size=’新值’)

6.三种主要收缩数据库方法:

   1)设置数据库为自动收缩;auto_shrink

   2)收缩整个数据库的大小: dbcc shrinkdatabase

   3)收缩制定的数据文件: dbcc shrinkfile

7.管理数据库快照:

概念: 数据库某一瞬时的状态.

1)创建数据库快照的语法:   create database 快照名称   on(name=’’,filename=’’, as snapshot of 源数据库名称)

2)删除快照: drop database  快照名称

8.RAID技术—独立磁盘冗余阵列:

RAID是一个磁盘系统,可以将多个磁盘驱动器合成一个磁盘阵列,以提供高性能、高可靠性及低成本。

1)  容错等级: RAID0(数据并行)、 RAID1(设备镜像)、 RAID5(奇偶信息并行存储).

 

四.T-SQL语言

1.T-SQL语言的4个特点:  一体化(定义、操纵、控制、事务管理语言与一体)、两种使用方式(交互式和嵌入式到高级语言中)、非过程化语言(告诉干什么就行)、类似人的语言,容易理解和掌握。

2.T-SQL语言的5个特点:数据定义语言、数据操纵语言、数据控制语言、事务管理语言和附加的语言元素。

3.重点说一下附加语言元素:

1)标识符格式规则:  规则一,第一个字符①Unicode标准定义的字母a-z、A-Z②下划线_、符号@、数字符号#; 以一个符号@开头的标识符表示局部变量,以两个符号@@开头表示内置的某些函数。以#开头表示临时表或临时存储过程,##开头标识符表示全局临时对象。

规则二:①跟第一字符规则一样,多了数字。

规则三:不能是保留字;

规则四:不允许嵌入空格和其他特殊字符。

分割标识符:“”、[]

4.变量和常量:

变量以@开头 定义变量如: declare @name varchar

用set为变量设置值

对于常量需要使用’’来使用。

  1. 控制流语言:

Begin…end 、break、goto、continue、if…else、 while、return、waitfor(悬挂起批处理、存储过程、事务的执行)。

  1. 错误的捕捉语言:

Try…catchj构造和@@ERROR函数、ERROR_NUMBER(返回错误号)。

  1. 数据类型:

 

 

  1. 聚合函数和内置函数:

  SUM                                                             返回选取结果集所有值的和
   MAX                                                             返回选取结果集中所有值的最大值
   MIN                                                             返回选取结果集中所有值的最小值
   AVG                                                             返回选取结果集中所有值的平均值
   COUNT                                                          返回选取结果集中行的数目

  内置函数:

Convert                 Convert(varchar(10),123)结果返回“123”                   装换数据类型
  DataLength              DataLength('12中国')结果返回6                           返回任何数据类型的字节数。“中国”包含4个字节,“12”包含两个字节,所以返回6.

 

日期和时间函数:

-时间函数

select  getDate() as currentTime;

select  GETUTCDATE() AS UTCtime;

select  DAY('2015-04-30 01:01:01');

SELECT  MONTH('2015-04-30')AS monthValue;

SELECT  YEAR('2015-04-30'),YEAR('1997-07-01');

 

SELECT DATENAME(year,'2015-04-30 01:01:01') AS yearValue,

       DATENAME(quarter,'2015-04-30 01:01:01') AS quaterValue,

       DATENAME(dayofyear,'2015-04-30 01:01:01') AS dayofyearValue,

       DATENAME(day,'2015-04-30 01:01:01') AS dayValue,

       DATENAME(week,'2015-04-30 01:01:01') AS weekValue,

       DATENAME(weekday,'2015-04-30 01:01:01') AS weekdayValue,

       DATENAME(hour,'2015-04-30 01:01:01') AS hourValue,

       DATENAME(minute,'2015-04-30 01:01:01') AS minuteValue,

       DATENAME(second,'2015-04-30 01:01:01') AS secondValue;

 

SELECT  DATEPART(year,'2015-04-30 01:01:01'),

        DATEPART(month,'2015-04-30 01:01:01'),

        DATEPART(dayofyear,'2015-04-30 01:01:01');

       

SELECT    DATEADD(year,1,'2015-04-30 01:01:01') AS yearAdd,

        DATEADD(month ,2, '2015-04-30 01:01:01') AS weekdayAdd,

        DATEADD(hour,2,'2015-04-30 01:01:01') AS hourAdd;

 

排名函数:

  • --创建测试数据表  
  • DECLARE @table TABLE  (姓名 VARCHAR(4),成绩 INT)  
  • insert into @table  
  • SELECT '张三',129 UNION ALL  
  • SELECT '李四',137 UNION ALL  
  • SELECT '王二',137 UNION ALL  
  • SELECT '小明',126 UNION ALL  
  • SELECT '小六',126 UNION ALL  
  • SELECT '小白',125 UNION ALL  
  • SELECT '小黑',124 UNION ALL  
  • SELECT '西大',123 UNION ALL  
  • SELECT '小才',120 UNION ALL  
  • SELECT '师兄',120 UNION ALL  
  • SELECT '唐朝',99 UNION ALL  
  • SELECT '尼玛',99  
  •     
  • --查看一下  
  • --SELECT * FROM @table  
  •    
  • SELECT 姓名,成绩,  
  •   ROW_NUMBER()  OVER(ORDER  BY 成绩 DESC) AS [ROW_NUMBER],  
  •   RANK()        OVER(ORDER  BY 成绩 DESC) AS [RANK],  
  •   DENSE_RANK()  OVER(ORDER  BY 成绩 DESC) AS [DENSE_RANK],  
  •   NTILE(6)      OVER(ORDER  BY 成绩 DESC) AS [NTILE]  
  • FROM @table  

五.数据库表

1.表的类型: 普通表、以分区表、临时表、系统表。

2.创建表:create table 表名

3.计算列:使用persisted关键字,指定由该列保存计算得到的数据,因此该列是一个物理列,当该列所依赖的其他列中的数据发生变化时该列中的数据也会发生变化。

4.创建临时表: #本地临时表只能由创建着使用 、 ##全局临时表,其生存期间可以为所有的用户使用。

例如:create table ##student(…)

5.增加列和删除列

Alert table 表名    add/drop  column_name 数据类型

6.修改该列的数据类型

Alter table table_name  alter COLUMN column_name new_type_name数据类型

7.创建和修改标识符列:

identity(seed, increment)

8.ROWGUIDCOL列

Uname unqiueidentityfier default NEWID() 这就一个ROWGUIDCOL列。

10.创建分区表步骤:

1).创建分区函数,指定如何分区。

2)创建分区方案,指定分区函数的分区在文件祖上的位置。

3)创建使用分区表方案的表。

例如:

创建分区函数:  create partition function saleAmountPF(INT)

                As rang left for values(10,100,1000,10000)

创建分区方案:   create partition schema saleAmountPS

                As partition saleAmountPF

                TO(saleFG1,saleFG2,…)

创建分区表:     create table salePT(

serolId int,  saleAmount int, saleDate datetime, salePerson varchar(32)  on saleAmountPF(saleamount)

)

六.操纵数据

 1.使用INSERT语句插入数据

Insert into 表名(字段列…) values(值…)

如果是unicode数据,应该在字符数据的引号前使用N字符。

字符数据需要使用 引号 引起来,数字数据可以直接插入。

提供空值:NULL、default。

使用default values向表中提供一行全部是默认值。

使用bulk insert语句按照用户指定的格式批量的插入数据。

2.使用update语句更新数据

Update 表名 set column_name=expression,….

Where 查询条件

 

3.使用delete语句删除数据

Delete from table_name where 删除条件

delete操作只是删除表中的数据,并没有破坏表的结构。被删除的数据会被记录在日志中。

Truncate table语句删除表中的数据,系统会立即释放表中的数据和索引所占的空间,并且不把这种数据的变化记录在日中。

4.改变列标题 = 和 as

5.order by进行排序:可以使用多个列进行排序,如果第一个列的值相同,那么就按照第二个列排序,每一个列都可以指定升序或降序。

Top:返回指定数量的数据行。用在分页。

6.like进行模糊匹配 % _ [] [^]

7.Compute字句:  当使用聚合函数得到的结果集全是聚合值,没有明细值,解决方法就是是哟哦那个compute字句。

两种形式:一种带by字句;一种不带by字句。

例如:  select … from 表名  order by 排序字段 compute sum(字段)

select … from 表名  order by 排序字段 compute sum(字段) by 字段

8.分组技术:group by子句

分为无条件的分组:

Select 字段…from 表名  where 条件 group by 列名

有条件的分组:

Select 字段…from 表名  where 条件 group by 列名 having 分组筛选条件

ROLLUP得到各组的单项组合和CUBE关键字得到各组之间的任意组合。

 

9.连接技术:交叉连接即笛卡儿积 cross join、内连接inner join 用on指定连接条件按、左外连接left outer join、有外连接right outer join、完全外连接full outer join

10.子查询技术:where、group by、having

七.索引

1.索引的类型和特点

建立索引的优点:
1).大大加快数据的检索速度;
2).创建唯一性索引,保证数据库表中每一行数据的唯一性;
3).加速表和表之间的连接;
4).在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间.
索引类型:
根据数据库的功能,可以在数据库设计器中创建四种索引:唯一索引、非唯一索引、主键索引和聚集索引.尽管唯一索引有助于定位信息,但为获得最佳性能结果,建议改用主键或唯一约束.
唯一索引:
唯 一索引是不允许其中任何两行具有相同索引值的索引.当现有数据中存在重复的键值时,大多数数据库不允许将新创建的唯一索引与表一起保存.数据库还可能防止 添加将在表中创建重复键值的新数据.例如,如果在 employee 表中职员的姓 (lname) 上创建了唯一索引,则任何两个员工都不能同姓.
非唯一索引:
非唯一索引是相对唯一索引,允许其中任何两行具有相同索引值的索引.当现有数据中存在重复的键值时,数据库是允许将新创建的索引与表一起保存.这时数据库不能防止添加将在表中创建重复键值的新数据.
主键索引:
数据库表经常有一列或列组合,其值唯一标识表中的每一行.该列称为表的主键.在数据库关系图中为表定义主键将自动创建主键索引,主键索引是唯一索引的特定类型.该索引要求主键中的每个值都唯一.当在查询中使用主键索引时,它还允许对数据的快速访问.
聚集索引(也叫聚簇索引):
在聚集索引中,表中行的物理顺序与键值的逻辑(索引)顺序相同.一个表只能包含一个聚集索引.如果某索引不是聚集索引,则表中行的物理顺序与键值的逻辑顺序不匹配.与非聚集索引相比,聚集索引通常提供更快的数据访问速度.

2.创建索引:create index语句

Create [unique唯一] [clustered聚集 | nonclustered非聚集] index index_name on table_or_view_name (列名)[include (列…)包含性]

例如: create unique clustered index index_xxx on tabl_xxx(…)

3.维护索引:dbccshow_statistics命令和图形化工具。查看索引碎片信息:sys.dm_db_index_physical_stats系统函数和使用图形化工具。

未完待续。。。。。。明天更新。

版权声明:本文由大奖888-www.88pt88.com-大奖888官网登录发布于网络数据,转载请注明出处:系统数据库和用户数据库,要求所有非主属性都