3.MySQL数据库2023-09-04

一、SQL

1. 简介

SQL （Structured Query Language）结构化查询语言；

它是一种数据库查询和程序设计语言，同时也是目前使用最广泛的关系型数据库操作语言。

2. 分类

2.1 DQL(Data Query Language) 数据查询语言

DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

select
from
where
order by
having

2.2 DML(Data Manipulation Language) 数据操作语言

DML主要用于对数据增加、修改和删除的操作。主要包括：

INSERT: 增加数据
UPDATE: 修改数据
DELETE: 删除数据

2.4 DDL(Data Definition Language) 数据定义语言

DDL主要用针对是数据库对象（数据库、表、索引、视图）进行创建, 修改和删除操作。主要包括：

CREATE: 创建
ALTER: 修改
DROP: 删除

2.5 DCL(Data Control Language) 数据控制语言

DCL用来授予或回收访问数据库的权限。

主要包括：

GRANT: 授予用户权限
REVOKE: 回收授予的权限。

2.6 TCL(Transaction Control Language) 事务控制语言

TCL用于数据库的事务管理。

主要包括：

START TRANSACTION: 开启事务
COMMIT: 提交事务
ROLLBACK: 回滚事务

注意：

数据操纵语言DML（insert、update、delete）针对表中的数据；

而数据定义语言DDL（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure；

二、DDL操作

1.DDL 操作数据库

例：

-- 查看所有的数据库
show databases;

-- 创建数据库
create database db_test;

-- 选择数据库
use bjsxt01;

-- 删除数据库
drop database db_test;

2.DDL 操作数据库表

2.1 MySQL中常见的数据类型

常见：

类型	描述
int	整型
double	浮点型
varchar	字符串型
date	日期类型，格式为：yyyy-MM-dd

详细：

　　整数类型：　　　　　　

整数类型	大小	表数范围(有符号)	表数范围(无符号)	作用
TINYINT	1字节	(-128，127)	(0，255)	小整数值
SMALLINT	2字节	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3字节	(-8388608，8388607)	(0，16777215)	大整数值
INT	4字节	(-214748364，2147483647)	(0，4294967295)	大整数值
BIGINT	8字节	(-9233372036854775808，9223372036854775807)	（0，18446744073709551615)	极大整数值

　　浮点类型：

浮点数类型	大小	作用
FLOAT(m,d)	4字节	单精度浮点数值, m总个数，d小数位
DOUBLE(m,d)	8字节	双精度浮点数值, m总个数，d小数位

注意事项： int类型会自动扩充，浮点数类型的宽度不会自动扩充，使用时需要指定长度。

　　　　如：score double(4,1) 总体长度是4 精度为1位。

　　字符类型：　　　

字符串类型	大小	描述
CHAR(M)	0~255字符	允许长度0~M个字符的定长字符串
VARCHAR(M)	0~65535字符	允许长度0~M个字符的变长字符串
TINYTEXT	0~255字节	短文本数据
TEXT	0~65535字节	长文本数据
MEDIUMTEXT	0~16777215字节	中等长度文本数据
LONGTEXT	0~4294967295字节	极大文本数据
BLOB(M)	最大65K	以二进制存储图片，文件等数据

char长度固定，即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长。

varchar可变长度，可以设置最大长度, VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，

所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

CHAR和VARCHAR表示的是字符的个数，而不是字节的个数。

text不设置长度，当不知道属性的最大长度时，适合用text。

按照查询速度： char最快， varchar次之，text最慢。

　　日期类型：　　　　

类型	格式	取值范围
TIME	‘HH:MM:SS’	(‘-838:59:59’, ‘838:59:59’)
DATE	‘YYYY-MM-DD’	(‘1000-01-01’, ‘9999-12-31’)
YEAR	YYYY	(1901, 2155), 0000
DATETIME	‘YYYY-MM-DD HH:MM:SS’	(‘1000-01-01 00:00:00’, ‘9999-12-31 23:59:59’)
TIMESTAMP	‘YYYY-MM-DD HH:MM:SS’	(‘1970-01-01 00:00:01’ UTC, ‘2038-01-19 03:14:07’ UTC)

2.2 DDL创建数据库表

create table 表名(
    字段1    数据类型,
    字段2 数据类型(长度)
　　 ......
);
-- 注意：最后一个字段不要加逗号，可以给字段的数据类型指定长度

2.3 DDL查看数据库表

-- 显示当前数据库的所有的表
show tables;
-- 显示某个表的结构 desc 表名
desc 表名;
-- 查看某个表完整的建表语句 show create table 表名
show create table 表名;

2.4 DDL删除数据库表

DROP TABLE 表名;

-- 删除当前数据库中的某个表  drop table 表名
drop table student;

2.5 DDL修改数据库表

2.5.1 修改表名

-- 语法格式：
rename table 旧表名 to 新表名;

