MySQL ——数据的增删改查

一、DML语言

1.1 insert插入数据

语法：insert [into] 表名 [字段名] values(值列表)；

插入一行数据

第一种：insert into file1(id,name,age) values (1,'aa',11);

第二种：insert into file1 values(1,'aa',11);

插入多行数据

insert into file1(id,name,age) values(1,'aa',11), (2,'bb',12),(3,'cc',13);

注意：为避免表结构发生变化引发错误，建议插入数据时写明具体字段名！

1.2 update 更新数据

语法：UPDATE 表名 SET 字段1=值1,字段2=值2,…,字段n=值n [WHERE 条件];

-- 示例：将id = 1的age改成14
update file1 set age = 14 where id =1;

注意：1、更新多列数据使用逗号隔开；2、添加条件限制

1.3 delete 删除数据

语法格式：delete from 表名 [where条件];

-- 示例：删除file1中的数据【表还在】
delete from file1;

-- 示例：删除id=1的数据
delete from file1 where id =1;

1.4 truncate 删除数据

语法：truncate table 表名;

-- 示例：删除学生表中的数据【清空数据，表还在】
truncate table students;

-- 示例：删除id是1的学生的数据【报错，不能加条件】
truncate table students where id = 1;

二、DQL语言

DQL是Data Qurey Language英文缩写，数据查询语言

常用语句：

基本查询语句、条件查询、多条件查询、模糊查询、分组查询、连接查询、子查询

关键字：

AS、运算符、IN、BETWEEN AND、DISTINCT、ORDER BY、LIMIT

条件：

模糊查询like ’字符‘关键字查询【具体值】in关键字查询【范围】between 值1 and 值2关键字查询【去重】distinct关键字查询【顺序】order by 排列列名 【asc升序，desc降序】根据行数查询limit

2.1 select基本查询语句

语法：select 列名 from 表名；

示例：查询所有学生信息

select *
from students;

例题：查询所有学生的姓名，性别

select name,sex
from students;

2.2 运算符

算术运算符

• • • / %

关系运算符

< = <> >= <= !=

赋值运算符

=

逻辑运算符

and or not (&& || !)

2.3 where条件查询

语法：select 列名 from 表名 where 条件

示例：查询id为1学生的姓名，性别

select name,sex
from students
where id =1;

例题：查询学生'邓超'的信息

select *
from students
where name = '邓超';

2.4 where多条件查询

语法：select 列名 from 表名 where 条件 运算符 条件

示例：查询id=1或者id=3学生的姓名，性别、

select name,sex
from students
where id = 1 or id = 3;

例题：查询性别为女并且在2班的学生的信息

select *
from students
where sex = '女' and cls_id = 2;

2.5 like 模糊查询

模糊查询是使用SQL 通配符替代一个或多个字符的条件查询。

语法：select 列名 from 表名 where 字段 like '值'

例题：查询名字里面包含'小'的学生的信息

select * from students where name like '小%'；

2.6 in关键字查询 是什么什么

语法：select 列名 from 表名 where 字段 in (值1，值2，……)；

示例：查询id为1,,5,6,10的学生的信息

select *
from students
where id in (1,5,6,10);

not in 不是什么什么

示例：查询id不为1,,5,6,10的学生的信息

select *
from students
where id not in(1,5,6,10);

2.7 between关键字查询 在什么什么之内

语法：select 列名 from 表名 where 字段 between 值1 and 值2；

示例：查询id为8-10的学生的信息

select *
from students
where id between 8 and 10;

2.8 distinct 关键字查询【去重】

语法: select distinct 字段名1，字段名2，…… from 表名；

示例：查询性别有几种分类

select distinct sex from students;

示例：查询有几个班级

select distinct cls_id from students;

2.9 order by关键字查询

语法：select 字段名列表 from 表名 [where 查询条件] [order by 排序的列名][asc(升序) 或 desc(降序)]

示例：将学生的身高按照升序排列

