Oracle資料庫語言——結構化查詢語言 (SQL)SQL

Oracle資料庫語言——結構化查詢語言 (SQL)SQL

一、資料定義語言 (Data Definition Language)DDL

1.建立資料表空間：CREAT TABLESPACE lyy DATAFILE 'C:/app/lyy.dbf' SIZE 10M;(建立一個10M的資料表空間，存放在C盤app檔案夾中)

刪除資料表空間：DROP TABLESPACE lyy;

2.建立使用者和賦權：CREAT USER lyy PROFILE DEFAULT IDENTIFIED BY 123456 DEFAULT TABLESPACE lyy TEMPORARY TABLESPACE temp ACCOUNT UNLOCK; （建立使用者lyy,預設密碼為123456，預設資料表空間為lyy,暫存資料表空間為temp,帳號開啟狀態）

GRANT CONNECT TO lyy;
GRANT RESOURCE TO lyy;(賦予使用者lyy connect和resource許可權)

3.建立表

文法格式：CREAT TABLE 表名（屬性名稱1 資料類型（長度），屬性名稱2 資料類型......屬性名稱n 資料類型）；

例：建立以下三個表：
--學生表 student：學號 sid、姓名 sname、性別 ssex、年齡 sage、電話 sphone
--課程表 course: 課程編號 cid、課程名稱 cname、老師名稱 tname、課時 chour
--成績表 score: 成績編號 scid、學號 sid、課程編號 cid、分數 grade

在Oracle中，表的全名是：方案名.表名；方案名就是使用者名稱，方案名不寫，表示方案名就是目前使用者自己；

——建立學生表

CREAT TABLE student(
  sid CHAR(10),
  sname VARCHAR2(50) NOT NULL,
  ssex CHAR(1),
  sage NUMBER,
  sphone INTEGER 
);

——建立課程表

CREAT TABLE course(
    cid CHAR(10),
    cname VARCHAR2(50),
    tname VARCHAR2(50),
    chour NUMBER
);

——建立成績表

CREAT TABLE score(
  scid CHAR(10),
  sid CHAR(10),
  cid CHAR(10),
  grade NUMBER
);

刪除表： DROP TABLE student;

4.約束條件

  約束是由使用者添加，用來保證儲存到資料庫的資料的實體完整性和參照完整性；

約束種類一般有五種：

--主鍵約束：Primary key，要求被定義為主鍵的欄位的值具有唯一性和非空性；
                 constraint p1_sid primary key(sid)              --定義sid為主鍵。
--外鍵約束：Foreign key，要求被定義為外鍵的欄位的值必須來源於所引用欄位的值， 外鍵欄位和所引用的欄位，名稱可以不一樣，但是兩者的資料類型和長度必須一致；
                 constraint f1_sid foreign key(sid) references student(sid)  --定義sid為外鍵，值來源於student表的sid
--唯一約束：Unique  ，要求具有唯一性；
                  unique(sid,cid)                           --定義sid和cid的組合有唯一性。
--非空約束：not null，要求必須有值；
                  sname varchar2(50) not null              --定義sname欄位非空
--檢查約束：check，使用者可以根據業務的需要，對欄位的值進行自訂限制。
                  constraint c1_ssex check(ssex in ('M','F'))  --定義檢查約束，要求ssex欄位的取值必須為M或者F 

5.修改表ALTER

ALTER TABLE SCOTT.TEST RENAME TO TEST1--修改表名
ALTER TABLE SCOTT.TEST RENAME COLUMN NAME TO NAME1 --修改表列名
ALTER TABLE SCOTT.TEST MODIFY NAME1 NUMBER(20)  --修改欄位類型
ALTER TABLE SCOTT.TEST ADD ADDRESS VARCHAR2(40) --添加表列
ALTER TABLE SCOTT.TEST DROP NAME CASCADECONSTRAINTS --刪除表列

二、資料操作語言DML

      DML是資料操作語言，主要可以完成三個操作：insert插入、update更新、delete刪除；DML語句是一種事務動作陳述式，需要做commit確認、rollback復原操作才能最終完成的。如果不做確認或者復原操作，則會鎖定當前的表，導致針對該表的DDL等操作都會失敗。

1.insert插入資料

