СУБД. Лекция 9 (окончание)

Сравнение с MySQL

CREATE TABLE k (
  A INT NULL,
  B NUMERIC (4, 2)
);


INSERT INTO k VALUES (NULL, 1234.5678);
Query OK, 1 row affected, 1 warning (0,00 sec)

SELECT * FROM k;
+------+-------+
| A    | B     |
+------+-------+
| NULL | 99.99 |
+------+-------+
1 rows in set (0,00 sec)

WARNING vs ERROR

sql_mode = 'STRICT_TRANS_TABLES'

Данная опция включена по-умолчанию с MySQL 5.7.5.

Первый General Availability релиз с данным изменением 5.7.9 вышел 21 октября 2015 года.

WARNING vs ERROR

ALTER TABLE k
  MODIFY COLUMN A INT NOT NULL;
Query OK, 1 row affected, 1 warning (0,00 sec)

SELECT * FROM k;
+---+-------+
| A | B     |
+---+-------+
| 0 | 99.99 |
+---+-------+
1 rows in set (0,00 sec)

TIMESTAMP

CREATE TABLE k (
  A INT NULL,
  B NUMERIC (4, 2),
  C TIMESTAMP
);


INSERT INTO k (A, B) VALUES (42, 22.7);


SELECT * FROM k;
+----+-------+---------------------+
| A  | B     |                     |
+----+-------+---------------------+
| 42 | 22.70 | 2017-04-27 20:27:17 |
+----+-------+---------------------+
1 rows in set (0,00 sec)

TIMESTAMP

SELECT * FROM k;
+----+-------+---------------------+
| A  | B     |                     |
+----+-------+---------------------+
| 42 | 22.70 | 2017-04-27 20:27:17 |
+----+-------+---------------------+
1 rows in set (0,00 sec)


UPDATE k SET B = B + 1;


SELECT * FROM k;
+----+-------+---------------------+
| A  | B     |                     |
+----+-------+---------------------+
| 42 | 23.70 | 2017-04-27 20:27:47 |
+----+-------+---------------------+
1 rows in set (0,00 sec)

TIMESTAMP

CREATE TABLE k (
  A INT NULL,
  B NUMERIC (4, 2),
  C TIMESTAMP,
  D TIMESTAMP
);
Error Code: 1067. Invalid default value for 'D'

GROUP BY

SELECT A, B, SUM(C)
FROM K
GROUP BY A;