mysql06-数据表的基本操作

1、创建数据表

• 在数据库中，数据表是数据库中最重要、最基本的操作对象，是数据存储的基本单位。
• 数据表被定义为列的集合，数据在表中是按照行和列的格式来存储的。
• 每一行代表一条唯一的记录，每一列代表记录中的一个域。

1、创建表的语法形式

• 数据表属于数据库，在创建数据表之前，应该使用语句“USE <数据库名>”指定操作是在哪个数据库，如果没有选择数据库，会抛出"No database selected"的错误。

• 基本语法格式如下：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件]
);

• 使用CREATE TABLE创建表时，必须指定以下信息:
  • 要创建的表的名称，不区分大小写，不能使用SQL语言中的关键字，如DROPALTER， INSERT等。
  • 数据表中每一个列(字段)的名称和数据类型，如果创建多个列，要用逗号隔开。
• 创建表时，指定存储引擎和字符集：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件]
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

示例：

mysql> create database db1;                           --创建数据库
mysql> use db1;                                       --选择使用数据库
mysql> create table tb11 (nid int, name char(10));    --创建数据表
mysql> show tables;                                   --查看数据表
+---------------+
| Tables_in_db1 |
+---------------+
| tb11          |
+---------------+

2、默认约束

• 默认约束(Default Constraint)指定某列的默认值。

• 在定义完列之后直接指定默认约束，基本语法格式如下：

字段名 数据类型 DEFAULT 默认值    --默认约束，使用列级约束定义

示例：

create table tb_emp1(
    id int(10) primary key,
    name char(20),
    deptid int(10) default 1
);

3、非空约束

• 非空约束(Not Null Constraint)指字段的值不能为空。对于使用了非空约束的字段,如果用户在添加数据时没有指定值，数据库系统会报错。

• 在定义列之后直接指定非空约束，基本语法格式如下：

字段名 数据类型 not nul1    --非空约束，使用列级约束定义

示例：

create table tb_emp2(
    id int(10) primary key,
	name char(20) not null,
	deptid int(10)
);

4、设置表的属性值自动增加

• 默认的，在MySQL中AUTO_NCREMENT的初始值是1，每新增一条记录，字段值自动加1。
• 一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须是主键的一部分。
• AUTO_INCREMENT约束的字段可以是任何整数型(TINYINT, SMALLIN, INT, BIGINT等) 。

• 基本语法格式如下：

字段名 数据类型 AUTO_INCREMENT

• 设置自增的初始值和步长

show session variables like 'auto_inc%';    --查看自增的初始值和步长
set session auto_increment_increment=2;     --设置自增的初始值
set session auto_increment_offset=10;       --设置自增步长

示例：

create table tb_emp3(
    id int(10) primary key auto_increment,
    name char(20),
    deptid int(10) default 1
);

5、主键约束

• 主键，又称主码，是表中一列或多列的组合。
• 主键约束(Primary Key Constraint)要求主键列的数据唯一，并且不为空。
• 主键能够唯一地标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。
• 主键和记录之间的关系如同身份证和人之间的关系，是一一对应的。
• 添加主键约束时，往往需要设置字段自动增加属性。
• 主键分为两种类型：单字段主键和多字段联合主键。

1、单字段主键

• 在定义列之后直接指定主键，基本语法格式如下：

字段名 数据类型 PRIMARY KEY                    --主键约束，使用列级约束定义

• 在定义完所有列之后指定主键，基本语法格式如下：

[CONSTRAINT <约束名>] PRIMARY KEY([字段名])    --主键约束，使用表级约束定义

示例1：列级约束

create table tb_test1(
    nid int(11) primary key,
    name char(20)
);

示例2：表级约束

create table tb_test2(
    nid int(11),
    name char(20),
    primary key(nid)    --字段要使用小括号
);

2、多字段联合主键

• 主键由多个字段联合组成。

• 在定义完所有列之后指定联合主键，基本语法格式如下：

PRIMARY KEY([字段1, 字段2, ... ,字段n])        --主键约束，使用表级约束定义

示例：

