sql server2000的一部分操作的笔记（里面有些和oracle数据库的比较）

--这样的情况下用char
--存放学号都是六位，查询起来很快。。。如果是varchar则要一个一个的比
--如果一个字段的长度是确定的则使用char


create table hgname
(
test float(3)
)

insert into hgname values(2)

select * from hgname

--如果比bigint还要大，
drop table hgname

数据类型
  float :
    float(5) 表示保留小数点后5位

  numeric:
    numeric(20,2)--表示一共可以是20位，保留小数点后两位

  --   如果超出会自动截去，推荐使用numeric
 

--日期类型：
  datetime :
create table hgname
(
     data datetime
)
---sql server  为我们专门提供了一个时间函数
--getdate（）//得到当前的系统时间

insert into hgname values(getdate())
select * from hgname

--timestamp (时间戳)

--可以存放图片 image类型 ，但是用的很少，一般用路径保存图片，在软件公司往往使用图片服务器和图床技术
--  在图片有安全要求时可以考虑放入数据库 而且图片不要太大

--存放视频 binary类型 但是我们往往 ,将视频文件保存在文件服务器上，数据库只保存路径

--在命令行中敲ping www.taobao.com.cn得到淘宝网的IP地址
--ping -t www.taobao.com.cn


--建表(公司管理系统)
--  职员表
-- 主键（primary key)不能重复且不能为空
create table clerk
(
cleId int primary key,
cleName nvarchar(50),
age int
)

insert into clerk values(1,'贾宝玉',18)
insert into clerk values(2,'贾政',20)


--插入部分字段
insert into clerk (cleId ,cleName) values(3,'林黛玉')

select * from clerk

--改一个字段
update clerk set age = 38 where cleName = '贾政'


--把2豪人物的名字改为薛 蟠 age 改为40
update clerk set cleName ='薛蟠' ,age = 40 where cleId =2


--主键能修改吗? 2改为4

update clerk set cleId = 4 where cleName ='薛蟠'

--可以改，但是不能改为重复的主键


--修改含有NUll的字段
--把年龄为null的人的名字改为‘薛宝钗’

update clerk set cleName= '薛宝钗' where age = null--不行

select * from clerk
--应该为
update clerk set cleName= '薛宝钗' where age is not

--删除全部数据
delete from 表名
--删除指定数据
delete from 表名 where 字段名= '值'  and 字段名 ='值'

insert into clerk values(5,'贾母',70)

--删除年龄>=40的人 并且<=70，或者age是null
delete from clerk where (age >=40 and age<=70 ) or age is null



--查询

--建两张表emp表和dept表
--因为oracle有，就不键了
create table dept
(
deptno int primary key,
dname  nvarchar(30),
loc nvarchar(30)
)

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) --因为的deptno根据需要要做成外键
)

--外键，请注意：
--！。外键只能指向主键
--2.外键和被指向的主键的类型要一致

select * from emp;

select * from dept;

select sal ,job,deptno from emp where ename ='SMITH';
--sql server是不区分大小写的

--取消重复行   distinct
select distinct sal from emp where ename = 'SMITH';
--统计有多少个部门编号
select distinct deptno from emp;

--使用算数表达式
--显示每个雇员的年工资
select ename ,sal*12+nvl(comm ,0) 年工资 from emp;vv
--sql server 2000中判断null的用 isnull（comm，0）
--显示工资高于3000的员工
select * from emp where sal >3000;
select * from emp where hiredate >'1982-1-1';

---模糊查询

select ename,sal from emp where ename like 'S%';
--% 代表0到多个字符
select ename,sal from emp where ename like '__A';
---_代表一个字符

---where 中使用in  批量查询
select * from emp where empno in(123,345,800);

-- is null操作符
--显示没有上级的人
select * from emp where mgr is null;


select * from emp where (sal>500 or job ='MANAGER') and ename like 'J%';

--安工资从低到高显示
select * from emp order by sal asc ;
--order by 默认是升序
--order by desc 表示降序

--按照入职的先后顺序排
select * from emp order by hiredate asc;

--按照部门号的升序排列，而雇员的工资降序排列
select * from emp order by deptno , sal desc;
--先考率第一个字段，然后第二个。。。

--统计每个人的年薪，并案中案从到高的顺序排序
select ename ,(sal*12 + nvl(comm,0) ) 年薪 from emp order by (sal*12 + nvl(comm,0) ) desc;

--分页查询
--先学子查询
--数据分组: max min avg sum count
select ename ,sal from emp where sal =(select  min(sal) from emp);

---显示所有雇员的平均工资
select avg(sal) 平均工资,sum(sal) 总工资 from emp;

