Mysql數(shù)據(jù)庫理論基礎(chǔ)之四 --- 表和索引的管理

發(fā)布時間：2020-08-16 13:40:51 來源：網(wǎng)絡(luò) 閱讀：430 作者：風(fēng)過_無痕欄目：數(shù)據(jù)庫

一、簡介

由MySQL AB公司開發(fā)，是最流行的開放源碼SQL數(shù)據(jù)庫管理系統(tǒng)，主要特點：

1、是一種數(shù)據(jù)庫管理系統(tǒng)
2、是一種關(guān)聯(lián)數(shù)據(jù)庫管理系統(tǒng)
3、是一種開放源碼軟件，且有大量可用的共享MySQL軟件
4、MySQL數(shù)據(jù)庫服務(wù)器具有快速、可靠和易于使用的特點
5、MySQL服務(wù)器工作在客戶端/服務(wù)器模式下，或嵌入式系統(tǒng)中

二、MySQL 存儲引擎，也稱表類型

2.1. 單字段：

PRIMARY KEY 主鍵

UNIQUE KEY 唯一鍵

2.2 單或者多字段：

PRIMARY KEY(col,...)

UNIQUE KEY(col,...)

INDEX(col,...)

2.3 數(shù)據(jù)類型：

BIT[(length)] 比特

| TINYINT[(length)] [UNSIGNED] [ZEROFILL] 非常小的整數(shù)（1字節(jié)）

| SMALLINT[(length)] [UNSIGNED] [ZEROFILL] 小的整數(shù)（2字節(jié)）

| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] 中等的整數(shù)（3字節(jié)）

| INT[(length)] [UNSIGNED] [ZEROFILL] 整數(shù)（4字節(jié)）

| INTEGER[(length)] [UNSIGNED] [ZEROFILL] 整數(shù)（4字節(jié)）相當(dāng)于INT

| BIGINT[(length)] [UNSIGNED] [ZEROFILL] 大的整數(shù)（8個字節(jié)）

| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] 實數(shù)

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

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

| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]十進制小數(shù)點型

| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]數(shù)值型

時間：

| DATE 日期型

| TIME 時間型

| TIMESTAMP 時區(qū)型

| DATETIME 日期時間型

| YEAR 年

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

VARCHAR(length) 變長字符型 65535 characters

[CHARACTER SET charset_name] [COLLATE collation_name]

| BINARY[(length)] 二進制數(shù)

| VARBINARY(length) 變長二進制數(shù)

| TINYBLOB 非常小的大對數(shù)

| BLOB 大對數(shù)

| MEDIUMBLOB 中等的大對數(shù)

| LONGBLOB 長的大對數(shù)

文本：（不大小寫）

| 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、創(chuàng)建數(shù)據(jù)庫：

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

創(chuàng)建數(shù)據(jù)庫可以設(shè)置字符集，排序規(guī)則

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

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

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

.......

| 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; #查看排序規(guī)則

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

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

| 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';

#創(chuàng)建一個students數(shù)據(jù)庫，字符集為gbk，排序規(guī)則為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數(shù)據(jù)庫中db.opt文件類型

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

2.5、修改數(shù)據(jù)庫：

ALTER {DATABASE | SCHEMA} [db_name] alter_specification ...#修改數(shù)據(jù)庫的屬性，比如字符集或者排序規(guī)則，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 #升級數(shù)據(jù)庫的數(shù)據(jù)目錄

2.6、刪除數(shù)據(jù)庫：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name #刪除數(shù)據(jù)庫

2.7、創(chuàng)建表：

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);

#創(chuàng)建一個表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.從其他表中查詢出數(shù)據(jù)，并以之創(chuàng)建新表；

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

#從courses表中查找CID小于等于2的數(shù)據(jù)，并作為新建testcourses表的內(nèi)容。

2.7.3.以其他表為模板創(chuàng)建一個空表；

查看表索引：

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

查看表結(jié)構(gòu)：

DESC tb_name; 查看表結(jié)構(gòu)

mysql> DESC courses;

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

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

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

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表的索引

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

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

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

2 rows in set (0.00 sec)

