03--DML与DQL语句

1、数据操作语言DML

1.1 添加数据

语法：

--语法
	insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
--举例
	insert into student(id,name,sex,birth) values (2,'李四','男','2006-06-08');

添加数据：

-- 添加数据 一一对应
insert into student(id,name,sex,birth,money) values (4,'赵六','男','2001-12-25',158.26);

-- 添加数据 省略主键
insert into student(name,sex,birth,money) values ('田七','男','2003-04-03',156);

-- 添加数据 指定字段
insert into student(name,sex,money) values ('苏珊','女',245);

-- 添加数据 省略字段,字段全写
insert into student values (10,'王玖','男','2001-12-25',158.26);

除了数字类型，其他类型需要使用引号(单双都可以)引起来

【建议大家都使用单引号 '' 不要使用双引号】sql语句拼接 sql语句在java代码中是一个String

批量插入数据

insert into student (id,name,sex) values
(7,'田丽','女'),
(8,'唐三','男'),
(9,'小舞','女');

复制添加（即将查询的表插入到当前表中）两个表的字段名一致

insert into teacher(name,sex) select name,sex from student where id > 5;

1.2 删除数据

语法

--语法
	delete from 表名 [where 条件] 
--举例
	delete from student where id = 10;

如果不加条件，则删除表中所有记录。

--删除所有的记录
	delete from student; 

如果要删除所有记录

delete from 表名;      -- 不推荐使用。有多少条记录就会执行多少次删除操作
truncate table 表名;   -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表

delete 和 truncate 有什么区别？

        delete 删除效率低，可以在事务中被回滚，删除的数据可以找到

        truncate 删除效率高，不能事务回滚，删除的数据不能找到。

1.3 修改数据

语法：

--语法
	update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
--举例
	update student set name = '小明' where id = 2;

如果不加任何条件，则会将表中所有记录全部修改。

update student set name = '小明';

2、数据查询语言DQL

2.1 查询完整语法

select															-- 5
    字段列表
from															-- 1
    表名列表
where															-- 2
    条件列表
group by														-- 3
    分组字段
having															-- 4
    分组之后的条件
order by														-- 6
    排序
limit
    分页限定

2.2 数据准备

建表

