数据库

1、 第一范式（1NF）：表中的每一行只包含一个实例的信息，即无重复的列。

2、 第二范式（2NF）：要求数据库表中的每个实例或行必须可以被唯一地区分，要求实体的属性完全依赖于主关键字

3、 第三范式(3NF)：要求一个数据库表中不包含已在其它表中已包含的非主关键字信息

4、 使用SQL身份验证登陆，对一个只有指定的登录名登录的数据库。首先使用默认的sa登录，然后新建数据库如（QQ）,然后在安全性中的登录名中新建登录名如（QQ）,

在常规中做如下修改：

然后在左边选择页中的用户映射中选中QQ

下面不做修改，点击确定。登录名中就会多出来一个QQ，同时QQ数据库---安全性---用户列表里也会多一个QQ，但是属性中无法修改用户名和登录名，如果用QQ登陆，就会无法对别的数据库进行操作，也无法对QQ数据库创建表，因为没有权限，所以就需要删除数据库中QQ ---安全性---用户中的QQ，重新创建一个用户如bbb，用户名设为bbb，登录名设为QQ，最下面的角色成员选择

点击确定。然后断开连接，在文件处点击连接对象资源管理器，选择登录名为QQ和相应密码登录，然后就可以对数据库QQ进行创建表，对别的数据库不能进行操作。

5、 数据库是由文件和日志文件组成的，文件又可以包括主文件和次要文件。文件默认的保存在primary组名中，数据库中的数组是分布在所有的次要文件中，如果主文件与次要文件所分的组不同，在创建时，应在最后跟上组名，否则会默认创建在primary中

createdatabaseMyDB

onprimary//默认组名

(

name=mydb_dat,

filename='F:\data\mydb_dat.mdf',

size=5mb,

maxsize=20mb,

filegrowth=10%

)

,

filegroupdbfg//次要文件的组名

(

name=dbfg_dat1,

filename='E:\data\mydb_dat1.ndf',

size=5mb,

maxsize=20mb,

filegrowth=5%

),

(

name=dbfg_dat2,

filename='D:\data\mydb_dat2.ndf',

size=5mb,

maxsize=10mb,

filegrowth=1mb

)

logon//日志文件

(

name=mydb_log,

filename='F:\data\mydb_log.ldf',

size=5mb,

maxsize=20mb,

filegrowth=10%

)

6、 查看数据库信息：用sp_helpdb数据库名，如：sp_helpdbmydb

7、 语句修改数据库名：alterdatabaseMyDBmodifyname=yourDB

8、 存储过程修改修改数据名

execsp_dboption'yourDB','single',true//锁定

execsp_renamedb'yourDB','myDB'//修改

execsp_dboption'myDB','single',false //保存

9、 修改数据库文件大小：

use master

alter database myDB

modify file

(

Name=mydb_dat,

Size=6mb,

Filegrowth=1%

)

10、 增加数据库文件：

usemaster

alterdatabasemyDB

addfile

(

filename='F:\data\mydb_dat1.ndf',

name=mydb_dat1,

size=6mb,

filegrowth=1%

)

11、 删除数据库：dropdatabasemyDB

12、 从SQL删除数据库，但保持其数据和事务日志文件：exec sp_detach_dbmyDB

13、 附加数据库须指定主数据文件的名称和物理位置

Exec sp_attach_dbmyDB, ’f:\data\myDB_dat.mdf’

若附加的文件超过了16个，可使用create database语句，并指定for attach选项

14、 备份数据库：

15、 还原数据库：

16、 创建和删除自定义的数据类型

创建：

删除：

如：execsp_addtypenameType,'nvarchar(20)','not null'

execsp_droptypenameType

17、 架构是为了避免名称相同的表名重复，系统默认架构是dbo。在sa中定义新的架构，在Test中的安全性---用户中的相应用户属性中对默认架构进行修改即可，然后创建的表就会放在对应的架构中

