【数据库差异研究】update与delete利用表别名的研究

商道如狼道  金牌会员 | 2024-10-5 09:18:45 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 900|帖子 900|积分 2700




目次
⚛️总结
☪️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语句示例OracleSQLitePostgreSQLMYSQL & mariadbUPDATE 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 & mariadbdelete 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';
  1. CREATE TABLE users (
  2.     name VARCHAR(255) NOT NULL,
  3.     age INT
  4. );
  6. INSERT INTO users (name, age) VALUES ('Alice', 25);
  7. INSERT INTO users (name, age) VALUES ('Bob', 30);
  8. INSERT INTO users (name, age) VALUES ('Charlie', 35);
  9. INSERT INTO users (name, age) VALUES ('Alice', 40);
  11. SELECT * FROM users;
  12. UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';
  13. SELECT * FROM users;
复制代码
修改前表内容如下：
nameageAlice25Bob30Charlie35Alice40
   1 ORACLE
  执行报错
  1. ORA-00971: missing SET keyword
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  2 sqlite
  执行报错
  1. Error: near line 12: near ".": syntax error
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  3 PG
  执行报错
  1. psql:commands.sql:12: ERROR:  column "a" of relation "users" does not exist
  2. LINE 1: UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice';
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  4 MYSQL & mariadb
  执行正常
  后表内容已经更新，如下：
  nameageAlice111Bob30Charlie35Alice111  
♏1.2 测试用例UPDATE users as a SET a.age = 111 WHERE name = 'Alice';

UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
  1. CREATE TABLE users (
  2.     name VARCHAR(255) NOT NULL,
  3.     age INT
  4. );
  6. INSERT INTO users (name, age) VALUES ('Alice', 25);
  7. INSERT INTO users (name, age) VALUES ('Bob', 30);
  8. INSERT INTO users (name, age) VALUES ('Charlie', 35);
  9. INSERT INTO users (name, age) VALUES ('Alice', 40);
  11. SELECT * FROM users;
  12. UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
  13. SELECT * FROM users;
复制代码
修改前表内容如下：
nameageAlice25Bob30Charlie35Alice40
   1 ORACLE
  执行报错
  1. ORA-00971: missing SET keyword
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  2 sqlite
   执行报错
  1. Error: near line 12: near ".": syntax error
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  3 PG
    执行报错
  1. psql:commands.sql:12: ERROR:  column "a" of relation "users" does not exist
  2. LINE 1: UPDATE users as a SET a.age = 111 WHERE name = 'Alice';
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  4 MYSQL & mariadb
  执行正常
  后表内容已经更新，如下：
  nameageAlice111Bob30Charlie35Alice111  
♐1.3 测试用例UPDATE users as a SET age = 111 WHERE a.name = 'Alice';

UPDATE users as a SET age = 111 WHERE a.name = 'Alice';
  1. CREATE TABLE users (
  2.     name VARCHAR(255) NOT NULL,
  3.     age INT
  4. );
  6. INSERT INTO users (name, age) VALUES ('Alice', 25);
  7. INSERT INTO users (name, age) VALUES ('Bob', 30);
  8. INSERT INTO users (name, age) VALUES ('Charlie', 35);
  9. INSERT INTO users (name, age) VALUES ('Alice', 40);
  11. SELECT * FROM users;
  12. UPDATE users as a SET age = 111 WHERE a.name = 'Alice';
  13. SELECT * FROM users;
复制代码
修改前表内容如下：
nameageAlice25Bob30Charlie35Alice40
   1 ORACLE
   执行报错
  1. ORA-00971: missing SET keyword
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  2 sqlite
  执行正常
  后表内容已经更新，如下：
  nameageAlice111Bob30Charlie35Alice111  
  3 PG
  执行正常
  后表内容已经更新，如下：
  nameageBob30Charlie35Alice111Alice111  
  4 MYSQL & mariadb
  执行正常
  后表内容已经更新，如下：
  nameageAlice111Bob30Charlie35Alice111  
