MySQL：《学习笔记与实战》之查询实例（3）更改月份

1.更新表名时的简写

UPDATE store_info_final_new  a, traffic_grade_final b,SET a.hospital_score = b.grade
WHERE a.id=b.id;

2.连接表要比update快

 select a.*,b.id from cudata_yuan a  left join  b where a.id =b.id

3.查询结果作为子表 再进行查询,查询每个月公司有多少人

SELECT mon,count(new_name) from 
(SELECT month as mon ,name as new_name from year_month_report_only_user 
WHERE type_name=3 GROUP BY month,name ORDER BY month)A 
GROUP BY mon ORDER BY mon;

#统计去重的每月人数
SELECT mon,count(new_name) from 
(SELECT month as mon ,name as new_name from year_month_my_user_copy 
WHERE type_name=3 GROUP BY month,name ORDER BY month)A 
GROUP BY mon ORDER BY mon;

#更改月份 2 变成02
update year_month_my_user_copy  
set month = (  
case   
  when (LENGTH(month))<2 then concat('0',month)
  else month
end);

4.数据类型转换函数

    ①：CAST(value as type)   Cast(字段名 as 转换的类型 )

          date：2015-11-03 15:31:26 select cast(date as signed) as date from table1;

            结果如下： date：20151103153126

②：CONVERT(value, type) 

例一

mysql> SELECT CONVERT('23',SIGNED);
+----------------------+
| CONVERT('23',SIGNED) |
+----------------------+
|                   23 |
+----------------------+
1 row in set
例二


mysql> SELECT CAST('125e342.83' AS signed);
+------------------------------+
| CAST('125e342.83' AS signed) |
+------------------------------+
|                          125 |
+------------------------------+
1 row in set

例三


mysql> SELECT CAST('3.35' AS signed);
+------------------------+
| CAST('3.35' AS signed) |
+------------------------+
|                      3 |
+------------------------+
1 row in set

5.多增加一列month 作为去重用

    从DealDate截取月 用作去重用；同一字段去中间条件部门多用between

    SELECT * ,substring(DealDate, 6, 2)as month FROM old 
    WHERE (DealDate BETWEEN '2018-02-26' and '2018-06-17') and DealStatus='D'
    GROUP BY MemberName,month,phone;

6.删除表用

    DELETE语句和TRUNCATE TABLE语句功能相似，但是二者究竟有何区别呢？
    https://www.cnblogs.com/lmaster/p/6373148.html

7.利用元组查询

     select name ,count(name) where name in ('张三','李四')