SQL语言

创建数据库

 

CREATE DATABASE Flight
ON
PRIMARY (NAME=SampleData, 
          FILENAME='c:/Program Files/../../Data/Flight.mdf',
          SIZE=10MB,

MAXSIZE=15MB,

FILEGROWTH=20%)
LOG ON
 ( NAME=SampleLog,
   FILENAME= 'c:/ProgramFiles/../../Data/        Flight.ldf',
   SIZE=3MB,

   MAXSIZE=5MB,

   FILEGROWTH=1MB)

修改数据库结构

 

ALTER DATABASE Sample
   MODIFY FILE ( NAME = 'SampleLog',
   SIZE = 15MB)

GO –-标识一批 Transact-SQL 语句的结束。

ALTER DATABASE Sample

ADD FILE

  (NAME = SampleData2,

   FILENAME='c:/Program Files/../../
      Data/Sample2.ndf',

   SIZE=15MB,

   MAXSIZE=20MB)

GO

 

删除数据库

DROP DATABASE DBNAME

 

使用存储过程配置数据库

 

1.设置数据库为只读

EXEC sp_dboption 'pubs', 'read only', 'True’

2.设置数据库为自动压缩

EXEC sp_dboption 'pubs' ,autoshrink, true

 

3.设置数据库为单用户

EXEC sp_dboption 'pubs','single user'

 

使用DBCC维护数据库

1.     收缩数据文件大小

DBCC SHRINKDATABASE(UserDB,10)

2.     显示指定目标的分布统计信息

DBCC SHOW_STATISTICS(authors,UPKCL_auidind)

 

自定义数据类型

创建

EXEC sp_addtype City, 'nvarchar(15)', NULL

EXEC sp_addtype PostCode, 'nvarchar(6)', NULL

EXEC sp_addtype NCode, 'nvarchar(18)', NULL

删除

EXEC  sp_droptype city

 

创建表

CREATE TABLE Categories

(CategoryID int IDENTITY(1,1),

 CategoryName nvarchar(15) NOT NULL,

 Description ntext NULL,

 Picture image NULL)

主键约束

CREATE TABLE jobs(

Job_id smallint PRIMARY KEY,

job_desc varchar(50) NOT NULL)

 

 

唯一约束

CREATE TABLE jobs( Job_id smallint UNIQUE)   （允许空值）

标识列

CREATE TABLE jobs(

Job_id smallint IDENTIRY（2,1） PRIMARY KEY)

实体完整性

1.ALTER TABLE [表名] ADD CONSTRAINT

<约束名> PRIMARY KEY (列名列表)

2. Uniqueidentifier数据类型和NEWID 函数

CREATE TABLE Customer

(CustID uniqueidentifier NOT NULL DEFAULT NEWID(),

  CustName char(30) NOT NULL)

  INSERT Customer VALUES (NEWID(),'ASB')

引用完整性

1.     在创建数据库表时设置外键

  CREATE TABLE ABC

(ABC NCHAR(5)  FOREIGN KEY

   REFERENCES Customers(CustomerID),

ABCD CHAR(6) NOT NULL)

2.     使用约束方式实施引用

ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Customers

  FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID)

更新表结构

1.  ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL

2.  ALTER TABLE jobs ADD HIRE_DATE DATETIME

3. ALTER TABLE Doc_ED DROP COLUMN Column_B

3.  ALTER TABLE Doc_ED WITH NOCHECK ADD CONSTRAINT Exd_Check CHECK (Column_a > 1)

 

删除表

DROP TABLE [表名]

DEFAULT约束

 

USE Northwind
CREATE TABLE ABC (ASD int Default 8,  

ASDE varchar(20) Default 'UNKNOWN')

CHECK约束

 

USE Northwind
ALTER TABLE Employees ADD CONSTRAINT CK_birthdate
CHECK (BirthDate > ' 01-01-1900 ' AND BirthDate < ’01-01 -2010’ )

PRIMARY KEY约束

 

CREATE TABLE AWC

(ASD int CHECK(ASD <100) PRIMARY KEY,

 ASDE varchar(80) Default 'UNKNOWN')

•UNIQUE约束

CREATE TABLE AAC

(ASD int CHECK(ASD <100) PRIMARY KEY,

 ASDE int UNIQUE)

FOREIGN KEY约束

 

1.CREATE TABLE ACC

(ASD int CHECK(ASD <100) PRIMARY KEY,

 ASDE int FOREIGN KEY REFERENCES AAC(ASD))

