数据库操作sql语句

1、删除指定行记录

删除id小于19的所有行数据：

DELETE FROM d_cbi_history_20200331 WHERE id < 19；

2、给指定字段插入数据

INSERT INTO c_b_signal (signalId,signalType,signalNr,signalName) VALUES (5,1,5,“XF”);

3、创建相同的表

create table employee2 like employee;

4、更改表名

方法1：
ALTER TABLE c_b_outerSignal RENAME TO c_b_confSignal;
方法2：
RENAME TABLE c_b_pio_i3 TO c_b_pio_i;

5、建表

CREATE TABLE c_b_outer(
id INT PRIMARY KEY,//主键
signal VARCHAR(50),
down INT
);

6、复制表

例1：
INSERT INTO c_b_lkroute SELECT * FROM c_b_confroute;
例2：
INSERT INTO c_b_signal22(signalId,signalType,signalNr,signalName) SELECT signalId,signalType,signalNr,signalName FROM c_b_signal WHERE signalId = 2；

7、复制表列数据

UPDATE c_b_signal INNER JOIN c_b_confsignal ON c_b_signal.signalId = c_b_confsignal.id
SET c_b_signal.signalName = c_b_confsignal.signalName;

8、删除表

DROP TABLE c_b_interSignal;

9、删除表中列数据

UPDATE c_b_line SET lineDir=NULL WHERE lineDir=2;

10、删除字段

ALTER TABLE test_table DROP COLUMN test_value;

11、插入多行数据

INSERT INTO c_b_signal22(signalType,signalNr,signalName) SELECT
signalType,signalNr,signalName FROM c_b_signal WHERE signalId > 5；

12、增加字段

ALTER TABLE c_b_outerSignal ADD signalType INT;

13、order by排序

1、 从数据库表获取全部数据按字段A的升序排列
　 select *from table_name order by 字段A ASC
2、从数据库表获取全部数据按字段A的降序排列
　　select *from table_name order by 字段A DESC
3、从数据库表获取满足条件的数据按字段A的升序排列
　　select *from table_name where [condition] order by 字段A ASC
4、从数据库表获取满足条件的数据按字段A的升序排列
　　select *from table_name where [condition] order by 字段A DES

14、删除带主键自增数据

TRUNCATE TABLE c_b_signal;

15、查询表中某多个字段（模糊查询）

select * from d_cbi_history_20200415 where equipmentTypeCode = 9 and codeList like ‘%1%’;
like是模糊查询，codeList like '%1%'是指codeList 的值为1的对应记录。codeList like '%1’指codeList的值从1到后面的对应记录。codeList like '1%'指codeList 的值是1之前的对应记录。

16、删除表中多行记录

delete from d_cbi_history_20200409 where id > 999；

17、查询long类型时间戳记录

SELECT * FROM d_cbi_history_alarm where alarmTime = ‘1583730000000’ ;

18、查询某字段包括特定值的记录

SELECT * FROM d_cbi_history_20200409 WHERE equipmentTypeCode in (15,17);
查询equipmentTypeCode 等于15或17的记录。

19、修改一列所有的数据

UPDATE c_sectioninfo SET stationGapId = 1

20、计算查出的记录条数（模糊查）

SELECT COUNT(*) FROM s_hardware_info where controlName LIKE ‘%DS6-80%’;

21、联表查询：内连接inner join

select d.id,d.alarmTime,d.alarmRecoveryTime,s.alarmSort,s.alarmType,s.alarmSubType,s.alarmDescription,s.alarmLevel,s.remarks from
(select * from d_tsrs_history_alarm where 1=1 and alarmTime >= 1594105463672 and alarmTime<=1594877078562 and alarmRecoveryTime is not null limit 4 ) d
inner join s_alarm_info s on d.alarmTypeId=s.alarmTypeId and d.alarmSubTypeId=s.alarmSubTypeId and d.alarmDescriptionId=s.alarmDescriptionId