SQL语句 for Access

SQL语句 for Access

项目需求，建立本地轻量级数据库。

• 创建表

create table 表名 (id int primary key ,name varchar(100) not null )

• 删除表

drop table [表名]

• 复制表结构为新的表

select * into NewTable from OldTable where 1=2

• 复制表结构及内容到新的表

select * into newtable from oldtable

• 判断表存在 C#

   public static bool TableExists(string table)
        {
            OleDbConnection conn;
            conn = new OleDbConnection(GetConnectionString());
            conn.Open();
            var exists = conn.GetSchema("Tables", new string[4] { null, null, table, "TABLE" }).Rows.Count > 0;
            conn.Close();
            return exists;
        }

• insert 增

insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)

• delete删

delete from 数据表 where 条件表达式

• update改

sql="update 数据表 set字段名=字段值 where 条件表达式"

sql="update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式"

• select查

sql="select * from 数据表 where字段名=字段值 order by字段名[desc]"

• GROUP BY 分组

SELECT 性别, count(*) AS 人数
FROM 学生
GROUP BY 性别;

• Count总数 & UNION合并

SELECT  COUNT(*) AS 总数
FROM     [表名]
WHERE   (Type = 1) AND (Result = 'Ture') AND (NOT(Tpye = '男'))
UNION ALL
SELECT  COUNT(*) AS 总数
FROM     [表名] [表名_1]
WHERE   (Type = 1) AND (Result = 'False')

---