2024-10-23 10:33:31

首页 / 软件技术

数据库中update与delete使用表别名的深入研究

数据库中update与delete使用表别名的深入研究

总结

Update

SQL语句示例OracleSQLitePostgreSQLMYSQL mariadb
UPDATE users as a SET a.age = 111 WHERE a.name = Alice ;报错报错报错正常执行
UPDATE users as a SET a.age = 111 WHERE name = Alice ;报错报错报错正常执行
UPDATE users as a SET age = 111 WHERE a.name = Alice ;报错正常执行正常执行正常执行
UPDATE users as a SET age = 111 WHERE name = Alice ;报错正常执行正常执行正常执行

Delete

SQL语句示例OracleSQLitePostgreSQLMYSQL mariadb
delete from users as a WHERE a.name = Alice ;报错报错报错报错
delete from users as a WHERE name = Alice ;报错报错报错报错

1 Update

1.1 测试用例UPDATE users as a SET a.age = 111 WHERE a.name = Alice ;

UPDATE users as a SET a.age = 111 WHERE a.name = Alice ;

CREATE TABLE users ( name VARCHAR(255) NOT NULL, age INT);INSERT INTO users (name, age) VALUES ('Alice', 25);INSERT INTO users (name, age) VALUES ('Bob', 30);INSERT INTO users (name, age) VALUES ('Charlie', 35);INSERT INTO users (name, age) VALUES ('Alice', 40);SELECT * FROM users;UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';SELECT * FROM users;

修改前表内容如下：

nameage
Alice25
Bob30
Charlie35
Alice40

1 ORACLE

执行报错

ORA-00971: missing SET keyword

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

2 sqlite

执行报错

Error: near line 12: near ".": syntax error

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

3 PG

执行报错

psql:commands.sql:12: ERROR: column "a" of relation "users" does not existLINE 1: UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

4 MYSQL mariadb

执行正常

后表内容已经更新，如下：

nameage
Alice111
Bob30
Charlie35
Alice111

1.2 测试用例UPDATE users as a SET a.age = 111 WHERE name = Alice ;

UPDATE users as a SET a.age = 111 WHERE name = Alice ;

CREATE TABLE users ( name VARCHAR(255) NOT NULL, age INT);INSERT INTO users (name, age) VALUES ('Alice', 25);INSERT INTO users (name, age) VALUES ('Bob', 30);INSERT INTO users (name, age) VALUES ('Charlie', 35);INSERT INTO users (name, age) VALUES ('Alice', 40);SELECT * FROM users;UPDATE users as a SET a.age = 111 WHERE name = 'Alice';SELECT * FROM users;

修改前表内容如下：

nameage
Alice25
Bob30
Charlie35
Alice40

1 ORACLE

执行报错

ORA-00971: missing SET keyword

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

2 sqlite

执行报错

Error: near line 12: near ".": syntax error

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

3 PG

执行报错

psql:commands.sql:12: ERROR: column "a" of relation "users" does not existLINE 1: UPDATE users as a SET a.age = 111 WHERE name = 'Alice';

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

4 MYSQL mariadb

执行正常

后表内容已经更新，如下：

nameage
Alice111
Bob30
Charlie35
Alice111

1.3 测试用例UPDATE users as a SET age = 111 WHERE a.name = Alice ;

UPDATE users as a SET age = 111 WHERE a.name = Alice ;

CREATE TABLE users ( name VARCHAR(255) NOT NULL, age INT);INSERT INTO users (name, age) VALUES ('Alice', 25);INSERT INTO users (name, age) VALUES ('Bob', 30);INSERT INTO users (name, age) VALUES ('Charlie', 35);INSERT INTO users (name, age) VALUES ('Alice', 40);SELECT * FROM users;UPDATE users as a SET age = 111 WHERE a.name = 'Alice';SELECT * FROM users;

修改前表内容如下：

nameage
Alice25
Bob30
Charlie35
Alice40

1 ORACLE

执行报错

ORA-00971: missing SET keyword

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

