MySQL表、库、索引管理

4rwgf

内容概述：
1.数据库的创建删除
2.创建表的三种方法
   1、直接定义一张空表；
   2、从其它表中查询出数据，并以之创建新表；（表结构会变化）
   3、以其它表为模板创建一个空表；（表结构不会变化，表结构完全一样）
3.修改表定义
   ALTER TABLE
   添加、删除字段、修改字段
   添加、删除、修改索引
   改表名
   修改表存储引擎

4.索引
   创建
   查看
   删除
   无法修改
5.shell环境使用MySQL语句
   
1.数据库操作
创建数据库
语法
CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name [CHARACTER SET=] [COLLATE=]
                                                     字符集          排序规则
mysql> CREATE SCHEMA IF NOT EXISTS seudents CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';
Query OK, 1 row affected (0.01 sec)

db.opt保存和数据库的排序规则和默认字符集
[iyunv@localhost ~]# cd /mydate/date/seudents/
[iyunv@localhost seudents]# ls
db.opt
[iyunv@localhost seudents]# cat db.opt
default-character-set=gbk
default-collation=gbk_chinese_ci

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

2.表管理
1、直接定义一张空表；
    CREATE TABLE [IF NOT EXISTS] tb_name (col_name col_defination, constraint )
     col_defination：字段名称，字段定义（类型，修饰符）  
     constraint：数据类型
     
     例：
     CREATE TABLE tb1 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL)
     tb1：表名
     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY：字段id，INT UNSIGNED字段类型，NOT NULL不允许为空，AUTO_INCREMENT自动增长（自动给增长需要为主键）， PRIMARY KEY主键
     Name CHAR(20) NOT NULL：Name字段名，CHAR（20）字段类型，不允许为空
     Age TINYINT NOT NULL)：Age字段，不允许为空
     
     定义id字段为主键PRIMARY KEY(id)
     CREATE TABLE tb2 (id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL, PRIMARY KEY(id)
     
     定义name字段为惟一建，age字段为索引字段
     CREATE TABLE tb3 (id INT UNSIGNED NOT NULL AUTO_INCREMENT, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL, PRIMARY KEY(id),UNIQUE KEY(name),INDEX(age)
     
     设置表使用指定的存储引擎ENGINE [=] engine_name，如果不指定将从数据库直接继承
     CREATE TABLE tb4 (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Name CHAR(20) NOT NULL, Age TINYINT NOT NULL)  ENGINE [=] engine_name
     
2、从其它表中查询出数据，并以之创建新表；（表结构会变化）
从students表中查询数据，创建一张名为test的表
mysql> CREATE TABLE test SELECT * FROM students WHERE SID <=2;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+-----+---------+------+--------+------+------+------+---------------------+
| SID | Name    | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+---------+------+--------+------+------+------+---------------------+
|   1 | GuoJing |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
+-----+---------+------+--------+------+------+------+---------------------+

分别查看两个表的表结构，发现test和students表的结构不一样
mysql> DESC students;
+------------+----------------------+------+-----+---------------------+----------------+
| Field      | Type                 | Null | Key | Default             | Extra          |
+------------+----------------------+------+-----+---------------------+----------------+
| SID        | smallint(5) unsigned | NO   | PRI | NULL                | auto_increment |
| Name       | varchar(50)          | NO   |     | NULL                |                |
| Age        | tinyint(3) unsigned  | YES  |     | NULL                |                |
| Gender     | enum('F','M')        | YES  |     | M                   |                |
| CID1       | smallint(5) unsigned | YES  |     | NULL                |                |
| CID2       | smallint(5) unsigned | YES  |     | NULL                |                |
| TID        | smallint(6)          | YES  |     | NULL                |                |
| CreateTime | datetime             | YES  |     | 2012-04-06 10:00:00 |                |
+------------+----------------------+------+-----+---------------------+----------------+
8 rows in set (0.00 sec)

mysql> DESC test;   
+------------+----------------------+------+-----+---------------------+-------+
| Field      | Type                 | Null | Key | Default             | Extra |
+------------+----------------------+------+-----+---------------------+-------+
| SID        | smallint(5) unsigned | NO   |     | 0                   |       |
| Name       | varchar(50)          | NO   |     | NULL                |       |
| Age        | tinyint(3) unsigned  | YES  |     | NULL                |       |
| Gender     | enum('F','M')        | YES  |     | M                   |       |
| CID1       | smallint(5) unsigned | YES  |     | NULL                |       |
| CID2       | smallint(5) unsigned | YES  |     | NULL                |       |
| TID        | smallint(6)          | YES  |     | NULL                |       |
| CreateTime | datetime             | YES  |     | 2012-04-06 10:00:00 |       |
+------------+----------------------+------+-----+---------------------+-------+
8 rows in set (0.00 sec)

3.以students表为模版创建一张空表,查看表结构和students表的结构完全一样
mysql> CREATE TABLE test2 LIKE students;
Query OK, 0 rows affected (0.07 sec)

mysql> DESC test2;
+------------+----------------------+------+-----+---------------------+----------------+
| Field      | Type                 | Null | Key | Default             | Extra          |
+------------+----------------------+------+-----+---------------------+----------------+
| SID        | smallint(5) unsigned | NO   | PRI | NULL                | auto_increment |
| Name       | varchar(50)          | NO   |     | NULL                |                |
| Age        | tinyint(3) unsigned  | YES  |     | NULL                |                |
| Gender     | enum('F','M')        | YES  |     | M                   |                |
| CID1       | smallint(5) unsigned | YES  |     | NULL                |                |
| CID2       | smallint(5) unsigned | YES  |     | NULL                |                |
| TID        | smallint(6)          | YES  |     | NULL                |                |
| CreateTime | datetime             | YES  |     | 2012-04-06 10:00:00 |                |
+------------+----------------------+------+-----+---------------------+----------------+

3.修改表定义
修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型;

mysql> ALTER TABLE students CHANGE Age 年龄 TINYINT(3);        #生产环境千万不要使用中文
Query OK, 10 rows affected (0.10 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM students;
+-----+--------------+--------+--------+------+------+------+---------------------+
| SID | Name         | 年龄   | Gender | CID1 | CID2 | TID  | CreateTime          |
+-----+--------------+--------+--------+------+------+------+---------------------+
|   1 | GuoJing      |     19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |     17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |     25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |     31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |     16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |     18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |     20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |     26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong  |     22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
|  10 | YiLin        |     19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+--------+--------+------+------+------+---------------------+

给表新添加字段
ALTER TABLE 表名 ADD 字段名 字段类型;
mysql> ALTER TABLE students ADD 课程3 smallint(5);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM students;                    
+-----+--------------+--------+--------+------+------+------+---------------------+---------+
| SID | Name         | 年龄   | Gender | CID1 | CID2 | TID  | CreateTime          | 课程3   |
+-----+--------------+--------+--------+------+------+------+---------------------+---------+
|   1 | GuoJing      |     19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |    NULL |
|   2 | YangGuo      |     17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |    NULL |
|   3 | DingDian     |     25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |    NULL |
|   4 | HuFei        |     31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |    NULL |
|   5 | HuangRong    |     16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |    NULL |
|   6 | YueLingshang |     18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |    NULL |
|   7 | ZhangWuji    |     20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |    NULL |
|   8 | Xuzhu        |     26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |    NULL |
|   9 | LingHuchong  |     22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |    NULL |
|  10 | YiLin        |     19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |    NULL |
+-----+--------------+--------+--------+------+------+------+---------------------+---------+

修改表名
ALTER TABLE 旧表名 RENAME TO 新表名；
RENAME TABLE 旧表名 TO 新表名；

mysql> ALTER TABLE students RENAME TO 学生;
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_jiaowu |
+------------------+
| 学生             |
| courses          |
| scores           |
| test             |
| test2            |
| tutors           |
+------------------+


修改表的存储引擎
mysql> SHOW TABLE STATUS like 'students'\G;
*************************** 1. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 10
Avg_row_length: 1638
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
Auto_increment: 3907
    Create_time: 2014-11-23 17:24:02
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> ALTER TABLE students ENGINE=MyISAM;
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS like 'students'\G;
*************************** 1. row ***************************
           Name: students
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 10
Avg_row_length: 32
    Data_length: 320
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
Auto_increment: 3907
    Create_time: 2014-11-23 17:31:01
    Update_time: 2014-11-23 17:31:01
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
Create_options:
        Comment:
1 row in set (0.00 sec)

4.索引管理
创建索引
CREATE　INDEX 索引名　ON 表名　(字段);
col_name [(length)] [ASC | DESC]
length:从最左侧开始比较指定长度的字符
ASC:升序
DESC：降序


给students表的Name字段创建一个名为name_on_students的索引

mysql> CREATE INDEX name_on_students ON students (Name);  
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

1 2 3 4 5 6 7 8

mysql> SHOW INDEXES FROM students; +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table    | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | students |          0 | SID              |            1 | SID         | A         |          10 |     NULL | NULL   |      | BTREE      |         |               | | students |          1 | name_on_students |            1 | Name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)

   
创建指定长度和排序规则的索引
mysql> CREATE INDEX name_on_Name ON students (Name(5) DESC) USING BTREE;      
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

1 2 3 4 5 6 7 8 9

mysql> SHOW INDEXES FROM students;                                       +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table    | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | students |          0 | SID              |            1 | SID         | A         |          10 |     NULL | NULL   |      | BTREE      |         |               | | students |          1 | name_on_students |            1 | Name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               | | students |          1 | name_on_Name     |            1 | Name        | A         |        NULL |        5 | NULL   |      | BTREE      |         |               | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)

删除索引
mysql> DROP INDEX name_on_Name ON students;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

1 2 3 4 5 6 7

mysql> SHOW INDEXES FROM students;          +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table    | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | students |          0 | SID              |            1 | SID         | A         |          10 |     NULL | NULL   |      | BTREE      |         |               | | students |          1 | name_on_students |            1 | Name        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               | +----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

5.在shell中使用MySQL语句
创建库和查看库

[iyunv@localhost ~]# mysql -e 'CREATE DATABASE edb;'
[iyunv@localhost ~]# mysql -e 'SHOW DATABASES;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| edb                |
| hellodb            |
| jiaowu             |
| mydb               |
| mysql              |
| performance_schema |
| seudents           |
| students           |
| test               |
| testdb             |
+--------------------+
插入数据
[iyunv@localhost ~]# mysql -e "INSERT INTO jiaowu.students (Name,Age,Gender,CID1,CID2,TID) VALUES ('stu1',23,'F',4,1,6);"