Sybase ASE常用命令

启动数据库
startsever -f /opt/sap/ASE-16_0/install/RUN_<实例名>
界面配置sybase ASE
asecfg

常用命令

一、基本命令及查看

#登录数据库
isql -U  sa -p -S LOCALHOST  

#查看所有数据库
sp_helpdb  
go

#查看数据库结构
sp_helpdb test
go

#选择一个数据库
use test  
go

#查看库当中的表
sp_help 
go

#查看表的结构
sp_help titles
go

 #查看启动了什么服务 
select srvname from sysservers 
go 

#关闭备份服务 
shutdown SYB_BACKUP 
go 

#关闭 
shutdown
 go

#查看用户进程
sp_who
go

#查看数据库锁状况
sp_lock
go

#查看设备信息
sp_helpdevice
go

#查看表占用空间大小
sp_spaceused
go

#查看版本号
select @@version
go

二、DDL与DML

#创建数据库 
create database test go

#创建表
create table titles  
(title_id tid not null,
title varchar(80) not null,
type char(12) not null,
pub_id char(4) null,
price money null,
advance money null,
total_sales int null,
notes varchar(200) null,
pubdate datetime not null,
contract bit not null)
go

#查看表结构
sp_help titles  
go

#插入操作和查询操作也符合SQL

三、用户管理

#创建登录用户,参数依次为:用户名,密码,默认数据库
sp_addlogin jipeng,jipeng,test
go

#删除登录用户
sp_droplogin jipeng
go

#创建数据库用户
sp_adduser jipeng
go

#删除数据库用户
sp_dropuser jipeng
go

四、常用系统表

sysdatabases        –记录所有数据库基本信息
sysusages        –数据库空间分配情况
sysdevices        –数据库设备信息
syslogins        –数据库服务器登录信息
sysusers        –数据库用户信息
sysobjects        –数据库对象表(U 用户表,P 存储过程)
sysprocesses        –进程表

五、interfaces

sybase的interfaces中存储了sybase的服务名、IP地址与端口,可以通过dscp命令修改。

enlightened在创建数据库时,可能出现以下错误:
CREATE DATABASE failed because of incorrectly specified or unavailable space.
disk resize name='master', size='20m'
create database 表名 on master = '10M'

参考:http://stackoverflow.com/questions/28022372/sybase-cant-create-database-why-not

命令大全

