pom+ssm+mysql

目录

1.类型转化

2.时间问题

3.数据库设计

3.1查本月的日子遍历

3.2每天借阅书籍的数量

 3.3每个读者借阅书籍的数量

---

1.类型转化

转化

Long转int

 int i = Long.valueOf(stockCount).intValue();

Integer转String

   Integer i1 = i - productNum1;
                        String s = Integer.toString(i1);

Integer转long

long l = i1.longValue();

double转int

         int intValue = (int) Math.round(doubleValue); 

String转double

Double.parseDouble(d)

         int intValue = (int) Math.round(doubleValue); 

2.时间问题

  获取当天日期

Calendar instance = Calendar.getInstance();
DateFormat DateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String format = DateFormat.format(instance.getTime());
Date date = DateFormat.parse(format);

2.2jsp页面格式化时间 fmt:formatDate格式化日期

<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>

       <c:forEach items="${perdaysum}" var="perdaysum">
                    '<fmt:formatDate value="${perdaysum.saleOrderDate}" pattern="yyyy-MM-dd"/>',
                    </c:forEach>

3.数据库设计

3.1查本月的日子遍历

SELECT DATE FROM (

SELECT DATE_FORMAT(DATE_SUB(LAST_DAY(CURDATE()), INTERVAL xc-1 DAY), '%Y-%m-%d') AS DATE

FROM ( 

SELECT @xi:=@xi+1 AS xc FROM 

(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, 

(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,  

(SELECT @xi:=0) xc0 

) xcxc) x0 WHERE x0.date >= (SELECT DATE_ADD(CURDATE(),INTERVAL-DAY(CURDATE())+1 DAY)); 

3.2每天借阅书籍的数量

 SELECT bi.name,DATE_FORMAT(ll.lendDate, '%Y-%m-%d') AS lendDate,COUNT(*) jcount  FROM lend_list ll
      LEFT JOIN book_info bi ON ll.bookId=bi.id
      WHERE DATE_FORMAT(ll.lendDate, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' )
      GROUP BY DATE_FORMAT(ll.lendDate, '%Y-%m-%d')      
      ORDER BY ll.lendDate 
        

 3.3每个读者借阅书籍的数量

      /*
        * 每个读者借阅书籍的数量
        * */   
 SELECT COUNT(ri.id) AS readerNumber , ri.realName FROM    reader_info ri 
 LEFT JOIN lend_list ll ON ri.id=ll.readerId
 GROUP BY ri.id
 

3.4获取本月的销售额

 -- 获取本月的销售额    
    
   SELECT id,sale_order_pay,SUM(sale_order_total) AS sale_order_total ,DATE_FORMAT(sale_order_date, '%Y-%m-%d') AS sale_order_date FROM c_sale_order
    WHERE DATE_FORMAT(sale_order_date, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' )
          GROUP BY DATE_FORMAT(sale_order_date, '%Y-%m-%d')
    ORDER BY sale_order_date

3.5获取本年的月销售额

    
   SELECT id,sale_order_pay,SUM(sale_order_total) AS sale_order_total ,DATE_FORMAT(sale_order_date, '%Y-%m-%d') AS sale_order_date FROM c_sale_order
    WHERE DATE_FORMAT(sale_order_date, '%Y' ) = DATE_FORMAT( CURDATE( ) , '%Y' )
          GROUP BY DATE_FORMAT(sale_order_date, '%Y-%m')
    ORDER BY sale_order_date
    

 

通过分组 求某个字段的总和

 SELECT s.id, s.stock_area, s.goods_id,s.flag,s.rukutime,SUM(s.stock_count) AS stock_count FROM `c_stock` s
        LEFT JOIN  `c_shopgoods` gs ON s.goods_id=gs.id
        WHERE  s.stock_count <= 500 AND s.goods_id IS NOT NULL
        GROUP BY s.goods_id 

获取累计后在判断

        

SELECT * FROM (
                SELECT s.id, s.stock_area, s.goods_id,s.flag,s.rukutime,SUM(s.stock_count) AS stock_count FROM `c_stock` s
        LEFT JOIN  `c_shopgoods` gs ON s.goods_id=gs.id
        WHERE  s.stock_count <= 500 AND s.goods_id IS NOT NULL
        GROUP BY s.goods_id ) a  WHERE  a.stock_count <= 500

3.7 两个字段进行分组

SELECT *,DATE_FORMAT(dp_date, '%Y-%c-%e') AS dp_date,COUNT(*) AS psum FROM dpiao dp
LEFT JOIN `piao`  ts ON ts.Id=dp.pid
LEFT JOIN `traininfo` ti ON ti.Id=ts.tid
WHERE DATE_FORMAT(dp.dp_date, '%Y-%m' ) = DATE_FORMAT( CURDATE( ) , '%Y-%m' )
 GROUP BY DATE_FORMAT(dp.dp_date, '%Y-%m-%d'),ti.traintype

4.mysql错误总结

1.The server time zone value '?й???????' is unrecognized or

方案1、在项目代码-数据库连接URL后，加上 ?serverTimezone=UTC（注意大小写必须一致）

方案2、在mysql中设置时区，默认为SYSTEM

mysql> set global time_zone='+8:00';

2.分组查询调用count函数出错

错误：：1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregat

如下两个命令，去掉sql_mode 的 ONLY_FULL_GROUP_BY

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

4.mysql授权问题

GRANT ALL PRIVILEGES ON *.* TO "user"@"192.168." IDENTIFIED BY '2020' WITH GRANT OPTION; 
GRANT ALL PRIVILEGES ON *.* TO "root"@"%" IDENTIFIED BY '202' WITH GRANT OPTION; 


GRANT ALL PRIVILEGES ON *.* TO "foodemarket"@"%" IDENTIFIED BY '123456' WITH GRANT OPTION; 
REVOKE ALL PRIVILEGES ON *.* FROM 'foodemarket'@'%'

GRANT ALL PRIVILEGES ON db_rlzy.* TO "db_rlzy"@"%" IDENTIFIED  BY  '123456' WITH GRANT OPTION; 
REVOKE ALL PRIVILEGES ON *.* FROM 'foodemarket'@'%'
FLUSH PRIVILEGES;

CREATE USER 'xiaoxiao'@'%' IDENTIFIED BY  '202'
GRANT ALL PRIVILEGES ON *.* TO "xiaoxiao"@"%" IDENTIFIED  BY  '202.' WITH GRANT OPTION; 

REVOKE ALL PRIVILEGES ON *.* FROM 'xiaoxiao'@'%'
FLUSH PRIVILEGES;


SHOW MASTER STATUS;
FLUSH LOGS

mysqlbinlog --start-POSITION 107 C:\BtSoft\mysql\MySQL5.5\DATA mysql-bin.000005 -d t -v -r C:\BtSoft\mysql\MySQL5.5\DATA mysql-bin.000005.sql
`market`
`srykzsblmis_db`
//取消用户所有权限
REVOKE ALL ON *.* FROM "user"@"%";
REVOKE ALL PRIVILEGES ON *.* FROM 'xiaoxiao'@'%'

--授权  把某个数据库所有权限授予
GRANT ALL PRIVILEGES ON db_9_jd.* TO 'nsg'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON xu.* TO "xu"@"%" IDENTIFIED BY '123456' WITH GRANT OPTION; 


GRANT ALL PRIVILEGES ON `xutao`.* TO 'xutao'@'%' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON xu.* TO "xu"@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;