MySQL-day5（DDL语言：常见约束、TCL语言）

最新推荐文章于 2024-07-15 16:30:03 发布
最新推荐文章于 2024-07-15 16:30:03 发布
阅读量289 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44742328/article/details/109727848
版权

常见约束

含义：
一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：
六大约束
NOT NULL： 非空，用于保证该字段的值不能为空
比如姓名、学号等

DEFAULT: 默认，用于保证该字段有默认值
比如性别

PRIMARY KEY: 主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等

UNIQUE: 唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号

CHECK: 检查约束【mysql中不支持】
比如年龄、性别

FOREIGN KEY: 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值。
比如学生表的专业编号，员工表的部门编号，员工表的工种编号

添加约束的时机：
1.创建表时
2.修改表时

约束的添加分类：

位置支持的约束类型是否可以起约束名
列级约束列的后面语法都支持，但外键没有效果不可以
表级约束所有列的下面默认和非空不支持，其他支持可以（主键没有效果）

列级约束：

六大约束语法上都支持，但外键约束没有效果( 默认、非空、主键、唯一)

语法：
直接在字段名和类型后面追加 约束类型即可。

表级约束：
除了非空、默认，其他的都支持

语法：在各个字段的最下面
【constraint 约束名】 约束类型(字段名)

主键和唯一的大对比：

保证唯一性是否允许为空一个表中可以有多少个是否允许组合
主键√×至多有1个√，但不推荐
唯一√√可以有多个√，但不推荐

外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表。

可以通过以下两种方式来删除主表的记录
方式一：级联删除

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major 
FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

方式二：级联置空

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major 
FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

一、创建表时添加约束

1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
删除非空
alter table 表名 modify column 字段名 字段类型 ;

2、默认
添加默认
alter table 表名 modify column 字段名 字段类型 default 值;
删除默认
alter table 表名 modify column 字段名 字段类型 ;
3、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(字段名);
删除主键
alter table 表名 drop primary key;

4、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(字段名);
删除唯一
alter table 表名 drop index 索引名;
5、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）;
删除外键
alter table 表名 drop foreign key 约束名;

#常见约束

CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束

)
CREATE DATABASE students;
#一、创建表时添加约束

#1.添加列级约束
/*
语法：

直接在字段名和类型后面追加 约束类型即可。

只支持：默认、非空、主键、唯一

*/

USE students;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL UNIQUE,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键

);


CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

#查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;


#2.添加表级约束
/*

语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名) 
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
	
);

SHOW INDEX FROM stuinfo;

#通用的写法：★

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)

);

二、修改表时添加约束和删除约束

#二、修改表时添加约束

/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;


*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一

#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);


#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 


#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

SHOW INDEX FROM stuinfo;

练习：


#1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）

ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

#2.	向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）


#3.	向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

标识列

又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值

特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型
4、标识列可以通过 SET auto_increment_increment=3;设置步长
可以通过 手动插入值，设置起始值

一、创建表时设置标识列

#标识列

#一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT  ,
	NAME FLOAT UNIQUE AUTO_INCREMENT,
	seat INT 

);
TRUNCATE TABLE tab_identity;

INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
INSERT INTO tab_identity(NAME) VALUES('lucy');
SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;

二、修改表时设置标识列

ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

三、修改表时删除标识列

ALTER TABLE tab_identity MODIDY COLUMN id INT;

TCL语言

事务

在这里插入图片描述
MySQL 中的存储引擎[了解]
在这里插入图片描述
事务的特点（ 事务的ACID(acid)属性）

  1. 原子性（Atomicity）
    原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

  2. 一致性（Consistency）
    事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  3. 隔离性（Isolation）
    事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

  4. 持久性（Durability）
    持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

事务的使用
在这里插入图片描述

#1.演示事务的使用步骤

#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';

#结束事务
ROLLBACK;
#commit;

SELECT * FROM account;

#2.演示事务对于delete和truncate的处理的区别

#演示delete（支持回滚）
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;

#演示truncate(不支持回滚)
SET autocommit = 0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

数据库的隔离级别
在这里插入图片描述
在这里插入图片描述
脏读 不可重复读 幻读
read uncommitted：√ √ √
read committed： × √ √
repeatable read： × × √
serializable × × ×

脏读不可重复读幻读
read uncommitted√√√
read committed×√√
repeatable read××√
serializable×××

在 MySql 中设置隔离级别
在这里插入图片描述

savepoint 节点名;设置保存点

#3.演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

视图

在这里插入图片描述
语法：
create view 视图名
as
查询语句;

一、创建视图

#一、创建视图

USE my_employees;

#1.查询邮箱中包含a字符的员工名、部门名和工种信息
#①创建视图
CREATE VIEW myv1
AS 
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON j.job_id = e.job_id;

#②使用
SELECT * FROM myv1 WHERE last_name LIKE '%a%';

#2.查询各部门的平均工资级别
CREATE VIEW myv2
AS 
SELECT AVG(salary) ag,department_id
FROM employees 
GROUP BY department_id;

SELECT * FROM myv2

DROP TABLE IF EXISTS myv2;

SELECT ag,grade_level
FROM myv2
JOIN job_grades g
ON myv2.ag BETWEEN g.`lowest_sal` AND g.`highest_sal`;


#3.查询平均工资最低的部门信息

SELECT * FROM myv2 orde BY ag LIMIT 1;


#4.查询平均工资最低的部门名和工资
#方式一：
CREATE VIEW myv3
AS
SELECT * FROM myv2 ORDER BY ag LIMIT 1;

SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON m.department_id = d.department_id;

#方式二：
SELECT ag,d.*
FROM myv2 
INNER JOIN departments d 
ON d.department_id = myv2.`department_id`
ORDER BY ag
LIMIT 1;

二、视图的修改

#二、视图的修改

#方式一：
/*
create or replace view  视图名
as
查询语句;

*/
SELECT * FROM myv3 

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