命令 描述
alter database Increases the amount of space allocated to a database.
alter role Defines mutually exclusive relationships between roles and adds, drops, and changes passwords for roles.
alter table Adds new columns; adds, changes, or drops constraints, changes constraints; partitions or unpartitions an existing table.
begin…end Encloses a series of SQL statements so that control-of-flow language, such as if…else, can affect the performance of the whole group.
begin transaction Marks the starting point of a user-defined transaction.
break Causes an exit from a while loop. break is often activated by an if test.
case Allows SQL expressions to be written for conditional values. case expressions can be used anywhere a value expression can be used.
checkpoint Writes all dirty pages (pages that have been updated since they were last written) to the database device.
close Deactivates a cursor.
coalesce Allows SQL expressions to be written for conditional values. coalesce expressions can be used anywhere a value expression can be used; alternative for a case expression.
commit Marks the ending point of a user-defined transaction.
compute clause Generates summary values that appear as additional rows in the query results.
connect to…disconnect Specifies the server to which a passthrough connection is required.
continue Causes the while loop to restart. continue is often activated by an if test.
create database Creates a new database.
create default Specifies a value to insert in a column (or in all columns of a user-defined datatype) if no value is explicitly supplied at insert time.
create existing table Confirms that the current remote table information matches the information that is stored in column_list, and verifies the existence of the underlying object.
create function (SQLJ)  
create index Creates an index on one or more columns in a table.
create plan Creates an abstract query plan.
create procedure Creates a stored procedure that can take one or more user-supplied parameters.
create proxy_table Creates a proxy table without specifying a column list. Component Integration Services derives the column list from the metadata it obtains from the remote table.
create role Creates a user-defined role.
create rule Specifies the domain of acceptable values for a particular column or for any column of a user-defined datatype.
create schema Creates a new collection of tables, views and permissions for a database user.
create table Creates new tables and optional integrity constraints.
create trigger Creates a trigger, a type of stored procedure often used for enforcing integrity constraints. A trigger executes automatically when a user attempts a specified data modification statement on a specified table.
create view Creates a view, which is an alternative way of looking at the data in one or more tables.
dbcc Database Consistency Checker (dbcc) checks the logical and physical consistency of a database. Use dbcc regularly as a periodic check or if you suspect any damage.
deallocate cursor Makes a cursor inaccessible and releases all memory resources committed to that cursor.
declare Declares the name and type of local variables for a batch or procedure.
declare cursor Defines a cursor.
delete Removes rows from a table.
delete statistics Removes statistics from the sysstatistics system table.
disk init Makes a physical device or file usable by Adaptive Server.
disk mirror Creates a software mirror that immediately takes over when the primary device fails.
disk refit Rebuilds the master database’s sysusages and sysdatabases system tables from information contained in sysdevices. Use disk refit after disk reinit as part of the procedure to restore the master database.
disk reinit Rebuilds the master database’s sysdevices system table. Use disk reinit as part of the procedure to restore the master database.
disk remirror Reenables disk mirroring after it is stopped by failure of a mirrored device or temporarily disabled by the disk unmirror command.
disk resize Dynamically increase the size of database devices, rather than initializing a new device
disk unmirror Disables either the original device or its mirror, allowing hardware maintenance or the changing of a hardware device.
drop database Removes one or more databases from a Adaptive Server.
drop default Removes a user-defined default.
drop function (SQLJ)  
drop index Removes an index from a table in the current database.
drop procedure Removes user-defined stored procedures.
drop role Removes a user-defined role.
drop rule Removes a user-defined rule.
drop table Removes a table definition and all of its data, indexes, triggers, and permission specifications from the database.
drop trigger Removes a trigger.
drop view Removes one or more views from the current database.
dump database Makes a backup copy of the entire database, including the transaction log, in a form that can be read in with load database. Dumps and loads are performed through Backup Server.
dump transaction Makes a copy of a transaction log and removes the inactive portion.
execute Runs a system procedure, a user-defined stored procedure, or a dynamically constructed Transact-SQL command.
fetch Returns a row or a set of rows from a cursor result set.
goto label Branches to a user-defined label.
grant Assigns permissions to users or to user-defined roles.
grant dbcc Allows the System Administrator to grant access on certain dbcc commands.
group by and having clauses Used in select statements to divide a table into groups and to return only groups that match conditions in the having clause.
if…else Imposes conditions on the execution of a SQL statement.
insert You can use the insert command to create a new file directory. To do so, use only the filename, filetype and content columns. You specify “DIR” as the filetype, then filename is created as a directory.
kill Kills a process.
load database Loads a backup copy of a user database, including its transaction log.
load transaction Loads a backup copy of the transaction log.
lock table Explicitly locks a table within a transaction.
nullif Allows SQL expressions to be written for conditional values. nullif expressions can be used anywhere a value expression can be used; alternative for a case expression.
online database Marks a database available for public use after a normal load sequence and, if needed, upgrades a loaded database and transaction log dumps to the current version of Adaptive Server.
open Opens a cursor for processing.
order by clause Returns query results in the specified column(s) in sorted order.
prepare transaction Used by DB-Library™ in a two-phase commit application to see if a server is prepared to commit a transaction.
print Prints a user-defined message on the user’s screen.
quiesce database Suspends and resumes updates to a specified list of databases.
raiserror Prints a user-defined error message on the user’s screen and sets a system flag to record that an error condition has occurred.
readtext Reads text and image values, starting from a specified offset and reading a specified number of bytes or characters.
reconfigure The reconfigure command currently has no effect; it is included to allow existing scripts to run without modification. In previous releases, reconfigure was required after the sp_configure system procedure to implement new configuration parameter settings.
remove java Removes one or more Java-SQL classes, packages, or JARs from a database. Use when Java is enabled in the database.
reorg Reclaims unused space on pages, removes row forwarding, or rewrites all rows in the table to new pages, depending on the option used.
return Exits from a batch or procedure unconditionally, optionally providing a return status. Statements following return are not executed.
revoke Revokes permissions or roles from users or roles.
revoke dbcc Allows the System Administrator to revoke access on some dbcc commands.
rollback Rolls a user-defined transaction back to the last savepoint inside the transaction or to the beginning of the transaction.
rollback trigger Rolls back the work done in a trigger, including the update that caused the trigger to fire, and issues an optional raiserror statement.
save transaction Sets a savepoint within a transaction.
select Retrieves rows from database objects.
set Sets Adaptive Server query-processing options for the duration of the user’s work session. Can be used to set some options inside a trigger or stored procedure. Can also be used to activate or deactivate a role in the current session.
setuser Allows a Database Owner to impersonate another user.
shutdown Shuts down Adaptive Server or a Backup Server™. This command can be issued only by a System Administrator.
truncate table Removes all rows from a table.
union operator Returns a single result set that combines the results of two or more queries. Duplicate rows are eliminated from the result set unless the all keyword is specified.
update Changes data in existing rows, either by adding data or by modifying existing data;updates all statistics information for a given table; updates information about the number of pages in each partition for a partitioned table; updates information about the distribution of key values in specified indexes.
use Specifies the database with which you want to work.
waitfor Specifies a specific time, a time interval, or an event for the execution of a statement block, stored procedure, or transaction.
where clause Sets the search conditions in a select, insert, update, or delete statement.
while Sets a condition for the repeated execution of a statement or statement block. The statement(s) execute repeatedly, as long as the specified condition is true.
writetext Permits non-logged, interactive updating of an existing text or image column.

参考:
sybase常用命令 
编写一个小脚本来启动和关闭sybase ASE