数据库增删改查命令

-- 1、统计行数
SELECT
?? ?count(*)?
FROM
?? ?equipment;
?? ?
-- 2、分组
SELECT
?? ?user_enters_contract,
?? ?count(*)?
FROM
?? ?equipment?
WHERE
?? ?user_enters_contract IS NOT NULL?
GROUP BY
?? ?user_enters_contract?
ORDER BY
?? ?count(*) DESC;

-- 3、并集
SELECT
?? ?item_code?
FROM
?? ?equipment UNION ALL
SELECT
?? ?item_code?
FROM
?? ?sheet1;

-- 4、交集
SELECT
?? ?t1.item_code,
?? ?count(*)?
FROM
?? ?sheet1 t1
?? ?JOIN equipment t2 ON t1.item_code = t2.item_code?
GROUP BY
?? ?t1.item_code?
ORDER BY
?? ?count(*) DESC;

-- 5、差集
SELECT
?? ?t1.item_code,
?? ?count( 1 )?
FROM
?? ?sheet1 t1
?? ?LEFT JOIN equipment t2 ON t1.item_code = t2.item_code?
WHERE
?? ?t1.item_code = '02358408'?
GROUP BY
?? ?t1.item_code;

-- 6、更新
UPDATE equipment?
SET user_enters_contract = '3300252204260R'?
WHERE
?? ?id IN (
?? ?SELECT
?? ??? ?a1?
FROM
?? ?( SELECT max( id ) AS a1 FROM `device_inventory`.`equipment` WHERE user_enters_contract = '3300252204260R' and id ='1111' GROUP BY shipment_barcode ) AS t1)

-- 7、删除
DELETE FROM equipment?
?WHERE
?? ?id IN (
?? ?SELECT
?? ?a1?? ??
? FROM
?? ?( SELECT max( id ) as a1, shipment_barcode FROM `device_inventory`.`equipment` WHERE `user_enters_contract` = '3300252204260R' GROUP BY shipment_barcode ) AS t1)

-- 8、创建新表
CREATE TABLE new_table AS
SELECT * FROM old_table
WHERE 1 = 0; -- 这里是一个占位符，实际上不会执行任何操作

--9、将查询结果插入到新表中
INSERT INTO new_table
SELECT * FROM old_table;

--10、创建索引

CREATE INDEX index_name
ON table_name (column1, column2, ...);

--11、创建游标

在 SQL 中，游标是一种数据库对象，用于从结果集中检索数据。创建游标的语法如下：
DECLARE cursor_name CURSOR FOR select_statement;

其中，`cursor_name` 是你要为游标指定的名称，`select_statement` 是一个查询语句，用于从结果集中检索数据。

例如，创建一个名为 `employees_cursor` 的游标，用于从 `employees` 表中检索所有员工的信息：
DECLARE employees_cursor CURSOR FOR SELECT * FROM employees;
?