MySQL数据表的基本操作

时间:2021-11-01 04:48:52

目标:掌握创建数据表的方法。

      掌握查看数据表结构的方法。

      掌握修改数据表的方法。

      掌握数据表的其他操作的方法。

      掌握删除数据表的方法。

一、创建数据表

   在创建数据库之后,接下来就要在数据库中创建数据表。所谓创建数据表,指的是在已经创建的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程,同时也是实施数据完整性(包括实体完整性,引用完整性和域完整性)约束的过程。

1、创建数据表的语法形式

  CREATE TABLE  <表名>

   

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

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

......

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

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

[表级别约束条件]

1)创建数据库create database mybase

2)选择数据库 use mybase;

3)创建数据表db_1;创建语句如下:

   Create table db_1

   (

   Id      INT(11),

   Name   VARCHAR(25),

   Sex     BOOLEAN,

   Salary   FLOAT

  );

 执行过程如下图所示:

 MySQL数据表的基本操作

4)创建数据表db_1完成后,使用show tables;(不能缺少s)语句查看数据表是否创建成功,执行结果如图所示。

 MySQL数据表的基本操作

查询结果可以看到,数据表db_1创建成功,数据库mybase中已经有数据表db_1

2、主键约束

 主键又称主码,是表中一列或多列的组合。主键约束(Primary Key Constraint)要求主键列的数据唯一,并且不允许为空。主键是能够唯一标识表中的一条记录,可以结合外键来定义不同数据表之间的关系,并且可以加快数据库查询的速度。主键分为两种类型,即单字段主键和多字段主键。

(1)单字段主键

 主键由一个字段组成,设置主键的SQL语句格式分为两种情况。

1)、在定义列的同时指定主键,语法格式如下:

字段名 数据类型 primary key[默认值]

如创建数据表db_2,其主键为ID

Create table db_2

   (

   Id      INT(11)  primary key,

   Name   VARCHAR(25),

   Sex     BOOLEAN,

   Salary   FLOAT

  );

MySQL数据表的基本操作

2) 、在定义完所有的列之后指定主键,语法格式如下:

[CONSTRAINT<约束名>] primary key [字段名]

  如创建数据表db_3,其主键为IDSQL语句如下:

Create table db_3

   (

   Id      INT(11),

   Name   VARCHAR(25),

   Sex     BOOLEAN,

   Salary   FLOAT,

   primary   key(id)

  );

 MySQL数据表的基本操作

上面两种方法执行的结果是一样的,都会在ID字段设置主键约束。

(2)多字段主键

 主键由多个字段联合组成,语法格式如下:

 Primary key[字段1,字段2,字段3......,字段n]

 如定义数据表db_4,假设表中没有主键ID,为了唯一确定一个员工,可以把namesex联合起来作为主键。SQL语句如下:

Create table db_4

   (

   Id      INT(11),

   Name   VARCHAR(25),

   Sex     BOOLEAN,

   Salary   FLOAT,

   primary   key(name,sex)

  );

 MySQL数据表的基本操作

3、外键约束

外键用来在两个表数据之间建立连接,它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性,一个表的的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中主键的某个值。外键的主要作用是保证数据引用的完整性,在定义外键后不允许删除在另一个表中具有关联关系的行。外键还保证数据的一致性、完整性。例如部门表tb_dept的主键ID,在员工表db_5中有一个键deptID与这个ID关联。

主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的那个表即是主表。

从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的那个表即是主表。

如定义数据表db_5,并且在该表中创建外键约束。

创建外键的语法格式如下:

[COMSTRAINT<外键名>]FOREIGN KEY 字段名1[,字段名2......]

REFERENCES<主表名> 主键列1[,主键列2......]

创建一个部门表tb_dept1,建表的SQL语句如下:

Create table tb_dept1

   (

   Id      INT(11)  primary key,

   Name   VARCHAR(22)  NOT Null,

   Location  VARCHAR(50)

  );

