表查询操作
程序员文章站
2024-01-06 20:29:10
1.普通查询 2.排序查询 order by asc | desc 3.区间查询 between | and 4.范围查询 in | not in 5.模糊查询 like % | _ (下划线一次只能匹配任意一个字符) 6.为空查询 is null | is not null 7.多条件查询 and ......
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,}';