Java面试题-数据库

数据库的存储引擎以及锁机制

锁的存在是为了数据的一致性，mysql在修改数据层面是支持并发修改的，那么在多个线程同时修改一个数据时产生的线程安全问题。

我们知道MySQL主要有三个存储引擎，分别是memory、myisam、innodb，每个存储引擎都有不同的锁方式，分别是表级锁、行级锁。

表锁：顾名思义，就是指的是将整个表锁住，锁住后，任何人都不能往表中添加、修改或者删除数据；这是一种独占思想，也就是当表被某一个线程独占后，在独占期间，这个线程可以对表做任何操作，而别的线程会堵塞，无法操作，只能等待。

行锁：顾名思义，就是指的是锁行数据，表里面的数据是成千上万条，当锁住一行记录时，可以修改这条数据，修改之后，再放入表中，这就是行锁。

memory：内存级别的表，不支持持久化存储，断电丢失数据，只支持表锁。

myisam：只支持表锁，并且也不支持事务，所有的读写操作都是串行。不支持设置外键，这是它重要的区别。

myisam在执行查询语句之后，会自动给涉及的所有表加读锁，执行更新操作前，会自动给涉及的表加写锁，这个过程不需要用户干预，因此用户一般不需要使用命令来显示加锁。

（1）当前线程读，其他线程读：当加上读锁后，所有会话都可以读取A表数据，并且不会受影响，也不会堵塞。

（2）当前会话先读后写：一旦给当前会话加上共享读锁后，就不能有写操作，否则会报错。写操作（insert,update,delete）。

（3）当前会话读，其他线程写：加上共享锁后，其他线程写入会进入堵塞状态，待解锁后可进行写操作。

（4）会话1给A表加读锁，会话1在查询其他表会报错：会话1给A表加读锁后，再读其他表会报错，要先释放A表的读锁才可以读其他表，这是一个比较特别的知识点。

（5）只锁当前会话的读锁（read local）：表示会话可读，不可写，其他会话可写可读。但是会有并发插入问题，也就是说，在当前会话开启read lock之后，在别的会话插入的数据，当前会话查询不到。

由于myisam是表锁，也就是表独占。那么当加写锁后，当前线程可以独占这个表，进行任何操作，而其他线程的读写操作都会堵塞。

**innodb：**支持表锁和行锁。innodb的锁分为共享锁（S锁，读锁）、排他锁（X锁，写锁）、意向共享锁、意向排他锁、自增锁。

加共享锁：

（1）事务1读，其他事务也可以读：加了共享锁后，所有线程都可以读数据。

（2）事务1读，事务2写会堵塞：当开启读锁后，当前事务可以读数据，其他事务也可以写数据，但是需要等事务1释放锁后才可以写，所以当其他事务进行写的时候会进入阻塞状态。

（3）事务A写会报错：当开启读锁后，当前事务只能读事务，而不能写数据，写数据会报错。

加排他锁：

（4）事务A获取锁，其他的事务都会进行阻塞等待。

<u>需要注意的是，innodb是支持行锁的，行锁是通过给索引项加锁实现的，所以你的表里面必须要有索引，否则就会退化成表锁；另外，insert插入数据的时候因为是针对整张表插入，而不是针对某一行数据，所以insert也是表锁。</u>

意向共享锁：事务准备给数据行加入共享锁，也就是说一个数据行在加共享锁之前必须先取得意向共享锁。

意向排他锁：事务准备给数据行加入排他锁，也就是说一个数据行在加排他锁之前必须先取得意向排他锁。

<u>意向锁是innodb存储引擎操作数据之前自动加的，不需要用户干预。</u>

自增锁是保证每次都自增的。

什么是事务: 在应用程序中一个完整的逻辑，其中包含有多个小的单元，每个小的单元相当于数据库的crud的操作。事务要求这些小的单元要么同时执行，要么同时都不执行。并且事务的执行前后保持一致。

事务的四个特性（ACID）

（1）原子性（Actimicity）：事务是原子操作，要么同时修改，要么同时回滚；要么同时执行，要么同时都不执行。

（2）一致性（Consistent）：事务执行前后保持一致。

