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

数据库的基本操作

 # 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,这种现象在某些情况会导致业务主键冲突或其他难以发现的问题

致谢

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

学习永无止境,让我们共同进步!!

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部