Select .从指定表中取出指定的列的数据
-- 选出所有列数据
SELECT * FROM table_name
-- 选出列名” Col1”、” Col2” 的数据
SELECT Col1,Col2 ..FROM table_name
Where .被用来规定一种选择查询的标准
- 从” Persons”表中选出生活在” Sandnes” 的人
SELECT * FROM Persons WHERE City='Sandnes'
And & Or .在WHERE子句中AND和OR被用来连接两个或者更多的条件
- 用AND运算子来查找"Persons" 表中FirstName为”Tove”而且LastName为” Svendson”的数据
SELECT * FROM Persons
WHERE FirstName='Tove'
AND LastName='Svendson'
- 用OR运算子来查找"Persons" 表中FirstName为”Tove”或者LastName为” Svendson”的数据
SELECT * FROM Persons
WHERE firstname='Tove'
OR lastname='Svendson'
SELECT * FROM Persons WHERE
(FirstName='Tove' OR FirstName='Stephen')
AND LastName='Svendson'
Between…And .指定需返回数据的范围
- 用BETWEEN…AND返回LastName为从”Hansen”到”Pettersen”的数据
SELECT * FROM Persons WHERE LastName
BETWEEN 'Hansen' AND 'Pettersen'
SELECT * FROM Persons WHERE LastName
NOT BETWEEN 'Hansen' AND 'Pettersen'
Distinct .DISTINCT关键字被用作返回唯一的值
- 用DISTINCT关键字返回Company字段中唯一的值
SELECT DISTINCT Company FROM Orders
Order by .指定结果集的排序
SELECT Company, OrderNumber FROM Orders
ORDER BY Company
SELECT Company, OrderNumber FROM Orders
ORDER BY Company DESC
Group by.对结果集进行分组,常与汇总函数一起使用
- 按照Company字段进行分组,求出每个Company的Amout的合计
SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company
Having .指定群组或汇总的搜寻条件
- 按照Company字段进行分组,求出每个Company的Amout的合计在10000以上的数据
SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company HAVING SUM(Amount)>10000
Join .从两个或者以上的表中选取结果集时,你就会用到JOIN
- 用Employees的ID和Orders的ID相关联选取数据
SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.ID = Orders.ID
Alias .可用在表、结果集或者列上,为它们取一个逻辑名称
SELECT LastName AS Family, FirstName AS Name
FROM Persons
Insert Into .在表中插入新行
INSERT INTO Persons
VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes')
Update .更新表中原有数据
- 将Person表中LastName字段为”Rasmussen”的FirstName更新为”Nina”
UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen'
Delete .删除表中的数据
- 删除Person表中LastName为”Rasmussen”的数据
DELETE FROM Person WHERE LastName = 'Rasmussen'
参考链接
-- 增删改查只针对表内的数据进行操作
-- 查 --
-- 选出表格中所有的数据
SELECT * FROM table_check_data;
-- 筛选出满足条件的数据
SELECT * FROM table_check_data WHERE 时间 > date('now','-10 day');
-- 对筛选后的结果按指定列进行排序(默认升序:ASC)DES
SELECT * FROM table_check_data WHERE 时间 > date('now','-10 day') ORDER BY 时间 ASC;
-- 将指定列改名后再进行筛选
SELECT 时间 AS time, 开距 AS kai FROM table_check_data WHERE 时间 > date('now','-10 day');
-- 筛选出指定范围内(可以是两个时间段之间)的数据
SELECT * FROM table_check_data WHERE 时间 BETWEEN date('now','-10 day') AND date('now');
-- 增 --
-- 插入一行数据到表格中(所有列都要有数据)
INSERT INTO table_system_config VALUES(4, '2', '3');
-- 在指定列插入数据
INSERT INTO table_system_config(name, value) VALUES('2', '3');
-- 改 --
-- 对满足指定条件的列进行数据更新
UPDATE table_check_data SET 开距1 = 12 WHERE id = 2;
-- 满足指定两个条件的列进行数据更新
UPDATE table_check_data SET 开距1 = 12, 开距2 = 4 WHERE id = 2;
-- 删 --
-- 删除满足条件的数据
DELETE FROM table_check_data WHERE id = 1;