-- 修改student表名为stu
rename table student to stu;

-- 显示所有表
show tables;

2.5.2 向表中添加字段

-- 语法格式：
alter table 表名 add 字段名称 字段类型;

-- 案例：向学生表中添加字段address，长度为50
alter table stu add address varchar(50); -- 默认添加到最后
alter table stu add address2 varchar(50) first; -- 添加到第一个
alter table stu add address3 varchar(50) after age; -- 添加到age字段之后

-- 查看表结构
desc stu;

2.5.3 修改表中字段

-- 语法格式： 
alter table 表名 modify 字段名称 字段类型;(修改字段类型和长度)

-- 案例：修改学生表中的字段address，长度改为30
alter table stu modify address varchar(30);

-- 语法格式：
alter table 表名 change 旧字段名 新字段名 类型(长度); (修改字段名 )

-- 案例：修改学生表中的字段address，修改为city，长度为10
alter table stu change address city varchar(10);

2.5.4 删除表中字段

-- 语法格式：
alter table 表名 drop 字段名;

-- 案例：删除学生表中 address2字段
alter table stu drop address2;

三、DML操作

1. 插入数据

-- 语法格式：
-- 1. 插入部分或全部字段 
insert into 表名(字段名1,字段名2,...,字段名n) values(值1,值2,...,值n);
-- 2. 插入全部字段 
insert into 表名 values(值1,值2,...,值n);

-- 案例：向学生表中插入数据
-- 方式1：插入全部字段，将所有字段名都写出来
insert into stu(sid,sname,sex,age,createdate,clazz,email) values(1,'张飞','男',50,'1990-05-05','1班','zhangfei@qq.com');

-- 方式2：插入全部字段，可以不写字段名
insert into stu values(2,'刘备','男',30,'1995-06-06','2班','liubei@qq.com');

-- 方式3：插入指定字段的值
insert into stu(sid,sname,age,createdate) values(3,'赵云',19,now());
insert into stu(sid,sname,createdate) values(4,'曹操',sysdate());
insert into stu(sid,createdate) values(5,current_date());

-- 一次添加多条数据
insert into 表名 values(值1,值2,...,值n),(值1,值2,...,值n),(值1,值2,...,值n);

注意事项：

值与字段必须要对应，个数相同、数据类型相同

值的数据大小，必须在字段指定的长度范围内

varchar char date类型的值使用单引号或者双引号包裹

如果要插入空值，可以忽略不写或者写null

如果插入指定字段的值，必须要上写列名

字符串日期类型支持 "2000-12-23" "2000/12/23" "2000.12.23"

2. 修改数据

-- 语法格式
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n; -- 慎用：不加条件会修改所有的数据

-- 案例1：将sid=1的学生性别改为女
update stu set sex='女' where sid=1;

-- 案例2：将sid=2的学生性别改为女，年龄改为30，班级改为11班
update stu set sex='女',age=30,clazz='11班' where sid=2;

3. 删除数据

-- 语法格式
delete from 表名 where 条件;
delete from 表名; -- 慎用：删除表中的所有数据
truncate table 表名; -- 慎用：删除表中的所有数据

-- 案例1：删除sid=5的学生信息
delete from stu where sid=5;

-- 案例2：删除stu表中所有的数据
-- 方式一：
delete from stu;
-- 方式二：
truncate table stu; -- 推荐使用

truncate 和 delete的对比：

这两种操作都可以删除表中的数据
delete为数据操作语言DML；truncate 为数据定义语言DDL。
delete操作是将表中所有记录一条一条删除直到删除完；truncate 操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，truncate 操作的效率更高。
delete操作可以回滚；truncate 操作会导致隐式提交，因此不能回滚。
delete操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“affected rows：4”）；

　　　　truncate 操作不会返回已删除的行量，结果通常是“affected rows：0”。

　　　　delete操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；

　　　　truncate 操作会重新从1开始自增。

四、表的约束

1. 作用

可以对表中的数据做一些限制, 从而保证数据的正确性, 有效性, 和完整性。

违反约束的不正确数据是无法插入到表中的。

2. 分类

约束名	关键字	作用
主键	primary key	不可重复, 不能为空，即唯一 + 非空
唯一	unique	不可重复
非空	not null	不能为空
默认值	default	赋予默认值
检查(Mysql8提供)	check	取值范围
外键	foreign key	表与表之间的关系

3. 主键约束

-- 语法格式：
字段名 字段类型 primary key

-- 案例1：创建一个带有主键的emp表 字段 eid int  ename varchar(10)  esex char(1)
-- 方式一
create table emp(
    eid int primary key,  -- 设置主键 非空唯一
    ename varchar(10),
    sex char(1)
);

-- 方式二
drop table emp; -- 删除emp表
create table emp(
    eid int,
    ename varchar(10),
    sex char(1),
    primary key(eid)  -- 指定主键为eid字段
);

