MySQL与Sybase不同点
- MySQL没有if exists() ,Sybase有if exists()
- MySQL中无select into语句,sybase中可用select into
- MySQL查询其他表数据并插入到新表:create table tablename (select * from table2)
- sybase查询其他表数据插入到新表:select column1,colum2 ... into new_table
- MYSQL创建临时表:create temporary table '表名'
- sybase创建临时表:create table #tablename (一般创建临时表是在脚本中进行)
- MySQL种查询表信息:
- 查询表列信息:select * from columns where table_name =’表名’
- 查询库中表信息:select table_name from information_schema.tables where table_schema='tablename'
-
information_schema 是MySQL系统自带的数据库,提供了对数据库元数据的访问
information_schema.tables 指数据库中的表(information_schema.columns 指列)
table_schema 指数据库的名称
table_type 指是表的类型(base table 指基本表,不包含系统表)
table_name 指具体的表名
如果本身是在tablename 这个库里新建的查询,可以去掉 table_schema='tablename ' 这一句
select table_name from information_schema.tables
- Sybase中查询表信息
- select * from sysobjects
- Sybase中判断库中是否有某张表,如果有则删除:
- 方法一:IF EXISTS (SELECT * FROM sysobjects WHERE name = "TableName" AND type = "U")
BEGIN
DROP table TableName
END - 方法二:IF OBJECT_ID('dbo.TableName') IS NOT NULL
BEGIN
DROP TABLE dbo.TableName
END
- 方法一:IF EXISTS (SELECT * FROM sysobjects WHERE name = "TableName" AND type = "U")
- mysql判断库中是否有某张表,有则删除:
- drop table if exists tablename
- 删除字段语句:
- MySQL:alter table tablename drop column column1,drop column column2....
- sybase: alter table tablename drop column1,column2...
- delete from 语句
- MySQL :delete t1,t2 from t1 join t2 on t1.id =t2.id and t1.name ='xxx'(会级联删除)
- sybase : delete t1 from t1,t2 where t1.id = t2.id and t1.name='xxxx' (只删除t1表中数据)
- 如果MySQL中也只删除t1表中数据而不删除t2数据,则需要使用子查询,
- delete from t1 where t1.id in (select t1.id from t1 join t2 on t1.id = t2.id and t1.name = 'xxxx') 这种方式会有问题,只能使用在t1表的id是唯一主键的时候,如果t1表是联合主键,或者id字段不是t1表中的唯一字段,则可能出现误删的可能。