--把高于平均工资的雇员的名字和他的工资显示出来
select ename,(select avg(sal) from emp) from emp where sal>(select avg(sal) avg_sal from emp);
--统计共多少雇员
select count(*) from emp;

--group by 和 having
--group by 用于对于查询的结果分组统计
--having 子句用于限制分组显示结果

--如和显示每个部门的平均工资和最高工资
--并显示部门名称 select * from dept;(多表查询)
select deptno,avg(sal),max(sal) from emp  group by deptno;

--显示每个部门的每个岗位的平均工资和最低工资
select avg(sal),min(sal) ,deptno ,job from emp group by deptno,job order by deptno;
--显示平均工资低于2000的部门号和他的平均工资
--having往往和group by 结合使用，可以对分组查询结果进行筛选
select avg(sal) ,deptno from emp group by deptno having avg(sal) <2000;

--1 分组函数只能出现在选择列表，having order by 子句中
--2 如果在select 语句中同时包含有group by ，having ，order by那么他们的顺序是
--group by ，having，order by

--3。在选择列中如果有列、表达式、分组函数，那么这些列和表达式必须有一个
--出现在group by 子句中，否则就会出错
--如select  deptno ,avg(sal) ,max(sal) from emp group by deptno having avg(sal) <2000;
--deptno这里就一定要出现在group by 中

--现有一个商店的数据库，记录顾客及其购物情况，请设计有多少张表，每张表的字段是什么？
create table customer
(
  cName nvarchar2(30),
  sex number(2),
  address nvarchar2(50),
  goodsNum number(100),
 
)

create table goods
(
 
)

--多表查询
--显示雇员名，雇员工资，及所在部门的名字
select e.ename ,e.sal ,d.dname from emp e ,dept d where e.deptno = d.deptno;
--显示部门号为10的部门名，员工名和工资
select d.dname ,e.ename, e.sal from emp e ,dept d where e.deptno =10 and d.deptno=10;
--显示雇员名，雇员工资及所在部门的名字，并按照部门排序
select e.ename ,e.sal ,d.dname from emp e ,dept d where e.deptno = d.deptno order by d.deptno;

--显示某个员工的上级领导的姓名,比如显示FORD的上级
select ename from emp where empno = (select mgr from emp where ename ='FORD');
select e1.ename , e2.ename from emp e1,emp e2 where e1.mgr = e2.empno and e1.ename ='FORD';
--显示每个员工和他的上级的名字
select e1.ename , e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
--外连接（左外连接，右外连接）
--显示没有上级的人的名字
select ename from emp where ename  not in(select e1.ename from emp e1,emp e2 where e1.mgr = e2.empno);

--子查询
--是嵌套在其他sql语句中的select 语句，也叫嵌套查询
--单行子查询
--是指只返回一行数据的子查询语句
--请思考如何显示与SMITH同一部门的所有员工
select e2.* from emp e1,emp e2 where e1.deptno = e2.deptno and e1.ename ='SMITH';
select * from emp where deptno=(select deptno from emp where ename ='SMITH');
--多行子查询
--指返回多行数据的子查询
--请思考：如何查询和部门10的工作相同的雇员的名字，岗位，工资，部门号
select * from emp where job in(select distinct job  from emp where deptno = 10);

---在from 子句中使用子查询
--请思考:如何显示高于部门平均工资的员工的信息
select avg(sal) from emp group by deptno ;
select * from emp e,(select avg(sal) avg_sal , deptno from emp group by deptno) t where e.deptno = t.deptno and e.sal >avg_sal;
--也可以创建一个视图


--分页查询
--显示第一个到第4个入职额雇员
select top 4 * from emp order by hiredate;--sql server200中可以，但oracle不可以
--oracle中应该用rownum
select t1.* ,rownum rn from (select * from emp order by hiredate) t1 where rownum <=4;
--请显示第5个到第10个入职的雇员（按时间的先后顺序）
select * from (select t1.* ,rownum rn from (select * from emp order by hiredate) t1  where rownum<=10) where rn>=5;

--sql server 200  中的写法---先排除前4个人，再重新的里面选出前6个
select top 6 * from emp where empno not in (select top 4 empno from emp order by hiredate) order by hiredate;


--sql server中实现
--identity(1,1)，表示该testId 字段自增，从1开始，每次加1
create table test(
testId int primary key identity(1,1),
testName varchar2(30),
testPass varchar2(30)
)

insert into test (testName,testPass) values('hugui','zhuxi');

insert into test (testName,testPass) select testName,testPass from test;

select count(*) from test;


select * from emp ;