（3）隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作时影响独立的环境之下。（即如果有多个用户并发访问数据库，数据库会为这些用户单独开事务，并且事务之间互不干扰。）

（4）持久性（Durable）：也称永久性，指一个事务一旦提交，则持久化的保存在数据库中，不会被回滚。接下来的其他操作或故障不应该对其执行结果有任务影响。

事务带来的危险

（1）脏读：就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

（2）幻读：指当事务不是独立执行时发生的一种现象，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

（3）不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该一数据。那么在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

事务的隔离级别

1）Readuncommitted

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。 缺点：出现脏读 2）Readcommitted

读已提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。解决脏读的问题，但是会引起不可重复读。 3）Repeatableread

可重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。可以解决不可重复读，但是会引起幻读。
4）Serializable

串行化，Serializable是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读，不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

在 MySQL数据库中，支持上面四种隔离级别，默认的为 Repeatableread(可重复读)；而在 Oracle数据库中，只支持 Serializable(串行化)级别和Readcommitted(读已提交)这两种级别，其中默认的为 Readcommitted。

ACID靠什么保证的？

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql；

C一致性由其他三大特性保证、程序代码要保证业务上的一致性；

I隔离性由MVCC来保证；

D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复

InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态。
如果前面 prepare 成功，binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么
InnoDB 事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)

redolog的刷盘会在系统空闲时进行

索引

（1）索引是什么？

索引（Index）是帮助 MySQL高效获取数据的数据结构。索引就是加快检索表中数据的方法，就是把无序的数据变成有序的查询。MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、组合索引、全文索引

（2）索引的分类？

主要分为聚簇索引（聚集索引）和非聚簇索引（辅助索引）。

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息。

聚簇索引有：【叶子节点保存所有的列值，即一整行的数据】

主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

如果表设置了主键，则主键就是聚簇索引
如果表没有主键，则会默认第一个NOT NULL，且唯一（UNIQUE）的列作为聚簇索引
以上都没有，则会自动创建一个6字节大小的指针，而且是隐藏，作为聚簇索引

使用聚集索引的好处有：

它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录。
范围查询，即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。

但是：

-- 聚集索引可能会降低数据的插入速度

非聚簇索引有：【非聚集索引叶子节点的值为索引列+主键】

普通索引：是最基本的索引，它没有任何限制。

唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 where语句的参数匹配。

叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。

由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。

索引的优点：

创建唯一索引，保证数据库表中每一行数据的唯一性大大加快数据的检索速度，这也是创建索引的最主要的原因。

加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

通过使用索引，可以在查询的过程中使用优化隐藏器【一般是用在聚集索引】，提高系统的性能。

索引的缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占物理空间，除了数据占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

（3）索引的原理？

索引的底层数据结构使用了B+树。事实上索引的本质就是不断缩小获取数据的筛选范围，找出我们想要的结果。同时把随机的事件变成顺序的事件，也就是说有了这种索引机制，我们就可以总是用同一种查找方式来锁定数据。

说白了索引就是为了提高查询的效率。

（4）索引的失效？

1.where条件使用函数操作；

2.使用or操作至少有一个字段没有索引；

3.where条件使用!=，null等；

4.前导模糊查询；

5.数据区分度不大的字段不宜使用索引

6.违背了最左前缀原则； b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

（5）索引回表？

聚簇索引查询会很快，因为可以直接定位到行记录。

而非聚簇索引（例如普通索引），需要扫码两遍索引树。

（1）先通过普通索引定位到主键值id=5；【因为普通索引的叶子结点存储是键值对，不是一行的信息】

（2）再通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

即从非聚集索引上找到对应的主键值然后到聚集索引上查找对应记录的过程为回表

-- 同样的解释
-- 当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得相应的主键索引的主键，然后再通过主键索引来找到一个完    整的行记录。
-- 举例来说，如果在一棵高度为3的辅助索引树中查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需    要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。

如何避免索引回表呢？

将要所查的字段成为联合索引，或者只查字段只有是加了索引的。

Mysql慢查询该如何优化？

