mysql面试题

什么是存储过程？有哪些优缺点？

 

存储过程的优点：

• 能够将代码封装起来
• 保存在数据库之中
• 让编程语言进行调用
• 存储过程是一个预编译的代码块，执行效率比较高
• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率

存储过程的缺点：

• 每个数据库的存储过程语法几乎都不一样，十分难以维护（不通用）
• 业务逻辑放在数据库上，难以迭代

 

      详细参考内容请点击:     储存过程基础教程

 

触发器的作用是什么？

 

  答：触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

维护数据库的完整性和一致性,你喜欢用触发器还是自写逻辑业务?为什么?

 

答：我是这样做的，尽可能使用约束，如check, 主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

 

 

 

什么是游标？

答：游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

 

三个范式是什么?

1. 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

2. 第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

3. 第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段x → 非关键字段y

 

 

上面的文字我们肯定是看不懂的，也不愿意看下去的。接下来我就总结一下：

• 首先要明确的是：满足着第三范式，那么就一定满足第二范式、满足着第二范式就一定满足第一范式
• 第一范式：字段是最小的的单元不可再分
  • 学生信息组成学生信息表，有年龄、性别、学号等信息组成。这些字段都不可再分，所以它是满足第一范式的
• 第二范式：满足第一范式,表中的字段必须完全依赖于全部主键而非部分主键。
  • 其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的
  • 学号为1024的同学，姓名为Java3y，年龄是22岁。姓名和年龄字段都依赖着学号主键。
• 第三范式：满足第二范式，非主键外的所有字段必须互不依赖
  • 就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖
  • 比如，我们大学分了很多系（中文系、英语系、计算机系……），这个系别管理表信息有以下字段组成：系编号，系主任，系简介，系架构。那我们能不能在学生信息表添加系编号，系主任，系简介，系架构字段呢？不行的，因为这样就冗余了，非主键外的字段形成了依赖关系(依赖到学生信息表了)！正确的做法是：学生表就只能增加一个系编号字段。

 

什么是视图？以及视图的使用场景有哪些？

    视图是一种基于数据表的一种虚表

（1）视图是一种虚表（2）视图建立在已有表的基础上, 视图赖以建立的这些表称为基表（3）向视图提供数据内容的语句为 SELECT 语句,可以将视图理解为存储起来的 SELECT 语句（4）视图向用户提供基表数据的另一种表现形式（5）视图没有存储真正的数据，真正的数据还是存储在基表中（6）程序员虽然操作的是视图，但最终视图还会转成操作基表（7）一个基表可以有0个或多个视图

我们应该做到：他们想看到什么样的数据，我们就给他们什么样的数据...一方面就能够让他们只关注自己的数据，另一方面，我们也保证数据表一些保密的数据不会泄露出来...

 

我们在查询数据的时候，常常需要编写非常长的SQL语句，几乎每次都要写很长很长....上面已经说了，视图就是基于查询的一种虚表，也就是说，视图可以将查询出来的数据进行封装。。。那么我们在使用的时候就会变得非常方便...

值得注意的是：使用视图可以让我们专注与逻辑，但不提高查询效率

 

drop、delete与truncate分别在什么场景之下使用？

 

 

drop table

• 1)属于DDL
• 2)不可回滚
• 3)不可带where
• 4)表内容和结构删除
• 5)删除速度快

truncate table

• 1)属于DDL
• 2)不可回滚
• 3)不可带where
• 4)表内容删除
• 5)删除速度快

delete from

• 1)属于DML

• 2)可回滚

• 3)可带where

• 4)表结构在，表内容要看where执行的情况

• 5)删除速度慢,需要逐行删除

• 不再需要一张表的时候，用drop

• 想删除部分数据行时候，用delete，并且带上where子句

• 保留表而删除所有数据的时候用truncate

索引是什么？有什么作用以及优缺点？

索引是帮助高效获取数据的数据结构.索引也可以是一个文件

 

为什么要建立索引，即索引的优点：
①  建立索引的列可以保证行的唯一性，生成唯一的rowId
②  建立索引可以有效缩短数据的检索时间
③  建立索引可以加快表与表之间的连接
④  为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

 

 

rowid的特点

• （1）位于每个表中，但表面上看不见，例如：desc emp是看不见的
• （2）只有在select中，显示写出rowid，方可看见
• （3）它与每个表绑定在一起，表亡，该表的rowid亡，二张表rownum可以相同，但rowid必须是唯一的
• （4）rowid是18位大小写加数字混杂体，唯一表代该条记录在DBF文件中的位置
• （5）rowid可以参与=/like比较时，用''单引号将rowid的值包起来，且区分大小写
• （6）rowid是联系表与DBF文件的桥梁

 

3. 索引的缺点：
①  创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大
②  创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）
③  会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长
 

