mysql 使用技巧 不断更新

datetime group by date部分

select date_format(gmt_create,'%Y-%m-%d'),count(*) from demo_table

where gmt_create >'2023-03-01 00:00:00' and gmt_create < '2023-03-09 00:00:00'

GROUP BY date_format(gmt_create,'%Y-%m-%d');

取时间差值

select TIMEDIFF(a_time,b_time) from table;

得到差值:2:10:10

limit 和 limit offset

#1,2,3,4,5
select * from test;
#2,3     跳过数值1个数，取数值2个数
select * from test limit 1,2;
#3       跳过数值2个数，取数值1个数
select * from test limit 1 offset 2;

·查看 所有数据库 所有表容量大小

select table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables order by data_length desc, index_length desc;

·查看指定数据库容量大小

select table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables where table_schema='数据库名';

·查看所有数据库容量大小

select table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc

·mysql 查询表存储、索引存储所占空间大小

select table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)'from information_schema.tables
where table_schema='数据库名'order by data_length desc, index_length desc;

·[MySQL] timestamp和datetime的区别

1.timestamp占用4个字节;datetime占用8个字节
2.timestamp范围1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999;
datetime是1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
3.timestamp默认支持not null default CURRENT_TIMESTAMP自动更新当前时间;datetime 在5.6版本后才支持,需要手动指定not null default CURRENT_TIMESTAMP
4.timestamp转成utc存储,查询再自动转回来;datetime原样存储

·mysql 建表语句

