sql必会基础2

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能够支持这样的备份应用程序。

URL：http://www.bianceng.cn/database/MySQL/201609/50453.htm

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.应用程序需要连接到数据库应该使用一般的用户帐号，开放少数必要的权限给该用户;

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;