架构视界
主页
企业架构
  • 方案总览
  • 业财一体
  • 企业中台
  • 组织与绩效
  • 流程治理
数据收集与分析
  • 转型总览
  • 信息化阶段
  • 数字化解决方案
  • 智数化解决方案
  • 智能化解决方案
  • 产品设计
  • 项目管理
全栈软件工程
AI 原生软件工程
方法论
博客
  • 资源导航
  • 效率工具
  • 展示专区
主页
企业架构
  • 方案总览
  • 业财一体
  • 企业中台
  • 组织与绩效
  • 流程治理
数据收集与分析
  • 转型总览
  • 信息化阶段
  • 数字化解决方案
  • 智数化解决方案
  • 智能化解决方案
  • 产品设计
  • 项目管理
全栈软件工程
AI 原生软件工程
方法论
博客
  • 资源导航
  • 效率工具
  • 展示专区
  • 标签索引
  • 2024年

    • 勇闯大A:美债与美元周期
    • 勇闯大A:交易纪律与自我管理
    • 勇闯大A:交易系统分层
    • 勇闯大A:买卖点与止损规则
    • 勇闯大A:债券与资金流向
    • 勇闯大A:微观、宏观与周期
    • Mac 开发环境:新机配置清单
    • 勇闯大A:内外盘与换手率
    • 会计管理:财报阅读与管理报表
    • 会计基础:记账原理与分录
    • 财务入门:财务体系全景
  • 2023年

    • 境外投资:泰国 BOI 申请实务
  • 2022年

    • 企业架构:故障管理与分级响应
    • 企业架构:技术债务的识别、度量与偿还
  • 2021年

    • Python 数据分析:员工分布可视化
    • Python 数据分析:IP 访问折线图
    • Python 数据分析:IP 词云图
    • Redis:RediSearch 全文检索
    • Rust 基础:第一个 CLI 程序
    • Rust 基础:所有权机制
    • Rust 基础:函数与控制流
    • Rust 基础:变量与数据类型
    • Rust 基础:入门与开发环境
    • 企业架构:分布式系统设计
    • 企业架构:编程式权限设计
    • JVM 调优:启动参数与 GC 日志
    • MyBatis 批量插入:三种写法对比
    • 测试:Spring Boot 分层 Mock
    • Redis:布隆过滤器判断元素存在
    • Jenkins:安装配置与应用部署
    • WebClient:非阻塞 HTTP 客户端
    • Drone:Docker 部署轻量 CI
    • Spring AOP:操作审计日志落库
    • Redisson:分布式锁与 RedLock
    • MyBatis 拦截器:雪花算法主键
    • Netty:线程模型与网络编程
    • Redis:SCAN 游标遍历大键空间
    • Jenkins:单机部署与升级脚本
    • Mac:Neo4j 与 Jupyter 安装
    • JavaFX:OpenJDK 11 环境配置
    • Mac:用 jenv 管理 JDK 版本
    • Redis:有序集合实现延时队列
    • Spring Boot 日志:MDC 串联链路
    • 定时任务:Spring 调度与 Cron
    • GoAccess:Nginx 日志分析
    • MyBatis 拦截器:慢 SQL 分析
    • Lucene:倒排索引与中文分词
    • 分布式锁:三种实现方案对比
    • 学习技巧:减少学习排斥心理
    • 动态数据源:多租户数据路由
    • Compose:Spring Boot 三件套
    • Java 阻塞队列:源码与用法
    • Redis:Docker Compose 哨兵
    • Redis:Docker Compose 主从
    • 网络通信:TCP/IP 与 HTTP 基础
  • 2020年

    • 延时队列:DelayQueue 与 Redis 实现
    • MySQL 基础:员工库导入实践
    • 雪花算法:分布式 ID 生成
    • Redis:地理位置查询
    • 抓包:Wireshark 与 Tcpdump
    • SPI:JDK 与 Spring 扩展机制
    • ZooKeeper:概念与集群搭建
    • Spring Boot:集成 JMH 基准测试
    • Wireshark:抓包工具入门
    • Compose:Redis 与 MySQL
    • CentOS 7:Docker 安装部署
    • Netty:源码阅读环境搭建
    • MySQL 基础:SQL 常见用法
    • CentOS 7:Selenium 运行环境
    • Nginx:HTTPS 证书配置
    • JMH:基准测试与 Spring Boot 集成
    • Spring Boot:指定 Tomcat 版本
    • Eureka:实例上下线告警
    • 钉钉告警:应用异常与实例上下线
    • JVM:内存布局、类加载与调优
    • Git:rebase 合并多次提交
    • CentOS 7:OpenResty 部署
  • 2019年

    • Redis CLI:macOS 安装与使用
    • Nginx:访问日志格式
    • 工程规范:可维护代码的写法
    • IntelliJ IDEA:常用插件
    • SkyWalking:源码阅读环境搭建
    • Spring Boot:Redis 超时配置
    • gRPC:ProtoBuf 定义与代码生成
    • Arthas:Java 线上问题诊断
    • Redis:缓存穿透与击穿防护
    • Docker 部署:Elasticsearch
    • Docker 部署:MySQL 5.7 单机
    • Spring Redis:字符串命令封装
    • Docker 部署:ZooKeeper 运行
    • Docker 部署:Redis 单机运行
    • Dubbo:Spring Boot 微服务集成
    • CentOS 7:CMake 源码编译安装
    • 性能度量:吞吐量与 QPS 指标
    • Java 递归:从笔试题到经典问题
    • CentOS 7:ELK 日志平台部署
    • CentOS 7:SonarQube 部署
    • Selenium:Chrome 浏览器自动化
    • JJWT:令牌生成与解析测试
    • Spring 源码:阅读环境搭建
    • JUnit 4:注解、断言与参数化
    • JWT:令牌结构与签名机制
    • FastDFS:架构与文件存取机制
    • XPath:XML 文档节点定位
    • Redis 基础:锁、队列与事务
    • Redis 基础:数据类型与命令
    • MyBatis:Slf4j 日志接入
    • MyBatis:H2 单元测试与 SQL 日志
    • 源码阅读:开源项目环境搭建
    • Git 配置:命令清单与作用域
    • Java 核心:面向对象与基础类型
    • Java 核心:关键字、异常与反射
    • Java 核心:多线程基础
    • Java 核心:同步关键字与线程池
    • Java 核心:锁与并发工具
    • Java 核心:语言基础与并发
    • Dubbo:远程调用与服务治理
    • Java 集合:框架总览与实现对比
    • Maven:生命周期与常用命令
    • MyBatis:执行流程与插件机制
    • Spring:IoC 容器与依赖注入
    • Spring:AOP 与自动装配
    • Spring:事务与启动流程
    • Spring:IoC、AOP 与事务
    • Spring MVC:请求分发与注解
    • MySQL:架构与索引
    • MySQL:事务、锁与 MVCC
    • MySQL:日志与 SQL 优化
    • MySQL:架构、事务与优化
    • Redis 基础:持久化与线程模型
  • 2018年

    • Java HashMap:扩容机制与哈希冲突
    • Java 集合:HashSet 去重原理
    • Java 基础:交换两个变量的值
    • Spring MVC:零 web.xml 改造
    • Mac 开发环境:新机配置清单
    • Log4j:日志分级与按业务拆分
    • Modbus:工业通信协议与 RTU 帧
    • MySQL 基础:子查询与连接查询
    • MySQL 基础:库与表操作
    • MySQL 基础:表操作与查询
    • MySQL 基础:约束、引擎与数据类型
    • MySQL 基础:库表、约束与类型
    • Java 集合:Vector 与 Stack
    • Java 集合:Vector 与 Stack
    • RabbitMQ:CentOS 7 单机部署
    • Redis:CentOS 7 单机部署
    • MongoDB:CentOS 7 单机部署
    • Linux:常用系统与运维命令
    • 注册中心:Eureka 与 ZooKeeper
    • MySQL:CentOS 7 单机部署
    • Git 分支:主分支与临时分支
    • CentOS 7:Java 开发环境部署
    • Java 集合:LinkedList 双向链表
    • Java 集合:ArrayList 动态数组
    • Spring AOP:注解方式实现切面
