Удаление базы данныхdelete a database

Example of MySQL Delete Query

Before we go into more details discussion the DELETE command, let’s insert some sample data into the movies table to work with.


INSERT INTO  `movies` (`title`, `director`, `year_released`, `category_id`) VALUES ('The Great Dictator', 'Chalie Chaplie', 1920, 7);
INSERT INTO `movies` (`title`, `director`, `category_id`) VALUES ('sample movie', 'Anonymous', 8);
INSERT INTO  movies (`title`, `director`, `year_released`, `category_id`) VALUES ('movie 3', 'John Brown', 1920, 8);

Executing the above script adds three (3) movies into the movies table. Before we go any further into our lesson, let’s get all the movies in our table. The script shown below does that.

SELECT * FROM `movies`;

Executing the above script gives us the following results.

movie_id itle director year_released category_id
1 Pirates of the Caribean 4 Rob Marshall 2011 1
2 Forgetting Sarah Marshal Nicholas Stoller 2008 2
3 X-Men NULL 2008 NULL
4 Code Name Black Edgar Jimz 2010 NULL
5 Daddy’s Little Girls NULL 2007 8
6 Angels and Demons NULL 2007 6
7 Davinci Code NULL 2007 6
9 Honey mooners John Schultz 2005 8
16 67% Guilty NULL 2012 NULL
18 The Great Dictator Chalie Chaplie 1920 7
19 sample movie Anonymous NULL 8
20 movie 3 John Brown 1920 8

 Let’s suppose that the Myflix video library no longer wishes to be renting out «The Great Dictator» to its members and they want it removed from the database. Its movie id is 18, we can use the script shown below to delete its row from the movies table.

DELETE FROM `movies` WHERE `movie_id` = 18;

Executing the above script in MySQL WorkBench against the Myflix deletes the movie with id 18 from the database table.

Let’s see the current status of movies table.

SELECT * FROM `movies`;
movie_id title director year_released category_id
1 Pirates of the Caribean 4 Rob Marshall 2011 1
2 Forgetting Sarah Marshal Nicholas Stoller 2008 2
3 X-Men NULL 2008 NULL
4 Code Name Black Edgar Jimz 2010 NULL
5 Daddy’s Little Girls NULL 2007 8
6 Angels and Demons NULL 2007 6
7 Davinci Code NULL 2007 6
9 Honey mooners John Schultz 2005 8
16 67% Guilty NULL 2012 NULL
19 sample movie Anonymous NULL 8
20 movie 3 John Brown 1920 8

NOTE:

  • the movie with id 18 has not been return in the query result set.
  • you cannot delete a single column for a table. You can delete an entire row.

Let’s say we  have a list of movies we want to delete . We can use the WHERE clause along with IN.

DELETE FROM `movies` WHERE `movie_id`  IN (20,21);

Executing the above script deletes movies with IDs 20 and 21 from our movies table.

Summary

  • The delete command is used to remove data that is no longer required from a table.
  • The «WHERE clause» is used to limit the number of rows affected by the DELETE query.
  • Once data has been deleted, it cannot be recovered, it is therefore strongly recommend make backups before deleting data.

Example — Using EXISTS with the DELETE Statement

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can’t list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.

In this example, we have a table called customers with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

And a table called orders with the following data:

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
4 4000 2016/04/20
5 NULL 2016/05/01

Enter the following DELETE statement:

DELETE FROM orders
WHERE EXISTS
  (SELECT *
   FROM customers
   WHERE customers.customer_id = orders.customer_id
   AND customers.last_name = 'Jackson');

There will be 1 record deleted. Select the data from the orders table again:

SELECT * FROM orders;

These are the results that you should see:

order_id customer_id order_date
1 7000 2016/04/18
2 5000 2016/04/18
3 8000 2016/04/19
5 NULL 2016/05/01

This example would delete all records from the orders table where there is a record in the customers table with the last_name of ‘Jackson’ and a matching customer_id value in both tables. In this example, the record for order_id=4 was deleted.

If you want to determine the number of rows that will be deleted, you can run the following SQL SELECT statement before performing the delete.

SELECT COUNT(*) FROM orders
WHERE EXISTS
  (SELECT *
   FROM customers
   WHERE customers.customer_id = orders.customer_id
   AND customers.last_name = 'Jackson');

