MySQL数据库

一、SQL语句

SQL语句主要可以划分为3个类别：
1、DDL（Data Definition Languages）语句：数据定义语言。
这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的关键字主要包括：create（增）、drop（删）、alter（改）、show/desc（查）等。
$mysql -uroot -p
链接MySQL服务器，-u后边跟连接的数据库用户 -p表示需要1输入密码

mysql>create database test1;
创建数据库test1

mysql>show databases;查看系统中有哪些数据库
4个系统自动创建的表：
information_schema：主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息等；
cluster：存储了系统的集群信息；
mysql：存储了系统的用户权限信息；
test：系统自动创建的测试数据库，任何用户都可以使用。

mysql>use test1;选择数据库test1

mysql>show tables;查看数据库中创建的所有数据表

mysql>drop database test1;删除数据库

mysql>create table emp(name varchar(10) not null,hiredate date not null,sal decimal(10,2) default null);创建一个名为emp的表，表中包含3个字段，name（姓名）、hiredate（雇用日期）、sal（薪水），字段类型分别是varchar(10）、date、decimal(10,2)，后边的为限制条件

mysql>desc emp;查看表的定义

mysql>show create table emp \G;查看表的详细信息。\G选项使得记录能够按照字段竖着排列，对内容较长的记录容易显示

mysql>drop table emp;删除表

mysql>alter table emp modify name varchar(20);
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]修改字段类型

mysql>alter table emp add column age int(3);
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]增加字段
mysql>alter table emp drop column age;
ALTER TABLE tablename DROP [COLUMN] col_name删除字段

mysql>alter table emp change age age1 int(1);
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition[FIRST|AFTER col_name]修改字段名及类型

前面介绍的的字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项 first|after column_name，这个选项可以用来修改字段在表中的位置，默认 ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默认都不会改变字段的位置。

mysql>alter table emp rename emp1;
ALTER TABLE tablename RENAME [TO] new_tablename 修改表名

2、DML（Data Mainpulation Language）语句：数据操纵语句。用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的关键字包括：insert、delete、updata和select等。

mysql> insert into emp (ename,hiredate,sal,deptno) values(‘zzx1’,’2000-01-01’,’2000’,1);
也可以不用指定字段名称，但是 values 后面的顺序应该和字段的排列顺序一致：
mysql> insert into emp values(‘lisa’,’2003-02-01’,’3000’,2);
对于含可空字段、非空但是含有默认值的字段、自增字段，可以不用在 insert 后的字段列表里面出现，values 后面只写对应字段名称的 value，这些没写的字段可以自动设置为 NULL、默认值、自增的下一个数字，这样在某些情况下可以大大缩短 SQL 语句的复杂性。在 MySQL 中，insert 语句还有一个很好的特性，可以一次性插入多条记录，语法如下：
INSERT INTO tablename (field1, field2,……fieldn)
VALUES
(record1_value1, record1_value2,……record1_valuesn),
(record2_value1, record2_value2,……record2_valuesn),
……
(recordn_value1, recordn_value2,……recordn_valuesn)
;

对于表里的记录值，可以通过 update 命令进行更改，语法如下：
UPDATE tablename SET field1=value1，field2.=value2，……fieldn=valuen [WHERE CONDITION]
例如，将表 emp 中 ename 为“lisa”的薪水（sal）从 3000 更改为 4000：
mysql> update emp set sal=4000 where ename=’lisa’;

在 MySQL 中，update 命令可以同时更新多个表中数据，语法如下：
UPDATE t1,t2…tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]
例如：mysql> update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;

如果记录不再需要，可以用 delete 命令进行删除，语法如下：
DELETE FROM tablename [WHERE CONDITION]
例如，在 emp 中将 ename 为‘dony’的记录全部删除，命令如下：
mysql> delete from emp where ename=’dony’;
在 MySQL 中可以一次删除多个表的数据，语法如下：
DELETE t1,t2…tn FROM t1,t2…tn [WHERE CONDITION]
例如：mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;

• 查询不重复的记录

SELECT * FROM tablename [WHERE CONDITION] 记录全部查出
将表中的记录去掉重复后显示出来，可以用 distinct 关键字来实现：
mysql> select distinct deptno from emp;

• 条件查询

根据限定条件来查询一部分数据，用 where 关键字可以来实现这样的操作：
mysql> select * from emp where deptno=1;
where 后面的条件是一个字段的‘=’比较，除了‘=’外，还可以使用>、<、>=、<=、!=等比较运算符；多个条件之间还可以使用 or、and 等逻辑运算符进行多条件联合查询。

• 排序和限制

我们经常会有这样的需求，取出按照某个字段进行排序后的记录结果集，这就用到了数据库的排序操作，用关键字 ORDER BY 来实现，语法如下：
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC] ，field2 [DESC|ASC]，……fieldn [DESC|ASC]]
其中，DESC 和 ASC 是排序顺序关键字，DESC 表示按照字段进行降序排列，ASC 则表示升序排列，如果不写此关键字默认是升序排列。ORDER BY 后面可以跟多个不同的排序字段，并且每个排序字段可以有不同的排序顺序。例如，把 emp 表中的记录按照工资高低进行显示：
mysql> select * from emp order by sal;
mysql> select * from emp order by deptno,sal desc;

