MySql学习笔记【1】-基础知识

　　1.TP6文档-数据库学习

　　记录一些文档里，容易忽略的知识。

　　（1）游标查询

　　　　如果你需要处理大量的数据，可以使用新版提供的游标查询功能，该查询方式利用了PHP的生成器特性，可以大幅减少大量数据查询的内存开销问题。

$cursor = Db::table('user')->where('status', 1)->cursor();
foreach($cursor as $user){
    echo $user['name'];
}

　　　　cursor方法返回的是一个生成器对象，user变量是数据表的一条数据（数组）。

　　（2）时间查询

　　　　使用whereTime方法

// 时间区间查询
Db::name('user')
    ->whereTime('birthday', 'between', ['1970-10-1', '2000-10-1'])
    ->select();
// 不在某个时间区间
Db::name('user')
    ->whereTime('birthday', 'not between', ['1970-10-1', '2000-10-1'])
    ->select();

// 查询两个小时内的博客
Db::name('blog')
	->whereTime('create_time','-2 hours')
    ->select();

　　　　查询某个时间区间

// 查询2017年上半年注册的用户
Db::name('user')
    ->whereBetweenTime('create_time', '2017-01-01', '2017-06-30')
    ->select();
    
// 查询不是2017年上半年注册的用户
Db::name('user')
    ->whereNotBetweenTime('create_time', '2017-01-01', '2017-06-30')
    ->select();

　　　　查询某年

//查询今年注册的用户
Db::name('user')
    ->whereYear('create_time')
    ->select();  

//查询去年注册的用户

Db::name('user')
    ->whereYear('create_time', 'last year')
    ->select();   

//查询某一年的数据使用

// 查询2018年注册的用户
Db::name('user')
    ->whereYear('create_time', '2018')
    ->select(); 

　　　　查询某月

//查询本月注册的用户
Db::name('user')
    ->whereMonth('create_time')
    ->select();
   
//查询上月注册用户
Db::name('user')
    ->whereMonth('create_time','last month')
    ->select();   

//查询2018年6月注册的用户
Db::name('user')
    ->whereMonth('create_time', '2018-06')
    ->select();    

　　　　查询某周

//查询本周数据
Db::name('user')
    ->whereWeek('create_time')
    ->select();    

//查询上周数据
Db::name('user')
    ->whereWeek('create_time', 'last week')
    ->select();    

//查询指定某天开始的一周数据
// 查询2019-1-1到2019-1-7的注册用户
Db::name('user')
    ->whereWeek('create_time', '2019-1-1')
    ->select();    

　　　　查询某天

//查询当天注册的用户
Db::name('user')
    ->whereDay('create_time')
    ->select();  
 
//查询昨天注册的用户
Db::name('user')
    ->whereDay('create_time', 'yesterday')
    ->select();   

//查询某天的数据使用
// 查询2018年6月1日注册的用户
Db::name('user')
    ->whereDay('create_time', '2018-06-01')
    ->select();    

　　　　时间字段区间比较

//可以支持对两个时间字段的区间比较

// 查询有效期内的活动
Db::name('event')
    ->whereBetweenTimeField('start_time', 'end_time')
    ->select();
上面的查询相当于

// 查询有效期内的活动
Db::name('event')
    ->whereTime('start_time', '<=', time())
    ->whereTime('end_time', '>=', time())
    ->select();

　　　　自定义时间查询规则

//自定义时间查询规则
//你可以通过在数据库配置文件中设置time_query_rule添加自定义的时间查询规则，

'time_query_rule'    =>    [
    'hour'    =>    ['1 hour ago', 'now'],
],

　　(3) 高级查询

　　　　　查询表达式：

EXP	表达式查询，支持SQL语法	whereExp
find in set	FIND_IN_SET查询	whereFindInSet

　　

 

 

　　　　

支持更复杂的查询情况 例如：

Db::name('user')->where('id','in','1,3,8')->select();
可以改成：

