• 后台开发常用mysql语句_v1.0


    一、基本信息查看

    1. 表描述

    DESCRIBE `table_name`;
    

    二、表操作

    1. 查看建表语句

    SHOW CREATE TABLE `table_name`;
    

    2.查看表

    SHOW TABLES;
    

    3. 创建表

    CREATE TABLE `table_name` (
      `c_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '唯一ID',
      `c_is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
      `c_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
      `c_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`c_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='table_name'
    
    CREATE TABLE IF NOT EXISTS `table_name` (
      `c_id` bigint(20) unsigned NOT NULL COMMENT '唯一ID',
      `c_uid` char(11) NOT NULL DEFAULT '' COMMENT 'uid列',
      `c_char` char(11) NOT NULL DEFAULT '' COMMENT 'char列',
      `c_varchar1` varchar(30) NOT NULL DEFAULT '' COMMENT 'varchar列',
      `c_varchar2` varchar(300) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT 'varchar列,单独设置字符集',
      `c_text` text CHARACTER SET utf8mb4 NOT NULL COMMENT 'text列,单独设置字符集',
      `c_tinyint` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'tinyint,当枚举用(0A 1B 2C)',
      `c_enum` enum('0','1') NOT NULL DEFAULT '0' COMMENT 'enum,枚举用',
      `c_json` json NOT NULL COMMENT 'json类型列',
      `c_is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
      `c_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
      `c_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      PRIMARY KEY (`c_id`),
      KEY `inx_c_uid` (`c_uid`) USING BTREE,
      UNIQUE KEY `udx_c1_c2` (`c_char`,`c_tinyint`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='table_name';
    

    4. 更新表

    • 增加列
    ALTER TABLE `table_name` ADD `c_column` tinyint(1) not null DEFAULT 0 COMMENT '新增列';
    
    • 修改列
    ALTER TABLE `table_name` MODIFY COLUMN `c_varchar1` VARCHAR ( 300 ) NOT NULL DEFAULT '' COMMENT '标识符' AFTER `c_varchar2`;
    
    • 删除列
    ALTER TABLE `table_name` DROP COLUMN `c_column`;
    

    5. 删除表

    DROP TABLE if EXISTS `table_name`;
    

    6. 重命名表

    RENAME TABLE `table_name` TO `table_name_2`;
    

    三、索引操作

    1. 查看索引

    SHOW INDEX FROM table_name [FROM db_name]
    

    2. 创建索引

    • 语法
    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON table_name (index_col_name, ...)
    
    CREATE UNIQUE INDEX idx_uid ON `table_name` (`c_uid`);
    
    -- TEXT和BLOB要指定长度
    CREATE UNIQUE INDEX idx_text ON `table_name` (`c_text`(6));
    
    • ALTER TABLE方式
    ALTER TABLE table_name
    ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]
    
    ALTER TABLE `table_name` ADD PRIMARY KEY ( `c_uid` )
    
    ALTER TABLE `table_name` ADD INDEX `udx_c1_c2` ( `c_char`(n), `c_tinyint` ) USING BTREE;
    

    3. 修改索引

    ALTER TABLE `table_name` DROP INDEX `udx_c1_c2`,
    ADD INDEX `udx_c1_c2` ( `c_char`, `c_tinyint`, `c_is_deleted` ) USING HASH;
    

    4. 删除索引

    ALTER TABLE table_name DROP PRIMARY KEY;
    
    DROP INDEX `idx_name` ON `table_name`;
    
    ALTER TABLE `table_name` DROP INDEX `udx_c1_c2`;
    

    四、记录操作

    1. 查询记录

    • 简单查询

    子句顺序:SELETCFROMWHEREGROUP BYHAVINGORDER BYLIMIT

    SELECT * FROM table_name WHERE ... ORDER BY c_column1,c_column2 DESC;
    
    • 模糊查询
      特殊字符要转义% _
    SELECT * FROM table_name WHERE c_column LIKE CONCAT('first','second','third');
    
    SELECT * FROM table_name WHERE c_column LIKE CONCAT('%','_','%');
    
    • 分组查询

      WHERE过滤行, HAVING过滤分组

    SELECT GROUP_CONCAT(c_column) FROM table_name WHERE c_is_deleted=0 GROUP BY c_column1, c_column2 HAVING COUNT(c_column3) >= 2;
    
    -- 可能报错:incompatible with sql_mode=only_full_group_by
    SELECT GROUP_CONCAT(c_column) FROM table_name WHERE c_is_deleted=0 GROUP BY c_column1, c_column2 HAVING COUNT(c_column3) >= 2 ORDER BY c_order DESC;
    
    SELECT GROUP_CONCAT(c_id),COUNT(*) FROM table_name GROUP BY c_type WITH ROLLUP;
    
    
    • 分页查询

      检索出来的第一行为行0而不是行1。

    -- 查询两条记录,开始行1
    SELECT * FROM table_name LIMIT 1, 2;
    
    • 计算字段和函数

    (1)计算字段

    SELECT 1+1 AS result;
    
    SELECT CONCAT('first', c_column, 'third') AS c_alias_name FROM table_name;
    
    SELECT CONCAT('first', TRIM(c_column), 'third') AS c_alias_name FROM table_name;
    

    (2)处理函数

    SELECT UUID() AS `uid`;
    
    SELECT UPPER('a');
    
    SELECT DATE(c_create_time) AS `date`, TIME(c_create_time) AS `time` FROM table_name;
    

    (3)聚集函数

    SELECT COUNT( * ),
    	     MIN( c_column ),
    	     MAX( c_column ),
    	     AVG( c_column ) 
    FROM table_name;
    
    SELECT SUM( c_column1 * c_column2 ) 
    FROM table_name;
    
    • 子查询
    SELECT * 
    FROM table_name1 
    WHERE c_column IN ( SELECT c_column 
                        FROM table_name2
                        WHERE c_is_deleted = 1 );
                        
    -- 计算字段作为子查询
    SELECT table_name1.c_name,
    	( SELECT COUNT( * ) 
    	  FROM table_name2 
    	  WHERE table_name2.c_key = table_name1.c_key ) AS nums
    FROM table_name1 
    WHERE c_is_deleted = 0;
    
    • 联结查询

    (1) 自联结

    SELECT t1.c_id, t1.c_name 
    FROM table_name AS t1, table_name AS t2 
    WHERE t1.c_id = t2.c_id 
    AND t2.c_key = '';
    

    (2) 自然联结

    SELECT t1.c_id, t1.c_name 
    FROM table_name1 AS t1, table_name2 AS t2 
    WHERE t1.c_id = t2.c_id 
    AND t2.c_key = '';
    

    (3) 外部联结

    SELECT
    	table_name1.c_id,
    	table_name1.c_name,
    	table_name2.c_name 
    FROM table_name1
    LEFT JOIN table_name2 ON table_name2.c_key = table_name1.c_key
    WHERE ...;
    
    • 查询结果case when then else end用法

    (1) 语法

    --简单Case函数 
    CASE sex 
        WHEN '1' THEN '男' 
        WHEN '2' THEN '女' 
        ELSE '其他' 
    END 
    
    --Case搜索函数, 
    CASE 
        WHEN sex = '1' THEN '男' 
        WHEN sex = '2' THEN '女' 
        ELSE '其他' 
    END 
    

    (2) 示例

    SELECT `name`,
    CASE `name` 
    	WHEN 'sam' THEN 'yong' 
    	WHEN 'lee' THEN 'handsome' 
    	ELSE 'good' 
    END AS `alias` 
    FROM `table_name`;
    
    SELECT `name`,
    CASE
    	WHEN birthday < '1981' THEN 'old' 
    	WHEN birthday > '1988' THEN 'yong' 
    	ELSE 'ok' 
    END AS `yorn` 
    FROM `table_name`;
    

    2. 添加记录

    • 简单插入
    INSERT INTO table_name ( column1, column2 ) VALUES ( value1, value2 );
    
    • 批量插入
    INSERT INTO table_name 
        ( column1, column2 )
    VALUES
    	( value1, value2 ),
    	( value1, value2 );
    
    
    • INSERT SELECT插入
    INSERT INTO table_name ( column1, column2 )
        SELECT column1, column2 
        FROM table_name 
        WHERE c_id=1;
    

    3. 更新记录

    UPDATE `table_name` 
    SET 
        `c_varchar1` = 'string',
        `c_update_time` = CURRENT_TIMESTAMP 
    WHERE `c_id` = 1;
    

    4. 删除记录

    • DELETE方式
    DELETE FROM `table_name` WHERE `c_id`=1;
    
    • TRUNCATE方式-清空表-慎用
     -- 删除原表再新建表
     TRUNCATE TABLE `table_name`
    
    走在同样的路上,遇见不一样的风景
  • 相关阅读:
    浅谈移动通信和无线通信
    jdk8处理时间
    Mysql查询报错:Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
    linux系统执行mysql脚本:Can't connect to local MySQL server through socket '/tmp/mysql.sock'
    org.springframework.boot.web.server.WebServerException: Unable to create tempDir. java.io.tmpdir is set to C:UsersADMINI~1AppDataLocalTemp2
    java实现每个单词首字母大写
    常用Java技术社区
    Hibernate处理事务并发问题
    Hibernate的检索策略
    Java对象在Hibernate持久化层的状态
  • 原文地址:https://www.cnblogs.com/flylinran/p/10171508.html
Copyright © 2020-2023  润新知