mysql学习笔记04

Charlotte384

已于 2024-01-28 20:48:00 修改

阅读量612

点赞数 17

文章标签： mysql 学习笔记

于 2024-01-28 16:47:23 首次发布

本文链接：https://blog.csdn.net/qq_68289621/article/details/135897232

版权

DQL：数据库查询语言

关键字：select ....from

格式：

select 查询的列

from 表名或者图名

[where<条件表达式>]

[group by<列名>]

[having<条件表达式>]

[order by<列名> [asc|desc]]

[LIMIT<数字或者列表>]；

简化：

select *|列名 from 表名 where 条件

DQL基本查询

准备工作：准备数据库和表和数据

-- 创建数据库
create database test02;
use test02;
-- 创建商品表
create table product(
pid int primary key auto_increment,
    pname varchar(20) not null,
    price double,
    category_id varchar(20)
);
-- 添加数据
insert into product values(null,'海尔洗衣机',5000,'c001'),
                          (null,'美的冰箱',3000,'c001'),
                          (null,'格力空调',5000,'c001'),
                          (null,'九阳电饭煲',5000,'c001'),
                          (null,'啄木鸟衬衣',300,'c002'),
                          (null,'恒源祥西裤',800,'c002'),
                          (null,'花花公子夹克',440,'c002'),
                          (null,'劲霸休闲裤',266,'c002'),
                          (null,'海澜之家卫衣',180,'c002'),
                          (null,'杰克琼斯运动裤',430,'c002'),
                           (null,'兰黛面霜',300,'c003'),
                          (null,'雅诗兰黛精华水',200,'c003'),
                          (null,'香奈儿香水',350,'c003'),
                          (null,'sk-ll神仙水',350,'c003'),
                          (null,'资生堂粉底液',180,'c003'),
                          (null,'老北京方便面',56,'c004'),
                          (null,'良品铺子海带丝',17,'c004'),
                          (null,'三只松鼠坚果',88,null);

简单查询

-- 查询所有的商品:select * from 表
select * from product;
-- 查询商品名和商品价格
select pname,price from product;
-- 别名查询：关键词as可以省略
   -- 表别名：多表查询常用
   select * from product as p;
   -- 列别名
   select price as '价格' from product;
-- 全部列去除重复项
select distinct * from product;
-- 个别列去除重复
select distinct price from product;

-- 需要查询结果是运算的:将价格提高10块钱
select pname, price+10 from product;

运算符查询

mysql支持的运算符：算术运算符，逻辑运算符，比较运算符，位运算符

算术运算符：

逻辑运算符：

比较运算符:

位运算：二进制使用，| or 或，& and 与，not ! 非,xor异或

use test02;
select  2+2； -- 4
select  2-1 ；-- 1
select 6*2; -- 12 
select 6%4; -- 2
  -- 将使用商品价格提升10%
select price*1.1 as new_prine from product;
-- 查询商品名称”海尔洗衣机“的商品所有信息
select * from product where pname = '海尔洗衣机';
-- 查询价格为800的所有商品信息
select * from product where price =800;
-- 查询价格不是800的使用商品信息
select * from product where price != 800;
select * from product where price <>800;
-- 查询价格大于600的所有商品信息
select * from product where price >600;
-- 查询价格在200到1000之间的所有商品
select pname from product where price between 200 and 1000;
select pname from product where price >=200 and price <= 1000;
-- 查询价格是200或者800的商品
select pname from product where price in (200,800);
select pname from product where price = 200 or price =800;
-- 查询含有‘鞋’字的所有商品信息
select * from product where pname like "%鞋%";
-- 查询以'海’开头的所有商品信息
select * from product where pname like "海%";
-- 查询第二个字为‘寇‘的所有商品信息
select * from product where pname like '_海%'
-- 查询category_id为null的商品信息
select * from product where category_id is null;
-- 查询category_id不为null的商品
select * from product where category_id is not  null;
-- 使用least()求最小值，有null就不比了，结果就是null
select least(1,2,3); -- 1
select least(1,null,3); -- null
-- 使用greatest()求最大值,有null就不比了，结果就是null
select greatest(1,2,3);
select GREATEST(1,null,3); -- null

排序查询

关键字：order by 字段1 [asc|desc] , 字段2 [asc|desc].....

asc 升序,desc降序，不写默认为升序

字段可以是数字，可以是字符串，字符串按字母表排序

字段1是首要条件，字段2是次要排序条件，只有字段1相同再按后面字段排序

-- 将商品按价格降序排序
select * from product order by price desc;
-- 在价格降序的基础上，以分类降序排序
select * from product order by price desc,category_id desc;
-- 显示商品价格（去重复），并排序（降序）
select distinct price as new_price  from product order by price desc;

聚合查询

聚合操作针对某一列，对行值进行处理

-- count()：count(*)统计使用记录条数，count（某单字段）统计除null以外的记录行数
-- count(1)等价于count(*)
-- sum():某一列求和，忽略null值
-- max()：求某列最大值，忽略null值
-- min()：求某列最小值，忽略null值
-- avg()：求列的平均值，忽略null值

分组查询

关键字：group by ,having

 -- select 字段....  from 表名  group by 分组条件  having  对分组后在筛选
 -- 将product 表用category 分组，查询各个分组下有多少商品
select  category_id,count(*) from product group by category_id;
 -- 将product 表用category 分组，查询各个分组下有商品（保留商品数量大于4）
select category_id,count(*) as cnt 
from product group by  category_id having  cnt >4;

分页查询

关键字：limit m,n;

