MySQL基础操作——实用SQL

搜集了一些实用的SQL，如果还不知道的话可能要多花几步才能实现相同的功能了。

9de7bb31d0644e7393039222babfed10 欢迎收藏。

创建测试表

DROP TABLE IF EXISTS bom;
CREATE TABLE bom(
  id INT PRIMARY KEY NOT NULL,
  name VARCHAR(100) NOT NULL,
  weight int NULL,
  material enum('P','M'),
  parent_id INT NULL,
  INDEX (parent_id),
  FOREIGN KEY (parent_id) REFERENCES bom(id)
);           

准备数据;

INSERT INTO bom(id,name,parent_id) VALUES
(1, "笔记本", NULL), 
(2, "主板", 1), 
(3, "外壳", 1),
(4, "电路板", 2),
(6, "电容", 4),
(7, "电阻", 4),
(5, "键盘", 3);           

实用SQL 1：循环插入数据

DROP PROCEDURE IF EXISTS batchInsert;
DELIMITER $
CREATE PROCEDURE batchInsert(in args int)
BEGIN
    DECLARE i INT DEFAULT 1;
    START TRANSACTION;
    WHILE i<=args DO
      INSERT INTO bom(id,name,weight,material,parent_id) VALUES(10+i,SUBSTRING(MD5(RAND()),1,10), ROUND(RAND() * 100), IF(MOD(ROUND(RAND() * 100),2) = 0, 'P', 'M'),4);  
      SET i = i+1;
    END WHILE;
    COMMIT;
END $
DELIMITER ;

call batchInsert(10);            

实用SQL 2：CTE (公共表表达式)

找到物料的bom路径

WITH RECURSIVE test(id, name, path)
AS
(
  SELECT id, name, CAST(id AS CHAR(200))
    FROM bom WHERE parent_id IS NULL
   UNION ALL
  SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    FROM test AS ep JOIN bom AS e  ON ep.id = e.parent_id
)
SELECT * FROM test ORDER BY path;           

实用SQL 3：分页查询

此处只是为了演示当前数据的显示效果。 实际情况可能分页数是20,50,100这种了。

select * from bom limit 7,100; -- 通常的写法 ;
select * from bom where id>7 limit 100;  -- 推荐的优化写法 ;           

实用SQL 4：插入或替换

如果你想要插入一行新数据，如果已经存在该行数据，则先删后插该行数据

REPLACE INTO bom (id, name, parent_id) VALUES (20,'usb接口',4);
REPLACE INTO bom (id, name, parent_id) VALUES (21,'sd卡接口',4);           

记得再次查询表，体会命令的效果

实用SQL 5：插入或更新

INSERT INTO bom (id, name, parent_id) VALUES (21, 'hdmi接口', 1) ON DUPLICATE KEY UPDATE name = 'hdmi接口', parent_id=4;
INSERT INTO bom (id, name, parent_id) VALUES (22, '耳机接口', 1) ON DUPLICATE KEY UPDATE name = '耳机接口', parent_id=4;           

记得再次查询表，体会命令的效果

实用SQL 6：插入或忽略

如果你想要插入一行新数据，如果已经存在该行数据，则忽略该行数据

INSERT IGNORE INTO bom (id, name, parent_id) VALUES (22, '雷电接口', 4);
INSERT IGNORE INTO bom (id, name, parent_id) VALUES (23, '雷电接口', 4);           

记得再次查询表，体会命令的效果

实用SQL 7：SQL 中 判断语句

SELECT id,name,IF(material ='P', '塑料', if(material ='M','金属','复合材料')) 性别 FROM bom;
SELECT id,name,CASE material WHEN 'P' THEN '塑料' WHEN 'M' THEN '金属' ELSE '复合材料' END 性别 FROM bom;