一、数据库中表的复杂查询
1)连接查询
1.0连接的基本语法格式:
from TABLE1 join_type TABLE2 [on (join_condition)][where (query_condition)]
TABLE1:左表
TABLE2:右表
join_type:连接的类型。交叉、内连接、左外连接、右外连接
on:设置连接条件
where:对连接查询的结果进步一的筛选
1.1交叉连接
select * from CUSTOMER cross join ORDERS;
或者
select * from CUSTOMER,ORDERS;
select c.name,o.order_number from CUSTOMER c,ORDERS o;
1.2内连接:
隐式内连接:(不使用on关键字,使用where关键字)
select * from CUSTOMER c,ORDERS o where c.id=o.customer_id;
显式内连接:(使用on关键字 注:也可以使用where关键字)
select * from CUSTOMER c inner join ORDERS o on c.id=o.customer_id;
1.3外连接:
左外连接:(返回符合连接条件的所有记录,同时还返回左表中其余的所有记录)
select * from CUSTOMER c left outer join ORDERS o on c.id=o.customer_id;
右外连接:(返回符合连接条件的所有记录,同时还返回右表中其余的所有记录)
select * from CUSTOMER c right outer join ORDERS o on c.id=o.customer_id;
2)子查询(嵌套查询)
子查询: select * from orders where customer_id=(select id from customer where name='张三');
3)联合查询
SELECT * FROM orders WHERE price>200 UNION SELECT * FROM orders WHERE customer_id=1;
取两条语句的并集,并去除重复的记录。
UNION 列出去重后的记录
UNION ALL 列出所有的记录,不会去重
二、数据库中的分页:
1)MySQL中的分页:(LIMIT m,n 注:m表示开始的下标,n表示要查询的条数)
select * from table WHERE … LIMIT 1; #返回第0行
select * from table WHERE … LIMIT 10; #返回0到9行
select * from table WHERE … LIMIT 0,10; #返回0到9行:从0开始,查10条
select * from table WHERE … LIMIT 10,15 #返回10到24行:从10开始,查15条
2)Oracle中的分页:
1)Oracle中的rownum和rowid都是伪列,但是两者在根本上是不同的:rownum是根据sql查询出的结果给每行分配一个逻辑编号,所以你的sql不同也就会导致最终rownum不同。
注意:rownum是从1开始记录的rowid是物理结构上的,在每条记录insert到数据库中时,都会有一个唯一的物理记录。rowid具有唯一性。
2)通常在sql分页时或是查找某一范围内的记录时使用rownum;在处理一张有重复记录的表时经常用到rowid。
例子一:查找小于3的记录(注意:如果直接用rownum,则查找的范围必须包含1)
SQL:select * from emp a where rownum < 3;
例子二:查找2到10范围内的记录(这里包括2和10的记录)
策略:把rownum查出来后放在一个虚表中作为这个虚表的字段再根据条件查询
SQL:select * from (select rownum rn, a.* from emp a) t where t.rn between 2 and 10;
三、分组查询:
1)查询最新的分组:
# 查询最新的分组(field_1,field_12) 说明:limit n:n要保证大于所有满足条件记录的数量。 5.7需要加limit,5.6不需要加limit。
select t1.* from (select field_1, field_2, field_3 from table_1 where create_time="2020-01-01 00:00:00" order by modify_time desc limit 100000 ) t1 group by field_1,field_12
举例(mysql5.6):
查询任意时间点下,所有商品的售卖价格。
表:
CREATE TABLE `goods` (
`id` bigint(10) NOT NULL auto_increment,
`name` varchar(255) default NULL,
`price` int(10) default NULL,
`datestr` datetime default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
sql:
select * from (select * from goods t where t.datestr < "2020-5-01" ORDER BY datestr desc ) temp GROUP BY temp.name
2)查询某个字段的值存在重复的数据:
select * from table_a where field_a in (select field_a from table_a group by field_a HAVING count(*) > 1)
四、表结构更改:
建表:
create table as:
说明:复制表结构和数据
用法:
create table new_table as select * from old_table;
create table new_table as select * from old_table limit 0;
# limit 0表示只复制表结构,不复制数据。
create table like:
说明:复制表结构和索引结构
用法:create table new_table like old_table
举例:create table t_log_2018_01_02 like t_log_2018_01_01;
加列:
# 在某列后面新增加一列
ALTER TABLE tabele_name add `field_name` int(11) NOT NULL DEFAULT '0' COMMENT 'xxx' after existed_filed_name