SQLite3创建和简单使用说明

package sqlite;


import java.sql.Statement;
import java.util.Scanner;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * 使用sqlit3
 * 如果想使用sqlite3这个android自带的数据库
首先需要将在硬盘里面创建一个db文件夹，然后通过cmd命令提示符
SQLite
D:/android/db/
或者
D:
cd android/db
sqlite3 student.db
.database


以分号;结束
以点.开头的都是命令
建立一个小型数据库，然后再创建一个表
--创建表student
CREATE TABLE student(
id integer,
name text,
age,integer
);
然后就可以使用程序对这个数据库进行操作了




 *增删改查相关的语句
 *
 --向student表中添加一列性别gender
ALTER TABLE student ADD COLUMN gender text;


--插入一条数据--
INSERT INTO 表名（列1，列2，列3）BALUES(值1，值2，值3）;
--修改数据----------------------------
update man set gender='男'where name='梅鑫';
-------------删除数据--------------
delete from man where id=10000;
------------查询-----------------
select * from man;


--select 列名   from 表名 where 条件
select * from man where age>=25;


select * from man where age>=25 and age<=35;
select * from man between 23 and 35;


select * from man where age!=35;
select * from man where age<>25;


--查询所有姓张的学生----模糊查询like
select * from man where name like '%张%';


----------查询前3条数据-----------------
select * from man limit 3;


select * from man limit 3,3; ---从第4条开始然后数3条
-----------------------------------------------------
-----查询所有英雄，按照年龄升序(降序desc)排序-------------
select * from man order by age asc;
--------按照年龄降序，年龄相同，则按照id升序排序-----------
select * from man order by age dest,id asc;


------------查询年龄最大的英雄姓名-----------------
select name from man order by age desc limit 1;


---聚合函数   sum() avg() max() min() count()


---求所有的英雄人数
select count(id)"英雄人数" from man;
select count(id) as "英雄人数" from man;


---求所有英雄年龄总和
select sum(age) from man;


select name,min(age) from man;
-----------------------------------------------------------


--------查询男生和女生个数
select gender,count(gender)from student group by gender;
select * from man group by gender;


---查询年龄大于平均年龄的英雄信息   子查询
select * from man where age>(select avg(age) from man);
select avg(age) from man;


--查询英雄姓名和所在国家名称
select man.name 英雄姓名,contry.name 国家姓名 form man,contry where man.cid=contry.cid;


--删除重复数据，只保留一个数据
delete from man where rowid not in(select max(rowid) rowid from man group by id);


 */


public class Login {


public static void main(String[] args) {
// TODO Auto-generated method stub
//add();
login();
}
public static void login(){
Scanner in=new Scanner(System.in);
System.out.println("请输入用户名：");
String name=in.nextLine();
System.out.println("请输入密码");
String pwd=in.nextLine();

String sql="select * from word where name='"+name+"'and password='"+pwd+"'";
try {
Class.forName("org.sqlite.JDBC");
Connection conn=DriverManager.getConnection("jdbc:sqlite:D:\\android\\db\\jin.db");
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
if(rs.next()){
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
public static  void add(){
try {
Class.forName("org.sqlite.JDBC");
Connection conn=DriverManager.getConnection("jdbc:sqlite:D:\\android\\db\\jin.db");
String sql="insert into word values('ashen','ashen')";
//String sql="delete from word where rowid not in(select max(rowid) rowid from word group by name)";
Statement stmt=conn.createStatement();
int result=stmt.executeUpdate(sql);
if(result>0){
System.out.println("已经成功加进来");
}else{
System.out.println("失败了");
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}