select * 
from students
order by height; -- 默认为升序 

示例：将学生的身高按照降序排列

select *
from students
order by height desc;

2.10 limit关键字查询

语法：

select 字段名列表

from 表名

[where 查询条件]

[order by 排序的列名 asc(升序) 或 desc(降序)]

[LIMIT <开始行数，需要查的行数> ];

如果开始行数不写默认为0

示例：只看前2条学生信息

select *
from students
limit 2;

示例：查看第四行到第七行的学生信息

select *
from students
limit 4,3;

三、连接查询

连接查询是将多张表中记录按照指定的条件进行连接的查询方式

注意：连接查询涉及到两个表以上，在查询的时候至少要有一个必备的连接条件，这个必备的条件就是两个表共有的那个字段相等，而且这个字段一定在一个表里是主键，在另一个表里是外健

3.1 内连接

内连接是返回连接表中符合连接条件记录的连接查询。

包括：显式内连接、隐式内连接

3.1.1 显示连接

语法格式：

select 字段 from 表1 inner join 表2 on 连接条件[where 条件]

示例：查看学生所在班级

select s.name as '学生姓名',c.name as '班级名称' -- as可以省略
from students s inner join classes c
on s.cls_id = c.id;

提问：查看学生所在班级并且班级是1

select s.name '学生姓名',c.name '教室名'
from students s inner join classes c
on s.cls_id = c.id and c.id = 1;

3.1.2 隐式内连接查询

select 字段 from 表1，表2 where 表1.条件 = 表2.条件

示例：查看学生所在班级

select s.name, c.id
from students s, classes c
where s.cls_id = c.id;

3.2 外连接

3.2.1 左外连接查询

左外连接是以左表为基表，返回左表中所有记录及连接表中符合条件的记录的外连接。

select 字段 from 表1 left join 表2 on 连接条件 [where 条件]

示例：查看老师所在班级

select t.name '教师姓名' ， c.id '班级名'
from teachers t left join classes c
on t.id = c.teacher_id;

3.2.1 右外连接查询

右外连接是以右表为基表，返回右表中所有记录及连接表中符合条件的记录的外连接

语法：select 字段 from 表1 right join 表2 on 连接条件 where 条件

select t.name '教室名称',c.id '班级名称'
from teachers t right join classes c
on t.id = c.teacher_id;

3.3 聚合函数

如何查看班级同学的平均身高？——使用聚合函数

聚合函数是可以对一组值进行计算，并返回单个值的函数。

语法：select 聚合函数<字段> from 表名 [where 条件][group by 聚合函数]

count()
计数sum()求和max()最大值min()最小值avg()平均值
示例：查询班级学生的平均身高

select avg(height)
from students;

示例：查询班级有多少同学

select count(*) -- 这里也可以用id
from students

3.4 子查询【查询嵌套】

如何只查询比刘德华高的同一班的学生信息？——子查询

定义:子查询是在一个查询的内部包括另一个查询的查询方式

3.4.1 简单子查询

示例：查看刘德华同学的所在班级的所有同学

select name
from students
where cls_id = (select cls_id from students where name ='刘德华');

3.4.2 any/some子查询

示例：查看赵老师所带的学生信息

select *
from students
where cls_id = any(select id from classes where teacher = 
select id from teachers where name = '赵老师'));

3.5 all子查询

• ALL子查询的关键在于对所有子查询返回值的比较。例如，当使用> ALL时，意味着主查询中的表达式必须大于子查询返回的所有值，才能满足条件。

示例：查看学生所在班级

select *
from students
where cls_id >=all(select id from classes where teacher_id = 
(select id from teachers where name = '赵老师'));

3.6 exists子查询

示例：删除表

drop table if exists file1;

示例：查看存在王老师的班级表

select *
from classes 
where exists (select * from teachers where name='王老师');

3.7 not exists子查询

示例：创建教师表

create table IF NOT EXISTS teachers(
    id int primary key,
    name varchar(20)
);

避免重复创建