【超详细】操作数据（DML、DQL）添加&修改&删除

r囧r小猫 2024-03-30 13:42 62阅读 0赞

**目录**

一、DML（Data Manipulation Language)

1.添加数据

2.修改数据

3.删除数据

二、DQL（Data Query Language)

1.基础查询

2. 条件查询（where）

3. 排序查询（order by）

4. 聚合查询

5. 分组查询（group by）

6. 分页查询(limit)

--------------------

## 一、DML**（Data Manipulation Language)** ##

### 1.添加数据 ###

> 1.  给指定列添加数据
>     
>         insert into 表名(列名1,列名2...) values (值1,值2...);
> 2.  给全部列添加数据
>     
>         insert into 表名 values (值1，值2，...);
> 3.  批量添加数据
>     
>         insert into 表名(列名1,列名2,...) values(值1,值2,...),(值1，值2，...)...;
>     
>         insert into 表名 values（值1,值2,...），(值1，值2，...)...;

-- 查询所有数据
    SELECT
    	* 
    FROM
    	stu;
    	
    -- 给指定列添加数据 insert into 表名 values (值1，值2，...);
    INSERT INTO stu ( id, NAME )
    VALUES
    	( 1, "张三" );
    	
    -- 给所有列添加数据,列名的列表可以省略（但建议不省略）
    INSERT INTO stu (
    	id,
    	NAME,
    	sex,
    	birthday,
    	score,
    	email,
    	tel,
    	STATUS 
    	)
    VALUES ( 2, "李四", "男", '1999-11-11', 88.88, "lisi@itcast.cn", "13466667777", 1 );
    
    INSERT INTO stu
    VALUES
    	( 2, "李四", "男", '1999-11-11', 88.88, "lisi@itcast.cn", "13466667777", 1 );
    	
    -- 批量添加数据
    INSERT INTO stu
    VALUES
    	( 2, "李四", "男", '1999-11-11', 88.88, "lisi@itcast.cn", "13466667777", 1 ),
    	( 2, "李四", "男", '1999-11-11', 88.88, "lisi@itcast.cn", "13466667777", 1 ),
    	( 2, "李四", "男", '1999-11-11', 88.88, "lisi@itcast.cn", "13466667777", 1 );

### 2.修改数据 ###

>     update 表名 set 列名1=值1,列名2=值2,...[where 条件];
> 
> **注意：修改语句中如果不加条件，则所有数据都修改！**

-- 修改数据 update 表名 set 列名1=值1,列名2=值2,...[where 条件];
    
    	-- 将张三的性别修改为女
    	update stu set sex="女" where name = "张三";
    	
      -- 将张三的生日改为 1999-12-12 分数改为99.99
    	update stu set birthday = '1999-12-12' ,score = 99.99 where name = "张三";
    
      -- 注意：修改语句中如果不加条件，则所有数据都修改！
    	update stu set sex="女" ;

### 3.删除数据 ###

>     delete from 表名 [where 条件];
> 
> **注意：删除语句中如果不加条件，则所有数据都删除！！！**

-- 删除数据 delete from 表名 [where 条件];
    
      -- 删除张三记录
    	
    	delete from stu where name = "张三" ;
    	
    	-- 注意：删除语句中如果不加条件，则所有数据都删除！
    	delete from stu;

## 二、DQL**（Data Query Language)** ##

**查询语法**

> **select**
> 
>  **字段列表**
> 
> **from**
> 
>  **表名列表**
> 
> **where**
> 
>  **条件列表**
> 
> **droup by**
> 
>  **分组列表**
> 
> **having**
> 
>  **分组后条件**
> 
> **order by** 
> 
>  **排序字段**
> 
> **limit**
> 
>  **分页限定**

**所用数据库：**

