SQL Server基础——T-SQL语句

一. SQL-Server基础前言

1. 几大数据库巨头

Microsoft SQL Server 2000/2005/2008/2008R2/2012/2014

Oracle Database 11g/12c

IBM DB2 Database

MySQL Database (open source)

Mongo DB Database (NoSQL)

2. 2012简体中文版官方下载页面

www.microsoft.com/downloads/zh-cn/details.aspx?FamilyID=a74d1b60-6566-4551-b581-03337853b82b

3. 登录SQL Server时，默然选择Windows Authertication

二. 附加Attach、分离Detach 数据库文件

1. 右击Database，点Attach，点击Add，选择要添加的数据库mdf文件，点击OK

2. 如果出现错误信息提示“An error occurred when attaching the database(s).Click the hyperlink in the Message column for details.”

不要慌张，点击右上角的提示信息Message，这时如果显示Error:5120，可以去要添加的mdf文件处，右击属性，安全，编辑，完全控制。

再添加即可。

如果显示Error：5133，则要把ldf在下半部分的提示框中remove掉，再点OK即可。

3.右击要分离的数据库，选Tasks，点Detach，该数据库就消失了，并不是删除，只是断开了SQL Server这个软件和这个数据库的链接

三. 数据库图表关系图

1.打开一个数据库，打开第一个文件Database Diagrams，会看到很多dbo.为开头的图表，右键一个图表选择Modify，即可看到图表。

2. 可以在图表界面右键，选择添加Table，选择一个类型，

四. 什么是关系型数据库

1. RDBMS (Relational Database Management System) 关系型数据库管理系统

SQL也称T-SQL是一种标准的关系型数据库语言。

2. 表格里，行叫作Row/Record，列叫作Column/Attribute，其中的一个小格子叫作字段Field/Cell

3. Primary Key主键

唯一标识一行数据，而且主列键必须包含唯一的值，并且不能包含空值null

可以建立在每张二维表中单列或者多列上

4. Foreign Key外键

一张二维表上的外键可以引用另一张二维表上对应的主键

5. 一个表上的PK就是另一个表上的FK，一个表上的FK就是另一个表上的PK

PK和FK的关系可见下图，黄色的是钥匙，钥匙在哪，就表明是这个图里的PK。

但是两个表之间连接的钥匙对准哪，就说明那个图表提供PK，那么此PK就在另一张表里以FK的形式出现

五. T-SQL简介

1. Transact-SQL是结构性查询语言，是针对关系型数据库进行查询、修改以及定义的标准化计算机语言。

2. T-SQL query，从数据库中查询索取信息的请求。

3. 最基本的SQL查询语句

<必须> SELECT <table fields list>

<必须> FROM <table names list>

<非必须> WHERE  <row constraints specification>

<非必须> GROUP BY  <grouping specification>

<非必须> HAVING  <grouping selection specification>

<非必须> ORDER BY  <order rules specification>

SELECT   FROM   语句，从某一个table里面选出所要的列

WHERE  语句，对行有一些限定条件

GROUP  BY  语句，集合运算，比如求年龄的平均值

HAVING  语句，针对进行过的集合运算，进行一些限制条件

ORDER  BY  语句，按某一顺序进行排列

4. use 语句

导入一个server之后，要用use语句来选择要使用的数据库，然后在使用select语句

六. select ... from ... 语句

1. select...from 语句

select  *  from ...  这里的*表示表格里的所有列所有行

实际工作中，尽量少用*，因为数据量很可能太大

select Top 100 * from ... 这里要显示表格里的前100行

from后面的表格名称添加，可以手动添加，自己打在from后面，也可以在左边列表中找到表格，然后拖拽进代码里

select Name, Color, Size  from ...

这里要显示表格里的Name，Color和Size列

2. order by 关键字

select ... from ...

order by Size desc

这里要按照Size这一列里所显示的内容倒序排列

desc=descending order 倒序排列

asc=ascending order 正序排列

select ... from ...

order by Size desc, Name

这里要先按Size这一列所显示的内容倒序排列，然后再按Name内容正序排列

select Name, Color, Size  from ...

order by 2

这里是说要按照所列出的列里的第二列来正序排列，此处说的是Color

3. isnull 函数：判断某一数据是否为空

select Name, isnull(Color, ' '),Size from ...

此处显示的表格中，Color列里之前显示的空值null变为一个空白的格子，而且列名字也不显示了，需要重命名

4. as 关键字：给表列起“别名”

接着7的步骤，被隐藏的列需要重命名

select Name, isnull(Color, ' ') as Color, Size from ...

5. + 关键字：将“列”与“字符串”连接起来

select ProductID, Name as ProductName, 

'The list price for  ' + Size + '  is $  ' + convert (varchar,ListPrice) + ' . ' as [Description]

from ...

把Name列重命名为ProductName，把Size列重命名为Description，而且显示的内容为The list price for ... is $...

代码里的convert (varchar, ListPrice)，后面内容会解释

6. 算术表达式

操作符   描述

  +           加法

   -           减法

   *           乘法

   /            除法

select Name

, rate*40*52 as AnnualSalary

, round(rate*40*52,1) as AnnualSalary

, round(rate*40*52,0) as AnnualSalary

from ...

第一列显示姓名，第二列是计算年薪，第三列是年薪的数据小数点后保留一位小数，第四列是年薪的数据小数点后保留零位数

说明，rate是原来表格里的一列，表示小时薪水

七. where 关键字

1. where 语句中使用=、>、<、>=、<=、<>

