我的学习日记

day9 数据库与SQL语句

发表于 更新于 分类于 ，

计划

  1. MySQL概述
  2. SQL语句（DDL、DML、DQL）
  3. 数据类型与约束
  4. 表结构设计案例

笔记

1.MySQL概述

相关概念
  • 数据库（DB）：存储和管理数据的仓库
  • 数据库管理系统（DBMS）：操作和管理数据库的大型软件
  • SQL（结构化查询语言）：操作关系型数据库的编程语言，定义统一标准
MySQL版本
  • 商业版本：收费，提供技术支持
  • 社区版本：免费开源，本课程使用MySQL 8.0.34社区版
连接方式
1
2
3
4
5
-- 本地连接
mysql -u用户名 -p密码

-- 远程连接
mysql -hIP地址 -P端口号 -u用户名 -p密码

2.SQL语句分类

SQL语句根据功能分为四大类：

分类 全称 说明
DDL Data Definition Language 数据定义语言，定义数据库对象（数据库、表、字段）
DML Data Manipulation Language 数据操作语言，对表中数据进行增删改
DQL Data Query Language 数据查询语言，查询表记录
DCL Data Control Language 数据控制语言，创建用户、控制权限

3.DDL语句

数据库操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询所有数据库
show databases;

-- 查询当前数据库
select database();

-- 创建数据库
create database [if not exists] 数据库名 [default charset utf8mb4];

-- 使用数据库
use 数据库名;

-- 删除数据库
drop database [if exists] 数据库名;
表结构操作

创建表

1
2
3
4
5
create table 表名(
    字段1 字段类型 [约束] [comment 注释],
    字段2 字段类型 [约束] [comment 注释],
    ...
) [comment 表注释];

查询表

1
2
3
4
5
6
7
8
-- 查询当前数据库所有表
show tables;

-- 查看表结构
desc 表名;

-- 查询建表语句
show create table 表名;

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

-- 修改字段类型
alter table 表名 modify 字段名 新类型(长度);

-- 修改字段名和类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释];

-- 删除字段
alter table 表名 drop 字段名;

-- 修改表名
rename table 表名 to 新表名;

删除表

1
drop table [if exists] 表名;

4.约束

约束概念

约束是作用在表中字段上的规则，保证数据的正确性、有效性和完整性。

常用约束
约束 描述 关键字
非空约束 限制字段值不能为null not null
唯一约束 保证字段数据唯一不重复 unique
主键约束 唯一标识一行数据，非空且唯一 primary key
默认约束 未指定值时采用默认值 default
外键约束 建立表间连接，保证数据一致性 foreign key
主键自增
1
id int primary key auto_increment comment 'ID,唯一标识'

5.数据类型

数值类型
类型 小写 大小 描述
TINYINT tinyint 1byte 小整数值
SMALLINT smallint 2bytes 大整数值
MEDIUMINT mediumint 3bytes 大整数值
INT/INTEGER int/integer 4bytes 大整数值
BIGINT bigint 8bytes 极大整数值
FLOAT float 4bytes 单精度浮点数值
DOUBLE double 8bytes 双精度浮点数值
DECIMAL decimal - 小数值（精确定点数）

示例：

1
2
3
4
5
-- 年龄（无负数，不会太大）
age tinyint unsigned

-- 分数（最多1位小数）
score double(4,1)
字符串类型
类型 小写 大小 描述
CHAR char 0-255 bytes 定长字符串
VARCHAR varchar 0-65535 bytes 变长字符串
TEXT text 0-65535 bytes 长文本数据

示例：

1
2
3
4
5
-- 用户名（长度不定，最长50）
username varchar(50)

-- 手机号（固定11位）
phone char(11)
日期时间类型
类型 小写 大小 格式
DATE date 3 YYYY-MM-DD
TIME time 3 HH:MM:SS
DATETIME datetime 8 YYYY-MM-DD HH:MM:SS
TIMESTAMP timestamp 4 YYYY-MM-DD HH:MM:SS

示例：

1
2
3
4
5
-- 生日（只需年月日）
birthday date

-- 创建时间（需要精确到秒）
create_time datetime

6.DML语句

添加数据（INSERT）
1
2
3
4
5
6
7
8
-- 指定字段添加
insert into 表名 (字段1, 字段2) values (值1, 值2);

-- 全部字段添加
insert into 表名 values (值1, 值2, ...);