2.USE Northwind

ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT FK_Or_Cu

  FOREIGN KEY (CustomerID)

  REFERENCES Customers(CustomerID)

向表中插入数据

INSERT [INTO] <TABLENAME> VALUES <VALUE>

 

INSERT INTO MyTable  (PriKey, Description)

       SELECT ForeignKey, Description

FROM SomeView

 

 

更新数据

UPDATE  <TABLENAME> SET <Column_Name = Value>

[WHERE <Search condition>]

 

UPDATE TITLES SET Price = Price + 0.25*Price

Where Title_ID = ‘TC 777’

删除数据行

1.DELETE FROM JOBS WHERE JOB_ID >1000

2.DELETE FROM SALES

4.  TRUNCATE TABLE SALES

 

SELECT 语句

1.USE northwind

SELECT employeeid, lastname, firstname, title

FROM employees

2. 查询全部的行和列

USE NorthWind

SELECT * FROM employees

3.     查询全部行和特定列

SELECT firstname,lastname,birthdate FROM Employees

4.     查询限定范围的行

SELECT * FROM employees WHERE city =' LONDON '

ORDER BY 子句

USE northwind

SELECT productid, productname, categoryid, unitprice

FROM products

ORDER BY categoryid, unitprice DESC

使用常量和运算符

USE Pubs

SELECT title_id +':'+ title + '->' + type

FROM titles

AS 子句

 

USE Pubs

 

SELECT title_id +':'+ title + '->' + type As MyTitle

FROM titles

带有约束的 SELECT 语句

 

USE NorthWind

Select IDENTITY(int,1,1) As P_id into

product_Det From products

TOP 子句

1.     限制返回数据行

2.  

SELECT Top 3 EmployeeID,LastName,FirstName

From Employees

2.限制返回行百分比

SELECT Top 80 Percent EmployeeID,LastName,FirstName

From Employees

聚合函数

 

1.SELECT SUM(ReportsTo) As Total

From Employees

2. SELECT AVG(ReportsTo) As Total

From Employees

3. SELECT COUNT(*) As Counts

From Employees WHERE ReportsTo >2

4. Use Pubs

Select Max(qty) As MaxValue from Sales

5. Use Pubs

Select Min(qty) As MinValue from Sales

GROUP BY

1.  USE northwind  

SELECT productid, SUM(quantity) AS total_quantity  FROM orderhist     GROUP BY productid   HAVING SUM(quantity)>=30

2. Use Pubs

SELECT type, AVG(price) FROM titles WHERE royalty = 10 GROUP BY ALL type

模糊查询

 

SELECT companyname FROM customers

WHERE companyname LIKE '%Restaurant%‘

IS NULL

SELECT Companyname, Fax FROM Suppliers

WHERE fax IS NULL

IN

SELECT Companyname, Country FROM suppliers

WHERE country IN (' Japan ', ' Italy ')

BETWEEN

SELECT productname, unitprice FROM products

WHERE unitprice BETWEEN 10 AND 20

 

多表联结查询

 

1. 内联接

USE joindb

SELECT buyer_name, sales.buyer_id, qty FROM buyers

INNER JOIN sales ON buyers.buyer_id = sales.buyer_id

2.左向外联接

USE joindb

SELECT buyer_name, sales.buyer_id, qty FROM buyers

LEFT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_id

3.右向外联接

USE joindb

SELECT buyer_name, sales.buyer_id, qty FROM buyers

RIGHT OUTER JOIN sales ON buyers.buyer_id = sales.buyer_id

4.自联接

USE joindb

SELECT a.buyer_id AS buyer1, a.prod_id

      ,b.buyer_id AS buyer2 FROM sales AS a

 JOIN sales AS b ON a.prod_id = b.prod_id

WHERE  a.buyer_id > b.buyer_id

5．交叉联接

USE joindb

SELECT buyer_name, qty FROM buyers

CROSS JOIN sales

 

使用外联接更新（不要求掌握）

1．UPDATE TITLES SET Price = Price + 10

From Titles LEFT OUTER JOIN TitleAuthor

ON Titles.Title_IS = TitleAuthor.Title_ID

WHERE Titles.Title = 'SuShi,AnyOne?'

 

2．UPDATE TITLES SET Price = Price + 10

From Titles RIGHT OUTER JOIN TitleAuthor

ON Titles.Title_IS = TitleAuthor.Title_ID

WHERE Titles.Title = 'SuShi,AnyOne?'

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3.