查询

      欢迎来到我的博客，希望大家一起学习和讨论：

    1、使用LIKE、BETWEEN、IN进行模糊查询

    --like模糊查询

 select id,name from users where name like '%b%'

    --between...and

 select id,name from users where id between 1 and 2

 select id,name from users where id >=1 and id<=2

      --in

 select id,name from users where id in(1,2,3)

      

     2、在查询中使用聚合函数(avg,sum,max,max,count)     

     --sum  avg

 select sum(id) as 总和 from users

 select avg(id) as 平均 from users

      --max min

 select max(id) as 最大,min(id) as 最小 from users

      --count

select count(*) as 总人数 from users

select count(password) as 总人数 from users

     

     3、使用GROUP BY进行分组查询

 select id,avg(id) as 平均 from users group by id

 select id,avg(id) as 平均 from users group by id,name

     4、进行多表联结查询

           <1>内联结(INNER JOIN)

           <2>外联结

           <3>左外联结   (LEFT JOIN)

           <4>右外联结   (RIGHT JOIN)

           <5>完整外联结(FULL JOIN)

           <6>交叉联结(CROSS JOIN)

   --内连接查询(跟表的位置无关)

 select u.name,s.grade from score as s inner join users as u on s.uid=u.id

 select u.name,s.grade from users as u  inner join score as s on s.uid=u.id

   --左外连接查询(跟表的位置有关)

 select u.name,s.grade from score as s left join users as u on s.uid=u.id

 select u.name,s.grade from users as u  left join score as s on s.uid=u.id

--右外连接查询(跟表的位置有关)

select u.name,s.grade from score as s right join users as u on s.uid=u.id

select u.name,s.grade from users as u  right join score as s on s.uid=u.id