18、 临时表的创建，本地临时表名称前有一个数字符号（#table_name），全局临时表前有两个（##table_name），本地临时表名称相同时也可以创建，为了区别，系统会自动为表名追加一个数字后缀。

createtable#MyTempTable(colaintprimarykey,idint)//创建临时表

insertinto#MyTempTablevalues(1,3)//插入相关数据

insertinto#MyTempTablevalues(2,6)

select*from#MyTempTable//查询该表

查询结果为：

createtable##MyTempTable2(idintprimarykey,namechar(20))

//创建临时全局表

insertinto##MyTempTable2values(1,'张三') //插入相关数据

select*from##MyTempTable2//查询该表

19、 创建表

useTestDB//指定数据库名

createtableTestTable//在指定数据库中创建表

(

dateasgetdate(),//自动获得日期时间

idint,

usenameasuser_name()//自动获得该表的用户名，如（dbo）

)

20、 表变量

useTestDB

declare@ttable

(

col1int,

col2varchar(10)

)

insertinto@tvalues(1,'很好')

select*from@t

21、 对表添加列

useTestDB

altertableTestTable

addphoneintnull,

emailchar(20)null

22、 修改某一列中的某个属性值

useTestDB

altertableTestTable

altercolumnemailvarchar(20)null

23、 修改字段名：

Exec sp_rename ‘架构名.表名.旧字段名‘,新字段名,’column

24、 删除列：

altertablecustoms

dropcolumncustomsAge

约束

25、 域约束：处理一个或多个列

26、 实体约束：对特定的行进行约束,记录就是行，行就是实体

27、 参数完整性约束：一列的两个值可以相同，但是对应的另一列的值不能相同

28、 在创建表时加主键：

createtablecustoms

(

customerNointidentitynotnullprimarykey,//identity为自动增长，并添加主键

customerNamevarchar(30)notnull

)

29、 主键约束，在现有表中添加主键：

altertablecustoms

addconstraintPK_customerNo//添加约束

Primarykey(customerNo)//选定主键

30、 外键约束，使用外键创建一个表：

createtableorders

(

orderIDintnotnullprimarykey,

customerNointnotnullforeignkeyreferencescustoms(customerno)//参照customs表中的no主键来创建本表的外键

)

31、 在已存在的表中添加外键

altertableorders

addconstraintFK_EmployeeCreatesOredr//添加外键约束

foreignkey(customerno)referencescustoms(customerno)//参照customs表中的no主键来创建本表的外键

32、 级联动作：默认的级联动作是，子表中如果有父表的ID引用，则父表的记录不能删除。如果父表中删除该记录，子表与该记录关联的记录全部删除

createtableorderDatails

(

orderidintnotnull,

partnovarchar(10)notnull,

constraintPKOrderDetailsprimarykey(orderid,partno),

constraintFKOrdercontrainsdetailsforeignkey(orderid)referencesorders(orderid)

onupdatenoaction

ondeletecascade

)

33、 唯一(unique)约束：要求列上有一个唯一的值

创建表时创建唯一约束：

createtableshippers

(

shipperidintidentitynotnullprimarykey,

phoneno1varchar(14)notnullunique,

phonenovarchar(14)notnull

)

在已存在的表中创建唯一约束:

altertableshippers

addconstraintAK_ShippersPhoneNounique(phoneno)

34、 check约束：不限制于一个特定的列

altertablecustoms

addconstraintcn_customerdateinsystem

check

(DatelnSystem<=GetDate())

删除约束：

altertablecustoms

dropconstraintcn_customerdateinsystem

35、 dafault约束：为表定义一个组成部分

创建默认值

createtableshippers

(

shipperidintidentitynotnullprimarykey,

dateinsystemsmalldatetimenotnulldefaultGetDate()

)

在已存在的表上加默认值

Altertablecustomers

Addconstraintcn_customerdefaultdateinsystem

Defaultgetdate()fordateinsystem

对表进行增删改查

36、 表中数据的操作

37、 使用insert into插入行：

insertintostudents(stuno,stuname,sex,age)

values(6,'张三','男',20)

