SQL基本文法，sql基本語句

SQL基本文法，sql基本語句

一 SQL語句中可能會涉及到的表格:

StudentInfo：

PersonInfo：

Test_outjoin：

Test2_outjoin:

二 基本的sql文法

if exists(select * from dbo.sysobjects where name='StudentInfo')--查詢資料庫中存在的視圖如果存在此表就刪除
   drop table StudentInfo
go
create table StudentInfo
(
    id int identity(20140001,1),--按步長為1進行增長
    name nvarchar(20),
    Chinese float,
    Math float,
    English float,
    address nvarchar(20),
    tel nvarchar(20),
    EnTime datetime
)
---------------------在表中插入相關的資料
insert StudentInfo values('張三丰',50.5,69.3,90.4,'山東','47356454','2014-01-09 00:00:00')
insert StudentInfo values('李小飛',80.5,39.3,90.4,'河北','47344454','2014-01-09 00:00:00')
insert StudentInfo values('盧第',6.5,39.3,90.4,'湖北','47344454','2014-01-09 00:00:00')
insert StudentInfo values('王解',80.5,79.3,70.4,'安徽','4777454','2014-01-09 00:00:00')
--------------------查詢表中的資料
select name,Math from StudentInfo
-------------------建立暫存資料表變數
declare @TempStudentInfo table(name nvarchar(20),Chinese float,Math float,English float)--聲明表變數
insert into @TempStudentInfo select name,Chinese,Math,English from StudentInfo-----向表變數中插入資料
select * from @TempStudentInfo
---------------自訂資料類型(都是基於已有的資料類型)方法1手動
--在資料庫----可程式化性---類型---使用者定義資料類型
---方式2通過代碼實現
exec sp_addtype newChar,'char(80)','not null'
-------------case語句
select *,語文=
case
when Chinese>60 then '合格'
when Chinese<60 then '不合格'
end
from StudentInfo
go
-------------迴圈語句
declare @mysum int
declare @i int
set @i=1
set @mysum=0
while(@i<101)
begin
   set @mysum=@mysum+@i
   set @i+=1
end
print @mysum
go
-------------------暫存資料表
--局部暫存資料表
create table #tempStu
(
  id int,
  name nvarchar(20)
)
insert into #tempStu select id,name from StudentInfo
select * from #tempStu
go
---全域暫存資料表
drop table ##tempStu
create table ##tempStu
(
  id int,
  name nvarchar(20)
)
insert into ##tempStu select id,name from StudentInfo
select * from ##tempStu

