java进阶|MySQL数据库系列（四）查询操作和多表关联查询

文章参考:https://blog.csdn.net/gaoweizang/article/details/52859449

  先讲述一下为什么在写这样的文章吧，由于好久好久之前一直在用MySQL这样的关系型数据库，对于sql的编写还是熟练操作的，后面项目慢慢用到了非关系型数据库Mongo以及内存级别数据库redis这样的数据库，导致mysql用的越来越少，以至于去写sql不是很熟练了，所以就有了这个系列的文章，学过的内容都还给了老师，所以这里就进行总结了。

一，单表查询操作

select * from 表名; 

2,查询指定字段:select 字段1，字段2，字段3...from 表名

select Sno,Sname from student;

3,where条件查询:select 字段1，字段2，字段3from 表名 where 条件表达式

select * from student where Sno=201215121;

select * from student where Sage>=19;

会使用条件运算符进行查询即可，这里不做讲述了。

4，带in关键字查询:select 字段1，字段2，字段3from 表名where 字段[not] in(元素1，元素2);

select * from student where Sno in(201215121,201215122);

5,带between and的范围查询:select 字段1，字段2，字段3 from 表名 where 字段[not] between 取值1 and 取值2;

select * from student where Sage between 18 and 19;

6,带like的模糊查询:select 字段1,字段2...from 表名 where 字段[not] like '字符串';

"%"代表任意字符
"_"代表单个字符

select * from student where Sname like '王敏';

7,空值查询:select 字段1，字段2...fom表名 where 字段is [not] null;

select * from student where Sname is null;
select * from student where Sname is not null;

8,带and的多条件查询:select 字段1，字段2...from 表名 where 条件表达式1 and 条件表达式2 and 条件表达式2 [and 条件表达式n]

select * from student where Sname ='王敏' and Sage=18;

9,带or的多条件查询:select字段1，字段2...from 表名 where 条件表达式1 or条件表达式2[or条件表达式n]

select * from student where Sname='王敏' or Sdept='CS';

10,distinct去重复查询:select distinct 字段名 from 表名;

select distinct Sdept from student;

11,对查询结果排序order by:select 字段1，字段2...from 表名 order by 属性名[asc|desc] 

select * from student order by Sno desc;

12,分组查询group by group by属性名[having 条件表达式][with rollup]

select Sdept,count(Sdept) from student group by Sdept;

13,limit分页查询:select 字段1，字段2，...from 表名 limit 初始位置，记录数。

select * from student limit 0,5;
select * from student limit 5,10;

二，多表关联查询

create table t_bookType
(
    id           int primary key auto_increment,
    bookTypeName varchar(20),
    bookTypeDesc varchar(200)
);

create table t_book
(
    id         int primary key auto_increment,
    bookName   varchar(10),
    price      decimal(6, 2),
    author varchar(255),
    bookTypeId int,
    constraint fk foreign key (bookTypeId) references t_bookType (id)
);

create table t_priceLevel
(
    id          int(11) primary key comment '自增主键',
    priceLevel  int(11) comment '价格级别',
    price       int(11) comment '价格',
    description varchar(255) comment '描述'
) engine = InnoDB
  charset = utf8;

create table t_priceLevel
(
    id          int(11)   comment '自增主键',
    priceLevel  int(11) comment '价格级别',
    price       int(11) comment '价格',
    description varchar(255) comment '描述'
) engine = InnoDB
  charset = utf8;

insert into `t_bookType`(id, bookTypeName, bookTypeDesc) values (1,'计算机类','计算机'),(2,'文学类','文学'),(3,'教育类','教育'),(null,null,null);
insert into `t_book`(id, bookName, price,author,    bookTypeId) VALUES (1,'Java从入门到精通', 100.00,'埃钟尔',1);
insert into `t_book`(id, bookName, price,author,    bookTypeId) VALUES (2,'Java从入门到精通', 80.00,'李钟尉',1);
insert into `t_book`(id, bookName, price,author,    bookTypeId) VALUES (3,'三剑客', 70.00,'大仲马',2);
insert into `t_book`(id, bookName, price,author,   bookTypeId) VALUES (4,'生理学(第二版)',24.00,'刘先国',4);
insert into `t_book`(id, bookName, price,author,   bookTypeId) VALUES (null,null,null,null,null);
insert into `t_priceLevel`(id, priceLevel, price, description) values (1,1,80,'价格贵的书');
insert into `t_priceLevel`(id, priceLevel, price, description) values (2,2,60,'价格适中的书');
insert into `t_priceLevel`(id, priceLevel, price, description) values (3,3,40,'价格便宜的书');
insert into `t_priceLevel`(id, priceLevel, price, description) values (null,null,null,null);

  1，笛卡尔积

select * from t_book,t_bookType;

2,内连接查询（两张或以上的表连接起来查询需要的数据）

select * from t_book t1,t_bookType where t1.bookTypeId=t2.id;

2,外连接查询（两张或以上的表连接起来查询某张表的信息）

select * from t_book t1 left join t_bookType t2  on t1.bookTypeId=t2.id;

说明:t_book的数据全部查出，t_bookType没有的字段用null代替

3，右连接查询

select * from t_book t1 right join t_bookType t2 on t1.bookTypeId=t2.id;

说明:右连接查询，查询出右边表所有信息，左表没有的用null

4，多条件查询

select * from t_book t1,t_bookType t2 where t1.bookTypeId=t2.id and t1.price>=70;

5,子查询

(1),带in关键字的子查询（一个查询语句的条件可能落在另一个select语句的查询结果中）

select * from t_book where bookTypeId in (select id from t_bookType);

select * from t_book where bookTypeId not in(select id from t_bookType);

(2)带比较运算符的子查询（子查询可以使用比较运算符）

select * from t_book where price >=(select price from t_priceLevel where priceLevel=1);

(3)带exists关键字的子查询(加入子查询查询到记录，则进行外层查询，否则，不执行外层查询)

select * from t_book where exists (select * from t_bookType);

(4)带any关键字的子查询(any关键字表示满足其中任意条件)

select * from t_book where price >=any(select price from t_priceLevel);  

(5)带all关键字的子查询(all关键字表示满足所有条件)

select * from t_book  where price>=all(select price from t_priceLevel);

合并查询

1,union关键字
使用union关键字时，数据库系统会将所有的查询结果合并到一起，然后去掉相同的记录。
select id from t_book union select id from t_bookType;
使用union all,不会去除重复的记录。
select id from t_book union all select id from t_bookType;

到这里就结束了对表常用的操作，无论它是单表操作还是多表的连接查询操作，这也是自己总结最全面的一篇关于多表连接查询的文章了。