基本常用查询

　　select * from student; 　　查询所有 　　select all sex from student; 　　过滤重复 　　select distinct sex from student; 　　统计 　　select count(*) from student; 　　select count(sex) from student; 　　select count(distinct sex) from student; 　　取前N条记录 　　select top 3 * from student; 　　列重命名 　   　select id as 编号, name '名称', sex 性别 from student; 　　表重命名 　   　select id, name, s.id, s.name from student s;

　　列运算 　　select (age + id) col from student; 　　select s.name + '-' + c.name from classes c, student s where s.cid = c.id;

　　条件 　　select * from student where id = 2; 　　select * from student where id > 7; 　　select * from student where id < 3; 　　select * from student where id <> 3; 　　select * from student where id >= 3; 　　select * from student where id !> 3; / 　　select * from student where id <= 5; 　　select * from student where id !< 5; 　　并且 　   　select * from student where id > 2 and sex = 1;

　　或者 　    　select * from student where id = 2 or sex = 1;

　　--between ... and ... 相当于并且 　　select * from student where id between 2 and 5; 　　select * from student where id not between 2 and 5;

　　--like 模糊查询 　　select * from student where name like '%a%'; 　　select * from student where name like '%[a][o]%'; //a和o一定同时存在 　　Select * from student where name like '%[a,o]%';//a和o不一定同时存在 　　select * from student where name not like '%[a,o]%'; //a和o不存在 　　select * from student where name not like '%a%'; select * from student where name like 'ja%'; 　　select * from student where name like '%[j,n,a]%'; 　　select * from student where name like '%[^ja,as,on]%'; 　　select * from student where name like '%[ja_on]%'; //含有ja或者on

　　--in 子查询 　　select * from student where id in (1, 2);

　　--not in 不在其中 　　select * from student where id not in (1, 2); 　　--is null 是空 　　select * from student where age is null; 　　--is not null 不为空 　　select * from student where age is not null; 　　--order by 排序 　　select * from student order by name; 　　select * from student order by name asc; 　　select * from student order by name desc; //降序排列

　　--group by 分组 　　按照年龄进行分组统计 　　select count(age), age from student group by age;

　　Ø 嵌套子查询 　　将一个table的查询结果当做一个新表进行查询 　　select * from ( 　　select id, name from student where sex = 1 　　) t where t.id > 2;

　　Ø 集合运算 　　操作两组查询结果，进行交集、并集、减集运算 　　1、 union和union all进行并集运算 　　--union 并集、不重复 　   　select id, name from student where name like 'jo%' 　　union 　　select id, name from student where id = 4; 　　--并集、重复        　　select * from student where name like 'ja%' 　　union all 　　select * from student;

　　2、 intersect进行交集运算 　　--交集（相同部分） 　   　select * from student where name like 'ja%' 　　intersect 　　select * from student;

　　3、 except进行减集运算 　　--减集（去除相同部分） 　　select * from student where name like 'ja%' 　　except 　　select * from student where name like 'jas%';

　　Ø 连接查询 　　1、 简化连接查询 　　--简化联接查询 　    　select s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id; 　　2、 left join左连接 　　--左连接  　　select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id;

　　3、 right join右连接 　　--右连接 　    　select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id;

　　4、 inner join内连接 　　--内连接 　　select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id; //inner可以省略

　　5、 自连接（同一张表进行连接查询） 　　--自连接 　　select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex;

　　本站技术原创栏目文章均为中睿原创或编译，转载请注明：文章来自中睿，本站保留追究责任的权利。