对于MySQL数据库的基础学习

最新推荐文章于 2023-09-19 09:00:35 发布

zhao_to

最新推荐文章于 2023-09-19 09:00:35 发布

阅读量268

点赞数

分类专栏：数据库学习笔记文章标签： mysql 数据库

本文链接：https://blog.csdn.net/zhao_to/article/details/116462283

版权

学习笔记同时被 2 个专栏收录

24 篇文章 1 订阅

订阅专栏

数据库

4 篇文章 0 订阅

订阅专栏

MySQL

1.数据库基本概念

1.数据库

DateBase 简写DB

2.概述

数据库是用于存储和管理数据的仓库

3.特点：

1.用于持久化存储数据的，数据库其实就是一个文件系统

1.只要是持久化存储，肯定都是以文件的形式存储在硬盘上

2.数据库的文件系统和windows文件系统的虽然都可以存储数据，但是数据库不光要求能存数据，还要求对于数据的操作更简单，管理更方便。

2.方便存储和管理数据

3.使用统一的操作数据语言——SOL(普通话)

4.常见的数据库

1.MySQL: 最初是MySql公司的，后来被Sun公司收购，随着Sun公司，又被Oracle收购。

特点：开源，免费，深受中小企业喜爱

2.Oracle ：甲骨文

特点：安全，扩展性强，功能完备

3.SQL Server

特点：C# 和 .net项目中应用比较多

5.启动和关闭

Mysql安装好之后，需要启动服务，什么是服务？服务就是没有界面的应用程序

1.启动，关闭服务

方式1：(开始键+R)运行-> services.msc
方式2：管理员身份cmd -> net start MySqL或net stop MySqL
- 注意:命令中的MySql 一定要与你的mysql服务的名称一致。

6.MySQL登录

方式一：

mysql -u用户名 -p密码

方式二：

mysql -h目标主机IP地址 -u用户名 -p密码

方式三：

mysql -host=目标主机IP地址 --user=用户名 --password=密码

7.MySQL退出

quit

exit

2.SQL

2.1概述：

SQL:结构化查询语言(Structured Query Language)简称SQL。只要是关系型数据库,都可以用SQL语言区操作数据。不同的数据库之间的SQL操作略有不同，但是主体部分还是一样的。对于数据库之间的差异,我们称之为：“方言”.

2.2SQL语法

1.SQL语句可以是单行，也可以是多行，多行以分号结尾

->show databases
->;

2.使用空格和缩进来增强语句的可读性

3.MySQL数据库的SOL语句不区分大小写，关键词建议使用大写

4. 三种注释

1.单行注释：-- 注释内容或 # 注释内容（Mysql特有）

2.多行注释：/* 注释内容*/

2.3 SQL语言的分类

1.DDL（Data Definition Language）：数据库定义语言

用来定义数据库对象：数据库，表，列表等。关键字：create，drop，alter等

2.DML（ Data Manipulation Language ）：数据库操作语言

用来对数据库中表的数据进行增删改操作的。关键字：insert，delete，update等

3.DQL（ Data QueryLanguage ）：数据库查询语言

用来查询表中的数据。关键字：select，where

4.DCL（Data Control Language）：数据库控制语言

用来定义数据库的访问权限和安全级别，创建用户。关键字grant，revork

3.DDL -数据库定义语言

3.1操作数据库 -CRUD

1.C – Create创建

-- 创建数据库
CREATE DATABASE test;
-- 查看test数据-编码集
SHOW CREATE DATABASE test;

注意：

1.数据库已存在，再次创建就会报错

-- 如果test不存在，就创建
CREATE DATABASE IF NOT EXISTS test;

2.创建数据库时指定编码集

-- 创建数据库时指定编码集
CREATE DATABASE test2 CHAR SET utf8;
-- 将数据库test2的编码集指定为utf-8
SHOW CREATE DATABASE test2;
-- 查看test2数据库-编码集

2.R – Retrieve查询

1.查询所有数据库名称：

-- 查询所有数据库
SHOW DATABASES;

2.查询指定数据库的编码集

-- 查看test数据-编码集
SHOW CREATE DATABASE test;
SHOW CREATE DATABASE 数据库名;

3.U – Upd修改

修改数据库编码集

-- 修改test的编码集为utf8
ALTER DATABASE test CHAR SET utf8;
ALTER DATABASE 数据库 CHAR SET 字符集;

4.D – Drop 删除

