MySQL5 总结（1）——开发篇

相同数据不要出现多次，一是重复，二是不利于修改维护。因此就有了关系数据库。

关系表的设计是为了保证把信息分解成多个表。一类数据一个表，各表通过某些常用的值互相关联。

关系数据库可有效存储和方便处理，因此，关系数据库的可伸缩性比非关系数据库要好。

 

三个层次：数据库开发、优化、管理；

1、关系数据库设计三范式（即原则，目的是不存在冗余数据）：

第一范式：要有主键、列不可分；

第二范式：（多对多关系这种表设计时要遵循）

当一张表中有多个字段做主键时，不是主键的这些字段不能依赖于部分主键，即不能存在部分依赖。

如：学生姓名不是主键，但依赖于学号，而学号是主键的一部分，此时会产生数据冗余。解决方法：将其分割为多张表。

第三范式：不能存在传递依赖，即除了主键外的任何其它字段必须直接依赖于主键。

不能存在一个字段依赖于另一个字段，而另一个字段依赖于主键，即产生了传递依赖。此时会产生数据冗余，解决方法：将其分割为多张表，保证一张表中的字段都只直接依赖于主键；

 

编写SQL语句需要对基础数据库的设计有良好理解。如信息存储在什么表中，表之间如何相互关联以及行内数据如何分解。

2、TIPS

（1）等值联结也叫做内部联结（inner join）；

三张表关联查询，如：select * from (A inner join B on A.id=B.id) inner join C on A.id=C.id where a.id=1 and b.name='u';

（2）cross join也叫笛卡尔积的联结类型，即就是在连接两个表时忘记加入一个where子句；

如：A表中有m条记录，B表中有n条记录，若产生笛卡尔积，会产生m*n条记录。

（3）外键约束（foreign key约束）

外键是表中的一列，它包含另一个表的主键，定义了两个表之间的关系。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

MySQL不允许删除具有与其它表相关联的数据的行。

外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

（4）左外连接（left outer join或left join）

左边的表的所有行保留，右边表匹配不上的显示Null。

左边表中一行全部显示，对应右表的一行，若有则显示没有则显示空。

（5）约束

约束用于限制加入表的数据的类型。

共有以下几种约束：NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT；

（6）全连接（full join，也叫full outer join）

FULL JOIN 关键字会从左表和右表 那里返回所有的行。如果左表中的行在右表中没有匹配，或者若右表中的行在左表中没有匹配，这些行同样会列出。

（7）insert into ... select语句
 如：insert into A(id) select id from A;

（8）客户机软件（用来实际运行MySQL命令的程序）。最易得到的客户机软件是mysql命令行实用程序（它包含在每个MySQL安装中）。也有一些其他的软件。

（9）聚合函数

MySQL有5个聚合函数，即sum(字段)、count()、avg()、max()、min()；——返回某列的和、行数、平均值、最大值、最小值；

（10）组合查询（union）：去除重复记录后的结果；

union all：将多表查询得到的结果集直接合并在一起；

（11）别名（as）

别名可用于列名、计算字段，也可给表名起别名。

表别名只在查询执行中使用。

如：

select * from A (as) a where a.id=1;

delete a from A (as) a where a.id=1;

（12）子查询

子查询总是从内向外执行！

（13）select

从表中检索一个或多个数据列。

（14） like

使用like，需配合通配符使用（%、_）。

如：like '001%'，而不是like 001%。

（15）distinct

检索出有不同值的列。  distinct应用于所有列，而不仅是前置它的列。

（16）关键字，也叫保留字，是不能用来命名的。

（17）排序（order by）

默认是升序。asc、desc只应用到直接位于其前面的列名。

按多个列排序，如select * from A order by id, name;表示首先按id，然后按name升序排序。

（18）null

列若不指定not null，默认认为指定的是null。

一个列不包含值，称其为包含空值null。

检查具有null值的列（is null），如where name is null;

（19）

select last_insert_id()——返回最后一个auto_increment值。

每个表只允许一个auto_increment列，且它必须被索引，如可通过使之成为主键。

（20）计算字段

并不实际存在于数据库表中，计算字段是运行时在select语句内创建的。

在数据库服务器上完成操作要比在客户机应用程序内完成快得多。

（21）truncate table 表名

更快的删除，实质是删除原来的表并重新创建一个表。

（22）添加多条数据

