本文详细介绍SQL查询技巧，包括复杂查询、子查询、多表连接等高级用法，并深入探讨了常用SQL函数的应用场景及示例。
/*
friuts table
'a2', '103', 'apricot', '25.20'
'b1', '101', 'blackberry', '10.20'
'bs1', '102', 'orange', '11.20'
'm1', '106', 'mango', '15.60'
'o2', '103', 'coconut', '9.20'
't1', '102', 'banana', '10.30'
*/
SELECT * FROM adadb.fruits;
-- 查询s_id 的 平均价格 ,并且保留两位小数
select s_id, round(avg(f_price),2) as '平均价格' from fruits group by s_id;
-- 查询每个供应商 最大的价格 ,并且排序
select s_id,max(f_price) as '贵' from fruits group by s_id order by 贵;
-- 多表链接查询 --内连接
select f.f_name,s.s_name
from fruits f ,suppliers s
where f_name='apple' and f.s_id=s.s_id;
-- 用标准语法
select * from fruits f
inner join suppliers s on f.s_id = s.s_id
where f_name='apple';
-- 自连接--用fruits 这张表就可以解决
select f2.s_id,f2.f_name from fruits f1, fruits f2 where f1.f_id=f2.f_id;
-- 外连接 左连接 ：
select f.s_id,s.s_name from fruits f right join suppliers s on f.s_id=s.s_id;
-- 外连接 右链接
select f.s_id,s.s_name from fruits f left join suppliers s on f.s_id =s.s_id;
-- 子查询
-- 1) 从fruits表中找出 价格最贵的那个 商品的信息
select * from fruits
where f_price = (select max(f_price) from fruits);
-- 2) 从fruits表中找出价格最便宜的那个商品的信息
select * from fruits
where f_price = (select min(f_price) from fruits);
-- 3）从fruits表中找出价格高于平均价格的那些商品的信息
select * from fruits
where f_price>(select avg(f_price) from fruits);
-- 4）从fruits表中找出价格低于平均价格的那些商品的信息
select * from fruits
where f_price<(select avg(f_price) from fruits);
-- 5）从fruits表中找出 价格高于105供应商任一商品价格的 那些水果
select * from fruits
where f_price >any(select f_price from fruits where s_id=105);
-- 6）从fruits表中找出 价格高于105供应商所有商品价格的 那些水果
select * from fruits
where f_price>all(select f_price from fruits where s_id=105);
-- any 与 all 的区别： any 是任意个人 all 是所有
-- exists: 如果子查询有结果,则外层查询执行;
-- 如果子查询没有结果，则外层查询不执行
select '登陆成功' as 查询结果 from fruits
where exists(
select * from fruits
where f_id='a2' and s_id='103' and f_price=25.20
) limit 1;
-- 查询 所有北京市的供货商供应的 商品信息
select * from fruits
where s_id in (select s_id from suppliers where s_city='北京市');
-- 合并查询 --找到更合适的例子
select s_name,s_id from suppliers
where s_id in(103,106)
union All
select s_name,s_id from suppliers
where s_name like '%01';
-- 正则查询
-- ^ ^. 以任意字符开头的
select f_name from fruits where f_name regexp '^a';
-- $ .$ 有任意字符结尾的
select f_name from fruits where f_name regexp 'a$';
-- . 匹配任何单个字符 匹配 a 与 e 之间有三个字符的 所有字段
select f_name from fruits where f_name regexp 'a...e';
-- * f * b 匹配字符 b 前面有0个任意个 字符f
select f_name from fruits where f_name regexp 'b*a';
-- + 匹配前面的字符 1次或多次 ba+ 匹配b开头后面最少一个a--例如 bana bannn
select f_name from fruits where f_name regexp 'ba+';
-- <字符串> 'fa' 匹配包含指定的字符串的文本 两个必须连在一起的
select f_name from fruits where f_name regexp 'ap';
-- <字符串集合> '[fa]' 匹配包含集合中的任何一个字符
select f_name from fruits where f_name regexp '[fa]';
-- [^.] '[^abc]' 匹配任何不包含a,b,c 的字符串
-- 对于这个 是这个字符串只要 Not in any[^ a b c] 这个字符串
-- 必须包含 这三个字母 才会不被查询到
select f_name from fruits where f_name regexp '[^aple]';
-- [^a-cp-z] 匹配不包含 a-c之间 p-z之间的字符串
select f_name from fruits where f_name regexp '[^a-z]';
-- {n} p{2} 匹配2个p或更多的p
select f_name from fruits where f_name regexp 'p{2}';
-- {n,m} p{2,3} 匹配的字符串最少2个p 最多3个p
select f_name from fruits where f_name regexp 'p{2,3}';
-- 数学函数 _在有错误产生时，数学函数将会返回空值null
-- ABS(x) -- 返回x的绝对值
select ABS(-1),ABS(-2); -- 返回 -1,-2 的绝对值
-- ceil(x) --返回大于x的最大整数
select ceil(2.45),ceil(-1); -- 结果 3,-1
-- floor(x) -- 返回小于x的最大整数
select floor(2.45),floor(-2.1); -- 结果 2,-3
-- mod(x,y) -- 返回x/y的模 --求余数
select mod(2,1);-- 0
select mod(1,2);-- 1 取整数
-- rand() --返回0到1的随机值
select rand(); -- '0.8985110520371339'
-- rand()*100 返回100之内的随机数
select rand()*100; -- '3.82232815383219'
select rand(2)*100;-- rand(种子值) 以这个种子为根据。不变
-- ceil(rand()*100) 返回100之内的随机整数
select ceil(rand()*100); -- 50
-- round(x,y) 返回参数x的四舍五入的有y位小数的值
select round(2.456,2); -- 2.46
-- truncate(x,y) 返回数字 X 截断为 y位小数的结果
select truncate(2.456,2); -- 2.45 比较暴力 truncate table；直接删除,不看索引
-- pi() 返回圆周率的值 -默认显示 6位结果
select pi();-- 3.141593
select round(pi(),3); -- 3.142
select truncate(pi(),3); -- 3.141
-- sqrt() 求方根
select sqrt(4); -- 2
select sqrt(0.09); -- 0.3
-- sign 符号函数 正数 显示的是 1 负数显示的是 -1
select sign(-21); -- -1
select sign(21); -- 1
-- power(x,y) 幂运算函数 x^y
select power(2,-1); -- 2^-1 0.5
select power(2,3); -- 2^3 8
-- concat(s1,s2) 将 s1,s2 组成一个新字符串
select concat('测试','成功') as '合并字符串';
-- insert(str,x,y,insertr) 将字符串str 从x位置开始 y个字符串长的 拼接新的字符串 insertr
select insert('123456',2,3,'qwe'); -- 1qwe56
-- lower 小写
select lower('AD'); -- ad
-- upper 大写
select upper('ad'); -- AD
-- left(str,x) 返回字符串最左边的 X 个字符
select left('abcdefg',3); -- abc
-- right(str,x) 返回字符串最右边的 X 个字符
select right('abcdefg',3);
-- lpad(str,n,pad) 用字符串 pad 对 str 最左边进行填充，直到长度为n 个字符长度
-- 如果str 满足长度n 就从str中取，如果小于n 则pad 从昨天开始添加直到满足 n个长度
select lpad('abc',1,'bn'); -- a
select lpad('abc',2,'bn'); -- ab
select lpad('abc',3,'bn'); -- abc
select lpad('abc',4,'bn'); -- babc
-- ltrim(str) 去掉字符串str左侧的空格
select concat('|',ltrim(' abc '),'|'); -- |abc |
-- rtrim(str) 去掉字符串右边的空格
select concat('|',rtrim(' abc '),'|'); -- | abc|
-- repeat(str,x) 返回str重复x次的结果
select repeat('a',2); -- aa
-- release(str,a,b) -- 用字符串b代替str中所有的a
select replace('aaaa','a','b'); -- bbbb
-- strcmp(s1,s2) 比较字符串 s1和 s2 -----88888888888888888
select strcmp('阿','不');
select strcmp('一','二');
-- trim 去掉字符串 行尾 和行头的空格
select concat('|',trim(' abc '),'|'); -- |abc|
-- substring(str,x,y) 返回从字符串str x 位置 截取y个字符
select substring('abcdefg',2,3); -- bcd
-- space(n) 空格函数 生成空格 我觉得没鸡毛用
select concat('|',space(6),'|'); -- | |
-- mid('str',x,y) 返回从字符串str x 位置 截取y个字符
select mid('abcdefg',-1); -- 负数从后面开始数 g
-- locate('str','strs') 返回str在strs中开始位置
select locate('zcz','abcdfg'); -- 0 如果没有就返回0
-- position('str'in'strs') 返回str在strs中开始位置
select position('str' in 'strs'); -- 1 返回开始位置1
-- instr('strs','str') 返回str 在 strs开始位置
select instr('strs','r'); -- 3
-- reverse(str)翻转字符串
select reverse('abc'); -- cba
-- elt(2,'str1','str2') 返回位置2的 str2 如果没有则返回null
select elt(3, '1st', '2nd', '3rd'), elt(3, 'net', 'os'); -- 3rd null
-- field('str','string1','string2','string3','str') 返回4
select field('Hi','hihi','Hey','Hi','bas') as col1; -- 3
-- 使用find_in_set()函数返回子字符串在字符串列表中的位置 --3
select find_in_set('Hi', 'hihi,Hey,Hi,bas'); -- 3
-- 日期函数
-- curdate() 返回当前日期
select curdate(); -- 2018-03-08
-- curtime() 返回当前时间
select curtime(); -- '19:19:03'
-- now 返回当前日期与时间
select now(); -- '2018-03-08 19:19:30'
-- unix_timestamp(date)
select unix_timestamp(now()); -- '1520508024'
select unix_timestamp('2018-03-08 19:22:03'); -- '1520508024'
-- from_unix_unixtime
select from_unixtime(unix_timestamp(now())); -- '2018-03-08 19:22:03'
select from_unixtime('1520508024'); -- '2018-03-08 19:22:03'
-- week(date) 返回日期 date 为一年中的第几周
select week(now()); -- 9
select week('2018-03-08 19:20:24.000000'); -- 9
-- year(date) 返回日期中的年费
select year(now()); -- 2018
select year('2018-03-08 19:20:24.000000'); -- 2018
-- hour(time) 返回time的小时值
select hour(now()); -- 19 晚上7点
select hour('2018-03-08 19:22:03'); -- 19点 这个也可以
-- minute(time) 返回time的分钟值
select minute('2018-03-08 19:22:03'); -- 22
-- monthname 返回date的月份名
select monthname('2018-03-08 19:22:03'); -- 'March'
-- date_formai(date,fmt);
select date_formai('2018-03-08 19:22:03',);
-- 返回日期对应的周索引: 1表示周日，2表示周一，...，7表示周六 mysql>
select dayofweek('2018-03-08'); -- 5 代表周4√
-- 返回日期对应的工作日索引 mysql>
select weekday(now() and ); -- 3 想不明白 报个错
-- 返回指定日期在一年中的位置 weekofyear(date) weekofmonth(date)
select weekofyear(now());
-- 返回季度值
select quarter('2018-3-8'); -- 1个季度
-- 返回指定时间的秒值
select second('10:05:03'); -- 3
-- extract(type from date)：获取日期的指定部分 -- 提取日期或者时间值 mysql>
select extract(year from '2011-07-02') as col1,
extract(year_month from '2011-07-12 01:02:03') as col2,
extract(day_minute from '2011-07-12 01:02:03') as col3;
-- time_to_sec(time) 返回秒
select time_to_sec('01:00:00'); -- 3600s
-- sec_to_time 返回 'HH:MM:SS'
select sec_to_time('3600'); -- 01：00：00.000
-- 使用date_add()和adddate()函数执行日期加操作 mysql>
select date_add('2010-12-31 23:59:59', interval 1 second) as col1,
adddate('2010-12-31 23:59:59', interval 1 second) as col2,
date_add('2010-12-31 23:59:59', interval '1:1' minute_second) as col3;
-- 使用date_sub()和subdate()函数执行日期减操作 mysql>
select date_sub('2011-01-02', interval 31 day) as col1,
subdate('2011-01-02', interval 31 day) as col2,
date_sub('2011-01-01 00:01:00', interval '0 0:1:1' day_second) as col3;
-- 使用addtime(date,expr)函数进行时间加操作 mysql>
select addtime('2000-12-31 23:59:59','1:1:1'),
addtime('02:02:02','02:00:00');
-- 使用subtime(date,expr)函数执行时间减操作 mysql>
select subtime('2000-12-31 23:59:59','1:1:1'),
subtime('02:02:02','02:00:00');
-- 使用datediff(date1,date2)计算两个日期之间的间隔天数 mysql>
select datediff('2010-12-31 23:59:59','2010-12-30') as col1,
datediff('2010-11-30 23:59:59','2010-12-31') as col2;
-- 使用date_format() 格式化输出日期与时间 --参照pdfMysql数据库函数文件
select date_format('1997-10-04 22:23:00','%W %M %Y')as col1;-- 'Saturday October 1997'
SELECT DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') as col1;-- '4th 97 Sat 04 10 Oct 277'
-- 使用 time_format(time,format) 根据 fromat字符串安排time值的格式
select time_format('16:00:00','%H %k %h %I %l'); -- -'16 16 04 04 4'
-- 流程函数
-- if(value,t f) 如果value是真，返回t 否 返回 f 和 1>2?true :fasle 一样
select if(4>2,'大','小'); -- 大
-- ifnull(value1,value2) 如果是value1为空 返回 value2
-- 如果不为空 则就是value
select ifnull('abc','sd');-- abc
select ifnull(null,'不详细'); -- 不详细
-- case when[value1] then[result1] else[default] end
-- 如果value1为真 返回result 否则 返回 default 相当于 if else
select case
when 2>1 then '大'
else '小'
end as '2>1比较值'; -- 大
-- case[expr] when[value1] then[result] else[default] end 相当于 java switch case
select case 2
when 2 then '在这呢，没错'
else '这儿没有'
end '找2'; -- 在这呢，没错
数据库_函数（date,数学，等）流程
最新推荐文章于 2023-09-25 11:53:47 发布
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
