sql练习-程序员宅基地

本次练习文章从创建表——>往表中插入数据——>查询 、修改、删除表数据

 

use master;  --使用系统数据库
create database anqier;  -- 创建数据库anqier
select *from emp; --查询emp表中的所有数据
drop table emp; --删除这个表的数据，并且删除了表本身。
truncate table emp ; --只是清空了表中的数据，并不会删除表本身
create table emp ----创建人员信息表
(
empid int ,
empname varchar(10),
empold int ,
empbirth datetime
);

create table bumen --创建部门表
(
bmid int,
bmname varchar(10)
);
select *from bumen;

create table kq --创建考勤表
(
empid int,
sbtime datetime,
xbtime datetime
);

--往人员表里插入数据

insert into emp (empid,empname) values (1,'张三');
insert into emp (empid,empname) values (2,'张三丰');
insert into emp (empid,empname) values (2,'李四');
insert into emp (empid,empname) values (3,'李四囍 ');
insert into emp (empid,empname) values (4,'王五 ');
insert into emp (empid,empname) values (4,'王小五');
insert into emp (empid,empname) values (5,'王二小');
insert into emp (empid,empname) values (6,'王三小 ');
insert into emp (empid,empname) values (7,'张一');
insert into emp (empid,empname) values (7,'阿卜杜勒');

select * from emp;    --查询人员表中数据

--往部门表中插入数据

insert into bumen values (1,'开发部');
insert into bumen values(2,'市场部');
insert into bumen values(3,'测试部');

select *from bumen;  --查询部门表
truncate table kq; --清空考情表中的数据 

--往考勤表中插入数据
insert into kq values(1,'2016/06/18 8:30','2016/06/18 18:30');
insert into kq values(2,'2016/06/18 9:00','2016/06/18 17:30');
insert into kq values(3,'2016/06/18 10:29','2016/06/18 17:20');

--查询考勤表中的数据 

select *from kq;
select *from emp where empname='张三';
sp_help emp;  --查看人员表结构

--基本查询
select *from emp where empid=7;

--in查询
select *from emp where empid in (1,4,7);

--查询的并列
select *from emp where empid=4 and empname='王五';

--日期的剪切
select *from kq where substring(convert(char(20),sbtime,121),12,5)>'08:30';

--将部门表中所有人的姓名更新为张三，因为没有带where条件，所以更新的是整个表的全部
update emp set empname='张三';
select *from emp;
update emp set empbirth='1992/01/01';

--更新人员表中所有人的empold为今年的年份
update emp set empold=datediff(yyyy,empbirth,getdate());

--清空人员表，重新插入数据

truncate table emp;

insert into emp (empid,empname,empbirth ) values (1,'张三','1990/01/03');
insert into emp (empid,empname,empbirth) values (2,'张三丰','1990/01/03 12:12:12');
insert into emp (empid,empname,empbirth) values (2,'李四','1993/09/01');
insert into emp (empid,empname,empbirth) values (3,'李四囍','1993/03/21');
insert into emp (empid,empname,empbirth) values (4,'王五','1995/04/30');
insert into emp (empid,empname,empbirth) values (4,'王小五','1995/04/30');
insert into emp (empid,empname,empbirth) values (5,'王二小','1995/02/15');
insert into emp (empid,empname,empbirth) values (6,'王三小 ','1993/06/08');
insert into emp (empid,empname,empbirth) values (7,'张一','1994/01/03');
insert into emp (empid,empname,empbirth) values (7,'阿卜杜勒','1991/08/27');

--带where条件的更新

update emp set empid=8 where empname ='阿卜杜勒';
update emp set empid=9 where empname='李四';
update emp set empid=10 where empname='王小五';

select *from emp;
select *from emp order by empid asc;
sp_help emp;
insert into emp (empid) values(1);

select empid,empname,empbirth from emp where year(empbirth) between 1993 and 1995;
select getdate();

select datediff(yyyy,'1990/01/01',getdate());

