mysql学习笔记

例子

什么是关系型数据库

关系型数据库优点

关系型数据库缺点

什么是非关系型数据库

优点

缺点

非关系的成本低

非关系的数据在缓存中，关系的数据在硬盘中，非关系的查找快

非关系的支持键值对，图片，文档，对象，集合等多种形式，而关系型的只支持基本数据类型

非关系的没有耦合性容易水平扩展，而关系的有连表查询的限制，很难扩展

非关系不适合持久储存，关系的适合

非关系不支持事务。关系支持。

[client]
# pipe=
# socket=MYSQL
port=3306

default-character-set

datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data
数据表的存放位置

default-storage-engine=INNODB
默认存储引擎

max_connections
最大同时连接数，会保留一个给管理员用

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
表示SQL模式的参数，通过这个参数可以设置检验SQL语句的严格程度

以及INNODB的一些设置参数

模糊查询 show tables LIKE '%u%'

alter table 表名 ADD（
列名 列明类型，
列名 列明类型
）；

alter table 表名 modify 列名 列类型；

alter table 表名 change 原列名 新列名 列类型；

alter table 表名 drop 列名；

alter table 原表名 rename to 新表名；

用户只能在指定的IP地址上登录

用户可以在任意IP地址上登录

权限、用户、数据库
给用户分派在指定数据库上的指定权限

给用户分派指定数据库上的所有权限

撤销指定用户在指定数据库上的指定权限

查看指定用户的权限

没有给出列名，则等同插入null
列值必须与列名顺序对应

没给出要插入的列，表示插入所有列
值的个数必须是列的个数
值的顺序，必须与表列的顺序相同

条件可选，必须是一个boolean类型的值或者表达式

运算符：=、！=、<>、>、<、>=、<=、between...and、in（...）、is null、is not null、not、or、and

1、字段（列）控制

2、条件控制

select * from emp order by sal asc,comm desc,empno asc;

关键字

注意：1、多列排序，先排前边的，如果分出胜负则没有后边的事了，如果没有，则按后边的排序接着排
2、limit关键字若有，则在其之后

聚合函数用来做某列的纵向运算 比如统计总人数

select count（*） 总人数，sum（sal） 工资总和，max（sal） 最高工资，min（sal） 最低工资，avg（sal） 平均工资 from emp；

1、count

2、max（列名）

3、min（列名）

4、sum（sal）

5、avg（sal）

把记录使用某一列进行分组，然后查询组信息  比如按部门分组，查询每个部门的人数

select deptno,count(*) from emp group by deptno;

关键字：group by 分组依据列 having 分组后条件

查询关键字  select、from、where、group by、having、order by

书写顺序

执行顺序

执行计划

用来限定查询结果的起始行，以及总行数

查询起始行第一行，查询5行数据

一般用于分页查询

source sql脚本路径

若采用该方式非常便于设置联合主键
在该示例中，将classid和studentid定义为联合主键

在该示例中，先创建了表，然后利用ALTER语句设置id字段为主键.

CREATE TABLE student(
id int PRIMARY KEY,
name varchar(50) NOT NULL,
gender varchar(10)
);

CREATE TABLE student(
id int primary key,
name varchar(50) unique
);

例如 部门、员工都是系统的实体，概念模型中的实体，最后都会成为java中的类、数据库中的表
实体类中存在关系：一对一，一对多，多对多

谁少谁是主，谁多谁是从

CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)

ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

alter table 从表名 drop foreign key 外键名；

wid与hid相同，都是主键保证了唯一，一对一
从表的主键就是外键

内连接

外连接

where后作为条件存在

from后作为表存在（多行多列，一般需要起别名）

单行单列

多行单列

单行多列

多行多列

ROLLBACK语句只能针对未提交的事务执行回滚操作，已提交的事务是不能回滚的。

(读未提交)是事务中最低的级别。
在该级别下的事务可以读 取到另一个事务中未提交的数据也被称为脏读(Dirty Read)。
由于该级别太低，所以在实际开发中避免不了任何情况，所以极少使用。

