MySQL如何管理創(chuàng)建CREATE表和索引

小編給大家分享一下MySQL如何管理創(chuàng)建CREATE表和索引，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

創(chuàng)新互聯(lián)公司專注于獻縣企業(yè)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)站設(shè)計,商城系統(tǒng)網(wǎng)站開發(fā)。獻縣網(wǎng)站建設(shè)公司,為獻縣等地區(qū)提供建站服務(wù)。全流程按需求定制網(wǎng)站，專業(yè)設(shè)計，全程項目跟蹤，創(chuàng)新互聯(lián)公司專業(yè)和態(tài)度為您提供的服務(wù)

SQL語句：

數(shù)據(jù)庫

表

索引

視圖

DML語句

單字段：

PRIMARY KEY 主鍵

UNIQUE KEY 唯一鍵

單或者多字段：

PRIMARY KEY(col,...)

UNIQUE KEY(col,...)

INDEX(col,...)

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

data_type:

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é)）相當于INT

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

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

| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]雙數(shù)

| 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)] 定長字符型

VARCHAR(length)變長字符型

[CHARACTER SET charset_name] [COLLATE collation_name]

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

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

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

| BLOB 大對數(shù)

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

| LONGBLOB 長的大對數(shù)

| TINYTEXT [BINARY]非常小的文本串

[CHARACTER SET charset_name] [COLLATE collation_name]

| TEXT [BINARY]文本串

[CHARACTER SET charset_name] [COLLATE collation_name]

| MEDIUMTEXT [BINARY]中等的文本串

[CHARACTER SET charset_name] [COLLATE collation_name]

| LONGTEXT [BINARY]長的文本串

[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 空間的類型

1、創(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 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

........

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

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 |

| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |

| gbk_bin | gbk | 87 | | Yes | 1 |

| latin5_turkish_ci | latin5 | 30 | Yes | Yes | 1 |

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

197 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

lamp.err mysql-bin.000002 mysql-bin.000007 mysql-bin.000012 test

[root@lamp ~]# file /mydata/data/students/db.opt #查看students數(shù)據(jù)庫中db.opt文件類型

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

2、修改數(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ù)目錄

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

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

4、創(chuàng)建表：

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

CREATE TABLE testcourses SELECT * FROM courses WHERE CID <= 2;#從courses表中查找

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

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

CREATE TABLE new_table LIKE old_table; 以old_table表為模板，建立new_table表

查看表索引：

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)

5、修改表定義：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)

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

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 ;

Query OK, 0 rows affected (0.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

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)建，查找等功能的練習；

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

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: 1

Create_time: 2017-04-25 10:19:13

Update_time: NULL

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

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

Index_length: 1024

Data_free: 0

Auto_increment: 1

Create_time: 2017-04-25 10:51:45

Update_time: 2017-04-25 10:51:45

Check_time: NULL

Collation: gbk_chinese_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ERROR:

No query specified

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

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

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

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

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

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

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

1 row in set (0.00 sec)

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; #查看當前數(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)

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

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

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

ERROR:

No query specified

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

增加外鍵索引；

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 |

| test |

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

3 rows in set (0.00 sec)

mysql> INSERT INTO student (Name,CID) VALUES ('Li Lianjie',1),('Cheng Long',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 Lianjie | 1 |

| 2 | Cheng Long | 2 |

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

2 rows in set (0.00 sec)

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

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

| CID | Couse |

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

| 1 | physics |

| 2 | english |

| 3 | chemistry |

| 4 | maths |

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

4 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 Lianjie | physics |

| Cheng Long | english |

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

2 rows in set (0.00 sec)

mysql> DELETE FROM student WHERE SID > 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;

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

| SID | Name | CID |

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

| 1 | Li Lianjie | 1 |

| 2 | Cheng Long | 2 |

| 3 | Xiao Longnv | 3 |

| 4 | Yang Guo | 4 |

| 5 | Guo Xiang | 3 |

| 6 | Qiao Feng | 2 |

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

6 rows in set (0.00 sec)

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

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è)定student表下一條數(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)

看完了這篇文章，相信你對“MySQL如何管理創(chuàng)建CREATE表和索引”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！

名稱欄目：MySQL如何管理創(chuàng)建CREATE表和索引
URL標題：http://weahome.cn/article/jshihs.html

真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

MySQL如何管理創(chuàng)建CREATE表和索引

其他資訊

網(wǎng)站制作

企業(yè)服務(wù)

網(wǎng)站建設(shè)

服務(wù)器托管