-- 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;