DATE_FORMAT()和STR_TO_DATE()
常用的 DATE_FORMAA()格式
DATE_FORMAT(updatetime,'%Y-%m-%d') -- 把mysql的datetime格式化成2021-09-23的字符串格式DATE_FORMAT(updatetime,'%Y-%m-%d %H:%i:%S') -- 把mysql的datetime格式化成2021-09-07 09:30:37的字符串格式
常用的 STR_TO_DATE()格式
STR_TO_DATE('2015-09-01 00:00:00','%Y-%m-%d %H:%i:%s') -- 把字符串转为datetimeg
mysql dump导入sql脚本后表中的数据中文乱码
解决办法:加上—default-character-set=utf8 参数即可
mysql在导入.sql文件的时候报错 1067 - Invalid default value for LOCK_TIME
推荐使用以下的方式永久修改
编辑 mysql 的配配置文件 my.cnf
在[mysqld]下面添加如下列:
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
any 和 all 关键字
A = any('a','b') 等价于 A = 'a' or A = 'b'A = all('a','b') 等价于 A = 'a' and A = 'b'
总结 :any 相当于用 or 链接后面括号里的子元素,all 相当于用 and 链接后面括号里面的子元素
mysql建立新用户和授权
需求:只允许某个用户访问某个库# 建立用户user能在任何ip登录,并且密码是passwdcreate user 'user'@'%' identified by 'passwd';# 授权:授予user用户能用密码passwd从任何ip登录并且有访问ctoms_test数据库任何资源的所有权限grant all privileges on ctoms_test.* to user@'%' identified by 'passwd'; # 刷新权限flush privileges;# 查看自己刚刚建立的用户SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
mysql建表的几个必备字段
id(主键)
码表(在基础字典表中该字段是必须的)
排序(在基础字典表中该字段是必须的)
创建人
创建时间
修改时间
逻辑删除字段
mysql常见的函数和问题的汇总
注意 mysql 里面关于字符串的截取下标一般都是从 1 开始
Substring(‘str’,a,b) 注意:a 是起始位置,b 是要截取得长度。且下标从 1 开始 ,如果 a 是 0,那么无论 b 是多少都返回一个空串
Round(num,a) num 如果为字符串,那么返回的也是数字。如果 num 为’a’或者’b’这种非数字类型的字符串,那么会把这种字符串当成数字 0 ,并且 a 是 0,那么就是 0 ,a 是 1,就是 0.0 。
注意:如果 num 是整数(round(2234,2)),那么无论 a 是多少,返回的都是整数(2234),如果 num 是整数型的字符串(round(‘2234’,3)),那么返回的就是带 0 的小数(2234.000)。
注意:abs(25.0)=>25.0 abs(‘25.0’) =>25
INSTR(str,substr) / instr(源字符串, 目标字符串) 获取子串第一次出现的索引,如果没有找到,则返回 0(下标从 1 开始)
使用 union all 链接两个查询结果的时候,如果链接查询结果要有各自的顺序并且总结 ...
show profile功能
开启 Show Profile 功能,默认该功能是关闭的,使用前需开启。show variables like 'profiling'; set profiling = on; ## 执行一部分sql后(默认保留15条) show profiles; ## duration是持续时间 ## 针对特定的sql进行诊断 show profile cpu,block io for query Query_ID;/*Query_ID为#3步骤中show profiles列表中的Query_ID*/
show profile 的常用查询参数。
①ALL:显示所有的开销信息。
②BLOCK IO:显示块 IO 开销。
③CONTEXT SWITCHES:上下文切换开销。
④CPU:显示 CPU 开销信息。
⑤IPC:显示发送和接收开销信息。
⑥MEMORY:显示内存开销信息。
⑦PAGE FAULTS:显示页面错误开销信息。
⑧SOURCE:显示和 Source_function,Source_file,Source_line 相关的开销信息。
⑨SWAPS:显示 ...
区间查询
取交集的区间查询两个区间段只要有交集就查出来
<if test='(startTime != "" and startTime != null) and (endTime == null or endTime == "")'> AND csrw.endtime >= #{startTime}</if><if test='(startTime == "" or startTime == null) and (endTime != null and endTime != "")'> AND csrw.createtime <= #{endTime}</if><if test='(startTime != "" and startTime != null) and (endTime != null and endTime != ...
mysql的安装
mysql8
查看是否有安装过 mysql
rpm -qa | grep -i mysql
使用rpm -e命令将上个命令中包列表全部删除
然后删除相关服务
chkconfig --list | grep -i mysqlchkconfig --del mysql
删除 mysql
yum -y remove MySQL-*yum -y remove MySQLyum remove mysql mysql-server mysql-libs compat-mysql51
一般用 rpm -e 的命令删除 mysql,这样表面上删除了 mysql,可是 mysql 的一些残余程序仍然存在,并且通过第一步的方式也查找不到残余,而 yum 命令比较强大,可以完全删除 mysql.(ps:用 rpm 删除后再次安装的时候会提示已经安装了,这就是 rpm 没删除干净的原因)
把所有出现的目录统统删除
find / -name mysql
查找 mysql 的一些目录,把所有出现的目录删除,可以使用 rm -rf 路径,删除时请注意,一旦删除无法恢复。
删除配置文件
rm -rf /etc/ ...
主从复制的搭建
#
做主从的前提
两台服务器的防火墙都开放了各自 mysql 的服务端口(下面以默认的 3306 为例子)
从库无法同步主库之前的数据。如果主库之前有数据,那么先把主库的数据导入到从库中。保证两台服务器在做主从复制之前的数据一致性
尽量保证两台服务器的 my.cnf 文件只有 server-id 不同。其他的配置都相同
修改主服务器的配置
vi /etc/my.cnf
[mysqld]# 启用主从配置(主服务器)# 主服务器idserver-id=1# 二进制日志log-bin=mysqlbin# 设置忽略复制的数据库# binlog-ignore-db=mysql# 设置需要复制的数据库# binlog-do-db=dtjc
重启 mysql 服务器
service mysqld restart
mysqld 无效的话把 mysqld 换成 mysql
运行
mysql> show master status;+-----------------+----------+--------------+------------------+---------------- ...
time和update_time的策略
二者默认值都设置为 CURRENT_TIMESTAMP(DEFAULT CURRENT_TIMESTAMP),保证插入时记录时间
update_time 勾选上根据当前时间戳更新(ON UPDATE CURRENT_TIMESTAMP),保证更新时记录时间
参考 sql
CREATE TABLE `mytest` ( `text` varchar(255) DEFAULT '' COMMENT '内容', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间') ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意:采用数据库层面的策略不支持切换数据库,如果想要开发的应用兼 ...
union和union all 关键字
区别 1:取结果的交集
union: 对两个结果集进行并集操作, 不包括重复行,相当于 distinct, 同时进行默认规则的排序;
union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;
区别 2:获取结果后的操作
union: 会对获取的结果进行排序操作
union all: 不会对获取的结果进行排序操作
区别 3:
union 看到结果中 ID=3 的只有一条select * from student2 where id < 4unionselect * from student2 where id > 2 and id < 6
union all 结果中 ID=3 的结果有两个
select * from student2 where id < 4union allselect * from student2 where id > 2 and id < 6
总结
union all 只是合并查询结果,并不会进行去重和排序操作,在没有去重的前提下,使用 union all 的 ...
查询时别名
在 mysql 中
group by ,having ,order by 支持字段的别名。group by ,order by 后使用别名select case when t.author_level in(1,2) then '1-2级' when t.author_level in(3,4) then '3-4级' when t.author_level in(5,6) then '5-6级' else '' end as level_cnt, count(s.issue_id) as numfrom author_tb t join answer_tb s on t.author_id = s.author_idwhere s.char_len >= 100group by level_cntorder by num desc
where 后面不支持字段的别名。
如果关联查询的时候给表起了别名。那么 where 后面如果 ...
比较date或者datetime
单个的比较可以直接使用> < 或者= 来比较,但是当两个值的组合与另两个值的组合进行比较的时候,可以使用UNIX_TIMESTAMP()函数
SELECT *FROM dtjc_jh_jdjhWHERE xm_id = #{xmId}ORDER BY (UNIX_TIMESTAMP( start_time ) + UNIX_TIMESTAMP( end_time ))/ 2;
如上就是根据起始时间和终止时间的中间值进行比较。其中 start_time 和 end_time 都是 datetime 类型
开放远程连接
5.7先连接到本地数据库
切换到 mysql 数据库
use mysql
使用以下命令可以更改远程连接的设置
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
刷新权限
flush privileges;
查询 user 表看看是否生效,如果 ‘%’ ‘root’ 在第一行证明生效了
select host,user from user;
锁
myisam 的读写锁(表锁)lock table emp read;lock table emp write;
myisam 是写锁调度优于读锁调度,所以 mysiam 要偏读(因为写会阻塞其他线程对当前表的任何操作)
myisam 执行 select 时会给所有涉及的表增加读锁。执行增删改时会给所有涉及到的表增加写锁
表读锁,当前 session 只能读当前表,对其他表任何操作都做不了,其他 session 能做任何操作,只是对有读锁的表的增删改会阻塞
表写锁,当前 session 只能对当前表做增删改查,对其他表任何操作都做不了,其他 session 对有写锁的表的任何操作都会堵塞,但是对其他的表可以做任何操作
show open tables; --查看哪些表被锁了show status like 'table%'; --分析表的锁定状况
innodb 的读写锁(行锁)session1 更新某一行时,且未提交。session2 读到的是旧数据。直到 ession1 提交。session2 才能读到新数据
session1 更新某一行时,且未提交。当 ses ...
学习路线
jlpt n5->n4->n3->n2
java:stream,lambda,IO 流,注解,反射,枚举,网络编程,websocket,nio
mysql,redis
jvm(初级),jvm(中级和高级),juc
spring->springmvc->springboot->springcloud
zookeeper,nginx,linux
kafka,rabbitmq
elasticsearch,minio
maven,docker,k8s
设计模式,计算机网络,数据结构与算法,计算机组成原理,操作系统,编译原理
项目:谷粒学院,谷粒商城,尚融宝
grid 布局
shiro,zookeeper,kafka计算机网络,操作系统,计算机组成原理maven,dockerjvm,juc,设计模式算法,刷题,面经
索引
什么情况下要用索引
主键自带主键索引
唯一约束自带唯一索引
外键自带外键索引
查询条件用到的字段需要
排序用的的字段
分组用到的字段
什么情况下不能用索引
数据量较少时不用建索引。
频繁更新字段不能建索引
索引的选择性(字段的值尽量复杂且尽量分布不平均)
where 条件查询用不到的字段不用建索引
mysql 优化索引十诫(附口诀)
全值匹配我最爱
最佳左前缀法则(如果索引引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列)
不在索引列上做任何操作(计算,函数,(自动 or 手动)类型转换),会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少 select *
mysql 在使用!=或者<>时候无法使用索引而转向全表扫描
is null,is not null 也无法使用索引
like 以通配符开头(‘%abc’)mysql 索引失效会变成全表扫描的操作。解决办法:使用覆盖索引
字符串不加单引号导致索引失效
少用 or,用 or 连接时会索引失 ...
远程请求http的三种方式
1.ajax 远程调用,远程服务器必须开放跨域访问权限
2.form 表单远程调用,不受跨域限制,缺点是需要组装表单
3.java 代码远程调用(常用的有 java.net.HttpURLConnection,springboot 的 restTemplate)