sql_server基礎學習

SQL DML 和 DDL

可以把 SQL 分為兩個部分：數據操作語言 (DML) 和 數據定義語言 (DDL)。
SQL (結構化查詢語言)是用于執行查詢的語法。但是 SQL 語言也包含用于更新、插入和刪除記錄的語法。
查詢和更新指令構成了 SQL 的 DML 部分：
?SELECT?- 從數據庫表中獲取數據
?UPDATE?- 更新數據庫表中的數據
?DELETE?- 從數據庫表中刪除數據
?INSERT INTO?- 向數據庫表中插入數據
SQL 的數據定義語言 (DDL) 部分使我們有能力創建或刪除表格。我們也可以定義索引（鍵），規定表之間的鏈接，以及施加表間的約束。
SQL 中最重要的 DDL 語句:
?CREATE DATABASE?- 創建新數據庫
?ALTER DATABASE?- 修改數據庫
?CREATE TABLE?- 創建新表
?ALTER TABLE?- 變更（改變）數據庫表
?DROP TABLE?- 刪除表
?CREATE INDEX?- 創建索引（搜索鍵）
?DROP INDEX?- 刪除索引

創建一段數據測試

use test
create table Persons(
Id int primary key,
Name varchar(14) not null,
Sex varchar(13) not null,
Address varchar(20) not null,
City varchar(20) not null
)
insert into Persons values(1,'張三','男','廣東茂名','廣東');
insert into Persons values(2,'李四','男','浙江杭州','浙江');
insert into Persons values(3,'王五','女','北京五環','北京');
insert into Persons values(4,'小小','女','上海浦東','上海');

select * from Persons

select語句

語法:
SELECT 列名稱 FROM 表名稱
?查看某列數據

select Name from Persons        

?可以查詢多列用“,”隔開

select Name,Sex from Persons

select * from sysdatabases;     #查看所以庫  （*代表所有）
use tongji                      #切換tongji庫
select * from sys.tables            #查看所有表

?查看schema，user的存儲過程

SELECT * FROM SYS.DATABASE_PRINCIPALS
SELECT * FROM SYS.SCHEMAS
SELECT * FROM SYS.SERVER_PRINCIPALS

?查看DEPT表所有內容

select * from DEPT 

?查看DEPT表中deptno為101的內容

select * from DEPT where deptno='101';

?select distinct語句
關鍵詞 DISTINCT 用于返回唯一不同的值
語法：
SELECT DISTINCT 列名稱 FROM 表名稱

select distinct Name,Sex from Persons

?SQL SELECT INTO 語句

SQL SELECT INTO----可用于創建表的備份文件

SQL SELECT INTO 語法
您可以把所有的列插入新表：

SELECT * INTO new_table_name [IN externaldatabase] 
FROM old_tablename

或者只把希望的列插入新表：

SELECT column_name(s) INTO new_table_name [IN externaldatabase] 
FROM old_tablename

?制作 "Persons" 表的備份復件

select  *  into Persons_backup from Persons;

子句可用于向另一個數據庫中拷貝表：

SELECT *INTO Persons IN 'Backup.mdb'
FROM Persons

如果我們希望拷貝某些域，可以在 SELECT 語句后列出這些域：

SELECT LastName,FirstNameINTO Persons_backup
FROM Persons

?create語句

?創建test庫

use master
create database test

?創建test登陸用戶，密碼為123，默認庫為tongji

create login test with password='123',default_database=tongji

?為登陸用戶創建數據庫用戶test1（create user），默認庫為dbo

create user test1 for login test with default_schema=dbo

?創建DEPT表(要先去到庫里執行)

create table DEPT
(
DEPTNO int primary key,
DNAME VARCHAR(14),
LOC VARCHAR(13)
)

數據類型（data_type）規定了列可容納何種數據類型。下面的表格包含了SQL中最常用的數據類型：
數據類型 描述
?integer(size)
?int(size)
?smallint(size)
?tinyint(size) 僅容納整數。在括號內規定數字的最大位數。
?decimal(size,d)
?numeric(size,d) 容納帶有小數的數字。
"size" 規定數字的最大位數。"d" 規定小數點右側的最大位數。
char(size) 容納固定長度的字符串（可容納字母、數字以及特殊字符）。
在括號中規定字符串的長度。
varchar(size) 容納可變長度的字符串（可容納字母、數字以及特殊的字符）。
在括號中規定字符串的最大長度。
date(yyyymmdd) 容納日期。

?create index