#方式二：
/*
语法：
alter view 视图名
as 
查询语句;

*/
ALTER VIEW myv3
AS
SELECT * FROM employees;

三、删除视图

#三、删除视图

/*

语法：drop view 视图名,视图名,...;
*/

DROP VIEW emp_v1,emp_v2,myv3;

四、查看视图

#四、查看视图

DESC myv3;

SHOW CREATE VIEW myv3;

视图的好处

• 重用sql语句
• 简化复杂的sql操作，不必知道它的查询细节
• 保护数据，提高安全性

注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新

①包含分组函数、group by、distinct、having、union、
②join
③常量视图
④where后的子查询用到了from中的表
⑤用到了不可更新的视图

视图和表的对比

关键字是否占用物理空间使用
视图view占用较小，只保存sql逻辑一般用于查询
表table保存实际的数据增删改查

每日一练

1、创建Book表，字段如下：
bid整形，要求主键
bname 字符型，要求设置唯一键，并非空
price 浮点型，要求有默认值 10
btypeId 类型编号，要求引用bookType表的 id字段

已知bookType表（不用创建），字段如下：
id
NAME

CREATE TABLE Book(
	bid INT PRIMARY KEY,
	bname VARCHAR(20) NOT NULL UNIQUE,
	price FLOAT DEFAULT 10,
	btypeId INT; 
	FOREIGN KEY(btypeid) REFERENCES bookType(id)
);

2、开启事务 向表中插入1行数据，并结束

SET autocommit = 0;
INSERT INTO Book VALUES(100,'xx',10,1)
COMMIT;

3、创建视图，实现查询价格大于100的书名和类型名

CREATE VIEW myv1
AS 
SELECT bname,NAME
FROM Book b
JOIN bookType t ON b.btypeid = t.id
WHERE price > 100;

4、修改视图，实现查询价格大于 90-120之间的书名和价格

CREATE OR REPLACE VIEW myv1
AS 
SELECT bname,price
FROM Books 
WHERE price BETWEEN 90 AND 120;

5、删除刚才创建的视图

