Sql用戶(hù)操作

登錄

sqlplus是oracle自帶的工具，可以使用其命令登錄到oracle數(shù)據(jù)中，并執(zhí)行用戶(hù)編寫(xiě)的sql語(yǔ)句。

如果在cmd命令中出提示沒(méi)有sqlplus命令，那就說(shuō)明oracle環(huán)境變量沒(méi)有配置好。

使用sqlplus中，使用指定賬號(hào)，登錄到oracle數(shù)據(jù)庫(kù)中

sqlplus 用戶(hù)名/密碼

// 普通用戶(hù)登錄
sqlplus jd2010/密碼

// 管理員登錄
sqlplus system/root

//使用DBA的身份登錄，這種情況不需要密碼，但是這種情況需要當(dāng)操作系統(tǒng)的用戶(hù)有權(quán)限才行
sqlplus "/as sysdba"

查看當(dāng)前登錄用戶(hù)

show user

清屏操作、退出

// Windows系統(tǒng)中
$CLS
$cls

// Ubuntu系統(tǒng)
!clear

// 退出
exit

會(huì)話(huà)

會(huì)話(huà)也就是當(dāng)前得控制臺(tái)，開(kāi)啟兩個(gè)sql控制臺(tái)就是兩個(gè)不相同得會(huì)話(huà)

登錄成功，當(dāng)前默認(rèn)會(huì)話(huà)得語(yǔ)言是中文，可以通過(guò)命令修改當(dāng)前會(huì)話(huà)的語(yǔ)言環(huán)境，例如將當(dāng)前會(huì)話(huà)修改成英文環(huán)境

alter session set nls_language=english;

用戶(hù)

我們可以通過(guò)使用管理員賬號(hào)登錄后創(chuàng)建一個(gè)用戶(hù)，并設(shè)置密碼以及授權(quán)

// 創(chuàng)建用戶(hù)jd2010，設(shè)置密碼root
create user jd2010 identified by root;

// 將連接數(shù)據(jù)庫(kù)權(quán)限和資源管理權(quán)限授權(quán)
grant connect,resource to jd2010;

切換用戶(hù)

conn 用戶(hù)名/密碼

刪除用戶(hù)

drop user jd2010 cascade;

導(dǎo)入

• s_region，區(qū)域表：

• s_dept，部門(mén)表：

• s_emp， 員工表：

// 導(dǎo)入sql文件命令 @
@文件路徑
@table.sql

查看表結(jié)構(gòu)

desc 表名

Sql表操作

sql語(yǔ)句的分類(lèi)：

• DQL (Data Query Language)，數(shù)據(jù)查詢(xún)語(yǔ)言

  用于檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)，主要是SELECT語(yǔ)句

• DML (Data Manipulation Language)，數(shù)據(jù)操縱語(yǔ)言

  用于改變數(shù)據(jù)庫(kù)中的數(shù)據(jù)，主要是 INSERT,UPDATE,DELETE 語(yǔ)句

• DDL（Data Define Langage)，數(shù)據(jù)定義語(yǔ)言

  用來(lái)建立、修改、刪除數(shù)據(jù)庫(kù)對(duì)象，主要是 CREATE、ALTER、DROP、TRUNCATE 語(yǔ)句

• TCL (Transaction Control Language)，事務(wù)控制語(yǔ)言

  用于維護(hù)數(shù)據(jù)的一致性，主要是 COMMIT,ROLLBACK,SAVEPOINT 語(yǔ)句

• DCL（Data Control Language），數(shù)據(jù)控制功能

  用于執(zhí)行權(quán)限授予和權(quán)限收回操作，主要是 GRANT,REVOKE 語(yǔ)句

注意：DML語(yǔ)句需要事務(wù)的支持（產(chǎn)生事務(wù)），DDL語(yǔ)句會(huì)自動(dòng)提交事務(wù)

sql、sqlplus、pl/sql的區(qū)別：

