mysql的系统结构

原创

weijinhai 2012-09-12 23:42:30 ©著作权

文章标签 mysql 系统 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者weijinhai的原创作品，请联系作者获取转载授权，否则将追究法律责任

mysql系统架构：

    --------------
    |SQL Layer|
    --------------
      |   |    |
    -----------------------------
    |storage    engine   layer |
    -----------------------------

逻辑模块组成
总的来说，mysql可以看成是两层架构，第一层我们通常叫做sql layer,在mysql数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断,query解析，执行计划优化，query cache的处理等；

第二层就是存储引擎层，通常叫做storage engine layer,也就是底层数据存取操作实现部分，由多种存储引擎共同组成。

SQL layer包含了多个子模块：
初始化模块
核心API
网络交互模块
client&server交互协议模块
用户模块
访问控制模块
连接管理，连接线程和线程管理
query解析和转发模块
query cache模块
query 优化器模块
表变更管理模块
表维护模块
系统状态管理模块
表管理器
日志记录模块
复制模块
存储引擎接口模块

--mysql存储引擎简介
在mysql5.1之前(不包括5.1)的版本中，存储引擎必须在myslql安装的时候和mysql一起被编译并同时被安装。也就是说，5.1版之前虽然存储引擎层和sql层的耦合已经非常少了，基本上完全是通过接口来实现交互的，但是这两层之间仍然没办法分离，即使在安装的时候也是一样。
但从mysql5.1开始，mysql AB公司对其结构体系做了较大的改造，并引入了一个新的概念：插件式存储引擎体系结构。存储引擎的加载和移出更为灵活方，也使自行开发存储引擎更为方便简单。在这一点上，目前还没有哪个数据库管理系统能够做到。

1。MyISAM --重点掌握的

默认的存储引擎，也是目前使用非常广泛的存储引擎之一，是ISAM的升级版本。
用于读多写少的环境

2。innoDB存储引擎简介 --学了oracle后比较好理解
除了MyISAM外，使用最为广泛的存储引擎，由第三方公司开发，和mysql遵守相同的开源许可协议。
支持事务安全
数据多版本读取
锁定机制的改进
实现外键
它有表空间的概念，但与oracle表空间相比又有较大的不同，首先，innodb的表空间分为两种形式，一种是共享表空间，也就是所有数据都被存放在同一个表空间（一个或多个数据文件）中，通过innodb_data_file_path来指定，增加数据文件须要停机重启。另外一种是独享表空间，也就是每个表的数据和索引都存放在一个单独的.ibd文件中。

3。NDB cluster存储引擎简介 --ULA课程会搭建
主要用于mysql cluster分布式集群环境。简单地说，mysql cluster就是在无共享存储设备的情况下实现的一种内存数据库cluster环境，主要是通过NDB cluster (简称NDB）存储引擎来实现的。

------
其他存储引擎介绍

4.merge
可以简单地理解其功能就是结构相同的MyISAM表通过一些特殊的包装对外提供一个单一的访问入口，从而减小应用复杂度的目的。
要创建merge表，不仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致
merge表本身并不存储数据，仅仅是多个基表提供一个统一的存储入口。所以在创建merge表的时候，mysql只会生成两个较小的文件，一个是.frm的结构定义文件，还有一个.MRG文件，用于存放参与MERGE表的名称（包括所属数据库schema)

5,Memory存储引擎
它是一个将数据存储在内存中的存储引擎。
memory存储引擎不会将任何数据存放到磁盘上，在磁盘上仅仅存放了一个表结构相关信息的.frm文件。所以一旦mysql崩溃或主机崩溃之后,memory的表就只剩下一个结构了。
它实现的是页级锁定XAMP
此引擎所有数据都存放在内存中，那么它消耗的内存量可想而知。

6.BDB存储引擎
BDB存储引擎全称为berkeleyDB存储引擎，和innoDB一样，也是第三方的，同样支持事务安全。
在锁定机制方面，和memory存储引擎一样，实现页级锁定。

7。federated存储引擎
它所实现的功能和oracle的DBLINK基本相似，主要是用来提供对远程mysql服务器上面的数据的访问接口，如果我们使用源码编译来安装mysql,那么必須手工指定启用federated存储引擎，因为mysql默认是不启用该存储引擎的。

