深入浅出MySQL数据库开发、优化于管理维护

MySQL数据库物理文件默认存放位置：C:\ProgramData\MySQL\MySQL Server 5.5\data\

MySQL通过配置my.int的datadir属性来指定数据库的物理存放位置。

一、DDL语句：
1.创建数据库：create database test;

2.删除数据库：drop database test;

3.描述表：desc emp;

4.删除表：drop table emp;

5.修改表：
(1)修改表类型：alter table emp modify ename varchar(20);
(2)增加表字段：alter table emp add column age int(3);
(3)删除表字段：alter table emp drop column age;
(4)字段改名：alter table emp change age age1 int(4);
【备注】：change和modify都可以修改表的定义，不同的是change后面需要写两次列名，但change的优点是可以修改列名称。
(5)修改字段排列顺序：alter table emp add birth date after ename;
alter table emp modify age int(3) first;
(6)更改表名：alter table emp rename emp1;

二、DML语句：
1.插入记录：insert into emp(ename,hiredate,sal,deptno) values('zzx','2000-01-01','2000',1);
【备注】：MySQL特有一次插入多条记录insert into dept values(5,'dept5'),(6,'dept6');

2.更新记录：update emp set sal=4000 where ename='Lisa';

3.删除记录：delete from emp where ename='dony';
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

4.查询记录：select * from emp;
(1)查询不重复的记录：select distinct deptno from emp;
(2)条件查询：select * from emp where deptno=1;
(3)排序和限制：select * from emp order by deptno,sal desc limit 1,10;
【备注】：limit为MySQL特有
(4)聚合：select deptno,count(1) from emp group by deptno with rollup;
select deptno,count(1) from emp group by deptno having count(1)>1;
select sum(sal),max(sal),min(sal) from emp;
(5)表连接：
【备注】：表连接分为内连接和外连接，内连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。外连接又分为左连接和右连接。
1>内连接：select ename,deptname  from emp,dept where emp.deptno=dept.deptno;
2>左连接：select ename,deptname from emp left join dept on emp.deptno=dept.deptno;
3>右连接：select ename,deptname from dept right join emp on dept.deptno=emp.deptno;
(6)子查询：select * from emp where deptno in(select deptno from dept);
如果子查询记录数唯一，还可以用=代替in：select * from emp where deptno=(select deptno from dept limit 1);
【备注】：表连接在很多情况下用于优化子查询。
(7)记录联合：
包含重复：
select deptno from emp
union all
select deptno from dept;
不包含重复：
select deptno from emp
union
select deptno from dept;

三、DCL语句：
创建一个数据库用户xavier，给予test数据库中所有表的select和insert权限：
grant select,insert on test.* to 'xavier'@'localhost' identified by '123456';

收回insert权限：
revoke insert on test.* from 'xavier'@'localhost'.

【备注】：? contents用来显示所有可供查询的分类。例如使用? Data Types来查看MySQL支持哪些数据类型。

四、杂乱知识：
1.删除数据库test下所有前缀为tmp的表：
select concat('drop table ',table_schema,'.',table_name,';') from information_schema.tables where table_schema='test' and table_name like 'tmp%';

