MySQL之day4
1.视图, 就是保存好的select 语句
- 创建视图
- create view <视图名> (<视图列>, ....) as select 语句.....
mysql> create view prdtsum (product_type,cnt_product) -> as -> select product_type,count(*) -> from product -> group by product_type; Query OK, 0 rows affected (0.74 sec) mysql> select * from prdtsum; +--------------+-------------+ | product_type | cnt_product | +--------------+-------------+ | 衣服 | 2 | | 办公用品 | 2 | | 厨房用品 | 4 | | 家具 | 3 | +--------------+-------------+ 4 rows in set (0.10 sec)
注意: 不可以使用 order by
- create view <视图名> (<视图列>, ....) as select 语句.....
- 多重视图
mysql> create view prdtsumjim (product_type,cnt_product) -> as -> select product_type,cnt_product -> from prdtsum -> where product_type='衣服'; Query OK, 0 rows affected (0.42 sec) mysql> select * from prdtsumjim -> ; +--------------+-------------+ | product_type | cnt_product | +--------------+-------------+ | 衣服 | 2 | +--------------+-------------+ 1 row in set (0.03 sec)
- 插入insert 数据 into view
mysql> create view prdtjim (product_id,product_name,product_type,sale_price,purchase_price,regist_date,co) -> as -> select * from product -> ; Query OK, 0 rows affected (0.37 sec) mysql> select * from prdtjim; +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | NULL | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | NULL | | 0003 | 运行T恤 | 衣服 | 4000 | 2800 | NULL | NULL | | 0004 | 菜刀 | 厨房用品 | 30000 | 1400 | 2009-09-20 | NULL | | 0005 | 高压锅 | 厨房用品 | 68000 | 2500 | 2009-01-15 | NULL | | 0006 | 叉子 | 厨房用品 | 5000 | NULL | 2009-09-20 | NULL | | 0007 | 砧板 | 厨房用品 | 8800 | 395 | 2008-04-28 | NULL | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | NULL | | 0009 | 电视机 | 家具 | 4000 | 3000 | 2019-07-04 | NULL | | 0010 | 电视机2 | 家具 | 40000 | NULL | 2019-07-05 | NULL | | 0020 | 电视机4 | 家具 | NULL | 40001 | 2019-07-05 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 11 rows in set (0.00 sec) mysql> insert into prdtjim values ('0011','4k电视','家具',10000,3000,'2019-07-07',null) -> ; Query OK, 1 row affected (0.41 sec) mysql> select * from prdtjim; +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | NULL | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | NULL | | 0003 | 运行T恤 | 衣服 | 4000 | 2800 | NULL | NULL | | 0004 | 菜刀 | 厨房用品 | 30000 | 1400 | 2009-09-20 | NULL | | 0005 | 高压锅 | 厨房用品 | 68000 | 2500 | 2009-01-15 | NULL | | 0006 | 叉子 | 厨房用品 | 5000 | NULL | 2009-09-20 | NULL | | 0007 | 砧板 | 厨房用品 | 8800 | 395 | 2008-04-28 | NULL | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | NULL | | 0009 | 电视机 | 家具 | 4000 | 3000 | 2019-07-04 | NULL | | 0010 | 电视机2 | 家具 | 40000 | NULL | 2019-07-05 | NULL | | 0011 | 4k电视 | 家具 | 10000 | 3000 | 2019-07-07 | NULL | | 0020 | 电视机4 | 家具 | NULL | 40001 | 2019-07-05 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 12 rows in set (0.00 sec)
mysql> select * from product; --同步更新到 源表 +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | NULL | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | NULL | | 0003 | 运行T恤 | 衣服 | 4000 | 2800 | NULL | NULL | | 0004 | 菜刀 | 厨房用品 | 30000 | 1400 | 2009-09-20 | NULL | | 0005 | 高压锅 | 厨房用品 | 68000 | 2500 | 2009-01-15 | NULL | | 0006 | 叉子 | 厨房用品 | 5000 | NULL | 2009-09-20 | NULL | | 0007 | 砧板 | 厨房用品 | 8800 | 395 | 2008-04-28 | NULL | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | NULL | | 0009 | 电视机 | 家具 | 4000 | 3000 | 2019-07-04 | NULL | | 0010 | 电视机2 | 家具 | 40000 | NULL | 2019-07-05 | NULL | | 0011 | 4k电视 | 家具 | 10000 | 3000 | 2019-07-07 | NULL | | 0020 | 电视机4 | 家具 | NULL | 40001 | 2019-07-05 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 12 rows in set (0.00 sec)
insert 要使用 既没有聚合 有没有结合的select
- drop 删除视图
View Codemysql> show tables; +----------------+ | Tables_in_shop | +----------------+ | prdtjim | | prdtsum | | prdtsumjim | | product | +----------------+ 4 rows in set (0.38 sec) mysql> drop view prdtsum; Query OK, 0 rows affected (0.38 sec) mysql> show tables; +----------------+ | Tables_in_shop | +----------------+ | prdtjim | | prdtsumjim | | product | +----------------+ 3 rows in set (0.00 sec) mysql> select * from prdtsumjim; ERROR 1356 (HY000): View 'shop.prdtsumjim' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> select * from prdtjim; +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | NULL | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | NULL | | 0003 | 运行T恤 | 衣服 | 4000 | 2800 | NULL | NULL | | 0004 | 菜刀 | 厨房用品 | 30000 | 1400 | 2009-09-20 | NULL | | 0005 | 高压锅 | 厨房用品 | 68000 | 2500 | 2009-01-15 | NULL | | 0006 | 叉子 | 厨房用品 | 5000 | NULL | 2009-09-20 | NULL | | 0007 | 砧板 | 厨房用品 | 8800 | 395 | 2008-04-28 | NULL | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | NULL | | 0009 | 电视机 | 家具 | 4000 | 3000 | 2019-07-04 | NULL | | 0010 | 电视机2 | 家具 | 40000 | NULL | 2019-07-05 | NULL | | 0011 | 4k电视 | 家具 | 10000 | 3000 | 2019-07-07 | NULL | | 0020 | 电视机4 | 家具 | NULL | 40001 | 2019-07-05 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 12 rows in set (0.00 sec) mysql> select * from product; +------------+--------------+--------------+------------+----------------+-------------+------+ | product_id | product_name | product_type | sale_price | purchase_price | regist_date | co | +------------+--------------+--------------+------------+----------------+-------------+------+ | 0001 | T恤 | 衣服 | 1000 | 500 | 2009-09-20 | NULL | | 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 | NULL | | 0003 | 运行T恤 | 衣服 | 4000 | 2800 | NULL | NULL | | 0004 | 菜刀 | 厨房用品 | 30000 | 1400 | 2009-09-20 | NULL | | 0005 | 高压锅 | 厨房用品 | 68000 | 2500 | 2009-01-15 | NULL | | 0006 | 叉子 | 厨房用品 | 5000 | NULL | 2009-09-20 | NULL | | 0007 | 砧板 | 厨房用品 | 8800 | 395 | 2008-04-28 | NULL | | 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 | NULL | | 0009 | 电视机 | 家具 | 4000 | 3000 | 2019-07-04 | NULL | | 0010 | 电视机2 | 家具 | 40000 | NULL | 2019-07-05 | NULL | | 0011 | 4k电视 | 家具 | 10000 | 3000 | 2019-07-07 | NULL | | 0020 | 电视机4 | 家具 | NULL | 40001 | 2019-07-05 | NULL | +------------+--------------+--------------+------------+----------------+-------------+------+ 12 rows in set (0.00 sec)
注意 : drop 时 有关联关系的view ,会受到影响
ERROR 1356 (HY000): View 'shop.prdtsumjim' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
2.子查询
2.1 子查询, 嵌套 select 语句
mysql> select product_type,cn_product -> from( select * from ( select product_type,count(*) as cn_product -> from product -> group by product_type) as prdtsum -> where cn_product=4) as prdttype; +--------------+------------+ | product_type | cn_product | +--------------+------------+ | 厨房用品 | 4 | | 家具 | 4 | +--------------+------------+ 2 rows in set (0.00 sec)
2.2 标量查询
标量: 只能是一行一列的结果
注意: where 语句不能使用聚合语句
mysql> select product_name,sale_price -> from product -> where sale_price>avg(sale_price); ERROR 1111 (HY000): Invalid use of group function
可以通过嵌套方式:
mysql> select product_name,sale_price -> from product -> where sale_price>( select avg(sale_price) --必须返回一行一列 -> from product); +--------------+------------+ | product_name | sale_price | +--------------+------------+ | 菜刀 | 30000 | | 高压锅 | 68000 | | 电视机2 | 40000 | +--------------+------------+ 3 rows in set (0.29 sec)
在 group by 和having 语句中使用
mysql> select product_type,avg(sale_price) -> from product -> group by product_type -> having avg(sale_price)>(select avg(sale_price) from product); +--------------+-----------------+ | product_type | avg(sale_price) | +--------------+-----------------+ | 厨房用品 | 27950.0000 | | 家具 | 18000.0000 | +--------------+-----------------+ 2 rows in set (0.31 sec)
3.关联子查询
关联子查询, 结合条件一定要写在 子查询中, 在细分组内进行比较,需要用到!!!!
--按照type计算平均值, 取出售价大于所属type类型的平均值
mysql> select product_type,product_name,sale_price -> from product as p1 -> where sale_price > (select avg(sale_price) -> from product as p2 -> where p1.product_type=p2.product_type ----关键结合语句 -> group by product_type); +--------------+--------------+------------+ | product_type | product_name | sale_price | +--------------+--------------+------------+ | 办公用品 | 打孔器 | 500 | | 衣服 | 运行T恤 | 4000 | | 厨房用品 | 菜刀 | 30000 | | 厨房用品 | 高压锅 | 68000 | | 家具 | 电视机2 | 40000 | +--------------+--------------+------------+ 5 rows in set (0.01 sec)
实例: 按照类型type计算平均值
mysql> create view AvgPriceByType -> as -> select product_id, product_name, product_type, sale_price, (select avg(sale_price) from product p2 -> where p2.product_type=p1.product_type -> group by product_type) as sale_price_avg -> from product as p1; Query OK, 0 rows affected (0.50 sec) mysql> select * from AvgPriceByType; +------------+--------------+--------------+------------+------------+ | product_id | product_name | product_type | sale_price | sale_price_avg | +------------+--------------+--------------+------------+------------+ | 0001 | T恤 | 衣服 | 1000 | 2500.0000 | | 0002 | 打孔器 | 办公用品 | 500 | 300.0000 | | 0003 | 运行T恤 | 衣服 | 4000 | 2500.0000 | | 0004 | 菜刀 | 厨房用品 | 30000 | 27950.0000 | | 0005 | 高压锅 | 厨房用品 | 68000 | 27950.0000 | | 0006 | 叉子 | 厨房用品 | 5000 | 27950.0000 | | 0007 | 砧板 | 厨房用品 | 8800 | 27950.0000 | | 0008 | 圆珠笔 | 办公用品 | 100 | 300.0000 | | 0009 | 电视机 | 家具 | 4000 | 18000.0000 | | 0010 | 电视机2 | 家具 | 40000 | 18000.0000 | | 0011 | 4k电视 | 家具 | 10000 | 18000.0000 | | 0020 | 电视机4 | 家具 | NULL | 18000.0000 | +------------+--------------+--------------+------------+------------+ 12 rows in set (0.64 sec)
附加:
查看 table 和view
1 mysql> describe prdtjim; 2 +----------------+--------------+------+-----+---------+-------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +----------------+--------------+------+-----+---------+-------+ 5 | product_id | char(4) | NO | | NULL | | 6 | product_name | varchar(100) | NO | | NULL | | 7 | product_type | varchar(32) | NO | | NULL | | 8 | sale_price | int(11) | YES | | NULL | | 9 | purchase_price | int(11) | YES | | NULL | | 10 | regist_date | date | YES | | NULL | | 11 | co | varchar(12) | YES | | NULL | | 12 +----------------+--------------+------+-----+---------+-------+ 13 7 rows in set (0.36 sec) 14 15 mysql> describe product; 16 +----------------+--------------+------+-----+---------+-------+ 17 | Field | Type | Null | Key | Default | Extra | 18 +----------------+--------------+------+-----+---------+-------+ 19 | product_id | char(4) | NO | PRI | NULL | | 20 | product_name | varchar(100) | NO | | NULL | | 21 | product_type | varchar(32) | NO | | NULL | | 22 | sale_price | int(11) | YES | | NULL | | 23 | purchase_price | int(11) | YES | | NULL | | 24 | regist_date | date | YES | | NULL | | 25 | co | varchar(12) | YES | | NULL | | 26 +----------------+--------------+------+-----+---------+-------+ 27 7 rows in set (0.00 sec) 28 29 mysql> show table status like 'prdtjim'; 30 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ 31 | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | 32 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ 33 | prdtjim | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 0 | 0 | NULL | 2019-07-07 08:56:59 | NULL | NULL | NULL | NULL | NULL | VIEW | 34 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+---------+ 35 1 row in set (0.42 sec) 36 37 mysql> show table status like 'product'; 38 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 39 | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | 40 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 41 | product | InnoDB | 10 | Dynamic | 11 | 1489 | 16384 | 0 | 0 | 0 | NULL | 2019-07-04 20:55:20 | NULL | NULL | utf8_general_ci | NULL | | | 42 +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 43 1 row in set (0.38 sec)
