MSSQL MYSQL ORACLE 语法差异
|
操作 |
SQLServer |
Oracle |
Mysql |
|
查看表结构 |
exec sp_help 表名 |
desc 表名 在command window看 |
desc 表名 或 describe 表名 或 show columns from 表名; |
|
修改数据库名称 |
exec sp_renamedb ‘旧数据库名’,’新数据库名’ |
不详 |
|
|
修改表名 |
exec sp_rename ‘旧表明’,’新表明’ |
rename 旧表名 to 新表名 |
alter table 表名 rename to 新表名 |
|
修改列名 |
exec sp_rename ‘表.旧列名’,’新列名’ |
alter table 表名 rename column 旧列名 to 新列名 |
alter table test change column address address1 varchar(30)--修改表列名 |
|
删除数据库 |
drop database 数据库名 |
不详 |
Drop database |
|
添加表中一列 |
alter table 表名 Add 列名 数据库类型 |
alter table 表明 add(列名 数据类型) 或 alter table 表名 Add 列名 数据库类型 |
alter table test add column name varchar(10); --添加表列 |
|
删除表中一列 |
alter table 表名 drop column 列名 |
alter table 表名 drop column 列名 |
alter table test drop column name; |
|
修改表现有列 |
alter table 表名 alter column 列名 新数据库类型 大小 |
alter table 表明 modify(列名 数据类型) |
alter table test modify address char(10) --修改表列类型 ||alter table test change address address char(40) |
|
删除约束 |
alter table 表名 drop constraint 约束名 |
完全一样 |
|
|
添加主键约束 |
alter table 表名 add constraint 主键约束名 primary key (列名) |
完全一样 |
alter table 表明add primary key (列名)
|
|
删除主键约束 |
|
|
alter table 表名 drop primary key
|
|
添加唯一约束 |
alter table 表名 add constraint 唯一约束名 unique (列名) |
完全一样 |
alter table 表名 add unique (列名)
|
|
添加默认约束 |
alter table 表名 add constraint default (值) for 列名 |
完全一样 |
|
|
添加检查约束 |
alter table 表名 add constraint check (列名 > 10) |
完全一样 |
|
|
添加外键约束 |
alter table 子表 add constraint 外键约束名 foreign key (子表的列名) references 主表 (列名) on update cascade / on update action 注意: 默认和 加 on update action 表示更新受限 加on update cascade 表示更新不受限,多项 操作时用逗号隔开 |
完全一样 |
|
|
添加索引约束 |
|
|
alter table 表名 add index 索引名 (列名) 添加普通索引： create index 索引名 ON 表名 (列名) 添加唯一索引： create unique索引名 ON 表名 (列名)
|
|
删除索引 |
|
|
drop index 索引名 on 表名 alter table 表名 drop index 索引名
|
|
创建登陆帐户 |
windows身份: exec sp_grantlog ‘windows域名\密码’ sql身份: exec sp_addlogin ‘登陆帐户’,’密码’
|
|
|
|
创建数据库用户 |
use 数据库名 Windwos身份: Exec sp_grantdbaccess ‘windows域名\密码’,’数据库用户名’ Sql身份: Exec sp_grantdbaccess ‘登陆帐户’,’数据库用户名’ |
create user HDEAM_TYMB identified by "" default tablespace HDEAM_TYMB temporary tablespace TEMP profile DEFAULT;
|
mysql>insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_sub CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; CREATE USER 'pig'@'%' IDENTIFIED BY ''; CREATE USER 'pig'@'%'; |
|
向数据库用户授权 |
use 数据库名 Grant 权限 [on 表明] to 数据库用户 |
-- Grant/Revoke role privileges grant dba to HDEAM_TYMB with admin option; -- Grant/Revoke system privileges grant unlimited tablespace to HDEAM_TYMB with admin option; |
grant select,insert,update,delete on *.* to test1@“%” Identified by “abc”; grant all privileges on phplampDB.* to phplamp@localhost identified by '1234'; mysql>flush privileges; |
|
删除权限 |
revoke 权限 [on 表名] from 数据库用户 |
|
|
|
删除数据库用户 |
exec sp_revokedbaccess ‘数据库用户’ |
drop user HDEAM_SBFF cascade; |
mysql>Delete FROM user Where User="phplamp" and Host="localhost"; mysql>flush privileges; |
|
删除登陆帐户 |
windows身份: exec sp_droplogin 登陆帐户名 sql身份: exec sp_revokelogin 登陆帐户名 |
|
|
|
移动数据库 |
1.分离数据库: Exec sp_detach_db 数据库名 2.附加数据库: Exec sp_attach_db 数据库名, @filename1=’主数据文件路径’, @filename2=’日志文件路径’ |
exp hdeam_product/d3B68Apk29v34Dj@orcl file=E:/tymb.dmp log=E:/tymb.log
imp HDEAM_LHSH/HDEAM_LHSH@orcldev file=E:\TYMBHDEAM_BAK_2013-03-25.dmp full=Y; |
mysqldump -h localhost -u root -pmysql oa >d:\oa.sql mysql -h localhost -u root -p jira<d:\jira.sql |
|
数据库的信息查询
|
查看数据库的所有对象:Select * from sysobjects 查询数据库 select * from sysdatabases (表) 查看表结构和属性 Exec Sp_help 表名 查看所有数据库 Exec sp_databases 查看数据库的位置和属性Exec sp_helpdb 数 据库名 查看表中的约束exec sp_helpconstraint 表名 查看表的索引 exec sp_helpindex 表 查看系统中的储蓄过程： exec _stored_procedures 储蓄过程/空 查看 过程,函数,视图,表的源代码: Exec sp_helptext 对象名 查看数据库所有登陆帐户: select * from syslogins 查看数据库所有数据库用户: select * from sysusers
|
注意: 下面都是通过( select * from 对象 ) 来查看信息 触发器 user_triggers 过程 user_procedures 查看源代码 user_source 查看数据库对象 user_objects 查看错误信息 show errors 查看索引信息 user_indexes 查看分区索引 user_ind_partitions 查看有关基于列创建的索引 user_ind_columns 查看表空间 -- user_tablespaces 查看序列 -- user_sequences 查看同义词 -- user_ind_columns 查看用户表信息 -- user_tables 查看用户所有的表信息 user_all_tables |
查看所有数据库 show databases; 查看库所有表 show tables; 查看表结构和属性 use information_schema; select * from columns where table_name=’表名’ 查看表源代码show create table 表名; 查看存储过程源代show create procedure 过程名 查看视图源代码show create procedure 过程名 查看视图资代码show create VIEW 视图名 查看表的索引show index from 表名 查看表的索引show keys from 表明 |
|
时间 |
datetime |
sysdate |
NOW() |
|
时间戳 |
timestamp |
|
|
|
字符串 |
varchar(20) |
varchar2(20) |
|
|
获取当前系统 时间和时间戳 |
getdate() |
sysdate |
|
|
数据插入 |
不一定要指定具体列名 |
不一定要指定具体列名 |
必须指定具体列名 |
|
标识 |
identity (标识的开始值, 标识种右) |
create sequence 序列名 1.序列名.nextval 获取下一个序列的值 2.序列名.currval 获取当前的序列的值 |
|
|
物理文件个数 |
主数据文件: *.mdf =1 次数据文件:*.ndf >=0 日志文件: *.log >=1 |
数据文件: *.dbf >=1
日志文件: *.log >=1 |
|
|
字符函数 |
charindex(t_char,s _char,1)从s字符串中的1位置开始查找t字符串所在的下标(默认从1开始) substring(char,pos,len)从pos位置开始截取len len(char) 获取字符串的长度 lower(char) 把字符串全部转换成小写 upper(char)把字符串全部转换成大写 ltrim(char)清除左边的空格 rtrim(char)清楚右边的空格 left(char,3)从左边返回指定长度的字符串 right(char,3)从右边返回指定长度的字符串 replace(s_char,t_char,n_char)将s中的t替换成 n字符串 stuff(‘ABCDEFG’2,3,’我的音乐我的世界’) 返回：A我的音乐我的世界EFG 在一个字符串中，删除从2位置开始指定长度为3的字符串，并在该位置插入一个新的字符串 |
instr(char ,search char) 查找第一个字符出现的位置 索引从1开始
substr(char, pos, len) 截取字符串 length(char) 返回字符串的长度 lower(char) 转换为小写 upper(char) 转换为大写 trim() 截取左右空格 initcap(char) 首字母大写 ltrim(char,set) 左剪裁 rtrim(char,set) 右剪裁 replace(char oldchar, newchar) 字符串替换 concat(char1, char2) 连接字符串 cha(67) 根据ASCII码返回对应的字符 lpad()和rpad() 在字符串的左边或右边添加东西 需要3个参数 第一个是字符串 第二个是返回值的总长度, 第三个是用来填充的字符
|
|
|
转换函数 |
cast(值 as varchar) 不需指定长度 convert (varchar(20), 值) 必须指定长度 |
to_char(d|n, fmt)将数字或日期转换为指定格式的字符串 to_date(char,frm)将char 或varchar 转换为日期数据类型 to_number() 将包含数字的字符转换为number数据类型 |
|
|
其他函数 |
abs(-1) - >1 取绝对值 ceiling(43.5) -> 44取上界最小整数 floor(43.5) - >43 取下界最大整数 power(5.2) - >25 取幂 round(4.34,1) ->4.3 四舍五入， 保留一位小数 sign(-43) -> -1 正数返回1 ,负数返回-1 sort(9) -> 3 求平方根 host_name() 获取计算机名称 |
nvl(ex1,ex2) 如果ex1为null则返回ex2; 如果ex1不为null则返回ex1; nvl2(ex1,ex2,ex3) 如果ex1不为null则返回ex2 如果ex1为null则返回ex3 nullif(ex1,ex2) 如果ex1=ex2则返回null 否则返回ex1; |
|
|
分组函数(聚合函数) |
avg(列名) 求这列的平均值 min(列名) 求这列的最小值 max(列名) 求这列的最大值 count(*) 统计所有的行 包括重复值和空值 count(列名) 统计指定列中非空值的个数 count(distinct 列名) 统计不是重复值的个数 group by右句用于将信息表划分为组,按组进行聚合运算 |
avg(列名) 求这列的平均值 min(列名) 求这列的最小值 max(列名) 求这列的最大值 count(*) 统计所有的行 包括重复值和空值 count(列名) 统计指定列中非空值的个数 count(distinct 列名) 统计不是重复值的个数 group by右句用于将信息表划分为组,按组进行聚合运算 |
|
|
日期函数 |
getdate() 返回当前系统时间 dateadd(mm,4,’01/01/99’)返回指定日期上+指定部分后的日期 datediff(mm,’01/01/99’,’05/01/99’)返回两日期间指定部分的值 datename(dw,’01/01/2000’)返回指定日期的指定部分的字符串格式 datepart(day,’01/15/2000’)返回指定日期部分的整数形式 |
a. add_months(d,n) 返回给指定的日期加上指定的月数后的日期值 select add_months(sysdate,2) from dual; b. months_between(d,d) 返回2个日期之间的月数select months_between (date '2005-05-06', date '2005-9-01') from dual; c. last_day(d) 返回指定日期当月的最后一天的日期select last_day(sysdate) from dual; d. round(d, [fmt]) 返回日期值 日期四舍五入为格式模型指定的单位 select round(date'2005-09-08','year') from dual; 返回 2005-01-01 select round(date'2005-09-08','month') from dual; 返回 2005-09-01 select round(date'2005-09-08','day') from dual; 返回 最靠近的一个星期日 e. next_day(d, day) 返回指定的下一个星期几的日期 select next_day(sysdate,'星期二') from dual f.trunc 语法与round 相同 区别:trunc 是只舍不入 g.extract 提取日期时间类型中的特定部分 |
|
|
连接操作符 |
+ |
|| 连接字符串用于将2个或多个字符串合并成一个字符串 |
|
|
集合查询操作 (两个表中的数据类型和列名必须相同) |
|
a.union union操作符返回2个查询选定的所有不重复的行select orderno from order_master union select orderno from order_detail; b.union all 合并2个查询选定的所有行 包括重复的行 c.intersect 返回2个查询都有的行 d.minus 第1个查询在第2个查询中不存在的数据 |
|
|
连接查询 (n个表,至少有n-1个条件) 原理: 内连: 1对1 左外连:
|
1.内连接:(查询出条件匹配的所有行) 1) Select 左别名.列名,右别名.列名 from 左 表 as 左别名 inner join 右表 as 右别名on 左别名.列名 = 右别名.列名 2) Select 左表名.列名,右表名.列名 from 左 表 ,右表 where 左表名.列名 = 右表名.列名 2左外连接:(返回左表的所有行,如果右表没有与左表匹配的行则全部用null代替,存在则显示具体数据,显示出来的总行数由左表决定) Select 左别名.列名,右别名.列名 from 左 表 as 左别名 left join 右表 as 右别名on 左别名.列名 = 右别名.列名 3右外连接:(返回右表的所有行,如果左表没有与右表匹配的行则全部用null代替,存在则显示具体数据,显示出来的总行数由右表决定) Select 左别名.列名,右别名.列名 from 左 表 as 左别名 right join 右表 as 右别名on 左别名.列名 = 右别名.列名 |
完全一样 连接分类: 1内连接: 条件 2外连接 1)左外连接: left join 或 left outer join 2)右外连接:right join 或 right outer join 3)完整外连接: Full join 或 Full outer join 3交叉连接: from …
4. 交叉连接:(返回左右表的所有行,如果左表没有与右表匹配的行则全部用null代替,如果右表没有与左表匹配的行则全部用null代替,存在则显示具体数据,显示出来的总行数=左表和右表行数和决定) Select 左别名.列名,右别名.列名 from 左 表 as 左别名 right join 右表 as 右别名on 左别名.列名 = 右别名.列名 |
|
|
利用现有表创建新表 |
a,表存在 1.完全拷贝: insert into 目标表 select * from 源表 2.部分拷贝: insert into 目标表 (列名) select 列名 from 源表 b,表不存 1.完全拷贝: select * into 新表 from 源表 2.部分拷贝: select 列名 into 新表 from 源表 3.拷贝表结构: select * into 新表 from 源表 Where 1=2 |
a.完全拷贝: create table temp as select * from emp; b.拷贝一部分create table temp as select 列1,列2 from 表 c.只拷贝一个空表(根据一个假条件) create table temp as select * from 表 where 1=2 d.使用列别名: select stu_id, stu_name as "学生姓名" from student; |
|
|
利用把A表数据,同步B表 |
方法1： update 目标表 set 列 = a.列 from 源表 a where a.关联列 = 目标表.关联列 方法2： update b set b.列 = a.列 from 源表 a , 目标表 b where a.关联列 = b.关联列 方法3： update 目标表 set 列 = a.列 from 源表 a , 目标表 b where a.关联列 = b.关联列
方法4： update 目标表 set 目标表.列 = a.列 from 源表 a , 目标表 b where a.关联列 = b.关联列
方法5： update 目标表 set目标表.列 = a.列 from 源表 a where a.关联列 = 目标表.关联列
方法6： 通过关联查询语句生成update语句
|
update 目标表a, 源表b set a.列 =b.列 where a.关联列 = b.关联列 |
update 目标表a, 源表b set a.列 =b.列 where a.关联列 = b.关联列 |
|
数据操纵语言(DML) |
a.创建一个新表 create table student(stu_id int, stu_name varchar(20)) b.修改表中的列 alter table 表名 alter column 列名 新数据库类型 大小 c.添加表中的列 alter table 表名 Add 列名 数据库类型 d.删除表中的列 alter table 表名 drop column 列名 e.删除表中的记录 而不删除表的结构truncate table student f.只是查看表的结构 g.删除表中的数据及表的结构 drop table student; |
a.创建一个新表 create table student(stu_id int, stu_name varchar2(20)) tablespace lijiao b.修改表中的列 alter table student modify(stu_name varchar2(40)) c.添加表中的列 alter table student add(stu_age int) d.删除表中的列 alter table student drop column stu_age e.删除表中的记录 而不删除表的结构truncate table student f.只是查看表的结构 desc student g.删除表中的数据及表的结构 drop table student; |
|
|
事务控制语言 事务:是单个的工作单元. 将多条相关的语句一起全部执行(必须是相关的) 执行一系列的操作 |
|
a. commit 命令(用于提交并结束事务处理) commit b.savepoint 保存点类似于标记 它将很长的事务处理划分为较小的部分 他们用来标记事务中可以应用回滚的点save point savepoint_id c.rollbace work ,rollback d.回滚到某个保存点 rollback to savepoint |
|
|
Oracle中特有的伪列(伪列就像数据库中的一个表列,但实际上并未存储在表中,伪列只能查询) |
不存在 |
a.rowid(可以唯一的标识数据库中的一行) b.rownum (代表行的序号) rownum 不跟> , = , >= 操作使用 |
|
|
定一个局部变量 |
Declare @变量名 DataType |
|
|
|
为变量赋值 |
Set @变量名 = 值 或 Select @变量名 = 值 |
|
|
|
带查询语句为变量 赋值 |
Select @变量名 = 列名 from 表明 where 列名 = 条件 |
|
|
|
常用的全局变量 (用户不能定义的变量) |
@@error 最后一个T-SQL错误号 @@identity 最后一次插入的标识值 @@Servername 本地服务器的名称 |
|
|
|
输出语句 |
Print ‘值’ 只出现在查询分析器中显示 RaisError(‘值’,16,1) 能在前台用异常扑捉 |
|
|
|
逻辑控制语句
判断存在时
判断不存在时 |
If (条件) begin … end If (条件) begin …end else begin … end
If exists(select * from 表名 where 列名 = 值) begin …. end If not exists(select * from 表名 where 列名 = 值) begin …. end |
a) if condition then exp_body end if; b) if condition then exp_body else exp_body end if; c) if condition then exp_body elsif condition then exp_body else exp_body end if; |
|
|
循环控制语句 |
While(true) begin … end |
1) loop condition end loop 2) while condition Loop exp_body end loop 3)for varable in [REVERSE] value1...value2 ( varable 变量不需声明 , ... 是范围连接符 ) loop exp_body end loop |
|
|
Case |
a) select 别名=case when 条件 then 值 when 条件 then 值 else 值 end from 表
b) select case 列名 when 值 then 值 when 值 then 值 else 值 end from 表 |
a) select case 列名 when 值 then 值 when 值 then 值 else 值 end case from 表明
b) select case when 列名=值 then 值 when 列名=值 then 值 else 值 end case from 表 |
|
|
属性类型: |
不存在 |
1.列类型: %TYPE varable tableName.columnName%TYPE; 2.行类型: %ROWTYPE varable tableName%ROWTYPE; 3.使用行类型: varable.列名; |
|
|
异常处理 |
RaisError(‘异常信息’,16,1) 能在前台用异常扑捉 |
1)自定义异常: a)定义异常:(位 declare和 begin之间) DECLARE varable EXCEPTION; b)显示引发异常(为 begin 和 end 之间): RAISE varable; 2)处理预定义异常:(位 begin 和 end 之间) EXCEPTION when Too_Many_ROWS then DBMS_OUTPUT.PUT_LINE('返回多行'); when others then DBMS_OUTPUT.PUT_LINE('全部处理'); 3) 引发应用程序错误: RAISE_APPLICATION_ERROR(error_number, error_message); |
|
|
子查询 (原理:在一个查询的基础上进行再次查询) |
相关子查询:(重复子查询,不能单独执行) 切套右查询(只执行一次,能单独执行, 查询原理: 从外(外sql一次查出所有的行一次一行的传递给里面的sql)到里 把外不查出来的值传递给里面来) 注意:查询到结果可以作为表来使用但必须指定别名 |
一样 切套子查询: Select * from AuthorBook a where CopyRight = (select Max(CopyRight) from AuthorBook where BookName = a.BopokName) |
|
|
查询性能的规则 |
能用连接查询时一般不用右查询 右查询功能(效率低)>连接查询(效率高) 右查询能用语查询所有查询, 而连接查询有些不能. 右查询有连接查询不能的功能 |
完全一样 |
|
|
In 和 not in |
只能适合于 返回多行一列 |
完全一样 |
|
|
=,>,<,=>,=<,1= |
只能适合于返回一行一列 |
完全一样 |
|
|
分组: Group by |
Where 只能出现在Group by分组之前, Having 不能单独使用 如果没有group by 就没有Having |
完全一样 |
|
|
排序 |
Order by desc / asc |
完全 一样 |
|
相对于mssql oracle显著的书写特点：
1.代码片段必须放到begin end .. 中
2.虚拟表 dual 的使用
3.每行代码强制分号";"结束，包括end
4.赋值符号 :=；（select xx into xx from daul;也可以用于赋值）
5.省略了 as
...
补充：
几种关系数据库中字符编码和存储长度需要注意的几个问题
1.mysql中char(n) varchar(n) 中再utf8编码存储方式下数字表示的是字符数，但是在其他方式下就根据情况定，需要再相应环境下探索一下。
2.SqlServer
char(n)：固定长度，存储ANSI字符，不足的补英文半角空格。 n是字节数。
nchar(n)：固定长度，存储Unicode字符，不足的补英文半角空格。n是字符数。
varchar：可变长度，存储ANSI字符，根据数据长度自动变化。n是字节数。
nvarchar：可变长度，存储Unicode字符，根据数据长度自动变化。n是字符数。
3.Oracle
和SqlServer类似 n字头的都是unicode类型,定义的长度是字符数(在我本地环境验证如此)
char/varchar/varchar2则和参数设置相关 ，通过语句
select * from sys.nls_database_parameters;
select * from sys.nls_session_parameters;
发现参数 NLS_LENGTH_SEMANTICS 是byte(非char)，所以定义的长度是byte(通过实验确认如此，但是没有改为char验证)。
总的来说，不管使用以上那种数据库，使用前或使用已有环境时先回顾并弄清楚，当前数据库的版本、引擎、存储编码、字符集、每种数据类型的含义。含义包括
a.是否可变长度
b.编码 -是否unicode等
c.定义长度的含义是字符还是字节
b.数据类型长度(单位)的取值范围
