MySQL技术分类一：DB应用开发基础整理

                             MySQL技术分类整理
一：DB应用开发基础
1.库
1).创建库
syntax：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

注：如果不使用if not exists，则当数据库存在的时候就会报错，如果使用了则不会报错且不会做任何操作，也就是说对现有数据库不存在任何破坏。
   建议：创建数据库的时候，不要加该选项，因为这可以明确提示我们数据库是否存在，如果加了当数据库存在，但没有任何错误提示，可能会导致脚本接下来的操作都不正常。
 
2).删除库
a.drop database [if exists] db_name;

注：这里的if exists，如果使用则不管数据库存在不存在都不会报错，且一定会删除其数据库。
    建议：删除数据库的时候加上该语句，防止在数据库不存在的时候产生报错信息。

b.在database目录下，直接删除相关的数据库文件即可 

3).数据库改名
a.如果数据库引擎是MyISAM，则可以直接修改database目录下数据库的文件名即可；
b.如果是非MyISAM引擎，则以上方法不可用，必须先导出数据，然后创建新数据库并导入数据，最后删除旧数据库；

4).查看数据的信息
a.查看数据库管理系统中所有的数据库信息
show databases;

b.查看某个数据库的创建信息
show create database db_name;

c.查看当前所在的数据库
select database();
注：database()是一个内置的函数，用来查看当前的数据库，而在MySQL中所有的函数调用都是通过select来调用

5).数据库属性的修改
Syntax:
ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

由上面的信息可知，对数据库的属性修改很少，只有字符集和校对规则两个属性

2.表
 表，是数据库中的重要元素，表必须依附于某个数据库下面，表用来存放了数据的一切属性及值，学习数据库也就是学习对表的各种操作。
1).创建表
表的创建方式有三种：
a.使用常规的create来创建表
create table [if not exists] tb_name (create_definition,...)
example: create table if not exists t1 (id int primary key auto_increment,name varchar(40));

b.使用select语句创建
create table [if not exists] tb_name [as] select ... from old_tb_name;
注：使用该方法创建的表的表字段取决于select后面所跟的字段名以及个数，创建的表的字段定义和old表是一样的，其次还将保存其对于字段的值，相当于创建一个子表或者附表

c.使用like形式创建
create table [if not exists] tb_name like old_tb_name;
注：使用该方创建的表只是创建了和old_tb_name表一样的表结构和定义，不包含old表中的任何数据，因此相当于复制了old表的表结构来创建新的表

注意：关于表的创建的定义和子选项非常多，具体信息请查看help帮助信息或者官网文档。对于开发来说重点就是设计表，创建表。

2).删除表
a.删除整个表
drop table [if exists] tb_name;

b.清空表的内容而保留表结构
truncate table tb_name;

3).查看表的信息
a.查看某个数据库下所有的表
show tables;
show table status; // 查看当前数据库下所有表的状态信息

b.查看表的创建信息
show create table tb_name;

c.查看表的结构字段信息
desc tb_name;

d.查看表中的数据信息
select {*|single_col} from tb_name;  //具体使用查看select语句的介绍

4).修改表的字段属性
常用的几个选项
syntax:
 alter table tb_name {add|drop|modify|change}...
add: 添加一个字段
drop：删除一个字段
modify: 修改某个字段的定义，但是字段名不变
change： 替换旧的字段，使用新的字段名及定义替换旧的字段名及定义

example:
alter table t1 add sex varchar(20);
多字段：alter table t1 add (weight int not null default 0,height int not null default 0); 其他几个同理
alter table t1 drop sex;
alter table t1 modify sex char(30);
alter table t1 change sex age int;

注：alter对表的修改功能和create 创建表的功能一样强大，几乎create table使用的定义选项，alter都可以对其进行修改定义

5).修改表的选项属性
 表的选项属性跟表的定义是不一样的，选项属性例如 engine，character collation等
example:
alter table t1 engine=innode character set utf8;

6).重命名表
    表的重命名的功能也经常使用；
syntax：
 rename old_tb_name to new_tb_name,old2_tb_name to new2_to_name...;
example:
 rename table t1 to new_t1;
 
 
3.用户及权限
1).用户管理
    MySQL中的用户与其它地方用户之说有点不一样，在DB中，用户的概念是用户名和主机的组合才被称为一个数据库用户，例如：'root'@'localhost',因此必须
要弄明白数据库中用户指的到底是什么。
a.创建用户
创建用户的方式有两种：
方法一：create user
 create user user_name [identified by "password"];
使用此方法创建的用户默认情况下是没有任何权限的,因此如果想单纯的创建一个用户，则可以使用该方法

方法二：grant 创建
 example:
  grant all privileges on *.* to 'lxm'@'localhost' identified by "password" [with grant option];
 使用这种方法也可以创建一个用户，但是在创建用户的同时也进行了授权，在实际工作中用的更多的是该方法，因为没有需要，是不允许存在无用的帐号的。

注：在mysql中创建的DB系统级用户帐号默认都是保存在mysql.user表中的。

b.删除用户
 删除用户也有两种方法：
方法一：delete语句
 delete from mysql.user where User = 'user_name' and Host = 'host_name';
使用该方法删除用户只是删除用户本身，如果对该用户授予了某些权限，则不会删除这些权限信息；

方法二:drop user
 drop user username;
使用该方法删除用户会一并删除用户的权限信息等，即彻底的删除用户帐号相关的所有信息；建议使用该方法

c.查看所有的用户信息
 在MySQL数据库中，并没有提供能够查询所有用户帐号的命令，但是我们可以使用select语句实现；
select distinct contact('user: ',User,'@',Host,';') as users from mysql.user;
example:
 mysql> select distinct concat('User: ',User,'@',Host,';') as users from mysql.user;
+----------------------------+
| users                      |
+----------------------------+
| User: root@127.0.0.1;      |
| User: @localhost;          |
| User: lxm@localhost;       |
| User: root@localhost;      |
| User: @node10.lxm.com;     |
| User: root@node10.lxm.com; |
+----------------------------+

2).权限管理
 只要存在用户的地方就有权限的管理
a.数据库中常见的权限控制
ALL PRIVILEGES ： 该权限表示出with grant option之外的所有权限；
WITH GRANT OPTION: 该权限表示对于授权的用户来说可以给其它用户进行授权的操作，即具有grant操作的权限；
GRANT OPTION： 授权grant的权限，和with grant option的功能是一样的，但是授权的方式却不一样；
ALTER: 表示授予alter命令的权限，针对表
ALTER ROUTINE: 表示可以修改存储过程
CREATE: 表示可以使用create命令，创建数据库，表和索引的权限
CREATE ROUTINE: 表示可以创建存储过程
CREATE TEMPORARY TABLES：表示可以创建临时表
CREATE USER: 表示可以创建用户
CREATE VIEW： 表示可以创建视图
DELETE： 表示可以使用delete命令，针对表删除数据权限；
DROP： 表示可以使用drop命令，删除数据库或这表权限；
EXECUTE： 表示可以调用存储过程(call procedure_name);
EVENT: 影响事件的执行能力；
FILE： 表示可以使用SELECT INTO OUTFILE 和LOAD DATA INFILE
INDEX： 表示可以对索引进行创建和删除，create index,drop index
INSERT: 表示可以使用insert命令，针对表
LOCK TABLES: 表示有权限锁定表
PROCESS： 表示可以查看当前的用户连接线程,show processlist;
RELOAD: 表示可以使用flush命令集，执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh,
  reload等命令的权限
