MySQL--数据库与表的操作_创建数据库后在创建数据表怎么操作mysql-CSDN博客

本文链接：https://blog.csdn.net/lerp020321/article/details/140856494

前言：本博客仅作记录学习使用，部分图片出自网络，如有侵犯您的权益，请联系删除

数据库的基本操作

 # 1、查看数据库
 show databases;
 
 # 2、创建数据库
 create database 数据库名称;
 
 # 3、删除数据库
 drop databse 数据库名称;

数据表的基本操作

一、创建数据表：

1、创建表的语法形式

创建数据表之前，应使用语句"use 数据库名"指定操作是在哪个数据库中进行；

创建数据表语法格式如下：

 create table 表名(
 字段名1,数据类型 [列级别约束条件] [默认值],
 字段名2,数据类型 [列级别约束条件] [默认值],
 ...
 [表级别约束条件]
 );
     
 # 示例：创建员工表tb_emp1；
 create database test_db;
 use test_db;
 create table tb_emp1(
 id      int(11),
 name    varchar(25),
 deptId  int(11),
 salary  float
 );
 show databases;

使用create table创建表时，必须指定以下信息：

要创建的表名称，不区分大小写，不能使用SQL语言中的关键字，如drop、alter、insert等
数据表中的每一列（字段）的名称和数据类型，如果要创建多列，就要用逗号隔开；

2、使用主键约束

2.1、单字段主键

主键由一个字段组成，SQL语句格式分为以下两种情况

（1）在定义列的同时指定主键，语法格式：

 字段名 数据类型 primary key [默认值]
 
 # 示例：定义数据表tb_emp2，主键为id
 create table tb_emp2(
 id       int(11) primary key,
 name     varchar(25),
 deptId   INT(11),
 salary   float
 );

（2）在定义完所有列后指定主键：

 [constraint <约束名>] primary key [字段名]
 
 # 示例: 定义数据表tb_emp3，主键为id
 create table tb_emp3(
 id      int(11),
 name    varchar(25),
 deptId  int(11),
 salary  float,
 primary key(id)
 );

2.2、多字段联合主键

主键由多个字段联合组成，其语法：

 primary key [字段1,字段2,...,字段n]
 
 # 示例：定义数据表tb_emp4，表无主键id，为了唯一确定一个员工，把name、deptId联合作为主键：
 create table tb_emp4(
 name     varchar(25),
 deptId   int(11),
 salary   float,
 primary key(name,deptId)
 );

3、使用外键约束

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

外键：首先它是表中的一个字段，可不是本表主键，但要对应另外一个表的主键。

主表（父表）：对于两个具有关联关系的表，相关联字段中主键所在的表为主表

从表（子表）：对于两个具有关联关系的表，相关联字段中外键所在的表为从表

创建外键的语法如下：

 [constraint <外键名>] foreign key 字段名1 [,字段名2,...]
 references <主表名> 主键列1 [,主键列2,...]
 
 # 外键名：定义的外键约束的名称，一个表中不能有相同名称的外键
 # 字段名：字段需要添加外键约束的字段列
 # 主表名：被字表外键所依赖的表的名称
 # 主键列：主键中定义的主键列
 
 # 示例：定义tb_emp5，在tb_emp5表上创建外键约束，首先创建部门表tb_dept1
 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_emp_dept1 foreign key(deptId) references tb_dept1(id)
 );
 # 在tb_emp5上添加了名称为fk_emp_dept1的外键约束，外键名称为deptId，依赖tb_emp1的主键id

4、使用非空约束

非空约束指字段的值不能为空；语法规则如下：

 字段名 数据类型 not null
 
 # 示例：定义数据表tb_emp6，指定员工的名称不能为空
 create table tb_emp6(
 id      int(11) primary key,
 name    varchar(25) not null,
 deptId  int(11),
 salary  float
 );

5、使用唯一性约束

唯一性约束要求该列唯一，允许为空，但只能出现一个空值。唯一约束可确保一列或几列不出现重复值；语法规则如下：

