SQL Server的学习

一、建库和表
1.新建数据库
语法：

CREATE DATABASE SuperMarket
//建立一个名为SuperMarket的数据库。

2.打开数据库
语法：

USE SuperMarket
//打开刚刚建立的数据库

3.建表
语法：
CREATE TABLE Product(
　　　　　　　　　　　　cProdCode CHAR(6),
　　　　　　　　　　　　vProdName VARCHAR(50),
　　　　　　　　　　　　mPrice MONEY,
　　　　　　　　　　　　vFrom VARCHAR(30)
)
4.添加记录

 语法：

　　INSERT INTO Product(cProCode,vProdName,mPrice,vFrom) VALUES('000001','IBM X201笔记本',6800,'美国')

　　INSERT INTO Product(cProCode,vProdName,mPrice,vFrom) VALUES('000002','APPLE MacBook笔记本',9900,'美国')

5.修改记录

把IBM X201的笔记本的价格更改为6600元

语法 ：

UPDATE Product SET mPrice=6600 WHERE cProCode='000001'

二、删除数据库和表

1.删除Product表

语法 ：

DROP TABLE Product

2.删除SuperMarket数据库

语法 ：

USE master//注意：删除数据库之前要用USE打开另一个数据库，如master

DROP DATABASE SuperMarket

四、确定表中的关键字

1.关键字的种类

侯选关键字：一个可以唯一确定一行的属性称为侯选关键字；

主键：被选作唯一确定一行的侯选关键字；

候补关键字：在表中没有被选作主键的候选关键字；

组合关键字：表中多个属性才可以唯一确定一行时 ，这些属性称为组合关键字；

外键:两个表具有“关系”，当一个表中的主关键字在另一个表中也作为一个属性存在时，称为外键；

2.确定表中的关键字

2.1主键

2.2组合关键字

例如：表中表示s001做p003项目需要9小时，s002做p001项目需要18小时，以此类推；

2.3外键

公司员工(Employee)和公司部门(Department)存在关系

五、使用SELECT显示所有数据

语法：

SELECT * FROM Client

操作步骤：

（1）打开Microsoft SQL SERVER Management Studio;

（2）连接到远程服务器；

（3）点击“新建”查询，打开查询编辑器；

（4）输入：SELECT * FROM Client,然后点击“执行”按钮；

（5）显示查询结果；

六、使用SELECT显示指定列

我们使用“SELECT * FROM 表”查看所有数据，所以只需要把*替换为指定列，并用逗号隔开即可；

语法：

SELECT vClientName,vContacter,cMobile FROM Client

七、使用WHERE条件查询符合条件的记录

语法：

SELECT * FROM Client WHERE cClientCode=‘41010066’

注：如果条件是字符串或者日期类型，需要在条件的两边加单引号。

 八、IS NULL的使用方法

NULL在SQL SERVER数据库中是比较特殊的值，代表没有，并不是字符串，所以NULL和‘NULL’是两个概念；

语法：

SELECT * FROM Client WHERE vEmail IS NULL

九、TOP的用法

TOP用于取前几名或者百分比，ORDER BY则指明排序方式，默认升序ASC,可以省略，如果是降序，使用关键字DESC。

语法：

SELECT TOP 5 * FROM CampusRecruitment ORDERY BY siTestScore DESC

十、集合函数

（1）使用集合函数统计数据（Sum Avg Max Min Count）

（2）包含集合函数的列必须是数值类型或者货币类型

集合函数可以按某列求和、平均值、最大值、最小值、统计记录数，再配合WHERE条件就可以实现初步的统计功能。

语法：

SELECT SUM(mTotal) '笔记本部' FROM DailySails WHERE cDepartmentCode='02'

SELECT SUM(mTotal) '数码部' FROM DailySails WHERE cDepartmentCode='03'

SELECT SUM(mTotal) '耗材部' FROM DailySails WHERE cDepartmentCode='04'

十一、使用GROUP BY分类汇总

语法：

SELECT cDepartmentCode,SUM(mTotal) '销售金额' FROM DailySails GROUP BY cDepartmentCode

分析：SELECT后有两列，中间用逗号隔开，第一列表示分类的列，如cDepartmentCode，第二列表示统计列，如SUM(mTotal)。GROUP BY后面为分类的列。

