day41 mycql 函数
一些经典的练习题,以及函数的简单用法,內建函数
-- 函数 python函数 def fun1(a1,a2,a3): sum = a1+a2+a3 return sum fun1(1,2,3) java中函数写法 public String fun2(int i1，String s2){ String sum = i1+s2; return sum; } A a = new A(); a.fun2(1,"assda"); javascript function fun3(a1,a2){ var sum = a1+a2; return sum; } mysql 自定义函数 create FUNCTION fun1(i int,x int) returns int -- 声明返回的类型 BEGIN DECLARE sum int DEFAULT 0; SET sum = i+x; return(sum);-- 提供返回结果 end 调用函数 select 函数名称(参数值) 在select使用函数 select ren.*,fun1(ren.p_sal,100) from ren 博客表 id name time 1 ADAS 2017-09-09 2 sa第三 2017-10-09 3 sadd 2017-10-19 select DATE_FORMAT(now(),'%Y年%m月') select CHAR_LENGTH("中午"); select length("中午"); select CONCAT("alex","asdsad","在法国风格",null) select CURDATE(); select CURTIME(); select now(); SQL练习： -- 1、查询课程编号“001”比课程编号“002” 成绩高的所有学生的学号； select s_score from score where c_id ='1'; select s_score from score where c_id ='2'; select A.s_id from (select s_score,s_id from score where c_id ='1') as A, (select s_score,s_id from score where c_id ='2') B where A.s_id = B.s_id and A.s_score > B.s_score; -- 2、查询平均成绩大于60分的同学的学号和平均成绩; select avg(s_score),s_id from score where s_id = 1 GROUP BY s_id having avg(s_score)>60 -- 3、查询所有同学的学号、姓名、选课数、总成绩； select student.s_id,student.s_name,count(1) as'选课数',sum(s_score) from score,student where score.s_id = student.s_id GROUP BY s_id -- 4查询含有"子"的老师的个数； select count(t_id) from teacher where t_name like '%子%' -- 5、查询没学过“老子”老师课的同学的学号、姓名； select c.c_id from teacher t,course c where t.t_id = c.t_id and t.t_name ='老子'; select s_id from score s where s.c_id in (select c.c_id from teacher t,course c where t.t_id = c.t_id and t.t_name ='老子'); select DISTINCT student.s_id,student.s_name from score,student where score.s_id not in (select s_id from score s where s.c_id in (select c.c_id from teacher t,course c where t.t_id = c.t_id and t.t_name ='老子') ) and student.s_id = score.s_id -- 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select st.s_id,st.s_name from score s inner join student st on s.s_id = st.s_id where s.c_id = '1' or s.c_id ='2' GROUP BY s.s_id HAVING count(s.s_id) = 2; select st.s_id,st.s_name from (select * from score s where s.c_id = '1' union all select * from score s where s.c_id = '2' ) as lin inner join student st on lin.s_id = st.s_id GROUP BY lin.s_id HAVING count(lin.s_id) = 2 注意： union ：表示去重复合并表 union ALL ：表示不去重复合并表 -- 7、查询学过“老子”老师所教的所有课的同学的学号、姓名； #1.先查询"老子"老师教哪些课程 #2.再查询哪些学生学习了这些课程 #3.再根据学生编号分组,如果分组后的个数 ="老子"老师所教授课程的个数, # 则表示学过该老师所有课程. select c_id from course c ,teacher t where c.t_id = t.t_id and t.t_name='张雪峰'; select st.s_id,st.s_name from score,student st where score.s_id = st.s_id and c_id in( select c_id from course c ,teacher t where c.t_id = t.t_id and t.t_name='张雪峰') GROUP BY s_id having count(sc_id) = (select count(1) from course c ,teacher t where c.t_id = t.t_id and t.t_name='张雪峰') -- 8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名； select a.s_id from (select * from score where c_id ='1') a, (select * from score where c_id ='2') b where a.s_id = b.s_id and a.s_score < b.s_score; -- 9、查询有课程成绩小于60分的同学的学号、姓名； select DISTINCT st.s_id,st.s_name from student st,score sc where st.s_id = sc.s_id and sc.s_score < 60 -- 10、查询没有学全所有课的同学； select count(1) from course c select * from student st where s_id in ( select s_id from score sc GROUP BY sc.s_id HAVING count(1) <> (select count(1) from course c) ) -- 11、查询至少有一门课与学号为“002”的同学所学相同的同学的学号和姓名； select sc.c_id from score sc,student st where sc.s_id =st.s_id and sc.s_id ='2' select DISTINCT st.s_id,st.s_name from score sc,student st where st.s_id =sc.s_id and sc.c_id in(select sc.c_id from score sc where sc.s_id ='2') -- 12、查询学过 学号为“002”同学全部课程 的其他同学的学号和姓名； select sc.c_id from score sc where sc.s_id = '2' select st.s_id,st.s_name from score sc,student st where sc.s_id =st.s_id and st.s_id !='2' and sc.c_id in (select sc.c_id from score sc where sc.s_id = '2') GROUP BY sc.s_id HAVING count(1) = (select count(1) from score sc where sc.s_id = '2') -- 14、把“score”表中“老子”老师教的课的成绩都更改为此课程的平均成绩； UPDATE score set s_score = (select * from (select avg(sc.s_score) from teacher t,course c,score sc where c.t_id = t.t_id and sc.c_id = c.c_id and t.t_name='张雪峰' ) ss) where score.sc_id in( select * from (select sc_id from teacher t,course c,score sc where c.t_id = t.t_id and sc.c_id = c.c_id and t.t_name='张雪峰') aaa )
concat函数就类似于字符串里面的拼接功能,把一些字段进行修改,对应的字段里面的值也会有变化,
例如:select concat('字符串',字段名,' ','字符串','字段名')from 表格名=======>
select concat('书名:','name',' ','作者:','author')from bk_l; ====>这里加上空格或者是其他自定义符号皆可,主要起到一个分隔符的作用
还可以写成这样,每个字段跟每个字段都分别用括号括起来,更便于区分开彼此,
select concat('书名:','name'),concat('作者:','author')from bk_l; ======这样就更便于区分每个字段
这里是mycql里面的内建函数里面时间的一些符号的使用方法
https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html
函数的一些需要掌握的知识点(较常用的):
-- create function f(w int,x int ) -- returns int -- begin -- declare sum int default 0; -- set sum=w+x; -- return(sum); -- end -- -- select person.*,f(person.p_sal,200)from person -- select DATE_FORMAT(now(),'%Y%M%W') -- select CHAR_LENGTH('午夜') -- select LENGTH('午夜') -- select CONCAT('alex','sowhat','午夜',NULL) 如果这里有null,就会直接显示空 -- select date_format ('2001-10-08 20:00:30','%W %M %Y'); -- select DATE_FORMAT('2007-10-08 20:00:00','%H:%i:%s') -- h=hour,i=minute,s=SECOND -- select date_format('2007-10-08 20:00:00','%a %b %j')-- a是首字母大写的星期,b是首字母大写的月份 j是day of year,一年中的第几天 -- select DATE_FORMAT('1997-10-04 22:23:05','%H %k %I %r %T %S %w %s') -- H是小时,显示的小时;K也是小时,是一天中第几个小时(23小时制);I是一天中的第几个小时(12小时制);r是12小时制,显示当前时间的时分秒,有后缀AM或者PM;T是24小时制显示时分秒;S是秒,第几秒; -- w是一个星期中的第几天,mon是第一天,Saturday是第六天 -- select date_format('1999-01-01','%X %V') -- 哪一年中的第几个礼拜 这一天是第1998年的第52个礼拜 -- select date_format('2006-06-10','%d') -- d是一个月中的第几天
时间和日期函数:
curdate() 或者current_date() 返回当前的日期 curtime() 或者current_time()返回当前的时间 date_add(date,interval in keyword) 返回日期date加上间隔时间int的结果,(int必须按照关键字进行格式化)如,selectdate_add(current_date,interval 6 month); date_format(date,fmt)依照指定的fmt格式格式化日期date值 date_sub(date,interval in keyword)返回日期date加上间隔时间int的结果,(int必须按照关键字进行格式化) dayofweek(date) 返回date所代表的一星期中第几天(1~7) dayofmonth(date)返回date是一个月的第几天(1~31) dayofyear(date) 返回date是一年中的第几天(1~366) dayname(date) 返回date的星期名 如:select dayname(current_date); form_unixtime(ts,fmt) 根据指定的fmt格式,格式化unix时间戳ts hour(time) 返回time的小时值(0~23) minute(time) 返回time的分钟值(0~59) month(date) 返回date的月份值(1~12) monthname(date) 返回date的月份名, 如:select monthname(current_date); now() 返回当前的日期和时间 quarter(date) 返回date在一年中的季度(1~4), 如select quarter(current_date); week(date) 返回日期date为一年中第几周(0~53) year(date) 返回日期date的年份(1000~9999)
date_format(date,fmt) 依照字符串fmt格式化日期date值
format(x,y) 把x格式化以逗号隔开的数字序列化,y是结果的小数位数
inet_aton(ip) 返回IP地址的数字表示
inet_ntoa(num) 返回数字所代表的ip地址
time_format(time,fmt) 依照字符串fmt格式化时间time值
其中最简单的是format()函数,他可以把大的数值格式化为以逗号间隔易读的序列
select format(2332.3332222,4); select date_format(now(),'%w, %d %M %Y %r'); select date_format(now(),'%Y-%m-%d'); select date_format(18890923,'%Y-%m-%d'); select date_format(now(), '%h:%i %p'); select inet_aton('10.211.36.75.); select inet_ntoa(139867898);
