SQL
Primary tabs
Источник (автор vedro-compota):
- https://www.youtube.com/watch?v=kPrROmWw...
- http://fkn.ktu10.com/?q=node/6389
- http://fkn.ktu10.com/?q=node/9229
Разворачиваем
---------------Установка и натсройка SQL----------------
Правой кнопкой мыши жмём "Advanced" выбираем консоль
create sqlstartq;
загрузка данных
mysql -u root -p
use sqlstartq;
show tables;
mysql -u root -p
Дальше пишем пароль root
Если появляется желтый флажок значит нужно поменять ерсию PHP в настройках OpenServera
Правила работы с базами данных:
1) mysql -u root -p -f sqlstart
2) вводим: mysql -u root -p ( пароль:root)
”становка MySQL https://dev.mysql.com/downloads/mysql/
https://ospanel.io/docs/#rabota-s-mysql пароль root
3) use sqlstart
4) show tables;
1) Не называть таблицу зарезервированным словом
Выборка одного или нескольких столбцов таблицы:
Выбрать список столбцов из таблицы
SELECT prod_id, prod_name, prod_price FROM Products;
Выбрать один столбец
SELECT prod_name FROM Products;
Выбрать все столбцы Select*FROM Products;
Сортировка полученных данных один столбец:
SELECT prod_name FROM Products ORDER BY prod_name (цифры к цифрам, буквы к буквам. Цифры от большего к меньшему , слова от А до Z)???
Сортировка нескольких столбцов:
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name; Cначала мы сортируем по цене, а потом по имени
Cортировка по относительному расположению столбца:
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3;
Меняем порядок сортировки от большего к меньшему:
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC;
По названию продукта сортируем потом по имени:
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC, prod_name;
ФИЛЬТРАЦИЯ ВЫБРАННЫХ ЗНАЧЕНИЙ
Выборка конкретного значения:
SELECT prod_name, prod_price FROM Products WHERE prod_price=3.49;
Чтобы изучить все логические операции нужно изучить документацию
SELECT prod_name, prod_price FROM Products WHERE prod_price>3.49;
Проверка на не на совпадение:
SELECT vend_id, prod_price FROM Products WHERE vend_id 'DLL011';
Проверка на диапазон значений:
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
Проверка на наличие пустых столбцов:
SELECT vend_id FROM Vendors WHERE vend_state IS NULL;
NULL - это ноль или несколько пробелов
Расширенная фильтрация:
SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL011' OR vend_id = 'BRS01';
Приоритетность:
SELECT prod_name, prod_price FROM Products WHERE (vend_id = 'DLL011' OR vend_id = 'BRS01') AND prod_price >= 10;
Диапозон условий IN то же что и OR:
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ('DLL01', 'BRS01') ORDER BY prod_name;
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name;
Все кроме того что после NOT ( аналогичный оператор):
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
метасимвол % ( возвращаются те значения которые начинаются с Fish, нужно учитвывать регистр):
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';
найти все значения, содержащие bean bag в любом месте названия, независимо от количества символов перед или после указанного текста:
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';
продуктов, которые начинаются на F и заканчиваются на у:
SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y';
Чтобы "отрезать" пробелы сделаем так:F%y%
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '_ inch teddy bear'; ?????????
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '% inch teddy bear'; ????
Ошибка в книге Форта:
SELECT cust_id, cust_name FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
SELECT cust_id, cust_name FROM Customers WHERE cust_contact LIKE 'J%' ORDER BY cust_contact;
Выведет два столбца где имена в другом столбце сust_contact начинаются с буквы J.
????
SELECT cust_id, cust_name FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;
Конкатекация полей:
??????????????????????????
SELECT vend_name + '('+vend_country +')' FROM Vendors ORDER BY vend_name;
SELECT vend_name || '('|| vend_country ||')' FROM Vendors ORDER BY vend_name;
SELECT CONCAT (vend__name, '(', vend_country, ')');
SELECT RTRIM(vend_name)+'(' +RTRIM(vend_country)+')' FROM Vendors ORDER BY vend_name;
SELECT RTRIM (vend_name) || '(' || RTRIM(vend_country) || ')' FROM Vendors ORDER BY vend_name;
SELECT RTRIM(vend_name)+ '(' + RTRIM (vend_country
^AS vend_title FROM Vendors ORDER BY vend name;
Правильный вариант!
SELECT CONCAT (vend_name, '(', vend_country, ')') FROM vendors;
SELECT vend_name FROM vendors limit 10;
-------НЕ ЗАПУСКАЛ-----
-----------Использование псевдонимов--------------------------
SELECT RTRIM(vend_name) + '(' +RTRIM (vend_country)+')' -> AS vend_title FROM Vendors ORDER BY vend name;
-----ВЫЧИСЛЕНИЯ и вычисляемые поля---------
SELECT prod_id, quantity, itrem_price FROM OrderItems WHERE order_nam = 20008;
----------------------------------
SELECT prod_id, quantity, item_price quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_nam = 20008;
--------------------------------------------------
------------------ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ МАНИПУЛИРОВАНИЯ ДАННЫМИ----------------------
SELECT vend_name UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend name;
SELECT cust_name cust_contract FROM Customers WHERE cust_contract = ' Michael Green';
SELECT cust_name cust_contract FROM Customers WHERE SOUNDEX(cust_contract )= SOUNDEX (' Michael
-> Green');
----Функции манипулирования датой и временем---------------------------------
SELECT order_num FROM Orders WHERE DATEPART (yy, order_date) = 2004;
SELECT order_num FROM Orders WHERE to_number (to_char(order_date,'YY') = 2004;
SELECT order_num FROM Orders WHERE order_date BETWEEN to_date ('01-JAN-2004') AND to_date('31-DEC-2004
-----------__СУММИРОВАНИЕ ДАННЫХ---------------------
SELECT AVG(prod_price) AS avg_price FROM Products;
SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id='DLL01';
SELECT COUNT (*) AS num_cust FROM Customers;
SELECT COUNT (cust_email) AS num_cust FROM Customers;
SELECT MAX (prod_price) AS max_price FROM Products;
SELECT MIN (prod_price) AS min_price FROM Products;
SELECT SUM (quantity) AS item_ordered FROM Orderltems WHERE order item = 20005;
SELECT SUM (item_price*quantity) AS total_price FROM OrderIterns WHERE order item = 20005;
SELECT AVG (DISTINCT prod_price) AS avg_price FROM Products WHERE vend _id= ' LL01 ' ;
SELECT COUNT(*) AS num_items,
MIN (prod_price) AS price_min,
MAX (prod_price) AS price_max,
AVG (prod_price) AS proce_avg
FROM Products;
Всего в SQL 5 ст. функций.
------------ИТОГОВЫЕ ДАННЫЕ-----------------------------------
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend id;
------------------------------------------------------------------------------------------
Фильтруюбщие группы
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
--------------------------------------------------------------------------------------------
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
----------------------------------------------------------------------------------------------------
------------------------запускаю------------------------
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 2;
------------Группирование и сортировка-------------------
SELECT order_num, COUNT(*) FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3;
SELECT order_num, COUNT(*) FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;
--------Упорядочение предложения SELECT----------------------
-------------ПОДЗАПРОСЫ----------------------
SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id ='RGAN01');
SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN ('1000000004', '4000000005');
SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN (SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01'));
Количество заказов сделанные клиентом:
SELECT COUNT(*) AS orders FROM Orders WHERE cust_id = '1000000001';
-----------------------------ЗАПУСКА-------------
Select cust_name, cust_state, ( SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;
ORDER BY cust_name- cортировка по столбцу cust_name;
SELECT COUNT(*) FROM Orders - количество строк в столбце ORDERS;
Получается, что SELECT возвращает три столбца cust_name, cust_state, orders из Customers.
Чтобы получить поле Orders мы должны его вычислить подзапросом:
SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id
Количество строк, у которых cust_id равняется cust_id пз Customers;
Этот подзапрос выполняется один раз для каждого выбранного клиента.
-------------ОБЪЕДИНЕНИЕ ТАБЛИЦ----------------------
Реляционные таблицы соотносятся (связываются) между собой через общие значения (и таким образом являются реляционными (относительными) в реляционной конструкции).
SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id
;
Декартово произведение:
Select vend_name, prod_name, prod_price FROM Vendors, Products;
Объединение, которое мы до сих пор использовали, называется объединение по эквивалентности — оно основано на проверке эквивалентности двух таблиц:
SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
Объединение многих таблиц ( !!!!!!!!!!)
SELECT prod_name, vend_name, prod_price, quantity FROM Orderitems, Products, Vendors WHERE Products.vend_id = Vendors.vend_id AND Orderitems.prod_id = Products .prod_id AND order_num = 20007;
SELECT cust_name, cust_contact FROM Customers, Orders, Orderitems WHERE Customers.cust_id = Orders.cust_id AND Orderitems.order_num = Orders.order_num AND prod_id='RGAN01';
---Создание расширенных объединений-----------------------------------
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')' AS vend_title FROM Vendors ORDER BY vend_name;
SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O, Orderitems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN011';
Самообъединения
----- Самообъединения----
SELECT cust_id, cust_name, cust_contact FROM Customers WHERE cust_name = (SELECT cust_name FROM Customers WHERE cust_contact = 'Jim Jones');
SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';
--- Естественные объединени----
SELECT С.*, О.order_num, O.order_date, OI.prod_id, 0I .quantity, OI. item_price FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = О.order_num AND prod_id = 'RGAN011; (!!!!!)
--- Внешние объединени-----
SELECT Customers.cust_id, Orders.order_num FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT Customers.cust_id, Orders.order_num FROM Customers RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
SELECT Customers.cust_id, Orders.order_num FROM Customers, Orders WHERE Customers.cust_id *= Orders.cust_id; (!!!!) (*=) читать документацию
SELECT Customers.cust_id, Orders.order_num FROM Customers, Orders WHERE Orders.cust_id =* Customers.cust_id; (!!!!) (=*) читать документацию
SELECT Customers.cust_id, Orders.order_num FROM Customers, Orders WHERE Customers.cust_id (+) = Orders.cust_id; (!!!!) читать документацию
SELECT Customers.cust_id, Orders.order_num FROM Orders FULL OUTER JOIN Customers ON Orders.cust id = Customers.cust id; (!!!!) читать документацию
------ Использование объединений со статистическими функциями-------------
SELECT Customers.cust_id, COUNT(Orders.order_num) AS
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_id FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust id; (!!!!)
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id; (запустился)
---------Использование объединений и условий объединения-------------
Посмотрите в документации СУБД, какой именно синтаксис объединений она поддерживает.
- Комбинированные запросы-------
Запросы в языке SQL комбинируются с помощью оператора UNION
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI');
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name IN ('Fun4ALL');
Комбинация этих запросов:
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name IN ('Fun4ALL');
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') OR cust_name='Fun4ALL';
--- Включение или исключение повторяющихся строк----
Запрос UNION автоматически удаляет все повторяющиеся строки из набора результатов запроса (иными словами, он ведет себя точно так же, как вели бы себя несколько предложений WHERE в одном операторе SELECT)
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION ALL SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name IN ('Fun4ALL');
При использовании запроса UNION ALL СУБД не удаляет дубликаты.
----Сортировка результатов комбинированных запросов----
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name IN ('Fun4ALL') ORDER BY cust_name, cust_contact;
------------- Добавление данных-------------------
Добавление полных строк
INSERT INTO Customers VALUES('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA', NULL, NULL);
Безопасный способ:
INSERT INTO Customers (cust_id, cust_name, cust_address, cust_city, cust_state, cust_ZIP, cust_country, cust_contact, cust_email) VALUES('1000000009', 'Toy Land', '123 Any Street' , 'New York', 'NY' , '11111' , 'USA' , NULL, NULL);
INSERT INTO Customers (cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_ZIP), VALUES('1000000006',NULL,NULL, 'Toy Land', '123 Any Street' , 'New York', 'NY' , '11111');
(!!!!)
INSERT INTO Customers (cust_id, cust_name, cust_address, cust_city, cust_state, cust_ZIP, cust_country) VALUES('1000000010', 'Toy Land', '123 Any Street' , 'New York', 'NY' , '11111' , 'USA');
--- Добавление выбранных данных-----
INSERT INTO Customers (cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_ZIP, cust_country) SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_ZIP, cust_country FROM CustNew; (!!!!)
-- Копирование данных из одной таблицы в другую---
SELECT * INTO CustCopy FROM Customers; (!!!!)
CREATE TABLE Custcopy AS SELECT*FROM Customers;
-------- Обновление и удаление данных-------------------
UPDATE Customers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '1000000005';
----несколько столбцов------
UPDATE Customers SET cust_contact = 'Sam Roberts', cust_email='sam@toyland.com' WHERE cust_id = '1000000006';
Чтобы удалить значения столбца, вы можете присвоить его строкам значения NULL
UPDATE Customers SET cust_email = NULL WHERE cust_id = '1000000005';
----------- Удаление данных--------------
DELETE FROM Customers WHERE cust_id = '1000000006';
--¬ Создание таблиц и работа с ними----
CREATE TABLE ProductsX ( prod_id CHAR(10) NOT NULL, vend_id CHAR(10) NOT NULL, prod_name CHAR(254) NOT NULL, prod_price DECIMAL(8,2) NOT NULL,prod_desc VARCHAR(1000) NULL);
------ Работа со значениями NULL----
CREATE TABLE Orders ( order_num INTEGER NOT NULL, order_date DATETIME NOT NULL, cust_id CHAR(10) NOT NULL);
CREATE TABLE Vendors ( vend_id CHAR(10) NULL, vend_name CHAR(50) NOT NULL, vend_address CHAR(50), vend_city CHAR(50), vend_state CHAR(5), vend_ZIP CHAR(10), vend_country CHAR(50));
---- Определение значений по умолчанию---
CREATE TABLE Orderltems ( order_num INTEGER NOT NULL, order_item INTEGER NOT NULL, prod_id CHAR(10) NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, item_price DECIMAL (8 ,2) NOT NULL);
-------- Обновление таблиц---------
ALTER TABLE Vendors ADD vend_phone CHAR(20);
ALTER TABLE Vendors DROP COLUMN vend_phone;
------- Удаление таблиц------------
DROP TABLE CustCopy;
-------------------- Использование представлений---------------------
Представления — это виртуальные таблицы. В отличие от таблиц, содержащих данные, представления содержат запросы, которые динамически выбирают данные, когда это необходимо.
SELECT cust_name, cust_contact FROM Customers, Orders, Orderltems WHERE Customers.cust_id = Orders.cust_id AND Orderltems.order_num = Orders.order_num AND prod_id = 'RGAN01';
Теперь предположим, что вы могли бы сохранить этот запрос в виртуальной таблице с именем ProductCustomers.
SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';
------------- Создание представлений-----------------
------- Использование представлений для упрощения сложных объединений----------------------------------
CREATE VIEW ProductCustomers AS SELECT cust_name, cust_contact, prod_id FROM Customers, Orders, Orderltems WHERE Customers.cust_id = Orders.cust_id AND Orderltems.order_num = Orders.order_num;
SELECT cust_name, cust_contact FROM ProductCustomers WHERE prod_id = 'RGAN01';
---- Использование представлений для переформатирования выбранных данных----
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend__country) + ' )' AS vend_title FROM Vendors ORDER BY vend_name; (!!!!)
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ' )' AS vend_title FROM Vendors ORDER BY vend_name;
CREATE VIEW VendorLocations AS SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country)+ ')' AS vend_title FROM Vendors;
CREATE VIEW VendorLocations AS SELECT RTRIM (vend_name) || '(' || RTRIM (vend_country) || ')' AS vend_title FROM Vendors; (!!!!)
Использование представлений для фильтрации нежелательных данных
CREATE VIEW CustomerEMailList AS SELECT cust_id, cust_name, cust_email FROM Customers WHERE cust_email IS NOT NULL;
SELECT * FROM CustomerEMailList;
Использование представлений с вычисляемыми полями:
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
CREATE VIEW OrderltemsExpanded AS SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM Orderltems;
FROM SELECT * OrderltemsExpanded WHERE order_num = 20008;
------------------Работа с хранимыми процедурами------------
----------- Выполнение хранимых процедур-----------
EXECUTE AddNewProduct ('JTS01' , 'Stuffed Eiffel Tower', 6.49, 'Plush stuffed toy with the text La Tour Eiffel in red white and blue');
-------------- Создание хранимых процедур-------------
CREATE PROCEDURE MailingListCount (ListCount OUT NUMBER) IS BEGIN SELECT * FROM Customers WHERE NOT cust_email IS NULL; ListCount := SQL%ROWCOUNT; END; (!!!!)
CREATE PROCEDURE MailingListCount AS DECLARE @cnt INTEGER SELECT @cnt = COUNT (*) FROM Customers WHERE NOT cust_email IS NULL ; RETURN @cnt; (!!!!)
CREATE PROCEDURE NewOrder @cust_id CHAR(10) AS -- Объявление переменной для номера заказа DECLARE @order_num INTEGER -- Получение текущего наибольшего номера заказа SELECT @order_num=MAX(order_num) FROM Orders Determine next order number SELECT @order_num=@order_num+l -- Добавление нового заказа INSERT INTO Orders(order_num VALUES(@order_num, GETDATE() -- Возвращение номера заказа RETURN ©order nura; (!!!)
CREATE PROCEDURE NewOrder @cust_id CHAR(10) AS -- Добавление нового заказа INSERT INTO Orders(cust_id) VALUES(@cust_id) -- Возвращение номера заказа SELECT order num = ©©IDENTITY; (!!!)
---- Обработка транзакций----
BEGIN TRANSACTION DELETE Orderltems WHERE order_num = 12345 DELETE Orders WHERE order_num = 12345 COMMIT TRANSACTION
Для отмены части транзакции вы должны иметь возможность размещения меток в стратегически важных точках блока транзакции. Потом, если понадобится отмена, вы сможете вернуть базу данных в состояние, соответствующее одной из меток
SAVE TRANSACTION deletel;
BEGIN TRANSACTION INSERT INTO Customers(cust_id, cust_name) VALUES(4000000010', 'Toys Emporium'); SAVE TRANSACTION StartOrder; INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20100,'2001/12/1','1000000010'); IF @@ERROR о 0 ROLLBACK TRANSACTION StartOrder; INSERT INTO Orderltems(order_num, order_item, (i>prod_id, quantity, item_price) VALUES(20010, 1, 'BR011, 100, 5.49); IF @@ERROR 0 ROLLBACK TRANSACTION StartOrder; INSERT INTO Orderltems(order_num, order_item, 4>prod_id, quantity, item_price) VALUES(20010, 2, 'BR031, 100, 10.99); IF @@ERR0R 0 ROLLBACK TRANSACTION StartOrderj COMMIT TRANSACTION (!!!!)
Транзакции представляют собой блоки из операторов SQL, которые должны выполняться в пакетном режиме (все вместе). Вы познакомились с правилами использования операторов COMMIT и ROLLBACK для явного управления процессами записи и отмены результатов операций.
Использование курсоров
DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL;
DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL;DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL;
DECLARE TYPE CustCursor IS REF CURSOR Ь RETURN Customers%ROWTYPE; DECLARE CustRecord Customers%ROWTYPE 3EGIN OPEN CustCursor; FETCH CustCursor INTO CustRecord; CLOSE CustCursor; END;
DECLARE TYPE CustCursor IS REF CURSOR •^ RETURN CuStomers%ROWTYPE; DECLARE CustRecord Customers%ROWTYPE BEGIN OPEN CustCursor; LOOP FETCH CustCursor INTO CustRecord; EXIT WHEN CustCursor%NOTFOUND;
Расширенные возможности SQL
Что такое ограничения
Первичные ключи
Что такое индексы
CREATE INDEX prod_name_ind ON PRODUCTS (prod_name);
Что такое триггеры
Безопасность баз данны
Применение PHP в SQL
http://fkn.ktu10.com/?q=taxonomy/term/4418
Пункт 8
Неупорядоченный массив данных из таблиц состоящих из 1 столбца (записей) есть база даннных. Запись содержит в себе столбцы поля. Каждое поле имеет свой тип.
Администрирование базы данных
http://dev.mysql.com/downloads/workbench/. MySQL Workbench
Первичный ключ таблицы (естественные и суррогатный)
CREATE DATABASE wet;
SHOW DATABASES
DROP DATABASE wet;
SHOW DATABASES;
CREATE DATABASE wet DEFAULT CHARACTER SET utf8;
USE test;
SHOW TABLES; (выводит список таблиц в текущей базе данных.)
SELECT mysql.User.User, mysql.User.Host FROM mysql.User;
CREATE TABLE authors (
id INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT,
passw TINYTEXT,
email TINYTEXT,
url TEXT,
iсq TINYTEXT,
about TEXT,
photo TINYTEXT,
putdate DATETIME DEFAULT NULL,
last_time DATETIME DEFAULT NULL,
themes INT(10) DEFAULT NULL,
statususer ENUM('user','moderator','admin') NOT NULL default 'user',
PRIMARY KEY (id)
);
CREATE TABLE forums (
id INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT,
rule TEXT,
logo TINYTEXT,
pos INT(11) DEFAULT NULL,
hide ENUM('show','hide') NOT NULL DEFAULT 'show',
PRIMARY KEY (id)
);
CREATE TABLE themes (
id INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT,
author TINYTEXT,
id_author INT(11) DEFAULT NULL,
hide ENUM('show','hide') NOT NULL DEFAULT 'show',
putdate DATETIME DEFAULT NULL,
forum_id INT(11) default NULL,
PRIMARY KEY (id)
);
CREATE TABLE posts (
id INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT,
url TEXT,
file TINYTEXT,
author TINYTEXT,
author_id INT(11) DEFAULT NULL,
hide ENUM('show','hide') NOT NULL DEFAULT 'show',
putdate DATETIME DEFAULT NULL,
parent_post INT(11) DEFAULT NULL,
theme_id INT(11) DEFAULT NULL,
PRIMARY KEY (id)
);
Посмотрим структуру forums
DESCRIBE forums;
Переименуем стобцец:
ALTER TABLE forums CHANGE test new_test TEXT;
DESCRIBE forums;
ALTER TABLE forums CHANGE new_test new_test INT(5) NOT NULL;
DESCRIBE forums;
ALTER TABLE forums DROP new_test;
DESCRIBE forums;
INSERT INTO tb1 VALUES (10,20);
SELECT * FROM tbl;
Вставка значений в таблицу
CREATE TABLE tbl (
id INT(11) NOT NULL DEFAULT '5',
cat_id INT(11) DEFAULT NULL
);
INSERT INTO catalogs VALUES (1, 'Процессоры');
INSERT INTO catalogs VALUES (2, "Память");
INSERT INTO catalogs VALUES (3, "Память \"DDR\"");
CREATE TABLE tbl (
id INT(11) NOT NULL,
putdate DATETIME NOT NULL,
lastdate DATE NOT NULL
);
INSERT INTO tbl VALUES (1, '2016-01-03 0:00:00', '2016-01-03');
INSERT INTO tbl VALUES (3, NOW(), NOW());
INSERT INTO tbl
VALUES (4, '2016-01-01 0:00:00' - INTERVAL 3 WEEK,
NOW() + INTERVAL 3 MONTH);
SELECT * FROM tbl;
Вставка уникальных значений
CREATE TABLE tbl (
id INT(11) NOT NULL,
name TINYTEXT NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO tbl VALUES (1, 'Видеоадаптеры');
INSERT INTO tbl VALUES (1, 'Видеоадаптеры');
Если необходимо, чтобы новые записи с дублирующим ключом отбрасывались без ге-
нерации ошибки, следует добавить после оператора INSERT ключевое слово IGNORE .
Механизм AUTO_INCREMENT
CREATE TABLE tbl (
id INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO tbl VALUES (NULL, 'Процессоры');
INSERT INTO tbl VALUES (NULL, 'Материнские платы');
INSERT INTO tbl VALUES (NULL, 'Видеоадаптеры');
SELECT * FROM tbl;
Многострочный оператор INSERT
CREATE TABLE catalogs (
catalog_id int(11) NOT NULL AUTO_INCREMENT,
name tinytext NOT NULL,
PRIMARY KEY (catalog_id)
);
INSERT INTO catalogs VALUES (NULL, 'Процессоры'),
(NULL, 'Материнские платы'),
(NULL, 'Видеоадаптеры'),
(NULL, 'Жесткие диски'),
(NULL, 'Оперативная память');
INSERT INTO catalogs (name) VALUES ('Процессоры'),
('Материнские платы'),
('Видеоадаптеры'),
('Жесткие диски'),
('Оперативная память');
Удаление данных
DELETE FROM catalogs WHERE id > 2;
SELECT * FROM catalogs;
Обновление записей
UPDATE [IGNORE] tbl
SET col1=expr1 [, col2=expr2 ...]
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]
UPDATE catalogs SET name = 'Процессоры (Intel)'
WHERE name = 'Процессоры';
SELECT * FROM catalogs;
REPLACE INTO catalogs VALUES
(4, 'Сетевые адаптеры'),
(5, 'Программное обеспечение'),
(6, 'Мониторы'),
(7, 'Периферия'),
(8, 'CD-RW/DVD');
SELECT * FROM catalogs ORDER BY catalog_id;
Выборка данных
SELECT catalog_id, name FROM catalogs;
SELECT name, catalog_id FROM catalogs;
Условная выборка
SELECT * FROM catalogs WHERE catalog_id > 2;
SELECT catalog_id, catalog_id > 2 FROM catalogs;
SELECT * FROM catalogs
WHERE catalog_id > 2 AND catalog_id
SELECT catalog_id, catalog_id > 2, NOT catalog_id > 2 FROM catalogs;
SELECT * FROM catalogs WHERE catalog_id NOT BETWEEN 3 AND 4;
SELECT * FROM catalogs WHERE catalog_id IN (1,2,5);
SELECT * FROM catalogs WHERE catalog_id NOT IN (1,2,5);
SELECT * FROM catalogs WHERE name = 'Процессоры';
SELECT * FROM catalogs WHERE name LIKE '%ы';
Оператор возвращает 0, если выражение expr соответствует выражению pat , и 1 —
в противном случае. Таким образом, с его помощью можно извлечь записи, которые не
удовлетворяют указанному условию.
expr NOT LIKE pat
SELECT * FROM catalogs WHERE name NOT LIKE '%ы';
Псевдонимы столбцов
SELECT catalog_id, name, DATE_FORMAT(putdate, '%d.%m.%Y') AS printdate
FROM catalogs;
Сортировка записей
SELECT catalog_id, name FROM catalogs ORDER BY catalog_id;
SELECT catalog_id, name FROM catalogs ORDER BY name;
Сортировка в обратном порядке
SELECT catalog_id, name FROM catalogs ORDER BY catalog_id DESC;
CREATE TABLE tbl (catalog_id int(11) NOT NULL, putdate datetime NOT NULL
);
SELECT * FROM tbl ORDER BY catalog_id, putdate DESC;
Вывод записей в случайном порядке
SELECT catalog_id, name FROM catalogs ORDER BY RAND();
Ограничение выборки
SELECT catalog_id, name FROM catalogs
ORDER BY catalog_id DESC
LIMIT 2;
SELECT catalog_id, name FROM catalogs
ORDER BY catalog_id DESC
LIMIT 2, 2;
Вывод уникальных значений
SELECT catalog_id FROM tbl ORDER BY catalog_id;
Расширение PDO
CREATE TABLE news (
news_id INT(11) NOT NULL AUTO_INCREMENT,
name TINYTEXT NOT NULL,
putdate DATETIME NOT NULL,
PRIMARY KEY (news_id)
);
CREATE TABLE news_contents (
content_id INT(11) NOT NULL AUTO_INCREMENT,
content TEXT NOT NULL,
news_id INT(11) NOT NULL,
PRIMARY KEY (content_id)
);
Подключаем PDO:
http://fkn.ktu10.com/?q=node/6541
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
- не работает, т.к. выдаёт ошибку.
Я проверил текущую версию, PHP.
echo 'Текущая версия PHP: ' . phpversion();
Потом, нужно в папке (http://fkn.ktu10.com/?q=taxonomy/term/4418) изменить конфигурационный файл, то есть нужно добавить строчки в файл .ini
// В php.ini добавить
extension=pdo.so
extension=pdo_mysql.so
Вот я сейчас разбираюсь.
Если вы запишете функцию f(a) , f(a,b), f(a,b,c) f(a[,b[,c]); Именно поэтому было написано
PDO::__construct (
string $dsn [,
string $username [,
string $password [,
array $options]]])
Таблица находится на сервере. Загружая дамп таблицы в C:/dumps мы загружаем дамп. А таблица на сервере может иметь другие поля. Для этого нужно снова создать таблицу и выбрать ее оператором use database.
$pdo = new PDO('mysql:host=localhost;dbname=sqlstartq', 'root', 'root');
<?php /* В Mysql вводим команду CREATE TABLE catalogs (catalog_id int(11) NOT NULL AUTO_INCREMENT,name tinytext NOT NULL, PRIMARY KEY (catalog_id));INSERT INTO catalogs VALUES (NULL, 'Процессоры'), (NULL, 'Материнские платы'), (NULL, 'Видеоадаптеры'), (NULL, 'Жесткие диски'), (NULL, 'Оперативная память'); */ /* // файл connect_db.php try { $pdo = new PDO('mysql:host=localhost;dbname=sqlstartq', 'root', 'root'); } catch (PDOException $e) { echo "Невозможно установить соединение с базой данных"; } */ /* // require_once('connect_db.php'); // Выполняем запрос Извлечение текущей версии файл version.php $query = "SELECT VERSION() AS version"; $ver = $pdo->query($query); // Извлекаем результат $version = $ver->fetch(); echo $version['version']; // 5.5.46-0ubuntu0.14.04.2 */ /* //Использование метода PDO::exec() Файл exec.php //require_once('connect_db.php'); $query = "CREATE TABLE catalogs ( catalog_id INT(11) NOT NULL AUTO_INCREMENT, name TINYTEXT NOT NULL, PRIMARY KEY (catalog_id))"; $count = $pdo->exec($query); if ($count !== false) echo "Таблица создана успешно"; else { echo "Не удалось создать таблицу"; echo "<pre>"; print_r($pdo->errorInfo()); echo "<pre>"; } // Дальше на сервере MySQL нужно проверитьналичие Catalogs. Для этого введем Show tables. Если ввести SELECT*FROM Catalogs то мы уввидим что поле не содержит данных. "Emty Set" */ /* // Ошибочный запрос Файл errors.php // require_once("connect_db.php"); try { $query = "SELECT VERSION1() AS version"; $ver = $pdo->query($query); echo $ver->fetch()['version']; } catch (PDOException $e) { echo "Ошибка выполнения запроса: " . $e->getMessage(); } */ // не запустился код ошибки нет Ошибка выполнения запроса: SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION test.VERSION1 does not exist // Обработка ошибки соединения с базой данных. Файл connect.php try { $pdo = new PDO ( 'mysql:host=localhost;dbname=sqlstartq', 'root', 'root', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION] ); } catch (PDOException $e) { echo "Невозможно установить соединение с базой данных"; } /* //Вывод содержимого таблицы catalogs. Файл fetch.php //require_once("connect.php") // Формируем запросы $query = "SELECT * FROM catalogs"; $cat = $pdo->query($query); try { while($catalog = $cat->fetch()) echo $catalog['name']."<br />"; } catch (PDOException $e) { echo "Ошибка выполнения запроса: " . $e->getMessage(); } */ /* $query = "SELECT * FROM catalogs"; $cat = $pdo->query($query); $catalog = $cat->fetch(); echo "<pre>"; print_r($catalog); echo "</pre>"; */ /* $query = "SELECT * FROM catalogs"; $cat = $pdo->query($query); $catalog = $cat->fetch(PDO::FETCH_ASSOC); echo "<pre>"; print_r($catalog); echo "</pre>"; */ /* // Использование метода fetchAll(). Файл fetch_all.php // require_once("connect.php"); try { $query = "SELECT * FROM catalogs"; $cat = $pdo->query($query); $catalogs = $cat->fetchAll(); foreach($catalogs as $catalog) echo $catalog['name']."<br />"; } catch (PDOException $e) { echo "Ошибка выполнения запроса:".$e->getMessage(); } */ /* //Параметризованный запрос. Файл prepare.php // require_once("connect.php"); try { $query = "SELECT*FROM catalogs WHERE catalog_id = :catalog_id"; $cat = $pdo->prepare($query); $cat->execute(['catalog_id' => 1]); echo $cat->fetch()['name']; // Процессоры } catch (PDOException $e) { echo "Ошибка выполнения запроса: " . $e->getMessage(); } */ /* // require_once("connect.php"); try { $query = "SELECT *FROM catalogs WHERE catalog_id = ?"; $cat = $pdo->prepare($query); $cat->execute([1]); echo $cat->fetch()['name']; // Процессоры } catch (PDOException $e) { echo "Ошибка выполнения запроса: " . $e->getMessage(); } */ /* Записываем в SQL код CREATE TABLE news (news_id INT(11) NOT NULL AUTO_INCREMENT,name TINYTEXT NOT NULL, putdate DATETIME NOT NULL, PRIMARY KEY (news_id) );CREATE TABLE news_contents (content_id INT(11) NOT NULL AUTO_INCREMENT, content TEXT NOT NULL, news_id INT(11) NOT NULL, PRIMARY KEY (content_id) ); */ /* <!DOCTYPE html> <html lang="ru"> <head> <title> Добавление новости </title> <meta charset='utf-8'> </head> <body> <table> <form action='0.php' method='POST'> <tr> <td>Название:</td> <td><input type='text' name='name'> </td> </tr> <tr> <td>Содержимое:</td> <td><textarea name='content' rows='10' cols='40'></textarea></td> </tr> <tr> <td> </td> <td> <input type='submit' value='Добавить'> </td> </tr> </form> </table> </body> </html> */ //Добавление новостного сообщения. Файл addnews.php // require_once("connect.php"); try { // Проверяем, заполнены ли поля HTML-формы if (empty($_POST['name'])) exit('Не заполнено поле "Название"'); if (empty($_POST['content'])) exit('Не заполнено поле "Содержимое"'); // Добавляем новостное сообщение в таблицу news $query = "INSERT INTO news VALUES (NULL, :name, NOW())"; $news = $pdo->prepare($query); $news->execute(['name' => $_POST['name']]); // Получаем только что сгенерированный идентификатор news_id $news_id = $pdo->lastInsertId(); // Вставляем содержимое новостного сообщения в таблицу news_contents. // Формируем запросы $query = "INSERT INTO news_contents VALUES (NULL, :content, :news_id)"; $news = $pdo->prepare($query); $news->execute(['content' => $_POST['content'], 'news_id' => $news_id]); // Осуществляем переадресацию на главную страницу header("Location: 1.html"); } catch (PDOException $e) { echo "Ошибка выполнения запроса: " . $e->getMessage(); } // данные записываются в news_content ?>
- Log in to post comments
- 11045 reads