REPLICATION CLIENT： 跟主从相关
REPLICATION SAVE: 复制从服务器所需的权限；
SUPER： 影响管理员级命令的使用，如CHANGE、MASTER、KILL thread、mysqladmin debug、PURGE MASTER LOGS和SET GLOBAL
SELECT： 表示可以使用select语句；
SHOW DATABASES: 表示可以查看数据库；
SHOW VIEW: 表示可以使用show create view
SHUTDOWN： 表示可以使用shutdown命令
UPDATE: 表示可以使用update语句
TRIGGER: 表示可以执行触发器
USAGE：该权限只能连接数据库，而不能做任何其它的操作，所有的用户都必须有该权限，否则将无法连接数据库

常见的MySQL权限分布：
表权限： 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
列权限： 'Select', 'Insert', 'Update', 'References'
过程权限： 'Execute', 'Alter Routine', 'Grant'
 
b.授予权限：grant
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

user_specification:
    user [IDENTIFIED BY [PASSWORD] 'password']

ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'

with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

以上为grant的语法格式，该命令的用处是很大的

example:
grant all privileges on *.* 'root'@'localhost' identified by "password" [with grant option];

c.查看用户相关的权限
show grants [for username];
如果不加for username，则表示查看当前用户的权限，如果加上for username，则表示查看特定用户的权限信息；
 
d.删除权限：revoke
 revoke命令用来删除用户的权限，但是必须该用户是存在的。如果用delete删除了用户，此时在用revoke删除权限，则会报错为没有定义该用户；
方法一：删除指定的权限
example: revoke select on *.* from 'lxm'@'localhost';

方法二：删除所有的权限
example: revoke all privileges,grant option from 'lxm'@'loalhost';

4.索引
 索引类似于书本的目录，加快查询的速度。在MySQL中常见的索引有：普通索引，主键索引，唯一索引，全文索引，聚合索引(多个列组合成的索引)
1).创建索引
 创建索引有三种方式：
a.create table 时顺带创建索引
example:
 主键索引：
  create table t (id int primary key,name varchar(20),sex char(10));
  create table t(id int ,name varchar(20),sex char(10),primary key (id));
 普通索引：
  create table t(id int,name varchar(20),sex char(10),index index_name (index_column));
 唯一索引：
  create table t(id int ,name varchar(20),sex char(10),unique index index_name (index_column));
 全文索引：
  create table t(id int ,name varchar(20),sex char(10), str text,fulltext (str));
 
 聚合索引：建议在上面索引的基础之上
  create table t (id int ,name varchar(20),sex char(10),index group_index (name,sex));
  

b.create index：
syntax:
 CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...) 

example:
 create index common_index on t1 (name);
 
c.alter 创建索引

alter table tablename add index/unique/fulltext [索引名] （列名）
alter table tablename add primary key (列名）//不要加索引名，因为主键只有一个

2).查看索引
syntax：
 SHOW INDEX FROM tbl_name [FROM db_name]

3).删除索引
 删除索引有两种方式：
a.drop index
syntax:
 DROP INDEX index_name ON tbl_name;

如果要删除主键索引，则必须使用下面的方式：
 DROP INDEX `PRIMARY` ON tbl_name;

b.alter 删除
 alter table tbl_name drop index/unique/fulltext index_name;
 alter table tbl_name drop primary key :删除主键索引

4).大数据迁移，索引处理机制
 当有大量的数据需要迁移时，此时如果设置了索引，则最好是先取消所有索引，然后迁移数据，最后在新的数据库表上在添加索引。之所以这样做是因为索引虽然加快了查询的速度，但是降
低了增删改的速度，且索引的容量有时可能比数据还要大。
 

5.变量
 对于数据库而言，也有自己的内部编程，例如自定义函数，存储过程。既然有涉及到编程，就不得不涉及到变量的概念，在mysql中变量大致分为以下几种内容：局部变量，用户变量，会
话变量，全局变量。
1).局部变量
 在mysql中，局部变量一般是在函数或者是存储过程中定义的，且使用declare来定义，当函数或者存储过程运行结束后，变量就失效了，因此被称为局部变量；局部变量也是自定义变量的一
种。
定义方式：
 declare c int [default 0]; //default可以为变量指定默认值
 
2).用户变量
 用户变量也是自定义变量的一种，它的使用范围比局部变量要大，其使用生命周期是当前用户的整个会话周期。一旦断开连接，则用户变量就失效了。
用户变量的定义：
 set @var_name = value;
 select @var_name := value;
 
3).会话变量
 会变变量是系统变量的一种，也就是说会话变量在用户连接时就已经初始化好，用户只能更改现有的且属于自己的会话变量，而无法新增会话变量。
查看所有会话变量：
 show session variables;
修改会话变量：
 set session sess_var = value; //常用
 set @@session.sess_var = value;
 set var_name = value;
查看会话变量：
 select @@var_name;
 select @@session.var_name;
 show session variables like 'pattern';

4).全局变量
 全局变量也是系统变量的一种，但是其生命周期是整个mysql的在线周期，只要mysql一直在线未重启，则所有全局变量都可以使用。但是重启后，则所有全局变量的更改全部失效，如果要长
期有效，则将变量写入配置文件中。
查看所有全局变量：
 show global variables;
修改全局变量：
 set global var_name = value;
 set @@global.var_name = value;
查看特定的全局变量：
 select global @@var_name;
 select @@global.var_name;
 show global variables like 'pattern'; 

6.函数
1).内置常用函数
now():查看当前时间，显示的是语句执行的当前时间
 example:select now(),sleep(2),now();
 虽然睡眠了2秒，但是第二now和第一个now一样，因为select执行的时间只有一个
sleep(num):设置睡眠时间
sysdate():显示系统时间，实时的时间
user():显示当前用户
database()：显示当前数据库
last_inert_id():显示最后一次的插入id号
connection_id():查看当前mysql连接线程的id，线程的id也可以从show processlist获取
concat()：连接字符串函数

max():取最大值

min():取最小值

sum(): 求和

avg(): 求平均值

count(*）：统计行数

2).自定义函数
 对于自定义函数来说，必须依赖于某个数据库的下面，也就是在哪个数据库下面创建的函数，在只能在该数据库下进行调用：
a.创建函数
syntax:
 create function 函数名 （参数列表）returns 返回值类型
 begin
 函数体;
 end