← 返回博客

MySQL 基础:表操作与查询

2018年4月29日 #数据与存储 #MySQL

系列文章:MySQL 基础:库表、约束与类型 的第 2 篇。

Mysql数据表操作

数据表,是数据库中重要的结构之一,是存储数据的基础。在关系型数据库中,实际上表就是一个二维的数据结构。

创建数据表

首先需要进入到某个数据库,上面的操作数据库中已经创建了一个库db1。

# 进入db1数据库
[root@localhost][(none)]> USE db1;
Database changed
[root@localhost][db1]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| db1        |
+------------+
1 row in set (0.00 sec)

进入后使用SELECT DATABASE(); 来查询当前进入的数据。

CRATE TABLE [IF NOT EXISTS] table_name(

column_name data_type

)

由于是二维表,所以column_name肯定是确认的,根据存储的数据类型在选择合适的data_type。

[root@localhost][db1]> CREATE TABLE emp(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8,2) UNSIGNED
    -> );
Query OK, 0 rows affected (0.12 sec)

创建一张员工表,存储了名字、年龄、工资。其中年龄和工资采用了无符号。


查看数据表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

  • 查询当前数据库全部表:SHOW TABLES ;
  • 查询其他数据库中表:SHOW TABLES FROM mysql
  • 模糊查询当前数据库表:SHOW TABLES LIKE 'pattern'
    • 这里的pattern是指匹配模式,%是指匹配多个字符,_是指匹配单个字符。需要注意的是在匹配有带有_下划线的数据表名字时候,需要添加\反斜杠转义。
