10 основных примеров команды delete в mariadb / mysql

Создание и удаление таблиц

Последнее обновление: 04.05.2018

Создание таблицы

Для создания таблиц используется команда CREATE TABLE. Эта команды применяет ряд операторов, которые определяют столбцы
таблицы и их атрибуты. Общий формальный синтаксис команды CREATE TABLE:

CREATE TABLE название_таблицы
(название_столбца1 тип_данных атрибуты_столбца1, 
 название_столбца2 тип_данных атрибуты_столбца2,
 ................................................
 название_столбцаN тип_данных атрибуты_столбцаN,
 атрибуты_уровня_таблицы
)

После команды CREATE TABLE идет название таблицы. Имя таблицы выполняет роль ее идентификатора в базе данных, поэтому оно должно быть
уникальным. Затем в скобках перечисляются названия столбцов, их типы данных и атрибуты. В самом конце можно определить атрибуты для всей таблицы.
Атрибуты столбцов, а также атрибуты таблицы указывать необязательно.

Создадим простейшую таблицу. Для этого выполним следующий скрипт:

CREATE DATABASE productsdb;

USE productsdb;

CREATE TABLE Customers
(
    Id INT,
    Age INT,
    FirstName VARCHAR(20),
    LastName VARCHAR(20)
);

Таблица не может создаваться сама по себе. Она всегда создается в определенной базе данных. Вначале здесь создается база данных productsdb.
И затем, чтобы указать, что все дальнейшие операции, в том числе создание таблицы, будут производиться с этой базой данных, применяется команда
USE.

Далее собственно идет создание таблицы, которая называется Customers. Она определяет четыре столбца: Id, Age, FirstName, LastName. Первые два столбца представляют идентификатор клиента и его возраст и имеют тип , то есть будут хранить числовые значения.
Следующие столбцы представляют имя и фамилию клиента и имеют тип , то есть представляют строку длиной не более 20 символов.
В данном случае для каждого столбца определены имя и тип данных, при этом атрибуты столбцов и таблицы в целом отсутствуют.

И в результате выполнения этой команды будет создана база данных productsdb, в которой будет создана таблица Customers.

Переименование таблиц

Если после создания таблицы мы захотим ее переименовать, то для этого нужно использовать команду RENAME TABLE,
которая имеет следующий синтаксис:

RENAME TABLE старое_название TO новое_название;

Например, переименуем таблицу Customers в Clients:

RENAME TABLE Customers TO Clients;

Полное удаление данных

Для полного удаления данных, очистки таблицы применяется команда TRUNCATE TABLE. Например, очистим таблицу Clients:

TRUNCATE TABLE Clients;

Удаление таблиц

Для удаления таблицы из БД применяется команда DROP TABLE, после которой указывается название удаляемой таблицы. Например,
удалим таблицу Clients:

DROP TABLE Clients;

НазадВперед

Подключение к СУБД

Если мы планируем работать в командной строке, заходим в среду управления MySQL.

а) В Linux вводим команду:

mysql -uroot -p

* где root — пользователь, под которым мы будем подключаться к оболочке; ключ -p потребует ввода пароля.

б) В Windows запускаем командную строку — в меню пуск или найдя ее в поиске. Переходим в каталог, с установленной СУБД и запускаем одноименную команду mysql, например:

cd «%ProgramFiles%\MySQL\MySQL Server 5.5\bin\»

* в данном примере предполагается, что у нас установлена MySQL версии 5.5.

mysql -u root -p

* здесь, как и в Linux, идет подключение к mysql/mariadb под учетной записью root с запросом пароля.

Работа с базами, таблицами — просмотр, удаление, редактирование записей. Консоль

Создать базу данных на MySQL сервере:

mysql  create database

Показать список всех баз данных на сервере MySQL:

mysql  use     ;

Отобразить все таблицы в базе данных:

mysql  show tables;

Просмотреть формат таблицы в базе:

mysql  describe     ;

Удалить базу:

mysql  drop database   ;

Удалить таблицу из базы:

mysql  drop table   ;

Показать все содержимое таблицы:

mysql  SELECT * FROM   ;

Отобразить столбцы и содержимое столбцов в выбранной таблице:

mysql  show columns from   ;

Отобразить строки в определенной таблице, содержащие » whatever «:

mysql  SELECT * FROM      WHERE      =   "whatever";

Отобразить все записи в определенной таблице, содержащие » Bob » и телефонный номер » 3444444 :

mysql  SELECT * FROM      WHERE name = "  Bob  " AND phone_number = '  3444444  ';

Отобразить все записи, НЕ содержащие имя » Bob » и телефонный номер » 3444444 «, отсортированные по полю phone_number :

mysql  SELECT * FROM      WHERE name != "  Bob  " AND phone_number = '  3444444  ' order by phone_number;

Показать все записи, начинающиеся с букв » bob » и телефонного номера » 3444444 » в определенной таблице:

mysql  SELECT * FROM      WHERE name like "  Bob  %" AND phone_number = '  3444444  ';

Показать все записи, начинающиеся с букв ‘ bob » и телефонного номера » 3444444 «, ограничиваясь записями с 1-ой до 5-ой:

mysql  SELECT * FROM      WHERE name like "  Bob  %" AND phone_number = '  3444444  ' limit 1,5;

Использование регулярных выражений ( «REGEXP BINARY» ) для поиска записей. Например, для регистро-независимого поиска — найти все записи, начинающиеся с буквы А :

mysql  SELECT * FROM      WHERE rec RLIKE "^  a  ";

Показать все уникальные записи:

mysql  SELECT DISTINCT      FROM     ;

mysql  SELECT   ,   FROM      ORDER BY      DESC;

Показать количество строк в таблице:

mysql  SELECT COUNT(*) FROM     ;

Подсчитать количество столбцов в таблице:

mysql  SELECT SUM(*) FROM     ;

Удаление столбца:

mysql  alter table      drop column     ;

Добавление колонки в базу данных:

mysql  alter table      add column      varchar (20);

Изменение имени столбца:

mysql  alter table    change    varchar (50);

Создать столбец с уникальным именем, что бы избежать дубликатов в названиях:

mysql  alter table  add unique ();

Изменение размера столбца:

mysql  alter table  modify  VARCHAR(3);

Удаление столбца из таблицы:

mysql  alter table  drop index ;

Загрузка файла CSV в таблицу:

mysql  LOAD DATA INFILE '  /tmp/filename.csv  ' replace INTO TABLE  FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (  field1,field2,field3  );

Удаление таблицы в базе данных с помощью PHP (PDO)

// Закрываем соединение
$db = null;
?>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

<?php

$server=»localhost»;

$user=»root»;

$password=»MySafePass4!»;

$db_name=»Bookstore»;

try{

// Открываем соединение

$db=newPDO(«mysql:host=$server;dbname=$db_name»,$user,$password);

// Создание исключения при ошибке

$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

// Запрос на удаление таблицы

$sql=»DROP TABLE books»;

// Выполняем запрос

$db->exec($sql);

echo»Таблица успешно удалена!»;

}

catch(PDOException$e){

echo»Ошибка при удалении таблицы в базе данных: «.$e->getMessage();

}

// Закрываем соединение

$db=null;

Пользователи, пароли сервера MySQL — добавление, изменение пользователей и паролей. Консоль

Создание нового пользователя — подключение к серверу MySQL под root, переключение к базе данных, добавление пользователя, обновление привилегий:

# mysql -u root -p
mysql  use mysql;
mysql  INSERT INTO user (Host,User,Password)
VALUES('%','  username  ', PASSWORD('  password  '));
mysql  flush privileges;

Изменений пользовательского пароля из консоли на удаленном хосте db1.example.org :

# mysqladmin -u username -h db1.example.org -p   password   '  new-password  '

Изменение пользовательского пароля из консоли MySQL — подключение под root, обновление пароля, обновление привилегий:

# mysql -u root -p
mysql  SET PASSWORD FOR '  user  '@'  hostname  ' = PASSWORD('  passwordhere  ');
mysql  flush privileges;

Восстановление/изменение пароля root сервера MySQL — остановка MySQL, запуск без таблиц привилегий, подключение под root, установка нового пароля, выход и перезапуск MySQL.

# /etc/init.d/mysql stop
# mysqld_safe -skip-grant-tables &
# mysql -u root

mysql  use mysql;
mysql  update user set password=PASSWORD("  newrootpassword  ") where User='root';
mysql ; flush privileges;
mysql  quit

# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root   password newpassword

Обновление пароля root:

# mysqladmin -u root -p   oldpassword newpassword

Установка права на подключение к серверу с хоста localhost с паролем » passwd » — подключение подroot, переключение к базе данных, установка привилегий, обновление привилегий:

# mysql -u root -p
mysql  use mysql;
mysql grant usage on *.* to  bob @localhost identified by ' passwd ';
mysql  flush privileges;

Установка привилегий пользователю на использование базы данных — подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:

# mysql -u root -p
mysql  use mysql;
mysql  INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql  flush privileges;

или

mysql  grant all privileges on   databasename  .* to   username  @localhost;
mysql  flush privileges;

Обновление информации в базе данных:

mysql  UPDATE      SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where  = user';

Удаление строки в таблице:

mysql  DELETE from      where      = 'whatever';

Обновление привилегий в базе данных:

mysql  flush privileges;

Синтаксис

Простой синтаксис оператора DELETE в MySQL:

DELETE FROM table
;
Теперь полный синтаксис оператора DELETE в MySQL:
DELETE FROM table

]
;

Параметры или аргументы

LOW_PRIORITY — необязательный. Если указан LOW_PRIORITY, удаление будет задерживаться до тех пор, пока не будет никаких процессов затрагивающих таблицу. LOW_PRIORITY может использоваться с таблицами MyISAM, MEMORY и MERGE, которые используют блокировку на уровне таблицы.QUICK — необязательный. Если указан QUICK, в процессе удаления не удаляются листы индекса, что делает удаление быстрее для таблиц MyISAMIGNORE — необязательный. Если указан IGNORE, все ошибки, возникающие во время удаления, игнорируются. IGNORE был представлен в MySQL 4.1.1.table — таблица, из которой вы хотите удалить записи.WHERE conditions — необязательный. Условия, которые должны быть выполнены для записей, подлежащих удалению. Если условий не предусмотрено, все записи из таблицы будут удалены.ORDER BY expression — необязательный. Он может использоваться в сочетании с LIMIT для сортировки записей соответствующим образом при ограничении количества удаляемых записей.LIMIT — необязательный. Если LIMIT указан, то он контролирует максимальное количество записей для удаления из таблицы. В лучшем случае количество записей, заданных number_rows, будет удалено из таблицы.

Зачем использовать Order с DELETE?

Вы можете использовать Order вместе с командой Delete.

Это может быть очень полезно, если вы хотите, чтобы ваши строки будут удалены в определенном порядке.

В команде удаления, когда вы объединяете “order by” и опцию “limit”, вы можете сделать некоторые трюки.

Например, при выполнении следующей команды удаления без “order by”, удалит запись сотрудника с кодом 100 (т.е. самого старого сотрудника в отделе «IT»).

MariaDB > DELETE FROM worker 
WHERE dept = 'IT' LIMIT 1;


Query OK, 1 row affected (0.00 sec)

Приведенная выше команда удалила идентификационный номер сотрудника 200 (самый старый работник IT).

MariaDB > SELECT * FROM worker;
+-----+----------+------------+--------+
| id  | name     | dept       | salary |
+-----+----------+------------+--------+
| 100 | AndreyEx | Sales      |   5000 |
| 300 | Anna     | IT         |   7000 |
| 400 | Anton    | Marketing  |   9500 |
| 500 | Dima     | IT         |   6000 |
+-----+----------+------------+--------+

Но, вы можете также удалить нового сотрудника в отделе «IT», если объединить и упорядочить по LIMIT, как показано ниже.

MariaDB > DELETE FROM worker 
WHERE dept = 'IT' 
ORDER BY id DESC LIMIT 1;


Query OK, 1 row affected (0.00 sec)

Приведенная выше команда удалила идентификационный номер сотрудника 500 (самый новый работник IT).

MariaDB > SELECT * FROM worker;
+-----+----------+------------+--------+
| id  | name     | dept       | salary |
+-----+----------+------------+--------+
| 100 | AndreyEx | Sales      |   5000 |
| 200 | Boris    | IT         |   5500 |
| 300 | Anna     | IT         |   7000 |
| 400 | Anton    | Marketing  |   9500 |
+-----+----------+------------+--------+