8。archive存储引擎
主要用于通过较小的存储空间来存放过期的很少访问的历史数据。archive表不支持索引，包含一个.frm的结构定义文件，一个.ARZ的数据压缩文件，还有一个.ARM的元数据
信息文件。由于其所存放的数据的特殊性，archive表不支持删除，修尽管操作，仅支持插入和查询操作。
锁定机制为行级锁定。

9。blackhole存储引擎
就是一个‘黑洞’，就像/dev/null设备一样，不管写入任何信息，都是有去无回。
比如数据迁移过程，数据要经过一个中转的mysql服务器做一些相关的转换操作，然后再通过复制移植到新的服务器上面。如果中转服务器没有足够的空间，可以用blackhole引擎，它不记录任何数据（这样就不会占用空间了），但是会在binary log中记录下所有的query，把这些记录复制应用到新的服务器上。

10。CSV存储引擎
操作的就是一个标准的CSV文件，它不支持索引，主要用途就是有时候可能要通过数据库导出一份报表文件，而CSV文件是很多软件都支持的一种标准格式，所以可以先在数据库中建立一张CVS表，然后将生成的报表信息插入该表，就能得到一份CSV报表文件了

--下面的是摘自5.1官档
下述存储引擎是最常用的：

· MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。

· InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。

· BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

· Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。

· Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。

· Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。

· Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。

· Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。

· Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。

-----------------------------------------------------------

说明memory引擎的小实例 tmpfs

[root@li ~]# mount |grep tmpfs
tmpfs on /dev/shm type tmpfs (rw)

dd if=/dev/zero of=/mnt/1.img bs=1M count=1000
1048576000 bytes (1.0 GB) copied, 3.88884 seconds, 270 MB/s

mount -t tmpfs tmpfs /mnt

dd if=/dev/zero of=/mnt/1.img bs=1M count=1000
1056743424 bytes (1.1 GB) copied, 1.22658 seconds, 862 MB/s --速度快很多，因为是在内存里写数据

umount /mnt

mount -t tmpfs tmpfs /mnt/

ls /mnt --umount 后再次挂载文件内容没有了

[root@dns ~]# cat /proc/meminfo --使用了tmpfs一G空间后的内存信息
MemTotal:      2066044 kB
MemFree:         77320 kB
Buffers:         10532 kB
Cached:        1671112 kB

[root@dns ~]# cat /proc/meminfo --umount之后再查看的内存信息
MemTotal:      2066044 kB
MemFree:       1101716 kB
Buffers:         10544 kB
Cached:         647112 kB

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

存储引擎相关的操作：

mysql> show engines;
mysql> show variable like "have%";

mysql> show engines;
里面的default表示默认引擎
YES表示支持的引擎，也就是可以在数据库里创建这种引擎的表
disable表示支持，但禁用的引擎
no表示不支持

mysql> use information_schema
mysql> select TABLE_NAME,ENGINE from tables; --查询information_schema库里表及其对应的存储引擎，可以看到都是myisam，也有少数表为memory

mysql> show create table CHARACTER_SETS; --查看这个表的创建语句，包含了它属于哪种存储引擎

--如果把disable改为yes，把yes改为disable
如：把ndbcluster改为yes，innodb改为disable，并把ndbcluster改为默认引擎
vim /usr/local/mysql/etc/my.cnf  --修改配置文件在mysqld参数组下加上下面两句
ndbcluster  --启用ndbcluster
skip-innodb  --禁用innodb
default-storage-engine=ndbcluster --默认引擎改为ndbcluster

/etc/init.d/mysql5 restart --重启后查看验证

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
--插入式存储引擎相关的概念，下面摘自5.1官档

5.1－5.5版本都为插入式存储引擎

插入存储引擎
能够使用存储引擎之前，必须使用INSTALL PLUGIN语句将存储引擎plugin（插件）装载到mysql。例如，要想加载example引擎，首先应加载ha_example.so模块：

INSTALL PLUGIN ha_example SONAME 'ha_example.so';

文件.so必须位于MySQL服务器库目录下（典型情况下是installdir/lib）。

拔出存储引擎

要想拔出存储引擎，可使用UNINSTALL PLUGIN语句：

UNINSTALL PLUGIN ha_example;

如果拔出了正被已有表使用的存储引擎，这些表将成为不可访问的。拔出存储引擎之前，请确保没有任何表使用该存储引擎。

插件式存储器的安全含义

为了安装插件式存储引擎，plugin文件必须位于恰当的MySQL库目录下，而且发出INSTALL PLUGIN语句的用户必须具有SUPER权限。

