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

一、簡(jiǎn)介

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

商水ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書(shū)未來(lái)市場(chǎng)廣闊！成為創(chuàng)新互聯(lián)的ssl證書(shū)銷(xiāo)售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18982081108（備注：SSL證書(shū)合作）期待與您的合作！

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

二、MySQL 存儲(chǔ)引擎，也稱(chēng)表類(lèi)型

2.1. 單字段：

PRIMARY KEY 主鍵

UNIQUE KEY 唯一鍵

2.2 單或者多字段：

PRIMARY KEY(col,...)

UNIQUE KEY(col,...)

INDEX(col,...)

2.3 數(shù)據(jù)類(lèi)型：

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個(gè)字節(jié)）

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

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

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

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

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

時(shí)間：

| DATE 日期型

| TIME 時(shí)間型

| TIMESTAMP 時(shí)區(qū)型

| DATETIME 日期時(shí)間型

| YEAR 年

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

VARCHAR(length) 變長(zhǎng)字符型 65535 characters

[CHARACTER SET charset_name] [COLLATE collation_name]

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

| VARBINARY(length) 變長(zhǎng)二進(jìn)制數(shù)

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

| BLOB 大對(duì)數(shù)

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

| LONGBLOB 長(zhǎng)的大對(duì)數(shù)

文本：（不大小寫(xiě)）

| 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] 長(zhǎng)的文本串 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 空間的類(lèi)型

2.4、創(chuàng)建數(shù)據(jù)庫(kù)：

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

創(chuàng)建數(shù)據(jù)庫(kù)可以設(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)建一個(gè)students數(shù)據(jù)庫(kù)，字符集為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ù)庫(kù)中db.opt文件類(lèi)型

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

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

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

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

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

2.7、創(chuàng)建表：

2.7.1.直接定義一張空表；col_name 字段名稱(chēng) col_defination 字段定義

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

col_defination字段定義包含：data_type字段類(lèi)型

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)建一個(gè)表tb1，包含三個(gè)字段:

id字段為無(wú)符號(hào)(UNSIGNED)，非空(NOT NULL)，自動(dòng)增長(zhǎng)(AUTO_INCREMENT)，為主鍵(PRIMARY KEY)的整型.

Name字段為定長(zhǎng)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.從其他表中查詢(xún)出數(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)建一個(gè)空表；

查看表索引：

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字段名稱(chēng)為Course并定義為變長(zhǎng)50字符長(zhǎng)度，非空

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表的名稱(chēng)為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

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

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

CREATE INDEX name_on_student ON student (Name) USING BTREE;

#在student表中Name字段上建立一個(gè)名為name_on_student索引，類(lèi)型為BTREE索引，默認(rèn)為BTREE類(lèi)型。

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個(gè)字符建立一個(gè)降序（DESC）排列的索引.

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

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

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

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ù)庫(kù)引擎

Version: 10

Row_format: Compact #類(lèi)似壓縮格式存儲(chǔ)

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ù)，添加物理，英語(yǔ)，化學(xué)，數(shù)學(xué)等課程。

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM courses; #查詢(xún)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樹(shù)索引

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

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

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

1 row in set (0.00 sec)

1.3.2新表中加入查詢(xún)值，但生成的表與原表格式會(huì)不一致，可先創(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ù)庫(kù)表的信息

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

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

實(shí)例2.建立student表，并進(jìn)行相關(guān)數(shù)據(jù)的插入，查詢(xún)操作練習(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個(gè)字段，SID字段為無(wú)符號(hào)、非空、自動(dòng)增長(zhǎng)、主鍵的整數(shù)型，

Name字段為變長(zhǎng)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); #對(duì)Name,CID字段插入2條數(shù)據(jù)。

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql>SELECT * FROM student; #查詢(xún)student表

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

| SID | Name | CID |

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

| 1 | Li Li | 1 |

| 2 | ChengChen | 2 |

2 rows in set (0.00 sec)

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

mysql> SELECT * FROM courses; #查詢(xún)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; #查詢(xún)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字段增加一個(gè)外鍵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`))

#提示報(bào)錯(cuò)，由于CID外鍵索引courses表中CID沒(méi)有5，所以無(wú)法增加。

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

Query OK, 4 rows affected (0.05 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM student; #查詢(xún)表的內(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開(kāi)始增長(zhǎng)，所以剛插入的數(shù)據(jù)是從5開(kāi)始；

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; #驗(yàn)證

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

| 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ù)的自動(dòng)增長(zhǎng)主鍵SID從3開(kāi)始增長(zhǎng)

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)建索引，實(shí)現(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)無(wú)法修改

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

網(wǎng)頁(yè)標(biāo)題：Mysql數(shù)據(jù)庫(kù)理論基礎(chǔ)之四---表和索引的管理
新聞來(lái)源：http://www.chinadenli.net/article16/gisogg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、ChatGPT、手機(jī)網(wǎng)站建設(shè)、自適應(yīng)網(wǎng)站、移動(dòng)網(wǎng)站建設(shè)、域名注冊(cè)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

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

一、簡(jiǎn)介

二、MySQL 存儲(chǔ)引擎，也稱(chēng)表類(lèi)型