SQLserverOracleMysql語法與用法對比(over)

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

2、 table表明add（列名數(shù)據類型）或alter table表名 Add列名 數(shù)據庫類型刪除表中一列alter table 表名 drop column列名alter table 表名 drop column 歹y名修改表現(xiàn) 有列alter table 表名 alter colu mn列名新數(shù)據庫類型大小alter table表明 modify（列名數(shù)據類型）刪除約束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 索引 名創(chuàng)建登陸 帳戶windows 身 份:exec sp gra ntlog Win dows域名密碼sql 身份： exec sp_addlogin 登陸帳 戶密碼創(chuàng)建數(shù)據 庫用戶use數(shù)據庫名Windwos 身份：Exec sp_gra ntdbaccess windows域名密碼 數(shù)據庫用戶名Sql 身份：Exec sp_gra ntdbaccess 登 陸帳戶數(shù)據庫用戶 名，向數(shù)據庫 用戶授權use數(shù)據庫名Grant權限 on表 明to數(shù)據庫用戶刪除權限revoke權限 on 表 名from數(shù)據庫用 戶刪除數(shù)據庫用戶exec sp_revokedbaccess 數(shù)據

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

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

8、 d_partitio ns查看有關基于列創(chuàng)建的索引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表明查看過程，函數(shù)，視圖，Exec sp_helptext 對查看數(shù)據庫所有登陸select*from查看數(shù)據庫所有數(shù)據select * from sysusers時間datetimesysdateNOW()時間戳timestamp字符串varchar(20)varchar2(20)獲取當前 系統(tǒng)時間和時間戳getdate()sysda

10、te數(shù)據插入不一定要指定具體列 名不一定要指定具體列名必須指定具體列名標識identity （標識的開始 值，標識種右）create seque nee 序歹 U名1.序列名.nextval獲取下一個序列的值2.序列名.currval獲取當前的 序列的值物理文件 個數(shù)主數(shù)據文件:*.mdf =1次數(shù)據文件:*.ndf =0 日志文件：*.log =1數(shù)據文件:*.dbf =1日志文件：*.log =1字符函數(shù)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)查找第 一個字符出現(xiàn)的位置索引從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個參數(shù)第一個是字符 串第二個是返回值的總長度 ， 第三個是用來填充的字符轉換函數(shù)cast(值 as varchar)不需扌曰疋長度convert (varchar(20),值)必須指定長度to_char(d| n, fmt)將數(shù)字或日期 轉換為指定格式的字符串 to_date(char,frm)將 char 或 varchar轉換為日期數(shù)據類型to_n umber()將包含數(shù)字的字 符轉換為number數(shù)據類型其他函數(shù)abs(-1)- 1 取絕對值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，負數(shù)返回-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;分組函數(shù)(聚合函數(shù))avg(列名)求這列的 平均值min(列名)求這列的 最小值max(列名)求這列的 最大值co

15、u nt(*) 統(tǒng)計所有的行包括重復值和空 值count(列名)統(tǒng)計指定列中非空值的個數(shù)count(distiinct 列 名)統(tǒng)計不是重復值的個 數(shù)group by 右句用于將 信息表劃分為組，按組 進行聚合運算avg(列名)求這列的平均值 min(列名)求這列的最小值 max(列名)求這列的最大值 count(*)統(tǒng)計所有的行包括重復值和空值count(列名)統(tǒng)計指定列中非 空值的個數(shù)count(distinct列名)統(tǒng)計不是 重復值的個數(shù)group by右句用于將信息表劃 分為組，按組進行聚合運算日期函數(shù)getdate()返回當前系統(tǒng)時間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個日期之間的月數(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)返回指定日期當月的最后一天的日期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相同區(qū) 別:trunc是只舍不入g. extract提取日期時間類型中 的特定部分連接操作 符+|連接字符串用于將2個或多個字符串合并成一個字符串集合查詢 操作（兩個表 中的數(shù)據 類型和列 名必須相同）a. unionunion操作符返回2個查詢選定的所有不重復的行 selectorder nofromorder_master unionselectorder no from order_detail;b. union all合并2

19、個查詢選定 的所有行包括重復的行c. intersect 返回2個查詢都有 的行d. minus第1個查詢在第 2個 查詢中不存在的數(shù)據連接查詢（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代替,存在 則顯示具體數(shù)據，顯示 出來的總行數(shù)由左表 決定）Select左別名.列名， 右別名.列名 from 左表 as左別名left join 右表 as 右 別名on左別名.列名=右別名.列名3右外連接:（返回右表 的所有行，如果左表沒 有與右表匹配的行則 全部用null代替,存在 則顯示具體數(shù)據，顯示 出來的總行數(shù)由右表 決定）Select左別名.列名， 右別名.列名 from 左表 as左別名right join 右表 as 右別名on左別名.列 名=右別名洌名2）右外連接:right join 或

21、 right outer join3）完整外連接：Full join或 Full outer join3交叉連接:from4.交叉連接:（返回左右表的所 有行,如果左表沒有與右表匹 配的行則全部用null代替，如果右表沒有與左表匹配的行則 全部用null代替，存在則顯示 具體數(shù)據，顯示出來的總行數(shù)- 左表和右表行數(shù)和決定） Select左別名洌名，右別名洌 名 from左 表 as左另U名 right join 右表 as 右另U名 on 左別名洌名-右別名.列名利用現(xiàn)有 表創(chuàng)建新 表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 表數(shù)據， 同步B表方法1：update目標表 set 列=a.列from 源表 a where a.關聯(lián)歹 U = 目標表.關聯(lián)列方法2:update b set b列 = a. 列from 源表a ,目 標表 b where a.關聯(lián) 列=b.關聯(lián)列方法3:update目標表 set 列=a.列from 源 表a,目標表 b where a.關聯(lián)歹 U = b. 關聯(lián)列方法4:update目標表 set 目標表洌=a.列 from 源表 a ,目

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

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

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

28、賦 值Set 變量名=值 或Select 變量名=值帶查詢語Select 變量名=列句為變量名 from 表明 where賦值列名=條件常用的全error 最后一個局變量T-SQL錯誤號（用戶不identity 最后一次能定義的插入的標識值變量）Servername 本地 服務器的名稱輸出語句Print 值只出現(xiàn)在 查詢分析器中顯示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;循環(huán)控制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