sql2005的分组、排序、连接

select getdate();

use test;
select  * from student;
insert into student values('王五', 'wenbin','女', 4);

-- group by  分组
-- distinct   消除重复记录
select sex from student group by sex;
--只有相同的性别的那些记录就会只取一条记录
select distinct sex from student ;
--如果有两条记录的sex和gradeid都相同，那么就会只去一条记录
select distinct sex, gradeid from student;
--如果用到了group by分组，那么前面的查询只能是这些分组的字段和用聚合函数
select sex, id from student group by sex, id;
select count(sex) from student group by id,sex;
--下面是一个固定顺序，
where , group by , having, order by

insert into student(name, password, sex, gradeid)
select name, password, sex, gradeid from student;
--如果根据那个字段分组，那么我们的having的条件也只能对分组的那个字段进行条件的刷选或者用聚合函数
select sex from student where id < 16 group by sex having sex <> '男';
select * from student;

select sex, gradeid from student where id > 14 group by sex, gradeid order by sex;

--聚合函数
select count(id) from student where sex = '女';
select  avg(id) from student ;
select sum(id) from student;
select max(id) from student;
select min(id) from student;

select sex, avg(id) from student group by sex having avg(id) > 12;

--多表查询
create table test1(
 id int,
    names varchar(10)
)
insert into test1 values(1,'张三');
insert into test1 values(2,'李四');
insert into test1 values(3,'王五');
insert into test1 values(4,'小刘');
insert into test1 values(5,'王三');
insert into test1 values(7,'小李子');

create table test2(
    id int,
    age int
)
insert into test2 values(1,34);
insert into test2 values(2,10);
insert into test2 values(3,45);
insert into test2 values(4,30);

select * from test1 ;

/*多表联结
  内联结  inner join
  外联结 
   左外
   右外
      全联结
  交叉联结
*/
--内连接
--等价连接
select * from test1 inner join test2  on test1.id = test2.id;

--外连接
--左外连接
 --左联结是: 左表记录全部有,右表记录是多退少补(null)
select * from test1  left outer join test2 on test1.id = test2.id;
 --右联结是: 右表记录全部有,左表记录是多退少补(null)
select * from test1  right outer join test2 on test1.id = test2.id;
--全链接，就是左右连接的整合
select * from test1 full outer join  test2 on test1.id = test2.id;
--交叉连接，左边的每一条记录与右边的每一天结合
select * from test1 cross join test2;
select * from test1, test2;
--这是判断系统中是否存在这个数据库
if exists(select * from sys.databases where name = 'caohuan')
drop database caohuan;

--如果表存在，就删除表
if exists(select * from sysobjects where name = 'dep')
drop table dep;
--这是查找系统中所有的数据库
select * from sys.databases;