mysql高级语法

用数据说话

已于 2023-08-08 20:36:58 修改

阅读量710

点赞数

分类专栏： sql 文章标签： mysql 数据库

于 2019-07-16 22:50:07 首次发布

本文链接：https://blog.csdn.net/xjh163/article/details/96012221

版权

sql 专栏收录该内容

31 篇文章 4 订阅

订阅专栏

本篇是在自学Mysql数据库的过程中，观看一个视频时的个人笔记，感觉这个视频还是不错的，对于已经掌握了基本语法，希望通过实际案例来锻炼的同学有很大的帮助。
在文末会附有视频链接，有兴趣的同学可以看一下。

文章目录

1. 内连接应用

列出客户信息和订单日期，并按订单日期排序

SELECT
	u.user_id,
	u.user_name,
	u.eamil,
	oi_order_sn,
	from_unixtime( oi.add_time ) AS ordertime 
FROM
	ecs_order_info AS oi
	INNER JOIN ecs_users AS u ON oi.user_id = u.user_id 
ORDER BY
	ordertime;

数据表的“addtime”字段一般是int型，用于存放时间戳，当想要直观的查看某条记录的addtime,我们需要用from_unixtime()将时间戳转换为日期格式，UNIX_TIMESTAMP()即将日期类型的转换为时间戳显示。

select UNIX_TIMESTAMP('2018-08-08')------------------->1533657600
select from_unixtime(1533657600)--------------------------->2018-08-08 00:00:00

列出被购买过的商品的名称，类型名称和交易价格(三表内连接)注意表连接时用那个字段

SELECT
	og.goods_id,
	og.goods_name,
	gt.cat_name,
	og.goods_price 
FROM
	 esc_order_goods og INNER JOIN ecs_goods g ON og.goods_id = g.goods_id 
	INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id;

如果是在表连接时，有对表进行where筛选的话，需要先进行限制在连接，可以提高运行效率

显示各订单下每个商品以及该商品的销售额，按照订单号排序
列出被购买过的商品的名称，品牌名称和客户信息(

SELECT
	og.goods_names,
	b.brand_name,
	u.user_name 
FROM
	(
		(
			( ecs_order_goods og INNER JOIN ecs_goods g ON og.goods_id = g.goods_id )
			INNER JOIN ecs_brand b ON g.brand_id = b.brand_id 
		)
		INNER JOIN ecs_order_info oi ON og.order_id = oi.order_id 
	)
	INNER JOIN ecs_users_u ON oi.user_id = USER.id;

可以修改为

SELECT
	og.goods_names,
	b.brand_name,
	u.user_name 
FROM
	ecs_order_goods og
	INNER JOIN ecs_goods g om og.goods_id = g.goods_id
	INNER JOIN ecsecs_brand b ON g.brand_id = b.brand_id
	INNER JOIN ecs_order_info oi ON og.order_id = oi.order_id
	INNER JOIN ecs_users_u ON oi.user_id = USER.id;

找出ecshop用户所有的订单信息

SELECT
	u.user_name,
	oi.order_id,
	og.goods_number,
	og.goods_price 
FROM
	( ecs_order_info oi INNER JOIN ecs_users u ON ou.user_id = u.user_id AND u.user_name = 'ecshop' )
	INNER JOIN ecs_order ON oi.ORDER = og.order_id;

注意表连接时的and，这里是先选取u表里user_name 为ecshop的行，然后和oi表进行内连接。要注意与where的区别

2. 外连接查询

在这里，刚学的时候会发生不知道用左右连接的情况，其实不用区分，两者是可以装换的，
看你想显示那个表的全部数据，左连接显示左表全部数据，右连接显示右表全部数据；
想要保留左边表的全部数据，用左连接，先要保留右边表的全部数据，用右连接

商品表和订单表连接，显示商品表里的全部数据，商品表为主表，没有订单的商品og.goos_id 显示为 NULL，进而可以筛选出

商品类型表中哪些商品类型没有出现在商品表中

SELECT
	* 
FROM
	ecs_goods g
	LEFT JOIN ecs_order_goods og ON g.goods_id = og.goods_id 
WHERE
	og.goods_id IS NULL;

要显示商品类型表里的全部数据，且商品类型表在右边，所以用右连接，没有出现在商品表中的商品类型g.goods_type为NULL

找出没有购买过诺基亚E66上商品的客户信息表

SELECT
	u.* 
FROM
	( ecs_order_info oi INNER JOIN ecs_order_goods og ON oi.order_id = og.order_id )
	RIGHT JOIN ecs_users u ON oi.user_id = u.user_id 
WHERE
	oi.user_id IS NULL;

可修改为

SELECT
	u.* 
FROM
	ecs_order_info oi
	INNER JOIN ecs_order_goods og ON oi.order_id = og.order_id
	RIGHT JOIN ecs_users u ON oi.user_id = u.user_id 
WHERE
	oi.user_id IS NULL;

列出从来没有购买过手机的客户名单

SELECT
	u.* 
FROM
	(
		(
			( ecs_goods g INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id AND gt.cat_name IN ( '手机', '精品手机' ) )
			INNER JOIN ecs_order_goods og ON g.goods_id = og.goods_id 
		)
		INNER JOIN ecs_order_info oi ON og.order_id = oi.ORDER 
	)
	RIGHT JOIN ecs_users u ON oi.USER = u.user_id

可修改为

SELECT
	u.* 
FROM
	ecs_goods g
	INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id 
	AND gt.cat_name IN ( "手机"，"精品手机" )
	INNER JOIN ecs_order_goods og ON g.goods_id = og.goods_id
	INNER JOIN ecs_order_info oi ON og.order_id = oi.ORDER
	RIGHT JOIN ecs_users u ON oi.USER = u.user_id

所有邮寄方式中那种邮寄方式没有被采用过
列出所有商品和各商品有关的所有订单的日期

SELECT
	g.goods_id,
	g.goods_name,
	oi.order_id,
	from_unixtime( oi.add_time ) ordertime 
FROM
	( ecs_order_info oi INNER JOIN ecs_order_goods og ON oi.order_id = og.ORDER )
	RIGHT JOIN ecs_goods g ON og.goods_id = g.goods_id;

可修改为

SELECT
	g.goods_id,
	g.goods_name,
	oi.order_id,
	from_unixtime( oi.add_time ) ordertime 
FROM
	ecs_order_info oi
	INNER JOIN ecs_order_goods og ON ou.order_id = og.
	ORDER RIGHT JOIN ecs_goods g ON og,
	goods_id = g.goods_id;

3. 子查询

3.1 不同关键字后面的子查询

select后面的子查询（列出商品表中各商品的类型名称）

SELECT
	goods_id,
	goods_name,
	( SELECT cat_name FROM ecs_goods_type WHERE cat_id = g.goods_type ) AS  type_name 
FROM
	ecs_goods g;

类型名在 ecs_goods_type中,从里面选出商品对应的类型名称取名为type_nameel
这里还牵涉到mysql执行顺序的问题

模拟orcale中的rownum(行编号)

select
(
select count(*)
from ecs_goods g
where g.goods_id <= eg.goods_id
)
goods_id,goods_name,market_price
from ecs_goods  eg
order by 1;

from后面的子查询（哪些用户购买了市场价格大于2000的商品)

