MySQL怎么创建存储过程
MySQL 存储过程是从 MySQL5.0 开始增加的新功能。存储过程的优点有一箩筐。不过最主要的还是执行效率和
SQL 代码封装。特别是 SQL 代码封装功能，如果没有存储过程，在外部程序访问数据库时，要组织很多 SQL 语句。
特别是业务逻辑复杂的时候，一大堆的 SQL 和条件夹杂在代码中，让人不寒而栗。现在有了 MySQL 存储过程，业
务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高。
一、创建MySQL存储过程
下面代码创建了一个叫pr_add 的MySQL 存储过程，这个MySQL 存储过程有两个int 类型的输入参数 “a”、
“b”，返回这两个参数的和。
1）drop procedure if exists pr_add; （备注：如果存在pr_add的存储过程，则先删掉）
2）计算两个数之和（备注：实现计算两个整数之和的功能）
create procedure pr_add ( a int, b int ) begin declare c int; if a is null then set a = 0; end if;
if b is null then set b = 0; end if; set c = a + b; select c as sum;
二、调用 MySQL 存储过程
call pr_add(10, 20);
MySQL触发器怎么写？
MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将
调用该对象，即表的操作事件触发表上的触发器的执行。
在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、
AFTER UPDATE、AFTER DELETE。
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。
假设系统中有两个表：
1)班级表 class(班级号 classID, 班内学生数 stuCount)
2)学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：
create trigger tri_stuInsert after insert on student for each row begin declare c int; set c = (select stuCount from class where classID=new.classID); update class set stuCount = c + 1 where classID = new.classID;
查看触发器：
和查看数据库（show databases;）查看表格（show tables;）一样，查看触发器的语法如下：
SHOW TRIGGERS [FROM schema_name];
其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，也就是说，可以
指定数据库名，这样就不必先“USE database_name;”了。
删除触发器：
和删除数据库、删除表格一样，删除触发器的语法如下： DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
说明：本文限于篇幅，故而只展示部分的面试内容，完整的Java面试学习文档小编已经帮你整理好了，有需要的朋友私信我{需要}即可免费领取Java、大厂面试学习资料哦！
MySQL语句优化
1 where 子句中可以对字段进行 null 值判断吗？
可以，比如select id from t where num is null 这样的sql也是可以的。但是最好不要给数据库留NULL，尽可
能的使用 NOT NULL填充数据库。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，
不管是否插入值（NULL也包含在内），都是占用100个字符的空间的，如果是varchar 这样的变长字段，null不占
用空间。可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num = 0。
2 select * from admin left join log on admin.admin_id = log.admin_id where log.admin_id>10 如何优化?
优化为： select * from (select * from admin where admin_id>10) T1 lef join log on T1.admin_id = log.admin_id。
使用JOIN 时候，应该用小的结果驱动大的结果（left join 左边表结果尽量小如果有条件应该放到左边先处理，
right join 同理反向），同时尽量把牵涉到多表联合的查询拆分多个query（多个连表查询效率低，容易到之后锁表和
阻塞）。
3 limit 的基数比较大时使用 between
例如：select * from admin order by admin_id limit 100000,10
优化为：select * from admin where admin_id between 100000 and 100010 order by admin_id。
4 尽量避免在列上做运算，这样导致索引失效
例如：select * from admin where year(admin_time)>
优化为： select * from admin where admin_time> '-01-01′
MySQL中文乱码问题完美解决方案
解决乱码的核心思想是统一编码。我们在使用 MySQL 建数据库和建表时应尽量使用统一的编码，强烈推荐的是utf8编码，因为该编码几乎可以兼容世界上所有的字符。
数据库在安装的时候可以设置默认编码，在安装时就一定要设置为utf8编码。设置之后再创建的数据库和表如果不指定编码，默认都会使用utf8编码，省去了很多麻烦。
数据库软件安装好之后可以通过如下命令查看默认编码：
1、查询数据库软件使用的默认编码格式
show variables like “%colla%”； show varables like “%char%”
其中collation，代表了字符串排序（比较）的规则，如果值是utf8_general_ci,代表使用utf8字符集大小写
不敏感的自然方式比较。
如果character_set的值不为utf8，那么可以使用如下命令修改为utf8。
2、修改数据库默认编码为utf8
SET character_set_client='utf8'; SET character_set_connection='utf8';
SET character_set_results='utf8';
如果不想设置数据库软件的全局默认编码，也可以单独修改或者设置某个具体数据库的编码也可以单独修改
或设置某个数据库中某个表的编码。
3、创建数据库的时候指定使用utf8编码
CREATE DATABASE `test` CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
4、创建表的时候指定使用utf8编码
CREATE TABLE `database_user` ( `ID` varchar(40) NOT NULL default '', `UserID` varchar(40) NOT NULL default '', ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
如果数据库已经创建好了，可以使用show database 数据库名；和 show create table 表名；查看一下数
据库和表的字符集是否为utf8 ，如果不是则在命令行下面可以用如下命令，将数据库和表编码修改为utf8.
5、修改具体某数据库或表的编码
ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `tb_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
如何提高MySQL的安全性
1.如果 MySQL 客户端和服务器端的连接需要跨越并通过不可信任的网络，那么需要使用 ssh 隧道来加密该连接的通信。
2.使用 set password 语句来修改用户的密码，先“mysql -u root”登陆数据库系统，然后“mysql> update mysql.user set password=password(’newpwd’)”，最后执行“flush privileges”。
3.MySQL需要提防的攻击有，防偷听、篡改、回放、拒绝服务等，不涉及可用性和容错方面。对所有的连接、查询、其他操作使用基于ACL（ACL（访问控制列表）是一种路由器配置和控制网络访问的一种有力的工具，它可控制路由器应该允许或拒绝数据包通过，可监控流量，可自上向下检查网络的安全性，可检查和过滤数据和限制不必要的路由更新，因此让网络资源节约成本的 ACL 配置技术在生活中越来越广泛应用。）即访问控制列表的安全措施来完成。
4.设置除了root用户外的其他任何用户不允许访问mysql主数据库中的user表；
5.使用 grant和revoke语句来进行用户访问控制的工作;
6.不要使用明文密码，而是使用md5()和sha1()等单向的哈系函数来设置密码;
7.不要选用字典中的字来做密码;
8.采用防火墙可以去掉 50%的外部危险，让数据库系统躲在防火墙后面工作，或放置在 DMZ（DMZ 是英文
“demilitarized zone”的缩写，隔离区，它是为了解决安装防火墙后外部网络的访问用户不能访问内部网络服务器的
问题，而设立的一个非安全系统与安全系统之间的缓冲区。）区域中;
9.从因特网上用nmap来扫描3306端口，也可用telnet server_host 3306的方法测试，不允许从非信任网络中
访问数据库服务器的3306号tcp端口，需要在防火墙或路由器上做设定;
10.服务端要对SQL进行预编译，避免SQL注入攻击，例如 where id=234，别人却输入where id=234 or 1=1。
11.在传递数据给mysql时检查一下大小;
12.应用程序连接到数据库时应该使用一般的用户帐号，开放少数必要的权限给该用户;
13.学会使用tcpdump和strings工具来查看传输数据的安全性，例如tcpdump -l -i eth0 -w -src or dst port 3306 strings。以普通用户来启动mysql数据库服务;
14.确信在mysql目录中只有启动数据库服务的用户才可以对文件有读和写的权限;
15.不许将 process 或 super 权限付给非管理用户，该 mysqladmin processlist 可以列举出当前执行的查询文
本;super权限可用于切断客户端连接、改变服务器运行参数状态、控制拷贝复制数据库的服务器;
16.如果不相信dns服务公司的服务，可以在主机名称允许表中只设置ip数字地址;
17.使用max_user_connections变量来使mysqld服务进程，对一个指定帐户限定连接数;
18.grant语句也支持资源控制选项;
19.启动 mysqld 服务进程的安全选项开关，–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来关闭掉。
20.碰到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就可以了。
mysql优化整理
关于数据库优化，网上有不少资料和方法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂
偶尔发现了这篇文章，总结得很经典，文章流量也很大，所以拿到自己的总结文集中，积累优质文章，提升个人能力，希望对大家今后开发中也有帮助
1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
2、使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：
DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)
如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID ISNULL
连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
3、使用联合(UNION)来代替手动创建的临时表
MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。
SELECT Name,Phone FROM client UNION
SELECT Name,BirthDate FROM author UNION
SELECT Name,Supplier FROM product
4、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN; INSERT INTO salesinfo SET CustomerID=14; UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。
LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
...
UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES
这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。
6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。
CREATE TABLE customerinfo( CustomerIDINT NOT NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;
CREATE TABLE salesinfo( SalesIDNT NOT NULL,CustomerIDINT NOT NULL,
PRIMARYKEY(CustomerID,SalesID),
FOREIGNKEY(CustomerID) REFERENCES customerinfo(CustomerID) ON DELETE CASCADE)TYPE=INNODB;
注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。
7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
那该对哪些字段建立索引呢？
一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况
例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。
8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。
下面是应该注意的几个方面。
首先，最好是在相同类型的字段间进行比较的操作。
在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。
其次，在建有索引的字段上尽量不要使用函数进行操作。
例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。
第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
例如下面的查询将会比较表中的每一条记录。
SELECT * FROM books
WHERE name like"MySQL%"
但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：
SELECT * FROM books WHERE name＞="MySQL" andname ＜"MySQM"
最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。
说明：本文限于篇幅，故而只展示部分的面试内容，完整的Java面试学习文档小编已经帮你整理好了，有需要的朋友私信我{需要}即可免费领取Java、大厂面试学习资料哦！