mysql> DESC test; #查看表結(jié)構(gòu)

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

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

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

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; 查看表結(jié)構(gòu)

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

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

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

2 rows in set (0.00 sec)

mysql> DROP TABLE testcourses; #刪除testcourses表

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;查看所有表

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

| Tables_in_students |

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

| courses |

| test |

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

2 rows in set (0.00 sec)

mysql> ALTER TABLE test RENAME TO testcourses; #修改test表的名稱為testcourses

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;查看所有表

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

| Tables_in_students |

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

| courses |

| testcourses |

2 rows in set (0.00 sec)

mysql> RENAME TABLE testcourses TO test; #也可以直接使用RENAME重命名。

Query OK, 0 rows affected (0.00 sec)

2.9、新增索引：（索引只能新建刪除，不能修改）

CREATE INDEX index_name ON tb_name (col,...) col_name (length) ASC|DESC

指定以字段前幾的長度為索引，ASC升序排列，DESC降序排列。

在tb_name表上的col字段創(chuàng)建一個索引index_name

CREATE INDEX name_on_student ON student (Name) USING BTREE;

#在student表中Name字段上建立一個名為name_on_student索引，類型為BTREE索引，默認為BTREE類型。

mysql> CREATE INDEX name_on_student ON student (Name) USING BTREE ;

mysql> SHOW INDEXES FROM student ;

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

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

| student | 0 | PRIMARY | 1 | SID | A| 4 |NULL | NULL| | BTREE| | |

| student | 1 |foreign_cid| 1 | CID | A| 4 |NULL | NULL| | BTREE | | |

| student | 1 |name_on_student| 1 | Name| A| 4 | NULL | NULL| YES| BTREE | | |

3 rows in set (0.00 sec)

mysql> DROP INDEX name_on_student ON student;#刪除student表中的索引name_on_student

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX name_on_student ON student (Name(5) DESC);#為student表以Name字段

的前5個字符建立一個降序（DESC）排列的索引.

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

實例1：創(chuàng)建一個students數(shù)據(jù)庫，以及表的創(chuàng)建，查找等功能的練習(xí)；

1.1.創(chuàng)建一個students數(shù)據(jù)庫

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

#字符集為gbk，排序規(guī)則為gbk_chinese_ci

Query OK, 1 row affected (0.01 sec)

mysql> USE students;

Database changed

mysql> CREATE TABLE courses(CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Couse VARCHAR(50) NOT NULL);

Query OK, 0 rows affected (0.07 sec)

mysql> SHOW TABLE STATUS LIKE 'courses'\G;

************************ 1. row ***********************

Name: courses

Engine: InnoDB #數(shù)據(jù)庫引擎

Version: 10

Row_format: Compact #類似壓縮格式存儲

Rows: 0

Avg_row_length: 0

Data_length: 16384

...

1.2.刪除表，新建引擎為MyISAM的新表

mysql> DROP TABLES courses; #刪除表

Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE courses(CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Couse VARCHAR(50) NOT NULL) ENGINE=MyISAM; #ENGINE設(shè)定引擎為MyISAM

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'courses' \G;

*************** 1. row *************

Name: courses

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 281474976710655

...

1 row in set (0.00 sec)

1.3.1新表中加入值

mysql> INSERT INTO courses (Couse) values ('physics'),('english'),('chemistry'),('maths');

#插入Couse課程字段數(shù)據(jù)，添加物理，英語，化學(xué)，數(shù)學(xué)等課程。

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM courses; #查詢courses表的條目

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

| CID | Couse |

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

| 1 | physics |

| 2 | english |

| 3 | chemistry|

| 4 | maths |

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

4 rows in set (0.00 sec)

mysql> SHOW INDEXES FROM courses; #查看courses表的索引 -B樹索引

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

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

| courses | 0 | PRIMARY | 1 | CID | A | 4 | NULL | NULL | | BTREE | | |

1 row in set (0.00 sec)

1.3.2新表中加入查詢值，但生成的表與原表格式會不一致，可先創(chuàng)建表再插入值；

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

#查找courses表中CID字段小于等于2的數(shù)據(jù)，并把查找到的數(shù)據(jù)作為新建testcourses表的數(shù)據(jù)內(nèi)容。

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SHOW TABLES; #查看當(dāng)前數(shù)據(jù)庫表的信息

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