-- 删除stu表
    drop table if exists stu;
    
    
    -- 创建stu表
    CREATE TABLE stu (
     id int, -- 编号
     name varchar(20), -- 姓名
     age int, -- 年龄
     sex varchar(5), -- 性别
     address varchar(100), -- 地址
     math double(5,2), -- 数学成绩
     english double(5,2), -- 英语成绩
     hire_date date -- 入学时间
    );
    
    -- 添加数据
    INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) 
    VALUES 
    (1,'马运',55,'男','杭州',66,78,'1995-09-01'),
    (2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
    (3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
    (4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
    (5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
    (6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
    (7,'张学右',22,'女','香港',99,99,'1998-09-01'),
    (8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

### 1.基础查询 ###

> 1.  查询多个字段
>     
>         select 字段列表 from 表名;
>         select * from 表名; -- 查询所有数据
> 2.  去除重复记录
>     
>         select distinct 字段列表 from 表名;
> 3.  起别名
>     
>         As: -- As 也可以省略

-- 条件查询 =====================
    
    -- 1.查询年龄大于20岁的学员信息
    select * from stu where age > 20;
    
    -- 2.查询年龄大于等于20岁的学员信息
    select * from stu where age >= 20;
    
    -- 3.查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
    select * from stu where age >= 20 && age <= 30; 
    select * from stu where age >= 20 and age <= 30;-- 推荐使用
    select * from stu where age between 20 and 30;
    
    -- 4.查询入学日期在'1998-09-01' 到 '1999-09-01'  之间的学员信息
    select * from stu  where hire_date between '1998-09-01' and '1999-09-01';
    
    -- 5. 查询年龄等于18岁的学员信息
    select * from stu where age = 18;
    
    -- 6. 查询年龄不等于18岁的学员信息
    select * from stu where age != 18;
    select * from stu where age <> 18;
    
    -- 7. 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
    select * from stu where age = 18 or age = 20 or age = 22;
    select * from stu where age in (18,20,22);-- 集合
    
    -- 8. 查询英语成绩为 null的学员信息  
    -- 注意： null值的比较不能使用 = 、!= 。需要使用 is 或者 is not
    select * from stu where english = null;-- 错误写法
    select * from stu where english is null;
    select * from stu where english is not null;

### 2. 条件查询（where） ###

1.  条件查询语法
    
        select 字段列表 from 表名 where 条件列表;
2.  条件![1a73e86cc8324e51a2b16476d8670407.png][]
    
        -- 条件查询 =====================
        
        -- 1.查询年龄大于20岁的学员信息
        select * from stu where age > 20;
        
        -- 2.查询年龄大于等于20岁的学员信息
        select * from stu where age >= 20;
        
        -- 3.查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
        select * from stu where age >= 20 && age <= 30; 
        select * from stu where age >= 20 and age <= 30;-- 推荐使用
        select * from stu where age between 20 and 30;
        
        -- 4.查询入学日期在'1998-09-01' 到 '1999-09-01'  之间的学员信息
        select * from stu  where hire_date between '1998-09-01' and '1999-09-01';
        
        -- 5. 查询年龄等于18岁的学员信息
        select * from stu where age = 18;
        
        -- 6. 查询年龄不等于18岁的学员信息
        select * from stu where age != 18;
        select * from stu where age <> 18;
        
        -- 7. 查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
        select * from stu where age = 18 or age = 20 or age = 22;
        select * from stu where age in (18,20,22);-- 集合
        
        -- 8. 查询英语成绩为 null的学员信息  
        -- 注意： null值的比较不能使用 = 、!= 。需要使用 is 或者 is not
        select * from stu where english = null;-- 错误写法
        select * from stu where english is null;
        select * from stu where english is not null;
3.  模糊查询（like）
    
        -- 模糊查询 like =====================
        /*
        	通配符：
        	 (1)_:代表单个任意字符
        	 (2)%:代表任意个数字符
        */
        
        -- 1. 查询姓'马'的学员信息
        select * from stu where name like "马%";
        
        -- 2. 查询第二个字是'花'的学员信息   
        select * from stu where name like "_花%";
        
        -- 3. 查询名字中包含 '德' 的学员信息
        select * from stu where name like "%德%"; -- 较常用

### 3. 排序查询（order by） ###

select 字段列表 from 表名 order by 排序字段名1 [排序方式1],
                                      排序字段名2 [排序方式2]
                                      ...;

> 排序方式：
> 
> ASC：升序排序（默认）
> 
> DESC：降序排序

**注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序 ！！！**

/*
    	排序查询：
    		* 语法：SELECT 字段列表 FROM 表名  ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;
    		* 排序方式：
    				* ASC：升序排列（默认值）
    				* DESC：降序排列
    */
    
    -- 1.查询学生信息，按照年龄升序排列 
    select * from stu order by age asc;
    select * from stu order by age;
    
    -- 2.查询学生信息，按照数学成绩降序排列
    select * from stu order by math desc;
    
    -- 3.查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列(多字段排序)
    select * from stu order by math desc,english asc;

### 4. 聚合查询 ###

概念：将一列数据作为一个整体，进行纵向计算

> 聚合函数分类：![8650fb530bd34ce38738252577fe4697.png][]

> 聚合函数用法：
> 
>     select 聚合函数名（列名） from 表;
> 
> **注意：null值不参与所有聚合函数运算 !!!**

/* 
    	*聚合函数
    	*count：统计数量
    		*取值：
    			1.主键（非空且为1）
    			2.*
    	*max:求最大值
    	*min:求最小值
    	*sum:求和
    	*avg:求平均值
    */
    -- 1. 统计班级一共有多少个学生
    		select count(id) from stu;-- count 统计的列名不能为空，count不统计为null的项
    		
    -- 2. 查询数学成绩的最高分
    		select max(math) from stu;
    		
    -- 3. 查询数学成绩的最低分
    		select min(math) from stu;
    		
    -- 4. 查询数学成绩的平均分
    		select avg(math) from stu;
    		
    -- 5. 查询数学成绩的总分
    		select sum(math) from stu;
    		
    -- 6. 查询英语成绩的最低分
    		select min(english) from stu;-- null值不参与聚合函数的运算

###  ###

### 5. 分组查询（group by） ###

> 分组查询语法
> 
>     select 字段列表 from 表名 [where 分组前限定条件] group by 分组字段名称 [having 分组后条件过滤];
> 
> **注意：分组后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义！！！**

**where 和 having的区别：**

**①执行时机不同：where是在分组之前进行限定，不满足where的条件则不参与分组；而having是在分组之后对结果进行过滤。**

**② 可判断的条件不一样：where不能对聚合函数进行判断，而having可以。**

**执行顺序:where>聚合函数>having**

/*
    	分组函数
    			SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤]…;
    */
    
    -- 1. 查询男同学和女同学各自的数学平均分
    		select sex,avg(math) from stu group by  sex;
    	
    -- 注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
    	  select name,sex,avg(math) from stu group by  sex;
    		
    -- 2. 查询男同学和女同学各自的数学平均分，以及各自人数
    		select sex,avg(math),count(*) from stu group by  sex;
    
    -- 3. 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
    		select sex,avg(math),count(*) from stu where math > 70 group by sex;
    
    -- 4.查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的。
    		select sex,avg(math),count(*) from stu 
                                          where math > 70 
                                          group by sex 
                                          having count(*) > 2;

### 6. 分页查询(limit) ###

> 分页查询语法：
> 
>     select 字段列表 from 表名 limit 起始索引,查询条目数;

*  起始索引：从0开始

**计算公式：起始索引 = （当前页码 - 1） \* 每页显示的条数；**

> **tips：**
> 
>  *  分页查询limit是MySQL数据库的方言
>  *  Oracle分页查询使用的是rownumber
>  *  SQL Server分页查询使用的是top

/*
    	分页查询:
    
    			SELECT 字段列表 FROM 表名 LIMIT  起始索引 , 查询条目数
    				* 起始索引：从0开始
    
    */
    
    -- 1. 从0开始查询，查询3条数据
    		select * from stu limit 0 , 3;
    
    -- 2. 每页显示3条数据，查询第1页数据
    		select * from stu limit 0 , 3;
    		
    -- 3. 每页显示3条数据，查询第2页数据
    		select * from stu limit 3 , 3;
    		
    -- 4. 每页显示3条数据，查询第3页数据
    		select * from stu limit 6 , 3;
    
    -- 起始索引 = （当前页码 - 1） * 每页显示的条数

[1a73e86cc8324e51a2b16476d8670407.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/1941a65623ef42fc99dc4ad9e68d1338.png
[8650fb530bd34ce38738252577fe4697.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/e7cc32288f8c4320898d9b2938a872e2.png