SQL SERVER 听课笔记
--创建数据库
create database LiangshanHeros2
--drop database LiangshanHeros2
use LiangshanHeros2 --使用这个数据库存
--创建表
create table hero
(heroId int,--排名
heroName varchar(50),--名字
heroNickName varchar(50),--外号
sex char(2),
sal int
)
--使用sqL语句来添加数据
insert into hero values(1,'宋江','及时雨','男',20000)
insert into hero values(2,'卢俊义','玉麒麟','男',1500)
insert into hero values(3,'吴用','智多星','男',5000)
insert into hero values(4,'公孙胜','入云龙','男',4000)
insert into hero values(5,'林冲','豹子头','男',3500)
--删除表(表的结构和数据全部删除)
--drop table hero
--使用sql语句来查询数据
select * from hero
--1.查询工资低于100的同志
select * from hero where sal<10000
--2.把工资低于5000的人的工资提高10%(update)
--语法 update 表名... set 字段名... where 条件
update hero set sal=sal*1.1 where sal<5000
--3.删除性别为女的好汉
delete from hero where sex='女'
--表名规范案例--------------------------
--表名字母(汉字)和_开头,长度不能超过128个字符
create table abc(test int,test2 bit)
insert into abc values(2,0)
select * from abc
drop table abc
--数据类型
--float
create table tp1 (test1 float(4))
insert into tp1 values(1.1211)
select * from tp1
drop table tp1
--numeric
create table tp1 (test1 numeric(10,2))
--2表示截取两位小数
insert into tp1 values(1.1211)
select * from tp1
drop table tp1
--日期类型datetime
--getdate()
create table tp1 (bir datetime)
insert into tp1 values(GETDATE())
select * from tp1
drop table tp1
--==================================================
--1.职员表
create table clerk(
cleId int primary key,--主键必须给值
cleName nvarchar(50),
age int,
)
--插入所有字段
insert into clerk values(1,'贾宝玉',18)
insert into clerk values(2,'贾政',48)
insert into clerk values(5,'贾母',68)
insert into clerk values(6,'焦大',55)
--插入部分字段,需要在表名后指定字段列表,但主键不能少
--语法: insert into 表名(字段列表) values(值列表)
insert into clerk(cleId,cleName) values(3,'林黛玉')
--改一个字段,把贾政的age改成38
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的数据
--把age为null的人的名字改为'薛宝钗'
update clerk set cleName='薛宝钗' where age is null
--删除全部数据
delete from clerk
--删除指定数据
--语法:delete from 表名 where 字段名='值' and 字段名='值' ...
--删除年龄>=55的人,并且<=65 and age<=75
delete from clerk where (age>=55 and age<=75) or age is null
select * from clerk
--======================================================================
-----------------公司管理系统---------------
--create database copdb1
--创建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) --需要做成外键
)
--针对外键,请注意:
--1.外键只能指向主键
--2.外键和主键的数据类型要一致
--插入数据
insert into dept values(10,'accounting','new york')
insert into dept values(20,'research','dallas')
insert into dept values(30,'sales','chicago')
insert into dept values(40,'operations','boston')
insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-9-8',1500.00,0.00,30)
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,null,20)
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600.00,300.00,30)
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-2-22',1250.00,500.00,30)
insert into emp values(7566,'JONES','MANAGER',7839,'1981-4-2',2975.00,null,20)
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250.00,1400.00,30)
insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-5-1',2850.00,null,30)
insert into emp values(7782,'CLARK','MANAGER',7839,'1981-6-9',2450.00,null,10)
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-4-19',3000.00,null,20)
insert into emp values(7839,'KING','PRESIDENT',null,'1981-11-17',5000.00,null,10)
insert into emp values(7876,'ADAMS','CLERK',7788,'1987-5-23',1100.00,null,20)
insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-3',950.00,null,30)
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-3',3000.00,null,20)
insert into emp values(7934,'MILLER','CLERK',7782,'1982-1-23',1300.00,null,10)
select * from dept;
select * from emp;
--查询SMITH的薪水、工作和所在部门编号
select sal,job,deptno from emp where ename='smith' ;
--sql server对里面的字段不区分大小写
--取消重复行,统计共有多少个部门编号
select distinct deptno from emp;
--处理null值问题,显示每个雇员的年工资
select ename,sal*13+ISNULL(comm*13,0) 年工资 from emp;
--查询工资高于3000的员工
select ename,deptno,sal from emp where sal>3000;
--查询1982.1.1后入职的员工
select * from emp where hiredate>'1982-1-1';
--查询工资在2000到2500之间的员工
select * from emp where sal between 2000 and 2500;
select * from emp where sal>=2000 and sal<=2500;
--查询首字符为S的员工姓名和工资
select ename,sal from emp where ename like 's%';
select ename,sal from emp where ename like '__O%';
--查询empno为7876,7788,7900...的雇员信息
select * from emp where empno in(7876,7788,7900);
--查询没上级的雇员的情况
select * from emp where mgr is null;
--查询工资高于500或者岗位为MANGER的雇员,
--同时还要满足他的姓名首字母为J
select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';
--按照工资从低以高的顺序显示雇员的信息
select * from emp order by sal;--默认 asc 升序
select * from emp order by sal desc;
--按照入职的先后顺序排列
select * from emp order by hiredate asc;
--按部门号升序而雇员的工资降序排列
select * from emp order by deptno,sal desc;
--统计每个人的年薪,并按照从低到高的顺序排列
select ename,(sal*13+ISNULL(comm,0)*13) 年薪 from emp order by 年薪;
--查询所有员工中最高的工资和工资最低的工资
select MIN(sal) 最低,MAX(sal) 最高 from emp ;
select ename,sal 最低 from emp
where sal=(select MIN(sal) from emp);
select ename,sal 最高 from emp
where sal=(select MAX(sal) from emp);
--sql语句中,先从右边开始扫描,即先执行右边
--显示所有员工的平均工资
select avg(sal) 平均工资,sum(sal) 总工资 from emp;
--把高于平均工资的雇员名字及其工资显示出来,并把平均工资显示出业
select ename,sal from emp where sal>(select AVG(sal) from emp);
--计算共有多少员工
select COUNT(*) from emp;
--查询每个部门的平均工资和最高工资
select deptno,avg(sal) 平均工资,MAX(sal) 最高工资 from emp group by deptno;
--查询每个部门的平均工资和最高工资,并显示部门名称
select emp.deptno,dept.dname,avg(sal) 平均工资,MAX(sal) 最高工资 from
emp,dept where emp.deptno=dept.deptno group by emp.deptno,dept.dname;
--查询每个部门的每种岗位的平均工资和最低工资
select deptno,job,AVG(sal) 平均工资,MIN(sal) 最低工资 from emp
group by deptno,job order by deptno,job;
--查询平均工资低于2000的部门号和它的平均工资
select deptno,AVG(sal) 平均工资 from emp
group by deptno having AVG(sal)>2000 order by AVG(sal);
--having往往和group by结合使用,可以对分组查询结果进行筛选
--复杂查询
select * from emp;
select * from dept;
--多表查询
--显示雇员的名称,雇员工资及所在部门的名字,并按部门排序
select * from emp,dept where emp.deptno=dept.deptno and dept.dname='sales';
select ename,sal,loc,e.deptno from emp e,dept d where e.deptno=d.deptno;
select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=d.deptno order by d.dname;
--如何显示部门号为10的部门名、员工名和工资
select d.dname,e.ename,e.sal from emp e,dept d where e.deptno=d.deptno and e.deptno=10;
--显示某个员工上级领导的姓名,比如'FORD'的上级
--1.知道FORD上级编号
select ename,'ford' 上级 from emp where
empno=(select mgr from emp where ename='ford');
--显示公司每个员工的名字和他的上级的名字
select worker.ename 雇员,boss.ename 上级 from emp worker,emp boss where worker.mgr=boss.empno;
--单行子查询,显示与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);
--如何显示高于部门平均工资的员工的名字,薪水,和他部门的平均工资
--1.首先要知道各个部门的平均工资
select AVG(sal),dept from emp group by deptno;
--2.把上面的查询结果,当做一个临时表对待
select emp.ename,emp.sal,tem.myavg,emp.deptno from
emp,(select avg(sal) myavg,deptno from emp group by deptno) tem
where emp.deptno=tem.deptno and emp.sal>tem.myavg
--请显示第5个到第10个入职的雇员(按时间的先后顺序)
--1.显示第1个到第4个入职的雇员
select top 4 * from emp order by hiredate;
--2.排除前面4条
select top 6 * from emp where empno not in
(select top 4 empno from emp order by hiredate) order by hiredate;
--显示第5个到第9个人的信息(按照薪水的高低)
select top 5 * from emp where empno not in
(select top 4 empno from emp order by sal desc) order by sal desc;
--如何删除一张表中的重复记录
create table cat(
catId int,
catName varchar(40));
insert into cat values(2,'bb');
select * from cat;
--删除重复记录
select distinct * into #temp from cat;
delete from cat;
insert into cat select * from #temp;
drop table #temp;
--讲左外连接与右外连接
--显示公司每个员工和它的上级的名字
--内连接
select w.ename,b.ename 上级 from emp w,emp b where w.mgr=b.empno;
--显示公司每个员工和它的上级的名字,要求没上级的也要显示
--左外连接:指左边的表的记录全部显示,若没有匹配的记录则用null填充
select w.ename,b.ename 上级 from emp w left join emp b on w.mgr=b.empno;
--右外连接:指右边的表的记录全部显示,若没有匹配的记录则用null填充
select w.ename,b.ename 上级 from emp b right join emp w on w.mgr=b.empno;
--==========================================================================
--约束
--创建一张表
--行级定义
--not null约束
drop table test1
create table test1(
testId int primary key identity(1,1),--自增长
testname varchar(30) not null,--不为空
testpass varchar(30) not null,
testage int
)
insert into test1(testname,testpass,testage) values('jim','yes',3);
select * from test1;
--unique约束
drop table test2
create table test2(
testId int primary key identity(1,1),--自增长
testname varchar(30) unique,--不为空
testpass varchar(30) not null,
testage int
)
insert into test2(testname,testpass,testage) values('jim','yes',3);
--复合主键
--表级定义
drop table test3
create table test3(
testId int,--自增长
testname varchar(30),--不为空
testpass varchar(30),
testage int,
primary key(testId,testname)
)
--check约束
drop table test4
create table test4(
testId int primary key identity(1,1),--自增长
testname varchar(30) unique,--不为空
testpass varchar(30) not null,
sal int check (sal>=1000 and sal<=2000)--规定sal的值是1000到2000
)
insert into test4 values('aa','aa',1900);
insert into test4 values('bb','bb',2200);
--default使用
drop table mes;
create table mes(
mdsId int primary key identity(1,1),
mescon varchar(2000) not null,
mesDate datetime default getdate()
)
insert into mes(mescon,mesDate) values('你好吗?','2000-11-11');
select * from mes;
--===================================================================
--商店售货系统设计案例
商品goods(商品号goodsId,商品名goodsName,
单价unitprice,商品类别category,供应商provider);
客户customer(客户号customerId,姓名name,住址address,电邮email,
性别sex,身份证cardId);
购买purchase(客户号customerId,商品号goodsId,购买数量nums);
--用SQL语言完成下列功能:
--1.每个表的主外键
--2.客户的姓名不能为空值
--3.单价必须大于0,购买数量必须在1到30之间
--4.电邮不能够重复
--5.客户的性别必须是男或者女,默认是男
--6.商品类别是'食物','日用品'
--goods表
create table goods(
goodsId nvarchar(50) primary key,
goodsName nvarchar(80) not null,
unitprice numeric(10,2) check(unitprice>0),
category nvarchar(3) check(category in('食物','日用品')),
provider nvarchar(50)
)
--customer表
create table customer(
customerId nvarchar(50) primary key,
cusname nvarchar(50) not null,
address nvarchar(100),
email nvarchar(100) unique,
sex nchar(1) check(sex in('男','女')) default '男',
cardId nvarchar(18)
)
--purchase表
create table purchase(
customerId nvarchar(50) foreign key references customer(customerId),
goodsId nvarchar(50) foreign key references goods(goodsId),
nums int check(nums>0)
)
--表的修改
--数据库的备份
use aaa
--备份
backup database aaa to disk='f:/sa.bak';
--删除数据库
drop database aaa;
--恢复数据库
restore database aaa from disk='f:/sa.bak';
--有时你可能不需要备份整个数据,表的备份...
--演示sql注入漏洞
drop table users;
create table users(username varchar(30),passwd varchar(30));
insert into users values('luowei','luowei');
select * from users where username='aaaluowei' and passwd='zhangshan' or 1='1';
--找出入职时间是各个倒数第三天的员工名字和入职时间
select ename,hiredate from emp
where (
datepart(mm,hiredate) in (1,3,5,7,8,10,12)
and datepart(dd,hiredate)=29
)
or
(
datepart(mm,hiredate) in (4,6,9,11)
and datepart(dd,hiredate)=28
)
or
(
(
datepart(yyyy,hiredate)%4=0
and datepart(yyyy,hiredate)%100<>0
or datepart(yyyy,hiredate)%400=0
)
and datepart(mm,hiredate)=2
and datepart(dd,hiredate)=27
)
or
(
datepart(mm,hiredate)=2
and datepart(dd,hiredate)=26
);
--找出12年前入职的员工
select * from emp where datediff(year,hiredate,getdate())>22;
--以首字母大写的方式显示所有员工的姓名
select upper(substring(ename,1,1))+
lower(substring(ename,2,len(ename))) from emp;
--显示正好为5个字符的员工姓名
select * from emp where len(ename)=5;
--显示所有员工的姓名，用a替换所有"A"
select replace(ename,'A','a') from emp;
--显示满10年服务年限的员工的姓名和受雇日期
select ename,hiredate from emp where datediff(year,hiredate,getdate)>10
--显示所有员工的，加入公司的年份和月份，按受雇日期所在月排序，若月份相同则将最早年份的员工 排在最前面
select ename,datepart(year,hiredate) y,datepart(mounth,hiredate) m
from emp order by m,y;
--列出至少有一个员工的所有部门
select count(*),deptno from emp group by deptno having count(*)>0;
--列出薪水比"smith"多的所有员工
select * from emp where sal>(select sal from emp where ename='smith');
--列出所有员工的姓名及其直接上级的姓名
select w.ename,b.ename from emp w,emp b where w.mgr=b.mgr;
--列出受雇日期晚于其直接上级的所有员工
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.ename,e.job from emp e right join dept d
on e.deptno=d.deptno;
--列出最低薪金大于1500的各种工作
select min(sal),job from emp group by job having min(sal)>1500;
--列出在部门"SALES"(销售部)工作的员工的姓名
select ename,'sales' from emp where deptno=
(select deptno from dept where dname='sales');
--列出薪金高于公司平均薪金的所有员工
select * from emp where sal>(select avg(avg) from emp);
--列出薪金高于部门30中员工的所有员工的薪金的员工姓名和薪金
select ename,sal from emp where sal>
(select max(sal) from emp where deptno=30);
--列出每个部门工作的员工数量、平均工资和平均服务期限
select count((*) 员工数量,avg(sal) 平均工资,avg(datediff(year,hiredate,
getdate())) 平均服务年限,deptnofrom emp group by deptno;
--列出所有员工的姓名、部门名称和工资
select emp.ename,dept.dname,emp.sal from emp,dept where emp.deptno=dept.deptno;
--列出从事同一种工作但属于不同部门的员工的一种组合
select w1.ename,w1.job,w1.deptno,w2.ename,w2.job,w2.deptno from
emp w1,emp w2 where w1.job=w2.job and w1.deptno<>w2.deptno;
--列出所有部门的详细信息和部门的人数
select d1.dname,d1.loc,isnull(d2.c,0) 人数 from dept d1 left join
(select count(*) c,deptno from emp group by deptno) d
on d1.deptno=d.deptno;
--列出manager(经理)的最低薪金
select min(sal) manager_sal from emp job='manager';
--列出所有员工的年工资，按年薪从低到高排序
select (sal+isnull(comm,0))*12 年工资,ename from emp order by 年工资;