select empname,empbirth,datediff(yyyy ,empbirth,getdate()) as nianling from emp ;
select empid,empname,empbirth from mp where datediff(yyyy,empbirth,getdate()) between 21 and 24;
select empname,empbirth from emp where datediff(yyyy,empbirth,getdate()) in (20,23,25);
select * from emp where (select empbirth from emp where empname='李四')>empbirth;

-----任务四 ---

--.计算2016年6月30日距离今天有多少天，多少月,多少周，多少小时
select '2016年6月31日距离今天'+ str(datediff(day,getdate(),'2016/06/30'),4)+'天'
union
select str(datediff(mm,getdate(),'2016/06/30'),4)+'月'
union
select str(datediff(wk,getdate(),'2016/06/30'),4)+'周'
union
select str(datediff(hh,getdate(),'2016/06/30'),4)+'小时';

---另一种方法展现--
select '2016年6月31日距离今天'+ str(datediff(day,getdate(),'2016/06/30'),4)+'天'
+ str(datediff(mm,getdate(),'2016/06/30'),4)+'月'
+ str(datediff(wk,getdate(),'2016/06/30'),4)+'周'
+str(datediff(hh,getdate(),'2016/06/30'),4)+'小时';
----.查询姓名中包含'三'或'小'结尾的人员信息:人员编号、姓名--
select empid,empname from emp where empname like '%三' or empname like '%小';
select *from emp;
---.查询姓李的所有人员信息:人员编号、姓名、生日
select empid,empname,empbirth from emp where empname like '%李%';
--.查询人员表中名字包含'国'的并且年龄分别为30、40、50的人员的:姓名、生日
select empname,empbirth from emp where datediff(yyyy,empbirth,getdate()) in (30,40,50);
--.查询人员表中姓名超过4个字节的并且年龄从20到40之间的人员的编号、姓名、年龄
select empid,empname,datediff(yyyy,empbirth,getdate())from emp where datalength(empname)>4 and datediff(yyyy,empbirth,getdate()) between 20 and 40;
--.查询人员表所有人的信息并按年龄的降序排列
select *from emp order by datediff(yyyy,empbirth,getdate()) desc;
--.查询人员表所有人的信息并按姓名的升序、年龄的降序排列
select *from emp order by empname asc,datediff(yyyy,empbirth,getdate()) desc;
--.将'张国华'中的华替换为荣
--先添加一条数据
insert into emp (empid,empname,empbirth )values(11,'张国华','1990/03/12');
select *from emp;
select replace ('张国华','华','荣'); --只能起到对数据的查找,并不能进行替换
update emp set empname='张国荣' where empname='张国华'; --update 才能实现对数据的更新

--.用单句sql实现在考勤表中给所有测试部的人员增加一条考勤,其中:
--上班时间为当前时间向前推6小时,下班时间为当前时间向后推50分钟
alter table kq add bumen varchar(20);
insert into kq (empid,sbtime,xbtime)
select empid,dateadd(hh,-6,getdate()),dateadd(mi,50,getdate())
from emp;
select *from kq ;
--删除列
alter table kq drop column bumen;
----.为人员表的每个人向考勤表中写入一条数据(上下班数据自拟)
insert into kq (empid,sbtime,xbtime)
select empid,dateadd(day,-3,getdate()),'2016/06/16 17:30' from emp ; --不同表之间插入数据
select *from kq;
truncate table kq;
select distinct *from kq; --按照所有字段的对比去重
select distinct empid from kq;
--.将人员表中年龄大于21的编号、生日写入到另一张表中去
select empid,empbirth into emp_bak from emp where datediff(yyyy,empbirth,getdate())>21;
select*from emp_bak;
--.对部门表的部门名称字段增加唯一约束
alter table bumen add constraint uk_1 unique(bmname);
--.对人员表增加工资字段,带小数保留2位，设置工资字段默认值4000
alter table emp add empsalary numeric(10,2) default 4000; --添加(默认约束)
--验证--、
select *from emp;
--可以不写默认字段,但必须写出其他字段
insert into emp (empid,empname,empold,empbirth)values(12,'anqier','23','1993/12/28');
select *from emp;

