數(shù)據(jù)庫中update與delete使用表別名的深入研究
總結(jié)
Update
SQL語句示例 | Oracle | SQLite | PostgreSQL | MYSQL & mariadb |
---|---|---|---|---|
UPDATE users as a SET a.age = 111 WHERE a.name = 'Alice'; | 報(bào)錯(cuò) | 報(bào)錯(cuò) | 報(bào)錯(cuò) | 正常執(zhí)行 |
UPDATE users as a SET a.age = 111 WHERE name = 'Alice'; | 報(bào)錯(cuò) | 報(bào)錯(cuò) | 報(bào)錯(cuò) | 正常執(zhí)行 |
UPDATE users as a SET age = 111 WHERE a.name = 'Alice'; | 報(bào)錯(cuò) | 正常執(zhí)行 | 正常執(zhí)行 | 正常執(zhí)行 |
UPDATE users as a SET age = 111 WHERE name = 'Alice'; | 報(bào)錯(cuò) | 正常執(zhí)行 | 正常執(zhí)行 | 正常執(zhí)行 |
Delete
SQL語句示例 | Oracle | SQLite | PostgreSQL | MYSQL & mariadb |
---|---|---|---|---|
delete from users as a WHERE a.name = 'Alice'; | 報(bào)錯(cuò) | 報(bào)錯(cuò) | 報(bào)錯(cuò) | 報(bào)錯(cuò) |
delete from users as a WHERE name = 'Alice'; | 報(bào)錯(cuò) | 報(bào)錯(cuò) | 報(bào)錯(cuò) | 報(bào)錯(cuò) |
1 Update
1.1 測(cè)試用例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;
修改前表內(nèi)容如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
1 ORACLE
執(zhí)行報(bào)錯(cuò)
ORA-00971: missing SET keyword
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
2 sqlite
執(zhí)行報(bào)錯(cuò)
Error: near line 12: near ".": syntax error
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
3 PG
執(zhí)行報(bào)錯(cuò)
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';
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
4 MYSQL & mariadb
執(zhí)行正常
后表內(nèi)容已經(jīng)更新,如下:
name | age |
---|---|
Alice | 111 |
Bob | 30 |
Charlie | 35 |
Alice | 111 |
1.2 測(cè)試用例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;
修改前表內(nèi)容如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
1 ORACLE
執(zhí)行報(bào)錯(cuò)
ORA-00971: missing SET keyword
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
2 sqlite
執(zhí)行報(bào)錯(cuò)
Error: near line 12: near ".": syntax error
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
3 PG
執(zhí)行報(bào)錯(cuò)
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';
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
4 MYSQL & mariadb
執(zhí)行正常
后表內(nèi)容已經(jīng)更新,如下:
name | age |
---|---|
Alice | 111 |
Bob | 30 |
Charlie | 35 |
Alice | 111 |
1.3 測(cè)試用例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;
修改前表內(nèi)容如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
1 ORACLE
執(zhí)行報(bào)錯(cuò)
ORA-00971: missing SET keyword
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
2 sqlite
執(zhí)行正常
后表內(nèi)容已經(jīng)更新,如下:
name | age |
---|---|
Alice | 111 |
Bob | 30 |
Charlie | 35 |
Alice | 111 |
3 PG
執(zhí)行正常
后表內(nèi)容已經(jīng)更新,如下:
name | age |
---|---|
Bob | 30 |
Charlie | 35 |
Alice | 111 |
Alice | 111 |
4 MYSQL & mariadb
執(zhí)行正常
后表內(nèi)容已經(jīng)更新,如下:
name | age |
---|---|
Alice | 111 |
Bob | 30 |
Charlie | 35 |
Alice | 111 |
1.4 測(cè)試用例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;
修改前表內(nèi)容如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
1 ORACLE
執(zhí)行報(bào)錯(cuò)
ORA-00971: missing SET keyword
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
2 sqlite
執(zhí)行正常
后表內(nèi)容已經(jīng)更新,如下:
name | age |
---|---|
Alice | 111 |
Bob | 30 |
Charlie | 35 |
Alice | 111 |
3 PG
執(zhí)行正常
后表內(nèi)容已經(jīng)更新,如下:
name | age |
---|---|
Bob | 30 |
Charlie | 35 |
Alice | 111 |
Alice | 111 |
4 MYSQL & mariadb
執(zhí)行正常
后表內(nèi)容已經(jīng)更新,如下:
name | age |
---|---|
Alice | 111 |
Bob | 30 |
Charlie | 35 |
Alice | 111 |
2 delete
2.1 測(cè)試用例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;
修改前表內(nèi)容如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
1 ORACLE
執(zhí)行報(bào)錯(cuò)
ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete users '
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
2 sqlite
執(zhí)行報(bào)錯(cuò)
Error: near line 12: near "users": syntax error
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
3 PG
執(zhí)行報(bào)錯(cuò)
psql:commands.sql:12: ERROR: syntax error at or near "users" LINE 1: delete users as a from a WHERE a.name = 'Alice';
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
4 MYSQL & mariadb
執(zhí)行報(bào)錯(cuò)
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
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
2.2 測(cè)試用例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;
修改前表內(nèi)容如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
1 ORACLE
執(zhí)行報(bào)錯(cuò)
ORA-03048: SQL reserved word 'AS' is not syntactically valid following 'delete users '
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
2 sqlite
執(zhí)行報(bào)錯(cuò)
Error: near line 12: near "users": syntax error
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
3 PG
執(zhí)行報(bào)錯(cuò)
psql:commands.sql:12: ERROR: syntax error at or near "users" LINE 1: delete users as a from a WHERE name = 'Alice';
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
4 MYSQL & mariadb
執(zhí)行報(bào)錯(cuò)
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
之后查詢表內(nèi)容未發(fā)生變化,如下:
name | age |
---|---|
Alice | 25 |
Bob | 30 |
Charlie | 35 |
Alice | 40 |
總結(jié)
到此這篇關(guān)于數(shù)據(jù)庫中update與delete使用表別名的文章就介紹到這了,更多相關(guān)update與delete使用表別名內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql如何按字段查詢重復(fù)的數(shù)據(jù)
這篇文章主要介紹了mysql如何按字段查詢重復(fù)的數(shù)據(jù)問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-05-05Mysql行轉(zhuǎn)列把逗號(hào)分隔的字段拆分成多行兩種方法
在數(shù)據(jù)庫中有時(shí)候我們需要將一些行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù),這在處理數(shù)據(jù)透視表、報(bào)表生成等場(chǎng)景下非常常見,這篇文章主要給大家介紹了關(guān)于Mysql行轉(zhuǎn)列把逗號(hào)分隔的字段拆分成多行的兩種方法,需要的朋友可以參考下2024-05-05虛擬主機(jī)MySQL數(shù)據(jù)庫的備份與還原的方法
虛擬主機(jī)MySQL數(shù)據(jù)庫的備份與還原的方法...2007-07-07Mysql外鍵設(shè)置中的CASCADE、NO ACTION、RESTRICT、SET NULL
本文主要介紹了Mysql外鍵設(shè)置中的CASCADE、NO ACTION、RESTRICT、SET NULL,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-07-07詳解sql中的參照完整性(一對(duì)一,一對(duì)多,多對(duì)多)
這篇文章主要介紹了sql中的參照完整性,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-04-04詳解MySQL數(shù)據(jù)類型DECIMAL(N,M)中N和M分別表示的含義
關(guān)于MySQL數(shù)據(jù)類型decimal中n和m分別表示什么含義?本文就此問題作了簡(jiǎn)單論述,并創(chuàng)建相關(guān)表進(jìn)行驗(yàn)證,需要的朋友可以了解下。2017-10-10mysql優(yōu)化之query_cache_limit參數(shù)說明
query_cache_limit指定單個(gè)查詢能夠使用的緩沖區(qū)大小,缺省為1M,一般不需要優(yōu)化2021-07-07