insert into 表名(字段1，字段2...) values(内容1，内容2，....), (内容1，内容2，....)  

（23）limit

limit 2,3：从第2条后算3条；

limit 3：显示前3条；

limit参数在数据下的时候效率高，随着数据量的增大，速度线性下降。

如：

select id form A limit10000,10;——>优化为：

select id from A where id>=(select id from A order by id limit 10000,1) limit 10;

（24）建立索引后，会在字段排序。不需要逐行搜索全表，利用索引进行有序查找。

（25）select count(字段) from 表名;——返回的是记录数，且不含null。

（26）表中创建外键：外键只能指向另一张表的主键。

（27）select 字段名,聚合函数 from 表名 where ...

                                                                     group by 字段名 having 条件

                                                                     order by 字段名 desc limit 2,3;

按照先后顺序执行，即先用where过滤，然后group by分组，分组后having对结果进行条件过滤，最后排序。

注：使用group by时，出现在select中的字段，若没有出现在聚合函数中，则必须要出现在group by字句里。

（28）随机取10条数据

select * from 表名 order by rand() limit 10;

（29）对于字符类型数据，必须使用单引号。

（30）在mysql数据库下，使用create user创建新用户。新创建后的用户没有任何授权，使用grant命令授权。

（31）DML：数据操作语言，涉及数据的SQL语句。包括CRUD语句。

DDL：数据定义语言，涉及数据库结构、表结构的SQL语句。包括创建、删除数据库和表。

（32）MySQL会把自己安装为windows服务。若是非安装版的，可以运行mysqld.exe命令启动MySQL服务。mysql.exe为客户端控制台。

（33）schema：就是数据库。一般一个应用程序对应一个数据库。

（34）varchar有最大长度限制，一般为255个字符。？

如varchar(1000)。

（35）三个以上表的结合是先结合两个表，然后将结果当做一个表，再与另一个表结合。

（36）MySQL中，from_unixtime()函数：表示将时间戳转化为yyyy-mm-dd的形式。

（37）<>不等于运算符。

（38）作用是为了满足多条件查询页面中不确定的各种因素，而采用的一种构造一条能正确运行的动态sql语句的方法。

 

3、PS

（1）视图（view）

视图是虚拟的表，不包含表中的任何列和数据，它包含的是一个sql查询。视图提供了一种Mysql的select语句的封装，可用来简化数据处理、重新格式化基础数据或保护基础数据。

为什么要使用视图？

a、重用sql语句

b、使用表的组成部分而不是整个表；

c、保护数据。可给用户授予表的特定部分的访问权限而不是整个表的访问权限；

d、视图可以返回与底层表的表示和格式不同的数据；

视图创建好后，可用与表基本相同的方式利用它。

视图本身不含数据，它返回的数据都是从其它表中检索出来的。在添加或修改这些表中的数据时，视图将返回改变过的数据。

视图名必须唯一；

视图可以嵌套；

视图不能索引、有关联的触发器和默认值；

视图可以和表一起使用；

并非所有视图都是可更新的。更新（insert、update、delete）一个视图将更新其基表。

一般将视图用于检索（select语句），而不用于更新（insert、update、delete）。

 

（2）存储过程（procedure）（实际是一种函数）

可能会有一个完整的操作需要多条sql语句才能完成。

存储过程是为了以后的使用而保存一条或多条sql语句的集合。

为什么使用存储过程？

a、性能好。使用存储过程比使用单独sql语句要快；

b、安全。

Mysql将编写存储过程的安全和访问与执行存储过程的安全和访问区分开来。

执行存储过程，也叫调用存储过程：call 存储过程名(参数1，参数2...);   

存储过程可以显示结果，也可以不显示结果。一般，存储过程不显示结果，而是把结果返回给指定的变量。

Mysql命令行客户机的语句结束分隔符默认为：“；”。

delimiter //：表示告诉命令行程序使用//作为新的语句结束分隔符。

 

删除存储过程：drop procedure 存储过程名;   （drop procedure if exists）

 

每个参数都必须具有指定的数据类型；

关键字out：指出相应的参数用来从存储过程传出一个值，返回给调用者；

关键字in：传递给存储过程；
关键字inout：对存储过程传入和传出；

关键字into：select语句检索的值保存到相应的变量；

存储过程的代码位于begin和end语句内；

 

