• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

第四章 数据表的基本操作

互联网 diligentman 4周前 (01-31) 13次浏览

数据表的基本操作

  • 一、创建数据表
    • 1、创建表的语法形式
    • 2、使用主键约束
      • 2.1、单字段主键
      • 2.2、多字段主键
    • 3、使用外键约束
    • 4、使用非空约束
    • 5、使用唯一性约束
      • 5.1、在定义完列之后直接指定唯一约束
      • 5.2、在定义完所有列之后指定唯一约束
    • 6、使用默认约束
    • 7、设置表的属性值自动增加
  • 二、查看数据表结构
    • 1、查看表基本结构语句DESCRIBE
    • 2、查看表详细结构语句SHOW CREATE TABLE
  • 三、修改数据表
    • 1、修改表名
    • 2、修改字段的数据类型
    • 3、修改字段名
    • 4、添加字段
      • 4.1、添加无完整性约束条件的字段
      • 4.2、添加有完整性约束条件的字段
      • 4.3、在表的第一列添加一个字段
      • 4.4、在表的指定列之后添加一个字段
    • 5、删除字段
    • 6、修改字段的排列位置
      • 6.1、修改字段为表的第一个字段
      • 6.2、修改字段到表的指定列之后
    • 7、更改表的存储引擎
      • 7.1、查看系统支持的存储引擎
      • 7.2、更改表的存储引擎
    • 8、删除表的外键约束
  • 四、删除数据表
    • 1、删除没有被关联的表
    • 2、删除被其他表关联的主表
  • 五、MySQL8.0的新特性1——默认字符集改为utf8mb4
    • 1、在MySQL5.X版本中,查看数据库的默认编码
    • 2、在MySQL5.X版本中,查看数据表的默认编码
    • 3、在MySQL8.0版本中,测试数据库默认编码
    • 4、在MySQL8.0版本中,查看数据表的默认编码
  • 六、MySQL8.0新特性2——自增变量的持久化
    • 在MySQL5.X版本中:
    • 在MySQL8.0版本中:
  • 补充
    • 1、并不是每个表都可以任意选择存储引擎
  • 总结

一、创建数据表

1、创建表的语法形式

数据表属于数据库,在创建数据表之前,先使用USE <DataBaseName>指定操作是在哪个数据库内进行的,如果没有进行选择,将会抛出No database selected的错误。

创建数据表的语句为CREATE TABLE,语法规则如下:

CREATE TABLE <TableName>(
	字段1	数据类型 [列级别约束] [默认值],
	字段2	数据类型 [列级别约束] [默认值],
	......,
	[表级别约束]

注意:
1、表名称不区分大小写,不可使用SQL语言中的关键字
2、数据表中每一列(字段)的名称和数据类型若创建多列,就要用逗号隔开

示例:

CREATE TABLE tb_emp1(
	id		INT(11),		//员工编号
	name	VARCHAR(25),	//员工姓名
	deptId	INT(11),		//所在部门编号
	salary	FLOAT			//工资
);

2、使用主键约束

主键,又称主码,是表中一列或多列的组合,主键约束要求主键列的数据唯一,并且不允许为空。

主键分为两种类型:

:root { --mermaid-font-family: "trebuchet ms", verdana, arial;}

































主键的类型









单字段主键









多字段联合主键








2.1、单字段主键

主键由一个字段组成

SQL语句分为两种情况:

(1)在定义列的同时指定主键

CREATE TABLE tb_emp2(
	id		INT(11)		PRIMARY	KEY,
	name	VARCHAR(25),
	deptId	INT(11),
	salary	FLOAT
);

(2)在定义完所有列之后指定主键

CREATE TABLE tb_emp3(
	id		INT(11),
	name	VARCHAR(25),
	deptId	INT(11),
	salary	FLOAT,
	PRIMARY	KEY(id)
);

2.2、多字段主键

主键由多个字段联合组成

语法规则如下:

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

示例演示:

CREATE TABLE tb_emp4(
	name	VARCHAR(25),
	deptId	INT(11),
	salary	FLOAT,
	PRIMARY	KEY(name, deptId)
);

3、使用外键约束

1、外键用来在两个表的数据之间建立连接,可以是一列或者多列
2、一个表可以有一个或多个外键
3、外键对应的是参照完整性,一个表的外键可以为空值,若不为空值,则每一个外键值必须等于另一个表中的主键的某个值

外键:它是表中的一个字段,可以不是本表的主键,但要对应另外一个表的主键

作用:外键的主要作用是保证数据的引用完整性(参照完整性)

主表(父表):相关联字段中主键所在的那个表

从表(子表):相关联字段中外键所在的那个表

语法规则如下:

CONSTRAINT 外键名	FOREIGN	KEY	(字段名1, 字段名2, ..., 字段名n)	REFERENCES 主表名(主键列1,, 主键列2, ..., 主键列n)

外键名:定义的外键约束的名称
一个表中,不能有相同名称的外键

示例演示:

CREATE	TABLE	tb_dept1(
	id			INT(11)		PRIMARY	KEY,	//部门编号
	name		VARCHAR(22)	NOT	NULL,		//部门名称
	location	VARCHAR(50)					//部门位置
);

CREATE	TABLE	tb_emp5(
	id		INT(11)	PRIMARY	KEY,
	name	VARCHAR(25),
	deptId	INT(11),
	salary	FLOAT,
	CONSTRAINT fk_emp5_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
);

注意:
子表的外键必须关联父表的主键且关联字段的数据类型必须匹配,若不匹配,则抛出ERROR 3780 (HY000): Referencing column 'deptId' and referenced column 'id' in foreign key constraint 'fk_emp5_dept1' are incompatible.


4、使用非空约束

非空约束指字段的值不能为空

语法规则如下:

字段名	数据类型	NOT	NULL

示例演示:(指定员工的姓名不能为空)

CREATE TABLE tb_emp6(
	id		INT(11)	PRIMARY	KEY,
	name	VARCHAR(25) NOT NULL,
	deptId	INT(11),
	salary	FLOAT
);

5、使用唯一性约束

唯一性约束要求该列唯一,允许为空,但只能出现一个空值
唯一约束可以保证一列或几列不出现重复值

5.1、在定义完列之后直接指定唯一约束

语法规则如下:

字段名	数据类型	UNIQUE

示例演示:

CREATE TABLE tb_dept2(
	id			INT(11)	PRIMARY KEY,
	name		VARCHAR(22) UNIQUE,
	location	VARCHAR(50)
);

5.2、在定义完所有列之后指定唯一约束

语法规则如下:

CONSTRAINT	约束名	UNIQUE(字段名)

示例演示:

CREATE TABLE tb_dept3(
	id			INT(11) PRIMARY KEY,
	name		VARCHAR(22),
	location	VARCHAR(50),
	CONSTRAINT STH UNIQUE(name)
);

UNIQUE与PRIMARY KEY的区别:
1、一张表中可以有多个字段声明为UNIQUE,但只能有一个PRIMARY KEY声明
2、声明为PRIMARY KEY 的列不允许有空值,但是声明为UNIQUE的字段允许空值的存在


6、使用默认约束

默认约束指定某列的默认值

语法规则如下:

字段名	数据类型	DEFAULT 默认值

实例演示:

CREATE TABLE tb_emp7(
	id		INT(11) PRIMARY KEY,
	name	VARCHAR(25) NOT NULL,
	deptId	INT(11) DEFAULT 1111,
	salary	FLOAT
);

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

1、在MySQL中AUTO_INCREMENT的初始值默认为1
2、一个表只能有一个字段使用AUTO_INCREMENT约束,且该字段必须为主键的一部分
3、AUTO_INCREMENT约束的字段可以是任何正整数类型,如(TINYINT,SMALLINT,INT,BIGINT等)

语法规则如下:

CREATE TABLE tb_emp8(
	id		INT(11) PRIMARY KEY AUTO_INCREMENT,
	name	VARCHAR(25) NOT NULL,
	deptId	INT(11),
	salary	FLOAT
);

执行如下插入语句:

INSERT INTO tb_emp8 (name,salary) VALUES ('Lucy',1000), ('Lura',1200), ('Kevin',1500);

结果如下:(可以观察到主键id的值是从1以1为步长增加的)

第四章 数据表的基本操作


二、查看数据表结构

  在MySQL中,使用SQL语句创建数据表之后,为了查看数据表的结构定义以确认是否正确,可以使用DESCRIBESHOW CREATE TABLE语句

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

DESCRIBE可以查看表的字段信息(字段名,数据类型,是否为主键,是否有默认值等)

语法规则如下:

DESCRIBE	表名
	或者
DESC	表名

示例演示:

DESCRIBE tb_dept1;

结果如下:
第四章 数据表的基本操作

实例演示:

DESC tb_emp1;

结果如下:
第四章 数据表的基本操作

字段解释:
1、NULL:表示该列是否可以为NULL
2、KEY:表示该列是否已编制索引,其中,PRI表示该列是主键的一部分,UNI表示该列是UNIQUE索引的一部分,MUL表示列中某个给定值允许出现多次
3、DEFAULT:表示改了是否有默认值,有的话显示指定值是多少
4、Extra:表示可以获取的与给定列有关的附加信息,如AUTO_INCREMENT


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

可以用来显示创建表时的SQL语句,查看存储引擎字符编码

语法格式如下:

SHOW CREATE TABLE 表名G

注意:如果不加G参数,显示的结果将非常混乱

示例演示:

SHOW CREATE TABLE tb_emp1G

结果如下:
第四章 数据表的基本操作


三、修改数据表

  修改数据库中已经存在的数据表的结构
  MySQL使用ALTER TABLE语句修改表
  常见操作:修改表名修改字段数据类型修改字段名增加字段删除字段修改字段排列位置更改表的存储引擎删除表得到外键约束

1、修改表名

MySQL通过ALTER TABLE语句来修改数据表。

语法规则如下:

ALTER TABLE 旧表名 RENAME [TO] 新表名

示例演示:

将表 tb_dept3 改名为 tb_department3
ALTER TABLE tb_dept3 RENAME tb_department3;

修改表名并不修改表的结构


2、修改字段的数据类型

语法规则如下:

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

“表名”指要修改数据类型的字段所在的表
“字段名”指要修改的字段
“数据类型”指修改后的新数据类型

实例演示:

DESC tb_dept1;
ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);
DESC tb_dept1;

