初学SQL Server数据库的一些常用操作总结.docx
《初学SQL Server数据库的一些常用操作总结.docx》由会员分享，可在线阅读，更多相关《初学SQL Server数据库的一些常用操作总结.docx（12页珍藏版）》请在冰豆网上搜索。
初学SQLServer数据库的一些常用操作总结
初学SQLServer数据库的一些常用操作总结
对于初学者来说，SQLServer数据库的学习似乎有一定的难度。
本文我们主要对SQLServer数据库的一些常用操作进行了总结，希望能够对初学者有所帮助。
1.增加字段
1.alter table docdsp
2.add dspcode char（200）
2.删除字段
1.ALTER TABLE table_NAME DROP COLUMN column_NAME
3.修改字段类型
1.ALTER TABLE table_name
2.ALTER COLUMN column_name new_data_type
4.sp_rename改名
更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称。
语法：
1.sp_rename [ @objname = ] 'object_name' ,
2.[ @newname = ] 'new_name'
3.[ , [ @objtype = ] 'object_type' ]
如：
1.EXEC sp_rename
2.'newname','PartStock'
5.sp_help显示表的一些基本情况
1.sp_help 'object_name'
2.如：
3.EXEC sp_help
4.'PartStock'
6.判断某一表PartStock中字段PartVelocity是否存在
1.if exists （select * from syscolumns where id=object_id（'PartStock'） and name='PartVelocity'）
2.print 'PartVelocity exists'
3.else print 'PartVelocity not exists'
另法：
判断表的存在性：
1.select count（*） from sysobjects where type='U' and name='你的表名'
判断字段的存在性：
1.select count（*） from syscolumns
2.where id
3.= （select id from sysobjects where type='U' and name='你的表名'）
4.and name = '你要判断的字段名'
7.随机读取若干条记录
Access语法：
SELECTtop10*From表名ORDERBYRnd（id）
Sqlserver：
selecttopn*from表名orderbynewid（）
mysql：
select*From表名OrderByrand（）Limitn
8.说明：
日程安排提前五分钟提醒
select*from日程安排wheredatediff（minute,f开始时间,getdate（））>5
9.前10条记录
selecttop10*formtable1where范围
10.包括所有在TableA中但不在TableB和TableC中的行并消除所有重复行而派生出一个结果表
（selectafromtableA）except（selectafromtableB）except（selectafromtableC）
11.说明：
随机取出10条数据
selecttop10*fromtablenameorderbynewid（）
12.列出数据库里所有的表名
selectnamefromsysobjectswheretype=U
13.列出表里的所有的字段名
selectnamefromsyscolumnswhereid=object_id（TableName）
14.说明：
列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select中的case。
1.select type,sum（case vender when A then pcs else 0 end）,sum（case vender when C then pcs else 0 end）,
2.sum（case vender when B then pcs else 0 end） FROM tablename group by type
15.说明：
初始化表table1
TRUNCATETABLEtable1
16.说明：
几个高级查询运算词
A：
UNION运算符
UNION运算符通过组合其他两个结果表（例如TABLE1和TABLE2）并消去表中任何重复行而派生出一个结果表。
当ALL随UNION一起使用时（即UNIONALL），不消除重复行。
两种情况下，派生表的每一行不是来自TABLE1就是来自TABLE2。
B：
EXCEPT运算符
EXCEPT运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。
当ALL随EXCEPT一起使用时（EXCEPTALL），不消除重复行。
C：
INTERSECT运算符
INTERSECT运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。
当ALL随INTERSECT一起使用时（INTERSECTALL），不消除重复行。
注：
使用运算词的几个查询结果行必须是一致的。
17.说明：
在线视图查询（表名1：
a）
select*from（SELECTa,b,cFROMa）Twheret.a>1;
18.说明：
between的用法,between限制查询数据范围时包括了边界值,notbetween不包括
1.select * from table1 where time between time1 and time2
2.select a,b,c, from table1 where a not between 数值1 and 数值2
19.说明：
in的使用方法
select*fromtable1wherea[not]in（‘值1’,’值2’,’值4’,’值6’）
20.说明：
两张关联表，删除主表中已经在副表中没有的信息
deletefromtable1wherenotexists（select*fromtable2wheretable1.field1=table2.field1）
21.说明：
复制表（只复制结构,源表名：
a新表名：
b）（Access可用）
法一：
select*intobfromawhere1<>1
法二：
selecttop0*intobfroma
22.说明：
拷贝表（拷贝数据,源表名：
a目标表名：
b）（Access可用）
insertintob（a,b,c）selectd,e,ffromb;
23.说明：
跨数据库之间表的拷贝（具体数据使用绝对路径）（Access可用）
insertintob（a,b,c）selectd,e,ffrombin‘具体数据库’where条件
例子：
..frombin"&Server.MapPath（"."）&"/data.mdb"&"where..
24.创建数据库
CREATEDATABASEdatabase-name
25.说明：
删除数据库
dropdatabasedbname
26.说明：
备份sqlserver
1.创建备份数据的device
2.USE master
3.EXEC sp_addumpdevice disk, testBack, c:
/mssql7backup/MyNwind_1.dat
4.开始备份
5.BACKUP DATABASE pubs TO testBack
27.说明：
创建新表
createtabletabname（col1type1[notnull][primarykey],col2type2[notnull],..）
根据已有的表创建新表：
A：
createtabletab_newliketab_old（使用旧表创建新表）
B：
createtabletab_newasselectcol1,col2…fromtab_olddefinitiononly
28.说明：
删除新表：
droptabletabname
29.说明：
增加一个列：
Altertabletabnameaddcolumncoltype
注：
列增加后将不能删除。
DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
30.说明：
添加主键：
Altertabletabnameaddprimarykey（col）
说明：
删除主键：
Altertabletabnamedropprimarykey（col）
31.说明：
创建索引：
create[unique]indexidxnameontabname（col….）
删除索引：
dropindexidxname
注：
索引是不可更改的，想更改必须删除重新建。
32.说明：
创建视图：
createviewviewnameasselectstatement
删除视图：
dropviewviewname
33.说明：
几个简单的基本的sql语句
选择：
select*fromtable1where范围
插入：
insertintotable1（field1,field2）values（value1,value2）
删除：
deletefromtable1where范围
更新：
updatetable1setfield1=value1where范围
查找：
select*fromtable1wherefield1like’%value1%’---like的语法很精妙，查资料!
排序：
select*fromtable1orderbyfield1,field2[desc]
总数：
selectcount*astotalcountfromtable1
求和：
selectsum（field1）assumvaluefromtable1
平均：
selectavg（field1）asavgvaluefromtable1
最大：
selectmax（field1）asmaxvaluefromtable1
最小：
selectmin（field1）asminvaluefromtable1
以上就是SQLServer数据库的一些常用的操作的全部内容，本文我们就介绍到这里了，希望本次的介绍能够对您有所帮助。
2.3SQLPlus常用命令
SQL>helpindex查看所有可用的命令
SQL>SETSQLBLANKLINESON支持空格行
SQL>SELECT*FROMdeptWHEREdeptno=&tt;&tt设置tt为变量
SQL>list简写L查看历史语句
SQL>change/n/m简写c，将缓存区中语句中的n变成m
SQL>/执行缓存区中的语句
SQL>delete4简写DEL删除缓存区中第几行
SQL>a语句在缓冲区中添加命令
SQL>save路径保存缓冲区中的语句
SQL>@路径执行某一个脚本
SQL>get路径查看某一个脚本
SQL>edit使用外部编辑器来编辑缓存区中的命令
SQL>col标签命令
SQL>DESC表名查看表结构
SQL>？
命令查看命令用法
3.SQL语言基础
3.1语言分类
DDL语句（数据定义语言）DataDefineLanguage，如CREATE,ALTER,DROP
特点:
1、建立和修改数据对象
2、建立和修改直接存入库中,直接生效
DML语句（数据操作语言）DataManipulateLanguage,如SELECT,INSERT,DELETE,UPDATE
特点:
1、对数据起作用的
2、这些语句的修改是在内存中发生的要想改动存入库中必须要commit语句
DCL语句（数据控制语句）DataControlLanguage，如grant授予权限，revoke撤销权限
3.2常用SQL命令
INSERT插入记录
INSERTINTO表名[（字段1,字段2…）]
[VALUES（值1,值2…）]
[SELECT字段1,字段2FROM表名e]
DELETE删除记录
DELETEFROM表名[WHERE{条件}]
UPDATE更新记录
UPDATE表名SET字段1=’值’[,字段2=’值’,…][WHERE{条件}]
DROP删除表
DROPTABLE表名
TRUNCATE删除所有表数据
TRUNCATETABLE表名
SELECT简单查询
SELECT[DISTINCT]字段1，字段2…FROM表名1,表名2,…
[WHERE{条件}]
[ORDERBY字段[ASC/DESC]]
[GROUPBY字段1,字段2…[HAVING条件]]
CREATE创建
CREATE
[TABLE新表名（字段1类型1,字段2类型2…）[AS{SELECT查询}]]
[VIEW新视图名AS{SELECT查询}]
[INDEX新索引名ON表名（字段1，字段2…）]
ALTERTABLE修改表
ALTERTABLE表名
[ADD字段类型][DROP字段][MODIFY字段类型]
3.3常用函数
字符函数
Length（）计算字符串长度,
Ltrim（）截取字符串最左边的空格,
Rtrim（）截取字符串最右边边的空格,
Substr（‘字符串’,起始位,截取数量）截取字符串
日期：
Sysdate系统时间
current_date
next_day
转换：
To_char,to_date,to_number
聚集函数：
Sum,avg,max,min,count
其他：
User用户信息
Decode（字段,值,真,假）字段值为真或假时执行条件
Nvl2（字段,值1,值2）字段为空显示值1,否则显示值2
4.视图、同义词、序列
4.1视图
定义：
视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。
视图基于的表称为基表。
优点：
1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
2.用户通过简单的查询可以从复杂查询中得到结果。
3.维护数据的独立性，试图可从多个表检索数据。
4.对于相同的数据可产生不同的视图。
创建：
CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview_name
[（alias[,alias]...）]
ASsubquery
[WITHCHECKOPTION[CONSTRAINTconstraint]]
[WITHREADONLY]
其中：
ORREPLACE：
若所创建的试图已经存在，ORACLE自动重建该视图；
FORCE：
不管基表是否存在ORACLE都会自动创建该视图；
NOFORCE：
只有基表都存在ORACLE才会创建该视图：
alias：
为视图产生的列定义的别名；
subquery：
一条完整的SELECT语句，可以在该语句中定义别名；
WITHCHECK OPTION：
插入或修改的数据行必须满足视图定义的约束；
WITHREADONLY：
该视图上不能进行任何DML操作。
视图的定义原则：
1.视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；
2.在没有WITHCHECKOPTION和READONLY的情况下，查询中不能使用ORDERBY子句；
3.如果没有为CHECKOPTION约束命名，系统会自动为之命名，形式为SYS_Cn;
4.ORREPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。
视图上DML操作应遵循的原则：
1.简单视图可以执行DML操作；
2.在视图包含GROUP函数，GROUPBY子句，DISTINCT关键字时不能删除数据行；
3.在视图不出现下列情况时可通过视图修改基表数据或插入数据：
a.视图中包含GROUP函数，GROUPBY子句，DISTINCT关键字；
b.使用表达式定义的列
c.ROWNUM伪列
d.基表中未在视图中选择的其他列定义为非空且无默认值
视图的删除：
DROPVIEW VIEW_NAME
删除视图的定义不影响基表中的数据。
只有视图所有者和具备DROPVIEW权限的用户可以删除视图。
视图被删除后，基于被删除视图的其他视图或应用将无效。
系统视图数据字典表
Dba_viewsDBA视图，All_views所有视图，User_views当前用户视图
4.2同义词
定义：
是指向其它数据库表的数据库指针。
类型：
私有（private）私有的同义词是在指定的模式中创建并且只创建者使用的模式访问
公共（public）公共同义词是由public指定的模式访问所有数据库模式（用户）都可以访问它
创建：
CREATE[PUBLIC]SYNONYMtable_nameFORuser.table_name
其中：
PUBLIC创建公共同义词
删除:
DROP[PUBLIC]SYNONYMtable_name
其中：
PUBLIC创建公共同义词
系统同义词数据字典表
Dba_synonyms公共同义词,all_synonyms所有视图,user_synonyms当前用户同义词
4.3序列
定义：
序列是用于产生唯一数码的数据库对象，序列创建时带有初始值，增量值，最大值等，最大可达38位整数。
格式:
createsequence<序列名称>
startwith<起始数>incrementby<增长量>
[maxvalue 值][minvalue 值]
[cycle当到达最大值的时候，将继续从头开始]
[Nocycle 一直累加，不循环][Cache]
应用：
NEXTVAL：
第一次返回的是初始值，往后递加
CURRVAL：
返回当前序列中
系统序列数据字典表
Dba_sequencesALL_sequencesuser_sequences
删除：
dropsequence序列名称
修改：
ALTERSEQUENCE<序列名称>
只有序列的所有者或者有ALTERANYSEQUENCE权限才能改动序列.可以alter除start至以外的所有sequence参数.如果想要改变start值，必须删除序列后重建序列。
5.安全管理
创建配置文件：
Create profile 文件名 limit
FAILED_LOGIN_ATTEMPTS：
指定锁定用户的登录失败次数
PASSWORD_LOCK_TIME：
指定用户被锁定天数
PASSWORD_LIFE_TIME：
指定口令可用天数
PASSWORD_REUSE_TIME：
指定在多长时间内口令不能重用
PASSWORD_REUSE_MAX：
指定在重用口令前口令需要改变的次数
SESSIONS_PER_USER：
限制用户并发会话个数
CPU_PER_SESSION：
限制会话所占用的CPU时间（百分之一秒）
CPU_PER_CALL：
限制每次调用所占用的CPU时间（百分之一秒）
CONNECT_TIME：
限制会话的总计连接时间（分钟）
IDLE_TIME：
限制会话的空闲时间（分钟））;
用户创建：
CREATEUSER用户名IDENTIFIEDBY密码
[DEFAULTTABLESPACE表空间名]
[TEMPORARYTABLESPACE临时表空间名]
[PROFILEprovile文件名]
[QUOTA限制空间大小ON表空间名]
删除用户
DORPUSER用户名[CASCADE]
其中CASCADE用户实体马上被删除，应再进入数据文件进行物理删除，当前连接的用户不得删除
用户修改：
ALTERUSER用户名[IDENTIFIEDBY密码]
[ACCOUNT[UNLOCK/LOCK]账户锁定及解锁]
创建角色：
CREATEROLE角色名
删除角色
DROPROLE角色名
撤销权限：
REVOKE{[角色][权限]}FROM{[用户名][角色]}
授权命令：
GRANT{[角色][权限]}TO{[用户名][角色]}
[WITHGRANTOPTION级联授权]
查询oracle权限
SELECTDISTINCTprivilegeFROMdba_sys_privsORDERBYprivilege
查看oracle用户权限
1.oracle用户查看自己的权限和角色
select*fromuser_tab_privs;
select*fromuser_role_privs;
2.sys用户查看任一用户的权限和角色
select*fromdba_tab_privs;
select*fromdba_role_privs
升级会员 