初学SQL Server数据库的一些常用操作总结.docx

《初学SQL Server数据库的一些常用操作总结.docx》由会员分享，可在线阅读，更多相关《初学SQL Server数据库的一些常用操作总结.docx（14页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、初学SQL Server数据库的一些常用操作总结对于初学者来说，SQL Server数据库的学习似乎有一定的难度。本文我们主要对SQL Server数据库的一些常用操作进行了总结，希望能够对初学者有所帮助。1.增加字段1. altertabledocdsp 2. adddspcodechar(200)2.删除字段1. ALTERTABLEtable_NAMEDROPCOLUMNcolumn_NAME3.修改字段类型1. ALTERTABLEtable_name 2. ALTERCOLUMNcolumn_namenew_data_type4.sp_rename 改名更改当前数据库中用户创建对象（

2、如表、列或用户定义数据类型）的名称。语法：1. sp_renameobjname=object_name, 2. newname=new_name 3. ,objtype=object_type如：1. EXECsp_rename 2. newname,PartStock5.sp_help 显示表的一些基本情况1. sp_helpobject_name 2. 如： 3. EXECsp_help 4. PartStock6.判断某一表PartStock中字段PartVelocity是否存在1. ifexists(select*fromsyscolumnswhereid=object_id(Par

3、tStock)andname=PartVelocity) 2. printPartVelocityexists 3. elseprintPartVelocitynotexists另法：判断表的存在性：1. selectcount(*)fromsysobjectswheretype=Uandname=你的表名判断字段的存在性：1. selectcount(*)fromsyscolumns 2. whereid3. =(selectidfromsysobjectswheretype=Uandname=你的表名) 4. andname=你要判断的字段名7.随机读取若干条记录Access语法：SELE

4、CT top 10 * From 表名 ORDER BY Rnd(id)Sql server：select top n * from 表名 order by newid()mysql ：select * From 表名 Order By rand() Limit n8.说明：日程安排提前五分钟提醒select * from 日程安排 where datediff(minute,f开始时间,getdate()59.前10条记录select top 10 * form table1 where 范围10.包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出

5、一个结果表(select a from tableA ) except (select a from tableB) except (select a from tableC)11.说明：随机取出10条数据select top 10 * from tablename order by newid()12.列出数据库里所有的表名select name from sysobjects where type=U13.列出表里的所有的字段名select name from syscolumns where id=object_id(TableName)14.说明：列示type、vender、pcs字段

6、，以type字段排列，case可以方便地实现多重选择，类似select 中的case。1. selecttype,sum(casevenderwhenAthenpcselse0end),sum(casevenderwhenCthenpcselse0end), 2. sum(casevenderwhenBthenpcselse0end)FROMtablenamegroupbytype15.说明：初始化表table1TRUNCATE TABLE table116.说明：几个高级查询运算词A： UNION 运算符UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表

7、中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。B： EXCEPT 运算符EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。C： INTERSECT 运算符INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使

8、用时 (INTERSECT ALL)，不消除重复行。注：使用运算词的几个查询结果行必须是一致的。17.说明：在线视图查询(表名1：a )select * from (SELECT a,b,c FROM a) T where t.a 1;18.说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括1. select*fromtable1wheretimebetweentime1andtime2 2. selecta,b,c,fromtable1whereanotbetween数值1and数值219.说明：in 的使用方法select * from

9、table1 where a not in (值1,值2,值4,值6)20.说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )21.说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)法一：select * into b from a where 11法二：select top 0 * into b from a22.说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用

10、)insert into b(a, b, c) select d,e,f from b;23.说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)insert into b(a, b, c) select d,e,f from b in 具体数据库 where 条件例子：.from b in &Server.MapPath(.)&/data.mdb & where.24.创建数据库CREATE DATABASE database-name25.说明：删除数据库drop database dbname26.说明：备份sql server1. 创建备份数据的device 2.

11、USEmaster 3. EXECsp_addumpdevicedisk,testBack,c:/mssql7backup/MyNwind_1.dat 4. 开始备份 5. BACKUPDATABASEpubsTOtestBack27.说明：创建新表create table tabname(col1 type1 not null primary key,col2 type2 not null,.)根据已有的表创建新表：A：create table tab_new like tab_old (使用旧表创建新表)B：create table tab_new as select col1,col2