--------------------------------------------------------------------------------

--mysql物理文件组成

－－－－－－－－－－－－－－－－－－－－－－－－－－
日志文件

1。错误日志 error log

记录了运行过程中所有较为严重的警告和错误信息，以及mysql server每次启动和关闭的详细信息。
在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出（stderr),如果要开启系统记录错误日志的功能，须要在启动时开启--log-error选项
error log默认存放在数据目录下，以hostname.err命名。但是可以使用命令:--log-error[=file_name]修改其存放目录和文件名

/usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/etc/my.cnf &
--启动时查看会报错误日志记录到哪里，发现和my.cnf里写的不一致。登录进行使用
mysql> show variables like '%log%error';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| log_error | /data/li.cluster.com.err | --发现在数据目录下
+---------------+--------------------------+
--查看my.cnf发现是log-error写成了log-err，修改后再启动就OK

--在错误日志里还发现了下面的一段警告信息，表示log参数已经不用了
110728 10:26:55 [Warning] The syntax '--log' is deprecated and will be removed in MySQL 7.0. Please use '--general_log'/'--general_log_file' instead.

--解决：在my.cnf里把log换成
general_log
general_log_file=/var/log/mysqld/mysql5.log
--重启OK

2。二进制日志： binary log& binary log index
在通过"--log-bin[=file_name]"打开记录的功能之后，mysql会将所有修改数据库数据的query以二进制形式记录到日志文件中，还包括每一条query所执行的时间，所消耗的资源，以及相关的事务信息，所以binlog是事务安全的。

--log-bin[=file_name]如果没有指定file_name，则会在数据目录下记录为my sql-bin.*****（*为0～9之间的一个数字，代表日志的序号）

--在my.cnf里加上下面一句
log-bin=mysql5 --后面这个名字只是日志文件名的头，随意定义

3。更新日志: update log
更新日志是mysql在较老的版本上使用的，其功能和binlog基本类似，只不过不是以二进制格式来记录，而是以简单的文本格式记录内容。自从mysql增加了binlog功能之后，就很少使用更新日志了。从版本5.0开始，mysql已经不再支持更新日志。

4。查询日志：query log
查询日志记录mysql中所有的query，可通过"--log[=file_name]来打开此日志。
由于记录了所有的query，包括所有的select,体积比较大，开启后对性能也有较大的影响，所以请大家慎用此功能。一般只在跟踪一些特殊的query性能问题时才会短暂打开此功能。
默认的查询日志文件名为hostname.log

5。慢查询日志：slow query log

此日志中记录的是执行时间较长的query,也就是常说的slow query,通过设--log-slow-queries[=file_name]来打开此功能并设置记录位置和文件名，默认文件名为hostname-slow.log,默认目录也是数据目录。
慢查询日志采用的是简单的文本格式，可以通过各种文件编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。
mysql还提供了专门用来分析慢查询日志的工具程序mysqlslowdump,以帮助数据库管理人员解决可能存在的性能问题。

6。innoDB引擎的在线redo日志：innoDB redo log
innoDB是一个事务安全的存储引擎，其事务安全性主要是通过在线redo日志和记录在表空间的undo信息来保证的。redo日志中记录了innoDB所做的所有物理变更和事务信息，通过redo日志和undo信息，innoDB保证了在任何情况下的事务安全性。innoDB的redo日志同样默认存放在数据目录下，可以通过innodb_log_group_home_dir来更改设置日志的存放位置，通过innodb_log_files_in_group设置日志的数量。

－－－－－－－－－－－－－－－－－－－－－－－－
数据文件
不同的存储引擎有各自不同的数据文件，存放位置也有区别。多数存储引擎的数据文件都存放在和myisam数据文件位置相同的目录下，但是每个数据文件的扩展名却各不一样。如myisam用“.MYD"为扩展名，innoDB用".ibd",archive用".arc",CSV用'.CSV',等等。

1。.frm 文件
不论是什么存储引擎，每一个表都会有一个以表命名的.frm文件，与表相关的元数据（meta)信息都存放在此文件中，包括表结构的定义信息等。
所有的.frm文件都存放在所属数据库的文件夹下面。

2。.MYD文件
myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同样存放在所属数据库的文件夾下，和.frm文件在一起

create table a (id int);
insert into a values(1);
insert into a select * from a;
......
插入几百W行记录，就会发现.MYD很大了

