mysql语句开发_后台开发常用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. 查询记录

简单查询

子句顺序：SELETC、 FROM、 WHERE、 GROUP BY、 HAVING、 ORDER BY、 LIMIT

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`