一、SQL学习之单表查询

表查询的执行顺序：

where>group by>select>having>order by

---

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL

一、语法

---

---

1.SQL不区分大小写，认为规定以方便编程，以分号;结尾

2.表命名规则：

                英文(a-zA-Z)、数字(0-9)、下划线_

                必须英文开头，不能重复

                一般使用小写

SHOW DATABASES;

CREATE DATABASE sqllearning;

USE sqllearning;

3.记录分隔符

• CRLF: Carriage-Return Line-Feed的缩写，意思是回车换行，即\r\n;
• LF: Line-Feed的缩写,意思是换行，即\n;
• CR: Carriage-Return的缩写，回车，即\r;
• 

 

 

4.关键字：

关键字又叫保留字，大写，不能用作表名或列名

5.列 表 数据库 一般小写

6.关键字 操作对象

7.语句结构

二、单表查询

---

---

SELECT prodname FROM order_list;
SELECT order_id,date,prodname FROM order_list;
SELECT * FROM order_list;
SELECT milk_tea.in_price FROM milk_tea;
SELECT milk_tea.net_w AS '重量' FROM milk_tea;
SELECT milk_tea.prod_id,milk_tea.valid_month AS '保质期' ,milk_tea.prod_name AS '产品名称' FROM milk_tea;
SELECT m.in_price,m.net_w,m.sale_price FROM milk_tea AS m;
SELECT *,0.9 FROM milk_tea;
SELECT *,0.9 AS 'discount' FROM milk_tea;
SELECT *,'零食' AS class FROM milk_tea;
SELECT m.prod_name,m.in_price,m.sale_price,m.sale_price-m.in_price AS '利润' FROM milk_tea AS m;
SELECT m.in_price,m.sale_price,m.sale_price*0.9 AS 'new_sale' FROM milk_tea AS m;

不影响表数据，只影响查询结果的展示

计算利润

 

字符拼接 ：

SELECT *,CONCAT(m.prod_id,':',m.prod_name,m.pro_date,'(',m.net_w,')',m.valid_month) AS '产品信息' FROM milk_tea AS m;
SELECT *,CONCAT_WS('-',m.prod_id,m.prod_name,m.pro_date,m.net_w,m.valid_month) AS '用统一分隔符' FROM milk_tea AS m;
SELECT DISTINCT net_w FROM milk_tea;

 

 

排序

SELECT * FROM milk_tea ORDER BY sale_price DESC;
SELECT * FROM milk_tea ORDER BY valid_month DESC,in_price ASC;
SELECT prod_id,prod_name FROM milk_tea ORDER BY sale_price;
SELECT * FROM milk_tea ORDER BY pro_date;
SELECT * FROM supplier_info ORDER BY CONVERT(supplier_info.supplier_name USING gbk);

排序：

 

数据过滤---where:

SELECT s.tel FROM supplier_info AS s WHERE s.tel LIKE '133%';
SELECT * FROM milk_tea AS m WHERE m.prod_id=2;
SELECT * FROM milk_tea WHERE in_price>10 and valid_month=18;
SELECT * FROM milk_tea WHERE net_w='150g';
SELECT * FROM milk_tea WHERE sale_price!=15;
SELECT * FROM milk_tea WHERE sale_price<>15;
SELECT * FROM milk_tea WHERE sale_price*0.9<15;

SELECT * FROM milk_tea WHERE IFNULL(sale_price,0)<>15;

 

 

SELECT * FROM milk_tea WHERE IFNULL(sale_price,0)<>15;
SELECT * FROM milk_tea WHERE sale_price IS NOT NULL; 
SELECT * FROM milk_tea WHERE sale_price IS NULL; 
SELECT * FROM milk_tea WHERE sale_price BETWEEN 15 AND 20;

 

like模糊查询

尽量不要把通配符放在开头，查询效率较低

? _一个字符

% *0个或1个或多个

\转义字符

 

 多条件查询

SELECT * FROM milk_tea AS m WHERE m.pro_date IS NULL AND m.in_price>10 OR sale_price<5;

 

 