12、from tab_old definition only28.说明：删除新表：drop table tabname29.说明：增加一个列：Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。30.说明：添加主键：Alter table tabname add primary key(col)说明：删除主键：Alter table tabname drop primary key(col)31.说明：创建索引：create unique index idxname o

13、n tabname(col.)删除索引：drop index idxname注：索引是不可更改的，想更改必须删除重新建。32.说明：创建视图：create view viewname as select statement删除视图：drop view viewname33.说明：几个简单的基本的sql语句选择：select * from table1 where 范围插入：insert into table1(field1,field2) values(value1,value2)删除：delete from table1 where 范围更新：update table1 set field1

14、=value1 where 范围查找：select * from table1 where field1 like %value1% -like的语法很精妙，查资料!排序：select * from table1 order by field1,field2 desc总数：select count * as totalcount from table1求和：select sum(field1) as sumvalue from table1平均：select avg(field1) as avgvalue from table1最大：select max(field1) as maxvalue

15、 from table1最小：select min(field1) as minvalue from table1以上就是SQL Server数据库的一些常用的操作的全部内容，本文我们就介绍到这里了，希望本次的介绍能够对您有所帮助。2.3 SQLPlus 常用命令SQL help index 查看所有可用的命令SQL SET SQLBLANKLINES ON 支持空格行SQL SELECT * FROM dept WHERE deptno=&tt; &tt设置tt为变量SQL list 简写L查看历史语句SQL change/n/m 简写c，将缓存区中语句中的n变成mSQL / 执行缓存区中的

16、语句SQL delete 4 简写DEL删除缓存区中第几行SQL a 语句 在缓冲区中添加命令SQL save 路径 保存缓冲区中的语句SQL 路径 执行某一个脚本SQL get路径 查看某一个脚本SQL edit 使用外部编辑器来编辑缓存区中的命令SQL col 标签命令SQL DESC 表名 查看表结构SQL？ 命令 查看命令用法3.SQL语言基础3.1语言分类DDL语句(数据定义语言) Data Define Language，如CREATE,ALTER,DROP特点:1、建立和修改数据对象2、建立和修改直接存入库中,直接生效DML语句(数据操作语言) Data Manipulate L

17、anguage,如SELECT,INSERT,DELETE,UPDATE特点:1、对数据起作用的2、这些语句的修改是在内存中发生的要想改动存入库中必须要commit语句DCL语句(数据控制语句) Data Control Language，如grant 授予权限，revoke 撤销权限3.2常用SQL命令INSERT 插入记录INSERT INTO 表名(字段1,字段2)VALUES (值1,值2 )SELECT字段1, 字段2 FROM表名eDELETE 删除记录DELETE FROM 表名 WHERE 条件UPDATE 更新记录UPDATE表名 SET 字段1=值,字段2=值, WHERE

18、 条件DROP 删除表DROP TABLE 表名TRUNCATE 删除所有表数据TRUNCATE TABLE 表名SELECT 简单查询SELECT DISTINCT 字段1，字段2 FROM 表名1,表名2,WHERE 条件ORDER BY 字段ASC/DESCGROUP BY 字段1,字段2HAVING 条件CREATE 创建CREATETABLE 新表名 (字段1 类型1, 字段2 类型2) AS SELECT 查询 VIEW 新视图名 AS SELECT 查询INDEX 新索引名 ON 表名(字段1，字段2)ALTER TABLE 修改表ALTER TABLE 表名ADD 字段 类型

19、DROP 字段 MODIFY 字段 类型3.3常用函数字符函数Length()计算字符串长度,Ltrim()截取字符串最左边的空格,Rtrim()截取字符串最右边边的空格,Substr(字符串,起始位,截取数量) 截取字符串日期：Sysdate 系统时间current_datenext_day转换：To_char,to_date,to_number聚集函数：Sum,avg,max,min,count其他：User 用户信息Decode(字段,值,真,假) 字段值为真或假时执行条件Nvl2(字段,值1,值2)字段为空显示值1,否则显示值24.视图、同义词、序列4.1视图定义：视图是基于一个表或多