create table tb_test3(
    nid int(11),
    name char(20),
    primary key(nid, name)
);

6、唯一性约束

• 唯一性约束(Unique Constraint)要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

• 在定义完列之后直接指定唯一约束，基本语法格式如下：

字段名 数据类型 UNIQUE                    --唯一性约束，使用列级约束定义

• 在定义完所有列之后指定唯一约束，基本语法格式如下：

[CONSTRAINT <约束名>] UNIQUE(<字段名>)    --唯一性约束，使用表级约束定义

示例1：列级约束

create table tb_emp4(
    id int(10) primary key,
    name char(20) unique,
    deptid int(10)
);

示例2：表级约束

create table tb_emp5(
    id int(10) primary key,
	name char(20),
	deptid int(10),
	unique(name)
);

7、外键约束

• 外键用来在两个表的数据之间建立链接，它可以是一列或者多列。
• 一个表可以有一个或多个外键。
• 外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
• 外键：首先它是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外键主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表(主表)中具有关联关系的行。外键的作用是保持数据的一致性、完整性。

• 主表(父表)：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。（被引用的表）
• 从表(子表)：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。
• 子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时，就会出现错误"ERROR 1005 (HY000): Can't create table 'database.tablename'(errno: 150)" 。

• 外键约束不能跨存储引擎。如果两个表指定了不同的存储引擎，那么这两个表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的。

• 在定义完所有列之后指定外键约束，基本语法格式如下：

[CONSTRAINT <外健名>] PORETGN KEY 字段名1 [,字段名2 ...] REFERENCES <主表名>(主健列1, [主健列2,...])    --外键约束，使用表级约束定义

• • 外键名：为定义的外键约束的名称，一个表中不能有相同名称的外键。
  • 字段名：表示子表需要添加外键约束的字段列。
  • 主表名：即被子表外键所依赖的表的名称。 
  • 主键列：表示主表中定义的主键列，或者列组合。

示例：一个员工属于一个部门

--创建部门数据表
create table tb_department(
    id int(10) primary key,
    name char(20) not null
	
);

--创建员工数据表
create table tb_employee(
    id int(10) primary key,
    name char(20),
    dep_name int(10),
    constraint fk_emp_dep foreign key(dep_name) references tb_department(id)
);

2、查看数据表

1、查看当前数据库所有数据表

show tables; 

示例：

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb_emp1       |
| tb_emp2       |
| tb_emp3       |
| tb_emp4       |
+---------------+

2、查看表基本结构语句DESCRIBE

• DESCRIBE/DESC语句可以查看表的字段信息，其中包括：字段名、字段数据类型、是否为空、是否为主键、是否有默认值等。

DESCRIBE 表名;

DESC 表名;        --DESCRIBE的简写

示例：

mysql> desc tb_emp3;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(10)  | NO   | PRI | NULL    | auto_increment |
| name   | char(20) | YES  |     | NULL    |                |
| deptid | int(10)  | YES  |     | 1       |                |
+--------+----------+------+-----+---------+----------------+

• NULL：表示该列是否可以存储NULL值。
• Key：表示该列是否已编制索引。
  • PRI表示该列是表主键的一部分;
  • UNI表示该列是UNIQUE索引的一部分;
  • MUL表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有的话值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，如AUTO_INCREMENT等。

3、查看表详细结构语句SHOW CREATE TABLE

• SHOW CREATE TABLE语句可以用来显示创建表时的CREATE TABLE语句

• 基本语法格式如下：

SHOW CREATE TABLE <表名\G>

示例：

mysql> SHOW CREATE TABLE tb_emp3\G
*************************** 1. row ***************************
       Table: tb_emp4
Create Table: CREATE TABLE `tb_emp4` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `deptid` int(10) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3、修改数据表

• 修改表指的是修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表。

1、修改表名

• 基本语法格式如下：

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

示例：

mysql> alter table tb_emp3 rename tb3;

2、修改字段名和数据类型

• 修改字段名，基本语法格式如下：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;    --修改字段名

• • “旧字段名”指修改前的字段名。
  • “新字段名”指修改后的字段名。 “新数据类型”指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样即可，但数据类型不能为空。
