数据库的基本操作
查看Mysql中存在的数据库
SHOW DATABASES;
这里显示的三个数据库是Mysql安装完成后自带的数据库。
创建一个数据库
CREATE DATABASE test_01;
查看已经存在的数据库的创建语句
SHOW CREATE DATABASE test_01;
删除数据库
DROP DATABASE test_01;
数据库表的基本操作
数据表属于数据库,所以在创建数据表之前我们需要使用USE 数据库名
这样的语句选择一个数据库
创建数据表
语法规则:
CREATE TABLE 表名
(
字段名1,数据类型[列级别约束条件][默认值],
字段名1,数据类型[列级别约束条件][默认值],
... ...
[表级别约束条件]
)
创建一个员工表
首先创建数据库
CREATE DATABASE test_01;
使用test_01
数据库
use test_01;
创建员工表
CREATE TABLE tb_emp1
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
使用命令查看数据库中存在的表
SHOW TABLES;
约束
2.3.1 主键约束(Primary Key Constraint)
主键是用来唯一标识一条数据的字段,比如说使用身份证号可以就可以唯一确定一个人。主键可以由一个字段组成,也可以由多个字段共同组成。
单字段主键
(1)在定义列的同时就指定主键
语法规则:
字段名 数据类型 PEIMARY KEY[默认值]
创建一个由ID作为主键约束的员工表th_emp2
CREATE TABLE tb_emp2
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);
(2) 在定义完所有列之后再指定主键
PRIMARY KEY 字段名
创建一个由ID作为主键约束的员工表th_emp3
CREATE TABLE tb_emp3
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);
多字段联合主键
PRIMARY KEY[字段1,字段2,...]
定义一个将name
和deptId
作为联合主键的员工表tb_emp4
CREATE TABLE tb_emp4
(
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id),
PRIMARY KEY(name,deptId)
);
外键约束(FOREIGN KEY)
外键用来在两个表之间建立连接,一个表可以有多个外键。外键是表中的一个字段,这个字段对应关联表中的主键字段。
举个例子,现在有两张表部门表和员工表,如何通过员工表查询到这个员工所在的部门呢?很明显就需要将员工表和部门表连接起来。那么我们在员工表中加入一个dept_id
,用来存储部门表的主键id
。那么dept_id
就相当于是员工表的外键,可以用这个外键关联到部门表。
被外键约束的员工表称为子表,外键指向的部门表被称为主表。
为何实际开发中不会创建外键约束呢?
有过实际开发经验的同学会发现,我们创建表的时候是不会创建外键约束的,要关联表的话,最多加个ID冗余字段。这是为什么呢?
(1)下面是阿里开发手册的说明:
以学生和成绩的关系为例,学生表中的 student_id 是主键,那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,即为级联更新。外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。
(2)分库分表无法使用外键:
只有一个场景不能使用外键,就是分库分表,其它因素都不是真正的原因。
- 高并发锁:外键一般是用来读的,但是一般不会设置读锁
- 性能:数据库的瓶颈在IO,即使不适用外键,在代码里做数据完整性检查也会占用IO
- 扩展性:主流数据库均支持外键约束
创建部门表tb_dept1
CREATE TABLE tb_dept1
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);
创建员工表tb_emp5
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_dept1的主键id。
2.3.3 非空约束(Not Null Constraint)
顾名思义添加了这个约束的字段在添加数据时不能为空,否则会报错。
语法规则:
字段名 数据类型 NOT NULL
定义一个员工表tb_emp6,指定员工的名称不能为空
CREATE TABLE tb_emp6
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) NOT NULL,
deptId INT(11),
salary FLOAT,
);
唯一性约束(Unique Constraint)
被这个约束修饰的字段,它的值禁止出现重复。允许为空,但只能出现一个空值。
这个约束和主键有点类似,主键可以唯一标识一条数据,那么主键也是唯一性的,区别就在于:
- 主键不能为空
- 在一张表中唯一性约束可以给0个或多个字段设置,但是主键只能设置一次。
(1)定义列时就指定唯一约束
创建部门表tb_dept2
CREATE TABLE tb_dept2
(
id INT(11) PRIMARY KEY,
name VARCHAR(22) UNIQUE,
location VARCHAR(50)
);
(2)定义完所有列之后再指定唯一约束
创建部门表tb_dept3
CREATE TABLE tb_dept3
(
id INT(11) PRIMARY KEY,
name VARCHAR(22),
location VARCHAR(50),
CONSTRAINT STH UNIQUE(name)
);
默认约束(DEFAULT)
也就是给某个字段添加一个默认值,如果添加数据时没有手动复制,就会自动带入默认值。
创建员工表tb_emp7,部门号默认为111
CREATE TABLE tb_emp7
(
id INT(11) PRIMARY KEY,
name VARCHAR(25) NOT NULL,
deptId INT(11) DEFAULT 111,
salary FLOAT
);
自增(AUTO_INCREMENT)
每次插入新记录时,我们可能希望主键字段能自增以保持唯一性,加入这个约束后,每增加一条记录,字段值自动加1。
一个表只能有一个字段使用改约束,且改字段必须为主键的一部分,这个约束可以修饰任何整数类型(TINYINT、SMALLINT、INT、BIGINT等)
定义一个员工表,员工的编号自增
CREATE TABLE tb_emp8
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
deptId INT(11) DEFAULT 111,
salary FLOAT
);
例如,插入一下数据
INSERT INTO tb_emp8(name , salary)
values('jack', 200),('rose', 2000),('sea', 9000);
查看表数据,发现主键自动创建了
数据表结构
使用DESCRIBLE
使用DESCRIBLE
能列出表的字段信息
语法规则:
DESCRIBLE 表名
也可以简写为
DESC 表明
现在我们查看之前定义的tb_temp1
的表结构
DESC tb_emp1;
使用SHOW CREATE TABLE
使用SHOW CREATE TABLE
可以查看表的创建语句
SHOW CREATE TABLE tb_emp8;
修改表数据
修改表名
语法规则:
ALTER TABLE 旧表名 RENAME 新表名
将tb_emp8
表名改为tb_emp9
ALTER TABLE tb_emp8 RENAME tb_emp9;
修改字段的数据类型
语法规则:
ALTER TABLE 旧表名 MODIFY 字段名 数据类型
将tb_emp9
中的name字段类型由VARCHAR(25)
改成VARCHAR(255)
ALTER TABLE tb_emp9 MODIFY name VARCHAR(255);
修改字段名
语法规则:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型
将tb_emp9
中的salary
字段名字改为money
ALTER TABLE tb_emp9 CHANGE salary money float;
当然使用这条命令可以只修改字段名,也可以只修改字段类型
添加字段
语法规则:
ALTER TABLE 表名 ADD 新字段名 数据类型 约束条件 [FIRST|AFTER 已存在字段名];
两个可选参数:
FIRST
:将新添加的字段置为表的第一个字段AFTER
:将新添加的字段添加到指定的“已存在字段名”的后面- 如果没有这两个字段默认会添加到最后位置。
给tb_emp9添加一个无约束字段managerId
ALTER TABLE tb_emp9 ADD managerId INT(10);
给tb_emp9在name
字段之后添加一个非空约束字段age
ALTER TABLE tb_emp9 ADD age INT(3) not null AFTER name;
给tb_emp9所有字段之前添加一个first
字段
ALTER TABLE tb_emp9 ADD first INT(3) not null FIRST;
删除字段
语法格式:
ALTER TABLE 表名 DROP 字段名
从tb_emp9删除字段first
ALTER TABLE tb_emp9 DROP first;
删除表的外键约束
语法格式:
ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名
在前面我们已经给表tb_emp5
添加了一个名为fk_emp_dept1
的外键约束,使用一下语句删除这个外键:
ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_emp_dept1;
更改表的存储引擎
语法规则
ALTER TABLE 表名 ENGINE=更改后的存储引擎名
删除表数据
删除没有被关联的表
使用DROP TABLE
可以一次删除一个或多个没有被其他表关联的表,语法规则:
DROP TABLE [IF EXISTS] 表1,表2...
IF EXISTS
:如果我们要删除的表不存在,直接删除就会报错,加上IF EXISTS
后就能避免报错。
删除表tb_emp9
DROP TABLE IF EXISTS tb_emp9;
删除被其他表关联的主表
如果一张表有关联关系的话,直接删除相关是会报错的。比如一张有外键的表,要是直接删除外键对应的主表,就会报错,可以先删除外键约束,再删除表。