注：在“查询编辑器”中，可以把GROUP BY句子放在第二行，这样看起来更清晰。

十二、使用GROUP BY ...HAVING分类汇总后二次筛选

语法：

SELECT cDepartmentCode,SUM(mTotal) '销售金额' FROM DailySails

　　GROUP BY cDepartmentCode

　　HAVING SUM(mTotal)>5000

十三、使用COMPUTE BY分类汇总

GROUP BY 可以实现分类汇总，而COMPUTE...BY...则在实现分类汇总的基础上同时能够显示明细。

 语法：

SELECT * FROM DailySails

　　ORDER BY cDepartmentCode

　　COMPUTE SUM(mTotal) BY cDepartmentCode

注：COMPUTE...BY...的前提是对分类列排序，所以SQL语句中用ORDER BY cDepartment进行排序，尔后COMPUTE 汇总列 BY 分类列。

十四、使用COMPUTE...BY...COMPUTE分类汇总

语法：

SELECT * FROM DailySails

　　ORDER BY cDepartmentCode

　　COMPUTE SUM(mTotal) BY cDepartmentCode

　　COMPUTE SUM(mTotal)

十五、LIKE模糊查询

例如查询一个含“广”字的员工

语法：

SELECT * FROM Employee WHERE vEmployeeName LIKE '%广%'

十六、使用DISTINCT筛选重复的列

例如显示所有员工的省份并删除相同的省份

语法：

SELECT DISTINCT(vProvince) FROM Employee

十七、数据库常用函数

（一）字符串函数

1.ASCII(‘A’)  -->65　　　　 返回A的ASCII值

2.CHAR(65)  -->'A'　　　　 返回ASCII为65所对应的字符

3.LEFT('Hello',4)-->‘Hell’　　取左四个字符

4.RIGHTH('Hello',3)-->'llo'　取右三个字符

5.LEN('Hello')-->5　　　　　字符的长度

6.LOWER('Hello')-->'hello'　转换为小写

7.UPPER('hello')-->'HELLO'　转换为大写

8.LTRIM('  Hello')-->'Hello'　去除左边空格

9.RTRIM('Hello ')-->'Hello'　去除右边空格

10.SUBSTRING('Hello',3,2)-->'ll'　　从第3个字符开始取2个字符

11.STUFF('Hello',2,3,'abcd')-->'Habcdo'　　用abcd替换从第2个字符开始的3个字符

（二）日期函数

1.DATEADD(日期元素，数字，日期)-->向指定日期添加“数字”个“日期元素”

2.DATEIFF(日期元素，日期1，日期2)-->返回两个日期之间的“日期元素”的个数

3.DATENAME(日期元素，日期)-->以字符串形式返回“日期元素”，如“May”

4.DATEPART(日期元素，日期)-->以数字形式返回“日期元素”，如“5”

5.GETDATE()-->返回当前的日期和时间

日期元素：

yy-->年

mm-->月

dd-->日

hh-->小时

ss-->秒

（三）数学函数

1.ABS(-5)-->5

2.RAND()-->0到1之间的随机浮点数

3.ROUND(数值表达式，长度)-->指定长度进行四舍五入

4.SQRT(浮点表达式)-->指定值的平方根

例：获取Email域名地址

语法：

SELECT SUBSTRING(vEmail,CHARINDEX('@',vEmail)+1,LEN(vEmail)-CHARINDEX('@',vEmail)) FROM Client

注：CHARINDEX('@',vEmail)返回@字符的位置；LEN(vEmail)返回Email的总长度；计算域名的地址长度=总长度-@的位置；SUBSTRING(vEmail,CHARINDEX('@',vEmail)+1,LEN(vEmail)-CHARINDEX('@',vEmail))则表示从vEmail中从“第@的位置+1”取“域名长度”个字符串。

十八、多表操作JOIN...ON...

注：SELECT * FROM 表一 JOIN 表二 ON 表一.条件列=表二.条件列；

　　一般常用于两个表存在外键关系，两个表名的列名可以不完全相同，但应该有相同条件。

语法：

SELECT vEmployeeName,cMobile,vDepartmentName FROM Employee

　　JOIN Department

　　ON Employee.cDepartmentCode=Department.cDepatmentCode

