数据库实验日记SQL SERVER

目录

一.安装配置问题

1.新安装是只有回收站标志：鼠标右键 个性化----更改桌面图标

2.vmware windows系统安装vmware tools ，安装按钮是灰色的：CD/DVD改成由自动检测改成.ios

VMware tools一直卡在正在准备安装，更换镜像源。主页有资源可以下载。

3.net framework​编辑

4.sqlserver2005 sqlserver2005安装（附加sqlserver2005 和 sqlserver2005 sp4补丁，完整安装包）_辛勤小王子的博客-CSDN博客_sqlserver2005

5.SQL server 无法连接到服务器​编辑

6.虚拟机上界面看起来粗糙老旧，偶然改成了清晰地---虚拟机全屏，按esc退出（不明白为什么就变清晰了）

二.数据库原理 SQL server 使用

1.创建数据库及表 

2.创建数据文件日记文件，追加文件

3.基本表的创建 

4. 四种数据插入方式2005SQL server

5.根据birthday类型计算生日

6.修改列

7.建立/删除索引

8.视图（虚表）

9.select 

一.安装配置问题

1.新安装是只有回收站标志：鼠标右键 个性化----更改桌面图标

2.vmware windows系统安装vmware tools ，安装按钮是灰色的：CD/DVD改成由自动检测改成.ios

VMware tools一直卡在正在准备安装，更换镜像源。主页有资源可以下载。

3.net framework

解决方法：参考https://jingyan.baidu.com/article/624e74595b699b34e8ba5af1.html（根据自己情况，略有出入）

4.sqlserver2005 sqlserver2005安装（附加sqlserver2005 和 sqlserver2005 sp4补丁，完整安装包）_辛勤小王子的博客-CSDN博客_sqlserver2005

5.SQL server 无法连接到服务器

 键入.

6.虚拟机上界面看起来粗糙老旧，偶然改成了清晰地---虚拟机全屏，按esc退出（不明白为什么就变清晰了）

二.数据库原理 SQL server 使用

1.创建数据库及表 

create database 数据库名称 //创建数据库

create table 表名(
   属性,
   属性，
   属性
   stu char (20),
   score int,
   sno int  (10)
);

2.创建数据文件日记文件，追加文件

create database Test
ON
(       NAME= Test2Datal,
		FILENAME = 'C:\Test\Test21.mdf ' ,
		SIZE= 6,
		MAXSIZE= 100 ,
		FILEGROWTH = 1 ),

(		NAME= Test2Data2 ,
		FILENAME= 'C:\TestiTest22.ndf ',
        SIZE= 1,
		MAXSIZE=10,
        FILEGROWTH= 1 )
LOG ON(
		NAME= Test2Log1,
		FILENAME= 'C:\irestiTest2Log1. ldf',
        SIZE= 512kb,
		MAXSIZE= 5,
		FILEGROWTH =512kb );//创建


ALTER DATABASE Test 
ADD FILE 
(
    NAME = Test2Log2,      
    FILENAME = 'C:\Test\TestLog2.ldf',
    SIZE = 1MB,       
    MAXSIZE = 10MB,    
    FILEGROWTH = 1MB
);//添加

3.基本表的创建 

use Test;
go
create table Student(
  Sno char(12) check(LEN(RTRIM(Sno)) = 12 AND PATINDEX('%[^0-9]%', RTRIM(Sno)) = 0 
               and Ascii(substring(Sno,1,1)) between Ascii('2') and Ascii('5'))PRIMARY KEY,
  Sname char(20) not null,
  Ssex char(2) check(Ssex in('男','女')),
  Sbirthday DATETIME default 'N',
  Sdept char(40) default 'N',
  Saddress char(50) default 'N'
);

create table Course(
  Cno char(6) primary key,
  Cname char(30),
  Cpno char(6) default 'N' foreign key references Course(Cno),
  Ccredit int default 2
)
create table SC(
  Sno char(12)foreign key references Student(Sno) ,
  Cno char(6) foreign key references Course(Cno) ,
  Grade float default -1,
  primary key(Sno,Cno)
)

4. 四种数据插入方式2005SQL server

use Test;
--method 1
insert into Course values('100001','概率论与数理统计',NULL,4);
insert into Course values('100002','离散数学','100001',4);

truncate table Course --删除表的数据，保留结构


--method 2
delete from Course where Cno='100010'--删除某行数据
insert into Student
select '419109231220','张伟','男','20020102','艺术学院广告学','山西省太原市' union (all)
select '419109231221','王敏','女','20000827','水资源与环境学院水利类','江苏省苏州市' 

--method 3
Bulk  insert  SC
      From  'C:\Users\Administrator\Desktop\SC.txt'
      With
      ( 
          fieldterminator=',',
          rowterminator='\n'
      )
--method 4
insert into <tablename> (select_statement)
  For example ：
  insert into employee1 select* from employee

4.

select distinct R.X from R as R1
where not exists
(
  select S.Y from S
   where not exists
   (
     select* from R as R2
     where R2.X = R1.X and R2.Y = S.Y
   )
);

5.根据birthday类型计算生日

SELECT sid,sname, ssex,sage, year(getdate())-year(sage) as age 
from student

6.修改列

alter table <tablename>
add 列名 类型

alter table <tablename>
drop column 列名

alter table <tablename>
modify 列名 类型

7.建立/删除索引

create [unique]index <index_name> on <tablename>

drop index <tablename.index_name>

8.视图（虚表）

create view view_name [column_name,...] as (select_statement)

drop view <view_name>

9.select 

select 字段1，字段2... from 表 [where <条件表达式>]
[group by...]
[having<条件表达式>]
[order by ...]

--For example:
--1.简单查询（单表或关系）
select ESalary as '工资' from dbo.Employee --查询所有工资

select distinct ESalary as '工资' from dbo.Employee --去掉重复值

select ENo as '职工号' from Employee where ESalary>1230 --工资大于1230的职工号

select ENo as '职工号' from Employee where ESalary<1250 and (WNo='WH1' or WNo='WH2')

select ESalary from employee group by Esalary --工资排序

select count(Eno) from employee --总人数

select count(Eno) from employee group by Wno--每个仓库总人数


--2.简单联接查询
select eno,wcity form employee,wearhouse where (esalary>1230) and 
employee.wno=wearhouse.wno--工资大于1230的职工号和所在城市

--3.嵌套查询
select wcity from wearhouse where Wno in (select wno from employee where esalary=1250)

select eno from employee where esalary = (select esalary from employee where eno ='E4') and eno!='E4'

--4.范围between and  闭区间
select * from employee where esalary between 1220 and 1240

--5.模糊查询 like
select * from 供应商 where 供应商名 like '%公司' --% 0个或多个  _ 1个字符

--6. 排序 order by
select * from <table> order by esalary,wno asc --默认升序，ASC降序

--7.简单计算查询
count\sum\avg\max\min

--8.group by having
select wno,count(*),avg(esalary) from employee group by wno having count(*)>=2

--9.别名
select 供应商名 from 供应商 S , 订购单 P 

--10.自然联接
SELECT S.SName+'领导'+E.SName  as '领导关系'FROM EManager S, EManager E WHERE S.ENo=E.EMo

--11.谓词ANY ALL SOME exists
select * from 仓库 where not exists (selcet语句)