-- 删除test2 和 test3 
DROP DATABASE IF EXISTS test2;
DROP DATABASE IF EXISTS test3;
DROP DATABASE IF EXISTS 数据库名;

5.使用数据库

USE 数据库名;

3.2操作表

1.Create 创建

学生表

学号	姓名	年龄

语法：

CREATE TABLE 表名(
	列名1 数据类型1，
    列名2 数据类型2，
    ……
    列名n 数据类型n
);

注意：

1.最后一个列不需要加,

2.常用数据类型

1.int：整形类型

例子：age INT

2.float，double小数类型

例子：score float(4,2) '4’代表共四位数字，”2“代表小数位为2位

3.date：日期类型：只包含年月日 yyyy-MM-dde

4.datetime:日期类型：包含年月日时分秒 yyyy-MM-dd HH-mm-ss

5.timestamp 时间戳类型：包含年月日时分秒 yyyy-MM-dd HH-mm-ss

该类型再插入数据时，不赋值或赋null，则数据库会使用当前系统时间，来自动赋值

6.char，varchar 字符串

char（n）：定长度数据类型，该类型在存储数据时，如果数据长度<n，则剩余部分用空格补齐，查询时，再去掉空格

varchar（n）：变长度数据类型

-- 创建学生表
CREATE TABLE student(
       id INT, -- 学号
       NAME VARCHAR(20),-- 姓名
       age INT, -- 年龄
       score DOUBLE(5,2), -- 成绩
       birthday DATE, -- 生日
       insert_time TIMESTAMP -- 插入时间
);

2.R–Retrieve查询

1.查询所有数据库中所有表名称：

SHOW TABLES;

2.查询指定数据库的某个表；

DESC student;
desc 表名；

3.U – Upd修改

1.修改表名

alter table 表名 rename to stu
-- 修改表名
ALTER TABLE student RENAME TO stu;
SHOW TABLES;
DESC stu;

2.修改表的字符集

-- 修改字符集
ALTER TABLE stu CHAR SET utf8;

3.添加字段

-- 添加字段
ALTER TABLE stu ADD sex CHAR(2);

4.修改列名，数据类型

方式一：alter table 表名 change 旧列名新列名新数据类型

方式二：alter table 表名 change 列名新数据类型

-- 修改列名，数据类型
ALTER TABLE stu CHANGE sex gender VARCHAR(10);
-- 只修改数据类型
ALTER TABLE stu MODIFY gender VARCHAR(5);

5.删除列

-- 删除列
ALTER TABLE stu DROP gender;

4.D – Drop删除表

drop table 表名；
drop table if exists 表名；--方式2
DROP TABLE IF EXISTS stu;

4.DML-数据库操作语言

4.1添加数据

语法：

insert into 表名（字段1，字段2，……） values（value 1 ，value 2，……）

注意：

1.列名和值需要一一对应

2.如果插入语句中不加列名，则表示默认向所有字段添加数据

3.除了数字类型，其他都要加引号（单双引号）

-- 插入一条数据
INSERT INTO student(id,NAME,age,score,birthday,insert_time) VALUES (1,"赵童",18,99.00,'2000-2-18',NULL);
DESC student;

INSERT INTO student VALUES (2,"屈波",18,90.00,'2000-3-18',NULL);
-- DQL 查询表中的所有数据
SELECT*FROM student;

4.2删除数据

语法：

delete from 表名[where 条件]

注意：

1.如果不带条件，则会把表中的所有数据删除

2.如果删除表中所有记录

1.delete from 表名（一条一条删除，效率低）

2.truncate table 表名（直接删除表，再创建一个一样的表,效率高）

-- 删除数据
DELETE FROM student WHERE id=1;
DELETE FROM student ; -- 清空表中数据
TRUNCATE TABLE student; -- 清空表中数据，然后创建一个一模一样的新

4.3修改数据

语法：

update 表名 set 列名 1 = value 1，列名2 = value2，……[ where 条件]

注意:

不加条件，修改所有的数据对应的字段

-- 修改数据
UPDATE student SET age=20;

-- 将id=2的学生的年龄修改成18
UPDATE student SET age=18 WHERE id=2;
UPDATE student SET score=100 WHERE id =1;

SELECT*FROM student;

5.DQL-数据库查询语言

语法：

select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定

测试表：

学生表（utf-8）：学号，姓名，年龄，性别，地址，java（int），c

