Mysql数据库理论基础之四 --- 表和索引的管理

• 1、是一种数据库管理系统

• 2、是一种关联数据库管理系统

• 3、是一种开放源码软件，且有大量可用的共享MySQL软件

• 4、MySQL数据库服务器具有快速、可靠和易于使用的特点

• 5、MySQL服务器工作在客户端/服务器模式下，或嵌入式系统中

• 
  

二、MySQL 存储引擎，也称表类型

2.1. 单字段：

   PRIMARY KEY 主键

   UNIQUE KEY 唯一键

2.2 单或者多字段：

   PRIMARY KEY(col,...)

   UNIQUE KEY(col,...)

   INDEX(col,...)

2.3 数据类型：

   BIT[(length)] 比特

  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]  非常小的整数（1字节） 

  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]  小的整数（2字节）

  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] 中等的整数（3字节）

  | INT[(length)] [UNSIGNED] [ZEROFILL]    整数（4字节）

  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]  整数（4字节）相当于INT

  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]  大的整数（8个字节）

  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]  实数

  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] 双精度整型

  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]  浮点型

  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]十进制小数点型

  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]数值型

时间：

  | DATE    日期型

  | TIME     时间型

  | TIMESTAMP  时区型

  | DATETIME  日期时间型

  | YEAR    年

  | CHAR[(length)]  定长字符型  255 characters

   VARCHAR(length) 变长字符型  65535 characters

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | BINARY[(length)]  二进制数

  | VARBINARY(length) 变长二进制数

  | TINYBLOB  非常小的大对数

  | BLOB    大对数

  | MEDIUMBLOB   中等的大对数

  | LONGBLOB    长的大对数

文本：（不大小写）

  | TINYTEXT [BINARY]  非常小的文本串 255 characters 2~8 

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | TEXT [BINARY]    文本串    65535 characters 2~16

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | MEDIUMTEXT [BINARY] 中等的文本串 16,777,215 characters 2~24

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | LONGTEXT [BINARY]  长的文本串 4,294,967,295 characters 2~32 

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | ENUM(value1,value2,value3,...)  枚举型

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | SET(value1,value2,value3,...)  集合型

      [CHARACTER SET charset_name] [COLLATE collation_name]

  | spatial_type             空间的类型

2.4、创建数据库：

    CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name [CHARACTER SET=] [COLLATE]

   创建数据库可以设置字符集，排序规则

mysql> SHOW CHARACTER SET;  #查看字符集

+----------+-----------------------------+---------------------+--------+

| Charset  | Description     | Default collation   | Maxlen |

+----------+-----------------------------+---------------------+--------+

.......

| big5    | Big5 Traditional Chinese  | big5_chinese_ci   |   2 |

| tis620   | TIS620 Thai        | tis620_thai_ci    |   1 |

| cp1250   | Windows Central European   | cp1250_general_ci   |   1 |

| gbk    | GBK Simplified Chinese    | gbk_chinese_ci     |   2 |

........

39 rows in set (0.00 sec)

mysql> SHOW COLLATION;   #查看排序规则

+-------------------+----------+-----+---------+----------+---------+

| Collation      | Charset  | Id  | Default | Compiled | Sortlen |

+-------------------+----------+-----+---------+----------+---------+

| big5_chinese_ci  | big5    |   1 | Yes   | Yes |  1 |

| big5_bin   | big5   |  84 |      | Yes  |  1 |

| cp1250_polish_ci  | cp1250   |  99 |     | Yes |  1 |

...

197 rows in set (0.00 sec)

mysql> show global variables like ‘%char%‘;

+------------------+-----------------+

| Variable_name  | Value |

+------------------+----------------+

| character_set_client | latin1 | 

| character_set_connection | latin1 |

| character_set_database  | latin1 |

| character_set_filesystem | binary |

| character_set_results  | latin1|

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ | 