定义数据表db_5,让deptID字段作为外键关联到tb_dept1表的主键IDSQL语句如下:

Create table db_5

   (

   Id      INT(11)  primary key,

   Name   VARCHAR(25),

   deptId   INT(11),

   Salary   FLOAT,

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

  );

 以上语句执行成功后在db_5表上添加了名称为fk_emp_dept1的外键约束,外键名称为deptId,其依赖于tb_dept1表的主键id

4、非空约束

 非空约束指字段的值不能为空,使用此约束后,添加数据时没有指定值,数据库系统会报错,非空约束语法格式如下:

字段名 数据类型 NOT NULL

如定义数据表db_6,指定员工的性别不能为空,SQL语句如下:

Create table db_6

   (

   Id      INT(11)  primary key,

   Name   VARCHAR(25) ,

   Sex     BOOLEAN NOT NULL

  );

执行后,数据库创建db_6数据表创建了一个sex字段,其插入的值不能为空。

5、唯一性约束

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

   在定义完列之后指定唯一性约束,语法格式如下:

   字段名 数据类型 UNIQUE

如定义数据表db_7,指定name字段唯一,SQL语句如下:

Create table db_7

   (

   Id      INT(11)  primary key,

   Name   VARCHAR(22)  UNIQUE,

   Sex     BOOLEAN NOT NULL,

   Age     INT(4)

  );

6、默认约束

 默认约束(Default Constraint)指定某列的默认值。例如,用户列表中的北京人比较多,就可以设置city字段的默认值为“北京”。如果插入一条新纪录时没有为这个字段赋值,那么系统会自动为这个字段赋值“北京”。

  默认约束的语法格式如下:

  字段名 数据类型 DEFAULT 默认值

  如定义数据表db_8,指定员工的城市默认值为“北京”(北京需要用双引号括起来,主要和数据库的版本有关系)SQL语句如下:

 Create table db_8

   (

   Id      INT(11)  primary key,

   Name   VARCHAR(25)  UNIQUE,

   City    VARCHAR(20)  DEFAULT “北京”

  );

1、自增属性

  MySQL数据库设计中会遇到需要系统自动生成字段的主键值得情况。例如用户表中需要id字段自增,需要使用AUTO_INCREMENT关键字来实现。

 属性值自动增加的语法格式如下:

 字段名 数据类型 AUTO_INCREMENT

如定义数据表db_9,指定员工的编号自动增加,SQL语句如下:

Create table db_9

   (

   Id      INT(11)  primary key  AUTO_INCREMENT,

   Name   VARCHAR(25)  UNIQUE,

   City    VARCHAR(20)

  );

 以上语句执行后会创建名称为db_9的数据表,表中的id字段值在添加记录的时候回自动增加,id字段值默认从1开始,每次添加一条新纪录,改制自动加1

 

二、查看数据表结构

1、查看数据表基本结构   使用DESCRIBE/DESC语句可以查看表字段信息,包括字段名、字段数据类型、是否为主键、是否有默认值等,其语法格式如下:

 DESCRIBE/DESC <表名>

 如查看db_2表的结构,SQL语句:DESCRIBE/DESC db_2;

 MySQL数据表的基本操作

 上图中各个字段的含义分别如下:

NULL:表示该列是否可以存储NULL值。

Key:表示该列是否已编制索引。PRI表示该列是表主键的一部分;UNI表示该列是UNIQUE(唯一性约束)索引的一部分;MUL表示在该列表中某个给定值允许出现多次。

Default:表示该列是否有默认值,如果有,值是多少。

Extra:表示可以获取的与给定列有关的附加信息,如AUTO_INCREMENT等。

2、查看数据表详细结构

SHOW CREATE TABLE语句可以用来查看表的详细信息,语法格式如下:

SHOW CREATE TABLE<表名\G>;

如查看db_1表的详细信息,SQL语句如下:

 SHOW CREATE TABLE db_1\G 执行结果如下图

 MySQL数据表的基本操作