example:
delimiter $$
create [definer=`username`@`host_name`] function `sayHello` () returns varchar(20)
begin
return "hello world";
end

注：definer是用来定义函数的创建者或者说那个账户可以访问，如果没有明确定义，则默认是'root'@'localhost'用户。这在视图，存储过程中都默认存在。

带参数的函数：
delimiter $$
create definer='root'@'localhost' function sayHello(string varchar(20)) returns varchar(20)
begin
return string;
end
$$

select sayHello('hello world')

注：函数中还可以使用循环语句

b.查看库下的函数
show function status; //查看mysql数据库中所有的函数，并且可以看到隶属于那个库
show function code func_name; //该命令经测试无效；
show create function func_name; //查看创建函数的代码

c.调用函数
select func_name(param);

d.删除函数
drop function [if exists] func_name;

7.存储过程
 存储过程和函数有点类似，但是也有很大的区别，存储过程通常是一组sql的集合，可以对不同的表等进行操作以完成某些任务，而函数通常针对性较强且简
单不能用户修改表等操作。
a.创建存储过程
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type
 
这里要注意的是存储过程有三个参数：in,out,inout
 in :表示输入参数，调用时必须指定，可以在存储过程中修改，但是无法返回修改后的值，即存储过程中修改了参数的值并不影响外部的参数原值；
 out: 表示输出参数，可以在存储过程中修改其值，并返回.存储过程并不会调用原值，但是会返回新值覆盖其原值
 inout: 输入输出参数，调用是必须指定，可以在存储过程中修改，且返回值
 
example:
delimiter $$
create procedure test(out param int)
begin
select count(*) into param from mysq.user;
end
$$
delimiter ;

call test(@a)

select @a; //此时就可以看到变量a被赋值了

 
b.查看存储过程
show procedure status; //查看mysql数据库中所有的存储过程
show create procedure proc_name; // 查看特定的存储过程的创建代码

c.调用存储过程
call proc_name(param);

d.删除存储过程
    drop procedure [if exists] proc_name;

8.触发器
触发器概念：
 所谓触发器，就是当满足某个条件时引发的某种动作，在mysql中，当发生某个事件时，就会执行一段sql的代码完成某些工作；
 在mysql中只能有一类触发器，就是监听表中行数据的改变
 一个完整的触发器通常由：事件，事件执行的时机和执行代码所构成
  事件：insert ,update ,delete
  时机：before, after
  由事件和时机组合成为六种触发状况：before insert,before update,before delete,after insert,after update,after delete
 动态获取触发器程序内的数据：old,new
  old：监听事件所在表上的数据，在事件发生之前时的数据，旧的数据。
  new：监听表上事件发生之后，新处理完毕的数据。
  数据就是触发该事件的记录。也就说说old和new永远代表的是触发该触发器操作的表的数据。或者说代表的是事件之后的表中的数据。
  还需注意的地方：
  insert:在插入之前没有数据，因此不能使用old
  delete：在删除之后没有数据，因此不能使用new
 触发器也必须是依赖于某个库下面

1).创建触发器
syntax:
 CREATE [DEFINER = { user | CURRENT_USER }]  TRIGGER trigger_name trigger_time trigger_event  ON tbl_name
 FOR EACH ROW
 BEGIN
 body_code
 END

example:
 delimiter $$
 create definer='root'@'localhost' trigger `trigger_test` after update on t1
 for each row
 begin
 update t2 set age = age + 10;
 END
 $$
 delimiter ;
 
注：创建触发器的时候，body_code部分操作的表不能和触发器监听的表是一张表，否则会报错
 
2).查看触发器
SHOW TRIGGERS [FROM db_name] [like_or_where]
show create trigger trigger_name; //查看具体触发器的创建代码

3).删除触发器
drop trigger trigger_name;

9.视图(view)
视图的相关概念：
 视图是mysql中的一种内部虚拟表，想对于真实表而言，其并不存在，是真实表的一个虚拟对象；
 视图对于用户来说是一张表，但是对于数据库内部来说其不是一张表，是真实表某些字段的映射，保存的只是一个语句；
 视图在未执行前其并不是一张表，而是一串sql语句，在对视图进行操作的时候，视图才会临时使用sql代码形成一张临时表或者嵌套真实语句形成新的语句对真实表进行操作；
 视图的最终操作结果还是真实表；
 视图通常是一串select语句从真实表中查询出需要的内容形成视图表展示给客户，这样可以隐藏不需要对用户开放的数据；
 视图表的数据和真实表的数据息息相关，视图表数据的改变也会影响真实表数据的改变，而真实表数据的改变也会影响视图表数据的改变，但为了安全起见，通常视图表只给予查询的权限，
  一切修改操作都将在真实表中完成

视图的算法：
 视图的算法决定了视图在执行时的一种流程，视图有三种算法(algorithm)：merge,temptable,undefined
  merge: 使用该算法，在访问视图的时候采用的是一种合并的机制，将用户的查询语句和视图的查询语句合并起来，最终对真实表进行操作；
  temptable: 临时表，使用该算法，在访问视图的时候，首先会创建视图的临时表，然后用户的查询操作针对临时表进行操作，此访问才有视图的效果
  undefined； 视图默认采用的算法，使用该算法，系统会根据情况自动从merge和temptable中选择一种合适的算法进行视图操作，因此无特殊需要，通常保留为undefined
1).创建视图
Syntax:
CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
 
从上面的语法格式来看，主体为：
 create view view_name [(column_list)] as select_statement;

example:
 create view v_test (v_name,v_age) as select name,age from t;
如上所示一个视图就创建完成了，这里使用了列改名(v_name,v_age),如果不加，则默认使用select后面所跟的查询字段名；
 
2).查看视图
 在mysql中没有专门的视图查看命令来查看视图的信息，但是可以通过查看表状态信息把视图过滤出来。这是因为在创建视图的时候，会自动在表状态信息的comment字段中添加view的字样。
show table status where comment='view'; //注：必须在数据库下面操作，查看的是当前数据库下面的视图信息

show create view view_name;//通过上面的命令可以查看到视图名，通过该命令则可以查看具体视图的创建代码；

查看视图的表结构：
 desc view_name;
 

3).修改视图
 修改视图其实是修改视图的sql代码而已，或者说是修改视图的虚拟表结构，但是跟真正的表的修改又不一样，视图的修改必须是改变了整个的查询字段；
syntax:
 ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
从上面的语法可知，和创建视图基本是一样的，只是修改视图的时候，视图名称是不能变化的
example:
 alter view v_test (vv_name,vv_age,vv_sex) as select name,age,sex from t;
 
4).删除视图  
drop view if exists view_name;