您可以在表中創建索引，以便更加快速高效地查詢數據。
用戶無法看到索引，它們只能被用來加速搜索/查詢。
注釋：更新一個包含索引的表需要比更新一個沒有索引的表更多的時間，這是由于索引本身也需要更新。因此，理想的做法是僅僅在常常被搜索的列（以及表）上面創建索引。

SQL CREATE INDEX 語法
在表上創建一個簡單的索引。允許使用重復的值：
CREATE INDEX index_name ON table_name (column_name)
注釋："column_name" 規定需要索引的列。

SQL CREATE UNIQUE INDEX 語法
在表上創建一個唯一的索引。唯一的索引意味著兩個行不能擁有相同的索引值。
CREATE UNIQUE INDEX index_name ON table_name (column_name)
假如您希望索引不止一個列，您可以在括號中列出這些列的名稱，用逗號隔開：

create index sed on Persons (City,Name);

?insert語句

語法
INSERT INTO 表名稱 VALUES (值1, 值2,....)
我們也可以指定所要插入數據的列：
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

?插入數據

insert into DEPT VALUES(101,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES(102,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES(103,'SALES','CHICAGO');
INSERT INTO DEPT VALUES(104,'OPERATIONS','BOSTON');

?--指定列中插入數據

insert  into Persons (Name,Sex) values('大大','女')

?update語句

語法：
UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

?為Name是大大的人添加Address

update Persons set Address='南京' where Name='大大'

?為Name是大大的人修改Address,City

update Persons set Address='中山',City='南京' where Name='大大'

?delete語句

DELETE 語句用于刪除表中的行。
語法
DELETE FROM 表名稱 WHERE 列名稱 = 值
刪除ID號為5的內容

delete from Persons where Id=5

可以在不刪除表的情況下刪除所有的行。這意味著表的結構、屬性和索引都是完整的：

DELETE FROM table_name
或者：
DELETE * FROM table_name

?drop語句

通過使用drop語句，可以輕松地刪除索引、表和數據庫。

SQL DROP TABLE 語句
DROP TABLE 語句用于刪除表（表的結構、屬性以及索引也會被刪除）：
DROP TABLE 表名稱
SQL DROP DATABASE 語句
DROP DATABASE 語句用于刪除數據庫：
DROP DATABASE 數據庫名稱

用于 Microsoft SQLJet (以及 Microsoft Access) 的語法:

DROP INDEX index_name ON table_name

?alter語句

ALTER TABLE 語句用于在已有的表中添加、修改或刪除列。

SQL ALTER TABLE 語法
如需在表中添加列，請使用下列語法:
ALTER TABLE table_name
ADD column_name datatype
要刪除表中的列，請使用下列語法：
ALTER TABLE table_name
DROP COLUMN column_name
注釋：某些數據庫系統不允許這種在數據庫表中刪除列的方式 (DROP COLUMN column_name)。
要改變表中列的數據類型，請使用下列語法：
ALTER TABLE table_name
ALTER COLUMN column_name datatype

在Order表里增加一列

alter  table Orders add Id nchar(10); 

在Persons表里添加一列,增加內容

alter table Persons add Birthday datetime;
update Persons set Birthday='1991-03-26' where Id=1

刪除Birthday列

alter table Persons drop column Birthday

?where語句

**語法：
SELECT 列名稱 FROM 表名稱 WHERE 列 運算符 值
下面的運算符可在 WHERE 子句中使用：
操作符 描述
= 等于
<> 不等于

大于
< 小于
= 大于等于
<= 小于等于
BETWEEN 在某個范圍內**
LIKE 搜索某種模式
注釋：在某些版本的 SQL 中，操作符 <> 可以寫為 !=。
?查詢居住在北京的人

select * from Persons where City='北京';

注意：SQL 使用單引號來環繞文本值（大部分數據庫系統也接受雙引號）。如果是數值，請不要使用引號。
?and |or運算符
and:兩者皆成立

select * from Persons where Name='王五' and Sex='女';

or:一者成立便可

select * from Persons where Name='王五' or Sex='女';

我們也可以把 AND 和 OR 結合起來（使用圓括號來組成復雜的表達式）:

select * from Persons where (Name='王五' or Sex='女') and Address='北京五環';

?order by語句

ORDER BY 語句用于根據指定的列對結果集進行排序。
ORDER BY 語句默認按照升序對記錄進行排序。
如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。
例：以字母順序顯示公司名稱（從小到大）

select * from Orders order by Company

逆序（從大到小）

select * from Orders order by Company desc

以逆字母順序顯示公司名稱，并以數字順序顯示順序號：

select * from Orders order by Company desc,OrderNo asc

?TOP 子句

TOP 子句用于規定要返回的記錄的數目。
對于擁有數千條記錄的大型表來說，TOP 子句是非常有用的。
注釋：并非所有的數據庫系統都支持 TOP 子句。

SQL Server 的語法：
SELECT TOP number|percent column_name(s) FROM table_name
?查詢Persons表中頭兩條記錄

select top 2 * from Persons

?"Persons" 表中選取 50% 的記錄

select top 50 percent * from Persons

?like語句

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

SQL LIKE 操作符語法
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

?查詢City以“廣”開頭， %通配符

select * from Persons where City like '廣%'

like %1---以1結尾
like 1%---以1開頭
like %1%---包含1內容
not like %1%---包含1內容

?in操作符

IN 操作符允許我們在 WHERE 子句中規定多個值

SQL IN 語法
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

?取出City為廣東和北京的人

select * from Persons where City in('廣東','北京')

?between操作符

操作符 BETWEEN ... AND 會選取介于兩個值之間的數據范圍。這些值可以是數值、文本或者日期。

SQL BETWEEN 語法
SELECT column_name(s) FROM table_name WHERE column_name
BETWEEN value1 AND value2

重要事項：不同的數據庫對 BETWEEN...AND 操作符的處理方式是有差異的。某些數據庫會列出介于 "Adams" 和 "Carter" 之間的人，但不包括 "Adams" 和 "Carter" ；某些數據庫會列出介于 "Adams" 和 "Carter" 之間并包括 "Adams" 和 "Carter" 的人；而另一些數據庫會列出介于 "Adams" 和 "Carter" 之間的人，包括 "Adams" ，但不包括 "Carter" 。
所以，請檢查你的數據庫是如何處理 BETWEEN....AND 操作符的！

?SQL Alias

?表的 SQL Alias 語法
SELECT column_name(s)
FROM table_name
AS alias_name
?列的 SQL Alias 語法
SELECT column_name AS alias_name
FROM table_name
?Alias 實例: 使用表名稱別名
假設我們有兩個表分別是："Persons" 和 "Product_Orders"。我們分別為它們指定別名 "p" 和 "po"。
現在，我們希望列出 "John Adams" 的所有定單。
我們可以使用下面的 SELECT 語句：

SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'

不使用別名的 SELECT 語句：

SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName
FROM Persons, Product_Orders
WHERE Persons.LastName='Adams' AND Persons.FirstName='John'

從上面兩條 SELECT 語句您可以看到，別名使查詢程序更易閱讀和書寫。

?SQL INNER JOIN 關鍵字

在表中存在至少一個匹配時，INNER JOIN 關鍵字返回行。

**INNER JOIN 關鍵字語法
SELECT columnname(s) FROM tablename1 INNER JOIN tablename2
ON tablename1.columnname=tablename2.columnname
注釋：INNER JOIN 與 JOIN 是相同的。

?SQL LEFT JOIN 關鍵字
LEFT JOIN 關鍵字會從左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中沒有匹配的行。

LEFT JOIN 關鍵字語法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注釋：在某些數據庫中， LEFT JOIN 稱為 LEFT OUTER JOIN。
?SQL RIGHT JOIN 關鍵字
RIGHT JOIN 關鍵字會右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中沒有匹配的行。

RIGHT JOIN 關鍵字語法
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注釋：在某些數據庫中， RIGHT JOIN 稱為 RIGHT OUTER JOIN。

?SQL FULL JOIN 關鍵字
只要其中某個表存在匹配，FULL JOIN 關鍵字就會返回行。

FULL JOIN 關鍵字語法
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注釋：在某些數據庫中， FULL JOIN 稱為 FULL OUTER JOIN。

?用戶授權

use master
create database test
use test 
create user test1 for login test with default_schema=dbo
exec sp_addrolemember 'db_owner','test1'

賦權（test1用戶擁有db_owner權限）

exec sp_addrolemember 'db_owner','test1'

?賬號管理
?禁用賬號
alter login test disable
?啟用賬號
alter login test enable;
?登錄賬號改名
alter login test with name=test_t;
修改密碼：
alter login test_t with password='123'
?數據庫用戶改名
alter user test1 with name='test_1';
?更改數據庫用戶
alter user test1 with default_schema=test;
?刪除數據庫用戶
drop user test1
?刪除登陸用戶
drop login test_t

?SQL 通配符

在搜索數據庫中的數據時，SQL 通配符可以替代一個或多個字符。
SQL 通配符必須與 LIKE 運算符一起使用。
在 SQL 中，可使用以下通配符：

?"Persons" 表中選取居住的城市以"廣" 或 "北" 開頭的人

    select * from Persons where City like '[廣北]%'

?"Persons" 表中選取居住的城市不以"廣" 或 "北" 開頭的人

    select * from Persons where City like '[^廣北]%'

?SQL 約束

約束用于限制加入表的數據的類型。
可以在創建表時規定約束（通過 CREATE TABLE 語句），或者在表創建之后也可以（通過 ALTER TABLE 語句）。
我們將主要探討以下幾種約束：
?NOT NULL
?UNIQUE
?PRIMARY KEY
?FOREIGN KEY
?CHECK
?DEFAULT

?SQL NOT NULL 約束
NOT NULL 約束強制列不接受 NULL 值。
NOT NULL 約束強制字段始終包含值。這意味著，如果不向字段添加值，就無法插入新記錄或者更新記錄。
下面的 SQL 語句強制 "Id_P" 列和 "LastName" 列不接受 NULL 值：
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
?SQL UNIQUE 約束
UNIQUE 約束唯一標識數據庫表中的每條記錄。
UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。
PRIMARY KEY 擁有自動定義的 UNIQUE 約束。
請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。

?SQL UNION 語法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
UNION 操作符用于合并兩個或多個 SELECT 語句的結果集。
請注意，UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的數據類型。同時，每條 SELECT 語句中的列的順序必須相同。

注釋：默認地，UNION 操作符選取不同的值。如果允許重復的值，請使用 UNION ALL。

?SQL UNION ALL 語法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 結果集中的列名總是等于 UNION 中第一個 SELECT 語句中的列名。

--use test
--go
--create table employees_China (
--E_ID int primary key,
--E_NAME VARCHAR(15)  NOT NULL
--)
--INSERT INTO employees_China VALUES(01,'Zhuang,Hua')
--INSERT INTO employees_China VALUES(02,'Wang,Wei')
--INSERT INTO employees_China VALUES(03,'Yang,Ming')
--
--
--create table employees_USA (
--E_ID int primary key,
--E_NAME VARCHAR(15)  NOT NULL
--)
--INSERT INTO employees_USA VALUES(01,'Adams,John')
--INSERT INTO employees_USA VALUES(02,'Bush,George')
--INSERT INTO employees_USA VALUES(03,'Yang,Ming')

?列出所有在中國和美國的不同的雇員名：

SELECT E_NAME FROM employees_USA UNION SELECT E_NAME FROM employees_China

注釋：這個命令無法列出在中國和美國的所有雇員。在上面的例子中，我們有兩個名字相同的雇員，他們當中只有一個人被列出來了。UNION 命令只會選取不同的值。

?SQL PRIMARY KEY 約束
PRIMARY KEY 約束唯一標識數據庫表中的每條記錄。
主鍵必須包含唯一的值。
主鍵列不能包含 NULL 值。
每個表都應該有一個主鍵，并且每個表只能有一個主鍵。

?SQL CREATE VIEW 語法
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
注釋：視圖總是顯示最近的數據。每當用戶查詢視圖時，數據庫引擎通過使用 SQL 語句來重建數據。

?SQL 更新視圖

您可以使用下面的語法來更新視圖：

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

?SQL 撤銷視圖
您可以通過 DROP VIEW 命令來刪除視圖。

SQL DROP VIEW Syntax
DROP VIEW view_name

?SQL Server Date 函數

下面的表格列出了 SQL Server 中最重要的內建日期函數：
函數 描述
GETDATE() 返回當前日期和時間
DATEPART() 返回日期/時間的單獨部分
DATEADD() 在日期中添加或減去指定的時間間隔
DATEDIFF() 返回兩個日期之間的時間
CONVERT() 用不同的格式顯示日期/時間

?SQL AVG函數

SQL AVG() 語法
SELECT AVG(column_name) FROM table_name
查詢平均值

select avg(OrderPrice) as OrderNoavg from Orders;

我們希望找到 OrderPrice 值高于 OrderPrice 平均值的客戶。

select Company from Orders where OrderPrice >(select avg(OrderPrice) from Orders)

?SQL COUNT() 函數

COUNT() 函數返回匹配指定條件的行數。
?SQL COUNT(column_name) 語法
COUNT(column_name) 函數返回指定列的值的數目（NULL 不計入）：
SELECT COUNT(column_name) FROM table_name
?SQL COUNT() 語法
COUNT() 函數返回表中的記錄數：
SELECT COUNT(*) FROM table_name
?SQL COUNT(DISTINCT column_name) 語法
COUNT(DISTINCT column_name) 函數返回指定列的不同值的數目：
SELECT COUNT(DISTINCT column_name) FROM table_name
注釋：COUNT(DISTINCT) 適用于 ORACLE 和 Microsoft SQL Server，但是無法用于 Microsoft Access。

SQL COUNT(column_name) 實例
我們擁有下列 "Orders" 表：
O_Id OrderDate OrderPrice Customer
1 2008/12/29 1000 Bush
2 2008/11/23 1600 Carter
3 2008/10/05 700 Bush
4 2008/09/28 300 Bush
5 2008/08/06 2000 Adams
6 2008/07/21 100 Carter
現在，我們希望計算客戶 "Carter" 的訂單數。
我們使用如下 SQL 語句：

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter'

以上 SQL 語句的結果是 2，因為客戶 Carter 共有 2 個訂單：
CustomerNilsen
2

查看表中總行數

SELECT COUNT(*) AS NUMBER FROM ORDERS

去掉重復行

SELECT COUNT(DISTINCT Company) AS NUMBER FROM ORDERS

?FIRST() 函數

FIRST() 函數返回指定的字段中第一個記錄的值。
提示：可使用 ORDER BY 語句對記錄進行排序。

SQL FIRST() 語法
SELECT FIRST(column_name) FROM table_name
sql2005不支持

?MAX() 函數
MAX 函數返回一列中的最大值。NULL 值不包括在計算中。

SQL MAX() 語法

SELECT MAX(column_name) FROM table_name
注釋：MIN 和 MAX 也可用于文本列，以獲得按字母順序排列的最高或最低值。

查找OrderPrice列中最大值

select max(OrderPrice) as maxprice from Orders

?MIN() 函數
MIN 函數返回一列中的最小值。NULL 值不包括在計算中。

SQL MIN() 語法
SELECT MIN(column_name) FROM table_name
注釋：MIN 和 MAX 也可用于文本列，以獲得按字母順序排列的最高或最低值。

select min(OrderPrice) as maxprice from Orders

?SUM() 函數

SUM 函數返回數值列的總數（總額）。

SQL SUM() 語法
SELECT SUM(column_name) FROM table_name
查看orderprice列總和

select sum(OrderPrice) as maxprice from Orders

?GROUP BY 語句

GROUP BY 語句用于結合合計函數，根據一個或多個列對結果集進行分組。

SQL GROUP BY 語法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

我們希望查找每個客戶的總金額（總訂單）。

select Company,sum(OrderPrice)as sumprice from Orders group by Company

?HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 關鍵字無法與合計函數一起使用。

SQL HAVING 語法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

查找客戶 "IBM" 或 "Apple" 擁有超過 1500 的訂單總金額。

select Company,sum(OrderPrice)as sumprice from Orders where Company='IBM' OR Company='Apple' group by Company having sum(OrderPrice) >1500

?UPPER() 函數

UPPER 函數把字段的值轉換為大寫。

SQL UPPER() 語法
SELECT UPPER(column_name) FROM table_name
全換成大寫

select upper(Company) as upname from Orders

?LOWER()函數
語法同上

全換成小寫

select lower(Company) as upname from Orders

?LEN() 函數

LEN 函數返回文本字段中值的長度。
SQL LEN() 語法
SELECT LEN(column_name) FROM table_name

取“Company” 的值的長度

select lEN(Company) as LENname from Orders

?ROUND() 函數

ROUND 函數用于把數值字段舍入為指定的小數位數。

SQL ROUND() 語法
SELECT ROUND(column_name,decimals) FROM table_name
參數 描述
column_name 必需。要舍入的字段。
decimals 必需。規定要返回的小數位數

把名稱和價格舍入為最接近的整數

select ProductName,round(UnitPrice,0) as roundprice from products

?NOW() 函數

NOW 函數返回當前的日期和時間。
提示：如果您在使用 Sql Server 數據庫，請使用 getdate() 函數來獲得當前的日期時間。

SQL NOW() 語法
SELECT NOW() FROM table_name

select ProductName,UnitPrice, getdate() as date  from products

修改日期格式

select ProductName,UnitPrice, CONVERT( VARCHAR(10),getdate(),23) as date  from products

參考資料：
http://www.w3school.com.cn/sql/sql_view.asp