♑1.4 测试用例UPDATE users as a SET age = 111 WHERE name = 'Alice';

UPDATE users as a SET age = 111 WHERE name = 'Alice';
  1. CREATE TABLE users (
  2.     name VARCHAR(255) NOT NULL,
  3.     age INT
  4. );
  6. INSERT INTO users (name, age) VALUES ('Alice', 25);
  7. INSERT INTO users (name, age) VALUES ('Bob', 30);
  8. INSERT INTO users (name, age) VALUES ('Charlie', 35);
  9. INSERT INTO users (name, age) VALUES ('Alice', 40);
  11. SELECT * FROM users;
  12. UPDATE users as a SET age = 111 WHERE name = 'Alice';
  13. SELECT * FROM users;
复制代码
修改前表内容如下：
nameageAlice25Bob30Charlie35Alice40
   1 ORACLE
   执行报错
  1. ORA-00971: missing SET keyword
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  2 sqlite
  执行正常
  后表内容已经更新，如下：
  nameageAlice111Bob30Charlie35Alice111  
  3 PG
  执行正常
  后表内容已经更新，如下：
  nameageBob30Charlie35Alice111Alice111  
  4 MYSQL & mariadb
  执行正常
  后表内容已经更新，如下：
  nameageAlice111Bob30Charlie35Alice111  
☪️2 delete

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

delete users as a from a WHERE a.name = 'Alice';
  1. CREATE TABLE users (
  2.     name VARCHAR(255) NOT NULL,
  3.     age INT
  4. );
  6. INSERT INTO users (name, age) VALUES ('Alice', 25);
  7. INSERT INTO users (name, age) VALUES ('Bob', 30);
  8. INSERT INTO users (name, age) VALUES ('Charlie', 35);
  9. INSERT INTO users (name, age) VALUES ('Alice', 40);
  11. SELECT * FROM users;
  12. delete users as a from a WHERE a.name = 'Alice';
  13. SELECT * FROM users;
复制代码
修改前表内容如下：
nameageAlice25Bob30Charlie35Alice40
   1 ORACLE
   执行报错
  1. ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete
  2. users '
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  2 sqlite
   执行报错
  1. Error: near line 12: near "users": syntax error
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  3 PG
   执行报错
  1. psql:commands.sql:12: ERROR:  syntax error at or near "users"
  2. LINE 1: delete users as a from a WHERE a.name = 'Alice';
复制代码
 之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  4 MYSQL & mariadb
   执行报错
  1. 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
复制代码
 之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
♈2.2 测试用例delete users as a from a WHERE name = 'Alice';

delete users as a from a WHERE name = 'Alice';
  1. CREATE TABLE users (
  2.     name VARCHAR(255) NOT NULL,
  3.     age INT
  4. );
  6. INSERT INTO users (name, age) VALUES ('Alice', 25);
  7. INSERT INTO users (name, age) VALUES ('Bob', 30);
  8. INSERT INTO users (name, age) VALUES ('Charlie', 35);
  9. INSERT INTO users (name, age) VALUES ('Alice', 40);
  11. SELECT * FROM users;
  12. delete users as a from a WHERE name = 'Alice';
  13. SELECT * FROM users;
复制代码
修改前表内容如下：
nameageAlice25Bob30Charlie35Alice40
   1 ORACLE
   执行报错
  1. ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete
  2. users '
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  2 sqlite
   执行报错
  1. Error: near line 12: near "users": syntax error
复制代码
之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  3 PG
   执行报错
  1. psql:commands.sql:12: ERROR:  syntax error at or near "users"
  2. LINE 1: delete users as a from a WHERE name = 'Alice';
复制代码
 之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  
  4 MYSQL & mariadb
   执行报错
  1. 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
复制代码
 之后查询表内容未发生变革，如下：
  nameageAlice25Bob30Charlie35Alice40  




免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

商道如狼道

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表