10.事务
事务的概念：
 a.数据库中所谓的事务，实则是一串sql语句执行并生效的时刻控制的问题。当启动事务时，接下来的一连串的sql语句都是事务的执行语句，直到提交(commi
t)命令给出，一个事务才算结束。而在事务未提交之前，事务语句执行结果的可见与否是由当前的事务隔离级别来控制的，但是即使结果可见也并不是真正生效，
取决于事务的提交(commit)还是回滚(rollback)
 b.在未事务未提交的之前，事务都是可以回滚的，可以回滚所有的操作，也可以回滚到某个保存点(必须设置事务保存点).所谓回滚即是取消事务所有的操作
 c.事务的隔离级别：
  read uncommitted: 表示事务未提交时，对于写入数据的事务，不允许其它的事务对该行的数据进行写操作，但是允许其它的事务读取此行的数据；
  read committed: 表示事务提交后，才能读取。读取数据的事务允许其它事务对该行数据进行访问，但是未提交的写事务是不允许其它人进行读取操作；
  repatable read:  表示事务在未提交之前,读取数据的事务允许其它事务的读取访问，但是读取的是原始的数据，禁止写事务的操作，写数据的事务则
   禁止其它任何事务的操作。
  seriablizable:可串行事务，同一个时刻只允许一个事务在执行，此时不存在任何的问题
 
  注：在mysql中默认的事务隔离级别是repatable-read,通过变量tx_isolation来设置的，通常是不需要修改此选项，但是如果想提高并发能力，则可以将
   事务的隔离级别设置的低一点，但是此时对于开发来说要求很高，必须保证数据不出问题。
 d.事务的ACID特性
  原子性(一组sql语句，要么都成功，要么都失败）
  一致性（事务周期内，事务是一致的，只受事务的影响，不会受到其它的改变）
  隔离性（多个事务之间是互相隔离的，不会互相影响）
  持久性(当事务提交之后，修改永久生效）
 e.事务的执行过程
  事务的执行过程跟一般的sql语句执行过程不一样，一般的sql语句直接针对数据文件进行操作。而事务则分为两步，首先事务的执行语句会现在事务日志
 中进行执行，当使用commit命令后，才会同步到数据文件中。正是因为这个原因使得事务可以进行回滚。此外当一个事务中的语句执行有错误时，该错误语句
 的执行结果是不会生效的。
  
 f.支持事务的引擎
  在mysql中，最常用的引擎有两种，一种是MyISAM，一种是InnoDB，而其中支持事务的引擎只有InnoDB。因此要想使用事务必须是用InnoDB引擎。在mysql
 5.5.x以后的版本中，默认的引擎已经改为InnoDB。
 
1).事务的执行与回滚
syntax:
 start transaction;
 sql1;
 sql2;
 commit/rollback;
 
2).事务保存点的回滚
syntax:
 start transaction;
 sql1;
 savepoint point_name;
 sql2;
 savepoint point_name;
 commit/rollback to savepoint point_name;

注：
 a.回滚到某个point之后，则包含point及其之前的数据修改在提交之后是生效的，而point之后的数据修改则不生效
 b.start transaction是显式的开始事务，而必须使用commit或者rollback表示结束一个事务。如果不想使用start，则表示讲autocommit设为0，不自动提交
  默认情况下是自动提交的，只有将commit设为0才能手动提交，事务执行后还要改为自动提交，否则会影响其它语句的执行。因此标准的做法是start显式
  开启。
  

11.字符集和校对规则
 简单的理解字符集就是某种字符编码规则，将字符和编码进行一一对应转换。存储数据时，是根据字符编码规则讲数据进行转换成特定的编码格式进行存储，
而在展示数据时，则根据编码规则将特定的编码转换成容易理解的数据进行展示。所谓校对规则则是字符集内部编码的排序方式。
1).MySQL中的字符集变量
 character_set_server：默认的内部操作字符集 ：mysql内部进行数据存储时候的字符集
 character_set_client：客户端来源数据使用的字符集 ：告诉服务器客户端发来的命令使用的字符集
 character_set_connection：连接层字符集 ：转换层字符集用作客户端和服务器字符集不一致时的转码工作
 character_set_results：查询结果字符集 ：查询结果以什么字符集发送给客户端
 character_set_database：当前选中数据库的默认字符集
 character_set_system：当设置中文数据库名是，将以此处设置字符集的编码的格式来保存数据库。该编码为当前标识符的编码，或者说是字段名的编码。该
       变量是一个只读变量，不可以对其修改，该值是系统自动识别设置。

2).MySQL中的字符集转换过程
 a.MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
 b.进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：
  使用每个数据字段的CHARACTER SET设定值；
  若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
  若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
  若上述值不存在，则使用character_set_server设定值。
 c.将操作结果从内部操作字符集转换为character_set_results

注：对于字符集而言，最常见的问题就是乱码的问题。乱码之所以产生，就是因为变量值设置的不合理导致字符集之间的转换出了问题。建议在使用中可以全部设
置为utf8的字符集编码。

3).编码的设置
 除了设置每一个变量之外，还可以使用偷懒的方式：set names code_name; 但是使用此方法的前提是character_set_client/connection/results三个变量的
字符集必须一致才行，也就是说set names 的结果是将这三个变量设为一个值。

12.增删改查以及show语句
 这里的增删改查只是从运维的角度来叙述需要掌握的基础内容，至于详细的参数设置等细节可自行查看help信息或者官方文档手册等
insert: 插入，增加数据
语法：
 insert into tb_name(col1_name,col2_name,...) values(col1_value,col2_value,...),(col1_value,col2_value,...),...;
插入数据通常分为两种：
单数据：
 insert into tb_name(col2_name,col2_name,...) values(col1_value,col2_value,...);
多数据：
 insert into tb_name(col1_name,col2_name,...) values(col1_value,col2_value,...),(col1_value,col2_value,...),...;
 
注：
1).对于写出来的字段，在values后面必须有其值相对应，也就是说字段名个数与值的个数必须相等；
2).对于有自动增长的列字段，可以不用写出，其会自动增加，但是也不能对其明确赋值；

delete:  删除表中的数据
syntax:
 DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count

1).删除表中的所有数据；
delete from tb_name;

2).删除where过滤的内容；
delete from tb_name where where_condition;

3).删除限制的几行内容；
delete from tb_name limit row_count;

update:  更新表中的数据
syntax:
 UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
 
1).更新相同字段的所有行的值；
update tb_name set col_name = col_value;

2).更新特定行的值
update tb_name set col_name = col_value where where_condition;

3).更新截取行的值
update tb_name set col_name = col_value limit row_count;

select: 查询语句
 查询语句的使用是MySQL中的重重之重，其功能之强大支撑着各种数据分析工作,因此看似简单的查询语句其实也是最难学的一个语句。下面就简单的总结
下：
syntax：
 SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

注：对于语法不做多说，但是特别要强调的是查询子语句的顺序，where,group by,having,limit这些的排列顺序，如果有多个组合时一定要遵循此规则。
 
1).普通查询
(1).无子句查询
select * from tb_name; //查询表中所有字段的内容
select col1_name,col2_name,... from tb_name;  //查询表中指定字段的内容

