您当前位置：首页 > 数据库 > 数据库应用 > 【面试】【MySQL常见问题总结】【02】

【面试】【MySQL常见问题总结】【02】

来源：程序员人生发布时间：2016-06-04 15:46:58 阅读次数：2865次

【常见面试问题总结目录>>>】

031 在已存在的表上创建唯1索引

　　1、CREATE UNIQUE INDEX index_name ON table_name (
　　　　field_name [(index_length)] [ASC|DESC])
　　2、ALTER TABLE table_name ADD UNIQUE INDEX|KEY
　　　　index_name(field_name [(index_length)] [ASC|DESC])

032 创建表时创建全文索引

　　table_name(
　　　　column_name
　　　　FULLTEXT INDEX|KEY [index_name](
　　　　　　field_name [(index_length)] [ASC|DESC])
　　)

033 在已存在的表上创建全文索引

　　1、CREATE FULLTEXT INDEX index_name ON table_name (
　　　　field_name [(index_length)] [ASC|DESC])
　　2、ALTER TABLE table_name ADD FULLTEXT INDEX|KEY
　　　　index_name(field_name [(index_length)] [ASC|DESC])

034 创建多列索引

　　和上面的方法类似
　　　　index_name(field_name_1 [(index_length)] [ASC|DESC],
　　　　 … ,
　　　　field_name_n [(index_length)] [ASC|DESC])

035 删除索引

　　DROP INDEX index_name ON table_name

036 创建视图

　　CREATE VIEW view_name AS select_query

037 查看视图信息

　　SHOW TABLE STATUS [FROM database_name] [LIKE ‘pattern’]

038 查看视图设计信息

　　DESC[RIBE] view_name

039 删除视图

　　DROP VIEW view_name[, view_name]

040 修改视图

　　1、CREATE OR REPLACE VIEW view_name AS select_query
　　2、ALTER VIEW view_name AS select_query

041 创建触发器

　　CREATE TIRGGER trigger_name
　　　　BEFORE|AFTER DELETE|INSERT|UPDATE
　　　　ON table_name FOR EACH ROW
　　　　Triggle_statement
　　Triggle_statement:触发器被触发要履行的语句（增、删、改、查等等）

042 查看触发器

　　SHOW TRIGGERS

043 删除触发器

　　DROP TRIGGER trigger_name

044 插入数据

　　INSERT INTO table_name (field_1, field_2, …) VALUES (value_1, value_2, vaule_3, …)

045 查看Mysql表结构的命令，以下:

　　desc 表名;
　　show columns from 表名;
　　describe 表名;
　　show create table 表名;
　　use information_schema
　　select * from columns where table_name=’表名’;

046 数据库分页查询

　　select * from userdetail where userid limit 0,20

047 MySQL数据库引擎种类

　　（mysql⑸.5.5开始,InnoDB作为默许存储引擎）之前是MyISAM，更早是ISAM你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。要添加1个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持3个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEYDB（BDB），也常常可使用。
　　ISAM是1个定义明确且历经时间考验的数据表格管理方法，它在设计之时就斟酌到数据库被查询的次数要远大于更新的次数。因此，ISAM履行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足的地方在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那末数据文件就没法恢复了。如果你正在把ISAM用在关键任务利用程序里，那就必须常常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份利用程序。
　　MYISAM是MYSQL的ISAM扩大格式和缺省的数据库引擎(5.5之前)。除提供ISAM里所没有的索引和字段管理的大量功能，MYISAM还使用1种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要常常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有1些有用的扩大，例如用来修复数据库文件的 MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。
　　MYISAM强调了快速读取操作，这可能就是为何MYSQL遭到了WEB开发如此青睐的主要缘由：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。
　　HEAP允许只驻留在内存里的临时表格。驻留在内存使得HEAP比ISAM和MYISAM的速度都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那末所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间，HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，用完表格后要删除表格。
　　INNODB和BERKLEYDB（BDB）数据库引擎都是造就MYSQL灵活性的技术的直接产品，这项技术就是MySql++ API。在使用MySql的时候，你所面对的每个挑战几近都源于ISAM和MYIASM数据库引擎不支持事务处理也不支持外来键。虽然要比ISAM和MYISAM引擎慢很多，但是INNODB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的1者或二者，那你就要被迫使用后两个引擎中的1个了。

048 MySQL锁类型

　　根据锁的类型分，可以分为同享锁，排他锁，意向同享锁和意向排他锁。
　　根据锁的粒度分，又可以分为行锁，表锁。
　　对mysql而言，事务机制更多是靠底层的存储引擎来实现，因此，mysql层面只有表锁，而支持事务的innodb存储引擎则实现了行锁(记录锁（在行相应的索引记录上的锁）)，gap锁（是在索引记录间歇上的锁），next-key锁（是记录锁和在此索引记录之前的gap上的锁的结合）。Mysql的记录锁实质是索引记录的锁，由于innodb是索引组织表；gap锁是索引记录间隙的锁，这类锁只在RR隔离级别下有效；next-key锁是记录锁加上记录之前gap锁的组合。mysql通过gap锁和next-key锁实现RR隔离级别。
　　说明：对更新操作(读不上锁)，只有走索引才可能上行锁；否则会对聚簇索引的每行上写锁，实际同等于对表上写锁。
　　若多个物理记录对应同1个索引，若同时访问，也会出现锁冲突；
　　当表有多个索引时，不同事务可以用不同的索引锁住不同的行，另外innodb会同时用行锁对数据记录(聚簇索引)加锁。
　　MVCC(多版本并发控制)并发控制机制下，任何操作都不会阻塞读操作，读操作也不会阻塞任何操作，只由于读不上锁。
　　同享锁：由读表操作加上的锁，加锁后其他用户只能获得该表或行的同享锁，不能获得排它锁，也就是说只能读不能写
　　排它锁：由写表操作加上的锁，加锁后其他用户不能获得该表或行的任何锁，典型是mysql事务中的更新操作
　　意向同享锁（IS）：事务打算给数据行加行同享锁，事务在给1个数据行加同享锁前必须先获得该表的IS锁。
　　意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给1个数据行加排他锁前必须先获得该表的IX锁。