This will return number of records that will be deleted when you execute the DELETE statement.

Инструкция DELETE

Инструкция DELETE удаляет строки из таблицы. Подобно инструкции INSERT, эта инструкция также имеет две различные формы:



Соглашения по синтаксису

Удаляются все строки, которые удовлетворяют условие в предложении WHERE. Явно перечислять столбцы в инструкции DELETE не то чтобы нет необходимости, а даже не разрешается, поскольку эта инструкция оперирует строками, а не столбцами. Использование первой формы инструкции DELETE показано в примере ниже, в котором происходит удаление из таблицы Works_on всех сотрудников с должностью ‘Менеджер’:

Предложение WHERE инструкции DELETE может содержать вложенный запрос, как это показано в примере ниже:

Поскольку сотрудница Вершинина уволилась, из базы данных удаляются все записи, связанные с ней. Запрос из этого примера можно также выполнить с помощью предложения FROM, как это показано ниже. В данном случае семантика этого предложения такая же, как и предложения FROM в инструкции UPDATE.

Использование предложения WHERE в инструкции DELETE не является обязательным. Если это предложение отсутствует, то из таблицы удаляются все строки:

Инструкции DELETE и DROP TABLE существенно отличаются друг от друга. Инструкция DELETE удаляет (частично или полностью) содержимое таблицы, тогда как инструкция DROP TABLE удаляет как содержимое, так и схему таблицы. Таким образом, после удаления всех строк посредством инструкции DELETE таблица продолжает существовать в базе данных, а после выполнения инструкции DROP TABLE таблица больше не существует.

Example — DELETE Statement with One Condition

If you run a DELETE statement with no conditions in the WHERE clause, all of the records from the table will be deleted. As a result, you will most often include a WHERE clause with at least one condition in your DELETE statement.

Let’s start with a simple example of a DELETE query that has one condition in the WHERE clause.

In this example, we have a table called suppliers with the following data:

supplier_id supplier_name city state
100 Microsoft Redmond Washington
200 Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

Enter the following DELETE statement:

Try It

DELETE FROM suppliers
WHERE supplier_name = 'Microsoft';

There will be 1 record deleted. Select the data from the suppliers table again:

SELECT * FROM suppliers;

These are the results that you should see:

supplier_id supplier_name city state
200 Mountain View California
300 Oracle Redwood City California
400 Kimberly-Clark Irving Texas
500 Tyson Foods Springdale Arkansas
600 SC Johnson Racine Wisconsin
700 Dole Food Company Westlake Village California
800 Flowers Foods Thomasville Georgia
900 Electronic Arts Redwood City California

This example would delete all records from the suppliers table where the supplier_name is ‘Microsoft’.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SELECT statement before performing the delete:

Try It

SELECT COUNT(*)
FROM suppliers
WHERE supplier_name = 'Microsoft';

This quey will return number of records that will be deleted when you execute the DELETE statement.

Delete Data From a MySQL Table Using MySQLi and PDO

The DELETE statement is used to delete records from a table:

DELETE FROM table_name WHERE some_column = some_value

Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!


To learn more about SQL, please visit our SQL tutorial.

Let’s look at the «MyGuests» table:

id firstname lastname email reg_date
1 John Doe john@example.com 2014-10-22 14:26:15
2 Mary Moe mary@example.com 2014-10-23 10:22:30
3 Julie Dooley julie@example.com 2014-10-26 10:48:23

The following examples delete the record with id=3 in the «MyGuests» table:

Example (MySQLi Object-oriented)

<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection$conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) {   die(«Connection failed: » . $conn->connect_error);} // sql to delete a record $sql = «DELETE FROM MyGuests WHERE id=3»;if ($conn->query($sql) === TRUE) {  echo «Record deleted successfully»;} else {   echo «Error deleting record: » . $conn->error;}$conn->close(); ?>

Example (MySQLi Procedural)

<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) {   die(«Connection failed: » . mysqli_connect_error());}// sql to delete a record $sql = «DELETE FROM MyGuests WHERE id=3»;if (mysqli_query($conn, $sql)) {  echo «Record deleted successfully»;} else {   echo «Error deleting record: » . mysqli_error($conn);}mysqli_close($conn); ?>

Example (PDO)