文法格式1： insert into 表名 values(值1，值2......，值n）；

注意事項：在SQL語句中，一般來說，除了值得部分（資料），其他部分都是不區分大小寫，資料部分，字元類型需要單引號，其他類型不需要單引號；該格式下，要保證值得數量、類型、長度、順序都要和表的欄位保持一致。

樣本：SELECT * FROM student;
        INSERT INTO student VALUES('S001','張三',‘M’,30，13089247856) ；

文法格式2： insert into 表明（欄位名1，欄位名2，......，欄位名n） values(值1,值2,......，值n);

注意事項：註定值和欄位的對應關係（按順序對應），好處是可以根據自身的值和欄位的對應關係，有選擇性進行插入操作。

樣本：  INSERT INTO student(sname,ssex,sid,sage) VALUES('李四','F','s0002',20);

2.update更新資料

文法格式：update 表名 set 賦值運算式 [where 條件]

注意事項：update本身是一個列操作的語句，即不加條件，操作的是表中的整列資料；如果加了條件，因為條件限定是行，則表示操作的是選中的行所對應的列。

樣本： SELECT * FROM student;
          UPDATE student SET sage=40;
          UPDATE student SET sage=(sage+sphone)-sage,sphone=(sage+sphone)-sphone;
          UPDATE student SET sage=sage+1;
          UPDATE student SET sage=sage+1 where ssec='F';
          UPDATE student SET sname=replace(sname,'張','陳');

3.delete刪除資料

文法格式：delete from 表 [where 條件]

注意事項：delete是一個行操作，最小操作單位是一條記錄，切記delete沒有*號；如果不加where條件，則表示刪除整個表中的所有記錄。

樣本：DELETE FROM student WHERE sid='s0002';
        INSERT INTO course VALUES('c0001','oracle','teacher 1',32);
        INSERT INTO score VALUES('sc0001','s0001','c0001',100);
        SELECT * FROM student;
         SELECT * FROM course;
        SELECT * FROM score;

4.truncate：是DDL語句，但也可以實現刪除整表的資料的效果

文法格式：truncate table  表名

樣本：  truncate table score；

三、資料查詢語言DQL

1. select 用來對資料進行查詢，擷取使用者想要的資訊。

文法格式：

select ： 查詢的內容，是必選關鍵字，後面跟要查詢的內容，一般以欄位為主、也可以是常量、運算式（包含欄位）。
  --from： 查詢內容的來源，是必選關鍵字，來源可以是表格、多個表格、其他的查詢語句等；
  --where：條件，可選關鍵字，一般用來指定查詢的條件，即用來過濾資料；
  --group by：欄位，可選關鍵字，用來實現分組查詢；
  --having：條件，可選關鍵字，是用來對分組之後的結果進行過濾；
  --order by：欄位，可選關鍵字，用來實現排序操作；

注意事項：不帶條件的查詢，即列查詢，查詢內容可以是表中的一個欄位、多個欄位、常量或者運算式。

樣本：

--查詢學生表的所有內容
        SELECT * FROM student;
--查詢所有學生的姓名和年齡
        SELECT sname,sage  FROM student;
--查詢常量
        SELECT sname,sage,'teacher 1'  FROM student;
--查詢內容是運算式
        SELECT sname,sage,sage+1 FROM student;

2. ||  用來實現字串、變數的拼接操作的

樣本：SELECT sname,sage,sage||'歲' FROM student;
         SELECT sname,ssex FROM student;
         SELECT sname,ssex,case WHEN ssex='M' THEN '男' ELSE '女' END FROM student;

3.條件

  加查詢條件是用來過濾資料的，過濾的基本單位是行，常見的關鍵字有：（> /< /= />= /<=/ !=/ <> /between..and.. /like/ in /all /any /exists/not exists等；多個條件的串連符有：and\or\!

樣本：

--查詢年齡大於28歲的所有學生的資訊
      SELECT * FROM student WHERE sage>28;
--查詢年齡大於等於28歲的所有學生的資訊；
     SELECT * FROM student WHERE sage>=28;
--查詢所有男生資訊
      SELECT * FROM student WHERE ssex='M';
      SELECT * FROM student WHERE ssex!='F';
      SELECT * FROM student WHERE ssex<>'F';
--查詢學號比s0010靠前的學生的資訊。
      SELECT * FROM student WHERE sid<'s0010';

1）between..and..

文法格式：欄位 between 值1 and 值2  等價於 欄位>=值1 and 欄位<=值2；是一個獨立、完整的欄位，不可拆分。

樣本：--查詢年齡在23-28歲之間（包含）的學生資訊。
             SELECT * FROM student WHERE sage>=23 AND sage<=28;
             SELECT * FROM student WHERE sage BETWEEN 23 AND 28;
        --查詢年齡在23-28歲之間（包含）的女生資訊。
             SELECT * FROM student WHERE sage BETWEEN 23 AND 28 AND ssex='F';
             SELECT * FROM student WHERE sage>=23 AND ssex='F'AND sage<=28;

2）like:實現的是模糊查詢，一般來說會結合兩個特殊符號使用，%萬用字元，匹配任意多個字元；_表示匹配一個字元。

樣本：--查詢所有姓張的同學資訊。
            SELECT *  FROM student WHERE sname LIKE '張%';
        --查詢所有姓張，姓名總共為2個字的同學資訊。
            SELECT *  FROM student WHERE sname LIKE '張_';
        --查詢所有姓張，姓名總共為3個字的同學資訊。
           SELECT *  FROM student WHERE sname LIKE '張__';
           SELECT *  FROM student WHERE sname LIKE '%張';

3)in：是一種枚舉用法，欄位in（值1，值2，......，值n）等價於 欄位=值1 or 欄位=值2 or ... or 欄位=值n。