2.将数据库下所有存储引擎为myisam的表改成innodb：
select concat('alter table ','table_schema,'.',table_name,' engine=innodb;') from information_schema.tables where engine='MyISAM';

3.加入zerofill参数， 在int位数不够时，补充0：create table t1(id int(5) zerofill);但是，如果int类型的数超过补充位但是没有超过int的表示范围，仍然会显示。同时，MySQL会在有zerofill的列中自动 加入unsigned属性。

4.MySQL中使用auto_increment设置自增主键，自增主键要求为primary key/unique键，且为not null

5.浮点数如果不写精度和标度，则会按实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错。定点数如果不写精度和标度，则会按照默认值decimal(10,0)来进行操作，如果数据超过了精度和标度值，系统就会报错。

6.对于bit类型，不能直接使用select id from table查询，需要使用bin(id),hex(id)来查看。

7. 如果要用来表示年月日，需要用DATE表示；如果要表示年月日时分秒，需要用DATETIME表示；如果要用来表示时分秒，需要用TIME表示。如果需要 经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示。TIMESTAMP返回显示为"YYYY-MM-DD HH:MM:SS".TIMESTAMP与时区相关。
【备注】：
create table t1(d date,t time,dt datetime);
insert into t1 values(now());

create table t2(ts timestamp);
inset into t2 values(null);
注意，MySQL只给表中第一个TIMESTAMP字段设置默认值为系统时间，如果有第二个TIMESTAMP类型，则默认值设置为0。

8.使用enum类型：create table t(gender enum('M','F'));
insert into t values('M'),('1'),('f'),(NULL);

9.NULL 不能用于“=”比较，select null=null;的结果为null,而不是1.如果要使用NULL 的安全比较，需要使用"<=>"，或者"is null"。select null<=>null,null is null;。

10.字符串函数：
mysql> select concat('aaa','bbb'),concat('aaa',null);
+---------------------+--------------------+
| concat('aaa','bbb') | concat('aaa',null) |
+---------------------+--------------------+
| aaabbb              | NULL               |
+---------------------+--------------------+
1 row in set (0.05 sec)
任何字符串与NULL连接的结果都是NULL

mysql> select insert('beijing2008you',12,3,'me');
+------------------------------------+
| insert('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me                      |
+------------------------------------+
1 row in set (0.02 sec)

mysql> select left('beijing2008',7),left('beijing',null),right('beijing2008',4);

+-----------------------+----------------------+------------------------+
| left('beijing2008',7) | left('beijing',null) | right('beijing2008',4) |
+-----------------------+----------------------+------------------------+
| beijing               | NULL                 | 2008                   |
+-----------------------+----------------------+------------------------+
1 row in set (0.00 sec)

11.数值函数：
mysql> select floor(-0.8),floor(0.8);
+-------------+------------+
| floor(-0.8) | floor(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.02 sec)
floor()返回小于x的最大整数

mysql> select ceil(-0.8),ceil(0.8);
+------------+-----------+
| ceil(-0.8) | ceil(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.00 sec)
ceil()返回大于x的最小整数

mysql> select rand(),rand();
+--------------------+--------------------+
| rand()             | rand()             |
+--------------------+--------------------+
| 0.1481672824809023 | 0.5434224144960031 |
+--------------------+--------------------+
1 row in set (0.02 sec)
rand()返回0-1内的随机值

mysql> select round(1.1),round(1.1,2),round(1,2);
+------------+--------------+------------+
| round(1.1) | round(1.1,2) | round(1,2) |
+------------+--------------+------------+
|          1 |         1.10 |          1.00 |
+------------+--------------+------------+
1 row in set (0.00 sec)
round(x,y)返回参数x的四舍五入的有y位小数的值

mysql> select round(1.235,2),truncate(1.235,2);
+----------------+-------------------+
| round(1.235,2) | truncate(1.235,2) |
+----------------+-------------------+
|           1.24 |              1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)
truncate(x,y)为返回数字x截断为y位小数的结果

12.日期和时间函数：
curdate()返回当前日期，只包含年月日；curtime()返回当前时间，只包含时分秒；now()返回当前的日期和时间，年月日时分秒

mysql> select date_format(now(),'%M,%D,%Y');
+-------------------------------+
| date_format(now(),'%M,%D,%Y') |
+-------------------------------+
| May,8th,2015                  |
+-------------------------------+
1 row in set (0.00 sec)
date_format(date,fmt)按字符串fmt格式化日期date

mysql> select date_add(now(),INTERVAL 31 day);
+---------------------------------+
| date_add(now(),INTERVAL 31 day) |
+---------------------------------+
| 2015-06-08 18:53:24             |
+---------------------------------+
1 row in set (0.01 sec)
date_add(date,INTERVAL expr type)返回与所给日期date相差INTERVAL时间段的日期。

mysql> select datediff('2020-01-01',now());
+------------------------------+
| datediff('2020-01-01',now()) |
+------------------------------+
|                         1699 |
+------------------------------+
1 row in set (0.00 sec)
datediff(date1,date2)用来计算两个日期之间相差的天数。

13.流程函数：
if(value,t,f)函数：满足value则使用t，否则使用f替换；
ifnull(value1,value2)函数：用来替换NULL值。

case when [value1] then [result]...else [default] end：用when...then。

14.常用函数：
database()，version()，user()，inet_aton(IP)返回IP地址的数字表示，inet_ntoa(num)返回数字表示的IP地址，password(str)返回字符串str的加密版本，md5()。

四、MySQL数据库引擎
MySQL中仅有InnoDB和BDB支持事务，MyISAM等引擎不支持事务。
1.查看MySQL支持的引擎:
show engine;支持当前数据库支持的存储引擎。
show variables like 'have%';也可以查看。

2.创建表时指定引擎：
create table ai(
    i bigint(20) not null auto_increment,
    primary key(i)
)engine=MyIsam default charset=utf8;

3.修改表的引擎：
alter table ai engine=innodb;
修改表的引擎

4.查看表的引擎：
show create table ai;

5.MyISAM 引擎：不支持事务，也不支持外键。其优势是访问速度快，对事务完整性没有要求，或者以select,insert为主的引用可以使用此引擎。每个 MyISAM引擎在磁盘上存储成.frm(存储表定义),.MYD（存储数据）,.MYI（存储引擎）3个文件。数据文件和索引文件可以放置在不同的目 录，平均分布IO，以求获得更快的速度。
MyISAM的表具有三种存储格式：静态表，动态表，压缩表。
【备注】：可以使用check table语句来检查MyISAM表的将抗，并且用repair table语句来修复一个损坏的MyISAM表。使用optimize table语句可以优化表提高性能。
在cmd下使用myisampack工具可以创建压缩表。

6.InnoDB 引擎：具有提交，回滚和崩溃回复能力的事务安全，但是处理效率相对与MyISAM引擎差，并且会占用更多的磁盘空间以保留数据和索引。InnoDB引擎在 磁盘上表结构存储在.frm（存储表定义）文件中，数据和索引保存在innodb_data_home_dir和 innodb_data_file_path。
(1)自动增长列 auto_increment：
alter table autoincre_demo auto_increment=7;来设置自动增长列的初始值。
select last_insert_id();来查询当前线程最后插入记录所使用的主键。（当使用insert一次插入多条数据时，在最前面的数据最后插入，在最后面的数据最先插入，但是在表中顺序仍然为从前向后插入）。
mysql> insert into autoincre_demo values ('11'),('12'),('13');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into autoincre_demo(name) values ('11'),('12'),('13');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from autoincre_demo;
+----+------+
| i  | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  7 | 7    |
|  8 | 11   |
|  9 | 12   |
| 10 | 13   |
+----+------+
7 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)
此处可以看出，一次插入多条数据11，12，13.表中顺序仍然为8-11，9-12，10-13.但是，使用last_insert_demo后显示的为最后插入的11的主键：8.

