当前位置:Gxlcms > 数据库问题 > mysql-查询

mysql-查询

时间:2021-07-01 10:21:17 帮助过:7人阅读

select查询语句:
语法:
(
    select field_name ...
    from tb_name
    where 查询条件
    group by field_name ...   having 过滤条件  (分组查询和分组查询过滤)
    order by field_name asc(升序) desc(降序)   (查询结果排序)
    limit
)

wherehaving 都有条件判断的功能(过滤)
区别:
    where 是在分组之前进行过滤
    haing 是在分组之后进行过滤


1、条件查询:
select field_name ... from tb_name ... where field_name>values;  返回field_name 大于 values 的记录
select field_name ... from tb_name ... where field_name in(value1,value2, ... );  返回 field_name 等于 value 中的一个的记录。
select field_name ... from tb_name ... where field_name not in(value1,value2 ...);  返回field_name 不在其中的记录。
select field_name ... from tb_name ... where field_name between value1 and value2; 返回field_name 的value 在value1 和 value2 之间的记录。
select field_name ... from tb_name ... where field_name not between value1 and value2; 返回field_name 的value 不在value1 和value2 之间的记录。


2、模糊查询:
select field_name ... from tb_name ... where field_name like a%b/a_b;  其中:% 表示多个任意字符,_ 表示一个任意字符。(查询field_name以a开头,以b结尾的所有记录)

3、空值查询:
select field_name ... from tb_name ... where field_name is null;

eg:
select * from massege where 总分 between 550 and 600;
select * from massege where 总分 not between 500 and 600;
select * from massege where 总分 like 5%;
select * from massege where 总分 like __5;

4、查询结果去重:distinct
select distinct field_name from tb_name;

5、分组查询:
select field_name as total from tb_name group by field_name;

select age,count(2) as姓名 from stu_temp group by age;   count(1)/count(*) 都是统计数据的

select age,group_concat(name) as姓名 from stu_temp group by age; group_concat(field_name)显示该组中的全部field_name。

select age,group_concat(name) as姓名 from stu_temp group by age having count(name)>3;  having count(field_name)>values 过滤掉count(field_name)<values 的分组,只显示大于的分组。


select age,count(2) as姓名 from stu_temp group by age with rollup;  rollup 统计归纳。

6、分页查询:









create table stu_temp(
id int(10) unsigned primary key auto_increment,
name varchar(20) not null,
age int(10) not null
);

insert into stu_temp values(null,qqq,19),(null,www,20),(null,eee,18),(null,aaa,19)
,(null,sss,20),(null,ddd,18),(null,zzz,20),(null,xxx,19),(null,ccc,20),(null,vvv,18);


insert into product values(null,qqq,20170601),(null,www,20170602),(null,eee,20170603),(null,aaa,20170601),(null,qqq,20170601);

 

 

 

子查询:

笛卡尔积:两个表的所有行组合以后构成的结果集。

交叉查询
select *
from employee
cross join department;

等价于:
select * 
from employee,department;

 

 

内连接查询 :返回符合 on 后面的查询条件的rows

inner join (内连接):基于联合条件进行的多表查询。匹配多个表中的行,并且允许你去查询在两个表中都包含的列的行。它被直接用在from的后面
inner join 的使用条件:
                    1、在from 后面指定主表
                    2、指定和主表连接的表,理论上,你可以联合多个表,但是为了更好的查询,应该限制表的个数
                    3、指定联合条件,联合条件应该在 inner join  的关键字 on 的后面。联合条件值匹配多表的规则。 
SELECT employee.`name` as 姓名 ,department.`name` as 部门
FROM employee
INNER JOIN department on department.id=employee.dep_id
WHERE department.id=4;

SELECT e.`name` as 姓名 ,d.`name` as 部门     这里是利用 as 为表其了别名(as 可以省略)
FROM employee as e
INNER JOIN department as d on d.id=e.dep_id
WHERE d.id=4;

 

 

左外连接查询(left [outer] join):返回左表的所有rows和右表中与左表符合查询条件的所有rows

select e.`name` as 姓名,d.name as 年龄
from employee as e
left join department as d on e.dep_id=d.id;

 

 

右外连接查询(right [outer] join):返回右表的所有rows和左表中与右表符合查询条件的所有rows

select e.`name` as 姓名,d.name as 年龄
from employee as e
right join department as d on e.dep_id=d.id;

 

 

全外连接查询(full [outer] join):返回 

select e.`name` as 姓名,d.name as 年龄
from employee as e
full join department as d on e.dep_id=d.id;
但:mysql 不支持 full [outer] join 语法,使用下列语法代替全外连接
(既:将左外连接和右外连接用 union 合并,就是 mysql 的全外连接)
select *
from `employee`
left join `department` on `employee`.dep_id=`department`.id
union
select * 
from `employee`
right join `department` on `employee`.dep_id=`department`.id;

 

mysql-查询

标签:部门   style   多表   signed   限制   sse   null   distinct   数据   

人气教程排行