SQL шпаргалка (операции с данными)

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

— CREATE DATABASE [IF NOT EXISTS] db_name - создает базу данных с указанным именем.
— DROP DATABASE [IF EXISTS] db_name - удаляет все таблицы в указанной базе данных и саму базу.
— USE db_name - предписывает MySQL использовать базу данных с именем db_name в последующих запросах по умолчанию.
— CREATE [TEMPORARY] TABLE [IF NOT EXISTS]
tbl_name [(create_definition,...)] [table_options] [select_statement] - создает таблицу с заданным именем в текущей базе данных
Пример:

--создаём базу данных
CREATE DATABASE shop;
--смотрим наши БД
SHOW DATABASES;
--переходим в shop
USE shop;
--создаём таблицу в shop
CREATE TABLE table1
(
  id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'cod item',
    name VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'name item',
    price FLOAT NOT NULL DEFAULT 0 COMMENT 'price item'
)COMMENT 'table items';
--выводим таблицу на экран
DESCRIBE table1;
--или
DESC table1;

--таблица с PRIMARY KEY AUTO_INCREMENT
CREATE TABLE table2
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'cod item',
    name VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'name item',
    price FLOAT NOT NULL DEFAULT 0 COMMENT 'price item',
    PRIMARY KEY (id)
)COMMENT 'table items';

--создание таблицы по образу
--создаёт пустую табл3 со структурой как у табл2
CREATE TABLE table3 LIKE table2;

— DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE] - удаляет одну или несколько таблиц
— CREATE INDEX - данный запрос используется для создания индексов в таблице. Индексы позволяют быстрее находить нужные данные, без чтения всей таблицы данных.

--создадим индекс для table2
CREATE INDEX ixName ON table2 (name);
CREATE INDEX ixPrice ON table2 (price);

— DROP INDEX - удаление индекса.

Манипуляция данными

sql INSERT:

— INSERT INTO таблица [ ( поле [,...] ) ]
{ VALUES ( Wertliste [,...] ) } | SELECT-команда - добавляет новые строки в указанную таблицу данных, которые состоят из констант или же являются результатом исполнения команды. VALUES ( константа [,...] ) записываем новую строку, состоящую из указанных констант, в таблицу данных.
Пример:

--добавим строку с PK 10
INSERT INTO table2
  VALUE (10, 'item', 123.5);
--смотрим данные в табл. после добавления
SELECT*FROM table2;
--PK добавится автоматически
INSERT INTO table2
  VALUE (NULL, 'item', 123.5);
SELECT*FROM table2;
--и ещё один способ
INSERT INTO table2 (name, price)
  VALUE ('item', 123.5);
--сразу несколько значений
INSERT INTO table2 VALUE 
  (23, 'item 1', 123.5),
  (24, 'item 4', 123.5),
    (25, 'item 78', 123.5);
SELECT*FROM table2;
--игнорирует повторяющуюся строку не выдаёт ошибки
INSERT IGNORE INTO table2 VALUE 
  (23, 'item 1', 123.5),
  (26, 'item 41', 123.5),
    (27, 'item 71', 123.5);

Так же можно добавлять выборку. Важно: при вставке выборки в таблицу количество столбцов в выборке должно быть равным числу столбцов в таблице. Так же должны быть одинаковые типы данных.
Пример:

--возвращает таблицу с именамии пользователей
SELECT user FROM mysql.user;
--добавляем в select ещё колонку
SELECT user, 345 as price 
    FROM mysql.user;
--теперь добавляем в table2
INSERT INTO table2 (name, price)
    SELECT user, 345 as price 
      FROM mysql.user;

Так же можно вставлять в БД данные из файла с помощью команды LOAD DATA INFILE Пример: Вставим в нашу table2 данные из файла C:/data.txt

LOAD DATA 
  INFILE 'C:/data.txt'
    INTO TABLE table2
--поля в файле разделены табуляцией
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\r\n';

sql DELETE:

— DELETE FROM таблица [ WHERE условие ] - удаляются строки из таблицы базы данных
Пример:

--удаляем запись из table2 c id=1
DELETE FROM table2
  WHERE id=1;
--удаление последней записи
DELETE FROM table2
  ORDER BY id DESC
    LIMIT 1;

sql UPDATE:

— UPDATE таблица SET { поле=значение } [,...] [ { WHERE условие } ] - меняет выбраные значения в элементах таблицы на другие данные. SET { поле=значение } [,...] - заменяет значения полей таблицы на указанные. WHERE- условие
Пример:

UPDATE table2
  SET
--добавляет к столбцу name *    
      name=CONCAT(name,' *'),
--умножает значение в столбце price на 1.5
        price=price*1.5
--производит эти операции с строками с id c 10 по 30        
  WHERE id BETWEEN 10 AND 30;

                                     
  <pre class="brush: html">                                     
    <div class="wraperr">
        <div class="lefmenu">Hello world </div>
        <div class="content"> </div>
    </div>
  </pre>

sql SELECT:

— SELECT [ALL | DISTINCT] <список_выбора>
    FROM <имя_таблицы>, ...
    [ WHERE <условие> ]
    [ GROUP BY <имя_столбца>,... ]
    [ HAVING <условие> ]
    [ORDER BY <имя_столбца> [ASC | DESC],... ]
- оператор SELECT предназначен для извлечения данных из таблиц БД.

         "ПРОСТОЙ" SELECT
--вывод значения 
SELECT 2+2;

--вывод значения в колонку result 
SELECT 2+2 AS result;

--переменные
SET @a='HI WORLD';
SELECT @a;
              ПРЕДИКАТ ORDER
               
--выберет из таблицы friends колонки name и phone и 
--отсортирует колонку name по возрастанию 
--(если DESC то по убыванию)
SELECT name, phone
  FROM friends
    ORDER BY name ASC;
    
                 ПРЕДИКАТ LIMIT

--сделает всё вышеперечисленное только выведет
--первые 2-е строки если LIMIT 2,3 то
--начиная с 2-й строки выведет  3-и    
SELECT name, phone
  FROM friends
    ORDER BY name 
    LIMIT 2;
          ПРЕДИКАТ WHERE

--выберет из таблицы friends колонки name и phone
--где phone='31-32-33'                
SELECT name, phone
  FROM friends
    WHERE phone='31-32-33';

--условие BETWEEN AND выбираем из заданого диапазона
SELECT name, price
  FROM table2
    WHERE id BETWEEN 10 AND 30;
    
--LIKE сравнение. Выберет из таблицы friends колонки 
--name и phone где номера телефонов с цифрой 31
--*=% в SQL 
SELECT name, phone
  FROM friends
    WHERE phone LIKE '%31%';    

--REGEXP
SELECT name, phone
  FROM friends
    WHERE phone REGEXP '[0-9]' ;
    
--IN множество
 SELECT name, phone
  FROM friends
    WHERE phone IN (1, 3, 5);
    
           ПРЕДИКАТ GROUP BY
--для извлечения уникальных записей
--распологается перед конструкциями ORDER BY и LIMIT  
SELECT id_catalog FROM products
  GROUP BY id_catalog
    ORDER BY id_catalog;
    
          ПРЕДИКАТ HAVING
SELECT id_catalog FROM products
  GROUP BY id_catalog
    HAVING id_catalog>2
    ORDER BY id_catalog;                       

MySQL поддерживает поиск по полнотекстовому индексу. Пример:

--создаём полнотекстовый индекс
CREATE FULLTEXT INDEX ixFullText ON content (title, description);

--Поиск в режиме естественного языка
SELECT title, length
  FROM content
    WHERE MATCH (title, description)
      AGAINST ('программирование' IN NATURAL LANGUAGE MODE);

--Поиск в режиме BOOLEAN
SELECT title, length
  FROM content
    WHERE MATCH (title, description)
      AGAINST ('-web +программ*' IN BOOLEAN MODE);
        
--смешанный поиск с сортировкой по релевантности
SELECT title, length
    MATCH (title, description) AGAINST ('программ' IN NATURAL LANGUAGE MODE) AS FROM content
  WHERE MATCH (title, description)
      AGAINST ('программ' IN BOOLEAN MODE)
    ORDER BY score DESC;  

Работает только на движке — MyISAM.

Функции агрегации

Агрегационные функции или суммирующие функции вычисляют среднее значение для определённой группы.

— AVG ( ) - возвращает среднее значение аргумента
— COUNT ( ) - подсчитывает число записей
— GROUP_CONCAT ( ) - объединяет значение отдельных групп
— MIN ( ) MAX () - возвращает соответственно минимальное и максимальное значение среди всех непустых значений выбранных строк
— SUM ( ) - возвращает сумму величин в строке