数据库管理系列首要性意义

SQLite常用命令

1。数据库基本概念:
数据、数据库。数据模型/DBMS(数据库管理连串)/DBS(数据库系统)
2。数据库内部整合
二维表、视图,索引
3,数据库管理体系重点功能:
陆大效益
四数据库操作语言:
SQL语言完毕增加和删除查改**1。数据库基本概念:
多少、数据库。数据模型/DBMS(数据库管理体系)/DBS(数据库系统)
二。数据库内部整合
二维表、视图,索引
三,数据库管理类别关键作用:
陆大服从
肆数据库操作语言:
SQL语言完成增加和删除查改
实施内容:
一.用VS20十创制名叫
insert into T_Student(sno,sname,ssex,sage,sdept)
values(”,”,”,”,”)
select \
from T_Student
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2001′,’李勇’,’男’,’20’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2002′,’刘晨’,’女’,’18’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2003′,’王敏’,’女’,’19’,’MA’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2004′,’张立’,’男’,’22’,’IS’)
insert into T_Course(Cno,Cname,Ccode) values (‘1′,’数据库’,’1′)
insert into T_Course(Cno,Cname,Ccode) values (‘2′,’英语’,’4′)
insert into T_SC(Cno,Sno,Grade) values (‘1′,’2001′,’88’)
insert into T_SC(Cno,Sno,Grade) values (‘2′,’2002′,’80’)
追加一条学号为200陆,姓名称叫张扬
insert into T_student(sno,sname,ssex) values (”,”,”)
(sno,sname,ssex,sage,sdept) (”,”,”,”,”)空字符串
select * from t_student where sname=’李勇’
select * from t_student where ssex=’女’
select sdept from t_student where sname=’张立’
select count() from T_student
select max(
) from t_student
update t_student set ssex=’女’ where sno=’2001′
update t_student set sage=’22’ where sname=’王敏’
update t_student sdept=’MA’ where ssex=’男’

  •  sqlite三 file.db 
    创设名为file的库文件并进入到SQL命令行形式,提醒符变为sqlite>

insert into T_Student (sno,sname,ssex) values (‘2006′,’张扬’,’男’)
delete from t_student where sno=’2006′
delete from t_student where sage=22

” height=”122″ width=”480″/>

create table t_course(
cno int primary key,
cname varchar(10),
ccode int
);
create view as(
select sno,sname,cno,cname,grade from t_student,t_sc,t_course where
t_student.sno=t_sc.sno and
t_sc. cno=t_course.cno
);
1。数据库基本概念:
数码、数据库。数据模型/DBMS(数据库管理连串)/DBS(数据库系统)
二。数据库内部整合
二维表、视图,索引
3,数据库管理类别重点作用:
6大职能
4数据库操作语言:
SQL语言实现增删查改
实验内容:
1.用VS20拾开立名称叫
insert into T_Student(sno,sname,ssex,sage,sdept)
values(”,”,”,”,”)
select * from T_Student
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2001′,’李勇’,’男’,’20’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2002′,’刘晨’,’女’,’18’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2003′,’王敏’,’女’,’19’,’MA’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2004′,’张立’,’男’,’22’,’IS’)
insert into T_Course(Cno,Cname,Ccode) values (‘1′,’数据库’,’1′)
insert into T_Course(Cno,Cname,Ccode) values (‘2′,’英语’,’4′)
insert into T_SC(Cno,Sno,Grade) values (‘1′,’2001′,’88’)
insert into T_SC(Cno,Sno,Grade) values (‘2′,’2002′,’80’)
充实一条学号为200陆,姓名称叫张扬
insert into T_student(sno,sname,ssex) values (”,”,”)
(sno,sname,ssex,sage,sdept) (”,”,”,”,”)空字符串
select * from t_student where sname=’李勇’
select * from t_student where ssex=’女’
select sdept from t_student where sname=’张立’
select count() from T_student
select max(
) from t_student
update t_student set ssex=’女’ where sno=’2001′
update t_student set sage=’22’ where sname=’王敏’
update t_student sdept=’MA’ where ssex=’男’

  • .help 可显示shell情势中可选拔的保有命令列表以及命令的意思

  • .database  突显数据库新闻;包涵当前数据库的地方