• 修改字段的数据类型，基本语法格式如下：

ALTER TABLE <表名> MODIFY <字段名> <数据类型>;                 --修改数据类型

• • 表名：指要修改数据类型的字段所在表的名称。
  • 字段名：指需要修改的字段。
  • 数据类型：指修改后字段的新数据类型。

• CHANGE也可以只修改数据类型，实现和MODIFY同样的效果，方法是将SQL语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。
• 由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响到数据表中已有的数据记录。因此，当数据库表中已经有数据时，不要轻易修改数据类型。

示例：

mysql> desc tb3;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(10)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| deptid | int(10)  | YES  |     | 1       |       |
+--------+----------+------+-----+---------+-------+

mysql> ALTER TABLE tb3 CHANGE name name_new varchar(25);    --使用change，同时修改字段名和数据类型
mysql> ALTER TABLE tb3 CHANGE id id_new int(10);            --使用change，只修改字段名
mysql> ALTER TABLE tb3 CHANGE deptid deptid char(10);       --使用change，只修改数据类型
mysql> desc tb3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_new   | int(10)     | NO   | PRI | NULL    |       |
| name_new | varchar(25) | YES  |     | NULL    |       |
| deptid   | char(10)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

mysql> ALTER TABLE tb3 MODIFY deptid int(10);                --使用modify，修改数据类型
mysql> desc tb3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id_new   | int(10)     | NO   | PRI | NULL    |       |
| name_new | varchar(25) | YES  |     | NULL    |       |
| deptid   | int(10)     | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

3、添加和删除字段

• 添加字段，基本语法格式如下：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名];

• • 新字段名：为需要添加的字段的名称。
  • FIRST：为可选参数，其作用是将新添加的字段设置为表的第一个字段。
  • AFTER：为可选参数，其作用是将新添加的字段添加到指定的"已存在字段名”的后面。
  • 如果SQL语句中没有"FIRST"或"AFTER已存在字段名”这两个参数,则默认将新添加的字段设置为数据表的最后列。
• 删除字段，基本语法格式如下：

ALTER TABLE <表名> DROP <字段名>;

示例1：添加字段

mysql> ALTER TABLE tb_emp1 ADD hh1 char(20);               --添加无完整性约束条件的字段
mysql> ALTER TABLE tb_emp1 ADD hh2 char(20) not null;      --添加有完整性约束条件的字段
mysql> ALTER TABLE tb_emp1 ADD hh3 char(20) FIRST;         --在表的第一列添加一个字段
mysql> ALTER TABLE tb_emp1 ADD hh4 char(20) AFTER name;    --在表的指定列之后添加一个字段

mysql> desc tb_emp1;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| hh3    | char(20) | YES  |     | NULL    |       |
| id     | int(10)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  | UNI | NULL    |       |
| hh4    | char(20) | YES  |     | NULL    |       |
| deptid | int(10)  | YES  |     | NULL    |       |
| hh1    | char(20) | YES  |     | NULL    |       |
| hh2    | char(20) | NO   |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

示例2：删除字段

mysql> ALTER TABLE tb_emp1 DROP hh4;

4、修改字段的排列位置

• 基本语法格式如下：

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

• • 字段1：指要修改位置的字段。
  • 数据类型：指“字段1”的数据类型。
  • FIRST：为可选参数，指将“字段1”修改为表的第一个字段。
  • AFTER 字段2：指将“字段1”插入到“字段2”后面。

示例：

mysql> desc tb_emp1;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| hh3    | char(20) | YES  |     | NULL    |       |
| id     | int(10)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  | UNI | NULL    |       |
| deptid | int(10)  | YES  |     | NULL    |       |
| hh1    | char(20) | YES  |     | NULL    |       |
| hh2    | char(20) | NO   |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

mysql> ALTER TABLE tb_emp1 MODIFY name varchar(20) FIRST;     --修改字段为表的第一个字段
mysql> ALTER TABLE tb_emp1 MODIFY id int(10) AFTER deptid;    --修改字段到表的指定之后

mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | YES  | UNI | NULL    |       |
| hh3    | char(20)    | YES  |     | NULL    |       |
| deptid | int(10)     | YES  |     | NULL    |       |
| id     | int(10)     | NO   | PRI | NULL    |       |
| hh1    | char(20)    | YES  |     | NULL    |       |
| hh2    | char(20)    | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

5、更改表的存储引擎

• 更改表的存储引擎，基本语法格式如下：

ALTER TABLE <表名> ENGINE=<更改后的存储引攀名>;

• 查看系统所支持的引擎类型

mysql> SHOW ENGINES;    --查看系统所支持的引擎类型
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

示例：

mysql> show create table tb3\G           --查看tb3表的创建声明
*************************** 1. row ***************************
       Table: tb3
Create Table: CREATE TABLE `tb3` (
  `id_new` int(10) NOT NULL,
  `name_new` varchar(25) DEFAULT NULL,
  `deptid` int(10) DEFAULT NULL,
  PRIMARY KEY (`id_new`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> ALTER TABLE tb3 ENGINE=MyISAM;    --更改存储引擎

mysql> show create table tb3\G           --查看tb3表的创建声明
*************************** 1. row ***************************
       Table: tb3
Create Table: CREATE TABLE `tb3` (
  `id_new` int(10) NOT NULL,
  `name_new` varchar(25) DEFAULT NULL,
  `deptid` int(10) DEFAULT NULL,
  PRIMARY KEY (`id_new`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

6、删除表的外键约束

• 对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

• 基本语法格式如下：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

示例：

--创建部门数据表
create table tb_department(
    id int(10) primary key,
    name char(20) not null
     
);
--创建员工数据表
create table tb_employee(
    id int(10) primary key,
    name char(20),
    dep_name int(10),
    constraint fk_emp_dep foreign key(dep_name) references tb_department(id)
);

mysql> ALTER TABLE tb_employee DROP FOREIGN KEY fk_emp_dep;    --删除外键约束

4、删除数据表

• 在进行删除操作前，最好对表中的数据做个备份，以免造成无法挽回的后果。
• 清空数据表的方式：
  • TRUNCATE TABLE直接删除原来的表，并重新创建一个表。TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。

delete from table_name;       --清空表内容
truncate table table_name;    --清空表内容,速度快,自增回到原点

1、删除没有被关联的表

• 基本语法格式如下：

DROP TABLE [IF EXISTS] 表1， 表2， ...， 表n;

• • 表n：指要删除的表的名称，后面可以同时删除多个表。如果要删除的数据表不存在，则MySQL会提示一条错误信息，"ERROR 1051 (42S02): Unknown table'表名"。
  • IF EXISTS：用于在删除前判断删除的表是否存在，加上该参数后，再删除表的时候，如果表不存在， SQL语句可以顺利执行，但是会发出警告(warning)。

示例：

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb3           |
| tb4           |
| tb_department |
| tb_emp1       |
| tb_emp2       |
| tb_employee   |
+---------------+

mysql> DROP TABLE IF EXISTS tb3, tb4, tb5;    --删除数据表，注意tb5是不存在的
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb_department |
| tb_emp1       |
| tb_emp2       |
| tb_employee   |
+---------------+

2、删除被其他表关联的主表

• 数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败。原因是直接删除，将破坏表的参照完整性。

• 删除父表有两种方式：
  • 先删除与它关联的子表（即删除了外键约束），再删除父表。
  • 将关联父表的外键约束条件取消，然后就可以删除父表。

示例：

--创建部门数据表
create table tb_department(
    id int(10) primary key,
    name char(20) not null
     
);
--创建员工数据表
create table tb_employee(
    id int(10) primary key,
    name char(20),
    dep_name int(10),
    constraint fk_emp_dep foreign key(dep_name) references tb_department(id)
);

mysql> drop table tb_department;                               --直接删除主表，报错
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

mysql> ALTER TABLE tb_employee DROP FOREIGN KEY fk_emp_dep;    --删除外键约束

mysql> drop table tb_department;                               --删除外键约束后，再删除主表
Query OK, 0 rows affected (0.00 sec)