# 查询当前数据库中全部表。
[root@localhost][db1]> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| emp           |
+---------------+
1 row in set (0.00 sec)

# 查询其他数据库中全部表。
[root@localhost][db1]> SHOW TABLES FROM mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

# 使用 LIKE 做模糊查询
[root@localhost][db1]> SHOW TABLES LIKE 'e%'
    -> ;
+--------------------+
| Tables_in_db1 (e%) |
+--------------------+
| emp                |
+--------------------+
1 row in set (0.00 sec)

查看表结构

SHOW COLUMNS FROM table_name

[root@localhost][db1]> SHOW COLUMNS FROM emp;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改表结构

平时用IDE用多了,估计很少会去写SQL修改表结构。

修改表结构分为两种

  • 修改表本身
    • 修改表名 RENAME TBALE ol_table_name TO new_table_name
    • 修改表选项 ALTER TABLE table_name table_option [=] option
  • 修改表字段
    • 新增字段 ALTER TABLE table_name ADD [colums_name] data_type [data_type] [location]
    • 修改字段 ALTER TABLE table_name MODIFY [columns_name] [data_type] [location]
    • 重命名字段 ALTER TABLE table_name CHANGE ol_columns_name new_columns_name data_type [columns_type] [location]
    • 删除字段 ALTER TABLE table_name DROP columns_name

插入表数据(一)

INSERT [INTO] table_name [(columns_name , ... )] VALUES(value , ...)

# 插入一条模拟数据,默认全部字段都插入。也就没有指定columns的时候所有的字段都需要赋值。
[root@localhost][db1]> INSERT emp VALUE('Tom',23,8432.34);
Query OK, 1 row affected (0.01 sec)

# 插入一条数据,只为部分字段赋值。
[root@localhost][db1]> INSERT emp(username , salary) VALUE('Jon',5600.32);
Query OK, 1 row affected (0.01 sec)

实际上插入数据的时候是有约束的。

简单的查询:SELECT expr,.. FROM table_name

# 先做一个简单的演示,使用*标识所有的列。
[root@localhost][db1]> SELECT * FROM emp;
+----------+------+---------+
| username | age  | salary  |
+----------+------+---------+
| Tom      |   23 | 8432.34 |
| Jon      | NULL | 5600.32 |
+----------+------+---------+
2 rows in set (0.00 sec)

在插入Jon的时候并没有指定age,实际上一般的业务上会出现数据不能为空的情况。比如说刚才创建emp所有的字段都是可以为空值。

列约束:NULL \ NOT NULL

NULL ,字段值可以为空;NOT NULL字段值不可为空。

# 模拟一条数据,并指定age 为空。
[root@localhost][db1]> INSERT emp(username,age) VALUE('Zo',NULL);
Query OK, 1 row affected (0.00 sec)
# 看下存储的结果
[root@localhost][db1]> SELECT * FROM emp;
+----------+------+---------+
| username | age  | salary  |
+----------+------+---------+
| Tom      |   23 | 8432.34 |
| Jon      | NULL | 5600.32 |
| Zo       | NULL |    NULL |
+----------+------+---------+

在创建一张表,并指定部分字段不能为空。

# 创建表emp1 ,指定username 不能为空。
[root@localhost][db1]> CREATE TABLE emp1(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL
    -> );
Query OK, 0 rows affected (0.10 sec)
# 查看下emp1的数据表结构。
[root@localhost][db1]> SHOW COLUMNS FROM emp1;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

