【网学网提醒】：以下是网学会员为您推荐的SQLserver、Oracle、Mysql语法与用法对比(over)，希望本篇文章对您学习有所帮助。
操作查看表结构
SQ:LServerexecsp_help表名
Oracledesc表名
Mysqldesc表名或describe表名或showcolumnsfrom表名;
修改数据库名称修改表名修改列名删除数据库添加表中一列
execsp_renamedb‘旧数据库名’,’新数据库名’execsp_rename‘旧表明’,’新表明’execsp_rename‘表.旧列名’,’新列名’dropdatabase数据库名altertable表名Add列名数据库类型
不详
rename旧表名to新表名altertable表名renamecolumn旧列名to新列名不详
altertable表明据类型)或add(列名数
altertable表名Add列名数据库类型删除表中一列修改表现有列删除约束添加主键约束altertable表名dropcolumn列名altertable表名altercolumn列名新数据库类型大小altertable表名dropconstraint约束名altertable表名addconstraint主键约束名primarykey(列名)
altertable表名dropcolumn列名
altertable表明modify(列名数据类型)完全一样完全一样
altertable表明addprimarykey(列名)table表drop
删除主键约束
alter名
primarykey
添加唯一约束
altertable表名addconstraint唯一约束名unique(列名)
完全一样
altertable表名addunique(列名)
添加默认约束添加检查
altertable表名addconstraintdefault(值)for列名altertable表名
完全一样
完全一样
约束添加外键约束
addconstraintcheck(列名>10)altertable子表addconstraint外键约束名foreignkey(子表的列名)references主表(列名)onupdatecascade/onupdateaction注意:默认和加onupdateaction表示更新受限加onupdatecascade表示更新不受限,多项操作时用逗号隔开完全一样
添加索引约束
altertable表名addindex索引名(列名)添加普通索引：createindex索引名ON表名(列名)添加唯一索引：createunique索引名ON表名(列名)
删除索引
dropindex索引名on表名altertable表名dropindex索引名
创建登陆帐户
windows身份:execsp_grantlog‘windows
域名\密码’sql身份:execsp_addlogin‘登陆帐户’,’密码’创建数据库用户use数据库名Windwos身份:Execsp_grantdbaccess‘windows域名\密码’,’数据库用户名’Sql身份:Execsp_grantdbaccess‘登陆帐户’,’数据库用户名’use数据库名Grant权限[on表明]to数据库用户revoke权限[on表名]from数据库用户execsp_revokedbaccess‘数据库用户’windows身份:execsp_droplogin登陆帐户名sql身份:execsp_revokelogin登陆帐户名1.分离数据库:Execsp_detach_db数据库名2.附加数据库:Execsp_attach_db数据库名,@filename1=’主数据文件路径’,@filename2=’日志文件路径’查看数据库的所有对象:Select*f
romsysobjects查询数据库select*fromsysdatabases注意:下面都是通过(select*from对象)来查看信息触发器user_triggers过程user_procedures查看源代码user_source
查看所有数据库showdatabases;查看库所有表showtables;
向数据库用户授权删除权限
删除数据库用户删除登陆帐户
移动数据库
数据库的信息查询
(表)查看表结构和属性ExecSp_help表名查看所有数据库Execsp_databases查看数据库的位置和属性Execsp_helpdb数据库名查看表中的约束execsp_helpconstraint表名查看表的索引execsp_helpindex表查看系统中的储蓄过
查看数据库对象user_objects查看错误信息showerrors查看索引信息user_indexes查看分区索引user_ind_partitions查看有关基于列创建的索引user_ind_columns查看表空间-user_tablespaces查看序列--user_sequences查看同义词-user_ind_columns查看用户表信息-user_tables程看用户所有的表信息查user_all_tables：sp_stored_procedures储蓄过程/空
exec
查看表结构和属性useinformation_schema;select*fromcolumnswheretable_name=’表名’查看表源代码showcreatetable表名;查看存储过程源代showcreateprocedure过程名查看视图源代码showcreateprocedure过程名查看视图资代码showcreateVIEW视图名查看表的索引showindex
from表名
查看表的索引showkeys
from表明
查看过程,函数,视图,表的源代码:Execsp_helptext对象名查看数据库所有登陆帐户:select*fromsyslogins查看数据库所有数据库用户:select*fromsysusers时间时间戳字符串获取当前系统时间和时间戳数据插入标识datetimetimestampvarchar(20)getdate()varchar2(20)
sysdatesysdate
NOW()
不一定要指定具体列名identity(标识的开始值,标识种右)
不一定要指定具体列名createsequence序列名1.序列名.nextval获取下一个序列的值
必须指定具体列名
2.序列名.currval获取当前的序列的值物理文件个数字符函数主数据文件:*.mdf=1次数据文件:*.ndf>=0日志文件:*.log>=1charindex(t_char,s_char,1)从s字符串中的1位置开始查找t字符串所在的下标(默认从1开始)substring(char,pos,len)从pos位置开始截取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(‘ABCDEFG’2,3,’我的音乐我的世界’)返回：A我的音乐我的世界EFG在一个字符串中，删除从
2位置开始指定长度为3的字符串，并在该位置插入一个新的字符串cast(值asvarchar)不需指定长度convert(varchar(20),值)必须指定长度数据文件:*.dbf>=1日志文件:*.log>=1instr(char,searchchar)查找第一个字符出现的位置索引从1开始substr(char,pos,len)截取字符串length(char)返回字符串的长度lower(char)转换为小写upper(char)转换为大写trim()截取左右空格initcap(char)首字母大写ltrim(char,set)左剪裁rtrim(char,set)右剪裁replace(charoldchar,newchar)字符串替换concat(char1,char2)连接字符串cha(67)根据ASCII码返回对应的字符lpad()和rpad()在字符串的左边或右边添加东西需要3个参数第一个是字符串第二个是返回值的总长度,第三个是用来填充的字符
转换函数
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,负数返回-1sort(9)->3求平方根host_name()获取计算机名称avg(列名)求这列的平均值min(列名)求这列的最小值max(列名)求这列的最大值count(*)统计所有的行包括重复值和空值count(列名)统计指定列中非空值的个数count(distinct列名)统计不是重复值的个数groupby右句用于将信息表划分为组,按组进行聚合运算getdate()返回当前系统时间dateadd(mm,4,’01/01/99’)返回指定日期上+指定部分后的日期datediff(mm,’01/01/99’,’05/01/99’)返回两日期间指定部分的值datename(dw,’01/01/2nvl(ex1,ex2)如果ex1为null则返回ex2;如果ex1不为null则返回ex1;nvl2(ex1,ex2,ex3)如果ex1不为null则返回ex2如果ex1为null则返回ex3nullif(ex1,ex2)如果ex1=ex2则返回null否则返回ex1;
分组函数(聚合函数)
avg(列名)求这列的平均值min(列名)求这列的最小值max(列名)求这列的最大值count(*)统计所有的行包括重复值和空值count(列名)统计指定列中非空值的个数count(distinct列名)统计不是重复值的个数groupby右句用于将信息表划分为组,按组进行聚合运算
日期函数
a.add_months(d,n)返回给指定的日期加上指定的月数后的日期值selectadd_months(sysdate,2)fromdual;b.months_between(d,d)返回2个日期之间的月数selectmonths_between(date'2005-05-06',date'2005-9-01')
000’)返回指定日期的指定部分的字符串格式datepart(day,’01/15/2000’)返回指定日期部分的整数形式
fromdual;c.last_d
ay(d)返回指定日期当月的最后一天的日期selectlast_day(sysdate)fromdual;d.round(d,[fmt])返回日期值日期四舍五入为格式模型指定的单位selectround(date'2005-09-08','year')fromdual;返回2005-01-01selectround(date'2005-09-08','month')fromdual;返回2005-09-01selectround(date'2005-09-08','day')fromdual;返回最靠近的一个星期日e.next_day(d,day)返回指定的下一个星期几的日期selectnext_day(sysdate,'星期二')fromdualf.trunc语法与round相同区别:trunc是只舍不入g.extract提取日期时间类型中的特定部分||连接字符串用于将2个或多个字符串合并成一个字符串a.unionunion操作符返回2个查询选定的所有不重复的行selectordernofromorder_masterunionselectordernofromorder_detail;b.unionall合并2个查询选定的所有行包括重复的行c.intersect返回2个查询都有的行d.minus第1个查询在第2个查询中不存在的数据
连接操作符集合查询操作(两个表中的数据类型和列名必须相同)
+
连接查询(n个表,至少有n-1个条件)原理:内连:
1.内连接:(查询出条件匹配的所有行)1)Select左别名.列名,右别名.列名from左表as左别名innerjoin右表as右别名on左别名.
完全一样连接分类:1内连接:条件2外连接1)左外连接:leftleftouterjoin
join或
1对1左外连:
列名=右别名.列名2)Select左表名.列名,右表名.列名from左表,右表where左表名.列名=右表名.列名2左外连接:(返回左表的所有行,如果右表没有与左表匹配的行则全部用null代替,存在则显示具体数据,显示出来的总行数由左表决定)Select左别名.列名,右别名.列名from左表as左别名leftjoin右表as右别名on左别名.列名=右别名.列名3右外连接:(返回右表的所有行,如果左表没有与右表匹配的行则全部用null代替,存在则显示具体数据,显示出来的总行数由右表决定)Select左别名.列名,右别名.列名from左表as左别名rightjoin右表as右别名on左别名.列名=右别名.列名a,表存在1.完全拷贝:insertinto目标表select*from源表2.部分拷贝:insertinto目标表(列名)select列名from源表b,表不存1.完全拷贝:select*into新表from源表
2)右外连接:rightjoin或rightouterjoin3)完整外连接:Fulljoin或Fullouterjoin3交叉连接:from…4.交叉连接:(返回左右表的所有行,如果左表没有与右表匹配的行则全部用null代替,如果右表没有与左表匹配的行则全部用null代替,存在则显示具体数据,显示出来的总行数=左表和右表行数和决定)Select左别名.列名,右别名.列名from左表as左别名righ
tjoin右表as右别名on左别名.列名=右别名.列名
利用现有表创建新表
a.完全拷贝:createtabletempasselect*fromemp;b.拷贝一部分createtabletempasselect列1,列2from表c.只拷贝一个空表(根据一个假条件)createtabletempasselect*from表where1=2d.使用列别名:selectstu_id,stu_nameas"学生"fromstudent;
2.部分拷贝:select列名into新表from源表3.拷贝表结构:select*into新表from源表Where1=2利用把A表数据,同步B表方法1：update目标表set列=a.列from源表awherea.关联列=目标表.关联列方法2：updatebsetb.列=a.列from源表a,目标表bwherea.关联列=b.关联列方法3：update目标表set列=a.列from源表a,目标表bwherea.关联列=b.关联列方法4：update目标表set目标表.列=a.列from源表a,目标表bwherea.关联列=b.关联列方法5：update目标表set目标表.列=a.列from源表awherea.关联列=目标表.关联列方法6：通过关联查询语句生成update语句update目标表a,源表bseta.列=b.列wherea.关联列=b.关联列update目标表a,源表bseta.列=b.列wherea.关联列=b.关联列
数据操纵语言(DML)
a.创建一个新表createtablestudent(stu_idint,
a.创建一个新表createtablestudent(stu_idint,stu_namevarchar2(20))
stu_namevarchar(20))b.修改表中的列altertable表名altercolumn列名新数据库类型大小c.添加表中的列altertable表名Add列名数据库类型d.删除表中的列altertable表名dropcolumn列名e.删除表中的记录而不删除表的结构truncatetablestudentf.只是查看表的结构g.删除表中的数据及表的结构droptablestudent;事务控制语言事务:是单个的工作单元.将多条相关的语句一起全部执行(必须是相关的)执行一系列的操作Oracle中特有的伪列(伪列就像数据库中的一个表列,但实际上并未存储在表中,伪列只能查询)定一个局部变量不存在
tablespacelijiaob.修改表中的列altertablestudentmodify(stu_namevarchar2(40))c.添加表中的列altertablestudentadd(stu_ageint)d.删除表中的列altertablestudentdropcolumnstu_agee.删除表中的记录而不删除表的结构truncatetablestudentf.只是查看表的结构descstudentg.删除表中的数据及表的结构droptablestudent;
a.commit命令(用于提交并结束事务处理)commitb.savepoint保存点类似于标记它将很长的事务处理划分为较小的部分他们用来标记事务中可以应用回滚的点savepointsavepoint_idc.rollbacework,rollbackd.回滚到某个保存点rollbacktosavepoint
a.rowid(可以唯一的标识数据库中的一行)b.rownum(代表行的序号)rownum不跟>,=,>=操作使用
Declare@变量名DataType
为变量赋值带查询语句为变量赋值常用的全局变量(用户不能定义的变量)输出语句
Set@变量名=值或Select@变量名=值Select@变量名=列名from表明where列名=条件@@error最后一个T-SQL错误号@@identity最后一次插入的标识值@@Servername本地服务器的名称Print‘值’只出现在查询分析器中显示RaisError(‘值’,16,1)能在前台用异常扑捉If(条件)begin…endIf(条件)begin…endelsebegin…endIfexists(select*from表名where列名=值)begin….endIfnotexists(select*from表名where列名=值)begin….endWhile(true)begin…enda)ifexp_bodyb)ifexp_bodyelseif;c)ifexp_bodyelsifexp_bodyelseif;conditionendif;conditionexp_bodyconditionconditionexp_bodythen
逻辑控制语句
thenendthenthenend
判断存在时判断不存在时
循环控制语句
1)loopconditionendloop2)whileconditionLoopexp_bodyendloop3)forvarablein[REVERSE]value1...value2(varable变量不需声明,...是范围连接符)loopexp_bodyendloopa)selectcase列名when值then值when值then值
Case
a)select别名=casewhen条件then值when条件then值
else值end表b)selectcase列名then值when值then值名=值when值then值else值endfrom表属性类型:不存在from表then值fromfrom表明
else值endcase
b)selectcasewhen列名=值when列else值endcase
1.列类型:%TYPEvarabletableName.columnName%TYPE;2.行类型:%ROWTYPEvarabletableName%ROWTYPE;3.使用行类型:varable.列名;1)自定义异常:a)定义异常:(位declare和begin之间)DECLAREvarableEXCEPTION;b)显示引发异常(为begin和end之间):RAISEvarable;2)处理预定义异常:(位begin和end之间)EXCEPTIONwhenToo_Many_ROWSthenDBMS_OUTPUT.PUT_LINE('返回多行');whenothersthenDBMS_OUTPUT.PUT_LINE('全部处理');3)引发应用程序错误:
异常处理
RaisError(‘异常信息’,16,1)能在前台用异常扑捉
RAISE_APPLICATION_ERROR(error_number,error_message);子查询(原理:在一个查询的基础上进行再次查询)相关子查询:(重复子查询,不能单独执行)切套右查询(只执行一次,能单独执行,查询原理:从外(外sql一次查出所有的行一次一行的传递给里面的sql)到里把外不查出来的值传递给里面来)注意:查询到结果可以作为表来使用但必须指定别名能用连接查询时一般不用右查询右查询功能(效率低)>连接查询(效率高)右查询能用语查询所有查询,而连接查询有些不能.右查询有连接查询不能的功能只能适合于返回多行一列只能适合于返回一行一列Where只能出现在Groupby分组之前,Having不能单独使用如果没有groupby就没有Ha
vingOrderbydesc/asc一样切套子查询:Select*fromAuthorBookawhereCopyRight=(selectMax(CopyRight)fromAuthorBookwhereBookName=a.BopokName)
查询性能的规则
完全一样
In和notin=,>,<,=>,=<,1=分组:Groupby
完全一样完全一样完全一样
排序
完全一样