• sql，結(jié)構(gòu)化的查詢(xún)語(yǔ)句，操作關(guān)系型數(shù)據(jù)庫(kù)的語(yǔ)言。
• sqlplus，oracle數(shù)據(jù)庫(kù)軟件自帶工具，可以接收用戶(hù)輸入的sql語(yǔ)句，然后將sql執(zhí)行結(jié)果顯示出來(lái)。
• pl/sql，程序化的sql語(yǔ)句，在sql語(yǔ)句的基礎(chǔ)上加入一定的邏輯操作，如if for等。

Select

注意，oracle中的表名、列名（字段名）、SQL語(yǔ)句等是不區(qū)分大小寫(xiě)的（大小寫(xiě)不敏感）

select語(yǔ)法：

select [distinct] *{col_name1,col_name2,..} from 表名;

1. []表示該部分可有可無(wú)。
2. *號(hào)表示所有列，不推薦寫(xiě)*。
3. col_name1，col_name2代表列名，如果有多個(gè)可以逗號(hào)分開(kāi)。

運(yùn)算

注意，select語(yǔ)句永遠(yuǎn)不會(huì)對(duì)原始數(shù)據(jù)進(jìn)行修改

例如，查看每個(gè)員工的員工id，名字和月薪漲100以后的年薪

select id,last_name,(salary+100)*12 from  s_emp

別名

select old_column [as] new_column_name from tb_name;

可以不使用as，通過(guò)空格就可以起別名

拼接

語(yǔ)法：

select col_name||'需要拼接的字符'||col_name from tb_name

注意，數(shù)據(jù)庫(kù)中的字符串，需要使用單引號(hào)括起來(lái)

nvl

使用nvl函數(shù)可以將null進(jìn)行替換

語(yǔ)法

select nvl(col_name,change_value) from tb_name;

nvl(需要替換的列，將null改變的值)

distinct

該關(guān)鍵字可以將重復(fù)數(shù)據(jù)去除。

select distinct col_name,col_name from tb_name;

注意，distinct關(guān)鍵詞只能放在select關(guān)鍵詞后面，如果distinct關(guān)鍵詞后面如果出現(xiàn)多列，表示多列聯(lián)合去重，即多列的值都相同的時(shí)候才會(huì)認(rèn)為是重復(fù)的記錄。

sqlplus

使用 sqlplus 登錄之后，可以使用buff（緩存）來(lái)存儲(chǔ)/執(zhí)行/修改上一條運(yùn)行的sql語(yǔ)句 。

• buff中只能存儲(chǔ)一條sql語(yǔ)句，但是這條sql語(yǔ)句可能有很多行
• 每次放入新的sql語(yǔ)句，會(huì)把之前的覆蓋掉
• 每次執(zhí)行sql語(yǔ)句，都會(huì)把這個(gè)sql語(yǔ)句放到buff里面

sqlplus 相關(guān)的命令：

• l 查看緩存中的sql語(yǔ)句
• a 在[定位]的那一行后面追加新的內(nèi)容
• i 在[定位]的那一行下面插入新的一行
• c 替換[定位]的那一行中的某些字符串 ，格式為：c/老的字符串/新的字符串
• del 刪除[定位]的那一行內(nèi)容
• n 后面加內(nèi)容可以重寫(xiě)這一行
• cls清屏，linux中使用!
• / 執(zhí)行緩存sql命令

注意：使用 l 命令查看buff中的sql后，可以直接輸入行數(shù)，然后回車(chē)，這就是定位到了這一行。

如果要清空buff中的sql：

clear buffer

其他的一些命令：

save test.sql   //buff中的sql語(yǔ)句保存在 test.sql 文件中
get test.sql    //把 test.sql 中的內(nèi)容加載到buff中，但是沒(méi)有運(yùn)行
start test.sql  //把 test.sql 中的內(nèi)容加載到buff中并且執(zhí)行
@test.sql       //把 test.sql 中的內(nèi)容加載到buff中并且執(zhí)行
edit file_name  //使用系統(tǒng)默認(rèn)編輯器去編輯文件
12345