create table b (id int) engine=innodb;
insert into b values (2);
insert into b select * from b;
......
插入几百W行记录后，就会发现/data/ibdata1由10M变得很大了

3。.MYI文件
也是myisam存储引擎专用，存放myisam表的索引相关信息。对于myisam存储引擎来说，可以被缓存(cache)的内容主要就是来源于.MYI文件中。每一个myisam表对应一个.MYI文件，其存放的位置和.frm及.MYD一样

4。.ibd文件和ibdata文件
存放innoDB的数据文件（包括索引）。innoDB存储引擎有两种表空间方式：独享表空间和共享表空间。
独享表空间存储方式使用.ibd文件来存放数据，且每个表一个.ibd文件，文件存放在和myisam数据相同的位置。
共享存储表空间使用.ibdata文件，所有表共同使用一个（或多个，自行配置）ibdata文件。

------------------------------------------

复制(replication)相关文件

1。master.info
存在于slave端的数据目录下，里面存放了该slave的master端的相关信息，包括master的主机地址，连接用户，连接密码，连接端口，当前日志位置，已经读取到的日志位置等信息。

2。relay log 和relay log index
mysql-relay-bin.xxxxxn文件用于存放slave端的i/O线程从mater端所读取的binary log信息，然后由slave端的sql线程从该relay log中读取并解析相应的日志信息，转化成master所执行的query语句，接着在slave端应用。
mysql-relay-bin.index文件的功能类似于mysql-bin.index,同样是记录日志存放位置的绝对路径，只不过它所记录的不是binary log,而是relay log.

3。relay-log.info文件
类似于master.info,relay-log.info文件存放通过slave的i/O线程写入本地的relay log相关信息，以便slave端的SQL线程及一此管理操作随时能够获取当前复制的相关信息

---------------------------------------------------

其他文件

1.系统配置文件
my.cnf,Unix/Linux默认存放在/etc目录下，windows环境一般存放在c:/windows目录下
此文件中可以包含多种参数选项组,每一种参数组都通过中括号给了固定的组名，如[mysqld]组中包括了mysqld服务启动时的初始化参数，［client]组中包含着客户端工具程序可以读取的参数，此外还有其他针对各个客户端软件的特定参数组，如mysql程序使用的[mysqld],mysqlchk使用的[mysqlchk]，等
如果读者编写了某个客户端程序，也可以自己设定一个参数组名，将相关参数配置在里面，然后调用mysql客户端API程序中的参数读取API相关参数

2。pid文件
pid文件是mysqld应用程序在Unix/Linux环境下的一个进程文件，和许多其他Unix/Linux服务端程序一样，它存放着自己的进程id

3。socket文件
socket文件也是在Unix/Linux环境下才有的，用户在Unix/Linux环境下客户端连接可以不通过TCP/IP网络而直接使用Unix Socket来连接mysql.

---------------------------------------------------------------

常用mysql命令行工具介绍：

ls /usr/local/mysql/bin/ --都在此目录下

1，mysql 一个命令行客户端程序，可以批处理方试执行sql语句，还可以用于恢复

[root@li mysql-5.0.33]# /usr/local/mysql/bin/mysql < /mysql.sql
Database
information_schema
mysql
source
test
[root@li mysql-5.0.33]# /usr/local/mysql/bin/mysql <<EOF
> show databases;
> Database
information_schema
mysql
source
test

2，mysqld 服务启动时的程序，
mysqld_safe 在mysqld上增加了一些安全特性，它可以读［mysqld]参数组和[mysqld_safe]参数组，现在都是用这种方式来启动的

/usr/local/mysql/bin/mysqld_safe & --可以用这种方式来启动，写到/etc/rc.local实现开机自启动

3，mysql_install_db 初始化数据库工具

4，mysqladmin 管理工具

可以用于数据库的一些状态监控
[root@li mysql-5.0.33]# /usr/local/mysql/bin/mysqladmin ping
mysqld is alive

[root@li mysql-5.0.33]# /usr/local/mysql/bin/mysqladmin status
Uptime: 323 Threads: 1 Questions: 2 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables: 6 Queries per second avg: 0.006

5，mysqlcheck
myisamchk 两个工具都是用来检查，维护，优化，分析myisam表的工具
区别：mysql运行时使用mysqlcheck,没有运行时使用myisamchk

6,mysqldump 导出数据，备份数据库
导出数据还有一种方法： select * from tab into outfile "/path/filename"