--用查询结果创建表
select * (字段) into 另一个表名 from 表

--如何删除一张表中的重复记录
create cat
(
catId int,
catName varchar2(40)
)

insert into cat values(2,'b');
select * from cat;
--把cat表的记录distinct的结果，放到#temp
select distinct * into #temp from cat;
--把cat表清空
delete table cat;
--把#temp表的数据（没有重复的记录）插入到cat表
insert into cat select * from #temp;
--删除#temp
drop table #temp;

--左外连接和右外连接

--内连接
select e1.ename , e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;
--显示每个员工和他的上级的名字，要求没有上级的人的名字也要显示

--左外连接：指左边的表的记录全部都要显示，如果没有匹配的记录就用null填
select e1.ename ,e2.ename from emp e1 left join emp e2 on e1.mgr =e2.empno;

--右外连接：指右边的表的记录全部都要显示，如果没有匹配的记录就用null填
select e1.ename ,e2.ename from emp e1 right join emp e2 on e1.mgr =e2.empno;


-----维护数据的完整性--约束
--约束包括：not null ，unique ，primary key ，foreign key ，和check 五种

--not null
  --插入时必须为字段指定数据,不能为空
--unique
   --该值不能重复,是唯一的,单丝可以为null，但是最多只能有一个null
--primary key
   --用于唯一的标示表行的数据主键唯一，且不能为空 ,一张表最多只有一个主键约束，但是可以有个unique
   --表可以有复合主键
--foreign key
  --用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，
  --当定义外键后，要求外键列数据必须在主表的主键列存在或是为null
--check
  --用于强制数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值
  --在1000-2000之间，如果不在1000-2000之间就会提示出错
create table test
(
Id number(10)unique,--行级定义
name varchar2(30),
pass  varchar2(30),
age number(3)default 18,--default  的使用
sal int check (sal>=1000 and sal<=2000),
primary key (Id ,name)--表级定义
)



