002Sql Server 的SQL语句案例.docx

1、002Sql Server 的SQL语句案例 DBMS(DataBaseManagementSystem,数据库管理系统)和数据库。平时谈到“数据库”可能有两种含义：MSSQLServer、Oracle等某种DBMS；存放一堆数据表的一个分类(Catalog) 数据库的构成-管理软件/服务/数据文件(表,视图.) 不同品牌的DBMS有自己的不同的特点：MYSQL、MSSQLServer、DB2、Oracle、Access、Sybase等。对于开发人员来讲，大同小异 SQLSQLServerMSSQLServer。最常见的错误。除了Access、SQLServerCE等文件型数据库之外，大部分数

2、据库都需要数据库服务器才能运行。学习开发时是连接本机的数据库，上线运行时是数据库运行在单独的服务器。 Catalog(分类)(又叫数据库DataBase,表空间TableSpace),不同类的数据应该放到不同的数据库中 1.便于对各个Catalog进行个性化管理 2.避免命名冲突3安全性更高 Table(表):书都放到书架上,碗都放到橱柜中,不同类型的资料放到不同的“格子”中，将这种区域叫做“表”(Table)。不同的表根据放的数据不同进行空间的优化，找起来也方便。 列(Column)、字段(Field) 主键就是一个表中每个数据行的唯一标识。不会有重复值的列才能当主键。一个表可以没有主键，但

3、是会非常难以处理，因此没有特殊理由表都要设定主键主键有两种选用策略：业务主键和逻辑主键。业务主键是使用有业务意义的字段做主键，比如身份证号、银行账号等；逻辑主键是使用没有任何业务意义的字段做主键，完全给程序看的，业务人员不会看的数据。因为很难保证业务主键不会重复（身份证号重复）、不会变化（帐号升位），因此推荐用逻辑主键。数据库概念SQL Server 2008 常用字段类型：bit(可选值0、1)、datetime、int、varchar、nvarchar（可能含有中文用nvarchar） Nvarchar(50)、Nvarchar(MAX) varchar、nvarchar 和char(n)

4、的区别： char(n)不足长度n的部分用空格填充。Var：Variable，可变的。分类备注和说明类型说明二进制数据类型存储非子符和文本的数据Image可用来存储图像文本数据类型字符数据包括任意字母、符号或数字字符的组合Char固定长度的非 Unicode 字符数据Varchar可变长度非 Unicode 数据Nchar固定长度的 Unicode 数据Nvarchar可变长度 Unicode 数据Text存储长文本信息(指针,2G)Ntext存储可变长度的长文本日期和时间日期和时间在单引号内输入Datetime 日期和时间数字数据该数据仅包含数字，包括正数、负数以及分数intsmallint

5、整数floatreal数字货币数据类型用于十进制货币值MoneyBit数据类型表示是/否的数据Bit存储布尔数据类型SQL语句入门SQL主要分DDL（数据定义语言）和DML（数据操作语言）两类。Create Table、Drop Table、Alter Table等属于DDL，Select、Insert、Update、Delete等属于DML。创建表CREATE TABLE T_Person(Id int NOT NULL,Name nvarchar(50),Age int NULL)删除删除数据：DELETE FROM T_Person where Age=20删除表：Drop table