38、 使用select into从现有的表中创建一个表并在同一操作中插入行，并且可

以使用top限制插入的行：

Selecttop 1customerNoasno,customerNameasname

//插入前一行

into#custom

fromcustoms//从customs表中的数据中创建临时表

select*from#custom//查询临时表

39、 插入部分数据并查询：

插入：Insertstudent(id,name)

values(6,'张三')

查询：select*fromstudent

whereid=6 andname='张三'

40、 删除表：delect 表名

如果要删除某一行可添加whete语句，如果删除前几行可使用top语句

41、 更新表中数据：

使用where指出哪些行要更新

用set指定新值

useNorthwind

updateProducts

setUnitPrice=(UnitPrice*1.5)

42、 比较字符串时，可使用like和通配符查找，like只可用于：char、nchar、

varchar、nvarchar和datetime,通配符的种类：

模糊查询companyname列包含restaurant的数据：

selectcompanynamefromCustomers

whereCompanyNamelike'%restaurant%'

43、 逻辑运算符的优先级：not、and、or

selectproductid,productname,supplierid,unitprice

fromProducts

where (ProductNamelike'T%'orProductID=16)

//产品名称以T开头或产品ID号为16的

and(UnitPrice>16.00)

44、 between查询在一定范围内的值，包含边缘值，返回不在指定区域的行时，使用

Not between

selectproductname,unitprice

fromProducts

whereUnitPricebetween 10 and 20

45、 in语句指定一个值的列表作为查询条件，搜索条件中不能包含null

值,使用not in搜索条件来返回值不在指定列表中的行

selectcompanyname,countryfromSuppliers

whereCountryin('Japan','Italy')

46、 is null用来查询某指定列没有任何信息的行，使用is not null来查询指定列中非空的行：

selectcompanyname,fax

fromSuppliers

whereFaxisnull

47、 使用order by为结果集中的行排序，默认为升序（asc），降序为（desc），可以在order by中选择列表中的位置，如order by 1就是第一列。如果有where，order by在whrer后。order by后可以接多个列名，先按第一列再按后面的依次排序，而且排序的列名可以不出现在列表中（select语句后）

selectEmployeeID,TitlefromEmployees

whereEmployeeID=6

orderbyFirstNamedesc

48、 使用distinct消除重复行，如果使用了distinct,order by中的字段必须出现在列表中（select语句后）

selectdistinctCountryfromSuppliers

orderbyCountry

49、 topn列出结果集中前n个记录，topnpercent列出结果集中前n%的记录，如果生成小数，向上取整；在order by 中指定值的范围，先排序后取值

selectdistincttop 5 Country//取前五行不重复的值

fromSuppliers

orderbyCountry

50、 聚合函数不能出现在where子句中：

selectproductid,SUM(quantity)asquantitySum,

AVG(Quantity)asqualityAvg//求数量总和和平均值

from[Order Details]

whereProductID=6

groupbyProductID

orderbyProductID

51、 使用聚合函数和group by能把表中的记录分组，并对组中数据进行汇总。

如果同时出现group by、order by和where，where应放在最前面，order by放在最后面；

52、 having子句，只有在使用group by子句的同时，使用having子句来限制分组，不能联合使用all和having子句。聚合函数不能出现在where子句中，但是可以出现在having子句中，放在group by和order by语句中间：

selectproductid,SUM(quantity)asquantitysum

from[Order Details]

groupbyProductID

havingSUM(Quantity)>500

orderbySUM(Quantity)

连接

53、 内部连接，返回两者的交集

selectProducts.*,Suppliers.SupplierID

fromProductsinnerjoinSuppliers

onProducts.ProductID=Suppliers.SupplierID

54、 外部连接分为左连接和右连接。左连接就是左边的表全部显示，右边显示满足条件的；同理右连接

左连接：selectProducts.*,Suppliers.SupplierID

fromProductsleftjoinSuppliers

onProducts.ProductID=Suppliers.SupplierID

右连接：selectProducts.*,Suppliers.SupplierID