insert into T_Student (sno,sname,ssex) values (‘2006′,’张扬’,’男’)
delete from t_student where sno=’2006′
delete from t_student where sage=22

 

create table t_course(
cno int primary key,
cname varchar(10),
ccode int
);
create view as(
select sno,sname,cno,cname,grade from t_student,t_sc,t_course where
t_student.sno=t_sc.sno and
t_sc. cno=t_course.cno
);
1。数据库基本概念:
数码、数据库。数据模型/DBMS(数据库管理种类)/DBS(数据库系统)
二。数据库内部整合
二维表、视图,索引
三,数据库管理连串首要成效:
陆大效益
肆数据库操作语言:
SQL语言完结增加和删除查改
实践内容:
一.用VS2010创办名叫
insert into T_Student(sno,sname,ssex,sage,sdept)
values(”,”,”,”,”)
select * from T_Student
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2001′,’李勇’,’男’,’20’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2002′,’刘晨’,’女’,’18’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2003′,’王敏’,’女’,’19’,’MA’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2004′,’张立’,’男’,’22’,’IS’)
insert into T_Course(Cno,Cname,Ccode) values (‘1′,’数据库’,’1′)
insert into T_Course(Cno,Cname,Ccode) values (‘2′,’英语’,’4′)
insert into T_SC(Cno,Sno,Grade) values (‘1′,’2001′,’88’)
insert into T_SC(Cno,Sno,Grade) values (‘2′,’2002′,’80’)
追加一条学号为200六,姓名叫张扬
insert into T_student(sno,sname,ssex) values (”,”,”)
(sno,sname,ssex,sage,sdept) (”,”,”,”,”)空字符串
select * from t_student where sname=’李勇’
select * from t_student where ssex=’女’
select sdept from t_student where sname=’张立’
select count() from T_student
select max(
) from t_student
update t_student set ssex=’女’ where sno=’2001′
update t_student set sage=’22’ where sname=’王敏’
update t_student sdept=’MA’ where ssex=’男’

  • create table student( Sno integer primary key,   Sname text not
    null,   Ssex text default ‘M’,Sage integer default 1九,Sdept text
    default ‘CS’); 
    创立一个分包Sno,Sname,Ssex,Sage,Sdept七个标题标2个表格,里面富含每贰个题名的自律规范

insert into T_Student (sno,sname,ssex) values (‘2006′,’张扬’,’男’)
delete from t_student where sno=’2006′
delete from t_student where sage=22

create table student :创造3个表,名称叫student

create table t_course(
cno int primary key,
cname varchar(10),
ccode int
);
create view as(
select sno,sname,cno,cname,grade from t_student,t_sc,t_course where
t_student.sno=t_sc.sno and
t_sc. cno=t_course.cno
);
一。数据库基本概念:
数量、数据库。数据模型/DBMS(数据库管理种类)/DBS(数据库系统)
2。数据库内部整合
二维表、视图,索引
叁,数据库管理系列首要作用:
6大作用
肆数据库操作语言:
SQL语言达成增加和删除查改
尝试内容:
一.用VS20十创建名叫
insert into T_Student(sno,sname,ssex,sage,sdept)
values(”,”,”,”,”)
select * from T_Student
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2001′,’李勇’,’男’,’20’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2002′,’刘晨’,’女’,’18’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2003′,’王敏’,’女’,’19’,’MA’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2004′,’张立’,’男’,’22’,’IS’)
insert into T_Course(Cno,Cname,Ccode) values (‘1′,’数据库’,’1′)
insert into T_Course(Cno,Cname,Ccode) values (‘2′,’英语’,’4′)
insert into T_SC(Cno,Sno,Grade) values (‘1′,’2001′,’88’)
insert into T_SC(Cno,Sno,Grade) values (‘2′,’2002′,’80’)
充实一条学号为200陆,姓名字为张扬
insert into T_student(sno,sname,ssex) values (”,”,”)
(sno,sname,ssex,sage,sdept) (”,”,”,”,”)空字符串
select * from t_student where sname=’李勇’
select * from t_student where ssex=’女’
select sdept from t_student where sname=’张立’
select count() from T_student
select max(
) from t_student
update t_student set ssex=’女’ where sno=’2001′
update t_student set sage=’22’ where sname=’王敏’
update t_student sdept=’MA’ where ssex=’男’