-- 批量添加
insert into 表名 (字段1, 字段2) values (值1, 值2), (值1, 值2);

注意：

  • 字段顺序与值顺序要一一对应
  • 字符串和日期用引号包含
  • 数据大小在字段规定范围内
修改数据（UPDATE）
1
update 表名 set 字段1 = 值1, 字段2 = 值2 [where 条件];

注意：

  • 不加条件会修改整张表所有数据
  • 修改时建议同步更新update_time字段
删除数据（DELETE）
1
delete from 表名 [where 条件];

注意：

  • 不加条件会删除整张表数据
  • 不能删除单个字段值（用UPDATE置为NULL）

7.DQL语句

基本查询
1
2
3
4
5
6
7
8
9
10
11
-- 查询指定字段
select 字段1, 字段2 from 表名;

-- 查询所有字段
select * from 表名;

-- 设置别名
select 字段1 as 别名1, 字段2 as 别名2 from 表名;

-- 去除重复记录
select distinct 字段列表 from 表名;
条件查询（WHERE）

比较运算符：

运算符 功能
>, >= 大于，大于等于
<, <= 小于，小于等于
= 等于
<> / != 不等于
between … and … 在范围内
in(…) 多选一
like 模糊匹配
is null 是null

逻辑运算符：

运算符 功能
and / && 并且
or / | | 或者
not / ! 非

示例：

1
2
3
4
5
6
7
8
-- 查询薪资大于5000的员工
select * from emp where salary > 5000;

-- 查询入职日期在2000-01-01到2010-01-01之间的员工
select * from emp where entry_date between '2000-01-01' and '2010-01-01';

-- 查询姓名以'张'开头的员工
select * from emp where name like '张%';
聚合函数
函数 功能
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和

示例：

1
2
3
4
5
6
7
8
-- 统计员工总数
select count(*) from emp;

-- 统计平均薪资
select avg(salary) from emp;

-- 统计最高薪资
select max(salary) from emp;
分组查询（GROUP BY）
1
select 字段列表 from 表名 [where 条件] group by 分组字段 [having 分组后条件];

示例：

1
2
3
4
5
-- 按性别分组统计人数
select gender, count(*) from emp group by gender;

-- 查询入职时间在2015年之前的员工，按职位分组，获取人数>=2的职位
select job, count(*) from emp where entry_date <= '2015-01-01' group by job having count(*) >= 2;

where与having区别：

  • where：分组前过滤，不能使用聚合函数
  • having：分组后过滤，可以使用聚合函数
排序查询（ORDER BY）
1
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

排序方式：

  • ASC：升序（默认）
  • DESC：降序

示例：

1
2
3
4
5
-- 按入职时间升序排序
select * from emp order by entry_date;

-- 按入职时间升序，入职时间相同按更新时间降序
select * from emp order by entry_date asc, update_time desc;
分页查询（LIMIT）
1
select 字段列表 from 表名 limit 起始索引, 查询记录数;

公式：起始索引 = (页码 - 1) * 每页条数

示例：

1
2
3
4
5
6
7
8
9
10
-- 查询第1页，每页5条
select * from emp limit 0, 5;
-- 简写
select * from emp limit 5;

-- 查询第2页，每页5条
select * from emp limit 5, 5;

-- 查询第3页，每页5条
select * from emp limit 10, 5;

8.表结构设计案例

员工表设计
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table emp(
    id int unsigned primary key auto_increment comment 'ID,主键',
    username varchar(20) not null unique comment '用户名',
    password varchar(32) not null comment '密码',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
    phone char(11) not null unique comment '手机号',
    job tinyint unsigned comment '职位',
    salary int unsigned comment '薪资',
    image varchar(255) comment '头像',
    entry_date date comment '入职日期',
    create_time datetime comment '创建时间',
    update_time datetime comment '修改时间'
) comment '员工表';
设计流程
  1. 阅读产品原型及需求文档
  2. 确定字段（类型、长度、约束）
  3. 添加基础字段（id、create_time、update_time）

9.核心知识点总结

知识点 说明
DDL 数据定义语言，操作数据库和表结构
DML 数据操作语言，增删改数据
DQL 数据查询语言，查询数据
primary key 主键约束，唯一标识
auto_increment 主键自增
not null 非空约束
unique 唯一约束
default 默认约束
int / varchar / datetime 常用数据类型
where 条件查询
group by 分组查询
order by 排序查询
limit 分页查询
count / max / min / avg / sum 聚合函数