fromProductsrightjoinSuppliers

onProducts.ProductID=Suppliers.SupplierID

55、 完全连接：将join两侧的数据全部匹配，并返回所有记录

selectdiscounttype,discount,s.stor_name

fromdiscountsd

fulljoinstoressond.stor_id=s.stor_id

56、 交叉连接：不使用on运算符，而是将join左侧的所有记录和另一侧的所有记录连接，返回的是笛卡尔积

selectdiscounttype,discount,s.stor_name

fromdiscountsd

crossjoinstoress

关键字查询

57、 Union用于将两个或两个以上的查询产生一个结果集

selectcompanyname,address,cityfromCustomers

union

selectcompanyname,address,cityfromSuppliers

58、 子查询：in关键字用来判断一个表中指定列的值是否包含在已定义的列表中或在另一个表中

select*fromCustomerswhereCustomerIDin

(selectdistinctCustomerIDfromOrders)

59、 Exists子查询不需要返回多行数据，而只需要返回一个真值或假值。作用是在where子句中测试子查询返回的行是否存在

select*fromOrderswhere

exists

(select*fromCustomerswhereCustomerID='alfki')

60、 Any子查询返回值中至少有一个值与条件比较为真，就满足搜索条件；all子查询返回的每个值与条件比较都为真

selectorderid,freightfromOrders

whereFreight<any

(selectSUM(unitprice)from[Order Details]groupbyOrderID)

61、 类型转换：cast和convert都可以执行数据类型转换。大部分情况下二者相同，不同的是covert能转换日期格式

Select'The Customer has an Order numbered '+cast

(orderidasvarchar)

fromorderswherecustomerid='alfki'

视图

62、 创建视图

createviewv_clastu

as

selectorders.orderID,customs.customerNo,customs.customerName

fromordersinnerjoincustoms

onorders.customerNo=customs.customerNo

63、 修改视图包括插入、修改、删除三方面的操作

自定义函数

64、 用户定义函数包括：标量函数、表值函数、内置函数

标量函数：返回一个标量（单值）结果

createfunctionfn_DateFormat

(@indatedatetime,@separatorchar(1))

returnsnchar(20)as

begin

return

convert(nvarchar(20),datepart(yy,@indate))

+@separator

+convert(nvarchar(20),datepart(mm,@indate))

+@separator

+convert(nvarchar(20),datepart(dd,@indate))

end

selectdbo.fn_DateFormat(GETDATE(),':')//查询函数

查询的返回结果是：

创建架构绑定函数：函数创建时使用SchemaBinding,创建后，所引用的数据库对象不能被更改。

创建、更改或删除用户定义函数，必须具有create function权限；

其他用户使用函数，则必须具有execute权限；

若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的reference权限；

更改函数：alter function

删除函数：drop function

表值函数：返回table数据类型,函数体内允许有：赋值语句、流控制语句、declare语句、select语句

Begin和end分隔了函数体

Returns子句指定table作为返回的数据类型

Returns子句定义了返回表的名字和格式

createfunctionfn_employees(@lengthvarchar(9))

returns@fn_employeestable//返回table

(employeeIDintprimarykeynotnull,

[employee name]nvarchar(61)notnull)

as

begin

if@length='shortname'

insert@fn_employeesselectemployeeID,lastnamefromemployees

elseif@length='longname'

insert@fn_employeesselectemployeeID,

(firstname+''+lastname)fromemployees

return

end

select*fromdbo.fn_employees('longname')//调用函数

内置函数：系统提供，返回标量数据类型或table数据类型

存储过程

65、 创建存储过程：根据用户名得到密码

createprocGetPassWord

@callnchar(10),

@passwordnchar(10)output

as

select@password=passwordfromV_users

wherecall=@call

66、 存储过程的查询：

declare@passwordnchar(10)

execGetPassWord'妈妈',@passwordoutput

print@password

67、 存储过程的简单创建和查询

createprocGetuser

as

select*fromuseres

select*fromfamilyUser

execgetuser