SQL练习(Navicat premium)
1.根据需要选择连接的类型
测试连接
2.双击打开连接
3. 新建数据库
4. 新建表
查询
别名
表别名
表名称比较长时,可以通过别名简化,在表名称后面加上命名即可,如下方的cash_order c
SELECT c.orderTime,c.shishou,c.totalPrice,c.cid=1164 FROM cash_order c WHERE c.cid=1164
FROM cash_order c 即设置表cash_order的别名为 c
字段别名
在字段后边加上 AS 然后设置别名即可,如下方的 l.choujiang_id AS 抽奖ID
SELECT l.choujiang_id AS 抽奖ID,COUNT(l.prize_id) AS 次数 FROM choujiang_takeaward_logs l WHERE l.choujiang_id=37 and l.prize_id=104
结果如下:
多个条件
如果有多个条件,用 AND 连接
SELECT c.orderTime,c.shishou,c.totalPrice,c.cid=1164 FROM cash_order c WHERE c.cid=1164 AND c.totalPrice<5 and c.totalPrice>=1
排序
排序时,用 ORDER BY,默认是升序(ASC),如果要倒序显示,加DESC
SELECT c.orderTime,c.shishou,c.totalPrice,c.cid=1164 FROM cash_order c WHERE c.cid=1164 AND c.totalPrice<5 and c.totalPrice>=1 ORDER BY c.totalPrice DESC
模糊查询,LIKE
SELECT o.orderID,p.ProductName as 产品名称, d.UnitPrice as 价格,d.Quantity as 数量 FROM Orders AS o,Order_Details AS d ,Products as p where o.RequiredDate LIKE '1998-05%' and o.orderID=d.orderID and d.ProductID=p.ProductID
限制查询数量
限制查询的数量,用关键字 LIMIT
SELECT c.orderTime,c.shishou,c.totalPrice,c.cid=1164 FROM cash_order c WHERE c.cid=1164 AND c.totalPrice<5 and c.totalPrice>=1 ORDER BY c.totalPrice DESC LIMIT 10
查询结果如下:
统计搜索结果
统计搜索结果数量,可以用 COUNT
SELECT l.choujiang_id,l.prize_id,COUNT(l.prize_id) FROM choujiang_takeaward_logs l WHERE l.choujiang_id=37 AND l.prize_id=104
结果如下:
统计搜索个数
COUNT
SELECT COUNT(`store_id`),`store_id` FROM `store_goods_box_code` WHERE `state` =1 GROUP BY `store_id`
搜索结果分组显示
根据不同的字段,进行分组显示GROUP BY,同类型的会自动合在一起显示
SELECT l.choujiang_id,l.prize_id,COUNT(l.prize_id) FROM choujiang_takeaward_logs l WHERE l.choujiang_id=37 GROUP BY l.prize_id
结果如下:
SELECT p.ProductName as 产品名称,d.UnitPrice as 价格,SUM(d.Quantity) as 数量 FROM Orders AS o,Order_Details AS d ,Products as p where o.RequiredDate LIKE '1998-05%' and o.orderID=d.orderID and d.ProductID=p.ProductID GROUP BY 产品名称,价格
数据累加
搜索结果数据累加,用 SUM
SELECT ca.billRequestPhone,SUM(ca.realTotalPrice) FROM cash_order ca WHERE ca.billRequestPhone=138****
结果如下:
执行多条指令
多条指令时,在指令的末尾用 分隔符 “ ; ” 可实现
阿里云语句查询
联表查询 DMS
SELECT s.`barcode` AS 条形码, d.`store_id` , d.`created_at` , d.`before` AS 操作前 ,d.`after` AS 操作后 ,d.`field` FROM `store_goods_log` d , `store_goods` s WHERE d.`goods_id` = s.`id` AND d.`store_id` = 3389 AND d.`field`=3 AND s.`barcode` = 6901798104595
直接输入查询
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
删除
删除某一行
DELETE FROM device_staff WHERE store_id=3393 AND mp_role_id=3743
运行结果: