最基本的数据库操作整理

原创于 2021-07-10 15:11:01 发布 · 299 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #触发器

数据库专栏收录该内容

2 篇文章

订阅专栏

本文详细介绍了SQL的基本操作，包括创建、修改和删除数据库及表，以及约束、外键、视图、存储过程和触发器的使用。通过实例展示了如何进行数据插入、更新和删除，以及如何处理外键约束。此外，还讲解了如何创建和管理存储过程以及触发器，为数据库的管理和维护提供了全面的知识。

1. 创建数据库

CREATE DATABASE MyDatabase;

2. 表的操作

1.创建表并且定义约束

1. 创建一主键约束

CREATE TABLE table1(
col1 varchar(10) PRIMARY KEY,
col2 varchar(10),
col3 varchar(30)
)

2. 创建多主键约束

CREATE TABLE table2(
col1 varchar(10),
col2 varchar(10),
col3 varchar(30) PRIMARY KEY(col1, col2, col3)
)
-- 将col1、col2、col3组合成主码

3. 创建唯一性约束

CREATE TABLE table3(
col1 varchar(10),
col2 varchar(10) UNIQUE,
col3 varchar(30) 
)

4. 创建外键约束

CREATE TABLE table4(
col1 varchar(10) NOT NULL,
col2 varchar(10) ,
col3 varchar(30) ,
FOREIGN KEY(col1) REFERENCES table1(col1)
)

tips: 被引用外键必须是唯一性索引，不然会外键创建失败

2. 修改表

一般格式为：

ALTER TABLE<表名>
[ADD <属性名><数据类型>[<列级完整性约束条件>]]
[ADD <完整性约束名><完整性约束>]
[DROP <完整性约束名>]
[DROP <属性名>]

1. 给表增加一个属性

ALTER TABLE table1
add col4 varchar(20)

2. 给表增加一个完整性约束

ALTER TABLE table1
ADD CONSTRAINT my_unique 
UNIQUE(COL2);

3.删除刚刚创建的完整性约束

ALTER TABLE table1
DROP CONSTRAINT my_unique

tips: 删除完整性约束需要知道约束名，如果不知道的话可能有一点难删，好像要用到一些稀奇古怪的函数

4. 删除刚刚创建的属性

ALTER TABLE table1
DROP COLUMN col4

3. 删除表

DROP TABLE table1

发现删除不了，报错了，报错信息：

消息 3726，级别 16，状态 1，第 1 行
无法删除对象 'table1'，因为该对象正由一个 FOREIGN KEY 约束引用。

那就删除table3

DROP TABLE table3

删除成功。

上面删除失败就是因为我们在创建table4的时候有一个外键约束绑定在table1上面，导致删除失败，我们就要先删除table4再删除table1。

DROP TABLE table4
DROP TABLE table1

删除成功。

3.表的查询

略

4. SQL数据更新

1. 插入记录

1.一次插入一条记录

INSERT
INTO <表名>[<列名1>[,<列名2>...]]
	VALUES(<常量值|表达式1>[,<常量值|表达式2>...])

例如向table2插入数据

INSERT INTO
table2(col1, col2, col3)
VALUES('INFO1', 'INFO2', 'INFO3')

2.一次插入多条记录

INSERT
INTO <表名>[<列名1>[,<列名2>...]]
	VALUES(<常量值|表达式1>[,<常量值|表达式2>...]),
	(<常量值|表达式3>[,<常量值|表达式4>...]),
	(<常量值|表达式5>[,<常量值|表达式6>...]).....

或者

INSERT
INTO <表名>[<列名1>[,<列名2>...]]
	<子查询>

例如向table2插入数据

INSERT INTO
table2(col1, col2, col3)
	VALUES
		('INFO2', 'INFO2', 'INFO3'),
		('INFO3', 'INFO2', 'INFO3'),
		('INFO4', 'INFO2', 'INFO3'),
		('INFO5', 'INFO2', 'INFO3')

2. 修改记录

格式：

UPDATE <表名>
SET<列名1>=<表达式1>[, <列名2>=<表达式2>]
[WHERE <条件>]

修改table2的数据

UPDATE table2
SET col2 = 'INFO4'
WHERE col3 = 'INFO3'

3. 删除记录

DELETE FROM <表名>[WHERE <条件>]

Tip: 不加条件会删除该表所有数据

删除table2中col1='INFO1'的数据

DELETE FROM table2 WHERE col1 = 'INFO1'

4. 关于外键约束

把table1和table4创建回来

CREATE TABLE table1(
col1 varchar(10) PRIMARY KEY,
col2 varchar(10),
col3 varchar(30)
);

CREATE TABLE table4(
col1 varchar(10) NOT NULL,
col2 varchar(10) ,
col3 varchar(30) ,
FOREIGN KEY(col1) REFERENCES table1(col1)
ON DELETE CASCADE -- 级联删除
ON UPDATE CASCADE -- 级联更新
);

我们先给table1插入数据