<?php$servername = «localhost»;$username = «username»; $password = «password»;$dbname = «myDBPDO»;try {  $conn = new PDO(«mysql:host=$servername;dbname=$dbname», $username, $password);   // set the PDO error mode to exception  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   // sql to delete a record   $sql = «DELETE FROM MyGuests WHERE id=3»;  // use exec() because no results are returned  $conn->exec($sql);   echo «Record deleted successfully»;} catch(PDOException $e) {  echo $sql . «<br>» . $e->getMessage(); }$conn = null; ?>

After the record is deleted, the table will look like this:

id firstname lastname email reg_date
1 John Doe john@example.com 2014-10-22 14:26:15
2 Mary Moe mary@example.com 2014-10-23 10:22:30

Другие инструкции и предложения Transact-SQL для модификации таблиц

Сервер SQL Server поддерживает следующие дополнительные инструкции и предложения для модификации таблиц:

  • инструкцию TRUNCATE TABLE;

  • инструкцию MERGE;

  • предложение OUTPUT.

Эти инструкции и предложение рассматриваются в последующих подразделах.

Инструкция TRUNCATE TABLE

Инструкция TRUNCATE TABLE является более быстрой версией инструкции DELETE без предложения WHERE. Эта инструкция удаляет все строки таблицы более быстро, чем инструкция DELETE, поскольку она удаляет содержимое постранично, тогда как инструкция DELETE делает это построчно. Инструкция TRUNCATE TABLE является расширением Transact-SQL стандарта SQL. Еще одним важным отличием этой инструкции является то, что она сбрасывает индекс столбца, для которого указано свойство автоинкремента IDENTITY.

Инструкция TRUNCATE TABLE имеет следующий синтаксис:

Инструкция MERGE

Инструкция MERGE объединяет последовательность инструкций INSERT, UPDATE и DELETE в одну элементарную инструкцию, в зависимости от существования записи (строки). Иными словами, можно синхронизировать две разные таблицы, чтобы модифицировать содержимое таблицы назначения в зависимости от различий, обнаруженных в таблице-источнике.

Основной областью применения для инструкции MERGE является среда хранилищ данных, где таблицы необходимо периодически обновлять, чтобы отражать новые данные, прибывающие с систем оперативной обработки транзакций OLTP (On-Line Transaction Processing). Эти данные могут содержать изменения существующих строк таблиц и/или новый строки, которые нужно вставить в таблицы. Если строка в новых данных соответствует записи, которая уже имеется в таблице, выполняется инструкция UPDATE или DELETE. В противном случае выполняется инструкция INSERT.

Альтернативно, вместо инструкции MERGE можно использовать последовательность инструкций INSERT, UPDATE и DELETE, в которых для каждой строки решается, какую операцию выполнять: вставку, удаление или обновление. Но этот подход имеет значительный недостаток, связанный с производительностью: в нем требуется выполнять несколько проходов по данным, а данные обрабатываются по принципу «запись за записью».

Предложение OUTPUT

По умолчанию единым видимым результатом выполнения инструкции INSERT, UPDATE или DELETE является только сообщение о количестве модифицированных строк, например «3 rows DELETED» (удалены 3 строки) и система не сохраняет информацию о модифицированных данных. Если такой видимый результат не удовлетворяет вашим требованиям, то можно использовать предложение OUTPUT, которое выводит модифицированные, вставленные или удаленные строки.

Предложение OUTPUT также применимо с инструкцией MERGE, для которой оно выводит все модифицированные строки в виде таблицы.

Результаты выполненных операций соответствующих инструкций предложение OUTPUT выводит в таблицах inserted и deleted. Кроме этого, чтобы заполнить таблицы, в предложении OUTPUT требуется использовать выражение INTO. Поэтому для сохранения результата используется табличная переменная.

В примере ниже показано использование инструкции OUTPUT с инструкцией DELETE:

При условии, что содержимое таблицы находится в исходном состоянии, выполнение запроса в примере дает следующий результат:

В этом примере сначала объявляется табличная переменная @deleteTable с двумя столбцами: Id и LastName. В этой таблице будут сохранены удаленные строки. Синтаксис инструкции DELETE расширен предложением OUTPUT: «OUTPUT deleted.Id, deleted.LastName INTO @deleteTable». Посредством этого предложения система сохраняет удаленные строки в таблице deleted, содержимое которой потом копируется в переменную @deleteTable.

В примере ниже показано использование предложения OUTPUT в инструкции UPDATE:

Результат выполнения этого запроса:

SQL Справочник

SQL Ключевые слова ADD ADD CONSTRAINT ALTER ALTER COLUMN ALTER TABLE ALL AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE

MySQL Функции Функции строк ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Функции чисел ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Функции дат ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Функции расширений BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION

SQL Server функции Функции строк ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER Функции чисел ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN Функции дат CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR Функции расширений CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME

MS Access функции Функции строк Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase Функции чисел Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val Функции дат Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year Другие функции CurrentUser Environ IsDate IsNull IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

Синтаксис оператора DELETE FROM

DELETE  FROM table_name
    
    
    

или

DELETE  table_name  ...]
    FROM table-references
    