结果如下:
第四章 数据表的基本操作

3、修改字段名

语法规则如下:

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

“表名”指要修改的字段所在的表
“旧字段名”指修改前的字段名
“新字段名”指修改后的字段名
“新数据类型”指修改后的数据类型(如果不需要修改字段的数据类型,将新数据类型设置为原来的数据类型即可,千万不可以为空)

示例演示:

将数据表 tb_dept1 中的 location 字段名称次改为 loc 且数据类型不变
DESC tb_dept1;
ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);
DESC tb_dept1;

结果如下:
第四章 数据表的基本操作

示例演示:

将数据表 tb_dept1 中的 loc 字段名称改为 location 同时将数据类型修改为VARCHAR(60)

DESC tb_dept1;
ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);
DESC tb_dept1;

结果如下:
第四章 数据表的基本操作

注意:
  CHANGE也可以值修改数据类型,实现和MODIFY同样的效果,方法是将“新字段名”和“旧字段名”设置为相同的名称而只改变数据类型

  由于不同的数据类型在机器中的存储方式及长度的不同,修改数据类型可能会影响到数据表中已有的数据记录,当数据表中已有数据时,不要轻易修改数据类型


4、添加字段

一个完整的字段包括字段名,数据类型,完整性约束

语法格式如下:

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

“表名”指要在哪个数据表中添加字段
“新字段名”指需要添加的字段名称
“FIRST”为可选参数,指将添加的字段表的第一个字段
“AFTER”为可选参数,指将新添加的字段添加到指定的“已经存在的字段名”后面

若无可选参数FIRST”和“AFTER 已存在的字段名”,则默认将新添加的字段设置为数据表的最后一列

4.1、添加无完整性约束条件的字段

实例演示:

在数据表 tb_dept1 中添加一个没有完整性约束的 INT 字段 managerId

DESC tb_dept1;
ALTER TABLE tb_dept1 ADD managerId INT(10);
DESC tb_dept1;

结果如下:第四章 数据表的基本操作


4.2、添加有完整性约束条件的字段

示例演示:

在数据表 tb_dept1 中添加一个不能为空的 VARCHAR(12) 类型的字段column1

DESC tb_dept1;
ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) NOT NULL;
DESC tb_dept1;

结果如下:
第四章 数据表的基本操作

4.3、在表的第一列添加一个字段