检查是否⾛了索引，如果没有则优化SQL利⽤索引
检查所利⽤的索引，是否是最优索引
检查所查字段是否都是必须的，是否查询了过多字段，查出了多余数据（即出现回表，需要消耗一定时间）
检查表中数据是否过多，是否应该进⾏分库分表了
检查数据库实例所在机器的性能配置，是否太低，是否可以适当增加资源

mysql中的innodb索引为什么使用B+树?

根据B类树的特点，构造一个多阶的B类树，然后尽量多的在结点上存储相关的信息，保证层数尽量的少，以便后面我们可以更快的找到信息，磁盘的I/O操作也少一些，而且B类树是属于多路搜索树，每个结点到叶子结点的高度都是相同，这也保证了每个查询是稳定的。

B树和B+树的区别，以一个m阶树为例。

关键字的数量不同；B+树中分支结点有m个关键字，其叶子结点也有m个，其关键字只是起到了一个索引的作用，但是B树虽然也有m个子结点，但是其只拥有m-1个关键字。
存储的位置不同；B+树中的数据都存储在叶子结点上，也就是其所有叶子结点的数据组合起来就是完整的数据，但是B树的数据存储在每一个结点中，并不仅仅存储在叶子结点上。
分支结点的构造不同；B+树的分支结点仅仅存储着关键字信息和儿子的指针（这里的指针指的是磁盘块的偏移量），也就是说内部结点仅仅包含着索引信息。
查询不同；B树在找到具体的数值以后，则结束，而B+树则需要通过索引找到叶子结点中的数据才结束，也就是说B+树的搜索过程中走了一条从根结点到叶子结点的路径。
优势不同：由于B+树所有的data域都在叶子节点，而且B+树的索引是双向链表，可以进行区间访问，批量得到数据。

数据库的规范化

第一范式：

第一范式的目标是确保每列的原子性:如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）。

第一范式（1NF）要求数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。

若某一列有多个值，可以将该列单独拆分成一个实体，新实体和原实体间是一对多的关系。

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份.城市.详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。

第二范式：

首先满足第一范式，并且表中非主键列不存在对主键的部分依赖。第二范式要求每个表只描述一件事情。

满足第二范式（2NF）必须先满足第一范式（1NF）。

第二范式要求实体中每一行的所有非主属性都必须完全依赖于主键；即：非主属性必须完全依赖于主键。

完全依赖：主键可能由多个属性构成，完全依赖要求不允许存在非主属性依赖于主键中的某一部分属性。

若存在哪个非主属性依赖于主键中的一部分属性，那么要将发生部分依赖的这一组属性单独新建一个实体，并且在旧实体中用外键与新实体关联，并且新实体与旧实体间是一对多的关系。

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式：

第三范式定义是，满足第二范式，并且表中的列不存在对非主键列的传递依赖。除了主键订单编号外，顾客姓名依赖于非主键顾客编号。

满足第三范式必须先满足第二范式。

第三范式要求：实体中的属性不能是其他实体中的非主属性。因为这样会出现冗余。即：属性不依赖于其他非主属性。

如果一个实体中出现其他实体的非主属性，可以将这两个实体用外键关联，而不是将另一张表的非主属性直接写在当前表中。

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

count(*) 和 count(1)和count(列名)区别

（1）执行效果上：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL。

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL。

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

（2）执行效率上：

列名为主键，count(列名)会比count(1)快；

列名不为主键，count(1)会比count(列名)快；

如果表多个列并且没有主键，则 count（1）的执行效率优于 count（*）。

如果有主键，则 select count（主键）的执行效率是最优的。

如果表只有一个字段，则 select count（*）最优。

关于mysql的为外键设置删除时和更新时的值

在使用Navicat for mysql设计表时，在设置外键的时候，删除时和更新时两列有四个值可以选择：CASCADE、NO ACTION、RESTRICT、SET NULL，自己全亲自试了一遍，它们的区别如下：

**CASCADE：**父表delete、update的时候，子表会delete、update掉关联记录； **SET NULL：**父表delete、update的时候，子表会将关联记录的外键字段所在列设为null，所以注意在设计子表时外键不能设为not null； **RESTRICT：**如果想要删除父表的记录时，而在子表中有关联该父表的记录，则不允许删除父表中的记录； **NO ACTION：**同 RESTRICT，也是首先先检查外键；

