delete语句及同时删除多表数据实现方法

2019-12-23 08:41栏目:计算机资讯

sql delete语句及同期删除多表数据达成方式delete [ from ] { table_name with ( table_hint_limited [ ...n ] ) | view_name | rowset_function_limited }

[ from { table_source } [ ,...n ] ]

[ where { search_condition | { [ current of { { [ global ] cursor_name } | cursor_variable_name } ] } } ]

from

是可选的主要字,可用在 delete 关键字与目的 table_name、view_name 或 rowset_function_limited 之间。

table_name

是要从内部删除行的表的名称。

在其功效域内的 table 变量、或是将 opendatasource 函数作为服务器名称的由四部分构成的表名还足以在 delete 语句中作为表源使用。

with (table_hint_limited [...n])

钦赐目的表所允许的三个或多个表提醒。供给有 with 关键字和圆括号。不容许有 readpast、nolock 和 readuncommitted。有关表提示的更加多音讯

多表关系sql删除语句

mysql教程 create table employee( - id int, - first_name varchar(15), - last_name varchar(15), - start_date date, - end_date date, - salary float(8,2), - city varchar(10), - description varchar(15) - );query ok, 0 rows affected (0.02 sec)

mysqlmysql create table job ( - id int, - title varchar(20) - );query ok, 0 rows affected (0.05 sec)

mysqlmysql insert into employee(id,first_name, last_name, start_date, end_date, salary, city, description) - values (1,'jason', 'martin', '19960725', '20060725', 1234.56, 'toronto', 'programmer');query ok, 1 row affected (0.00 sec)

mysqlmysql insert into employee(id,first_name, last_name, start_date, end_date, salary, city, description) - values(2,'alison', 'mathews', '19760321', '19860221', 6661.78, 'vancouver','tester');query ok, 1 row affected (0.00 sec)

mysqlmysql insert into employee(id,first_name, last_name, start_date, end_date, salary, city, description) - values(3,'james', 'smith', '19781212', '19900315', 6544.78, 'vancouver','tester');query ok, 1 row affected (0.02 sec)

mysqlmysql insert into employee(id,first_name, last_name, start_date, end_date, salary, city, description) - values(4,'celia', 'rice', '19821024', '19990421', 2344.78, 'vancouver','manager');query ok, 1 row affected (0.00 sec)

mysqlmysql insert into employee(id,first_name, last_name, start_date, end_date, salary, city, description) - values(5,'robert', 'black', '19840115', '19980808', 2334.78, 'vancouver','tester');query ok, 1 row affected (0.00 sec)

mysqlmysql insert into employee(id,first_name, last_name, start_date, end_date, salary, city, description) - values(6,'linda', 'green', '19870730', '19960104', 4322.78,'new york', 'tester');query ok, 1 row affected (0.00 sec)

mysqlmysql insert into employee(id,first_name, last_name, start_date, end_date, salary, city, description) - values(7,'david', 'larry', '19901231', '19980212', 7897.78,'new york', 'manager');query ok, 1 row affected (0.00 sec)

mysqlmysql insert into employee(id,first_name, last_name, start_date, end_date, salary, city, description) - values(8,'james', 'cat', '19960917', '20020415', 1232.78,'vancouver', 'tester');query ok, 1 row affected (0.00 sec)

mysqlmysql insert into job (id, title) values (1,'tester');query ok, 1 row affected (0.00 sec)

mysql insert into job (id, title) values (2,'accountant');query ok, 1 row affected (0.02 sec)

mysql insert into job (id, title) values (3,'developer');query ok, 1 row affected (0.00 sec)

mysql insert into job (id, title) values (4,'coder');query ok, 1 row affected (0.00 sec)

mysql insert into job (id, title) values (5,'director');query ok, 1 row affected (0.02 sec)

mysql insert into job (id, title) values (6,'mediator');query ok, 1 row affected (0.00 sec)

mysql insert into job (id, title) values (7,'proffessor');query ok, 1 row affected (0.00 sec)

mysql insert into job (id, title) values (8,'programmer');query ok, 1 row affected (0.00 sec)

mysql insert into job (id, title) values (9,'developer');query ok, 1 row affected (0.00 sec)