从emp2结构中看到,此时的username 已经不能为NULL了。

# 模拟数据,尝试吧NULL赋值到username。
[root@localhost][db1]> INSERT emp1(username,age) VALUE(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null

列约束:AUTO_INCREMENT

AUTO_INCREMENT 自增必须定义成主键使用。

# 如果列约束 AUTO_INCREMENT 不配合主键使用会出现下列警告。
[root@localhost][db1]> CREATE TABLE emp2(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 修改创建表语句,并指定主键
[root@localhost][db1]> CREATE TABLE emp2(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.09 sec)
# 检查下emp2表结构。
[root@localhost][db1]> SHOW COLUMNS FROM emp2;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
# 插入一条数据试试。
[root@localhost][db1]> INSERT emp2(username) VALUE('Jon');
Query OK, 1 row affected (0.01 sec)
# 查看结果
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
|  1 | Jon      |
+----+----------+
1 row in set (0.00 sec)
# 如果插入的时候指定id的值,看看会发生什么?
[root@localhost][db1]> INSERT emp2(id,username) VALUE(10,'Tom');
Query OK, 1 row affected (0.01 sec)
# 查看结果。
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
|  1 | Jon      |
| 10 | Tom      |
+----+----------+
2 rows in set (0.00 sec)
# 也就是说,如果我们指定插入的值,是可以插入成功的。在插入一条看看
[root@localhost][db1]> INSERT emp2(username) VALUE('King');
Query OK, 1 row affected (0.03 sec)
# 再查看
[root@localhost][db1]> SELECT * FROM emp2;
+----+----------+
| id | username |
+----+----------+
|  1 | Jon      |
| 10 | Tom      |
| 11 | King     |
+----+----------+
3 rows in set (0.00 sec)
#  结果可以看到,指定主键后,在此插入若不指定会在最大的id上进行自增。

列约束:PRIMARY KEY

PRIMARY KEY 主键约束,上面我们使用了AUTO_INCREMENT而它是必须配合PRIMARY KEY使用。但是PRIMARY KEY 可以单独使用。一张表也只能存在一个主键。

# 创建一个表,并指定PRIMARY KEY 但是不使用AUTO_INCRMENT。
[root@localhost][db1]> CREATE TABLE emp3( 
	id SMALLINT UNSIGNED PRIMARY KEY , 
	username VARCHAR(20)
);
Query OK, 0 rows affected (0.09 sec)

主键有一个特性就是保证唯一性,所以不可以重复。

# 检查下emp3表结构,可以看到NULL列是NO。
[root@localhost][db1]> SHOW COLUMNS FROM emp3;
+----------+----------------------+------+-----+---------+-------+
| Field    | Type                 | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    |       |
| username | varchar(20)          | YES  |     | NULL    |       |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# 我们来模拟一次主键重复的情况。
[root@localhost][db1]> INSERT emp3(id,username) VALUES(1,'Tom');
Query OK, 1 row affected (0.01 sec)
# 主键重复异常。
[root@localhost][db1]> INSERT emp3(id,username) VALUES(1,'Jon');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

列约束:UNIQUE KEY

UNIQUE KEY唯一约束,唯一的约束可以保证数据的唯一性,唯一约束的字段可以设置为NULL。每张表可以设置多个唯一约束,这个主键不同。

# 创建一张主键自增并且有唯一约束的表emp4。
[root@localhost][(none)]> CREATE TABLE emp4(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> age TINYINT UNSIGNED
    -> );
Query OK, 0 rows affected (0.09 sec)
# 查看下emp4表结构情况。
[root@localhost][Demo]> SHOW COLUMNS FROM emp4;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# 插入数据试试
[root@localhost][Demo]> INSERT emp4(username,age) VALUES('Tom',22);
Query OK, 1 row affected (0.01 sec)
# 重复插入Tom试试,这会提示错误。
[root@localhost][Demo]> INSERT emp4(username,age) VALUES('Tom',22);
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'

这里其实有一个疑问,为什么QNIQUE KYE可以为NULL,如果为NULL怎么表示唯一呢?

列约束:DEFAULT

DEFAULT 默认值,如果没有明确字段赋值,则自动赋值为默认值。

# 创建一张表,指定sex DEFAULT 值为 0,如果插入数据的时候不指定,那么这个字段就是0。
[root@localhost][Demo]> CREATE TABLE emp5(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE KEY,
    -> sex TINYINT DEFAULT 0
    -> );
Query OK, 0 rows affected (0.11 sec)

#检查表emp5结构
[root@localhost][Demo]> SHOW COLUMNS FROM emp5;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   | UNI | NULL    |                |
| sex      | tinyint(4)           | YES  |     | 0       |                |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# 插入模拟数据
[root@localhost][Demo]> INSERT emp5(username) VALUES('Tom');
Query OK, 1 row affected (0.02 sec)
[root@localhost][Demo]> INSERT emp5(username) VALUES('Jon');
Query OK, 1 row affected (0.01 sec)

# 查看数据,并没有指定sex的值,就会使用默认值。
[root@localhost][Demo]> SELECT * FROM emp5;
+----+----------+------+
| id | username | sex  |
+----+----------+------+
|  1 | Tom      |    0 |
|  2 | Jon      |    0 |
+----+----------+------+
2 rows in set (0.00 sec)

列约束:小结

列的属性,除了数据类型的约束,就是上面几种列约束。

  • 约束保证数据的完整性。
  • 约束分为表级约束和列级别约束。
  • 约束的类型包括
    • NOT NULL 非空约束
    • PRIMARY KEY 主键约束
    • UNIQUE KEY 唯一约束
    • DEFAULT 默认约束
    • FOREIGN KEY 外键约束

插入数据需要注意

在插入数据的时候也是存在一些需要注意的地方,下面演示下。

# 创建测试表 users
[root@localhost][Demo]> CREATE TABLE users(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20) UNIQUE KEY,
    -> pwd VARCHAR(30) NOT NULL,
    -> age TINYINT UNSIGNED NOT NULL,
    -> sex TINYINT UNSIGNED DEFAULT 0
    -> );
Query OK, 0 rows affected (0.07 sec)
#看下表的结构。
[root@localhost][Demo]> SHOW COLUMNS FROM users;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | smallint(6)         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)         | YES  | UNI | NULL    |                |
| pwd   | varchar(30)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | NO   |     | NULL    |                |
| sex   | tinyint(3) unsigned | YES  |     | 0       |                |
+-------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

