目录
...
数据源
表名:milk_tea
prod_id | prod_name | net_w | pro_date | valid_month | in_price | sale_price |
1 | 奶茶 | 150g | 2018/9/11 | 12 | 10.8 | 15 |
2 | 奶糖 | 150g | 2019/5/13 | 18 | 12.3 | 20 |
3 | 棒棒糖 | 15g | 2019/4/29 | 18 | 2.1 | 2.5 |
4 | 饼干 | 200g | 12 | 16.1 | 23 | |
5 | 薯片 | 100g | 2018/8/27 | 12 | 9.3 | 15 |
6 | 薯条 | 100g | 2018/8/31 | 12 | 8.8 | 15 |
7 | 火腿肠 | 550g | 2019/2/4 | 12 | 15.5 | |
8 | 方便面 | 100g | 2018/12/9 | 18 | 3.6 | 4 |
数据源自取:https://pan.baidu.com/s/1QF5flTKHKTsPgc3xH3Xziw?pwd=Q5S2
提取码:Q5S2
一、最常见的四个SQL语句
1. SELECT FROM查询语句
简单来说就是 select 列 from 表;这也是SQL最常用的语句之一。
#基础操作
SELECT prod_id FROM milk_tea; /*单列查询*/
SELECT prod_name, sale_price FROM milk_tea;/*多列查询*/
SELECT * FROM milk_tea;/*全部查询*/
2. AS命名语句
列字段 AS xx 就是给查询出来的列字段命名
表名 AS xx 就是给语句里的表重新取个名,当表名很长又要做表连接时经常用到。
/*AS命名,查询时对列再命名*/
SELECT prod_id, net_w AS 净含量, pro_date, valid_month AS 保质期 FROM milk_tea;
/*查询时对表再命名*/
SELECT m.prod_name FROM milk_tea AS m;
运行结果如下:
3. WHERE数据过滤语句
可以接">","=","<",">=","<=","!=",between and,like等等等等(后面再详细写一篇吧)
#WHERE查询
select m.* from milk_tea as m
WHERE m.net_w = "150g";
运行结果如下:
4. ORDER BY 排序语句
#英文
select m.* from milk_tea as m ORDER BY m.sale_price;#默认升序
select m.* from milk_tea as m ORDER BY m.sale_price DESC;#降序
select m.* from milk_tea as m ORDER BY m.valid_month,m.sale_price;#按字段先后顺序依次排序
中文排序,需要先转码,再排序
#先转码再排序,utf-8 转换成gbk
CONVERT(prod_name,USING gbk)
select m.* from milk_tea as m ORDER BY CONVERT(prod_name USING gbk);#默认升序
二、其他也需要掌握的语句
1. DISTINCT命令
找出列中的不重复值,内容去重
SELECT DISTINCT m.net_w FROM milk_tea as m;
运行结果如下:
2. SQL四则运算
SELECT prod_name, in_price, sale_price,
sale_price - in_price,
sale_price * 0.9
FROM milk_tea;
运行结果如下:
3. 添加常数项
可以用来做表连接,当两个表里没有相同列时
SELECT prod_name, 0.9 AS discount FROM milk_tea;/*新建常数列,并命名为discount*/
运行结果如下:
未完待续...
整理不易,点个赞吧~
整理自B站:【MySQL教程】SQL零基础教程,带你掌握最受企业欢迎的数据库语言!
数据源及导图来自up主:未明学院