| Tables_in_students |

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

| courses |

| testcourses |

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

2 rows in set (0.00 sec)

mysql> SELECT * FROM testcourses; #查看testcourses表的內(nèi)容

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

| CID | Couse |

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

| 1 | physics |

| 2 | english |

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

2 rows in set (0.00 sec)

mysql> DESC courses; #查看courses表結(jié)構(gòu)

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

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

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

2 rows in set (0.00 sec)

mysql> DESC testcourses; #查看testcourses表結(jié)構(gòu)與上面的courses不一樣

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

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

| CID | tinyint(3) unsigned | NO | | 0 | |

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

2 rows in set (0.00 sec)

mysql> CREATE TABLE test LIKE courses; #以courses表為模板創(chuàng)建test空表。

Query OK, 0 rows affected (0.00 sec)

mysql> DESC test; #查看test表結(jié)構(gòu)

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

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

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

2 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'test'\G; #查看test表的狀態(tài)

*************** 1. row *********

Name: test

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 0

Avg_row_length: 0

Data_length: 0

Max_data_length: 281474976710655

Index_length: 1024

Data_free: 0

Auto_increment: 1

Create_time: 2017-04-25 11:31:46

Update_time: 2017-04-25 11:31:46

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

實例2.建立student表，并進行相關(guān)數(shù)據(jù)的插入，查詢操作練習(xí)，修改引擎，修改字段修飾，增加外鍵索引；

2.1.建立student表

mysql>CREATE TABLE student (SID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name VARCHAR(30),CID INT NOT NULL);

#創(chuàng)建student表，包含3個字段，SID字段為無符號、非空、自動增長、主鍵的整數(shù)型，

Name字段為變長30字符，

CID字符為非空整數(shù)型。

mysql> SHOW TABLES;

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

| Tables_in_students |

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

| courses |

| student |

| testcourses |

3 rows in set (0.00 sec)

2.2.在student表中插入數(shù)據(jù)

mysql>INSERT INTO student (Name,CID) VALUES ('Li Li',1),('ChengChen',2); #對Name,CID字段插入2條數(shù)據(jù)。

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql>SELECT * FROM student; #查詢student表

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

| SID | Name | CID |

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

| 1 | Li Li | 1 |

| 2 | ChengChen | 2 |

2 rows in set (0.00 sec)

2.3.在student表中查詢數(shù)據(jù)

mysql> SELECT * FROM courses; #查詢courses表

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

| CID| Couse|

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

|1|physics|

|2|english|

|3|chemistry|

|4| maths|

|5| Hamagong|

|6| Pixiejianfa|

|7| Kuihuabaodian |

7 rows in set (0.00 sec)

mysql> SELECT Name,Couse FROM student,courses WHERE student.CID=courses.CID; #查詢student表和courses表中CID相同的Name和Couse字段內(nèi)容

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

| Name|Couse|

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

| Li Li| physics |

| Cheng Chen | english |

2 rows in set (0.00 sec)

2.4.在student表中刪除、修改數(shù)據(jù)

mysql>DELETE FROM courses WHERE CID > 5; #刪除SID大于5的行。

Query OK, 5 rows affected (0.01 sec)

mysql>ALTER TABLE courses ENGINE=Innodb; #修改courses表的引擎為Innodb;

Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql>ALTER TABLE student MODIFY CID TINYINT UNSIGNED NOT NULL;#修改student表中CID字段的修飾MODIFY

Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> DESC courses;

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

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

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

2 rows in set (0.00 sec)

mysql> DESC student;

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

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

| SID | int(10) unsigned | NO | PRI | NULL | auto_increment |

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

3 rows in set (0.00 sec)

mysql> ALTER TABLE student ADD FOREIGN KEY foreign_cid (CID) REFERENCES courses (CID);

#為student表的CID字段增加一個外鍵foreign_cid關(guān)聯(lián)courses表的CID字段。

Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

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

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

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

|student |0 | PRIMARY | 1 | SID | A|2 |NULL | NULL| | BTREE| | |

