MySQL 增删改查语法记录快速复习-Toy模板网

这篇具有很好参考价值的文章主要介绍了MySQL 增删改查语法记录快速复习。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

MySQL 增删改查语法记录快速复习

MySQL 的增删改查

了解 DDL、DML、DQL
数据定义语言 DDL(Data Definition Language) ，操作数据库、表、列等；如：CREATE、 ALTER、DROP
数据操作语言 DML(Data Manipulation Language) ，操作数据表里的数据；如：INSERT、 UPDATE、 DELETE
数据查询语言 DQL(Data Query Language) ，用来查询数据如：SELECT

套路写法

create database 库名;
create table 表名 (字段名 数据类型 字段约束 注释);
insert into 已有的数据表名 () values ();
update 表名 set 字段名=新的值 where 条件;
select ... from ... where ... group by ... having ... order by ... limit

增

创建数据库

## 创建数据库
create database 库名;

## 创建数据库，当指定名称的数据库不存在时执行创建
create database if exists 库名;

## 在创建时指定字符集
create database 库名 character set utf8;

创建数据表

创建表

## 语法
create table 表名 (
    字段名 数据类型 字段约束 注释 ,
);

create table students(
	id int primary key comment '主键约束'
    stu_num char(8) not null unique comment '字段的注释内容',
    stu_name varchar(20) not null comment '学生名字',
    stu_gender char(2) not null,
    stu_age int,
    stu_tel char(11) not null unique,
    stu_qq varchar(11) unique
    );

字段约束

非空约束 not null
唯一约束 unique
主键约束 primary key
外键约束 foreign key

数据类型

主要分为三类：数值、字符串(字符)、日期/时间类型。

数值类型

类型
tinyint
smallint
mediumint
int
bigint
float
double
decimal

字符串(字符)类型

类型
char
varchar
tinytext
text
mediumtext
longtext

日期/时间类型

类型
date
time
year
datetime
timestamp

插入数据 ( insert )

## 语法
insert into 数据表名称 ( 字段1 , 字段2... ) values(字段1对应的值 , 字段2对应的值...);
## 实例
insert into students(stu_num,stu_name,stu_gender,stu_age,stu_tel) values('20220423','张三','男',21,'18218596100');
## 向数据表中指定的列添加数据（不允许为空的列必须提供数据）
## 数据表名后的字段名列表顺序可以不与表中⼀致，但是values中值的顺序必须与表名后字段名顺序对应
## 当要向表中的所有列添加数据时，数据表名后面的字段列表可以省略，但是values中的值的顺序要与数据表定义的字段保持⼀致;
## 不过在项目开发中，即使要向所有列添加数据,也建议将列名的列表显式写出来(增强SQL的稳定性)

删

删除数据库

drop database 库名;
## 如果数据库存在则删除
drop database if exists 库名;

删除数据表

drop table 表名;
## 如果数据表存在时则删除
drop table if exists 表名;

删除数据

## 语法  一定要带上条件，不带上的话整个表的数据都被删了
## delete from 表名;  别轻易使用
delete from 表名 条件;
##实例
delete from students where stu_num='1';
## 删除年龄⼤于20岁的学⽣信息(如果满⾜where⼦句的记录有多条，则删除多条记录)
delete from stus where stu_age>20;
## 如果删除语句没有where⼦句，则表示删除当前数据表中的所有记录(敏感操作)
delete from stus;

改

修改数据库

## 修改数据库字符集
alter database 库名 character set utf8;
## 修改数据库名（重命名）
alter database 原来名称 rename to 新的名称;

修改数据表

## 修改表名
alter table 原来表名 rename to 新的表名;
## 数据表也是有字符集，默认和数据库一致
alter table <tableName> character set utf8;
## 添加列（字段）
alter table <tableName> add <columnName> varchar(200);
## 修改字段名
alter table test3 rename column new_name to name;
## 修改列（字段）的列表和类型
alter table <tableName> change <oldColumnName> <newColumnName> <type>;
## 只修改列（字段）类型
alter table <tableName> modify <columnName> <newType>;
## 删除列（字段）
alter table <tableName> drop <columnName>;

修改数据

