复习数据库笔记

这两天重新学习了一下数据库，在此做笔记整理。

数据库分为三层： 

物理层，概念层，逻辑层


SQL语言包含四个部分
1.数据 定义 语言DDL:CREATE / DROP / ALTER
2.数据 操作 语言DML:INSERT / UPDATE / DELETE
3.数据 查询 语言DQL:SELECT
4.数据 控制 语言DCL:GRANT / REVOKE

各种编码的区别：
unicode : 两个字节表示一个字符（可以是英文字母s，也可以是汉字）
 对汉字支持比较好
非unicode : 用一个字节表示一个字母，两个字节表示一个汉字
//big5 : 支持繁体
//iso-8859-1编码 : 支持欧文
//GB2312国标码 
//gbk : 支持更多的汉字 
utf-8
utf-16


可以存放汉字，unicode编码：
nchar
nvarchar 


 

drop table abc

--创建表
CREATE table clerk
(cleId int primary key,       //这里声明主键
 cleName nvarchar(50),
 age int
)

--插入
insert into clerk values(1,'贾宝玉',18)
insert into clerk values(2,'贾政',18)
insert into clerk (cleId,cleName) values(3,'林黛玉')

--查询
select * from clerk

--修改
--把贾政的年龄修改成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='薛蟠'


update clerk set cleName='薛宝钗' where age is null


insert into clerk values(5,'贾母',70)
insert into clerk values(6,'焦大',75)


--删除年龄大于70且小于75
delete from clerk where age>=70  and age<=75
--						或者age 是null
delete from clerk where (age>=70  and age<=75) or age is null

--------------------------------下面是针对一个员工、部门表的操作
--创建一个新的数据库
create database spd1
--创建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(8,2),
 comm  numeric(8,2),
 deptno int	foreign key references dept(deptno)		--	因为deptno我们根据需要要做成外键
)


--针对外键，请注意
--①外键只能指向主键
--②外键和主键的数据类型要一致
select * from dept
select * from emp 


--查询所有列
select * frome emp where -- * 能不用就不用 


--查询指定列
select 字段1,字段2 frome 表名 where 条件


--？？？查询Smith的薪水，工作，所在部门
select sal,job,deptno from emp where ename='smith' --sql server 不区分大小写


--如何取消重复行（distinct只能消除完全一样的行，保留一行）
select distinct 字段 from 表名 where 条件


--？？？统计多少个部门
select deptno from emp
select distinct deptno from emp


--使用算数表达式	使用列的别名
--？？？显示每个雇员的年工资+奖金  （年工资就是别名）
select ename,sal*13+comm*13 '年工资' from emp
--如何处理 NULL 的问题 isnull(字段,0)  isnull(comm,0)*13


--如何使用where
--？？？工资高于3000
select * 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


--如何使用 like 操作符 （模糊查询） %表示0到多个字符 _ 表示单个字符
--？？？显示首字符为 s 的员工姓名，工资
select ename ,sal from emp where ename like 's%'
--？？？显示第三个字符为 o 的员工
select ename,sal from emp where ename like '__o%'


--批量查询 in
select * from emp where empno in (7369,7521,7499)


--？？？显示没有上级的雇员的信息
select * from emp where mgr is null



--使用逻辑操作符号
--？？？查询工资高于500或者岗位为MANAGER的雇员，同时还要姓名首字母为J
select * from emp where (sal>500 or job='manager') and ename like 'j%'


--使用order by 按顺序排名  默认是升序排列 asc  / 降序 desc
--？？？按照工资高低排名
select * from emp order by sal desc
--？？？按照入职先后顺序排列
select * from emp order by hiredate asc
select * from emp order by ename asc
--？？？按照部门号升序而员工工资降序
select * from emp order by deptno , sal desc --逗号后面是候选排序规则


--？？？使用别名排序。 统计每个人的年薪，并按照从低到高的顺序排列
select ename , (sal +ISNULL(comm,0))*13 年薪 from emp order by 年薪 


---------分页查询----------------------------------------
等学习了子查询再看
----------------------------------------------------




---------表的复杂查询----------------------------------------
--最低工资
select MIN(sal) from emp
select ename , sal from emp where  sal = (select MIN(sal) from emp)
--平均工资和工资总合
--把高于平均工资的员工名字和他的工资显示,并显示平均工资
select AVG (sal) 平均工资 ,SUM(sal) 总工资 from emp
select ename , sal  from emp where sal > (select AVG(sal) from emp)


--统计共有多少员工
select　COUNT(*) from emp


--GROUP BY 和 having
--group by 用于对查询的结果分组统计
--having 往往和group by 结合使用，可以对分组查询到的结果进行筛选


--？？？显示每个部门的平均工资和最高工资	？并显示部门名称（多表查询）
select AVG(sal) 平均工资,deptno,MAX(sal) 最高工资 from emp group by deptno
--？？？显示每个部门每种岗位的平均工资和最高工资
select avg(sal),min(sal),deptno,job from emp group by deptno ,job order by deptno




--？？？显示平均工资低于2000的部门号和它的平均工资
select AVG(sal) 平均,deptno from emp group by deptno having AVG(sal)<2000




--对数据分组的总结
--1. 分组函数只能出现在选择列表、having 、 order by子句中
--2. 在select语句中 顺序为 group by 、having 、order by ，例如
select AVG(sal) 平均,deptno from emp group by deptno having AVG(sal)>2000 order by avg(sal) desc
--3. 在选择列中如果有列、表达式 、分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则会出错






---------表的多表查询----------------------------------------