表查询操作

1.普通查询

1 select   待显示列字段  from  表名 [where 条件];    
2 例：select userid ,username from userinfo where userage<30;
3 #查询所有字段使用：select * from 表名;

2.排序查询　　order by asc | desc

1 select * from userinfo order by userage asc;        //升序
2 select * from userinfo order by userage,userid desc;        //降序

3.区间查询　　between | and

1 select * from userinfo where userage>=18 and userage<=40;
2 select * from userinfo where userage between 18 and 40;

4.范围查询　　in | not in

1 select * from userinfo where useraddress in ('武汉','罗马');
2 select * from userinfo where useraddress not in ('武汉','罗马');

5.模糊查询　　like % | _ （下划线一次只能匹配任意一个字符）

1 select * from userinfo where username like '张%';    ##以张开头的数据
2 select * from userinfo where username like '%亮';    ##以亮结尾的数据
3 select * from userinfo where username like '%亮%';   ##包含亮的数据
4 select * from userinfo where username like '__民';   ##第三个字是民的数据

6.为空查询　　is null | is not null

1 select * from userinfo where useraddress is not null;

7.多条件查询　　and | or

1 select * from userinfo where usersex='男' and userage>20;
2 select * from userinfo where usersex='男' or userage>20;

8.唯一查询　　distinct

1 select distinct useraddress from userinfo; 

9.分组查询　　group by 字段 having 多条件表达式

1 #求出每个城市有多少人，并显示姓名，统计总人数
2 select useraddress,count(*) ,group_concat(username) from userinfo group by useraddress with rollup;
3 #求出每个城市有多少男的,有多少女的
4 select useraddress,usersex,count(*) from userinfo group by useraddress,usersex;
5 #求出有两个年龄大于40岁以上人的城市
6 select  * from userinfo where userage>40  group by useraddress having count(*)=2;
7 ##where，order by，limit 的先后顺序
8 ##查询前10个男生,按照年龄排序    select * from userinfo where usersex='男' order by userage limit 0,10;
9 ##如何在查询中筛选行：where；如何在查询中筛选列：select 列 from

10.分页查询　　limit ？,? （第一个？：index从即开始（0），第二个？：总共显示几个）

1 select * from userinfo limit 3,7;
2 ##每页显示10条,如何显示第2页；页码:page 每页显示:num
3 select * from userinfo limit (page-1)*num,num;

11.别名查询　　as

1 select userid as 编号 ,username as 姓名 ,usersex as 性别 from userinfo u;

12.函数查询

 1 #数学函数
 2 select abs(-4),abs(5);  ##绝对值
 3 select bin(5),oct(13),hex(21);
 4 select ceiling(5.4),floor(5.7),round(5.7);
 5 select greatest(6,5,4,6,7,3),least(6,5,34,7,8,2);
 6 select mod(7,5);
 7 select pi();
 8 select rand();  ##随机数
 9 select round(); ##四舍五入
10 #聚合函数
11 select count(*) from userinfo;    ##表中总行数
12 select max(字段),min(字段) from userinfo;
13 select sum(字段),avg(字段) from userinfo;    ##总和及平均数
14 #字符函数
15 select ascii('a');
16 select length('a比cd'),char_length('a比cd'),bit_length('a比cd');    ##计算字符串个数
17 select concat('a','b','c','d'),concat_ws(',','a','b','c','d');        ##连接字符串
18 select lower('abcd'),upper('abcd');    ##转换大小写
19 select insert('abcdefg',2,3,'888'),replace('abcdefg','cd','888');    ##替换字符串
20 select instr('abcdefg','c'),position('c' in 'abcdefg'),find_in_set('21',userage) from userinfo;
21 select left('abcdefg',3),right('abcdefg',3);    ##返回左边或右边指定个数的字符
22 select lpad('hello',10,'???'),rpad('hello',10,'???');    ##在左边或右边填充字符串
23 select ltrim('  a   b   c   '),rtrim('  a   b   c   '),trim('  a   b   c   ');    ##删除空格
24 select trim('xy' from 'asdasdxyadsfdas');    ##删除指定字符串
25 select reverse('abcd');    ##反转字符串
26 select substr('abcdefg',2,3);
27 ##时间日期函数
28 select now();    ##获取当前系统日期和时间
29 select curdate(),current_date();    ##获得系统当前日期
30 select curtime(),current_time();    ##获取系统当前时间
31 select year(now()),month(now());
32 select hour(now()),minute(now()),second(now());    ##获取当前时间的时分秒
33 select dayofweek(now()),dayofyear(now()),dayofmonth(now());    ##返回今天是一周/年/月的第几天
34 select date_add(now(),interval -3 month);
35 select unix_timestamp(),unix_timestamp(now());    ##返回nuix格式的时间戳
36 select from_unixtime('1566396366');    ##将unix格式的时间戳转换为普通格式时间
37 ##系统函数(类型转换) cast(值 as 新类型) 
38 select cast('-029' as signed integer);
39 select cast(now() as char);
40 select cast(now() as signed integer);
41 ##加密函数
42 select password('12346');
43 select md5('123456');
44 select encode ('123456','123');    ##加密
45 select decode(encode('123456','123'),'123');    ##解密
46 ##其他函数
47 select format (12313.1313313,3);    ##格式化数字，保留小数点后指定位数
48 select inet_aton('192.168.0.3'),inet_ntoa('3232235523');    ##字符串网络点地址与数值网络地址相互转换