20、个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。优点：1. 对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。2. 用户通过简单的查询可以从复杂查询中得到结果。3. 维护数据的独立性，试图可从多个表检索数据。4. 对于相同的数据可产生不同的视图。创建：CREATE OR REPLACE FORCE|NOFORCE VIEW view_name(alias, alias.)AS subqueryWITH CHECK OPTION CONSTRAINT constraintWITH READ ONLY其中：OR REPLACE：若所创建的

21、试图已经存在，ORACLE自动重建该视图；FORCE：不管基表是否存在ORACLE都会自动创建该视图；NOFORCE：只有基表都存在ORACLE才会创建该视图：alias：为视图产生的列定义的别名；subquery：一条完整的SELECT语句，可以在该语句中定义别名；WITH CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；WITH READ ONLY ：该视图上不能进行任何DML操作。视图的定义原则：1. 视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；2. 在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用

22、ORDER BY 子句；3. 如果没有为CHECK OPTION约束命名，系统会自动为之命名，形式为SYS_Cn;4. OR REPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。视图上 DML操作应遵循的原则：1. 简单视图可以执行DML操作；2. 在视图包含GROUP 函数，GROUP BY子句，DISTINCT关键字时不能删除数据行；3. 在视图不出现下列情况时可通过视图修改基表数据或插入数据：a.视图中包含GROUP 函数，GROUP BY子句，DISTINCT关键字；b.使用表达式定义的列c.ROWNUM伪列d.基表中未在视图中选择的其他列定义为非空且无默认值视

23、图的删除：DROP VIEW VIEW_NAME删除视图的定义不影响基表中的数据。只有视图所有者和具备DROP VIEW权限的用户可以删除视图。视图被删除后，基于被删除视图的其他视图或应用将无效。系统视图数据字典表Dba_views DBA视图，All_views 所有视图，User_views当前用户视图4.2同义词定义：是指向其它数据库表的数据库指针。类型：私有(private)私有的同义词是在指定的模式中创建并且只创建者使用的模式访问公共(public)公共同义词是由public 指定的模式访问所有数据库模式(用户)都可以访问它创建：CREATE PUBLIC SYNONYM table

24、_name FOR user.table_name其中：PUBLIC 创建公共同义词删除:DROP PUBLIC SYNONYM table_name其中：PUBLIC 创建公共同义词系统同义词数据字典表Dba_synonyms 公共同义词, all_synonyms 所有视图,user_synonyms 当前用户同义词4.3序列定义：序列是用于产生唯一数码的数据库对象，序列创建时带有 初始值，增量值，最大值等，最大可达38 位整数。格式:create sequence start with increment by maxvalue 值 minvalue 值cycle 当到达最大值的时候，将

25、继续从头开始Nocycle 一直累加，不循环 Cache 应用：NEXTVAL：第一次返回的是初始值，往后递加CURRVAL：返回当前序列中系统序列数据字典表Dba_sequences ALL_sequences user_sequences删除：drop sequence 序列名称修改：ALTER SEQUENCE 只有序列的所有者或者有ALTER ANY SEQUENCE 权限才能改动序列. 可以alter除start至以外的所有sequence参数.如果想要改变start值，必须删除序列后重建序列。5.安全管理创建配置文件：Create profile 文件名 limitFAILED_L

26、OGIN_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：限制会话的空闲时间（分

27、钟）);用户创建：CREATE USER 用户名 IDENTIFIED BY 密码DEFAULT TABLESPACE 表空间名TEMPORARY TABLESPACE 临时表空间名PROFILE provile文件名QUOTA 限制空间大小 ON 表空间名删除用户DORP USER 用户名 CASCADE其中CASCADE 用户实体马上被删除，应再进入数据文件进行物理删除，当前连接的用户不得删除用户修改：ALTER USER 用户名 IDENTIFIED BY 密码ACCOUNT UNLOCK/LOCK账户锁定及解锁创建角色：CREATE ROLE 角色名删除角色DROP ROLE 角色名撤

28、销权限：REVOKE 角色权限 FROM 用户名角色授权命令：GRANT 角色权限 TO 用户名角色WITH GRANT OPTION 级联授权查询oracle权限SELECT DISTINCT privilege FROM dba_sys_privs ORDER BY privilege查看oracle用户权限 1. oracle用户查看自己的权限和角色select * from user_tab_privs; select * from user_role_privs;2. sys用户查看任一用户的权限和角色select * from dba_tab_privs; select * from dba_role_privs