• 聚合

对于排序后的记录，如果希望只显示一部分，而不是全来实现，LIMIT 的语法如下：
SELECT ……[LIMIT offset_start,row_count]
其中 offset_start 表示记录的起始偏移量，row_count 表示行数，在默认情况下，起始偏移量为 0，只需要写记录行数，看下面例子：
例如，显示 emp 表中按照 sal 排序后的前 3 条记录：
mysql> select * from emp order by sal limit 3;
如果要显示 emp 表中按照 sal 排序后从第二条记录开始，显示 3 条记录：
mysql> select * from emp order by sal limit 1,3;

• 聚合

很多情况下，我们需要进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人46数，这个时就要用到 SQL 的聚合操作。聚合操作的语法如下：
SELECT [field1,field2,……fieldn] fun_name
FROM tablename
[WHERE where_contition]
[GROUP BY field1,field2,……fieldn
[WITH ROLLUP]]
[HAVING where_contition]
对其参数进行以下说明。
fun_name 表示要做的聚合操作，也就是聚合函数，常用的有 sum（求和）、count(*)（记录数）、max（最大值）、min（最小值）。
GROUP BY 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在 group by 后面。
WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总。
HAVING 关键字表示对分类后的结果再进行条件的过滤。
注意：having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤，而 where 是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用 where 先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用 having 进行再过滤。

• 表连接

当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。从大类上分，表连接分为内连接和外连接，它们之间的最主要区别是內连接仅选出两张表中
互相匹配的记录，而外连接会选出其他不匹配的记录。
外连接有分为左连接和右连接，具体定义如下。
左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。

• 子查询

某些情况下，当我们查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。用于子查询的关键字主要包括 in、not in、=、!=、exists、not exists 等。
例如，从 emp 表中查询出所有部门在 dept 表中的所有记录：
mysql> select * from emp where deptno in(select deptno from dept);

• 记录联合

我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，这个时候，就需要用 union 和 union all 关键字来实现这样的功能，具体语法如下：
SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
……
UNION|UNION ALL
SELECT * FROM tn;
UNION 和 UNION ALL 的主要区别是 UNION ALL 是把结果集直接合并在一起，而 UNION 是将
UNION ALL 后的结果进行一次 DISTINCT，去除重复记录后的结果。
来看下面例子，将 emp 和 dept 表中的部门编号的集合显示出来：
mysql> select deptno from emp
-> union all
-> select deptno from dept

3、DCL（Data Control Language）语句：数据控制语言。用于控制不同的数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要包括grant、revoke等。

二、存储引擎

插件式存储引擎是 MySQL 数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL 默认支持多种存储引擎，以适用于不同领域的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储，用户甚至可以按照自己的需要定制和使用自己的存储引擎，以实现最大程度的可定制性。MySQL 5.0 支持的存储引擎包括 MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等。
默认情况下，创建新表不指定表的存储引擎，则新表是默认的存储引擎，则可以在参数文件中设置 default-table-type
查看当前的默认存储引擎，可以使用以下命令：
mysql> show variables like ‘table_type’;
可以通过下面两种方法查询当前数据库支持的存储引擎，第一种方法为：
mysql> SHOW ENGINES \G
或者采用第二种方法：
mysql> SHOW VARIABLES LIKE ‘have%’;

1、 MyISAM存储引擎
MyISAM 是 MySQL 的默认存储引擎。MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。
每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：
 .frm（存储表定义）；
 .MYD（MYData，存储数据信息）；
 .MYI （MYIndex，存储索引信息）
MyISAM 的表又支持 3 种不同的存储格式，分别是：
 静态（固定长度）表；
静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表的数据在存储的时候会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。但是也有些需要特别注意的问题，如果需要保存的内容后面本来就带有空格，那么在返回结果的时候也会被去掉。
 动态表；
动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。
 压缩表
压缩表由 myisampack 工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。
2、 InnoDB存储引擎
数据和索引结合在一起，支持事务，主要是面向在线事务处理方面的应用，特点是行锁设计，并支持外键。Innodb采用聚集索引的方式。如果没有主键和唯一键，加一个6字节长度的列auto_increment，作为主键。InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
InnoDB 存储表和索引有以下两种方式。
使用共享表空间存储，这种方式创建的表的表结构保存在.frm 文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。
使用多表空间存储，这种方式创建的表的表结构仍然保存在.frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。如果是个分区表，则每个分区对应单独的.ibd文件，文件名是“表名+分区名”，可以在创建分区的时候指定每个分区的数据文件的位置，以此来将表的 IO 均匀分布在多个磁盘上。

3、 MEMORY存储引擎
MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。varchar()默认是按照char()存储的，浪费内存。不支持text和BLOB类型。如果原来数据中有text和BLOB类型，数据库会把这些数字转换到磁盘上。
MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对 MEMORY 存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。
4、 ARCHIVE存储引擎
只支持INSERT和SELECT操作，使用压缩算法将数据进行压缩后存储，压缩比例一般是1:10，主要提供插入和压缩功能，不能修改数据。

常用存储引擎的适用环境
 MyISAM：默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
 InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。
 MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境
下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。
 MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善 MERGE 表的访问效率。这对于诸如数据仓储等 VLDB
环境十分适合。