7,mysqlimport 导入数据
load data local infile 也可以导入数据

8,mysqlhotcopy 热备份工具

9，mysqlbinlog 二进制日志读取工具

10，mysqlbug 收集bug信息，传给官方

11，mysqld_multi mysql多启动

12， mysql_fix_privilege_tables 修复权限表，一般升级数据库后，要用些命令刷新

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

mysql 数据库的帮助 --oracle就没有，需要去查官方文档

mysql > help;

mysql > help create table;

mysql> ? contents --分了很多类

   Account Management
   Administration
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Language Structure
   Storage Engines
   Stored Routines
   Table Maintenance
   Transactions
   Triggers

mysql> ? Account Management --一级一级的向内部查

mysql> ? CREATE USER

-------------------------------------------

sql 语句（结构化查询语言,structure query language) ，在不同数据库都是通用的，但会有少许不同

最早是由ANSI定义的标准 sql-86

DQL 数据查询语言   select
DDL 数据定义语言   create drop alter truncate rename
DML 数据操纵语言   insert update delete
DCL 数据控制语言    grant revoke

DDL

mysql> create database source; --创建一个叫source的库，也就是数据目录下创建了一个source的目录，owner为mysql.mysql，并在其内有一个db.opt的默认字符集文件
[root@li ~]# ll /data/source/
-rw-rw---- 1 mysql mysql 59 07-31 15:51 db.opt

手动创建一个库
[root@li ~]# mkdir /data/uplooking

mysql> use uplooking;

mysql> create table upl (id int); --创建一个表，发现有错误
ERROR 1005 (HY000): Can't create table 'upl' (errno: 13)

[root@li ~]# perror 13 --可以在shell下用perror打印出13的错误。这个错误号的范围为1－152
OS error code 13: Permission denied

[root@li ~]# chown mysql.mysql /data/uplooking/ --修改其owner

[root@li ~]# cp -a /data/source/db.opt /data/uplooking/ --拷贝一个字符集文件

mysql> create table upl (id int); --再次创建就可以创建成功了
Query OK, 0 rows affected (0.00 sec)

[root@li ~]# ll /data/uplooking/
总计 20
-rw-r----- 1 mysql mysql 59 08-01 11:21 db.opt
-rw-rw---- 1 mysql mysql 8556 08-01 11:20 upl.frm
-rw-rw---- 1 mysql mysql 0 08-01 11:20 upl.MYD
-rw-rw---- 1 mysql mysql 1024 08-01 11:20 upl.MYI

mysql> create database ccc character set=latin1;
--创建库的时候，指定非默认的字符集

mysql> alter database ccc character set=gbk;
--修改一个库的字符集

# vim /data/ccc/db.opt
--直接vim打开修改字符集，也可以

-----------------

mysql> show create database source; --可以用show查看创建库或者表的相关参数

mysql> create database if not exists source; --如果不存在则创建，如果存在，则有一个警告
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop database ccc;
--删除ccc库

mysql> create table emp (ename varchar(20),sex char(1),hiredate date,sal decimal(10,2),deptno int(1)); --创建表

mysql> create table emp
    -> (ename varchar(20),
    -> sex char(1),
    -> hiredate date,
    -> sal decimal(10,2),
    -> deptno int(1)); --可以分开写，比较容易阅读，但注意这只是一行

------------------------------------
关于字符集的讨论
--比较创建库的字符集文件
[root@li source]# cat /data/source/db.opt --db.opt代表source这个库的默认字符集文件，是一个ASCII text文本文件，可以cat看
default-character-set=gbk --定义默认字符集为gbk
default-collation=gbk_chinese_ci --这个为校验规则；字符集是用来定义mysql存储字符串的方式，校验规则是定义了比较字符串的方式

mysql> create database ccc default character set latin1;

[root@li source]# cat /data/ccc/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci

-字符集概述

