SQLserverOracleMysql語(yǔ)法與用法對(duì)比(over)

1、操作SQLServerOracleMysql查看表纟口 構(gòu)exec sp_help 表名desc表名desc 表名 或 describe 表名或showcolu mns from 表名；修改數(shù)據(jù) 庫(kù)名稱exec sp_renamedb 舊 數(shù)據(jù)庫(kù)名新數(shù)據(jù)庫(kù) 名，不詳修改表名exec sp_rename 舊表 明新表明rename 舊表名 to 新表名修改列名exec sp_rename 表.舊列名新列名alter table 表名renamecolumn舊列名to新列名刪除數(shù)據(jù)庫(kù)drop database 數(shù)據(jù)庫(kù) 名不詳添加表中 一列alter table 表名 Add列名數(shù)據(jù)庫(kù)類型alter

2、 table表明add（列名數(shù)據(jù)類型）或alter table表名 Add列名 數(shù)據(jù)庫(kù)類型刪除表中一列alter table 表名 drop column列名alter table 表名 drop column 歹y名修改表現(xiàn) 有列alter table 表名 alter colu mn列名新數(shù)據(jù)庫(kù)類型大小alter table表明 modify（列名數(shù)據(jù)類型）刪除約束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 名）添加默認(rèn) 約束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注意:默認(rèn)和加on update action 表示更新受 限力口 on update cascade 表示更新不受限，多項(xiàng)操作時(shí)用逗號(hào)隔開完全一樣添加索引 約束alter table 表 名 add index 索引名（列 名）添加普通索 引：create in dex 索引名ON 表名（列名） 添加唯一索 引：create unique 索引名ON 表名（列名）刪除索引drop in dex 索引名 on 表名alter table 表 名dr

5、opindex 索引 名創(chuàng)建登陸 帳戶windows 身 份:exec sp gra ntlog Win dows域名密碼sql 身份： exec sp_addlogin 登陸帳 戶密碼創(chuàng)建數(shù)據(jù) 庫(kù)用戶use數(shù)據(jù)庫(kù)名Windwos 身份：Exec sp_gra ntdbaccess windows域名密碼 數(shù)據(jù)庫(kù)用戶名Sql 身份：Exec sp_gra ntdbaccess 登 陸帳戶數(shù)據(jù)庫(kù)用戶 名，向數(shù)據(jù)庫(kù) 用戶授權(quán)use數(shù)據(jù)庫(kù)名Grant權(quán)限 on表 明to數(shù)據(jù)庫(kù)用戶刪除權(quán)限r(nóng)evoke權(quán)限 on 表 名from數(shù)據(jù)庫(kù)用 戶刪除數(shù)據(jù)庫(kù)用戶exec sp_revokedbaccess 數(shù)據(jù)

6、庫(kù)用戶刪除登陸 帳戶windows 身 份:exec sp_droplogin 登陸帳 戶名sql 身份: exec sp_revokelogin 登陸 帳戶名移動(dòng)數(shù)據(jù) 庫(kù)1分離數(shù)據(jù)庫(kù)：Exec sp_detach_db 數(shù)據(jù)庫(kù)名2附加數(shù)據(jù)庫(kù)：Exec sp_attach_db 數(shù)據(jù)庫(kù)名，filename仁主數(shù) 據(jù)文件路徑，filename2=日志 文件路徑數(shù)據(jù)庫(kù)的信息查詢查看數(shù)據(jù)庫(kù)的所有對(duì)象：Select* fromsysobjects查詢數(shù)據(jù)庫(kù) select * fromsysdatabases注意:下面都是通過（select * from對(duì)象）來查看信息 觸發(fā)器 user_trigger

7、s過程 user_procedures 查看源代碼user source查看所有數(shù)據(jù) 庫(kù)showdatabases;查看庫(kù)所有表show tables;俵）查看表結(jié)構(gòu)和屬性Exec Sp_help 表名查看所有數(shù)據(jù)庫(kù)Exec sp_databases查看數(shù)據(jù)庫(kù)的位置和屬性 Exec sp_helpdb數(shù)據(jù)庫(kù)名查看表中的約束execsp_helpc on stra int表名查看表的索引execsp_helpindex 表查看系統(tǒng)中的儲(chǔ)畜過exec查看數(shù)據(jù)庫(kù)對(duì)象user_objects查看錯(cuò)誤信息 show errors 查看索引信息 user_ in dexes 查看分區(qū)索引 user_ in