JOIN以后：

 十九、左连接或右连接LEFT/RIGHT OUTER JOIN　　

LEFT OUTER 表示JOIN左边的无论ON条件如何都全部显示；

RIGHT OUTER表示JOIN右边的无论ON条件如何都全部显示；

在正常的JOIN...ON...的基础上，再把JOIN左边的表全部显示出来：

语法：

SELECT vEmployeeName,cMobile,vDepartmentName FROM Employee

　　LEFT　JOIN Department

　　ON Employee.cDepartmentCode=Department.cDepartmentCode

二十、多表操作UNION

SELECT 列1，列2，列3 FROM 表一 UNION SELECT 列1,列2，列3 FROM 表二；

语法：

SELECT vContacter,vPhone,cMobile FROM Client

　　UNION

SELECT vEmployeeName,vPhone,cMobile FORM Employee

二十一、SELECT...INTO...把一个表中的数据复制到另一个新表

语法：

SELECT * INTO tmp_Client FROM Client

二十二、INSERT  INTO...把一个表中的数据复制到另一个老表

语法：

　　INSERT INTO AllSails SELECT * FROM DailySails

二十三、子查询

当一个查询是另一个查询的条件时，称为子查询；

语法：

　　SELECT * FROM Newspaper

　　　　WHERE vCity=(SELECT vCity FROM Campus WHERE vCampusName='郑州大学')

二十四、修改表的结构

无论添加一列、修改一列还是删除一列，都需要修改表的结构，使用ALTER TABLE语句；

1.添加一列

添加一列电子邮件（vEmail）:

ALTER TABLE Employee ADD vEmail VARCHAR(50)

2.修改一列

修改地址列（vAddress）的长度为50：

ALTER TABLE Employee ALTER COLUMN vAddress VARCHAR(50)

3.删除一列

删除爱好列（vFav）：

ALTER TABLE Employee DROP COLUMN vFav

验证：使用SP_HELP Employee语句查看现在表的结构。

二十五、删除多条记录DELETE  FROM  WHERE...IN...

语法：

DELETE FROM Employee WHERE cEmployeeCode IN('E00001','E00004','E00005')

二十六、视图

创建视图 CREATE VIEW 

我们通过创建视图可以完成简化查询的功能

语法：

CREATE VIEW vwEmployeeDepartment

AS

　　SELECT vEmployeeName,cMobile,vDepartmentName FROM Employee

　　JOIN Department

　　ON Employee.cDepartmentCode=Department.cDepartmentCode

 

CREATE VIEW vwXX AS SQL语句，SQL语句是我们常见的SELECT语句，即可以是一个表，也可以是多个表的查询结果。

创建视图成功后，我们可以把视图看作一个表来查询，如：SELECT * FROM vmEmployeeDepartment

注：虽然我们感觉视图像一个表，但实际上视图并不是表，是虚拟的表，我们把Employee表和Department表称为基表。

修改视图 ALTER VIEW 

如果打算在视图vmEmployeeDepartment中把员工手机号也显示出来，只需加上cMobile即可

ALTER VIEW vwEmployeeDepartment

AS

　　SELECT vEmployeeName,cMobile,vDepartmentName,cMobile FROM Employee

　　JOIN Department

　　ON Employee.cDepartmentCode=Department.cDepartmentCode

删除视图DROP VIEW

语法：

　　DROP VIEW vmEmployeeDepartment

二十七、索引

索引：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。

1.使用索引的优点

　　提高执行查询的速度

　　实施数据唯一性

　　加速了表之间的连接

2.使用索引的缺点

　　创建索引要花时间

　　需要大量的磁盘空间来存储数据和数据源

　　每次修改数据都需要更新索引

3.索引的种类

　　在簇索引中：数据被物理地排序，每个表只可创建一个簇索引。

　　在非簇牵引中：行的物理顺序不同于索引的顺序，非簇索引一般用于连接和WHERE子句的列，且它的值可能被经常修改。

　　当给出CREATE INDEX命令时，SQL Server缺省地创建非簇索引，每个表可多达249个非簇索引。

4.索引的特性

　　索引加速了连接表的询问、执行排序和分组。

　　索引可用来实施行的唯一性

　　索引对数据大多是唯一的列很有用

