Linux命令:MySQL系列之四--MySQL管理创建CREATE表和索引

  1. 云栖社区>
  2. 博客>
  3. 正文

Linux命令:MySQL系列之四--MySQL管理创建CREATE表和索引

技术小甜 2017-11-15 16:38:00 浏览770
展开阅读全文

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)















本文转自wang650108151CTO博客,原文链接:http://blog.51cto.com/woyaoxuelinux/1919659 ,如需转载请自行联系原作者



网友评论

登录后评论
0/500
评论
技术小甜
+ 关注