# 插入数据模拟数据,不指定插入数据的位置。
[root@localhost][Demo]> INSERT users VALUES(NULL,'Tom','_23234awdasdwf',23,NULL);
Query OK, 1 row affected (0.03 sec)

# 再试一次,少插入一个位置的数据。如果省略了列名,就要需要将列数据补全,否则会造成插入失败的情况。
[root@localhost][Demo]> INSERT users VALUES(NULL,'Tom','_23234awdasdwf',23);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

插入时候使用DEFAULT关键字,同时可以使用表达式作为参数。

# 关键子DEFAULT也可以使用,sex字段就有DEFAULT,age 用到了表达式求值
[root@localhost][Demo]> INSERT users VALUES(NULL,'Rose','_23234awdasdwf', 2*3 + 11,DEFAULT);
Query OK, 1 row affected (0.00 sec)

# 一次性插入多条数据
[root@localhost][Demo]> INSERT users VALUES(NULL,'Atom','pwd',21,DEFAULT),(NULL,'Per','pwd',23,DEFAULT);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  17 | NULL |
|  4 | Rose | _23234awdasdwf |  17 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
+----+------+----------------+-----+------+

插入表数据(二)

INSERT [INTO] table_name SET columns_name ={ expr | DEFAULT},...

  • 与第一种插入方式不同的是,此方法可以使用子查询(SubQuery);而且这方式一次只能插入一条数据。
# 模拟一条测试数据。
[root@localhost][Demo]> INSERT users SET name = 'CoCo', pwd = 'pwd',age = 22;
Query OK, 1 row affected (0.01 sec)
# 查询结果。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  17 | NULL |
|  4 | Rose | _23234awdasdwf |  17 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  7 | CoCo | pwd            |  22 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)


插入表数据(三)

INSERT [INTO] tablle_name [(columns_name,...)] SELECT ...

  • 这种方式,可以将查询结果插入到指定的数据表中。
# 创建一张测试表。只有两个字段,id 和 name。
[root@localhost][Demo]> CREATE TABLE test(
    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(30) 
    -> );
Query OK, 0 rows affected (0.07 sec)

