java 从零开始，学习笔记之基础入门<SQL_Server_常用查询>（二十二）

SQL_Server_常用查询

--创建一张person表tb_person

--表中的字段pid(id) pname(姓名) pass(密码) psex(性别) pcomp(公司)

 

create table tb_person(

   pid int  identity(1,1),

   pname varchar(32)notnull,

   pass  varchar(32)notnull,

   psex varchar(4)  notnull,

   pcomp varchar(32),

  

   primary key(pid)

 

)

 

--往表中插入条数据

 insert into tb_person (pname,pass,psex,pcomp)values('am','123','男','华为');

 insert into tb_person (pname,pass,psex,pcomp)values('es','456','男','天灾');

 insert into tb_person (pname,pass,psex,pcomp)values('bm','789','男','一号店');

 insert into tb_person (pname,pass,psex,pcomp)values('pa','11','男','一盘');

 insert into tb_person (pname,pass,psex,pcomp)values('ss','12','男','而非');

 insert into tb_person (pname,pass,psex,pcomp)values('ts','13','男','斯达');

 insert into tb_person (pname,pass,psex,pcomp)values('apm','14','女','科索');

 insert into tb_person (pname,pass,psex,pcomp)values('pom','15','男','花萼');

 insert into tb_person (pname,pass,psex,pcomp)values('gx','16','男','武汉群所');

 insert into tb_person (pname,pass,psex,pcomp)values('fj','17','女','江浙软件');

insert into tb_person(pname,pass,psex,pcomp)values('fj','17','女','江浙软件');

 insert into tb_person (pname,pass,psex,pcomp)values('gx','16','男','武汉群所');

 

--查询表中的部分列

select pname ,pcomp  from tb_person;

--查询表中所有的数据

select *from tb_person;

select *from tb_personwhere psex='男' or psex='女';

--查询表的记录条数

select count(*) from tb_person;

--在做查询的时候，如果一个表中有两条或者多条相同的数据，查询的时候就应该不取出相同的数据

--使用distinct这个关键字去出重复数据

select distinct pname,pass,psex,pcompfrom tb_person;

--表中的重复数据还是存在的，并没有删除

--查询pid为到之间的记录

select * form tb_personwhere pid>3and pid <7;

--查询pid不等于的所有记录

select *from tb_personwhere pid<>4;

select *from tb_personwhere pid!>4;

--包括３和７

select *from tb_personwhere pidbetween 3and 7;

 select * from tb_person where pid not between 3and 7;

 

--in 与not in

--in 后面跟的是一个字段的集合

select *from tb_person where pidin (3,4,5,6,7);

--子查询语句查询的时候返回多个pid的值作为一个集合

--主查询语句根据子查询的语句返回的值显示查询内容

select *from tb_person where pidin (

  select pid from tb_person where pid between 3 and 7

);

 

--group by 按照条件进行分组

--按照公司进行分组,显示每个公司的人数

select pcomp as '公司名',count(*) as '人数'from tb_person groupby pcomp;

--按照性别分男子组和女子组

select pname,psex,count(psex)from tb_person groupby psex,pname;

--注意:如果普通列要出现在显示的目标上,那么普通列也需要出现在group by语句中

 

--having 跟上对分组之后的组的条件

--按照公司进行分组,取出分组之后公司人数>1的公司名

 

select pcomp,count(pname)from tb_persongroupby pcomp having count(pname)>=2;

--having和where有什么区别?

--having语句是对分组后的条件选择where是对分组前的条件选择

--查询女性人数大于多少的公司?

select pcomp,count(psex)from tb_personwhere psex='女'groupby pcomp having count(psex)>=2;

 

 

--top关键字

select *from tb_person;

--显示表中的前五条记录

select top 5*from tb_person;

--显示-10这条记录

select top 5*from tb_person where pid> 5;

--分页的变量就一个页数常量每页显示的个数

select top 5*from tb_person where pidnot in(

   select top 10 pid from tb_person order by pid

)orderby pid;

--(页数-1)每页的条数

 

多表连接查询

--多表链接查询

