SQL操作（创建、检索、更新、条件、分组、排序、空值、distinct、union、case、date函数）

一、概述

■SQL语句是和DBMS“交谈”的专用语句，不同DBMS都认SQL语法。SQL语句中字符串用单引号

■SQL语句是大小写不敏感的，特指关键字，不包括字符串的值

SQL主要分DDL（数据定义语言）和DML（数据操作语言）两类。CREATE Table、DROP Table、ALTER Table等属于DDL，SELECT、INSERT、UPDATE、DELETE等属于DML

二、创建、删除表，插入数据

■create table question (q_Id int not null, q_Stem varchar(500))【列名1属性，列名2 属性】

■drop table question

■insert into question(q_Id,q_Stem) values(2,'测试试题')

★SQLServer中两种常用的主键数据类型：int（或bigint）+标识列（又称自动增长字段）;uniueidentifier(又称Guid、UUID)

★用标识列是想字段自增可以避免并发等问题，不要开发人员控制自增。用标识列的字段在insert的时候不用指定主键的值。将字段的“是标识列”设置为“是”

三、Guid算法

★Guid算法是一种可以产生位移标识列的高效算法，它使用网卡MAC、地址、纳秒级时间、芯片ID码等算出来的，这样保证每次生产的GUID永远不会重复，无论是同一个计算机还是不同的计算机。

★SQLServer中生产ＧＵＩＤ的函数newid（），.NET中生产的Guid的方法：Guid.NewGuid（），返回是Guid类型。

★Int自增字段的优点：占用空间小，无需开发人员干预、易读；缺点：效率低；数据导入导出的时候很痛苦

★Guid的优点：效率高、数据导入导出方便：缺点：占用空间大、不易读

业界主流倾向于使用Guid

四、表更新（插入、更新）

★insert into 数据库名.dbo.表名(列名1，列名2，列名3) values（NEWID()，’内容1’,’内容2’）

★update Person set NickName

★更新一个列：UPDATET_Person set Age=30

★更新多个列：updateT_Person Set Age=30，Name=’Tom’

★更新一部分数据：UpdateT_Person Set Age=30  where Name=’Tom’，用where语句表示只更新Name 是’Tom’的行，★注意SQL中等于判断用单个‘=’，而不是‘==’。

Where 中还可以使用辅助的逻辑判断Update T_person Set Age=30 Where Name=’Tom’or Age<25,or相当于C#中的||(或)

★where中可以使用的其他逻辑运算符：or、and、not、<、>、>=、<=、!=（或<>）

★update Person set NIckName=N’青年人’where age>=20 and age<=30,(注意数据库中设计中文字符时一般在前面加N，否则可能出现错误)

同时也可以在SQL查询语句中可以选中部分进行执行。

五、数据删除

★删除表中全部数据：Delete from T_Person

Delete只是删除数据，但表还在，比较删除表的Drop Table不同

★Delete也可以带where子句来删除一部分数据：delete fromT_Person where age>20;

六、数据检索

★简单的数据检索：Select * from Table

★只检索需要的列：Select列名 from table 、

★列别名：Select 列名1 as 别名1，列名2 as 别名2，列名3 as 别名3 from table

★使用where 检索符合条件的数据：select 列名1 from table where 列名>条件。

★还可以检索不与任何表示关联的数据：select 1+1；selectnewid（）；select getdate（）；select@@version

七、数据汇总

★ＳＱＬ聚合函数：max（最大值）、min（最小值）、avg（平均值）、sum（和）、count（数量）

★大于25岁的员工最高工资：

select max（列名工资）from table【员工表】 where 列名工资>25

★最低工资和最高工资：

Select MIN（列名工资）,max（列名工资） from table【员工表】

八、数据排序

★Order By 子句位于select 语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）或者降序（从大到小排列，DESC）。默认为升序

★按照年龄升序排序所有员工信息的列表：Select * from 表名 order by 列名 ASC

★对两列进行排序：select* from 表名 order by 列名1  DESC，列名2  ASC。排序条件用逗号隔开，从左到右的顺序判断优先级

★Order by 子句要放到where子句之后：select * from 表名 where 列名>条件order by 列名1  DESC　，列名2 ASC

 九、通配符过滤使用LIKE

★单个字符匹配的通配符为半角下划线“_”，它匹配单个出现的字符。

★以任意字符开头，剩余部分为“erry”：Select * from 表名 where 列名’_erry’

★多字符匹配的通配符为半角百分号“%”，它匹配任意次数（零或多个）出现的任意字符。“K%”匹配以“K”开头、任意长度的字符串。

检索姓名中包含字母“n”的员工信息：select * from 表名 where 列名 like ‘%n’

十、空值处理

★数据库中，一个列如果没有指定值，那么值就为null，这个null和C#中的null有区别，数据库中的null表示“不知道”，而不是表示没有。因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”

★Select * from 表名 where列名=null；

Select * from 表名 where列名！=null；

