数据库_函数（date,数学，等）流程

/*
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'; -- 在这呢，没错