调用存储过程：call 存储过程名(@p1, @p2);    //其中@变量表示是保存存储过程的值；可以通过select @p1,@p2查询得到值；

 

在存储过程体中，用DECLARE语句定义两个局部变量，要求指定变量名和数据类型。

if  ...   then

end if;

 

数据库中的boolean数据类型，值指定为1表示真，指定为0表示假；

 

（3）游标

使用游标的原因：需要在检索出来的行中前进或后退一行或多行。

游标是一个存储在MySQL服务器上的数据库查询，不是一条select语句，而是被该语句检索出来的结果集。MySQL游标只能用于存储过程（和函数）。
创建游标： 

create procedure 存储过程名()

begin

        declare 游标名  cursor 

        for 

        select id form A;

end;

打开游标：open 游标名;

关闭游标：close 游标名;（若不明确关闭游标，MySQL会在到达end语句时自动关闭它）

在一个游标被打开后，可以使用fetch语句分别访问它的每一行。

（declare语句定义的局部变量必须在定义任意或句柄之前定义，而句柄必须在游标之后定义）

（4）触发器（保证数据的一致性）

触发器是MySQL响应（update、insert、delete语句）而自动执行的一条MySQL语句（或位于begin和end之间的一组语句）；

触发器名必须在每个表中唯一，最好是在数据库范围内使用唯一的触发器名；

只有表才支持触发器，视图、临时表都不支持。每个表每个事件每次只允许一个触发器。

在insert触发器代码中，new是虚拟表，用来访问被插入的行（第一个表）；

在delete触发器代码中，old是虚拟表，用来访问被删除的行；old中的值全部都是只读的，不能更新；

在update触发器代码中，old访问update语句前的值，new访问新更新的值；

使用begin和end块的好处是触发器能容纳多条SQL语句。

不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

（5）事务处理（用来维护数据库的完整性）

保证成批的MySQL操作要么完全执行，要么完全不执行；

事务：指一组SQL语句所组成的一个不可分割的工作单元；有ACID特征。

回退：指撤销指定SQL语句的过程； rollback只能在一个事务处理内使用（在执行一条start transaction命令之后）；

提交：指将未存储的SQL语句结果写入数据库表；

start transaction;

...

rollback;或commit;

事务处理用来管理insert、update和delete语句。

一般提交操作都是自动进行的。但在事务处理中，提交不会自动进行，需要进行明确的提交，即commit。

当commit或rollback语句执行后，事务会自动关闭。

保留点：事务处理可能需要部分提交或部分回退，因此在事务处理中合适位置放置保留点。

若需要回退，可以回退到某个保留点。

如savepoint aaa;

rollback to aaa;

保留点在事务处理完成后自动释放。（即执行rollback或commit后）

set autocommit=0；指示MySQL不自动提交，autocommit标志是针对每个连接的。

取消自动提交，在提交事务之前，对数据库所做的全部操作都保存在session中。

 

具体操作步骤：

a、取消自动提交，即set autocommit=0;

b、开始事务

c、操作

d、提交

e、若有问题，则进行回滚

（6）字符集和校对

校对：为规定字符如何比较的指令。在对用order by字句检索出来的数据排序时起重要作用。

如果创建表的时候不指定字符集（character set），也不指定collate，则使用数据库默认。

串可以在字符集之间进行转换。cast()或convert()函数。

可对表或列定义字符集和校对。

 

（7）数据库中一个汉字占两个字符，英文一个字母占一个字符。一个字符占一个字节。char(6)表示定义6个字符。

 

（8）转义

select * form A where name like '\%'——匹配name为%的数据行；

为了和oracle一致，使用select * form A where name like '\%'escape '\ '。

（9）使用绑定变量：安全、性能；

jdbc使用PreparedStatement对象处理绑定变量。

ibatis：##定义的变量为绑定变量，$$定义的变量不是绑定变量。

分布式数据库MySQL：数据量大、与原业务关联少；

集中式数据库Oracle：强事务需求、与原业务结合密切，经常多表关联

数据库是否能满足需求？数据库表结构设计？SQL优化。

开发准则：总是使用绑定变量；避免大量like全模糊搜索。

数据库访问优化：

a、减少数据访问（index）

b、返回更少数据（Pagination）

c、减少交互次数（batch）

d、减少CPU及内存开销（sort、bind var）

（10）