　　当你修改索引列的数据时，相关索引会被自动更新

　　你需要时间和资源来维护索引，不要创建不被经常性使用的索引

　　簇索引应在非簇索引之前被创建，簇索引改变了行的顺序，如果非簇索引在簇索引之前被创建，那么它需要被重新构造

语法：

CREATE NONCLUSTERED INDEX idxCampusRecruitment ON CampusRecruitment(cCampusCode)

CREATE CLUSTERED INDEX idxCampus ON Campus (cCampusCode)

 

二十八、约束

1.数据完整性

　　数据完整性保证了在数据库中存储数据的一致性和正确性，数据完整性大致可分为以下四种类型：

　　实体完整性：保证每一个列都能由称为主键的属性来唯一标识

　　域完整性：保证只在有效范围内的值才能存储到列中

　　引用完整性：保证外键的值必须与相关的主键值相匹配

　　用户定义完整性：指的是由用户指定的一组规则，它不属于实体、域或引用完整性

2.创建约束

　　创建约束来保证数据的完整性：

　　约束定义了必须遵循的用于维护数据一致性和正确性的规则

　　约束可以在创建表时一同创建，也可随后加入

　　约束可在两个层次上实施：列层和表层

例：

CREATE TABLE Newspaper(

　　　　　　　　　　　　　　cNewspaperCode CHAR(2) PRIMARY KEY,

　　　　　　　　　　　　　　vNewspaperName char (20) NOT NULL,

　　　　　　　　　　　　　　cProvinceCode CHAR(2) DEFAULT ('41'),

　　　　　　　　　　　　　　vPhone VARCHAR(13) CHECK(vPhone LIKE('[0][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))

　　　　　　　　　　　　　　)

CREATE TABLE NewsAd(

　　　　　　　　　　　　cNewsAdNo CHAR(2) PRIMARY KEY,

　　　　　　　　　　　　vNewsAdDescript VARCHAR(200),

　　　　　　　　　　　　cNewspaperCode CHAR(2) REFERENCES Newspaper(cNewspaperCode)

)

 

二十九、IF 条件语句

语法：

DECLARE @TestScore INT

　　SET @TestScore=(SELECT siTestScore FROM CampusRecruitment WHERE cStudCode='41010033')

　　IF @TestScore>90

　　　　PRINT '欢迎您参加面试，您的成绩为：'+CAST(@TestScore AS CHAR(3))

　　ELSE

　　　　PRINT'对不起，希望我们下次有机会合作！'

注：DECLARE @TestScore INT 表示声明一个整型变量；

SET @TestScore=(SELECT siTestScore FROM CampusRecruitment WHERE cStudCode='41010033')表示把学号为“41010033”学生的成绩赋值给@TestScore；

CAST(@TestScore AS CHAR(3))用于把整型转换为字符串；

 

三十、CASE...WHEN...分支语句

例：90分以上者显示“5号参加面试”，80分以上者显示“10号参加面试”，80分以下显示“'对不起，希望我们下次有机会合作！”

语法：

SELECT vStudName,siTestScore,面试日期=

　　CASE

　　　　WHEN siTestScore>=90 THEN '5号参加面试'

　　　　WHEN siTestScore>=80 THEN '10号参加面试'

　　　　WHEN siTestScore<80 THEN '对不起，希望我们下次有机会合作'

　　END

FROM CampusRecruitment

三十一、WHILE循环语句

例：新建一个空表，用WHILE循环插入100条记录

建表：

CREATE TABLE UserInfo(

　　　　　　　　　　　　vUserName VARCHAR(20),

　　　　　　　　　　　　vPassword VARCHAR(20),

　　　　　　　　　　　　vEmail VARCHAR(50),

　　　　　　　　　　　　cMobile CHAR(11),

　　　　　　　　　　　　dRegDate DATETIME

　　　　　　　　　　　　)

语法：

DECLARE @Count INT

SET @Count=0

WHILE (@Count<=100)

BEGIN

　　SET @Count=@Count+1

　　INSERT INTO UserInfo VALUES('User'+CAST(@Count AS CHAR(3)),'Password'+CAST(@Count AS CHAR(3)),'Email'+CAST(@Count AS CHAR(3)),'13903710'+CAST(@Count AS CHAR(3)),GETDATE())

END

待续