"SQLite 官网：https://www.sqlite.org/index.html 菜鸟教程文档：https://www.runoob.com/sqlite/sqlite-tutorial.html 一、数据库简介与基本语法 1.1- 数据库的作用 txt 去保存 1 万行的数据.（数据量超过一 ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 1 浏览 • 4 个月前

SQLite入门指南：轻松学习带有实例的完整教程（含示例）

SQLite 官网：https://www.sqlite.org/index.html
菜鸟教程文档：https://www.runoob.com/sqlite/sqlite-tutorial.html

一、数据库简介与基本语法

1.1- 数据库的作用

txt 去保存 1 万行的数据.（数据量超过一定量级 [大于 1w]）
数据格式的管理，以及数据内容的分片

1.2- 数据库的选择

目前所说：都是 SQL（结构化查询语言）语句
单机版本：
- ACCESS(微软)
  - 最大缺点：必须要安装 Office、数据量、查询速度、写法有少许不同
- SQLite
  - 唯一携带一个 DLL 驱动文件（几百 K）
  - 缺点：超过 10w 的，不建议使用。
企业级数据库：
- MsSQLServer
  - 数据量：5000w 没什么问题
  - 最适合 C#
- My SQL：
  - 要一份非.net 官方的驱动
  - 开源
  - 相对于 MSSQL Server，优势是体积小，跨平台
- Oracle：
  - 需要非官方驱动
  - 适合 JAVA
- MongDB：
  - 后期支秀
  - 非关系型数据库

二、数据库增删改查语法与实例

2.1- 创建表

（1）下载并打开这个工具

（2）创建一个数据库，然后创建一个表如下：

（3）添加列明、数据类型、约束

2.2- 增删改查

-- 插入 -- 注意：Integer 允许自动增长（不要被 Identity 忽悠） insert into UserInfo(UserId,UserNames,UserPasss,RegDate) values(1001,'admin','admin','2021-01-21') insert into UserInfo(UserId,UserNames,UserPasss,RegDate) values(1002,'sanha','sanha', datetime('now','localtime')) -- 查询 select * from UserInfo --Limit 跳过几个, 取几个 --Limit 2,2 跳过 2 个，取 2 个 -- 删除 delete from UserInfo where UserId=1002

-- 修改 update UserInfo set UserNames='sanha_update' where UserId=1002

2.3- 使用 WinForm 和 SQLite 做登录注册

（1）管理 Nuget 程序包，下载这个类库：

1.1- 将数据库文件拷贝在 Bin 路径下。

（2）写一个 SQLite 帮助类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Threading.Tasks;
using System.Configuration;
namespace SQLite
{
public class SQLiteHelper
{
    <span class="hljs-keyword">private</span> <span class="hljs-keyword">readonly</span> <span class="hljs-built_in">string</span> _str;
    <span class="hljs-function"><span class="hljs-keyword">public</span> <span class="hljs-title">SQLiteHelper</span>(<span class="hljs-params"><span class="hljs-built_in">string</span> str</span>)</span> {
        _str = str;
    }

