無標題文件

操作	SQLServer	Oracle	Mysql
查看表結構	exec sp_help 表名	desc 表名	desc 表名或 describe 表名或
查看表結構	exec sp_help 表名	在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 表名 Add 列名資料庫類型	alter table test add column name varchar(10); --添加表列
刪除表中一列	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 表明 modify(列名資料類型)	\|\|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 表明add primary key (列名)
刪除主鍵約束			alter table 表名 drop primary key
刪除主鍵約束			alter table 表名 drop primary key
添加唯一約束	alter table 表名	完全一樣	alter table 表名 add unique (列名)
添加唯一約束	add constraint 唯一約束名 unique (列名)	完全一樣	alter table 表名 add 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";
刪除資料庫使用者	exec sp_revokedbaccess ‘資料庫使用者’	drop user HDEAM_SBFF cascade;	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
時間和時間戳記	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