【常见面试问题总结目录>>>】
031 在已经存在的表上创建唯一索引
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.5开始,InnoDB作为默认存储引擎)之前是MyISAM,更早是ISAM你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。要添加一个新的引擎,就必须重新编译MYSQL。在缺省情况下,MYSQL支持三个引擎:ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEYDB(BDB), 也常常可以使用。
ISAM是一个定义明确且历经时间考验的数据表格管理方法,它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此,ISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于,它不支持事务处理,也不能够容错:如果你的硬盘崩溃了,那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里,那就必须经常备份你所有的实时数据,通过其复制特性,MYSQL能够支持这样的备份应用 程序。
MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎(5.5之前)。除了提供ISAM里所没有的索引和字段管理的大 量功能,MYISAM还使用一种表格锁定的机制,来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令,来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展,例如用来修复数据库文件的 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包括了对事务处理和外来键的支持,这两点都是前两个引擎所没有的。如前所述,如果你的设计需要这些特性中的一者或者两者,那你就要被迫使用后两个引擎中的一个了。
048 MySQL锁类型
根据锁的类型分,可以分为共享锁,排他锁,意向共享锁和意向排他锁。
根据锁的粒度分,又可以分为行锁,表锁。
对于mysql而言,事务机制更多是靠底层的存储引擎来实现,因此,mysql层面只有表锁,而支持事务的innodb存 储引擎则实现了行锁(记录锁(在行相应的索引记录上的锁)),gap锁(是在索引记录间歇上的锁),next-key锁(是记录锁和在此索引记录之前的gap上的锁的结合)。Mysql的记录锁实质是索引记录的锁,因为innodb是索引组织表;gap锁是索引记录间隙的锁,这种锁只在RR隔离级别下有效;next-key锁是记录锁加上记录之前gap锁的组合。mysql通过gap锁和next-key锁实现RR隔离级别。
说明:对于更新操作(读不上锁),只有走索引才可能上行锁;否则会对聚簇索引的每一行上写锁,实际等同于对表上写锁。
若多个物理记录对应同一个索引,若同时访问,也会出现锁冲突;
当表有多个索引时,不同事务可以用不同的索引锁住不同的行,另外innodb会同时用行锁对数据记录(聚簇索引)加 锁。
MVCC(多版本并发控制)并发控制机制下,任何操作都不会阻塞读操作,读操作也不会阻塞任何操作,只因为读不上锁。
共享锁:由读表操作加上的锁,加锁后其他用户只能获取该表或行的共享锁,不能获取排它锁,也就是说只能读不能写
排它锁:由写表操作加上的锁,加锁后其他用户不能获取该表或行的任何锁,典型是mysql事务中的更新操作
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的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即访问控制列表的安全措施来完成。也有一些对ssl连接的支持。
4.设置除了root用户外的其他任何用户不允许访问mysql主数据库中的user表; 加密后存放在user表中的加密 后的用户密码一旦泄露,其他人可以随意用该用户名/密码相应的数据库;
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时检查一下大小;
12.应用程序需要连接到数据库应该使用一般的用户帐号,开放少数必要的权限给该用户;
pagedevide
13.在各编程接口(c c++ php perl java jdbc等)中使用特定‘逃脱字符’函数; 在因特网上使用mysql数据 库时一定少用传输明文的数据,而用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服务进程,对一个指定帐户限定连接数;
21.grant语句也支持资源控制选项;
22.–local-infile=0或1 若是0则客户端程序就无法使用local load data了,赋权的一个例子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就可以了。
053 MySQL取得当前时间的函数是?,格式化日期的函数是
取得当前时间用 now() 就行。在数据库中格式化时间 用DATE_FORMAT(date, format)。根据格式串
format 格式化日期或日期时间值date,返回结果串。
054 你如何确定 MySQL 是否处于运行状态?
答案: Debian 上运行命令 service mysql status,在RedHat 上运行命令 service mysqld status。然后看看输出即可。
055 如何开启或停止 MySQL 服务?
答案:运行命令 service mysqld start 开启服务;运行命令 service mysqld stop 停止服务。
056 如何通过 Shell 登入 MySQL?
答案:运行命令 mysql -u root –p
057 如何列出所有数据库?
答案:运行命令 show databases;
058 如何切换到某个数据库并在上面工作?
答案:运行命令 use database_name; 进入名为 database_name 的数据库。
059 如何列出某个数据库内所有表?
答案:在当前数据库运行命令 show tables;
060 如何获取表内所有 Field 对象的名称和类型?
答案:运行命令 describe table_name;