spool命令：可以記錄操作的過(guò)程

spool file_name //將接下來(lái)的sql 語(yǔ)句以及 sql的運(yùn)行結(jié)果保存到文件中
    sql1
    result1
    sql2
    result2
    ...
spool off       //關(guān)閉pool功能

排序

語(yǔ)法：

select col_name,...
from tb_name
orderby col_name [asc|desc]

默認(rèn)排序?yàn)樯蛞簿褪莂sc，可以不用加，但是如果想要降序那么說(shuō)就必須加desc，如果有多個(gè)列排序，后面的列排序的前提是前面的列排好序以后有重復(fù)（相同）的值。

條件查詢(xún)

select col_name,...
from tb_name
where col_name 比較操作表達(dá)式

注意：

1. 限制查詢(xún)條件，使用where子句
2. 條件可以多個(gè)，使用邏輯操作符或者小括號(hào)進(jìn)行條件的邏輯整合
3. where子句的優(yōu)先級(jí)別最高
4. 比較操作表達(dá)式由操作符和值組成

• 邏輯比較操作符

  = > < >= <= !=

• 不等于操作符，以下三個(gè)都表示不等于的意思，經(jīng)常用的是 !=

  != <> ^=

between and

between and操作符，表示在倆個(gè)值之間

查看員工工資在700 到 1500之間的員工id和名字

select id,last_name,salary
from s_emp
where salary between 700 and 1500;

in

in()：表示值在一個(gè)指定的列表中

查看員工號(hào)1,3,5,7,9員工的工資

select id,last_name,salary
from s_emp
where id in (1,3,5,7,9);

like

模糊查詢(xún)，在值不精確的時(shí)候使用

注意：在數(shù)據(jù)量比較大的表中盡量少用like

oracle instr函數(shù)(oracle 用instr 來(lái)代替 like)

• %，通配 0 到多個(gè)字符
• _，通配一個(gè)字符，并且是一定要有一個(gè)字符
• \，轉(zhuǎn)義字符，需要使用 escape 關(guān)鍵字指定，轉(zhuǎn)義字符只能轉(zhuǎn)義后面的一個(gè)字符

例如：

--查看員工名字以C字母開(kāi)頭的員工的id，工資
select id,last_name,salary
from s_emp
where last_name like 'C%';
1234

注意：數(shù)據(jù)庫(kù)中的字符串，需要使用 單引號(hào) 括起來(lái)

例如：

查看員工名字長(zhǎng)度不小于 5 ，且第四個(gè)字母為n字母的員工id和工資

select id, last_name,salary
from s_emp
where last_name like '%\_%' escape '\';

is null

判斷值為null的時(shí)候使用，null值的判斷不能使用等號(hào)

例如：

--查看員工提成為為空的員工的id和名字
select id,last_name,commission_pct
from s_emp
where commission_pct is null;
1234

注意：類(lèi)似的，還有 is not null

and、or

邏輯操作符，當(dāng)條件有多個(gè)的時(shí)候可以使用

注意：and 比 or 的優(yōu)先級(jí)要高

例如：

查看員工部門(mén)id為 41 且職位名稱(chēng)為Stock Clerk（存庫(kù)管理員）的員工id和名字

select id,last_name,dept_id,title
from s_emp
where
dept_id = 41
and
title = 'Stock Clerk';

查看員工部門(mén)為 41 或者 44 號(hào)部門(mén)，且工資大于 1000 的員工id和名字

select id,last_name,dept_id,title
from s_emp
where
salary > 1000
and
( dept_id = 41
or
dept_id = 44
);

例如，查看員工部門(mén)為 41 且工資大于 1000 ，或者 44 號(hào)部門(mén)的員工id和名字

select id,last_name,dept_id,title
from s_emp
where
salary > 1000
and
dept_id = 41
or
dept_id = 44;

注意：以上倆個(gè)sql語(yǔ)句，執(zhí)行的結(jié)果是不一樣的，它們的區(qū)別就是第一個(gè)sql在條件中多了個(gè)小括號(hào)