mysql中常用和面试重点

sql:关系型数据库的标准语言。

关系型数据库的三级模式：外模式：视图。                       视图是从一个或者是从几个基本表导出的表

                                           模式：

                                           内模式：存储文件的形式。       基本表是本省存在的表

sql语句的重点查询

1.新建一个学生表

    Student(sno,Sname,sex,sage,sdept)

     create table Student(

     sno char(9) primary key,

      sname  char(10),

      sex   char(2),

      sage   smallInt,

      sdept   char(20)   );

2.建立索引

  建立索引是加快查询速度的有效手段

  create  index  索引名 on 表名（列名）

3.数据查询（重点）

  select   [distinct] <目标表达式>

  from  表名

  where  条件表达式

  group by  <分组列名、>  having

  order by <列名>

                                                                            student表

学号 sno	姓名 Sname	性别 Ssex	年龄 Ssno	所在系 Sdept
201215121	李勇	男	20	CS
201215122	李晨	女	19	CS
201512123	王敏	女	18	MA
201215125	张立	男	19	IS

                                                                          course表

课程号 Cno	课程名 cname	先行课	学分 Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	6	3
4	操作系统	7	4
5	数据结构		2
6	数据处理	6	4

 

                                                                            SC

学号 sno	课程号 cno	成绩 Grade
201215121	1	92
201215121	2	85
201215121	3	88
201215122	2	90
201215122	3	80

（1）查询选修 课程的学号

        select sno from sc;

(2)选出的结果是重复的

      select distinct sno from sc;

(3)查询学号‘201215121’的学生的详细情况

  select * from student where sno like '201215121';

（4）查询选修了课程名为“信息系统”的学生学号和姓名

select sno,sname

where sno in

(select sno from sc where cno in

(select cno from course where cname="信息系统"))；

4.关系型数据库的理论片

 数据库的范式：

   第一范式：每一个分量不许是不可分的数据项

   第二范式：每一个非主属性依赖于任何一个候选码

   第三范式：没有非主属性

  E-R表达式：主要是用来进行描述实体和属性之间的关系。

数据库编程：

   游标：是为用户开设的一个数据缓冲区，存放sql语句的执行结果

   存储过程和存储函数：

          存储过程：是过程化SQL语句的数据，编译和优化都在服务器中，则成为存储过程

           优点： 不像SQL语句进行语法分析和优化工作，因而运行 效率是比较高的。

                      降低了客户机与服务器之间的通信

   存储函数和存储过程是比较相似的，不同的是函数是具有返回值的

1.函数的定义格式

CREATE    OR  replace function(函数名【参数1，参数2】Returns<类型> as  <过程sql块>)

2.事务：是用户定义数据库操作的序列，这些事要么做要么不做

事务的四个特性：

 原子性：事务的操作要么全做，要么都不做

 隔离性：并发执行时，各个事务之间是不能互相干扰的

一致性：两次操作数据的结果是一致的。

持续性：事务一旦提交，它对数据库中数据的改变就是 永久的。

数据库优化

1. 添加索引
    
    1). 什么时候考虑添加索引
        数据量大 
        查询频次高的表
    2). 针对什么样的字段添加索引?
        
        经常在where条件之后出现的字段 
    3). 数据库表的索引太多会有什么影响?
    
        会insert , update , delete 的效率 ;        
    4). 索引的使用规则?
        
        explain
        EXPLAIN SELECT * FROM product WHERE NAME LIKE '测试商品201%
2. select 语句中 尽量不适用 select * from ..., 使用具体的字段代替 select name , age  from ...; 
   
    ---> select * , * 代表所有的列, 不同的表, 列也不同, 需要解析列, 会查找所有的字段. 效率低 

3. where 和 having, where子句中不能跟组函数. 当使用where 和 having 都可以使用时, 尽量使用where.

    1). select deptno, avg(sal) from emp where deptno = 10 group by deptno;  ---> 先过滤,后分组, 分组的对象数据少, 效率高.

    2). select deptno, avg(sal) from emp  group by deptno having deptno = 10; ---> 先分组后过滤.

4. 如果需要查询多张表, 可以使用子查询, 也可以使用多表查询时, 理论上，尽量使用多表查询 ---> 因为性能高.

    因为子查询, 对数据操作两次或多次, 而多表查询则只操作一次, 所以效率高---------> 不考虑笛卡尔积的情况下.

    但是,如果表中的数据太多, 多表产生的笛卡尔积太大, 会影响多表查询的效率.

5. 尽量不使用集合运算. 

    参与运算的集合越多, 运行效率越低.  UNION(并集) , UNION ALL(全并集) , INTERCEPT(交集) , MINUS(差集)

6. 尽量不适用in操作符.

    ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采
    
    用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程

    在业务密集的SQL当中尽量不采用IN操作符，用 EXISTS 方案代替。

7. 强烈不建议使用 not in 操作符.
    
    此操作是强列不推荐使用的，因为它不能应用表的索引  ;  可以使用 not exists 来替代

8. IS NULL 或 IS NOT NULL 操作（判断字段是否为空）

    判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的 ;

    用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，
   

9. LIKE的模糊查询如果用的不好也会降低性能.

    LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题
    如LIKE '%5400%' 这种查询不会引用索引，而LIKE '5400%'则会使用索
10. 查询表顺序的影响