(2).where 子句查询
 where查询用作条件的过滤，后面所跟的内容为条件的判断
example:
 select * from tb_name where name = 'jack';
where条件过滤类型：
a.比较运算符
 <  小于
 <=  小于等于
 = 等于
 in 在某集合内（等于集合内的任意一个值都行） exmaple: in (1,2,3)
 not in :不在集合之内  example　not in (1,2,3)
 != or <> 不等于
 >= 大于等于
 > 大于
 between  num1 and num2 在某范围内大于等于num1小于等于num2

注：在数据库系统中，列字段可以当作变量进行计算
example:
 select * from price where new_price - old_price > 2000;

b.逻辑运算符
NOT 或 ！ 逻辑非
OR 或 || 逻辑或
AND 或 && 逻辑与

c.模糊查询
like模糊插叙：
 select goods_id,goods_name from goods where goods_name like '三星‘；

 %->通配任意字符
 ‘-’-> 通配单个字符
 
正则表达式查询：
REGEXP,RLIKE：支持正则表达式的写法
  example:
   select name from students where name rlike '^[XY].*$';

(3).group by 子句查询
 group by 查询是将表中的数据按照某字段的值进行分组并显示组中的第一个值
syntax:
 select col1,col2,...from tb_name group by col1,col2,...
 
注：group by 的用法不难，但是结果需要注意。group by 查询之后默认显示的是分组中的一条记录，此时毫无意义，但是group by 和聚合函数一起使用就显得很重要。

常见的聚合函数：
 avg(col_name): 取平均值
 sum(col_name): 取和
 max(col_name): 取最大值
 min(col_name): 取最小值
 count(*):  统计行数
 
因此，当group by 和这些聚合函数一起使用时，就可以取出每组中平均值，最大值，最小值等。千万不要尝试在分组的情况下使用排序加limit的取最大最小值，那样的结果是错误的。
example:
 select id,max(days) from tb_name group by c_id; //以c_id进行分组，并取每组中days最大的一组值

如果想显示某个字段的所有值，则可以结合group_concat()函数进行使用：
example:
 select id,t_id,c_id,group_concat(days) from join_teacher_class_time group by c_id;
+----+------+------+--------------------+
| id | t_id | c_id | group_concat(days) |
+----+------+------+--------------------+
|  1 |    1 |    1 | 15,20,15,15,5      |
|  2 |    1 |    2 | 18,22              |
|  3 |    1 |    3 | 22,15              |
+----+------+------+--------------------+

(4).having 子句查询
 having 子句查询和where子句查询几乎是一样的，但是有一个很重要的区别。having子句查询具备在查询结果之上在进行筛选，而where则不具有这个功能。
example:
select id,t_id,c_id as class_id,days from join_teacher_class_time having class_id = 1;
+----+------+----------+------+
| id | t_id | class_id | days |
+----+------+----------+------+
|  1 |    1 |        1 |   15 |
|  4 |    2 |        1 |   20 |
|  6 |    3 |        1 |   15 |
|  7 |    1 |        1 |   15 |
|  9 |    2 |        1 |    5 |
+----+------+----------+------+
5 rows in set (0.00 sec)

由上面的可见，c_id 被命名为class_id，having可以完好插叙，看where：
mysql> select id,t_id,c_id as class_id,days from join_teacher_class_time where class_id = 1;
ERROR 1054 (42S22): Unknown column 'class_id' in 'where clause'

由上面可见，where查询报错了。

(5).order by 子句查询
 order by子句查询，顾名思义是用来对查询结果进行排序的。可以通过asc和desc来指明是升序排序还是降序排序，默认情况下是按升序排序的。通常会结合limit子句查询获取最大值和最小
值。
example:
 select * from tb_name order by c_id desc limit 1 //按照c_id进行排序并获取c_id最大的一组值

(6).limit 子句查询
 limit 子句查询，顾名思义限制查询，限制查询结果显示的行数。
syntax:
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]
用法一：
 select col1,col2,... from tb_name limit row_count; //明确表示取多少行
用法二：
 select col1,col2,... from tb_name limit num1,num2 //表示从num1行开始往后去num2行数据，不包括num1行数据

(7).into 子句查询
syntax:
  [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]

从上面可知，into的用法有三种：
用法1：into outfile  将数据导入到一个文件中
example:
 select col1,col2,...from tb_name into outfile "filename";
 
对于outfile的文件还可以使用格式符号，常见的有以下几种：
  fields terminated by ','  --字段的结束符
 optionally enclosed by '"' –-字符串的结束符
 lines terminated by '\n'  --行的结束符

用法2：into dumpfile  用来将数据导入到一个文件中，但是这个用法很奇特，只能导入一行的内容，且无任何格式符号可用
 select * from tb_name limit 1 into dumpfile "filename";

用法3：into var_name  使用该方法可以为一个变量赋值
example:
 select c_name from tb_name limit 1 into @test;
 select $test
mysql> select @test;
+---------+
| @test   |
+---------+
| php0115 |
+---------+
1 row in set (0.00 sec) 
 
(8).去重复查询 ：distinct
example:
 select distinct col1,distinct col2,...from tb_name;

2).子查询
 所谓子查询有点类似于嵌套查询，外层查询和内层查询都是一个完成的查询语句，而外层查询是基于内层查询。注意，千万不要和子句查询混淆了。

(1).where 子查询
关键：把内层的查询结果当作外层的条件进行比较
example:
  select * from join_class where room = (select max(room) from join_class);

(2).from 子查询
关键：把内层的查询结果当作临时表供外层查询
example:
 mysql> select * from (select * from join_class where room < 200) as tmp;
+----+---------+------+
| id | c_name  | room |
+----+---------+------+
|  2 | php0228 | 104  |
|  3 | php0331 | 102  |
+----+---------+------+

(3).exists 子查询
关键：把外层的查询结果拿到内层，看内层是否成立(有点虚幻)
example:
 查栏目下有商品的栏目：
 select cat_id,cat_name from category where exists (select * from goods where goods.cat_id = category.cat_id);

3). 多表连接查询
测试表内容：
mysql> select * from join_class;
+----+---------+------+
| id | c_name  | room |
+----+---------+------+
|  1 | php0115 | 207  |
|  2 | php0228 | 104  |
|  3 | php0331 | 102  |
|  4 | php0505 | 202  |
|  6 | php0202 | 302  |
+----+---------+------+
5 rows in set (0.00 sec)

mysql> select * from join_teacher;
+----+-----------+--------+
| id | t_name    | gender |
+----+-----------+--------+
|  1 | 韩信      | male   |
|  2 | 李白      | female |
|  3 | 韩非子    | secret |
|  4 | 孙武      | male   |
|  5 | 王八      | male   |
+----+-----------+--------+
5 rows in set (0.00 sec)