# 尝试将users表中的数据插入到test中。
# 先看看当前users表中的数据。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  18 |    1 |
|  3 | Jon  | _23234awdasdwf |  17 |    1 |
|  4 | Rose | _23234awdasdwf |  17 |    0 |
|  5 | Atom | pwd            |  16 |    0 |
|  6 | Per  | pwd            |  18 |    0 |
|  8 | CoCo | agc            |  17 |    1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 尝试将 age > 17 的name插入到test中。注意了,插入几个值和查询的值要匹配。这里提示2条成功
[root@localhost][Demo]> INSERT test(name) SELECT name FROM users WHERE age > 17;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

#检查结果,这里可以看到 users 表中 age > 17 的name值插入到test中。
[root@localhost][Demo]> SELECT * FROM test;
+----+------+
| id | name |
+----+------+
|  1 | Tom  |
|  2 | Per  |
+----+------+
2 rows in set (0.00 sec)



更新表数据(一)

UPDATE [LOW_PRIORITY] [IGNORE] tabel_refernce SET columns_name1 = { expre1 | DEFAULT} , columns_name2 = { expre2 | DEFAULT}] ... [WHERE where_condition]

  • 单表更新,这里需要注意的是,如果不添加where判断条件,会导致该表所有记录都被更新。
# 这里使用users表中的数据。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  17 | NULL |
|  4 | Rose | _23234awdasdwf |  17 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  7 | CoCo | pwd            |  22 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 这条SQL语句表示让这个表中所有的age 都 + 5;
[root@localhost][Demo]> UPDATE users SET age = age + 5;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6  Changed: 6  Warnings: 0

# 查看结果,已经全部更新。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  28 | NULL |
|  3 | Jon  | _23234awdasdwf |  22 | NULL |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  26 |    0 |
|  6 | Per  | pwd            |  28 |    0 |
|  7 | CoCo | pwd            |  27 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 添加where_condition。将age > 25 的记录 age - 5;
[root@localhost][Demo]> UPDATE users SET age = age - 5 WHERE age > 25;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

# 查看结果
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  22 | NULL |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  7 | CoCo | pwd            |  22 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)


更新表数据(二)

  • 多表更新: UPDATE table_reference SET columns_name = { expr1 | DEFAULT } [ columns_name2 = { expr2 | DEFAULT }]... [WHERE where_condition]
  • 但是呢多表更新实际上是需要使用要连接,这里不做过多的皆是,简单的演示一下。
# 创建一张user_names 表来存储用户的名字,其实没啥意义,就是演示下。
[root@localhost][Demo]> CREATE TABLE user_names(
    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(40) NOT NULL,
    -> uid TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.07 sec)

# 检查下表结构
[root@localhost][Demo]> SHOW COLUMNS FROM user_names;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40)         | NO   |     | NULL    |                |
| uid   | tinyint(3) unsigned | NO   |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

# 我们先吧用户的名字和主键都插入进来。
[root@localhost][Demo]> INSERT INTO user_names(uid,name) SELECT id , name FROM users ;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

[root@localhost][Demo]> SELECT * FROM user_names;
+----+------+-----+
| id | name | uid |
+----+------+-----+
|  1 | Atom |   5 |
|  2 | CoCo |   8 |
|  3 | Jon  |   3 |
|  4 | Per  |   6 |
|  5 | Rose |   4 |
|  6 | Tom  |   1 |
+----+------+-----+
6 rows in set (0.00 sec)

# 这个时候吧users表中某个用户的名字修改一次,在更新到user_names表中去。
[root@localhost][Demo]> UPDATE users SET name = 'Misa' WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 最后演示多表更新
[root@localhost][Demo]> UPDATE user_names AS un INNER JOIN users AS u ON un.uid = u.id SET un.name = u.name;
Query OK, 1 row affected (0.00 sec)
Rows matched: 6  Changed: 1  Warnings: 0

# 这里可以看到uid 为 1 的名字已经发生了变化。
[root@localhost][Demo]> SELECT * FROM user_names;
+----+------+-----+
| id | name | uid |
+----+------+-----+
|  1 | Atom |   5 |
|  2 | CoCo |   8 |
|  3 | Jon  |   3 |
|  4 | Per  |   6 |
|  5 | Rose |   4 |
|  6 | Misa |   1 |
+----+------+-----+
6 rows in set (0.00 sec)


删除表数据(一)

DELETE FROM table_name [WHERE where_condition]

  • 单条删除,如果省略where_condition将删除所有数据。
# 删除一条id为7的记录。
[root@localhost][Demo]> DELETE FROM users WHERE id = 7;
Query OK, 1 row affected (0.01 sec)

# 检查结果
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Misa | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  22 | NULL |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
+----+------+----------------+-----+------+
5 rows in set (0.00 sec)