Кроме того, имейте в виду, что вы можете использовать ORDER BY в предложении, если вы хотите удалить записи в определенной последовательности, чтобы избежать каких-либо ограничений ссылочной целостности.

Пример с двумя условиями

Рассмотрим MySQL пример DELETE, где у нас есть только два условия в инструкции DELETE.
Например:

MySQL

DELETE FROM contacts
WHERE last_name = ‘Markoski’
AND contact_id < 2000;

1
2
3

DELETEFROMcontacts

WHERElast_name=’Markoski’

ANDcontact_id<2000;

Этот MySQL пример DELETE удалит все записи из таблицы contacts, где last_name = ‘Markoski’, а customer_id менее 2000.

Вы можете проверить количество строк, которые будут удалены. Вы можете определить количество строк, которые будут удалены, вызвав функцию mysql_info или выполнив следующую MySQL предложение SELECT перед выполнением удаления.

MySQL

SELECT count(*)
FROM contacts
WHERE last_name = ‘Markoski’
AND contact_id < 2000;

1
2
3
4

SELECT count(*)

FROMcontacts

WHERElast_name=’Markoski’

ANDcontact_id<2000;

Настройка прав доступа

Чтобы к созданной базе можно было подключиться, добавим пользователя:

> GRANT ALL PRIVILEGES ON newdb.* TO dbuser@localhost IDENTIFIED BY ‘password’ WITH GRANT OPTION;

* где newdb.* — наша база и все ее таблицы; dbuser@localhost — имя учетной записи, которая будет подключаться с локального сервера; password — придуманный нами пароль.** В данном примере, учетной записи будут предоставлены полные права (ALL PRIVILEGES). Подробнее о правах в MySQL читайте статью Как создать пользователя MySQL и дать ему права.

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

> SELECT db, host, user FROM mysql.db WHERE db=’newdb’;

* в данном примере мы выведем учетные записи, которым был дан прямой доступ к созданной нами базе. В данном списке не будут отражены пользователи с глобальными правами (например, root).

Поменять пароль пользователю можно одной из команд (в зависимости от версии СУБД):

> SET PASSWORD FOR ‘dbuser’@’localhost’ = PASSWORD(‘new_password’);

> ALTER USER ‘dbuser’@’localhost’ IDENTIFIED BY ‘new_password’;

> UPDATE mysql.user SET Password=PASSWORD(‘new_password’) WHERE USER=’dbuser’ AND Host=’localhost’;

* все 3 команды меняют пароль для пользователя dbuser@localhost на новый — new_password.

При необходимости, удалить пользователя можно командами:

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘dbuser’@’localhost’;

> DROP USER ‘dbuser’@’localhost’;

* первая команда отнимает все привилегии, выданные пользователю. Вторая удаляет самого пользователя.

Запрос на создание таблицы в MySQL

Для создания таблицы потребуется придумать:

Имя таблицы
Имена полей таблицы
Значения, которыми могут быть заполнены поля

Синтаксис запроса на создание таблицы.

CREATE TABLE table_name (column_name column_type attributes);

1	CREATETABLEtable_name(column_namecolumn_typeattributes);

Для примера создадим таблицу books со списком книг условной базы данных Bookstore.

Оповестим сервер MySQL с какой базой данных мы собираемся работать.

USE Bookstore;

1	USEBookstore;

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

Составим запрос на создании таблицы со следующими полями:

id — Идентификатор записи.
title — название книги.
author — имя автора книги.
genre — жанр произведения в книге.
price — цена книги.
edition — номер издания.
isbn — номер ISBN.
page_num — число страниц в книге.
publish_year — год выхода книги в печать.
creation_date — дата создания записи (когда занесли книгу в БД).

CREATE TABLE books(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(50) NOT NULL,
author VARCHAR(50) NOT NULL,
genre SET(‘Драма’,’Роман’,’Повесть’,’Рассказ’,’Поэма’),
price DECIMAL(15,2),
edition TINYINT UNSIGNED DEFAULT ‘1’ NOT NULL,
isbn VARCHAR(50),
page_num SMALLINT UNSIGNED,
publish_year SMALLINT(4),
creation_date TIMESTAMP
);

1
2
3
4
5
6
7
8
9
10
11
12

