mysql学习总结

1、一个汉字占多少长度与编码有关：
UTF－8：一个汉字＝3个字节
GBK：一个汉字＝2个字节
一秒等于1000毫秒,mysql中int默认长度为11

如果想知道mysql 数据库中的每个表占用的空间、表记录的行数的话，可以打开mysql的information_schema数据库。在该库中有一个tables表，这个表主要字段分别是：
table_schema:数据库名
table_name：表名
engine:所使用的存储引擎
table_rows:记录数
data_length:数据大小（单位字节）
index_length:索引大小
row_format: 可以查看数据表是否压缩过
1.查看执行时间和cpu占用时间
set statistics time on
select * from dbo.Product
set statistics time off

2.查看查询对I/0的操作情况
set statistics io on
select * from dbo.Product
set statistics io off

--比较实用的sql优化
http://www.cnblogs.com/knowledgesea/p/3686105.html

https://www.cnblogs.com/Little-Li/p/8031295.html

一、 基本操作（建、增删改查、关键字(约束、函数等)）

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1（只是MySQL特有的）
PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔
UNIQUE 唯一约束

-- 用于查询mysql中的时间（显示当前时间）
-- 获取当前时间，格式为（YYYY-MM-DD HH:MM:SS）
SELECT NOW();
-- 获取当前时间，格式为（YYYY-MM-DD）
SELECT CURDATE();
-- 获取当前时间，格式为（HH:MM:SS）
SELECT CURTIME();

时间日期数据类型：
date:你直接就可以理解为2017-3-21 不带时分秒的
datetime，则是带时分秒的 
time (HH-MM-SS)
timestamp:时间戳 很好理解
-- date (YYYY-MM-DD) datetime(YYYY-MM-DD HH-MM-SS)
-- time  (HH-MM-SS)  TIMESTAMP(YYYY-MM-DD HH-MM-SS)

mysql解决datetime与timestamp精确到毫秒的问题
DATETIME(3)       TIMESTAMP(3)  

distinct  查询出某个字段不重复的记录，返回不重复字段的条数count(distinct id)

数据的类型和长度说明：https://www.runoob.com/mysql/mysql-data-types.html

1 、-------------------------------------建：-------------------------------------

索引的类型：
    UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
    INDEX(普通索引)：允许出现相同的索引内容
    PROMARY KEY(主键索引)：不允许出现相同的值
    fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维
    组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

-- 创建用户表（先判断是否存在该用户）

DROP TABLE IF EXISTS users;
CREATE TABLE users(
userId INT AUTO_INCREMENT,
userPhone VARCHAR(20) NOT NULL,
userName VARCHAR(100) NOT NULL,
userPwd VARCHAR(40) NOT NULL,
userOther VARCHAR(100),
userNo VARCHAR(100) UNIQUE NOT NULL,
login_Error INT,
login_Old_Time DATETIME,
PRIMARY KEY (userId)
);

INSERT INTO users(userPhone,userName,userPwd,userNo) VALUES('13122100063','朕1','123456','zhen');
SELECT * FROM users;

DROP TABLE IF EXISTS userInfo;
CREATE TABLE userInfo(
sex VARCHAR(2),
userAddress VARCHAR(100),
userCity VARCHAR(50),
user_No VARCHAR(100) NOT NULL,
user_IDS INT
);
--  创建外键
 ALTER TABLE userInfo ADD FOREIGN KEY (user_IDS)
      REFERENCES user (userId);

或者是：
CREATE TABLE userInfo(
sex VARCHAR(2),
userAddress VARCHAR(100),
userCity VARCHAR(50),
user_No VARCHAR(100) NOT NULL,
user_IDS INT,
FOREIGN KEY fk_emp(user_IDS) REFERENCES USERs(userId)
);

//mysql 中创建视图
    create view V_ESB_SERV_LIST(id,name) as select id,name from esb_serv_list ORDER BY id;
    
