当前位置：网站首页>数据库的增删改查

数据库的增删改查

2022-07-17 20:41:00 【蓝鲸不蓝369】

文章目录

DDL

在这里插入图片描述

DDL操作表

在这里插入图片描述

DML

添加数据

在这里插入图片描述

修改数据

在这里插入图片描述

DQL查询

基础查询

在这里插入图片描述

drop table if exists stu;
select *from stu
create table stu(
id int,
name varchar(5),
age int,
sex varchar(5),
address varchar(10),
math double(5,2),
english double(5,2),
hire_date date
);
alter table stu add address varchar(10);
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,'2001-01-11'),
(4,'陆佰',20,'女','湖南',76,65,'1997-09-05'),
(5,'刘青',20,'男','湖北',86,NULL,'1998-09-01'),
(6,'刘德华',45,'男','香港',33,56,'1998-09-11'),
(7,'张学友',22,'女','香港',99,99,'1997-02-11'),
(8,'德玛西亚',18,'男','南京',67,76,'1994-09-02');
select name,age from stu;
select distinct address from stu;
select name ,math as 数学成绩,english as 英语成绩 from stu;

条件查询

在这里插入图片描述

模糊查询

在这里插入图片描述

drop table if exists stu;
select *from stu
create table stu(
id int,
name varchar(5),
age int,
sex varchar(5),
address varchar(10),
math double(5,2),
english double(5,2),
hire_date date
);
alter table stu add address varchar(10);
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,'2001-01-11'),
(4,'陆佰',20,'女','湖南',76,65,'1997-09-05'),
(5,'刘青',20,'男','湖北',86,NULL,'1998-09-01'),
(6,'刘德华',45,'男','香港',33,56,'1998-09-11'),
(7,'张学友',22,'女','香港',99,99,'1997-02-11'),
(8,'德玛西亚',18,'男','南京',67,76,'1994-09-02');
select name,age from stu;
select distinct address from stu;
select name ,math as 数学成绩,english as 英语成绩 from stu;
select *from stu where age>=20;
select *from stu where age>20;
select* from stu where age>20&&age<30;
select *from stu where hire_date BETWEEN '1998-01-01' and '2022-02-01';
select*from stu where age in(18,19,20);
select*from stu where english is NULL;
select*from stu where english is not null;
select*from stu where name like '马%';
select*from stu where name like'_斯%';
select*from stu where name like'%刘%';

排序查询

在这里插入图片描述

分组查询

在这里插入图片描述

-- 查询学生信息按升序排列
select *from stu order by age asc;
-- 查询学生信息按数学成绩降序排列.
select*from stu order by math desc,english desc;
select count(id) from stu;
select count(english)from stu;
select min(math) from stu;
select sum(math) from stu;
select avg(math) from stu;
select min(english) from stu;

在这里插入图片描述

-- 查询学生信息按升序排列
select sex,avg(math)from stu GROUP BY sex;
select sex,avg(math),count(*) from stu group by sex;
select sex,avg(math),count(*) from stu where math>70 group by sex;
select sex,avg(math),count(*) from stu where math>70 group by sex having count(*)>1;