mysql语法之DQL操作详解

目录

• 简单查询
• 运算符查询
• 排序查询
• 聚合查询
• 分组查询
• 分页查询
• 一张表查询结果插入到另一张表
• sql语句分析
• dql小练习1
• dql小练习2
• 正则表达式
• 总结

dql（data query language），数据查询语言，主要是用来查询数据的，这也是sql中最重要的部分！

简单查询

#dql操作之基本查询

#创建数据库
create database if not exists mydb2;
#使用数据库
use mydb2;
#创建表
create table if not exists product(
		pid int primary key auto_increment,
		pname varchar(20) not null,
		price double,
		category_id varchar(20)
);
#添加数据 可以简写 但建议平常养成良好代码规范
insert into product(pid,pname,price,category_id)
						values(null,'海尔洗衣机',5000,'c001'),
									(null,'海尔洗衣机',3000,'c001'),
									(null,'格力空调',5000,'c001'),
									(null,'九阳电饭煲',5000,'c001');
									
#查询所有商品
select * from product;
select pid,pname,price,category_id from product;

#查询商品名和价格
select pname,price from product;

#别名查询 关键字是as 可省略
#表别名
select * from product as p;
#列别名
select pname as '商品名',price as '商品价格' from product;

#去掉重复值
#去除某一列重复的
select distinct price from product;
#去除某一行重复的
select distinct * from product;

#运算查询（查询结果是表达式）：可以将结果进行取别名
select pname,price+10 as now_price from product;

运算符查询

#运算符 （在哪查（表） 要什么（字段） 什么条件（条件））

#算术运算符
select pname,price+10 as now_price from product;
select pname,price*1.2 as now_price from product;

#比较运算符
#逻辑运算符

select * from product where pname = '海尔洗衣机';
select * from product where price <> 3000;
select * from product where price between 3000 and 5000;
select * from product where price in(3000,4000);

# %可以匹配任意字符 _可以匹配单个字符

select * from product where pname like '%洗衣机';
select * from product where pname like '__洗衣机%';

#null判断
select * from product where category_id is not null;

#使用least函数来求最小值 如果有一个是null 则不会进行比较 直接结果为null
select least(10,20,3); 

#使用greatest函数来求最大值
select greatest(28,35,21);

排序查询

#排序查询 对读取的数据进行排序 多个字段时按照从前往后依次排序 默认asc升序 desc是降序
select * from product order by price;
select * from product order by price desc;

select * from product order by price desc,pname asc;

#去重排序
select distinct price from product order by price;

聚合查询

#聚合查询 聚合函数 以前的查询是以行为单位 聚合查询是以列为单位的 

#常见聚合查询函数是对null视而不见的 count(*) count(1) count(主键) 三个一样

#一般聚合函数和分组一起用

#查询商品的总条目
#pid字段不为空的（建议主键）
select count(pid) from product;
#行不为空的
select count(*) from product;

#查询价格大于3000的商品总条目 先查大于3000的 再算总条目
select count(pid) from product where price >3000;

#查询价格总和
select sum(price) from product;

#聚合查询对null处理
#如果是整数的话可以在定义的时候设置default 0

分组查询

#分组查询 group by（group by后面的字段相同的放在一组）  
#首先是根据group by来进行分组 然后再对每组进行聚合查询 最后进行select得出结果
#分组之后 select后面只能写分组字段和聚合函数

#统计各个分类商品的个数
select category_id,count(pid) from product group by category_id;

#分组之后的条件筛选 having   from->where->group by->select->having
#where筛选from子句产生的行 group by筛选where子句 having用来筛选group by产生的结果

#统计各个分类商品的个数 并且筛选出大于等于3的
select category_id,count(pid) from product group by category_id having count(pid)>=3;

分页查询

#分页查询 limit 用于商品数量太大 故进行分页显示 下标为0奥
select * from product limit 5;
select * from product limit 3,2;

一张表查询结果插入到另一张表

#insert into select 将一张表的数据导入另一场存在的表中

create table product2(
	pname varchar(20),
	price double
);

insert into product2(pname,price) select pname,price from product where category_id = 'c001';

create table product3(
	category_id varchar(20),
	product_count int
);

insert into product3 select category_id,count(*) from product group by category_id;

可以先根据表结构分析要进行的操作是什么，对应的操作顺序是什么，先干什么，再干什么，整个分析来后，再去写对应的sql语言。虽然有很多简写方法，但是先建议写基础的，写熟悉了后再简化，基础阶段打基础为主。

sql语句分析

#sql书写顺序（基本不以人的意志为转移）

select category_id,count(pid) as cnt
	from product
		where price >1000 
			group by category_id
				having cnt > 3
					order by cnt
						limit 1;

#sql执行顺序（帮助分析）

from->where->group by->count->having->order by->limit

dql小练习1

基础一定打牢固，重要不在于答案，在于分析答案的过程！

#dql操作练习

use mydb2;

create table if not exists student(
	id int,
	name varchar(20),
	gender varchar(20),
	chinese int,
	english int,
	math int
);
#最后一个字段定义后不要加,并且字段名和类型之间是用空格隔开

