SQL簡單使用-進階篇

標籤：快速   必須   資料   top   art   部分   ali   增刪改查   join   

與上一篇的《SQL簡單使用-基礎篇》相連續的篇章，《SQL簡單使用-基礎篇》以下簡稱《基礎篇》。在《基礎篇》中，主要簡單的帶大家瞭解一下SQL命令中最主要的增刪改查命令的使用，增INSERT INTO、刪DETELE/DROP/TRUNCATE、改UPDATE、查SELECTE。因為增刪改查是SQL命令的核心也是最基礎的部分，所以本篇張還是圍繞增刪改查的使用進行進階性的介紹與使用。

先從《基礎篇》中提到的where子句裡面的萬用字元講起。

1.like 用於在where子句中搜尋列中的指定模式
樣本：
select * from websites where name like ‘%oo%‘;
註：（%分號表示任意資料，_表示任意一個資料，動手練兩邊就能熟悉）
‘G%‘ 搜尋以G開頭的資料
‘%G‘ 搜尋以G結尾的資料
‘%g%‘ 搜尋包含g的資料
‘G‘ 搜尋以G開頭的兩位元據
‘G‘ 搜尋以G結尾的兩位元據
‘G‘ 搜尋包含G的三位元據

1.1 萬用字元還有一種（%、_和[charlist]）
樣本：[charlist]使用
select * from websites where name REGEXP ‘^[A-H]‘;

2.between 用於選取介於兩個值之間的資料範圍內的值
樣本：
select * from websites where alexa between 1 and 20;
樣本：添加not使用
select * from websites where alexa not between 1 and 20;
樣本：結合IN使用
select * from websites where ( alexa BETWEEN 1 and 20) and country in (‘USA‘,‘CN‘);
樣本：文本
select * from websites where name between ‘A‘ and ‘H‘; 不包含H

3.top 用於規定返回記錄的資料，實用
樣本：SQL server (SELECT TOP number|percent column_name(s) FROM table_name;)
select top 50 percent * from websites;
樣本：Oracle（SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;）
select * from websites where ROWNUM <5;
樣本：MYSQL （SELECT column_name(s) FROM table_name LIMIT number;）
select * from websites limit 3;

4.IN 操作符允許在where子句中規定多個值
樣本：查看錶websites中name列的多條資料
select * from websites where name in(‘baidu‘,‘Google‘)；

5.別名 可以為表名稱或列名稱指定別名。
文法：列名稱文法
SELECT column_name AS alias_name FROM table_name;
樣本：
select name AS n,country AS c from websites;
文法：表名稱文法
SELECT column_name(s) FROM table_name AS alias_name;
樣本：
select w.name,w.url,a.count,a.date from websites AS w ,access_log AS a where w.id=a.site_id and w.name=‘菜鳥教程‘;
注:
1.在查詢中涉及超過一個表
2.在查詢中都是用了函數
3.列名稱很長或者可讀性差 都需要把兩個列或者多個列結合在一起。

6.join 子句用於把來自兩個表或者多個表的行結合起來，基於這些表之間的共同欄位
join類型有一下幾種：
INNER JOIN：如果表中有至少一個匹配，則返回行
LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行
RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行
FULL JOIN：只要其中一個表中存在匹配，則返回行（MYSQL不支援）

首先，串連的結果可以在邏輯上看作是由SELECT語句指定的列組成的新表。
左串連與右串連的左右指的是以兩張表中的哪一張為基準，它們都是外串連。
外串連就好像是為非基準表添加了一行全為空白值的萬能行，用來與基準表中找不到匹配的行進行匹配。假設兩個沒有空值的表進行左串連，左表是基準表，左表的所有行都出現在結果中，右表則可能因為無法與基準表匹配而出現是空值的欄位。
來源：《資料庫系統原理教程》，王珊，陳紅編著，P86

樣本： inner join

SELECT    websites.id,    websites.NAME,    access_log.count,    access_log.dateFROM    websitesINNER JOIN access_log ON websites.id = access_log.site_id;

7.union 用於合并兩個或多個select語句的結果集
文法：
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
樣本： union 去重

SELECT country FROM WebsitesUNIONSELECT country FROM appsORDER BY country; 

