Mysql Notes

用navicat等客户端连接mysql8时，可能会遇到[Authentication plugin 'caching_sha2_password' cannot be loaded]的问题。可以用命令行登录mysql，并执行以下命令：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newrootpassword';

VARCHAR

CHAR

CREATE TABLE `char_test` (
  `char_col` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO char_test (char_col)
VALUES
	('string1'),
	(' string2'),
	('string3 ');

SELECT
	CONCAT("'", char_col, "'")
FROM
	char_test;

CREATE TABLE `varchar_test` (
	`char_col` VARCHAR (10) DEFAULT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO varchar_test (char_col)
VALUES
	('string1'),
	(' string2'),
	('string3 ');

SELECT
	CONCAT("'", char_col, "'")
FROM
	varchar_test;

CREATE TABLE `enum_test` (
	`e` enum ('fish', 'apple', 'dog') NOT NULL
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO enum_test (e)
VALUES
	('fish'),
	('dog'),
	('apple');

SELECT
	e + 0
FROM
	enum_test;

SELECT
	e
FROM
	enum_test
ORDER BY
	e

SELECT
	e
FROM
	enum_test
ORDER BY
	FIELD(e, 'apple', 'dog', 'fish')

CREATE TABLE msg_per_hr (
	hr DATETIME NOT NULL,
	cnt INT UNSIGNED NOT NULL,
	PRIMARY KEY (hr)
);

SELECT
	SUM(cnt)
FROM
	msg_per_hr
WHERE
	hr BETWEEN CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 23 HOUR
AND CONCAT(LEFT(NOW(), 14), '00:00') - INTERVAL 1 HOUR

CREATE TABLE tbl_shelf_new LIKE tbl_shelf

INSERT INTO tbl_shelf_new SELECT
	ID,
	CREATE_TIME,
	MODIFY_TIME,
	NAME,
	NO,
	STATUS,
	POINT_NO,
	TYPE,
	REMARAK
FROM
	tbl_shelf

RENAME TABLE tbl_shelf TO tbl_shelf_old,
 tbl_shelf_new TO tbl_shelf

CREATE TABLE `hit_counter` (
	slot TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
	cnt INT UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`slot`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

CREATE PROCEDURE pro()
BEGIN
DECLARE i INT;
SET i = 0;
WHILE i < 100 DO
INSERT INTO `hit_counter` (`cnt`) VALUES ('0');
SET i = i + 1;
END WHILE;
END;

CALL pro()

SELECT
	SUM(cnt)
FROM
	hit_counter

CREATE TABLE daily_hit_counter (
	day date NOT NULL,
	slot TINYINT UNSIGNED NOT NULL,
	cnt INT UNSIGNED NOT NULL,
	PRIMARY KEY (DAY, slot)
) ENGINE = INNODB;

INSERT INTO daily_hit_counter (DAY, slot, cnt)
VALUES
	(CURRENT_DATE, RAND() * 100, 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

UPDATE daily_hit_counter AS c
INNER JOIN (
	SELECT
		day,
		SUM(cnt) AS cnt,
		MIN(slot) AS mslot
	FROM
		daily_hit_counter
	GROUP BY
		day
) AS x USING (DAY)
SET c.cnt =
IF (c.slot = x.mslot, x.cnt, 0),
 c.slot =
IF (c.slot = x.mslot, 0, c.slot);

DELETE
FROM
	daily_hit_counter
WHERE
	slot <> 0
AND cnt = 0;

加快ALTER TABLE操作的速度

ALTER TABLE hit_counter ALTER COLUMN cnt
SET DEFAULT 5

左链接的含义

1. 以左链接为主表，返回左表里所有的行，并以此和右表连接。
2. 即使右表没有和左表相对应的行，也需要返回null值。

select指定的字段要么包含在group by语句里，要么被包含在聚合函数中。