ASP.NET MVC 从入门到放弃(Day15)-SQL 语法基本介绍

接下来讲讲SQL基本语法介绍如下
以下会讲一些常用而且基础的语法

资料表查询

SELECT "栏位名" FROM "表格名";

ex:

Select * From Member

Select:选取
*:全部
From:来自於
Member:哪张资料表

如果要选择资料表某一个栏位如下

ex:

Select MemberID From Member

资料表查询判断

SELECT "栏位名" FROM "表格名"WHERE "条件";

ex:

Select * From Member Where MemberID = '0001'

要取得会员边号0001 会员

注解如果程序有碰到多个Where 用法会如下(加And)

Select * From Member Where 1 = 1 And MemberID = '0001' And Type = 1

注解:Where 1 = 1 是因为不确定第一个判断是哪个栏位可以用 1 = 1

资料表范围查询

SELECT "栏位名"
FROM "表格名"
WHERE "栏位名" IN ('值一', '值二', ...);

ex:

Select * From Member Where MemberID in('0001','0002')

要取得会员边号0001 、0002会员

SELECT "栏位名"
FROM "表格名"
WHERE "栏位名" BETWEEN '值一' AND '值二';

资料表区间查询
ex:

SELECT *
FROM Member
WHERE BeginDate BETWEEN '2021/01/01' AND '2021/12/30';

注解:常用於日期区间的判断

资料表区间模糊

SELECT "栏位名"
FROM "表格名"
WHERE "栏位名" LIKE {模式};

ex:

SELECT *
FROM Member
WHERE MemberName LIKE '%A%';

注解:模糊查询 ，常用於需要比对字串(效能较差)

资料表排序

SELECT "栏位名"
FROM "表格名"
[WHERE "条件"]
ORDER BY "栏位名" [ASC, DESC];

ex:

Select * From Member ORDER BY MemberID DESC;

注解:ASC 是小到大 DESC 大到小排序

资料表新增

INSERT INTO "表格名" ("栏位1", "栏位2", ...)
VALUES ("值1", "值2", ...);

ex:

INSERT INTO Member(MemberID, MemberName)
VALUES ('0001', 'AAA');

资料表修改

UPDATE "表格名"
SET "栏位1" = [新值]
WHERE "条件"

ex:

UPDATE Member
SET MemberName = 'VV'
WHERE MemberID = '0001'

资料表删除

DELETE FROM "表格名"
WHERE "条件";

ex:

DELETE FROM Member

注解:其实绝大部分不会下这个语法，因为要保存资料的完整性。

简单介绍到这边後续请看1keydata