SQL的五大约束

主键约束、唯一约束、外键约束、默认约束、非空约束

数据库的保护

安全性控制、完整性控制、并发性控制、数据的恢复

varchar和char的区别：

区别一：varchar是不定长的存储，char是定长的存储。【如：varchar（20）不一定是存储空间大小是20，根据实际存储字符的大小来存储；而char(20)一开始就划分的存储空间，如果存储的字符小于设定的存储大小，则后面以空格代替】

区别二：char的存储速度要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。

区别三：取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

区别四：char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。

group by 、having、 order by的区别？

Group by分组查询，从字面意义上理解就是根据“By”指定的规则对数据进行分组；一般是需要进行一些数据统计的时候，比如在一组数据中求得最大值和最小值，平均值等，可能需要这个分组；

having ：Having跟where的功能差不多，只不过他是在分完组之后，再对分组数据进行过滤。

Order by：排序，后面跟上要排序的字段，默认为升序（asc），想要降序需要在最后加desc。还可以采用多个字段排序，需要用”,”把字段隔开，如果根据第一个字段排序重复了，会根据第二个字段排序。

一般执行的顺序是 group by - having - order by

truncate table 表名

truncate table 表名速度快,而且效率高,因为: TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。

DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

TRUNCATE TABLE 不能用于参与了索引视图的表。

基础SQL


create table Student(Name VARCHAR(20),Score INT,Cource varchar(200));

insert into Student VALUES('张三',89,'数据库');
insert into Student VALUES('张三',92,'java');
insert into Student VALUES('李四',69,'数据库');
insert into Student VALUES('张五',79,'数据库');
insert into Student VALUES('张五',99,'java');

insert into Student VALUES('张刘',69,'java');
drop table Student;

-- 查询有几门课程分数是超过80的学生
select Name ,count(*) from Student where Score>80 GROUP BY Name;