樣本：--查詢年齡等於27歲或者28歲的學生的資訊。
            SELECT * FROM student WHERE sage=27 OR sage=28;
            SELECT * FROM student WHERE sage IN (27,28);

4)distinct：用來修飾欄位，表示唯一查詢、去除重複值。

樣本：--查詢所有選課了的學生的學號。
            SELECT  sid FROM score;
            SELECT  DISTINCT sid FROM score;

5)別名：可以應用在查詢內容和來源中。

文法格式： 原名 as 別名，一般來說，as是省略的， 原名 別名。

樣本：SELECT  sname,sage FROM student;
         SELECT  sname as 姓名,sage 年齡 FROM student;
         SELECT  sname,sage+1 sage FROM student;
         SELECT  sname sage FROM student; --文法沒問題，從應用程式層面是有問題的。查詢學生姓名，但結果是列名改為了sage。

6）嵌套

  SQL語句中，查詢語句是可以被嵌套使用。嵌套是通過括弧()實現。每一條查詢語句的結果本身就是一個表、是一些值的集合，可以被嵌套使用在值或者來源的部分。

--查詢所有選修oracle課程的學生的姓名。
  SELECT sname FROM student WHERE sid IN (SELECT sid FROM score WHERE cid=(SELECTcid FROM course WHERE cname='oracle'));

--查詢某一位同學所選修的課程的名稱。（已經學生姓名）
  SELECT cname FROM course WHERE cid IN (SELECT cid FROM score WHERE sid in (SELECT sid FROM student WHERE sname='張三'));

7)all: 使用and來對錶達式進行分解

--欄位 > all(值1，值2，。。。，值N)  等價於： 欄位>值1 and 欄位>值2 and... and 欄位>值N。
--欄位 < all(值1，值2，。。。，值N)  等價於： 欄位<值1 and 欄位<值2 and... and 欄位<值N。

樣本：查詢比所有女生年齡都大的男生資訊。
         SELECT * FROM student WHERE ssex='M' AND sage>39;
         SELECT sage FROM student WHERE ssex='F'; --21\25\39
         SELECT * FROM student WHERE ssex='M' AND sage>ALL(SELECT sage FROM student WHERE ssex='F');
        --等價於  SELECT *FROM student WHERE ssex='M' AND sage>21 AND sage>25 AND sage>39;

8）any: 就是用or來對錶達式進行分解

--欄位 > any(值1，值2，。。。，值N)  等價於： 欄位>值1 or 欄位>值2 or ... or 欄位>值N。

樣本：--查詢比任一女生年齡都大的男生資訊。
           SELECT * FROM student WHERE ssex='M' AND sage>ANY(SELECT sage FROM student WHERE ssex='F');

4.彙總函式（分組函數/組函數）

  常見的彙總函式有：count()\avg()\min()\max()\sum(), 括弧裡加欄位或欄位運算式。

樣本：--查詢男生的數量
            SELECT count(*) FROM student WHERE ssex='M';
         --查詢有成績的學生的數量
            SELECT count(distinct sid) FROM score;
        --查詢女生的最小年齡
            SELECT min(sage) FROM student WHERE ssex='F';
         --查詢某一位學生的平均成績
            SELECT avg(grade) FROM score WHERE sid='s0001';
          --查詢年齡最大的學生的姓名
            SELECT* FROM student WHERE sage=(SELECT max(sage) FROM student);

5.分組查詢

文法格式： group by 分組欄位

作用：表示會將表格中所有的資料按照分組欄位進行分組處理、分組欄位的值相同的行會被合并為一條記錄、即一組；分組欄位以外的欄位是無法再被獨立查詢，只能通過彙總函式來實現查詢，分組欄位可以是多個，用逗號分隔。

樣本：--查詢男生和女生的數量
          SELECT ssex,COUNT(*) FROM student GROUP BY ssex;
        --查詢男生和女生的平均年齡、最大年齡、最小年齡
           SELECT ssex,AVG(sage),MAX(sage),min(sage) FROM student GROUP BY ssex;
        --查詢每一門課程被選的數量
           SELECT cid,COUNT(DISTINCT sid) FROM score GROUP BY cid;

注意：where是發生在分組之前，having是發生在分組之後，意味分組函數是不能直接作為條件的值出現在where之後，但是可以出現在having之後。
樣本：--查詢所有科目都及格的學生的sid。
            SELECT sid FROM score WHERE grade>=60 GROUP BY sid; --錯誤的寫法,實現的是任意科目及格的學生。
            SELECT sid FROM score GROUP BY sid HAVING MIN(grade)>=60; --先按學生分組，設定條件每個學生的最低分要大於等於60.