USE test;
CREATE TABLE IF NOT EXISTS student(
id INT,#学号
NAME VARCHAR(20),#姓名
age INT,#年龄
sex VARCHAR(5),#性别
address VARCHAR(100),#地址
math INT,#数学
english INT#英语
);
DROP TABLE IF EXISTS student;
INSERT INTO student (id,NAME,age,sex,address,math,english) VALUES
(1,'马云',18,'男','杭州',80,80),
(2,'马化腾',19,'男','深圳',75,60),
(3,'埃隆马斯克',31,'男','美国',76,93),
(4,'扎克伯格',27,'男','美国',65,NULL),
(5,'郎平',16,'女','上海',90,98),
(6,'姚明',32,'男','上海',80,81);

5.1基础查询

1.SELECT*FROM 表名; – 查询该表中所有字段对应的数据

2.SELECT 字段1，字段2，……FROM 表名；查询表中指定字段对应的数据

-- 2.SELECT  字段1，字段2，……FROM 表名；查询表中指定字段对应的数据
SELECT NAME,age FROM student;

3.去重查询：SELECT DISTINCT 字段1，字段2，……FROM 表名

-- 查询学生表中的地址字段，去重
SELECT DISTINCT site FROM student;

**4.运算查询 Select 字段1，字段2，（字段1+字段2），… from 表名 **

-- 查询学生的java ，c 的成绩和总成绩
select Java,C,(Java+c) as 总成绩 from student; -- as 起别名

注意：

当字段中有null值参与运算，结果也为null，这样的话不太合理，用于IFNULL

-- 查询学生的java ，c 的成绩和总成绩,如果缺考，则成绩按0处理
SELECT NAME,Java,IFNULL(c,0),(Java+IFNULL(C,0)) AS 总成绩 FROM student;

5.2条件查询

1.where子句后跟条件

2.运算符

1.>,<, >= ,<=, = , <>

2.BETWEEN…AND