这里需要注意的是,如果是自增类型主键,当被删除了最大的主键之后。再次插入数据并不会在现在的最大主键上自增。

# 在添加一条测试数据来验证下主键自增的规律。
[root@localhost][Demo]> INSERT users VALUES(NULL,'CoCo','agc',22,1);
Query OK, 1 row affected (0.05 sec)
# 结果发现直接从6到8,而7是刚才被删除的记录。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Misa | _23234awdasdwf |  23 | NULL |
|  3 | Jon  | _23234awdasdwf |  22 | NULL |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  8 | CoCo | agc            |  22 |    1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

删除表数据(二)

DELETE table_name[.*] [, table_name[.*]] ... FROM table_referneces [WHERE where_condition]

# 这里使用一张表进行删除。这里吧重复密码的账号进行删除,但是只保留id最小的那一个。实际上这个操作没啥用。
[root@localhost][Demo]> DELETE t1 FROM users AS t1 LEFT JOIN (SELECT id , pwd  FROM users GROUP BY pwd HAVING count(pwd) >= 2 ) AS t2 ON t1.pwd  = t2.pwd WHERE t1.id > t2.id;
Query OK, 3 rows affected (0.02 sec)

# 在检查一下。
[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Misa | _23234awdasdwf |  18 |    1 |
|  5 | Atom | pwd            |  16 |    0 |
|  8 | CoCo | agc            |  17 |    1 |
| 10 | Pet  | abc            |  22 |    1 |
+----+------+----------------+-----+------+


查询表数据(一)

在通常情况下,大部分的SQL都是查询语句,所以查询最后说明。

SELECT select_expr[ select_expre ...]

[

​ FROM table_reference

​ [WHERE where_condition]

​ [GROUP BY {columns_name | position}] [ASC | DESC]

​ [HAVING where_condition]

​ [ORDER BY {columns_name | expre | position}] [ASC | DESC]

​ [LIMIT {[offset] row_count | row_count OFFSET}]

]

查询表达式 SELECT_EXPR

  1. 每个表达式标识想要的一列,必须至少有一列;多列之间使用逗号分割。* 号表示全部,table_name.* 表示该命名表的所有列。
  2. 查询表达式可以使用[AS] alisa_name 赋予别名。同时别名可用与GROUP BY ,ORDER BY 或者HAVING子句。字段顺序、字段的别名会影响结果集。
# 依然使用users来做测试数据。
# 只查询users表中id 和 name;并且使用使用alisa_name。
[root@localhost][Demo]> SELECT u.id AS uid , u.name AS uname FROM users AS u;
+-----+-------+
| uid | uname |
+-----+-------+
|   5 | Atom  |
|   8 | CoCo  |
|   3 | Jon   |
|   6 | Per   |
|   4 | Rose  |
|   1 | Tom   |
+-----+-------+
6 rows in set (0.00 sec)

# 使用陷阱,如果使用alisa_name 而没有使用AS 关键就会导致下面的问题。可能是少写了一个逗号,而结果确实一个结果集。
[root@localhost][Demo]> SELECT id name FROM users;
+------+
| name |
+------+
|    5 |
|    8 |
|    3 |
|    6 |
|    4 |
|    1 |
+------+
6 rows in set (0.00 sec)

条件表达式 GROUP BY

GROUP BY {columns_name | position} [ASC | DESC],...

  • 如果没有指定WHERE子句,则显示全部数据。在WHERE表达式中,可以使用MYSQL内置函数或者是运算符。
  • ASC 是升序、DESC是降序,若存在多个分组条件那就用逗号分割。
# 依然使用users表中的测试数据,但是要将sex 为NULL的数据进行修改。
[root@localhost][Demo]> UPDATE users SET sex = 1 WHERE sex IS NULL;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

[root@localhost][Demo]> SELECT * FROM users;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  6 | Per  | pwd            |  23 |    0 |
|  8 | CoCo | agc            |  22 |    1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 使用GROUP BY 来做分组。这里分组的结果就是0和1。
[root@localhost][Demo]> SELECT sex FROM users GROUP BY sex;
+------+
| sex  |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.00 sec)

分组条件 HAVING

[HAVING where_condition]

  • 使用HAVING只对一部分数据来分组。需要注意的是,如果使用HAVING语句,那么在查询结果中就存在对应的字段。并切与函数进行配合。

