Sybase ASE系列第四讲:常用SQL语句

sybase 常用sql语句

1.sybase 常用DDL

Sybase中DDL语句不能修改字段的数据类型，只能修改空与非空

1.删除列
ALTER TABLE table_name DELETE column_name
go

2.增加列
ALTER TABLE table_name ADD (column_name DATA_TYPE [NOT] NULL)
go

eg:
ALTER TABLE test_table ADD vend_no INT NULL
go

3.修改列的空与非空
ALTER TABLE table_name MODIFY column_name [NOT] NULL
go

4.修改列名
ALTER TABLE table_name RENAME old_column_name TO new_column_name
go

5.快速建立临时表
SELECT * INTO [#]table_name FROM .....
go

6.修改表名
ALTER TABLE old_table_name RENAME new_table_name
go

7.增加主键约束
ALTER TABLE tb_name ADD CONSTRAINT pk_name PRIMARY KEY(col_name,..)
go

8.删除主键约束
ALTER TABLE tb_name DROP CONSTRAINT pk_name
go

9.建立自增长字段,与Oracle的SEQUENCE类似
CREATE TABLE TMP_001 (RES_ID INTEGER IDENTITY NOT NULL)
go

10.添加表注释
COMMENT ON TABLE table_name IS '....'
go

11.创建索引
CREATE INDEX index_name ON table_name(column_name)
go

2.sybase 两表关联更新

Another way to give each employee a pay increase with the department bonus:

update Employees
set emp.salary = emp.salary + dept.bonus
from Employees as emp inner join Departments as dept ON emp.DepartmentID = dept.DepartmentID
go

3.sybase 批量插入

sybase ase中没有类似mysql的 insert into table values(xxxx) (xxxxx) 那样的语法。
sybase向一个表使用insert into 插入数据的时候，有2种方式：
一、insert into table select from另外的表 ---- 这种方式根据你写的select语句，可以一次插入多条记录。

二、insert into table values(......) --- 这种方式没插入一条记录，必须写一次 insert into values(...) 语句。
你可以在执行go之前提供多个insert into values(...)语句，这样可以实现你说的一次插入多条记录：比如:

insert into t values(1,'a')
insert into t values(2,'b')
insert into t values(3,'c')
go

4.sybase 常用SQL

-- 1.查询所有表名
SELECT name
FROM CIS..sysobjectswhere type = 'U'
ORDER BY name

-- 2.查询所有视图名
SELECT name
FROM CIS..sysobjects
WHERE type = 'V'
ORDER BY name

-- 3.查询所有存储过过程
SELECT name
FROM CIS..sysobjects
WHERE type = 'P'
ORDER BY name

-- 4.查询所有字段名
SELECT a.name
,b.name
FROM syscolumns a
LEFT JOIN systypes b ON a.usertype = b.usertype
INNER JOIN sysobjects d ON a.id = d.id
AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
WHERE d.name = 'order_eta_detail'

-- 5.临时表批量插入数据
CREATE TABLE #sku_disc (
 sku_no INT NOT NULL
 ,vend_no INT NULL
 ,vpl_no INT NULL
 )

INSERT INTO #sku_disc (sku_no,vend_no,vpl_no)(select 3,3,3) union all (select 2,2,2)
SELECT * FROM #sku_disc

-- 6.查询索引信息
sp_helpindex [table_name]

sybase 创建自增表

-- Identity 字段必须定义Gap,值视具体情况而定(10-10000),表操作越频繁,定义的Gap应越大
CREATE TABLE #t_details(
    id int identity
    ,sku_no int not NULL
    ,rebate_amt money not NULL
    ,begin_date datetime NULL
    ,active char(1) not NULL
    ,close_date datetime NULL
    ,close_id   int NULL
    ,entry_datetime datetime not NULL
    ,entry_id   int not NULL
) WITH identity_gap = 500

drop table #t_details

If the field length is too long, it will be automatically intercepted

CREATE TABLE #test_table (test_column VARCHAR(1) NULL)
GO

INSERT INTO #test_table
VALUES ('aa')
GO

SELECT *
FROM #test_table
GO