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

DML语句

 增加数据（INSERT语句）

INSERT INTO `表名` [(`字段1`,`字段2`,...`字段n`)]

VALUES/VALUE ('值1','值2',...'值n')[,('值1','值2',...'值n')...];

其中： •“[]” 包含的内容可以省略；

• 字段或值之间用英文逗号隔开；

• 可同时插入多条数据，values 后用英文逗号隔开；

• values和value的方式均可。

增加数据（INSERT INTO SELECT ）

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。 目标表中任何已存在的行都不会受影响。

INSERT INTO table2 (column_name,...)

SELECT column_name,... FROM table1;

-- 新增
-- insert into 表名 (字段名,字段名,...,字段名) 
-- values/value(值,值,...,值)
-- 日期 使用字符串的形式进行书写日期格式（yyyy-MM-dd HH:mm）
-- 全字段的插入
-- 方式一
insert into student (sid,sname,birthday,ssex,classid)
value(9,'张三','2007-1-2','男',1);

-- 方式二 1.null 
insert into student values(null,'苏乞儿','1978-8-4','男',2);
insert into student values(default,'潇洒哥','1989-10-4','男',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;

修改数据（UPDATE语句） 

UPDATE 表名 SET `字段名1` = '值1' [ , `字段名2` = '值2', …. ] [ WHERE 条件];

•“[]” 包含的内容可以省略；

• `字段名`为要更改数据的字段，'值'表示字段改后的值，注意字段和值的对应；

• 可同时修改多个字段，多个字段后用英文逗号隔开；

•“WHERE”是where子句，可以给修改增加条件；

• 条件：为筛选条件，如不指定则修改该表的所有列数据。

-- 修改
-- update 表名 set 字段名=值,字段名=值,...,字段名=值
update stu1 set birthday='1998-11-23' 

 WHERE子句

 有条件的从数据库表中获取记录，通常同在修改，删除，和查询语句的时候，协助该类语句从条件中获取 记录。针对修改和删除语句，如果没有条件，则全部修改和全部删除。

-- [where 子句条件]
-- where 子句 中的条件是对表中每一条数据进行判断
-- 判断该成立该数据的父句执行，判断不成立该数据父句不执行
update stu1 set birthday='1998-11-23' 
where sname='潇洒哥';

update newstu set classid = 200 where xingbie != '男';
update newstu set classid = 300 where xingbie <> '女';

update newstu set xingbie = '保密' where classid < 260;
update newstu set xingbie = '外星人' where classid >= 30 and classid <= 90;

update newstu set xingbie='地球人' where classid=30 or classid=50 or classid=70;

update newstu set xingbie = '火星人' where classid >= 30 and classid <= 60;

update newstu set xingbie = '水星人' where classid between  60 and 100;

 删除数据（DELETE语句）

DELETE FROM 表名 [ WHERE 条件];  

其中： •“[]” 包含的内容可以省略；

             • “WHERE”可以通过where子句增加删除的条件。

-- 删除
-- delete from 表名 [where 子句]
delete from newstu 
delete from stu1 where sname = '潇洒哥';

 删除数据（TRUNCATE语句）

 TRUNCATE [TABLE] 表名

其中： • TRUNCATE是一个特殊的删除语句，又叫做清空语句；

           • “[]”包含的内容可以省略；

           • 功能：清空某一张表内的全部数据，重置自增计数器；

           • 特点：由于没有条件约束，所以速度快,而且效率高。

-- 清空表 （截断表）
-- truncate 表名 不能加条件
truncate stu1;

delete from student;
insert into student(sname) values('小明');

truncate student;

 delete、truncate、drop的区别
-- delete  只删数据
-- truncate 不仅把数据删掉，还删除了索引
-- drop 不仅把数据删掉，还删除了索引，还把表结构也删了

单 表 查 询

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

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

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

查询语句语法规则 

•“[ ]”包含的内容可以省略；

•“{ }”包含的内容必须存在；

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

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

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

-- 查询出从平均分不及格的学生
-- having 对分组聚合后的数据进行条件筛选（重点）
-- where 子句 中的条件是对表中每一条数据进行判断
select sid,avg(score) ,sum(score) from sc group by sid having avg(score)<60
select sid,avg(score) ,sum(score) where score<60 from sc group by sid having avg(score)<60

 指定字段列

SELECT * | 字段名1, 字段名2... FROM 表名

其中： •“*”表示所查询的数据库表的全部字段。

-- DQL数据查询（重点）

-- 所有查询都会得到一张虚拟表（查过的这张虚拟表不再会变）
-- 最简单的查询
select 123;
select 'abc';
select 1+1; 

-- 从表中获取数据
-- select 字段名，字段名 from student 表名
-- 全字段查询
select sid,sname,birthday,ssex,classid from student;
select * from student; -- 不使用*来代替字段名，因为*先查询所有的数据，查找效率慢

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

 表别名和字段别名

 SELECT 表别名.字段名1 AS 字段别名1, 表别名.字段名2 AS 字段别名2

FROM 表名 AS 表别名

其中： • “.” 当前表存在的字段；

           • “AS”可忽略不写，“AS”的功能如下：

           • 给字段取一个新别名；

           • 给表取一个新别名；

           • 把经计算或总结的结果用另外一个新名称来代替。

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

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

 DISTINCT去重

SELECT DISTINCT 字段名1, 字段名2... FROM 表名

其中： •去掉SELECT查询返回的记录结果中重复的记录（所有返回列的值都相同）， 只返回一条。

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

WHERE条件子句

SELECT * FROM 表名[ WHERE条件];

其中：

•WHERE条件子句不是必须的；

•WHERE子句，可以给查询增加条件；

•条件：为筛选条件，如不指定则修改该表的所有数据。

-- [where 子句条件]
-- where 子句 中的条件是对表中每一条数据进行判断
-- 判断该成立该数据的父句执行，判断不成立该数据父句不执行
update stu1 set birthday='1998-11-23' 
where sname='潇洒哥';

update newstu set classid = 200 where xingbie != '男';
update newstu set classid = 300 where xingbie <> '女';

update newstu set xingbie = '保密' where classid < 260;
update newstu set xingbie = '外星人' where classid >= 30 and classid <= 90;

update newstu set xingbie='地球人' where classid=30 or classid=50 or classid=70;

update newstu set xingbie = '火星人' where classid >= 30 and classid <= 60;

update newstu set xingbie = '水星人' where classid between  60 and 100;

-- 查询出从平均分不及格的学生
-- having 对分组聚合后的数据进行条件筛选（重点）
-- where 子句 中的条件是对表中每一条数据进行判断
select sid,avg(score) ,sum(score) from sc group by sid having avg(score)<60
select sid,avg(score) ,sum(score) where score<60 from sc group by sid having avg(score)<60

LIKE 关键字

SELECT * FROM 表名 WHERE字段 LIKE条件;

其中：

•在WHERE子句中，使用LIKE关键字进行模糊查询；

•与“%”一起使用，表示匹配0或任意多个字符；

•与“_”一起使用，表示匹配单个字符。

-- like 模糊查询
-- 模糊符号 
-- %	任意多的任意字符
-- _	表示一个任意字符
insert into student(sname) value('张甜甜'),('甜甜'),('甜甜甜'),('田甜'),('甜儿');
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 '甜__'

IN 关键字

SELECT * FROM 表名 WHERE 字段 IN (值1，值2...);

其中： • 查询的字段的值，至少与IN 后的括号中的一个值相同；

           • 多个值之间用英文逗号隔开。

-- in在某个特定的范围
-- 3 5 7 9
select * from student where sid=3 or sid=5 or sid=7 or sid=9; -- or会让索引失效

-- 推荐使用
select * from student where sid in(3,5,7,9); -- in可以使用到索引

NULL 值查询

SELECT * FROM 表名 WHERE 字段 IS NULL | IS NOT NULL

其中： • NULL代表“无值”；

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

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

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

-- NULL
-- is 是一个什么
select * from student where birthday is null 
select * from student where birthday is  not null 

GROUP BY和HAVING分组

GROUP BY

1、对所有的数据进行分组统计；

2、分组的依据字段可以有多个，并 依次分组。

HAVING

与GROUP BY结合使用，进行分组 后的数据筛选。

-- 查询出从平均分不及格的学生
-- having 对分组聚合后的数据进行条件筛选（重点）
-- where 子句 中的条件是对表中每一条数据进行判断
select sid,avg(score) ,sum(score) from sc group by sid having avg(score)<60
select sid,avg(score) ,sum(score) where score<60 from sc group by sid having avg(score)<60

ORDER BY排序

SELECT * FROM 表名 ORDER BY 字段名 [DESC|ASC]

其中： • ORDER BY 表示对SELECT语句查询得到的结果，按字段名进行排序；

           • DESC表示排序的顺序为降序，ASC表示排序的顺序为升序；

           •“[ ]”包含的内容可以省略。

-- order by 排序
-- 先写先排
-- 升序 asc 不写（默认）
-- 降序 desc 必须写
select * from student order by classid desc;
select * from student order by classid asc;
select * from sc order by score desc,cid desc;-- 先按分数降序排再按课程排

LIMIT实现分页显示的方式

-- limit 分页 0 开始 （页码-1）*步长，步长
-- select * from student limit 位置，步长
select * from student limit 0,3
select * from student limit 3
select * from student limit 6,3
-- 应用层解决
select * from student limit (3-1)*3,3 -- 错误的

-- 找到成绩及格的总分数排名第二的学生的sid和总成绩
select sid,sum(score) from sc where score>=60 group by sid order by sum(score) desc limit 1,1

MySql8新关键词OFFSET

SELECT * FROM 表名 limit m offset n

其中：

• LIMIT关键字是MySQL特有关键字；

• LIMIT限制SELECT返回结果的行数；

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

•“[ ]”包含的内容可以省略。

常用的聚合函数 

函数名	返回值
AVG(col)	返回指定列的平均值
COUNT(col)	返回指定列中非NULL值的个数
MIN(col)	返回指定列的最小值
MAX(col)	返回指定列的最大值
SUM(col)	返回指定列的所有值之和

-- 聚合函数（重点）
-- 把多个值变成一个值
-- count()	统计个数
-- max()	求最大值
-- min()	求最小值
-- sum()	求和
-- avg()	求平均值

-- count()	任何类型 不统计null(重点)
-- select count(字段\常量\*) from 表名 
select count(sid) from student; -- 主键
select count(classid) from student; -- 不统计null
select count('a') from student; -- 不推荐
select count(123) from student; -- 推荐
select count(*) 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 count(*),sum(score),avg(score),max(score),min(score),min(score) from sc