对于InnoDB表，自动增长列必须为索引（主键就是一种索引）。如果是组合索引，也必须是组合索引的第一列。但是对于MyISAM表，自动增长列可以是组合索引的其他列。

(2)外键约束：
restrict 和no action相同（restrict是在企图删除父表中的一行的时候，如果子表中有记录则阻止。no action是先删除父表中的记录，再去查看子表中的记录，如果有记录，则回滚父表的删除），是指限制在子表有关联记录的情况下父表不能更 新；cascade表示父表在更新或者删除时，更新或者删除子表对应的记录;set null则表示父表在更新或者删除的时候，子表的对应字段被设置成NULL。

6.MEMORY引擎：使用存在于内存中的内容来创建表。每个MEMORY表值实际对应一个磁盘文件，存储格式是.frm。MEMORY类型的表访问非常快，但是一旦服务关闭，表中的数据就会丢失掉。
create table t1 engine=memory select * from t;创建
show table status like 't1';查看表信息
create index t1_index_hash using hash on t1(id);创建hash索引
show index from t1;查看t1表的索引
drop index t1_index_hash on t1;删除t1表上的hash索引
create index t1_index_btree using btree on t1(id);创建btree索引
【备注】：MySQL中还有其他的存储引擎，例如：MERGE引擎（一组MyISAM表的组合），Infobirght引擎（列式存储引擎），TokuDB（高写性能高压缩引擎）

五、MySQL中选择合适的数据类型
1.char和varchar类型：
char 属于固定长度的字符类型，而varchar属于可变长度的字符类型。由于char是固定长度，所以他的处理速度比varchar快得多，但缺点是浪费时 间，程序需要对行尾空格进行处理。varchar对于'abcd'四个字符，需要5个字节（多一个），而char只需要4个字节。使用MyISAM或 MEMORY引擎时，推荐使用char类型；对于使用InnoDB引擎，建议使用varchar类型。

2.text和blob类型：
blob 用来保存二进制数据，比如图片；而Text只能保存字符数据，比如文章和日记。text中又分为text,mediumtext,longtext。 blob分为blob,mediumblob,longblob，它们的主要区别是存储文本长度不同和存储字节不同。由于blob和text值可能会引起 一些性能文件，建议使用optimize table进行碎片整理功能。
应该使用合成索引来提高大文本字段(blob或text)的查询性能。（使用md5()函数，或者sha1(),crc32()等函数，对大文本字段生成索引，提高查询性能）
在不必要的时候，避免检索大型的blob或text值。将blob或text列最好分离到单独的表中，提高性能。
【 备注】：repeat("hello",2)函数可以将字符串"hello"重复2次。结果为："hellohello"

