Programming

常見MySQL語法

(參考Mysql)

Menu

Type CRUD
Table Create
Read
Update
Delete
— —
Data Create
Read
Update
Delete
— —
Index Create
Delete
— —
Column Create
Read
Update
Delete
— —
Trigger Create
Read
Delete
— —
Procedure Create
Read
Delete
— —
Variable Create
Read
Delete
— —

Create Table

1
2
3
4
5
6
7
8
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT 'user name',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `index_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Read Table

1
SHOW CREATE TABLE `users`;

Update Table

1
2
ALTER TABLE `old_table_name`
RENAME TO `new_table_name`;

Delete Table

1
DROP TABLE IF EXISTS `users`;

(Back to Menu)

Create Data

1
2
3
INSERT INTO `users` (`id`, `name`, `created_at`, `updated_at`)
VALUES (1, 'Bear', NOW(), NOW()),
(2, 'Aries', NOW(), NOW());
1
2
3
4
INSERT INTO `foods` (`name`, `country`)
SELECT 'sushi', `name`
FROM `countries`
WHERE `id` = 2;

Read Data

1
SELECT * FROM `users`;

Update Data

1
2
UPDATE `users` SET `name`='Lin'
WHERE `id` = 1;
  • JSON 欄位 (extension_item 不能為 null)

只更新 json 中的特定 key

1
2
UPDATE `foods` SET `extension_item` = json_set(`extension_item`, "$.created_at", '2022-09-27')
WHERE `id` = 1;

取代整個 json 欄位的資料

1
2
UPDATE `foods` SET `extension_item` = json_object("created_at", '2022-09-28', "updated_at", '2022-09-29')
WHERE `id` = 2;

Delete Data

1
2
3
4
5
6
SET SQL_SAFE_UPDATES = 0;

DELETE FROM `users`
WHERE `name` = 'Lin';

SET SQL_SAFE_UPDATES = 1;

(Back to Menu)

Create Index

1
2
3
4
ALTER TABLE `users`
ADD INDEX `index_name` (`name`),
ALGORITHM = INPLACE,
LOCK = NONE;

Delete Index

1
2
3
4
ALTER TABLE `users`
DROP INDEX `index_name`,
ALGORITHM = INPLACE,
LOCK = NONE;

(Back to Menu)

Create Column

1
ALTER TABLE `users` ADD COLUMN `email` varchar(255);

Read Column

1
2
DESC `users`;
SHOW COLUMNS FROM `users`;

Update Column

1
ALTER TABLE `users` MODIFY COLUMN `email` varchar(63);

Delete Column

1
ALTER TABLE `users` DROP COLUMN `email`;

(Back to Menu)

Create Trigger

1
2
3
4
5
6
7
8
CREATE TRIGGER `trigger_insert_posts`
AFTER INSERT ON `posts`
FOR EACH ROW
BEGIN
  UPDATE `stats`
  SET `post_count` = 'post_count' + 1
  WHERE user_id = new.user_id;
END;

Read Trigger

1
2
3
SHOW TRIGGERS;

SHOW CREATE TRIGGER `trigger_name`;

Delete Trigger

1
DROP TRIGGER IF EXISTS `trigger_name`;

(Back to Menu)

Create Procedure

1
2
3
4
5
CREATE PROCEDURE `procedure_users_name` (IN input_name varchar(255), OUT count INT)
BEGIN
	SELECT count(*) INTO count FROM `users`
	WHERE `name` = input_name;
END;

Read Procedure

1
2
3
SHOW PROCEDURE STATUS;

SHOW CREATE PROCEDURE `procedure_name`;

Delete Procedure

1
DROP PROCEDURE IF EXISTS `procedure_name`;

(Back to Menu)

Create Variable

1
2
3
SET @name = 'bear';

SET @id = LAST_INSERT_ID();

Read Variable

1
select @id;

Delete Variable

1
SET @id = NULL;

(Back to Menu)

ʕ •ᴥ•ʔ：記錄一些常見語法，需要時方便查詢。
大寫及``為非必要，僅用來區分語法及關鍵字。