MYSQL数据库详解（3） --DML数据操作语句&基本的DQL语句

DML数据操作语言

新增

– insert into 表名(字段名,字段名,…,字段名) values(值,值,…,值)
– 日期类型的值 用字符串的形式来表示

1.全字段插入

1）方式1：不写字段

insert into class values(1,'2307班');
insert into class values(2,'2308班');

2）方式2：全部字段

insert into student(sid,sname,birthday,ssex,classid) values(1,'张三','1998-1-1','男',2);

3）插入默认值

insert into student values(default,'李四','2023-1-1','女',1);

2. 选择字段插入
   – 非空字段必须设置默认值，否则报错

   insert into student(sname) values(‘刘备’);

1）一次性插入多条数据
– 方式1：（最常用的）

insert into student(sname,birthday) values('大宝','2023-1-1'),('二宝','2023-4-4'),('三宝','2023-5-5');

– 方式2
– 插入的表要存在，查询的表也要存在

create table stu1(
    name varchar(10),
    sex char(1)
)
insert into stu1 select sname,ssex from student;

– 方式3
– 查询表要存在,插入的表一定不存在

create table stu2 select sid,sname,ssex,classid from student;

修改

1.where 子句 先执行

update stu1 set name='烟十一'; （没有条件修改全部数据）
update stu2 set sname='烟十一' where sid = 1;
alter table stu2 modify ssex varchar(10);
update stu2 set ssex= '猛男' where ssex != '女';

2. 范围型

– 方式1：

update stu2 set ssex= '帅哥' where sid >=2 and sid<=5;

– 方式2：

update stu2 set ssex = '靓女' where sid between 2 and 5;

– 错误逻辑（between 后面的数值较小，and后面的值较大

update stu2 set ssex ='靓女' where sid between 8 and 2;

– 1,3,4,6,8

update stu2 set ssex = '靓女' where sid=1 or sid=3 or sid =4 or sid =6 or sid =8;

删除

– delete from 表名 【where 条件】

delete from stu1;（没有条件删除全部数据）
delete from stu2 where sid=1;
delete from stu2 where ssex = '猛男';
delete from stu2 where sid between 1 and 5;

清空表

truncate stu2;

– 面试题 ： delete truncate（截断） drop

计算列（MySql8 新特性）

简单来说就是某一列的值是通过别的列计算得来的。在MySQL 8.0中,CREATE TABLE和 ALTER TABLE 中都支持增加计算列。

CREATE TABLE jsltab(
    id INT PRIMARY KEY AUTO_INCREMENT**,
    a INT ,
    b INT ,
    c INT GENERATED ALWAYS AS (a + b) VIRTUAL
)

数据库的备份和还原

MySQL数据库恢复的方法

一、使用工具
1.还原：
1）先建库
2）在新建的库右键 【允许SQL文件】

2.备份：
1）在已有的库右键 【转储SQL文件】
2）选择路径后进行保存，会保存为xxx.sql 的文件

二、控制台恢复
控制台备份时，依赖的是MySQL自带的备份工具mysqldump，该工具的功能是转储数据库，将数据转移到另一个SQL数据库，当然并不局限于MySQL数据库。

1.还原
“dbname”表示需要恢复的数据库，
“/path/db_name.sql”表示所恢复数据库的备份文件绝对路径

mysql –h localhost -P 3306 –u root –p dbname < /path/db_name.sql;

2.备份

mysqldump -h localhost –P 3306 –u root –p dbname > /path/db_name.sql

数据库备份的价值

DQL 数据库查询

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

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

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

– 所有的查询都会得到一个虚拟表

select 1;
select 'hello';
select 2+3;

单表查询

全字段查询

1.select sid,sname,birthday,ssex,classid from student;

不推荐，*代替字段（需要查询两次）

2.select * from student;

选择字段查询

select sname,ssex,birthday from student;

字段起别名

select sname as '姓名',ssex '性别',birthday 生日 from student;

添加一个常量字段

select sname as '姓名',ssex '性别',birthday 生日,'猿究院' 学校 from student;

去除重复

– distinct 修饰的字段的值一致时去除重复

select distinct sid,sname,ssex from student;

带条件的查询

– 查询班级编号=1，并且性别为男

select * from student where classid=1 and ssex='男';

– 查询sid在3-6之间的数据

SELECT * from student where sid >=3 and sid <=6;
SELECT * from student where sid between 3 and 6;

– 查询出学生表中1990-1-1前出生的学生

select * from student where birthday <'1990-1-1'

like 模糊查询

模糊符号
– % 任意多的任意字符
– _ 一个任意字符

select * from student where sname like '%侯%'
select * from student where sname like '侯_'

in 某个特定范围

select * from student where sid in(1,3,5,7,8)

IS 是（主要用来判断是否是null）对null的处理

select * from student where birthday is null
select * from student where birthday is not null

聚合函数

count() – 个数 不统计null（常量 ，字段 ，*）

select count(sname) from student
select count(sid) from student
select count(1) from student
select count(*) from student

sum() – 求和

select sum(score) from sc where sid =3

avg() – 平均值

select avg(score) from sc where sid = 2

max() – 最大值

select max(score) from sc

min() – 最小值

select min(score) from sc

– 总考试次数，总分，平均分，最高分，最低分
select count(*),sum(score),avg(score),max(score),min(score) from sc

分组

select ssex 性别,count(*) 人数 from student sc group by ssex

– 每个班有多少人

select * from student
select classid 班级人数,count(*) from student group by classid

– 平均分

select sid,avg(score) from sc group by sid

– 及格的总成绩

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

– 总分> 100的

having 对分组聚合后的结果集进行筛选

select sid,sum(score) from sc group by sid having sum(score)>200

排序 order by

asc 升序
desc 降序

select * from sc order by score desc, cid asc

分页 limit

limit 确定的常量（Java逻辑中计算 （页码-1）*步长,步长）
– limit 位置0开始,步长

select * from student limit 0,3

– 找到考试成绩总分排名第二的学生

select sid,sum(score) from sc group by sid order by sum(score) desc limit 1,1

偏移量 OFFSET （MySql8新关键词）

SELECT * FROM 表名 limit m offset n

n 表示第一条记录的偏移量，m 表示显示记录的数量；