樣本：union all 顯示所有包括重複部分

select country from websitesunion allselect country from apps;

樣本：帶有where的union all

select country,name from websites where country=‘CN‘union allselect country,app_name from apps where country=‘CN‘ order by country;

8. into 從一個表複製資料，把資料插入到另一個新表中
   註：MySQL 資料庫不支援 SELECT ... INTO 語句，但支援 INSERT INTO ... SELECT 。
   文法： 複製所有的列插入新表中
   SELECT * INTO newtable [IN externaldb] FROM table1;
   文法：只複製希望的列插入到新表中
   SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;

8.1 insert into select
樣本：複製 "apps" 中的資料插入到 "Websites" 中：
INSERT INTO websites (name,country) select app_name,country from apps;

9. create 用於建立資料庫或者資料表
   文法：建立資料庫
   create database db_name;
   文法：建立資料表

   create table table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),... ...);    date_type 資料類型，size參數規定表中列的最大長度

樣本：建立名稱為runoob的資料庫
create database runoob;
樣本：創一個student_informaton表，包含五列：student_id,student_name,student_class,student_tele,student_add

create table student_infomation (student_id int(10),student_name char(4),student_class char(10),student_tele int(11),student_add varchar(255));

前面是列名，後面跟的是對於列名的資料類型

10.約束 用於規定表中的資料規則
約束可以在建立表的時候通過create table語句規定，或者在表建立之後通過alter table語句規定
文法：crate table + constraint

create table table_name(column_name1 type_data(size) constraint,column_name2 type_data(size) constraint,column_name3 type_data(size) constraint,... ...);

在SQL中，我們有如下約束：
NOT NULL 指示某列不能儲存 NULL 值，強制欄位始終包含值，否則就無法插入新記錄或者更新記錄。
UNIQUE 保證某列的每行必須有唯一的值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的結合。確保某列（或兩個列多個列的結合）有唯一標識，有助於更容易更快速地找到表中的一個特定的記錄。
FOREIGN KEY 保證一個表中的資料匹配另一個表中的值的參照完整性。
CHECK 保證列中的值符合指定的條件。
DEFAULT 規定沒有給列賦值時的預設值。

10.1 not null約束 約束強制不接受到任何null值
樣本： student_tele不可為空

create table student_information (    student_id INT (10) ,    student_name CHAR (4),    student_class CHAR (10),    student_tele INT (11) NOT NULL,    student_add VARCHAR (255));

10.2 UNIQUE 約束唯一標識資料庫表中的每條記錄。
UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。
PRIMARY KEY 約束擁有自動定義的 UNIQUE 約束。
請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。
樣本：MYSQL

create table student_information (    student_id INT (10),    student_name CHAR (4),    student_class CHAR (10),    student_tele INT (11),    student_add VARCHAR (255),    unique (student_id)    );

樣本:SQL server/oracle

create table student_information (    student_id int (10) NOT NULL UNIQUE,    student_name CHAR (4),    student_class CHAR (10),    student_tele INT (11),    student_add VARCHAR (255),    );

樣本：SQL mysql/server/oracle 定義過個列的unique約束。

create table student_information (    student_id int (10) NOT NULL UNIQUE,    student_name CHAR (4),    student_class CHAR (10),    student_tele INT (11),    CONSTRAINT  stu_inf UNIQUE (student_id,student_name)    );  這裡的 stu_inf 為約束名稱constraint_name，自訂。

alter table時的unique約束
樣本：

alter table student_informationadd unique (student_id);

樣本：添加多個unique,

alter table student_informationadd constraint stu_inf unique (student_id,student_add);

撤銷unique約束
樣本：mysql

alter table student_informationdrop index stu_inf;

樣本：SQL

alter table student_informationdrop constraint stu_inf;

10.3 primary key 主鍵必須包含唯一的值，主鍵不能為null，每個表都應該有一個主鍵，並且是唯一的。
樣本：參照unique，將其中的unique替換為 primary key即可。 上述有添加多個unique樣本，如果改為primary 可以意思就是主鍵由添加的幾個列組成。

10.4 foreign key 約束
a.可以用來預防破壞表之間串連的行為
b.防止非法資料插入外鍵列，因為它必須是指向的那個表中的值之一

