操作 | SQLServer | Oracle | Mysql |
查看表結構 | exec sp_help 表名 | desc 表名 | desc 表名 或 describe 表名 或 |
在command window看 | 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 test add column name varchar(10); --添加表列 |
alter table 表名 Add 列名 資料庫類型 | |||
刪除表中一列 | 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 表名 | 完全一樣 | alter table 表明add primary key (列名) |
add constraint 主鍵約束名 primary key (列名) | |||
刪除主鍵約束 | alter table 表名 drop primary key | ||
添加唯一約束 | alter table 表名 | 完全一樣 | alter table 表名 add unique (列名) |
add constraint 唯一約束名 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功能變數名稱\密碼’ | SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword'); | |
sql身份: exec sp_addlogin ‘登陸帳戶’,’密碼’ | |||
創建資料庫使用者 | use 資料庫名 | create user HDEAM_TYMB | mysql>insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_sub |
Windwos身份: Exec sp_grantdbaccess ‘windows功能變數名稱\密碼’,’資料庫用戶名’ | identified by "" | ject) values("localhost","pppadmin",password("passwd"),'','',''); | |
Sql身份: Exec sp_grantdbaccess ‘登陸帳戶’,’資料庫用戶名’ | default tablespace HDEAM_TYMB | CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456'; | |
temporary tablespace TEMP | CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456'; | ||
profile DEFAULT; | CREATE USER 'pig'@'%' IDENTIFIED BY '123456'; | ||
CREATE USER 'pig'@'%' IDENTIFIED BY ''; | |||
CREATE USER 'pig'@'%'; | |||
向資料庫使用者授權 | use 資料庫名 | -- Grant/Revoke role privileges | grant select,insert,update,delete on *.* to test1@“%” Identified by “abc”; |
Grant 許可權 [on 表明] to 資料庫使用者 | grant dba to HDEAM_TYMB with admin option; | grant all privileges on phplampDB.* to phplamp@localhost identified by '1234'; | |
-- Grant/Revoke system privileges | mysql>flush privileges; | ||
grant unlimited tablespace to HDEAM_TYMB with admin option; | |||
刪除許可權 | revoke 許可權 [on 表名] from 資料庫使用者 | REVOKE SELECT ON *.* FROM 'pig'@'%'; | |
刪除資料庫使用者 | 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.分離資料庫: | exp hdeam_product/d3B68Apk29v34Dj@orcl file=E:/tymb.dmp log=E:/tymb.log | mysqldump -h localhost -u root -pmysql oa >d:\oa.sql |
Exec sp_detach_db 資料庫名 | mysql -h localhost -u root -p jira<d:\jira.sql | ||
2.附加資料庫: | imp HDEAM_LHSH/HDEAM_LHSH@orcldev file=E:\TYMBHDEAM_BAK_2013-03-25.dmp full=Y; | ||
Exec sp_attach_db 資料庫名, | |||
@filename1=’主資料檔案路徑’, | |||
@filename2=’日誌檔路徑’ | |||
資料庫的資訊查詢 | 查看資料庫的所有物件:Select * from sysobjects | 注意: 下面都是通過( select * from 物件 ) 來查看資訊 | 查看所有資料庫 show databases; |
查詢資料庫 select * from sysdatabases (表) | 觸發器 user_triggers | 查看庫所有表 show tables; | |
查看表結構和屬性 Exec Sp_help 表名 | 過程 user_procedures | 查看表結構和屬性 | |
查看所有資料庫 Exec sp_databases | 查看原始程式碼 user_source | use information_schema; | |
查看資料庫的位置和屬性Exec sp_helpdb 數 據庫名 | 查看資料庫物件 user_objects | select * from columns where table_name=’表名’ | |
查看表中的約束exec sp_helpconstraint 表名 | 查看錯誤資訊 show errors | 查看表原始程式碼show create table 表名; | |
查看表的索引 exec sp_helpindex 表 | 查看索引資訊 user_indexes | 查看存儲過程源代show create procedure 過程名 | |
查看系統中的儲蓄過程: | 查看分區索引 user_ind_partitions | 查看視圖原始程式碼show create procedure 過程名 | |
exec _stored_procedures 儲蓄過程/空 | 查看有關基於列創建的索引 user_ind_columns | 查看視圖資代碼show create VIEW 視圖名 | |
查看 過程,函數,視圖,表的原始程式碼: | 查看表空間 -- user_tablespaces | 查看表的索引show index from 表名 | |
Exec sp_helptext 對象名 | 查看序列 -- user_sequences | 查看表的索引show keys from 表明 | |
查看資料庫所有登陸帳戶: | 查看同義詞 -- user_ind_columns | ||
select * from syslogins | 查看使用者表資訊 -- user_tables | ||
查看資料庫所有資料庫使用者: | 查看使用者所有的表資訊 user_all_tables | ||
select * from sysusers | |||
時間 | datetime | sysdate | NOW() |
時間戳記 | timestamp | ||
字串 | varchar(20) | varchar2(20) | |
獲取當前系統 | getdate() | sysdate | |
時間和時間戳記 | |||
資料插入 | 不一定要指定具體列名 | 不一定要指定具體列名 | 必須指定具體列名 |
標識 | identity (標識的開始值, 標識種右) | create sequence 序列名 | |
1.序列名.nextval 獲取下一個序列的值 | |||
2.序列名.currval 獲取當前的序列的值 | |||
物理檔個數 | 主資料檔案: *.mdf =1 | 資料檔案: *.dbf >=1 | |
次資料檔案:*.ndf >=0 | |||
日誌檔: *.log >=1 | 日誌檔: *.log >=1 | ||
字元函數 | charindex(t_char,s _char,1)從s字串中的1位置開始查找t字串所在的下標(默認從1開始) | instr(char ,search char) 查找第一個字元出現的位置 索引從1開始 | |
substring(char,pos,len)從pos位置開始截取len | |||
len(char) 獲取字串的長度 | substr(char, pos, len) 截取字串 | ||
lower(char) 把字串全部轉換成小寫 | length(char) 返回字串的長度 | ||
upper(char)把字串全部轉換成大寫 | lower(char) 轉換為小寫 | ||
ltrim(char)清除左邊的空格 | upper(char) 轉換為大寫 | ||
rtrim(char)清楚右邊的空格 | trim() 截取左右空格 | ||
left(char,3)從左邊返回指定長度的字串 | initcap(char) 首字母大寫 | ||
right(char,3)從右邊返回指定長度的字串 | ltrim(char,set) 左剪裁 | ||
replace(s_char,t_char,n_char)將s中的t替換成 | rtrim(char,set) 右剪裁 | ||
n字串 | replace(char oldchar, newchar) 字串替換 | ||
stuff(‘ABCDEFG’2,3,’我的音樂我的世界’) | concat(char1, char2) 連接字串 | ||
返回:A我的音樂我的世界EFG | cha(67) 根據ASCII碼返回對應的字元 | ||
在一個字串中,刪除從2位置開始指定長度為3的字串,並在該位置插入一個新的字串 | lpad()和rpad() 在字串的左邊或右邊添加東西 | ||
需要3個參數 第一個是字串 第二個是返回值的總長度, 第三個是用來填充的字元 | |||
轉換函數 | cast(值 as varchar) 不需指定長度 | to_char(d|n, fmt)將數位或日期轉換為指定格式的字串 | |
convert (varchar(20), 值) 必須指定長度 | to_date(char,frm)將char 或varchar 轉換為日期資料類型 | ||
to_number() 將包含數位的字元轉換為number資料類型 | |||
其他函數 | abs(-1) - >1 取絕對值 | nvl(ex1,ex2) | |
ceiling(43.5) -> 44取上界最小整數 | 如果ex1為null則返回ex2; | ||
floor(43.5) - >43 取下界最大整數 | 如果ex1不為null則返回ex1; | ||
power(5.2) - >25 取冪 | nvl2(ex1,ex2,ex3) | ||
round(4.34,1) ->4.3 四捨五入, 保留一位小數 | 如果ex1不為null則返回ex2 | ||
sign(-43) -> -1 正數返回1 ,負數返回-1 | 如果ex1為null則返回ex3 | ||
sort(9) -> 3 求平方根 | nullif(ex1,ex2) | ||
host_name() 獲取電腦名稱 | 如果ex1=ex2則返回null 否則返回ex1; | ||
分組函數(彙總函式) | avg(列名) 求這列的平均值 | avg(列名) 求這列的平均值 | |
min(列名) 求這列的最小值 | min(列名) 求這列的最小值 | ||
max(列名) 求這列的最大值 | max(列名) 求這列的最大值 | ||
count(*) 統計所有的行 包括重複值和空值 | count(*) 統計所有的行 包括重複值和空值 | ||
count(列名) 統計指定列中非空值的個數 | count(列名) 統計指定列中非空值的個數 | ||
count(distinct 列名) 統計不是重複值的個數 | count(distinct 列名) 統計不是重複值的個數 | ||
group by右句用於將資訊表劃分為組,按組進行聚合運算 | group by右句用於將資訊表劃分為組,按組進行聚合運算 | ||
日期函數 | getdate() 返回當前系統時間 | a. add_months(d,n) 返回給指定的日期加上指定的月數後的日期值 select add_months(sysdate,2) from dual; | |
dateadd(mm,4,’01/01/99’)返回指定日期上+指定部分後的日期 | b. months_between(d,d) 返回2個日期之間的月數select months_between (date '2005-05-06', date '2005-9-01') from dual; | ||
datediff(mm,’01/01/99’,’05/01/99’)返回兩日期間指定部分的值 | c. last_day(d) 返回指定日期當月的最後一天的日期select last_day(sysdate) from dual; | ||
datename(dw,’01/01/2000’)返回指定日期的指定部分的字串格式 | 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; 返回 最靠近的一個星期日 | ||
datepart(day,’01/15/2000’)返回指定日期部分的整數形式 | 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個查詢中不存在的資料 | |||
連接查詢 | 1.內連接:(查詢出條件匹配的所有行) | 完全一樣 | |
(n個表,至少有n-1個條件) | 1) Select 左別名.列名,右別名.列名 from 左 表 as 左別名 inner join 右表 as 右別名on 左別名.列名 = 右別名.列名 | 連接分類: | |
原理: | 2) Select 左表名.列名,右表名.列名 from 左 表 ,右表 where 左表名.列名 = 右表名.列名 | 1內連接: | |
內連: | 2左外連接:(返回左表的所有行,如果右表沒有與左表匹配的行則全部用null代替,存在則顯示具體資料,顯示出來的總行數由左表決定) | 條件 | |
1對1 | Select 左別名.列名,右別名.列名 from 左 表 as 左別名 left join 右表 as 右別名on 左別名.列名 = 右別名.列名 | 2外連接 | |
左外連: | 3右外連接:(返回右表的所有行,如果左表沒有與右表匹配的行則全部用null代替,存在則顯示具體資料,顯示出來的總行數由右表決定) | 1)左外連接: left join 或 left outer join | |
Select 左別名.列名,右別名.列名 from 左 表 as 左別名 right join 右表 as 右別名on 左別名.列名 = 右別名.列名 | 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,表存在 | a.完全拷貝: create table temp as select * from emp; | |
1.完全拷貝: insert into 目標表 select * from 源表 | b.拷貝一部分create table temp as select 列1,列2 from 表 | ||
2.部分拷貝: insert into 目標表 (列名) select 列名 from 源表 | c.只拷貝一個空表(根據一個假條件) | ||
b,表不存 | create table temp as select * from 表 where 1=2 | ||
1.完全拷貝: select * into 新表 from 源表 | d.使用列別名: select stu_id, stu_name as "學生姓名" from student; | ||
2.部分拷貝: select 列名 into 新表 from 源表 | |||
3.拷貝表結構: select * into 新表 from 源表 Where 1=2 | |||
利用把A表資料,同步B表 | 方法1: | update 目標表a, 源表b set a.列 =b.列 where a.關聯列 = b.關聯列 | update 目標表a, 源表b set a.列 =b.列 where a.關聯列 = b.關聯列 |
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語句 | |||
資料操縱語言(DML) | a.創建一個新表 | a.創建一個新表 | |
create table student(stu_id int, stu_name varchar(20)) | create table student(stu_id int, stu_name varchar2(20)) tablespace lijiao | ||
b.修改表中的列 | b.修改表中的列 | ||
alter table 表名 alter column 列名 | alter table student modify(stu_name varchar2(40)) | ||
新資料庫類型 大小 | c.添加表中的列 | ||
c.添加表中的列 | alter table student add(stu_age int) | ||
alter table 表名 Add 列名 資料庫類型 | d.刪除表中的列 | ||
d.刪除表中的列 | alter table student drop column stu_age | ||
alter table 表名 drop column 列名 | e.刪除表中的記錄 而不刪除表的結構truncate table student | ||
e.刪除表中的記錄 而不刪除表的結構truncate table student | f.只是查看表的結構 | ||
f.只是查看表的結構 | desc student | ||
g.刪除表中的資料及表的結構 | g.刪除表中的資料及表的結構 | ||
drop table student; | 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 | a) if condition then exp_body | |
If (條件) begin …end else begin … end | end if; | ||
b) if condition then exp_body | |||
判斷存在時 | If exists(select * from 表名 where 列名 = 值) | else exp_body end if; | |
begin …. end | c) if condition then exp_body | ||
判斷不存在時 | If not exists(select * from 表名 where 列名 = 值) begin …. end | 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 | a) select case 列名 | |
when 條件 then 值 | when 值 then 值 | ||
when 條件 then 值 | when 值 then 值 | ||
else 值 | else 值 | ||
end from 表 | end case from 表明 | ||
b) select case 列名 | b) select case | ||
when 值 then 值 | when 列名=值 then 值 | ||
when 值 then 值 | when 列名=值 then 值 | ||
else 值 | else 值 | ||
end from 表 | 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.資料類型長度(單位)的取值範圍
參考:https://blog.csdn.net/qq_39151274/article/details/114685416