8、 d_partitio ns查看有關(guān)基于列創(chuàng)建的索引user_i nd_colu mns查看表空間-user_tablespaces查看序歹 U - user_seque nces 查看同義詞-user_i nd_colu mns查看用戶表信息-user_tables程查看用戶所有的表信息:user_all_tablesspstoredprocedures儲(chǔ)蓄過程/空查看表結(jié)構(gòu) 和屬性u(píng)sein formatio n_s chema;select * from colu mns wheretable_ name= 表名查看表源代 碼showcreate table 表名；查看存儲(chǔ)過 程源代sh

9、owcreate procedure 過程名查看視圖源 代碼 showcreate procedure 過程名查看視圖資 代碼 showcreateVIEW 視圖 名查看表的索弓丨 show index from表名查看表的索 弓丨 show keys from表明查看過程，函數(shù)，視圖，Exec sp_helptext 對(duì)查看數(shù)據(jù)庫(kù)所有登陸select*from查看數(shù)據(jù)庫(kù)所有數(shù)據(jù)select * from sysusers時(shí)間datetimesysdateNOW()時(shí)間戳timestamp字符串varchar(20)varchar2(20)獲取當(dāng)前 系統(tǒng)時(shí)間和時(shí)間戳getdate()sysda

10、te數(shù)據(jù)插入不一定要指定具體列 名不一定要指定具體列名必須指定具體列名標(biāo)識(shí)identity （標(biāo)識(shí)的開始 值，標(biāo)識(shí)種右）create seque nee 序歹 U名1.序列名.nextval獲取下一個(gè)序列的值2.序列名.currval獲取當(dāng)前的 序列的值物理文件 個(gè)數(shù)主數(shù)據(jù)文件:*.mdf =1次數(shù)據(jù)文件:*.ndf =0 日志文件：*.log =1數(shù)據(jù)文件:*.dbf =1日志文件：*.log =1字符函數(shù)chari ndex(t_char,s _char,1)從s字符串中 的1位置開始查找t字符串所在的下標(biāo)(默 認(rèn)從1開始)substri ng(char,pos,le n) 從pos位置開

11、始截取 lenlen(char)獲取字符串 的長(zhǎng)度lower(char)把字符串 全部轉(zhuǎn)換成小寫 upper(char)把字符串 全部轉(zhuǎn)換成大寫 ltrim(char)清除左邊的 空格rtrim(char)清楚右邊的 空格left(char,3)從左邊返 回指定長(zhǎng)度的字符串 right(char,3)從右邊返 回指定長(zhǎng)度的字符串 replace(s_char,t_char, n _char)將s中的t替換 成n字符串stuff( BCDEFG ,3, 我的音樂我的世界 返回：A我的音樂我 的世界EFG 在一個(gè)字符串中，刪 除從2位置開始指定 長(zhǎng)度為3的字符串， 并在該位置插入一個(gè) 新的字符串i

12、nstr(char ,search char)查找第 一個(gè)字符出現(xiàn)的位置索引從1開始substr(char, pos, len) 截取字符 串length(char)返回字符串的長(zhǎng) 度lower(char)轉(zhuǎn)換為小寫 upper(char)轉(zhuǎn)換為大與 trim()截取左右空格 initcap(char) 首字母大與 ltrim(char,set)左剪裁 rtrim(char,set)右剪裁 replace(char oldchar, newchar) 字符串替換concat(char1, char2)連接字符 串cha(67)根據(jù)ASCII碼返回對(duì) 應(yīng)的字符lpad()和rpad()在字符串的

13、左 邊或右邊添加?xùn)|西需要3個(gè)參數(shù)第一個(gè)是字符 串第二個(gè)是返回值的總長(zhǎng)度 ， 第三個(gè)是用來填充的字符轉(zhuǎn)換函數(shù)cast(值 as varchar)不需扌曰疋長(zhǎng)度convert (varchar(20),值)必須指定長(zhǎng)度to_char(d| n, fmt)將數(shù)字或日期 轉(zhuǎn)換為指定格式的字符串 to_date(char,frm)將 char 或 varchar轉(zhuǎn)換為日期數(shù)據(jù)類型to_n umber()將包含數(shù)字的字 符轉(zhuǎn)換為number數(shù)據(jù)類型其他函數(shù)abs(-1)- 1 取絕對(duì)值ceiling(43.5) - 44 取上界最小整數(shù)floor(43.5)- 43 取下界最大整數(shù)power(5.2) -

14、 25 取 幕rou nd(4.34,1) -4.3 四 舍五入，保留一位小 數(shù)sig n(-43) - -1 正數(shù)返回1，負(fù)數(shù)返回-1sort(9) - 3 求平方根 host_name()獲取計(jì) 算機(jī)名稱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;分組函數(shù)(聚合函數(shù))avg(列名)求這列的 平均值min(列名)求這列的 最小值max(列名)求這列的 最大值co