    <span class="hljs-comment">//获取连接字符串</span>
    <span class="hljs-comment">//private static readonly string str = ConfigurationManager.ConnectionStrings["DBFilerURL"].ConnectionString;</span>


    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;summary&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> 做增删改的功能</span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;/summary&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;param name="sql"&gt;</span>SQL语句<span class="hljs-doctag">&lt;/param&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;param name="ps"&gt;</span>SQL语句中的参数<span class="hljs-doctag">&lt;/param&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;returns&gt;</span>受影响的行数<span class="hljs-doctag">&lt;/returns&gt;</span></span>
    <span class="hljs-function"><span class="hljs-keyword">public</span>  <span class="hljs-built_in">int</span> <span class="hljs-title">ExecuteNonQuery</span>(<span class="hljs-params"><span class="hljs-built_in">string</span> sql, <span class="hljs-keyword">params</span> SQLiteParameter[] ps</span>)</span>
    {
        <span class="hljs-comment">//连接数据库</span>
        <span class="hljs-keyword">using</span> (SQLiteConnection con = <span class="hljs-keyword">new</span> SQLiteConnection(_str))
        {
            <span class="hljs-keyword">using</span> (SQLiteCommand cmd = <span class="hljs-keyword">new</span> SQLiteCommand(sql, con))
            {
                con.Open();<span class="hljs-comment">//打开数据库</span>
                <span class="hljs-keyword">if</span> (ps != <span class="hljs-literal">null</span>)
                {
                    cmd.Parameters.AddRange(ps);<span class="hljs-comment">//参数，加集合(ps)</span>
                }
                <span class="hljs-keyword">return</span> cmd.ExecuteNonQuery();
            }
        }
    }


    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;summary&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> 查询首行首列</span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;/summary&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;param name="sql"&gt;</span>SQL语句<span class="hljs-doctag">&lt;/param&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;param name="ps"&gt;</span>SQL语句的参数<span class="hljs-doctag">&lt;/param&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;returns&gt;</span>返回首行首列object<span class="hljs-doctag">&lt;/returns&gt;</span></span>
    <span class="hljs-function"><span class="hljs-keyword">public</span>  <span class="hljs-built_in">object</span> <span class="hljs-title">ExecuteScalar</span>(<span class="hljs-params"><span class="hljs-built_in">string</span> sql, <span class="hljs-keyword">params</span> SQLiteParameter[] ps</span>)</span>
    {
        <span class="hljs-keyword">using</span> (SQLiteConnection con = <span class="hljs-keyword">new</span> SQLiteConnection(_str))
        {
            <span class="hljs-keyword">using</span> (SQLiteCommand cmd = <span class="hljs-keyword">new</span> SQLiteCommand(sql, con))
            {
                con.Open();
                <span class="hljs-keyword">if</span> (ps != <span class="hljs-literal">null</span>)
                {
                    cmd.Parameters.AddRange(ps);
                }
                <span class="hljs-keyword">return</span> cmd.ExecuteScalar();
            }
        }
    }


    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;summary&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> 查询多行</span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;/summary&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;param name="sql"&gt;</span>SQL语句<span class="hljs-doctag">&lt;/param&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;param name="ps"&gt;</span>SQL语句的参数<span class="hljs-doctag">&lt;/param&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;returns&gt;</span>返回多行SQLiteDataReader<span class="hljs-doctag">&lt;/returns&gt;</span></span>
    <span class="hljs-function"><span class="hljs-keyword">public</span>  SQLiteDataReader <span class="hljs-title">ExecuteReader</span>(<span class="hljs-params"><span class="hljs-built_in">string</span> sql, <span class="hljs-keyword">params</span> SQLiteParameter[] ps</span>)</span>
    {
        SQLiteConnection con = <span class="hljs-keyword">new</span> SQLiteConnection(_str);
        <span class="hljs-keyword">using</span> (SQLiteCommand cmd = <span class="hljs-keyword">new</span> SQLiteCommand(sql, con))
        {
            <span class="hljs-keyword">if</span> (ps != <span class="hljs-literal">null</span>)
            {
                cmd.Parameters.AddRange(ps);
            }
            <span class="hljs-keyword">try</span>
            {
                con.Open();
                <span class="hljs-keyword">return</span> cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }
            <span class="hljs-keyword">catch</span> (Exception ex)
            {
                con.Close();
                con.Dispose();
                <span class="hljs-keyword">throw</span> ex;
            }
        }
    }


    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;summary&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> 查询数据表</span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;/summary&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;param name="sql"&gt;</span>SQL语句<span class="hljs-doctag">&lt;/param&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;param name="ps"&gt;</span>SQL语句中的参数<span class="hljs-doctag">&lt;/param&gt;</span></span>
    <span class="hljs-comment"><span class="hljs-doctag">///</span> <span class="hljs-doctag">&lt;returns&gt;</span>返回表DataTable<span class="hljs-doctag">&lt;/returns&gt;</span></span>
    <span class="hljs-function"><span class="hljs-keyword">public</span> DataTable <span class="hljs-title">ExecuteTable</span>(<span class="hljs-params"><span class="hljs-built_in">string</span> sql, <span class="hljs-keyword">params</span> SQLiteParameter[] ps</span>)</span>
    {
        DataTable dt = <span class="hljs-keyword">new</span> DataTable();
        <span class="hljs-keyword">using</span> (SQLiteDataAdapter sda = <span class="hljs-keyword">new</span> SQLiteDataAdapter(sql, _str))
        {
            <span class="hljs-keyword">if</span> (ps != <span class="hljs-literal">null</span>)
            {
                sda.SelectCommand.Parameters.AddRange(ps);
            }
            sda.Fill(dt);
            <span class="hljs-keyword">return</span> dt;
        }
    }

}

}

