sql基礎知識（筆記）（一）

**

sql基礎知識（筆記）（一）

**
當時學習sql的筆記 （oracle）

1.簡單查詢:

select * from 表名;

其中select和from是關鍵字，泛綠色,Oracle所有泛綠色的都是關鍵字
*是萬用字元,代表所有的列;
表名即需要查詢的表的名字,我們要看哪張表裡的資料,就寫哪個表的名字,查出來的就是這個表的所有資料;
每一句SQL後都需要有分號,分號代表一個SQL語句的結束，切記.

select 列1,列2,列3 from 表名;

當我們要查詢表中的一部分列時,就不能使用了,因為查出的是所有的列,比如成績單表,語文老師只想看語文成績,就不需要使用select *
當我們想查詢部分列時,把想查詢的列名代替*即可,如果要查詢多列，則多個列名之間用逗號隔開.
比如查詢emp表的empno和ename,按照我們上面說的,就可以寫作 select empno, ename from emp;更多的列也是如此,都好隔開即可.
這裡注意一個細節,我們查詢的列名的順序,決定結果中列展示的前後順序,即先查詢了empno,則資料展示時先展示empno,再查詢了ename,則ename第二位展示.
查詢結果的展示順序,只與查詢順序有關.select *時順序一直是固定的,因為表中列的順序是固定的,DBA_TAB_COLUMNS表中有COLUMN_ID這個東西,它就決定了表中列的順序,也決定了展示順序;

表取別名和列取別名

select b.列1,b.列2 from 表名 b;

我們在查詢一部分列時,實際都是列名前都是有表名的,即 表名.列名 的形式,取別名後,就可以用別名代替表名,即變為 別名.列名形式. 因為只有一張表,所有 表名. 這個部分可以省略。
但我們之後會學習到從多張表查詢資料,這時候就不能省略了.尤其表名比較長的時候,表取別名就很實用了,可以提高我們的編碼效率,不用再多次資料表名.

select 列1 as 別名1, 列2 別名2 from 表名;

列取別名時,需要注意別名的命名規範:
儘量是單詞縮寫,若多個單詞時,用下劃線_連線,不能使用中劃線;
數字,_開頭時,需要用雙引號包裹
列名中包含特殊符號,比如括號等時,需要用雙引號包裹
別名可以是中文,且不需要引號，這也是唯一的一處.

**

2.篩選查詢:

**

資料型別:
資料型別是指的是資料的格式,這裡的資料不是單指數字,是說表裡的內容.

常見資料型別有如下幾個:
varchar2:字串型別,包含各種字元,字母,漢字,符號等等.因為編碼格式的不同,漢字所佔位數會有所差異,GBK 漢字佔2位,UTF-8佔3位
varchar2區別於varchar,回憶區別的點在哪兒:都是存放字元型別 varchar2是可變的,varchar2(40),如果我只存放了1個字母,實際佔位1 varchar分配多少佔多少, varchar(40),存放了一個字母,這40個字元全部佔滿,別人無法使用
字串格式使用時需要用單引號包裹.(有沒有例外情況?) 最大長度 4000

number:數字型別,即資料.1,2,3等的這種.數字是可以直接比較大小的. number(5) 代表是數字型別,最大長度5位. number(4,2)代表最大長度4位,其中小數2位.
比如number(5),最大長度5位，資料可以是1位,比如數字7,也可以是3位,比如156,最多不超過5位.
number(4,2):是指最大長度4位,其中小數2位.即資料可以是1,但顯示為1.00,因為有2位小數.即整數最多2位,小數一直是2位.
最長38位

date:時間型別,代表時間,比如當前時間 sysdate. to_date(), to_char()

篩選查詢:
之前講解的都是查詢整張表的全部內容或者某幾列的全部內容,如果要查詢一部分資料,就得用到篩選查詢.
基本語法:

select * from 表名 where 列名=值;--查詢表中列名的值=值的資料.
比如select * from emp where ename='ADAM';--查詢emp表中ename是'ADAM'的人的資訊

看列資料型別的幾種方式:
1.select * from 表名;滑鼠放在某一列資料上,下方會顯示這一列資料的型別;
2.Ctrl+滑鼠左鍵,在表名上,點選進去後可以看到每一列的資料型別;
3.USER_TAB_COLUMNS檢視內,有每一列的資料型別長度等資訊,COLUMN_ID也在裡面

其中表名後出現一個關鍵字 where,可以理解為條件是,或者滿足條件，後面跟上查詢的條件 列名=值. 表達的意思是滿足列中的資料等於後面的值這個條件.

這裡面不止=一個運算子,另外還有>,<,>=,<=,<>, between… and, in, not in
其中注意between … and是包含邊界的,所有在使用時要注意,如果是統計資料,則慎用.

當前我們是隻有一個查詢條件,實際應用中我們經常是多個條件同時使用,提高查詢的準確度.這裡需要引入and和or滿足多個條件查詢.

and:意為同時滿足and前後兩個條件
or:滿足or前後任意一個條件即可,也可以理解為查詢結果是滿足前面條件的結果和滿足後面條件結果的合集.