insert into student(id,name,gender,chinese,english,math)
						values(1,'张明','男',89,78,90),
									(2,'李进','男',67,53,95),
									(3,'王五','女',87,78,77),
									(4,'李一','女',88,98,92),
									(5,'李财','男',82,84,67),
									(6,'张宝','男',55,85,45),
									(7,'黄蓉','女',75,65,30),
									(7,'黄蓉','女',75,65,30);
									
#查询每个学生的总分（聚合查询是一整列运算 但是此处的是不同列相加）
select name,(chinese+english+math) as total_score from student;

#我一开始还想的group by id再进行相加 但是仔细想想select时候 
#本来就是一行一行的 所以这些字段就相当于在某一行的基础上的操作

#查询总分大于200的同学

#此处where (chinese+english+math)>200不能写成total_score>200 因为想想sql执行顺序 当where的时候都没有前面的select执行
select *,(chinese+english+math) as total_score from student where (chinese+english+math)>200;

#查询数学分数不为89 90 91的同学 in()表示是这几个数其中的某一个 以下两个均可
select name,math from student where math not in(89,90,91);
select name,math from student where not(math in(89,90,91));

#对姓李的同学总分降序排序 先是找到姓李的同学 再求总分并且降序排序
select * from student where name like '李%' order by (chinese+english+math) desc;

#查询男生女生各有多少人 并且将人数降序排序输出 
#按照顺序写的时候 select不知道写什么可以先写* 后面写了后再改前面的
select gender,count(id) from student group by gender order by count(id);

#查询男生女生各有多少人 并且将人数降序排序输出 并选择总数大于4的输出
#此处需要明白 这个总数大于4在分组后就已经开始筛选了 筛选后再排序！！ 
#先根据要求分析要进行什么操作 再看这些操作的依次顺序 最后再进行写sql语言 一步一查
select gender,count(id) from student group by gender having count(id) > 4 order by count(id);

dql小练习2

#dql操作练习

use mydb2;

create table if not exists 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),
											(7499,'allen','salesman',7698,'1981-02-20',1600,300,30),
											(7521,'ward','salesman',7698,'1981-02-22',1250,500,30),
											(7566,'jones','manager',7839,'1981-04-02',2975,null,20),
											(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30),
											(7698,'blake','manager',7839,'1981-06-09',2450,null,10);
										
#查询姓名第二个字母不是a且薪水大于1000的员工信息 按年薪降序排列 第二个字母不是a就是not like实现
#ifnull(sal,0) 如果sal为null则为0 否则是原来的数值
select * from emp where ename not like '_a%' && sal >1000 order by 12*sal+ifnull(comm,0) desc;

#求每一个部门的平均薪水 每一个部门 那就是分组查询 平均想到聚合函数
select deptno,avg(sal) from emp group by deptno;

#求每一个部门的最高薪水
select deptno,max(sal) from emp group by deptno;

#求每一个部门每一个岗位最高薪水 每一个部门 每一个岗位 分组字段是两个 不是两次分组两个group by
select deptno,job,max(sal) from emp group by deptno,job;

#看到最高 最低 平均就要想到聚合函数（对列操作）一般是一个数 加上分组其个数就是分组数

#查询最高和最低薪资的差距
select max(sal) - min(sal) from emp;

正则表达式

正则表达式是一套描述字符匹配的规则，mysql使用regexp关键字支持正则表达式进行字符串匹配。

符号	含义
^	匹配输入字符串的开始位置
$	匹配输入字符串的结束位置
.	匹配除’\n’之外的任何单个字符
[…]	匹配所包含的任意一个字符
[^…]	匹配未包含的任意字符
p1|p2|p3	匹配p1或者p2或者p3
*	匹配前面的子表达式0次或多次
+	匹配前面的子表达式1次或多次
?	匹配前面的子表达式1次或0次
{n}	匹配确定的n次
{n,}	匹配最少n次
{n,m}	匹配最少n次最多m次

#正则表达式查询（千万不能死记 用到什么回来查什么）

select 'abc' regexp '^a';
select 'abc' regexp 'c$';

#regexp 表达式 就是正则表达式 即需要匹配的字符串格式
select * from product where pname regexp '^海';

#.是任意除'\n'外的单个字符 '.b'表示任意字符+b
select 'abc' regexp '.b';

#表示是否存在字符在前面出现了
select 'abc' regexp '[xaz]';

#表示是否存在任意字符都没有在前面出现
select 'abc' regexp '[^abc]';

#一般是自动匹配*|+前面一个字符 想要多个就打括号

#a*表示匹配0个或者多个a包括空字符串
select 'stab' regexp '.ta*b';
select 'stb' regexp '.ta*b';

#a+表示匹配1个或者多个a不包括空字符串
select 'stab' regexp '.ta+b';
select 'stb' regexp '.ta+b';

#a?表示匹配0个或者1个a包括空字符串
select 'stab' regexp '.ta?b';
select 'staab' regexp '.ta?b';

select 'a' regexp 'a|b';
select 'c' regexp '^(a|b)';

select 'auuuuc' regexp 'au{4}c';

select 'auuuuc' regexp 'au{4,}c';

select 'auuuuc' regexp 'au{3,5}c';

到这里基础查询就结束啦，明天开始多表查询，由于都是我在navicat写的，然后再来写的csdn，故大部分我在学习中的注释也都写啦，重在分析过程！！！

总结

到此这篇关于mysql语法之dql操作详解的文章就介绍到这了,更多相关mysql dql语法内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！