SELECT
	u.user_id,
	u.user_name,
	t.market_price 
FROM
	( ecs_users u INNER JOIN ecs_order_into oi ON u.user_id = oi.user_id )
	INNER JOIN ( SELECT * FROM ecs_order_goods og WHERE og.markeyt_rice > 2000 ) AS t ON oi.order_id = t.order_id;

一定不要忘了给虚拟表取别名

where后面的子查询（找出市场价格与P806相同的商品信息）

SELECT
	* 
FROM
	ecs_goods 
WHERE
	market_price in ( SELECT markrt_price FROM ecs_goods WHERE goods_name = 'P806' );#P806只有一行信息


SELECT
	* 
FROM
	ecs_goods 
WHERE
	market_price in ( SELECT markrt_price FROM ecs_goods WHERE brand = 1 LIMIT 0, 1 );#当返回的不止一行是，为防止报错，可以用limit来限制来只取一行

SELECT
	* 
FROM
	ecs_goods 
WHERE
	goods_id IN ( SELECT gooes_id FROM ecs_order_goods );#哪些商品被购买过



SELECT
	* 
FROM
	ecs_goods 
WHERE
	goods_id NOT IN ( SELECT gooes_id FROM ecs_order_goods );#哪些商品没被购买过

(in与not in需慎用)
当in或not in 后的表含有空值时，会没有结果，为防止这种情况，可进行限制

SELECT
	* 
FROM
	ecs_brand 
WHERE
	brand_id NOT IN ( SELECT brand_id FROM ecs_goods WHERE brand_is IS NOT NULL );

3.2 关联子查询和非关联子查询的区分

列出购买诺基亚手机的客户信息

SELECT
	* 
FROM
	ecs_users u 
WHERE
	u.user_id IN (
	SELECT
		oi.user_id 
	FROM
		(
			(
				ecs_goods g
				INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id 
				AND gt.cat_name IN ( '手机', '精品手机' ) 
				AND g.goods_name LIKE '诺基亚%' 
			)
			INNER JOIN ecs_order_goods og ON g.goods_id = og.goods_id 
		)
	INNER JOIN ecs_order_info oi ON og.order_id = oi.order_id 
	);

