`
darrenzhu
  • 浏览: 804487 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

Sybase常用命令,SQL语句

阅读更多
SQL online sample
http://sqlzoo.net/wiki/Main_Page

查询系统全局变量,日期,日期转换,日期加减
select @@ERROR
select OBJECT_ID('tableName/procedureName/...')
select getUTCDate()
select convert(datetime, '01/01/01')
select convert(char(26), getdate(), 109)
select convert(char(26), getUTCdate(), 109)
select dateadd(dd, -8, getUTCdate())

复制数据, copy table data
INSERT INTO targetTable SELECT * FROM sourceTable WHERE CLAUSE

INSERT INTO targetTable(column1, column2, ...) SELECT 'a' as column1, column2, ... FROM sourceTable WHERE CLAUSE


复制copy表结构table schema,从查询结构创建表
SELECT * into tbl_allocation_party_history from  tbl_allocation_party where 1=2

不是用insert into ... select 语句


判断表table,存储过程stored procedure是否存在
IF OBJECT_ID('dbo.objName') is not null
    BEGIN
        drop table/procedure dbo.objName
        PRINT '<<<Drop table/procedure dbo.objName>>>'
    END


判断索引是否存在, check whether the index exists?
IF EXISTS (select 1 from sysindexes where id = OBJECT_ID('tableName') and name = 'indexName')
    BEGIN
        drop index tableName.indexName
        PRINT '<<<drop index tableName.indexName>>>'
    END
ELSE
    PRINT '<<<skip drop index tableName.indexName due to not existed>>>'


或者

IF EXISTS (select 1 FROM sysindexes s, sysobjects so WHERE s.name = 'my_index' AND s.id = so.id AND so.type = 'U' AND so.name = 'my_table') BEGIN
  DROP INDEX my_table.my_index
END

重命名字段的名字
sp_rename 'table1.column1', 'new_column_name'

添加字段
ALTER TABLE table1 ADD new_column varchar(10) NULL

删除字段
alter table table1 drop column_to_be_deleted

修改字段的数据类型,是否为空
alter table table1 modify column_name date null

Insert "Space","new line","tab" characters into a field as value
There are times when you need to insert some special characters such as "newline","tab" etc as values into a field. Usually every one tries with '\n' or '\t', in vain. we can do this by using the ASCII value of the character. The steps are very simple. if you want to insert a special character find the ASCII value of that character like '9 for tab', '12 for new line' etc. you can find the lists of characters with its ASCII values at https://awesomesql.wordpress.com/2009/08/10/ascii-character-set-table. Once this is done, concatenate your string with the character of that particular ASCII value as

'my string and'+ char(12)+'someting'


this will be

my string and

something



Check table Primary Key/Reference Key/Constraints
sp_helpconstraint tableName


-How to determine a Sybase servers character set and sort order
sp_helpsort

-how to check permissions on a table in sybase
sp_helprotect proc_name

-单引号(')转义
sybase里面用来转义的符号不是"\",而是单引号,所以
So how do you escape quotes in Sybase? In fact, in Sybase SQL the single quote acts as the escape character.
See below for an example UPDATE statement in both “languages”:
MySQL
UPDATE Animals SET NAME = 'Dog\'s friends' WHERE uid = 12
Sybase
UPDATE Animals SET NAME = 'Dog''s friends' WHERE uid = 12

另外"(" ")" ","是不需要转义的


--Get all tables for the specified column
SQL: select b.name as tablename, a.name as columnname
from syscolumns a join sysobjects b on (a.id = b.id)
where b.type='U' and a.name = 'column'


--Get all columns for the specified table
SQL: select b.name as tablename, a.name as columnname
from syscolumns a join sysobjects b on (a.id = b.id)
where b.type='U' and b.name = 'table'


获取Sybase数据库版本信息
There are two ways to know the about Sybase version,

1) Using this System procedure to get the information about Sybase version
SQL: sp_version

2) Using this command to get Sybase version
SQL: select @@version



who am I?
SQL: SELECT user, user_id()

When you are in a database that you own the answer is dbo (database owner). If you USE someone else's database then you get a user id. (the machine being used to connect to MySQL)


--To get list of all users and alias in database
select 'user:' 'Type', t1.suid, t1.name 'dbuser', t2.name 
from sysusers t1, master..syslogins t2 
where t2.suid=*t1.suid 
union 
select 'alias:' 'Type', t1.suid, ' ' 'dbuser', t2.name 
from sysalternates t1, master..syslogins t2 
where t2.suid=*t1.suid



--To get list of all users in database
SQL: use database
     select name from sysusers where udi<16384
or
SQL: select name from databasename.dbo.sysusers  where udi<16384

--suid is 'server user id ' is reference to sybase login which recorded in master.dbo.syslogins.
--16384 is a magic id number from which group names are recorded in sysusers table. (all with id> 16384 are group names not users)

--There is also what called 'aliases'. To see all sybase login that are aliased to another login in current database:
SQL: select suser_name(suid),suser_name(altsuid) from sysalternates
--Which mean whan anyone named in first column try to access database it will be treated as login from second column.
use master
--Next query will give you sybase database user name and corresponding server login (may be different )
SQL: select l.name,d.name from master.dbo.syslogins l, databasename.dbo.sysusers d where l.suid=d.suid


获取table的lock scheme
1) select lockscheme('tableName')
2) SELECT sysobj.* FROM (
select name, lockscheme(name) lockscheme, type from sysobjects
) as sysobj
where sysobj.type='U' and sysobj.lockscheme != 'datarows'

3) select convert(varchar(30),name) as OBJECT_NAME,
       "LOCKING_SCHEME" = case (sysstat2 & 57344)
         when 8192 then "all pages"
         when 16384 then "datapages"
         when 32768 then "datarows"
       end
from sysobjects where type in ("U") order by name

SQL for changing the locking scheme for a table
alter table table_name lock {allpages | datapages | datarows}

-Default value
Actually the default value of a column is a constraint for the table, you can use sp_helpconstraint TABLENAME to check.
Firstly we should use create default and drop default to achieve our goal.
To alter a default you need to use replace rather than modify:
alter table downloads replace is_completed default 0
If you need to change the data type or the null/not null then you should use
alter table t modify c

-Identity gap
Managing identity gaps in tables
The IDENTITY column contains a unique ID number, generated by Adaptive Server, for each row in a table. Because of the way the server generates ID numbers by default, you may occasionally have large gaps in the ID numbers. The identity_gap parameter gives you control over ID numbers, and potential gaps in them, for a specific table.

By default, Adaptive Server allocates a block of ID numbers in memory instead of writing each ID number to disk as it is needed, which requires more processing time. The server writes the highest number of each block to the table’s object allocation map (OAM) page. This number is used as the starting point for the next block after the currently allocated block of numbers is used or “burned.” The other numbers of the block are held in memory, but are not saved to disk. Numbers are considered burned when they are allocated to memory, then deleted from memory either because they were assigned to a row, or because they were erased from memory due to some abnormal occurrence such as a system failure.

Allocating a block of ID numbers improves performance by reducing contention for the table. However, if the server fails or is shut down with no wait before all the ID numbers are assigned, the unused numbers are burned. When the server is running again, it starts numbering with the next block of numbers based on the highest number of the previous block that the server wrote to disk. Depending on how many allocated numbers were assigned to rows before the failure, you may have a large gap in the ID numbers.

Identity gaps can also result from dumping and loading an active database. When dumping, database objects are saved to the OAM page. If an object is currently being used, the maximum used identity value is not in the OAM page and, therefore, is not dumped.

Setting the table-specific identity gap
Set the table-specific identity gap when you create a table using either create table or select into.

This statement creates a table named mytable with an identity column:

create table mytable (IdNum numeric(12,0) identity)
with identity_gap = 10
The identity gap is set to 10, which means ID numbers are allocated in memory in blocks of ten. If the server fails or is shut down with no wait, the maximum gap between the last ID number assigned to a row and the next ID number assigned to a row is ten numbers.

If you are creating a table in a select into statement from a table that has a specific identity gap setting, the new table does not inherit the identity gap setting from the parent table. Instead, the new table uses the identity burning set factor setting. To give the new table a specific identity_gap setting, specify the identity gap in the select into statement. You can give the new table an identity gap that is the same as or different from the parent table.

For example, to create a new table (newtable) from the existing table (mytable) with an identity gap:

select IdNum into newtable
with identity_gap = 20
from mytable

分享到:
评论

相关推荐

    sybase T-SQL命令

    - 存储过程是一组预编译的T-SQL语句,可以作为独立的单元执行,提高性能并简化代码管理。 - `CREATE PROCEDURE`:定义存储过程。 - `EXEC` 或 `EXECUTE`:执行存储过程。 6. **变量与控制流**: - `DECLARE @...

    Sybase常用命令

    isql是Sybase数据库的一个命令行工具,用于连接到数据库执行SQL语句。登录到isql的方法如下: ```bash isql -Uusername -Ppassword -Sservername ``` 其中`username`和`password`分别代表登录用户名和密码,而`...

    常用的SQL语句.pdf

    【SQL语句基础】 SQL(Structured Query Language)是用于管理和处理关系数据库的标准语言,它包含了数据查询、数据更新、数据插入和数据删除等操作。SQL并非一种完整的编程语言,而是一种特殊的、高度结构化的查询...

    sybase导出建表SQL工具

    标题中的“sybase导出建表SQL工具”指的是一个专门针对Sybase数据库系统的应用程序,它的主要功能是帮助用户方便地导出数据库中的表结构定义,即建表SQL语句。在数据库管理中,建表SQL语句是创建数据库表结构的关键...

    sybase库中导出全部表的oracle、mysql和sybase的建表语句

    它可能包含了连接数据库、执行SQL、解析结果和生成SQL语句的逻辑。 接下来是`oracle`,它是另一种广泛使用的RDBMS,具有不同的语法和特性。将Sybase的建表语句转换为Oracle格式,需要考虑Oracle特定的语法差异,如...

    SQL语句教程 写法大全

    同时,本教程还涵盖了 SQL 高级知识,如 Top 语句、Like 语句、通配符、In 语句、Between 语句、Aliases 语句、Join 语句、Inner Join 语句、Left Join 语句、Right Join 语句、Full Join 语句、Union 语句、Select ...

    sybase常用的命令集合

    在Sybase数据库管理系统中,掌握常用命令对于日常管理和优化至关重要。以下是一些关键的Sybase命令,涵盖了配置、权限管理及TSQL使用等方面。 一、配置命令 1. 检查CPU使用情况:`sp_sysmon`是系统监控存储过程,...

    sybase t_sql 手册

    6. **存储过程和触发器**: 存储过程是一组预编译的T-SQL语句,可以提高性能,降低网络流量。触发器则是在特定数据操作(如INSERT, UPDATE, DELETE)前或后自动执行的代码。 7. **游标**: 提供了一种逐行处理结果集...

    常用的SQL语句.docx

    SQL语句的执行流程大致分为以下几个步骤:首先,客户端提交SQL语句给服务器;接着,服务器会对SQL语句进行整体的语法分析,检查语句是否符合SQL的语法规则;然后,服务器会对SQL进行优化,确定最佳的执行计划;最后...

    Sybase数据库的SQL语法资料

    存储过程是一组预先编译的SQL语句,可以接受参数并返回结果。 ```sql CREATE PROCEDURE CalculateBonus @EmployeeID INT, @Bonus DECIMAL(10,2) OUTPUT AS BEGIN UPDATE Employees SET Salary = Salary + @Bonus ...

    Sybase 常用语句

    以下是一些常用的Sybase SQL语句及其用途: 1. **查看数据库版本**:使用`select @@version`可以获取当前数据库系统的版本信息,这对于确认系统兼容性和确定支持的功能很有帮助。 2. **查看操作系统参数**:`exec ...

    sybase常用命令

    - `isql`:交互式SQL命令行工具,用于连接到Sybase数据库,执行SQL语句并显示结果。 - `-U username -P password -S servername`:isql命令的参数,分别代表用户名、密码和服务器名。 3. **查询数据库信息** - `...

    SQL语句生成及分析器

    无论多么复杂的语句,都能分析出来(包括SQL各子句中嵌套的SQL语句) 5、数据库视图定义和重建 6、支持将SQL查询语句,替换为插入(Insert into)和更新(Update)语句 7、附属工具内嵌入Delphi IDE(支持Delphi 5和...

    sybase与microsoft—sql的语法区别

    在数据库管理领域,Sybase和Microsoft SQL Server是两种广泛应用的关系型数据库管理系统。...在实际操作中,应当根据具体需求和数据库系统的特性来编写SQL语句,避免直接将一种系统的语法应用到另一种系统上。

    Transact-SQL用户指南Sybase

    1. **数据定义语言(DDL)和数据操纵语言(DML)**:内容中提到了各种SQL语句,如创建表、修改表结构、插入数据、更新数据和删除数据等操作。这些是Sybase Transact-SQL中用于数据定义和操纵的核心组成部分。 2. **...

    IQ_16 SQL 语句应用

    ### IQ 16 SQL 语句应用:ALTER TABLE 基本语法 在数据库管理中,经常需要对现有的表结构进行修改以适应业务需求的变化。SQL 提供了 `ALTER TABLE` 语句来实现这一功能。本文将详细介绍 `ALTER TABLE` 语句的基本...

    Mysql、oracle、Sybase数据库两列合并成一列的sql语句

    本文将详细介绍如何在MySQL、Oracle以及Sybase三种主流数据库中使用SQL语句来完成两列数据的合并操作。 ### 1. MySQL中的两列合并 在MySQL中,可以使用`CONCAT()`函数或者连接运算符`+`来合并两个字段。但是,需要...

    sybase数据库命令指南

    - **SELECT**:用于检索数据库中的数据,是最常用的SQL语句。 - **WHERE**:与SELECT结合使用,用于筛选特定条件下的数据。 - **JOIN**:用于从两个或多个表中获取数据。 #### 权限管理命令 - **GRANT**:授予用户...

    sybase ASE sql expert

    - 探索并使用SQL Expert的各种功能,例如在“查询分析器”中输入SQL语句进行分析,或者在“性能监视器”中查看数据库状态。 - 利用“优化顾问”来优化查询性能,根据建议进行调整。 - 定期生成性能报告,以便持续...

    PDM转sql语句

    标题中的“PDM转sql语句”指的是将PowerDesigner Model(PDM)转换为SQL语句,以便在MySQL数据库中使用。PDM是Sybase PowerDesigner的一个组件,它是一种强大的数据建模工具,用于设计和管理数据库的逻辑结构。...

Global site tag (gtag.js) - Google Analytics