SQL语句

SQL语句

1. 前言

1.1 引言

在实际的 Web 开发中，为了让应用程序的职责更加单一、便于维护，我们通常将 Web 应用分为 Controller、Service、Dao 三层。早期的案例中，数据可能存储在 txt 文件中，但在企业开发中，为了高效管理数据，必然会使用数据库。程序员通过发送 SQL 语句给数据库管理系统（DBMS），实现数据的创建、查询、修改和删除操作。

1.2 相关

• 数据库（Database）： 用于存储和管理数据的仓库，如电商网站、OA 系统中的数据均存储在数据库中。
• 数据库管理系统（DBMS）： 用于操作和管理数据库的软件，通过该软件可对数据库中的数据进行各种操作。
• SQL（Structured Query Language）： 结构化查询语言，用于定义和操作关系型数据库。

小测试题：

1. 请简述数据库、DBMS 和 SQL 的概念及它们之间的关系。
2. 为什么在企业开发中通常不会使用 txt 文件存储数据？

2. MySQL概述

MySQL 是当前互联网公司中使用最广泛的开源关系型数据库，主要分为商业版和社区版。本文中我们主要以 MySQL 社区版为例，版本号可参考 8.0.34。

2.1 安装与连接

MySQL 的安装可以通过官网下载安装包进行，安装后可使用以下命令连接数据库服务器：

mysql -u用户名 -p密

若需要连接远程服务器，可加入 -h 和 -P 参数。对于安全性考虑，建议在提示后输入密码而不是在命令行直接写明文密码。

小测试题：

1. 连接本地 MySQL 服务器和远程 MySQL 服务器的命令分别如何书写？
2. 为什么不建议在命令行中直接输入明文密码？

2.2 数据模型

MySQL 是基于二维表进行数据存储的关系型数据库。一个数据库包含多个数据表，每张表由行和列构成。操作步骤通常为：

1. 创建数据库
2. 在数据库下创建数据表
3. 向表中插入数据

小测试题：

1. 什么是关系型数据库？
2. 描述数据库中数据从创建到存储的基本流程。

3. SQL语句详解

SQL 语句大致可分为四类：DDL、DML、DQL 和 DCL。下面主要介绍前三种常用语句类型，并结合实例进行说明。

3.1 DDL语句（数据定义语言）

DDL 用于定义数据库和数据表的结构，包括创建、查询、修改和删除操作。

3.1.1 数据库操作

• 查询所有数据库：

  
  show databases;
  
  

• 查询当前数据库：

  
  select database();
  
  

• 创建数据库：

  
  create database if not exists itcast default charset utf8mb4;
  
  

• 使用数据库：

  
  use itcast;
  
  

• 删除数据库：

  drop database if exists itcast;
  
  

3.1.2 表操作

• 创建表：

  create table tb_user (
      id int primary key auto_increment comment 'ID,唯一标识',
      username varchar(20) not null unique comment '用户名',
      name varchar(10) not null comment '姓名',
      age int comment '年龄',
      gender char(1) default '男' comment '性别'
  ) comment '用户表';
  
  

• 查询表结构：

  desc tb_user;
  
  

• 修改表结构：

  添加字段：

  alter table tb_user add qq varchar(11) comment 'QQ号码';
  
  

  修改字段：

  alter table tb_user modify qq varchar(13) comment 'QQ号码';
  
  

  删除字段：

  alter table tb_user drop qq;
  
  

• 删除表：

  
  drop table if exists tb_user;
  
  

小测试题：

1. 如何使用 SQL 语句创建一个包含自增主键的用户表？
2. 说出修改表结构时常用的三个操作。

3.2 DML语句（数据操作语言）

DML 用于对数据表中的数据进行操作，包括数据的插入、修改和删除。

3.2.1 插入数据

• 插入指定字段：

  
  insert into emp(username, name, gender, phone, create_time, update_time)
  values ('wuji', '张无忌', 1, '13309091231', now(), now());
  
  

• 批量插入：

  
  insert into emp(username, name, gender, phone, create_time, update_time)
  values ('Tom1', '汤姆1', 1, '13309091231', now(), now()),
         ('Tom2', '汤姆2', 1, '13309091232', now(), now());
  
  

3.2.2 修改数据