-- 创建表
CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` varchar(20) NOT NULL COMMENT '学生姓名',
  `chinese` double unsigned zerofill DEFAULT NULL COMMENT '语文成绩',
  `math` double unsigned zerofill DEFAULT NULL COMMENT '数学成绩',
  `english` double unsigned zerofill DEFAULT NULL COMMENT '英语成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

使用·Navicat15或者Navicat 16生成数据

2.3 简单查询

2.3.1 查询所有记录

查询表中所有学生和学生信息

-- 语法
	select * from 表名;
-- 举例
  select * from stu;	

2.3.2 查询部分字段

查询表中所有学生的姓名和对应的语文成绩

-- 语法
	select 字段名1，字段名2... from 表名;
-- 举例
	select name,chinese from stu;

查询学生和学生总成绩（字段可以参与数学运算(+、-、*、/、%)）

-- 查询学生和学生的总成绩
select name,chinese + math + english from stu;

2.4 条件查询

2.4.1 操作符

运算符	说明
> 、< 、<= 、>= 、= 、<> !=	<>和·!= 均为不等于
between……and	在一个范围之内[1000,3000]闭区间
in( 集合)	集合表示多个值，使用逗号分隔
is null 不为空 is not null	查询某一列为 NULL 的值，注：不能写=NULL
like	模糊查询 占位符: _:单个任意字符 %：多个任意字符
and 或 &&	与，SQL 中建议使用前者，后者并不通用。
or 或 ||	或
not 或 !	非

2.4.2 运算符的使用

-- 查询语文成绩大于90的学生
select name,chinese from stu where chinese > 90;

-- 查询语文成绩小于60的学生
select name,chinese from stu where chinese < 60;

-- 查询语文成绩等于99的学生
select name,chinese from stu where chinese = 99;

-- 查询语文成绩打大于等于80的学生
select name,chinese from stu where chinese >= 80;

-- 查询语文成绩打小于等于80的学生
select name,chinese from stu where chinese <= 80;

-- 查询语文成绩打不等于80的学生
select name,chinese from stu where chinese <> 80;
select name,chinese from stu where chinese != 80;

2.4.3 模糊查询

%代表任意多个字符，_代表任意1个字符。

-- 查询姓氏为张的学生信息
select * from stu where name like '张%';

-- 查询名字中含有子的学生信息
select * from stu where name like '%子%';

-- 查询名字中最后一个字为伦的学生信息
select * from stu where name like '%伦';

-- 查询名字为两个字，最后一个字为岚的学生信息
select * from stu where name like '_岚';

-- 找出名字中带有_的
select * from stu where name like '%\_%';

2.4.4 范围查询

between...and...是闭区间 [1100 ~ 3000]

between and在使用的时候必须左小右大。

between and除了可以使用在数字方面之外，还可以使用在字符串方面。左闭右开

-- 查询语文成绩在60-100之间的学生
select name,chinese from stu where chinese between 60 and 100;

2.4.5 空值查询

在数据库当中NULL不是一个值，代表什么也没有，为空。

空不是一个值，不能用等号衡量。

必须使用 is null或者is not null

-- 查询没有参加语文考试的学生
select * from stu where chinese is null;

-- 查询参加语文考试的学生
select * from stu where chinese is not null;

2.4.6 and 和 or

-- 查询语文成绩大于60分且姓秦的学生信息
select * from stu where chinese > 60 and name like '秦%';
select * from stu where chinese > 60 && name like '秦%';

-- 查询语文成绩大于60或者数学成绩大于90的学生
select * from stu where chinese > 60 or math > 90;
select * from stu where chinese > 60 || math > 90;

-- 测试and和or优先级
select * from stu where chinese > 90 or math > 60 and english > 90;
-- 查询结果为语文成绩大于90 或者 数学成绩大于60且英语成绩大于90的学生
-- and 优先级高于or
-- 使用时尽量加上()

-- !(非)的使用
select * from stu where !(chinese > 60 or math > 90);

2.4.7 in 和 not in

in后面的值不是区间，是具体的值。

-- 查询语文成绩等于100和99的学生
select * from stu where chinese in(100,99);
select * from stu where chinese = 100 or chinese  = 99;

-- 查询语文成绩不等于100和99的学生
select * from stu where chinese not in(100,99);
select * from stu where chinese <> 100 or chinese != 99;

2.5 去重操作

distinct只能出现在所有字段的最前面。

select distinct sex from student;

select distinct sex,name from student;

2.6 排序操作

注意：默认是升序。asc表示升序，desc表示降序。

按照语文成绩降序排列，当语文成绩相同的时候再按照数学成绩降序排列。越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

-- 根据语文成绩降序排序
select * from stu order by chinese desc;

-- 根据语文成绩降序排序
select * from stu order by chinese asc;
select * from stu order by chinese;

-- 查询语文成绩大于60的学生然后降序排序
select name,chinese from stu where chinese > 60 order by chinese desc;

-- 根据语文成绩降序排序，语文成绩相同根据数学成绩降序排序
select * from stu order by chinese desc,math desc;

2.7 Ifnull的使用

在进行运算时,null参与的运算，计算结果都为null

ifnull(参数1,参数2) 如果参数1有值，就走参数1 ，如果参数1为null，就走参数2

select name,ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) from stu;

2.8 分组操作

group by ： 按照某个字段或者某些字段进行分组。

having : having是对分组之后的数据进行再次过滤。

分组之后查询的字段：分组字段、聚合函数

where 和 having的区别？

• where 在分组之前进行过滤。having在分组之后进行过滤。
• where 后不可以跟聚合函数，having可以进行聚合函数。
• having 不能脱离 group by ，如果脱离group by 不能直接使用，一般与group by 一块使用

where: 操作的数据源: 原始表

having: 操作的数据源: 结果集

-- 查询所有的部门编号
select dept_id from emp group by dept_id;

-- 查询每个部门的员工人数
select dept_id,count(*) '人数' from emp group by dept_id;

-- 查询部门中人数大于10人的部门id和部门人数
select dept_id,count(*) '人数' from emp group by dept_id having count(*) > 10;

-- 查询各部门中工资大于5000的人数
select dept_id,count(*) '人数' from emp where salary > 5000 group by dept_id;

2.9 分页查询

limit offset,length;

• offset：起始行数，从 0 开始计数，如果省略，默认就是 0
• length：显示的条数

-- 查询前10位员工的信息
select * from emp limit 0,10;
select * from emp limit 10;

-- 查询工资最高的5位员工信息
select * from emp order by salary desc limit 0,5;

开始的索引 = （当前的页码 - 1） * 每页显示的条数

2.10 别名

as 的使用位置（as 可省略）

1. 可以用在某个字段上 name as 姓名

2. 可以用在函数上 min(sarlay) as 最低工资

3. 可以用在表上 from sutdent as s

注意事项: 如果给表起了别名,后面在使用的时候,必须要用别名

-- 把列的名换一下，起别名
select name,ifnull(chinese,0)+ifnull(math,0)+ifnull(english,0) '总分' from stu;

select id as '学号' from stu;
-- as 可以省略
select id '学号2' from stu;

-- 列别名
select id , name , chinese math from stu;

-- 表别名，作用： 如果有多个表连查，就用表名可以区分各自的字段
select s.id ,s.name ,s.chinese from stu s;

-- 直接表别名
select stu.id,stu.name,stu.chinese from stu;

-- 如果你指定了表别名，就一定要使用
select s.id ,s.name ,s.chinese from stu s;