3.IN(集合）

4.LINK：模糊查询

占位符：

		’_:一个任意字符    周_
		%: 多个任意字符

5.IS NULL

6.AND或者&&

7.OR或||

8.NOT 或！

-- 查询年龄大于20的
SELECT*FROM student WHERE  age>20;

-- 查询年龄大于等于 20的学生
SELECT*FROM student WHERE  age>=20;

-- 查询年龄不等于32岁的学生
SELECT*FROM student WHERE age<>32;
SELECT*FROM student WHERE age!32;

-- 查询年龄在20岁 - 30岁之间的学生信息
SELECT*FROM student WHERE age<30 AND age>20;
SELECT * FROM student WHERE age>20 && age<30;
SELECT*FROM student WHERE age BETWEEN 20 AND 30;

-- 查询年龄时21和32
SELECT * FROM student WHERE age=21 OR age=32;
SELECT * FROM student WHERE age = 21 ||age = 32;
SELECT * FROM student WHERE age IN(21,32);

-- 查询C语言缺考的学生
SELECT * FROM student WHERE c IS NULL;
SELECT * FROM student WHERE c = NULL;-- 该条语句不正确,因为null不能用 = ，!= 判断

-- 查询C语言有成绩的学生
SELECT*FROM student WHERE c IS NOT NULL;

5.3 like-模糊查询

模糊查询在项目中用的比较多，因为大多数搜索框都是模糊查询

##################模糊查询###################
-- like查询
-- 查询姓赵的学生的信息
SELECT * FROM student WHERE NAME LIKE '赵%';

-- 查询第二个字有必学生信息
SELECT * FROM student WHERE NAME LIKE "_必%";

-- 查询名字中包含唐子学生信息
SELECT * FROM student WHERE NAME LIKE "%唐%";

-- 查询名字是三个子的名字
SELECT * FROM student WHERE NAME LIKE '___';

5.4排序查询

语法：

order by 子句

具体写法

order by 排序字段1，排序方式1，排序字段2 ，排序方式2 ……

排序方式：

1.ASC：升序（默认排序）

2.DESC: 降序

##################排序查询###################
-- 查询学生信息，按照Java创建升序排序
SELECT*FROM student ORDER BY Java;
SELECT*FROM student ORDER BY Java ASC;

-- 查询学生信息，按照Java创建降序排序
SELECT*FROM student ORDER BY Java DESC;

-- 查询学生信息，按照Java创建降序排序,如果java成绩相同，再按照c成绩进行降序排序
SELECT*FROM student ORDER BY Java DESC,C DESC;

注意：

如果有多个排序条件，当前一个条件值相同时，才会按照第二个条件进行排序

5.5聚合函数

概述：

将一列数据作为整体，进行纵向计算

格式：

select 聚合函数（字段名）[as 别名] from 表名

分类：

1.count：統計个数

2.max ：计算最大值

3.min：计算最小

4.sum ：求和

5.avg ：求平均值

注意事项：

聚合函数在进行计算时，会自动排除null

##################聚合函数查询###################
-- 統計学生表中有多少条信息
SELECT COUNT(id) FROM student;
SELECT COUNT(IFNULL(C,0)) FROM student;

-- 求学生中C语言成绩最高分
SELECT MAX(IFNULL(C,0)) AS C最高分 FROM student;

-- 求学生中C语言成绩最低分
SELECT MIN(IFNULL(C,0)) AS C最低分 FROM student;

-- 求学生中Java语言成绩总和
SELECT SUM(Java) AS 成绩总和 FROM student;

-- 求学生中Java语言平均成绩
SELECT AVG(Java) AS 平均成绩 FROM student;

5.6分组查询

格式：

	group  by 分组字段

注意：

1.分组之后的查询的字段：分组字段，聚合函数

SELECT * FROM student GROUP BY sex;-- 没有任何意义结果：查出局部数据

2.where 和 having 的区别

where对分组前进行限定，如果不满足，则不参与分组，having是对分组后结果进行限定，如果不满

足，就不会被查询出来。

where后不能跟聚合函数，having后可以跟聚合函数

-- 按照性别分组，分别查询男，女同学的数学平均分
SELECT * FROM student GROUP BY sex;-- 没有任何意义
SELECT sex,AVG(math) FROM student GROUP BY sex;

-- 按照性别分组，分别查询男，女同学的数学平均分，及相应性别对应的人数
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;

-- 分组前添加一些限定条件
-- 按照性别分组，分别查询男，女同学的英语平均分，及相应性别对应的人数,分组要求：分数小于60的不参与统计
SELECT sex,AVG(IFNULL(english,0)),COUNT(id) FROM student WHERE english >60 GROUP BY sex;
SELECT sex,AVG(IFNULL(english,0)),COUNT(id) FROM student GROUP BY sex HAVING
english >60;-- 错误，因为条件是分组前的条件，having只对分组后的结果进行条件添加

-- 按照性别分组，分别查询男，女同学的英语平均分，及相应性别对应的人数,分组要求：分数小于60的不参与统计，并且只查看人数大于2的分组数据
SELECT sex,AVG(IFNULL(english,0)),COUNT(id) FROM student WHERE english >60 GROUP
BY sex HAVING COUNT(id)>2;
-- 另一种写法
SELECT sex,AVG(IFNULL(english,0)),COUNT(id) 人数 FROM student WHERE english >60
GROUP BY sex HAVING 人数>2;

5.7 分页查询

格式：

limit 开始的索引，每页显示的条数；

分页公式：

开始的索引 = （当前页码-1）* 每页显示的条数

推导过程：

-- 每页显示2条数据
SELECT * FROM student LIMIT 0,2;-- 第1页
SELECT * FROM student LIMIT 2,2;-- 第2页
SELECT * FROM student LIMIT 4,2;-- 第3页
SELECT * FROM student LIMIT 6,2;-- 第4页

-- 后面在做网站实现分页效果的时候，我们只需要确定每页起始的索引就可以实现分页效果
-- 分页公式：开始的索引 = （当前页码-1）* 每页显示的条数

注意：

分页操作limit语法是MySql的“方言”

6.约束

概述：

对表中的数据进行限定，保证数据的正确性，有效性和完整性。

理解：

对于我们一直操作的学生表，我们可以尝试添加一些非法数据，比如名字=null，它是能被正常保存进去的，这样对于的数据是无效，不完整，且没有任何意义的。所以我们在设计表的时候可以给表中的字段加一些约束，比如名字字段对应的值不能为null，这样就可以有效的保证数据的正确性，有效性和完整性。

使用：

1.创建表时

2.修改表时

分类：

1.主键约束：primary key

2.非空约束：not null

3.唯一约束：unique

4.外键约束：foreign key(多表关系讲解后讲)

非空约束：not null

方式1：创建表时

CREATE TABLE people(
	NAME VARCHAR(20) NOT NULL,
	age INT
);
SELECT * FROM people;

INSERT INTO people (NAME,age) VALUES('马化腾',25);
INSERT INTO people (NAME,age) VALUES(NULL,25);-- 报错：Column 'name' cannot be null

方式2：修改表时，可添加，可删除

-- 修改表来添加或删除约束
ALTER TABLE people MODIFY NAME VARCHAR(20);
INSERT INTO people (NAME,age) VALUES(NULL,25);-- 不报错：我们将非空约束删除了
ALTER TABLE people MODIFY NAME VARCHAR(20) NOT NULL;
INSERT INTO people (NAME,age) VALUES(NULL,25);-- 报错：Column 'name' cannot be null

唯一约束：unique

注意：

多个null值不算重复值。

方式1：创建表时

DROP TABLE IF EXISTS people;
CREATE TABLE IF NOT EXISTS people(
	NAME VARCHAR(20),
	p_id BIGINT(18) UNIQUE
);
INSERT INTO people (NAME,p_id) VALUES('马云',610503197702021125);
INSERT INTO people (NAME,p_id) VALUES('马云',610503197702021125);-- 报错：Duplicate entry '610503197702021125' for key 'p_id'
INSERT INTO people (NAME,p_id) VALUES('马化腾',NULL);-- 不报错
INSERT INTO people (NAME,p_id) VALUES('马化腾',NULL);-- 不报错
SELECT * FROM people;

方式2：修改表时，可添加，可删除

ALTER TABLE people MODIFY p_id BIGINT(18);
INSERT INTO people (NAME,p_id) VALUES('马云',610503197702021125);-- 报错，因为唯一约束的删除有特殊语法
ALTER TABLE people DROP INDEX p_id;
INSERT INTO people (NAME,p_id) VALUES('马云',610503197702021125);-- 不报错
ALTER TABLE people MODIFY p_id BIGINT(18) UNIQUE;-- 报错，因为表中有不唯一数据，删除之后就不会报错了
SELECT * FROM people;

主键约束：primary key

主键：

1.主键 = 非空且唯一

2.一张表中只能有一个字段为主键

3.主键就是表中记录的唯一标识

DROP TABLE IF EXISTS people;
CREATE TABLE IF NOT EXISTS people(
	id INT PRIMARY KEY,-- 主键
	NAME VARCHAR(20)
);

INSERT INTO people (id,NAME) VALUES(1,'马云');
INSERT INTO people (id,NAME) VALUES(2,'刘德华');
INSERT INTO people (id,NAME) VALUES(2,'张惠妹');-- 报错：Duplicate entry '2' for key 'PRIMARY'
INSERT INTO people (id,NAME) VALUES(NULL,'张惠妹');-- 报错：Column 'id' cannot be null
-- 删除主键
ALTER TABLE people DROP PRIMARY KEY;

-- 添加主键
ALTER TABLE people MODIFY id INT PRIMARY KEY;

自动增长：AUTO_INCREMENT

概述：

如果某一列是数值类型的，并且连续非空且连续的时候，我们就可以使用 auto_increment 来完成自动增长

方式1 创建表时添加主键并将主键设置为自增

DROP TABLE IF EXISTS people;
CREATE TABLE IF NOT EXISTS people(
	id INT PRIMARY KEY AUTO_INCREMENT ,-- 主键
	NAME VARCHAR(20)
);

-- 自增序列我们可以不给值，它会自己给
INSERT INTO people (id,NAME) VALUES(NULL,'马云');
INSERT INTO people (id,NAME) VALUES(NULL,'刘德华');

-- 我们也可以手动的指定值，并且下一次添加的值只与最后一条数据有关
INSERT INTO people (id,NAME) VALUES(100,'马云');
INSERT INTO people (id,NAME) VALUES(NULL,'刘德华');--id=101
SELECT * FROM people;

方式2：修改表时，可添加，可删除

ALTER TABLE people MODIFY id INT;
INSERT INTO people (id,NAME) VALUES(NULL,'刘德华');-- 报错：Column 'id' cannot be null
ALTER TABLE people MODIFY id INT AUTO_INCREMENT;

外键约束：foreign key

首先我们先建一个测试表：emp

CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_name VARCHAR(30),-- 部门名称
	dep_location VARCHAR(30)-- 部门地址
)
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES('马化腾',20,'研发部','深圳');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES('马云',26,'研发部','深圳');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES('周杰伦',18,'销售部','北京');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES('蔡依林',19,'销售部','北京');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES('求伯君',22,'研发部','深圳');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES('詹姆斯高斯林',30,'研发部','深圳');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES('汤姆克鲁斯',20,'销售部','北京');
INSERT INTO emp (NAME,age,dep_name,dep_location) VALUES('丁磊',27,'研发部','深圳');

	通过查询数据，我们发现 研发部对应深圳，销售部对应北京,这样相同部门的员工信息中部门地址就会