CREATE TABLE IF NOT EXISTS tree_t (
 id int auto_increment not null,
  p int,
  s int,
`gmt_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

·mysql 自连接

表test,搜索 字段name_a=字段name_b的数据

SELECT t1.`name_a`,t1.`name_b`,t2.`name_a` FROM test t1,test t2 WHERE t1.`name_b`=t2.`name_a`

·mysql 入参为字符串，比较数据库时间类型

str_to_date(字符串 , '%Y-%m-%d')

·mysql获取前一天

DATE_SUB(date类型,INTERVAL 数字 DAY)，获得 date+ 数字天数的日期。返回的是一个字符串

·mysql 字符串转化为数字进行判断

str类型为 varchar

入参为字符串“002”，搜索结果满足 str 转化为数字 大于 2 的结果

SELECT * FROM test WHERE CAST('002' AS DECIMAL(20,0))<CAST(str AS DECIMAL(20,0))

· mysql order by 考虑 null

ORDER BY if(isnull(字段),0,1) ASC ,null 排到最先

ORDER BY if(isnull(字段),0,1) DESC，null排到最后

使用mysql数据库的时候会遇到这样的问题，即存储到数据库表中的数据是中文乱码。

解决办法有两个。

1、简单的，其实这是方法2的变种，只不过是哪有问题改哪里。

找到mysql的安装文件，找到my.ini

将其中的改为utf-8

default-character-set=utf8

应该是该两处这样之后，就是你建立的数据库本身应该是utf8的有时保存my.ini说没有权限，那么就使用兼容模式，以管理员身份运行

2、彻底的，先把mysql卸载，然后载从新安装，选择字符集的时候注意utf8,然后就ok了

免安装版本mysql的配置与启动

找到解压目录（比如我这里是 D:\software\mysql-5.6.36-winx64\）下的 my-default.ini
然后复制并重命名为 my.ini


环境变量修改  
新增
MYSQL_HOME  D:\software\mysql-5.6.36-winx64
修改path %MYSQL_HOME%\bin;




然后打开cmd 运行 
注册mysql mysqld install MySQL --defaults-file="D:\software\mysql-5.6.36-winx64\my.ini"
去除mysql mysqld remove

启动
cmd 下 net start mysql
cmd 下停止 net stop mysql

或者在系统服务中启动

windows 下修改密码

本部分内容转自 http://www.jb51.net/article/39454.htm

方法1： 用SET PASSWORD命令 
首先登录MySQL。 
格式：mysql> set password for 用户名@localhost = password('新密码'); 
例子：mysql> set password for root@localhost = password('123'); 


方法2：用mysqladmin 
格式：mysqladmin -u用户名 -p旧密码 password 新密码 
例子：mysqladmin -uroot -p123456 password 123 


方法3：用UPDATE直接编辑user表 
首先登录MySQL。 
mysql> use mysql; 
mysql> update user set password=password('123') where user='root' and host='localhost'; 
mysql> flush privileges; 


方法4：在忘记root密码的时候，可以这样 
以windows为例： 
1. 关闭正在运行的MySQL服务。 
2. 打开DOS窗口，转到mysql\bin目录。 
3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。 
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。 
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。 
6. 连接权限数据库： use mysql; 。 
6. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。 
7. 刷新权限（必须步骤）：flush privileges;　。 
8. 退出 quit。 
9. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

·count用于计算总数，group by 之后如何count?

group by 之后，数据被分组了，即可以获得多条数据，那么有多少组呢？需要把分组搜做结果作为一个表从新搜索。

SELECT COUNT(1) AS flag FROM usermodel u GROUP BY u.decimalNum

SELECT COUNT(t.flag) FROM (SELECT COUNT(1) AS flag FROM usermodel u GROUP BY u.decimalNum) t

·mysql 对于为空的数据赋值为默认值

这里，如果为空，则转为100

SELECT IFNULL(u.age,"100") FROM USER u 

·mysql 的 workbench 由表逆向生产 diagram

Database—> reverse engineering—>数据库用户登陆——>一直next，直到选择数据库——>完成

·PowerDesigner 显示注释

选中准备编辑的表,【右键】->【Properties】->【Columns】->【Customize Columns and Filter】->【Comment】->【OK】 

·mysql workbench 中文注释

注释和特定版本有关系，或许是资料不全的缘故，或许就没有这个功能，我在网上没有找到开启或者关闭注释的办法。

我的版本是6.3，后来找到一个6.2版本的，鼠标移动到表diagram上方会自动显示全表注释。

下载地址http://download.youkuaiyun.com/detail/bestcxx/9917539

·mysql 使用 TINYINT(1)存储boolean类型

'TINYINT 1'类型，可以用来保存 BOOLEAN,默认为false=0 s搜索 =false或者 =0都可以，即0是假，1是真

select * from tabel where a=true 等同于 select * from table where a=1

不要使用 bit(1),否则搜索结果可能出现 "AQ=="，此时数据库存储的是 b(1)，但是使用TINYINT则是1或者0

·为表增加新列，多列之间使用逗号(column不是必要的)

新增多列

ALTER TABLE person ADD COLUMN (age2 INT(2) COMMENT '年龄2',age3 INT(2) COMMENT '年龄3');
ALTER TABLE person ADD (age4 INT(2) COMMENT '年龄4',age5 INT(2) COMMENT '年龄5');

新增一列

alter table table_name add column column_name VARCHAR(50);

·删除列-多列需要DROP，Oracle则只需要一个

ALTER TABLE person DROP COLUMN age;
ALTER TABLE person DROP COLUMN age4,DROP COLUMN age5;

·重定义列-多行使用多个MODIFY，逗号隔开

ALTER TABLE person MODIFY COLUMN age2 INT(2) COMMENT '年龄修改注释';

ALTER TABLE person MODIFY COLUMN age2 INT(2) COMMENT '年龄修改注释',MODIFY age3 INT(2) COMMENT '年龄修改注释';

·为列重命名

alter table table_name rename column column_name1 to column_name2;

·删除索引

alter table table_name drop index index_name;

或者 

drop index index_name on table_name

·添加索引

·varchar和char的区别（varchar2是oracle特有标准的varchar）

·char是固定存储长度，设置多少存储多少；varchar是限制最大长度，小于最大长度则按实际长度存储；

·char比varchar效率高；

·varchar是工业标准，varchar2是oracle数据库特有的标准，区别在于，varchar可以使用空字符串作为参数存储，varchar2则把空字符串作为null,不允许varchar存储空字符串;

·

·使用：如果字段长度相差无几，修改频率高，使用char。否则使用varchar。

VARCHAR2虽然比CHAR节省空间，但是如果一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会更好一些。

· left join 表1 and 条件 与 where 条件的区别

表0 left join 表1 and 条件 与 where 条件的区别
表0 left join 表1 and 条件 是对表1中先进行筛选，然后在参与left join
where 则是先 表0和表1 left join 对结果集进行筛选

·快速删除一张表里的全部数据

TRUNCATE TABLE [表明]，这是最快的办法，原因是delete from 是按照条件逐一删除的，这个“不管三七二十一”

·mysql 表外键级联删除 Cascade\Set null\Restrict\No Action

Cascase 跟从主表操作，主表删除我删除，主表更新我更新

Set null 主表删除或者更新，我的外键字段变为null

Restrict 不允许主表更新或者删除

No Action 允许修改本表的外键字段-主表的另一个已存的字段，不允许主表删除

二者的区别：

1、事务：truncate是不可以rollback的，但是delete是可以rollback的；

原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引    
3、 truncate 不能触发任何Delete触发器。
4、delete 删除可以返回行数