15、u nt(*) 統(tǒng)計(jì)所有的行包括重復(fù)值和空 值count(列名)統(tǒng)計(jì)指定列中非空值的個(gè)數(shù)count(distiinct 列 名)統(tǒng)計(jì)不是重復(fù)值的個(gè) 數(shù)group by 右句用于將 信息表劃分為組，按組 進(jìn)行聚合運(yùn)算avg(列名)求這列的平均值 min(列名)求這列的最小值 max(列名)求這列的最大值 count(*)統(tǒng)計(jì)所有的行包括重復(fù)值和空值count(列名)統(tǒng)計(jì)指定列中非 空值的個(gè)數(shù)count(distinct列名)統(tǒng)計(jì)不是 重復(fù)值的個(gè)數(shù)group by右句用于將信息表劃 分為組，按組進(jìn)行聚合運(yùn)算日期函數(shù)getdate()返回當(dāng)前系統(tǒng)時(shí)間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)返回給指 定的日期加上指定的月數(shù)后的日期值selectadd_ mon ths(sysdate,2)fromdual;b. months_between(d,d)返回 2個(gè)日期之間的月數(shù)select mon ths_betwee n(date2005-05-06, date 2005-9-01)000）返回指定日期的 指定部分的字符串格 式datepart（day,01/15/2000）返回指定日期部

17、分的整數(shù)形式from dual;c. Iast_day(d)返回指定日期當(dāng)月的最后一天的日期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; 返回 最靠近的一 個(gè)星期日e. next_

18、day(d, day) 返回指定的下一個(gè)星期幾的日期select n ext_day(sysdate,星 期二)from dualf. trunc語(yǔ)法與 round相同區(qū) 別:trunc是只舍不入g. extract提取日期時(shí)間類型中 的特定部分連接操作 符+|連接字符串用于將2個(gè)或多個(gè)字符串合并成一個(gè)字符串集合查詢 操作（兩個(gè)表 中的數(shù)據(jù) 類型和列 名必須相同）a. unionunion操作符返回2個(gè)查詢選定的所有不重復(fù)的行 selectorder nofromorder_master unionselectorder no from order_detail;b. union all合并2

19、個(gè)查詢選定 的所有行包括重復(fù)的行c. intersect 返回2個(gè)查詢都有 的行d. minus第1個(gè)查詢?cè)诘?2個(gè) 查詢中不存在的數(shù)據(jù)連接查詢（n個(gè)表， 至少有n-1個(gè)條 件）原理： 內(nèi)連：1內(nèi)連接：（查詢出條件 匹配的所有行）1） Select左別名.列 名，右別名.列名 from 左 表as左另寸 名 inner join 右表 as右別名on左別名.完全一樣連接分類：1內(nèi)連接： 條件2外連接1）左外連接：left join或 left outer join1對(duì)1左外連：列名=右別名列名2） Select左表名列 名，右表名列名 from 左 表,右表 where左表名.列名 - 右表名

20、.列名2左外連接:（返回左表 的所有行，如果右表沒 有與左表匹配的行則 全部用null代替,存在 則顯示具體數(shù)據(jù)，顯示 出來的總行數(shù)由左表 決定）Select左別名.列名， 右別名.列名 from 左表 as左別名left join 右表 as 右 別名on左別名.列名=右別名.列名3右外連接:（返回右表 的所有行，如果左表沒 有與右表匹配的行則 全部用null代替,存在 則顯示具體數(shù)據(jù)，顯示 出來的總行數(shù)由右表 決定）Select左別名.列名， 右別名.列名 from 左表 as左別名right join 右表 as 右別名on左別名.列 名=右別名洌名2）右外連接:right join 或

21、 right outer join3）完整外連接：Full join或 Full outer join3交叉連接:from4.交叉連接:（返回左右表的所 有行,如果左表沒有與右表匹 配的行則全部用null代替，如果右表沒有與左表匹配的行則 全部用null代替，存在則顯示 具體數(shù)據(jù)，顯示出來的總行數(shù)- 左表和右表行數(shù)和決定） Select左別名洌名，右別名洌 名 from左 表 as左另U名 right join 右表 as 右另U名 on 左別名洌名-右別名.列名利用現(xiàn)有 表創(chuàng)建新 表a, 表存在1. 完全拷貝:insert into 目標(biāo)表 select * from 源表2. 部分拷貝:i

22、nsert into 目標(biāo)表（列名） 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. 只拷貝一個(gè)空表（根據(jù)一個(gè)假 條件）create table temp as select * from 表 where 1-2d. 使用 列別名:select stui_id, stu_name as 學(xué)生姓名 ” from stude nt;2