//mysql ------    eg:  创建索引  
  alert table 表名 add index 索引名(被创建索引的列名)
    普通索引
    alter table table_name add index index_name (column_list) ;
    唯一索引
    alter table table_name add unique (column_list) ;
    主键索引
    alter table table_name add primary key (column_list) ;
    
    CREATE INDEX index_name ON table_name(username(length)); -- 直接创建索引
  删除索引：
    drop index index_name on table_name ;
    alter table table_name drop index index_name ;
    alter table table_name drop primary key ;-- 只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引

    
    
-- 创建存储过程，把一个表中的数据转移到另一个表中。存储过程中先查询最大行数值，通过into赋值给变量，在以变量为查询条件进行数据转移。
-- 1 
DELIMITER$$
DROP PROCEDURE IF EXISTS `cunChu`$$
CREATE PROCEDURE cunChu(IN days INT)
BEGIN
DECLARE maxS INT;
SELECT MIN(userId) INTO maxS FROM users WHERE userId<days;
INSERT INTO myuser SELECT * FROM users WHERE userId>maxS;
END$$
DELIMITER;
-- 调用存储过程
CALL cunChu(5);

2、------------------------------------增-------------------------------------

INSERT INTO users(userPhone,userName,userPwd,userNo) VALUES('13122100063','朕1','123456','zhen');

-- 新增  (给表中新增加一列)
alert table +表名 add(列名 类型);
eg:alert table user add(name varchar2(20));

3、-------------------------------------改-------------------------------------
UPDATE userInfo SET sex='' WHERE user_No=''

-- 删除主键约束
alter table 表名 drop primary key;
-- 添加主键约束
alter table 表名 add primary key(id,name);
-- 修改主键约束
alter  table 表名 modify id int primary key；
-- 添加外键约束
 ALTER TABLE 表名1 ADD FOREIGN KEY (表名1.列名)  REFERENCES 表名2 (表名2.列名);
 
 --修改 (修改表中一列的类型长度)alter--数据库中修改的关键词  MODIFY--指修改(修改已存在的列)
ALTER TABLE Timed_Task_Master MODIFY COLUMN last_Execution_Time DATETIME(3);-- 把日期的精确度改为毫秒

4、-------------------------------------删-------------------------------------

delete from +表名 where +条件;-- 删除数据库中对应的数据
-- 删除 表 已存在的数据
DROP TABLE IF EXISTS userInfo;
-- 删除存储过程-
DROP PROCEDURE IF EXISTS 存储名称

  删除--程度从强到弱
drop  table tb 
 drop将表格直接删除，没有办法找回
truncate (table) tb
 删除表中的所有数据，不能与where一起使用
delete from table (where)
删除表中的数据(可制定某一行)

在mysql中模糊查询写法：
SELECT *FROM ESB_APPS_SYS_LIST WHERE queue_id LIKE '%SAP%';

5、-------------------------------------查------------------------------------- 

limit ?,? 第一个参数显示分页从哪一条开始的,第二个参数为取出多少条数据（limit 5,7从第6条开始然后取出7条数据）

//mysql中的分页：
pageNo显示当前页数
pageSize显示每页显示的条数
（int startIndex=page.getPageSize() * (page.getPageNO()-1);//从哪一条开始的
int endIndex=page.getPageSize();//显示多少条        ）

mysql中 round(x,y);x指要处理的数，y是指保留几位小数
        eg:查询: select round(1123.26723,2);
        结果：1123.27        
        查询: select round(1123.26723,-2);
        结果：1100
        
CONCAT()函数用于将多个字符串连接成一个字符串
        eg:SELECT CONCAT(’My’, ‘S’, ‘QL’);
        结果为：MySQL

-- ORDER BY的用法，对一个表的数据列分为A段、B段，分别按照两列进行排序 ，先按照A段中pub_created_time列进行倒叙，再按照B段中的sub_created_time列进行排序
ORDER BY pub_created_time DESC,sub_created_time DESC

desc 降序 、asc 升序

1：join、inner join、cross join；语法select * from A inner join B on A.id = B.A_ID，返回匹配的行

2：left join；语法 select * from A left join B on A.id = B.A_ID,即使右表中没有匹配，也返回左表的所有的行，右表没匹配的都为null；