DROP VIEW myv1;
vid。
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql DDL 学习随笔(9) 常见约束
lllcaleb的博客
11-30 335
常见约束 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性 分类：六大约束    NOT NULL：非空，用于保证该字段的值不能为空    比如姓名、学号等    DEFAULT：默认，用于保证该字段有默认值    比如性别    PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空    比如学号、员工编号    UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空    比如座位号    CHECK：检查约束 【mysql中不支持】    比如年龄、性别    
7 MySQL 约束 Constraint
weixin_44406595的博客
11-27 351
0 约束条件,即对数据表的行数据/列数据的限定条件: 约束 关键字 说明 非空 not null 不能为空的数据 主键 primary key 非空+唯一; 易于快速查询 唯一 unique 唯一性，允许为空 默认 default 数据的默认值 外键 foreign key 与其它数据表的关联数据,确保数据完整性 * 唯一 unique 与主键 primary ...
测试题讲解---mysql
追梦的女孩博客
09-05 209
#1.创建Book表，字段如下 /*bid 整型，要求主键 bname 字符型，要求设置唯一键，并非空 price 浮点型，要求有默认值 10 btypeId类型编号，要求引用bookType表的id 已知bookType表(不用创建)，字段如下： id name */ CREATE TABLE Book( bid INT PRIMARY KEY, bname VARCHAR(20) UNIQUE NOT NULL, price FLOAT DEFAULT 10, btypeId I
常见约束---案例讲解
追梦的女孩博客
08-24 1223
#1.向表emp2的id列中添加primary key约束(my_emp_id_pk) ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY; ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY; #2.向表dept2的id列中添加primary key约束(my_dept_id_pk) ALTER TABLE dept2 MODIFY COLUMN id INT PRIMARY KEY; AL.
unique约束需要注意的地方
vicvan331的专栏
05-03 761
SQL代码 --创建作者表 create table author ( --主键 id int primary key, --姓名不能为空 name nvarchar(8) not null, --年龄必须大于0小于120 age tinyint constraint ck_age check(age>0 and age<120),
T-SQL数据库规则约束及默认值设置学习笔记
专栏
04-16 2223
create database test1gouse test1create table table1(   book_ID int primary key not null,   book_name varchar(50) not null,   book_price varchar(100) not null,   book_type varchar(50) not null,   maste
MySQL-DDL数据定义语言.sql
12-16
数据定义语言,包括库和表的管理的语法语句相关案例脚本。 一、库的管理： 创建、修改、删除；二、表的管理： 创建、修改、删除。 创建：create 修改：alter 删除：drop
MySQL数据定义语言DDL的基础语句
09-08
MySQL 数据定义语言（DDL）是用于创建和修改数据库结构的SQL命令集，它涉及到数据库、表、索引等对象的创建、更改和删除。在本文中，我们将深入探讨MySQL DDL的基础语句，并通过示例代码帮助理解这些概念。 首先，...
详解SQL四种语言：DDL DML DCL TCL
09-10
SQL语言是用于管理和操作关系型数据库的强大工具，它由四种主要的语句类型组成：DDL（Data Definition Language），DML（Data Manipulation Language），DCL（Data Control Language），以及TCL（Transaction ...
22.4-SQL-DDL和架构设计：Springboard分配单元22.4
02-12
在本单元中，我们将深入探讨SQL（Structured Query Language）中的DDL（Data Definition Language）和数据库架构设计，这是Springboard分配的第22.4单元。SQL是用于管理和操作关系型数据库的强大工具，而DDL则是SQL...
SQL---DDL语言之常见约束案例
weixin_38932035的博客
02-07 334
1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk） ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY; ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id); #2. 向表dept2的id列中添加PRIMARY KEY约束（my_dept_id...
淘淘商城第30讲——实现商品添加功能
热门推荐
李阿昀的博客
05-30 1万+
经过上文的学习，我相信大家都知道富文本编辑器的使用方法了，我们能走到这步，实属不易，本文终于可以来实现商品添加这个功能了。 在item-add.jsp页面当中，当点击提交按钮后，会触发submitForm方法，如下图所示。 在提交表单前需要校验输入的内容是否合法，如下图所示。 下面我们看下数据库中商品表的建表信息，可以看到价格定义的字段类型是long型，单位为分，之所以这样做是为了避免
修正或添加字段默认值约束的名称
aol94890的博客
09-18 495
旧项目中的数据库约束名称不规范，写了个脚本重新修改。 1. 名称重新修改为已有的默认值约束的名称为'DF_' + 表名 + 字段名； 2. 没有设置默认值约束的列设置默认值，字符串为空，数值为0； 3. 时间字段除了addtime不加默认值； 1 --修正或添加字段默认值约束的名称为DF_表名_字段 2 3 /* 4 SELECT ...
myspl数据库基础
auo8191的博客
12-14 693
mysql数据库sql 语句大全收录---sql语句不区分大小写 启动/停止 net start mysql net stop mysql 用户登录 mysql -uroot -p 登录本地数据库 root初始没有密码,回车登录 mysql -u用户名 -p密码 登录本地数据库 mysql -u用户名 -p密码 -h网段 登录指定...
【案例讲解】常见约束
似水流年的博客
06-19 1572
#1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);#2. 向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）...
mysql常见约束
程宇寒
01-05 796
mysql常见约束 直接po代码和案例 #常见约束 /* 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性(或者叫一致性) 分类：六大约束 NOT NULL：非空，用于保证该字段的值不能为空 比如姓名、学号等 DEFAULT:默认，用于保证该字段有默认值 比如性别 PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空 比如学号、员工编...
MS SQL入门基础:数据库规则Rule
weixin_33747129的博客
07-26 210
　　（Rule）规则 就是数据库中对存储在表的列或用户自定义数据类型中的值的规定和限制。规则是单独存储的独立的数据库对象。规则与其作用的表或用户自定义数据类型是相互独立的，即表或用户自定义对象的删除、修改不会对与之相连的规则产生影响。规则和约束可以同时使用，表的列可以有一个规则及多个CHECK 约束。规则与CHECK 约束很相似，相比之下，使用在ALTERTABLE 或CREATE TABLE 命...
MySQL & NaviCat 安装及配置教程（Windows）【安装】
绯樱殇雪的博客
07-15 849
MySQL & NaviCat 安装及配置教程
来聊一聊MySQL InnoDB的LSN
最新发布
神技圈子的博客
07-15 716
详细讲解了MySQL中的LSN作用已经实现方式，这部分对于从事数据库内核开发来说非常重要
MySQL-----DDL基础操作
02-23
MySQL的DDL（Data Definition Language）是用于定义和管理数据库结构的基础操作。 以下是MySQL中DDL的基础操作： 1. 创建数据库：使用CREATE DATABASE语句可以创建一个新的数据库。例如，CREATE DATABASE ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值