Sno integer primary key:标题为Sno,类型为integer整型,约束原则为primary
key主键即Sno在表中唯壹,且数值自动增进

insert into T_Student (sno,sname,ssex) values (‘2006′,’张扬’,’男’)
delete from t_student where sno=’2006′
delete from t_student where sage=22

Sname text not null :标题为Sname,类型为text字符串,约束规范为not
null就是不足省略,必须填写

create table t_course(
cno int primary key,
cname varchar(10),
ccode int
);
create view as(
select sno,sname,cno,cname,grade from t_student,t_sc,t_course where
t_student.sno=t_sc.sno and
t_sc. cno=t_course.cno
);

Ssex text default
‘男’:类型为Ssex,类型为text字符串,约束规范为default,默认为男,省略不写的话默以为M

“*
实验内容:
一.用VS2010创立名叫
insert into T_Student(sno,sname,ssex,sage,sdept)
values(”,”,”,”,”)
select * from T_Student
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2001′,’李勇’,’男’,’20’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2002′,’刘晨’,’女’,’18’,’CS’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2003′,’王敏’,’女’,’19’,’MA’)
insert into T_Student(Sno,Sname,Ssex,Sage,Sdept) values
(‘2004′,’张立’,’男’,’22’,’IS’)
insert into T_Course(Cno,Cname,Ccode) values (‘1′,’数据库’,’1′)
insert into T_Course(Cno,Cname,Ccode) values (‘2′,’英语’,’4′)
insert into T_SC(Cno,Sno,Grade) values (‘1′,’2001′,’88’)
insert into T_SC(Cno,Sno,Grade) values (‘2′,’2002′,’80’)
充实一条学号为200六,姓名叫张扬
insert into T_student(sno,sname,ssex) values (”,”,”)
(sno,sname,ssex,sage,sdept) (”,”,”,”,”)空字符串
select * from t_student where sname=’李勇’
select * from t_student where ssex=’女’
select sdept from t_student where sname=’张立’
select count() from T_student
select max(
) from t_student
update t_student set ssex=’女’ where sno=’2001′
update t_student set sage=’22’ where sname=’王敏’
update t_student sdept=’MA’ where ssex=’男’

Sage integer default 19:标题为Sage,类型为integer整型,约束原则为default默以为1玖,正是说倘使省略不写的话就以19填充

insert into T_Student (sno,sname,ssex) values (‘2006′,’张扬’,’男’)
delete from t_student where sno=’2006′
delete from t_student where sage=22

Sdept text default ‘CS’
:标题为Sdept,类型为text字符串,约束规范为default默以为CS,正是说假如省略不写的话就以CS填充

create table t_course(
cno int primary key,
cname varchar(10),
ccode int
);
create view as(
select sno,sname,cno,cname,grade from t_student,t_sc,t_course where
t_student.sno=t_sc.sno and
t_sc. cno=t_course.cno
);

  • .tables  列出当前数据库中存在的表
  • insert into student values(‘95001’,’李勇’,’M’,20,’CS’);
  • insert into student values(‘95002’,’刘晨’,’F’,19,’IS’);
  • insert into student values(‘95003’,’王敏’,’F’,18,’MA’);
  • insert into student values(‘9500四’,’张立’,’M’,1八,’IS’); 
    向student这么些表中插入四名上学的儿童的新闻

  • select * from student;
    显示表中全体音信(可是来得的只是内容,未有标题)

  • .header on/off 
    给表中的音讯加多题指标开/关,但足以窥见那样标题和新闻并从未对齐

  • .mode list  私下认可的展现格局

  • .mode column  表中的内容依据列方式对齐展现

  • .mode line  表中的内容以情势行的不二秘技展示

  • .mode tabs  以标签的方法体现

  • .mode tcl  以双引号的样式显得

  • .mode csv 以逗号隔绝彰显

课程二:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication2
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        try 
        {
            string strCon = "Data Source =.\\sqlexpress;Initial Catalog=XSGL;Integrated Security=True";
            SqlConnection mycon = new SqlConnection(strCon);
            mycon.Open();





            string StrUpd = "update T_Student set sage='25' where sname='张立'";
            SqlCommand mycmd = new SqlCommand(StrUpd, mycon);
            mycmd.ExecuteNonQuery();

            string strSel = "select sno as 学号,sname as 姓名,ssex as 性别,sage as 年龄,sdept as 专业 from T_Student";
            SqlDataAdapter myda = new SqlDataAdapter(strSel, mycon);
            DataTable dt = new DataTable();
            myda.Fill(dt);
            dataGridView1.DataSource = dt;

            MessageBox.Show("update sucess!");
            mycon.Close();

        }
            //MessageBox.Show("update sucess!");
        catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
    }
}
}

