MySqL（三）数据操作语言DML和数据查询语言DQL详细讲解（练习，笔记，注意点）

4.数据操作语言DMLDML数据操作语言用来对数据库表中的数据进行增删改4.1 DML-添加数据INSERT给指定字段添加数据INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...);//insert into 表名 (字段名1,字段名2,...) values (值1,值2,...);给全部字段添加数据INSERT INTO 表名 VALUES (值1,值2,...);//insert into 表名 values (值1,值2,...);批量添加数据为当前表中给定字段批量添加数据 INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),...,(值1,值2,...);//为当前表中全部字段批量添加数据INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),...,(值1,值2,...);细节插入数据时指定的字段顺序需要与值的顺序是一一对应的字符串和日期型数据应该包含在单引号中插入的数据大小应该在字段的规定范围内4.2 DML-修改数据UPDATE 表名 SET 字段1值1,字段2值2,...[WHERE 条件];//update 表名 set 字段1值1,字段2值2,...[where 条件];注意如果没有加where条件会修改整张表的所有数据4.3 DML-删除数据DELETE FROM 表名 [WHERE 条件];//delete from 表名 [where 条件];细节DELETE如果没有指定条件会删除整张表的所有数据DELETE不能删除某一个字段的值可以使用UPDATEDELETE仅仅删除表中的数据DROP会把整张表和数据一起删除5.数据查询语言DQLDQL数据查询语言用来查询数据库中表的记录5.1 DQL-语法SELECT //select 字段列表 FROM //from 表名列表 WHERE //where 条件列表 GROUP BY // group by 分组字段列表 HAVING //having 分组后条件列表 ORDER BY //order by 排序字段列表 LIMIT //limit 分页参数5.2 DQL-基本查询1、查询多个字段SELECT 字段1,字段2,... FROM 表名;SELECT * FROM 表名; --*是通配符代表所有字段因为不够直观不建议使用2、设置别名SELECT 字段1 [AS 别名1],字段2 [AS 别名2]... FROM 表名;-- AS可以省略 //select workadress as 工作地址 from employees;3、去除重复记录SELECT DISTINCT 字段列表 FROM 表名;//select distinct 字段列表 from 表名5.3 DQL-条件查询SELECT 字段列表 FROM 表名 WHERE 条件列表;//select 字段列表 from 表名 where 条件列表;条件比较运算符说明大于大于等于小于小于等于等于或!不等于BETWEEN 最小值 AND 最大值在最小值和最大值之间包含最小值和最大值IN(...)在in之后的列表中的值多选一LIKE 占位符模糊匹配_匹配单个字符, %匹配任意个字符IS NULL是NULLIS NOT NULL不是NULL逻辑运算符说明AND 或 并且 (多个条件同时成立)OR 或 ||或者 (多个条件任意一个成立)NOT 或 !非 , 不是LIKE 后如果要表示单纯的_或%需要用到转义字符\like 模糊匹配一个‘_’表示一个元素一个‘%’表示多个元素-- ------------------DQL基本查询------------------ -- 1、查询指定字段 select name,workID,age from employees; select name,entrydate from employees; -- 2、查询所有字段 select * from employees; -- 3、查询所有员工的工作地址起别名 select workaddress as 工作地址 from employees; select workaddress 工作地址 from employees; -- 4、查询所有员工的上班地址不要重复 select distinct workaddress from employees; -- ------------------DQL条件查询--------------- select * from employees where age 88; select * from employees where age 20; select * from employees where age 20; select * from employees where IDcard is null; select * from employees where IDcard is not null; select * from employees where age ! 88; select * from employees where age 88; select * from employees where age 15 age20; select * from employees where age 15 and age20; select * from employees where age between 15 and 20; select * from employees where age 18 || age 20 || age 40; select * from employees where age in(18,20,40); select * from employees where age 18 or age 20 or age 40; -- 查询姓名为两个字的员工 用到like模糊匹配 __ select * from employees where name like __; -- 查询身份证号最后一位是X的员工信息 用到like % select * from employees where IDcard like %X; select * from employees where IDcard like _________________XOverridepublic ListLocation findByNameLike(String keyword) { ListLocation list new ArrayList(); String sql SELECT * FROM location WHERE name LIKE CONCAT(%, ?, %); try (Connection conn DBUtil.getConnection(); PreparedStatement pstmt conn.prepareStatement(sql)) { pstmt.setString(1, keyword); ResultSet rs pstmt.executeQuery(); while (rs.next()) { list.add(mapRow(rs));} } catch (SQLException e) { e.printStackTrace();}return list;}5.4 DQL-聚合函数聚合函数将一列数据作为一个整体进行纵向计算。SELECT 聚合函数(字段列表) FROM 表名;//select 聚合函数(字段列表) from 表名;常见聚合函数函数功能COUNT统计个数MAX最大值MIN最小值AVG平均值SUM求和注意NULL值不参与聚合函数的运算count(*)默认统计总行数-- --------------------聚合函数------------- -- 1、统计员工数量 select count(*) from employees; select count(id) from employees; select count(IDcard) from employees; -- null不参与聚合函数运算 select avg(age) from employees; select max(age) from employees; select min(age) from employees; -- 统计所有西安地区员工的年龄之和 select sum(age) from employees where workaddress 西安;5.5 DQL-分组查询SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后的过滤条件]; //select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件];WHERE和HAVING的区别WHERE和HAVING的区别执行时机不同WHERE是分组之前进行过滤不满足WHERE条件不参与分组而HAVING是分组之后对结果进行过滤判断条件不同WHERE不能对聚合函数进行判断而HAVING可以注意执行顺序: where 聚合函数 having分组之后查询的字段一般为聚合函数和分组字段查询其他字段无任何意义-- ------------------分组查询----------------- -- 1、根据性别分组统计男性员工 和 女性员工的数量 select gender,count(*) from employees group by gender; -- 2、根据性别分组统计男性员工 和 女性员工的平均年龄 select gender,avg(age) from employees group by gender; -- 3、查询年龄小于45的员工并根据工作地址分组获取员工数量大于等于3的工作地址 select workaddress,count(*) from employees where age 45 group by workaddress having count(*) 3; -- 注意聚合函数也可以设置别名 select workaddress,count(*) as address_count from employees where age 45 group by workaddress having address_count 3;5.6 DQL-排序查询SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2...; //select 字段列表 from 表名 order by 字段1 排序方式2,字段2 排序方式2...;排序方式ASC升序排列默认值DESC降序排列注意如果是多字段排序当第一个字段值相同时才会根据第二个字段进行排序-- -------------------排序查询-------------- select * from employees order by age ASC; select * from employees order by age; select * from employees order by entrydate desc; -- 根据年龄对公司的员工进行升序排序年龄相同再按照入职时间进行降序排序 select * from employees order by age asc, entrydate desc -- 4、查询所有年龄小于等于35岁员工的姓名和年龄并对查询结果按年龄升序排序如果年龄相同按入职时间降序排序。 select name,age from employees where age 35 order by age asc, entrydate desc;5.7 DQL-分页查询SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数; //select 字段列表 from 表名 limit 起始索引m,查询记录数;起始索引从0开始起始索引 查询页码 - 1* 每页显示记录数分页查询是数据库的方言不同的数据库有不同的实现MySQL中是LIMIT如果查询的是第一页数据起始索引可以省略直接简写为 limit n;-- --------------分页查询----------------- -- 1、查询第1页元仍是数据每页展示10条记录 select * from employees limit 0,10; select * from employees limit 10; -- 2、查询第2页员工数据每页展示10条记录 -------起始索引页码数-1* 页展示数 select * from employees limit 10,10;5.8 DQL-执行顺序5.9 DQL-练习-- -------------------DQL练习------------------- select * from employees where gender 女 and age in(20,21,22,23); -- 2、查询性别为 男并且年龄在20-40岁以内的姓名为三个字的员工; select * from employees where gender 男 and age between 20 and 40 and name like ___; select * from employees where gender 男 and (age between 20 and 40) and name like ___; -- 3、统计员工表中年龄小于60岁的男性员工和女性员工的数量 select gender,count(*) from employees where age 60 group by gender; -- 4、查询所有年龄小于等于35岁员工的姓名和年龄并对查询结果按年龄升序排序如果年龄相同按入职时间降序排序。 select name,age from employees where age 35 order by age asc, entrydate desc; -- 5、 查询性别为男且年龄在20-40岁以内的前5个员工信息查询结果按年龄升序排序年龄相同按入职时间升序排序。 select * from employees where gender 男 and (age between 20 and 40) order by age asc, entrydate asc limit 0,5; -- 6、 查询年龄大于15的员工的姓名年龄并根据年龄进行升序排序(验证DQL语句执行顺序) select e.name as ename,e.age as eage from employees as e where e.age 15 order by eage asc;