## 语法
update 表名 set 字段名=新的值 ;  ## 这改的是表里所有数据的值
update 表名 set 字段名=新的值 where 条件 ;  ## 这改的是表里符合条件数据的值
## 实例
update stus set stu_name='啊强' where stu_name='张三';
## 将学号为20210103的学⽣ 性别修改为“男”,同时将QQ修改为 777777（修改多列）
update stus set stu_gender='男',stu_qq='777777' where stu_num='20210103';
## 根据主键修改其他所有列
update stus set stu_name='啊珍',stu_gender='⼥',stu_age=18,stu_tel='13131313311' ,stu_qq='999999' where stu_num='20210102';
## 如果update语句没有where⼦句，则表示修改当前表中所有⾏（记录）
update stus set stu_name='Tom';

查

查询数据库

## 显示当前mysql中的数据库列表
show databases;

## 显示指定名称的数据库的创建的sql指令
show create database 库名;

## 使用数据库
use 库名;

查询数据表

## 查询库里有哪些数据表
show tables:

## 查询表结构
desc <tableName>;

查询数据

## 语法
select 需要显示的字段名1,需要显示的字段名2... from 表名 where 符合的条件 ;
## select 关键字后指定要显示查询到的记录的列
select columnName1[,columnName2,columnName3...] from <tableName> [where conditions];
## 如果要显示查询到的记录的所有列，则可以使⽤ * 替代字段名列表（开发中不建议使⽤*）
select * from <tableName>;

where

在删除、修改及查询的语句后都可以添加where（条件），⽤于筛选满⾜特定的添加的数据进⾏删除、修改和查询操作。

条件关系运算符

## = 等于
select * from stus where stu_num = '20210101';
## != <> 不等于
select * from stus where stu_num != '20210101';
select * from stus where stu_num <> '20210101';
## > ⼤于
select * from stus where stu_age>18;
## < ⼩于
select * from stus where stu_age<20;
## >= ⼤于等于
select * from stus where stu_age>=20;
## <= ⼩于等于
select * from stus where stu_age<=20;
## between and 区间查询 between v1 and v2 [v1,v2]
select * from stus where stu_age between 18 and 20;

条件逻辑运算符

并且 and
或 or
取反 not

## and 并且
select * from stus where stu_gender='女' and stu_age<=22;
## or 或者 筛选多个条件中⾄少满⾜⼀个条件的记录
select * from stus where stu_gender='女' or stu_age<=22;
## not 取反
select * from stus where stu_age not between 18 and 21;

like

在where的条件中，我们可以使用like关键字来实现模糊查询

## 语法
select * from tableName where columnName like 'reg';
## 在like关键字后的reg表达式中
	## % 表示任意多个字符 [ %a% 包含字母a ]
	## _ 表示任意一个字符 [ _a% 第二个字母为a ]

##实例 
## 查询包含 张
select * from stus where stu_name like '%张%';
# 查询学⽣姓名第⼀个字为`张`的学⽣信息
select * from stus where stu_name like '张%';
# 查询学⽣姓名最后⼀个字⺟为o的学⽣信息
select * from stus where stu_name like '%o';
# 查询学⽣姓名中第⼆个字⺟为o的学⽣信息
select * from stus where stu_name like '_o%';

别名

as 后面跟着取的别名

## 计算列
select stu_name,2022-stu_age from stus;
## as 字段取别名
select stu_name,2022-stu_age as stu_birth_year from stus;
select stu_name as 姓名,2022-stu_age as 出生年份 from stus;

distinct 消除重复⾏

## 查询年龄
select stu_age from stus;
## 取消重复行
select distinct stu_age from stus;

排序 order by

将查询到满足条件的记录按指定列的值升序/降序排列
asc 升序
desc 降序

## 语法  asc升序  desc降序
select * from tableName where 条件 order by columnName asc|desc;

聚合函数

SQL中提供了⼀些可以对查询的记录的列进⾏计算的函数——聚合函数
count 统计函数
max 计算最大值
min 计算最小值
sum 计算和
avg 求平均值

select avg(stu_age) from stus;
select avg(stu_age) from stus where stu_gender='男';

日期函数和字符串函数

当我们向⽇期类型的列添加数据时，可以通过字符串类型赋值（字符串的格式必须为 yyyy-MM-dd hh:mm:ss）

如果我们想要获取当前系统时间添加到⽇期类型的列，可以使⽤ now() 或者 sysdate()

日期函数

now()
sysdate()