-- 方式三
drop table emp; -- 删除emp表
create table emp(
    eid int primary key,
    ename varchar(10),
    sex char(1)
);
-- 创建表时不指定主键, 然后通过DDL语句进行设置
alter table emp add primary key(eid);

3.1 删除主键约束

-- 使用DDL 删除表中的主键约束
alter table emp drop primary key;

-- 查看表结构
desc emp;

-- 删除唯一约束(了解)
-- 添加了唯一约束为 eid int not null, 通过设置字段属性删除唯一约束 
alter table emp modify eid int;

3.2 主键自增

-- 语法格式: 
关键字 auto_increment     表示自增长(字段类型必须为整数类型)

-- 案例：为emp表eid字段添加主键约束, 并设置为自增
drop table emp;
create table emp(
    eid int primary key auto_increment,
    ename varchar(10),
    sex char(1)
);
-- 测试主键自增
insert into emp values(null,'张三','男');
insert into emp values(default,'李四','男');
insert into emp(ename, esex) values('王五', '男');

4. 非空约束

-- 语法格式: 字段名 字段类型 not null

-- 案例：创建emp表，eid为主键约束,自增，ename为非空约束
-- 删除存在的emp表 
drop table emp;
create table emp(
    eid int primary key auto_increment,
    ename varchar(10) not null,
    sex char(1)
);
-- 测试非空约束
insert into emp values(default,null,'n');
-- Column 'ename' cannot be null  ename不能为空

5. 唯一约束

-- 语法格式： 
字段名 字段类型 unique

-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex唯一约束
drop table emp; -- 删除存在的emp表 
create table emp(
    eid int primary key auto_increment,
    ename varchar(10) not null,
    sex char(1) unique
);
-- 测试唯一约束 添加两个性别为男
insert into emp values(default,'zs','男');
insert into emp values(default,'lisi','男');
-- Duplicate entry 'n' for key 'emp.esex'  esex重复

6. 默认值约束

-- 语法格式： 字段名 字段类型 default '值'

-- 案例：emp表 eid 主键约束,自增  ename 非空约束 esex默认值'男'
-- 删除存在的emp表 
drop table emp;
create table emp(
    eid int primary key auto_increment,
    ename varchar(10) not null,
    sex char(1) default '男'
);
-- 查看emp表结构 
desc emp;
-- 测试默认值约束
insert into emp values(default,'zs',default);

-- 错误用法
insert into emp values(default,'zs',null);

7. 检查约束

字段名 字段类型 check(字段='值' or 字段='值')
字段名 字段类型 check(字段>n or 字段<m)
字段名 字段类型 check(字段>n and 字段<m)

案例：emp表 eid 主键约束, 自增 ename 非空约束 esex默认值'男'并检查男|女

-- 删除存在的emp表 
drop table emp;
create table emp(
    eid int primary key auto_increment,
    ename varchar(10) not null,
    esex char(1) default '男' check(esex='男' or esex='女')
);
-- 查看emp表结构 
desc emp;
-- 测试检查约束
insert into emp values(default,'lisi','n');
-- Check constraint 'emp_chk_1' is violated 不符合字段要求

posted @ 2023-09-04 20:02 ygdgg 阅读(23) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· MySQL_DQL操作

· MySQL_DCL、TCL、索引、视图

· MySQL中DDL和DML

· MySQL基础

· mysql

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· C#/.NET/.NET Core技术前沿周刊 | 第 29 期（2025年3.1-3.9）
· 从HTTP原因短语缺失研究HTTP/2和HTTP/3的设计差异

公告

昵称： ygdgg
园龄： 1年11个月
粉丝： 8
关注： 3

+加关注

2025年3月

日

一

二

三

四

五

六

giaogiaoyang

MySQL数据库

一、SQL

1. 简介

2. 分类

2.1 DQL(Data Query Language) 数据查询语言

2.2 DML(Data Manipulation Language) 数据操作语言

2.4 DDL(Data Definition Language) 数据定义语言

2.5 DCL(Data Control Language) 数据控制语言

2.6 TCL(Transaction Control Language) 事务控制语言

二、DDL操作

1.DDL 操作数据库

2.DDL 操作数据库表

2.1 MySQL中常见的数据类型

2.2 DDL创建数据库表

2.3 DDL查看数据库表

2.4 DDL删除数据库表

2.5 DDL修改数据库表

2.5.1 修改表名

2.5.2 向表中添加字段

2.5.3 修改表中字段

2.5.4 删除表中字段

三、DML操作

1. 插入数据

2. 修改数据

3. 删除数据

四、表的约束

1. 作用

2. 分类

3. 主键约束

3.1 删除主键约束

3.2 主键自增

4. 非空约束

5. 唯一约束

6. 默认值约束

7. 检查约束

公告

搜索

常用链接

最新随笔

积分与排名

合集

随笔分类

随笔档案

推荐排行榜

最新评论