2 sqlite

执行正常

后表内容已经更新，如下：

nameage
Alice111
Bob30
Charlie35
Alice111

3 PG

执行正常

后表内容已经更新，如下：

nameage
Bob30
Charlie35
Alice111
Alice111

4 MYSQL mariadb

执行正常

后表内容已经更新，如下：

nameage
Alice111
Bob30
Charlie35
Alice111

1.4 测试用例UPDATE users as a SET age = 111 WHERE name = Alice ;

UPDATE users as a SET age = 111 WHERE name = Alice ;

CREATE TABLE users ( name VARCHAR(255) NOT NULL, age INT);INSERT INTO users (name, age) VALUES ('Alice', 25);INSERT INTO users (name, age) VALUES ('Bob', 30);INSERT INTO users (name, age) VALUES ('Charlie', 35);INSERT INTO users (name, age) VALUES ('Alice', 40);SELECT * FROM users;UPDATE users as a SET age = 111 WHERE name = 'Alice';SELECT * FROM users;

修改前表内容如下：

nameage
Alice25
Bob30
Charlie35
Alice40

1 ORACLE

执行报错

ORA-00971: missing SET keyword

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

2 sqlite

执行正常

后表内容已经更新，如下：

nameage
Alice111
Bob30
Charlie35
Alice111

3 PG

执行正常

后表内容已经更新，如下：

nameage
Bob30
Charlie35
Alice111
Alice111

4 MYSQL mariadb

执行正常

后表内容已经更新，如下：

nameage
Alice111
Bob30
Charlie35
Alice111

2 delete

2.1 测试用例delete users as a from a WHERE a.name = Alice ;

delete users as a from a WHERE a.name = Alice ;

CREATE TABLE users ( name VARCHAR(255) NOT NULL, age INT);INSERT INTO users (name, age) VALUES ('Alice', 25);INSERT INTO users (name, age) VALUES ('Bob', 30);INSERT INTO users (name, age) VALUES ('Charlie', 35);INSERT INTO users (name, age) VALUES ('Alice', 40);SELECT * FROM users;delete users as a from a WHERE a.name = 'Alice';SELECT * FROM users;

修改前表内容如下：

nameage
Alice25
Bob30
Charlie35
Alice40

1 ORACLE

执行报错

ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'deleteusers '

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

2 sqlite

执行报错

Error: near line 12: near "users": syntax error

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

3 PG

执行报错

psql:commands.sql:12: ERROR: syntax error at or near "users"LINE 1: delete users as a from a WHERE a.name = 'Alice';

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

4 MYSQL mariadb

执行报错

ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as a from a WHERE a.name = 'Alice'' at line 1

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

2.2 测试用例delete users as a from a WHERE name = Alice ;

delete users as a from a WHERE name = Alice ;

CREATE TABLE users ( name VARCHAR(255) NOT NULL, age INT);INSERT INTO users (name, age) VALUES ('Alice', 25);INSERT INTO users (name, age) VALUES ('Bob', 30);INSERT INTO users (name, age) VALUES ('Charlie', 35);INSERT INTO users (name, age) VALUES ('Alice', 40);SELECT * FROM users;delete users as a from a WHERE name = 'Alice';SELECT * FROM users;

修改前表内容如下：

nameage
Alice25
Bob30
Charlie35
Alice40

1 ORACLE

执行报错

ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'deleteusers '

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

2 sqlite

执行报错

Error: near line 12: near "users": syntax error

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

3 PG

执行报错

psql:commands.sql:12: ERROR: syntax error at or near "users"LINE 1: delete users as a from a WHERE name = 'Alice';

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

4 MYSQL mariadb

执行报错

ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as a from a WHERE name = 'Alice'' at line 1

之后查询表内容未发生变化，如下：

nameage
Alice25
Bob30
Charlie35
Alice40

总结

数据库中update与delete使用表别名的深入研究

推荐游戏

更多+ 

8B8.CN

晋ICP备18009649号-2 EMALL：GG.E@163.COM