The differences between SQL and Oracle syntax are: 1. Different data types; 2. Different functions to obtain the current system time; 3. Different ways of creating users; 4. Different ways of connecting variables and strings ;5. The syntax of the conditional statement "if...else..." is different, etc.
The syntax differences between SQL and Oracle include different data types, different functions to obtain the current system time, no default constraints in Oracle, and the way to connect variables and strings Different, the case statement is different, etc.
The data types are different
SQL server data types: int, smallint, char, varchar, nchar, nvarchar, ntext,datetime,smalldatetime,money,decima,float,bit
Oracle data type: number(p,s),char,varchar2,Date,LOB
Get the current system The functions of time are different
sql server: getdate()
oracle: sysdate
For example: function to set date format
to_char(sysdate,'yyy-mm-dd');
There is no default constraint in oracle
Add default constraint in sql server:
alter table talbe_name add DF_table_name default('男') for sex;
Add default value in oracle:
alter table table_name modify(sex default('男'));
The methods of connecting variables and strings are different
sql server medium connection: use " " connection, for example:
print 'aaaa'+@name;
oracle medium connection: use "|| "Connection, for example:
dbms_output.put_line('aaa'||name);//name为变量
oracle does not have an identity automatic growth column, but uses a sequence to achieve growth
sql server automatic growth: identity (1,1 can be used directly in the primary key column of the table )Achieve growth
oracle Use the sequence to automatically grow:
create sequence se_id start with 1 increment by 1
Use the sequence to realize automatic growth: se_id.nextval
The syntax of the conditional statement if...else... Different
sql server:
if 条件 begin ………… end else begin ………… end
oracle:
if 条件1 then …………; elsif 条件2 then …………; else …………; end if;
The syntax of case statement is different
sql server In:
select ....case.....(else)....end....语句 select stuno '学号',case when grade>=90 and grade<=100 then '★★★★' when grade>=80 and grade<90 then '★★★' when grade>=70 and grade<80 then '★★' when grade>=60 and grade<70 then '★' else '差' end as '等级' from score go
oracle:
declare nums number:=&nos;--&nos表示提示传入值 begin case nums when 100 then dbms_output.put_line('满分也,不错'); when 90 then dbms_output.put_line('90分页很不错了'); end case; end;
The way to create users is different
sql server
Create login Account: sa-----123456
create Login 登陆名称 with password='登陆密码'
Modify login account:
alter Login 登陆名称 with name='新登录名称' and password='新登录密码'
Disable/enable login account
alter Login 登录名称 disable(禁用)/enable(启用)
Delete login account
drop Login 登录名称
Create user:
create user 用户名 for/from Login 登陆名称
Modify user name
alter user 用户名 with name='新用户名'
Delete user name
drop user 用户名
Authorization limit
grant select/update/delete/insert on 表名 to 用户名
oracle:
Create user syntax
create user 用户名 identified by 密码 default tablespace users temporary tablespace temp quota 10M on users
Modify password
alter user 用户名 identified by 新密码
Grant permission
grant create session to 用户名
Delete User
drop user 用户名 cascade;
The above is the detailed content of What is the difference in syntax between sql and oracle?. For more information, please follow other related articles on the PHP Chinese website!