(1).合并查询: union (union all)
合并查询的概念：
 所谓合并查询是将多张的表的查询结果合并起来，但是此处的合并是从上往下的方式进行合并，而不是左右合并
 合并查询的命令是union，默认情况下合并结果如果有重复的行，则会进行去重复的操作，如果想显示所有的行，则使用union all
 合并查询的前提是字段个数必须一样多，其次字段类型也最好一样，如果不类型不一样可能会出现混乱。
 合并查询中，如果字段名不一样，则以第一个查询列字段为准
 合并查询中的order by子句如果不跟limit则会被语法分析器去除，因为order by很影响性能
syntax:
 (select col1,col2,... from tb1_name) union (select col1,col2,...from tb2_name);
 (select col1,col2,... from tb1_name) union all (select col1,col2,...from tb2_name);

(2).笛卡尔积查询
 所谓笛卡尔积就是两张表在没有任何条件判断的情况下进行互乘.例如表A M行，表B，N行，则表A，表B的笛卡尔积为M*N
syntax:
 select * from tb1,tb2,...;

example:
 mysql> select * from join_class,join_teacher;
+----+---------+------+----+-----------+--------+
| id | c_name  | room | id | t_name    | gender |
+----+---------+------+----+-----------+--------+
|  1 | php0115 | 207  |  1 | 韩信      | male   |
|  2 | php0228 | 104  |  1 | 韩信      | male   |
|  3 | php0331 | 102  |  1 | 韩信      | male   |
|  4 | php0505 | 202  |  1 | 韩信      | male   |
|  6 | php0202 | 302  |  1 | 韩信      | male   |
|  1 | php0115 | 207  |  2 | 李白      | female |
|  2 | php0228 | 104  |  2 | 李白      | female |
|  3 | php0331 | 102  |  2 | 李白      | female |
|  4 | php0505 | 202  |  2 | 李白      | female |
|  6 | php0202 | 302  |  2 | 李白      | female |
|  1 | php0115 | 207  |  3 | 韩非子    | secret |
|  2 | php0228 | 104  |  3 | 韩非子    | secret |
|  3 | php0331 | 102  |  3 | 韩非子    | secret |
|  4 | php0505 | 202  |  3 | 韩非子    | secret |
|  6 | php0202 | 302  |  3 | 韩非子    | secret |
|  1 | php0115 | 207  |  4 | 孙武      | male   |
|  2 | php0228 | 104  |  4 | 孙武      | male   |
|  3 | php0331 | 102  |  4 | 孙武      | male   |
|  4 | php0505 | 202  |  4 | 孙武      | male   |
|  6 | php0202 | 302  |  4 | 孙武      | male   |
|  1 | php0115 | 207  |  5 | 王八      | male   |
|  2 | php0228 | 104  |  5 | 王八      | male   |
|  3 | php0331 | 102  |  5 | 王八      | male   |
|  4 | php0505 | 202  |  5 | 王八      | male   |
|  6 | php0202 | 302  |  5 | 王八      | male   |
+----+---------+------+----+-----------+--------+
25 rows in set (0.00 sec) 

(3).左右连接查询
概念：
 左连接是以左表为基准，从右表中查找匹配的行组合成一行，如果右表中没有则返回null
 右连接是以右表为基准，从左表中查找匹配的行组合成一行，如果左表中没有则返回null
 通常左右连接可以互相转换，为了统一，建议使用左连接，即讲右连接转换为左连接
syntax:
左连接：
 select col1,col2,...from tb1 left join tb2 on tb1.colN = tb2.colN;
右连接：
 select col1,col2,...from tb1 right join tb2 on tb1.colN = tb2.colN;
 
example:
左连接：
 mysql> select * from join_class left join join_teacher on join_class.id = join_teacher.id;
+----+---------+------+------+-----------+--------+
| id | c_name  | room | id   | t_name    | gender |
+----+---------+------+------+-----------+--------+
|  1 | php0115 | 207  |    1 | 韩信      | male   |
|  2 | php0228 | 104  |    2 | 李白      | female |
|  3 | php0331 | 102  |    3 | 韩非子    | secret |
|  4 | php0505 | 202  |    4 | 孙武      | male   |
|  6 | php0202 | 302  | NULL | NULL      | NULL   |
+----+---------+------+------+-----------+--------+
5 rows in set (0.00 sec)
 
右连接：
 mysql> select * from join_class right join join_teacher on join_class.id = join_teacher.id;
+------+---------+------+----+-----------+--------+
| id   | c_name  | room | id | t_name    | gender |
+------+---------+------+----+-----------+--------+
|    1 | php0115 | 207  |  1 | 韩信      | male   |
|    2 | php0228 | 104  |  2 | 李白      | female |
|    3 | php0331 | 102  |  3 | 韩非子    | secret |
|    4 | php0505 | 202  |  4 | 孙武      | male   |
| NULL | NULL    | NULL |  5 | 王八      | male   |
+------+---------+------+----+-----------+--------+
5 rows in set (0.00 sec)

mysql> select * from join_teacher left join join_class on join_class.id = join_teacher.id;
+----+-----------+--------+------+---------+------+
| id | t_name    | gender | id   | c_name  | room |
+----+-----------+--------+------+---------+------+
|  1 | 韩信      | male   |    1 | php0115 | 207  |
|  2 | 李白      | female |    2 | php0228 | 104  |
|  3 | 韩非子    | secret |    3 | php0331 | 102  |
|  4 | 孙武      | male   |    4 | php0505 | 202  |
|  5 | 王八      | male   | NULL | NULL    | NULL |
+----+-----------+--------+------+---------+------+
5 rows in set (0.00 sec)
 
从上面的信息可见，左右连接是可以互换的，当然这里的位置好像不一致，如果要一直，只要在select的时候指定列的顺序即可。

(4).内外连接查询
概念：
 内连接既是两张表的交集，必须在两种表中都有匹配的内容才显示，否则不显示，即不会显示为null的值
 外连接既是两张表的并集，在mysql中没有外连接语句，但是可以整合左右连接来达到实现外连接的效果
syntax:
内连接：
 select * from tb1 inner join tb2 on tb1.colN = tb2.colN;
外连接：
 转换：(select * from tb1 left join tb2 on tb1.colN = tb2.colN) union (select * from tb1 right join tb2 on tb1.colN = tb2.colN);
 
example:
内连接：
mysql> select * from join_class inner join join_teacher on join_class.id = join_teacher.id;
+----+---------+------+----+-----------+--------+
| id | c_name  | room | id | t_name    | gender |
+----+---------+------+----+-----------+--------+
|  1 | php0115 | 207  |  1 | 韩信      | male   |
|  2 | php0228 | 104  |  2 | 李白      | female |
|  3 | php0331 | 102  |  3 | 韩非子    | secret |
|  4 | php0505 | 202  |  4 | 孙武      | male   |
+----+---------+------+----+-----------+--------+