3.浮点数与定点数：
MySQL 中float，double,real用来表示浮点数，使用decimal或number来表示定点数。浮点数一般用于表示含有小数部分的数值，如果插入 数据的精度超过了该列定义的实际精度，则会四舍五入。定点数实际上是按照字符串形式存放的，如果插入数据超过了实际定义的精度，同时SQLMode处于 traditional传统模式下，则会报错，导致数据无法插入；如果插入数据超过了实际定义的精度，同时处于默认SQL Mode下，则会进行警告，但是数据按四舍五入存入。
对于精度要求比较高的应用（例如货币）要使用定点数而不是浮点数来保存数据。
同时，在编程时候要注意浮点数的误差问题，尽量避免浮点数的比较。同时要注意浮点数中的一些特殊值处理。

4.日期类型选择：
按 照实际需要选择能够满足引用的最小存储的日期类型。如果要记录年月日时分秒，并且记录的年份比较救援，则最好使用datetime，而不要使用 timestamp。如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp，因为日期类型中只有它能够和实际时区相对应。

【备 注】：MySQL中使用show character set来查看所有可用的字符集。使用desc information_schema.character_sets显示所有字符集和该字符集默认的校对规则。使用show collation like 'gbk%'查看相关字符表的校对规则。（ci表示大小写不敏感，cs表示大小写敏感，bin表示二元）。MySQL的字符集和校对规则有4个级别的默认 设置：服务器级，数据库级，表级和字段级。

【备注】：使用索引的原则：搜索的索引列不一定是所要选择列；使用唯一索引，索引的列的基数越大，索引的效果越好，索引选择性别列最多也就有一半，效果不好；使用短索引；利用最左前索引；不要过度使用索引；InnoDB尽量自己制定主键。
在对索引字段进行范围查找是，只有btree索引可以通过索引访问。而hash索引实际上是全表扫描

【备注】：视图相对于普通的表的优势主要体现在：简单，安全，数据独立。
创建视图：create or replace view staff_list_view as select s.staff_id,s.first_name from staff;
MySQL的视图定义有一些限制，from关键字后不能包含子查询。
视图的更新性和视图中的查询定义有关。以下视图是不可更新的：
包含以下关键字的SQL语句：聚合函数，distinct,group by,having,union,union all；常量视图；select中包含子查询；join；from一个不能更新的视图；where字句的子查询引用了from字句中的表。

删除视图：drop view staff_list_view;
查看视图信息的操作：show table status like 'staff_list';
查询某个视图的定义：show create view staff_list_view;
查看系统表information_schema.views也可以查看视图的相关信息。
PS:貌似没有更改视图的！！！

六、存储过程和函数
存储过程和函数是事先经过编译并且存储在数据库中的一段SQL语句集合。函数必须要有返回值，而存储过程可以没有。
MySQL的存储过程和函数中允许包含DDL语句，也允许在存储过程中执行提交，但是不允许执行load data infile语句。
delimiter $$ 将语句结束符改成其他符号$$
delimiter ; 将语句结束符改成;

1.创建存储过程
create procedure film_in_stock(IN p_film_id INT,IN p_store_id INT,OUT p_film_count INT)
begin
  select inverntory_id
  from inventory
  where film_id=p_film_id
  and store_id=p_store_id
  and inventory_in_stock(inventory_id);

  select FOUND_ROWS() into p_film_count;
end;

2.删除存储过程
drop procedure film_in_stock;

3.查看存储过程的状态
show procedure status like 'film_in_stock';

4.查看存储过程的定义
show create procedure film_in_stock;

5.通过information_schema.Routines了解存储过程和函数的信息
select * from routines where ROUTINE_NAME='film_in_stock';

6.光标的使用：
create procedure payment_stat()
begin
  declare i_staff_id int;
  declare d_amount decimal(5,2);
  declare cur_payment cursor for select staff_id,amount from payment;
  declare exit handler for nou found close cur_payment;

  set @x1=0;
  set @x2=0;

  open cur_payment;

  repeat
    fetch cur_payment into i_staff_id,d_amount;
      if i_staff_id=2 then
        set @x1=@x1+d_amount;
      else
        set @x2=@x2+d_amount;
  until 0 end repeat;

  close cur_payment;

end;

