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)
);