или

DELETE 
    FROM table_name,  ...]
    USING table-references
    

Команда DELETE удаляет из таблицы table_name все записи, удовлетворяющие условию where_definition. Если условие WHERE where_definition не задано, то из таблицы table_name удаляются все записи.

Команда DELETE возвращает количество удаленных записей.

Следует учитывать, что если в команде DELETE не задано условие WHERE where_definition, то команда возвратит 0, хотя записи были удалены.

Если все же необходимо удалить все записи и узнать их количество, то в условии where_definition нужно задать заведомо истинное значение:

DELETE FROM
    `users`
WHERE
    1=1

Но в этом случае выполнение команды происходит значительно медленнее.

LOW_PRIORITY
— Если указано это ключевое слово, то удаление записей из таблицы table_name не будет произведено до тех пор, пока другие процессы не закончат чтение их этой таблицы.
QUICK
— Если указано это ключевое слово, то при удалении записей обработчик таблицы не будет объединять индексы, что может ускорить операцию удаления.
table_name
— Имя таблицы в базе данных, из которой будет происходить удаление строк.
LIMIT
— Задает максимальное количество строк, которые могут быть удалены за текущий запрос.
Следующий запрос удалит из таблицы users только 5 строк:
DELETE FROM
    `users`
LIMIT
    5
ORDER BY
— Задает имя поля, или имена полей через запятую, по которым происходит сортировка удаляемых записей.
Эта возможность актуальна при необходимости удалить только определенное кол-во записей, отсортированных по какому-либо свойству.
Следующий пример удалит из таблицы users запись самого молодого человека.
Допустим таблица users имеет следующий вид:
CREATE TABLE
    `users`(
        `name` VARCHAR(255) NOT NULL,
        `age` SMALLINT(3) NOT NULL
    )
Запишем в нее несколько записей:
INSERT INTO
    `users` (
        `name`, `age`
    )
VALUES
    ('Миша', 25),
    ('Ксюша', 15),
    ('Настя', 12),
    ('Саша', 26),
    ('Дима', 30)
Таблица users имеет значения:
SELECT
    *
FROM
    `users`
+-------+-----+
| name  | age |
+-------+-----+
| Миша  |  25 |
| Ксюша |  15 |
| Настя |  12 |
| Саша  |  26 |
| Дима  |  30 |
+-------+-----+
5 rows in set (0.00 sec)
Удаляем самого молодого:
DELETE FROM
    `users`
ORDER BY
    `age`
LIMIT
    1
Теперь таблица имеет значения:
SELECT
    *
FROM
    `users`
+-------+-----+
| name  | age |
+-------+-----+
| Миша  |  25 |
| Ксюша |  15 |
| Саша  |  26 |
| Дима  |  30 |
+-------+-----+
4 rows in set (0.00 sec)
Можем также удалить самого старого:
DELETE FROM
    `users`
ORDER BY
    `age` DESC
LIMIT
    1
Теперь таблица имеет значения:
SELECT
    *
FROM
    `users`
+-------+-----+
| name  | age |
+-------+-----+
| Миша  |  25 |
| Ксюша |  15 |
| Саша  |  26 |
+-------+-----+
3 rows in set (0.00 sec)
Сортировка удаляемых записей при помощи команды ORDER BY появилась в MySQL начиная с версии 4.0.
FROM table-references и USING …
— Позволяют удалять записи сразу из нескольких таблиц руководствуясь многотабличным условием WHERE where_definition.
Следующие два примера равнозначны по производимым действиям и удаляют одинаковые записи в таблицах table_1 и table_2 и используя при поиске значений еще и таблицу table_3:
DELETE
    `table_1`,
    `table_2`
