Fork me on GitHub
Fork me on GitHub

MySQL数据库及表管理

发表于   |   更新于   |   分类于   |   阅读次数

MySQL中字符大小写

1、SQL关键字及函数名不区分字符大小写； 但是为了提高缓存命中率，无论你使用大写或小写，风格上要一致。
2、数据库、表及视图名称的大小区分与否取决于底层OS及FS。如果你是装在windows上的，是不区分大小写的，如果是安装在Linux上的，默认是区分大小写。因此很多时候我们把windows上的库移植到linux上就可能出问题。反过来移植更有问题。
3、存储过程、存储函数及事件调度器的名字不区分大小写，但触发器区分；
4、表的别名区分大小写；
5、对字段中的数据，如果字段类型为Binary类型，则区分大小写；非Binary不区分大小写(非Binary的排序取决于字符集及排序规则)。

MySQL DDL

数据库

1.创建数据库

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

mysql> HELP CREATE DATABASE
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

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

CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE.

URL: http://dev.mysql.com/doc/refman/5.6/en/create-database.html

【注意】:创建数据库时使用database或者schema都可以，对于oracle来讲，通常数据库被叫做schema，对Linux而言，schema或datable都表示数据库。
【示例】：

mysql> create database t1 default charset utf8;
Query OK, 1 row affected (0.00 sec)

2.删除和修改数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']  #如果创建数据库时忘记指定其默认字符集默认排序规则，可以使用ALTER进行修改。

【注意】:如果想换数据库的名字，没有什么好办法，只能把数据库删除再重新创建或者备份数据库，然后删除，创建个新库，把表在导进去，这是比较理想的做法。还有一种比较粗暴的方法，到数据目录下，把那个目录名改一下就可以了。但改完后，这个数据库的数据字典中的信息并没有改变。因此千万不要试图通过修改那个目录名称来达到修改数据库名的目的，所以唯一的可靠做法就是备份、删除、创建新库、导入表，这个代价是很大的。

表

表创建

1.第一种方式

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name       
(create_definition,...)
[table_options]
[partition_options]   # 把一张表切割开，分成几张小表。
    TEMPORARY：创建临时表，临时表是保存在内存中，将占据内存空间，因此一般情况下非管理员在创建内存表上是没有权限的。

(create_definition,...)：
    字段的定义：字段名、类型和类型修饰符
    键、约束或索引：
        PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK(检查约束)
        {INDEX|KEY}

[table_options]
    ENGINE [=] engine_name   ——指定表的存储引擎，如果不指定，将使用默认存储引擎。

【示例】：
查看默认存储引擎:

查看表的存储引擎：

创建表时指定存储引擎为MyISAM：

mysql> CREATE TABLE `t3`(Name VARCHAR(50) NOT NULL, Age TINYINT UNSIGNED NOT NULL, PRIMARY KEY(Name,Age)) ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)


[table_options]
    AUTO_INCREMENT [=] value
    [DEFAULT] CHARACTER SET [=] charset_name
    [DEFAULT] COLLATE [=] collation_name
    COMMENT [=] 'string'   #表的注释。如果表名不足以描述表的功能的话，给表加注释是一个好的习惯。
    DELAY_KEY_WRITE [=] {0 | 1}   ——延迟键写入。这一项在一定程度上能够提升性能的，但是有可能会影响查询。索引是加速查询的，但索引会降低写操作性能，为什么？因为我们更新表时必然得重建索引。所以一个修改非常频繁的表，其索引几乎是无效的。因为索引会经常被重建，索引的大量重建会导致性能很低。这就需要延迟键写入，把跟索引相关的信息几个合并起来，过一次会儿写入。
    ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
    TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]  #指定表空间。MySQL的表主要有两种类型，一种是MyISAM表，一种是InnoDB表。对于InnoDB表来说，如果使用默认的存储方式，我们可以通过TABLESPACE来指定多个共享表空间。如果是独立的表空间存储方式，也可以通过TABLESPACE来指定表空间名字。

MyISAM表，每张表有三个文件，都位于数据库目录中：

  • tb_name.frm: 表结构定义
  • tb_name.MYD: 数据文件
  • tb_name.MYI: 索引文件

InnoDB表，有两种存储方式：
（1）默认：每表有一个独立文件和一个多表共享的文件

  • tb_name.frm: 表结构的定义，位于数据库目录(与数据库名同名的目录)中；
  • ibdata#: 所有数据库的所有Innodb表共享的表空间文件，默认位于数据目录(datadir指向的目录)中；管理起来不方便，所以一般很少这么用。采用第二种方式，将来备份恢复时也很方便。

（2）独立的表空间：

  • tb_name.frm: 每表有一个表结构文件
  • tb_name.ibd: 一个独有的表空间文件

【注意】:一般我们创建一张表时，表就会放在数据库目录下，但是对Innodb存储引擎例外。Innodb存储引擎的所有表默认都放在一个统一的表空间的文件中。
如何启用第二种方式呢？

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb%';


这里已经开启了第二种。可以修改这个值：

mysql> SET GLOBAL innodb_file_per_table=OFF;

【注意】：修改GLOBAL变量对当前会话不生效，需要退出重建会话。如果想要永久有效就得把这个配置写到配置文件的[mysqld]中。

# vim /etc/my.cnf
innodb_file_per_table=ON

2.表创建的第二种方式（复制表数据，也就是根据其他表来创建当前表）

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
  [(create_definition,...)]
  [table_options]
  select_statement  #只是数据一致，但是字段属性跟源表并不一定一致。因此不是复制表，而是复制表中的数据。并自动判断实现字段的创建。对我们而言，这种方法不靠谱。

3.表创建：第三种方式（只复制表结构，不复制数据）

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

所以要先用第三种方式复制表结构，在使用INSERT语句往新表中插入数据，但数据不是自己手动创建的，而是从另外一张表中查询而来的。这样才能做到完整复制一张表。

表删除

DROP [TEMPORARY] TABLE [IF EXISTS]
  tbl_name [, tbl_name] ...
  [RESTRICT | CASCADE]  #CASCADE做级联删除，就是说如果某张表被其他表依赖，那么删除这个某表，使用CASCADE就会删除依赖这张某表的其他表。

表修改

和表创建一样麻烦。

ALTER TABLE tbl_name
  [alter_specification [, alter_specification] ...]

修改字段定义

1.插入新字段
插入新字段：可以一次插入单个字段，也可以一次插入多个字段。插入多个字段时，多个字段需要用()括起来。

2.删除字段

DROP [COLUMN] col_name

3.修改字段
(1)仅修改字段名称

CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]

(2)修改字段类型及属性等

MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]

修改约束、键或索引等

ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...

表改名

使用ALTER命令：RENAME [TO|AS] new_tbl_name

mysql> ALTER TABLE `t5` RENAME TO `t7`;
Query OK, 0 rows affected (0.03 sec)

或者RENAME命令：

mysql> RENAME TABLE old_name TO new_name;

修改表的存储引擎

表引擎的修改背后mysql所执行的操作是创建一个你所指定存储引擎的新表，并把老的表中的数据导入到新表中。不建议通过ALTER直接修改表的存储引擎。