看到当年自己学SQL Server 的笔记
时间:2022-03-14 02:53
1 数据库 2 数据量DataBase,不同类型的数据应该放到不同的数据库中, 3 1.便于对各个数据类别进行个性管理 4 2.避免命名冲突 5 3.安全性更高; 6 table(表):数据库中的关系指的就是表; 7 一张表就是一个类,列就是类的字段,行就是一个类的对象 8 数据库的主键(Primary key); 9 主键就是数据行的唯一标识。不会重复的列才能当主键。一个表可以没有主键,但是会非常难以处理,因此没有特殊理由表都要设定主键 10 主键有两种选用策略:业务主键和逻辑主键。业务主键是使用有业务意义的字段做主键,比如身份证号、银行账号等;逻辑主键是使用没有任何业务意义的字段做主键,完全给程序看的,业务人员不会看的数据。因为很难保证业务主键不会重复(身份证号重复)、不会变化(帐号升位),因此推荐用逻辑主键。 11 12 1.主键的作用:唯一标识表中的一条记录。 13 2.选择多列同时作为一个主键→组合主键(复合主键).(一般不建议采用) 14 组合主键-复合主键(多列同时作为主键)—不推荐 15 1.唯一的。不能为空值 16 2.不经常变化的(稳定)比较稳定的列(不经常更新的,最好是建好以后再也不更新。) 17 3.大量字符串的列不适合作为主键 18 4.优先选择单列作为主键(避免使用组合主键) 19 5.优先使用逻辑主键(没有意义的),避免使用业务主键(身份证号、工号等。) 20 21 主键表,外键表 22 外键表就是A表引用了B表的主键,那么A表叫做外键表, 23 B表叫做主键表,两者之间的联系通过主键和外键联系, 24 25 创建数据库 26 create database MyDatabase 27 on 28 ( 29 name=‘文件名‘, 30 filename=‘文件地址‘, 31 size=3mb, 32 filegrowth=1mb,--自动增长 33 ) 34 log on --操作日志文件 35 ( 36 name=‘文件名_log‘, 37 filename=‘地址名_log‘, 38 size=1mb, 39 filegrowth=10%, 40 ) 41 42 创建表 43 create table Student 44 ( 45 stuid int primary key identity(1,1), 46 stuName nvarchar(15) not null, 47 stuGender bit not null, 48 stuAge int , 49 stuClass nvarchar(20), 50 ) 51 52 数据类型 53 nvarchar(10),存10个汉字,10个字母 54 varchar(10),里面的内容可以是10个也可以不是10,少于10个 55 char(10),5个汉字,10个字母, 如果存的内容(字母)小于10,不足的用空格补充.固定的内容 56 nchar(10),凡是带n的那么就可以存汉字 57 int bit(true,false)写代码的时候用1,0的方式 float 58 --查询表中的全部内容 59 select *from Student--(表名) 60 61 向表中插入数据 62 --第一种方式 63 insert into Student(stuName,stuGender,stuAge,stuClass)values(‘刘备‘,1,27,‘三年四班‘) 64 --第二种方式 65 insert into Student values(‘小乔‘,0,18,‘三年二班‘) 66 --第三种,可以多条插入 用select 和union 连接 67 insert into Student 68 select ‘貂蝉‘,0,18,‘三年四班‘ union 69 select ‘郭嘉‘,1,38,‘三年三班‘ union 70 select ‘张飞‘,1,34,‘三年四班‘ 71 修改表中的数据 72 --修改数据 关键字 是update <表名> set <要修改的列名=要修改的值> where 条件 73 update Student set stuAge-=1 74 update Student set stuAge=30 where stuid=1 75 76 删除数据 77 --删除数据 delete from <表名> where <条件> 78 delete from student where stuid=7 79 --delete 删除的是数据,表还在,但自动增长的Id会接着已经删除的id接着加,不会在从1开始 80 --第二种删除方式 能把表给删除 81 drop table student 82 --第三种方i 式删除 删除的也是数据 表还在 但id是从1开始 83 truncate table student 84 --truncate 效率要比 delete 快的多 85 86 约束--非空约束 87 not null 88 --主键约束 89 primary key 90 --为年龄增加一个检查约束:年龄必须在-120岁之间,含岁与岁。 91 alter table student add constraint CK_stuAge check(stuAge >=0 and stuAge<=120) 92 --为EmpId增加一个主键约束 93 alter table student add constraint PK_stuId primary key(stuid) 94 --非空约束,为stuName增加一个非空约束 95 alter table student alter column stuName varchar(50) not null 96 --为stuName增加一个唯一约束 97 alter table student add constraint UQ_stuName unique(stuName) 98 --为性别增加一个默认约束,默认为‘男‘ 99 alter table student add constraint DF_stuGender default(‘男‘) for stuGender 100 --手动修改一下stuName的数据类型(varchar(200)) 101 alter table student alter column stuName varchar(200) 102 103 增加删除列 104 --增加一个EmpId列 105 --alter table <表名> add <要加的列名> <数据类型> 106 alter table student add EmpId int 107 108 --手动删除一列(删除列) 109 --alter table <表名> drop column <要删除的列名> 110 alter table student drop column empid --(要删除的列名) 111 112 查询表中的内容 113 --第一种方式 114 select stuName as ‘姓名‘,stuAge as ‘年龄‘from student 115 --第二种方式 116 select 姓名=stuName,年龄=stuAge from student 117 118 --带条件查询 119 select *from Student where stuGender=0 120 --查找前十位 top 121 select top 10 * from TblStudent 122 123 --查找按照年龄从小到大 排序 order by asc 124 select *from TblStudent order by TSAge asc 125 126 --查找按照年龄从大到小 排序 order by desc 127 select *from TblStudent order by TSAge desc 128 129 --查找年龄最小的前十位 130 select top 10 * from tblstudent order by TSAge asc 131 132 --查找年龄最大的显示总数的10% 133 134 select top 10 percent * from TblStudent order by TSAge desc 135 136 --去除重复姓名查找 137 select distinct TSName as ‘姓名‘ from TblStudent 138 139 聚合函数 140 141 --查询多少行; count()只能有一个参数 142 select COUNT(TSName) as ‘人数‘ from TblStudent 143 --也可以这样查询 144 select COUNT(TSName) as ‘人数‘,COUNT(TSCardId) as ‘标号‘ from TblStudent 145 146 --切换成绩表 147 select *from TblScore 148 149 --查询英语成绩最高 150 select MAX(tsenglish) as ‘英语最高‘ from TblScore 151 152 --查询英语最低 和数字最高分 153 select MIN(tsenglish) as ‘英语最低‘,MAX(tsmath) as ‘数学最高‘ from TblScore 154 155 --聚合函数还有sum()求和,avg()求平均值,len()长度, 156 157 多条件查询 158 --查询年龄在20-30之间的男生 159 select * from student where stuage>=20 and stuage<=30 and stugender=1 160 第二种做法 用between ...and ... 161 select * from student where stuAge between 20 and 30 and stugender =1 162 --in 查询班级id 为1 或者2 或者 3 的同学信息 163 select * from student where classid=1 or classid=2 or classid=3 164 第二种做法 165 select * fromstudent where classid in(1,2,3) 166 模糊查询 167 --名字中已张字开头的%代表任何内容,一字或多字 168 select 8 from student where tsName like ‘张%‘ 169 --姓张的后面跟个%符号的, 170 select 8 from student where tsName like ‘张[%]%‘ 171 --查询姓张的的但是后面只能有一个字‘_‘ 172 select 8 from student where tsName like ‘张_‘ 173 --查询姓张的姓名一共三个字 174 select 8 from student where tsName like ‘张%‘ and len(tsName)=3 175 176 数据分组 177 --查询每个班级的id有多少人 178 --group by 分组 179 select stuid as ‘班级的id‘, 180 count(*) as ‘人数‘ 181 from student 182 group by stuid 183 184 --查询每个班中男生有多少人 185 select stuid as ‘班级‘, 186 count(*) as ‘人数‘ 187 from student 188 where stuGender=1 189 group by stuid 190 191 --查询每个班人数超过5个的人 192 select stuid as ‘班级‘, 193 count(*) as ‘人数‘ 194 from student 195 group by stuid 196 having count(*) in(5) 197 --注意having 不能使用在未分组的列 having 是筛选的意思 198 199 --统计每种商品的总销售量 并进行降序排序 200 select 商品名称, 201 SUM(销售数量) as ‘总销售量‘ 202 from MyOrders 203 group by 商品名称 204 having SUM(销售数量) >100 205 order by ‘总销售量‘ desc 206 207 --请统计总销售价格超过3000元的商品和销售总价 并进行降序排序 208 select 商品名称, 209 SUM(销售数量*销售价格) as ‘总销售价‘ 210 from MyOrders 211 group by 商品名称 212 having SUM(销售数量*销售价格)>3000 213 order by ‘总销售价‘ desc 214 215 --统计各个客户对可口可乐的喜爱度(统计每个购买人对可口可乐的购买数量) 216 select 购买人, 217 sum(销售数量)as ‘销售总量‘ 218 from MyOrders 219 where 商品名称=‘可口可乐‘ 220 group by 购买人 221 order by ‘销售总量‘ desc 222 223 类型转换函数 224 数据类型转换的语法是 225 如果要把int类型转换为字符串 convert(char,列名) 226 例如如果要把所有的信息放在一个单元格之中的语法就是 227 select stuname+convert(varchar(10),stuAge)+stuClass from student 228 229 --表连接,首先列数相同 ,类型相同 230 231 select *from TblTeacher 232 233 select *from TblStudent 234 235 select ttname ,ttgender from TblTeacher 236 union --单独的union 去除重复 加上all 不去重复 237 select tsgender,tsaddress from TblStudent 238 239 select * from TblScore 240 241 --查询学生的的 最高成绩 最低成绩 总分 242 --第一种 243 select max(tenglish) as ‘最高成绩‘, 244 MIN(tenglish) as ‘最低成绩‘, 245 SUM(tenglish) as ‘总成绩‘ 246 from TblScore 247 248 --第二种 249 select ‘最高成绩‘,MAX(tenglish) from TblScore 250 union 251 select ‘最低成绩‘,MIN(tenglish) from TblScore 252 union 253 select ‘总成绩‘,SUM(tenglish) from TblScore 254 255 1 2 3 变成列 256 select 1 union 257 select 2 union 258 select 3 259 260 数据备份-- 261 select *from TblStudent 262 --把TblStudent表中的结构和数据,存到一个新表中,这个新表可以没有,在备份数据的同时新表自动生成 263 --就是把一个表复制过来 264 select *into newd from TblStudent 265 --表复制了 但没有数据 266 select *into newd1 from TblStudent where 1<>1 267 --第三种 表有了 没有数据 建议使用这个 268 select top 0 * into newd2 from TblStudent 269 --表要存在的一中 270 insert into newd3 select *from TblStudent 271 272 字符串函数 273 select len("字符串的长度"); 274 select datalength(‘aa‘);--获得字符串的字节数 275 --获得tblstudent表中tsname的字节数 276 select *,datalength(tsName)from tblstudent 277 select lower(‘转小写‘); 278 select upper(‘转大写‘); 279 select rtrim(‘去除字符串右侧的空格‘); 280 select ltrim(‘去除字符串左侧的空格‘); 281 select left(‘截取左边字符串‘,2)--‘截取‘ 从左边切2个 282 select right(‘从右边切字符串‘,2)-- ‘符串‘ 从右边切2个 283 select substring(‘str‘,1,2); 284 select getdate()-- 获取当前时间 285 select dateadd(year,5,getdate());--在当前时间加上5年 286 --计算生日 287 select datediff(year,‘2010-10-1‘,getdate()) 288 --获取年月日 时间的部分 289 select datepart(year,getdate()); 290 291 292 --创建一个存储过程(就相当于方法) 293 --计算两个数的和 294 create proc usp_addnumber --(usp_addnumber 相当于方法名 usp_ 是 我们自己写的标示) 295 @num1 int, 296 @num2 int 297 as 298 begin 299 select @num1+@num2 300 end 301 302 --执行存储过程的时候传参 ,有三种方式 303 --第一种 304 exec usp_addnumber 1,2 --(参数1 参数 2 ) 305 --第二种 306 exec usp_addnumber @num1=10,@num2=20 --(直接给参数名赋值) 307 --第三种 308 declare @n1 int=100, @n2 int=200 309 exec usp_addnumber @num1=@n1,@num2=@n2 310 311 --分页存储过程 312 create proc GetPage 313 @Page int, -- 页数 314 @ct int,-- 每页条数 315 @sumCt int output --返回总条数 316 as 317 begin 318 select *from (select 编号=row_number() over(order by NewsId desc),* from vw_news ) as news where news.编号 between (@Page-1)*@ct+1 and @Page*@ct 319 320 set @sumCt=(select COUNT(*)from vw_news) 321 322 end 323 324 325 326 327 328 329 330 331 332 333 334