面试常见问题-mysql

April 12th 2019 | 日常

  • MyISAM 和 InnoDB 的基本区别?
    MYISAM不支持外键和事务处理,采用表锁机制,查询速度稍快,数据存储文件有3个,
    InnoDB支持外键和事务处理,采用行锁机制,查询速度比MYISAM稍慢,数据存储文件只有一个。
  • 线性表和顺序表的区别?
    1、线性表包括顺序表和链表。
    2、顺序表中的元素的地址是连续的,链表里面节点的地址不是连续的,是通过指针连起来的。
  • 如何实现多个线程安全的写入一个文件数据。
    采用锁机制,当一个用户在对此文件进行读写入操作时,将此文件锁定,操作完毕后解除锁定,在该用户进行读写入操作过程中,其他用户不能操作此文件,需要排队等待。
  • 优化MYSQL数据库的方法。(4分,多写多得)
    1、选取最适用的字段属性,尽可能减少定义字段长度,尽量把字段设置NOT NULL,例如’省份,性别’,最好设置为ENUM
    2、使用连接(JOIN)来代替子查询:
    a.删除没有任何订单客户:DELETE FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo)
    b.提取所有没有订单客户:SELECT FROM customerinfo WHERE customerid NOT in(SELECT customerid FROM orderinfo)
    c.提高b的速度优化:SELECT FROM customerinfo LEFT JOIN orderid customerinfo.customerid=orderinfo.customerid
    WHERE orderinfo.customerid IS NULL
  • 从表login中选出name字段包含admin的前10条结果所有信息的sql语句
    Select *from login where name like '%admin%' limit 0,10;
    解释:左连接,右连接,内连接,索引。
    1、內连接仅选出两张表中互相匹配的记录.因此,这会导致有时我们需要的记录没有包含进来。内部连接是两个表中都必须有连接字段的对应值的记录,数据才能检索出来。
    2、左连接和右连接都是外部连接,也就是区别于内部连接,它对不满足连接条件的行并不是象内部连接一样将数据完全过滤掉,而是保留一部分数据,行数不会减少。
    3、左连接是只要左边表中有记录,数据就能检索出来,而右边有的记录必要在左边表中有的记录才能被检索出来
    4、右连接是只要右边表中有记录,数据就能检索出来 ;
    5、全连接则会回返回两个表中的所有记录
  • nosql和Mysql的区别
       也即非关系型数据库和关系型数据库。
    目前世界上主流的存储系统大部分还是采用了关系型数据库,其主要有一下优点:
    1.事务处理—保持数据的一致性;
    2.由于以标准化为前提,数据更新的开销很小(相同的字段基本上只有一处);
    3.可以进行Join等复杂查询。
    nosql在优势方面,主要体现在下面这三点: 
    1. 简单的扩展:典型例子是Cassandra,由于其架构是类似于经典的P2P,所以能通过轻松地添加新的节点来扩展这个集群;
    2. 快速的读写:主要例子有Redis,由于其逻辑简单,而且纯内存操作,使得其性能非常出色,单节点每秒可以处理超过10万次读写操作; 
    3. 低廉的成本:这是大多数分布式数据库共有的特点,因为主要都是开源软件,没有昂贵的License成本; 
    4. 
    但瑕不掩瑜,NoSQL数据库还存在着很多的不足,常见主要有下面这几个: 
    1. 不提供对SQL的支持:如果不支持SQL这样的工业标准,将会对用户产生一定的学习和应用迁移成本; 
    2. 支持的特性不够丰富:现有产品所提供的功能都比较有限,大多数NoSQL数据库都不支持事务,也不像 SQL Server和Oracle那样能提供各种附加功能,比如BI和报表等; 
    3. 现有产品的不够成熟:大多数产品都还处于初创期,和关系型数据库几十年的完善不可同日而语; 
  • mongodb与mysql区别
    MySQL是关系型数据库。
       优势:
    在不同的引擎上有不同 的存储方式。
    查询语句是使用传统的sql语句,拥有较为成熟的体系,成熟度很高。
    开源数据库的份额在不断增加,mysql的份额页在持续增长。
       缺点:
    在海量数据处理的时候效率会显著变慢。
    Mongodb是非关系型数据库(nosql ),属于文档型数据库。文档是mongoDB中数据的基本单元,类似关系数据库的行,多个键值对有序地放置在一起便是文档,语法有点类似javascript面向对象的查询语言,它是一个面向集合的,模式自由的文档型数据库。
    存储方式:虚拟内存+持久化。
    查询语句:是独特的Mongodb的查询方式。
    适合场景:事件的记录,内容管理或者博客平台等等。
    架构特点:可以通过副本集,以及分片来实现高可用。
    数据处理:数据是存储在硬盘上的,只不过需要经常读取的数据会被加载到内存中,将数据存储在物理内存中,从而达到高速读写。
    成熟度与广泛度:新兴数据库,成熟度较低,Nosql数据库中最为接近关系型数据库,比较完善的DB之一,适用人群不断在增长。
    优点:
    快速!在适量级的内存的Mongodb的性能是非常迅速的,它将热数据存储在物理内存中,使得热数据的读写变得十分快。高扩展性,存储的数据格式是json格式!
    缺点:
    不支持事务,而且开发文档不是很完全,完善。

     

  • mongodb基于什么开发的
    MongoDB是一个基于分布式文件存储的数据库。由C++语言编写。旨在为WEB应用提供可扩展的高性能数据存储解决方案。
  • mongodb是非范式还是范式
    数据表示的方式有很多种,其中最重要的问题之一就是在多大程度上对数据进行范式化。范式化(normalization)是将数据分散到多个不同的集合,不同集合之间可以相互引用数据。虽然很多文档可以引用某一块数据,但是这块数据只存储在一个集合中。所以,如果要修改这块数据,只需修改保存这块数据的那一个文档就行了。但是,MongoDB没有提供连接(join)工具,所以在不同集合之间执行连接查询需要进行多次查询。
    反范式化(denormalization)与范式化相反:将每个文档所需的数据都嵌入在文档内部。每个文档都拥有自己的数据副本,而不是所有文档共同引用同一个数据副本。这意味着,如果信息发生了变化,那么所有相关文档都需要进行更新,但是在执行查询时,只需要一次查询,就可以得到所有数据。
    决定何时采用范式化何时采用反范式化时比较困难的。范式化能够提高数据写入速度,反范式化能够提高数据读取速度。需要根据自己应用程序的十几需要仔细权衡。
  • Mysql和Mongodb主要应用场景(简单了解叙述下即可)
    1.如果需要将mongodb作为后端db来代替mysql使用,即这里mysql与mongodb 属于平行级别,那么,这样的使用可能有以下几种情况的考量: (1)mongodb所负责部分以文档形式存储,能够有较好的代码亲和性,json格式的直接写入方便。(如日志之类) (2)从data models设计阶段就将原子性考虑于其中,无需事务之类的辅助。开发用如nodejs之类的语言来进行开发,对开发比较方便。 (3)mongodb本身的failover机制,无需使用如MHA之类的方式实现。
    2.将mongodb作为类似redis ,memcache来做缓存db,为mysql提供服务,或是后端日志收集分析。 考虑到mongodb属于nosql型数据库,sql语句与数据结构不如mysql那么亲和 ,也会有很多时候将mongodb做为辅助mysql而使用的类redis memcache 之类的缓存db来使用。 亦或是仅作日志收集分析。
  • 数据库设计时,常遇到的性能瓶颈有哪些,常有的解决方案?
    1) 查询速度慢  ,避免全盘扫描,因为全盘扫描会导致大量磁盘 I/O 操作 用 sphinx 来解决
    2) 读写过于频繁 读写分离来做
    3) 设置主从,并做好冗余备份
    4) 数据库压力过大 采用分布式数据库负载均衡来解决
    5) memcache 缓存层
    6) 链接数据库使用 PDO,或者 mysqli 预处理缓存字段
    7) 索引优化
  • 如何设计或配置Mysql,才能达到高效使用的目的。
    1、数据库设计方面,设计结构良好的数据库,允许部分数据冗余。
    选取最适用的字段属性,尽可能把字段设置为NOTNULL,这样在查询的时候,数据库不用去比较NULL值。
    2、系统架构设计方面,表散列,把海量数据散列到几个不同的表里面,集群,数据库查询和写入分开。
    写高效sql语句,以提高效率。
    使用连接(join)来代替子查询
    使用联合(union)来代替手动创建的临时表
    所得皆必须,只从数据库取必须的数据。
    必要的时候用不同的存储引擎,比如Innodb可以减少死锁,HEAP可以提高一个数量级的查询速度。
    使用事务
    使用外键
    使用索引
  • mysql优化怎么做的?
    答:mysql优化主要从以下几个方面来实现:
      1设计角度:存储引擎的选择,字段类型选择,范式
      2功能角度:可以利用mysql自身的特性,如索引,查询缓存,碎片整理,分区、分表等
      3sql语句的优化方面:尽量简化查询语句,能查询字段少就尽量少查询字段,优化分页语句、分组语句等。
      4部署大负载架构体系:数据库服务器单独出来,负载大时可以采用主从复制,读写分离机制进行设计
      5从硬件上升级数据库服务器。

    - mysql优化,读写分离如何实现?

    mysql优化前面已经总结了。主要说下读写分离,当我们的数据量很大时,数据库服务器的压力变大,这时候我们需要从架构方面来解决这一问题,
    在一个网站中读的操作很多,写的操作很少,这时候我们需要配置读写分离,把读操作和写操作分离出来,最大程度的利用好数据库服务器。
    读写分离的实现原理就是在执行SQL语句的时候,判断到底是读操作还是写操作,把读的操作转向到读服务器上(从服务器,一般是多台),
    写的操作转到写的服务器上(主服务器,一般是一台,视数据量来看)。
    当然为了保证多台数据库数据的一致性,需要主从复制。
    主从复制的实现原理是:mysql中有一种日志,叫做bin日志(二进制日志),会记录下所有修改过数据库的sql语句。
    主从复制的原理实际是多台服务器都开启bin日志,然后主服务器会把执行过的sql语句记录到bin日志中,之后从服务器读取这个bin日志,把该日志的内容保存到自己中继日志里面,
    从服务器再把中继日志中记录的sql语句同样的执行一遍。这样从服务器上的数据就和主服务器相同了。

    酒店预订怎么实现?怎么设计表

      你好,我大概的说下我们的业务流程,我们的业务流程是:用户在网站浏览酒店信息,可以根据地区检索出该地区的酒店信息。列表展示酒店的信息由:酒店的名称,酒店图片,酒店位置,评论人数,评论分数以及最低入住价格。用户选中要入住的酒店进入酒店详情页面,查看酒店的介绍以及酒店的房型列表,用户根据他要入住的时间和离店的时间,检索出这个时间段内的所有可选房型(房间数量-当天的订单-当天未离店订单=剩余房间数量)显示给用户。用户选择好房型后就可以进行下单,要求有订单的开始时间,结束时间,房间数量,住客姓名,抵店时间,联系方式,备注信息等等。
     那我的表是这么设计的,总共有6张表,分别是:
    用户表user,里面有下面几个字段,(用户编号,用户名称,用户密码,用户联系方式)
    酒店表hotel,里面有(酒店编号,酒店名称,酒店图片,评论人数,评论分数,最低入住价格,所在地区)
    酒店图片表pic(图片编号,图片地址,图片排序,图片所属酒店)
    评论表comment(评论编号,评论内容,评论时间,用户编号,酒店编号)
    房型表house(房型编号,床型,早餐,宽带,人数上限,房价,房间数量,最长预定时间)
    订单表order(订单编号,开始时间,结束时间,房间数量,住客姓名,最晚抵店时间,联系电话,使用优惠券,备注,订单状态)
    以上就是我对这个酒店预订系统的设计
    预定时间怎么写入数据库的
    以预订当时的时间戳作为预订时间写入数据库。用户下订单时会选择一个抵店时间,将该抵店时间以时间戳方式存入数据库中。离店时间以当时的日期转为时间戳方式存入数据库中
    怎么判断还有没有房间
    我可以根据用户的入住时间和离店时间来检索这个有效时间段内房间的库存。房间数量扣除在这个时间段内入住的订单和在这个时间段内离店的订单。扣除后等到的数量才是这段时间内有效房间数量。
    怎么记录每天的房间库存
    我的思路是根据一个公式来推理实现的,每天房间的库存=房型下房间数量-(当天入住的订单+当天未离店的订单),这样我就可以得到每天还有多少房间是剩余的了。
    怎么在数据库里对房间做唯一标识
    上面所设计的房型表就是我们的房间表,每个房间是唯一的,我们是使用数字作为编号的,也即使用主键作为唯一标识。
  • 订单表用是什么存储引擎
    答:因为订单表存在着事务的处理,比如下了订单,商品的库存就要减少,这里就涉及到了事务,所以就用到innodb。
  • MySQL存储引擎中MyISAM和InnoDB,在同样的应用场景中各有什么优缺点,索引结构如何实现?
    MyISAM不支持外键和事务处理,但是查询速度比InnoDB类型的稍快。
    InnoDB类型数据库支持外键和事务处理,查询速度比MyISAM稍慢。
  • mysql存储引擎
    答:常用的主要分为两种,一种是innodb,一种是myisam,两者的主要区别是
    1myisam不支持事务处理,而innoDB支持事务处理
    2myisam 不支持外键,innoDB支持外键
    3myisam支持全文检索,而innoDB在MySQL5.6版本之后才支持全文检索
    4④数据的存储形式不一样,mysiam表存放在三个文件:结构、索引、数据,innoDB存储把结构存储为一个文件,索引和数据存储为一个文件
    5myisam在查询和增加数据性能更优于innoDB,innoDB在批量删除方面性能较高。
    6myisam支持表锁,而innoDB支持行锁
  • sql语句的优化
    答:首先我们得确定哪些sql语句需要优化,一般在一个系统中,查询语句最多,所以我们主要是针对查询语句进行优化。主要采用两种方式来确定要优化的sql语句:
       1使用慢查询日志,设置需要优化的sql语句的执行时间,记录下超过该设置时间的语句,即为需要优化的语句。
       2使用profiling机制,记录下每条sql语句的执行时间,找出执行较慢的语句,即为需要优化的语句。
       我们主要通过给表字段添加索引的方式进行优化,加上索引后,sql语句的执行时间显著提高了,但并不是加上索引了这条sql语句就会用到索引,所以首先看执行慢的语句后面是否有加索引,我们可以使用explain或者desc加在要执行的sql语句前,查看是否使用到索引。有几个地方需要注意的是:
      1为了避免建议索引而造成索引文件过大,有时候我们会使用复合索引,这时候要遵循最左原则。
      2like查询,前%不会用到索引
      3如果条件中有or,则要求or的索引字段都必须有索引,否则不能用到索引。
      4如果列类型是字符串,一定要在条件中将数据使用引号引用起来,否则不使用索引。
      5优化group by 语句
      6尽量避免模糊匹配,这样会导致全盘扫描
  • 什么是队列?排它锁,Myisam死锁如何解决?

    在默认情况下MYisam是表级锁,所以同时操作单张表的多个动作只能以队列的方式进行;
    排它锁又名写锁,在SQL执行过程中为排除其它请求而写锁,在执行完毕后会自动释放;
    死锁解决:先找到死锁的线程号,然后杀掉线程ID
  • 事务处理:
    a.保证数据完整性,例如添加和修改同时,两者成立则都执行,一者失败都失败
    mysql_query(“BEGIN”);
    mysql_query(“INSERT INTO customerinfo (name) 8i VALUES (‘$name1′)”;
    mysql_query(“SELECT * FROM `orderinfo` where customerid=”.$id”);
    mysql_query(“COMMIT”);
  • 锁定表,优化事务处理:
    a.我们用一个 SELECT 语句取出初始数据,通过一些计算,用 UPDATE 语句将新值更新到表中。
    包含有 WRITE(write) 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 命令被执行之前,
    不会有其它的访问来对 inventory 进行插入、更新或者删除的操作
    mysql_query(“LOCK TABLE customerinfo READ, orderinfo WRITE”);
    mysql_query(“SELECT customerid FROM `customerinfo` where id=”.$id);
    mysql_query(“UPDATE `orderinfo` SET ordertitle=’$title’ where customerid=”.$id);
    mysql_query(“UNLOCK TABLES”);
  • 数据库中的事务是什么?
    事务就是一系列的操作,这些操作完成一项任务,只要这些操作中有一个没有操作成功,事务就操作失败,发生回滚事件,即撤销当前操作,这样可以保证数据的一致性。而且可以把操作暂时放在缓存里,等所有操作都都成功在提交到数据库,这样保证了操作的有效性。
  • Mysql事务的特性
    事务是作为一个逻辑单元执行的一系列操作,一个逻辑工作单元必须有四个属性,称为 ACID(原子性、一致性、隔离性和持久性)属性,只有这样才能成为一个事务:
    原子性
    事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。
    一致性
    事务在完成时,必须使所有的数据都保持一致状态。在相关数据库中,所有规则都必须应用于事务的修改,以保持所有数据的完整性。
        事务结束时,所有的内部数据结构(如 B 树索引或双向链表)都必须是正确的。
    隔离性
    由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态,要么是另一并发事务修改它之前的状态,
           要么是另一事务修改它之后的状态,事务不会查看中间状态的数据。这称为可串行性,因为它能够重新装载起始数据,
           并且重播一系列事务,以使数据结束时的状态与原始事务执行的状态相同。
    持久性
    事务完成之后,它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。
    begin 开始一个事务
    rollback事务回滚
    commit事务确认
  • Mysql事务的应运场景
    事务处理在各种管理系统中都有着广泛的应用,比如人员管理系统,很多同步数据库操作大都需要用到事务处理。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!
    比如手机充值过程,支付宝金额减少,相应的手机话费增加,只要有一个操作不成功,则另外一个操作也不会成功
  • 什么是数据库索引,主键索引,唯一索引的区别,索引的缺点是什么?
    索引用来快速地寻找那些具有特定值的记录。
    主键索引和唯一索引的区别:主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”,每个表只能有一个主键。唯一索引索引列的所有值都只能出现一次,即必须唯一。
    索引的缺点:
    1、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
    2、索引需要占用物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,需要的空间就会更大。
    3、当对表中 的数据进行增加、删除、修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
  • 索引的建立与使用
         索引就是类似书的目录,提高检索数据的效率。
    索引是系统按照某个具体的算法(哈希,散列,二叉树),将数据从全部数据里进行提取,维护成一个索引文件,然后系统在进行数据查询的时候,发现如果查询条件刚好满足索引条件,就可以从索引文件中快速的定位的数据所在位置。
    mysql中有以下几种索引:
    主键索引(primary key效率最高的索引)
    唯一索引(unique key):不为空的情况下效率最高
    普通索引(index)对数据没有要求,文件很大,效率比较低
    全文索引(fulltext),对整个文章内部进行关键字索引(mysql5.5以后InnoDB支持全文索引)
    英文的全文索引很简单:英文单词默认是用空格分离的
    中文的全文索引很难:中文的词组成很麻烦,需要利用分词工具(sphinx)
     
         索引可以在创建表的同时创建索引,也可以在修改表结构时添加索引,索引主要是加在经常做为查询条件的字段上,可以使用相应的手段来检测所执行的sql语句中是否使用到了索引。
  • 对关系型数据库而言,索引是相当重要的概念,请回答有关索引几个问题:
    a) 索引的目的是什么?
    b) 索引对数据库系统的负面影响是什么?
    c) 为数据表建立索引的原则有哪些?
    d) 什么情况下不宜建立索引?
    a) 索引的目的:
    1、快速访问数据表中的特定信息,提高检索速度
    2、创建唯一性索引,保证数据库表中每一行数据的唯一性
    3、加速表和表之间的连接
    4、使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间
    b) 负面影响:
    创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加;索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间;当对表进行增、删、改的时候索引也要动态维护,这样就降低了数据的维护速度。
    c) 建立索引的原则:
    1、在最频繁使用的、用以缩小查询范围的字段上建立索引
    2、在平频繁使用的、需要排序的字段上建立索引
    d) 什么情况下不宜建立索引:
    1、对于查询中很少涉及的列或者重复值比较多的列,不宜建立索引
    2、对于一些特殊的数据类型,不宜建立索引,比如文本字段(text)等。
  • 建立索引:
    a.格式:
    (普通索引)->
    创建:CREATE INDEX <索引名> ON tablename (索引字段)
    修改:ALTER TABLE tablename ADD INDEX [索引名] (索引字段)
    创表指定索引:CREATE TABLE tablename([...],INDEX[索引名](索引字段))
    (唯一索引)->
    创建:CREATE UNIQUE <索引名> ON tablename (索引字段)
    修改:ALTER TABLE tablename ADD UNIQUE [索引名] (索引字段)
    创表指定索引:CREATE TABLE tablename([...],UNIQUE[索引名](索引字段))
    (主键)->
    它是唯一索引,一般在创建表是建立,格式为:
    CREATA TABLE tablename ([...],PRIMARY KEY[索引字段])
  • 索引的优缺点
    1、优点:
    a)可以保证数据库表中每一行的数据的唯一性
    b)可以大大加快数据的索引速度
    c)加速表与表之间的连接,物别是在实现数据的参考完事性方面特别有意义
    d)在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间
    f)通过使用索引,可以在时间查询的过程中,使用优化隐藏器,提高系统的性能
    2、缺点:
    创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
    索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间,如果需要建立聚簇索引,那么需要占用的空间会更大
    以表中的数据进行增、删、改的时候,索引也要动态的维护,这就降低了整数的维护速度
    建立索引的原则
    在经常需要搜索的列上,可以加快搜索的速度
    在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构
    在经常用在连接的列上,这些列主要是一外键,可以加快连接的速度
    在经经常需要根据范围进行搜索的列上创建索引,国为索引已经排序,其指定的范围是连续的
    在经常需要排序的列上,国为索引已经排序,这样井底可以利用索引的排序,加快排序井底时间
    在经常使用在where子句中的列上,加快条件的判断速度
  • 创建索引的原则  
    索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该 仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引, 例如:在经常需要搜索的列上,可以加快搜索的速度;在作为 主键的列上,强制该列的唯一性和组织表中数据的排列结构;在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的 这些列具有下列特点:
    第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。
    第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的 取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加 快检索速度。
    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。
  • 索引有几种
    答:索引主要有:
          主键索引:数据记录里面不能有null,数据内容不能重复,在一张表里面不能有多个主键索引。
          普通索引:使用字段关键字建立的索引,主要是提高查询速度
          唯一索引:字段数据是唯一的,数据内容里面能否为null,在一张表里面,是可以添加多个唯一索引。
          全文索引:在比较老的版本中,只有myisam引擎支持全文索引,在innodb5.6后引擎也支持全文索引,在mysql中全文索引不支持中文。我们一般使用sphinx集合coreseek来实现中文的全文索引。
  • 左前索引原则
    答:左前索引主要指的是在复合索引中,给两个或多个字段建立了复合索引后,在sql语句后的条件中,只有复合索引前面的字段在条件的前面时,该索引才起作用,比如创建了个复合索引index (a,b),在使用where或者orderby条件时,如果只有条件b的,该索引不会生效,必须有条件a且必须要在条件b的前面该索引才会生效。
  • MySQL数据库中的字段类型varchar和char的主要区别是什么?那种字段的查找效率要高,为什么?
    Varchar是变长,节省存储空间,char是固定长度。查找效率要char型快,因为varchar是非定长,必须先查找长度,然后进行数据的提取,比char定长类型多了一个步骤,所以效率低一些
  • 请写出数据类型(int char varchar datetime text)的意思;请问 varchar 和 char有什么区别? 
    Int 整数 char 定长字符 
    Varchar 变长字符 
    Datetime 日期型 
    Text 文本型 Varchar 与char的区别 char是固定长度的字符类型,分配多少空间,就占用多长空间。 
    Varchar是可变长度的字符类型,内容有多大就占用多大的空间,能有效节省空间。 
    由于varchar类型是可变的,所以在数据长度改变的时,服务器要进行额外的操作,所以效率比char类型低。
  • 使用联合(UNION)来代替手动创建的临时表
    a.创建临时表:SELECT name FROM `nametest` UNION SELECT username FROM `nametest2`
  • 使用php写一段简单查询,查出所有姓名为“张三”的内容并打印出来 (2分)
      表名User
    Name Tel Content Date
      张三 13333663366 大专毕业 2006-10-11
      张三 13612312331 本科毕业 2006-10-15
      张四 021-55665566 中专毕业 2006-10-15
      请根据上面的题目完成代码:
    $mysql_db=mysql_connect("local","root","pass");
    @mysql_select_db("DB",$mysql_db);
    $result = mysql_query("SELECT * FROM `user` WHERE name='张三'");
    while($rs = mysql_fetch_array($result)){
    echo $rs["tel"].$rs["content"].$rs["date"];
    }
  • 写出 SQL语句的格式 : 插入 ,更新 ,删除 (4分)
      表名User
    Name Tel Content Date
      张三 13333663366 大专毕业 2006-10-11
      张三 13612312331 本科毕业 2006-10-15
      张四 021-55665566 中专毕业 2006-10-15
    (a) 有一新记录(小王 13254748547 高中毕业 2007-05-06)请用SQL语句新增至表中
    mysql_query("INSERT INTO `user` (name,tel,content,date) VALUES
    ('小王','13254748547','高中毕业','2007-05-06')")
    (b) 请用sql语句把张三的时间更新成为当前系统时间
    $nowDate = date("Ymd");
    mysql_query("UPDATE `user` SET date='".$nowDate."' WHERE name='张山'");
    (c) 请写出删除名为张四的全部记录
    mysql_query("DELETE FROM `user` WHERE name='张四'");
  • 完成以下:
    (一)创建新闻发布系统,表名为message有如下字段 (3分)
    id 文章id
    title 文章标题
    content 文章内容
    category_id 文章分类id
    hits 点击量
    CREATE TABLE 'message'(
    'id' int(10) NOT NULL auto_increment,
    'title' varchar(200) default NULL,
    'content' text,
    'category_id' int(10) NOT NULL,
    'hits' int(20),
    PRIMARY KEY('id');
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    (二)同样上述新闻发布系统:表comment记录用户回复内容,字段如下 (4分)
    comment_id 回复id
    id 文章id,关联message表中的id
    comment_content 回复内容
      现通过查询数据库需要得到以下格式的文章标题列表,并按照回复数量排序,回复最高的排在最前面
      文章id 文章标题 点击量 回复数量
      用一个SQL语句完成上述查询,如果文章没有回复则回复数量显示为0
    SELECT message.id id,message.title title,IF(message.`hits` IS NULL,0,message.`hits`) hits,
    IF(comment.`id` is NULL,0,count(*)) number FROM message LEFT JOIN
    comment ON message.id=comment.id GROUP BY message.`id`;
    (三)上述内容管理系统,表category保存分类信息,字段如下 (3分)
    category_id int(4) not null auto_increment;
    categroy_name varchar(40) not null;
      用户输入文章时,通过选择下拉菜单选定文章分类
      写出如何实现这个下拉菜单
    function categoryList()
    {
    $result=mysql_query("select category_id,categroy_name from category")
    or die("Invalid query: " . mysql_error());
    print("
    ");
    }
  • 在数据库test中的一个表student,字段是name,class,score。分别代表姓名、所在班级,分数。
    1) 学出每个班级中的学生,按照成绩降序排序;
    2) 查出每个班的及格人数和不及格人数,格式为:class、及格人数、不及格人数;
    3) 用PHP写入连接数据库(“localhost”,”msuser”,”mspass”)、执行以上SQL、显示结果、判断错误、关闭数据库的过程;
    1、select * from student order by score desc;
    2、Select A.class,A.及格人数,B.不及格人数 from (select class, count(*) as ’及格人数‘ from student where score>=60 group by class) as A,(select  class,count(*) as ’不及格人数‘ from student where score<60 group by class) as B where A.class=B.class;
    3、$conn=mysql_connect(‘localhost’,'root’,’12345′);
    Mysql_select_db(‘test’);
    $sql=”";
    $result=Mysql_query($sql);
    Mysql_close();
  • 请写出5个常用的SQL操作关键字。
    Select ,update, delete, insert into, create ,group by ,order by
  • 有两张数据表,表user(id, name)记录了用户的ID和昵称,表article(id, title, content, time)记录了用户发表的文章标题、内容和时间,写一个SQL语句打印出每个用户的昵称及其发表的文章总数。
    Select user.name,A.id,A.总数 from user,(select id,count(*) as ’总数‘ from article group by id) as A where user.id=A.id;
  • MYSQL取得当前时间的函数是?,格式化日期的函数是(2分)
    now(),date()
  • 使用外键,优化锁定表
    a.把customerinfo里的customerid映射到orderinfo里的customerid,
    任何一条没有合法的customerid的记录不会写到orderinfo里
    CREATE TABLE customerinfo
    (
    customerid INT NOT NULL,
    PRIMARY KEY(customerid)
    )TYPE = INNODB;
    CREATE TABLE orderinfo
    (
    orderid INT NOT NULL,
    customerid INT NOT NULL,
    PRIMARY KEY(customerid,orderid),
    FOREIGN KEY (customerid) REFERENCES customerinfo
    (customerid) ON DELETE CASCADE
    )TYPE = INNODB;
    注意:’ON DELETE CASCADE’,该参数保证当customerinfo表中的一条记录删除的话同时也会删除order
    表中的该用户的所有记录,注意使用外键要定义事务安全类型为INNODB;
  • 优化查询语句
    a.最好在相同字段进行比较操作,在建立好的索引字段上尽量减少函数操作
    例子1:
    SELECT * FROM order WHERE YEAR(orderDate)<2008;(慢)
    SELECT * FROM order WHERE orderDate<"2008-01-01";(快)
    例子2:
    SELECT * FROM order WHERE addtime/7<24;(慢)
    SELECT * FROM order WHERE addtime<24*7;(快)
    例子3:
    SELECT * FROM order WHERE title like "%good%";
    SELECT * FROM order WHERE title>=”good” and name<"good";
  • 一个表中的Id有多个记录,把所有这个id的记录查出来,并显示共有多少条记录数,用SQL语句及视图、
    存储过程分别实现。
    DELIMITER //
    create procedure proc_countNum(in columnId int,out rowsNo int)
    begin
    select count(*) into rowsNo from member where member_id=columnId;
    end
    call proc_countNum(1,@no);
    select @no;
    方法:视图:
    create view v_countNum as select member_id,count(*) as countNum from member group by
    member_id
    select countNum from v_countNum where member_id=1
  • 你常用到的mysql命令?
    Select * from table
    Update table set field=’value’ where id=
    Delete from where id=
    Insert into table values(‘value1′,’value2′)
    Create table tablename
    Show tables
  • 进入mysql管理命令行的命令?
    Mysql -u root -p
  • show create database mysql; 这个命令的作用?
    显示创建数据库的sql语句
  • show create table user; 这个命令的作用?
    显示创建表的sql语句
  • desc user; 这个命令的作用?
    查询user表的结构
  • explain select * from user; 这个命令的作用?
    获取select相关信息
  • show processlist; 这个命令的作用?
    显示哪些线程正在运行
  • SHOW VARIABLES; 这个命令的作用?
    显示系统变量和值
  • SHOW VARIABLES like ’%conn%’; 这个命令的作用?
    显示系统变量名包含conn的值
  • LEFT JOIN 写一个SQL语句?
    Select A.id,A.class from A left join B on A.cid=B.id
  • in, not in, exist, not exist的作用和区别?
    in在什么中
    Not in 不在什么中
    Exists 存在
    Not exists 不存在
  • 创建索引:
    alert table tablename add index (`字段名`)
  • 如下user表结构
    名称 类型 说明 备注
    Uid Int unsigned 主键
    Name Varchar(20)
    Age Tinyint unsigned
    需求:
    l 增加一个字段性别sex,写出修改语句
    Alert table user add sex enum(’0′,’1′);

    l 查询出年龄介于20岁到30岁之间的用户

    Select * from user where age>20 and age<30
  • 在数据库test中的一个表student,字段是name,class,score。分别代表姓名、所在班级,分数。 
    1) 查出每个班级中的学生,按照成绩降序排序; 
    2) 查出每个班的及格人数和不及格人数,格式为:class、及格人数、不及格人数;
    3) 用PHP写入连接数据库("localhost","msuser","mspass")、执行以上SQL、显示结果、判断错误、关闭数据库的过程; 
    1) SELECT * FROM student ORDER BY score desc;
    <?php 
    $conn=mysql_connect('localhost','root','123456'); 
    @mysql_select_db('test');  
    $sql="";  
    $result=Mysql_query($sql);
    If($result && mysql_affected_rows()){ ............................. }
    Mysql_close($conn);
  • 写出发贴数最多的十个人名字的SQL,利用下表:members(id,username,posts,pass,email)(2分)
    SELECT * FROM `members` ORDER BY posts DESC limit 0,10;