--.设置工资的取值范围为2000至20000 添加(检查约束)
alter table emp add constraint ck_1 check(empsalary>2000 and empsalary<20000);
--验证--
insert into emp (empid,empsalary) values (13,300000);
--.建表实现一个字段自动增长，从10开始每次增长2
create table shiyan
(
a int identity(10,2)
);
alter table shiyan add b varchar(20);
insert into shiyan values('安琪儿1');
insert into shiyan values('安琪儿2');
select *from shiyan ;
--往表中插入数据
insert into shiyan values(18,'安琪儿18');

set identity_insert shiyan ON ; --解决不能随意插入有设置自动增长的字段的方法
insert into shiyan (a,b)values(18,'安琪儿18'); --必须写出字段名
insert into shiyan values(19,'安琪儿19'); --这种形式在该种情况下不能插入数据
select *from shiyan;

--.按姓氏统计人员数量
select count(empid ),substring(empname,1,1) from emp
group by substring(empname,1,1);
--.按年龄统计人员数量
select count(empid),datediff(yyyy,empbirth,getdate()) from emp
group by datediff(yyyy,empbirth,getdate());
--.查询如下信息：工资最高、最低工资、平均工资、最大生日、最小生日
select max(empsalary) 最高工资,
min(empsalary) 最低工资,
avg(empsalary) 平均工资 ,
max(empbirth) 最大生日,
min(empbirth) 最小生日 from emp;

--.在人员表中增加性别并造相应数据
alter table emp add sex varchar(1); --添加性别字段
update emp set sex =0 where empid in (1,3,5,7,8,9,10);
update emp set sex=1 where empid in (2,4,6,11,12);
select *from emp ;
update emp set empold = datediff(yyyy,empbirth,getdate()) from emp; --更新人员表中所有成员的年龄
--.按性别统计人员数量
select count(empid) 数量 ,sex from emp
group by sex;
--.按年龄统计工资合计
update emp set empsalary=8000 where empid in (1,2,3,4,5,6);
update emp set empsalary=8500 where empid between 7 and 12;
select * from emp;
--.按姓氏和性别统计人员数量
select count(empid ) 人数 ,substring(empname,1,1)姓氏 ,sex 性别
from emp
group by substring(empname,1,1),sex ;

--.按姓氏和年龄统计人员数量
select count (empid) 人数 ,substring(empname,1,1) 姓氏 ,datediff(yyyy,empbirth,getdate()) 年龄
from emp
group by substring(empname,1,1),datediff(yyyy,empbirth,getdate()) ;

--按姓氏、性别和年龄统计人员数量
select count(empid) 人数 ,substring(empname,1,1) 姓氏 ,datediff(yyyy,empbirth,getdate()) 年龄
from emp
group by substring(empname,1,1),datediff(yyyy,empbirth,getdate());

--统计考勤表中各人的迟到次数
select count(empid),empid
from kq
where substring(convert(char(20),sbtime,121),12,5)> '08:30'
group by empid;

select *from kq;
--.按年月统计考勤表中迟到次数
select count(empid) 迟到次数 ,substring(convert(char(20),sbtime,121),1,7) 年月
from kq
where substring(convert(char(20),sbtime,121),12,5)> '08:30'
group by substring(convert(char(20),sbtime,121),1,7);

--关联查询及多表之间的查询

----.在人员表中增加部门编号字段
alter table emp add bmid int ;

----.查询人员表中姓'张'的或者部门为'测试部'人员的姓名、年龄、生日并按年龄的逆序排序
select *from emp;
select *from bumen;
update emp set bmid=1 where empid between 1 and 3; --给人员表中的人员进行部门编号，分配所属部门
update emp set bmid=2 where empid between 4 and 6;
update emp set bmid=3 where empid between 7 and 10;
update emp set bmid=2 where empid between 11 and 12;
select *from emp;
----.查询人员表中姓'张'的或者部门为'测试部'人员的姓名、年龄、生日并按年龄的逆序排序
select empname,empold,substring(convert(char(20),empbirth,121),6,5) 生日
from emp
where empname like '张%' or bmid=3
order by empold desc ;
----.查询人员表中名字包含'国'的并且年龄分别为21、22、24的人员的姓名、部门名称、年龄 --多表之间的联查要避免笛卡尔积
select a.empname,b.bmname,a.empold
from emp a,bumen b
where empname like '%国%' and empold in (21,22,24) and a.bmid=b.bmid;
--查询不到符合条件的，自行增加一条语句验证
insert into emp values (13,'张国立',22,'1994/09/15',9000,1,3);