FROM
    `table_1`,
    `table_2`,
    `table_3`
WHERE
    `table_1`.`id`=`table_2`.`id` &&
    `table_2`.`id`=`table_3`.`id`
Аналогичный запрос:
DELETE FROM
    `table_1`,
    `table_2`
USING
    `table_1`,
    `table_2`,
    `table_3`
WHERE
    `table_1`.`id`=`table_2`.`id` &&
    `table_2`.`id`=`table_3`.`id`
Возможность многотабличного удаления записей появилась в MySQL начиная с версии 4.0.

Инструкция UPDATE

Инструкция UPDATE используется для модифицирования строк таблицы. Эта инструкция имеет следующую общую форму:



Соглашения по синтаксису

Строки таблицы tab_name выбираются для изменения в соответствии с условием в предложении WHERE. Значения столбцов каждой модифицируемой строки изменяются с помощью предложения SET инструкции UPDATE, которое соответствующему столбцу присваивает выражение (обычно) или константу. Если предложение WHERE отсутствует, то инструкция UPDATE модифицирует все строки таблицы. С помощью инструкции UPDATE данные можно модифицировать только в одной таблице.

В примере ниже инструкция UPDATE изменяет всего лишь одну строку таблицы Works_on, поскольку комбинация столбцов EmpId и ProjectNumber является первичным ключом этой таблицы и, следственно, она однозначна. В данном примере изменяется должность сотрудника, значение которого было ранее неизвестно или имело значение NULL:

В примере ниже значения строкам таблицы присваиваются посредством выражения. Запрос пересчитывает бюджеты всех проектов с долларов на евро:

В данном примере изменяются все строки таблицы Project, поскольку в запросе отсутствует предложение WHERE.

В примере ниже в предложении WHERE инструкции UPDATE используется вложенный запрос. Поскольку применяется оператор IN, то этот запрос может возвратить более одной строки:

Согласно этому запросу, для сотрудницы Вершининой Натальи во всех ее проектах в столбце ее должности присваивается значение NULL. Запрос в этом примере можно также выполнить посредством предложения FROM инструкции UPDATE. В предложении FROM указываются имена таблиц, которые обрабатываются инструкцией UPDATE. Все эти таблицы должны быть в дальнейшем соединены. Применение предложения FROM показано в примере ниже. Логически, этот пример идентичен предыдущему:

В примере ниже показано использование выражения CASE в инструкции UPDATE. (Подробное рассмотрение этого выражения описывалось ранее.) В данном примере нужно увеличить бюджет всех проектов на определенное число процентов (20, 10 или 5), в зависимости от исходной суммы бюджета: чем меньше бюджет, тем больше должно быть его процентное увеличение:

DELETE

The command is used to delete existing records in a table.

The following SQL statement deletes the customer «Alfreds Futterkiste» from the «Customers» table:

DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;

Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

The following SQL statement deletes all rows in the «Customers» table, without deleting the table. This means that the table structure, attributes, and indexes will be intact:

Поиск подстроки в строке средствами sql

Для вычисления позиции подстроки в строке в языке sql существует несколько функций. Первая, которую мы рассмотрим, функция POSITION:

integer POSITION(substr string IN str string)


Возвращает номер позиции первого вхождения подстроки substr в строке str и возвращает 0 если подстрока не найдена. Функция POSITION может работать с многобайтовыми символами.

Пример:

SELECT POSITION (‘cd’ IN ‘abcdcde’); Результат: 3 SELECT POSITION (‘xy’ IN ‘abcdcde’); Результат: 0

Следующая функция LOCATE позволяет начинать поиск подстроки с определенной позиции:

integer LOCATE(substr string, str string, pos integer)

Возвращает позицию первого вхождения подстроки substr в строке str, начиная с позиции pos. Если параметр pos не задан, то поиск осуществляется с начала строки. Если подстрока substr не найдена, то возвращает 0. Поддерживает многобайтовые символы.

Пример:

SELECT LOCATE (‘cd’, ‘abcdcdde’, 5); Результат: 5 SELECT LOCATE (‘cd’, ‘abcdcdde’); Результат: 3

Аналогом функций POSITION и LOCATE является функция INSTR:

integer INSTR(str string, substr string)

Также как и функции выше возвращает позицию первого вхождения подстроки substr в строке str. Единственное отличие от функций POSITION и LOCATE то, что аргументы поменяны местами.

Далее рассмотрим функции, которые помогают получить подстроку.

Первыми рассмотрим сразу две функции LEFT и RIGHT, которые похожи по своему действию:

string LEFT(str string, len integer)string RIGHT(str string, len integer)

Функция LEFT возвращает len первых символов из строки str, а функция RIGHT столько же последних. Поддерживают многобайтовые символы.

Пример:

SELECT LEFT (‘Москва’, 3); Результат: Мос SELECT RIGHT (‘Москва’, 3); Результат: ква

Далее рассмотрим одинаковые по итоговому результату функции SUBSTRING и MID:

string SUBSTRING(str string, pos integer, len integer)string MID(str string, pos integer, len integer)

Функции позволяют получить подстроку строки str длиною len символов с позиции pos. В случае если параметр len не задан, то возвращается вся подстрока начиная с позиции pos.

Пример:

SELECT SUBSTRING (‘г. Москва — столица России’, 4, 6); Результат: Москва SELECT SUBSTRING (‘г. Москва — столица России’, 4); Результат: Москва — столица России

Примеры с функцией MID не привожу, потому что результаты будут аналогичные.

Интересная функция SUBSTRING_INDEX:

string SUBSTRING_INDEX(str string, delim string, count integer)

Функция возвращает подстроку строки str, полученную путем удаления символов, идущих после разделителя delim, находящимся в позиции count. Параметр count может быть как положительным, так отрицательным. Если count положительный, то отсчет позиции разделителя будет вестись слева и удаляться будут символы находящиеся справа от разделителя. Если count отрицательный, то отсчет позиции разделителя ведется справа и удаляются символы находящиеся слева от разделителя. Возможно, описание получилось слишком запутанным, но на примерах станет понятней.

Пример:

SELECT SUBSTRING_INDEX (‘www.mysql.ru’, ‘.’, 1); Результат: www

В данном примере функция находит, первое вхождения символа точки в строке «www.mysql.ru» и удаляет все символы, идущие после нее, включая сам разделитель.

SELECT SUBSTRING_INDEX (‘www.mysql.ru’, ‘.’, 2); Результат: www.mysql

Здесь функция ищет второе вхождение точки, удаляет все символы справа от нее и возвращает получившуюся подстроку. И еще один пример с отрицательным значением параметра count:

SELECT SUBSTRING_INDEX (‘www.mysql.ru’, ‘.’, -2); Результат: mysql.ru

В этом примере функция SUBSTRING_INDEX ищет вторую точку, отсчитывая позицию справа, удаляет символы слева от нее и выдает полученную подстроку.

DELETE statement

DELETE statement is used to remove rows from a table.

Version: MySQL 5.6

Single-table syntax:

DELETE    FROM table_name
    
    
    
    

Explanation:

  • The DELETE statement deletes rows from table_name and returns the number of deleted rows. You can use ROW_COUNT() function to check the number of deleted rows.
  • The conditions in the WHERE clause (optional) identify which rows to delete.
  • Without WHERE clause, all rows are deleted.
  • If you specify the ORDER BY clause, the rows are deleted in specified order.
  • The LIMIT clause is used to place a limit on the number of rows that can be deleted. These clauses apply to single-table deletes, but not multi-table deletes.

Multiple-table syntax :

DELETE   
    tbl_name ] ...
    FROM table_references
    

Or :

DELETE   
    FROM tbl_name ] ...
    USING table_references
    

Important points:

  • Privileges: To delete a record(s) from a table, the user must have the DELETE privilege on that particular table.
  • The TRUNCATE TABLE statement is a faster way to empty a table than a DELETE statement with no WHERE clause. Here is a detail discussion on the difference between DELETE and TRUNCATE statement.
  • Subqueries: Currently there is no option to delete from a table and select from the same table in a subquery.
  • As of MySQL 5.6.2, DELETE supports explicit partition selection using the PARTITION option, which takes a comma-separated list of the names of one or more partitions or subpartitions (or both) from which to select rows to be dropped. Partitions not included in the list are ignored. Given a partitioned table t with a partition named p0, executing the statement DELETE FROM t PARTITION (p0) has the same effect on the table as executing ALTER TABLE t TRUNCATE PARTITION (p0); in both cases, all rows in partition p0 are dropped.
  • Auto-Increment Columns : If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value is not reused for a MyISAM or InnoDB table.