6、T_Person1简单插入INSERT INTO T_Person(Id,Name,Age) VALUES(1,Jim,20)更新一列：UPDATE T_Person Set Age=30多列：UPDATE T_Person Set Age=30, Name =Jim2局部数据：加where语句样例代码：CREATE TABLE T_Employee (FNumber VARCHAR(20),FName VARCHAR(20),FAge INT,FSalary NUMERIC(10,2),PRIMARY KEY (FNumber);INSERT INTO T_Employee(FNumber,

7、FName,FAge,FSalary) VALUES(DEV001,Tom,25,8300);INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES(DEV002,Jerry,28,2300.80);INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES(SALES001,John,23,5000);INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES(SALES002,Kerry,28,6200);INSERT

8、INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES(SALES003,Stone,22,1200);INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES(HR001,Jane,23,2200.88);INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES(HR002,Tina,25,5200.36);INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES(IT001

9、,Smith,28,3900);INSERT INTO T_Employee(FNumber,FAge,FSalary)VALUES(IT002,27,2800);SELECT FNumber AS 编号,FName AS 姓名,FAge AS 年龄 FROM T_Employee数据汇总：SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量）select count(*)as 记录总行数 from T_Employee;select max(FSalary) as 最大值 from T_Employee;select min(FSalary

10、)as 最小值 from T_Employee;select avg(FSalary)as 平均值 from T_Employeeselect sum(FAge)as 总值 from T_Employee;select count(*) as 工资大于的总数 from T_Employeewhere FSalary=5000; -大于岁的员工的最高工资SELECT MAX(FSalary) FROM T_Employee WHERE FAge25 -最低工资和最高工资SELECT MIN(FSalary) as 最低工资,MAX(FSalary)as 最高工资 FROM T_Employee数

11、据排序ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）select * from T_Employee order by FSalary ASC; select * from T_Employee order by FSalary DESC;select * from T_Employee order by FAge DESC;-按照年龄从大到小排序，如果年龄相同则按照工资从大到小排序select * from T_Employee order by FAge DESC,F

12、Salary ASC;-ORDER BY子句要放到WHERE子句之后SELECT * FROM T_Employee WHERE FAge23 ORDER BY FAge DESC,FSalary DESC通配符过滤通配符过滤关键字使用LIKE 。单字符匹配：SELECT * FROM T_Employee WHERE FName LIKE _erry多字符匹配：SELECT * FROM T_Employee WHERE FName LIKE %n% 通配符解释示例_一个字符A Like C_%任意长度的字符串B Like CO_% 括号中所指定范围内的一个字符C Like 9W01-2不在

13、括号中所指定范围内的一个字符D Like %A-D1-2空值处理SQL中使用is null、is not null来进行空值判断SELECT * FROM T_Employee WHERE FNAME is null SELECT * FROM T_Employee WHERE FNAME is not null多值匹配-INSELECT FAge,FNumber,FName FROM T_Employee WHERE FAge IN (23,25,28) -判断SELECT * FROM T_Employee WHERE FAGE=23 AND FAGE 1限制结果集行数SELECT top

14、 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 ORDER BY FSalary DESC) ORDER BY FSalary DESC去掉数据重复-样例ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);ALTER TABLE T_Employee ADD FDepart

15、ment VARCHAR(20);UPDATE T_Employee SET FSubCompany=Beijing,FDepartment=Development WHERE FNumber=DEV001;UPDATE T_Employee SET FSubCompany=ShenZhen,FDepartment=Development WHERE FNumber=DEV002;UPDATE T_Employee SET FSubCompany=Beijing,FDepartment=HumanResource WHERE FNumber=HR001;UPDATE T_Employee SE

16、T FSubCompany=Beijing,FDepartment=HumanResource WHERE FNumber=HR002;UPDATE T_Employee SET FSubCompany=Beijing,FDepartment=InfoTech WHERE FNumber=IT001;UPDATE T_Employee SET FSubCompany=ShenZhen,FDepartment=InfoTech WHERE FNumber=IT002;UPDATE T_Employee SET FSubCompany=Beijing,FDepartment=Sales WHERE

17、 FNumber=SALES001;UPDATE T_Employee SET FSubCompany=Beijing,FDepartment=Sales WHERE FNumber=SALES002;UPDATE T_Employee SET FSubCompany=ShenZhen,FDepartment=Sales WHERE FNumber=SALES003;-查看SELECT * FROM T_Employee-去重SELECT FDepartment FROM T_EmployeeSELECT DISTINCT FDepartment FROM T_Employee SELECT