三、修改数据表

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

1、修改表名

MySQL是通过ALTER TABLE语句来实现表名的修改的,具体语法格式如下:

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

其中,To为可选参数,使用与否不影响结果。

例如将数据表db_1改名为tb_new.

操作步骤:

查看数据库中所有表:SHOW TABLES

    使用ALTER TABLEdb_1表名修改为tb_new,SQL语句如下:

ALTER TABLE db_1 RENAME tb_new;

操作结果执行完成后,在下图中对比结果发现数据表db_1已经没有了,而多出来了tb_new这个数据表。修改表名成功。

 MySQL数据表的基本操作

2、修改字段数据类型

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

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

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

例如:将tb_newname字段的数据类型由VARCHAR(25)修改成VARCHAR(28)

修改数据类型的步骤如下:

1)、查看tb_new表的结构;

2)、执行修改语句ALTER TABLE tb_new MODIFY name VARCHAR(28);

3)、再次查看表结构。

 执行结果如下图,tb_new表中name字段的数据类型已经修改为VARCHAR28),修改成功。

 MySQL数据表的基本操作

3、修改字段名

 MySQL中修改表字段名的语法格式如下:

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

其中,“旧字段名”指修改前的字段名;“新字段名”指修改后的字段名;“新数据类型”指修改后的数据类型,如果不需要修改字段的数据类型,可以将新数据类型设置成原来的一样,但数据类型不能为空。

例如:将数据表tb_newname字段的名称修改为newnameSQL语句如下:

ALTER TABLE tb_new CHANGE name newname VARCHAR(28)

 MySQL数据表的基本操作

从结果可以看出,name字段的名称已经修改为newname

提示:由于不同类型的数据在计算机中存储的方式及长度并不相同,修改数据类型可能会影响到数据表中已有的数据记录,因此当数据库中已经有数据时不要轻易地修改数据类型。

4、添加字段

添加字段的语法格式如下:

ALTER TABLE<表名>ADD<新字段名><数据类型>

[约束条件][FIRST|AFTER 已存在字段名]

其中,新字段名为需要添加的字段名称;FIRST为可选参数,其作用是将添加的字段设置为表的第一个字段;AFTER为可选参数,其作用是将新添加的字段添加到已存在字段名指定的字段后面。

例如:在tb_new中添加一个字段citySQL语句如下:

ALTER TABLE tb_new ADD city VARCHAR(20);

 MySQL数据表的基本操作

从结果可以看出添加了一个字段city,在默认情况下,该字段放在最后一列。

也可以在数据表的第一列添加字段。

例如:在数据表tb_new中添加一个INT类型的字段newIDSQL语句如下:

ALTER TABLE tb_new ADD newid INT(11) FIRST;

执行结果发现在表的第一列添加了一个名为newidINT11)类型的字段,结果如图所示:

 MySQL数据表的基本操作

例如:在数据表tb_new中的sex列后添加一个INT类型的字段ss.SQL语句如下:

ALTER TABLE tb_new ADD ss INT(11) AFTER sex;

 MySQL数据表的基本操作

  从结果可以看出,tb_new表中添加了一个名称为ss的字段,其位置在指定的sex字段的后面,添加字段成功。

5、删除字段

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

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

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

例如:删除数据表tb_new中的SS字段,SQL语句如下:

ALTER TABLE tb_new DROP ss;

从结果可以看出,tb_new表中已经不存在名称为ss的字段,删除字段成功。

 MySQL数据表的基本操作

四、数据表的其他操作 

1、修改字段排序

通常使用ALTER TABLE修改字段的排列顺序,修改字段排序的语法格式如下:

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

例如:将数据表tb_new中的id字段修改为表的第一个字段,SQL语句如下:

ALTER TABLE tb_new MODIFY id INT(11) FIRST;

 MySQL数据表的基本操作

也可以修改字段到指定列之后。