## 通过字符串类型 给⽇期类型的列赋值
insert into
stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20200108','张⼩三','⼥',20,'13434343344','123111','2021-09-01
09:00:00');
## 通过now()获取当前时间
insert into
stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20210109','张⼩四','⼥',20,'13434343355','1233333',now());
## 通过sysdate()获取当前时间
insert into
stus(stu_num,stu_name,stu_gender,stu_age,stu_tel,stu_qq,stu_enterence)
values('20210110','李雷','男',16,'13434343366','123333344',sysdate());

## 通过now和sysdate获取当前系统时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-04-23 13:22:39 |
+---------------------+
mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2022-04-23 13:22:53 |
+---------------------+

字符串函数

# concat(colnum1,colunm2,...) 拼接多列
select concat(stu_name,'-',stu_gender) from stus;
# upper(column) 将字段的值转换成⼤写
select upper(stu_name) from stus;
# lower(column) 将指定列的值转换成⼩写
select lower(stu_name) from stus;
# substring(column,start,len) 从指定列中截取部分显示 start从1开始
select stu_name,substring(stu_tel,8,4) from stus;

分组查询 group by

分组——就是将数据表中的记录按照指定的类进⾏分组

## 语法
select 分组字段/聚合函数 from 表名 [where 条件] group by 分组列名 [having 条件] [order by 排序字段]

select 后通常显示分组字段和聚合函数(对分组后的数据进⾏统计、求和、平均值等)
语句执⾏属性：
- 先根据where条件从数据库查询记录 !
- group by对查询记录进⾏分组
- 执⾏having对分组后的数据进⾏筛选

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再分别统计每组学⽣的个数
select stu_gender,count(stu_num) from stus group by stu_gender;

# 先对查询的学⽣信息按性别进⾏分组（分成了男、⼥两组），然后再计算每组的平均年龄
select stu_gender,avg(stu_age) from stus group by stu_gender;

# 先对学⽣按年龄进⾏分组（分了16、17、18、20、21、22六组），然后统计各组的学⽣数量，还可以对最终的结果排序
select stu_age,count(stu_num) from stus group by stu_age order by stu_age;

# 查询所有学⽣，按年龄进⾏分组，然后分别统计每组的⼈数，再筛选当前组⼈数>1的组，再按年龄升序显示出来
select stu_age,count(stu_num) from stus group by stu_age having count(stu_num)>1 order by stu_age;

# 查询性别为'男'的学⽣，按年龄进⾏分组，然后分别统计每组的⼈数，再筛选当前组⼈数>1的组，再按年龄升序显示出来
select stu_age,count(stu_num) from stus where stu_gender='男' group by stu_age
having count(stu_num)>1 order by stu_age;

分页查询 limit

## 语法
select ... from ... where ... limit param1,param2;

param1 int , 表示获取查询语句的结果中的第⼀条数据的索引（索引从0开始）
param2 int, 表示获取的查询记录的条数（如果剩下的数据条数<param2，则返回剩下的所有记录）

总记录数 count 10

每⻚显示 pageSize 3

总⻚数： pageCount = count%pageSize==0 ? count/pageSize : count/pageSize +1

# 查询第⼀⻚：
select * from stus [where ...] limit 0,3; (1-1)*3
# 查询第⼆⻚：
select * from stus [where ...] limit 3,3; (2-1)*3
# 查询第三⻚：
select * from stus [where ...] limit 6,3; (3-1)*3
# 查询第四⻚：
select * from stus [where ...] limit 9,3; (4-1)*3

# 如果在⼀张数据表中：
# pageNum表示查询的⻚码
# pageSize表示每⻚显示的条数
# 通⽤分⻚语句如下：
select * from <tableName> [where ...] limit (pageNum- 1)*pageSize,pageSize;

连接查询

在MySQL中可以使⽤join实现多表的联合查询——连接查询，join按照其功能不同分为三个操作：
inner join 内连接
left join 左连接
right join 右连接

创建班级信息表和学生信息表