CREATETABLEbooks(

idINTUNSIGNEDAUTO_INCREMENTPRIMARY KEY,

titleVARCHAR(50)NOT NULL,

authorVARCHAR(50)NOT NULL,

genreSET(‘Драма’,’Роман’,’Повесть’,’Рассказ’,’Поэма’),

priceDECIMAL(15,2),

editionTINYINTUNSIGNEDDEFAULT’1’NOT NULL,

isbnVARCHAR(50),

page_numSMALLINTUNSIGNED,

publish_yearSMALLINT(4),

creation_dateTIMESTAMP

);

После того как вы указали тип данных для поля, вы можете также указать дополнительные атрибуты:

UNSIGNED — позволяет увеличить диапазон числовых значений за счет отключения использования отрицательных чисел. Используется только с числовыми типами данных.
AUTO_INCREMENT — значение поля с этим атрибутом будет увеличиваться на 1 при каждом создании новой записи в таблице. Часто используется для автоматического заполнения полей с идентификатором записи.
PRIMARY_KEY — первичный ключ используется для идентификации записей в таблице. Первичным ключом может быть только уникальное значение поэтому чаще всего это значение поля id с атрибутом .
NOT NULL — обязательное поле для заполнения (не может быть пустым).
DEFAULT — значение поля по умолчанию.

Каждая таблица должна иметь поле которое помечено как первичный ключ.

Создание таблицы в терминале:

Резервные копии — создание, восстановление бд . Консоль

Создать резервную копию (dump) всех баз данных в файл alldatabases.sql :

# mysqldump -u root -p   password   -opt  ;  /tmp/alldatabases.sql

Создать резервную копию одной базы данных в файл databasename.sql :

# mysql dump -u username -p   password   -databases   databasename    ;  /tmp/databasename.sql

Создать резервную копию одной таблицы в файл databasename.tablename.sql :

# mysql dump -c -u   username   -p   password databasename tablename    ;  /tmp/databasename.tablename.sql

Восстановление базы данных (или таблицы) из резервной копии:

# mysql -u   username   -p   password databasename   <    /tmp/databasename.sql&#91;/html&#93;

<h2>Создание таблиц БД. Консоль</h2>

<p>маленькими буквами указаны имена столбцов;<br />
ПРОПИСНЫМИ буквами - типы и атрибуты столцов;<br />
в (скобках) - значение типа столбца.</p>

<p>Создать таблицу, пример 1:</p>


mysql  CREATE TABLE      (  firstname   VARCHAR(20),   middleinitial   VARCHAR(3),  lastname   VARCHAR(35),   suffix   VARCHAR(3),   officeid   VARCHAR(10),   userid   VARCHAR(15),  username   VARCHAR(8),   email   VARCHAR(35),   phone   VARCHAR(25),   groups   VARCHAR(15),  datestamp   DATE,   timestamp   TIME,   pgpemail   VARCHAR(255));

Создать таблицу, пример 2:

mysql  create table      (  personid   INT(50) NOT NULL AUTO_INTCREMENT PRIMARY KEY,   firstname   VARCHAR(35),   middlename   VARCHAR(50),   lastname   VARCHAR(50) default 'bato');

Создание таблицы в базе данных с помощью PHP (PDO)

<?php
$server = «localhost»;
$user = «root»;
$password = «MySafePass4!»;
$db_name = «Bookstore»;
try {
// Открываем соединение
$db = new PDO(«mysql:host=$server;dbname=$db_name», $user, $password);
// Создание исключения при ошибке
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Запрос на создание таблицы
$sql = «CREATE TABLE books(«.
«id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,».
«title VARCHAR(50) NOT NULL,».
«author VARCHAR(50) NOT NULL,».
«genre SET(‘Драма’,’Роман’,’Повесть’,’Рассказ’,’Поэма’),».
«price DECIMAL(15,2),».
«edition TINYINT UNSIGNED DEFAULT ‘1’ NOT NULL».
«isbn VARCHAR(50),».
«page_num SMALLINT UNSIGNED,».
«publish_year SMALLINT(4),».
«creation_date TIMESTAMP);»;
// Выполняем запрос
$db->exec($sql);
echo «Таблица успешно создана!»;
}
catch(PDOException $e) {
echo «Ошибка при создании таблицы в базе данных: » . $e->getMessage();
}