Example: MySQL DELETE specific rows or records

The following statement will remove those records from the ‘newcate’ table which satisfies the condition ‘cate_id’ = ‘CA002’.

Sample table: newcate


Code:

Example: MySQL DELETE all rows or records

If not accompanied by any condition about which rows are to be removed, MySQL DELETE statement removes all records or rows from a table. The following statement will remove all the rows or records from ‘Newcate’ table.

Code:

Example: MySQL DELETE with ORDER BY for limited number of rows

ORDER BY and LIMIT keyword can be used with MySQL DELETE statement to remove only a given number of rows, where columns are sorted in a specific order. The ORDER BY clause sorts the columns in a specific order and the LIMIT keyword deletes only the number rows mentioned by the numeric value immediately followed by LIMIT keyword. See the following example:

Sample table : newauthor

Code:

The statement above will do the following -1. order the rows of ‘newauthor’ table in descending order according to column ‘country’, 2. delete only two(2) rows for each ‘country’.

Example: MySQL DELETE rows using subqueries with alias and EXISTS

A subquery can be used with MySQL DELETE statement. This is useful when you want to delete rows depending upon a complex condition.

If we want to remove records from ‘newauthor’ table with following conditions -1. ‘aut_id’ of ‘newauthor’ table must exist in ‘book_mast’ table,2. ‘no_pages’ of ‘book_mast’ table must be more than 300,3. ‘aut_id’ of ‘newauthor’ and ‘aut_id’ of ‘book_mast’ must match,

then execute the following code.

Sample table: newauthor

Sample table: book_mast

Code:

MySQL TRUNCATE table

MySQL TRUNCATE TABLE statement is used to delete all the rows from a table. Apparently, this is similar to ‘DELETE FROM <TABLE NAME>’ statement,but they are quite different.

Difference between TRUNCATE and DELETE

TRUNCATE DELETE
It is a DDL command (i.e. a command used to define the database structure or schema) and once you have deleted the rows using it, you can not use the ROLLBACK to undo the task. It is a DML command (i.e. a command used for managing data) and can be rolled back.
You can’t use WHERE clause. You can use WHERE clause.
Faster than DELETE. Slower than TRUNCATE.

Syntax:

TRUNCATE table<table_name>

Where table_name indicates name of the table

Example: MySQL TRUNCATE table

The following MySQL statement will delete all the rows from the newauthor table.

Sample table: newauthor

Code:

Previous: MySQL UpdateNext: MySQL basic select statement

Символьные функции в языке sql

Начнем по порядку с самого простого. Первой рассмотрим строковую функцию ASCII, которая используется для определения ASCII-кода текстовых символов:

integer ASCII(str string)

Функция возвращает целое значение — ASCII-код первого левого символа строки str. В случае если строка str пустая возвращает 0 и NULL если строка str не существует.

Пример:

SELECT ASCII (‘t’); Результат: 116 SELECT ASCII (‘test’); Результат: 116 SELECT ASCII (1); Результат: 49

Далее функция ORD, которая также определяет ASCII- код символов, но может обрабатывать также многобайтовые символы:

integer ORD(str string)

Если первый левый символ строки str многобайтовый, то возвращает его код в формате: ((первый байт ASCII- код)*256+(второй байт ASCII -код)). В случае если первый левый символ строки str не является многобайтовым, работает как функция ASCII — возвращает его ASCII-код.

Пример:

SELECT ORD (‘test’); Результат: 116

Функция CHAR, тесно связанная с функцией ASCII и выполняет обратное действие:

string CHAR(int integer, …)

Функция CHAR возвращает строку символов по их ASCII-кодам. Если среди значений встречается значение NULL, то оно пропускается.

Пример:

SELECT CHAR ( 116, ‘101’, 115, ‘116’ ); Результат: ‘test’


С этим читают