day9 数据库与SQL语句

计划

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

笔记

1.MySQL概述

MySQL版本

商业版本：收费，提供技术支持
社区版本：免费开源，本课程使用MySQL 8.0.34社区版

连接方式

-- 本地连接
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语句

数据库操作

-- 查询所有数据库
show databases;

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

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

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

-- 删除数据库
drop database [if exists] 数据库名;

表结构操作

创建表

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

查询表

-- 查询当前数据库所有表
show tables;

-- 查看表结构
desc 表名;

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

修改表

-- 添加字段
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	-	小数值（精确定点数）

示例：

-- 年龄（无负数，不会太大）
age tinyint unsigned

-- 分数（最多1位小数）
score double(4,1)

字符串类型

类型	小写	大小	描述
CHAR	char	0-255 bytes	定长字符串
VARCHAR	varchar	0-65535 bytes	变长字符串
TEXT	text	0-65535 bytes	长文本数据

示例：

-- 用户名（长度不定，最长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

示例：

-- 生日（只需年月日）
birthday date

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

6.DML语句

添加数据（INSERT）

-- 指定字段添加
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语句

基本查询

-- 查询指定字段
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 / !	非

示例：

-- 查询薪资大于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	求和

示例：

-- 统计员工总数
select count(*) from emp;

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

-- 统计最高薪资
select max(salary) from emp;

分组查询（GROUP BY）

1	select 字段列表 from 表名 [where 条件] group by 分组字段 [having 分组后条件];

示例：

-- 按性别分组统计人数
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：降序

示例：

-- 按入职时间升序排序
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页，每页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.表结构设计案例

员工表设计

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 '员工表';

设计流程

阅读产品原型及需求文档
确定字段（类型、长度、约束）
添加基础字段（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	聚合函数

我的学习日记

day9 数据库与SQL语句

计划

笔记

1.MySQL概述

相关概念

MySQL版本

连接方式

2.SQL语句分类

3.DDL语句

数据库操作

表结构操作

4.约束

约束概念

常用约束

主键自增

5.数据类型

数值类型

字符串类型

日期时间类型

6.DML语句

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

7.DQL语句

基本查询

条件查询（WHERE）

聚合函数

分组查询（GROUP BY）

排序查询（ORDER BY）

分页查询（LIMIT）

8.表结构设计案例

员工表设计

设计流程

9.核心知识点总结