20世纪60年代初，美国标准化组织ANSI发布了第一个计算机字符集－－ASCII（American standard code for information interchange),采用7位编码，定义了包括大小写字母，阿拉伯数字和标点符号，以及33个控制符号等。它比较简单，包括的符号也比较少，但直到今天它依然是计算机世界里奠基性的标准，其后制定的各种字符集都兼容它。
自ASCII之后，各国政府，标准化组织，各大计算机公司先后发明了几百种字符集，造成了一个软件要在使用不同文字的国家或地区发面，就必须要进行本地化开发，基于这个原因，统一字符编码成了迫切需要。
国标化标准组织ISO（international organization for standardization)于1984年发起制定新的国际字符集标准，以容纳全世界各种语言文字和符号。这个标准叫做(universal multiple-octet coded character set),简称UCS，标准编号定为ISO-10646。它采用4字节（32bit)编码，因此简称为UCS－4。
后来xerox联合apple,ibm,dec,sun,microsoft,novell等公司成立unicode协会，与ISO达成协议，改进了代码空间的划分，只需要2字节来编码就足够了，相对于4字节的编码方法，在节省内存，带宽和处理时间上都具有优势。慢慢发展出了的有utf-32,utf-16，utf-8（utf-8的汉字编码要占用3个字节）等。

中国也制定了一些编码标准：
GB2312－80：于1980年发布，收录了6763个常用汉字和682个非汉字图形符号
GB13000：于1993年发布，收录了除GB2312－80支持的之外，还收录了第1，3，5，7辅助集的全部汉字共27484个，以及一些偏旁部首等。但它推出后，几乎没有得到业界的支持，也就成了一个形式上的标准。
GBK:于1995年发布，它并不是一个强制性的国家标准，只是一个行业指导规范，并没有强制力。由于得到了windows 95的支持而大为流行
GB18030：于2000年发布，采用2字节或4字节编码，其2字节编码部分与GBK保持一致。它是GBK的超集，是为了解决GBK强制力不够的问题。

常用字符集比较

字符集是否定长编码方式其它说明

ACSII  是  单字节7位最早的奠基性字符集
latin1  是  单字节8位西欧字符集，经常被一些程序员用来转码
GB2312－80 是  双字节  早期标准，不推荐再使用
GBK  是  双字节  非国际标准，但不少系统都支持
GB 18030 否  2字节或4字节开始有一些支持，但数据库支持的还少见
UTF－32  是  4字节  目前很少采用
UTF－16  否  2字节或4字节 java和windows XP/NT等内部使用UTF－16
UTF－8  否  1~4字节编码互联网和unix/linux广泛支持的unicode字符集；mysql也使用utf-8

--选择合适的字符集
对于数据库来说，字符集是很重要的参数，因为数据库存储的数据大部分都是各种文字，对数据库的存储，处理性能，以及日后系统的移植，推广都会有影响。

mysql 5.0目前支持几十种字符集，UTF－8是mysql5.0支持的唯一unicode字符集
--选择字符集的原则
1，如果应用要处理各种各样的文字，或者要发布到使用不同语言的国家或地区，就应该选择unicode字符集。对mysql来说，目前就是UTF－8
2，如果应用涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性；假如已有数据是GBK，选择GB2312－80为数据库字符集，就很可能出现某些文字无法正确导入的问题
3，如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，如GBK。相对于UTF－8来说，GBK比较小，只占2个字节，相对于UTF－8汉字编码要3个字节来说，可以减少I／O，数据库cache以及网络传输时间，提高性能。
如果主要处理英文字符，仅有少量汉字数据，则选择UTF－8更好，因为GBK的西文字符编码都为2个字节，会造成很大不必要的开销。
4，如果数据库需要做大量的字符运算，如比较，排序等，选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
5，如果所有客户端程序都支持相同的字符集，应该优先选择此字符集作为数据库字符集，这样可以避免因字符集转换带来的性能开销和数据损失。

mysql可以支持多种字符集，在同一台服务器，同一个数据库甚至同一个表的不同字段都可以指定使用不同的字符集，相比oracle等其他数据库在同一个数据库上只能使用相同的字符集，mysql明显存在更大的灵活性。

mysql> show character set; --查看所有可用的字符集

information_schema库下的
CHARACTER_SETS，COLLATIONS，COLLATION_CHARACTER_SET_APPLICABILITY这三张表也可以查看相关信息

--校对规则的命名约定：字符集开头，包括一个语言名，并且以_ci（大小写不敏感），_cs（大小写敏感），或_bin（二元，就是比较是基于字符编码的值而与language无关）结束。

========================================================

--------------------------------------------------------
数据类型

数值类型
tinyint    1 -128-127    0-255 (unsigned)
smallint   2
mediumint 3
int     4
bigint     8

小数型
float 4
double 8

定点数
decimal (M,D)   M=整数位＋小数位 D＝小数位

