server sql 学习笔记

server sql 学习笔记

数据类型

1.字符

1. char 定长字符，不足时会补空格
2. varchar 不定长
3. text 可变长度非unicode，多字节格式存储英文，
4. ntext 可变长度unicode，多字节格式存储unicode，可存各种文字
5. nchar 定长，unicode 编码

6. nvarchar 变长，unicode编码。无论单个字母还是单个汉子都占了2个字节

   2.数字型

7. bit 范围0~1
8. int 范围 负的2的31次方到正的2的31次方减1
9. bigint 范围更大的整数
10. float 放小数不建议使用，放1.211时存进去变成1.211000000000000000001。

11. 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
)


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






--?查询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)
)