大多数的数据库管理系统(例如Oracle)的默认隔离级别都是READ COMMITTED(读提交)。
该级别下的事务只能读取其它事务已经提交的内容，可以避免脏读但不能避免重复读和幻读的情况。
重复读就是在事务内重复读取别的线程已经提交的数据的时读取的结果不一致。导致该问题的原因是查询的过程中其它事务做了更新操作。
幻读又被称为虚读，是指在一个事务内两次查询中数据条数不一致。导致该问题的原因是查询的过程中其它的事务做了添加操作。

(可重复读)是MySQL默认的事务隔离级别。它可以避免脏读和不可重复读。
但理论上，该级别会出现幻读的情况。不过，MySQL的存储引擎通过多版本并发控制机制解决了该问题，因此该级别是可以避免幻读的。

(可串行化)是事务的最高隔离级别，它会强制对事务进行排序，使之不会发生冲突，从而解决脏读、幻读、重复读的问题。
但是，该级别可能导致大量的超时现象和锁竞争，实际应用中很少使用。

在该存储过程中我们期望依据传入的指定年纪查询出符合条件的学生；详解如下：

MySQL默认以;为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理从而造成编译过程会报错。所以需要事先用DELIMITER //声明当前段分隔符，让编译器把两个//之间的内容当做存储过程的代码。在编写完存储过程之后利用DELIMITER ;把分隔符还原为默认分隔符;。DELIMITER也可以指定其他符号作为结束符。需要格外注意的是：DELIMITER与要设定的结束符之间一定要有一个空格，否则设定无效。
利用CREATE PROCEDURE 存储过程名称(参数)创建存储过程，即示例代码：CREATE PROCEDURE procedureHelloWorld(IN sage INT)
存储过程的过程体以BEGIN开始以END 结束

利用call 存储过程名(参数);调用存储过程，即示例代码：call procedureHelloWorld(15);类似地，可利用DROP PROCEDURE 存储过程名;删除存储过程。

IN表示输入参数，表示该参数的值必须在调用存储过程时指定

OUT表示输出参数，可在存储过程内改变该值并将其返回

INOUT表示输入输出参数，可在调用存储过程时指定该参数并在存储体中改变该值并将其返回

在编写存储过程中有时需要使用变量保存数据处理过程中的值。这些变量的作用范围为BEGIN…END，语法如下：

在定义变量之后，可使用SET为变量赋值或者修改变量的默认值

变量使用示例

语法

第一种语法格式

第二种语法格式

子主题

-- 创建视图
DROP VIEW IF EXISTS view_student;
CREATE VIEW view_student(m,c,s) AS SELECT math, chinese,math+chinese FROM student;
-- 查看视图
SELECT * FROM view_student;

-- 创建视图
DROP VIEW IF EXISTS view_student_studentInfo;
CREATE VIEW view_student_studentInfo (id,name,province,fname,mname)
AS
SELECT student.sid, student.sname,studentInfo.province,studentInfo.fatherName,studentInfo.motherName
FROM student,studentInfo WHERE student.sid=studentInfo.sid;
-- 查看视图
SELECT * FROM view_student_studentInfo;

如果存在

分类注释中写明

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (字段名 [(长度)] [ASC|DESC]);

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX
ON 表名 (字段名 [(长度)] [ASC|DESC]);

子主题

子主题

select GROUP_CONCAT(SCHEMA_NAME) from information_schema.SCHEMATA where SCHEMA_NAME like '%库名关键字%'

查询所有库学生表的字段并集

查询字段

SELECT
    TABLE_SCHEMA AS '数据库',
    TABLE_NAME AS '表名',
    column_name AS '字段名称',
    COLUMN_TYPE AS '字段类型',
    IS_NULLABLE AS '是否为空',
   COLUMN_DEFAULT AS '默认值',
   column_comment AS '字段备注'
FROM
   information_schema. COLUMNS
WHERE
    table_name = 'yyd_elife_city'
