工具理论篇-MySQL知识汇总

1. 唯一索引

普通索引允许被索引的数据列包含重复的值。唯一索引则是不允许有重复的值，当然 null 除外，唯一索引不仅仅可以存储 null ， 还可以存储多个 null。这么做的好处是：

• 简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；

• MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了，如果是，MySQL将拒绝插入那条新记录。一般来说，设置唯一索引是为了避免数据出现重复。

2. 删除有外键的表的数据报错

报了个这个错

Cannot delete or update a parent row: a foreign key constraint fails

临时解决办法

先关闭外键约束，执行删除操作，然后再开启外键约束

SET foreign_key_checks = 0;  // 先设置外键约束检查关闭
drop table mytable;  // 删除数据，表或者视图
SET foreign_key_checks = 1; // 开启外键约束检查，以保持表结构完整性

3. OR

今天用到了mysql中的or，记录下

-- 查询结果是 name=‘gou'或name='hou' 且age = 20
SELECT * from student WHERE (name = 'gou' or name = 'hou') AND age = 20;

4. INSERT

经常忘记mysql中的insert怎么写，记录下

INSERT INTO items(name,price,number,picture)  VALUES('耐克运动鞋',500,1000,'003.jpg');

多条同时插入的话

INSERT INTO 
items(name,price,number,picture) 
VALUES
('耐克运动鞋',500,1000,'003.jpg'),
('耐克运动鞋2',600,1000,'002.jpg');

 推荐多条同时插入吧，可以避免程序和数据库建立多次连接，多次连接会增加服务器负荷。

5. UPDATE

经常忘记mysql的update怎么写，记录下

update user set type = 'student' where name='hk';

6. DELETE

delete也记下吧

delete from user where id = 3 or id = 4;

7. like

有时候会查询一个字段中是否包含某个字符串

select * from test where `name` like "%gle%";
// 查询出包含gle字符的字符串

8. 修改字段

有时候会修改字段的长度和类型，记下方便拿来就用

# 更改字段类型
alter table 表名 modify column 字段名 类型;

# 更改字段长度
alter table 表名 modify column 字段名 varchar(130);

// 修改字段名
alter table 表名 change 旧字段名 新字段名 varchar(255);

当然有时候还会增加删除字段

alter table 表名 add 字段名 varchar(225); //增加一个字段
alter table 表名 DROP COLUMN 字段名; 　 //删除一个字段

9. 修改表名

ALTER  TABLE t_old RENAME TO t_new

10. MySQL按时间求平均值和插值

有时候需要按字段聚合求平均值，比如有每小时的数据（值为0就不存储），求每天的平均值，可以直接用AVG函数，如下：

SELECT
AVG(字段)
FROM
`表`
GROUP BY
DATE_FORMAT(date_time, '%Y%m%d');

# 或者
SELECT
AVG(字段)
FROM
`表`
GROUP BY
SUBSTRING(date_time,1,10)

还有每小时的数据统计，如果没有值的写0

SELECT
    date_add(date, interval 1 hour),
    COALESCE (字段, 0)  新字段名
FROM
    ‘表‘ 
WHERE
    date_add(date, interval 1 hour) >= '2019-07-20 01:00:00'

这里用到coalesce函数和date_add函数，coalesce函数是指返回参数中的第一个非空表达式（从左向右依次类推）；

select date_add(date interval 1 day);   - 加1天
select date_add(date interval 1 hour);   -加1小时
select date_add(date, interval 1 minute);    - 加1分钟
select date_add(date, interval 1 second); -加1秒
select date_add(date, interval 1 microsecond);-加1毫秒
select date_add(date, interval 1 week); -加1周
select date_add(date, interval 1 month); -加1月
select date_add(date, interval 1 quarter); -加1季
select date_add(date, interval 1 year); -加1年

有时候读出来的日期数据会有.0，比如

2019-02-22 02:00:00.0

这时候可以用

DATE_FORMAT(字段名称,'%Y-%m-%d %H:%m:%s') 别名

