MySQL常用语句

MySQL实用指南
最新推荐文章于 2025-09-09 20:40:49 发布
最新推荐文章于 2025-09-09 20:40:49 发布
阅读量52 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： 数据库 json
原文链接：http://www.cnblogs.com/waterystone/p/5085825.html
本文提供MySQL数据库的实用指导，包括编程规范、用户管理、表结构创建与维护、数据操作及索引管理等内容，帮助读者掌握MySQL的基本操作技巧。

一. 规范

  • 编程时一般关键字大写，其他小写；
  • 尽量少用join查询；
  • 选择小而简单的数据类型，尽量避免NULL(使得索引复杂，可用DEFAULT）；
  • 除非有非常特别的原因需要其他存储引擎，否则应该优先考虑InnoDB引擎；
  • 尽量使用整型定义主键。使用InnoDB应该尽可能按主键顺序插入数据，并尽可能使用单调增加的聚簇键。

二. 库相关

1.1 MySQL

1.1.1 版本

SELECT VERSION();

　　

2.1 用户

2.1.1 创建

CREATE USER 'test'@'%' IDENTIFIED BY '123456';

如果新用户权限拒绝，编辑/etc/my.cnf：

[mysqld]
skip-grant-tables

 

2.1.2 授权

grant all privileges on *.* to test@"%" identified by '1234';

 

2.1.3 删除

Drop USER test;

　　

　　

2.1.4 登陆

mysql -hlocalhost -P3306 -uroot -p123456
mysql -hlocalhost -P3306 -uroot -p123456 -DmyDB
mysql -hlocalhost -P3306 -uroot -p123456 -DmyDB -e "select * from user" > /home/work/data/result.xls

 

三. 表结构

3.1 查看创建完整结构

DESC my_table;
SHOW CREATE TABLE my_table;
SHOW COLUMNS FROM my_table LIKE "my_field";

USE information_schema;
SELECT column_name FROM columns WHERE table_name='my_table'; 
SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_COMMENT FROM information_schema. COLUMNS WHERE table_schema = 'my_db_name' AND table_name = 'my_table_name';
SELECT * FROM information_schema. COLUMNS WHERE table_schema = 'my_db_name' AND table_name = 'my_table_name' \G;

　

　　

3.2 重命名

RENAME TABLE my_table TO my_table_new,other_table TO other_table_new;

　　

3.3 创建

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nid` bigint(20) NOT NULL references t_keywords(id),
  `ugc_name` varchar(20)  NOT NULL COMMENT '名称',
  `ugc_type` enum('one','two','three') NOT NULL DEFAULT 'one',
  `total_count` int(11) unsigned NOT NULL DEFAULT '0',
  `data` json,
  `create_date` date NOT NULL,
  `insert_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最近更新时间',
  `is_finished` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_keyword_id_is_finished` (`keyword_id`,`is_finished`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务配置表';

#复制表结构
CREATE TABLE my_table_new LIKE my_table;

#将select的结果存成表（保留原字段的属性设置，但不复制索引）
CREATE TABLE my_table_new ENGINE=INNODB AS SELECT * FROM my_table;

 

注：

3.3.1 字符集

3.3.2 排序规则 - COLLATION

一个字符集有一个或多种collation，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束。　

 

3.4 数据类型

类型名称占用空间可用默认值说明
tinyint1B-128~127小整数型
smallint2B-32768~32767大整数型
mediumint3B-8388608~8388607大整数型
int/integer4B-2147483648~2147483647大整数型
bigint8B-9233372036854775808~9223372036854775807极大整数型
    
float4B-3.402823466E+38~1.175494351E-38单精度浮点数型
double8B1.7976931348623157E+308~2.2250738585072014E-308双精度浮点数型
decimal(m,d)  对小数需要精确计算时用此类型，但在mysql处理时会转为double，所以能不用此类型尽量不用。
    
char  

定长字符串，一个字符长度为1，但根据字符集一个字符可能占多个字节 （适用于定长或较短的串）

注：在多字节字符编码中，innodb将char视为非定长，按varchar存储

varchar

0~65535B

Latin1(1B/字符)：0~65532字符

utf-8(1~3B/字符)：21845字符

gbk(2B/字符)：32767字符

 

变长字符串 。适用于长度变化大且更新少（压缩存储，若经常变大，则可能需要分裂页的操作）。存储时，按实际存入数据存储。并有额外字段标记其长度，以便定位。

注：所有varchar列总长度之和不能超过65536B

tinyblob/tinytext0~255字符 小型长度
blob/text0~65535字符 正常长度
mediumblob/mediumtext0~16777215字符 中等长度
longblob/longtext0-4294967295字符 极大长度
json 受限于max_allowed_packet  需要5.7+版本
    
timestamp4BCURRENT_TIMESTAMP,1970~2038保存1970-01-01 00:00:00以来的秒数，同unix时间戳。
datetime8B1001~9999精度为秒


四. 字段操作

4.1 增

ALTER TABLE my_table ADD COLUMN status tinyint(4) NOT NULL DEFAULT '0';
ALTER TABLE my_table ADD COLUMN my_field VARCHAR(30) NOT NULL DEFAULT '' AFTER id;
ALTER TABLE my_table ADD COLUMN age int(11) COMMENT '年龄' ;

 

4.2 删

alert TABLE my_table DROP COLUMN my_field;

 

4.3 改

ALTER TABLE my_table CHANGE my_field my_field_new VARCHAR (50);
ALTER TABLE my_table MODIFY COLUMN my_field INT(11) NOT NULL DEFAULT '0';
ALTER TABLE my_table ALTER COLUMN my_field SET DEFAULT 5;

 

4.3.1 ALTER　　

ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
设置或删除列的默认值。该操作会直接修改.frm文件而不涉及表数据。所以，这个操作非常快。

alter table film alter column rental_duration set default 5;
alter table film alter column rental_duration drop default;

 

4.3.2 CHANGE


CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
列的重命名、列类型的变更以及列位置的移动

ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL FIRST;
ALTER TABLE MyTable CHANGE COLUMN foo bar VARCHAR(32) NOT NULL AFTER baz;

 

 

4.3.3 MODIFY

MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
除了列的重命名之外，他干的活和CHANGE COLUMN是一样的

ALTER TABLE MyTable MODIFY COLUMN foo VARCHAR(32) NOT NULL AFTER baz;

　　

4.3.4 总结

  • change可以更改字段名，而modify/alter不能；
  • 所有的MODIFY COLUMN/CHANGE COLUMN操作都将导致表重建；而ALTER COLUMN操作会修改.frm文件而不涉及表数据。


4.4 调整

ALTER TABLE my_table CHANGE my_field my_field VARCHAR(30) NOT NULL DEFAULT ''AFTER id;

使用change，保持原属性。

五. 基本操作

5.1 增

INSERT INTO my_table (id, keyword) VALUES (1, 'keyword');
INSERT INTO my_table (field1,field2) SELECT field1,field2 FROM other_table;

//批量插入时有字节限制，max_allowed_packet，一般为1MB
INSERT LOW_PRIORITY INTO t_keywords (id, keyword) VALUES (1, 'a'), (2, 'b')
INSERT IGNORE INTO my_table (a,b,c) VALUES (1,2,3),(4,5,6);
REPLACE INTO my_table (keyword, insert_time) VALUES ('a1', now())//删除冲突的旧记录，增加新记录

　

5.2 删

DELETE FROM my_table WHERE id = 1;
DELETE FROM my_table WHERE create_time < date_sub(now(), INTERVAL 3 MONTH);

DELETE FROM t_keywords;
TRUNCATE t_keywords;//删除所有数据，上边的逐行删除，下边的更高效，而且自增键会重置。

　　

5.3 改

UPDATE t_keywords SET keyword = 'test' WHERE id = 5
UPDATE t_keywords SET keyword = 'test',name="du" WHERE id = 5

INSERT INTO my_table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE b=2,c=3;
INSERT INTO my_table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=c+VALUES(c);

　　

5.4 查

SELECT id, keyword FROM t_keywords WHERE id = 5 AND count > 10 LIMIT 3, 5
SELECT id, keyword FROM t_keywords WHERE id BETWEEN 1 AND 5
SELECT DISTINCT id FROM t_keywords
SELECT DISTINCT id FROM t_keywords ORDER BY id, count DESC
SELECT SQL_NO_CACHE/SQL_CACHE * FROM t_keywords WHERE id = 5;//明确是否缓存
SELECT * FROM my_table WHERE id >=(SELECT floor(RAND() * (SELECT MAX(id) FROM my_table)) ) ORDER BY id LIMIT 5;  //随机取值
SELECT * FROM my_table WHERE id >=(SELECT floor(RAND() * ((SELECT MAX(id) FROM my_table)-(SELECT MIN(id) FROM my_table)) + (SELECT MIN(id) FROM my_table))) ORDER BY id LIMIT 5;  //随机取值

　　

5.4.1 where子句

  1. 逻辑操作符：and、or   (and优先级高于or)
  2. 关系操作符：=、!=、<>、<、<=、>、>=、BETWEEN、is、not、in
  3. 通配符(与like搭配使用)：%(任意字符任意次)、_(任意字符单次)


六. 索引

6.1 查看

SHOW INDEX FROM my_table;

　　

6.2 创建单个索引

ALTER TABLE t_keywords ADD INDEX idx_keyword (keyword);
CREATE INDEX idx_keyword ON t_keywords (keyword);

　　

6.3 创建联合索引

ALTER TABLE t_keywords ADD INDEX idx_keyword_time (keyword,time);

 

6.4 创建前缀索引

ALTER TABLE t_keywords ADD INDEX idx_keyword (keyword(5));

 

6.5 扩展索引

ALTER TABLE t_keywords DROP INDEX idx_keyword ADD INDEX idx_keyword_create_time (keyword, create_time);

　　

6.6 删除

ALTER TABLE t_keywords DROP INDEX idx_keyword;

　　

七. 外键

7.1 创建

ALTER TABLE my_table ADD CONSTRAINT fk_userId FOREIGN KEY (my_user_id) REFERENCES other_table (other_user_id);

　　

7.2 删除

ALTER TABLE my_table DROP FOREIGN KEY fk_name;

　

八、存储引擎

8.1 修改

ALTER TABLE my_table ENGINE=INNODB;

　　

 

转载于:https://www.cnblogs.com/waterystone/p/5085825.html

mysql常用语句
10-25
mySQL常用语句，包括增、删、改、查。以及一些比较偏僻的方法
MySQL常用语句大全.pdf
03-15
### MySQL常用语句详解 #### 一、数据库管理 **1. 显示数据库列表** - **语法**: `SHOW DATABASES;` - **说明**: 此命令用于列出MySQL服务器上所有的数据库。 - **示例**: 执行`SHOW DATABASES;`将会显示所有可用...
MySQL常用语句大全
热爱编程、热爱生活、热爱探索
08-03 5万+
本文主要记录了常用的SQL语句，欢迎收藏，如果觉得对你有帮助就动动你的小手指给我一个赞👍，你的支持将是我持续输出的动力
最全的mysql常用语句大全
热门推荐
吴名氏的博客
10-08 15万+
最全的mysql常用语句大全
mysql常用语句汇总
weixin_42471170的博客
06-25 2279
1.连接数据库： 连接本地数据库： mysql -u root -p 连接远程数据库： mysql -h 192.169.22.199 -u root -p 退出数据库： exit 2.创建数据库： create database payment; 使用 mysqladmin 创建数据库 mysqladmin -u root -p create abc_db; 3.显示所有数据库： show databases; 4.删除数据库： drop database payment; 使用 mysqladmin
MySql常用语句汇总
m0_51516106的博客
06-15 7320
讲解了，MySQL中常用的语法，并且包含了使用实例。
【MySQL】常用语句
AAADiao的博客
02-04 2473
MySQL 常用语句主要围绕数据库、表和数据操作展开，能满足基本的数据库管理需求。本文对其进行分类总结。掌握这些MySQL语句后，可覆盖MySQL日常开发与管理的90%场景。进阶学习可探索存储过程、触发器、视图等高级功能。
MySQL常用语句大全：从入门到精通（附实战示例）
weixin_69555518的博客
05-09 1253
无论是开发、数据分析还是运维，MySQL作为最流行的开源关系型数据库，掌握其核心SQL语句是每个技术人员的必备技能。本文精心整理了MySQL高频使用语句，涵盖数据查询、表结构管理、性能优化等场景，配合实战代码示例，助你快速成为MySQL高手！
MySQL常用语句（二）
Palpitate_LL的博客
10-11 1460
创建一个名为tasks的新表来练习insert语句：例如将tasks表复制到tasks_bak表：首先通过复制tasks表的结构，创建一个名为tasks_bak的新表。
MySQL 查询语句大全
qq_57570052的博客
08-08 10万+
超全MySQL查询语句，MySQL查询语句有这一篇就够了
MySQL 常用 SQL 语句大全
admin_Object的博客
08-15 5972
这些 SQL 语句和技巧覆盖了数据库操作的广泛领域，从基础查询到高级管理和优化。掌握这些可以帮助你更高效地管理和操作 MySQL 数据库。使用 GROUP_CONCAT 函数。删除表中的所有记录(也叫截断表)内连接（INNER JOIN）右连接（RIGHT JOIN）基于其他表的更新(关联更新)左连接（LEFT JOIN）全连接（FULL JOIN）在 WHERE 子句中。计算字段的平均值和总和。在 FROM 子句中。查看当前的数据库状态。
MySQL 常用语句汇总
02-27
本内容根据MySQL的特点和应用实际，汇总了一些常用的MySQL语句，涵盖了从基础的数据库操作到数据表的创建与管理，再到数据的增删改查等常见操作。详细内容包括以下几个方面： 首先，在数据库操作方面，基础的创建、...
MySQL常用语句指令和功能详解.rar
04-29
本资料包“MySQL常用语句指令和功能详解.rar”包含了一份关于MySQL基础操作和高级特性的详细文本文件——“MySQL常用语句指令和功能详解.txt”。下面，我们将深入探讨MySQL的一些关键知识点。 1. 数据库创建与删除...
MySQL常用语句查询
10-16
MySQL 常用语句查询 MySQL 是一种关系数据库管理系统（RDBMS），它可以帮助我们存储、管理和检索数据。本文将对 MySQL 中的一些常用语句进行查询和介绍。 登录 MySQL 要登录 MySQL，我们可以使用以下命令： `...
从Java全栈开发到微服务架构的实战经验分享
CatchLight的博客
09-09 387
应聘者：我认为最重要的是能够理解业务需求，并将其转化为技术方案。同时，持续学习新技术，保持对行业趋势的关注也很关键。毕竟，技术更新很快，只有不断学习才能跟上节奏。通过这次面试，我们可以看到一位资深Java全栈开发者的专业能力和实践经验。他不仅掌握了多种技术栈，还能在实际业务场景中灵活运用，解决实际问题。同时，他也展现了良好的沟通能力和学习能力，这些都是成为一名优秀工程师的关键素质。
怎么快速构建一个deep search模型呢
qq_57565004的博客
09-05 896
本文提出了一种快速构建具备深度信息检索能力的智能体系统（DeepSearch）的方法。该方法采用分阶段开发策略：首先基于RAG架构搭建基础检索链路，再通过LangChain框架整合大语言模型和搜索引擎API，赋予系统自主规划与迭代搜索能力，最终实现多源信息整合和结构化报告生成。技术选型上强调利用开源工具（如LangChain、ChromaDB）和预训练模型，通过"思考-检索-整合-迭代"的智能体工作流，高效构建可验证原型的核心功能。
今天继续昨天的正则表达式进行学习
最新发布
Lynnxiaowen的博客
09-09 447
egrepawk，注意：使用grep要配合-E或者-P使用，sed要配合-r使用。go?dgdgod()g(oo)+doogoodgoooodg(oo|la)dgoodo{number}表示含有number个o的内容显示，o{number，}表示大于等于number的o的内容显示，o{2,4}表示大于等于2小于等于4个o的内容显示\W\d\D[^0-9]\s\S。
【Linux】MySQL数据目录迁移步骤(含流程图&踩坑经验)
Billy_hxp的博客
09-08 493
本文记录了作者在解决生产环境MySQL服务器磁盘空间告急问题的全过程。通过分析发现MySQL数据目录占用了38GB空间，而/home分区仍有空闲空间。作者详细介绍了迁移MySQL数据目录到/home分区的步骤，包括停服、复制数据、修改配置、更新权限、处理SELinux等关键操作，并分享了迁移过程中遇到的权限问题、SELinux拒绝访问等踩坑经验。最后总结了生产环境操作需要保留回退方案、注意权限和SELinux问题等经验教训，并提供了完整的命令速查表。整个迁移过程体现了处理生产问题的三个关键能力：快速定位根因
MySQL常用语句与命令详解
"MySQL常用语句和命令，包括特殊字符处理、数据库与表名的大小写规则、变量使用、十六进制操作以及各种符号的含义，此外还涉及数字类型和函数的运用。" 在MySQL中，当数据库或表名包含特殊字符时，可以使用反引号...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值