Грабер М. Введение в SQL

Добавляем возможность быстрого запуска к mysql на OpenServer windows 10

Ошибка «mysql не является внутренней или внешней командой, исполняемой программой или пакетным файлом.

ВАЖНО! Должна быть запущена служба или приложение mysqld.exe. Проверить можно так:
tasklist /FI "IMAGENAME eq mysqld.exe"

Для справки
tasklist /?

Проверяем и ставим нужные версии, через «настройки» openserver

Узнать версию MySQL

mysql -V

Подключение:

mysql -u имя-пользователя -p пароль-пользователя -h IP-адрес-сервера-mysql

Копируем ссылку

Вставляем ссылку в path

Перезапускаем cmd

——-

Создаем базу данных

create database sql_2;  //создаем новую базу данных (хранится OSPanel\userdata\MySQL-5.7-x64)

drop database sql_2; //удаляем базу данных

---

Показать базы

SHOW DATABASES;

Выбираем нужную базу:

USE name-database

Показать таблицы

SHOW TABLES;

---

Запросы, оператор SELECT, в нужной базе:

SELECT * FROM name-base; —выводим все существующие поля в естественном порядке

SELECT cnum, sname, comm FROM name-base; —выбираем только нужные поля (выборка)

———-

SELECT DISTINCT odate FROM orders; —не показываем повторы

---

SELECT sname, comm*100 FROM salers; — выражения умножаем

---

WHERE где (выводит условие)

SELECT odate FROM orders WHERE odate = ‘1990-03-10’; —выводит истину (совпадения), ложь не выводит

SELECT cname FROM customers WHERE rating=200; -выводим две колонки, показываем с рейтингом 200 из третьей колонки

Проблемы с кодировкой

set names cp866;

SELECT amt FROM orders WHERE amt >= 767; — выводит значения <;>;>=;<=;=

SELECT sname FROM sales WHERE city <> ‘London’; — неравенство <> или != ; выводит все города кроме Лондон

Сортировка по имени:

SELECT sname FROM sales ORDER BY sname;

———

AND; OR (или) NOT (не)

SELECT * FROM salers WHERE city = ‘London’ AND comm > 0.11;    —AND дополнительное условие удовлетворяет обоим условиям и выводит

SELECT * FROM customers WHERE NOT city = ‘London’;

Более сложный пример

SELECT * FROM orders WHERE NOT ((odate =’1990-03-10′ AND snum > 1002) OR amt > 2000.00);

———

Выборка списком IN

SELECT city FROM salers WHERE city IN (‘London’, ‘New York’);

BETWEEN диапазон

SELECT * FROM sales WHERE (comm BETWEEN 0.11 AND 0.15) AND comm NOT IN (0.11, 0.15)

NULL — пустота

Добавить значение в таблицу

INSERT INTO salers (snum, city, comm) VALUES (1112, ‘Paris’, 0.15); —без sname, в sname в структуре добавили NULL

SELECT sname FROM customers WHERE sname IS NOT NULL; —без NULL значений

SELECT sname FROM customers WHERE sname IS NULL;

Оператор LIKE сравнение, часть

SELECT *FROM salers WHERE sname LIKE ‘%e%’; —%. вместо * ищет любое количество

//_ —ищет один знак

Поиск записей с одним знаком _

SELECT *FROM salers WHERE sname LIKE ‘\_’    — \экранирование

Агрегатная функция

COUNT() —количество чего то значений или рядов

SELECT COUNT (DISTINCT city) FROM salers;

SELECT COUNT (*) AS xxx FROM salers; —переименовать таблицу показа (псевдоним)

SUM()

SELECT SUM(amt) FROM orders;

AVG() —среднее значение

MIN() —минимальное значение

MAX()

SELECT MAX(amt) AS res FROM orders;— выводит максимальное значение amt и переименовать таблицу показа (псевдоним res)

---

Группировка GROUP BY

SELECT snum, MAX(amt) FROM orders GROUP BY snum;

SELECT * FROM orders GROUP BY snum;

HAVING

SELECT snum, MAX(amt) FROM orders GROUP BY snum HAVING MAX(amt) > 2000; —выбрали из таблицы orders след поля snum и максимальное значение поля, из сгруппированного нам по полю snum группы, при этом каждая группа должна иметь максимальную сумму 2000

С псевдонимом :

SELECT snum, MAX(amt) AS res FROM orders GROUP BY snum HAVING res > 2000;

---

Добавляем столбец

SELECT ‘Продавец’, sname, comm, ‘%’ FROM salers ; —добавляем продавца и знак %

---

ORDER BY упорядочить по; сортировка

SELECT * FROM orders ORDER BY onum;  — сортирует по порядку любой столбец, в данном случаем onum

ASC в порядке увеличения DESC в обратном порядке сортировка уменьшения

SELECT * FROM orders ORDER BY onum DESC;

SELECT snum, amt, odate FROM orders ORDER BY snum, amt; —сортировка по первичному и вторичному столбцу

SELECT snum, amt, odate FROM orders ORDER BY snum DESC, amt;

SELECT snum, MAX(amt), odate FROM orders GROUP BY  snum ORDER BY MAX(amt);

SELECT snum, MAX(amt), odate FROM orders GROUP BY  snum ORDER BY 2;  —2вместо MAX(amt)

SELECT snum, MAX(amt) AS res, odate FROM orders GROUP BY  snum ORDER BY res; —псевдоним res вместо 2

---

ошибки

скриншоты iPhone 11-07-2020

---

LIMIT

SELECT * FROM salers LIMIT 2; —показываем две первых записи

SELECT * FROM salers LIMIT 0, 2; —с какой записи начинать выборку, нумерация начинается с 0; будет взяты первые две записи

SELECT * FROM salers ORDER BY snum DESC LIMIT 0, 2; —последние две записи

---

Связывание таблиц

SELECT sname, cname FROM salers, customers;  — выводим из разных таблиц столбцы

SELECT salers.sname, customers.cname, salers.city FROM salers, customers; —указываем явно привязку к таблицам и данным (сначала имя таблицы)

SELECT sname, cname, salers.city FROM salers, customers WHERE salers.snum = customers.snum ORDER BY sname; —указываем привязку продавца=покупателя и фильтр

---

Псевдоним

SELECT s.sname, c.name, s.city FROM salers s, customers c WHERE s.num = c.num ORDER BY s.name; — тот же результат что и в прошлом примере

---

Функции

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

CONCAT

SELECT CONCAT (sname, city) FROM salers; —объединяем две строки

SELECT CONCAT (sname, ‘ ‘,city) AS res FROM salers; —с разделением с переименованием в res

CONCAT_WS (separator, strl1, strl2, …). — добавляет разделение

LENGHT — возращает длину строки в байтах

SELECT sname, LENGHT(sname) FROM salers;

CHAR_LENGHT —количество символов в строке тоже самое CHARACTER_LENGHT

SELECT *FROM salers WHERE , CHAR_LENGHT(sname) <= 6;

LOCATE (substr, str) —

SELECT WHERE LACATE (‘kin’, sname) FROM salers; — где есть kin и на каком месте находится

SELECT * FROM salers WHERE LACATE (‘kin’, sname); —находим в именах kin и выводим

LEFT (str,len) —возращает символы слева

SELECT odate, LEFT(date,7) FROM orders; —показ даты без дня

RIGHT(str.len) —тоже что left только наоборот

SUBSTRING (str,pos)

SELECT odate, SUBSTRING (odate,6) FROM orders; —только месяц и число

SUBSTRING (str,pos,len) —len сколько символов взять; pos с какой позиции начинать

SELECT odate, SUBSTRING (odate,1,7) FROM orders; —показываем первые 7 символов