实例演示:

在数据表 tb_dept1 中添加一个 INT(11) 类型的字段 column2

DESC tb_dept1;
ALTER TABLE tb_dept1 ADD column2 INT(11) FIRST;
DESC tb_dept1;

结果如下:
第四章 数据表的基本操作


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

实例演示:

在数据表 tb_dept1 中 name 列后添加一个 INT(11) 类型的字段 column3

DESC tb_dept1;
ALTER TABLE tb_dept1 ADD column3 INT(11) AFTER name;
DESC tb_dept1;

结果展示:
第四章 数据表的基本操作


5、删除字段

删除字段是将数据表中的某个字段从数据表中移除

语法格式如下:

ALTER TABLE 表名 FROP 字段名

“字段名”指要从表中删除的字段的名称

示例演示:

删除数据表 tb_dept1 中的 column2 字段

DESC tb_dept1;
ALTER TABLE tb_dept1 DROP column2;
DESC tb_dept1;

结果如下:
第四章 数据表的基本操作


6、修改字段的排列位置

语法格式如下:

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

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

6.1、修改字段为表的第一个字段

示例演示:

将数据表 tb_dept1 中的 column1 字段修改为数据表的第一个字段

DESC tb_dept1;
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;
DESC tb_dept1;

结果如下:
第四章 数据表的基本操作


6.2、修改字段到表的指定列之后

将数据表 tb_dept1 中的 column1 字段插入到 location 字段后面

DESC tb_dept1;
ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;
DESC tb_dept1;

结果如下:
第四章 数据表的基本操作


7、更改表的存储引擎

  存储引擎是MySQL中的数据存储在文件或内存中时采用的不同技术实现

  MySQL中主要的存储引擎有MyISAMInnoDBMEMORY(HEAP)BDBFEDERATED

7.1、查看系统支持的存储引擎

演示:

SHOW ENGINES;

结果:
第四章 数据表的基本操作


7.2、更改表的存储引擎

语法格式如下:

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

示例演示:

将数据表 tb_department3 的存储引擎修改为MyISAM

SHOW CREATE TABLE tb_department43G //查看数据表的存储引擎
ALTER TABLE tb_department3 ENGINE = MyISAM;
SHOW CREATE TABLE tb_department43G

结果如下:
第四章 数据表的基本操作


8、删除表的外键约束

语法格式如下:

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

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

示例演示:

CREATE TABLE tb_emp9(
	id		INT(11) PRIMARY KEY,
	name	VARCHAR(25),
	deptId	INT(11),
	salary	FLOAT,
	CONSTRAINT fk_emp9_dept1 FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);

SHOW CREATE TABLE tb_emp9G

ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp9_dept1;	//删除外键约束

SHOW CREATE TABLE tb_emp9G

结果如下:
第四章 数据表的基本操作


四、删除数据表

1、删除没有被关联的表

  在MySQL中DROP TABLE可以一次删除一个或多个没有被其他表关联的数据表

语法格式如下:

DROP TABLE IF EXISTS1,2,4, ..., 表n;

“表n”指要删除的表的名称

若要删除的表不存在,则会抛出ERROR 1051 (42S02): Unknown table '表名

  参数IF EXISTS用于在删除前判断删除的表是否存在,加上该参数后,在删除表的时候,如果表不存在,SQL语句也可以顺利执行,但是会发出警告。

示例演示:

删除数据表 tb_dept2

DROP TABLE IF EXISTS tb_dept2;	//执行后发现表tb_dept2删除成功

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

  在数据表之间存在外键关联的情况下,直接删除父表将失败,其破坏了参照完整性。
  删除的方式:
  1、先删除与它关联的子表,再删除父表(子表和父表一起删除)
  2、将关联的表的外键约束条件取消,再删除父表(保留子表)

实例演示(第2种方式):

CREATE TABLE tb_dept2(
	id			INT(11) PRIMARY KEY,
	name		VARCHAR(22),
	location	VARCHAR(50)
);

CREATE TABLE tb_emp(
	id		INT(11) PRIMARY KEY,
	namne	VARCHAR(25),
	deptId	INT(11),
	salary	FLOAT,
	CONSTRAINT fk_emp_dept2 FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
);

  其中,“tb_emp”表为子表,具有“fk_emp_dept2”的外键约束;“tb_dept2”为父表,其主键被子表“tb_emp”所关联。