-- select 字段....  from 表名 limit m,n
-- m表示从m-1开始索引，n表示从m开始向后索引n条记录
-- m,n为整数,m第一个索引为0，不写默认为0
-- 查询前五条数据
select * from product limit 0,5；
select * from product limit 5；
-- 查询第四条显示往后八条数据
select * from product limit 3,8;
-- 假设有大量数据，每一页有60条数据，查询第一条m-1开始全部记录
select * from product limit 0,60; -- 从第一条为m-1，往后60条记录
-- 查询第二页60条记录
select * from product limit 60,60;
-- 查询第n页的全部60条数据
select * from product limit (n-1)*60,60;

insert into select语句

作用：将查询到的数据插入到另一张表，前提是另一张表要存在

-- 将product 表用category 分组，查询各个分组下有多少商品的数据插入到product1表中
create table product1(
category varchar(20),
num int
);
insert into product1 select category_id ,count(*)from product group by category_id;

DQL练习

-- 练习1
use test;
create table student(
id int,
name varchar(20),
gender varchar(20),
Chinese int,
English int,
Math int
);
insert into student values(1,'张明','男',89,78,90);
insert into student values(2,'李进','男',67,53,95);
insert into student values(3,'王五','女',87,78,77);
insert into student values(4,'李一','女',88,98,92);
insert into student values(5,'李财','男',82,84,67);
insert into student values(6,'张宝','男',55,85,45);
insert into student values(7,'黄蓉','女',75,65,30);
insert into student values(7,'黄蓉','女',75,65,30); 
-- 查询表中所有学生信息
select * from student;
-- 查询表中所有学生姓名和英语成绩
select name,English from student;
-- 过滤表中重复数据
select distinct * from student;
-- 统计每个学生的总分
select name, (Chinese+English+Math) as total_score from student;
-- 在所有学生分数上加10分特长分
select name, (Chinese+English+Math+10) as total_score from student;
-- 使用别名表示学生分数
select name '姓名', Chinese '语文',English '英语', Math '数学' from student;
-- 查询英语成绩大于90分的同学
select name from student where English > 90;
-- 查询总分大于200分的同学
select name from student where (Chinese+English+Math)>200;
-- 查询英语成绩在80-90之间的学生
select name from student where English >= 80 && English <=90;
-- 查询英语不在80-90之间的同学
select name from student where not(English >= 80 && English <=90);
-- 查询数学成绩分数在89，90，91同学
select name from student where Math in (89,90,91);
-- 查询所有姓李的同学的英语成绩
select 	English from student where name like '李%';
-- 查询数学成绩80且语文80
select name from student where Math = 80 and Chinese = 80;
-- 查询英语80或者总分大于200学生姓名和总分·
select name,(Chinese+Math+English) as total from student where English = 80 or (Chinese+Math+English) > 200;
-- 对数学成绩降序排序
SELECT * from student order by Math desc;
-- 对总分排序后输出降序,输出姓名和总分
select name , (Chinese+Math+English) as '总分' from student order by (Chinese+Math+English) desc;
-- 对姓李的学生按总成绩降序排序
select name , (Chinese+Math+English) as '总分' from student  where name like '李%' order by (Chinese+Math+English) desc;
-- 查询男生女生各有多少人，并将人数排序输出
select gender,count(name) as num from student group by gender;
-- 对上一条大于4的性别和人数输出
-- 由于人数一样，修改一条数据
update student set gender = '女' where id = 6;
select gender,count(name) as num from student group by gender having num >4;
-- 练习2
use test;
create table emp(
empno int,
ename varchar(50),
job varchar(50),
mgr int ,-- 上级领导编号
hiredate date, -- 入职日期
sal int, -- 工资
comm int , -- 奖金
deptno int -- 部门编号
);

insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7449,'allen','salemen',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salemen',7698,'1981-2-22',1250,500,30);
insert into emp values(7566,'jones','manager',7835,'1981-4-02',2975,null,20);

insert into emp values(7654,'martin','salemen',7698,'1981-9-28',1250,1400,30);
insert into emp values(7698,'black','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clerk','manager',7839,'1981-6-09',2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-4-19',3000,null,20);

insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'trner','salemen',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'1987-5-23',1100, null,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);

insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'mtller','clerk',7782,'1982-01-23',1300,null,10);
-- 按员工编号升序排列不在10号部门的员工信息
select * from emp where deptno != 10 order by empno;
-- 查询姓名第二个字不是“a”且sal大于1000的员工信息，按年薪降序排序
-- ifnull(a，b）如果a为null,就当作b,如果不为null,a就是自己的值
-- ifnul(comm,0)
select * from emp where ename not like '_a%' and sal > 1000 order by (sal*12 + ifnull(comm,0)) desc;
-- 求每个部门的平均薪水
select deptno,avg(sal) from emp group by deptno;
-- 求各个部门的最高薪水
select deptno,max(sal) from emp group by deptno;
-- 求每个部门每个岗位的最高薪水
select deptno,job,max(sal) from emp group by deptno,job;
-- 求平均薪水大于2000的部门编号
select deptno from emp group by deptno having avg(sal)>2000;
-- 将部门平均薪水大于1500的部门编号列出来，按平均薪水降序排序
select deptno from emp group by deptno having avg(sal)>1500 order by avg(sal) desc;
-- 查询公司中有奖金的员工的姓名，工资
select ename,sal from emp where comm is not null and comm !=0;
-- 查询寻员工最高工资和最低工资
select max(sal),min(sal) from emp;