zzzzy09

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
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.00 sec)

mysql> alter table tb_tt1 rename to tb_test;    #ALTER TABLE <旧表名> RENAME [TO] <新表名>；
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_test           |
+-------------------+
1 row in set (0.00 sec)

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.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

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` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312
1 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.00 sec)

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` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

mysql> alter table tb_test change id IDcard int(11);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

 

修改字段数据类型

修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 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` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

mysql> alter table tb_test  modify name varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

删除字段

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

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` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

mysql> alter table tb_test drop salary;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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
) ENGINE=MyISAM DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

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.06 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_emp3           |
| tb_test           |
+-------------------+
2 rows in set (0.00 sec)

mysql> drop table tb_emp3;
Query OK, 0 rows affected (0.01 sec)

mysql>

 

 

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

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

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

  • 先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
  • 将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。


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

mysql> create table tb_emp4( 
    -> id int(11),
    -> name varchar(30),
    -> deptID int(20) );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table tb_emp4 modify id int(11) primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

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=latin1
1 row in set (0.00 sec)

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.01 sec)

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=latin1
1 row in set (0.00 sec)

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` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp4_emp5` (`deptId`),
  CONSTRAINT `fk_emp4_emp5` FOREIGN KEY (`deptId`) REFERENCES `tb_emp4` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop table tb_emp4;
ERROR 1217 (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.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table emp4;
ERROR 1051 (42S02): Unknown table 'test_db.emp4'
mysql> drop table tb_emp4;
Query OK, 0 rows affected (0.00 sec)

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.00 sec)

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.00 sec)

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.01 sec)

mysql> alter table tb_test add age int(4);   #默认末尾添加
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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    |       |
| age    | int(4)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table tb_test add stuID int(10) first;    #在开头添加测试字段
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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.00 sec)

mysql> alter table tb_test add man varchar(10) after name;  #在中间添加字段
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 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.00 sec)

mysql>

 

posted on 2020-06-04 17:28  zzzzy09  阅读(199)  评论(0)  编辑  收藏  举报