外连接：
 mysql> (select * from join_class left join join_teacher on join_class.id = join_teacher.id) union (select * from join_class right join join_teacher on join_class.id = join_teacher.id);
+------+---------+------+------+-----------+--------+
| id   | c_name  | room | id   | t_name    | gender |
+------+---------+------+------+-----------+--------+
|    1 | php0115 | 207  |    1 | 韩信      | male   |
|    2 | php0228 | 104  |    2 | 李白      | female |
|    3 | php0331 | 102  |    3 | 韩非子    | secret |
|    4 | php0505 | 202  |    4 | 孙武      | male   |
|    6 | php0202 | 302  | NULL | NULL      | NULL   |
| NULL | NULL    | NULL |    5 | 王八      | male   |
+------+---------+------+------+-----------+--------+

注：
 a.如果内连接没有使用条件，则等价于笛卡尔积；
 b.所为的外连接其实就是联合查询(union)
 c.除了on条件外，还有where和using条件，但是通常建议使用on
  on：表示数据连接条件，条件不满足，连接都不形成，建议使用的方法
  where：表示数据过滤条件，先形成笛卡尔积，然后在过滤出想要的数据
  using：只适用于字段一样的，并且会去掉结果中的重复字段，并放在列前。专门为连接条件设置的条件
   example: select * from join_class left join join_teacher using(id);

(5).自然连接
 所谓自然连接，也即在不给定条件的情况下，也能自己进行判断连接。相比内外左右连接而言，可以在不给定条件的情况下，系统自动使用相同的列字段名
进行连接。如果没有相同的列字段名，则等价于笛卡尔积。
 自然连接又分为三种：自然内连接，自然左外连接，自然右外连接
syntax:
 自然内连接：
 select * from tb1 natural join tb2; == select * from tb1 inner join tb2 using(public_field);
 自然左外连接：
 select * from tb1 natural left join tb2;  == select * from tb1 left join tb2 using(public_field);
 自然右外连接：
 select * from tb2 natural right join tb2;  == select * from tb1 right join tb2 using(public_field);
 

show: 查看相关的信息
syntax:
SHOW AUTHORS  ： 查看MySQL数据的作者信息 
SHOW {BINARY | MASTER} LOGS  //查看binlog日志信息
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]  //查看binlog日志内容
SHOW CHARACTER SET [like_or_where]  //查看字符集信息
SHOW COLLATION [like_or_where]  //查看校对规则信息
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]  //查看表的字段信息，等价于desc tb_name;
SHOW CONTRIBUTORS    //查看贡献者信息
SHOW CREATE DATABASE db_name   //查看创建数据的代码
SHOW CREATE EVENT event_name  //查看创建事件的代码
SHOW CREATE FUNCTION func_name  //查看创建函数的代码
SHOW CREATE PROCEDURE proc_name  //查看创建存储过程的代码
SHOW CREATE TABLE tbl_name  //查看创建表的代码
SHOW CREATE TRIGGER trigger_name   //查看创建触发器的代码
SHOW CREATE VIEW view_name  //查看创建视图的代码
SHOW DATABASES [like_or_where]  //查看数据的信息
SHOW ENGINE engine_name {STATUS | MUTEX}  //查看特定存储引擎的状态信息
SHOW [STORAGE] ENGINES  //查看系统中支持的存储引擎信息
SHOW ERRORS [LIMIT [offset,] row_count]  //查看当前会话所发生的错误信息
SHOW EVENTS  //查看事件信息
SHOW FUNCTION CODE func_name  //查看特定函数的代码信息，但是目前似乎废弃无效
SHOW FUNCTION STATUS [like_or_where]  //查看函数的状态信息
SHOW GRANTS FOR user  //查看特定用户的权限信息
SHOW INDEX FROM tbl_name [FROM db_name]  //查看表的索引信息
SHOW MASTER STATUS  //查看当前binlog的pos信息等，用在主从的情况下
SHOW OPEN TABLES [FROM db_name] [like_or_where]  //查看当前打开的表的信息
SHOW PLUGINS //查看支持的插件信息
SHOW PROCEDURE CODE proc_name  //查看特定存储过程的信息
SHOW PROCEDURE STATUS [like_or_where]  //查看存储过程的状态信息
SHOW PRIVILEGES  //查看数据库支持的所有权限控制信息
SHOW [FULL] PROCESSLIST  //查看当前打开的连接线程信息
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]  //查看profile的信息
SHOW PROFILES  //分析mysql的性能
SHOW SLAVE HOSTS  //查看当前的从服务器
SHOW SLAVE STATUS  //查看从服务器的主从状态信息
SHOW [GLOBAL | SESSION] STATUS [like_or_where]  //查看全局或者会话的状态变量信息
SHOW TABLE STATUS [FROM db_name] [like_or_where]  //查看表的状态信息
SHOW [FULL] TABLES [FROM db_name] [like_or_where] //查看所有的表
SHOW TRIGGERS [FROM db_name] [like_or_where]  //查看触发器的信息
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] //查看全局后者会话的变量信息
SHOW WARNINGS [LIMIT [offset,] row_count] //查看警告信息

like_or_where:
    LIKE 'pattern'
  | WHERE expr

13.数值类型
 在MySQL中数值类型其实就是编程语言中所说的变量类型，但是不同的体系结构下有点区别而已。在MySQL中数值类型其实是一种域约束，或者说是字段值的约
束，其规定了该字段能够存储那些类型的数据以及其长度大小等。
 MySQL中常见的数值类型：
整数型：
 tinyint(num):最小整数型，1个字节，无符号范围为0-256，有符号范围是-128-127 默认是有符号的，通常用在年龄这些范围不大的情况下
 smallint(num):2byte，无符号范围0-65536，有符号范围-32768-32767
 mediumint(num):3byte
 int(num):4byte
 bigint(num):8byte
 
 注：这里的num表示的是允许显示的数字个数，而不是代表的字节数

浮点型：
 float(M,D):单精度浮点数，4个字节，可以提供7位有效数字
 double(M,D):双精度浮点数，8个字节，可以提供16位有效数字
 
 注：M表示的数值总位数，不包括小数点；D表示的是小数点的个数
 
decimal:定点小数类型
 decimal(M,D): M表示的总长度，不包括小数点，D表示的是小数的个数
注：看起来decimal好像和浮点类型是一样的，但是其并不属于浮点类型，也没有明确的表明其属于那种类型，这里我表示的是定点小数类型，意思是该类型的数
据必须保证有D位小数，也就是说即使你存入的是个整数，但是会自动以0填充加上小数位，读取时自然是带小数位。而浮点型就不同了，加入你存入的是整数，则
会按照整数类型存入，没有任何小数位，这在对小数为要求高的地方就容易出问题了。

example:
 decimal(10,2)---->存入10时---->10.00
 float(10,2)----->存入10时----->10
 