都没有任何返回结果，因为数据库也“不知道”

★SQL 中使用is null、is not null 来进行空值判读：select * from 表名 where 列名is null ；select * from 表名 where 列名is not null；

十一、数据分组

★按照年龄进行分组统计各个年龄段的人数：

select 列名，count（*） from表名 Group by 列名

★Group by子句必须放到where语句之后，没有出现在group by 子句中的列是不能放到select语句后的列名列表中的（聚合函数除外）

错误：select 列名1，列名2 from 表名 group by 列名1

正确：select 列名1，avg（列名2） from 表名Group by 列名1

十二、Having语句

★在where 中不能使用聚合函数，必须使用Having ，Having要位于group by之后，

★Select 列名1，count（*）as 人数 from 表名

Group by 列名1 Having count（*）>1

★注意having 中不能使用未参数分组的列，having 不能替代where ，作用不一样，having 是对组进行过滤

十三、限制结果集的行数

★Select top 5 * from表名

order by 列名1 desc（*）

★检索按照工资从高到低排序检索从第六名开始一共三个人的信息：select top 3* From表名 where 列名1

not in（slect top 5列名2 from 表名 order by 列名3 desc）

Order by 列名3 desc

SQLServer2005后增加了Row_Number函数简化实现，

十四、去掉数据重复

★Select 列名 from 表名

★Select distinct列名 from 表名

Distinct是对整个结果集进行数据重复处理的，而不是针对每一个列，因此下面的语句并不会只保留查询列进行重复值处理：selectdistinct列名1、列名2 from 表名

★后添加列：Altertable 表名 add 列名 varchar（29）

 

十五、联合结果集

★简单的结果集联合：select列名1，列名2，列名3 from 表名1  Union select 列名4 ，列名2，列名3，from 表名2

★基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相容

Select 列名1，列名2，列名3，列名4  From 表名1 union select  列名5，列名2，列名3 ，’临时工，无部门‘ from 表名2

★Union 对两个表中重复项进行自动删除，利用union all可以保存所有的数据

 Select 列名1 from表名1 union select 列名1，列名2 from 表名2。

★Union 合并两个查询结果集，并且将其中完全重复的数据合并为一条

Select 列名1，列名2 from 表名1 union all select 列名1，列名2 from 表名2 union因为要进行重复值扫描，所以效率低，因此结果不是确定要合并重复行，那么就用union all

十六、数据函数

★ABS（）：求绝对值。

★Ceiling：舍入到最大整数。3.33将被舍入为4。2.89 将被舍入为3。-3.54将被舍入为-3

★Floor();舍入到最小整数，与ceiling相反

★Round（）：四舍五入。舍入到“离我半径最近的数”。例如：★round——>。Round（3.1435,2）——执行结果为3.1400，其中2是小数的精确位数位数，

十七、字符串函数：

★Len（）：计算字符串长度

★Lower（）、upper（）：转小写、大写

★Ltrim（）：字符串做出的空格去掉

★★Rtrim（）：字符串右侧的空格去掉

Ltrim(Rtrim(‘    bb     ’))

★Substring（string，start_position，length）：

参数string为主字符串，start_position为字符串在住字符串的起始位置，length为字符串的最大长度。

Select substring（’abcdef111’,2,3）

十八、日期函数

★GETDATE():取得当前日期时间

★dateADD（日期格式（年月日等），数字，date）计算增加以后的日期。

参数date为待计算的日期：参数date为增量；参数datepart为计量单位，可选值见备注。

★DateADD（DAY，2，date）为计算日期date的3天后的日期，★dateADD（month，-8，date）为计算日期date的8个月前的日期

★datediff（日期格式，startdate，enddate）：计算两个日期之间的差额。Datepart为计量单位，可取值参考dateAdd

datepart（datepart，date）：返回一个日期的特定部分datepart（datepart）

★datepart可选值：

取值

别名

说明

year

yy，yyyy

年份

quarter

qq，q

季度

month

mm，m

月份

dayofyear

dy，y

当年度的第几天

day

dd，d

日

week

wk，ww

当年度的第几周

weekday

dw，w

星期几

minute

mi，n

分

second

ss，s

秒

millisecond

ms

毫秒

 

十九、类型转换函数：

★Cast（exprssion AS data_type）

★Convert (data_type,expression)

★Select fidnumber

Right(fidnumber,3)as 后三位

★Cast（right（fidnumber，3） as integer ）as 后三位的整数形式

★Cast（right（fidnumber，3） as integer）+1 后三位加1

★Convert（integer，right（fidnumber，3））/2 as 后三位除以2

From 表名

★ISNULL（expression，value）：如果expression不为空则返回expression，否则返回value。Select ISNULL(列名，’佚名’)as 姓名 from 表名

二十、Case函数的用法

★单值判断，相当于switch case

CASE expression

When value 条件1 then return value1

When value 条件2 then return value2

When value 条件2 then return value3

Else default return value

End