11. MySQL里的Boolean

MySQL保存BOOLEAN值时用1代表TRUE,0代表FALSE，BOOLEAN在MySQL里的类型为tinyint(1)，这是一种方法。另外，我在创建数据表的时候还使用过bit(1)表示BOOLEAN，在MYSQL终端查询是不显示值的，但是程序查询可以看到true或者false。

12. MySQL的表复制

• 只复制表结构到新表

create table 新表 select * from 旧表 where 1=2
或者     create table 新表 like 旧表 

• 只复制表数据到另一张表

INSERT INTO  表1  SELECT * FROM  表2;

• 复制表结构及数据到新表

create table 新表 select * from 旧表

13. show table

show table的时候末尾加 \G，可以规范化

14. MySQL的表数据导出到Excel

方法有很多种，这里试下最简单的一种（当然也可以在navicat里边运行导出，嘿嘿）

echo "select * from db_web.help_cat where 1 order by sort desc limit 0,20" | mysql -h127.0.0.1 -uroot > /data/sort.xls 

15. MySQL和Java中数据类型对应

类型名称	显示长度	数据库类型	JAVA类型	JDBC类型索引(int)
VARCHAR	L+N	VARCHAR	java.lang.String	12
CHAR	N	CHAR	java.lang.String	1
BLOB	L+N	BLOB	java.lang.byte[]	-4
TEXT	65535	VARCHAR	java.lang.String	-1
INTEGER	4	INTEGER	UNSIGNED java.lang.Long	4
TINYINT	3	TINYINT UNSIGNED	java.lang.Integer	-6
SMALLINT	5	SMALLINT UNSIGNED	java.lang.Integer	5
MEDIUMINT	8	MEDIUMINT UNSIGNED	java.lang.Integer	4
BIT	1	BIT	java.lang.Boolean	-7
BIGINT	20	BIGINT UNSIGNED	java.math.BigInteger	-5
FLOAT	4 + 8	FLOAT	java.lang.Float	7
DOUBLE	22	DOUBLE	java.lang.Double	8
DECIMAL	11	DECIMAL	java.math.BigDecimal	3
BOOLEAN	1	同TINYINT
ID	11	PK (INTEGER UNSIGNED)	java.lang.Long	4
DATE	10	DATE	java.sql.Date	91
TIME	8	TIME	java.sql.Time	92
DATETIME	19	DATETIME	java.sql.Timestamp	93
TIMESTAMP	19	TIMESTAMP	java.sql.Timestamp	93
YEAR	4	YEAR	java.sql.Date	91

注：

• 对于bolb，一般用于对图片的数据库存储，原理是把图片打成二进制，然后进行的一种存储方式，在java中对应byte［］数组。
• mysql不支持bool类型。

create table xs
(
id int primary key,
bl boolean
)

这样可以创建成功，但检查表结构会发现mysql把它替换成tinyint(1)。即当把一个数据设置成bool类型的时候,数据库会自动转换成tinyint(1)的数据类型,其实这个就是变相的bool。 默认值也就是1,0两种,分别对应了bool的true和false，在Java对应的POJO类中，可以根据实际情况使用java.lang.Boolean或者java.lang.Integer。

16. 导入导出指定数据库数据

• 导出 

mysqldump -u root -p123456 数据库 > test.sql

• 导入

mysql -u root -p123456 数据库 < "D:\MySQL 5.5\bin\test.sql" 

17. 创建UTF-8字符集数据库

CREATE DATABASE 数据库1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

18. 查看数据表最后更新时间

SELECT 
    `TABLE_NAME`, `UPDATE_TIME` 
FROM 
    `information_schema`.`TABLES` 
WHERE 
    `information_schema`.`TABLES`.`TABLE_SCHEMA` = 'mysql' 
AND
    `information_schema`.`TABLES`.`TABLE_NAME` = 'user';

如：

19. Unkonw

有时候逻辑表达式有三种值：True、False、Unknown，具体可以参见：关于数据库中的三值逻辑（Tree-Value-Logic）