DML数据操作语句和基本的DQL语句

一、MySQL对数据的增删改查

1.DML语句

1.1 增加数据(INSERT)

insert into 表名 (字段名,字段名,...字段名)
values/value (值,值,...值)

1.1.1  新增数据的具体实现

（1）全字段的插入

方式一：

        insert into student (sid,sname,birthday,ssex,classid)  values(9,'苏一','2007-1-12','男',1);

方式二：null default

        insert into student values(null,'十个勤天','2022-10-1','男',2);
        insert into student values (default,'十个勤天','2022-10-1','男',2);

（2）部分字段插入

        insert into student(sname,ssex) values('鹭卓','男');

        insert into student(sname) values('卓沅');

（3）一次性插入数据

方式一   最常用的方式
        -- insert into 表名 (字段名..) values(值..),(值..)..

        insert into student (sname,ssex)
        values('赵一博','男'),('何浩楠','男'),('王一珩','男');

方式二 不常用了解
-- insert into select 
-- 插入表和被插入表都必须存在
create table newstu(
    xingming varchar(10),
    xingbie  varchar(10),
    classid int
);

insert into newstu(xingming,xingbie,classid)
select sname,ssex,classid from student;

# MDL
# 新增
-- insert into 表名 (字段名,字段名,...字段名)
-- values/value (值,值,...值)

# 全字段的插入
# 日期 使用字符串的形式进行书写日期格式（yyyy-MM-dd HH:mm:ss)
-- 方式一
insert into student (sid,sname,birthday,ssex,classid)
values(9,'苏一','2007-1-12','男',1);
-- 方式二  1.null 2.default
insert into student values(null,'十个勤天','2022-10-1','男',2);
insert into student values (default,'十个勤天','2022-10-1','男',2);

-- 部分字段插入
insert into student(sname,ssex) values('鹭卓','女');

alter table student modify ssex varchar(10) not null default '保密';

insert into student(sname) values('卓沅');

-- 一次性插入数据
-- 方式一   最常用的方式
-- insert into 表名 (字段名..) values(值..),(值..)..
insert into student (sname,ssex)
values('赵一博','男'),('何浩楠','男'),('王一珩','男');

-- 方式二 不常用了解
-- insert into select 
-- 插入表和被插入表都必须存在
create table newstu(
xingming varchar(10),
xingbie  varchar(10),
classid int
);

insert into newstu(xingming,xingbie,classid)
select sname,ssex,classid from student;

-- 方式三
-- create table SELECT
-- 被插入表不能存在 -- 被插入表没有任何约束
create table stu1 select sid,sname,birthday from student;

1.2  修改数据(UPDATE)

1.2.1 修改数据的具体体现

update 表名 set 字段名=值,字段名=值....字段名=值;
【where 子句】 条件
注：-- where 子句 中的条件是对表中每一条数据进行判断，
-- 判断成立该条数据的父句去执行，
-- 判断不成立该数据的父句不执行

# MDL
-- 修改
-- update 表名 set 字段名=值,字段名=值....字段名=值;
-- 【where 子句】 条件
-- where 子句 中的条件是对表中每一条数据进行判断，
-- 判断成立该条数据的父句去执行，
-- 判断不成立该数据的父句不执行

update stu1 set birthday='2024-07-20' where sname='何浩楠';
update stu1 set birthday='2024-07-20' where sname='十个勤天';

-- 不等于 != <>
update newstu set classid=200 where xingbie !='男';-- 不等于!=
update newstu set classid=300 where xingbie <>'男'; -- 不等于<>

-- 小于 <
update newstu set xingbie='保密' where classid < 250;

-- 区间 and连接
update newstu set xingbie='外星人' where classid >=30 and classid <= 90;

-- 30 50 70 性别变为地球人
update newstu set xingbie='地球人' 
where classid = 30 or classid=50 or classid=70;

-- 下面俩条件是等价的
update newstu set xingbie='火星人' 
where classid >= 30 and classid<=90;


update newstu set xingbie='水星人' 
where classid between 30 and 90;