----.查询人员表中姓名超过4个字节的并且年龄从20到40之间的人员的姓名、部门名称
select a.empname,b.bmname
from emp a,bumen b
where datalength(a.empname)>4 and a.empold between 20 and 40 and a.bmid=b.bmid;

----按部门编号统计人员数量:部门ID、人数
select count(bmid) 人数,bmid 部门ID --按部门表统计
from bumen
group by bmid ;
select *from bumen ;

select count(a.empid) 人数, b.bmid --按人员表统计
from emp a, bumen b
where a.bmid=b.bmid
group by b.bmid;

----按部门名称统计人员数量:部门名称、人数
select b.bmname 部门名称 ,count(a.empid) 人数
from emp a,bumen b
where a.bmid=b.bmid
group by b.bmname;
----对各部门的工资求合计:部门名称，工资合计,并按部门名称和工资排序(降序)
select b.bmname,sum(empsalary) 部门工资合计
from emp a,bumen b
where a.bmid=b.bmid
group by b.bmname
order by b.bmname desc ,sum(empsalary) desc ;
----统计各部门上班迟到的人数：部门名称、迟到人数
select b.bmname,count(c.empid) 迟到人数
from emp a,bumen b,kq c
where a.empid=c.empid and a.bmid=b.bmid and
substring(convert(char(20),c.sbtime,121),12,5)>'08:30'
group by b.bmname;
----统计各部门各月份上班迟到的人数：部门名称、年月、迟到人数
select b.bmname 部门名称,substring(convert(char(20),c.sbtime,121),1,7) 年月 ,count(c.empid) 迟到人数
from emp a,bumen b,kq c
where substring(convert(char(20),sbtime,121),12,5)>'08:30'
and a.empid=c.empid and a.bmid=b.bmid
group by b.bmname,substring(convert(char(20),c.sbtime,121),1,7);

--两张库表之间的左右关联及全关联查询

--.两张库表关联查询后两张表都出现数据丢失(查询不全)，可用什么方式解决,举例写出语法
--Select * from table1 full outer join table2 on table1.num2=table2.num2;

----统计各部门各月份迟到人数
--如:
--部门 月份 迟到人数
--------------------------
--市场部 2016.1 2
--市场部 2016.2 3
--市场部 2016.3 4
--开发部 2016.1 5
--开发部 2016.2 6
--开发部 2016.3 2

select b.bmname,substring(convert(char(20),c.sbtime,121),1,7) as 年月,
count(c.empid) as 迟到人数
from emp a,bumen b,kq c
where substring(convert(char(20),c.sbtime,121),12,5)>'08:30'
and a.empid=c.empid and a.bmid=b.bmid
group by b.bmname,substring(convert(char(20),c.sbtime,121),1,7);

---对人员表工资做如下分类统计:
--如：
--工资等级 人数
-----------------
--2000以下 1
--2001-3000 3
--3001-4000 2
--4001-5000 2
--5000以上 1
select *from emp;
select '2000以下' as 工资等级 ,count(empid) 人数 from emp where empsalary>0 and empsalary<2000
union
select '2001-3000' as 工资等级 ,count(empid) 人数 from emp where empsalary >2001 and empsalary<3000
union
select '3001-4000' as 工资等级,count(empid) 人数 from emp where empsalary between 3001 and 4000
union
select '4001-5000' as 工资等级,count(empid) 人数 from emp where empsalary between 4001 and 5000
union
select '5001-6000' as 工资等级, count(empid) 人数 from emp where empsalary between 5001 and 6000
union
select '6001-7000' as 工资等级, count(empid) 人数 from emp where empsalary between 6001 and 7000
union
select '7001-8000' as 工资等级, count (empid) 人数 from emp where empsalary between 7001 and 8000
union
select '8001-9000' as 工资等级,count(empid) 人数 from emp where empsalary between 8001 and 9000
union
select '9001-10000' as 工资等级,count(empid) 人数 from emp where empsalary between 9001 and 10000;

