从表中查询列c1、c2中的数据
select value,set_time from sys_config
查询表中的所有行和列
select * from sys_config sc
查询数据并使用条件筛选行
select value,set_time from sys_config sc WHERE value =100
select value,set_time from sys_config sc WHERE value <>100
select value,set_time from sys_config sc WHERE value like '%F%'
select value,set_time from sys_config sc WHERE value not like '%F%'
select value,set_time from sys_config sc WHERE value ISNULL
select value,set_time from sys_config sc WHERE value is not NULL
select value,set_time from sys_config sc WHERE value BETWEEN50AND200
select value,set_time from sys_config sc WHERE value IN(64,65,66)
select value,set_time from sys_config sc WHERE value not IN(64,65,66)查询表中的不同行(去重)
select distinct value,set_time, from sys_config sc
按升序或降序对结果集排序
select * from sys_config sc ORDERBY set_time ASC
select * from sys_config sc ORDERBY set_time DESC跳过行的偏移并返回下n行(分页)SELECT*FROM sys_config sc ORDERBY value limit 10OFFSET0
使用聚合函数对行进行分组
SELECT value,MAX(set_time),count(*)FROM sys_config sc GROUPBY value,set_time
SELECT value,avg(set_time)FROM sys_config sc GROUPBY value,set_time
使用HAVING子句筛选组(过滤)SELECT value,avg(set_time)FROM sys_config sc GROUPBY value,set_time having value <>100
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
从多个表查询
内部连接 t1 和 t2
SELECT n.*,a.ages FROM names n INNERJOIN ages a on n.name = a.name
左连接t1和t1
SELECT n.*,a.ages FROM names n left JOIN ages a on n.name = a.name
1
2
3
4
修改数据
在表格中插入一行
INSERTINTOsys_config(variable,value,set_time,set_by)VALUES(1,2,'2023-07-11 18:29:24',4);
在表格中插入多行
INSERTINTOsys_config(variable,value,set_time,set_by)VALUES(15,2,'2023-07-11 18:29:24',4),(5,6,'2023-07-11 18:29:24',7);将行从t2插入t1(跨表)INSERTINTOnames(id,name,num)SELECT id,name,1AS num FROM ages;
更新列c1中所有行的新值
UPDATE names SET num =1;
更新列c1、c2中与条件匹配的值
UPDATE names SET num =11 where name =2;
删除表中的行子集
DELETEFROM names WHERE id =1;