出现很多重复数据，并且在修改部门地址时非常不方便，需要每条数据单独操作。

为了解决上述问题，我们可以将表进行拆分，将员工表拆分成员工表和部门信息表，让这两个表产生联。

-- 解决数据冗余问题
-- 拆分表
DROP TABLE emp;
CREATE TABLE employee( -- 员工信息表
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);
CREATE TABLE department(-- 部门信息表
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);
-- 添加数据
-- 添加部门
INSERT INTO department (dep_name,dep_location) VALUES ('研发部','深圳');
INSERT INTO department (dep_name,dep_location) VALUES ('销售部','北京');
-- 添加员工信息
INSERT INTO employee (NAME,age,dep_id) VALUES('马化腾',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('马云',26,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('周杰伦',18,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('蔡依林',19,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('求伯君',22,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('詹姆斯高斯林',30,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('汤姆克鲁斯',20,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('丁磊',27,1);

这样做看似解决了之前所说的数据冗余，操作不方便等问题，但是又有了新问题，比如我现在直接将部门表中一个部门删掉，并且是可以删除成功的，但是我员工表中还有几个员工在被删除的这个部门，这样的话就会出现逻辑问题，所以应该有一个约束：只有当前部门中没有员工时，才能删除该部门。

方式1：创建表时添加

语法：

create table 表名（
	……
	外键列
	constraint 外键名称 foreign key （外键列名称） references 关联表名称（主表列名称）
）;

案例：

-- 添加外键
DROP TABLE employee;
DROP TABLE department;
CREATE TABLE department(-- 部门信息表
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);
CREATE TABLE employee( -- 员工信息表
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT ,-- 外键列
	CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 添加数据
-- 添加部门
INSERT INTO department (dep_name,dep_location) VALUES ('研发部','深圳');
INSERT INTO department (dep_name,dep_location) VALUES ('销售部','北京');
-- 添加员工信息
INSERT INTO employee (NAME,age,dep_id) VALUES('马化腾',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('马云',26,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('周杰伦',18,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('蔡依林',19,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('求伯君',22,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('詹姆斯高斯林',30,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('汤姆克鲁斯',20,2);
INSERT INTO employee (NAME,age,dep_id) VALUES('丁磊',27,1);
INSERT INTO employee (NAME,age,dep_id) VALUES('丁磊',27,3);-- 报错：Cannot add or update a child row
DELETE FROM department WHERE id = 1;-- 报错：annot delete or update a parent row:

方式2：修改表时，可添加，可删除

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;
-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id);

级联操作（慎用）

当我们设置了外键之后，我想修改我的部门id，可以操作，但是比较麻烦

-- 1. 将员工表中关联的数据设置为null
UPDATE employee SET dep_id = NULL WHERE dep_id = 1;
-- 2. 修改部门id
UPDATE department SET id = 10 WHERE id=1;
-- 3. 将外键为null的值再设置成10
UPDATE employee SET dep_id=10 WHERE dep_id IS NULL;

设置级联更新：ON UPDATE CASCADE

-- 设置级联更新
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;-- 先删除外键
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;
UPDATE department SET id = 1 WHERE id=10;

设置级联删除：ON DELETE CASCADE

-- 设置级联删除
ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk;-- 先删除外键
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES
department(id) ON UPDATE CASCADE ON DELETE CASCADE;--可以同时设置更新和删除，也可以分开设置
DELETE FROM department WHERE id=1;

zhao_to

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
对于MySQL数据库的基础学习

MySQL1.数据库基本概念1.数据库DateBase 简写DB2.概述数据库是用于存储和管理数据的仓库3.特点： 1.用于持久化存储数据的，数据库其实就是一个文件系统1.只要是持久化存储，肯定都是以文件的形式存储在硬盘上2.数据库的文件系统和windows文件系统的虽然都可以存储数据，但是数据库不光要求能存数据，还要求对于数据的操作更简单，管理更方便。 2.方便存储和管理数据 3.使用统一的操作数据语言——SOL(普通话)4.常见的数据库 1.MySQL: 最初是
复制链接

扫一扫

专栏目录