- sql的四种连接查询
内连接
inner join 或者 join
外连接
1.左连接left join 或者 left outer join
2.右连接 right jion 或者 right outer join
3.完全外连接 full join 或者 full outer join
建立新的数据库：
create database testjoin；
创建两个表：
person表：id，name，cardid
create table person(
id int ,
name varchar(20),
cardid int);
card表：id，name
create table card(
id int,
name varchar(20));
插入数据：
insert into card values(1,‘饭卡’);
insert into card values(2,‘建行卡’);
insert into card values(3,‘农行卡’);
insert into card values(4,‘工商卡’);
insert into card values(5,‘邮政卡’);
±-----±----------+
| id | name |
±-----±----------+
| 1 | 饭卡 |
| 2 | 建行卡 |
| 3 | 农行卡 |
| 4 | 工商卡 |
| 5 | 邮政卡 |
±-----±----------
insert into person values(1,‘张三’,1);
insert into person values(2,‘李四’,3);
insert into person values(3,‘王五’,6);
±-----±-------±-------+
| id | name | cardid |
±-----±-------±-------+
| 1 | 张三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
±-----±-------±-------+
两个表没有创建外键，所以王五的卡号 可以使6！
1，进行 inner join的查询：
内连查询就是，两张表的数据，通过某个字段相等，查询出相关记录的数据。
inner join 和join的效果差不多。！
select *from person inner join card on person.cardid=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardid | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
±-----±-------±-------±-----±----------+
2，left join（左外连接）
左外连接：会把左边表里的所有数据取出来，而右边表的数据，如果有相等的就显示出来，没有就为NULL。
select * from person left join card on person.cardid=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardid | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
±-----±-------±-------±-----±----------+
3，right join（右外连接）
右外连接查询，跟左外连接相反，右边的数据全部显示，左边有相等的显示出来，没有就等于NULL；
select * from person right join card on person.cardid=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardid | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
±-----±-------±-------±-----±----------+
4，full join全外连接
mysql不支持full join
要实现的话，就是把做连接和右连接拼起来！用union
select * from person left join card on person.cardid=card.id
union
select * from person right join card on person.cardid=card.id;
±-----±-------±-------±-----±----------+
| id | name | cardid | id | name |
±-----±-------±-------±-----±----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
| NULL | NULL | NULL | 2 | 建行卡 |
| NULL | NULL | NULL | 4 | 工商卡 |
| NULL | NULL | NULL | 5 | 邮政卡 |
±-----±-------±-------±-----±----------+
day4
mysql事务
- 事务就是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性。
举个例子，银行转账：
mysql如何控制事务的 - mysql默认是开启事务的（自动提交的）。
select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 1 |
±-------------+
默认事务开启的作用：当我们去执行一个sql语句的时候，效果会立即体现出来，不能回滚。举例说明：
create databse bank;
create table user(
id int primary key,
name varchar(20),
money int );
insert user values(1,‘a’,1000);
select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
事务回滚：撤销sql语句执行效果 rollback；
mysql> rollback;
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
结果显示并未回滚成功。一旦提交语句，就自动执行了。怎么办呢？
设置mysql 自动提交为false： set autocommit=0；
mysql> select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 0 |
±-------------+
这时候就可以通过rollback进行回滚了！
现在默认是不执行，想要这条语句马上执行不能回滚，就在后面执行：commit；
执行 commit；之后 上条语句不能被回滚！
手动提交数据！ commit；语句。
-
现在是默认自动执行！ 然后再要执行的语句之前先执行：begin；语句，这样的话 下一次就可以执行回滚了。
begin；手动开启事务！，start transaction;有同样的效果！
事务开启之后，一旦 commit 提交，就不可以回滚了！也就是当前这个事务在提交的时候就结束了。 -
事物的一些特征，ACID
A，事务的原子性，事务是最小的单位，不可以再分割。
C，一致性，同一事物中的sql语句，必须保证同时成功或者同时失败。
I，隔离性，事务1和事务2,之间具有隔离性的。
D，持久性，事务一旦结束（commit），就不可以返回（rollback）。
事务开启有三种方式： -
修改默认提交，set autocommit=0
-
begin;
-
start transaction;
事务提交：commit；
手动回滚：rollback；
- 事务的隔离性。
主要分为四种：
1，read uncommitted; 读未提交的
2，read committed;读已经提交的
3，repeatable read;可以重复读
4，serializable;串行化
1-read uncommitted;
如果有事务a和事务b
a事务对数据进行操作，在操作过程中，事务没有被提交，但是b可以看见a操作的结果！
mysql8.0 查看隔离级别：
如何修改隔离级别？
set global transaction isolation level read uncommitted;
查询：
select @@global.transaction_isolation;
mysql> select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| REPEATABLE-READ | mysql的默认隔离级别
±-------------------------------+
mysql> select @@transaction_isolation;
±------------------------+
| @@transaction_isolation |
±------------------------+
| REPEATABLE-READ |
±------------------------+
1 row in set (0.00 sec)
mysql> set global transaction isolation level read uncommitted;
mysql> select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| READ-UNCOMMITTED |
±-------------------------------+
如果在两个地方，都在进行操作，如果事务a开启之后，他的数据可以被其他事物读取到，但是s事务a可以回滚的。
这是在 read uncommitted隔离级别下：
这就会出现-脏读！：一个事物读取到了另一个事务没有提交的数据。
实际开发时不允许的！
2，read committed;读已经提交的
还是bank；里
先修改隔离级别。
小张查询了数据表，然后出去了一趟，这时候小王往数据库里添加了一条新的数据，并提交了。
小张回来计算平均数据，发现不对。是因为现在计算时已经把小王添加的数据计算进去了！
虽然在这个隔离级别下只能读到另一个事务提交的数据，但是还是出问题了。
读取同一个表的数据，发现前后不一致！这就是不可重复读现象！
3，repeatable read;可以重复读
先修改隔离级别。
小王开启一个事务 start 。。。；
小张也开启一个事务start。。。；
然后小王 插入一个数据，然后commit；
小王可以查到自己的数据。
但是小张查询没有这条数据，所以小张提交带有相同数据是会报错。
这就是幻读现象！
事务a和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到，就可以造成幻读！
4，serializable;串行化
先修改隔离级别为串行化！
小张开启一个事务 start。。。。；
小王开启一个事务start。。。。；
小张 加入数据。commit；
小王可以查询到。
小张再开启一个事务start。。；
小张插入数据就会 一直卡住。因为现在是串行化！必须等小王的事务提交才能下一个事物执行。
当一个表被另一个事务操作时，其他事务里面的写操作，是不可以进行的。进入排队状态，直到另一个事物结束之后，这边的写操作才会执行，在未超时的情况下！
串行化带来的问题就是，性能特差！
隔离级别越高，性能越差！
day4
数据库里的锁机制
封锁类型：
- 读写锁
a. 排它锁，简写为X锁，又称写锁。
b. 共享锁，简称为S锁，又称读锁。 两者都是行锁！
有以下两个规定：
1，一个事务对数据对象a加了排它锁，就可以对a进行读取和更新。加锁期间其他事务不能对a加任何锁。
2，一个事务对数据对象a加了共享锁，可以对a进行读取操作，但是不能进行更新操作。加锁期间其他事务能对a也能加共享锁，但是不能加排它锁。 - 意向锁
事务a锁住了表中的一行，事务b申请了整个表的写锁，如果b成功，这与事务a锁定一行产生冲突。
为解决这个问题，有了意向锁。就是事务a必须先申请表的意向共享锁，成功之后再申请一行的行锁。申请意向锁是数据库自动的，不需要我们手动操作。
所以这个冲突解决方法：
先判断表中是否被其他事务用表锁锁表。
发现表上有意向锁，说明表中的某写行被共享行锁锁定了，因此事务b的申请表的写锁会被阻塞。
一个事务执行数据更新的时候，插入 删除 修改数据库。数据库会自动对语句操纵的资源使用独占锁。独占锁不能与任何锁共存。
死锁：两个事务都获取了同一数据资源的共享锁，然后都要把锁升级为独占锁，但需要等待另一个事务解除共享锁才能升级为独占锁，这就造成了死锁
使用更新锁 避免死锁：更新锁和共享锁是兼容的，当一个事物执行update时，数据库会分配一个更新锁。当数据读取完毕后，执行更新琐时，会把更新锁升级为独占锁。
一个资源最多放置一把更新锁，这样当多个事务更新一个数据时，只有一个事务能获得更新锁，然后升级为独占锁。其他事务必须等待前一个事务结束后才能获得更新锁。这样就避免了死锁。
避免脏读：
我们需要在写操作时加锁，使读写分离。保证读数据时，数据不能被修改，写数据的时候，数据不能被读取。从而保证写的同时不能被另一个事物写和读。
具体操作：修改时加排它锁，直到事务提交后才释放。读取时加共享锁，这样在读取的时候，其他事务不能修改该数据，只能读取。如果之后有更新操作，就会转化为排它锁。其他事务就不能进来读写。
不可重复读：
读取数据时加共享锁，写数据时加排它锁，都是事务提交后才释放。读取的时候不允许其他事务修改该数据，不管数据在事务过程中读取多少次，数据都是一致的。避免了不可重复读。
解决：使用行级锁，锁定该行。事务a多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
幻读：
解决：使用表级锁，锁定整张表，事务a多次读取数据总量之后才释放该锁。这个时候才允许其他事务新增数据。
索引
索引是帮助mysql高效获取数据的数据结构。
是B+树实现的
B+树：
每个节点的指针上限为2d而不是2d+1。d是度！
内节点不存储data，只存储key。
叶子结点不存储指针。
一般在数据库系统或文件系统中使用的B+树都在经典的B+数基础上进行了优化，增加了顺序访问指针！（增加了指向相邻叶子结点的指针，为了提高区间访问的性能）
数据库索引一般存储在外存磁盘上，索引查找过程就会产生磁盘IO的消耗，所以评价一个数据结构作为索引的最重要指标就是：查找过程中磁盘IO操作次数的渐进复杂度。
数据库使用B+树而不用红黑树：
1.更少的查找次数，平衡树查找操作的时间复杂度与树高h相关，h=londN 底数d为每个节点的出度。红黑树出度为2，B+树的出度一般都很大，所以红黑树更高，查找的次数也就越多。
2.利用磁盘的预读性
利用磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次IO就可以完全载入。每次建立新节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对页的，就实现了一个node只需一次IO。
一般来说出度d非常大，因此h会很小（通常不超过3），所以搜索时IO次数一般不会超过3次。
- B+树索引
是大多数mysql存储引擎的默认索引类型，查找速度很快，因为只需要对树进行搜索即可。
因为B+树的有序性，还可以用于排序和分组。
适用于全键值、键值范围和键前缀查找
mtsql索引实现
MyISAM索引：叶节点的data域存放的是数据记录的地址。
主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。
myISAM的索引方式也叫非聚集索引。
索引文件和数据文件是分离的。索引文件仅保存数据记录的地址。
可以没有主键。
主索引和附注索引结构一样，但是主索引要求key唯一，附注索引可以重复。
InnoDB索引，实现方式与MyISAM方式截然不同。叫聚集索引
第一重大区别：InnoDB的数据文件本身就是索引文件。树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键。，所以InnoDB表数据文件本身就是主索引。
数据文件要按主键聚集，所以必须有主键（MyISAM可以没有）。一般会自动选择数据的列作为主键，如果没有这种列，就会生成一个隐含字段作为主键。
第二与MyISAM索引不同的是。InnoDB的辅助索引data域存储相应记录主键的值，而不是地址。
聚集索引十分高效，但是附注索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
索引使用策略和优化
主要分为结构优化和查询优化
联合索引和最左前缀原理
联合索引可以理解为多个字段创建的一个索引。
比如按照（a,b,c）创建一个联合索引，则索引记录会先按照a字段索引，再按照b，再按照c。
特点：
第一个字段一定是有序的
当地一个字段相等时，第二个字段又是有序的，当第二个字段相等时，第三个字段又是有序的。
就像查字典一样，现根据第一个字母，在根据第二个。
这就是所谓的最左前缀原理。
扫一扫
8463
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