select id,name into #temptable from StudentInfo  ---將id和name插入到暫存資料表(同時建立了暫存資料表)
-----------------------------列的計算
select name as 姓名,Chinese as 語文,Math as 數學,English as 英語,Chinese+Math+English as 總分 from StudentInfo order by 總分 desc--預設為升序降序則為desc
--------區間查詢 select * from table id (not)between 17 and 20 
-------in的使用
select * from StudentInfo where Math in(69.3,79.3)
-------------------------------------串連兩張表(join)
select * from StudentInfo
-----性別表
create table PersonInfo
(
   id int,
   sex nvarchar(10)
)
insert into PersonInfo values(20140007,'女')
select * from PersonInfo
---------開始串連兩張表
select A1.name as 姓名,A2.sex as 性別 from StudentInfo A1,PersonInfo A2 where A1.id=A2.id
go
-------group by 的使用
select address as 地區,Sum(Math)as 數學分數 from StudentInfo A1 group by A1.address
--------distance的使用   選取相關列的不同資料 使用方式
select distinct sex from PersonInfo
---------like的使用
---LIKE 是另一個在 WHERE 子句中會用到的指令。基本上，LIKE 能讓我們依據一個套式 (pattern) 來找出我們要的資料。
--SELECT "欄位名" FROM "表格名" WHERE "欄位名" LIKE {套式};
--套式經常包括野卡一下幾個例子：
/*
1 'A_Z': 所有以 'A' 起頭，另一個任何值的字原，且以 'Z' 為結尾的字串。 'ABZ' 和 'A2Z' 都符合這一個模式，而 'AKKZ' 並不符合 (因為在 A 和 Z 之間有兩個字原，而不是一個字原)。
2'ABC%': 所有以 'ABC' 起頭的字串。舉例來說，'ABCD' 和 'ABCABC' 都符合這個套式。
3'%XYZ': 所有以 'XYZ' 結尾的字串。舉例來說，'WXYZ' 和 'ZZXYZ' 都符合這個套式。
4'%AN%': 所有含有 'AN' 這個套式的字串。舉例來說， 'LOS ANGELES' 和 'SAN FRANCISCO' 都符合這個套式。
*/
select name from StudentInfo where name like '張_'
--------Having的使用
/*
那我們如何對函數產生的值來設定條件呢？舉例來說，我們可能只需要知道哪些人的分數超過60。在這個情況下，我們不能使用 WHERE 的指令。那要怎麼辦呢？很幸運地，SQL 有提供一個 HAVING 的指令，而我們就可以用這個指令來達到這個目標。 HAVING 子句通常是在一個 SQL 句子的最後。一個含有 HAVING 子句的 SQL 並不一定要包含 GROUP BY 子句。HAVING 的文法如下：
SELECT "欄位1", SUM("欄位2")
FROM "表格名"
GROUP BY "欄位1"
HAVING (函數條件);
*/
select name ,sum(Math) from StudentInfo group by name having sum(Math)>60
/*內部連結和外部連結：左串連 (left join)，又稱內部串連 (inner join)。在這個情況下，要兩個表格內都有同樣的值，那一筆資料才會被選出。那如果我們想要列出一個表格中每一筆的資料，無論它的值在另一個表格中有沒有出現，那該怎麼辦呢？在這個時候，我們就需要用到 SQL OUTER JOIN (外部串連) 的指令。
外部串連的文法是依資料庫的不同而有所不同的。舉例來說，在 Oracle 上，我們會在 WHERE 子句中要選出所有資料的那個表格之後加上一個 "(+)" 來代表說這個表格中的所有資料我們都要。
*/
--------------------------------------------------SQL的外部連結
--重建立兩張表格
go
create table Test_outjoin
(
  city nvarchar(30),
  StoreNummber int,
  infodatetime datetime
)
create table Test2_outjoin
(
   City_in_Where nvarchar(10),
   City nvarchar(10)
)
select * from Test_outjoin
select * from Test2_outjoin
-------河北和北京不在表一中我們將採用外部連結兩張表
select A1.City,SUM(A2.StoreNummber)from Test2_outjoin A1 left join Test_outjoin A2 on A1.City=A2.city group by A1.City
--Orcle的寫法為select A1.City,SUM(A2.StoreNummber)from Test2_outjoin A1,Test_outjoin A2 where A1.City=A2.city(+) group by A1.City（右串連為:A1.City(+)=A2.city）

-----------------------UNION的使用
/*
   UNION 指令的目的是將兩個sql語句合并起來。從這個角度看union跟join有些類似，因為這兩個指令都可以有多個表格中擷取資料。union的一個限制是兩個sql語句所產生的欄位需要是同樣的資料種類。另外當我們用union這個指令時我們只會看到不同的資料值(類似與select distinct)
*/
go

select Test_outjoin.city from Test_outjoin union select Test2_outjoin.City from Test2_outjoin--兩種查詢區並集
--union all UNION ALL 這個指令的目的也是要將兩個 SQL 陳述式的結果合并在一起。 UNION ALL 和 UNION 不同之處在於 UNION ALL 會將每一筆合格資料都列出來，無論資料值有無重複

---INTERSECT  與union恰好相反他有點像and取交集的意思
select Test_outjoin.city from Test_outjoin intersect select Test2_outjoin.City from Test2_outjoin

---MINUS（MSSQL不存在）不相同值只列出一次
/*MINUS 指令是運用在兩個 SQL 陳述式上。它先找出第一個 SQL 陳述式所產生的結果，然後看這些結果有沒有在第二個 SQL 陳述式的結果中。如果有的話，那這一筆資料就被去除，而不會在最後的結果中出現。如果第二個 SQL 陳述式所產生的結果並沒有存在於第一個 SQL 陳述式所產生的結果內，那這筆資料就被拋棄。
*/
-----SQL Concatenate
/*有的時候，我們有需要將由不同欄位獲得的資料串聯在一起。每一種資料庫都有提供方法來達到這個目的：

MySQL: CONCAT( )
Oracle: CONCAT( ), ||
SQL Server: +
CONCAT( ) 的文法如下：
CONCAT (字串1, 字串2, 字串3, ...)
將字串1、字串2、字串3，等字串聯在一起。請注意，Oracle 的 CONCAT( ) 只允許兩個參數；換言之，一次只能將兩個字串串聯起來。不過，在Oracle中，我們可以用 '||' 來一次串聯多個字串。

*/
select Test2_outjoin.City_in_Where+''+Test2_outjoin.City as '測試Concatenate' from Test2_outjoin

