数据库查询sql——高级

基础篇——增删改查

1.新增语句

INSERT INTO 表名(列名1，列名2，列名3，......) VALUES(值1，值2，值3，......)

# 新增语句可以有两种方式
# 1.指定列名，对应属性值
INSERT INTO do_pack_count(user_id,packed_number,packed_goods_number) values(1,1,1)
# 2.直接插入值
INSERT INTO do_pack_count values(1,1,1)

在sql查询中操作界面如图：

image.png

2. 删除语句

DELECT FROM 表名 WHERE 条件1

DELETE FROM  do_pack_count  WHERE user_id=1

image.png

3.修改（更新）语句

UPDATE 表名 SET 字段1=值1，字段2=值2,..... WHERE 条件1

UPDATE do_pack_count  SET packed_goods_number=8  where user_id=1

image.png

4.查询语句

SELECT * FROM 表名 WHERE 条件1
SELECT 列名1，列名3...... FROM 表名 WHERE 条件1

# 查询表中所有数据
SELECT * FROM do_pack_count ;
# 查询符合条件的，指定列的值
SELECT packed_number,packed_goods_number FROM do_pack_count WHERE user_id=162

image.png
image.png

5.聚合函数

我们通常所有的聚合函数有：
①count(id或其他字段名)：计数
②sum(字段名)：求和
③avg(字段名)：求平均值
聚合函数和GROUP BY语句通常是一起使用

6.having的用法以及与where的不同

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

语法：

SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

同样使用本文中的学生表格，如果想查询平均分高于80分的学生记录可以这样写：

SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore

FROM student

GROUP BY id

HAVING AVG(score)>=80;

在这里，如果用WHERE代替HAVING就会出错

高级篇——多表查询左连接

左连接查询

SELECT 表1*，表2.列1，表2.列2，表2.列3 FROM 表1 LEFT JOIN 表2 ON 表1.id=表2.表1id
WHERE 条件1。一般经常配合聚合函数COUNT,SUM等一起使用

  SELECT goods.id AS id, 
                 goods_config.id AS goods_spu_id, 
                 goods_config.name AS goods_spu_name, 
                 SUM(CASE WHEN (whi.transport_state = 'onway' OR buyr.buy_receipt_state = 'onway') AND line.cost_time <= '2019-01-01 23:59:59'  THEN 0 ELSE line.qty_remaining END) AS goods_qty,
                 SUM(CASE WHEN (whi.transport_state = 'onway' OR buyr.buy_receipt_state = 'onway') AND line.cost_time <= '2019-01-31 23:59:59'  THEN 0 ELSE line.qty_remaining END) AS goods_qty_end,
                 SUM(CASE WHEN (whi.transport_state = 'onway' OR buyr.buy_receipt_state = 'onway') AND line.cost_time <= '2019-01-31 23:59:59'  AND wh.site='foreign' THEN 0 ELSE line.qty_remaining END) AS goods_qty_end_out,
                 SUM(line.qty_remaining * line.cost_unit) AS cost
  FROM wh_move_line line
                LEFT JOIN wh_move whm ON whm.id = line.move_id
                LEFT JOIN wh_internal whi ON whm.id = whi.move_id
                LEFT JOIN buy_receipt buyr ON whm.id = buyr.buy_move_id
                LEFT JOIN warehouse wh ON line.warehouse_dest_id = wh.id
                LEFT JOIN goods goods ON line.goods_id = goods.id
                LEFT JOIN goods_config ON goods_config.id=goods.goods_config_id
   WHERE wh.type in ('stock','supplier')
                AND wh.active=true
                AND wh.code not in ('Supplier','General')
                AND line.state in ('done','onway')
                AND line.qty_remaining!=0
                AND line.cost_time <= '2019-01-31 23:59:59'
                AND wh.site in ('inland','foreign')
                AND (goods.no_stock IS NULL OR goods.no_stock = false)
                AND goods.is_group=False
                AND goods.not_saleable='on_sale'
  GROUP BY goods.id,goods_config.id,goods_config.name
  ORDER BY goods.id 

聚合函数通常要和GROUP BY 一起使用，要求查询的指定的列中，未使用聚合的函数的列（goods_spu_id，goods_spu_name）必须要出现在GROUP BY后面。