SELECT odate, SUBSTRING (odate,6,2) FROM orders; —только месяц

SUBSTRING_INDEX(str,delim,count) —str — из этой строки; delim — разделитель ;count количество знаков показывать;

SELECT odate, SUBSTRING_INDEX(odate, ‘-‘, -2) FROM orders; —показывать только месяц и число

LTRIM(str) —удаляет пробелы с начала слева

SELECT LTRIM(‘string   ‘);

RTRIM

SELECT RTRIM(‘    string’);

SELECT LTRIM(RTRIM(‘   string   ‘));

TRIM —удаляет с обоих сторон пробелы см пример выше

TRIM [BOTH | LEADING | TRAILING]

trailing — удалит с конца

SELECT TRIM(TRAILING ‘-10’ FROM odate) FROM orders; —выводим год и месяц

leading —

SELECT TRIM(LEADING ‘1990-‘ FROM odate) FROM orders;

BOTH — с обеих сторон строки

SELECT TRIM(BOTH ‘xx’ FROM ‘xxxbarxxx’);

REPLACE(str,from_str,to_str) —str-строка;from_str -часть строки из первого; to_str-то на что меняем второй параметр

SELECT comm, REPLACE (comm,’0.’,») FROM salers; —заменяем убираем 0.

LCASE(str), аналог LOWER(str) —переводит в нижний регистр

SELECT LCASE (sname) FROM salers;

UCASE (str), аналог UPPER(str) —переводит в верхний регистр

Функция REGEXP (очень мощная, можно найти практически всё, для поиска на сайте Есть LIKE см выше)

https://dev.mysql.com/doc/refman/5.6/en/regexp.html

SELECT * FROM salers WHERE sname REGEXP ‘[a-z]’; -поиск только англ имен (см.regexp.pdf)
SELECT * FROM salers WHERE sname REGEXP ‘[а-я]’; -поиск только рус имен (см.regexp.pdf)
SELECT * FROM salers WHERE sname REGEXP ‘[a-zа-я]’; -поиск только англ и рус имен (см.regexp.pdf)

Специальный символы (якоря)
^
SELECT * FROM salers WHERE sname REGEXP ‘^s’; -поиск слов начинающих с s
$
SELECT * FROM salers WHERE sname REGEXP ‘n$’; -слова заканчивающие на n

SELECT * FROM salers WHERE sname REGEXP ‘^[^r]’; — без первой буквы r

Другие
SELECT * FROM salers WHERE sname REGEXP ‘^[^r]*$’; — кроме r он может встречаться сколько угодно раз
*-сколько угодно раз
?-всего один раз
+-должен встречаться
{3,5} от 3 до 5 раз должен встречаться
SELECT * FROM salers WHERE sname REGEXP ‘[r]{2}’; — ищет где есть две rr
SELECT * FROM salers WHERE sname REGEXP ‘(rr)’; -тоже
SELECT * FROM salers WHERE sname REGEXP ‘(r)+’; -тоже

SELECT * FROM salers WHERE sname REGEXP ‘\»; -экранируем и ищем ‘

Математические функции

ABS(x) возвращает абсолютное значение величины (x)
SELECT comm, ABS(comm) as test FROM salers; -выводим столбцы comm и ABS без знака и переименовываем столбец в test

SIGN
SELECT comm, ABS(comm) as test FROM salers; -возращает 1 если положительное число, -1 если отрицательное
SELECT * FROM salers WHERE SIGN(comm) = -1; — показываем все колонки таблицы, даем условие, в колонке comm должно быть отрицательное число

RAND — случайное число от 0 до 1
SELECT RAND(); —случайное число
SELECT * FROM salers ORDER BY RAND() LIMIT 1; —показываем все столбцы, из таблицы salers, сортируем в случайном порядке, только первую строку

TRUNCATE -усечение числа без его округления (round — с округлением)
SELECT comm, TRUNCATE(comm, 1) FROM salers ;