----SubString
/*SQL 中的 substring 函數是用來抓出一個欄位資料中的其中一部分。這個函數的名稱在不同的資料庫中不完全一樣：

MySQL: SUBSTR( ), SUBSTRING( )
Oracle: SUBSTR( )
SQL Server: SUBSTRING( )
最常用到的方式如下 (在這裡我們用 SUBSTR( ) 為例)：
SUBSTR (str, pos)
由 <str> 中，選出所有從第 <pos> 位置開始的字元。請注意，這個文法不適用於 SQL Server 上。
SUBSTR (str, pos, len)
由 <str> 中的第 <pos> 位置開始，選出接下去的 <len> 個字元。

*/
--------------------Trim
/*
SQL 中的 TRIM 函數是用來移除掉一個字串中的字頭或字尾。最常見的用途是移除字首或字尾的空白。這個函數在不同的資料庫中有不同的名稱：
MySQL: TRIM( ), RTRIM( ), LTRIM( )
Oracle: RTRIM( ), LTRIM( )
SQL Server: RTRIM( ), LTRIM( )
各種 trim 函數的文法如下：
TRIM ( [ [位置] [要移除的字串] FROM ] 字串): [位置] 的可能值為 LEADING (起頭), TRAILING (結尾), or BOTH (起頭及結尾)。 這個函數將把 [要移除的字串] 從字串的起頭、結尾，或是起頭及結尾移除。如果我們沒有列出 [要移除的字串] 是什麼的話，那空白就會被移除。
LTRIM(字串): 將所有字串起頭的空白移除。
RTRIM(字串): 將所有字串結尾的空白移除。
*/

---------------------視圖 view
---以現有表(StudentInfo)建立視圖
create view V_Student
as select name,Chinese,Math,English from StudentInfo
select * from V_Student
-----建立索引  在StudentInfo 表的name欄位建立索引
create index index_name_Studentinfo on Studentinfo(name)
------------------AlTER TABLER 改變表結構
/*
ALTER TABLE 的文法如下：
ALTER TABLE "table_name"
 [改變方式];
•加一個欄位: ADD "欄位 1" "欄位 1 資料種類"
•刪去一個欄位: DROP "欄位 1"
•改變欄位名稱: CHANGE "原本欄位名" "新欄位名" "新欄位名資料種類"
•改變欄位的資料種類: MODIFY "欄位 1" "新資料種類"

*/
---增加一列
Alter table StudentInfo add 備忘 nvarchar(20)
-----改變列名（SQL server 請用 sp_rename預存程序詳細使用請查資料
exec sp_rename 'StudentInfo.name','姓名','COLUMN'
------------------------建立表格主鍵
/*
主鍵 (Primary Key) 中的每一筆資料都是表格中的唯一值。換言之，它是用來獨一無二地確認一個表格中的每一行資料。主鍵可以是原本資料內的一個欄位，或是一個人造欄位 (與原本資料沒有關係的欄位)。主鍵可以包含一或多個欄位。當主鍵包含多個欄位時，稱為按鍵組合 (Composite Key)。

主鍵可以在建置新表格時設定 (運用 CREATE TABLE 語句)，或是以改變現有的表格架構方式設定 (運用 ALTER TABLE)。

以下舉幾個在建置新表格時設定主鍵的方式：
[MySQL: ]
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

[Oracle: ]
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

[SQL Server: ]
CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

以下則是以改變現有表格架構來設定主鍵的方式：
[MySQL:]
ALTER TABLE Customer ADD PRIMARY KEY (SID);
[Oracle:]
ALTER TABLE Customer ADD PRIMARY KEY (SID);
[SQL Server:]
ALTER TABLE Customer ADD PRIMARY KEY (SID);
請注意，在用 ALTER TABLE 語句來添加主鍵之前，我們需要確認被用來當做主鍵的欄位是設定為 『NOT NULL』 ；也就是說，那個欄位一定不能沒有資料。
*/
------------------------------------------刪除表中的全部資料TRUNCATE TABLE 指令
truncate table  PersonInfo
select * from PersonInfo
go
----------------------------------------資料的插入 insert into
----操作形式一:INSERT INTO "表格名" ("欄位1", "欄位2", ...)VALUES ("值1", "值2", ...);
----操作形式二:INSERT INTO "表格1" ("欄位1", "欄位2", ...)SELECT "欄位3", "欄位4", ...FROM "表格2";
-----------------------------------------修改表格中的值 UPDATE
-----文法為:UPDATE "表格名"SET "欄位1" = [新值]WHERE "條件"; 同時修改幾個欄位:UPDATE "表格"SET "欄位1" = [值1], "欄位2" = [值2]WHERE "條件";
----------------------------------------直接資料庫中的部分資料
--DELETE FROM "表格名"WHERE "條件";
select * from StudentInfo
delete from StudentInfo where Math<60