溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下MySQL如何管理創建CREATE表和索引，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！
SQL語句：
數據庫
表
索引
視圖
DML語句
單字段：
PRIMARY KEY 主鍵
UNIQUE KEY 唯一鍵
單或者多字段：
PRIMARY KEY(col,...)
UNIQUE KEY(col,...)
INDEX(col,...)
數據類型：
data_type:
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)] 定長字符型
VARCHAR(length)變長字符型
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]二進制數
| VARBINARY(length)變長二進制數
| TINYBLOB 非常小的大對數
| BLOB 大對數
| MEDIUMBLOB 中等的大對數
| LONGBLOB 長的大對數
| 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、創建數據庫：
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 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
........
+----------+-----------------------------+---------------------+--------+
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 |
| 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';
#創建一個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
lamp.err mysql-bin.000002 mysql-bin.000007 mysql-bin.000012 test
[root@lamp ~]# file /mydata/data/students/db.opt #查看students數據庫中db.opt文件類型
/mydata/data/students/db.opt: ASCII text
2、修改數據庫：
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 #升級數據庫的數據目錄
3、刪除數據庫：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name #刪除數據庫
4、創建表：
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.從其他表中查詢出數據，并以之創建新表；
CREATE TABLE testcourses SELECT * FROM courses WHERE CID <= 2;#從courses表中查找
CID小于等于2的數據，并作為新建testcourses表的內容。
3.以其他表為模板創建一個空表；
CREATE TABLE new_table LIKE old_table; 以old_table表為模板，建立new_table表
查看表索引：
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)
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表的索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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; 查看表結構
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| CID | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| Course | varchar(50) | NO | MUL | NULL | |
+--------+---------------------+------+-----+---------+----------------+
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字段創建一個索引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 ;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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：創建一個students數據庫，以及表的創建，查找等功能的練習；
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> 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設定引擎為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課程字段數據，添加物理，英語，化學，數學等課程。
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表的索引
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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的數據，并把查找到的數據作為新建testcourses表的數據內容。
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SHOW TABLES; #查看當前數據庫表的信息
+--------------------+
| Tables_in_students |
+--------------------+
| courses |
| testcourses |
+--------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM testcourses; #查看testcourses表的內容
+-----+---------+
| CID | Couse |
+-----+---------+
| 1 | physics |
| 2 | english |
+-----+---------+
2 rows in set (0.00 sec)
mysql> DESC courses; #查看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)
mysql> DESC testcourses; #查看testcourses表結構
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| CID | tinyint(3) unsigned | NO | | 0 | |
| Couse | varchar(50) | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE test LIKE courses; #以courses表為模板創建test空表。
Query OK, 0 rows affected (0.00 sec)
mysql> DESC test; #查看test表結構
+-------+---------------------+------+-----+---------+----------------+
| 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)
mysql> SHOW TABLE STATUS LIKE 'test'\G; #查看test表的狀態
*************************** 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表，并進行相關數據的插入，查詢操作練習，修改引擎，修改字段修飾，
增加外鍵索引；
mysql> CREATE TABLE student (SID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,Name
VARCHAR(30),CID INT NOT NULL);#創建student表，包含3個字段，SID字段為無符號非空自動增長主鍵的整數型，Name字段為變長30字符，CID字符為非空整數型。
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條數據。
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字段內容
+------------+---------+
| 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;
+-------+---------------------+------+-----+---------+----------------+
| 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)
mysql> DESC student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| SID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(30) | YES | | NULL | |
| 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關聯courses表的CID字段。
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SHOW INDEXES FROM student; 查看student表的索引
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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;#設定student表下一條數據的自動增長主鍵SID
從5開始增長。
Query OK, 4 rows affected (0.05 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM student;#查詢表的內容
+-----+-------------+-----+
| 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);#插入一條數據，SID主鍵由于上面設置從5開始增長，所以剛插入的數據是從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);插入數據
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<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; #設定student表下一條數據的自動增長主鍵
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條數據
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如何管理創建CREATE表和索引”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