|student |1 |foreign_cid| 1 | CID | A|2 |NULL | NULL| | BTREE| | |

2 rows in set (0.00 sec)

mysql> INSERT INTO student (Name,CID) VALUES ('Guo Xiang',5);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`students`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`CID`) REFERENCES `courses` (`CID`))

#提示報錯，由于CID外鍵索引courses表中CID沒有5，所以無法增加。

mysql> ALTER TABLE student AUTO_INCREMENT=5; #設(shè)定student表下一條數(shù)據(jù)的自動增長主鍵SID從5開始增長。

Query OK, 4 rows affected (0.05 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM student; #查詢表的內(nèi)容

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

| SID | Name| CID |

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

| 1 | Li Lianjie | 1 |

| 2 | Cheng Long | 2 |

| 3 | Xiao Longnv | 3 |

| 4 | Yang Guo | 4 |

4 rows in set (0.00 sec)

mysql> INSERT INTO student (Name,CID) VALUES ('Guo Xiang',3);

#插入一條數(shù)據(jù)，SID主鍵由于上面設(shè)置從5開始增長，所以剛插入的數(shù)據(jù)是從5開始；

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM student;

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

| SID | Name | CID |

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

| 1 | Li Lianjie| 1 |

| 2 | Cheng Long | 2 |

| 3 | Xiao Longnv | 3 |

| 4 | Yang Guo | 4 |

|5 | Guo Xiang | 3 |

5 rows in set (0.00 sec)

mysql> INSERT INTO student (Name,CID) VALUES ('Qiao Feng',2);插入數(shù)據(jù)

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM student;

mysql> DELETE FROM student WHERE SID >2 AND SID <5; #刪除2<SID<5的行

Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM student; #驗證

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

| SID | Name | CID |

| 1 | Li Lianjie | 1 |

| 2 | Cheng Long | 2 |

| 5 | Guo Xiang | 3 |

| 6 | Qiao Feng | 2 |

4 rows in set (0.00 sec)

mysql> DELETE FROM student WHERE SID in (5,6); #刪除SID為5和6的行

Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM student;

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

| SID | Name | CID |

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

| 1 | Li Lianjie | 1 |

| 2 | Cheng Long | 2 |

2 rows in set (0.00 sec)

mysql>ALTER TABLE student AUTO_INCREMENT=3;#設(shè)定表下一條數(shù)據(jù)的自動增長主鍵SID從3開始增長

Query OK, 2 rows affected (0.07 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql>INSERT INTO student (Name,CID) VALUES ('Yang Guo',3),('Guo Jing',4);#插入2條數(shù)據(jù)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM student;

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

| SID | Name | CID |

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

| 1 | Li Lianjie | 1 |

| 2 | Cheng Long | 2 |

| 3 | Yang Guo | 3 |

| 4 | Guo Jing | 4 |

4 rows in set (0.00 sec)

2.5.在student表中創(chuàng)建索引，實現(xiàn)比較或排序

mysql> SELECT * FROM student;

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

| SID | Name | CID |

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

| 1 | Li Li | 1 |

| 2 | Cheng Chen | 2 |

| 3 | YangGuo | 3 |

| 4 | GuoJing | 4 |

4 rows in set (0.00 sec)

mysql> CREATE INDEX name_on_student ON student (Name) USING BTREE;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DROP INDEX name_on_student ON student; #索引只能新建或刪除,因其是結(jié)構(gòu)無法修改

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX name_on_student ON student (Name(5) DESC) USING BTREE;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW INDEXES FROM student;

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

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

| student|0 | PRIMARY| 1 | SID | A | 2 | NULL | NULL | BTREE | | |

| student|1 | foreign_cid | 1| CID | A |2 | NULL | NULL| | BTREE | | |

| student|1 | name_on_student|1| Name| A |4 | 5 | NULL | YES| BTREE | | |

3 rows in set (0.00 sec)

---end-6---

向AI問一下細節(jié)

Mysql數(shù)據(jù)庫理論基礎(chǔ)之四 --- 表和索引的管理

一、簡介

二、MySQL 存儲引擎，也稱表類型

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標簽

一、簡介

二、MySQL 存儲引擎，也稱表類型