--创建一张tb_book表(book(书id),bookName(书名),authoorid(作者id))

--创建第二表tb_author表(authorId,anthorName,authorTel)

create table tb_book(

  bookId int identity(1,1),

  bookName varchar(32)notnull,

  authorId int not null,

 primary key(bookId)

)

--1 <三国演义> 2

 

 

create table tb_author(

  authorId int identity(1,1),

  authorName varchar(32)notnull,

  authorTel varchar(32),

  primary key (authorId)

 

)

--1 曹雪芹123456

--2 罗贯中789

--往两张表中插入数据

insert into tb_authorvalues('曹雪芹','123456');

insert into tb_authorvalues('罗贯中','123456');

insert into tb_authorvalues('李时珍','123456');

insert into tb_authorvalues('周易','123456');

insert into tb_authorvalues('老子','123456');

 

insert into tb_bookvalues('红楼梦',1);

insert into tb_bookvalues('三国演义',2);

insert into tb_bookvalues('西游记',7);

insert into tb_bookvalues('本草纲目',3);

insert into tb_bookvalues('易经',4);

insert into tb_bookvalues('道德经',5);

insert into tb_bookvalues('三字经',8);

insert into tb_bookvalues('葵花宝典',9);

insert into tb_bookvalues('水浒传',10);

 

--查询两张表的所有记录

select *from tb_book b,tb_author awhere b.authorId=a.authorId;

--没有设置主外键,查询出来的记录中如果book表中的记录大于作者表id的个数

--,查询出来的时候就用之前的记录与多出来的bookId拼接

--这种情况不符合实际情况

--可以将book表中的authorId设置成外键关联author表的主键

--建立主外键关联之后,那么book表中插入书的时候就必须保证在author表中存在authorId字段

--否则书的信息是不能保存在book表中的

 

--设置外键 book表的authorId作为外键author表的authorId作为主键

alter table tb_bookaddconstraint baforeignkey (authorId)references tb_author;

--清空两张表的记录

truncate table tb_author;

truncate table tb_book;

--如果两张表是主外键关联,那么一张表中可以存在多个外键,只能存在一个主键

 

 

--查询没有书的作者

  select authorName from tb_author where authorId not in(

select b.authorIdfrom tb_book b,tb_author awhere b.bookId=a.authorId

)

 

 

--book表通过设置外键之后不能插入没有authorId的书,但是author表可以插入多条记录

--作者表中没有写书的作者

--把所有作者和所有书都输出来

--可以采用外连接的方式来输出两张表中所有的记录

--外连接分为:左外连接右外连接内连接全连接交叉连接等

--通过左连接的方式把两张表中的记录都查询出来

--使用book表的id作为标准到作者表中去匹配匹配到对应的作者记录拼接到book表

 

select *from tb_book bleftouter join tb_author aon b.authorId=a.authorId;

 

select *from tb_author aleftouter join tb_book bon a.authorId=b.authorId;

 

--换位

select *from tb_author aleftouter join tb_book bon a.authorId=b.authorId;

--以左边的表为基准去与右边的表中的数据匹配,如果有相同的数据,则将左边的表拼接在右边表的左边

--如果左边表有数据,右边表没有数据则右边表的数据用null填充

--用 *=代表左连接

select a.*,b.*from tb_author a,tb_book bwhere a.authorId*= b.authorId;

 

--右连接

select *from tb_author brightouter join tb_book aon b.authorId=a.authorId;

--右连接符号

select a.*,b.*from tb_author a,tb_book bwhere a.authorId=* b.authorId;

 

--全连接

select a.*,b.*from tb_author afullouter join tb_book bon b.authorId= b.authorId;

 

--交叉连接

select *from tb_book bcrossjoin tb_author aorderby b.bookId;

 

 

 

 

 

 

--左连接右连接顺序比较(运行下面三个例子对照)

--左连接

select *from tb_author aleftouter join tb_book bon a.authorId=b.authorId;

 

select *from tb_book bleftouter join  tb_author a on a.authorId=b.authorId;

 

--右连接

select *from  tb_book brightouter join  tb_author a on b.authorId=a.authorId;