02 MySQL之数据表的基本操作

01-创建数据表

# 切换数据库

use  test_db;

# 创建数据表

语法规则如下：

create table  表名 (

    字段名1, 数据类型 [列级别约束条件] [默认值],

    字段名2, 数据类型 [列级别约束条件] [默认值],

    ......

    [表级别约束条件]

);

注意：

    1、要创建的表名称，不区分大小写，不能使用SQL关键字。

    2、数据表中创建多列，要用 逗号 隔开。

例：

create table test_db (

    id  INT(11),

    name  VARCHAR(25),

    deptID  INT(11),

    salary  FLOAT

);

# 查看数据表是否创建成功

show tables;

1.1 主键约束

主键约束要求主键列的数据唯一，并且不允许为空。主键分为两种类型：单子段主键和多字段联合主键。

# 单字段主键

1、语法规则如下：

字段名  数据类型  PRIMARY KEY  [默认值]

create table test_db (

    id  INT(11)  PRIMARY KEY,

    name  VARCHAR(25),

    deptID  INT(11),

    salary  FLOAT

);

或

create table test_db (

    id  INT(11),

    name  VARCHAR(25),

    deptID  INT(11),

    salary  FLOAT,

    PRIMARY KEY(id)

);

# 多字段主键

1、语法规则如下：

PRIMARY KEY (字段1, 字段2, 字段3, ....)

例：

create table test_db (

    name  VARCHAR(25),

    deptId  INT(11),

    salary  FLOAT,

    PRIMARY KEY(name, deptId)

);

1.2 外键约束

外键用来在两个表的数据之间建立链接，可以是一列或者多列。一个表可以有多个外键，外键可以是空值，若不为空值，则每个外键值必须等于另一个表中主键的某个值。

外键的作用是保持数据的一致性和完整性。

外键的语法规则如下：

constraint  外键名  foreign  key  字段名1  references  主表名  主键列1

外键名：为定义的外键约束名称，一个表中不能有相同的外键名称；

字段名：当前表需要添加外键的字段列；

主表名：被子表外键所依赖的表的名称；

主键列：主表中定义的主键列 或 主键组合。

例：

create  table  tb_dept1 (

    id  INT(11)  PRIMARY KEY,

    name  VARCHAR(22) NOT NULL,

    location  VARCHAR(50)

);

CREATE TABLE tb_dept5 (

    id  INT(11)  PRIMARY KEY,

    name  VARCHAR(25),

    deptId  INT(11),

    salary  FLOAT,

    CONSTRAINT  fk_emp_dept1  FOREIGN KEY (deptId)  REFERENCES  tb_dept1(id)

);

1.3 非空约束

非空约束(NOT NULL)是指字段的值不能空。

语法规则如下：

字段名  数据类型  NOT NULL

1.4 唯一性约束

唯一性约束(Unique Constraint)要求该列唯一，允许为空，但只能出现一次空值。

语法规则如下：

字段名  数据类型  UNIQUE

create  table  tb_dept1 (

    id  INT(11)  PRIMARY KEY,

    name  VARCHAR(22) UNIQUE,

    location  VARCHAR(50)

);

# 或

create  table  tb_dept1 (

    id  INT(11)  PRIMARY KEY,

    name  VARCHAR(22),

    location  VARCHAR(50),

    CONSTRAINT  SHT  UNIQUE(name)

);

UNIQUE 与 PRIMARY KEY的区别：

一个表中可以有多个字段声明为UNIQUE，但只能有一个 PRIMARY KEY。

声明为 PRIMARY KEY 的列 不能有空值，但声明为 UNIQUE 的字段允许为空值(NULL)的存在。

1.5 默认约束

指定某列的默认值。DEFAULT

create  table  tb_dept4 (

    id  INT(11)  PRIMARY KEY,

    deptId  INT(11)  DEFAULT 1234,

    name  VARCHAR(22) UNIQUE,

    location  VARCHAR(50)

);

1.6 设置表的属性值自动增加

希望系统自动生成字段的主键值。AUTO_INCREMENT 关键字

create  table  tb_dept4 (

    id  INT(11)  PRIMARY KEY  AUTO_INCREMENT,

    name  VARCHAR(22) NOT NULL,

    location  VARCHAR(50)

);

1.7 查看数据表的结构

# 查看数据表的基本结构语句  DESCRIBE/DESC

DESCRIBE  表名;

或简写为：

DESC  表名;

mysql> desc tb_dept1;

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

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

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

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

| name     | varchar(22) | NO   |     | NULL    |       |

| location | varchar(50) | YES  |     | NULL    |       |

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

3 rows in set (0.01 sec)

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

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

# 语法格式如下：

SHOW  CREATE  TABLE  表名\G;

例：

mysql> SHOW CREATE TABLE tb_dept1\G;

*************************** 1. row ***************************

       Table: tb_dept1

Create Table: CREATE TABLE `tb_dept1` (

  `id` int(11) NOT NULL,

  `name` varchar(22) NOT NULL,

  `location` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

02-修改数据表

MySQL使用 ALTER TABLE 语句修改表。

常用修改表的操作有：修改表名、修改字段名或字段数据类型、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

2.1 修改表名

语法规则如下：

ALTER  TABLE  旧表名  RENAME  TO  新表名;

例：

mysql> show tables;

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

| Tables_in_test_db |

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

| tb_dept1          |

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

1 row in set (0.00 sec)

mysql> ALTER TABLE tb_dept1 RENAME TO tb_deptment1;

Query OK, 0 rows affected (0.03 sec)

mysql> show tables;

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

| Tables_in_test_db |

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

| tb_deptment1      |

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

1 row in set (0.00 sec)

2.2 修改字段的数据类型

语法规则如下：

ALTER  TABLE  表名  MODIFY   字段名  数据类型

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

字段名：修改数据类型的字段；

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

ALTER  TABLE  tb_dept1  MODIFY  name  VARCHAR(30);

2.3 修改字段名

语法规则如下：

ALTER  TABLE  表名  CHANGE  旧字段名  新字段名  新数据类型;

2.4 添加字段

语法规则如下：

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

FIRST 和 AFTER 是可选参数；

FIRST：将新添加字段设置为表的第一个字段；

AFTER：将新添加字段添加到指定的'已存在字段名'的后面。

# 添加无完整性约束条件字段

ALTER TABLE tb_dept1 ADD managerId INT(10);

# 添加完整性约束条件的字段

ALTER TABLE tb_dept1 ADD memo VARCHAR(50) not null;

# 在表的第一列添加一个字段

ALTER TABLE tb_dept1 ADD column1 INT(10) FIRST;

# 在表的指定列之后添加一个字段

ALTER TABLE tb_dept1 ADD column2 INT(10) ALTER name;

2.5 删除字段

语法规则如下：

ALTER  TABLE  表名  DROP  字段名;

2.6 修改字段的排列位置

语法格式如下：

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

2.7 更改表的存储引擎

语法格式：

ALTER TABLE 表名 ENGINE=更改后的存储引擎名;

2.8 删除表的外键约束

语法格式：

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

外键约束名：指在定义表时 CONSTRAINT 关键字后面的参数

03-删除数据表

# 1、删除没有被关联的表

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

例：DROP TABLE IF EXISTS tb_dept1;

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

数据存在外键关联的情况下，如果直接删除父表，结果会显示失败。

如果必须删除，可以先删除与它关联的子表，再删除父表。

但有的情况下，可能要保留子表，如要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表。

具有 fk_emp_dept 的外键约束 为 子表tb_empt，父表tb_empt2的id被主表关联。

例：

ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_empt;

DROP TABLE tb_empt2;

秒客网