sql day4
for example, table day4
mysql> select sum(singles) total_singles, sum(doubles) total_doubles, sum(triples) total_triples, sum(hr) total_hr from day4 where hits/ab>=.300;(重命名的时候,名字必须是一个词中间有连接符的那种)
数学函数::
1. count(算总和数)
select count(*)from day4 where hits/ab<.35; (返回总数的行)
2. sum(计算一列的总数)
mysql> select sum(singles) total_singles from day4;
mysql> select sum(singles) total_singles, sum(doubles) total_doubles, sum(triples) total_triples, sum(hr) total_hr from day4 where hits/ab>=.300;(有限定条件的显示一列总和)
3. avg(average)
mysql> select avg(so) ave_strike_outs from day4;
4.max
mysql> select name from day4 where hits=max(hits); (max函数不能在where语句中使用)
mysql> select max(name) from day4; (也可以max字符串,选择最长的那个字符串)
5.min
mysql> select min(ab), max(ab) from day4;
6.variance(方差)
mysql> select variance(hits) from day4;
7.stddev(标准差)
日期/时间函数::
insert into day41(task,startdate,enddate)values('KICKOFFMRG',current_date(),current_date()); (显示现在的日期)
insert into day41(task,startdate,enddate)values('DESIGNWIDGET','95-5-15','95-5-30');
1. add_months
mysql> select task task_shorter_than_one_month from day41 where add_months(startdate, 1)>enddate; (报错,说这个function不存在。。)
2. last_day
mysql> select enddate, last_day(enddate) from day41; (显示当月的最后一天)
mysql> select last_day('95-2-1') non_leap, last_day('96-2-1') leap;
mysql> select distinct last_day('95-2-1') non_leap, last_day('96-2-1') leap from day41; (输出闰年)
3. months_between
mysql> select task, startdate, enddate, months between(startdate, enddate) duration from day41; (这个语句好像也不行呢。。)
同样的,new_time这个function也不存在
next_day也不存在
数学函数::
1. abs(绝对值)
mysql> select abs(A) absolute_value from day42;
2. ceil(返回给定参数大or相等的最小整数) floor(返回给定参数小or相等的最大整数)
mysql> select B, ceil(B) ceiling from day42;
mysql> select A, floor(A) floor from day42;
3. cos, cosh, sin, sinh, tan, tanh
mysql> select A, cos(A) from day42;
4.exp (参数为指数,e为底数的幂值)
mysql> select A, exp(A) from day42;
5. ln返回给定参数的自然对数
mysql> select A,ln(abs(A)) from day42;
6. log
mysql> select B, log(B,10) from day42;
7. mod 返回相除后的余数
mysql> select A, B, mod(A,B) from day42;
8. power 返回某一个数对另一个数的幂
9. sign 参数的值为负数,返回-1; 参数值为正数,返回+1; 参数为0,返回0
mysql> select A, sign(A) from day42;
mysql> select A from day42 where sign(A)=1;
10. sqrt 返回参数的平方根(不能为负数)
mysql> select A, sqrt(A) from day42;
字符函数::
1. initcap(将参数的第一个字母变成大写)
sql>select firstname before, initcap(firstname) after from characters;
2.lower(全变成小写字母) upper(全变成大写字母)
sql>select firstname, upper(firstname), lower(firstname) from characters;
3.lpad rpad(第一个参数是需要处理的字符串, 第二个参数是需要将字符串扩充的宽度, 第三个参数是加宽部分用什么字符做填补)
lpad(在前面加字符串) rpad(在后面加字符串)
sql>select lastname, lpad(lastname, 20, '*') from characters;
4. replace 替换
(第一个参数:需要搜索的字符串,第二个参数:搜索的内容, 第三个参数:需要替换的字符串,如果省略或者null,则只执行搜索操作)
sql>select lastname, replace (lastname, 'st') replacement from characters;
sql>select lastname, replace (lastname, 'st', '**') replacement from characters;
5. substr 将目标字符串的一部分输出
(第一个参数:目标字符串, 第二个参数:输出子串的起点,第三个参数:输出子串的长度)
sql>select firstname, substr (firstname, 2, 3) from characters;
sql>select firstname, substr (firstname, -13, 2) from characters; (从尾部定位往前)
sql>select substr(ssn, 1, 3) ||'-'||substr(ssn, 4, 2) ||'-'||substr(ssn, 6, 4) ssn from ssn_table
(于是中间就加了分割号) 从300541117变成300-54-1117
6. translate(在目标字符串和源字符串都出现的字符将会被替换)
sql>select firstname, translate(firstname, '123456789abcdefghijklmn'AAAAAAAAAAAAAA) from characters;
7. instr 确定字符串中满足特定内容的位置
(第一个参数:目标字符串,第二个参数:匹配的内容,第三个参数:指定开始搜索的起点, 第四个参数:第几个满足条件将返回)
sql>select lastname, instr(lastname, 'o', 2, 1) from characters;
8. length 返回长度
sql>select firstname, length (rtrim(firstname)) from characters;
转换函数::(convert)
1. to_char(数字转换为字符型)
sql>select testnum, to_char(testnum) from convert;
sql>select testnum, length(to_char(testnum))from convert; (可以看到长度就很小)
2. to_number(字符型转换为数字)
sql>select name, testnum, testnum*to_number(name) from convert;
其他函数::
1. greatest(返回最大)
2. least(返回最小)
3. user(返回用户的名字)