而且and和or有優先順序關係: ()>and>or
即當有and和or同時存在時,先執行and,再執行or. 如果有括號,則先執行括號. 回看筆記,看一下我們之前的練習題.

日常編碼中,我們需要使用括號,提高程式碼可讀性.

篩選查詢中還有兩個點:

1. in的用法: 現階段in可以理解為多個or的組合,在某個範圍之內的資料 同理還有 not in
2. oracle判空: is null,判斷不為空時 is not null.其餘的方式都不對(=‘ ’, = null)等.

練習題:

create table emp_0204
(emp_no number(4),
emp_name varchar2(50),
emp_job varchar2(50),
emp_hiredate date,
emp_dept varchar2(50)
);

insert into emp_0204 values(0001,'Sucre','MANAGER',to_date('19850308','YYYYMMDD'),'10');
insert into emp_0204 values(0002,'Alex','SALESMAN',to_date('19820110','YYYYMMDD'),'10');
insert into emp_0204 values(0003,'Bill','SALESMAN',to_date('19950820','YYYYMMDD'),'10');
insert into emp_0204 values(0004,'Shown','SALESMAN',to_date('19871102','YYYYMMDD'),'10');
insert into emp_0204 values(0005,'Han','SALESMAN',to_date('19931008','YYYYMMDD'),'10');
insert into emp_0204 values(0006,'George','MANAGER',to_date('19981126','YYYYMMDD'),'20');
insert into emp_0204 values(0007,'John','SALESMAN',to_date('19830510','YYYYMMDD'),'20');
insert into emp_0204 values(0008,'Peter','SALESMAN',to_date('19930815','YYYYMMDD'),'20');
insert into emp_0204 values(0009,'Matthew','SALESMAN',to_date('19940619','YYYYMMDD'),'20');
insert into emp_0204 values(0010,'Edward','PRESIDENT',to_date('20081028','YYYYMMDD'),'30');
insert into emp_0204 values(0011,'Hamilton','SALESMAN',to_date('20010609','YYYYMMDD'),'30');
insert into emp_0204 values(0012,'Connor','SALESMAN',to_date('19821112','YYYYMMDD'),'30');
commit;

1.查詢所有MANAGER的入職時間;

select emp_hiredate from emp where job='MANAGER';

2.查詢所有SALESMAN的名字;

select emp_name from emp where emp_job='SALESMAN';

3.查詢部門20的MANAGER;

select * from emp where emp_job='MANAGER' and emp_dept='20';

4.查詢入職時間在90年之後且職位不是SALESMAN的;

select *from emp where emp_hiredate>to_date('19900101','YYYYMMDD') and emp_job <>'SALESMAN';

5.查詢部門10的MANAGER和部門20的SALESMAN;

select * from emp where
(emp_dept='10' and emp_job='MANAGER')
or
(emp_dept='20' and emp_job='SALESMAN');

6.查詢部門30的PRESEDENT和部門20的MANAGER;

select * from emp where
(emp_dept='30' and emp_job='PRESIDENT')
or
(emp_dept='20' and emp_job='MANAGER');

7.查詢所有部門的SALESMAN;

select * from emp where emp_job='SALESMAN';

8.查詢部門10的所有SALESMAN;

select * from emp where emp_dept='10' and emp_job='SALESMAN';

**

模糊查詢

**
模糊查詢的概念:條件不完整或者不充分的時候，需要用到模糊查詢
like的用法,模糊查詢的語法: select * from 表名 where 列名 like ‘%內容%’;
%和_的含義:%在它和它之前有0個或者多個字元 _在當前位置有一個字元

select * from emp where ename like '%TH%';--ename中包含TH字樣的
select * from emp where ename like '___TH';--查詢ename以TH結尾,且長度是5位的

搜尋以固定內容開頭或者結尾的資料

select * from 表名 where 列名 like '內容%';--以固定內容開頭
selct * from 表名 where 列名 like '%內容';--以固定內容結尾的資料
搜尋固定長度的資料
select * from 表名 where 列名 like '_____';--搜尋長度是5位的資料 考慮函式方式
搜尋帶%或者_的資料
select * from 表名 where 列名 like '%/%%' escape '/';
select * from 表名 where 列名 like '%/_%' escape '/';
搜尋帶%和_且不挨著的資料
'%/%%_/_%' escape '/'
'%/_%_/%%' escape '/'

排序
排序語法:

select * from 表名 order by 列名 ;

排序方式;
order by:排序,也是關鍵字,按照之後的列和排序方式進行排序
排序的兩種方式 asc,desc:升序 asc (預設), 降序 desc
多種排序的語法和樣式： order by 列名1 asc, 列名2 desc;–先按照第一列排序,當第一列中有重複資料時,按照第二列排序.如果第一列中沒有重複資料,則根據第一列的排序就是最終結果

select * from 表名		order by 列名1 desc, 列名2 asc;

order by 1, order by 2: 1,2代表查詢內容的第幾列.
排序使用的位置
模糊查詢與排序聯用時如何使用
select * from 表名 where 列名 Like ‘%內容%’ order by 列名 排序方式;

練習:

insert into emp_0204 values (13,'AB_CD','TEST',sysdate-20,'10');
insert into emp_0204 values (14,'A%D','TEST',sysdate-100,'20');
insert into emp_0204 values (15,'A%D_E%F','TEST',sysdate-15,'30');
insert into emp_0204 values (16,'AD_E%F','TEST',sysdate-15,'30');
commit;

--1.查詢emp_0204表emp_name包含n的所有人資訊;
select * from emp_0204 where emp_name like '%n%';
--2.查詢emp_0204表emp_name以H開頭的;
select * from emp_0204 where emp_name like 'H%';
--3.查詢emp_0204表emp_name以F結尾的;
select * from emp_0204 where emp_name like '%F';
--4.查詢emp_0204表emp_name中包含_的;
select * from emp_0204 where emp_name like '%/_%' escape '/';
--5.查詢emp_0204表emp_name中包含%的;
select * from emp_0204 where emp_name like '%/%%' escape '/';
--6.查詢emp_0204表emp_name中包含%和_的;
select * from emp_0204 where emp_name like '%/_%' escape '/' and emp_name like '%/%%' escape '/';
select * from emp_0204 where emp_name like '%/_%/%%' escape '/' or emp_name like '%/%%/_%' escape '/';
--7.查詢emp_0204中emp_dept為10的員工資訊,以emp_name升序展示;
select * from emp_0204 where emp_dept='10' order by emp_name asc;
--8.查詢emp_0204中emp_dept為20的員工資訊,按照emp_job降序展示;
select * from emp_0204 where emp_dept='20' order by emp_job desc;
--9.查詢emp_0204中emp_dept為30的員工資訊,先按照emp_job升序展示,再按照emp_name降序展示;
select * freom emp_0204 where emp_dept='30' order by emp_job asc,emp_name desc;
--10.查詢emp_0204中emp_dept為20的員工資訊,按照emp_hiredate降序展示,再按照emp_name升序展示;
select *from emp_0204 where emp_dept='20' order by emp_hiredate desc, emp_name asc;
--11.查詢emp_0204中emp_name包含e字母的,按照emp_name升序排序;
select * from emp_0204 where emp_name like '%e%' order by emp_name asc;

**

分組:

**
基本語法 多列分組(語法和含義)
group by 列名1,列名2;
select之後的內容(聚合函式,每個什麼意思 coount(1), count(2))
分組列,聚合函式處理過的列
MAX–最大值
MIN–最小值
SUM–求和
AVG–平均數
COUNT–數量 count(*), count(1), count(2),
count(列名) 如果列中有空值,那麼得出的數量是不包括空值的.

select 內容 from 表名 group by 列名;

內容:分組列或者聚合函式處理過的列

having和where:
where:針對全表資料做過濾
having:針對分組內的資料過濾

group by 列名 having 內容;
having之後的內容:
1.分組列可用;
2.聚合函式可以用;

group by, having, order by的寫的時候的順序
SQL執行順序

select * from 表名 where 條件 group by 列 having 內容 order by 列 asc/desc;

1.同時使用group by, having和order by時的寫法;
2.執行順序:
(1):where條件;
(2):group by;
(3):having 過濾;
(4):select;
(5):order by;

case when 2種寫法

case 列名
when 值1 then 執行語句1
when 值2 then 執行語句2
when 值3 then 執行語句3
else 執行語句4 end case;

case 
when 列>值1 then 執行語句1
when 列>值2 and 列<值3 then 執行語句2
when 列>值4 and 列<值5 then 執行語句3
else 執行語句4 end case;

擴充套件:
利用group by過濾重複資料
1:distinct
2:group by–max(rowid)/min(rowid) group by的列名是分組列,通常是主鍵列
3:union
4:row_number() over(partition by)

rowid–每一行資料都有一個rowid,是一個唯一的字串,跟資料一一對應,可以用來過濾重複資料
rownum–偽劣,可以過濾資料的前幾行,rownum<4,但是不能rownum>某個資料.

習題:

--針對emp_0204表:
--1.獲取10部門最晚的入職時間;
select max(emp_hiredate) from emp_0204 where emp_dept='10';
--2.獲取20部門的最早入職時間;
select min(emp_hiredate) from emp_0204 where emp_dept='20';
--3.獲取SALESMAN中最早的入職時間;
select min(emp_hiredate) from emp_0204 where emp_job='SALESMAN';
--4.獲取所有MANAGER中入職最晚的;
select max(emp_hiredate) from emp_0204 where emp_job='MANAGER';
--5.篩選人數超過6人的部門;
select emp_dept from emp_0204 group by emp_dept having count(1)>6
--7.查詢sal<1000, 1000-3000,3000-5000和>5000的各階段人數;
select SUM(case when
          sal is null then 1 else 0 end) sal_null,
          SUM(case when sal<1000 then 1 else 0 end) sal_1000,
          sum(case when sal>=1000 and sal<3000 then 1 else 0 end) sal_1000_3000,
          sum(case when sal>=3000 and sal<=5000 then 1 else 0 end) sal_3000_5000,
          sum(case when sal>5000 then 1 else 0 end) sal_5000
          from emp_0204;