--为了让每个工资阶段都有人,所有可通过更改人员表信息
update emp set empsalary=2000 where empid=1;
update emp set empsalary=2967 where empid=2;
update emp set empsalary=3008 where empid=2;
update emp set empsalary=4007 where empid=3;
update emp set empsalary=5218 where empid=4;
update emp set empsalary=6899 where empid=5;
update emp set empsalary=7803 where empid=6;

--分工资等级统计员工人数：
--如:
--L2000以下 L2001-3000 L3001-4000 L4001-5000 L5000以上
------------------------------------------------------------------------------------------------
-- 1 3 2 2 1

select
(select count(empid) from emp where empsalary <2000 ) '2000以下',
(select count(empid) from emp where empsalary between 2001 and 3000) '2001 - 3000',
(select count(empid) from emp where empsalary between 3001 and 4000) '3001-4000',
(select count(empid) from emp where empsalary between 4001 and 5000) '4001-5000',
(select count(empid) from emp where empsalary between 5001 and 6000) '5001-6000',
(select count(empid) from emp where empsalary between 6001 and 7000) '6001-7000',
(select count(empid) from emp where empsalary between 7001 and 8000) '7001-8000',
(select count(empid) from emp where empsalary between 8001 and 9000) '8001-9000';

--语句块实现 对1..100的偶数求和

declare @a int; --声明定义一个整型变量a
declare @sum int; --声明定义一个整型变量sum
begin --处理开始
set @a=0; --为变量a 赋初始值
set @sum=0; --为变量sum 赋初始值
while (@a<=100) --循环条件 判断
begin --循环语句开始
set @sum=@sum+@a; --赋值语句
set @a=@a+2;
end --结束循环语句
print @sum; --打印输出结果
end ; --结束

--视图
create view v_1 as
select empid,empname from emp where empid in (1,2,3,4,5); --创建视图成功
--查询视图
select * from v_1;
--注意:在视图中不允许存在无列名的现象，每一个列都要有对应的列名
--如：

create view v_2 as --创建一个查询人员表全部信息的视图
select *from emp;

--删除视图;
drop view v_2; --同删除表操作

drop view v_3;
create view v_4 as
select empid,empname from emp ;
--注:一个视图里面只能包含一条查询语句,而且视图名字不能相同

create index index_1 on emp(empname); --在人员表名字那一列创建索引 允许使用重复的值：
create unique index index_2 on emp(empid) --在人员表ID那一列创建唯一性索引 唯一的索引意味着两个行不能拥有相同的索引值
drop index index_1 on emp ; --删除索引
--索引原理:
--排序,需要占用额外的磁盘空间(空间换时间)排好序的数据单独存放，加快查询速度
--副作用:额外的磁盘消耗，更新插入数据变慢
--创建索引生效的条件：索引的字段放在where条件中
select empname from emp where empname='anqier'; --如此可提高查询速度 --索引的使用

--事务;对数据进行修改，可提交、回滚

--下面这些代码有错
select *from emp;
begin transaction
update emp set empname='安琪儿' where empid=1 ;
delete from table emp where empname='张三';
rollback transaction;
end ;

--存储过程:将某些需要多次调用的/实现某个特定任务的代码段编写成一个过程 ,
--并将其视为独立的数据库对象保存在数据库中,由SQL服务器通过过程名来调用他们
create procedure p_1 as --创建存储过程
update emp set empname='安琪儿1' where empid=1 ; -- 存储过程里面可包含多个语句
select empname from emp where empid in(1,2,4);
select *from bumen;

drop procedure p_1; --删除存储过程
execute p_1; --调用存储过程

--触发器(依赖于表或者视图)
create trigger tr_1 on emp for delete as
begin
print '人员表正在删除数据'
end

--含义：如果人员表做删除数据的操作，将会触发执行begin中的语句，进行打印输出
--delete 可换成insert/update 等
--验证
delete from emp where empid=13;