若直接删除其父表(测试)

DROP TABLE tb_dept2;

结果如下:
第四章 数据表的基本操作

抛出ERROR 3730 (HY000): Cannot drop table 'tb_dept2' referenced by a foreign key constraint 'fk_emp_dept2' on table 'tb_emp'.异常

正确打开方式:

(1)解除关联子表的外键约束fk_emp_dept2

ALTER TABLE tb_emp DROP FOREIGN KEY fk_emp_dept2;

(2)删除父表tb_dept2

DROP TABLE tb_dept2;

执行SHOW TABLES可以发现tb_dept2数据表已经删除成功。


五、MySQL8.0的新特性1——默认字符集改为utf8mb4

  在MySQL8.0之前,默认字符集为latin1utf8字符集指向的是utf8mb3;从MySQL8.0开始默认改为utf8mb4,避免乱码

1、在MySQL5.X版本中,查看数据库的默认编码

SHOW VARIABLES LIKE 'character_set_database';

结果将显示为 latin1(拉丁语)

2、在MySQL5.X版本中,查看数据表的默认编码

SHOW CREATE TABLE tb_emp1G

结果也将显示为 latin1(拉丁语)

3、在MySQL8.0版本中,测试数据库默认编码

SHOW VARIABLES LIKE 'character_set_database';

结果如下:
第四章 数据表的基本操作

4、在MySQL8.0版本中,查看数据表的默认编码

SHOW CREATE TABLE tb_emp1G

结果如下:
第四章 数据表的基本操作

六、MySQL8.0新特性2——自增变量的持久化

  在MySQL8.0之前,自增主键AUTO_INCREMENT的值如果大于max(primary key) + 1,在MySQL重启后,会重置AUTO_INCREMENT = max(primary key) + 1这种现象。

测试:

在MySQL5.X版本中:

创建测试数据表test1
CREATE TABLE test1 (
	id INT AUTO_INCREMENT PRIMARY KEY
);

插入4个空值
INSERT INTO test1 VALUES (0), (0), (0), (0);

查询数据表test1中的数据
SELECT * FROM test1;	//结果将显示[1 2 3 4]'

删除id为4的记录
DELETE FROM test1 WHERE id = 4;

再插入一个空值
INSERT INTO test1 VALUES (0);

再查看数据表test1中的数据
SELECT * FROM test1;	//结果将显示[1 2 3 5]'
//由此可以看出,删除了id = 4的记录,再次插入空值时,并没有重用被删除的4,而是分配了5

再删除id = 5的记录
DELETE FROM test1 WHERE id = 5;

重启数据库,重新插入一个空值
INSERT INTO test1 VALUES (0);

再次查询数据表test1中的数据
SELECT * FROM test1;	//结果将显示[1 2 3 4]'
//由此可以看出,新插入的0值分配的是4,按照未重启前的逻辑,此时应该分配6。
//出现此现象的原因是:自增主键没有持久化。

  在MySQL5.7系统中,对于自增主键的分配规则,是由InnoDB数据字典内部的一个计数器来决定的。而该计数器值在内存中维护,并不会持久化到磁盘中。当数据库重启时,该计数器通过SELECT MAX(ai_col) FROM table_name FOR UPDATE方式初始化,


在MySQL8.0版本中:

  MySQL8.0将自增主键的计数器持久化到重做日志中,每次计数器发生改变,都会将其写入重做日志中。如果数据库重启,InnoDB会根据重做日志中的信息来初始化计数器的内存值,
  为了减少对系统性能的影响,计数器写入到重做日志时并不会马上刷新数据库系统。


补充

1、并不是每个表都可以任意选择存储引擎

  外键约束不能跨引擎使用,外键约束是用来保证数据的参照完整性的,如果表之间需要关联外键,却指定了不同的存储引擎,那么这些表之间是不可以创建外键约束的
  默认AUTO_INCREMENT的初始值为1,可以指定第一条插入记录的id值来指定自增字段的值从何处开始自增。
  添加唯一性的主键约束时,往往需要设置字段自动增加属性。


总结

转载请注明https://blog.csdn.net/weixin_43754860/article/details/113408495


程序员灯塔
转载请注明原文链接:第四章 数据表的基本操作
喜欢 (0)