3：right join ；语法select * from A right join B on A.id = B.A_ID,即使左表中没有匹配，也返回右表所有的行，左表没匹配的都为null。

二、优化操作（查询、插入等）

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
3）很多时候用 exists 代替 in 是一个好的选择
4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

当只需要一行数据的时候使用limit 1
（union all链接两表时，两表的字段要保持一致。）
两表链接查询统计一共有多少条数据：(注：必需给子查询的表加一个伪名eg:allTable)
select count(1),allTable.id
from 
(
select id
from a 
where id>5
union all
select id 
from b 
where id>5
) AS allTable

                   -------            数据转移                 -------            
-- 创建存储过程，把一个表中的数据转移到另一个表中。存储过程中先查询最大行数值，通过into赋值给变量，在以变量为查询条件进行数据转移。
-- 1 
DELIMITER 
USE `esb`
DROP PROCEDURE IF EXISTS `procBackupEsbServHealthLog`
CREATE PROCEDURE `procBackupEsbServHealthLog`(IN days INTEGER)
BEGIN 
    DECLARE maxSeq INT;
    SELECT MAX(seq) INTO maxSeq FROM esb_log.esb_serv_health_log  WHERE DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'), DATE_FORMAT(created_time,'%Y-%m-%d'))>days;
    INSERT INTO esb_log.esb_serv_health_log_history SELECT * FROM esb_log.esb_serv_health_log WHERE seq <= maxSeq;
  DELETE FROM esb_log.esb_serv_health_log WHERE seq <= maxSeq;
    END
DELIMITER ;

-- 2
DELIMITER 
USE `esb`
DROP PROCEDURE IF EXISTS `procBackupEsbServLog`
CREATE PROCEDURE `procBackupEsbServLog`(IN days INTEGER)
BEGIN 
    DECLARE maxSeq INT;
    SELECT MAX(seq) INTO maxSeq FROM esb_log.esb_serv_log  WHERE DATEDIFF(DATE_FORMAT(NOW(),'%Y-%m-%d'), DATE_FORMAT(created_time,'%Y-%m-%d'))>days;
    INSERT INTO esb_log.esb_serv_log_history SELECT * FROM esb_log.esb_serv_log WHERE seq <= maxSeq;
  DELETE FROM esb_log.esb_serv_log WHERE seq <= maxSeq;
    END
DELIMITER ;

unique一般表示唯一，多与主键primary key
constraint是用来添加完整性约束条件，并且指定该约束条件的名字(这样就方便以后删除这个约束条件)。

三、问题解决

//----- 往MySQL数据库中保存较大的文件时，遇到：
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (8332986 > 4194304).异常
造成这个错误的原因是因为MySQL对数据写入操作进行了限制，限定了单次写入操作的数据大小上限。 
要解决这个问题很简单，只要修改MySQL的该配置即可。

找到MySQL的安装路径下的  my.ini 并打开
查找max_allowed_packet，有没有，没有的话增加
#上传文件的最大值
max_allowed_packet = 20M   

然后重启MySQL

//-----    mysql中添加外键约束遇到一下情况： 
cannot add foreign key constraint 
出现这个问题一般是开发工作者对外键的使用出现了疏忽，我们先清晰一下外键的使用： 
1. 外键字段不能为该表的主键； 
2. 外键字段参考字段必须为参考表的主键。
(外键的建立条件：主表需要有主键，从表关联字段是主表主键。因为主键只有一个，两个表之间只能有一个外键。)

//----- mysql中的查询：(倒叙，OR，模糊查询)
-- 查询内容
SELECT SEQ,SERV_ID,SERV_SN,SERV_DIR,DIRACTORY_NAME,SERV_NAME,REQ_APPS_SYS_ID,REQ_APPS_SYS_NAME,
PROVIDER_APPS_SYS_ID,PROVIDER_APPS_SYS_NAME,BUSINESS_STATUS,MESSAGE_STATUS,REQ_IN_TIME,REQ_OUT_TIME,
RES_IN_TIME,RES_OUT_TIME,ESB_TIME,REQ_TIME,RES_TIME,SERV_TIME,SERV_IP 
FROM V_ESB_SERV_LOG_ALL2 WHERE 1 = 1  AND SERV_ID  LIKE'%10060000000004%'
AND SERV_TYPE  ='2' AND ( MESSAGE_STATUS  ='F' OR BUSINESS_STATUS ='F' ) 
AND CREATED_DATE >='2018-01-01' AND CREATED_DATE <='2018-04-24' ORDER BY SEQ DESC