SELECT * FROM milk_tea WHERE milk_tea.prod_name='奶茶';
SELECT * FROM milk_tea WHERE NOT milk_tea.prod_name='奶茶';
SELECT * FROM milk_tea WHERE in_price IN(12.3,2.1,16.1);
SELECT * FROM milk_tea WHERE NOT prod_name IN('饼干','火腿肠','棒棒糖');

 

数据类型

VARCHAR不定长省空间，CHAR定长方便查找，适合固定长度的：手机号、身份证号

GBK一个中文占2个字节，最好自己测试一下不同编码的站字节数

 

 

 

 

聚合函数

COUNT对空值不计入

--聚合函数COUNT SUM AVG MAX MIN

--计算表的总行数
SELECT COUNT(*) FROM milk_tea;
SELECT COUNT(1) FROM milk_tea;

--计算某一列的行数 NULL不计入
SELECT COUNT(m.pro_date) FROM milk_tea AS m;

--DISTINCT计算种类数,去除重复值
SELECT COUNT(DISTINCT milk_tea.sale_price) FROM milk_tea;

--DISTINCT同时对多列,尽量一个语句不要多个COUNT函数
SELECT COUNT(DISTINCT milk_tea.in_price),COUNT(DISTINCT milk_tea.valid_month) FROM milk_tea;

 

 

--SUM直接忽略掉空值  155.5
SELECT SUM(m.in_price) FROM milk_tea AS m;

--171.5
SELECT SUM(m.sale_price) FROM milk_tea AS m;

--31.5 之所以不相等，是因为sale_price中有一个空值
SELECT SUM(m.sale_price-m.in_price) FROM milk_tea AS m;

--16加上IFNULL之后值就相等了
SELECT SUM(IFNULL(m.sale_price,0)-m.in_price) FROM milk_tea AS m;

--NULL 
SELECT m.sale_price-m.in_price FROM milk_tea AS m WHERE m.in_price=15.5;

---15.5结果正常
SELECT IFNULL(m.sale_price,0)-m.in_price FROM milk_tea AS m WHERE m.in_price=15.5;

--忽略空值 /8(空值的个数不计算在内)
SELECT AVG(m.sale_price) FROM milk_tea AS m;


SELECT MAX(m.in_price) FROM milk_tea AS m;
SELECT MIN(m.in_price) FROM milk_tea AS m;

 

 

数据分组：

SELECT m.net_w,SUM(m.sale_price) FROM milk_tea AS m GROUP BY m.net_w;

SELECT m.net_w,AVG(m.sale_price) FROM milk_tea AS m WHERE m.net_w IN('100g','150g') GROUP BY m.net_w;

SELECT m.net_w,SUM(m.sale_price) FROM milk_tea AS m GROUP BY m.net_w;

SELECT COUNT(o.prodname),o.quantity FROM order_list AS o WHERE o.quantity IN(1,2) GROUP BY o.quantity;

SELECT COUNT(o.prodname),o.quantity FROM order_list AS o GROUP BY o.quantity HAVING o.quantity IN(1,2);--不推荐

SELECT SUM(m.sale_price),m.net_w FROM milk_tea AS m  GROUP BY m.net_w;

--Having 对聚合之后的结果再过滤
SELECT SUM(m.sale_price),m.net_w FROM milk_tea AS m  GROUP BY m.net_w HAVING SUM(m.sale_price)>30;

SELECT p.class,SUM(p.cost) FROM prod_info AS p GROUP BY p.class;

SELECT p.class,COUNT(p.cost) FROM prod_info AS p GROUP BY p.class HAVING COUNT(p.cost)>4;

SELECT p.class,COUNT(p.cost) FROM prod_info AS p WHERE p.class<>'零食' GROUP BY p.class HAVING COUNT(p.cost)>4;

SELECT p.class,COUNT(p.cost) FROM prod_info AS p WHERE p.sale_price>10 GROUP BY p.class;

 

单表查询总结