-- 查询每个学生有多少门课，分数超过80;
-- 关键函数:count(if (字段判断，true,null);
-- 该函数中if的字段判断就是你对指定字段的判断内容，（可以用大于小于，等于，between ...and等判断符号，
-- 判断成立时为true 则自动加1，反之不成立时为null，count（null） 为不统计数，当全都不成立时，默认值为0；
-- 答案一
select  Name,count(if (Score > 80,true,null)) as 'count(课程)' from Student GROUP BY Name;
-- 答案二
select Name,sum(case when Score > 80 then 1 else 0 end ) from Student GROUP BY Name;
-- 答案三
select Name,count(case when Score > 80 then 1 else null end)  from Student GROUP BY Name;


-- 将男的改成女的，将女的改成男的
update yfx_email set sex = 
case WHEN sex = 'f' THEN 'm'
	 WHEN sex = 'm' THEN 'f'
else sex END;


-- 单表查询
create table t1(
id int not null unique auto_increment,
姓名 varchar(20) not null,
性别 enum('男','女') not null default '男', #大部分是男的
年龄 int(3) unsigned not null default 28,
入职日期 date not null,
部门 varchar(50),
职位描述 varchar(100),
薪资 double(15,2),
部门办公室 int, #一个部门一个屋子
部门编号 int
);


insert into t1(姓名,性别,年龄,入职日期,部门,薪资,部门办公室,部门编号) values
('李三胖','男',18,'20170301','办公室主任',7300.33,401,1), #以下是教学部
('李狗蛋','男',78,'20150302','老师',1000000.31,401,1),
('老皮','男',81,'20130305','老师',8300,401,1),
('猴子','男',73,'20140701','老师',3500,401,1),
('catdexin','男',28,'20121101','老师',2100,401,1),
('那月','女',18,'20110211','老师',9000,401,1),
('muki','男',18,'19000301','老师',30000,401,1),
('小虫','男',48,'20101111','老师',10000,401,1),

('歪歪','女',48,'20150311','招生办',3000.13,402,2),#以下是销售部门
('丫丫','女',38,'20101101','招生办',2000.35,402,2),
('丁丁','女',18,'20110312','招生办',1000.37,402,2),
('星星','女',18,'20160513','招生办',3000.29,402,2),
('格格','女',28,'20170127','招生办',4000.33,402,2),
('张鹏','男',28,'20160311','运营',10000.13,403,3), #以下是运营部门
('shafufu','男',18,'19970312','运营',20000,403,3),
('憨批','女',18,'20130311','运营',19000,403,3),
('瓷锤','男',18,'20150411','运营',18000,403,3),
('王大头','女',18,'20140512','运营',17000,403,3)
;

-- 年龄在20 到35岁之间的员工
select 姓名  from t1 where 年龄 between 20 and 35;

-- 查询暂时没有部门的员工
select 姓名 from t1 where 部门 is  null;

select 部门,count(*),avg(年龄) from t1 GROUP BY 部门;

-- 查询至少有2个员工的部门
select 部门 ,count(*) from t1 GROUP BY 部门 HAVING count(*)>1; 

-- limit 限制行数 
select * from t1 LIMIT 10;


-- 开始多表查询
create table department(
id int,
name varchar(20) 
);

create table staff(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

insert into department values
(200,'荣耀王者部'),
(201,'钻石部'),
(202,'铂金队'),
(203,'黄金队');

insert into staff(name,sex,age,dep_id) values
('亚瑟','male',38,200),
('貂蝉','female',26,201),
('吕布','male',38,201),
('昭君','female',28,202),
('程咬金','male',118,200),
('猴子','female',16,204)
;

-- 内连接
select * from staff s,department d where s.dep_id = d.id;

-- 左外连接
select * from staff s LEFT JOIN department d on s.dep_id = d.id;

-- 右外连接
select * from staff s right join department d on s.dep_id = d.id;

-- 全外连接
select s.id,s.name,sex,age,s.dep_id,d.id,d.name from staff s LEFT JOIN department d on s.dep_id = d.id
UNION
select * from staff s RIGHT JOIN department d on s.dep_id = d.id;


-- 插入 高级sql——视图
-- 视图  利用上述全外连接来创建视图

create view view_staff(员工id,姓名,性别,年龄,所属部门id,部门id,所属部门) as 
select s.id,s.name ,s.sex,s.age,s.dep_id,d.id,d.name from staff s LEFT JOIN department d on s.dep_id = d.id
UNION
select * from staff s RIGHT JOIN department d on s.dep_id = d.id;

select * from view_staff;

-- 查询数据库中的所有视图
select table_name from information_schema.`TABLES` where TABLE_SCHEMA = 'a' and TABLE_TYPE='view';

-- 删除视图
drop view view_staff;


-- 开始子查询
-- 查询跟吕布同一部门的员工

select s.* ,d.name from Staff s,department d where s.dep_id = 
(select dep_id from Staff where name = '吕布') and s.dep_id = d.id;

-- 查询跟亚瑟同一性别同一部门的员工
select s.* from staff s where (s.sex,s.dep_id)=
(select sex,dep_id from Staff s where name = '亚瑟');


-- 查询哪个部门没有员工
select * from department where id not in 
(select dep_id from Staff);


-- 虚拟表
select s.id,temp.name,temp.sex from Staff s ,(select id, name ,sex from Staff ) temp 
where s.id = temp.id;


-- 教师表
CREATE TABLE teacher(
    tno INT NOT NULL PRIMARY KEY,
    tname VARCHAR(20) NOT NULL
);

INSERT INTO teacher(tno,tname)VALUES(1,'张老师');
INSERT INTO teacher(tno,tname)VALUES(2,'王老师');
INSERT INTO teacher(tno,tname)VALUES(3,'李老师');
INSERT INTO teacher(tno,tname)VALUES(4,'赵老师');
INSERT INTO teacher(tno,tname)VALUES(5,'刘老师');
INSERT INTO teacher(tno,tname)VALUES(6,'向老师');
INSERT INTO teacher(tno,tname)VALUES(7,'李文静');
INSERT INTO teacher(tno,tname)VALUES(8,'叶平');

-- 学生表
CREATE TABLE student1(
    sno INT NOT NULL PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    sage DATETIME NOT NULL,
    ssex CHAR(2) NOT NULL
);

INSERT INTO student1(sno,sname,sage,ssex) VALUES(1,'张三','1980-1-23','男');
INSERT INTO student1(sno,sname,sage,ssex) VALUES(2,'李四','1982-12-12','男');
INSERT INTO student1(sno,sname,sage,ssex) VALUES(3,'张飒','1981-9-9','男');
INSERT INTO student1(sno,sname,sage,ssex) VALUES(4,'莉莉','1983-3-23','女');
INSERT INTO student1(sno,sname,sage,ssex) VALUES(5,'王弼','1982-6-21','男');
INSERT INTO student1(sno,sname,sage,ssex) VALUES(6,'王丽','1984-10-10','女');
INSERT INTO student1(sno,sname,sage,ssex) VALUES(7,'刘香','1980-12-22','女');

-- 课程表
CREATE TABLE course(
    cno INT NOT NULL PRIMARY KEY,
    cname NVARCHAR(20) NOT NULL,
    tno INT NOT NULL
);

-- 添加外键
ALTER TABLE course
ADD CONSTRAINT fk_course_teacher
FOREIGN KEY (tno) REFERENCES teacher (tno);

INSERT INTO course(cno,cname,tno) VALUES(1,'企业管理',3);
INSERT INTO course(cno,cname,tno) VALUES(2,'马克思',1);
INSERT INTO course(cno,cname,tno) VALUES(3,'UML',2);
INSERT INTO course(cno,cname,tno) VALUES(4,'数据库',5);
INSERT INTO course(cno,cname,tno) VALUES(5,'物理',8);

-- 创建成绩表
CREATE TABLE sc(
    sno INT NOT NULL,
    cno INT NOT NULL,
    score INT NOT NULL
);

ALTER TABLE sc
ADD CONSTRAINT fk_sc_course
FOREIGN KEY (cno) REFERENCES course (cno);

ALTER TABLE sc
ADD CONSTRAINT fk_sc_student1
FOREIGN KEY (sno) REFERENCES student1 (sno);

INSERT INTO sc(sno,cno,score)VALUES(1,1,80);
INSERT INTO sc(sno,cno,score)VALUES(1,2,86);
INSERT INTO sc(sno,cno,score)VALUES(1,3,83);
INSERT INTO sc(sno,cno,score)VALUES(1,4,89);

INSERT INTO sc(sno,cno,score)VALUES(2,1,50);
INSERT INTO sc(sno,cno,score)VALUES(2,2,36);
INSERT INTO sc(sno,cno,score)VALUES(2,3,43);
INSERT INTO sc(sno,cno,score)VALUES(2,4,59);

INSERT INTO sc(sno,cno,score)VALUES(3,1,50);
INSERT INTO sc(sno,cno,score)VALUES(3,2,96);
INSERT INTO sc(sno,cno,score)VALUES(3,3,73);
INSERT INTO sc(sno,cno,score)VALUES(3,4,69);

INSERT INTO sc(sno,cno,score)VALUES(4,1,90);
INSERT INTO sc(sno,cno,score)VALUES(4,2,36);
INSERT INTO sc(sno,cno,score)VALUES(4,3,88);
INSERT INTO sc(sno,cno,score)VALUES(4,4,99);

INSERT INTO sc(sno,cno,score)VALUES(5,1,90);
INSERT INTO sc(sno,cno,score)VALUES(5,2,96);
INSERT INTO sc(sno,cno,score)VALUES(5,3,98);
INSERT INTO sc(sno,cno,score)VALUES(5,4,99);

INSERT INTO sc(sno,cno,score)VALUES(6,1,70);
INSERT INTO sc(sno,cno,score)VALUES(6,2,66);
INSERT INTO sc(sno,cno,score)VALUES(6,3,58);
INSERT INTO sc(sno,cno,score)VALUES(6,4,79);

INSERT INTO sc(sno,cno,score)VALUES(7,1,80);
INSERT INTO sc(sno,cno,score)VALUES(7,2,76);
INSERT INTO sc(sno,cno,score)VALUES(7,3,68);
INSERT INTO sc(sno,cno,score)VALUES(7,4,59);
INSERT INTO sc(sno,cno,score)VALUES(7,5,89);

-- 1、查询课程1的成绩 比 课程2的成绩 高 的所有学生的学号
select a.sno from (select * from sc where cno=1) a ,(select * from sc where cno = 2) b
where a.score>b.score and a.sno = b.sno;

-- 2、查询平均成绩大于60分的同学的学号和平均成绩
select sno ,avg(score) from sc GROUP BY sno HAVING avg(score)>60;

-- 3、查询所有同学的学号、姓名、选课数、总成绩
select s.sno,sname,count(*),sum(score) from student1 s,sc where s.sno = sc.sno group by  sc.sno;

-- 4、查询姓“李”的老师的个数
select count(*) from teacher where tname like CONCAT('李','%'); 

-- 5、查询没学过“叶平”老师课的同学的学号、姓名
select s.sno,s.sname from student1 s where sno not in
(select s.sno from student1 s, sc ,teacher t,course c where s.sno = sc.sno and sc.cno = c.cno and c.tno = t.tno and t.tname ='叶平' );


-- 四大排名函数
（一） ROW_NUMBER()
row_number() 在排名是序号 连续 不重复，即使遇到表中的两个一样的数值亦是如此。
注意：在使用row_number() 实现分页时需要特别注意一点，over子句中的order by 要与SQL排序记录中的order by保持一致，否则得到的序号可能不是连续的
-- select *,row_number() OVER(order by number ) as row_num from num;
即 1 2 3 4 5 6

（二）Rank()
rank() 函数会把要求排序的值相同的归为一组且每组序号一样，排序不会连续执行。
-- select Score,rank() over (order by Score desc) 'Rank' from Scores;
即 1 2 2 4 4 6

（三）dense_rank()
dense_rank() 排序是连续的，也会把相同的值分为一组且每组排序号一样。
-- select Score,dense_rank() over (order by Score desc) 'Rank' from Scores;
当然也可以进行分组排序，在排名函数里统一使用 partition by 进行分组排序
select Score,dense_rank() over (partition by xxx order by Score desc) 'Rank' from Scores;
即 1 1 2 3 3 4

（四）ntile()
ntile(group_num) 将所有记录分成group_num个组，每组序号一样。
-- select *,ntile(2) OVER(order by number ) as row_num from num; 
即 1 1 1 2 2 2

高级SQL

-- 触发器

-- 查询所有触发器
show TRIGGERS;
-- 用户表
create table user(id int primary key auto_increment,name varchar(20));

-- 统计用户数量表
create table user_count(count int);

select * from user;

select * from user_count;

-- 触发添加一条 更改

create trigger after_insert_user after insert on user for each row 
BEGIN
	UPDATE user_count set count = count +1;
END;

-- 触发删除一条 更改

create trigger after_delete_user after delete on user for each row 
BEGIN
	update user_count set count = count - 1;
END;


delete from user;

drop trigger after_insert_user;

insert into user set id = 1,name = '张三';

delete from user where id = 1;

-- 一张员工表，复制一张员工表，当员工表数据发生变化时，复制表也要发生变化
create table employee(id int PRIMARY KEY auto_increment,name varchar(20),sal double);

create table employee_copy(id int PRIMARY KEY auto_increment,name varchar(20),sal double);

-- 添加
create trigger after_insert_emp after insert on employee for each row
BEGIN
	insert into employee_copy set id = new.id,name = new.name,sal= new.sal;
END;


-- 更改
create trigger after_update_emp after update on employee for each ROW
BEGIN
	update employee_copy set name = new.name ,sal= new.sal where id = old.id;
END;

-- 删除
create trigger after_delete_emp after DELETE on employee for each ROW
BEGIN
		delete from employee_copy where id = old.id;
END;

drop trigger after_insert_emp;
select * from employee;
select * from employee_copy;

insert into employee values(1,'张三',5000);

update employee set name = '张思思' where id = 1;

delete from employee where id = 1;

-- 给user表新增字段，age, sex，使用触发器实现 age只可以0-120  性别只可以男 女
-- 抛异常
alter table user add age INT;
alter table user add sex varchar(2)

create trigger before_insert_user before insert on user for each ROW
BEGIN
	if new.age not BETWEEN 0 and 120
			then SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '年龄只能是0-120之间';
	end if;
	if new.sex not in ('男','女')
			then SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '性别只能是男或女';
	end if;
END;


insert into user values (1,'张三',85,'男');


-- 一张员工表，包含员工姓名、员工薪资，再有一张统计表，统计员工数量，员工总薪资

create table emp_count(count int,salCount double);

insert into emp_count VALUEs (0,0);

select * from employee;
select * from emp_count;

create TRIGGER after_insert_employee after insert on employee for each ROW
BEGIN
	update emp_count set count = count + 1 ,salCount = salCount + new.sal;
END;

create trigger after_update_employee after update on employee for each ROW
BEGIN
	update emp_count set salCount = salCount - old.sal + new.sal;
END;

create trigger after_delete_employee after delete on employee for each row 
BEGIN
		update emp_count set count = count -1 ,salCount = salCount - old.sal;
END;

insert into employee values (1,'张三','50000');


update employee set name = '张三三' ,sal = 8000 where id = 1;

delete from employee where id = 1;

-- 存储过程
-- 无参
create procedure p1()
BEGIN
	select count(*) from staff;
END;

call p1();

-- 有输入参数的存储过程
-- 参数分为 输入参数 in  输出参数 out  输入输出参数 inout
-- 定义参数  in 参数名 参数类型
create procedure p2(in param1 int)
BEGIN
	select * from staff where id = param1;
END;

call p2(2);

-- 定义输出参数
create procedure p3(out param2 int)
BEGIN
	select count(*) into param2 from staff;
END;

set @a = 0;
call p3(@a);
-- 查询结果
select @a;

-- 输入参数和输出参数
create procedure p4 (in param1 int ,in param2 varchar(20),in param3 double,out param4 int)
BEGIN
	insert into employee set id = param1,name = param2,sal = param3;
	select count(*) into param4 from employee;
END;

insert into employee values (1,'姚飞鑫',80000);

call p4(2,'姚飞鑫',850000,@a);
select @a;

-- 循环添加20万条记录

create table yy(id int ,name varchar(200));
create PROCEDURE p5(in param1 int)
BEGIN
	declare i int;
	set i=1;
	while (i<=param1)
	do 
		insert into yy set id = i,name = concat(i,'姚飞鑫');
		set i = i + 1;
	end while;
END;

call p5(200000);

select name from mysql.proc where db  = 'a' and type = 'procedure';

-- 创建普通索引
-- 第一种方式 alter table yy add index name_index(name)  创完表之后再写
-- 第三种方式  index name_index(name)  在创表的语句中

-- 第二种方式 是在创完表之后写的
create index name_index on yy(NAME);


EXPLAIN select * from yy where name = '8500姚飞鑫';

做题的知识点：

DBMS集数据定义、数据查询、数据操纵、数据控制功能于一身。
数据库设计通常分为6个阶段

1.需求分析：分析用户的需求，包括数据、功能和性能需求；

2.概念结构设计：主要采用E-R模型进行设计，包括画E-R图；

3.逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换；

4.数据库物理设计：主要是为所设计的数据库选择合适的存储结构和存取路径；

5.数据库的实施：包括编程、测试和试运行；

6.数据库运行与维护：系统的运行与数据库的日常维护。

主要讨论其中的第3个阶段,即逻辑设计。通过一个实际的案例说明在逻辑设计中E-R图向关系模式的转换。

MYSQL中处理插入过程主键或唯一重复值的解决办法：

1.IGNORE:有则忽略，无则插入

2.ON DUPLIACATE KEY UPDATE ：有则更新，只更新新增部分，其余未涉及的字段不变；无则添加；

3.REPLACE INTO：有则删除，更新新增部分，未涉及的字段变为默认值；无则添加。

秒客网