例如:将数据表tb_new中的newname字段移动到salary字段后面,SQL语句如下:

ALTER TABLE tb_new MODIFY newname VARCHAR(28) AFTER salary;

 MySQL数据表的基本操作

2、更改表的存储引擎

更改表的存储引擎的语法格式如下:

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

例如:将db_2数据表的存储引擎修改为MyISAMSQL语句如下:

ALTER TABLE db_2 ENGINE=MyISAM; 

操作步骤:1、先查看db_2表当前的存储引擎(ENGINE=InnoDB;

                  2、修改存储引擎;

                 3、再次查看db_2表的存储引擎(ENGINE=MyISAM);

 MySQL数据表的基本操作

3、删除表的外键约束

   对于数据库中定义的外键,如果不在需要,可以将其删除。外键一旦删除,就会解除主表和从表间的关联关系。MySQL中删除外键的语法格式如下:

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

   其中,“外键约束名”指在定义表时CONSTRAINT关键字后面的参数。

   例如:删除数据表db_5中的外键约束fk_emp_dept1.

   首先查看db_5的结构,SQL语句如下:

   Show CREATE TABLE db_5\G

   删除数据表db_5的外键fk_emp_dept1,SQL语句如下:

    ALTER TABLE db_5 DROP FOREIGN KEY fk_emp_dept1;

 MySQL数据表的基本操作MySQL数据表的基本操作

可以看到,db_5表中已经不存在FOREIGN KEY,原有的名称为fk_emp_dept1的外键约束删除成功。

五、删除数据表 

   对于不在需要的数据表,可以将其从数据库中删除。

1、删除没有被关联的表

MySQL中使用DROP TABLE可以一次删除一个或多个没有被其他表关联的数据表,语法格式如下:

DROP TABLE [IF EXISTS] 1,表2......n;(n表示要删除的表名称)

例如:删除db_2数据表,SQL语句如下:

DROP TABLE db_2;(删除后数据列表中已经不存在名称为db_2的数据表,删除操作成功)

 MySQL数据表的基本操作 

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

   在数据表之间存在外键关联的情况下,如果直接删除父表,结果会显示失败,原因是直接删除将破坏表的完整性。如果必须删除,可以先删除与它关联的子表,再删除父表,只是这样同时删除了两个表中的数据。有些情况下需要保留子表,这时如果要单独删除父表,只需将关联的表的外键约束条件取消,然后就可以删除父表。

  在数据库中创建两个关联表,首先创建tb_1表,SQL语句如下:

Create table tb_1

   (

   Id      INT(11)  primary key,

   Name   VARCHAR(22)

  );

接下来创建tb_2表,SQL语句如下:

Create table tb_2

   (

   Id      INT(11)  primary key,

   Name   VARCHAR(25),

   wwid     INT(11),

   CONSTRAINT fk_tb_dt FOREIGN KEY (wwid) REFERENCES tb_1(id)

  );

 使用SHOW CREATE TABLE命令查看tb_2表的外键约束,SQL语句如下:

SHOW CREATE TABLE tb_2\G(执行结果如下图)

 MySQL数据表的基本操作

从结果可以看到,在数据表tb_2上创建了一个名称为fk_tb_dt的外键约束。 

例如:删除父表tb_1.

首先直接删除父表tb_1,输入删除语句如下:

DROP TABLE tb_1;

执行结果如图所示,可以看到,当存在外键约束时主键不能被直接删除。

 MySQL数据表的基本操作

接下来删除关联子表tb_2的外键约束,SQL语句如下:

ALTER TABLE tb_2 DROP FOREIGN KEY fk_tb_dt;

该语句执行后将取消tb_1tb_2表之间的关联关系,此时可以输入删除语句,将原来的父表tb_1删除,SQL语句如下:

DROP TABLE tb_1;

最后通过SHOW TABLES查看数据表列表,结果如图所示。

 MySQL数据表的基本操作

可以看到,数据表列表中已经不存在名称为tb_1的表。