SQL server 练习1（SQL server 数据库基本操作）

  本篇博客用来记录，编者做sql server练习的题目及解答，以及遇到的一些问题和总结。

题目

1、数据库创建 名称：stuManage 主数据文件： 逻辑文件名：stuManage_M_data 物理文件名：stuManage__data.mdf 初始大小：3M 递增：10% 限制文件增长：500M 次数据文件： 逻辑文件名：stuManage_N_data 物理文件名：stuManage__data2.ndf 初始大小：3M 递增：1M 限制文件增长：不限 日志文件： 逻辑文件名：stuManage_log 物理文件名：stuManage__log.ldf 其他信息：默认 物理文件的路径请设置为D盘的myData文件夹。

2、表的创建
在stuManag数据库中创建下面的表

3、表中数据的SQL方式编辑
（1）在表中添加1个新的系，并为该系添加2个专业信息和2条教师信息，具体数据自拟。
（2）将所有出生年月在年龄超过41岁职称为讲师的教师的职称改为“副教授”。
（3）将“d01006”号院系所有的专业名称前面加上“互联网”三个字。
（4）删除“d01006”号院系及其的所有相关信息。

4、思考题
（1）请思考如何使用SQL语句快速删除表中的全部数据？
（2）如果要实现一个巨型表并行读取，以提高读取效率，应当如何实现？

解答

1.建数据库 ``` -- 建立数据库 create database stuManage ON ( name = stuManage_M_data, fileName = 'D:\myData\stuManage_data.mdf', size = 5, -- 由于sql server允许的最小值为5m，只能从5m开始，不能从3m开始，否则报错主文件必须至少是 5 MB 才能容纳 model 数据库的副本 maxsize = 500, filegrowth = 10%) LOG ON( name = stuManage_N_data, fileName = 'D:\myData\stuManage__log.ldf', size = 3, filegrowth = 1);

2.建数据库表

– 创建表
– 创建院系信息表
use stuManage;
create table department(
dNo char(6) primary key, – 院系号
dName varchar(20) not null, – 院系名称
dTel varchar(11) – 院系电话
);

– 创建专业信息表
create table major(
mNo char(6) primary key, – 专业号
mName varchar(40) not null, – 专业名称
mFirSubject varchar(40) not null, – 一级学科
mSecSubject varchar(40),-- 二级学科
dNo char(6) constraint dNo_fore foreign key references department(dNo) – 院系编号
);

– 创建教师信息表
create table teacher(
tNo char(6) primary key, – 教师号
tName varchar(20) not null,-- 姓名
tSex char(4), – 性别
tBirth datetime, – 出生年月
tRank varchar(20) not null, – 职称
dNo char(6) constraint dNo_teacher_fore foreign key references department(dNo) – 院系编号
);

3.表中数据的SQL方式编辑
(1)

– 添加纪录
– 添加系记录
insert into department(dNo,dName,dTel) values(‘161’,‘金融数学与统计学院’,‘1234567’);
– 添加专业记录
insert into major(mNo,mName,mFirSubject,mSecSubject,dNo)
values(151612,‘信息与计算科学’,‘理科’,‘数理科’,‘161’)
,(151611,‘金融数学’,‘理科’,‘数理科’,‘161’);
– 添加教师记录
insert into teacher(tNo,tName,tSex,tBirth,tRank,dNo)
values(‘1’,‘张三三’,‘男’,‘1990-10-10 12:10:00’,‘教授’,161),
(‘2’,‘李四四’,‘女’,‘1950-10-10 12:10:00’,‘教授’,161);

(2)将所有出生年月在年龄超过41岁职称为讲师的教师的职称改为“副教授”。

– 法一
update teacher set tRank = ‘副教授’ where datediff(yy,tBirth,getDate())>41 and tRank=‘讲师’;
– 法二：自连接删除，顺便复习了一下自连接
update tem1 set tRank = ‘副教授’
from teacher as tem1
inner join teacher as tem2 on datediff(yy,tem1.tBirth,getDate())>41 and tem1.tRank=‘讲师’;

(3)将“d01006”号院系所有的专业名称前面加上“互联网”三个字。

update major set mName=‘互联网’+mName where dNo= ‘d01006’;

(4)删除“d01006”号院系及其的所有相关信息。

– 法一，逐个删除，先删除子表再删夫表
delete major where dNo=‘d01006’;
delete teacher where dNo=‘d01006’;
delete department where dNo=‘d01006’;
– 法二，建立了级联操作，可以直接删除父表
alter table major drop constraint dNo_fore;
alter table major add constraint dNo_fore foreign key(dNo) references department(dNo) ON UPDATE CASCADE ON DELETE CASCADE;
alter table teacher drop constraint dNo_teacher_fore;
alter table teacher add constraint dNo_teacher_fore foreign key(dNo) references department(dNo) ON UPDATE CASCADE ON DELETE CASCADE;
delete department where dNo=‘161’;

4.思考题
1.
法一：delete from 删除的数据可以回滚（事务） 
法二：truncate table: 可以全表删除
2.
暂时还不知怎么解答，因为对于并行没有太多了解。

2022-10-10 回答： 目前工作有3年了，现在有了些思路，如果有个大表，程序读取的时候可以根据条件分成多个线程，多线程来并行查询，或者将 原大表进行分表来查询；不过目前sql server 大部分没在用了，如果是大表，目前有类似tidb，clickhouse 等数据库选择，超大表（亿行以上）尽量还是不要用sql server实现

<h4>总结</h4>
&emsp;&emsp;这次练习主要做了一些sql语句建库，建表的操作，都是比较简单的，不过需要注意的一些地方是在建库，增长量的时候是可以有两个度量单位选择的，filegrowth =10%时，是以10%来增长，如果是filegrowth =10则默认单位是m，以10m来增长。本次练习对于我来说是比较简单，因为之前有学过mysql数据库的相关课程，对于外键，查询，插入之类的操作已经是比较熟悉了的。就我个人体会来看，初次接触sql server的时候，感觉sql server操作与mysql操作并没有什么不同。