时间类型
date  年月日
year  年
time  时分秒
datetime 年月日时分秒
timestamp 用于经常插入或者更新系统时间

字符类型
char[M] 定长，M代表长度
varchar[M] 变长，M代表长度限制

mysql> alter table emp add column age int(2); --增加一个列，默认增加到最后

mysql> alter table emp add column manager varchar(30) after hiredate; --在hiredate这列后面增加一列，使用after关键字

mysql> alter table emp add column manageraaaa varchar(30) first; --把一列加到最前面，使用first关键字

--first和after是mysql自己的SQL语法扩展，在oracle数据库里就没有这种功能，它需要使用select按需求的顺序给选取出来，再导入到新表，删除老表

--比如说加了一个age列，要把age列放到最前面，则需要select时把age列放在第一位，再创建成emp1表
mysql> create table emp1 as select age,ename,sex,hiredate,sal,deptno from emp;

mysql> drop table emp; --删除emp表
mysql> rename table emp1 to emp; --再把emp1表重命名成emp

mysql> alter table emp modify manager varchar(40); --使用modify修改一列的数据类型

mysql> alter table emp change manager manager varchar(30); --使用change修改一列的数据类型

mysql> alter table emp change manager mana varchar(30); --修改列名要使用change去修改

--modify和change的区别，都可以修改数据类型，但change要写原列名;只有change可以修改列名，modify不可以

mysql> alter table emp drop manageraaaa; --删除某一列

mysql> truncate emp; --截断表，清空了表内的所有数据，但是表的结构还在

---------------------------------------------------------

DML insert delete update

mysql> insert into emp values ('zhangsan','m','2007-01-06','10000',1);
Query OK, 1 row affected (0.01 sec)
--插入数据，注意在mysql里数字可以不用加引号，别的都加引号

mysql> insert into emp values (
    -> 'lisi','m','2008-03-09','8000','2'),
    -> ('wangwu','f','2009-04-10','7000','2'),
    -> ('maliu','f','2009-05-03','7000','2'),
    -> ('tianqi','m','2008-06-13','5000','3'),
    -> ('furongjiejie','m','2008-10-09','4500','3'),
    -> ('fengjie','m','2009-11-23','4200','3'),
    -> ('小二','f','2010-03-15','3000','4'),
    -> ('小三','f','2010-05-03','3000','4');
