mysql数据库第六章_MYSQL 第六章 表基本操作

修改选项的语法格式如下：

{ ADD COLUMN

| CHANGE COLUMN

| ALTER COLUMN { SET DEFAULT |DROP DEFAULT }| MODIFY COLUMN

| DROP COLUMN

| RENAME TO

| CHARACTER SET

| COLLATE }

修改表名

mysql>show tables;+-------------------+

| Tables_in_test_db |

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

| tb_tt1 |

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

1 row in set (0.00sec)

mysql>alter table tb_tt1 rename to tb_test; #ALTER TABLE RENAME [TO] ；

Query OK,0 rows affected (0.00sec)

mysql>show tables;+-------------------+

| Tables_in_test_db |

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

| tb_test |

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

1 row in set (0.00sec)

mysql>

修改表字符集

ALTER TABLE 表名 [DEFAULT] CHARACTER SET [DEFAULT] COLLATE ;

mysql>alter table tb_test character set gb2312 default collate gb2312_chinese_ci;

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql>show create table tb_test\G*************************** 1. row ***************************Table: tb_test

Create Table: CREATE TABLE `tb_test` (

`id` int(11) DEFAULT NULL,

`name` varchar(25) CHARACTER SET latin1 DEFAULT NULL,

`deptID`int(11) DEFAULT NULL,

`salary`floatDEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=gb23121 row in set (0.00 sec)

MySQL修改/删除字段

修改字段名称

MySQL 中修改表字段名的语法规则如下：

ALTER TABLE CHANGE ；

其中：

旧字段名：指修改前的字段名；

新字段名：指修改后的字段名；

新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

mysql>show tables;+-------------------+

| Tables_in_test_db |

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

| tb_test |

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

1 row in set (0.00sec)

mysql>show create table tb_test\G*************************** 1. row ***************************Table: tb_test

Create Table: CREATE TABLE `tb_test` (

`id` int(11) DEFAULT NULL,

`name` varchar(25) CHARACTER SET latin1 DEFAULT NULL,

`deptID`int(11) DEFAULT NULL,

`salary`floatDEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=gb23121 row in set (0.00sec)

mysql> alter table tb_test change id IDcard int(11);

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql>

修改字段数据类型

修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：

ALTER TABLE MODIFY

其中：

表名：指要修改数据类型的字段所在表的名称；

字段名：指需要修改的字段；

数据类型：指修改后字段的新数据类型。

mysql>show create table tb_test\G*************************** 1. row ***************************Table: tb_test

Create Table: CREATE TABLE `tb_test` (

`IDcard`int(11) DEFAULT NULL,

`name` varchar(30) DEFAULT NULL,

`deptID`int(11) DEFAULT NULL,

`salary`floatDEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=gb23121 row in set (0.00sec)

mysql> alter table tb_test modify name varchar(30);

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql>

删除字段

删除字段是将数据表中的某个字段从表中移除，语法格式如下：

ALTER TABLE DROP ；

其中，“字段名”指需要从表中删除的字段的名称。

mysql>show create table tb_test\G*************************** 1. row ***************************Table: tb_test

Create Table: CREATE TABLE `tb_test` (

`IDcard`int(11) DEFAULT NULL,

`name` varchar(30) DEFAULT NULL,

`deptID`int(11) DEFAULT NULL,

`salary`floatDEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=gb23121 row in set (0.00sec)

mysql>alter table tb_test drop salary;

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql>show create table tb_test\G*************************** 1. row ***************************Table: tb_test

Create Table: CREATE TABLE `tb_test` (

`IDcard`int(11) DEFAULT NULL,

`name` varchar(30) DEFAULT NULL,

`deptID`int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=gb23121 row in set (0.00sec)

mysql>

MySQL删除数据表(DORP TABLE语句)

在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。

下面我们来了解一下 MySQL 数据库中数据表的删除方法。

基本语法

使用 DROP TABLE 语句可以删除一个或多个数据表，语法格式如下：

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

对语法格式的说明如下：

表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。

IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告(warning)。

两点注意：

用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。

表被删除时，用户在该表上的权限不会自动删除。

删除表的实例

mysql>create table tb_emp3(-> id int(20),-> name varchar(30),-> deptID int(20),-> salary float);

Query OK,0 rows affected (0.06sec)mysql>show tables;+-------------------+

| Tables_in_test_db |

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

| tb_emp3 |

| tb_test |

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

2 rows in set (0.00sec)

mysql>drop table tb_emp3;

Query OK,0 rows affected (0.01sec)

mysql>

MySQL删除被其它表关联的主表

数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。

删除父表有以下两种方法：

先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。

将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

下面介绍了如何取消关联表的外键约束并删除主表，也就是上面所说的删除父表的第二种方法。

mysql>create table tb_emp4(-> id int(11),-> name varchar(30),-> deptID int(20) );

Query OK,0 rows affected (0.01sec)

mysql> alter table tb_emp4 modify id int(11) primary key;

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql>show create table tb_emp4\G*************************** 1. row ***************************Table: tb_emp4

Create Table: CREATE TABLE `tb_emp4` (

`id` int(11) NOT NULL,

`name` varchar(30) DEFAULT NULL,

`deptID`int(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00sec)

mysql>CREATE TABLE tb_emp5->(-> id INT(11) PRIMARY KEY,-> name VARCHAR(25),-> deptId INT(11),->salary FLOAT,-> CONSTRAINT fk_emp4_emp5 FOREIGN KEY (deptId) REFERENCES tb_emp4(id)->);

Query OK,0 rows affected (0.01sec)

mysql>show create table tb_emp4\G*************************** 1. row ***************************Table: tb_emp4

Create Table: CREATE TABLE `tb_emp4` (

`id` int(11) NOT NULL,

`name` varchar(30) DEFAULT NULL,

`deptID`int(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00sec)

mysql>show create table tb_emp5\G*************************** 1. row ***************************Table: tb_emp5

Create Table: CREATE TABLE `tb_emp5` (

`id` int(11) NOT NULL,

`name` varchar(25) DEFAULT NULL,

`deptId`int(11) DEFAULT NULL,

`salary`floatDEFAULT NULL,

PRIMARY KEY (`id`),

KEY `fk_emp4_emp5` (`deptId`),

CONSTRAINT `fk_emp4_emp5` FOREIGN KEY (`deptId`) REFERENCES `tb_emp4` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00sec)

mysql>drop table tb_emp4;

ERROR1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql>alter table tb_emp5 drop foreign key fk_emp4_emp5;

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql>drop table emp4;

ERROR1051 (42S02): Unknown table 'test_db.emp4'mysql>drop table tb_emp4;

Query OK,0 rows affected (0.00sec)

mysql>

DESCRIBE：以表格的形式展示表结构

mysql>desc tb_emp5;+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | NO | PRI | NULL | |

| name | varchar(25) | YES | | NULL | |

| deptId | int(11) | YES | MUL | NULL | |

| salary | float | YES | | NULL | |

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

4 rows in set (0.00sec)

mysql>

MySQL数据表添加字段(三种方式)

mysql>desc tb_emp5;+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | NO | PRI | NULL | |

| name | varchar(25) | YES | | NULL | |

| deptId | int(11) | YES | MUL | NULL | |

| salary | float | YES | | NULL | |

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

4 rows in set (0.00sec)

mysql>mysql>mysql>mysql>desc tb_test;+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| IDcard | int(11) | YES | | NULL | |

| name | varchar(30) | YES | | NULL | |

| deptID | int(11) | YES | | NULL | |

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

3 rows in set (0.01sec)

mysql> alter table tb_test add age int(4); #默认末尾添加

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc tb_test;+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| IDcard | int(11) | YES | | NULL | |

| name | varchar(30) | YES | | NULL | |

| deptID | int(11) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

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

4 rows in set (0.00sec)

mysql> alter table tb_test add stuID int(10) first; #在开头添加测试字段

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc tb_test;+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| stuID | int(10) | YES | | NULL | |

| IDcard | int(11) | YES | | NULL | |

| name | varchar(30) | YES | | NULL | |

| deptID | int(11) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

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

5 rows in set (0.00sec)

mysql> alter table tb_test add man varchar(10) after name; #在中间添加字段

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql>desc tb_test;+--------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| stuID | int(10) | YES | | NULL | |

| IDcard | int(11) | YES | | NULL | |

| name | varchar(30) | YES | | NULL | |

| man | varchar(10) | YES | | NULL | |

| deptID | int(11) | YES | | NULL | |

| age | int(4) | YES | | NULL | |

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

6 rows in set (0.00sec)

mysql>