SQLserverOracleMysql语法与用法对比(over)

1、操作SQLServerOracleMysql查看表纟口 构exec sp_help 表名desc表名desc 表名 或 describe 表名或showcolu mns from 表名；修改数据 库名称exec sp_renamedb 旧 数据库名新数据库 名，不详修改表名exec sp_rename 旧表 明新表明rename 旧表名 to 新表名修改列名exec sp_rename 表.旧列名新列名alter table 表名renamecolumn旧列名to新列名删除数据库drop database 数据库 名不详添加表中 一列alter table 表名 Add列名数据库类型alter

2、 table表明add（列名数据类型）或alter table表名 Add列名 数据库类型删除表中一列alter table 表名 drop column列名alter table 表名 drop column 歹y名修改表现 有列alter table 表名 alter colu mn列名新数据库类型大小alter table表明 modify（列名数据类型）删除约束alter table 表名 drop con stra int约束名完全一样添加主键 约束alter table 表名add constraint 主键约 束名 primary key （歹U 名）完全一样alter tabl

3、e 表明add primary key（列名）删除主键 约束alter table 表 名dropprimary key添加唯一 约束alter table 表名 add constraint 唯一约 束名unique （列名）完全一样alter table 表 名addunique （歹U 名）添加默认 约束alter table 表名add constraint default（值）for列名完全一样添加检查alter table 表名完全一样约束add constraint check 例名 10)添加外键 约束alter table 子表add constraint 夕卜键约 束名 f

4、oreign key（子表的列名） references 主表 （歹U 名）on update cascade / on update action注意:默认和加on update action 表示更新受 限力口 on update cascade 表示更新不受限，多项操作时用逗号隔开完全一样添加索引 约束alter table 表 名 add index 索引名（列 名）添加普通索 引：create in dex 索引名ON 表名（列名） 添加唯一索 引：create unique 索引名ON 表名（列名）删除索引drop in dex 索引名 on 表名alter table 表 名dr

5、opindex 索引 名创建登陆 帐户windows 身 份:exec sp gra ntlog Win dows域名密码sql 身份： exec sp_addlogin 登陆帐 户密码创建数据 库用户use数据库名Windwos 身份：Exec sp_gra ntdbaccess windows域名密码 数据库用户名Sql 身份：Exec sp_gra ntdbaccess 登 陆帐户数据库用户 名，向数据库 用户授权use数据库名Grant权限 on表 明to数据库用户删除权限revoke权限 on 表 名from数据库用 户删除数据库用户exec sp_revokedbaccess 数据

6、库用户删除登陆 帐户windows 身 份:exec sp_droplogin 登陆帐 户名sql 身份: exec sp_revokelogin 登陆 帐户名移动数据 库1分离数据库：Exec sp_detach_db 数据库名2附加数据库：Exec sp_attach_db 数据库名，filename仁主数 据文件路径，filename2=日志 文件路径数据库的信息查询查看数据库的所有对象：Select* fromsysobjects查询数据库 select * fromsysdatabases注意:下面都是通过（select * from对象）来查看信息 触发器 user_trigger

7、s过程 user_procedures 查看源代码user source查看所有数据 库showdatabases;查看库所有表show tables;俵）查看表结构和属性Exec Sp_help 表名查看所有数据库Exec sp_databases查看数据库的位置和属性 Exec sp_helpdb数据库名查看表中的约束execsp_helpc on stra int表名查看表的索引execsp_helpindex 表查看系统中的储畜过exec查看数据库对象user_objects查看错误信息 show errors 查看索引信息 user_ in dexes 查看分区索引 user_ in

8、 d_partitio ns查看有关基于列创建的索引user_i nd_colu mns查看表空间-user_tablespaces查看序歹 U - user_seque nces 查看同义词-user_i nd_colu mns查看用户表信息-user_tables程查看用户所有的表信息:user_all_tablesspstoredprocedures储蓄过程/空查看表结构 和属性usein formatio n_s chema;select * from colu mns wheretable_ name= 表名查看表源代 码showcreate table 表名；查看存储过 程源代sh

9、owcreate procedure 过程名查看视图源 代码 showcreate procedure 过程名查看视图资 代码 showcreateVIEW 视图 名查看表的索弓丨 show index from表名查看表的索 弓丨 show keys from表明查看过程，函数，视图，Exec sp_helptext 对查看数据库所有登陆select*from查看数据库所有数据select * from sysusers时间datetimesysdateNOW()时间戳timestamp字符串varchar(20)varchar2(20)获取当前 系统时间和时间戳getdate()sysda

10、te数据插入不一定要指定具体列 名不一定要指定具体列名必须指定具体列名标识identity （标识的开始 值，标识种右）create seque nee 序歹 U名1.序列名.nextval获取下一个序列的值2.序列名.currval获取当前的 序列的值物理文件 个数主数据文件:*.mdf =1次数据文件:*.ndf =0 日志文件：*.log =1数据文件:*.dbf =1日志文件：*.log =1字符函数chari ndex(t_char,s _char,1)从s字符串中 的1位置开始查找t字符串所在的下标(默 认从1开始)substri ng(char,pos,le n) 从pos位置开