这里需改修改一处配置信息,由于本人使用的是MYSQL5.7版本,所以GROUP BY和以往不一样。

# 检查当前sql_mode;
select @@sql_mode;

5.7中默认设置了sql_mode=only_full_group_by

修改下my.cof。在[mysqld]下面添加

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

就是不要only_full_group_by这个。

简单解释下only_full_group_by是干嘛的。

使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行

# 回到 HAVING分组条件上。
[root@localhost][Demo]> SELECT sex , age FROM users GROUP BY sex HAVING age > 21;
+------+-----+
| sex  | age |
+------+-----+
|    0 |  22 |
|    1 |  23 |
+------+-----+
2 rows in set (0.00 sec)

结果排序 ORDER BY

ORDER BY {columns_name | expr | position} [ASC | DESC],...

# 使用ORDER BY 来做排序 DESC ,由于默认是ASC升序;所以这里用DESC降序来演示。
[root@localhost][Demo]> SELECT * FROM users ORDER BY id DESC;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  8 | CoCo | agc            |  22 |    1 |
|  6 | Per  | pwd            |  23 |    0 |
|  5 | Atom | pwd            |  21 |    0 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 对多个字段进行排序。这里可以看到虽然CoCo 和 Roes 都是22age 但是id采用了降序。所以CoCo排在前面。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age , id DESC;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  5 | Atom | pwd            |  21 |    0 |
|  8 | CoCo | agc            |  22 |    1 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  6 | Per  | pwd            |  23 |    0 |
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
+----+------+----------------+-----+------+

限制查询 LIMIT

LIMIT {offset,} row_count | row_count OFFSET offset

  • 限制查询结果的返回集合。
# 这会返回第一和第二条数据。这个LIMIT和ID的排序没有一点关系的。
[root@localhost][Demo]> SELECT * FROM users LIMIT 2;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
+----+------+----------------+-----+------+

# 需要注意的是LIMIT限制查询和其他条件是没有关系的,它仅仅是在结果集中过滤。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC LIMIT 2;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
|  6 | Per  | pwd            |  23 |    0 |
+----+------+----------------+-----+------+
2 rows in set (0.00 sec)

# 还有一种情况就是范围。先看看全部数据。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  1 | Tom  | _23234awdasdwf |  23 |    1 |
|  6 | Per  | pwd            |  23 |    0 |
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
|  8 | CoCo | agc            |  22 |    1 |
|  5 | Atom | pwd            |  21 |    0 |
+----+------+----------------+-----+------+
6 rows in set (0.00 sec)

# 我们尝试获取结果集中第三条和第四条。这里需要注意的时候结果集的下标是从0开始的。
[root@localhost][Demo]> SELECT * FROM users ORDER BY age DESC LIMIT 2 , 2;
+----+------+----------------+-----+------+
| id | name | pwd            | age | sex  |
+----+------+----------------+-----+------+
|  3 | Jon  | _23234awdasdwf |  22 |    1 |
|  4 | Rose | _23234awdasdwf |  22 |    0 |
+----+------+----------------+-----+------+
2 rows in set (0.00 sec)

数据库表操作小结

  • 插入

    • INSERT [INTO] table_name [(columns_name , ... )] VALUES(value , ...)
    • INSERT [INTO] table_name SET columns_name ={ expr | DEFAULT},...
    • INSERT [INTO] tablle_name [(columns_name,...)] SELECT ...
  • 更新

    • UPDATE [LOW_PRIORITY] [IGNORE] tabel_refernce SET columns_name1 = { expre1 | DEFAULT} , columns_name2 = { expre2 | DEFAULT}] ... [WHERE where_condition]
  • 删除

    • DELETE FROM table_name [WHERE where_condition]
  • 查询

    • SELECT select_expr[ select_expre ...]

      [

      ​ FROM table_reference

      ​ [WHERE where_condition]

      ​ [GROUP BY {columns_name | position}] [ASC | DESC]

      ​ [HAVING where_condition]

      ​ [ORDER BY {columns_name | expre | position}] [ASC | DESC]

      ​ [LIMIT {[offset] row_count | row_count OFFSET}]

      ]


小结

插入、更新、删除都要带明确的 WHERE 条件,查询按 SELECT、GROUP BY、HAVING、ORDER BY、LIMIT 组合;列约束在建表阶段就应确定。

最近更新: 2026/11/20 22:35
Contributors: 庆峰
Prev
MySQL 基础:库与表操作
Next
MySQL 基础:约束、引擎与数据类型