1、多表连接查询
1>左连接:left join........on 以lzcardapp为主表,查询出documentsampling 表中takebackno 字段相同的数据
例:select * from lzcardapp a LEFT JOIN documentsampling b on a.takebackno = b.takebackno;
2>右连接:right join.......on 以documentsampling 为主表,查询出lzcardapp 表中takebackno 字段相同的数据
例:select * from lzcardapp a RIGHT JOIN documentsampling b on a.takebackno = b.takebackno;
3>内连接:inner join ......on 取两表的交集
例:select * from lzcardapp a inner JOIN documentsampling b on a.takebackno = b.takebackno;
该语句等同与 ,但若追求查询速度时应选择inner join,可以将数据量较小的表放在前面,数据量较大的表放在后面
例:select * from lzcardapp a,documentsampling b where a.takebackno = b.takebackno;
2、去重
1>DISTINCT:消除重复的值并至返回唯一的值,该关键字只能放在字段前,否则报错 ,并且如果跟多个字段的话,会多个字段都进行去重
例:select DISTINCT sex,age FROM student;
3、限制查询行数
1>limit:limit 指定行数开始(从0开始),要返回的行数
例:select id,name,age from person limit 1.4
返回该表从第二行开始的4条数据
4、排序
1>关键字:order by
例:select * from person order by age
2>排序大小:desc,asc
例:select * from person order by age desc ;按年龄倒序排序
例:select * from person order by age [asc];按年龄正序排序,不写时默认按正序排序
5、条件语句
1> =:等于
2> >:大于
3> <>:不等于
4> >=:大于等于
5> !=:不等于
6> !>:不大于
7> <:小于
8> between:在指定的两个值之间,该关键字只能用于数值类型的列
例:select * from person where age between 20 and 35; 查询年龄在20到 35之间的数据[包括20,35这两个值]
9> <=:小于等于
10> is null:为null的值
11> !<:不小于
12> is not null:不为null的值
13> and:满足所有给定条件的行
例:select * from person where sex='女' and age = '25';
14> or:匹配任一给定条件的行
例:select * from person where age = '20' or age = '21';
15> in:范围中的每个条件都可以匹配
例:SELECT * FROM demo where no in ('11','12','13','14','15');
16> not:否定其后条件
例:SELECT * FROM my_table WHERE my_column NOT LIKE '%abc%';
17> like:模糊查询
例:select * from person where name like '张%';查询姓张的数据
例:select * from person where name like '王_三';查询所有叫王某三的数据
6、分组
1>group by
例:select age,count(1) from person group by age;
2>having:过滤分组
例:select age,count(1) as num from person group by age having number>1;
3>having和where的区别:where在数据分组前过滤,having在数据分组后进行过滤;