原文鏈接:http://www.ifyao.com/2015/05/18/%E6%A0%87%E5%87%86%E7%9A%84sql%E8%AF%AD%E8%A8%80%E4%BD%BF%E7%94%A8%E6%96%B9%E6%B3%95%E5%8F%8A%E5%A4%9A%E8%A1%A8%E8%BF%9E%E6%8E%A5/
SQL語言是目前最通用的關系數據庫語言。ANSI SQL是指由美國國家標准局(ANSI)的數據庫委員會制定的標准SQL語言,多數關系數據庫產品支持標准SQL語言,但是它們也往往有各自的SQL方言。
在分層的軟件結構中,關系數據庫位於最底層,它的上層應用都被稱為數據庫的客戶程序。以MySql為例,mysql.exe和Java應用就是它的兩個客戶程序。這些客戶程序最終通過SQL語言與數據庫通信。
SQL(Structured Query Language)的英語全稱可翻譯為結構化查詢語言,但實際上它除了具有數據查詢功能,還具有數據定義、數據操縱和數據控制功能。
SQL語言的類型
語言類型 描述 SQL語句 DDL(Data Definition Language) 數據定義語言,定義數據庫中的表、視圖和索引等 create、drop和alter語句 DML(Data Manipulation Language) 數據操縱語言,保存、更新或刪除數據 insert、update和delete語句 DQL(Data Query Language) 數據查詢語言,查詢數據庫中的數據 select語句 DCL(Data Control Language) 數據控制語言,用於設置數據庫用戶的權限 grant和remove語句
當用戶向數據庫輸入數據時,由於種種原因,用戶有可能輸入錯誤數據。保證輸入的數據符合規定,成為數據庫系統,尤其是多用戶的關系數據庫系統首要關注的問題。為了解決這一問題,在數據庫領域出現了數據完整性的概念。數據完整性(Data Integrity)就是指數據必須符合的規范,它主要分為三類:
規定表的一行(即每一條記錄)在表中是唯一的實體。實體完整性通過表的主鍵來實現。
指數據庫表的列(即字段)必須符合某種特定的數據類型或約束。如not null。
保證一個表的外鍵和另一個表的主鍵對應。
用於定義數據庫中的表、視圖和索引等。相關DDL語句如下:
crate table CUSTOMERS(
ID bigint not null,
NAME varchar(15) not null,
AGE int,
primary key (ID)
);
crate table ORDERS(
ID bigint not null,
ORDER_NUMBER varchar(15) not null,
PRICE double precision,
CUSTOMER_ID bigint,
foreign key(CUSTOMER_ID) references CUSTOMERS(ID)
);
在創建數據庫schema時,通常所有表的DDL語句都放在同一個SQL腳本文件中,必須按照先父表後子表的順序來定義DDL語句。假如表之間的參照關系發生變化,就必須修改DDL語句的順序,這增加了維護SQL腳本文件的難度。為了解決這一問題,可以采用另一種方式來定義外鍵。
crate table CUSTOMERS(
ID bigint not null,
NAME varchar(15) not null,
AGE int,
primary key (ID)
);
crate table ORDERS(
ID bigint not null,
ORDER_NUMBER varchar(15) not null,
PRICE double precision,
CUSTOMER_ID bigint,
primary key (ID)
);
alter table ORDERS add constraint FK_CUSTOMER foreign key (CUSTOMER_ID) references CUSTOMERS(ID);
DML用於向數據庫插入、更新或刪除數據,這些操作分別對應insert、update和delete語句。
在執行這些語句時,數據庫系統先進行數據完整性檢查,如果這些語句違反了數據完整性,數據庫系統會異常終止執行SQL語句。
SQL語言的核心就是數據查詢語言。查詢語句的語法如下:
select 目標列 from 基本表(或視圖) [where 條件表達式] [group by 列名1[having 條件表達式]] [order by 列名2[asc|desc]]
簡單SQL查詢語句,其中where子句設定查詢條件,order by子句設定查詢結果的排序方式。
(1)查詢年齡在10到50之間的客戶,查詢結果先按照年齡降序排列,再按照名字升序排列。
select * from customers where age between 18 and 50 order by age desc,name asc;
(2)查詢名字為“Tom”、“Mike”、“Jack”的客戶。
select * from customers where name in (‘Tom’,’ Mike’,’ Jack’);
(3)查詢姓名的第二個字母是“a”的客戶。
select * from customers where name like ‘’_a%’’;
(4)查詢年齡為null的客戶的名字。
select name from customers where age is null;
注意:不能用表達式age=null來比較age是否為null,因為這個表達式的值既不為true,也不為false,而是永遠為null。當where子句的取值為null,select的查詢語句的查詢結果為空。
(5)在查詢語句中為表和字段指定別名:
select name c_name,age c_age from customer c where c.id=1;
連接查詢的from子句的連接語法格式為:
from talbe1 join_type table2 [on (join_condition)] [where (query_condition)]
table1和talbe2表示參與連接操作的表,table1為左表,table2為右表。on子句設定連接條件,where子句設定查詢條件,join_type表示連接類型,可分為3種:
Select * from customers,orders;
返回兩張表中記錄數的乘積。若customers中有5條記錄,orders表中有7條記錄,則結果返回35條記錄。
顯式內連接:使用inner join關鍵字,在on子句中設定連接條件
Select c.id,o.customer_id,c.name,o.id roder_id,order_number from customers c inner join orders o on c.id=o.customer_id;
隱式內連接:不包含inner join關鍵字和on關鍵字,在where子句中設定連接條件
Select c.id,o.customer_id,c.name,o.id order_id,order_number from customers c,orders o where c.id=o.customer_id;
Select c.id,o.customer_id,c.name,o.id order_id,order_number from customers c left outer join orders o on c.id=o.customer_id;
以上查詢語句的查詢結果不僅包含符合on連接條件的數據,還包含customers左表中的其他數據行。
子查詢也叫嵌套查詢,是指在select子句或者where子句中又嵌入select查詢語句,下面舉例說明它的用法。
1)查詢具有3個以上訂單的客戶:
select * customers c where c<=(select count(*) from orders o where c.id=o.customer_id);
2)查詢名為“Mike”的客戶的所有訂單
select * from orders o where o.customer_id in (select id form cutomers where name=’Mike’);
3)查詢沒有訂單的客戶:
select * from customers c where 0=(select count(*) from order o where c.id=o.customer_id);
或者
select * from customers c where not exists (select * from orders o where c.id=o.customer_id);
4)查詢ID為1的客戶的姓名、年齡及它的所有訂單的總價格:
select name,age,(select sum(price) from orders where customer_id=1) total_price from customers where id=1;
total_price是別名
也可以通過左外連接查詢來完成相同的功能:
select name,age,sum(price) from customers c left outer join orders o on c.id=o.customer_id where c.id=1 group by c.id;
如果數據庫不支持子查詢,可以通過連接查詢來完成相同的功能。事實上,所有的子查詢語句都可以改寫為連接查詢語句。
聯合查詢能夠合並兩條查詢語句的查詢結果,去掉其中的重復數據行,然後返回沒有重復數據行的查詢結果。聯合查詢使用union關鍵字,例如:
select * from customers where age<25 union select * from customers where age>=24;
報表查詢對數據行進行分組統計,其語法格式為:
[select …] from … [where…] [ group by … [having… ]] [ order by … ]
其中group by 子句指定按照哪些字段分組,having子句設定分組查詢條件。在報表查詢中可以使用以下SQL聚集函數。
count():統計記錄條數
min():求最小值
max():求最大值
sum():求和
avg():求平均值
用法:
1)按照客戶分組,查詢每個客戶的所有訂單的總價格:
select c.id,c.name,sum(price) from customers c left outer join orders o on c.id=o.customer_id group by c.id;
2)按照客戶分組,查詢每個客戶的所有訂單的總價格,並且要求訂單的總價格大於100:
select c.id,c.name,sum(price) from customers c left outer join orders o on c.id=o.customer_id group by c.id having(sum(price)>100);