目录
1.MySQL体系结构与存储引擎
MySQL插件式存储引擎，只要写好相应接口，任何引擎都可以访问MySQL，这也是 MySQL 流行的原因之一。
Mysq数据库的体系结构如下：
MySQL 5.7 新特性：
- 将 Undo 从共享表空间 ibdata 文件中分离出来，安装可自定义文件大小和数量。
- 增加 temporary 临时表空间，里面存储着临时表或临时查询结果集的数据。
- ***Buffer Pool 大小可以动态修改，无需重启数据库实例。
MySQL 8.0 新特性：
- 将 InnoDB 表的数据字典和 Undo 都从共享表空间 ibdata 中彻底分离出来。
- temporary 临时表空间也配置多个nnoDB 存储引擎并能创建索引的物理文件，优化速度。
- 用户可像 Oracle 一样设置一些表空间给多个表使用，但一个表只能存储在一个表空间中。
存储引擎方面，在5.6版本后默认由 MyISAM变为 InnoDB。InnoDb支持事务和外键、性能也相对较高。
MySQL事务与锁机制
- 原子性：事务的所有操作，要么全部完成，要么全部不完成，不会结束在某个中间环节。
- 一致性：事务开始之前和事务结束之后，数据库的完整性限制未被破坏。
- 隔离性：当多个事务并发访问数据库中的同一数据时，所表现出来的相互关系。使用锁。
- 持久性：事务完成之后，事务所做的修改进行持久化保存，不会丢失。
并发事务控制
单版本控制-锁：用独占的方式来保证在只有一个版本的情况下事务之间相互隔离，所以锁可以理解为单版本控制。
在 MySQL 事务中，锁的实现与隔离级别有关系，在 RR（Repeatable Read）隔离级别下，MySQL 为了解决幻读的问题，以牺牲并行度为代价，通过 Gap 锁来防止数据的写入，而这种锁，因为其并行度不够，冲突很多，经常会引起死锁。现在流行的 Row 模式可以避免很多冲突甚至死锁问题，所以推荐默认使用 Row + RC（Read Committed）模式的隔离级别，可以很大程度上提高数据库的读写并行度。
多版本控制-MVCC：为了实现高并发访问，进行多版本处理，并通过事务的可见性来保证合适版本展示。
每一次对数据库操作，都会在 Undo 日志中记录当前修改记录的事务号及修改前数据状态的存储地址（即 ROLL_PTR），以便回滚。例如，一个读事务查询到当前记录，而最新的事务还未提交，根据原子性，读事务看不到最新数据，但可以去回滚段中找到老版本的数据，这样就生成了多个版本。MVCC巧妙地将稀缺资源的独占互斥转换为并发，优化数据库吞吐量及读写性能。
在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读 （Current Read）。
快照读：读取的是记录的可见版本（有可能是历史版本），简单的 select 操作,不用加锁。
当前读：读取的是记录的最新版本，并且当前读返回的记录，插入/更新/删除操作,都会加锁。
注意：MVCC 只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。
2.数据库兼容性问题以及设置访问权限
大概是每个版本会有些兼容性问题。一方升级了高版本可能是为了得瑟，变厉害了以后呢就不能和以前的老版本朋友一起玩耍了^_^。二是版本升级肯定会有功能的变化，会产生兼容性问题，貌似这才是重点。
登录mysql并使用
use mysql；
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
命令为mysql添加远程连接权限/账号
使用 flush privileges; 命令刷新权限
-
mysql8版本：com.mysql.cj.jdbc.Driver，MySQL5版本：com.mysql.jdbc.Driver。
-
jdbc:mysql:///你的数据库?useSSL=false&serverTimezone=UTC，不要忘了设置时区啥的，8.x版增加了几个参数。
-
SSH中，<hibernate.dialect>org.hibernate.dialect.MySQL8Dialect</property>方言MySQL8或5Dialect，关系不大。
3.serverTimezone useSSL 时差8小时
驱动包用的是mysql-connector-java-8.0.11.jar
新版的驱动类改成了com.mysql.cj.jdbc.Driver
新版驱动连接url也有所改动
I、指定时区
//北京时间东八区
serverTimezone=GMT%2B8 这个时区要设置好，不然会出现时差，
如果你设置serverTimezone=UTC，连接不报错，
但是我们在用java代码插入到数据库时间的时候却出现了问题。
比如在java代码里面插入的时间为：2018-06-24 17:29:56
但是在数据库里面显示的时间却为：2018-06-24 09:29:56
有了8个小时的时差
UTC代表的是全球标准时间 ，但是我们使用的时间是北京时区也就是东八区，领先UTC八个小时。
//北京时间东八区
serverTimezone=GMT%2B8
//或者使用上海时间
serverTimezone=Asia/Shanghai为何没有asia/beijing时区？
II、指定是否用ssl连接，true值还报错了
useSSL=false完整代码：
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/mydb3?serverTimezone=GMT%2B8&useSSL=false";
String user="root";
String password="123";
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);1、导包mysql-connector-java-8.0.11.jar
2、加载驱动类：Class.forName("com.mysql.cj.jdbc.Driver");
3、定义url,user,password
4、通过DriverManager.getConnection建立连接Connection connection = DriverManager.getConnection(url, user, password);
4.同时配置MySQL5和MySQL8在同一主机
1.官网下载不同版本的mysql包（5.7和8.0）下载后解压到相关文件夹，如图：
2，环境变量问题 可以都配置也可以不配置，不配的话就得到不同mysql的bin下执行mysql -u -p 等命令；在此不进行配置；
3，两个安装目录下配置各自的my.ini文件，注意：两个mysql端口要不一样最好我这里一个3306 一个3307.还有目录不同。其他都是相同的 ，就贴一个吧。
[mysqld]
# 设置3307端口
port=3307
# 设置mysql的安装目录
basedir=你的mysql安装目录 # 切记此处一定要用双斜杠\\，单斜杠也行。
# 设置mysql数据库的数据的存放目录
datadir=安装目录\\Data # 此处同上
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3307
default-character-set=utf8
4，在各自的bin目录下进行数据库安装和服务注册
mysqld --initialize --console：此时记住临时密码root@localhost:后面的“随机数”就是初始密码（不含首位空格）。
mysqld --install MYSQL5 或者mysqld --install MYSQL8 分别起两个服务名字
5，注册表服务指向确认，确认两个服务 不同的执行路径。
5，最后可以set password for username@localhost=password('新密码')
5，浅析Sql注入
有一张学生表
现在需要根据学生名称获取学生的期末考试分数。
1.public static void getStudent(String name) throws ClassNotFoundException {
2. Connection conn = null;
3. Statement stmt = null;
4. ResultSet rs = null;
5. try {
6. Class.forName(JDBC_DRIVER);
7. conn = DriverManager.getConnection(DB_URL, USER, PASS);
8. stmt = conn.createStatement();
9. rs = stmt.executeQuery("select name,score from student where name =' " + name +"'");
10. while (rs.next()) {
11. System.out.println(rs.getString("name") + ":" + rs.getInt("score"));
12. }
13. } catch (SQLException e) {
14. // ignore
15. } finally {
16. if (rs != null) {
17. try {
18. rs.close();
19. } catch (Exception e) {
20. // ignore
21. }
22. }
23. if (stmt != null) {
24. try {
25. stmt.close();
26. } catch (Exception e) {
27. // ignore
28. }
29. }
30. if (conn != null) {
31. try {
32. conn.close();
33. } catch (SQLException e) {
34. // ignore
35. }
36. }
37. }
38. }1. 请指出上面这段程序存在什么安全风险？
用户在输入表单，通过恶意sql欺骗服务器。根源在于动态拼接sql；
此时可以使用preparedstatement。？占位符代表参数就可以简单防止sql注入了。
在一个就是注意封装数据库报错信息。还有就是涉密信息一定加密处理。
6，工作中SQL语句整理
窗口：记录集合窗口函数：在满足某些条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同，窗口大小都是固定的，称为静态窗口；有的函数则相反，不同的记录对应着不同的窗口，称为滑动窗口。
1. 窗口函数和普通聚合函数的区别：
①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。
②聚合函数也可以用于窗口函数。
2. 窗口函数的基本用法：
函数名 OVER 子句
over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。
①window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；
②PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
③ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；
④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。
ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2
--------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 查询昨日一整天的数据
SELECT * from 表名where date(列名) in (DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY) ,'%Y-%m-%d 00:00:00') ,DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY) ,'%Y-%m-%d 23:59:59') )
-- 查询今日开始到当前时间的数据
SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY) ,'%Y-%m-%d 00:00:00') AS '今日开始时间' ,DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY) ,'%Y-%m-%d %H:%i:%s') AS '今日当前时间'
-- 查询上一周的数据
select * from 表名WHERE YEARWEEK( date_format(alarm_time, '%Y-%m-%d')) = YEARWEEK(now()) - 1
-- 查询从现在开始一周内数据
@Select("select count(*) from 表名where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(列名)")
从现在开始前7天的数据不算小时 就算天数 Now就是自带小时格式了
select * from 表名 where DATE_SUB(now(), INTERVAL 7 DAY) <= date(列名)
或者 规范格式化：
select * from 表名 where DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 7 DAY),'%Y-%m-%d %H:%M:%S')<=date(列名)-- 查询上月一整个月的数据
SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH) ,'%Y-%m-01 00:00:00') AS '上个月开始时间' ,
date_sub(date_sub(date_format(now(),'%Y-%m-%d 23:59:59'),interval extract( day from now()) day),interval 0 month) AS '上个月结束时间'
-- 查询本月开始到当前时间的数据
SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 MONTH) ,'%Y-%m-01 00:00:00') AS '本月开始时间' ,DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY) ,'%Y-%m-%d %H:%i:%s') AS '本月当前时间'
-- 查询去年一整年的数据
SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 YEAR) ,'%Y-01-01 00:00:00') AS '去年开始时间' ,
DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 YEAR) ,'%Y-12-31 23:59:59') AS '去年结束时间'
-- 查询本年开始到当前时间的数据
SELECT DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY) ,'%Y-01-01 00:00:00') AS '本年开始时间' ,DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 0 DAY) ,'%Y-%m-%d %H:%i:%s') AS '本年当前时间'
--查询12月30当天数据
SELECT * from 表名 where date(列名) in (('2019-12-30 00:00:00') ,('2019-12-30 23:59:59'))
-- SELECT NOW(),CURDATE(),CURTIME() 注意格式是不一样的
网络转载：https://www.cnblogs.com/caidao-cbj/p/4582758.html
今天 select * from 表名 where to_days(时间字段名) = to_days(now());
昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
7天 SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天 SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月 SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上月 SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
#查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());
#查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());
#查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
查询当前月份的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
查询上个月的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select * from user where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())
1736
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