8 rows in set (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS students CHARACTER SET ‘gbk‘ COLLATE ‘gbk_chinese_ci‘;

#创建一个students数据库，字符集为gbk，排序规则为gbk_chinese_ci

Query OK, 1 row affected (0.01 sec)

mysql> \q

Bye

[root@lamp ~]# ls /mydata/data  #查看students是否新建成功

ib_logfile1  mysql-bin.000001  mysql-bin.000006  mysql-bin.000011  students

[root@lamp ~]# file /mydata/data/students/db.opt  #查看students数据库中db.opt文件类型 

/mydata/data/students/db.opt: ASCII text

2.5、修改数据库：

ALTER {DATABASE | SCHEMA} [db_name] alter_specification ...#修改数据库的属性，比如字符集或者排序规则，alter_specification CHARACTER SET = charset_name  COLLATE = collation_name

alter_specification包含:

    [DEFAULT] CHARACTER SET [=] charset_name

   | [DEFAULT] COLLATE [=] collation_name

ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAME #升级数据库的数据目录

2.6、删除数据库：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name #删除数据库

2.7、创建表：

   2.7.1.直接定义一张空表；col_name 字段名称 col_defination 字段定义

      CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination,)

   col_defination字段定义包含：data_type字段类型      

      data_type [NOT NULL | NULL] [DEFAULT default_value]

      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

      [COMMENT ‘string‘]

      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]

      [STORAGE {DISK|MEMORY|DEFAULT}]

      [reference_definition]

Usage:CREATE TABLE tb1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name CHAR(20) NOT NULL,Age TINYINT NOT NULL);

#创建一个表tb1，包含三个字段:

  id字段为无符号(UNSIGNED)，非空(NOT NULL)，自动增长(AUTO_INCREMENT)，为主键(PRIMARY KEY)的整型.

  Name字段为定长20(CHAR(20)),非空的字符型。

  Age字段为非空的非常小的整型。

或者 CREATE TABLE tb2(id INT UNSIGNED NOT NULL AUTO_INCREMENT,Name CHAR(20) NOT NULL,Age TINYINT NOT NULL,PRIMARY KEY(id),Unique KEY (Name),INDEX(age));  #Unique KEY 唯一键，INDEX索引

   2.7.2.从其他表中查询出数据，并以之创建新表；

    CREATE TABLE testcourses SELECT * FROM courses WHERE CID <= 2;

#从courses表中查找CID小于等于2的数据，并作为新建testcourses表的内容。

   2.7.3.以其他表为模板创建一个空表；  

   查看表索引：

      SHOW INDEXES FROM courses; 显示制定表索引 

   查看表结构：

      DESC tb_name; 查看表结构

mysql> DESC courses;

+-------+----------+------+-----+---------+----------------+

| Field | Type   | Null | Key | Default | Extra  |

+-------+----------+------+-----+---------+----------------+

| CID | tinyint(3) unsigned | NO | PRI |NULL|auto_increment|

| Couse | varchar(50) | NO   |  | NULL |     |

2 rows in set (0.00 sec)

2.8、修改表定义：ALTER TABLE

       添加、删除、修改字段，添加、删除、修改索引，改表名，修改表属性。

mysql> ALTER TABLE test ADD INDEX(Couse); #给test表增加以Couse字段为索引

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM test; #查看test表的索引

+------+-------+-------+------+------+-------+-------+-------+------+------+-------+-------+-------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+------+-------+-------+------+------+-------+-------+

| test |  0 | PRIMARY  |  1 | CID  | A |  0 |  NULL | NULL |   | BTREE |   |  |

| test | 1 | Couse  |  1 | Couse | A  | NULL |  NULL | NULL |  | BTREE  |   |  |

2 rows in set (0.00 sec)

mysql> DESC test; #查看表结构

+-------+---------------------+------+-----+---------+----------------+

| Field | Type  | Null | Key | Default | Extra |

+-------+---------------------+------+

| CID   | tinyint(3) unsigned | NO  | PRI | NULL  | auto_increment |

| Couse | varchar(50) | NO  | MUL | NULL    |    |

2 rows in set (0.00 sec)

mysql> ALTER TABLE test CHANGE Couse Course VARCHAR(50) NOT NULL; #修改test表的Couse字段名称为Course并定义为变长50字符长度，非空

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test; 查看表结构

+--------+---------------------+------+-----+---------+----------------+

• < 上一篇

  sqlserver 调试WINDBG ---troubleshootingsql.com

• 下一篇 >

  mongoDb，下载及启动