--商店售货系统表设计案例
--现有一个商店的数据库，记录客户及其购物情况，有下面三个表组成：
--商店goods （商品号goodsId ,商品名goodsName,单价unitprice ,商品类别category,供应商provider);
--客户customer(客户号 customerId ，姓名 name，地址address ，电子邮件email ，性别sex，身份证cardId）；
--购买purchase (客户号customerId ，商品号goodsId，购买数量nums）；
--请用sql语言完成下列功能
--建表，在定义中要求声明：
--1，每个表的住外键
--2。客户的姓名不能为null
--3。单价必须>0，购买数量必须子啊1到30之间
--4，电子邮件不能重复
--5。客户的性别必须是男或者女，默认为男
--6。商品的类别必须是食物或者是日用品

create table goods
(
goodsId nvarchar(50) primary key,
goodsName  nvarchar(80) not null,
unitprice  numeric(10,2) check (unitprice>0),
category  nvarchar(6) ckeck(category in('食物','日用品')),
provider  nvarchr(50)
)

create table customer
(
customerId  nvarchar(50) primary key,
cusname  nvarchar(50) not null,
address nvarchar(100),
email nvarchar(100) unique,
sex nchar(1) ckeck (sex in('男','女')) default '男',
cardId nvarchar(18)
)

create table purchase
(
customerId  nvarchar(50) foreign key references customer(customerId),
goodsId nvarchar(50) foreign key references goods(goodsId),
nums int ckeck (nums >0 and nums <30)
)


--修改表

--1。掌握sql server的数据库的备份和恢复
--2。掌握java对表的crud操作
--3、掌握Statement和PreparedStatement
--4。能完成一般的java数据库编程任务

--使用企业管理器备份和恢复数据库
--1.分离、附加
--分离完后，请到sql server 安装的目录下去找两个文件 数据库名。mdf 和数据库名。ldf
--这两个文件就是分离后的文件，数据库分离后，该数据看就不能再使用了
--附加是指，当用户需要重使用某个分离的数据库式进行的操作，就是让sql server数据库重新关联该数据库

--用文件

--使用查询分析器完成备份和恢复

--如何备份数据库

语法是
backup database 数据库名 to disk ='d:/hg.back'

backup database test to disk ='d:/hg.back'


--删除数据库
drop database test

--恢复数据库
restore database 数据库名 from disk ='d:/hg.back'

--有时可能不需要备份整个数据库，表的备份。。和数据库一样


--找出早于12年前受雇的员工
-- sql server 2000
select * from emp where datediff(year,hiredate,getdate())>22;
--oracle中
select * from emp where sysdate >= add_months(hiredate ,12*12);

--找出各月倒数第3天的受雇的所有员工
--oracle
select * from emp where hiredate = last_day(hiredate)-2;

--以首字母大写的方式显示名字
--sql server 2000
select upper(substring(ename,1,1))+lower(substring(ename ,2,len(ename))) from emp;
--oracle
select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;

--显示姓名正好是5个字母的
select * from emp where ename like '_____';
select * from emp where len(ename) =5;
--oracle 中: select * from emp where length(ename)=5;


--显示所有员工的前三个字符
--oracle
select substr(ename,1,3) from emp;
--sql server 2000
select substring(ename,1,3) from emp;

--显示所有员工的姓名，用a替换所有的A
--oracle
select replace(ename,'A','a') from emp;

--显示满10年服务年限的员工的姓名和受雇日期
--oracle
select ename ,hiredate from emp where sysdate >= add_months(hiredate,12*10);
--sql server 2000
select ename,hiredate form emp where datediff(year,hiredate,getdate())>10;

--显示所哟员工的姓名、工作和薪金，按工作的升序排列，若工作相同，则按薪金排序
select ename,job,sal from emp order by job desc ,sal;
--order by 先按desc排序，再按sal排序

select avg(sal),deptno from emp group by deptno;


--显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在月排序，
--若月份相同则将做早年份的员工排在最前面
--oracle
select ename, to_char(hiredate,'YYYY-MM') from emp  order by to_char(hiredate,'mm'),to_char(hiredate,'YYYY') ;
--sql server 2000
select ename,detepart(year,hiredate),datepart(month,hiredate) m from emp by m,y;

--列出至少有一个员工的所有部门
--sql server 2000  and oracle
select count(*), deptno from emp  group by deptno having count(*) >1;

--列出薪金比SMITH多的所有员工
select * from emp where sal>(select sal from emp where ename ='SMITH') and ename <>'SMITH';

--列出多有员工的姓名，及其直接上级的姓名
select w.ename 员工 , b.ename 上级 from emp w ,emp b where w.mgr = b.empno;


--列出受雇日期晚于其直接上级的所有员工
select w.ename 员工 , w.hiredate, b.ename 上级, b.hiredate  from emp w ,emp b where w.mgr = b.empno and w.hiredate >b.hiredate;


--列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
select d.dname , e.* from dept d left join emp e on e.deptno = d.deptno;


--列出所有CLERK 的姓名及其部门名称
select e.ename ,d.dname from emp e,dept d where e.job='CLERK' and e.deptno = d.deptno;

--列出最低薪金大于1500的各种工作
select min(sal),job from emp group by job having min(sal) >1500;


--列出在部门’SALES‘工作的员工的名字，假定不知道销售部的部门编号
select ename from emp where deptno =(select deptno from dept where dname = 'SALES');

--列出薪金高于公司平均薪金的所有员工
select ename ,sal from emp where sal>(select avg(sal) from emp);

--列出与SCOTT从事相同工作的所有员工
select * from emp where job = (select job from emp where ename ='SCOTT');

--列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金
select * from emp where sal in(select sal from emp where deptno =30);


--列出薪金大于部门30中员工的薪金的所有员工的姓名和薪金
select * from emp where sal >(select max(sal) from emp where deptno =30);


--列出在每个部门工作的员工数量、平均工资和平均服务期限
--sql server 2000
select count(*),avg(sal),avg(datediff(year,hiredate,getdate()))，deptno from emp group by deptno;
--oracle
select count(*) ,avg(sal) ,avg(sysdate - hiredate) ,deptno from emp group by deptno;

--列出所有员工的姓名、部门名称和工资
select e.ename,d.dname ,e.sal from emp e,dept d where e.deptno=d.deptno;



--列出从事同一工作但是属于不同部门的员工的一种组合
select e1.ename ,e2.ename from emp e1,emp e2 where e1.job = e2.job and e1.deptno <> e2.deptno;

--列出所有部门的详细信息和部门人数
--select d2.dname,d2.loc ,d.c from dept d2 ,(select count(*) c,deptno de from emp group by deptno) d
--where d2.deptno = d.de;
select d2.dname,d2.loc ,nvl(d.c,0) from dept d2 left join (select count(*) c,deptno de from emp group by deptno) d
on d2.deptno = d.de;

--列出各种工作的最低工资
select min(sal),job from emp group by job;


--列出MANAGER的最低薪金
--sql server 2000不区分大小写 而oracle区分
select min(sal) from emp where job ='MANAGER';


--列出所有员工的年工资，按年薪从低到高排序 oracle :nvl(comm,0)  sql server:isnull(comm,0)
select sal*12+nvl(comm,0),ename from emp order by (sal*12+nvl(comm,0));