049 MYSQL支持事务吗？

　　在缺省模式下，MYSQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。但是如果你的MYSQL表类型是使用InnoDB Tables 或 BDB tables的话，你的MYSQL就能够使用事务处理,使用SET AUTOCOMMIT=0就能够使MYSQL允许在非autocommit模式，在非autocommit模式下，你必须使用COMMIT来提交你的更改，或用ROLLBACK来回滚你的更改。
　　示例以下：
　　START TRANSACTION;
　　　　SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
　　　　UPDATE table2 SET summmary=@A WHERE type=1;
　　COMMIT;

050 MYSQL相比于其他数据库有哪些特点？

　　1、可以处理具有上千万条记录的大型数据
　　2、支持常见的SQL语句规范
　　3、可移植行高，安装简单小巧
　　4、良好的运行效力，有丰富信息的网络支持
　　5、调试、管理，优化简单（相对其他大型数据库）

051 如何解决MYSQL数据库中文乱码问题？

　　1、在数据库安装的时候指定字符集
　　2、如果在安完了以后可以更改以配置文件
　　3、建立数据库时候：指定字符集类型
　　4、建表的时候也指定字符集

052 如何提高MySql的安全性？

　　1.如果MYSQL客户端和服务器真个连接需要逾越并通过不可信任的网络，那末需要使用ssh隧道来加密该连接的通讯。
　　2.使用set password语句来修改用户的密码，先“mysql -u root”登陆数据库系统，然后mysql> update mysql.user set password=password(‘newpwd’)，最后履行flush privileges就能够了。
　　3.Mysql需要防备的攻击有，防偷听、篡改、回放、谢绝服务等，不触及可用性和容错方面。对所有的连接、查询、其他操作使用基于acl即访问控制列表的安全措施来完成。也有1些对ssl连接的支持。
　　4.设置除root用户外的其他任何用户不允许访问mysql主数据库中的user表; 加密后寄存在user表中的加密后的用户密码1旦泄漏，其他人可以随便用该用户名/密码相应的数据库;
　　5.使用grant和revoke语句来进行用户访问控制的工作;
　　6.不要使用明文密码，而是使用md5()和sha1()等单向的哈系函数来设置密码;
　　7.不要选用字典中的字来做密码;
　　8.采取防火墙可以去掉50%的外部危险，让数据库系统躲在防火墙后面工作，或放置在dmz区域中;
　　9.从因特网上用nmap来扫描3306端口，也可用telnet server_host 3306的方法测试，不允许从非信任网络中访问数据库服务器的3306号tcp端口，需要在防火墙或路由器上做设定;
　　10.为了避免被歹意传入非法参数，例如where id=234，他人却输入where id=234 or 1=1致使全部显示，所以在web的表单中使用”或””来用字符串，在动态url中加入%22代表双引号、%23代表井号、%27代表单引号;传递未检查过的值给mysql数据库是非常危险的;
　　11.在传递数据给mysql时检查1下大小;
　　12.利用程序需要连接到数据库应当使用1般的用户帐号，开放少数必要的权限给该用户; $page_devide$
　　13.在各编程接口(c c++ php perl java jdbc等)中使用特定‘逃脱字符’函数; 在因特网上使用mysql数据库时1定少用传输明文的数据，而用ssl和ssh的加密方式数据来传输;
　　14.学会使用tcpdump和strings工具来查看传输数据的安全性，例如tcpdump -l -i eth0 -w -src or
dst port 3306 strings。以普通用户来启动mysql数据库服务;
　　15.不使用到表的联结符号，选用的参数 –skip-symbolic-links;
　　16.确信在mysql目录中只有启动数据库服务的用户才可以对文件有读和写的权限;
　　17.不准将process或super权限付给非管理用户，该mysqladmin processlist可以罗列出当前履行的查询文本;super权限可用于切断客户端连接、改变服务器运行参数状态、控制拷贝复制数据库的服务器;
　　18.file权限不付给管理员之外的用户，避免出现load data ‘/etc/passwd’到表中再用select 显示出来
的问题;
　　19.如果不相信dns服务公司的服务，可以在主机名称允许表中只设置ip数字地址;
　　20.使用max_user_connections变量来使mysqld服务进程，对1个指定帐户限定连接数;
　　21.grant语句也支持资源控制选项;
　　22.–local-infile=0或1 若是0则客户端程序就没法使用local load data了，赋权的1个例子grant insert(user) on mysql.user to ‘user_name’@’host_name’;若使用–skip-grant-tables系统将对任何用户的访问不做任何访问控制，但可以用 mysqladmin flush-privileges或mysqladmin reload来开启访问控制;默许情况是show databases语句对所有用户开放，可以用–skip-show-databases来关闭掉。
　　23.碰到error 1045(28000) access denied for user ‘root’@’localhost’ (using password:no)毛病时，你需要重新设置密码，具体方法是:先用–skip-grant-tables参数启动mysqld，然后履行 mysql -u root mysql,mysql>update user set password=password(’newpassword’) where user=’root’;mysql>flush privileges;，最后重新启动mysql就能够了。