11、始截取 lenlen(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( BCDEFG ,3, 我的音乐我的世界 返回：A我的音乐我 的世界EFG 在一个字符串中，删 除从2位置开始指定 长度为3的字符串， 并在该位置插入一个 新的字符串i

12、nstr(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()在字符串的

13、左 边或右边添加东西需要3个参数第一个是字符 串第二个是返回值的总长度 ， 第三个是用来填充的字符转换函数cast(值 as varchar)不需扌曰疋长度convert (varchar(20),值)必须指定长度to_char(d| n, fmt)将数字或日期 转换为指定格式的字符串 to_date(char,frm)将 char 或 varchar转换为日期数据类型to_n umber()将包含数字的字 符转换为number数据类型其他函数abs(-1)- 1 取绝对值ceiling(43.5) - 44 取上界最小整数floor(43.5)- 43 取下界最大整数power(5.2) -

14、 25 取 幕rou nd(4.34,1) -4.3 四 舍五入，保留一位小 数sig n(-43) - -1 正数返回1，负数返回-1sort(9) - 3 求平方根 host_name()获取计 算机名称n vl(ex1,ex2)如果ex1为null则返回ex2;如果ex1不为null则返回ex1;n vl2(ex1,ex2,ex3)如果ex1不为null则返回ex2如果ex1为null则返回ex3n ullif(ex1,ex2)如果ex仁ex2则返回 null否则返回ex1;分组函数(聚合函数)avg(列名)求这列的 平均值min(列名)求这列的 最小值max(列名)求这列的 最大值co

15、u nt(*) 统计所有的行包括重复值和空 值count(列名)统计指定列中非空值的个数count(distiinct 列 名)统计不是重复值的个 数group by 右句用于将 信息表划分为组，按组 进行聚合运算avg(列名)求这列的平均值 min(列名)求这列的最小值 max(列名)求这列的最大值 count(*)统计所有的行包括重复值和空值count(列名)统计指定列中非 空值的个数count(distinct列名)统计不是 重复值的个数group by右句用于将信息表划 分为组，按组进行聚合运算日期函数getdate()返回当前系统时间dateadd(mm,4, 1/01/9 9返回指

16、定日期上 + 指定部分后的日期datediff(mm, 1/01/99 ,5/01/99 )返回两日 期间指定部分的值date name(dw,01/01/2a. add_months(d,n)返回给指 定的日期加上指定的月数后的日期值selectadd_ mon ths(sysdate,2)fromdual;b. months_between(d,d)返回 2个日期之间的月数select mon ths_betwee n(date2005-05-06, date 2005-9-01)000）返回指定日期的 指定部分的字符串格 式datepart（day,01/15/2000）返回指定日期部

17、分的整数形式from dual;c. Iast_day(d)返回指定日期当月的最后一天的日期selectlast_day(sysdate) from dual;d. round(d, fmt)返回日期值日期四舍五入为格式模型指定的单位selectrou nd(date2005-09-08,year) from dual;返回 2005-01-01 selectrou nd(date2005-09-08,mo nth) from dual;返回 2005-09-01 selectrou nd(date2005-09-08,day) from dual; 返回 最靠近的一 个星期日e. next_

18、day(d, day) 返回指定的下一个星期几的日期select n ext_day(sysdate,星 期二)from dualf. trunc语法与 round相同区 别:trunc是只舍不入g. extract提取日期时间类型中 的特定部分连接操作 符+|连接字符串用于将2个或多个字符串合并成一个字符串集合查询 操作（两个表 中的数据 类型和列 名必须相同）a. unionunion操作符返回2个查询选定的所有不重复的行 selectorder nofromorder_master unionselectorder no from order_detail;b. union all合并2

19、个查询选定 的所有行包括重复的行c. intersect 返回2个查询都有 的行d. minus第1个查询在第 2个 查询中不存在的数据连接查询（n个表， 至少有n-1个条 件）原理： 内连：1内连接：（查询出条件 匹配的所有行）1） Select左别名.列 名，右别名.列名 from 左 表as左另寸 名 inner join 右表 as右别名on左别名.完全一样连接分类：1内连接： 条件2外连接1）左外连接：left join或 left outer join1对1左外连：列名=右别名列名2） Select左表名列 名，右表名列名 from 左 表,右表 where左表名.列名 - 右表名

20、.列名2左外连接:（返回左表 的所有行，如果右表没 有与左表匹配的行则 全部用null代替,存在 则显示具体数据，显示 出来的总行数由左表 决定）Select左别名.列名， 右别名.列名 from 左表 as左别名left join 右表 as 右 别名on左别名.列名=右别名.列名3右外连接:（返回右表 的所有行，如果左表没 有与右表匹配的行则 全部用null代替,存在 则显示具体数据，显示 出来的总行数由右表 决定）Select左别名.列名， 右别名.列名 from 左表 as左别名right join 右表 as 右别名on左别名.列 名=右别名洌名2）右外连接:right join 或

21、 right outer join3）完整外连接：Full join或 Full outer join3交叉连接:from4.交叉连接:（返回左右表的所 有行,如果左表没有与右表匹 配的行则全部用null代替，如果右表没有与左表匹配的行则 全部用null代替，存在则显示 具体数据，显示出来的总行数- 左表和右表行数和决定） Select左别名洌名，右别名洌 名 from左 表 as左另U名 right join 右表 as 右另U名 on 左别名洌名-右别名.列名利用现有 表创建新 表a, 表存在1. 完全拷贝:insert into 目标表 select * from 源表2. 部分拷贝:i

22、nsert into 目标表（列名） select 列名 from 源 表b, 表不存1.完全拷贝：select * into 新表 from 源 表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-2d. 使用 列别名:select stui_id, stu_name as 学生姓名 ” from stude nt;2

23、. 部分拷贝:select列名into 新表from源表3. 拷贝表结构：select * in to 新表 from 源表Where仁2利用把A 表数据， 同步B表方法1：update目标表 set 列=a.列from 源表 a where a.关联歹 U = 目标表.关联列方法2:update b set b列 = a. 列from 源表a ,目 标表 b where a.关联 列=b.关联列方法3:update目标表 set 列=a.列from 源 表a,目标表 b where a.关联歹 U = b. 关联列方法4:update目标表 set 目标表洌=a.列 from 源表 a ,目

24、标 表 b where a.关联列=b.关联列方法5:update目标表 set目 标表.列 = a.列 from 源表 a where a.关联 列=目标表.关联列方法6:通过关联查询语句生成update语句update 目标表 a,源表 b seta. 歹U =b.歹U where a.关联歹U =b. 关联列update 目标 表a,源表b set a列=b. 歹U where a. 关联列 =b.关联列数据操纵 语言(DML)a.创建一个新表createtablestudent(stu idint,a.创建一个新表create table student(stu_id int, stu

25、 namevarchar2(20)stu_ name varchar（20）b. 修改表中的列alter table 表名 alter colu mn 列名新数据库类型大小c. 添加表中的列alter table 表名 Add 列名数据库类型d. 删除表中的列alter table 表名 drop column列名e. 删除表中的记录 而 不删除表的结构trun cate table stude ntf. 只是查看表的结构g. 删除表中的数据及 表的结构drop table stude nt;tablespace lijiaob. 修改表中的列altertablestude ntmodify(

26、stu_ name varchar2(40)c. 添加表中的列alter table student add(stu_age int)d. 删除表中的列alter table stude nt drop colu mn stu_agee. 删除表中的记录而不删除表的结构 truncate table studentf. 只是查看表的结构desc stude ntg. 删除表中的数据及表的结构drop table stude nt;事务控制 语言 事务：是 单个的工 作单元 将多条相 关的语句 一起全部 执行（必 须是相关 的）执行 一系列的 操作a. commit命令（用于提交并结束事务处理）

27、commitb. savepoint 保 存点类 似于标记它将很长的事务处理划分 为较小的部分他们用来标记事务中可以应用回滚的点savepoint savepo in t_idc. rollbace work ,rollbackd. 回滚到某个保存点rollback to savepo intOracle 中 特有的伪 列（伪列 就像数据 库中的一 个表列， 但实际上 并未存储 在表中， 伪列只能 查询）不存在a. rowid（可以唯一的标识数据 库中的一行）b. rownum （代表行的序号）row num 不跟 ,=, = 操作使用定一个局部变量Declare 变量名DataType为变量

28、赋 值Set 变量名=值 或Select 变量名=值带查询语Select 变量名=列句为变量名 from 表明 where赋值列名=条件常用的全error 最后一个局变量T-SQL错误号（用户不identity 最后一次能定义的插入的标识值变量）Servername 本地 服务器的名称输出语句Print 值只出现在 查询分析器中显示RaisError（值,16,1）能在前台用异常扑捉逻辑控制If (条件)begin enda)ifcon diti onthe n语句If（ 条 件 ）exp_bodybegi n endelseend if;begi n endb)ifcon diti onth

29、e n判断存在exp_body时If exists(select * from 表名 where列名 =elseif;exp_bodyend判断不存值）c)ifcon diti onthe n在时begin .endexp_bodyIf not exists(select *elsifcon diti onthe nfrom 表名 where 歹Uexp_body名=值)begin.elseexp_bodyendendif;循环控制While(true) begin 1) loop con diti onend loop语句end2) whilecon diti onLoopexp_bodyend loop3)for varable in REVERSE value1.value2 ( varable 变量不需声明,.是范围连接符）loopexp_bodyendloopCasea) select 另U名=casea) select case 歹U名whe n 条when 值the n件then值值when 值the nwhen条件then值值else 值end from表b） select case 歹U名when 值 then 值when 值 then 值else 值endfrom 表else 值 end case from表明b） select cas