СУБД. Лекция 3

INSERT

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

INSERT

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

INSERT

INSERT INTO tbl_temp2 (fld_id)
    SELECT 
        tbl_temp1.fld_order_id
    FROM 
        tbl_temp1 
    WHERE 
        tbl_temp1.fld_order_id > 100;
 
INSERT INTO table (a,b,c) VALUES (1,2,3) 
    ON DUPLICATE KEY UPDATE c=c+1;
 
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) 
    ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

DELETE

DELETE
    t1, t2 
FROM 
    t1 INNER JOIN t2 
    INNER JOIN t3 
WHERE 
    t1.id=t2.id AND t2.id=t3.id;

Or:

DELETE FROM
    t1, t2 
USING 
    t1 INNER JOIN t2 
    INNER JOIN t3 
WHERE 
    t1.id=t2.id AND t2.id=t3.id;

Пользовательские переменные

SET @var_name = expr [, @var_name = expr] ...

SET @t1 = 1, @t2 = 2, @t3 := 4;
SELECT @t1, @t2, @t3, @t4 := @t1 + @t2 + @t3

SELECT @t1 := (@t2 := 1) + @t3 := 4, @t1, @t2, @t3;

SET @i := 0;
SELECT @i := @i + 1 AS row_number, t.* FROM your_table AS t;
 
SELECT @i := @i + 1 AS row_number, t.*
FROM your_table AS t, (SELECT @i:=0) AS z;

SUBQUERIES

SELECT
    col0,
    (SELECT col1 FROM table1 WHERE table1.id = table0.id),
    (SELECT col2 FROM table1 WHERE table1.id = table0.id)
FROM
    table0;

non_subquery_operand comparison_operator (subquery)
=  >  <  >=  <=  <>  !=  LIKE

SELECT *
FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);

SELECT *
FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

SUBQUERIES

perand comparison_operator ANY (subquery)
operand IN (subquery) operand
comparison_operator SOME (subquery)
 
    SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
 
    SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
    SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
 
operand comparison_operator ALL (subquery)
 
    SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
 
    SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
    SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

ROW SUBQUERIES

SELECT *
FROM t1
WHERE (col1, col2)
	= (SELECT col3, col4 FROM t2 WHERE id = 10);
 
SELECT *
FROM t1
WHERE ROW (col1, col2)
	= (SELECT col3, col4 FROM t2 WHERE id = 10);

SELECT column1, column2, column3
FROM t1
WHERE (column1, column2, column3)
    IN (SELECT column1, column2, column3 FROM t2);

[NOT] EXISTS

SELECT DISTINCT store_type
FROM stores
WHERE EXISTS (
    SELECT *
    FROM cities_stores
    WHERE cities_stores.store_type = stores.store_type
);
 
SELECT DISTINCT store_type
FROM stores
WHERE NOT EXISTS (
    SELECT *
    FROM cities_stores
    WHERE cities_stores.store_type = stores.store_type
);

SUBQUERIES in FROM

SELECT ... FROM (subquery) [AS] name ...
 
SELECT AVG(sum_column1)
FROM (
    SELECT SUM(column1) AS sum_column1
    FROM t1
    GROUP BY column1
) AS t1;

UNION

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
SELECT ...]

SELECT * FROM sales2010
UNION
SELECT * FROM sales2011;

SELECT * FROM sales2010
UNION ALL
SELECT * FROM sales2011;

Набор данных

CROSS JOIN

SELECT * FROM Employee CROSS JOIN Department;

CROSS JOIN

SELECT * FROM Employee, Department;

INNER JOIN

SELECT *
FROM Employee
INNER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

INNER JOIN

SELECT *
FROM Employee, Department
WHERE Employee.DepartmentID = Department.DepartmentID;

NATURAL (EQUAL) JOIN

SELECT *
FROM Employee
NATURAL JOIN Department;

NATURAL JOIN

SELECT *
FROM Employee
JOIN Department USING (DepartmentID);

LEFT OUTER JOIN

SELECT *
FROM Employee
LEFT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

FULL OUTER JOIN

SELECT *
FROM Employee
FULL OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

FULL OUTER JOIN