13.表连接查询　　join

 1 #左外连接(重点)
 2 select 待显示列字段 from 左表 left [outer] join 右表 on 连接条件 [where 筛选条件];
 3 select * from classinfo c left outer join stuinfo s on c.classid=s.classid;
 4 #右外连接
 5 select 待显示列字段 from 左表 right [outer] join 右表 on 连接条件 [where 筛选条件];
 6 select * from classinfo c right outer join stuinfo s on c.classid=s.classid;
 7 #内连接(重点)
 8 select 待显示列字段 from 表a inner join 表b on 连接条件 [where 筛选条件];
 9 select 待显示列字段 from 表a,...表n where 连接条件 [and 筛选条件];
10 例：select province ,city ,area from provinces p,cities c ,areas a where p.provinceid=c.provinceid and c.cityid=a.cityid;
12 #获取浙江省的信息
13 select province ,city ,area from provinces p,cities c ,areas a where p.provinceid=c.provinceid and c.cityid=a.cityid and province='浙江省';
15 #显示 省名称,市名称,区县名称
16 select province,city,area from cities c left join provinces p on p.provinceid=c.provinceid left join areas a on c.cityid=a.cityid;

14.子查询：将一个查询的结果当成是一个查询的条件，那么这个查询就是子查询，外部查询就是父查询。

　　1）带any、some关键字的子查询：表示满足其中任一个条件，就返回一个结果作为外层查询的条件。

1 例：select * from userinfo where userage>any(select userage from userinfo where useraddress='苏州');

　　2）带all关键字的子查询：使用all时需要同时满足所有内层查询的条件。

　　3）带exists关键字的子查询：exists关键字后面的参数是一个任意的子查询，如果至少返回一行，则exists结果为true并进行外查询；如果没有返回，则exists结果为false，不进行外部查询。

1 例：select * from userinfo where userage<30 and exists (select username from userinfo where username='白莲女');

　　4）带in关键字的子查询：内层查询仅仅返回一个数据列，这个数据列里的值将提供给外部查询语句进行比较查询。

1 例：查询与'王璐'或者'郭二旦'地址一样的人
2 select * from userinfo where useraddress in ( select useraddress from userinfo where username='王璐'or username = '郭二旦' );

15.合并查询结果-->并集（union，union all），交集（intersect），差集（minus）

1 例：select userid from userinfo where userid<10  
2   　union
3   　select userid from userinfo where userid between 5 and 14;

16.使用正则表达式查询　　regexp

 1 字符“ ^ ” 匹配以特定字符或字符串开头的文本
 2 　　例：select * from userinfo where username regexp '^折';
 3 字符“ $ ” 匹配以特定字符或字符串结尾的文本
 4 　　例：select * from userinfo where username regexp '女$';
 5 字符“ . ” 匹配任意一个字符
 6 字符“ * ”匹配前面的字符任意多次，包括0次；“ + ”匹配前面的字符至少一次
 7 匹配多个字符串，使用分隔符“ | ”隔开
 8 　　例：select * from userinfo where username regexp '折|女';
 9 方括号“ [] ”匹配指定字符中的任意一个
10 　　例：select * from userinfo where userage regexp '[03]';
11 “ [^字符集合] ” 匹配不在指定集合中的任意字符
12 　　例：select * from userinfo where userage regexp '[^50-60]';
13 字符串{n, } 表示至少匹配n次前面的字符串；字符串{n,m}表示匹配前面的字符串不少于n次，不多于m次
14 　　例：select * from userinfo where userage regexp '2{1,}';