（3）写一个简单的界面

（4）在后端代码中先写上这些代码

// 获取数据库路径
 public static string SQLitePath = AppDomain.CurrentDomain.BaseDirectory + "db/SQLiteDemo1.db";
// 数据库连接字符串
public  static string str = string.Format("Data Source={0};Pooling=true;FailIfMissing=false;", SQLitePath);
// 实例化对象
SQLiteHelper SQLite = new SQLiteHelper(str);

（5）【登录】的逻辑

  private void button2_Click(object sender, EventArgs e)
        {
            string name = this.textBox1.Text.ToString();
            string password = this.textBox2.Text.ToString();
            // 参数化查询
            string sql = string.Format("select UserId from UserInfo where UserNames=@name and UserPasss=@password;");
            SQLiteParameter[] parameters =new   SQLiteParameter[]
            {
                new SQLiteParameter("@name",name),
                new SQLiteParameter("@password",password)
            };
        <span class="hljs-built_in">object</span> obj=SQLite.ExecuteScalar(sql, parameters);
        <span class="hljs-built_in">int</span> i =Convert.ToInt32(obj);
        <span class="hljs-keyword">if</span> (i &gt; <span class="hljs-number">0</span>)
        {
            <span class="hljs-keyword">this</span>.label4.Text = <span class="hljs-string">"登录成功！"</span>;
            <span class="hljs-keyword">this</span>.label4.Show();
        }
        <span class="hljs-keyword">else</span> {
            <span class="hljs-keyword">this</span>.label4.Text = <span class="hljs-string">"登录失败！"</span>;
            <span class="hljs-keyword">this</span>.label4.Show();
        }
    }

（6）【注册】的逻辑

private void button1_Click(object sender, EventArgs e)
        {;
            string name = this.textBox1.Text.ToString();
            string password = this.textBox2.Text.ToString();
            // 参数化查询
            string sql = string.Format("insert into UserInfo(UserId,UserNames,UserPasss,RegDate) values(@userid,@username,@passwod,datetime('now','localtime'))");
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@userid",new Random().Next(10)),
                new SQLiteParameter("@username",name),
                new SQLiteParameter("@passwod",password)
            };
        <span class="hljs-built_in">object</span> obj = SQLite.ExecuteNonQuery(sql, parameters);
        <span class="hljs-built_in">int</span> i = Convert.ToInt32(obj);
        <span class="hljs-keyword">if</span> (i &gt; <span class="hljs-number">0</span>)
        {
            <span class="hljs-keyword">this</span>.label4.Text = <span class="hljs-string">"注册成功！"</span>;
            <span class="hljs-keyword">this</span>.label4.Show();
        }
        <span class="hljs-keyword">else</span>
        {
            <span class="hljs-keyword">this</span>.label4.Text = <span class="hljs-string">"注册失败！"</span>;
            <span class="hljs-keyword">this</span>.label4.Show();
        }
    }

原文链接：
https://www.cnblogs.com/kimiliucn/p/17595354.html

__EOF__

本文作者： 西瓜程序猿

本文链接： https://www.cnblogs.com/kimiliucn/p/17595354.html

关于博主： 评论和私信会在第一时间回复。或者直接私信我。

声援博主： 如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。

SQLite入门指南：轻松学习带有实例的完整教程（含示例）

一、数据库简介与基本语法

1.1- 数据库的作用

1.2- 数据库的选择

二、数据库增删改查语法与实例

2.1- 创建表

2.2- 增删改查

2.3- 使用 WinForm 和 SQLite 做登录注册

相关帖子

随便看看

SQLite入门指南：轻松学习带有实例的完整教程（含示例）