【MySQL】常用 SQL 语句

# 查看所有数据库
show DATABASES;
# 创建数据库
CREATE DATABASE `test`
# 我宁愿什么都不做，也不愿犯错
CREATE DATABASE IF NOT EXISTS `test`
# 创建数据库的时候设置字符集
CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET = "utf8mb4"

CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `NAME` varchar(128),
  `AGE` int COMMENT '年龄',
  `ADDRESS` varchar(128),
  `CREATE_TIME` timestamp DEFAULT CURRENT_TIMESTAMP,
) COMMENT = '用户表';

ALTER TABLE `user` RENAME `user2`;
ALTER TABLE `user` ADD COLUMN `hobby` VARCHAR(256) COMMENT '爱好';
ALTER TABLE `user` DROP `hobby`;
ALTER TABLE `user` MODIFY `age` VARCHAR(256) COMMENT '年龄';
ALTER TABLE `user` DROP `age`, DROP `address`;

查询：

SELECT * FROM `user`;
SELECT id from `user`;
SELECT id, name from `user`;
SELECT id as user_id FROM `user`;
# DESC 降序 ASC 升序
SELECT * FROM `user` ORDER BY id DESC;
# 开始行hang为 0 查询 4 条数据
SELECT * FROM `user` LIMIT 0, 4;
# and && or || where 完全匹配
SELECT * FROM `user` WHERE name = 'xx' AND age <= 20;
# like 模糊匹配 %x 以x结尾，前面有一个字符 %x% 带有x的都查出来 __x% 前面有两个字符后跟着x结尾
SELECT * FROM `user` WHERE name LIKE '%x';

INSERT INTO user(`name`, `age`) VALUES('heo', 20),('heo2', NULL);
UPDATE `user` SET name='heoo',age=200 WHERE id = 2;
DELETE FROM `user` WHERE id=2;
DELETE FROM `user` WHERE id IN(11,12,13);