MySQL 常用SQL语句【基础篇】

数据库

1. 选择数据库

   USE db_name;
   

2. 创建数据库

   -- 语法格式
   CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
       [DEFAULT] CHARACTER SET [=] charset_name
     | [DEFAULT] COLLATE [=] collation_name
     | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
   
   -- 创建数据库使用默认字符集：
   CREATE DATABASE db_name;
   
   -- 如果数据库不存在进行创建，并使用默认字符集：
   CREATE DATABASE IF NOT EXISTS db_name;
   
   -- 创建使用指定字符集的数据库：
   CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARACTER SET 'utf8mb4';
   
   -- 创建使用指定字符集和校对规则的数据库：
   CREATE DATABASE IF NOT EXISTS db_name 
   CHARACTER SET 'utf8mb4'
   COLLATE 'utf8mb4_general_ci';
   
   -- 创建使用指定默认字符集和校对规则的数据库：
   CREATE DATABASE IF NOT EXISTS db_name 
   DEFAULT CHARACTER SET 'utf8mb4'
   DEFAULT COLLATE 'utf8mb4_general_ci';
   
   -- 创建使用指定字符集和校对规则的数据库，并启用加密：
   CREATE DATABASE IF NOT EXISTS db_name 
   CHARACTER SET 'utf8mb4'
   COLLATE 'utf8mb4_general_ci'
   ENCRYPTION 'Y';
   

3. 修改数据库

   -- 语法格式
   ALTER {DATABASE | SCHEMA} [db_name]
       [DEFAULT] CHARACTER SET [=] charset_name
     | [DEFAULT] COLLATE [=] collation_name
     | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
     | READ ONLY [=] {DEFAULT | 0 | 1}
   
   -- 修改数据库字符集
   ALTER DATABASE db_name COLLATE utf8mb4_bin;
   
   -- 修改数据库字符集和校对规则
   ALTER DATABASE db_name 
   COLLATE utf8mb4_bin 
   COLLATE 'utf8mb4_general_ci';
   
   -- 修改数据库字符集和校对规则，并启用加密
   ALTER DATABASE db_name 
   COLLATE utf8mb4_bin 
   COLLATE 'utf8mb4_general_ci'
   ENCRYPTION 'Y';
   
   -- 修改数据库字符集和校对规则，并启用加密，将数据库设定为只读模式。
   ALTER DATABASE db_name 
   COLLATE utf8mb4_bin 
   COLLATE 'utf8mb4_general_ci'
   ENCRYPTION 'Y'
   READ ONLY 1;
   

4. 删除数据库

   -- 语法格式
   DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
   
   -- 删除数据库
   DROP DATABASE db_name;
   
   -- 当数据库存在时删除数据库
   DROP DATABASE IF EXISTS db_name;
   

数据表