23、. 部分拷貝:select列名into 新表from源表3. 拷貝表結(jié)構(gòu)：select * in to 新表 from 源表Where仁2利用把A 表數(shù)據(jù)， 同步B表方法1：update目標(biāo)表 set 列=a.列from 源表 a where a.關(guān)聯(lián)歹 U = 目標(biāo)表.關(guān)聯(lián)列方法2:update b set b列 = a. 列from 源表a ,目 標(biāo)表 b where a.關(guān)聯(lián) 列=b.關(guān)聯(lián)列方法3:update目標(biāo)表 set 列=a.列from 源 表a,目標(biāo)表 b where a.關(guān)聯(lián)歹 U = b. 關(guān)聯(lián)列方法4:update目標(biāo)表 set 目標(biāo)表洌=a.列 from 源表 a ,目

24、標(biāo) 表 b where a.關(guān)聯(lián)列=b.關(guān)聯(lián)列方法5:update目標(biāo)表 set目 標(biāo)表.列 = a.列 from 源表 a where a.關(guān)聯(lián) 列=目標(biāo)表.關(guān)聯(lián)列方法6:通過關(guān)聯(lián)查詢語(yǔ)句生成update語(yǔ)句update 目標(biāo)表 a,源表 b seta. 歹U =b.歹U where a.關(guān)聯(lián)歹U =b. 關(guān)聯(lián)列update 目標(biāo) 表a,源表b set a列=b. 歹U where a. 關(guān)聯(lián)列 =b.關(guān)聯(lián)列數(shù)據(jù)操縱 語(yǔ)言(DML)a.創(chuàng)建一個(gè)新表createtablestudent(stu idint,a.創(chuàng)建一個(gè)新表create table student(stu_id int, stu

25、 namevarchar2(20)stu_ name varchar（20）b. 修改表中的列alter table 表名 alter colu mn 列名新數(shù)據(jù)庫(kù)類型大小c. 添加表中的列alter table 表名 Add 列名數(shù)據(jù)庫(kù)類型d. 刪除表中的列alter table 表名 drop column列名e. 刪除表中的記錄 而 不刪除表的結(jié)構(gòu)trun cate table stude ntf. 只是查看表的結(jié)構(gòu)g. 刪除表中的數(shù)據(jù)及 表的結(jié)構(gòu)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. 刪除表中的記錄而不刪除表的結(jié)構(gòu) truncate table studentf. 只是查看表的結(jié)構(gòu)desc stude ntg. 刪除表中的數(shù)據(jù)及表的結(jié)構(gòu)drop table stude nt;事務(wù)控制 語(yǔ)言 事務(wù)：是 單個(gè)的工 作單元 將多條相 關(guān)的語(yǔ)句 一起全部 執(zhí)行（必 須是相關(guān) 的）執(zhí)行 一系列的 操作a. commit命令（用于提交并結(jié)束事務(wù)處理）

27、commitb. savepoint 保 存點(diǎn)類 似于標(biāo)記它將很長(zhǎng)的事務(wù)處理劃分 為較小的部分他們用來標(biāo)記事務(wù)中可以應(yīng)用回滾的點(diǎn)savepoint savepo in t_idc. rollbace work ,rollbackd. 回滾到某個(gè)保存點(diǎn)rollback to savepo intOracle 中 特有的偽 列（偽列 就像數(shù)據(jù) 庫(kù)中的一 個(gè)表列， 但實(shí)際上 并未存儲(chǔ) 在表中， 偽列只能 查詢）不存在a. rowid（可以唯一的標(biāo)識(shí)數(shù)據(jù) 庫(kù)中的一行）b. rownum （代表行的序號(hào)）row num 不跟 ,=, = 操作使用定一個(gè)局部變量Declare 變量名DataType為變量

28、賦 值Set 變量名=值 或Select 變量名=值帶查詢語(yǔ)Select 變量名=列句為變量名 from 表明 where賦值列名=條件常用的全error 最后一個(gè)局變量T-SQL錯(cuò)誤號(hào)（用戶不identity 最后一次能定義的插入的標(biāo)識(shí)值變量）Servername 本地 服務(wù)器的名稱輸出語(yǔ)句Print 值只出現(xiàn)在 查詢分析器中顯示RaisError（值,16,1）能在前臺(tái)用異常撲捉邏輯控制If (條件)begin enda)ifcon diti onthe n語(yǔ)句If（ 條 件 ）exp_bodybegi n endelseend if;begi n endb)ifcon diti onth

29、e n判斷存在exp_body時(shí)If exists(select * from 表名 where列名 =elseif;exp_bodyend判斷不存值）c)ifcon diti onthe n在時(shí)begin .endexp_bodyIf not exists(select *elsifcon diti onthe nfrom 表名 where 歹Uexp_body名=值)begin.elseexp_bodyendendif;循環(huán)控制While(true) begin 1) loop con diti onend loop語(yǔ)句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