找出购买了诺基亚手机但没有购买摩托罗拉手机的客户信息

SELECT
	* 
FROM
	ecs_users u 
WHERE
	u.user_id IN (
	SELECT
		oi.user_id 
	FROM
		(
			(
				ecs_goods g
				INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id 
				AND gt.cat_name IN ( '手机', '精品手机' ) 
				AND g.goods_name LIKE '诺基亚%' 
			)
			INNER JOIN ecs_order_goods og ON g.goods_id = og.goods_id 
		)
		INNER JOIN ecs_order_info oi ON og.order_id = oi.order_id 
	) 
	AND u.uer_id NOT IN (
	SELECT
		oi.user_id 
	FROM
		(
			(
				ecs_goods g
				INNER JOIN ecs_goods_type gt ON g.goods_type = gt.cat_id 
				AND gt.cat_name IN ( '手机', '精品手机' ) 
				AND g.goods_name LIKE '摩托罗拉' 
			)
			INNER JOIN ecs_order_goods og ON g.goods_id = og.goods_id 
		)
	INNER JOIN ecs_order_info oi ON og.order_id = oi.order_id 
	);

3.3 分组统计

统计每种类型下各商品的销售总额

SELECT
	gt.cat_name,
	IFNULL( sum( og.goods_number * og.goods_prices ), 0 ) amount 
FROM
	( ecs_goods g INNER JOIN ecs_goods_type gt ON g.goods_type = qt.cat_id )
	LEFT JOIN ecs_order_goods og ON g.goods_id = og.goods_id 
GROUP BY
	gt.cat_name

IFNULL(sum(og.goods_number*og.goods_prices),0)  如果商品没有销售时，销售额显示为0

统计后过滤
- 统计每一个客户购物的总开销

SELECT
	u.user_name,
	IFNULL( sum( og.goods_numbers * og.goods_price ), 0 ) AS amount 
FROM
	( ecs_order_info oi INNER JOIN ecs_order_goods og ON oi.order_info = og.order_id )
	RIGHT JOIN ecs_users u ON ou.user_id = u.user_id 
GROUP BY
	u.user_name;

- 哪些类型的商品平均价格超过了2000

SELECT
	gt.cat_name,
	AVG( g.market_price ) 
FROM
	ecs_goods_type gt
	LEFT JOIN ecs_goods g ON gt.cat_id = g.goods_type 
GROUP BY
	gt.cat_name 
HAVING
	AVG( g.market_price ) > 2000;

- 哪些类型的商品平均价格超过了所有产品均价 group by 与having

SELECT
	gt.cat_name,
	AVG( g.market_price ) 
FROM
	ecs_goods_type gt
	LEFT JOIN ecs_goods g ON gt.cat_id = g.goods_type 
GROUP BY
	gt.cat_name 
HAVING
	AVG( g.market_price ) > ( SELECT avg( market_price ) FROM ecs_goods );

- 列出总销售额超过10000的商品信息

SELECT
	og.goods_name,
	sum( og.goods_price * og.goods_number ) 
FROM
	ecs_order_goods og 
GROUP BY
	og.goods_name 
HAVING
	sum( og.goods_price * og.goods_number ) > 10000;

- 有多少个订单只购买了一种商品

SELECT
	count( * ) 
FROM
	( SELECT order_id, count( goods_id ) FROM ecs_goods GROUP BY order_id HAVING count( goods_id ) = 1 ) t;#注意要给虚拟表加别名

4.DML

数据操作语言，插入，删除，更新数据库中的数据表

4.1 insert

- 插入一条记录
insert into  表名（c1,c2,c3...） 
values（v1,v2,v3...）#顺序，类型，个数；插入的值要和列名顺序相对应
-  插入多条数据
insert into  表名（c1,c2,c3...） 
values（v1,v2,v3...），
（v1,v2,v3...），
（v1,v2,v3...）；
-  不指定插入列，表名插入所有列
insert into  表名 
values（v1,v2,v3...）

复制表:将a表中的数据复制到b表中

insert into b
select * from a

快速备份表

create table b
as select * from a;

4.2 update

语法

update  tab_name
set col_name=new_value,col_name=new_value......
where search_condition;

修改用户zhangsan的密码和用户名
update ecs_users
set password='123345',qq='10000'
where user_name='zhangsan'

4.3 delete

语法：

delete from tab_name
where search_condition;

删除zhangsan用户
delete from ecs_users
where user_name='zhangsan';

参考链接：
https://study.163.com/course/courseMain.htm?courseId=1005821015

用数据说话

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录