SELECT *
FROM Employee
FULL OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;

SELECT *
FROM Employee
LEFT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID;
 
UNION ALL
 
SELECT *
FROM Employee
RIGHT OUTER JOIN Department
ON Employee.DepartmentID = Department.DepartmentID
WHERE Employee.DepartmentID IS NULL;

SELF-JOIN

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F
INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

SELF-JOIN

SELF-JOIN

Представление (VIEW)

Объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)] AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION];

Преимущества VIEW

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

Ограничения VIEW

• Нельзя повесить триггер на представление;
• Нельзя сделать представление на основе временных таблиц;
• Нельзя сделать временное представление;
• В определении представления нельзя использовать подзапрос в части FROM;
• В определении представления нельзя использовать системные и пользовательские переменные;
• Таблицы и представления, присутствующие в определении представления должны существовать.

Особенности VIEW

CREATE VIEW v AS
    SELECT a.id, b.id FROM a,b;
-- Error Code: 1060. Duplicate column name 'id'

CREATE VIEW v (a_id, b_id) AS
    SELECT a.id, b.id FROM a,b;
CREATE VIEW v AS
    SELECT a.id a_id, b.id b_id FROM a,b;

CREATE VIEW v AS
    SELECT GROUP_CONCAT(
        DISTINCT column_name
        ORDER BY column_name separator '+'
    )
    FROM table_name;

Особенности VIEW

• Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
• Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.
• При наличии в обоих операторах модификаторов, влияющих на механизм блокировки, таких как HIGH_PRIORITY, результат их совместного действия неопределен. Для избежания неопределенности рекомендуется в определении представления не использовать подобные модификаторы.

Алгоритмы VIEW (MERGE)

CREATE VIEW v AS
SELECT subject, num_views / num_replies AS param
FROM topics
WHERE num_replies > 0;

SELECT subject, param FROM v WHERE param > 1000;

SELECT subject, num_views / num_replies AS param
FROM topics
WHERE num_replies>0 AND num_views / num_replies > 1000;

Алгоритмы VIEW (TEMPTABLE)

CREATE VIEW v AS
SELECT forum_id, COUNT(*) AS num
FROM topics GROUP BY forum_id;

SELECT MAX(num) FROM v;

SELECT MAX(COUNT(*))
FROM topics GROUP BY forum_id;

CREATE TEMPORARY TABLE tmp_table
SELECT forum_id, COUNT(*) AS num
FROM topics GROUP BY forum_id;

SELECT MAX(num) FROM tmp_table;

DROP TABLE tmp_table;

Изменение VIEW

WITH [CASCADED | LOCAL] CHECK

• Изменение данных (UPDATE) будет происходить только если строка с новыми значениями удовлетворяет условию WHERE в определении представления.
• Добавление данных (INSERT) будет происходить только если новая строка удовлетворяет условию WHERE в определении представления.
• Для LOCAL происходит проверка условия WHERE только в собственном определении представления.
• Для CASCADED происходит проверка для всех представлений на которых основанно данное представление. Значением по умолчанию является CASCADED.

Изменение VIEW

CREATE OR REPLACE VIEW v AS
    SELECT forum_name, subject, num_views
    FROM topics,forums f
    WHERE forum_id = f.id AND num_views > 2000
    WITH CHECK OPTION;


UPDATE v SET num_views = 1999 WHERE subject = 'test';
-- ERROR 1369 (HY000): CHECK OPTION failed 'test.v'

UPDATE v SET num_views = 2003 WHERE subject = 'test';
-- Query OK

Изменение VIEW

CREATE OR REPLACE VIEW v AS
    SELECT forum_name, subject, num_views
    FROM topics,forums f
    WHERE forum_id = f.id AND num_views > 2000
    WITH CHECK OPTION;


INSERT INTO v (subject, num_views)
VALUES ('test1', 4000);
-- ERROR 1369 (HY000): CHECK OPTION failed test.v

INSERT INTO v (forum_id, subject, num_views)
VALUES (1, 'test1', 4000);
-- ERROR 1054 (42S22): Unknown COLUMN 'forum_id' IN 'field list'