樣本：MYSQL

create table websites(  id int(11) NOT NULL,  name char(20) NOT NULL,  url varchar(255) NOT NULL,  alexa int(11) NOT NULL,  country char(10) NOT NULL,  primary key (id),  foreign KEY (id) references apps(id));

樣本：SQL server/oracle

CREATE TABLE websites ( Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, Id int FOREIGN KEY REFERENCES apps(Id) );

樣本：MySQL/SQL Server/Oracle

CREATE TABLE websites ( Id int NOT NULL, OrderNo int NOT NULL, Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (Id) REFERENCES apps(Id) );

alter table 使用foreign key約束
樣本：

ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

樣本：如需命名 FOREIGN KEY 約束，並定義多個列的 FOREIGN KEY 約束

ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

撤銷FOREIGN KEY約束
樣本：mysql

alter table Orders drop index fk_PerOrders;

樣本：SQL

alter table Orders drop constraint fk_PerOrders;

10.5 CHECK 條件約束
用於限制列中的值的範圍
樣本：MYSQL

create table websites(  id int(11) NOT NULL,  name char(20) NOT NULL,  url varchar(255) NOT NULL,  alexa int(11) NOT NULL,  country char(10) NOT NULL,  check (id>0));

alter table 使用check約束
alter table websites add check (id>0);
撤銷check約束（參照unique約束中的alter table）
alter table websites drop check constraint_name;

10.6 DEFAULT 約束
1.用於向列中插入預設值
2.如果沒有規定其它值，那麼將預設值添加到所有的記錄
樣本：MYSQL

create table student_information (    student_id INT (10) NOT NULL,    student_name CHAR (4),    student_class CHAR (10) DEFAULT ‘‘ comment ‘班級‘,    student_tele INT (11),    student_add VARCHAR (255));  comment 是為 欄位或列的屬性添加註釋用的

alter table 使用 default
樣本：MYSQL

alter table websitesalter country set default ‘CN‘;

樣本：SQL server
alter table websites add constraint ad_c default ‘CN‘ for country;
樣本：oracle
alter table websites modify country default ‘CN‘;
撤銷default約束
樣本：MYSQL

alter table websitesalter country drop default;

樣本：SQL server/oracle

alter tables websitesalter column country drop default;

11. create index 用於在表中建立索引
    在表中建立索引可以更高效的查詢資料，使用者無法查看到索引，他們只能被用來加速搜尋/查詢。
    註：更新一個包含索引的表所耗費的時間比沒有索引表的時間更長，這是由於索引本身也需要更新。因此，理想的做法是僅僅在嘗嘗被所有的列(及表)上面建立索引。
    文法：建立一個簡單的索引，允許使用重複的值
    create index index_name ON table_name (column_name);
    文法：在表中建立唯一的索引，不允許使用重複的值（create unique table）：唯一的索引意味著兩個行不能擁有相同的索引值。
    create UNIQUE index index_name ON table_name (column_name);

樣本：將websites表中name列中建立名為web_index的索引。
create index web_index ON websites (name);

12.drop 可以刪除表，索引和資料庫
DROP INDEX 語句用於刪除表中的索引。
用於 SQL Server 的 DROP INDEX 文法：
DROP INDEX table_name.index_name
用於 DB2/Oracle 的 DROP INDEX 文法：
DROP INDEX index_name
用於 MySQL 的 DROP INDEX 文法：
ALTER TABLE table_name DROP INDEX index_name
DROP TABLE 語句用於刪除表。
DROP DATABASE 語句用於刪除資料庫。

僅僅需要刪除表內的資料，但並不刪除表本身
TRUNCATE TABLE table_name

13.ALTER TABLE 用於在已有的表中添加、刪除或修改列。
添加列的文法：
ALTER TABLE table_name ADD column_name datatype;
刪除表中的列文法：
ALTER TABLE table_name DROP COLUMN column_name datatype;
改變表中資料類型文法：
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

樣本： 在website表中添加名為column_date的列，然後修改列的資料類型，刪除添加的列

alter table websites add column_date date；  添加alter table websites modify column column_date year；修改alter table websites drop column column_date;   刪除

參考菜鳥教程請添加連結描述整理的筆記

SQL簡單使用-進階篇