Query OK, 8 rows affected, 0 warning (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
--一次插入多条记录

mysql> select * from emp;
+--------------+------+------------+----------+--------+
| ename        | sex | hiredate   | sal      | deptno |
+--------------+------+------------+----------+--------+
| zhangsan     | m    | 2007-01-06 | 10000.00 |      1 |
| lisi         | m    | 2008-03-09 | 8000.00 |      2 |
| wangwu       | f    | 2009-04-10 | 7000.00 |      2 |
| maliu        | f    | 2009-05-03 | 7000.00 |      2 |
| tianqi       | m    | 2008-06-13 | 5000.00 |      3 |
| furongjiejie | m    | 2008-10-09 | 4500.00 |      3 |
| fengjie      | m    | 2009-11-23 | 4200.00 |      3 |
| 小二       | f    | 2010-03-15 | 3000.00 |      4 |
| 小三       | f    | 2010-05-03 | 3000.00 |      4 |
+--------------+------+------------+----------+--------

把dept表也插入记录，方便下面的实验
mysql> insert into dept values
    -> (1,'manager','beijing'),
    -> (2,'it','shenzhen'),
    -> (3,'sale','shanghai'),
    -> (4,'services','guangzhou');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

update
把小二性别改成m
mysql> update emp set sex='m' where ename='小二';
wangwu的工资加500
mysql> update emp set sal=sal+500 where ename='wangwu';

tianqi由3号部门换成2号部门，同时工资加1000
mysql> update emp set deptno=2, sal=sal+1000 where ename='tianqi';

搞IT的工资涨10%
mysql> update emp set sal=sal*1.1 where deptno=2;

delete
fengjie离职了，需要删除这条记录
mysql> delete from emp where ename='fengjie'; --删除一条记录

-------------------------------------------------------------

--------------------------------------------------------------

DQL

select

mysql> select * from emp; --* 代表查看所有列

mysql> select ename,sal from emp; -- 只查看其中几列

mysql> select distinct deptno from emp; --distinct 去除重复行

查询所有男员工的姓名和工资
mysql> select ename,sal from emp where sex='m';

查询所有工资大于5000的员工的所有信息
mysql> select * from emp where sal>5000;

查询所有工资在4000到8000之间的员工的所有信息
mysql> select * from emp where sal>=4000 and sal<=8000;
mysql> select * from emp where sal between 4000 and 8000;

查询入职时间在2008那年的员工的姓名和工资
mysql> select ename,sal from emp where hiredate>'2007-12-31' and hiredate <'2009-01-01';
--但上面这种在oracle里不行，因为时间类型和字符串类型不能比较

查询07年入职，并且工资大于8000的员工姓名
mysql> select ename from emp where hiredate between '2007-01-01' and '2007-12-31' and sal>8000;

排序操作
mysql> select * from emp order by sal; --以工资排序，默认升序排序
mysql> select * from emp order by sal asc; --加不加asc都是升序

mysql> select * from emp order by sal desc; --desc表示降序排序

找出工资最低的三个人的信息
mysql> select * from emp order by sal asc limit 3; --limit是mysql数据库的sql扩展，oracle里要实现这种功能需要使用row number的伪列
oracle> select * from emp order by sal asc where rownum<4;

找出工资最高的三个人的信息
mysql> select * from emp order by sal desc limit 3;

找出工资从高到低第三到第五的人的信息
mysql> select * from emp order by sal limit 2,3; --前面的2表示前两行不显示，3表示除前两行不显示外，往后显示3行

oracle里的做法是先找出1－5的集合，再找出1的集合，再互相求补集

------------------------------------------------

聚合和分组操作：

mysql> select count(*) from emp; --统计记录条数

mysql> select count(distinct deptno) from emp;
+------------------------+
| count(distinct deptno) |
+------------------------+
| 4 |
+------------------------+
1 row in set (0.00 sec)

mysql> select count(distinct deptno) deptcount from emp; --别名
+-----------+
| deptcount |
+-----------+
| 4 |
+-----------+

统计每个部门的人数
mysql> select deptno,count(*) from emp group by deptno;

mysql> select ename,deptno,count(*) from emp group by deptno;
--这种在mysql里可以查，但无意义，在oracle里属于错误语法。因为前面select的列名除了count(),max(),min(),avg()等外，别的列名都必须在group by里。

统计男，女员工各有多少人
mysql> select sex,count(*) from emp group by sex;

mysql> select sex,count(*) from emp group by sex with rollup;
--加上with rollup可以汇总

统针每个部门里男女员工各有多少个
mysql> select deptno,sex,count(*) from emp group by deptno,sex;

查看部门人数大于3的部门号
mysql> select deptno,count(*) from emp group by deptno having count(*)>3;
--在分组里做限定，不能用where，用having

统计工资总额，最大工资，最小工资，平均工资
mysql> select sum(sal),max(sal),min(sal),avg(sal) from emp;

查出员工姓名和其对应的工资，部门名，部门所在地，并显示

mysql> select ename,sal,deptname,location from emp,dept;
--错误写法，产生了笛卡尔乘积

mysql> select ename,sal,deptname,location from emp,dept where emp.deptno=dept.deptno;
--正确写法，需要做一个表的链接,这是等值链接

子查询：
查出比李四工资高的人的姓名和工资

mysql> select ename,sal from emp where sal >(select sal from emp where ename='lisi');

----------------------------------------------------------------

DCL

grant 授权

mysql> grant select,insert on source.emp to 'aa'@'localhost' identified by '123'; --授权使aa@localhost用户，对source库的emp表拥有select和insert权限
mysql> flush privileges; --刷新权限

mysql> revoke select on source.emp from 'aa'@'localhost'; --回收aa@localhost用户对source库的emp的select权限
mysql> flush privileges;

远程授权

服务端
mysql> grant all on *.* to 'aa'@'%' identified by '123'; --all代表所有权限，*.*代表所有库的所有表 %代表所有IP
mysql> flush privileges;

客户端

[root@li ~]# /usr/local/mysql/bin/mysql -u aa -h 10.1.1.35 -p123 -P 3307

-p 参数表示密码，－P参数表示端口，如果是使用默认的3306就不用改端口

---------------------------------------------------------

函数：

mysql> select user();
mysql> select database();
mysql> select version();

password() 函数

字符函数：
mysql> select concat(ename,' salary is ', salary) from emp;

mysql> select left(ename,2) from emp; --取ename的左两个字符