用户
搜索

该用户从未签到

i春秋-呆萌菜鸟

Rank: 1

3

主题

3

帖子

28

魔法币
收听
0
粉丝
0
注册时间
2015-10-19
发表于 2015-10-19 16:10:48 207059
我的学习笔记,包含基本的表操作和查询操作,资料绝对一手,对sql不太了解的同学可以快速入门,比较菜,高手退散
  1. use master
  2. go
  3. --如果系统数据库表中有此数据库,删除原数据库
  4. if exists(select *from sysdatabases where name='StudentManageDB')
  5. drop database StudentManageDB
  6. create database StudentManageDB
  7. on primary
  8. (
  9.         --数据库的逻辑名
  10.         name='StudentManageDB_data',
  11.         --数据库物理文件名
  12.         filename='E:\Vcare .net培训\DB\StudentManageDB.mdf',
  13.         --数据库初始大小
  14.         size=10MB,
  15.         --数据库增长量
  16.         filegrowth=2MB
  17. )
  18. --创建次要文件
  19. ,
  20. (
  21.         name='StudentManageDB_data1',
  22.         filename='E:\Vcare .net培训\DB\StudentManageDB1.ndf',
  23.         size=10MB,
  24.         filegrowth=2MB
  25. )
  26. log on
  27. (
  28.         name='StudentManageDB_log',
  29.         filename='E:\Vcare .net培训\DB\StudentManageDB.ldf',
  30.         size=3MB,
  31.         filegrowth=1MB
  32.        
  33. )
  34. ,
  35. (
  36.         name='StudentManageDB_log1',
  37.         filename='E:\Vcare .net培训\DB\StudentManageDB1.ldf',
  38.         size=3MB,
  39.         filegrowth=1MB
  40.        
  41. )
  42. go

  43. --添加SQL账户
  44. use master
  45. go
  46. exec sp_addlogin 'xiaohong','password02!'

  47. --添加数据库用户
  48. --对指定数据库创建数据库用户
  49. use StudentManageDB
  50. go
  51. exec sp_grantdbaccess 'xiaohong','xiaohongdbuser'


  52. --删除对应的数据库用户架构
  53. use StudentManageDB
  54. go
  55. drop schema xiaohongdbuser
  56. --删除账号对应数据库用户
  57. exec sp_dropuser'xiaohongdbuser'
  58. --删除登录账号
  59. use master
  60. go
  61. exec sp_droplogin 'xiaohong'

  62. --建表
  63. use StudentManageDB
  64. go
  65. if exists (select * from sysobjects where name='Student')
  66. drop table Students
  67. go
  68. create table Students
  69. (
  70.         StudentId int identity(100000,1),--创建标示列 学号,自动增长列
  71.         StudentName varchar(20) not null,--学生姓名
  72.         Gender char(2)  not null,
  73.         Birthday smalldatetime not null,
  74.         StudentIdNo numeric(18,0) not null,--身份证号(18整数,0小数)
  75.         Age int not null,--int类型默认为4个字节
  76.         PhoneNumber varchar(50),
  77.         StudentAddress varchar(500),
  78.         ClassId int not null --班级编号(外键)
  79. )
  80. go

  81. --创建班级表
  82. if exists(select * from sysobjects where name='StudentClass')
  83. drop table StudentClass
  84. go
  85. create table StudentClass
  86. (
  87.                 ClassId int primary key,--直接定义主键
  88.                 ClassName varchar(20) not null--班级名称
  89. )
  90. go   

  91. --插入实体
  92. use StudentManageDB
  93. go
  94. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  95. values('张三','男','1990-07-23',21,120105199007233014,'13899893898','天津市西青区宾水西道399号',1)
  96. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  97. values('李四','男','1989-07-23',22,120105198907233014,'18637872872','天津市河北区狮子林大街',3)
  98. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  99. values('王五','男','1988-01-07',23,120106198801073014,'15987984987','天津市红桥区三条石',2)
  100. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  101. values('赵六','女','1991-08-22',20,120101199108223013,'13289987898','',1)
  102. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  103. values('孙七','男','1987-12-01',24,120106273827382738,'13899893898','天津市西青区',1)
  104. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  105. values('周巴','男','1991-09-10',20,120103199109103014,'13787849787','天津市河东区',2)
  106. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  107. values('吴九','女','1990-10-01',21,120102199010013012,'13838787837','天津市南开区广开四马路',2)
  108. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  109. values('郑十','女','1989-02-28',22,120104198902283012,'13678997667','',3)
  110. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  111. values('冯一','男','1990-06-15',21,120106199006153014,'13787899877','天津市红桥区光荣道',1)
  112. insert into Students (StudentName,Gender,Birthday,Age,StudentIdNo,PhoneNumber,StudentAddress,ClassId)
  113. values('陈二','男','1990-08-10',21,120108199008103012,'13567898778','',2)

  114. select *from Students

  115. --标识列重新开始的删除!只能删除整个表的数据,不能删除带条件的数据!用于测试完成后恢复原样。
  116. truncate table Students

  117. --建立约束
  118. --实体完整性
  119. use StudentManageDB --指向要操作的数据库
  120. go
  121. --创建主键约束
  122. if exists(select * from sysobjects where name='pk_StuudentId')
  123. alter table Students
  124. drop constraint pk_StudentId--删除约束也是对表的修改!前面必须加上alter table Students!!!
  125. go
  126. alter table Students
  127. add constraint pk_StuudentId primary key (StudentId)
  128. go
  129. --创建唯一约束
  130. if exists(select * from sysobjects where name='uq_StudentIdNo')
  131. alter table Students
  132. drop constraint uq_StudentIdNo--删除约束也是对表的修改!前面必须加上alter table Students!!!
  133. go
  134. alter table Students
  135. add constraint uq_StudentIdNo unique (StudentIdNo)
  136. go

  137. --域完整性
  138. --创建检查约束
  139. if exists(select * from sysobjects where name='ck_Age')
  140. alter table Students
  141. drop constraint ck_Age--删除约束也是对表的修改!前面必须加上alter table Students!!!
  142. go
  143. alter table Students
  144. add constraint ck_Age check (Age between 18 and 25)
  145. go

  146. if exists(select * from sysobjects where name='ck_Gender')
  147. alter table Students
  148. drop constraint ck_Gender--删除约束也是对表的修改!前面必须加上alter table Students!!!
  149. go
  150. alter table Students
  151. add constraint ck_Gender check (Gender='男'or Gender='女')
  152. --创建默认约束
  153. if exists(select * from sysobjects where name='df_StudentAddress')
  154. alter table Students
  155. drop constraint df_StudentAddress--删除约束也是对表的修改!前面必须加上alter table Students!!!
  156. go
  157. alter table Students
  158. add constraint df_StudentAddress default ('本人地址不详') for StudentAddress  --和其他约束写法有区别
  159. go

  160. --插入带默认值的实体
  161. insert into Students (StudentName,Gender,Birthday,PhoneNumber,StudentAddress,ClassId,StudentIdNo,Age)
  162. values('王小毛','女','1990-06-09','13233344333',default,1,120106199006093012,21)
  163. select * from Students where StudentName='王小毛'

  164. insert into StudentClass (ClassId,ClassName)
  165. values (1,'软件一班')
  166. insert into StudentClass (ClassId,ClassName)
  167. values (2,'软件二班')
  168. insert into StudentClass (ClassId,ClassName)
  169. values (3,'软件三班')
  170. insert into StudentClass (ClassId,ClassName)
  171. values (4,'软件四班')
  172. --引用完整性
  173. --创建外键约束
  174. if exists(select * from sysobjects where name='fk_classId')
  175. alter table Students
  176. drop constraint fk_classId--删除约束也是对表的修改!前面必须加上alter table Students!!!
  177. go
  178. alter table Students
  179. add constraint fk_classId foreign key (ClassID) references StudentClass(ClassId)
  180. go

  181. --对表操作的授权
  182. --创建登录账号
  183. use master
  184. go
  185. exec sp_addlogin 'wangjian','password02!'
  186. --创建数据库用户
  187. use StudentManageDB
  188. go
  189. exec sp_grantdbaccess 'wangjian','wangjianDBUser1'

  190. grant select on Students to wangjianDBUser1

  191. --给数据库用户授予db_owner权限
  192. exec sp_addrolemember 'db_owner','wangjianDBUser1'
  193. --改变数据库的所有者
  194. --exec sp_changedbowner 'sa'

  195. --第四讲 查询

  196. select StudentName as 姓名,StudentId as 学号,性别=Gender from Students
  197. where ClassId=2 order by StudentId DESC

  198. select 姓名和电话=StudentName+'('+PhoneNumber+')'from Students

  199. --创建成绩表
  200. use StudentManageDB
  201. go
  202. if exists (select * from sysobjects where name='ScoreList')
  203. drop table ScoreList
  204. go
  205. create table ScoreList
  206. (
  207.         Id int identity(1,1),
  208.         StudentId int not null,
  209.         CSharp int null,
  210.         SQLServerDB int null,
  211.         UpdateTime smalldatetime not null
  212. )
  213. go

  214. --创建ScoreList表约束
  215. if exists(select * from sysobjects where name='df_UpdateTime')
  216. alter table Students
  217. drop constraint df_UpdateTime--删除约束也是对表的修改!前面必须加上alter table Students!!!
  218. go
  219. alter table ScoreList
  220. add constraint df_UpdateTime default (getdate()) for UpdateTime
  221. --创建ScoreList外键约束
  222. if exists(select * from sysobjects where name='fk_StudentId')
  223. alter table ScoreList
  224. drop constraint fk_StudentId--删除约束也是对表的修改!前面必须加上alter table Students!!!
  225. go
  226. alter table ScoreList
  227. add constraint fk_StudentId foreign key (StudentId) references Students(StudentId)
  228. go

  229. --插入含空的值
  230. insert into ScoreList (StudentId,CSharp)values(100009,87)
  231. --查询空值
  232. select * from ScoreList where SQLServerDB is null
  233. select top 2StudentID,CSharp,所在学院='理工大学' from ScoreList

  234. insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100008,87,35)
  235. insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100009,90,58)
  236. insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100001,91,66)
  237. insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100002,62,88)
  238. insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100003,55,88)
  239. insert into ScoreList (StudentId,CSharp,SQLServerDB)values(100003,55,88)


  240. --排序查询
  241. select StudentId,(CSharp+5) as C#,DB=SQLServerDB from ScoreList
  242. order by SQLServerDB ASC,CSharp DESC
  243. select StudentId,CSharp as C#,DB=SQLServerDB from ScoreList
  244. order by SQLServerDB ASC,CSharp DESC

  245. --模糊查询
  246. select * from Students where StudentAddress like '天津%'
  247. select StudentName,Birthday,Age from Students where Age in(20,22,23)

  248. --使用聚合函数 必须是值类型!
  249. select SUM(CSharp) as C#总成绩 from ScoreList
  250. select COUNT(*) as 全校总人数 from Students
  251. select MAX(CSharp) as C#最高分,MIN(CSharp) as C#最低分,AVG(CSharp) as C#平均分 from ScoreList

  252. --多表间查询数据
  253. --使用多表链接查询
  254. --内链接(inner join...on...)
  255. use StudentManageDB
  256. go
  257. select StudentName,Students.StudentId,C#成绩=Csharp from Students
  258. inner join ScoreList on Students.StudentId=ScoreList.StudentId
  259. where CSharp>80
  260. --没有主外键关系,内连接几乎无法实现。
  261. --StudentId必须显式的写明在那个表!

  262. --多表连接查询
  263. use StudentManageDB
  264. go
  265. select StudentName,Students.StudentId,ClassName,C#成绩=Csharp,数据库成绩=SQLServerDB
  266. from Students
  267. inner join ScoreList on Students.StudentId=ScoreList.StudentId
  268. inner join StudentClass on Students.ClassId=StudentClass.ClassId
  269. where CSharp>60 and SQLServerDB>60

  270. select StudentName from Students where Gender='男'
  271. --左外连接(left outer join...on...)查询
  272. use StudentManageDB
  273. go
  274. select StudentName,Students.StudentId,Gender,C#成绩=Csharp,数据库成绩=SQLServerDB
  275. from Students
  276. left outer join ScoreList on Students.StudentId=ScoreList.StudentId
  277. where Gender='男'
  278. --对照内连接
  279. select StudentName,Students.StudentId,Gender,C#成绩=Csharp,数据库成绩=SQLServerDB
  280. from Students
  281. inner join ScoreList on Students.StudentId=ScoreList.StudentId
  282. where Gender='男'
  283. --左外连接优先保证前面的表
  284. --左外连接查询结果包括两个表所有满足连接条件的记录,
  285. --以及左表所有不满足条件的其他记录。这些不满住左表
  286. --的记录在结果的右边位置全部填充为NULL。

  287. --右外连接查询
  288. select * from StudentClass
  289. insert into StudentClass (ClassId,ClassName) values(5,'软件五班')

  290. select StudentId,StudentName,Students.ClassId,StudentClass.ClassName from Students
  291. right outer join StudentClass on StudentClass.ClassId=Students.ClassId
  292. --连接查询结果包括两个表所有满足连接条件的记录,
  293. --以及右表所有不满足条件的其他记录。这些不满住左表
  294. --的记录在结果的左边位置全部填充为NULL。
  295. --右外连接常用于查错
  296. --无论什么连接,on后面都写主外键!

  297. --分组查询
  298. select ClassId,StudentName from Students order by ClassId

  299. select ClassId,总人数=COUNT(*) from Students
  300. group by ClassId

  301. --常见错误:
  302. --select Students.ClassId,ClassName,总人数=COUNT(*) from Students
  303. --inner join StudentClass on Students.ClassId=StudentClass.ClassId
  304. --group by Students.ClassId
  305. --消息 8120,级别 16,状态 1,第 2 行
  306. --选择列表中的列 'StudentClass.ClassName' 无效,
  307. --因为该列没有包含在聚合函数或 GROUP BY 子句中。

  308. select 班级编号=avg(StudentClass.ClassId),ClassName,总人数=COUNT(*) from Students
  309. inner join StudentClass on Students.ClassId=StudentClass.ClassId
  310. group by ClassName

  311. --按照班级编号统计C#的平均分和数据库的最高分
  312. select 班级编号=avg(Students.ClassId),班级名称=ClassName,C#平均分=AVG(ScoreList.CSharp),数据库最高分=MAX(ScoreList.SQLServerDB),
  313. 班级总人数=Count(*)
  314. from ScoreList
  315. inner join Students on Students.StudentId=ScoreList.StudentId
  316. inner join StudentClass on Students.ClassId=StudentClass.ClassId
  317. group by ClassName
  318. --查询结果会有出入!

  319. --分组统计筛选:HAVING
  320. select 班级编号=avg(Students.ClassId),班级名称=ClassName,C#平均分=AVG(ScoreList.CSharp),数据库最高分=MAX(ScoreList.SQLServerDB),
  321. 班级总人数=Count(*)
  322. from ScoreList
  323. inner join Students on Students.StudentId=ScoreList.StudentId
  324. inner join StudentClass on Students.ClassId=StudentClass.ClassId
  325. group by ClassName
  326. having AVG(CSharp)>=80
  327. --where 只能对单个实体进行条件限制
  328. --where子句:从数据源中去掉不符合其搜索条件的数据
  329. --group by子句:搜集数据行到各个组中,统计函数为各个组计算统计值
  330. --having子句:在分组结果中,去掉不符合其组搜索条件的各组数据行