Db::name('user')->where('id','exp',' IN (1,3,8) ')->select();
exp查询的条件不会被当成字符串，所以后面的查询条件可以使用任何SQL支持的语法，包括使用函数和字段名称。

推荐使用whereExp方法查询

Db::name('user')->whereExp('id', 'IN (1,3,8) ')->select();

 

　　快捷方法

whereExists	EXISTS条件查询
whereNotExists	NOT EXISTS条件查询
whereExp	表达式查询
whereColumn	比较两个字段

 

　

 

 

 

 

下面举例说明下两个字段比较的查询条件whereColumn方法的用法。

查询update_time大于create_time的用户数据

Db::table('think_user')
    ->whereColumn('update_time','>','create_time')
    ->select();
生成的SQL语句是：

SELECT * FROM `think_user` WHERE ( `update_time` > `create_time` ) 
查询name和nickname相同的用户数据

Db::table('think_user')
    ->whereColumn('name','=','nickname')
    ->select();
生成的SQL语句是：

SELECT * FROM `think_user` WHERE ( `name` = `nickname` )
相同字段条件也可以简化为

Db::table('think_user')
    ->whereColumn('name','nickname')
    ->select();
支持数组方式比较多个字段

Db::name('user')->whereColumn([
    ['title', '=', 'name'],
    ['update_time', '>=', 'create_time'],
])->select();
生成的SQL语句是：

SELECT * FROM `think_user` WHERE ( `name` = `nickname` AND `update_time` > `create_time` ) 

 

　　　(4) JSON字段

如果你的user表有一个info字段是JSON类型的（或者说你存储的是JSON格式，但并非是要JSON字段类型），你可以使用下面的方式操作数据。

JSON数据写入
$user['name'] = 'thinkphp';
$user['info'] = [
    'email'    => 'thinkphp@qq.com',
    'nickname' => '流年',
];
Db::name('user')
    ->json(['info'])
    ->insert($user);

JSON数据查询
查询整个JSON数据：

$user = Db::name('user')
    ->json(['info'])
    ->find(1);
dump($user);
查询条件为JSON数据

$user = Db::name('user')
    ->json(['info'])
    ->where('info->nickname','ThinkPHP')
    ->find();
dump($user);
由于JSON字段的属性类型并不会自动获取，所以，如果是整型数据查询的话，可以设置JSON字段类型，例如：

$user = Db::name('user')
    ->json(['info'])
    ->where('info->user_id', 10)
    ->setFieldType(['info->user_id' => 'int'])
    ->find();
dump($user);
JSON数据更新
完整JSON数据更新

$data['info'] = [
    'email'    => 'kancloud@qq.com',
    'nickname' => 'kancloud',
];
Db::name('user')
    ->json(['info'])
    ->where('id',1)
    ->update($data);
单个JSON数据更新

$data['info->nickname'] = 'ThinkPHP';
Db::name('user')
    ->json(['info'])
    ->where('id',1)
    ->update($data);

 

 

索引

 

索引（Index）是帮助MySQL高效获取数据的数据结构。

 

索引的目的是减少磁盘I/O次数，加快查询效率。

 

索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。 这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现 高级查找算法 。

 

优点

 

降低数据库的IO成本，提高了查询速度

 

通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性

 

加速表和表之间的连接

 

减少查询中分组和排序的时间

 

缺点

 

创建索引和维护索引要耗费时间

 

索引需要占磁盘空间

 

降低更新表的速度

 

 

事务

 

ACID

 

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位

 

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

 

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

 

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

 

事务的并发问题

 

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

 

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致

 

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

 

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

 

4、脏写：如果事务 A 修改了 另一个 未提交 事务 B 修改过 的数 据，那就意味着发生了 脏写

 

事务隔离级别

 

事务隔离级别　　　　　　　　　　脏写　　脏读         不可重复读      幻读　　

 

读未提交（read-uncommitted）　     否　　  是            是                  是　　　

 

读已提交（read-committed）　　     否　     否            是                  是          

 

可重复读（repeatable-read）　　     否        否            否                  是          

 

串行化（serializable）　　              否        否            否                  否