1. 创建表，关于表创建的选项实在太多，在此仅列举出常用的

   -- 语法格式
   CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
       (create_definition,...)
       [table_options]
       [partition_options]
      
   -- 创建表
   CREATE TABLE tbl_name (id INT(10), name VARCHAR(20));
   
   -- 创建InnoDB数据表
   CREATE TABLE tbl_name (id INT(10), name VARCHAR(20)) ENGINE=InnoDB;
   
   -- 创建InnoDB表，并指定utf8mb4字符集
   CREATE TABLE tbl_name (id INT(10), name VARCHAR(20)) ENGINE=InnoDB CHARSET 'utf8mb4';
   
   -- 创建InnoDB表，并指定列字符集
   CREATE TABLE tbl_name (id INT(10), name VARCHAR(20) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci') ENGINE=InnoDB CHARSET 'utf8mb4';
   
   -- 创建表，并添加索引列
   CREATE TABLE tbl_name (id INT(10), name VARCHAR(20), PRIMARY KEY (`id`), INDEX `idx_name`(`name`) USING BTREE);
   
   -- 创建表，包含：自增主键、字段注释、默认值、索引、非NULL字段
   CREATE TABLE `tbl_name`.`Untitled`  (
     `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
     `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名字',
     `age` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
     PRIMARY KEY (`id`),
     INDEX `idx_name`(`name`) USING BTREE
   );
   
   -- 创建分区表，注意此处根据时间分片，时间字段必须加入主键中
   CREATE TABLE `db_name`.`Untitled`  (
     `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
     `name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '名字',
     `age` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
     `create_time` DATETIME NOT NULL COMMENT '添加时间',
     PRIMARY KEY (`id`, `create_time`),
     INDEX `idx_name`(`name`) USING BTREE
   ) PARTITION BY RANGE (YEAR(create_time))
   (
   PARTITION `p2020` VALUES LESS THAN (2020) MAX_ROWS = 0 MIN_ROWS = 0 ,
   PARTITION `p2021` VALUES LESS THAN (2021) MAX_ROWS = 0 MIN_ROWS = 0 ,
   PARTITION `p2022` VALUES LESS THAN (2022) MAX_ROWS = 0 MIN_ROWS = 0 ,
   PARTITION `p2023` VALUES LESS THAN (2023) MAX_ROWS = 0 MIN_ROWS = 0 
   );
   
   -- 创建临时表
   CREATE TEMPORARY TABLE tbl_name (id INT(10), name VARCHAR(20));
   
   -- 使用`tbl_name`表的表结构创建`users`表
   CREATE TABLE users LIKE tbl_name;
   
   -- 使用`tbl_name`表的表结构和数据，创建`users`表
   CREATE TABLE users AS SELECT * FROM tbl_name;
   

2. 修改表。

   • CHANGE重命名列或更改其定义，或者同时重命名和更改定义
   • MODIFY更改列定义，但不能更改其名称
   • RENAME COLUMN可以更改列名，但不能更改其定义
   • ALTER仅用于修改列的默认值

   -- 语法格式
   ALTER TABLE tbl_name
       [alter_option [, alter_option] ...]
       [partition_options]
       
   -- 只重命名列
   ALTER TABLE tbl_name RENAME COLUMN name TO new_name;
   ALTER TABLE tbl_name CHANGE name new_name VARCHAR(20) NOT NULL;
   
   -- 只修改列的定义
   ALTER TABLE tbl_name MODIFY name VARCHAR(50) NOT NULL;
   ALTER TABLE tbl_name CHANGE name name VARCHAR(50) NOT NULL;
   
   -- 同时修改列的定义和名称
   ALTER TABLE tbl_name CHANGE name new_name VARCHAR(50) NOT NULL;
   
   -- 同时修改多个列
   ALTER TABLE `db_name`
   MODIFY COLUMN `name` varchar(50) NOT NULL,
   MODIFY COLUMN `age` bigint(0) UNSIGNED NOT NULL;
   
   -- 删除列
   ALTER TABLE db_name DROP COLUMN name, DROP COLUMN age;
   
   -- 删除索引
   ALTER TABLE `tbl_name` DROP INDEX `idx_name`;
   

3. 删除表

   DROP [TEMPORARY] TABLE [IF EXISTS]
       tbl_name [, tbl_name] ...
       
   -- 删除表
   DROP TABLE tbl_name;
   
   -- 表存在时删除
   DROP TABLE IF EXISTS tbl_name;
   
   -- 删除临时表
   DROP TEMPORARY TABLE tbl_name;
   

数据

1. 查询数据

   -- 语法格式
   SELECT
       [ALL | DISTINCT | DISTINCTROW ]
       [HIGH_PRIORITY]
       [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
       select_expr [, select_expr] ...
       [into_option]
       [FROM table_references
         [PARTITION partition_list]]
       [WHERE where_condition]
       [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
       [HAVING where_condition]
       [WINDOW window_name AS (window_spec)
           [, window_name AS (window_spec)] ...]
       [ORDER BY {col_name | expr | position}
         [ASC | DESC], ... [WITH ROLLUP]]
       [LIMIT {[offset,] row_count | row_count OFFSET offset}]
       [into_option]
       [FOR {UPDATE | SHARE}
           [OF tbl_name [, tbl_name] ...]
           [NOWAIT | SKIP LOCKED]
         | LOCK IN SHARE MODE]
       [into_option]
       
    -- 一个简单查询
    SELECT `id`, `name` FROM `db_name`.`tbl_name`; 
   

2. 新增数据

   -- 语法格式
   INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
       [INTO] tbl_name
       [PARTITION (partition_name [, partition_name] ...)]
       [(col_name [, col_name] ...)]
       { {VALUES | VALUE} (value_list) [, (value_list)] ... }
       [AS row_alias[(col_alias [, col_alias] ...)]]
       [ON DUPLICATE KEY UPDATE assignment_list]
    
    -- 添加一条数据
    INSERT INTO tbl_name(`name`, `age`) VALUES('zhangsan', 18);
    
    -- 批量添加
    INSERT INTO tbl_name(`name`, `age`) VALUES('zhangsan', 18), ('lisi', 20);
    
    -- 省掉可以为`NULL`的列或有默认值的，假设`name`列
    INSERT INTO tbl_name(`age`) VALUES(18), (20);
    
   

3. 修改数据

   -- 语法格式
   UPDATE [LOW_PRIORITY] [IGNORE] table_reference
       SET assignment_list
       [WHERE where_condition]
       [ORDER BY ...]
       [LIMIT row_count]
   
   -- 更新单个列
   UPDATE tle_name SET name = 'LISI';
   
   -- 更新多个列
   UPDATE tle_name SET name = 'LISI', age = 20;
   
   -- 按条件更新
   UPDATE tle_name SET name = 'LISI' WHERE age = 20;
   
   -- 更新10条数据
   UPDATE tle_name SET name = 'LISI' LIMIT 10;
   
   -- 按年龄排序更新10条
   UPDATE tle_name SET name = 'LISI' ORDER BY age DESC LIMIT 10;
   

4. 删除数据

   -- 语法格式
   DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
       [PARTITION (partition_name [, partition_name] ...)]
       [WHERE where_condition]
       [ORDER BY ...]
       [LIMIT row_count]
       
    -- 删除数据
    DELETE FROM tbl_name;
    
    -- 按条件删除
     DELETE FROM tbl_name WHERE name = 'LISI';
   

视图

1. 使用视图。视图查询与普通表查询一致。

2. 新增视图

   -- 语法格式
   CREATE
       [OR REPLACE]
       [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
       [DEFINER = user]
       [SQL SECURITY { DEFINER | INVOKER }]
       VIEW view_name [(column_list)]
       AS select_statement
       [WITH [CASCADED | LOCAL] CHECK OPTION]
    
    -- 创建一个简单视图，并将`name`别名为`nickname`
    CREATE VIEW view_name AS SELECT id, name AS nickname, age FROM tbl_name;
   

3. 修改视图。修改视图与创建视图类似

   -- 语法格式
   ALTER
       [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
       [DEFINER = user]
       [SQL SECURITY { DEFINER | INVOKER }]
       VIEW view_name [(column_list)]
       AS select_statement
       [WITH [CASCADED | LOCAL] CHECK OPTION]
    
    -- 修改视图，去掉`age`列和`name`的别名
     ALTER VIEW view_name AS SELECT id, name FROM tbl_name
   

4. 删除视图

   -- 语法格式
   DROP VIEW [IF EXISTS]
       view_name [, view_name] ...
       [RESTRICT | CASCADE]
   
   -- 删除视图
   DROP VIEW IF EXISTS view_name;
   

存储过程

1. 修改SQL结束符：

   -- 语法格式
   DELIMITER end_char;
   
   -- 修改结束符为 //
   DELIMITER //;
   

2. 创建存储过程

   -- 语法格式
   CREATE
       [DEFINER = user]
       PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
       [characteristic ...] routine_body
       
   -- 创建存储过程，用于统计某个年龄段的人数
   DELIMITER //
   CREATE DEFINER=`root`@`192.168.100.%` PROCEDURE `age_count`(IN _age INT, OUT counts INT)
   BEGIN
   	SELECT COUNT(*) INTO counts FROM tbl_name WHERE `age` = _age;
   	SELECT counts;
   END//
         
   -- 调用存储过程
   call age_count(18， counts);
   

3. 删除存储过程

   -- 语法格式
   DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
   
   -- 删除存储过程
   DROP PROCEDURE IF EXISTS age_count;
   

触发器

1. 创建触发器

   -- 语法格式
   CREATE
       [DEFINER = user]
       TRIGGER [IF NOT EXISTS] trigger_name
       trigger_time trigger_event
       ON tbl_name FOR EACH ROW
       [trigger_order]
       trigger_body
   
   -- 创建触发器使用到的汇总表
   CREATE TABLE `user_count`  (
     `id` TINYINT NOT NULL,
     `count` INT UNSIGNED NOT NULL DEFAULT 0,
     PRIMARY KEY (`id`)
   );
   -- 写入一条数据到汇总表
   INSERT INTO user_count VALUES(1, 0)；
   
   -- 创建触发器
   DELIMITER //
   CREATE TRIGGER update_user_count AFTER INSERT
   ON tbl_name FOR EACH ROW
   BEGIN
   DECLARE user_cnt INT;
   SET user_cnt = (SELECT COUNT(*) FROM tbl_name);
   UPDATE user_count SET `count` = user_cnt WHERE id = 1;
   END//
   DELIMITER ;
   
   -- 在`tbl_name`写入一条测试数据
   INSERT INTO `tbl_name`(`name`, `age`, `create_time`) VALUES ('zhaosi', 21, '2022-06-02 22:12:36');
   
   -- 查看统计表，即可看到`count`列更新为`tbl_name`表的数据条目数
   SELECT * FROM `user_count`；
   
   

2. 删除触发器

   -- 语法格式
   DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
   
   -- 删除触发器
   DROP TRIGGER IF EXISTS update_user_count;