INSERT INTO table1 (col1, col2, col3)
VALUES('INFO1', 'INFO2', 'INFO3')

再给table2插入数据

INSERT INTO table4 (col1, col2, col3)
VALUES('INFO2', 'INFO2', 'INFO3')

这个插入失败，报错信息：

消息 547，级别 16，状态 0，第 1 行
INSERT 语句与 FOREIGN KEY 约束"FK__table4__col1__24927208"冲突。该冲突发生于数据库"MyDatabase"，表"dbo.table1", column 'col1'。
语句已终止。

原因是table1的col1属性值中不包含INFO这个数据。

执行

INSERT INTO table4 (col1, col2, col3)
VALUES('INFO1', 'INFO3', 'INFO2')

执行成功

这时候更新table1的col1的值，可以发现table4的值随之改变

UPDATE table1
SET col1 = 'INFO10' 
where col1='INFO1'

执行：

SELECT * FROM table4

结果：

INFO10	INFO3	INFO2

确实发生了改变，这是因为我在定义table4的外键的时候设置了级联更新，不然这段代码要报错。

同理，在table1删除col1的属性值INFO10，同时会在table4删除该条记录。

5. 复制表的结构和数据

格式：

SELECT <目标列表达式1>[别名1][, <目标列表达式2>[别名2]...]
INTO <新表名>
FROM <表名1|视图名1>[, <表名2|视图名2>...]
WHERE <条件表达式>
GROUP BY <分组属性> [HAVING<条件表达式>] 
ORDER BY <排列属性>

备份table1到table3

SELECT *
INTO table3
FROM table1

5. 视图

1. 创建视图

CREATE VIEW <视图名>[(<列名1>[, <列名2>...])...]
AS
<SELECT 子查询>
[WITH CHECK OPTION]

这里开始使用新数据库

USE JX;

创建一个查询Students表中学号大于等于06007的视图

CREATE VIEW Sno_limt
AS
SELECT * FROM Student
WHERE Sno >= '06007'

2.查询视图

把视图当成表用即可

SELECT * FROM Sno_limt

3. 修改视图

格式：

ALTER VIEW <视图名>[(<列名1>[, <列名2>...])...]
AS
<新的 SELECT 子查询>
[WITH CHECK OPTION]

注意有无WITH CHECK OPTION的区别

修改视图Sno_limt的条件是大于06008

ALTER VIEW Sno_limt
AS
SELECT * FROM Student
WHERE Sno >= '06008'

4. 删除视图

格式：

DROP VIEW <视图名>

删除视图Sno_limt

DROP VIEW Sno_limt

6. 存储过程

1.创建存储过程

创建一个存储过程用来查询某个系的全部男生或者女生，存储过程名为Pro_sexDept

CREATE PROC Pro_sexDept @dept VARCHAR(20), @sex VARCHAR(10)
AS
SELECT * FROM Student WHERE Sdept = @dept AND Sex = @sex

2. 使用存储过程

EXEC Pro_sexDept  '计算机',  '男'

3. 修改存储过程

和创建差不多，把CREATE改成ALTER其余的没什么区别

4. 删除存储过程

删除刚刚创建的存储过程

DROP PROC Pro_sexDept

7. 触发器

1. 创建触发器

语法：

CREATE TRIGGER trigger_name 
ON table_name 
[WITH ENCRYPTION] 
FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE] 
AS  
SQL语句

如果激活时间是 BEFORE，那么将在触发器事件执行之前对受影响的行集中的每行激活触发操作。因此，只有在前触发器完成每行的执行后才修改主题表。请注意，前触发器必须具有 FOR EACH ROW 粒度。

如果激活时间是 AFTER，那么将对受影响的行集中的每行或对语句激活触发操作，这取决于触发器粒度。此操作在触发器事件完成后并且在数据库管理器检查触发器事件可能影响的所有约束（包括引用约束的操作）后发生。请注意，后触发器可以具有 FOR EACH ROW 或 FOR EACH STATEMENT 粒度。

如果激活时间是 INSTEAD OF，那么将对受影响的行集中的每行激活触发操作，而不是执行触发器事件。INSTEAD OF 触发器必须具有 FOR EACH ROW 粒度，并且主题表必须是视图。其他触发器均无法将视图用作主题表。

-----摘自MYSQL文档

关于DELETE, INSERT, UPDATE三个：

DELETE: 删除时触发
INSERT：插入时触发
UPDATE：修改更新数据时触发

在Student表中创建一个触发器，当删除一个学生的时候就删除学号为06001的学生

CREATE TRIGGER tri_StudentAdd
ON Student
AFTER DELETE
AS
DELETE FROM Student WHERE Sno = '06001'

当执行

DELETE FROM Student WHERE Sno = '06002'

时学号为06002的学生就被删掉了

2. 修改触发器

修改触发器的方法和创建触发器除了创建触发器是用create修改触发器是用alter以外以取的都一样

3. 删除触发器

删除刚刚创建的触发器

DROP TRIGGER tri_StudentAdd