1.3 删除数据(DELETE) 

delete from 表名 【where 子句】

 1.3.1 删除数据的具体实现

#MDL
-- 删除
-- delete from 表名 【where 子句】
delete from stu1 where sname='周梅';

-- 清空表，截断表
-- truncate 表名 
-- truncate 不仅删数据，还删掉了索引
-- drop 不仅删数据，删索引，表结构也删了
truncate stu1;


-- delete仅仅删数据
delete from studet;
insert into student(sname) values('小美');

truncate studet;

二、DQL语言

1.概念

DQL(Data Query Language 数据查询语言)。用途是查询数据库数据，如SELECT语句。是SQL语句 中最核心、最重要的语句，也是使用频率最高的语句。其中，可以根据表的结构和关系分为单表查询和多 表联查。

2.分类

（1）单表查询

针对数据库中的一张数据表进行查询，可以通过各 种查询条件和方式去做相关的优化。

（2）多表查询

针对数据库中两张或者两张以上的表同时进行查询， 依赖的手段有复杂查询和嵌套查询。

3.查询语句语法规则

DISTINCT

设定DISTINCT可以去掉重复记录。

AS

表名或者字段名过长时，可以用AS关键字起别名，方便操作。

GROUP BY

按组分类显示查询出的数据。

HAVING

GROUP BY分组时依赖的分组条件。

ORDER BY

将查询出来的结果集按照一定顺序排序完成。

LIMIT

限制显示查询结果的条数。

3.1 查询

#DQL
-- 所有的查询都会得到一张虚拟表
-- 最简单的查询
select 123;
select 'abc';
select 1+1;

-- 从表中获取数据
-- select 字段名,字段名 from 表名
-- 不建议使用星号代替字段名

-- 全字段查询
select sid,sname,birthday,ssex,classid from student;

select * from student; 

-- 部分字段查询
select sname,ssex from student;

3.2  给字段名起别名AS  (三种都可以)

• 字段名   as   '别名'
• 字段名   ‘别名’
• 字段名   别名

-- 给字段名起别名(三种都可以)
select sname as '姓名',birthday '生日', ssex 性别 from student;

3.3 添加字段

-- 添加字段
select sname,'猿究院' 学校 from student;

3.4 去重  distinct

-- 所有字段的数据要一致才会去重
select distinct ssex from student;

-- 所有字段的数据要一致才会去重
select distinct ssex from student;
select distinct sid,sname,ssex from student;

3.5  在某个特定的范围  in

-- in 可以使用到索引（常用）
select * from student
where sid in (3,5,7,9,200,300);

-- in 在某个特定的范围
3 5 7 9

-- in 可以使用到索引（常用）
select * from student
where sid in (3,5,7,9,200,300);

-- or 会让索引失败
select * from student
where sid =3 or sid=5 or sid=7 or sid=9 or sid=10;

3.6 模糊查询 like

-- 模糊符号
 % 任意多的任意字符，0也算任意多字符
 _ 一个任意字符

-- like 模糊查询
-- 模糊符号
-- % 任意多的任意字符，0也算任意多字符
-- _ 一个任意字符
insert into student(sname)
values('赵靓'),('赵小童'),('小童'),('赵帅哥'),('帅气的赵同学');

select *from student where sname like '%赵%'    -- 跟赵有关的
select *from student where sname like '%赵'    -- 最后一个字是赵的
select *from student where sname like '赵%'  -- 姓赵

select *from student where sname like '赵_';  -- 赵某
select *from student where sname like '赵__';    -- 赵某某
select *from student where sname like '赵___';    -- 赵某某某

3.7  分组 group by

-- 分组 group by
-- 男女同学各有多少人
select ssex,count(1) from student group by ssex;

-- 统计各班有多少人
select classid,count(*) from student group by classid;

-- 统计成绩表每个同学的总分和平均分
select sid,sum(score),avg(score) from sc group by sid;

-- 查询出平均分不及格的学生（where 后面不能跟聚合函数）
select sid,sum(score),avg(score) from sc 
group by sid
having avg(score) < 60;

