MySQL基础操作——实用SQL

拼搏现实的明天。 2024-03-23 14:40 65阅读 0赞

搜集了一些实用的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;