EVE Light on Life

SQL学习笔记

#数据类型 ##1.字符

  1. char 定长字符,不足时会补空格
  2. varchar 不定长
  3. text 可变长度非unicode,多字节格式存储英文,
  4. ntext 可变长度unicode,多字节格式存储unicode,可存各种文字
  5. nchar 定长,unicode 编码
  6. nvarchar 变长,unicode编码。无论单个字母还是单个汉子都占了2个字节

2.数字型

  1. bit 范围0~1
  2. int 范围 负的2的31次方到正的2的31次方减1
  3. bigint 范围更大的整数
  4. float 放小数不建议使用,放1.211时存进去变成1.211000000000000000001。
  5. numeric 建议使用。numeric(p,s)。p:长度,S:小数位数

3.时间

  1. datetime
  2. timestamp

主键

字段 int primary key

外键

deptno int foreign key references dept(deptno)
1. 外键只能指向主键
2. 外键与主键的类型需要一致

常用操作

--1.创建表
create table clerk(

cleId int primary key, --主键必须有值
cleName nvarchr(50),
cleAge int
)

--2.插入数据
insert into clerk values()
insert into clerk(cleId,cleName) values() ---插部分数据 

--3.查询数据

--?查询SMITH的薪水,工作,所在部门
select sal,job,deptno from emp where ename='smith'

select distinct deptno from emp

--?显示每个雇员的年工资
select ename,sal*13 年工资 from emp;

--处理空值问题
select ename,sal*13+isnull(comn,0)*13 年工资 from emp;

--如何查找1982年1.1后入职的员工
select * from emp where hiredate > '1982-1-1'

--如何查找工作在2000到5000的员工
select * from emp where sal between 2000 and 5000

--如何显示首字母为s的员工姓名和工资
select ename,sal from emp where ename like 's%'

--如何显示第三个字符为大写o的所有员工的姓名和工资
select ename,sal from emp where ename like '--o%'

--如何显示empno为123,345,800..的雇员情况
select * from emp where empno in (123,345,800)

--使用is null操作符显示没有上级的员工
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 语句。如何按照工资低到高显示员工信息
--order by 默认是升序 asc
select * from emp order by sal asc
select * from emp order by sal desc

--按照部门升序而雇员的工资降序排列,order by 可以根据不同的字段排序
select * from emp order by deptno,sal desc

--使用列的别名排序,使用年薪排序,从低到高排序
select ename,()sal+isnull(comm,0))*13 年薪 from emp order by 年薪



-- 

复杂查询

--如何显示所有员工中最高工资和最低工资
--如何显示最低工资和该雇员的名字
select ename,sal where sal=(select min(sal) from emp)

--显示所有员工的平均工资和工资总和
--把高于平均工资的雇员的名字和它的工资,并显示平均工资.
select avg(sal) 平均工资,sum(sal) 总工资 from emp
select ename,sal,(select avg(sal) from emp) from emp where sal>(select avg(sal) from emp)
--计算有多少员工
select count(*) from emp

--如何显示每个部门的平均工资和最高工资
select avg(sal),max(sal),deptno 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 order by avg(sal) asc

--数据分组的总结
1. 分组函数只能出先在选择列表中
2. group by having order by的顺序
3. 

--显示公司每个员工名字和他的上级名字
--分析,把emp表看做两个表,分别是worker和boss
--外连接(左外连接 右外连接)
select worker.ename 雇员,boss.ename 老板 from emp worker,emp boss where worker.mgr=boss.empno


--子查询

--如何显示与SMITH同一部门的所有员工

select * from emp where depno=(select depno from emp where ename='smith')

--如何查询和部门10工作相同的雇员名字、岗位、工资、部门号

select * from emp where job in
(select distinct job from emp where deptno=10)

--显示高于部门平均工资的员工名字,薪水,和他部门的平均工资
--1.分析,首先要知道各个部门的平均工资
select avg(sal),deptno from emp  group by deptno
--2.把上面的查询结果作为一张临时表

select emp.ename,emp.ename,tem.myavg 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

select top 6 * from emp where empno not in (select top 4 empno from emp order by hiredate) order by hiredate



--如何删除掉一张表的重复记录
--1.把cat 的记录distinct后的结果放入#temp
select distinct * into #temp from cat
--2.把cat 表的记录清空
delect from cat
--3.把#temp表的数据插入cat
insert into cat select * from #temp
--4.删除temp3
drop table #temp


--左连接和右连接

--显示公司每个员工和他的上级名字,要求没有上级的名字也要显示

select w.ename,b.ename from emp w,emp b where w.mgr=b.empno

--左外连接(left join)表示左边的表的记录全部显示,如果没有匹配的记录就用null来填
--右外连接(right join)表示右边的表的记录全部显示,如果没有匹配的记录就用null来填
select w.ename,b.ename from emp w left join emp b on w.mgr=b.empno

约束

not null

表示改列必须插入数据

unique

该列值不能重复,可以有一个null

primary

一张表只能有一个主键。不能为空不能重复 可以有多个unique 表可以有复合主键

foreign key 外键

外键约束是定义在从表上, 对应的主表必须有主键约束或是unique约束, 当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为Null

check

用于强制

default

赋给默认初始值

--创建一张表

create table test(

testId int primary key identity(1,1), --表示初始为1,自增1
testName varchar(30) unique,
testAge varchar(30) not null,
sal int check (sal>=1000 and sal<=2000), 
sendDate datetime default getdate()
)


商店售货系统表设计案例

--商品goods(商品号goodsId,商品名goodsName,单价uniprice,商品类别category,供应商probider)
--客户customer(客户号custonmerId,姓名name,住址address,电邮email,性别sex,身份证cardId)
--购买purchase(客户号customerId,商品号goodId,购买竖线nums)

--要求
--1.每个表的主外键
--2.客户的姓名不能为空
--3.单价必须大于0,购买数量必须在1~30之间
--4.电邮不能重复
--5.客户的性别必须是男或者女,默认男
--6.商品类别必须是'食物','日用品'


--goods表
create table goods)(
goodsId varchar(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(30) foreign key references customer(customerId),
goodsId nvarchar(50) foreign key references goods(goodsId),
nums int check(nums>0)
)