mysql知识点（二）

1.什么叫聚集索引和非聚集索引？

答：

聚集索引：该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

     聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

非聚集索引：数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

      非聚集索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。对于非聚集索引，可以为在表非聚集索引中查找数据时常用的每个列创建一个非聚集索引。有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。

2.外键的参考完整性？

答：

雇员表是部门字段使用了外键

05 不存在与部门表中，所以不能插入，否则违反了参考完整性。

 

3.创建符合数据库三范式的表，比如：

答：

雇员信息表： 

雇员号 雇员名 部门号 工资 

001       张山      02         2000 

010      王宏达   01         1200 

056      马林生   02         1000 

101       赵敏      04         1500 

部门信息表 

部门号 部门名 主任 

01         业务部 李建 

02         销售部 应伟东 

03         服务部 周垠 
04         财务部 陈力胜

 

4.数据库中什么叫视图?

答：视图（View）是从一个或多个表（或视图）导出的表。视图与表（有时为与视图区别，也称表为基本表——Base Table）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

1.若视图是由两个以上基本表导出的，则此视图不允许更新。
2.若视图的字段来自字段表达式或常数，则不允许对视图执行INSTER和UPDATE操作，但允许delete。
3.若视图的字段来自聚集函数，则此视图不允许更新。
4.若视图中含有GROUP by子句，则此视图不允许更新。
5.若视图中含有DISTINCT短语，则此视图不允许更新。.
6若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。.
7.一个不允许更新的视图上定义的视图不允许更新。

 

视图除了进行查询记录外，也可以利用视图进行插入、更新、删除记录的操作，减少对基表中信息的直接操作，提高了数据的安全性。 
在视图上使用INSERT语句添加数据时，要符合以下规则。 
（1）使用INSERT语句向数据表中插入数据时，用户必须有插入数据的权利。 
（2）由于视图只引用表中的部分字段，所以通过视图插入数据时只能明确指定视图中引用的字段的取值。而那些表中并未引用的字段，必须知道在没有指定取值的情况下如何填充数据，因此视图中未引用的字段必须具备下列条件之一。 
      该字段允许空值。 
      该字段设有默认值。 
      该字段是标识字段，可根据标识种子和标识增量自动填充数据。 
      该字段的数据类型为timestamp或uniqueidentifier。 
（3）视图中不能包含多个字段值的组合，或者包含使用统计函数的结果。 
（4）视图中不能包含DISTINCT或GROUP BY子句。 
（5）如果视图中使用了WITH CHECK OPTION，那么该子句将检查插入的数据是否符合视图定义中SELECT语句所设置的条件。如果插入的数据不符合该条件，SQL Server会拒绝插入数据。 
（6）不能在一个语句中对多个基础表使用数据修改语句。因此，如果要向一个引用了多个数据表的视图添加数据时，必须使用多个INSERT语句进行添加。

 

6.设有一个关系：DEPT（DNO，DNAME），如果要找出倒数第三个字母为W，并且至少包含4个字母的DNAME，则查询条件子句应写成WHERE DNAME LIKE__________.

答：‘_ % W _ _’

7.查找 student表中所有电话号码(列名：telephone)的第一位为8或6，第三位为0的电话号码？

答：SELECT telephone FROM student WHERE telephone LIKE ‘[8,6]_0%’

 

8.用命令（）可以查看mysql数据库中user表的表结构？

答：desc user;show create table user;

 

9.内连接与外联结？

答：内连接返回的结果集是两个表中所有相匹配的数据，不包含没有匹配的行。

外连接有三种：左外连接，右外连接，全外连接。外连接不仅包含符合连接条件的行，还包含左表（左外连接）、右表（右外连接）或两个表（全外连接）中的所有数据行。对于没有匹配的行就用NULL值来填充。

因此，外连接中：既包含相匹配的行也包括不相匹配的行、不相匹配的行就用NULL值填充、外连接中也可以只有不匹配的行。

 

10.怎样设置主键？

答：最小性原则，我的理解是，单列，int型产生方便，维护方便，易比较，缺点是没啥实际意义。

　　唯一性原则，如果不能有同时重复的useame和password，那么useame和password可以组合在一起作为主键。

11.mysql怎样开启事务？

答：MySQL事务控制语句 

        在mysql命令行的默认下，事务都是自动提交的，sql语句提交后马上会执行commit操作。因此开启一个事务必须使用begin，start transaction，或者执行 set autocommit=0;
 可以使用的事务控制语句

start transction | begin : 显示的开启一个事务

 

12.有订单表orders，包含字段用户信息userid，字段产品信息productid，以下语句能够返回至少被订购过两会的productid？

答：select productid from orders group by productid having count（productid）>1

 

13.建立索引的原则？

答：

 

14.

表中删除主键为：

alert table table_test drop primary key;

表中增加主键为：

alert table table_test add primary key(id);

 

15.在一个表中可以建立（）

答：多个普通索引，多个唯一索引，多个候选索引，一个主索

 

16.mysql 数据库有选课表 lea(student_id int,course_id int),字段分别表示学号和课程编号, 现在想获取每个学生所选课程的个数信息,请问如下的 sql 语句正确的是()？

答：select student_id,count(course_id)from lea group by student_id

 

17.未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据提交读(Read Committed)：只能读取到已经提交的数据.可重复读(Repeated Read)：在同一个事务内的查询都是事务开始时刻一致的。

18.关于group by子句的作用描述正确的是?

答：

用于过滤数据的子句是where

根据题目给出的答案去做推论

首先group by的作用是用来分组，这点明确了；然后看答案

A过滤数据，这个和分组没什么关系pass

然后BC sum和avg两个函数分别是求和和求平均，这个时候分组就有存在价值了，一组数据的和，一组数据的平均数两个；

D having这个关键字本身就是用来和分组一起使用的，是分组后的过滤条件

GroupBy语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。

 

19.为了提高数据的查询效率，需要在数据库中建立索引，则下列设计索引的原则描述正确的是（）

答：

在频繁进行排序或分组（即进行group by 或order by操作）的列上建立索引

考虑列中指的分布，列的基数越大，索引的效果越好
不包含（在select关键字后选择列表的列上，建立索引，错，应该是where命令）

表的某个字段值得离散度越高，该字段越适合选作索引的关键字。主键字段以及唯一性约束字段适合选作索引的关键字，原因就是这些字段的值非常离散。尤其是在主键字段创建索引时， cardinality （基数，集的势）的值就等于该表的行数。 MySQL 在处理主键约束以及唯一性约束时，考虑周全。数据库用户创建主键约束的同时， MySQL 自动创建主索引（ primary index ），且索引名称为 Primary；数据库用户创建唯一性索引时， MySQL 自动创建唯一性索引（ unique index ），默认情况下，索引名为唯一性索引的字段名。

2、   占用存储空间少的字段更适合选作索引的关键字。例如，与字符串相比，整数字段占用的存储空间较少，因此，较为适合选作索引关键字。

3、   存储空间固定的字段更适合选作索引的关键字。与 text 类型的字段相比， char 类型的字段较为适合选作索引关键字。

4、   Where 子句中经常使用的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。

5、   更新频繁的字段不适合创建索引，不会出现在 where 子句中的字段不应该创建索引。

6、   最左前缀原则。

7、   尽量使用前缀索引。