sql server 应用与开发范例宝典.docx

sql server 应用与开发范例宝典sql server 应用与开发范例宝典04094. 创建数据库: CREATE DATABASE db_YYJXC095. 修改数据库: (1).ALTER DATABASE db_YYJXCMODIFY FILE(NAME=db_YYJXC_DATA,SIZE=2MB,FILEGROWTH=512KB)(2).ALTER DATABASE db_YYJXCADD FILE(NAME=db_YYJXC_DATA2,FILENAME=C:ProgramFilesMicrosoftSQLServerMSSQLDatadb_YYJXC_Data2.ndf,MAXSIZE=UNLIMITED,SIZE=2MB,FILEGROWTH=512KB)096. 删除数据库: DROP DATABASE db_YYJXC097. 创建数据表: CREATE TABLE 人员表 (编号 int IDENTITY PRIMARY KEY,姓名 varchar(10) NOT NULL,性别 varchar(2) NULL,地址 varchar(30) NULL,电话 varchar(20) NULL)098. 定义计算字段: CREATE TABLE 销售信息表 (药品编号 varchar(7) NOT NULL,药品名称 varchar(30) NOT NULL,规格 varchar(20) NULL,产地 varchar(30) NULL,数量 float NOT NULL,销售价 float NOT NULL,金额 AS(数量*销售价),销售票号 varchar(30) NOT NULL)099. 修改表: ALTER TABLE 人员表 ADD相片 image NULL100. 删除表: DROP TABLE 人员表101. 修改人员表名称: (1)EXEC sp_rename人员表,人员信息表(2)修改字段名称: EXEC sp_rename人员信息表.电话,联系电话,COLUMN102. 分离数据库: sp_detach_db dbname=db_YYJXC104. 附加单文件数据库: EXEC sp_detach_db dbname = pubsEXEC sp_attach_single_file_db dbname = pubs, physname = c:Program FilesMicrosoft SQL ServerMSSQLDatapubs.mdf105. 建立备份设备: sp_addumpdevice disk,mybackup,d:Backupmybackup.bak106. 删除备份设备: sp_dropdevice mybackup,delfile107. 备份数据库: BACKUP DATABASE db_CSell TO disk=d:mybackupdb_CSell备份.bak108. 还原数据库: RESTORE DATABASE db_CSellFROM disk=d:mybackupdb_CSell备份.bak05109. select 产品名称 from tb_mrbccd110. select 产品编号,产品名称,销售价格from tb_mrbccd111. select* from tb_mrbccd112. SELECT ID 编号,Name 姓名,Math_Score 数学成绩, Music_Score AS 音乐成绩, English_Score AS 英文成绩 FROM tb_StuScore113. SELECT 编号, 商品名称,数量,单价,进价, (单价*数量) AS 销售额,(单价*数量-进价*数量) AS 利润 FROM tb_xsb114. SELECT 商品名称 FROM tb_xsb WHERE (单价*数量-进价*数量)300115. 列出商品利润小于300的商品名称SELECT 商品名称 FROM tb_xsbWHERE (单价*数量-进价*数量)200 AND (单价*数量-进价*数量)= 90) OR (Math_Score=95)128. 利用_通配符查数学成绩在7099之间学生信息SELECT *FROM tb_StuScoreWHERE Math_score LIKE 8_129. 利用%通配符查询学生表中姓李的员工信息SELECT *FROM tb_Stu05WHERE 姓名 LIKE 李%130. 利用通配符查英语成绩9599之间学生信息SELECT * FROM tb_StuScoreWHERE English_score LIKE 95-9%131. 利用通配符查数学不在9099分间学生信息SELECT *FROM tb_StuScoreWHERE Math_Score LIKE 90-9132. 复杂的模式查询SELECT * FROM tb_StuScoreWHERE (Name LIKE 大%) AND (Math_Score LIKE 7_) OR (EngLish_score LIKE 90-9)133. 除去图书销售表中相同的图书信息SELECT DISTINCT 书号,书名,作者,出版社 FROM tb_BookSell ORDER BY 书号134. 列出图书销售表中图书重印超过1次的图书SELECT 书名,书号,作者,COUNT(书名) AS 重复数量FROM tb_BooksellGROUP BY 书名,书号,作者HAVING COUNT(书名)=2135. 查询学生信息表中备注信息不为空的学生信息SELECT 学生姓名,所在学院,备注信息FROM tb_StuInfoWHERE 备注信息 IS NOT NULL136. 列出学生信息表中备注信息为空的学生信息SELECT 学生姓名,所在学院,备注信息FROM tb_StuInfoWHERE 备注信息 IS NULL137. 利用OR运算符进行区间定位查询SELECT *FROM tb_StuScoreWHERE Math_Score 95 OR EngLish_Score95138. 利用AND运算符进行区间定位查询SELECT *FROM tb_StuScoreWHERE Math_Score 90 AND EngLish_Score90139. 应用OR、AND执行多个搜索条件查询SELECT *FROM tb_StuScoreWHERE (Math_Score =95 OR Music_Score95) AND EngLish_Score=90 140.统一字段格式进行查询SELECT 姓名：+ Name + ；编号：+CONVERT(varchar(3),id) AS 学生信息FROM tb_StuScore141. 将客户信息表中客户姓名字段中的空格去除SELECT 姓名,LTRIM(姓名) AS 去除左面空格, RTRIM(姓名) AS 去除右面空格, LTRIM(RTRIM(姓名) AS 去除左右空格 FROM 客户信息表142. 对工资数据表中查询出的工资进行四舍五入SELECT 人员姓名,ROUND(代扣税,-1) AS 代扣税 ,ROUND(本月扣零,0) AS 本月扣零, CAST(ROUND(应发合计,1) AS real) AS 应发合计 ,实发合计FROM 工资数据表06143. 在职工表中去除编号与姓名字符串中尾部空格select 编号,rtrim (编号) as 去空格后,姓名,rtrim(姓名) as 去空格后,专业,rtrim(专业) as 去空格后from tb_stu06144. 在商品订单表中截取客户的姓和名的信息select 姓名,substring(姓名,1,1) as 姓,substring(姓名,2,2)as 名字from tb_stu05145. 连接图书表中书名与类别生成图书说明信息select 图书名称,图书分类,(图书名称+ 的类别为 + 图书分类) as 说明from tb_aspnetbook146. 在报名表中将“200900”插入到原用户报名编号中select 学号,姓名,stuff(学号,2,2,200900) as 新学号from tb_stu05147. 获取字符在字符串中出现的次数select len(apples is in the apple tree)- len(replace (apples is in the apple tree,p,)/len(p) as afrom tb_name06148. 将外籍留学生表中的学生姓大写名小写select upper(firstname)as up_firstname ,lower(lastname )as low_lastnamefrom tb_name06149. 从字符串中删除元音字符select firstname,replace(replace(replace(replace(replace(firstname,A,),E,),I,),O,),U,)as 修改后from tb_name06150. 从字符串中分离字符和数字declare i nvarchar(50);set i=;declare j nvarchar(50);set j=;select i=i+b.c from(select substring(a.n,iter.pos,1) as c from (select asdf1*h456 as n)a ,(select id as pos from tb_num06)iterwhere iter.pos=len(a.n)b where ascii(b.c) between 48 and 57;select j=j+b.c from(select substring(a.n,iter.pos,1) as c from (select asdf1*h456 as n)a ,(select id as pos from tb_num06)iterwhere iter.pos=len(a.n)b where ascii(b.c) not between 48 and 57;select i as 数字,j as 字符151.去除字符串中非字母和数字的字符declare i nvarchar(50);set i=;declare j nvarchar(50);set j=as#df*4,56;select i=i+b.c from(select substring(a.n,iter.pos,1) as c from(select j as n)a ,(select id as pos from tb_num06) iter where iter.pos e.出版日期) 下一次出版日期 from tb_booksell06 e) x167. 将日期中的年月日以列的形式显示select 书名, year(select 出版日期 from tb_booksell06 where 书号 = 1) as 年份,month(select 出版日期 from tb_booksell06 where 书号 = 1)as 月份,day(select 出版日期 from tb_booksell06 where 书号 = 1)as 日期 from tb_booksell06 where 书号 = 1168. 查询当前日期加3个月以后的日期select dateadd(mm,3,getdate() as DAY NUMBER169. 分别获取某月份、本周和当前季度的第一天/*获取某月份第一天 */select dateADD(mm,datediff(mm,0,2009-3-18),0)as DAY_NUMBER/*获取本周的第一天*/select dateadd(wk,datediff(wk,0,getdate(), 0) as DAY_NUMBER/*获取当前季度的第一天*/select dateadd(qq,datediff(qq,0,getdate(),0)as DAY_NUMBER170. 分别获取上个月、去年和本年的最后一天/*获取上个月的最后一天*/select dateadd(ms,-3,dateadd(mm,datediff(mm,0,getdate(),0)as DATE NUMBER上个月最后一天/*获取去年最后一天*/SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate(), 0) as DATE NUMBER去年最后的一天/*获取本年的最后一天*/ SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()+1, 0)as DATE NUMBER本年最后的一天 171. 把长日期格式数据转化为短日期格式数据select 书名, convert(char(10),出版日期,120) as 日期 from tb_booksell06172.显示为日期格式数据中的小时、分、秒 SELECT CONVERT(nvarchar(10), getdate(), 8)as 当前时间173. 将日期可式中的“.”转化为“-”update tb_student06 set 入校时间=20+replace(入校时间,.,-)where 编号=3 07174. 利用SUM函数对职员工资进行汇总SELECT SUM(salary) AS 工资,SUM(salary + bonus) AS 工资及奖金FROM tb_treatment175. 利用AVG函数求学生各学科的平均成绩SELECT AVG(语文) AS 语文 ,AVG(代数) AS 代数 ,AVG(几何) AS 几何 ,AVG(英语) AS 英语 FROM tb_stuAchievement176. 利用MIN函数求销售额和利润的最低值SELECT DISTINCT(编号),商品名称,销价 AS 销售额最少, 利润 AS 利润最少,门店名称FROM tb_sell WHERE 销价 IN (SELECT MIN(销价) FROM tb_sell) OR 利润 IN (SELECT MIN(利润) FROM tb_sell)177. 利用MAX函数求员工月销售业绩的最高值SELECT tb1.销售员,tb1.销售额 AS 最高销售额from (SELECT 销售员,SUM(销售额) AS 销售额 FROM tb_SellerWHERE MONTH(日期)=12 GROUP BY 销售员) AS tb1WHERE tb1.销售额 = ( SELECT MAX(销售额) AS 最高销售额FROM (SELECT 销售员,SUM(销售额) AS 销售额 FROM tb_Seller WHERE MONTH(日期)=12 GROUP BY 销售员)AS tb1)178. 利用COUNT函数求日销售额大于某值的商品数SELECT COUNT(DISTINCT(书名) AS 图书种类FROM (SELECT 书名,日期,SUM(金额) AS 总金额FROM tb_BookSell GROUP BY 书名,日期 HAVING SUM(金额) 150) AS tb1179. 统计学生的总成绩并排序SELECT 学生编号,学生姓名,sum(高数+外语+计算机文化基础+马经+数据库管理+数据结构+软件工程) AS 总成绩 FROM tb_StuMark GROUP BY 学生编号 ,学生姓名ORDER BY 总成绩 DESC180. 使用COALESCE函数将NULL转换成0后，计算员工的平均工资SELECT 所属部门,AVG(COALESCE(工龄工资,0) AS 工龄平均工资 FROM tb_emPay GROUP BY 所属部门coalesce vi.合并；结合；联合 vt.使联合；使合并过去式coalesced 过去分词coalesced 现在分词coalescing 181. 使用ALL查询书名和出版社的全部分组SELECT 书名,出版社,SUM(金额) AS 总计金额FROM tb_BookSell WHERE 单价=48 or 单价=20GROUP BY ALL 书名,出版社182. 使用ROLLUP按所属类别和等级统计商品的总销售额SELECT 所属类别,商