select Sno , Sname, Ssex, Sage, Sdept from student; .m.20.cs ilk”, f,
19,1S . f. 18.MA” height=”159″ width=”403″/>

平素分界面能改

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace shujuku
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            string strCon = "Data Source =.\\sqlexpress;Initial Catalog=XSGL;Integrated Security=True";
            SqlConnection mycon = new SqlConnection(strCon);
            mycon.Open();





            //string StrUpd = "update T_Student set sage='25' where sname='张立'";
            //SqlCommand mycmd = new SqlCommand(StrUpd, mycon);
            //mycmd.ExecuteNonQuery();

            string strSel = "select sno as 学号,sname as 姓名,ssex as 性别,sage as 年龄,sdept as 专业 from T_Student";
            SqlDataAdapter myda = new SqlDataAdapter(strSel, mycon);
            DataTable dt = new DataTable();
            myda.Fill(dt);
            dataGridView1.DataSource = dt;

            //string StrUpd = "update T_Student set sname='李晨' where sno='2001'";
            //SqlCommand mycmd = new SqlCommand(StrUpd, mycon);
            //mycmd.ExecuteNonQuery();

            //MessageBox.Show("update sucess!");
            mycon.Close();

        }
        //MessageBox.Show("update sucess!");
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

    private void button1_Click(object sender, EventArgs e)
    {
        string strCon = "Data Source =.\\sqlexpress;Initial Catalog=XSGL;Integrated Security=True";
        SqlConnection mycon = new SqlConnection(strCon);
        mycon.Open();

        string sname = textBox1.Text;
        string sage = textBox2.Text;



        string StrUpd = "update T_Student set sage='" + sage + "' where sname='" + sname + "'";
        SqlCommand mycmd = new SqlCommand(StrUpd, mycon);
        mycmd.ExecuteNonQuery();

        string strSel = "select sno as 学号,sname as 姓名,ssex as 性别,sage as 年龄,sdept as 专业 from T_Student";
        SqlDataAdapter myda = new SqlDataAdapter(strSel, mycon);
        DataTable dt = new DataTable();
        myda.Fill(dt);
        dataGridView1.DataSource = dt;



        MessageBox.Show("update sucess!");
        mycon.Close();
    }

    private void richTextBox1_TextChanged(object sender, EventArgs e)
    {

    }

    private void button3_Click(object sender, EventArgs e)
    {

    }
}
}
  • .mode insert 将插入的内容呈现出来

  • .exit /.quit  退出数据库方式

  • select *from student where Sname=’李勇’;  依据人名单一查找
  • select *from student where Sname=’李勇’ and Sage=20; 
    根据七个标准化一齐推断

  • select Sage from student where Sname=’李勇’; 
    依照姓名的准绳查看姓名对应的新闻中的年龄消息

  • insert into student(Sname,Sage) values (‘pengd’,19);

  • insert into student(Sname,Sage) values (‘彭丹(péng dān )’,1玖); 
    根据缺省的形式插入部分情节,Sno自动扩大,个中Sname是必须输入的其他的都有暗许值

  • delete from student where Sname=’pengd’;  内定条件删除钦命的音讯

  • create table student_1(Sno integer primary key,Sname text not null,
    Ssex text default ‘M’,Sage integer default 19,Sdept text default
    ‘CS’);  成立多少个新表,然后能够因此.tables查看多出去的表

  •  drop table student_一;  删除名字为student_1的表

  • .schema student   查看表的组织,也正是表中蕴藏音讯的布局

  • update student set Ssex=’f’,Sage=二一 where Sname=’李勇’; 
    更新名叫李勇这厮的其余有关新闻

  • alter table student add column Spwd text default ‘123456’; 
    插入3个字段名叫Spwd,默许类型是text,暗中认可值是12345陆

  •  alter table student rename to stu;  将表重命名称叫stu