字符型：
 char(M):定长字符型，不区分大小
 binary(M):二进制定长字符型，区分大小写
 varchar(M):不定长字符型，不区分大小写
 varbinary(M):二进制不定长字符型，区分大小写
 text: 文本类型，通常用在大段文本内容的地方
 logtext: 长文本类型
 
 注：M表示的是字符长度
 
 常见字符型需要空间大小以及最大长度
 type     sotrage required                            max length
 char(M)     M characters                             255
 varchar(M)   L characters plus 1 or 2bytes           65535
 tinytext     L character+1byte                        255
 text         L character+2byte      65535   
 mediumtext    L character+3byte                     16777215
 longtext l character+4bytes                      4294967295
 binary   0-255
 varbinary  0 - 65535
 blob：二进制大对象字符类型
 tinyblob:最大255个字节  1 byte overhead
 blob:最大64kb，2 bytes overhead 
 mediumblob--up to 16Mb 3 bytes overhead
 longblob---up to 4Gb 4 bytes overhead

日期时间型：
 日期时间相关的数值类型有如下几种：
 date  '1000-01-01' to '9999-12-31'  3bytes
 time  '-838:59:59' to '838:59:58'    3 bytes
 datetime :date 和time的组合          8 bytes
 year  00 to 99  1bytes
 year(4)  1901 to 2155 1 bytes
 timestamp  '1970-01-01 00:00:00' to '2038-01-18 22:14:07'  4 bytes

枚举型：enum  用于单项选择，2个字节
example:
create table tb_enum (
a enum ('female'，'male'）
）；
枚举的特点：
1）从数据的角度限制可能的选项
2）枚举看起来是字符类型，其实存储的是整型
3)枚举最多允许65535个枚举

集合型：set 用户多项选择，最多可以占8个字节，最多64个选项
example:
create table s_9(
hobby set('basket','football','pingpang')
);
用户在选择的时候可以选择多个

14.MySQL存储引擎
 存储引擎在数据库系统中是非常重要的组成部分，其负责数据库系统中数据的展示和存储方式的转换。不同的存储引擎其所能支持的功能也是有所不同的，在
MySQL中支持的存储引擎有：MyISAM,InnoDB,MEMORY等，至于其它所有支持的存储引擎可以使用show engines来查看。
 在平时的生产环境中，常见以及经常使用的存储引擎则是：MyISAM　和 InnoDB。对于这两种引擎比较大致有几点主要的区别：
                    myisam      innodb
批量插入速度   高       低
事务安全            支持
全文索引      支持  5.5以后支持
锁机制       表锁  行锁（并发性好）
存储限制     无限制  64TB
B数索引          支持  支持
哈希索引            支持
集群索引            支持
数据缓存            支持
索引缓存            支持
数据可压缩      可压缩  不支持
空间使用       低       高
内存使用             低          高
外键支持             否         支持

 因此从上面的比较来看，通常业界的普遍的选择标准是，如果不需要事务的功能，不是频繁的写入，则选项MyISAM引擎，而如果需要事务的功能，写操作频繁
则选择InnoDB引擎

15.sql 模型
 sql mode指的是数据库中sql语句需要遵从的规则法则或者说是某些限制，不要跟数据模型搞混淆。
sql mode definitions:
1).ANSI QUOTES:
 该模型下,双引号的含义和反引号的含义是一样的，只能用来引用库名，表
名和字段名，而其它任何字符串只能使用单引号来引用。
2).IGNORE_SPACE:
 该模型下，在内建函数中会忽略多余的空白字符，否则可能会出错
3).STRICT_ALL_TABLES
 该模型下，在插入表时,任何非法的数值都是被拒绝的
4).STRICT_TRANS_TABLES：
 该模型下，任何支持事务表中的插入非法数值的时候是不允许的，并抛出错
误。
5).TRADITIONAL
 该模式下，语法和行为期望满足传统的风格
6).NO_ENGINE_SUBSTITUTION
 该模式下，如果需要的存储引擎被禁用或者未编译，可以防止自动替换存储
引擎。

注：在mysql5.6之前，sql_mode为空，在5.6之后，sql_mode 默认为STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

16.开发中列的常用属性
列的常见属性有：
 auto_increment  //设置自动增长
 not null or null  //设置值可以为null 还是不允许为null，默认情况下是允许为null的
 default value  //设置字段的默认值,只有在字段为空的时候才设置默认值，注意null值并不等价于空，即null值也是有值的
 primary key  //设置字段是否为主键
 comment  //设置字段的注释，特别有用
 
主键的注意点：
 可以唯一标识某条记录的字段或者是字段的集合就是主键
 每一个记录必须要有一个主键，主键必须唯一
 主键一般来说不属于任何实体的真实属性，但是可以是真实属性不过不建议使用真实属性
 建议主键不与业务逻辑发生关系，只用于标志记录
 主键不能为null，当一个字段为主键的时候，会自动设置为不能为空
 主键可以是负值或者为0，只要唯一即可。
 主键的查找速度特别快，通过主键来锁定数据
 主键可以保证当前字段数据的唯一性

auto_increment: 对于自动增长通常有两个问题需要解决：
1).清空了一个表的内容后，怎么样使得自动增长从1开始；
方法一：使用truncate 来情况表的内容，此方法会一并初始化auto_increment计数器
方法二: 修改表的auto_increment的值从1开始
 alter table tb_name auto_increment=1;
2).一个表的内容删除了一部分之后，需要自动增长的值按顺序执行
 此时的方法只有一个,修改自动增长的值
 alter table tb_name auto_increment=num;
 
17.外键约束
概念：
 外键约束也是字段属性的一种，通常是用来关联两张表即两个实体，用一个实体去约束另一个实体的行为；
 外键约束的存在是为了业务逻辑数据的完整性，以便更加的符合实际情况，比如当一个班级表中不存在的班级，则不允许在学生表中插入属于该不能存在班
级的学生；
 外键约束通常有两张表，一张表称为主表，被指向的表，一张表是从表，负责指向主表主键的表。注意，外键约束从表中的字段只能指向主表中的主键字段
 外键约束的默认行为：
  主表更新时，从表不做任何限制；
  主表删除时，必须保证从表关联的数据已经删除,否则不允许删除；
  从表更新时，如果关联的外键在主表中不存在，则不允许更新；
  从表删除时，主表不做任何限制
 外键约束允许的级联动作：
  cascade关联操作：如果主表被执行删除或更新，则从表也会执行相应的操作
  set null:设置为null，表示从表外键不指向任何主表记录，设置外键值为null
  restrict:拒绝主表的相关操作，该值为默认的操作，查看表语法时不会显示，但是效果会达到。

创建外键的方式：
1).create table时创建
 create table tb_name( col1_name type_define,col2_name,type_define,...,foreign key (col_name) references master_tb_name (col_name));

2).alter 创建
 alter table tb_name add foreign key (col_name) reference master_tb_name (col_name);
 
 
ok，over！

转载于:https://blog.51cto.com/mingyang/1593070