一,数据查询
1。基本查询
select [ all | distinct ] * from tablename;(distinct :去掉重复行)
select t.name as username from tablename t ;(count(*),avg(字段),sum(字段),min(字段),max(字段))
select t.id from tablename t where t.name in ('','');(all,any ,between,exists,in,like,some,is null,is not null)
order by desc;asc;
select sex,count(sex) as 人数 from usertable group by sex;( having *;必须和group by 连用,作用于组)
2.嵌套查询
select * from user t where t.id = (select id from userrole where role = 1)
select t.id ,t.name from user t where t.depart = 'A部门' and t.wage > any (select m.wage from user where m.depart = 'B部门');(all :最高;any:最低)
select * from *inner join * on * where *;
左外连接:
select a.id ,a.name ,b.depart from A a left outer join B b on a.id = b.userid;
(左外连接限制b表中的行,而没有显示表a中的行,即a中不符合的数据也显示,但是不符合连接条件的数值都为null)
右外连接:
select a.id ,a.name ,b.depart from A a right outer join B b on a.id = b.userid;
(与左连接相反)
全外连接:
full outer join
交叉连接:cross join