（1）在定义完列之后指定唯一约束：

 字段名 数据类型 unique
 
 # 示例：定义数据表tb_dept2，指定部门的名称唯一
 create table tb_dept2(
 id       int(11) primary key,
 name     varchar(22) unique,
 location varchar(50)
 );

（2）在定义完所有列之后指定唯一约束：

[constraint <约束名>] unique(<字段名>)

# 示例：定义数据表tb_dept3，指定部门的名称唯一
create table tb_dept3(
id        int(11) primary key,
name      varchar(22),
location  varchar(50),
constraint sth unique(name)
);

6、使用默认约束

默认约束指定某列的默认值；其语法规则如下：

字段名 数据类型 default 默认值

# 示例：定义数据表tb_emp7，指定员工的部门编号默认为1111
create table tb_emp7(
id      int(11) primary key,
name    varchar(25) not null,
deptId  int(11) default 1111,
salary  float
);

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

可通过为表主键添加auto_increment关键字来实现自动生成字段的主键值；默认初始值是1，每增加一条记录，字段值自动加1；一个表只能有一个字段使用auto_increment约束，且该字段必须为主键的一部分。auto_increment约束的字段可以是任何整数类型；其语法：

字段名 数据类型 auto_increment

# 定义数据表tb_emp8，指定员工的编号自动递增
create table tb_emp8 (
id      int(11) primary key auto_increment,
name    varchar(25) not null,
deptId  int(11),
salary  float
);

# 在添加记录的时候，id字段自动加1
insert into tb_emp8 (name,salary) values('lucy',1000),('lura',1200);
select * from tb_emp8;
mysql> select * from tb_emp8;
+----+------+--------+--------+
| id | name | deptId | salary |
+----+------+--------+--------+
|  1 | lucy |   NULL |   1000 |
|  2 | lura |   NULL |   1200 |
+----+------+--------+--------+

二、查看数据表结构

1、查看表基本结构语句

describe/desc语句可以查看表的字段信息，包括字段名、字段数据类型、是否为主键、是否有默认值等，其语法：

describe  表名;
或简写:
desc 表名;

# 示例：查看tb_emp1表结构：
mysql> desc tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

其中，各个字段的含义分别解释如下：

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

2、查看表详细结构语句

show create table <表名\G>;

此语句不仅可以查看表创建时候的详细语句，还可查看存储引擎和字符编码；若不加\G参数，显示的结果会非常混乱；示例：查看tb_emp1的详细信息：

show create table tb_emp1;
+---------+----------------------------+
| Table   | Create Table               |
+---------+----------------------------+
| tb_emp1 | CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------+

三、修改数据表

1、修改表名

MySQL是通过ALTER TABLE语句来实现表名的修改的，其具体的语法规则如下：

alter table <旧表名> rename [to] <新表名>;
to 为可选参数

# 示例：将数据表tb_dept3改名为tb_department3
show tables;
alter table tb_dept3 rename tb_department3;
show tables;	# 检查

2、修改字段的数据类型

在MySQL中修改字段数据类型的语法规则如下：

alter table <表名> modify <字段名> <数据类型>

# 示例：将数据表tb_dept1中name字段的数据类型有varchar(22)修改成varchar(30)
alter table tb_dept1 modify name varchar(30);

3、修改字段名

alter table <表名> change <旧字段名> <新字段名> <新数据类型>;

# 示例1：将数据表tb_dept1中的location字段名称改为loc，数据类型不变
alter table tb_dept1 change location loc varchar(50);
# 示例2：将数据表tb_dept1中的loc字段改为location，同时将数据类型变为varchar(60)
alter table tb_dept1 change loc location varchar(60);

4、添加字段

一个完整的字段包括字段名、数据类型、完整性约束。添加字段的语法格式如下：

alter table <表名> add <新字段名><数据类型> [约束条件] [first | after 已存在字段名];

新字段名为需要添加的字段的名称；first为可选参数，表示将新添加的字段设置为表的第一个字段；after为可选参数，表示将新添加的字段添加到"已存在字段名"的后面；默认最后列；

# 1、添加无完整性约束条件的字段
# 示例：在数据表tb_dept1中添加一个没有完整性约束的int类型的字段managerId；
alter table tb_dept1 add managerId int(10);

