常用字段类型:bit(可选值 0、1)、datetime、int、varchar、nvarchar(可能含有中文的就用nvarchar) (var:variable)
char(n) 不足长度n的部分用空格填充
SQL语句对大小写不敏感
创建表:CREATE TABLE T_Person ( Id int NOT NULL,Name nvarchar(50),Age int NULL)、Drop table T_Person1
简单的Insert语句。INSERT INTO T_Person(Id,Name,Age) VALUES(1,'Jim',20)
在需要建立的主键后面添加identity(1,1)如CREATE TABLE T_Person ( Id int NOT NULL identity(1,1),Name nvarchar(50),Age int NULL)、Drop table T_Person1
即表示增量1
SQL主要分DDL(数据的定义语言)和DML(数据操作语言)两类。Create Table、Drop Table 等属于DDL,Select、Insert等属于DML
SQL Server中两种常用的主键数据类型:int(或bigint)+标识列(又称 自动增长字段);uniqueindentifier(又称Guid、UUID)
标识列时间字段自增可以避免并发问题,将字段的“是标识列”设置为“是”,一个表只能有一个标识列。可以设置标志增量
Guid算法是一种可以产生唯一标志的高效算法,在公元3400年以前产生的GUID与其他任何产生过的GUID都不相同。SQL Server 中生成GUID的函数是newid(),.Net中生成GUID的方法:Guid.NewGuid(),返回Guid类型。
把Guid类型主键的默认值设置为newid( )就会自动生成。
Insert into person (Id,Name,Age) values(newid(), ‘tom’ , 30)
数据的更新:
更新一个列:UPDATE T_Person Set Age = 30
更新多个列:UPDATE T_Person Set Age = 30,Name = ‘tom’
更新一部分数据:UPDATE T_Person Set Age = 30 where Name = ‘tom’,SQL中判断用的是单个的 = ,而不是 = =
Where 中还可以用复杂的判断 where Name = ‘tom’ or age <25
Where 中还可以用其他的逻辑运算符:or , and , not , < , > , <= , >= , != (或<>),IN(1,2,5)
数据的删除:
删除全部数据:DELETE FROM T_Person
Delete 只是删除数据,表还在,和Drop Table 不同
Delete 可以带where 子句:DELETE FROM T_Person where FAGE > 20
数据的检索:
简单的数据检索:SELECT * FROM T_Employee
只检索需要的列:SELECT FName , FAge FROM T_Employee
列别名:SELECT FNumber AS 编号FROM T_Employee
可以检索不与任何表关联的数据:selecct 1+1; select newid(); select getdate();
数据排序:
ORDER BY 子句位于SELECT语句的末尾,可以按一个列或多个列进行排序,还可以指定升序(ASC)或降序(DESC)
按年龄升序排序所有员工信息:SELECT * FROM T_Employee ORDER BY FAge ASC
按年龄降序,如果年龄相同则按工资升序:SELECT * FROM T_Employee ORDER BY FAge DESC ,FSalary DESC
ORDER BY 子句要放到WHERE子句之后:SELECT * FROM T_Employee WHERE FAge > 23 ,ORDER BY FAge ASC
通配符过滤 LIKE:
单字符匹配的通配符为半角下划线“_”,它匹配单个出现的字符。以任意字符开头,剩余部分为 ”erry” ,SELECT * FROM T_Employee WHERE FName LIKE ‘_erry’
多字符匹配的通配符为半角百分号 “%”,它匹配任意次数(零或多个)的字符。”k%”匹配以”k”开头、任意长度的字符串。
检索姓名中含有字母 ”n” 的员工信息:SELECT * FROM T_Employee WHERE FName LIKE ‘%n%’
数据分组:
按照年龄进行分组统计各个年龄段的人数:
SELECT FAge, Count (*) FROM T_Employee GROUP BY Fage
GROUP BY 子句必须放到WHERE语句之后
没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的(聚合函数中除外)
错误:SELECT FAge ,FSalary FROM T_Employee GROUP BY FAge
正确:SELECT FAge ,AVG ( FSalary ) FROM T_Employee GROUP BY FAge
有唯一的FSalary对应组
Having 语句:
在Where中不能使用聚合函数,聚合函数必须使用Having,Having 要位于Group By之后,SELECT FAge ,COUNT (*) AS 人数 FROM T_Employee
Having 不能使用未参数分组的列,Having不能替代Where。作用不一样,Having是对组进行过滤
限制结果集的函数:
Select top 5 * from T_Employee order by FSalary Desc
检索按照工资从高到低排序检索从第六名开始一共三个人的信息:
SELECT top 3 * FROM T_Employee WHERE FNumber NOT IN ( SELECT TOP 5 FNumber FROM T_Employee BY FSalary DESC) ORDER BY FSalary DESC
去掉数据重复:
SELECT DISTINCT FDepartment FROM T_Employee
DISTINCT 是对整个结果集进行数据重复处理的,而不是针对每一个列,因此下面语句并不会只保留Fdepartment 进行重复值处理(而是两个列一起判断):
SELECT DISTINCT FDepartment,FSubCompany FROM T_Employee
联合结果集:
SELECT FNumber,Fname,FAge FROM T_Employee UNION SELECT FldCardNumber,FName,Fage FROM T_TempEmployee
两个查询结果合并
基本原则:每个结果集必须有相同的列数;每个结果集的列必须类型相容。
UNION ALL 在UNION之后加ALL会防止数据因为重复而丢失,速度也更快,因为不用扫描重复。
SELECT ‘正式员工最高年龄’,MAX(FAge) FROM T_Employee
UNION ALL
SELECT ‘正式员工最低年龄’,MIN(FAge) FROM T_Employee
SELECT FNumber,FSalary FROM T_Employee
UNION
SELECT ‘工资合计’,SUM(FSalary) FROM T_Employee
数据库函数:
数字函数:
ABS():求绝对值。
CEILING():舍入到最大整数,进一法,ceiling->天花板。
FLOOR():舍入到最小整数,去尾法,floor->地板。
ROUND():四舍五入。
字符串函数:
LEN():计算字符串的长度
LOWER(),UPPER():转小写,转大写
LTRIM():去掉字符串左边的空格
RTRIM():去掉字符串右边的空格
SUBSTRING(string,start_position,length):参数string为主字符串,start_position为自字符串在住字符串中的起始位置,length为子字符串的最多长度。
SUBSTRING(‘abcdef111’,2’3) (bcd)
日期函数:
GETDATE():取得当前日期时间。
DATEADD(datepart,number,date),计算增加后的日期。Date为待计算日期,number为增量,datepart为计量单位。DATEADD(DAY,3,date)计算date三天后的日期,DATEADD(MONTH,-8,date)计算date八个月前的日期。
DATEDIFF(datepart,startdate,enddate):计算两个日期之间的差额。
DATEPART(datepart,date)返回一个日期的特定部分。
Datepart的可选值:year(yy,y),quarter(qq,q),month,dayofyear(一年中第几天),week,day…
统计不同工龄的员工的个数:select DateDiff(year,FIndate,Getdate()),count(*) from T_Employee GROUP BY DateDiff(year,FIndate,getdate())
类型转换函数:
CAST (expression AS data_type)
CONVERT(data_type,expression)
SELECT FIdNumber,
RIGHT(FldNumber,3) as后三位
CAST(RIGHT(FIdNumber,3)AS INTEGER)as 后三位的整数形式
流控函数:
空值处理函数:
ISNULL(expression,value):如果expression不为空则返回expression,否则返回value。SELECT ISNULL(FNAME,‘佚名’)as 姓名 FROM T_Employee
CASE函数用法:
CASE expression
WHEN value1 THEN returnvalue1
WHEN value2 THEN returnvalue2
WHEN value3 THEN returnvalue3
ELSE defaultreturnvalue
END
例子:
SELECT FName,
(CASE FLevel WHEN 1 THEN ‘VIP客户’
WHEN 2 THEN ‘高级用户’
WHEN 3 THEN ‘普通用户’
ELSE ‘客户类型错误’
END)as FLevelName
FROM T_Customer
索引:Index
创建索引的方式,在表设计器中右键,选择“索引/键”->添加->在列中选择索引包含的列
使用索引能提高查询效率,但索引也是占空间的,而且添加、更新、删除数据的时候也需要同步更新索引,因此会降低Insert,Update,Delete 的速度,只有在经常检索的字段上才创建索引。
CREATE TABLE CallRecords (Id int NOT NULL identity,CallerNumber nvarchar(10),TelNum nvarchar(20),StartDateTime datetime,EndDateTime datetime)
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('003','123456789',CAST('2010-10-10 10:00' AS datetime),CAST('2010-10-10 11:00' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('001','123456789',CAST('2010-10-10 10:00' AS datetime),CAST('2010-10-10 10:23' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('001','9876543',CAST('2010-10-11 7:00' AS datetime),CAST('2010-10-11 7:33' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('001','123456789',CAST('2010-10-31 5:00' AS datetime),CAST('2010-10-31 5:11' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('002','13579246810',CAST('2010-10-15 6:00' AS datetime),CAST('2010-10-15 7:00' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('002','1111111',CAST('2010-10-2 16:00' AS datetime),CAST('2010-10-2 16:33' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('001','123456789',CAST('2010-10-3 19:00' AS datetime),CAST('2010-10-3 19:18' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('003','1111111',CAST('2010-10-7 10:00' AS datetime),CAST('2010-10-7 10:52' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('003','13579246810',CAST('2010-10-19 12:00' AS datetime),CAST('2010-10-19 12:12' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('001','9876543',CAST('2010-10-20 18:00' AS datetime),CAST('2010-10-20 18:02' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('004','9876543',CAST('2010-9-10 10:00' AS datetime),CAST('2010-9-10 10:30' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('002','1111111',CAST('2010-8-31 15:00' AS datetime),CAST('2010-8-31 15:11' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('003','13579246810',CAST('2010-9-30 10:00' AS datetime),CAST('2010-9-30 11:00' AS datetime))
INSERT INTO CallRecords(CallerNumber,TelNum,StartDateTime,EndDateTime)VALUES ('001','9876543',CAST('2010-10-1 12:00' AS datetime),CAST('2010-10-1 12:12' AS datetime))
输出所有数据中通话时间最长的5条记录
SELECT top 5 * FROM CallRecords
ORDER BY DATEDIFF(minute,StartDateTime,EndDateTime) DESC
输出以9开头的电话号码记录
SELECT * FROM CallRecords
WHERE TelNum LIKE '9%'
输出本月内通话时间最长的前三名呼叫员
SELECT top 3 CallerNumber,SUM(DATEDIFF(minute,StartDateTime,EndDateTime))AS Diff FROM CallRecords
WHERE DATEDIFF(MONTH,StartDateTime,GETDATE()) = 0
GROUP BY CallerNumber
ORDER BY Diff DESC
输出本月拨打电话次数最多的三个呼叫员的编号
SELECT Top 3 CallerNumber,COUNT(*) AS CallTime FROM CallRecords
WHERE DATEDIFF(MONTH,StartDateTime,GETDATE()) = 0
GROUP BY CallerNumber
ORDER BY CallTime DESC