mysqlmysql select * from job; ------ ------------ | id | title | ------ ------------ | 1 | tester || 2 | accountant || 3 | developer || 4 | coder || 5 | director || 6 | mediator || 7 | proffessor || 8 | programmer || 9 | developer | ------ ------------ 9 rows in set (0.00 sec)

mysql select * from employee; ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- | id | first_name | last_name | start_date | end_date | salary | city | description | ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- | 1 | jason | martin | 1996-07-25 | 2006-07-25 | 1234.56 | toronto | programmer || 2 | alison | mathews | 1976-03-21 | 1986-02-21 | 6661.78 | vancouver | tester || 3 | james | smith | 1978-12-12 | 1990-03-15 | 6544.78 | vancouver | tester || 4 | celia | rice | 1982-10-24 | 1999-04-21 | 2344.78 | vancouver | manager || 5 | robert | black | 1984-01-15 | 1998-08-08 | 2334.78 | vancouver | tester || 6 | linda | green | 1987-07-30 | 1996-01-04 | 4322.78 | new york | tester || 7 | david | larry | 1990-12-31 | 1998-02-12 | 7897.78 | new york | manager || 8 | james | cat | 1996-09-17 | 2002-04-15 | 1232.78 | vancouver | tester | ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- 8 rows in set (0.00 sec)

mysqlmysqlmysqlmysql delete employee - from employee, job - where (employee.id = job.id) - and (job.id = 3);query ok, 1 row affected (0.00 sec)

mysqlmysqlmysql select * from employee; ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- | id | first_name | last_name | start_date | end_date | salary | city | description | ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- | 1 | jason | martin | 1996-07-25 | 2006-07-25 | 1234.56 | toronto | programmer || 2 | alison | mathews | 1976-03-21 | 1986-02-21 | 6661.78 | vancouver | tester || 4 | celia | rice | 1982-10-24 | 1999-04-21 | 2344.78 | vancouver | manager || 5 | robert | black | 1984-01-15 | 1998-08-08 | 2334.78 | vancouver | tester || 6 | linda | green | 1987-07-30 | 1996-01-04 | 4322.78 | new york | tester || 7 | david | larry | 1990-12-31 | 1998-02-12 | 7897.78 | new york | manager || 8 | james | cat | 1996-09-17 | 2002-04-15 | 1232.78 | vancouver | tester | ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- 7 rows in set (0.00 sec)

方法二

mysql delete ab - from authorbook as ab, authors as a - where ab.authid=a.authid and authln='a';query ok, 1 row affected (0.05 sec)

mysqlmysql drop table authorbook;query ok, 0 rows affected (0.06 sec)

mysql drop table books;query ok, 0 rows affected (0.05 sec)

mysql drop table authors;query ok, 0 rows affected (0.06 sec)

並且删除二个表

mysql delete employee, job - from employee, job - where (employee.id = job.id) - and (job.id = 3);query ok, 2 rows affected (0.01 sec)

mysqlmysqlmysqlmysql select * from employee; ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- | id | first_name | last_name | start_date | end_date | salary | city | description | ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- | 1 | jason | martin | 1996-07-25 | 2006-07-25 | 1234.56 | toronto | programmer || 2 | alison | mathews | 1976-03-21 | 1986-02-21 | 6661.78 | vancouver | tester || 4 | celia | rice | 1982-10-24 | 1999-04-21 | 2344.78 | vancouver | manager || 5 | robert | black | 1984-01-15 | 1998-08-08 | 2334.78 | vancouver | tester || 6 | linda | green | 1987-07-30 | 1996-01-04 | 4322.78 | new york | tester || 7 | david | larry | 1990-12-31 | 1998-02-12 | 7897.78 | new york | manager || 8 | james | cat | 1996-09-17 | 2002-04-15 | 1232.78 | vancouver | tester | ------ ------------ ----------- ------------ ------------ --------- ----------- ------------- 7 rows in set (0.00 sec)

mysqlmysql select * from job; ------ ------------ | id | title | ------ ------------ | 1 | tester || 2 | accountant || 4 | coder || 5 | director || 6 | mediator || 7 | proffessor || 8 | programmer || 9 | developer | ------ ------------ 8 rows in set (0.00 sec)

版权声明:本文由威尼斯网站发布于计算机资讯,转载请注明出处:delete语句及同时删除多表数据实现方法