select * from ...

where SalesPersonID=275

把表格中，SalesPersonID这一列中的数值等于275的显示出来

注意，因为这一列的数据属于int整数型，所以可以这样写

如果是string字符型，就要加引号，例如

select * from ...

where SalesOrderNumber='so43670'

select * from ...

where TotalDue>5000

表格中TotalDue列中值大于5000的显示出来

2. where 语句中使用and、or

select ...

from ...

where SalesPersonID=275 and TotalDue>5000

并列关系，表格中显示275这个人大于5000的销售业绩的数据

select ...

from ...

where SalesPersonID=275 and TotalDue>5000and Orderdate between '2005-08-01' and '1/1/2006'

表格中显示275这个人大于5000的销售业绩中，处于2005年8月1日和2006年1月1日之间的数据

select ...

from ...

where SalesPersonID=275 and TotalDue>5000 and Orderdate >=  '2005-08-01' and Orderdate < '1/1/2006'

表格中显示275这个人大于5000的销售业绩中，处于2005年8月1日和2006年1月1日之间的数据，但不包含2006年1月1日这一天

3. where 语句中使用 like、% 或 _ 通配符

通配符英文叫法是wildcard

select * from ...

where name like '%Mountain%'

表格中显示Name列中包含有Mountain的数据

此时的%表示，在Mountain前面可以有或没有任何字符，在Mountain后面可以有或没有任何字符

select * from ...

where name like 'Mountain%'

表格中显示Name列中是由Mountain开始的且包含Mountain的数据

select * from ...

where name like 'Mountain'

如果写成这样，就是表示name列中完全是Mountain的数据

select * from ...

where name like '_ountain%'

表格中显示Name列中第二个字母开始是ountain，且包含这个ountain的而且ountain后面可有可无其它字符的数据

这种情况一般是管理人员把一个单词的某一个字母忘记了

4. where 语句中使用 in 或 not in

select * from ...

where color in ('red' , 'white' , 'black')

表格中color列里是red、white和black的显示出来

select * from ...

where size in ('60' , '61' , '62')

表格中size列里是60、61和62的显示出来

select * from ...

where class not in ('H')

表格中class列里不是H类型的数据显示出来

这里的not in 还可以换成 <>

5. where 语句中使用 is null 或 is not null

select * from ...

where size is null

 表格中显示size列中是无效数据的数据

select * from ...

where size is not null

更常用

表格中显示size列不是无效数据的数据

6. where 语句中使用or或and

select * from ...

where color = 'white' or color = 'black'

表格中color列是white或black的数据显示出来，满足其中一个条件即可

select * from ...

where color = 'white' or color = 'black'

表格中color列是white且也是black的数据显示出来

select ...

from ...

where (SalesPersonID=275 or SalesPersonID=278) and TotalDue>5000

表格中275或者278这两个人，销售额大于5000的数据

八. 集合运算

1. count 数一下表格中有多少行

select count (SalesPersonID)

from ...

where SalesPersonID is not null

结果会返回一个数值，例如3000，表示一共有多少销售人员的记录

2. distinct 独一无二

select distinct (SalesPersonID)

from ...

where SalesPersonID is not null

结果返回销售人员，一一列出来，例如17

select count (distinct (SalesPersonID) )

from ...

where SalesPersonID is not null

返回一个数值17，数值就是销售人员的数量

九. 聚合函数 aggreated function

1. avg 平均值

select

Avg (TotalDue) as AverageTotalSales

from ...

结果返回一个数值，表示所有销售额的平均值

select

Avg (TotalDue) as AverageTotalSales

, Min (TotalDue) as MinimumTotalSales

, Max (TotalDue) as MaximumTotalSales

, Sum (TotalDue) as SummaryTotalSales

from ...

结果返回四列，分别每列为一个数，平均值，最小值，最大值，总值

2. 聚合函数返回都是一个值，并不是很多行

下面的代码就会提示错误

select SalesPersonID, Max (TotalDue) as MaximumTotalSales

from ...

但是可以用 group by 修改为下面的形式

select SalesPersonID, Max (TotalDue) as MaximumTotalSales

from ...

where SalesPersonID is not null

group by SalesPersonID

order by SalesPersonID

列出17个销售人员每个人的最大销售额，而且按照销售人员的ID号顺序排列

select SalesPersonID, OrderDate, Max (TotalDue) as MaximumTotalSales

from ...

where SalesPersonID is not null

group by SalesPersonID, OrderDate

order by SalesPersonID

结果会列出销售人员，时间和最大值三列，会有很多行

3. having 专门是为了限制聚合函数而使用的

select SalesPersonID, OrderDate, Max (TotalDue) as MaximumTotalSales

from ...

where SalesPersonID is not null

group by SalesPersonID, OrderDate

having Max (TotalDue) > 150000

order by SalesPersonID

只会列出最大销售额大于150000的数据，例如有5行

select SalesPersonID, OrderDate, Max (TotalDue) as MaximumTotalSales

from ...

where SalesPersonID is not null and OrderDate >= '2007/1/1'

group by SalesPersonID, OrderDate

having Max (TotalDue) > 150000

order by OrderDate desc

结果只有2行，比上面的例子少

4. 给代码加注释

十. 技巧

1. 如何显示Line Number

Tools >>> Options >>> Source Control >>> Transact-SQL >>> Display >>> Line numbers >>> OK

2. 如何自由转换queries大小写

选中需要转换的代码 >>> Edit >>> Advanced >>> Make Lowercase / Make Uppercase