OR table_name = 'yyd_elife_column_list'
OR table_name = 'yyd_elife_coupon_list'
OR table_name = 'yyd_elife_goods_label_list'
OR table_name = 'yyd_elife_member_extend'
OR table_name = 'yyd_biz_content_log'
OR table_name = 'yyd_e_refund'
OR table_name = 'yyd_e_statistic_goods'
OR table_name = 'yyd_e_statistic_ip'
OR table_name = 'yyd_e_statistic_main'

-- 不存在某表的数据库
SELECT
 *
FROM
 information_schema. COLUMNS
WHERE
 NOT EXISTS (
-- 存在某表的数据库
  SELECT
   *
  FROM
   information_schema. COLUMNS
  WHERE
   TABLE_NAME = 'module_enroll_user_info'
  GROUP BY
   TABLE_SCHEMA
 )
GROUP BY
 TABLE_SCHEMA;

-- 存在某个字段 
SELECT
   *
  FROM
   information_schema. COLUMNS
  WHERE
   TABLE_NAME = 'module_enroll_user_info'
AND COLUMN_NAME = 'site_code'
and TABLE_SCHEMA = 'risen'


SELECT
   *
  FROM
   information_schema. COLUMNS
  WHERE
   TABLE_NAME = 'module_enroll_user_info'
AND COLUMN_NAME = 'fk_enroll_core_id'
and IS_NULLABLE = 'NO';

SELECT
   *
  FROM
   information_schema. COLUMNS
  WHERE
   TABLE_NAME = 'module_enroll_user_info'
AND COLUMN_NAME = 'fk_enroll_step_id'
and IS_NULLABLE = 'NO';

C:\ProgramData\MySQL\MySQL Server 5.7\Data\test

文件说明

索引文件和数据文件是分离的

索引文件和数据文件是在一起的

对索引的key进行一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+树索引更高效
仅能满足"=" , "IN" ,不支持范围查询
hash冲突问题

非叶子节点不存储data,只存储,引(冗余),可以放更多的索引
叶子节点包含所有索引字段
叶子节点用指针连接,提高区间问的性能

范围查找（B+tree通过指针查找，MySQL优化了，改为双箭头）

1）simple：简单查询。查询不包含子查询和union

2）primary：复杂查询中最外层的 select

3）subquery：包含在 select 中的子查询（不在 from 子句中）

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

用这个例子来了解 primary、subquery 和 derived 类型

5）union：在 union 中的第二个和随后的 select

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

mysql> explain select min(id) from film;

mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system

mysql> explain extended select * from (select * from film where id = 1) tmp;

mysql> show warnings;

primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

1. 简单 select 查询，name是普通索引（非唯一索引）

2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，
这里使用到了film_actor的左边前缀film_id部分。

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

mysql> explain select * from actor where id > 1;

扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

mysql> explain select * from film;

即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

mysql> explain select * from actor;

举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。
通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

1）Using index：使用覆盖索引

2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4）Using temporary：mysql需要创建一张临时表来处理查询。
出现这种情况一般是要进行优化的，首先是想到用索引来优化。

5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。
这种情况下一般也是要考虑使用索引来优化的。

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

select * from test where date_format(create_time,'%Y-%m-%d') 
between '2018-07-30' and '2018-07-31';

select date_format(operateDate,'%Y-%m-%d'),count(id) 
from t_log
where datediff(now(),operateDate)<=30
GROUP BY date_format(operateDate,'%Y-%m-%d');

类型 ：datetime
DEFAULT值 ：CURRENT_TIMESTAMP

类型 ：timestamp
DEFAULT值 ：CURRENT_TIMESTAMP

select * from tablename where time >='2017-1-1'  and mod(second(real_time),2)=0

mysql8.0 引入了新特性 caching_sha2_password；这种密码加密方式客户端不支持；
客户端支持的是mysql_native_password 这种加密方式；

我们可可以查看mysql 数据库中user表的 plugin字段；

可以使用命令将他修改成mysql_native_password加密模式

flush privileges;

匹配中文