• 更新指定记录：

  
  update emp set name='张三', update_time=now() where id=1;
  
  

• 更新所有记录：

  
  update emp set entry_date='2010-01-01', update_time=now();
  
  

3.2.3 删除数据

• 删除指定记录：

  
  delete from emp where id=1;
  
  

• 删除所有记录：

  
  delete from emp;
  
  

小测试题：

1. 插入数据时，为什么要注意字段的顺序与值的顺序？
2. 更新数据时为何需要同步修改 update_time 字段？

3.3 DQL语句（数据查询语言）

DQL 主要用于从数据表中查询数据，是所有 SQL 操作中最常用的。

3.3.1 基本查询

• 查询多个字段：

  
  select name, entry_date from emp;
  
  

• 查询所有字段：

  
  select * from emp;
  
  

• 设置别名：

  
  select name as '姓名', entry_date as '入职日期' from emp;
  
  

• 去重查询：

  
  select distinct job from emp;
  
  

3.3.2 条件查询

• 简单条件查询：

  
  select * from emp where name = '杨逍';
  
  

• 数值比较查询：

  
  select * from emp where salary <= 5000;
  
  

• 空值判断：

  
  select * from emp where job is null;
  
  

• 范围查询：

  
  select * from emp where entry_date between '2000-01-01' and '2010-01-01';
  
  

• 模糊查询：

  查询姓名包含“二”：

  select * from emp where name like '%二%';
  
  

3.3.3 聚合函数与分组查询

• 统计总人数：

  
  select count(*) from emp;
  
  

• 求平均薪资：

  
  select avg(salary) from emp;
  
  

• 分组统计：

  按性别统计：

  
  select gender, count(*) from emp group by gender;
  
  

3.3.4 排序查询

• 升序排序：

  
  select * from emp order by entry_date asc;
  
  

• 降序排序：

  
  select * from emp order by entry_date desc;
  
  

• 多字段排序：

  
  select * from emp order by entry_date asc, update_time desc;
  
  

3.3.5 分页查询

• 分页查询示例：

  查询从索引 0 开始的 5 条记录：

  
  select * from emp limit 0, 5;
  
  

  查询第 2 页数据（每页 5 条）：

  select * from emp limit 5, 5;
  
  

小测试题：

1. 什么是聚合函数？请举例说明。
2. 区分 where 和 having 在分组查询中的作用。
3. 分页查询中的起始索引如何计算？

• 什么是聚合函数？请举例说明。

  聚合函数是 SQL 中对一组数据进行汇总计算的函数，它将一列数据作为一个整体进行计算，并返回单一的结果。例如：

  • COUNT(列名)： 统计非 NULL 值的行数。

  • SUM(列名)： 计算某列所有值的和。

  • AVG(列名)： 求某列值的平均数。

  • MAX(列名)： 查找某列中的最大值。

  • MIN(列名)： 查找某列中的最小值。

    例如，在员工表中，可以使用 SELECT COUNT(*) FROM emp; 来统计员工总数；使用 SELECT AVG(salary) FROM emp; 来计算平均薪资。

• 区分 where 和 having 在分组查询中的作用。

  • WHERE 子句：在进行分组前对数据进行过滤，它用于筛选满足条件的行，并且不能包含聚合函数。

  • HAVING 子句：在数据分组后对分组结果进行过滤，它允许使用聚合函数来判断每个分组是否满足条件。

    举例来说，如果想统计每个职位下员工数量大于 2 的情况，可以先使用 GROUP BY 分组，再用 HAVING COUNT(*) >= 2 过滤分组后的数据，而不能把 COUNT(*) >= 2 写在 WHERE 子句中。

• **分页查询中的起始索引如何计算？**起始索引=(当前页码−1)×每页显示记录数(3−1)×5=10

  在 MySQL 中使用 LIMIT 进行分页查询时，起始索引的计算公式为：

  起始索引=(当前页码−1)×每页显示记录数\text{起始索引} = (\text{当前页码} - 1) \times \text{每页显示记录数}

  例如，如果每页显示 5 条记录，查询第 3 页的数据时，起始索引为：

  (3−1)×5=10(3-1) \times 5 = 10

  SQL 语句示例：

  SELECT * FROM emp LIMIT 10, 5;