复制代码



评论一下,证明我已经看过这篇文章啦
使用道具 举报 回复
看看,学习一下。
使用道具 举报 回复
发表于 2015-10-27 17:13:22
赞一个!
使用道具 举报 回复
发表于 2015-11-15 22:44:58
使用道具 举报 回复
发表于 2016-2-10 23:46:27
赞赞赞+++++++
使用道具 举报 回复
发表于 2016-2-11 10:15:38
sql-cheat-sheet
使用道具 举报 回复
发表于 2016-2-11 11:34:15
学习了!!
使用道具 举报 回复
发表于 2016-2-11 12:26:00
我来占个位置。。。。。
Hacking the earth.My Blog:https://isbase.cc
使用道具 举报 回复
day day up!
使用道具 举报 回复
发表于 2016-3-5 08:19:48
赞.....................
使用道具 举报 回复
发表于 2016-3-5 09:20:28
看看,学习啦,感谢分享,很详细呢
使用道具 举报 回复
看看,学习啦,感谢分享,赞
使用道具 举报 回复
发表于 2016-3-5 12:27:18
谢谢分享
使用道具 举报 回复
发表于 2016-3-5 12:45:16
thank you for sharing !!
使用道具 举报 回复
发表于 2016-3-26 00:45:49
受益了受益了受益了受益了
使用道具 举报 回复
12下一页
发新帖
您需要登录后才可以回帖 登录 | 立即注册