. 为什么需要预编译
JDBC 中使用对象 PreparedStatement 来抽象预编译语句，使用预编译。预编译阶段可以优化 sql 的执行。
预编译之后的 sql 多数情况下可以直接执行，DBMS 不需要再次编译，越复杂的sql，编译的复杂度将越大，预编译阶段可以合并多次操作为一个操作。
预编译语句对象可以重复利用。把一个 sql 预编译后产生的 PreparedStatement 对象缓存下来，下次对于同一个sql，可以直接使用这个缓存的 PreparedState 对象。
mybatis 默认情况下，将对所有的 sql 进行预编译

#{}与${}的区别： 
1. 能使用#{}的地方尽量使用#{}。${}在预编译之前已经被变量替换了，这会存在sql注入问题。
    #方式能够很大程度防止sql注入，预编译时会编译为？代替参数，能预防SQL注入。
（预编译：sql 预编译指的是数据库驱动在发送 sql 语句和参数给 DBMS 之前对 sql 语句进行编译，这样 DBMS 执行 sql 时，就不需要重新编译。）
2. 表名作为变量时，必须使用 ${ }，这是因为，表名是字符串，使用 sql 占位符替换字符串时会带上单引号 ''，这会导致 sql 语法错误

在mysql中创建函数时：报This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
（mysql 报错This function has none of DETERMINISTIC解决方案（网页地址：https://www.jb51.net/article/97037.htm））
原因：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 deterministic 不确定的
2 no sql 没有sql语句，当然也不会修改数据
3 reads sql data 只是读取数据，当然也不会修改数据
4 modifies sql data 要修改数据
5 contains sql 包含了sql语句
其中在function里面，只有 deterministic, no sql 和 reads sql data 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
解决方法：
-- 执行
show variables like 'log_bin_trust_function_creators';
-- 显示结果为
 Variable_name          | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF  |

-- 执行（解决方法（可设置的参数：log_bin_trust_function_creators=off
或者设置为log_bin_trust_function_creators=on））
set global log_bin_trust_function_creators=1;
-- 执行
show variables like 'log_bin_trust_function_creators';
-- 显示结果为
| Variable_name          | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON  |

四、面试常见问题：

1、数据库事务的四个特性及含义（ACID属性）
原子性，一致性，隔离性，持久性
原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。
持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

并发事务处理带来的问题：

2、视图的作用，可以更改吗？
视图是虚拟的表，不包含任何列和数据，是为了简化复杂的sql查询，隐藏具体细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值

3.drop,delete与truncate的区别
drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。
TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）

4、数据库三大范式
第一范式就是无重复的域,确保每列保持原子性。

第二范式确保每列都和主键相关。

第三范式确保每列都和主键直接相关，而不是间接相关。

5、简述触发器、函数、视图、存储过程？
触发器：trigger触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。
eg：create trigger trigger_name
after/before insert /update/delete on 表名
for each row
begin
sql语句：（触发的语句一句或多句）
end

函数： 可以定义，也有内置函数如：sum、concat、round、COUNT(col)返回指定列中非NULL值的个数
视图：view 用于简化复杂的sql操作，
存储过程:procedure

6、索引
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引

    索引的弊端：
1占用磁盘空间
2对dml(插入、修改、删除)操作有影响，变慢
使用场景：
1在经常用where条件，查询数据的地方
2该字段的内容不是唯一的几个值
3字段的内容不需要经常变化，（如修改）
索引优点：
索引大大提高了查询速度，

. 列举 创建索引但是无法命中索引的8种情况

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
2.对于多列索引，不是使用的第一部分，则不会使用索引
3.like查询是以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？
InnoDB是基于索引来完成行锁