7.流程语句的使用：if,case,loop,leave,iterate,repeat,while.
(1)if:
      if i_staff_id=2 then
        set @x1=@x1+d_amount;
      else
        set @x2=@x2+d_amount;

(2)case:
     case i_staff_id
         when 2 then
            set @x1=@x1+d_amount;
         else
            set @x2=@x2+d_amount;
     end case;

(3)loop,leave,iterate:
create procedure actor_insert()
begin
  set @x=0;
  ins:Loop
    set @x=@x+1;
    if @x=10 then
      leave this;
    elseif mod(@x,2)=0 then
      iterate ins;
    end if;
    insert into actor(actor_id,first_name,last_name) values(@x+200,'Test',@x);
  end loop ins;
end;

8.使用事件调度器
(1)创建事件调度器，每5s往表中插入数据：
create event test_event _t
on schedule
every 5 second
do
insert into test.t(id,time) values('test',now());

(2)打开调度器：
set global event_scheduler=1;

(3)创建事件调度器，每分钟清空表：
create event test_event_trunc
on schedule
every 1 minute
do
truncate table t;

(3)禁用调度器：
alter event test_event_t disable;

(4)删除调度器：
drop event test_event_t;

七、触发器：
1.创建触发器：
create trigger ins_film_bef
before insert on film for each row begin
   insert insto tri_demo(note) value("before insret");
end;

2.删除触发器：
drop trigger ins_film_bef;

3.查看触发器：
show triggers;
desc triggers;
select * from triggers where trigger_name='inf_film_bef';

八、事务控制和锁定语句
1.表锁：
lock table t read;
unlock tables;

2.事务控制：
start transaction
commit
rollback

【备注】：MySQL解决SQL Injection的方法：PrepareStatement和变量绑定，使用引用程序提供的转换函数，自己定义函数进行校验和过滤。

【备注】：SQL Mode:
select @@sql_mode;查看SQL所有的模式。
set sessopm sql_mode='ANSI'; 设置SQL模式。

九、MySQL分区：
【备注】：MySQL分区都要求分区键必须是INT类型。同时分区的名字是不区分大小写的。
MySQL分区类型：
RANGE分区：基于一个给定连续区间范围，将数据分配到不同的分区
LIST分区：累死RANGE分区，区别在LIST分区是基于枚举出的值列表分区，RANGE是基于给定的连续区间范围分区
HASH分区：基于给定的分区个数，把数据分配大不同的分区
KEY分区：类似于HASH分区

1.RANGE分区：
create table emp(
  id int not null,
  ename varchar(30),
  hired date not null default '1970-01-01',
  spearated date not null default '9999-12-31',
  job varchar(30) not null,
  store_id int nou null
)
partition by range(store_id)(
  pattition p0 values less than (10),
  partition p1 values less than (20),
  partition p2 values less than MAXVALUE
);

RANGE分区中，NULL值是最小值。

2.LIST分区：
create table expenses(
  expense_date date not null,
  category int,
  amount decimal(10,3)
)
partiton by list(category)(
  partition p0 values in(3,5),
  partition p1 values in (1,10),
  partition p2 values in (6)
)
LIST分区中，需要任何值都必须在值列表中找到。

3.Columns分区：
Columns分区相比与RANGE分区和LIST分区，支持更多的数据类型，同时也支持多列分区。
create table t(
  a int,
  b int
)
partiton by range columns(a,b)(
  partition p01 values less than(0,10),
  partition p02 values less than(10,10),
  partition p03 values less than(maxvalue,maxvalue)
);

4.HASH分区：
create table emp(
  id int not null,
  ename varchar(30),
  hired date not null default '1970-01-01',
  separated date nou null default '9999-12-31',
  job varchar(30) not null,
  store_id int not null
)
partition by hash(store_id) partitions 4;

5.KEY分区：
KEY分区不允许使用用户自定义的表达式，需要使用MySQl服务器提供的HASH函数。HASH分区只支持整数分区，但是KEY分区还支持BLOB or TEXT类型作为分区列，也可以不制定分区键，默认会首先选择使用主键作为分区将。
create table emp(
  id int not null,
  ename varchar(30),
  hired date not null default '1970-01-01',
  separated date nou null default '9999-12-31',
  job varchar(30) not null,
  store_id int not null
)
partition by hash( ) partitions 4;

6.删除分区：
alter table emp_date drop partition p2;

7.增加分区：
alter table epenses add parittion(partition p5 values in(7,8));

8.拆开分区：
alter table emp_date reorganize partition p3 into(
  partition p2 values less than(2005),
  partition p3 values less than(2015)
);