# 2、添加有完整性约束条件的字段
# 示例：在数据表tb_dept1中添加一个不能为空的varchar(12)类型的字段column1；
alter table tb_dept1 add column1 varchar(12) not null;

# 3、在表的第一列添加一个字段
# 示例：在数据表tb_dept1中添加一个int(11)类型的字段column2；
alter table tb_dept1 add column2 int(11) first;

# 4、在表的指定列之后添加一个字段
# 示例：在数据表tb_dept1中name列后添加一个int(11)类型的字段column3；
alter table tb_dept1 add column3 int(11) after name;

5、删除字段

alter table <表名> drop <字段名>;

# 示例：删除数据表tb_dept1表中的column2字段
alter table tb_dept1 drop column2;

6、修改字段的排列位置

alter table <表名> modify <字段1> <数据类型> first|after <字段2>;

"字段1"指要修改位置的字段；"数据类型"指"字段1"的数据类型；"first"为可选参数，指将"字段1"修改为表的第一个字段；"after 字段2"指将"字段1"插入到"字段2"后面；

# 1、修改字段为表的第一个字段
# 示例：将数据表tb_dept1中的culumn1字段修改为表的第一个字段
alter table tb_dept1 modify column1 varchar(12) first;

# 2、修改字段到表的指定列之后
# 示例：将数据表tb_dept1中的culumn1字段插入到location字段后
alter table tb_dept1 modify column1 varchar(12) after location

7、更改表的数据引擎

可以使用"show engines;"语句查看系统支持的存储引擎；

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

alter table <表名> engine=<更改后的存储引擎名>;

# 示例：将数据表tb_department3的存储引擎修改为MyISAM
show create table tb_department3 \G
alter table tb_department3 engine=MyISAM;

8、删除表的外键约束

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

alter table <表名> drop foreign key <外键约束名>
# 外键约束名 指定义表时constraint关键字后的参数

# 示例：删除表tb_emp9中的外键约束
create table tb_emp9(
id      int(11) primary key,
name    varchar(25),
deptId  int(11),
salary  float,
constraint fk_emp_dept foreign key (deptID) references tb_dept1(id)
);
show create table tb_emp9 \G
alter table tb_emp9 drop foreign key fk_emp_dept;

四、删除数据表

1、删除没有被关联的表

drop table [if exists]表1,表2,...表n;

# 示例：删除数据表tb_dept2
drop table if exists tb_dept2;

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

先删子表再删父表；有的情况下要单独删除父表，只需要将关联的表的外键约束条件取消，就可以删除父表了；下面进行示例：

# 首先创建两个关联表
mysql> create table tb_dept2(
id       int(11) primary key,
name     varchar(22),
location varchar(50)
);
mysql> create table tb_emp(
id       int(11) primary key,
name     varchar(25),
deptId   int(11),
salary   float,
constraint fk_emp_dept foreign key (deptId) references tb_dept2(id)
);
mysql> show create table tb_emp \G

# 删除被数据表tb_emp关联的数据表tb_dept2
# 直接删除父表tb_dept2，发现报错；解除关联子表tb_emp的外键约束
drop table tb_dept2;
alter table tn_emp drop foreign key fk_emp_dept;
drop table tb_dept2;
show tables;

五、MySQL 8.0的新特性

1、默认字符集改为utf8mb4

在MySQL8.0之前，默认字符集为latin1，网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。若忘记修改；会出现乱码问题；使用以下命令查看数据库的默认编码：

show variables like 'character_set_database';

2、自增变量的持久化

在MySQL8.0之前，自增主键auto_increment的值如果大于max(primary key)+1，在MySQL重启后，会重置auto_increment=max(primary key)+1，这种现象在某些情况会导致业务主键冲突或其他难以发现的问题

致谢

在此，我要对所有为知识共享做出贡献的个人和机构表示最深切的感谢。同时也感谢每一位花时间阅读这篇文章的读者，如果文章中有任何错误，欢迎留言指正。

学习永无止境，让我们共同进步！！