测试学习6（数据库）

自关联

（多查自己几次然后关联起来）

查询河南省的市：

select * from areas as sheng，areas as shi（这时候同一个表必须要起别名）

where sheng.aid=shi.pid and sheng.atitle='河南省'

 查询河南省所有的区县:

(三级自关联)

select * from areas as sheng，areas as shi，areas as qu

（这时候同一个表必须要起别名）

where sheng.aid=shi.pid and sheng.atitle='河南省‘ and shi.aid=qu.pid

子查询

在一个select里面嵌入了一个或多个select

查询大于平均年龄的学生：

select * from students where age >(select avg（age）from students)

子查询返回的结果是一个值，称为标量查询

查询王昭君数据库的成绩，要求显示成绩：

select * from scores where studentno=(select studentno from students where name ='王昭君‘

and courseno=（select courseno from courses where name='数据库’）

列级子查询：

select * from scores where studentno in （select studentno from students where age=18）

此时子查询中返回的结果是1列多行，不能用=，只能用in

行级子查询：

select * from students where (sex,age)=(select sex,age from students where sex='男’ order by age desc limit 1）

此时子查询中返回的是一行多列

上面这几种查询都是子查询返回的结果作为主查询的条件

表子查询

子查询返回的结果是一个表，多行多列

（子查询返回的结果作为主查询的数据源）

select * from scores inner join 

（select * from courses where name in ('数据库'，‘系统测试’)）

（把查询出来的结果当作数据源使用）

as c(必须要起一个别名) on scores.courseno=c.courseno

子查询中特定关键字的使用

in：列级子查询中使用，子查询中返回了多个数据

=any，=some：相当于in

any，some前面也可以加大于号（大于后面范围中的最小值）小于号（小于最大值）

all前面加大于号（大于最大值）小于号（小于最小值）

条件查询，排序，分组，聚合函数，获取部分行，连接查询，子查询

查询练习：

有一商品数据库goods

字段为：id,name,cate,brand_name,price,is_show,is_saleoff

查询所有价格大于平均价格（保留两位小数）的商品，并且按照价格降序排序

select * from goods where  price>(select round(avg(price),2) from goods)  order by price desc

查询价格大于或等于超极本价格的商品，并且按照价格降序排序

select * from goods where price >=any (select price from goods where cate='超极本')

order by price desc

数据分表：

表中类型冗余，新建一个表存储笔记本类型

创建一个新表

create table goods_cates(

id int unsigned primary key auto_increment,

cate_name varchar(10)

)

查询出电脑的不同类型

select distinct cate from goods

将不同电脑类型插入到新建的表中

insert into goods_cate（cate_name） select distinct cate from goods

表中品牌冗余，新建一个表存储品牌类型

create table goods_brand(

id int unsigned primary key auto_increment,

brand_name varchar(10)

)

%查询出来的列必须对应表中的字段名才可以自动对应插入，如果在表中找不到同名字段，

会新建一个字段。

select distinct brand_name from goods

（一个分号表示一个语句）

现在需要把原表中的电脑类型换成类型编号

先用连接查询：

select * from goods

inner join goods_cates on goods,cate=cates.cate_name

查完以后更新列

update goods 

inner join goods_cates on goods.cate=goods_cates.cate_name

set goods.cate=goods_cates.id

类似的更新品牌，一步完成

update goods 

inner join goods_brands on goods.brand_name=goods_brands.brand_name

set goods.brand_name=goods_brands.id

根据上面三个表查询商品名称，类型名称，品牌名称

方法1（全连接查询，产生笛卡尔积）：

select goods.name,goods_cates.cate_name,goods_brands.brandname

 from goods,goods_cates,goods_brands

where goods.cate=goods_cates.id and goods.brand_name=goods_brand.id

这里*要更改成需要查询的字段

方法2（内连接）：

select * from goods

inner join goods_cates on goods.cate=goods_cates.id

inner join goods_brands on goods.brand_name=goods_brands.id

这里*不用更改，因为后面连接的就是需要查询的字段，就会显示这些字段。

方法2效率更高，不会产生笛卡尔积，所以优先用方法2查询。

现在添加一些类型和品牌

          

 其中商品表中类型12在类型表中不存在，现在要把它显示出来

左连接：

select * from goods

left join goods_cates on goods.cate_id=goods_cates.id

left join goods_brands on goods.brands_id=goods_brands.id

左右连接：

select * from goods_cates

right join goods on goods.cate_id=goods_cates.id

left join goods_brands on goods.brands_id=goods_brands.id

查询哪些品牌没货

方式1：select  * from goods

right join goods_brands on goods.brand_id=goods_brands.id

where goods.name is null

方式2：select * from goods_brands where id not in (select brand_id from goods)

查询哪些品牌有货

select * from goods where id in (select id from goods_brands)

查询某个表中按月新增的用户数

SELECT
  from_unixtime(ctime, '%Y-%m') AS mon,
  COUNT(user_id)
FROM
  user
WHERE
  ctime > UNIX_TIMESTAMP('2019-1-01 00:00:00')
GROUP BY
  mon
ORDER BY
  mon

from_unixtimr(ctime,'%Y-%m'),查询时间戳字段，转换为时间格式。