4. 什么样的表跟列要建立索引：
①  总的来说就是数据量大的，经常进行查询操作的表要建立索引
②  表中字段建立索引应该遵循几个原则：
1)        越小的数据类型通常更好：越小的数据类型通常在磁盘、内存中都需要更少的空间，处理起来更快。
2)          简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂，处理起来也更耗时。
3)        尽量避免NULL：应该指定列为NOT NULL。含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
4)        对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义，所以索引的建立应当更多的选取唯一性更高的字段。
③  表与表连接用于多表联合查询的约束条件的字段应当建立索引
④  用于排序的字段可以添加索引，用于分组的字段应当视情况看是否需要添加索引。
⑤  添加多列索引的时候，对应的多条件查询可以触发该索引的同时，索引最左侧的列的单条件查询也可以触发。
⑥  如果有些表注定只会进行查询所有，也就没必要添加索引，因为查询全部只能进行全量搜索即扫描全表。

 

 

索引分类：

• 唯一索引：唯一索引不允许两行具有相同的索引值
• 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空
• 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个
• 非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

 

深入理解索引可参考：

• kb.cnblogs.com/page/45712/
• www.cnblogs.com/drizzlewith…

 

 

什么是事务？

答:单个逻辑单元执行的一系列操作,这些操作要么全做要么全不做,是不可分割的.事务的开始和结束用户是可以控制的,如果没控制则由数据库默认的划分事务.

ACID — 数据库事务正确执行的四个基本要素

• 包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

(1)原子性

    指一个事务要么全执行,要么全不执行.也就是说一个事务不可能执行到一半就停止了.比如:你去买东西,钱付掉了,东西没拿.这两步必须同时执行 ,要么都不执行.

(2)一致性

   指事务的运行并不改变数据库中的一致性.比如 a+b=10;a改变了,b也应该随之改变.

(3)独立性

   两个以上的事务不会出现交替运行的状态,因为这样可能导致数据的不一致

(4)持久性

   事务运行成功之后数据库的更新是永久的,不会无缘无故的回滚.

 

举个例子:A向B转账，转账这个流程中如果出现问题，事务可以让数据恢复成原来一样【A账户的钱没变，B账户的钱也没变】。

事例说明：

 

1. /*

2. * 我们来模拟A向B账号转账的场景

3. * A和B账户都有1000块，现在我让A账户向B账号转500块钱

4. *

5. * */

6. //JDBC默认的情况下是关闭事务的，下面我们看看关闭事务去操作转账操作有什么问题

7.  

8. //A账户减去500块

9. String sql = "UPDATE a SET money=money-500 ";

10. preparedStatement = connection.prepareStatement(sql);

11. preparedStatement.executeUpdate();

12.  

13. //B账户多了500块

14. String sql2 = "UPDATE b SET money=money+500";

15. preparedStatement = connection.prepareStatement(sql2);

16. preparedStatement.executeUpdate();

17.  
18.  

从上面看，我们的确可以发现A向B转账，成功了。可是**如果A向B转账的过程中出现了问题呢？**下面模拟一下

1. //A账户减去500块

2. String sql = "UPDATE a SET money=money-500 ";

3. preparedStatement = connection.prepareStatement(sql);

4. preparedStatement.executeUpdate();

5.  

6. //这里模拟出现问题

7. int a = 3 / 0;

8.  
9.  

10. String sql2 = "UPDATE b SET money=money+500";

11. preparedStatement = connection.prepareStatement(sql2);

12. preparedStatement.executeUpdate();

显然，上面代码是会抛出异常的，我们再来查询一下数据。A账户少了500块钱，B账户的钱没有增加。这明显是不合理的。

---

我们可以通过事务来解决上面出现的问题

 

1. //开启事务,对数据的操作就不会立即生效。

2. connection.setAutoCommit(false);

3.  

4. //A账户减去500块

5. String sql = "UPDATE a SET money=money-500 ";

6. preparedStatement = connection.prepareStatement(sql);

7. preparedStatement.executeUpdate();

8.  

9. //在转账过程中出现问题

10. int a = 3 / 0;

11.  

12. //B账户多500块

13. String sql2 = "UPDATE b SET money=money+500";

14. preparedStatement = connection.prepareStatement(sql2);

15. preparedStatement.executeUpdate();

16.  

17. //如果程序能执行到这里，没有抛出异常，我们就提交数据

18. connection.commit();

19.  

20. //关闭事务【自动提交】

21. connection.setAutoCommit(true);

22.  
23.  