create table classes(
 class_id int primary key auto_increment,
 class_name varchar(40) not null unique,
 class_remark varchar(200) 
);
 create table students(
 stu_num char(8) primary key,
 stu_name varchar(20) not null,
 stu_gender char(2) not null,
 stu_age int not null,
 cid int,
 constraint FK_STUDENTS_CLASSES foreign key(cid) references
classes(class_id) ON UPDATE CASCADE ON DELETE CASCADE
);
# Java2104 包含三个学⽣信息
insert into classes(class_name,class_remark) values('Java2104','...');
# Java2105 包含两个学⽣信息
insert into classes(class_name,class_remark) values('Java2105','...');
# 以下两个班级在学⽣表中没有对应的学⽣信息
insert into classes(class_name,class_remark) values('Java2106','...');
insert into classes(class_name,class_remark) values('Python2105','...');

# 以下三个学⽣信息 属于 class_id=1 的班级 （Java2104）
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210101','张三','男',20,1);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210102','李四','⼥',20,1);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210103','王五','男',20,1);
# 以下三个学⽣信息 属于 class_id=2 的班级 （Java2105）
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210104','赵明','⼥',20,2);
insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
values('20210105','孙紫','男',20,2);
# ⼩红和⼩明没有设置班级信息
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('20210106','⼩红','⼥',20);
insert into students(stu_num,stu_name,stu_gender,stu_age)
values('20210107','⼩明','男',20);

内连接 INNER JOIN

## 语法
select ... from tableName1 inner join tableName2 ON 匹配条件 [where 条件];

内连接条件 – 使⽤ on 设置两张表连接查询的匹配条件

## 使⽤where设置过滤条件：先生成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
select * from students INNER JOIN classes where students.cid=classes.class_id;
## 使⽤ON设置连接查询条件：先判断连接条件是否成⽴，如果成⽴两张表的数据进⾏组合⽣成⼀条结果记录
select * from students INNER JOIN classes ON students.cid=classes.class_id;
## 结果：只获取两种表中匹配条件成⽴的数据，任何⼀张表在另⼀种表如果没有找到对应匹配则不会出现在查询结果中（例如：⼩红和⼩明没有对应的班级信息，Java2106和Python2106没有对应的学⽣）。

左连接 LEFT JOIN

左连接：显示左表中的所有数据，如果在有右表中存在与左表记录满⾜匹配条件的数据，则进⾏匹配；如果右表中不存在匹配数据，则显示为Null

# 语法
select * from leftTable LEFT JOIN rightTable ON 匹配条件 [where 条件];

## 左连接 ：显示左表中的所有记录
select * from students LEFT JOIN classes ON students.cid = classes.class_id;

右链接 RIGHT JOIN

## 右连接 ：显示右表中的所有记录
select * from students RIGHT JOIN classes ON students.cid = classes.class_id;

数据表别名

如果在连接查询的多张表中存在相同名字的字段，我们可以使⽤表名.字段名来进⾏区分，如果表名太⻓则不便于SQL语句的编写，我们可以使⽤数据表别名

select s.*,c.class_name
from students s
INNER JOIN classes c
ON s.cid = c.class_id;

子查询/嵌套查询

子查询先进行一次查询，第一次查询的结果作为第二次查询的源/条件（第二次查询是基于第一次的查询结果来进行的）

⼦查询返回单个值单⾏单列

普通查询

## a.查询Java2104班的班级编号
select class_id from classes where class_name='Java2104';
## b.查询此班级编号下的学⽣信息
select * from students where cid = 1;

子查询

## 如果⼦查询返回的结果是⼀个值（单列单⾏），条件可以直接使⽤关系运算符（= != ....）
select * from students where cid = (select class_id from classes where
class_name='Java2105');

⼦查询返回多个值多⾏单列

子查询

## 如果⼦查询返回的结果是多个值（单列多⾏），条件使⽤IN / NOT IN
select * from students where cid IN (select class_id from classes where class_name LIKE 'Java%');

⼦查询返回多个值多⾏多列

查询cid=1的班级中性别为男的学⽣信息

## 多条件查询：
select * from students where cid=1 and stu_gender='男';
## ⼦查询:先查询cid=1班级中的所有学⽣信息，将这些信息作为⼀个整体虚拟表(多⾏多列)
	### 再基于这个虚拟表查询性别为男的学⽣信息（‘虚拟表’需要别名）
select * from (select * from students where cid=1) t where
t.stu_gender='男';

更多参考：
https://blog.csdn.net/weixin_45851945/article/details/114287877
https://www.runoob.com/mysql/mysql-tutorial.html文章来源地址https://www.toymoban.com/news/detail-415233.html

到了这里，关于MySQL 增删改查语法记录快速复习的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！