关于SQL知识

最新推荐文章于 2024-09-20 21:26:51 发布

-星星电灯

最新推荐文章于 2024-09-20 21:26:51 发布

阅读量174

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_41296326/article/details/115914435

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.你怎么设计数据库？

设计数据库

• 在需求分析阶段，设计数据库的一般步骤为：

– 收集信息

与该系统有关人员进行交流、坐谈，充分理解数据库需要完成的任务

– 标识对象

标识数据库要管理的关键对象或实体

– 标识每个对象的属性

– 标识对象之间的关系

• 在概要设计阶段和详细设计阶段，设计数据库的步骤为：

– 绘制E-R图

– 将E-R图转换为表格

– 应用三大范式规范化表格

2.什么是数据库索引？索引的种类有哪些?

⑴什么是数据库索引？

数据库索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。在数据库中，索引的含义与日常意义上的“索引”一词并无多大区别（想想小时候查字典），它是用于提高数据库表数据访问速度的数据库对象。

①一些情况下，索引还可用于避免排序操作；

②对于非聚集索引，有些查询甚至可以不访问数据页；

③索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页，而不是遍历所有数据页；

④聚集索引可以避免数据插入操作集中于表的最后一个数据页。

当然，虽然数据库索引可以有效提高数据库数据的查询速度，但是也会导致数据库系统更新数据的性能下降，因为大部分数据更新需要同时更新索引。

⑵数据库索引的类型：

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一索引。

①聚集索引，表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页；

②非聚集索引，表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。

在一张表上只能创建一个聚集索引，因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引，那么它被称为“堆集”（Heap）。这样的表中的数据行没有特定的顺序，所有的新行将被添加的表的末尾位置。

3.什么情况下设置了索引但无法使用（什么情况下索引会失效）？

索引查询失效的几个情况：

1、使用like关键字时
2、使用or关键字时

3、组合索引

4、数据类型

数据类型出现隐式转化。如某个索引字段的数据类型为varchar，查询内容为123，如不加引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5、在索引字段上使用not及运算符

6、对索引字段使用函数

7、全表扫描的速度大于索引速度（如表内数据极少）

4.说说对 SQL 语句优化有哪些方法？

用EXISTS替代IN、用NOT EXISTS替代NOT IN
避免在索引列上使用计算，因为这样会导致索引失效
避免在索引列上使用 IS NULL 和 IS NOT NULL
对查询进行优化，应尽量避免全表扫描，首先可以考虑在where和order by涉及的列上建立索引
尽量避免在where的子句中对字段进行null值判断，否则引擎就会放弃使用索引，从而进行全表扫描
尽量避免在where子句中对字段进行表达式操作，否则引擎就会放弃使用索引，从而进行全表扫描
尽量避免在where子句中使用!=或<>操作符，否则引擎就会放弃使用索引，从而进行全表扫描
尽量避免在where子句中使用or来连接条件，否则引擎就会放弃使用索引，从而进行全表扫描
如果数值是连续的，能用between就不要用in
任何地方都不要使用 select * from table ，要用具体的字段代替“*”，只取出需要的字段
模糊搜索尽量避免使用前置百分号，否则引擎就会放弃使用索引，从而进行全表扫描
一个表的索引数最好不要超过6个，因为太多的索引会影响到表的更新速度
如果字段只包含数值信息，尽量不要设计为字符类型，否则会降低查询和连接的性能，同时还会增加存储开销
避免频繁创建和删除临时表，减少系统表资源的消耗
尽量使用 varchar/nvarchar 代替 char/nchar ，因为变长字段的存储空间小，这样可以节省存储空间，同时对于查询来说，在一个相对较小的字段内搜索，效率自然会更高
尽量使用表变量来代替临时表
避免在视图中进行嵌套！！！
多张表JOIN关联时，需考虑关联字段是否是同一类型，是否是索引字段，能否用索引字段进行关联

5.Statement 接口与 PreparedStatement 接口区别

preparedStatement接口继承了 statement接口，继承了 statement的所有功能，并提供了一整套方法，用 setXxx()方法来设置输入参数（占位符）,并且更改了execute、executeUpdate、executeQuery方法，不再需要参数
preparedStatement 能够进行预编译，多次执行相同的 SQL语句时，比statement效率更高。并且能够防止 SQL注入。
拿 MySQL来说，MySQL执行一次数据库操作分为几步：
- 首先检查 SQL语法格式
- 然后将 SQL编译成可以被执行的函数
- 最后才是执行 SQL得到我们想要的结果
预编译：preparedStatement 执行过程是将带参的 SQL发送给 MySQL数据库，然后 MySQL数据库对这条是 SQL进行语法校验、编译，在执行 SQL的时候再讲参数作为真实数据传给数据库执行真实数据操作。preparedStatement 将statement的一步分为了两步来操作，这就是 preparedStatement的预编译。
执行效率：preparedStatement在同一连接下执行相同 SQL操作时，第一次连接将带参 SQL发送给数据库，数据库进行校验、编译，然后将这个编译后的函数缓存到服务器中，然后进行SQL执行；当第二次执行这个 SQL时，会直接在服务器中对这个 SQL进行操作，省去了校验和编译的步骤。而statement 每次执行，MySQL都会对这个 SQL进行校验、编译。所以说 preparedStatement的效率比statement效率高，前提是第二次以后的执行，第一次执行并不占优势。而且前提是 MySQL服务器开起了预编译和缓存功能。
防止 SQL注入：preparedStatement 向 MySQL服务器发送 SQL时，首先发送的是带参的 SQL语句，也就是发送的是占位符，并没有将具体的参数值同时发送给服务器，然后 MySQL对 SQL语句进行了校验、编译以后，preparedStatement再将具体的参数值发送给服务器，然后执行是 SQL语句，因为编译是讲过 SQL语法编译成了能够被执行的函数，函数会对传入的参数值进行类型校验，传入的参数不符合函数的参数类型时，会发生异常，所以 preparedStatement能够防止 SQL注入。而 statement向 MySQL发送 SQL语句时，直接将拼接好的 SQL发送给了MySQL服务器，很容易发生 SQL注入问题。

