数据库中update与delete使用表别名的深入研究
目录
- 总结
- 1 Update
- 1.1 测试用例UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';
- 1.2 测试用例UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
- 1.3 测试用例UPDATE users as a SET age = 111 WHERE a.name = 'Alice';
- 1.4 测试用例UPDATE users as a SET age = 111 WHERE name = 'Alice';
- 2 delete
- 2.1 测试用例delete users as a from a WHERE a.name = 'Alice';
- 2.2 测试用例delete users as a from a WHERE name = 'Alice';
- 总结
总结
Update
| SQL语句示例 | oracle | SQLite | PostgreSQL | mysql & 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语句示例 | Oracle | SQLite | PostgreSQL | MYSQL & 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 (napythonme, 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;
修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-00971: missing SET keyword
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
2 sqlite
执行报错
Error: near line 12: near ".": syntax error
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
3 PG
执行报错
psql:commands.sql:12: ERROR: column "a" of relation "users" does not exist LINE 1: UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
4 MYSQL & mariadb
执行正常
后表内容已经更新，如下：
| name | age |
|---|---|
| Alice | 111 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 111 |
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 jsINT
);
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;
修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-00971: missing SET keyword
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
2 sqlite
执行报错
Error: near line 12: near ".": syntax error
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
3 PG
执行报错
psql:commands.sql:12: ERROR: column "a" of relation "users" does not exist LINE 1: UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
4 MYSQL & mariadb
执行正常
后表内容已经更新，如下：
| name | age |
|---|---|
| Alice | 111 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 111 |
1.3 测试用例UPDATBgzAFrbE 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;
修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-00971: missing SET keyword
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
2 sqlite
执行正常
后表内容已经更新，如下：
| name | age |
|---|---|
| Alice | 111 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 111 |
3 PG
执行正常
后表内容已经更新，如下：
| name | age |
|---|---|
| Bob | 30 |
| Charlie | 35 |
| Alice | 111 |
| Alice | 111 |
4 MYSQL & mariadb
执行正常
后表内容已经更新，如下：
| name | age |
|---|---|
| Alice | 111 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 111 |
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;
修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-00971: missing SET keyword
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
2 sqlite
执行正常
后表内容已经更新，如下：
| name | age |
|---|---|
| Alice | 111 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 111 |
3 PG
执行正常
后表内容已经更新，如下：
| name | age |
|---|---|
| Bob | 30 |
| Charlie | 35 |
| Alice | 111 |
| Alice | 111 |
4 MYSQL & mariadb
执行正常
后表内容已经更新，如下：
| name | age |
|---|---|
| Alice | 111 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 111 |
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;
修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete users '
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
2 sqlite
执行报错
Error: near line 12: near "users": syntax error
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
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';
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
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
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
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;
修改前表内容如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
1 ORACLE
执行报错
ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete users '
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
2 sqlite
执行报错
Error: near line 12: near "users": syntax error
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
3 PG
执行报错
psql:commands.sql:12: ERROR: syntax error at or near "users" LINE 1: delete users as a from a WHERE name = 'Alice';
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
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
之后查询表内容未发生变化，如下：
| name | age |
|---|---|
| Alice | 25 |
| Bob | 30 |
| Charlie | 35 |
| Alice | 40 |
总结
到此这篇关于数据库中update与delete使用表别名的文章就介绍到这了,更多相关update与delete使用表别名内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！
加载中,请稍侯......
精彩评论