24. } catch (SQLException e) {

25. try {

26. //如果出现了异常，就会进到这里来，我们就把事务回滚【将数据变成原来那样】

27. connection.rollback();

28.  

29. //关闭事务【自动提交】

30. connection.setAutoCommit(true);

31. } catch (SQLException e1) {

32. e1.printStackTrace();

33. }

上面的程序也一样抛出了异常，A账户钱没有减少，B账户的钱也没有增加。

注意：当Connection遇到一个未处理的SQLException时，系统会非正常退出，事务也会自动回滚，但如果程序捕获到了异常，是需要在catch中显式回滚事务的。

事务隔离级别

数据库定义了4个隔离级别：

    1.Read uncommitted【级别最低，什么都避免不了】

    2.Read committed【可避免脏读】

    3.Repeatable read【可避免脏读，不可重复读】

    4.Serializable【可避免脏读，不可重复读，虚读】

分别对应Connection类中的4个常量

1. TRANSACTION_READ_UNCOMMITTED
2. TRANSACTION_READ_COMMITTED
3. TRANSACTION_REPEATABLE_READ
4. TRANSACTION_SERIALIZABLE

 

---

脏读：一个事务读取到另外一个事务未提交的数据

例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。

---

不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改

注：假设事务1读取了一条记录(select user_name from user where user_id = 1),得到user_name = '456',事务1暂时没提交。事务2更新了一条记录(update user set user_name = '123' where user_id = 1),事务2提交。此时事务1再次select user_name from user where user_id = 1得到了user_name = '123',这样就导致事务1在读取同一行数据却得到不同的user_name。这就是所谓的不可以重复读

---

虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

注：这个跟不可重复读相反,当事务1查询到user_name='456'时，事务2将user_name更新成'123'并提交，事务1再次查询还是会发现user_name='456',这样保证了可重复读。幻读的话就是当事务2插入一条新的数据id为2并提交，事务1由于可重复读的性质，只能在表中查到id为1的数据，如果此时事务1插入id为2的数据则会产生错误，因为此时表中已经有了id为2的数据,但是事务1只看到了id为1的数据。

---

简单总结：脏读是不可容忍的，不可重复读和虚读在一定的情况下是可以的【做统计的肯定就不行】。

 

数据库的乐观锁和悲观锁是什么？

确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

 

 

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作

• 在查询完数据的时候就把事务锁起来，直到提交事务
• 实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。

• 在修改数据的时候把事务锁起来，通过version的方式来进行锁定
• 实现方式：使用version版本或者时间戳

悲观锁：

 

 

乐观锁：

 

 

参考资料：

• www.open-open.com/lib/view/op…

 

 

悲观锁和乐观锁的实现
一、乐观锁
 总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。
 version方式：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。
核心SQL代码：
[sql] view plain copy
update table set x=x+1, version=version+1 where id=#{id} and version=#{version};    


 CAS操作方式：即compare and swap 或者 compare and set，涉及到三个操作数，数据所在的内存值，预期值，新值。当需要更新时，判断当前内存值与之前取到的值是否相等，若相等，则用新值更新，若失败则重试，一般情况下是一个自旋操作，即不断的重试。
一、悲观锁

 总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起。可以依靠数据库实现，如行锁、读锁和写锁等，都是在操作之前加锁，在Java中，synchronized的思想也是悲观锁。

 

 

超键、候选键、主键、外键分别是什么？

• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
• 候选键(候选码)：是最小超键，即没有冗余元素的超键。
• 主键(主码)：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
• 外键：在一个表中存在的另一个表的主键称此表的外键。

候选码和主码：

例子：邮寄地址（城市名，街道名，邮政编码，单位名，收件人）

• 它有两个候选键:{城市名，街道名} 和 {街道名，邮政编码}
• 如果我选取{城市名，街道名}作为唯一标识实体的属性，那么{城市名，街道名} 就是主码(主键)

 

SQL 约束有哪几种？

SQL 约束有哪几种？

• NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
• UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
• CHECK: 用于控制字段的值范围。

数据库运行于哪种状态下可以防止数据的丢失？

数据库运行于哪种状态下可以防止数据的丢失？

在archivelog mode(归档模式)只要其归档日志文件不丢失，就可以有效地防止数据丢失。

Mysql存储引擎

Mysql的存储引擎有以下几种：

 

 

我的是5.7.15版本，默认使用的是Innodb版本！

常用的存储引擎有以下：

• Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyIASM引擎(原本Mysql的默认引擎),不提供事务的支持，也不支持行级锁和外键。
• MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

 

 

同一个数据库也可以使用多种存储引擎的表。如果一个表修改要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

参考资料：

• www.cnblogs.com/xiaohaillon…
• blog.****.net/ls5718/arti…
• blog.****.net/t146lla128x…

MyIASM和Innodb两种引擎所使用的索引的数据结构是什么？

MyIASM和Innodb两种引擎所使用的索引的数据结构是什么？

答案:都是B+树!

MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

varchar和char的区别

varchar和char的区别

Char是一种固定长度的类型，varchar是一种可变长度的类型

mysql有关权限的表都有哪几个

mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

数据表损坏的修复方式有哪些？

数据表损坏的修复方式有哪些？

使用 myisamchk 来修复，具体步骤：

• 1）修复前将mysql服务停止。
• 2）打开命令行方式，然后进入到mysql的/bin目录。
• 3）执行myisamchk –recover 数据库所在路径/*.MYI

使用repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表 REPAIR TABLE 用于修复被破坏的表。OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）

MySQL中InnoDB引擎的行锁是通过加在什么上完成

MySQL中InnoDB引擎的行锁是通过加在什么上完成

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,

如果 id 不是索引键那么InnoDB将完成表锁,,并发将无从谈起

数据库优化的思路

SQL优化

点击链接进入sql优化

 

在我们书写SQL语句的时候，其实书写的顺序、策略会影响到SQL的性能，虽然实现的功能是一样的，但是它们的性能会有些许差别。

因此，下面就讲解在书写SQL的时候，怎么写比较好。

①选择最有效率的表名顺序

数据库的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表将被最先处理

在FROM子句中包含多个表的情况下：

• 如果三个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推
• 也就是说：选择记录条数最少的表放在最后

如果有3个以上的表连接查询：

• 如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推。
• 也就是说：被其他表所引用的表放在最后

例如：查询员工的编号，姓名，工资，工资等级，部门名

emp表被引用得最多，记录数也是最多，因此放在form字句的最后面

1.  

2. select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname

3. from salgrade,dept,emp

4. where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)

5.  
6.  

②WHERE子句中的连接顺序

数据库采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。

emp.sal可以过滤多条记录，写在WHERE字句的最右边

1.  

2. select emp.empno,emp.ename,emp.sal,dept.dname

3. from dept,emp

4. where (emp.deptno = dept.deptno) and (emp.sal > 1500)

③SELECT子句中避免使用*号

我们当时学习的时候，“*”号是可以获取表中全部的字段数据的。

• 但是它要通过查询数据字典完成的，这意味着将耗费更多的时间
• 使用*号写出来的SQL语句也不够直观。

---

④用TRUNCATE替代DELETE

这里仅仅是：删除表的全部记录，除了表结构才这样做。

DELETE是一条一条记录的删除，而Truncate是将整个表删除，保留表结构，这样比DELETE快

⑤多使用内部函数提高SQL效率

例如使用mysql的concat()函数会比使用||来进行拼接快，因为concat()函数已经被mysql优化过了。

⑥使用表或列的别名

如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些SQL的性能。毕竟要扫描的字符长度就变少了。。。

⑦多使用commit

comiit会释放回滚点...

⑧善用索引

索引就是为了提高我们的查询数据的，当表的记录量非常大的时候，我们就可以使用索引了。

⑨SQL写大写

我们在编写SQL 的时候，官方推荐的是使用大写来写关键字，因为Oracle服务器总是先将小写字母转成大写后，才执行

⑩避免在索引列上使用NOT

因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描

①①避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢

①②用 >= 替代 >

1. 低效：

2. SELECT * FROM EMP WHERE DEPTNO > 3

3. 首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录

4. 高效：

5. SELECT * FROM EMP WHERE DEPTNO >= 4

6. 直接跳到第一个DEPT等于4的记录

①④总是使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引。 当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引

1.  

2. create index emp_sal_job_idex

3. on emp(sal,job);

4. ----------------------------------

5. select *

6. from emp

7. where job != 'SALES';

8.  
9.  

10. 上边就不使用索引了。

数据库结构优化

• 1）范式优化： 比如消除冗余（节省空间。。）
• 2）反范式优化：比如适当加冗余等（减少join）
• 3）拆分表： 垂直拆分和水平拆分

服务器硬件优化

这个么多花钱咯！

SQL练习题

下列练习题参考自公众号Java知音：

• mp.weixin.qq.com/s?__biz=MzI…
• mp.weixin.qq.com/s?__biz=MzI…

基本表结构：

1. student(sno,sname,sage,ssex)学生表

2. course(cno,cname,tno) 课程表

3. sc(sno,cno,score) 成绩表

4.  

5. teacher(tno,tname) 教师表

6.  

题目：

1.  
2.  

3. 101，查询课程1的成绩比课程2的成绩高的所有学生的学号

4. select a.sno from

5. (select sno,score from sc where cno=1) a,

6. (select sno,score from sc where cno=2) b

7. where a.score>b.score and a.sno=b.sno

8.  
9.  
10.  

11. 102，查询平均成绩大于60分的同学的学号和平均成绩

12. select a.sno as "学号", avg(a.score) as "平均成绩"

13. from

14. (select sno,score from sc) a

15. group by sno having avg(a.score)>60

16.  
17.  
18.  

19. 103，查询所有同学的学号、姓名、选课数、总成绩

20. select a.sno as 学号, b.sname as 姓名,

21. count(a.cno) as 选课数, sum(a.score) as 总成绩

22. from sc a, student b

23. where a.sno = b.sno

24. group by a.sno, b.sname

25.  

26. 或者：

27.  

28. selectstudent.sno as 学号, student.sname as 姓名,

29. count(sc.cno) as 选课数, sum(score) as 总成绩

30. from student left Outer join sc on student.sno = sc.sno

31. group by student.sno, sname

32.  

33. 104，查询姓“张”的老师的个数

34.  

35. selectcount(distinct(tname)) from teacher where tname like '张%‘

36. 或者：

37. select tname as "姓名", count(distinct(tname)) as "人数"

38. from teacher

39. where tname like'张%'

40. group by tname

41.  
42.  
43.  

44. 105，查询没学过“张三”老师课的同学的学号、姓名

45. select student.sno,student.sname from student

46. where sno not in (select distinct(sc.sno) from sc,course,teacher

47. where sc.cno=course.cno and teacher.tno=course.tno and teacher.tname='张三')

48.  
49.  
50.  

51. 106，查询同时学过课程1和课程2的同学的学号、姓名

52. select sno, sname from student

53. where sno in (select sno from sc where sc.cno = 1)

54. and sno in (select sno from sc where sc.cno = 2)

55. 或者：

56.  

57. selectc.sno, c.sname from

58. (select sno from sc where sc.cno = 1) a,

59. (select sno from sc where sc.cno = 2) b,

60. student c

61. where a.sno = b.sno and a.sno = c.sno

62. 或者：

63.  

64. select student.sno,student.sname from student,sc where student.sno=sc.sno and sc.cno=1

65. and exists( select * from sc as sc_2 where sc_2.sno=sc.sno and sc_2.cno=2)

66.  
67.  
68.  

69. 107，查询学过“李四”老师所教所有课程的所有同学的学号、姓名

70. select a.sno, a.sname from student a, sc b

71. where a.sno = b.sno and b.cno in

72. (select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四')

73.  

74. 或者：

75.  

76. select a.sno, a.sname from student a, sc b,

77. (select c.cno from course c, teacher d where c.tno = d.tno and d.tname = '李四') e

78. where a.sno = b.sno and b.cno = e.cno

79.  
80.  
81.  

82. 108，查询课程编号1的成绩比课程编号2的成绩高的所有同学的学号、姓名

83. select a.sno, a.sname from student a,

84. (select sno, score from sc where cno = 1) b,

85. (select sno, score from sc where cno = 2) c

86. where b.score > c.score and b.sno = c.sno and a.sno = b.sno

87.  
88.  
89.  

90. 109，查询所有课程成绩小于60分的同学的学号、姓名

91. select sno,sname from student

92. where sno not in (select distinct sno from sc where score > 60)

93.  
94.  
95.  

96. 110，查询至少有一门课程与学号为1的同学所学课程相同的同学的学号和姓名

97. select distinct a.sno, a.sname

98. from student a, sc b

99. where a.sno <> 1 and a.sno=b.sno and

100. b.cno in (select cno from sc where sno = 1)

101.  

102. 或者：

103.  

104. select s.sno,s.sname

105. from student s,

106. (select sc.sno

107. from sc

108. where sc.cno in (select sc1.cno from sc sc1 where sc1.sno=1)and sc.sno<>1

109. group by sc.sno)r1

110. where r1.sno=s.sno

111.  

112. 111、把“sc”表中“王五”所教课的成绩都更改为此课程的平均成绩

113. update sc set score = (select avg(sc_2.score) from sc sc_2 wheresc_2.cno=sc.cno)

114. from course,teacher where course.cno=sc.cno and course.tno=teacher.tno andteacher.tname='王五'

115.  
116.  

117. 112、查询和编号为2的同学学习的课程完全相同的其他同学学号和姓名

118. 这一题分两步查：

119.  

120. 1，

121.  

122. select sno

123. from sc

124. where sno <> 2

125. group by sno

126. having sum(cno) = (select sum(cno) from sc where sno = 2)

127.  

128. 2，

129. select b.sno, b.sname

130. from sc a, student b

131. where b.sno <> 2 and a.sno = b.sno

132. group by b.sno, b.sname

133. having sum(cno) = (select sum(cno) from sc where sno = 2)

134.  

135. 113、删除学习“王五”老师课的sc表记录

136. delete sc from course, teacher

137. where course.cno = sc.cno and course.tno = teacher.tno and tname = '王五'

138.  

139. 114、向sc表中插入一些记录，这些记录要求符合以下条件：

140. 将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩

141. insert sc select sno, 3, (select avg(score) from sc where cno = 2)

142. from student

143. where sno not in (select sno from sc where cno = 3)

144.  

145. 115、按平平均分从高到低显示所有学生的如下统计报表：

146. -- 学号,企业管理,马克思,UML,数据库,物理,课程数,平均分

147. select sno as 学号

148. ,max(case when cno = 1 then score end) AS 企业管理

149. ,max(case when cno = 2 then score end) AS 马克思

150. ,max(case when cno = 3 then score end) AS UML

151. ,max(case when cno = 4 then score end) AS 数据库

152. ,max(case when cno = 5 then score end) AS 物理

153. ,count(cno) AS 课程数

154. ,avg(score) AS 平均分

155. FROM sc

156. GROUP by sno

157. ORDER by avg(score) DESC

158.  

159. 116、查询各科成绩最高分和最低分：

160.  

161. 以如下形式显示：课程号，最高分，最低分

162. select cno as 课程号, max(score) as 最高分, min(score) 最低分

163. from sc group by cno

164.  

165. select course.cno as '课程号'

166. ,MAX(score) as '最高分'

167. ,MIN(score) as '最低分'

168. from sc,course

169. where sc.cno=course.cno

170. group by course.cno

171.  

172. 117、按各科平均成绩从低到高和及格率的百分数从高到低顺序

173. SELECT t.cno AS 课程号,

174. max(course.cname)AS 课程名,

175. isnull(AVG(score),0) AS 平均成绩,

176. 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/count(1) AS 及格率

177. FROM sc t, course

178. where t.cno = course.cno

179. GROUP BY t.cno

180. ORDER BY 及格率 desc

181.  

182. 118、查询如下课程平均成绩和及格率的百分数(用"1行"显示):

183.  

184. 企业管理（001），马克思（002），UML （003），数据库（004）

185. select

186. avg(case when cno = 1 then score end) as 平均分1,

187. avg(case when cno = 2 then score end) as 平均分2,

188. avg(case when cno = 3 then score end) as 平均分3,

189. avg(case when cno = 4 then score end) as 平均分4,

190. 100 * sum(case when cno = 1 and score > 60 then 1 else 0 end) / sum(casewhen cno = 1 then 1 else 0 end) as 及格率1,

191. 100 * sum(case when cno = 2 and score > 60 then 1 else 0 end) / sum(casewhen cno = 2 then 1 else 0 end) as 及格率2,

192. 100 * sum(case when cno = 3 and score > 60 then 1 else 0 end) / sum(casewhen cno = 3 then 1 else 0 end) as 及格率3,

193. 100 * sum(case when cno = 4 and score > 60 then 1 else 0 end) / sum(casewhen cno = 4 then 1 else 0 end) as 及格率4

194. from sc

195.  

196. 119、查询不同老师所教不同课程平均分, 从高到低显示

197. select max(c.tname) as 教师, max(b.cname) 课程, avg(a.score) 平均分

198. from sc a, course b, teacher c

199. where a.cno = b.cno and b.tno = c.tno

200. group by a.cno

201. order by 平均分 desc

202. 或者：

203. select r.tname as '教师',r.rname as '课程' , AVG(score) as '平均分'

204. from sc,

205. (select t.tname,c.cno as rcso,c.cname as rname

206. from teacher t ,course c

207. where t.tno=c.tno)r

208. where sc.cno=r.rcso

209. group by sc.cno,r.tname,r.rname

210. order by AVG(score) desc

211.  

212. 120、查询如下课程成绩均在第3名到第6名之间的学生的成绩：

213. -- [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

214. select top 6 max(a.sno) 学号, max(b.sname) 姓名,

215. max(case when cno = 1 then score end) as 企业管理,

216. max(case when cno = 2 then score end) as 马克思,

217. max(case when cno = 3 then score end) as UML,

218. max(case when cno = 4 then score end) as 数据库,

219. avg(score) as 平均分

220. from sc a, student b

221. where a.sno not in

222.  

223. (select top 2 sno from sc where cno = 1 order by score desc)

224. and a.sno not in (select top 2 sno from sc where cno = 2 order by scoredesc)

225. and a.sno not in (select top 2 sno from sc where cno = 3 order by scoredesc)

226. and a.sno not in (select top 2 sno from sc where cno = 4 order by scoredesc)

227. and a.sno = b.sno

228. group by a.sno

229.  
230.  
231.  

 

   执行数据库查询时候,如果查询的数据有很多,假设有1000万条,用什么方法可以提高查询效率?在数据库方面或java代码方面有什么优化办法?

1.在数据库设计方面

(1)建立索引

(2)分区(比如说按时间分区)

(3)选取最适用的字段属性(尽量减少字段宽度和使用固定长度的字段)

2.在数据库I/O方面

(1)增加缓冲区

(2)如果涉及到表的级联,最好不同的表放在不同的磁盘,以增加I/O速度

3.Sql语句方法

(1)优化sql语句,减少比较次数,可用连表查询来代替子查询

(2)限制返回的条目数(mysql用limit)

(3)有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就

去掉外键（比喻：就好比免检产品，就是为了提高效率，充分相信产品的制造商）

4.在java方面

如果反复使用的查询,可通过preparedstatement,因为他在运行前已经预编译,只要运行就Ok了.

 

 

sql语言的常见问题

1.增删查改之类的问题,连表查询是必出题目,可以参考之前我写的那篇文章

这里讲一些分组查询跟降序升序分页

分组:group by 列名 

统计各个部门薪水大于5000的人数

例子:SELECT department , COUNT(money) from employee where money>=5000group bydepartment;

 

分页:limit ()

select * from table limit 5; --返回前5行 

select * from table limit 0,5; --同上，返回前5行  

select * from table limit 5,10; --返回6-15行

应用到java里,select * from table limit " +(pageNo-1)*pageSize + "," + pageSize

排序:order by 列名 

asc 和desc 不写默认为升序

前者是升序后者是降序

 

综合题:

 以下两张表,员工表employee 与部门表 department

             

查询结果如下:

请写出sql语句

分析:

我们可以看到这是一般的连表,但是连表的显示的统计人数,所以我们分两步来走

第一步:查出各个部门的对应人数,把这个看做一个新表e1

select e.dep_id,count(name) num from employee e group by dep_id;

第二部:将查询结果与部门表连表

由于查询结果是显示左边的全部表格信息所以用左联,空的话为0,则采用ifnull()函数来解决

select d.*,ifnull(num,0) num  from  department d left  join (select e.dep_id,count(name) num from employee e group by dep_id) e1 on d.id=e.dep_id;

 

 

union 与union all的区别

 

union 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排 序运算，删除重复的记录再返回结果。 union all 则会显示重复结果,只是简单的两个结果合并并返回.所以效率比union高,在保证没有重复数据的情况下用union all.

select name from table1

union

select name  from table2;

 

.用一条 SQL  语句查询出每门课都大于 80 分的学生姓名 

name kecheng fenshu 

张三   语文          81 

张三   数学          75 

李四   语文          76 

李四   数学          90 

王五   语文          81 

王五   数学        100 

王五   英语         90  

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)

select name from table group by name having min(fenshu)>80

 

2. 学生表 如下:
自动编号   学号   姓名 课程编号 课程名称 分数
1        2005001 张三 0001     数学    69
2        2005002 李四 0001      数学    89
3        2005001 张三 0001      数学    69
删除除了自动编号不同, 其他都相同的学生冗余信息

A: delete tablename where 自动编号 not in(select min( 自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

 

 

3.一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.
你先按你自己的想法做一下，看结果有我的这个简单吗？

答：select a.name, b.name
from team a, team b 
where a.name < b.name

4.请用SQL 语句实现：从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。请注意：TestDB 中有很多科目，都有1 －12 月份的发生额。
AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额。
数据库名：JcyAudit ，数据集：Select * from TestDB

答：select a.*
from TestDB a 
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

************************************************************************************

5.面试题：怎么把这样一个表儿
year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1   m2   m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

答案一、
select year, 
(select amount from   aaa m where month=1   and m.year=aaa.year) as m1,
(select amount from   aaa m where month=2   and m.year=aaa.year) as m2,
(select amount from   aaa m where month=3   and m.year=aaa.year) as m3,
(select amount from   aaa m where month=4   and m.year=aaa.year) as m4
from aaa   group by year

*******************************************************************************
6. 说明：复制表( 只复制结构, 源表名：a新表名：b) 

SQL: select * into b from a where 1<>1       (where1=1，拷贝表结构和数据内容)
ORACLE:create table b

As

Select * from a where 1=2

 

[<>（不等于）(SQL Server Compact)

 

 

比较两个表达式。 当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为 TRUE。 否则，结果为 FALSE。]

 

7. 说明：拷贝表( 拷贝数据, 源表名：a目标表名：b) 

SQL: insert into b(a, b, c) select d,e,f from a; 

8. 说明：显示文章、提交人和最后回复时间
SQL: select A.title, A.username, (select max(adddate) adddate from table where table.title=A.title) as adddate 
from table A

9. 说明：外连接查询( 表名1 ：a表名2 ：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.c

ORACLE：select a.a, a.b, a.c, b.c, b.d, b.f from a ,b

where a.a = b.c(+)

10. 说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f 开始时间,getdate())>5

11. 说明：两张关联表，删除主表中已经在副表中没有的信息

SQL: 
Delete from info where not exists (select * from infobz where info.infid=infobz.infid )

*******************************************************************************

12.有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value
这道题的SQL 语句怎么写？

update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key);

***************************************************************************

 

13.高级sql 面试题

原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
为了便于阅读, 查询此表后的结果显式如下( 及格分数为60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
写出此查询语句

select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course

完全正确

SQL> desc course_v
Name Null? Type
----------------------------------------- -------- ----------------------------
COURSEID NUMBER
COURSENAME VARCHAR2(10)
SCORE NUMBER

SQL> select * from course_v;

COURSEID COURSENAME SCORE
---------- ---------- ----------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80

SQL> select courseid, coursename ,score ,decode(sign(score-60),-1,'fail','pass') as mark from course_v;

COURSEID COURSENAME SCORE MARK
---------- ---------- ---------- ----
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass

SQL面试题（1）

create table testtable1
(
id int IDENTITY,
department varchar(12) 
)

select * from testtable1
insert into testtable1 values('设计')
insert into testtable1 values('市场')
insert into testtable1 values('售后')
/*
结果
id department
1   设计
2   市场
3   售后 
*/
create table testtable2
(
id int IDENTITY,
dptID int,
name varchar(12)
)
insert into testtable2 values(1,'张三')
insert into testtable2 values(1,'李四')
insert into testtable2 values(2,'王五')
insert into testtable2 values(3,'彭六')
insert into testtable2 values(4,'陈七')
/*
用一条SQL语句，怎么显示如下结果
id dptID department name
1   1      设计        张三
2   1      设计        李四
3   2      市场        王五
4   3      售后        彭六
5   4      黑人        陈七
*/

答案：

SELECT testtable2.* , ISNULL(department,'黑人')
FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID

也做出来了可比这方法稍复杂。

sql面试题（2）

有表A，结构如下： 
A: p_ID p_Num s_id 
1 10 01 
1 12 02 
2 8 01 
3 11 01 
3 8 03 
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID。请用SQL语句实现将上表中的数据合并，合并后的数据为： 
p_ID s1_id s2_id s3_id 
1 10 12 0 
2 8 0 0 
3 11 0 8 
其中：s1_id为仓库1的库存量，s2_id为仓库2的库存量，s3_id为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。

结果：

select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id
,sum(case when s_id=2 then p_num else 0 end) as s2_id
,sum(case when s_id=3 then p_num else 0 end) as s3_id
from myPro group by p_id

 

求各个部门直接的比赛结果?  有个叫team 的表格,里面有id跟部门名name,如果各个部门要举行比赛用语句求出比赛结果  

select a.name,b.name from game a cross join game b where a.name<b.name;

 

 

一个用户表中有一个积分字段，假如数据库中有 100 多万个用户，若要在 多万个用户，若要在

每年第一天凌晨将积分清零，你将考虑什么，你将想什么办法解决 每年第一天凌晨将积分清零，你将考虑什么，你将想什么办法解决?

 

alter table drop column score;

alter table add colunm score int;

可能会很快，但是需要试验，试验不能拿真实的环境来操刀，并且要注意，

这样的操作时无法回滚的，在我的印象中，只有 inert update delete 等 DML 语句才能回滚，

对于 create table,drop table ,alter table 等 DDL 语句是不能回滚

 

 

    点击查看 :     50个常用sql语句

原表: 
courseid coursename score 
------------------------------------- 
1                  java              70 
2                 oracle            90 
3                  xml               40 
4                  jsp                30 
5                  servlet          80 
------------------------------------- 
为了便于阅读,查询此表后的结果显式如下(及格分数为60): 
courseid   coursename    score     mark 
--------------------------------------------------- 
1                  java              70             pass
2                 oracle            90             pass    
3                  xml               40             fail
4                  jsp                30             fail
5                  servlet          80             pass
--------------------------------------------------- 
写出此查询语句 

ORACLE : select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course

(DECODE函数是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数)

(SQL: select courseid, coursename ,score ,（case when score<60 then 'fail' else 'pass' end) as mark from course )

 

Oracle和Mysql的区别

在Mysql中，一个用户下可以创建多个库：

 

 

而在Oracle中，Oracle服务器是由两部分组成

• 数据库实例【理解为对象，看不见的】
• 数据库【理解为类，看得见的】

一个数据库实例可拥有多个用户，一个用户默认拥有一个表空间。

表空间是存储我们数据库表的地方，表空间内可以有多个文件。

 

 

当我们使用Oracle作为我们数据库时，我们需要指定用户、表空间来存储我们所需要的数据！

最后

参考资料：

• blog.****.net/xlgen157387…
• blog.****.net/beauty_1991…
• zhuanlan.zhihu.com/p/23713529
• blog.****.net/wickedvalle…
• blog.****.net/zhugewendu/…

阅读原文