目录
1.新安装是只有回收站标志:鼠标右键 个性化----更改桌面图标
2.vmware windows系统安装vmware tools ,安装按钮是灰色的:CD/DVD改成由自动检测改成.ios
VMware tools一直卡在正在准备安装,更换镜像源。主页有资源可以下载。
6.虚拟机上界面看起来粗糙老旧,偶然改成了清晰地---虚拟机全屏,按esc退出(不明白为什么就变清晰了)
一.安装配置问题
1.新安装是只有回收站标志:鼠标右键 个性化----更改桌面图标
2.vmware windows系统安装vmware tools ,安装按钮是灰色的:CD/DVD改成由自动检测改成.ios
VMware tools一直卡在正在准备安装,更换镜像源。主页有资源可以下载。
3.net framework![](https://i-blog.csdnimg.cn/blog_migrate/dc461e3a6f8d54cc66650ce8deb36ca2.png)
解决方法:参考https://jingyan.baidu.com/article/624e74595b699b34e8ba5af1.html(根据自己情况,略有出入)
4.sqlserver2005 sqlserver2005安装(附加sqlserver2005 和 sqlserver2005 sp4补丁,完整安装包)_辛勤小王子的博客-CSDN博客_sqlserver2005
5.SQL server 无法连接到服务器![](https://i-blog.csdnimg.cn/blog_migrate/c6ec571c046473ee7f33e62727daf52d.png)
键入.
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语句)