3.8 排序 orde  by

先写先排

• 升序 asc 不写(默认)
• 降序 desc 必须声明

-- order by 排序
-- 先写先排
-- 升序 asc 不写(默认)
-- 降序 desc 必须声明

select * from student order by classid desc;-- 降序
select * from sc order by score desc,cid desc;-- 课程降序
select * from student order by classid asc;

3.9  分页 limit 

limit 分页 0 开始 (页码-1)*步长，步长

SELECT 字段1，字段2...字段n FROM 表 WHERE 条件 LIMIT ( 当前页码 - 1 ) * 页面容量 ， 页面容量
select * from student limit 位置,步长
select * from student limit 0,3;  -- 第一行开始，到第三行

 

-- limit 分页 0 开始 (页码-1)*步长，步长
-- select * from student limit 位置,步长
select * from student limit 0,3;    --第一行元素开始，到第三行
select * from student limit 3,3;    -- 第四行开始，到第六行
select * from student limit 6,3;    -- 地七行开始，到第九行


-- 应用层解决
-- select * from student limit (3-1)*3,3 错误的

3.10 NULL值查询

select * from 表名 字段 is NULL | is not NULL

其中：

• NULL代表“无值”；

• 区别于零值0和空符串；

• 只能出现在定义允许为NULL的字段；

• 须使用 IS NULL 或 IS NOT NULL 比较操作符去比较

4. WHERE条件子句

 WHERE子句的功能？

• 依赖逻辑条件对数据库的记录修改，删除或者查询

-- 带条件的查询
-- 【where 子句】
select * from student where sid=5;  -- id为5
select * from student where sid <> 5;  -- id不为5
select * from student where sid > 5;   -- id大于5
select * from student where sid between 3 and 6;  -- id在3和6之间

-- 查找1班的女同学
-- 多个条件and连接
select * from student;
select * from student where classid =1 and ssex='女';

-- 面试题
-- 查询年龄大于1990-1-1的学生
-- 日期本质是一个数
select * from student where birthday < '1990-01-01';

5.  聚合函数（重要）

聚合函数  非常重要  ----  把多个值变为一个值

count() 统计个数
max()  求最大值

min()  求最小值
sum()  求总和
avg()  求平均值

-- COUNT() 任何类型 不统计NULL
-- select count(字段\常量\*) from 表名;
select count(sid) from student;-- 用的主键则没有问题。
select count(classid) from student;-- 不统计空NULL的。

select count(123) from student; -- 推荐！写多少无所谓，一般写1
select count(*) from student;-- 推荐
select count('a') from student;-- 不推荐使用

-- sum avg min max 数值类型
select sum(score) from sc;
select avg(score) from sc;
select max(score) from sc;
select min(score) from sc;

-- 统计出成绩表中一共有多少次考试，总成绩是多少，平均分，最高分，最低分
select * from sc;
select count(cid),sum(score),avg(score),max(score),min(score) from sc;

面试常问？

1.在MySQL中，delete、deop、truncate的区别？

#MDL
-- 删除
-- delete from 表名 【where 子句】
delete from stu1 where sname='周梅';

-- 清空表，截断表
-- truncate 表名 
-- truncate 不仅删数据，还删掉了索引
-- drop 不仅删数据，删索引，表结构也删了
truncate stu1;


-- delete仅仅删数据
delete from studet;
insert into student(sname) values('小美');

truncate studet;

 2.-- 面试题      查询年龄大于1990-1-1的学生

-- 日期本质是一个数
select * from student where birthday < '1990-01-01';

3.-- 找到成绩及格的总分数排名第二的：sid总成绩

select sid,sum(score) from sc where score >= 60 group by sid order by sum(score) desc limit 1,1;

4.TRUNCATE语句和DELETE语句的异同？

• 相同点：都能删除数据，都不能修改表结构；

• 不同点：前者会重置自增计数器，后者不会；

                前者无条件约束，速度快效率高。

原文地址：https://blog.csdn.net/weixin_65978343/article/details/140644332

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！