注意：

可执行函数存储在 MySQL服务器中，当前连接断开后，MySQL服务器会清除已经存储的可执行函数（也就是只在当前连接生效）
MySQL 4.1之前的版本不支持预编译，MySQL驱动版本5.0.5之后默认预编译是关闭的。另外，数据库的预编译需要同时开启预编译功能和缓存功能的，但是在jdk1.7以后 connection实现类自动关闭接口，使用后直接关闭连接，同一连接内大量执行同一 SQL的场景并不多见，所以预编译的使用范围并不广，可能这也是 5.0.5以后默认关闭的原因之一吧。
- 设置参数开启缓存和预编译：useServerPrepStmts=true&cachePrepStmts=true

总的来说，preparedStatement与statement的区别，就在于预编译、效率、防止 SQL注入这三点上，而关键就在于预编译

总结：

Statement接口在创建语句对象时，不带参数，进行查询更新操作时，再传入完整的SQL 语句。而PreparedStatement接口创建预处理语句对象时，传入带 **? **站位符的SQL语句，进行查询更新操作时，不带参数。
PreparedStatement接口代码可读性高，无需拼接SQL语句，语句和代码分离。
PreparedStatement接口执行效率高，它预编译和缓存了结构相同的SQL语句，像String常量池。
PreparedStatement接口安全性更高，避免了产生SQL漏洞注入。

6.在进行数据库编程时，连接池有什么作用？

由于创建连接和释放连接都有很大的开销（尤其是数据库服务器不在本地时，每次建立连接都需要进行TCP的三次握手，释放连接需要进行TCP四次握手，造成的开销是不可忽视的），为了提升系统访问数据库的性能，可以事先创建若干连接置于连接池中，需要时直接从连接池获取，使用结束时归还连接池而不必关闭连接，从而避免频繁创建和释放连接所造成的开销，这是典型的用空间换取时间的策略（浪费了空间存储连接，但节省了创建和释放连接的时间）。

池化技术在Java开发中是很常见的，在使用线程时创建线程池的道理与此相同。

基于Java的开源数据库连接池主要有：C3P0、Proxool、DBCP、BoneCP、Druid等。

补充：在计算机系统中时间和空间是不可调和的矛盾，理解这一点对设计满足性能要求的算法是至关重要的。大型网站性能优化的一个关键就是使用缓存，而缓存跟上面讲的连接池道理非常类似，也是使用空间换时间的策略。可以将热点数据置于缓存中，当用户查询这些数据时可以直接从缓存中得到，这无论如何也快过去数据库中查询。当然，缓存的置换策略等也会对系统性能产生重要影响，对于这个问题的讨论已经超出了这里要阐述的范围。

7.事务的 ACID 是指什么？

原子性(Atomic)：事务中各项操作，要么全做要么全不做，任何一项操作的失败都会导致整个事务的失败；
一致性(Consistent)：事务结束后系统状态是一致的；
隔离性(Isolated)：并发执行的事务彼此无法看到对方的中间状态；
持久性(Durable)：事务完成后所做的改动都会被持久化，即使发生灾难性的失败。通过日志和同步备份可以在故障发生后重建数据。

8.编写以下 3 条 SQL 语句

有以下这个表：

Student(Sid,Sname,Sage,Ssex) 学生表

Course(Cid,Cname,Tid) 课程表

SC(Sid,Cid,score) 成绩表

Teacher(Tid,Tname) 教师表

（1）查询所有同学的学号、姓名、选课数、总成绩；

select st.sid, st.sname,count(*) as '选课数',sum(sc.score) as '总成绩'
from student st,
     course c,
     sc
where st.sid=sc.sid and sc.cid=c.cid group by st.sid order by st.sid asc;

（2）查询不同老师所教不同课程平均分从高到低显示

select t.tname, c.cname, avg(sc.score) as '课程平均分'
from course c,
     sc,
     teacher t
where c.cid = sc.cid
  and c.tid = t.tid
group by c.cname, t.tname
order by avg(sc.score);

（3）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓

名；

select sid, sname
from student
where sid in (select sid
             from sc s1
             where exists(select *
                          from sc s2
                          where s1.sid = s2.sid
                            and s1.cid = 2
                            and s2.cid = 1
                            and s1.score < s2.score));