MySql 高级函数用法

排序

1. rank() over
   作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
   说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7

2. dense_rank() over
   作用：查出指定条件后的进行排名，条件相同排名相同，排名连续。
   说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

3. row_number() over
   作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。
   说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6

select score, 
    dense_rank() over (order by score desc) as 'rank' 
from scores;

前后函数

1. LAG(expr,n)
   返回当前行的前n行的expr的值
2. LEAD(expr,n)
   返回当前行的后n行的expr的值

# 查找所有至少连续出现三次的数字。
select distinct num ConsecutiveNums 
from (
    select num, 
        lag(num, 1, null) over (order by id) lag_num, 
        lead(num, 1, null) over (order by id) lead_num
    from logs
    ) l
where 
    l.Num = l.lag_num and l.Num = l.lead_num;

锁表

LOCK TABLES为当前线程锁定表。 UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表会自动被解锁。

1. 如果一个线程获得在一个表上的一个READ锁，该线程和所有其他线程只能从表中读。

2. 如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。

   /* 锁表 */
   lock tables `tb_order` write;
   	
   insert into `tb_order` values 
       (1,'d001','customera','2018-01-01','storea','proda',1);
   
   /* 解锁 */
   unlock tables;
   

约束

1. alter table 表名
   add constraint
   约束名称 约束类型 (列名)
   references 被引用的表名称 (列名)

2. 外键其实就是引用, 因为主键实现了实体的完整性，外键实现了引用的完整性，应用完整性规定，所引用的数据必须存在！
   其实就是个引用，比方说一个表名称叫dept 里面有2列数据 一列是ID一列是ENAME

   id：表示产品的编号
   ename：表示产品的名称

3. 另外一个表格名称是emp 里面有2列数据，一列是ID 一列是DID

   id:表示用户号
   did：表示购买的产品号

4. 要让emp表中的did列去引用dept表中的id, 可以用下面的方法

   alter table emp 
   add constraint 
   	jfkdsj foreign key (did) 
   references dept (id)
   

bigint (unsigned)

1. bigint
   默认是有符号，即取值范围是正负范围
   比如：bigint(20)，就是-1234567890123456789~+1234567890123456789

2. bigint unsigned
   无符号，即取值范围就是正值范围
   比如：bigint(20)，就是+12345678901234567890

   REATE TABLE tb_sales(
   	sales_id bigint unsigned auto_increment COMMENT '编号',
   	...
   

if判断与case判断

1. case 字段 when 条件 then 结果1 else 结果2 end

   select
   stu_name as 姓名,
   case stu_sex
      when 1 then '男'
      else '女'
   end as 性别
   from tb_student
   where stu_name = '杨过';
   

2. IF(condition, value_if_true, value_if_false)

   select
   stu_name as 姓名,
   if(stu_sex, '男', '女') as 性别
   from tb_student
   where stu_name = '杨过';
   

空字符（trim）

1. 删除指定字符串中的空格 trim(str)
2. 删除指定的首字符 trim(leading ’ ’ from str)
3. 删除指定的首尾字符 trim(both ’ ’ from str)
4. 删除指定的尾字符 trim(trailing ’ ’ from str)

时间日期

1. now() - 获取当前数据库服务器的时间

2. curdate() - current date - 获取当前日期

3. curtime() - current time - 获取当前时间

4. datediff(dt1, dt2)

5. floor() - 向下取整

   /* 计算年龄 */
   select floor(datediff(curdate(), '1995-06-13') / 365);
   

6. ceiling() / ceil() - 向上取整

7. round() - 四舍五入

8. decimal - 小数
   decimal(10,2)中的“2”表示小数部分的位数,如果插入的值未指定小数部分或者小数部分不足两位则会自动补到2位小数,若插入的值小数部分超过了2为则会发生截断,截取前2位小数。 “10”指的是整数部分加小数部分的总长度,也即插入的数字整数部分不能超过8(10-2)位,否则不能成功插入,会报超出范围的错误。

9. 其他函数

   函数	用法
   coalesce(x, y, z)	返回参数中第一个非空值
   ifnull(x, 0)	如果x为null， 则返回0
   stddev_pop / stddev_samp	方差
   var_pop / var_samp	标准差

连接

1. 内连接/自然连接：只会将两张表满足连表条件的记录获取到
   2.左外连接：获取左表（写在join前面的表）所有的数据，不满足连表条件的地方填充null
   – left outer join / left join
2. 右外连接：获取右表（写在join后面的表）所有的数据，不满足连表条件的地方填充null
   – right outer join / right join
3. 全外连接：获取左表和右表所有的数据，不满足连表条件的地方填充null（MySQL不支持全外连接）
   – full outer join / full join
4. 在MySQL中如果要实现全外连接的效果，可以用左外连接和右外连接求并集（union）来实现

不去重

1. union 会去重
2. union all 不会去重

   select 
       device_id, gender, age, gpa
   from user_profile
   where university='山东大学'
   union all
   select 
       device_id, gender, age, gpa
   from user_profile
   where gender='male';
   

计算留存率或连续出现的情况

1. 题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

2. DATE_ADD() 函数向日期添加指定的时间间隔。
   语法:DATE_ADD(date,INTERVAL expr type)

   OrderId	ProductName	OrderDate
   1	Jarlsberg Cheese	2008-11-11 13:23:44.657

   SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
   FROM Orders
   

   OrderId	OrderDate
   1	2008-12-26 13:23:44.657

3. DATE_SUB() 函数从日期减去指定的时间间隔。

   date_sub(date,interval 1 day)
   

4. DATEDIFF() 函数返回两个日期之间的天数。

   SELECT DATEDIFF('2008-12-30','2008-12-29')
   

5. 题解：

   select
       count(date2) / count(date1) as avg_ret
   from
       (select
           distinct qpd.device_id,
           qpd.date as date1,
           uniq_id_date.date as date2
       from
           question_practice_detail as qpd
       left join
           (select
               distinct device_id,
               date
           from
               question_practice_detail
           ) as uniq_id_date 
       on qpd.device_id = uniq_id_date.device_id
       and date_add(qpd.date, interval 1 day) = uniq_id_date.date
       ) as id_last_next_date;
   

   select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
   from
       (select
           distinct device_id,
           date as date1,
           lead(date) over (partition by device_id order by date) as date2
       from 
           (select distinct device_id, date
           from question_practice_detail
           ) as uniq_id_date
       ) as id_last_next_date;
   

字符串截取之substring_index

1. substring_index(str,delim,count)
   str：要处理的字符串
   delim：分隔符
   count：计数
2. 例子：str = www.wijibt.com
   substring_index(str,‘.’,1)
   结果是：www
   substring_index(str,‘.’,2)
   结果是：www.wikibt
   也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容。相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：
   substring_index(str,‘.’,-2)
   结果是：wikibt.com
3. 有人会问，如果我要中间的wikibt怎么办？
   很简单的，两个方向：
   从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：
   substring_index(substring_index(str,‘.’,-2),‘.’,1);
4. 其他函数：

   字符串的截取：substring(字符串，起始位置，截取字符数）
   字符串的拼接：concat(字符串1，字符串2，字符串3,…)