记录一下,很多网上找不到。
- 获取时间方法
- Sybase:getdate() 函数
- MySQL:now() 函数
- CASE WHEN使用方式
### sybase 写法 select t.a, case when t.a is null then 'xxxx' else t.b end as b from tempTable t #### MySQL写法,有两种使用方式 简单函数 CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END SELECT *, ( CASE sex WHEN 1 THEN '男' WHEN 2 THEN '女' END ) '性别' FROM `user` 搜索函数 CASE WHEN [expr] THEN [result1]…ELSE [default] END SELECT *, ( CASE WHEN score>=90 THEN '优秀' WHEN score>=60 THEN '及格' ELSE '不及格' END ) '等级' FROM math ORDER BY score DESC
- convert()的用法,处理时间
- Sybase中:select getdate() -- '10/11/12'
select convert(char,getdate(),101) -- '09/27/2010'
select convert(char,getdate(),102) -- '2010.11.12'
select convert(char,getdate(),103) -- '27/09/2010'
select convert(char,getdate(),104) -- '27.09.2010'
select convert(char,getdate(),105) -- '27-09-2010'
select convert(char,getdate(),106) -- '27 Sep 2010'
select convert(char,getdate(),107) --'Sep 27, 2010'
select convert(char,getdate(),108) --'11:16:06'
select convert(char,getdate(),109) --'Sep 27 2010 11:16:28:746AM'
select convert(char,getdate(),110) --'09-27-2010'
select convert(char,getdate(),111) --'2010/09/27'
select convert(char,getdate(),112) --'20100927'
select rtrim(convert(char,getdate(),102))+' '+(convert(char,getdate(),108)) -- '2010.11.12 11:03:41' - MySQL中处理时间:select date_format(now(),'%Y-%m-%d %H:%i:%s') --'2010-09-27 00:00:00'
- Sybase中:select getdate() -- '10/11/12'
- 复制表结构和数据
- Sybase可通过select into : select * into newtable from oldtable
- MySQL:create table newtable (select * from oldtable)
- 创建临时表
- Sybase:create table #temptablename (临时表名已#开头)
- MySQL:create temporary table tablename
- 查看表信息
- Sybase:select * from sysobjects
- MySQL:select * from information_schema.tables
- 使用存储过程中的差异
- 存储过程中执行动态sql的方法:
- Sybase:使用
- declare @sql char(100)
select @sql='select * from sysobjects'
exec(@sql)
- declare @sql char(100)
- mysql:
-
declare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句
set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'"); -- 拼接查询sql语句
set @sql = SQL_FOR_SELECT;
PREPARE stmt FROM @sql; -- 预处理动态sql语句
EXECUTE stmt ; -- 执行sql语句
deallocate prepare stmt; -- 释放prepare
-
- Sybase:使用
- 存储过程中执行动态sql的方法:
-
存储过程的使用语法有取别:
-
记录一个MySQL使用存储过程中遇到的问题
------------------DBeaver------------------- @delimiter &&; create procedure testa() begin select * from users; select * from orders; end; && @delimiter; && -----------------DBvisulizer------------------------ create procedure testa() begin select * from users; select * from orders; end;
-
在DBeaver与Dbvisualizer中,创建存储过程的方式不一样
-
-
sql语句块的使用(MySQL不支持在外部使用begin if end 等):
-
Sybase中:
------------------sybase中------------------------ While(select avg(price) from titles)>;$20 Begin Update titles set price=price/2 If(select avg(price) from titles)<$40 Break Else Continue End 在syabse中,可以直接运行begin end 等sql块语句 --------------------mysql------------------------- ELIMITER $$ CREATE PROCEDURE GetCustomerLevel( in p_customerNumber int(11), out p_customerLevel varchar(10)) BEGIN DECLARE creditlim double; SELECT creditlimit INTO creditlim FROM customers WHERE customerNumber = p_customerNumber; IF creditlim > 50000 THEN SET p_customerLevel = 'PLATINUM'; ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN SET p_customerLevel = 'GOLD'; ELSEIF creditlim < 10000 THEN SET p_customerLevel = 'SILVER'; END IF; 通常begin-end用于定义一组语句块,在各大数据库中的客户端工具中可直接调用,但在mysql中不可用。 MySQL中begin-end、流程控制语句、局部变量只能用于函数、存储过程内部、游标、触发器的定义内部。
-
11.ifnull()与isnull()
1.mysql使用ifnull()
2.sybase 使用ifnull()
12.获取字符串中某个字符所在位置
1.MySQL:instr()
2.sybase:charindeex()
13.部分语法差异:
1.update from 语句:
Mysql:update tableA join tableB on a=b set a.column = xxx
Sybase: update tableA set a.column =xxx from tableA,tableB where a=b
2.表连接查询,sybase可使用*=,=*
Mysql :select * from tableA left join tableB on a=b;
Sybase: select * from tableA,tableB where a*=b
3.group语法使用差异(sybase中更加精简):
Mysql:select colum1,column2,column3 from tableA where column1 in (select cloum1 from tableA group by cloumn1)
sybase: select colum1,column2,column3 from tableA group by cloumn1
4.别名的设置
Mysql :select uuid() as uuid,a.* from tableA a
sybase : select newid() as uuid,* from tableA
5.表连接的操作,sybase 更简洁
mysql : select *column1 from tableA join tableB on a= b
sybase : select column1 from tableA, tableB where a=b