分界面加多新闻

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace shujuku
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
try
{
string strCon = "Data Source =.\\sqlexpress;Initial Catalog=XSGL;Integrated Security=True";
SqlConnection mycon = new SqlConnection(strCon);
mycon.Open();





//string StrUpd = "update T_Student set sage='25' where sname='张立'";
//SqlCommand mycmd = new SqlCommand(StrUpd, mycon);
//mycmd.ExecuteNonQuery();

string strSel = "select sno as 学号,sname as 姓名,ssex as 性别,sage as 年龄,sdept as 专业 from T_Student";
SqlDataAdapter myda = new SqlDataAdapter(strSel, mycon);
DataTable dt = new DataTable();
myda.Fill(dt);
dataGridView1.DataSource = dt;

//string StrUpd = "update T_Student set sname='李晨' where sno='2001'";
//SqlCommand mycmd = new SqlCommand(StrUpd, mycon);
//mycmd.ExecuteNonQuery();

//MessageBox.Show("update sucess!");
mycon.Close();

}
//MessageBox.Show("update sucess!");
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

private void button1_Click(object sender, EventArgs e)
{
string strCon = "Data Source =.\\sqlexpress;Initial Catalog=XSGL;Integrated Security=True";
SqlConnection mycon = new SqlConnection(strCon);
mycon.Open();

string sname = textBox1.Text;
string sage = textBox2.Text;
string sno = textBox3.Text;
string ssex = textBox4.Text;
string sdept = textBox5.Text;



//string StrUpd = "update T_Student set sage='" + sage + "' where sname='" + sname + "'";
string StrUpd = "insert into T_Student(Sno,Sname,Ssex,Sage,Sdept)  values ('"+sno+"','"+sname+"','"+ssex+"','"+sage+"','"+sdept+"')";

SqlCommand mycmd = new SqlCommand(StrUpd, mycon);
mycmd.ExecuteNonQuery();

string strSel = "select sno as 学号,sname as 姓名,ssex as 性别,sage as 年龄,sdept as 专业 from T_Student";

SqlDataAdapter myda = new SqlDataAdapter(strSel, mycon);
DataTable dt = new DataTable();
myda.Fill(dt);
dataGridView1.DataSource = dt;



MessageBox.Show("update sucess!");
mycon.Close();
}

private void richTextBox1_TextChanged(object sender, EventArgs e)
{

}

private void button3_Click(object sender, EventArgs e)
{

}
}
}

==================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================

 

三.SQLite数据库编制程序接口函数

  头文件:#include<stdio.h>

      #include<stdlib.h>

      #include<sqlite3.h>

  打开sqlite数据库:int sqlite3_open(char *path,sqlite3 **db);

  关闭sqlite数据库:int sqlite3_close(sqlite3 *db);

  传递错误新闻:const char *sqlite3_errmsg(sqlite3 *db);

  参数:char *path:数据库文件的路线

            sqlite3 **db:指向sqlite句柄的指针

  重回值:成功为0,退步再次来到错误码

 

  int sqlite3_exec(sqlite3 *db,const
char *sql,sqlite_callback callback,void *para,char **errmsg); 

  功能:执行SQL操作

  参数:sqlite3 *db:指向sqlite句柄的指针

            const char *sql:SQL语句

            sqlite3_callback callback:回调函数

            void *para:传递给回调函数的参数

  char **errmsg:错误音讯指针的地方

  重返值:成功重临0,错误重临错误码

 

   typedef int(*sqlite_callback)(void *para,int f_num,char
**f_value,char **f_name);

  功用:每找到一条记下自动推行三次回调函数

  参数:para:传递给回调函数的参数

    f_num:记录中涵盖的字段数目

    f_value:包涵每一种字段值的指针数组

    f_name:包涵每一种字段名称的指针数组

  重回值:成功重回0,退步重返-1

 

   int sqlite3_get_table(sqlite3 *db,const char *sql,char
***resultp,int *nrow,int *ncolumn,char **errmsg);

  功能:执行SQL操作

  参数:db:数据库句柄

            sql:SQl语句

            resultp:用来指向sql试行结果的指针

            nrow:满意条件的记录的多寡

            ncolumn:每条记下中带有的字段数目

            errmsg:错误音讯指针的地点

  再次回到值:成功再次回到0,失利重返错误码

相关文章