18、DISTINCT FDepartment,FSubCompany FROM T_Employee联合结果集基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相容。 -样例CREATE TABLE T_TempEmployee (FIdCardNumber VARCHAR(20),FName VARCHAR(20),FAge INT, PRIMARY KEY (FIdCardNumber);INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES(1234567890121,Sarani,33);INSERT INTO T

19、_TempEmployee(FIdCardNumber,FName,FAge) VALUES(1234567890122,Tom,26);INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES(1234567890123,Yalaha,38);INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES(1234567890124,Tina,26);INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES(123

20、4567890125,Konkaya,29);INSERT INTO T_TempEmployee(FIdCardNumber,FName,FAge) VALUES(1234567890126,Fotifa,46); -查看select * from T_TempEmployee-简单的结果集联合SELECT FNumber,FName,FAge FROM T_Employee UNION SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee SELECT FNumber,FName,FAge,FDepartment FROM T_Employ

21、ee UNION SELECT FIdCardNumber,FName,FAge,临时工,无部门 FROM T_TempEmployee-UNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条SELECT FName FROM T_Employee UNION SELECT FName FROM T_TempEmployee ORDER BY FName DESC -如果不是确定要合并重复行，那么就用UNION ALLSELECT FName FROM T_Employee UNION ALL SELECT FName FROM T_TempEmployee ORDER BY FN

22、ame DESC -要求查询员工的最低年龄和最高年龄，临时工和正式员工要分别查询SELECT 正式员工最高年龄,MAX(FAge) FROM T_Employee UNION ALL SELECT 正式员工最低年龄,MIN(FAge) FROM T_Employee UNION ALL SELECT 临时工最高年龄,MAX(FAge) FROM T_TempEmployee UNION ALL SELECT 临时工最低年龄,MIN(FAge) FROM T_TempEmployee-查询每位正式员工的信息，包括工号、工资，并且在最后一行加上所有员工工资额合计。SELECT FNumber,FS

23、alary FROM T_Employee UNION ALL SELECT 工资合计,SUM(FSalary) FROM T_Employee数字函数ABS() ：求绝对值。CEILING()：舍入到最大整数 。3.33将被舍入为4、2.89将被舍入为3、-3.61将被舍入为-3。 Ceiling天花板FLOOR()：舍入到最小整数。3.33将被舍入为3、2.89将被舍入为2、-3.61将被舍入为-4。 Floor地板。ROUND()：四舍五入。舍入到“离我半径最近的数” 。Round“半径”。Round(3.1425,2)-样例CREATE TABLE T_Person (FIdNumbe

24、r VARCHAR(20), FName VARCHAR(20),FBirthDay DATETIME, FRegDay DATETIME,FWeight NUMERIC(10,2);INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES (123456789120,Tom,1981-03-22,1998-05-01,56.67);INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES (123456789121,Jim,1

25、987-01-18,1999-08-21,36.17);INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES (123456789122,Lily,1987-11-08,2001-09-18,40.33);INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES (123456789123,Kelly,1982-07-12,2000-03-01,46.23);INSERT INTO T_Person(FIdNumber,FN

26、ame,FBirthDay,FRegDay,FWeight) VALUES (123456789124,Sam,1983-02-16,1998-05-01,48.68);INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES (123456789125,Kerry,1984-08-07,1999-03-01,66.67);INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES (123456789126,Smith,1980

27、-01-09,2002-09-23,51.28);INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES (123456789127,BillGates,1972-07-18,1995-06-19,60.32);INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight) VALUES (123456789128,BillGates,1972-07-19,1995-06-19,-60.32);-查看select * from T_Person-

28、求绝对值。select FWeight,ABS(FWeight) from T_Person-舍入到最大整数。.33将被舍入为、.89将被舍入为、-3.61将被舍入为-3。Ceiling天花板select FWeight,CEILING(FWeight) from T_Person-舍入到最小整数。.33将被舍入为、.89将被舍入为、-3.61将被舍入为-4。Floor地板。select FWeight,FLOOR(FWeight) from T_Person-四舍五入。舍入到“离我半径最近的数”。Round“半径”。Round(3.1425,2)。select FWeight,Round(FWeight,3) from T_Person字符串函数LEN() ：计算字符串长度LOWER() 、UPPER () ：转小写、大写LTRIM()：字符串左侧的空格去掉 RTRIM () ：字符串右侧的空格去掉 SUBSTRING(string,start_position,length)-计算字符串长度SELECT LEN(sf