SQL基础用法(实例一)
时间：2021-07-01 10:21:17
帮助过：9人阅读
/*
2
3
4 2006年10月01日
5
6 SQL Server 数据库的基本操作
7 (1) 数据库的创建
8 (2) 数据表的创建以及相关约束的指定(含临时表)
9 (3) 数据的添/删/改
10 (4) 数据的查询
11
12 */
13
14 (
0)创建数据库
15 -- 指定数据库名称
16 -- (注：如果数据库名中包含空格可以使用[]将其标示)
17 create database [Super WC]
18 -- 关于数据文件的定义
19 on
20 (
21 name
= Super_WC_Data,
-- 逻辑名
22 filename
= ‘C:\Super_WC_Data.MDF‘,
-- 物理路径以及物理名
23 size
= 2MB,
-- 初始大小
24 maxsize
= 4MB,
-- 最大限制
25 filegrowth
= 1MB
-- 增长大小
26 )
27 -- 关于日志文件的定义
28 log on
29 (
30 name
= Super_WC_Log,
31 filename
= ‘C:\Super_WC_Log.LDF‘,
32 size
= 3MB,
33 maxsize
= 7MB,
34 filegrowth
= 20% -- 增长比例
35 )
36
37 -- 附加数据库
38 execute sp_attach_db
‘[Super WC]‘,
‘C:\Super_WC_Data.MDF‘,
‘C:\Super_WC_Log.LDF‘
39 -- 分离数据库
40 execute sp_detach_db
‘[Super WC]‘
41 -- 复制数据库
42 execute master.dbo.xp_cmdshell
‘copy C:\Super_WC_Data.MDF D:\Super_WC_Data.MDF‘
43 execute master.dbo.xp_cmdshell
‘copy C:\Super_WC_Log.LDF D:\Super_WC_Log.LDF‘
44
45
46 (
1)创建数据表
47
48 创建一个数据表：学生（students）
49 结构如下：
50 字段 类型 是否允许为空 约束 备注
51 no
char(
4) No 主键 学号
52
53 name
nvarchar(
8) No 唯一 姓名
54
55 birthday
datetime No 检查（至少18年前） 生日
56
57 age
tinyint No 缺省（默认等于当前时间减去生日） 年龄
58
59 sex
nchar(
1) No 缺省（默认
‘女‘） 性别
60
61 phone
char(
11) Yes 检查（要么没有，要么长度等于11） 电话
62
63 address
nvarchar(
24)No 地址
64
65 没有特别约束的情况：
66 create table student
67 (
68 no
char(
4)
not null,
69 name
nvarchar(
8)
not null,
70 birthday
datetime not null,
71 phone
char(
11)
null,
72 address
nvarchar(
24)
null
73 )
74
75 注意：没有特别约束的情况下，创建数据表可以参考“企业管理器”中“设计表”的操作格式！
76
77 包含约束的情况：
78 create table students
79 (
80 no
char(
4)
primary key,
81 name
nvarchar(
8)
unique,
82 birthday
datetime check(
datediff(
year, birthday,
getdate())
>= 18),
83 age
as datediff(
year, birthday,
getdate()),
84 sex
nchar(
1)
default(
‘女‘)
check(sex
= ‘女‘ or sex
= ‘男‘),
85 phone
char(
11)
check((phone
is null)
or (
len(phone)
= 11)),
86 address
nvarchar(
24)
87 )
88
89
90 create table scores
91 (
92 no
char(
4)
foreign key references students(no),
93 chinese numeric(
4,
1)
check(chinese
>= 0 and chinese
<= 100),
94 english numeric(
4,
1)
check(english
>= 0 and english
<= 100)
95 )
96
97 以上答案只是最简单的描述形式！
98
99 比较规范的写法是
100 先用create table声明数据表的结构，
101
102 CREATE TABLE students
103 (
104 no
char(
4),
105 name
nvarchar(
8),
106 birthday
datetime,
107 age
as DATEDIFF(
year, birthday,
getdate()),
108 sex
nchar(
1),
109 phone
char(
11),
110 address
nvarchar(
24)
111 )
112
113 然后再ALTER
TABLE ADD CONSTRAINT 分别指定每个字段的约束:
114 每个约束都有一个独特的名称，其中，命名规范采用以下格式：
115 约束类型的简写_表名_字段名
116 pk_students_no
117
118 ALTER TABLE students
119 ADD CONSTRAINT pk_students_no
PRIMARY KEY (no)
120
121 ALTER TABLE students
122 ADD CONSTRAINT uq_students_name
UNIQUE (name)
123
124 ALTER TABLE students
125 ADD CONSTRAINT ck_students_birthday
CHECK (
datediff(
year,
[birthday],
getdate())
>= 18)
126
127 ALTER TABLE students
128 ADD CONSTRAINT df_students_sex
default (
‘女‘)
for sex
129
130 ALTER TABLE students
131 ADD CONSTRAINT ck_students_sex
CHECK (
[sex] = ‘男‘ or [sex] = ‘女‘)
132
133 ALTER TABLE students
134 ADD CONSTRAINT ck_students_phone
CHECK (
[phone] is null or len(
[phone])
= 11)
135
136 相对应的对约束进行删除，则是通过DROP CONSTRAINT子句来完成：
137 ALTER TABLE students
138 DROP CONSTRAINT pk_students_no
139
140 ALTER TABLE students
141 DROP CONSTRAINT uq_students_name
142
143 注意：
144 约束只有添加与删除操作，没有修改操作！
145
146
147 注意：
148 其中，age(年龄)采用了“计算列”的表示方法！
149 “计算列”的定义：
150 在表中某个字段的值源于某一表达式的值（某一函数的运算结果或是其他字段的运算结果）！
151 比如：
152 某学生的成绩表结构如下：
153 数学
154 语文
155 体育
156 总分
157
158 创建命令如下：
159 create table scores
160 (
161 math numeric(
3,
1),
162 chinese numeric(
3,
1),
163 sport numeric(
3,
1),
164 total
as math
+ Chinese
+ sport
165 )
166
167 insert into scores
values (
80,
69,
95)
168
169 total 部分的值会自动计算，为
244
170
171 -- 创建临时表
172 -- 临时表将会存储在TempDB的临时数据库中
173 -- 当用户断开连接后，就会自动删除
174 -- 语法：在表名前加上#
175 create table #tt
176 (
177 a
int,
178 b
int
179 )
180
181 insert into #tt
values (
1,
1)
182 insert into #tt
values (
2,
2)
183
184 select * from #tt
185
186
187 (
2)数据操作(添加
/删除
/修改)
188
189 添加操作(
insert)的语法格式：
190 Insert [into] 数据表 (字段)
values (数据)
191
192 -- 添加记录(一般情况)
193 insert into students
194 (no,name,birthday,sex,phone,address)
195 values
196 (
‘0001‘,
‘AHuang‘,
‘2000-01-01‘,
‘男‘,
‘13307331100‘,
‘株洲‘)
197
198 (注意:
into 可以省略 )
199
200
201 -- 添加记录(如果是给所有字段添加数据，可以省略字段标示)
202 insert into students
203 values
204 (
‘0002‘,
‘ANing‘,
‘2008-08-08‘,
‘女‘,
‘13307330011‘,
‘北京‘)
205
206
207 -- 添加记录(如果是给具有默认约束的字段添加数据，想利用默认约束,可以利用default)
208 insert into students
209 values
210 (
‘0002‘,
‘ANing‘,
‘2008-08-08‘,
default,
‘13307330011‘,
‘北京‘)
211
212
213 删除操作(
delete)的语法格式：
214 Delete [from] 数据表
where 条件
215
216 -- 删除记录(删除所有)
217 delete from students
218
219 (注意:
from 可以省略,即可以:
delete students )
220
221 -- 删除记录(删除特定记录,可以通过 where 条件来过滤,比如:学号‘0001‘的学生记录)
222 delete from students
where no
= ‘0001‘
223
224
225 修改操作(
update)的语法格式：
226 update 数据表
set 字段
= 新值
where 条件
227
228 -- 修改记录(修改所有)
229 update students
set 性别
= ‘女‘
230
231 -- 修改记录(修改特定记录,可以通过 where 条件来过滤,比如:学号‘0001‘的学生记录)
232 update students
set 性别
= ‘男‘ where no
= ‘0001‘
233
234
235
236 (
3)数据查询
237
238 查询操作(
select)的语法格式：
239 select 字段
from 数据表
where 条件
order by 字段
240
241 -- 查询记录(查询所有行与所有列,指定数据表所有字段)
242 select no, name, birthday, sex, phone, address
from students
243
244
245 -- 查询记录(查询所有行与所有列,除了指定数据表所有字段,还可以通过 * 来指代所有字段)
246 select * from students
247
248 -- 查询记录(查询所有行与特定列,指定数据表特定字段)
249 select no, name, phone, address
from students
250
251 -- 查询记录(给字段指定别名)
252 select no
as ‘学号‘, name
as ‘姓名‘, phone
as ‘电话‘, address
as ‘地址‘ from students
253
254 -- 查询记录(合并字段并指定别名)
255 select no
as ‘学号‘, name
as ‘姓名‘, address
+ ‘ ‘ + phone
as ‘地址 电话‘ from students
256
257 (注意:合并字段的前提是字段类型必须一致,否则需要通过类型转换函数convert来实现!)
258
259 -- 类型转换函数 convert 语法
260 convert(目标类型, 数据)
261
262 convert(
varchar(
12),
1234)
263
264
265 (
4)数据查询的高级应用(利用PUBS数据库作为演示数据)
266
267 -- 根据价格对书籍表记录排序
268 select * from titles
order by price
269
270 (注意：默认数据排序采用升序：由低到高或是有小到大，可以通过设定关键字来调整)
271
272 -- 根据价格对书籍表记录排序(降序)
273 select * from titles
order by price
desc
274
275 -- 根据价格对书籍表记录排序(升序)
276 select * from titles
order by price
asc
277
278 -- 找出书籍表中最高与最低的价格
279 select max(price),
min(price)
from titles
280
281 -- 统计书籍表中书籍总数以及总价格和平均价格
282 select count(title_id),
sum(price),
avg(price)
from titles
283
284 -- 找出书籍表中最贵3本书
285 select top 3 title_id, price
from titles
order by price
desc
286
287 select * from titles
288 select * from jobs
289 --
290
291 -- 统计书籍表中每种类型的书籍总量
292 select type
as ‘书籍类型‘,
count(title_id)
‘书籍数量‘
293 from titles
group by type
294
295 select type, title_id
from titles
order by type
296
297 -- 统计书籍表中每种类型的书籍的预付款(advance)的总和
298 select type
as ‘书籍类型‘,
sum(advance)
‘预付款总和‘
299 from titles
group by type
300
301 -- 列出所有书籍类型
302 select type
as ‘书籍类型‘ from titles
303 select distinct type
as ‘书籍类型‘ from titles
304 select distinct type
as ‘书籍类型‘, title_id
from titles
305
306 -- 列出所有作者所在州
307 select distinct state
as ‘州‘ from authors
308 select distinct state
as ‘州‘, city
as ‘城市‘ from authors
where state
= ‘CA‘
309 select state
as ‘州‘, city
as ‘城市‘ from authors
where state
= ‘CA‘
310
311 (注:
distinct 列出指定字段的值同时剔出所有重复的！)
312
313
314 -- 根据给定的作者姓名列出其所著书籍名称
315 select * from authors
316 select * from titles
317
318 select a.au_lname
as ‘名‘, a.au_fname
as ‘姓‘, t.title
as ‘书籍名称‘
319 from authors
as a
join titleauthor
as ta
on a.au_id
= ta.au_id
320 join titles
as t
on ta.title_id
= t.title_id
321 where a.au_lname
= ‘Green‘ and a.au_fname
= ‘Marjorie‘
322
323
324 -- 根据给定的出版社名称列出其所有的出版书籍名称
325 select * from publishers
326
327 select p.pub_name
as ‘出版社‘, t.title
as ‘书籍名称‘
328 from publishers
as p
join titles
as t
on p.pub_id
= t.pub_id
329 where pub_name
= ‘New Moon Books‘
330
331 select pub_name
as ‘出版社‘, title
as ‘书籍名称‘
332 from publishers
as p
join titles
as t
on p.pub_id
= t.pub_id
333 where pub_name
= ‘New Moon Books‘
334
335
336 -- 在销售表中挑选1993年度的订单
337 select ord_num
as ‘订单编号‘, title_id
as ‘书籍编号‘, ord_date
as ‘订购日期‘
338 from sales
339 where ord_date
between ‘1993-01-01‘ and ‘1993-12-31‘
340
341 (注意：
between and 之间的值必须满足"开始点"
<="结束点")
342 select ord_num
as ‘订单编号‘, title_id
as ‘书籍编号‘, ord_date
as ‘订购日期‘
343 from sales
344 where ord_date
between ‘1993-12-31‘ and ‘1993-1-1‘
345
346
347 -- 在销售表中统计每本书的订单数量
348 select title_id
as ‘书籍编号‘,
count(ord_num)
as ‘订单总数‘ from sales
group by title_id
349
350
351 -- 在销售表中统计每本书的总订购量
352 select title_id
as ‘书籍编号‘,
sum(qty)
as ‘总定购量‘ from sales
group by title_id
353
354 -- 在销售表中统计每个商店的总订购量
355 select stor_id
as ‘商店编号‘,
sum(qty)
as ‘总定购量‘ from sales
group by stor_id
356
357 -- 在销售表中查询每个商店定购了那几本书以及每本书相应的订购量
358 select stor_id
as ‘商店编号‘, title_id
as ‘书籍编号‘,
sum(qty)
as ‘总定购量‘ from sales
group by stor_id, title_id
order by stor_id
359
360
361
