数据库之 SQLServer

--SQL Server 2005 笔记


--创建数据库
create database db_LiangShanHeros


--使用数据库
use db_LiangShanHeros


--创建数据表
create table tb_Heros
(
heroId int identity(1,1) primary key, --好汉排名
heroName varchar(50), --好汉姓名
heroNickName varchar(50), --好汉外号
sex char(2), --好汉性别
sal int --好汉薪水
)


--使用sql语句添加数据
insert into tb_Heros values('宋江','及时雨','男',20000)
go
insert into tb_Heros values('卢俊义','玉麒麟','男',15000)
go
insert into tb_Heros values('吴用','智多星','男',30)
go
insert into tb_Heros values('公孙胜','入云龙','男',80)
go
insert into tb_Heros values('公孙胜1','入云龙','男',100)


insert into tb_Heros values('公孙胜2','入云龙','男',110)
insert into tb_Heros values('公孙胜3','入云龙','男',120)
insert into tb_Heros values('公孙胜4','入云龙','男',130)
insert into tb_Heros values('公孙胜5','入云龙','男',140)
insert into tb_Heros values('公孙胜6','入云龙','男',150)


--使用sql语句更新数据
update tb_Heros set sal = sal * 1.1 where sal < 100


--使用sql语句查询数据
--①查询所有数据
select * from tb_Heros
--②查询某个范围的数据
select * from tb_Heros where sal < 100
--模糊查询
select heroName,sal 工资 from tb_Heros where sal like '_'
/*
%:表示0到多个字符
_:表示单个字符
*/
--批量查询
select * from tb_Heros where sal in(33,88)
--空条件查询
select * from tb_Heros where heroName is null


--使用sql语句删除数据
--①删除所有数据
delete from tb_Heros
--②删除某个范围的数据
delete from tb_Heros where sal < 100




--删除数据表
drop table tb_Heros


--删除数据库
drop database db_LiangShanHeros


--数据库数据类型30种


--【字符型】
char --定长，最大8000字节，非uniocde编码
char(10)
nchar
nchar(10)
varchar --变长，最大8000字节，非uniocde编码
varchar(10)
nvarchar
nvarchar(10)
text --变长，最大2的31次方减1字节，非uniocde编码
ntext
/* 
①一般带有汉字的字段用nvarchar
全英文或符号的用varchar
因为nvarchar为unicode字符集，该类型的字段字母和汉字
都占两个字节，所以nvarchar处理汉字或其它unciode字符集
的速度要比varchar字段快
②如果有一些特殊字符在nvarchar中没有，比如日文的某些片名
那应该选varchar，否则会造成字符丢失
③text是字节格式存储英文的，也可以存中文但有时候会显示成乱码
④ntext是多字节格式存储unicode的，也就是存储各种文字用的
*/


--【数字型】
bit --范围 0 到 1
int --范围 负的2的31次方 到 正的2的31次方减一
bigint --范围 负的2的63次方 到 正的2的63次方减一
float --存放小数，不推荐使用
numeric --小数,推荐使用


--【日期型】
datetime --日期
timestamp --时间戳


create table tb_bir
(
bir datetime ,
)
insert into tb_bir values(getdate())
select * from tb_bir


--【图片型】
image


--【视频型】
binary


--主键
create table spname
(
cleId int primary key,
cleName nvarchar(50),
age int
)
insert into spname values(1,'贾宝玉',18)
insert into spname values(2,'贾宝玉',18)
select * from spname
update spname set cleName = '贾政' where cleName = '贾宝玉' and cleId = 2
delete from spname
drop table spname


--多表查询案例
--创建spdb数据库
create database spdb
--创建dept表
create table dept
(
deptno int primary key,
dname nvarchar(30),
loc nvarchar(30)
)
--创建emp表
create table emp
(
empno int primary key,
ename nvarchar(30),
job nvarchar(30),
mgr int,
hiredate datetime,
sal numeric(10,2), --工资
comm numeric(10,2), --奖金
deptno int foreign key references dept(deptno)   --外键      
)


/*
注意：
①外键只能指向主键
②外键与主键的数据类型要一致
*/


select dept.deptno,job '工作' from dept,emp


--排序order by语句
select heroName '姓名',sal '工资' from tb_Heros order by sal
select heroName '姓名',sal '工资' from tb_Heros order by sal asc --默认升序排列
select *,heroName '姓名',sal '工资' from tb_Heros order by sal desc --降序排列
select heroId 'ID',heroName '姓名',sal*12 '年薪' from tb_Heros order by '年薪' desc --使用别名降序排列


--数据分组max(最大),min(最小),avg(平均数),sum(求和),count(总数)
select min(sal) '工资' from tb_Heros
select heroName '姓名',sal '工资最低' from tb_Heros where sal =(select min(sal) from tb_Heros)
select heroName '姓名',sal '工资最高' from tb_Heros where sal =(select max(sal) from tb_Heros)
select avg(sal) '平均工资',sum(sal) '员工总工资',count(sal) '总员工人数' from tb_Heros
select heroName '姓名',sal '高于平均工资' from tb_Heros where sal >(select avg(sal) from tb_Heros)


--group by 用于对查询的结果分组统计


select heroNickName '外号',avg(sal)'部门平均工资',max(sal)'部门最高工资' from tb_Heros group by heroNickName --把外号归为一组


-- having 用于限制分组显示结果
select heroNickName '外号',avg(sal)'部门平均工资',max(sal)'部门最高工资' 
from tb_Heros group by heroNickName having avg(sal) < 10000 order by avg(sal)  


--顺序group by ->having ->order by
distinct --去除重复记录
--复杂查询
--多表查询
--单行子查询
--多行子查询


--自我复制
insert into tb_Heros(heroName,heroNickName,sex,sal)
select heroName,heroNickName,sex,sal from tb_Heros
select count(*) from tb_Heros
select distinct heroName,heroNickName,sex from tb_Heros


--删除重复的记录
create table cat
(
catId int,
catName varchar(40)
)
insert into cat values(1,'yaoyun')


select distinct * into tb_temp from cat
delete from cat
insert into cat select * from tb_temp
drop table tb_temp


select * from cat
select * from tb_temp




--分页查询
select top 4 * from tb_Heros order by heroId
 
select top 4 * from tb_Heros where heroId not in
(select top 4 heroId from tb_Heros order by heroId  desc) order by heroId  desc




--约束
/*
primary key 主键约束
foreign key 外键约束
例如：deptno int foreign key references dept(deptno)
not null 非空约束
unique 唯一约束
check 约束
例如：sal int check (sal>=1000 and sal<=2000)
*/


--默认值default
'日期' datetime default getdate()








--修改表名?


--添加字段
ALTER TABLE dept
ADD beizhu varchar(30)
--修改字段
ALTER TABLE dept
ALTER COLUMN beizhu nvarchar(50)
--删除字段
ALTER TABLE dept 
DROP COLUMN beizhu




--备份恢复
backup database '要备份的数据库名' to disk='备份路径/备份的文件名'
backup database spdb to disk = 'd:/spdb.bak'
backup database '要恢复的数据库名' from disk='备份路径/备份的文件名'
restore database spdb from disk = 'd:/spdb.bak'






--JDBC-ODBC
use spdb
select * from dept
delete from dept
insert into dept values(1,'yaoyun',1000)