主键与外键、子语句查询.unique.check
主键：须 唯一性，不能为空。
create table bumen
(
code int primary key,--主键：须唯一性，不为空，
--(表示设置主键)当code int primary key(1,2)时，表示code从1开始逐个加2,自增长，一般不用自增长列！
--设置主键可以 大 大 加快查询的速度。减缓插入的速度。
bumen varchar(20),
zhineng varchar(20),
ceo varchar(20),
phone varchar(20),
)
Go
Drop table haha -–删除整个 haha表！
Create table haha
(
Code int ,
Name varchar(20),
Bumen int references [dbo].[bumen]([code]) -- 设置外键！外键约束：数据必须存在于‘主键’！
)
Go
直接操作时，下面操作！
在数据库 表中打开“设计”，右键单击创建列的左头端，打开~主键~设置‘主键’；
打开~关系~设置‘外键’。主键和外键要保证 数据类型一致 才可以设置关系！
use new
go
select *from haha
------------从haha表查询部门人数大于等于5的最大年龄的人的信息！-------
-------------------子查询---------------------------------------------
select bumen ,COUNT(*)from haha group by bumen having COUNT(*)>5
select MAX(age)from haha where bumen ='销售部'
select *from haha where bumen='销售部'and age =35
---------------------汇总查询-----------------------------------------
select *from haha where age =
(select MAX (age)from haha where bumen
in (select bumen from haha group by bumen having COUNT (*)>=5))
and bumen= (select bumen from haha group by bumen
having COUNT(*)>=5)
----------------------按年龄升序排列，显示第6、7、8位的信息------------------
select top 8 *from haha order by age asc
select top 5 *from haha order by age asc
select top 3 *from haha where code not in (select top 5 code from haha order by age )order by age
-----------------------------显示最大年龄、男性的信息--------------------
select top 1* from haha where sex='男' order by age desc
select *from haha where age=(select MAX(age)from haha where sex='男')and sex='男'
---------------------------------------------------------------------
select top 1 bumen ,COUNT (*)from haha group by bumen order by COUNT(*) desc
select distinct bumen from haha
select *from haha where age=35 and bumen = '销售部'
select *from haha where age=35 and bumen =(select top 1 bumen from haha group by bumen order by COUNT(*)desc)
---------------------五个数据一组， “分页” 显示----------------
select top 5 *from haha
select top 5 *from haha where code not in(select top 5 code from haha )
select top 5 *from haha where code not in(select top 10 code from haha )
--查看数据'分组的数量'（按每组5个分组，5.0表示浮点型数值，得出值也就是浮点型，取上限就可以了）----------------
select ceiling (COUNT(*)/5.0)from haha
------------------------------------------------新建bumen 表格-----------------------------------------
create table bumen
(
code int primary key,--主键：须唯一性，不为空，
--(表示设置主键)当code int primary key(1,2)时，表示code从1开始逐个加2,自增长，一般不用自增长列！
bumen varchar(20),
zhineng varchar(20),
ceo varchar(20),
phone varchar(20),
)
go
insert into bumen values(1,'销售部','卖货','快结婚','1123456789')
insert into bumen values(2,'财务部','卖货','快结婚','2123456789')
insert into bumen values(3,'生产部','卖货','快结婚','3123456789')
insert into bumen values(4,'管理部','卖货','快结婚','4123456789')
go
select *from bumen
select *from haha
select distinct bumen from haha
update haha set bumen=1 where bumen ='销售部'
update haha set bumen=2 where bumen ='财务部'
update haha set bumen=3 where bumen ='生产部'
update haha set bumen=4 where bumen ='管理部'
--------------------------'利斯'所在部门的 信息-------------------------------------
select bumen from haha where name='利斯'
select *from bumen where bumen=(select bumen from haha where name='利斯')
----------------------------”快结婚“所在部门所有人的信息-----------------------------------------
select *from bumen where ceo='快结婚'
select * from haha where bumen =(select code from bumen where ceo='快结婚')order by code desc
############### 唯一性约束unique，check 约束 ###########################
类似于设置主键和外键，打开‘设计’在一行的左端右键单击，找到‘索引/键’，
--设置要选中‘列’--唯一性--
create table wtable
(
code int primary key,
name varchar(30),
cid varchar(30)
)
go
create table atable
(
code int primary key,
name varchar(20),
cid varchar(50) unique--unique 代表唯一性！输入信息不能重复！
)
--create unique index_aaa --创建唯一性的索引‘aaa’ 作用在atable 中cid 列！
--on atable (cid) ----------这是在外部增加唯一性的表达方法！
insert into atable values (1,'呵呵','21432432432')
insert into atable values (2,'呵呵','21432432432')--此时这一条不能插入表中！cid信息重复。
select *from atable
([age]>(0)AND[age]<(150))--check 约束表达格式！
