简单的sql语句

1.截断表

自增id会从1开始

truncate table users

2.插入语句

insert into table(***) values ('%s') % 'aaa'

或者是

insert into table(***) values ('{}').format('***')

3.查询语句

select * from ***

4.更新语句

需要修改的内容在前面，后面是条件，下面语句的意思是：把id为3的改成id为100

update table set id = '%s' , name = '%s' where id = '%s' % (100,张三,3)

或者是

update table set id = '{}' , name = '{}' where id = '{}'.format(100,张三,3)

5.删除语句

删除id大于20的数据

delete from table where id > '%s' % (20)

或者是

delete from table where id > '{}'.format(20)

6.查询重复的数据

查询一样的数据有多少条

SELECT count(*) count, Part FROM list GROUP BY Part HAVING count >0

7.1000条，倒叙排列

SELECT * FROM visitlist WHERE Date > '2021-05-05 00:00:00' ORDER BY ID DESC LIMIT 1000;

8.1000条，正序排列

SELECT * FROM visitlist WHERE Date > '2021-05-05 00:00:00' ORDER BY ID ASC LIMIT 1000;

9.多表查询

SELECT
	st.accession_no,
	lo.storage_path 
FROM
	study st
	INNER JOIN series se ON st.pk = se.study_fk
	INNER JOIN instance it ON se.pk = it.series_fk
	INNER JOIN location lo ON it.pk = lo.instance_fk 
WHERE
	st.id = '5485178907989083720' 
ORDER BY
	lo.pk DESC 
	LIMIT 1;

10.如果数值是连续的，应该使用between，而不要用in，如果数值是不连续的，可以分成多个SQL，用union all连接查询结果。

select id,name,age from persons where age between 22 and 24
 
select id,name,age from persons where age = 22
union all
select id,name,age from persons where age = 26
union all
select id,name,age from persons where age = 30

11.命令行查询字段的属性

第一种

select * from information_schema.columns where table_schema= '数据库名称' and TABLE_NAME='表结构名称'

实例：

select COLUMN_NAME,COLUMN_TYPE,DATA_TYPE from information_schema.columns where table_schema= '数据库名称' and TABLE_NAME='表结构名称'

第二种，更简单一些

show columns FROM list

12.删除数据库中所有的表

SELECT CONCAT('DROP TABLE ', table_name,';') FROM information_schema.`TABLES` WHERE table_schema='test';   -- test为数据库名称