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(); } } }
原文链接:https://www.f2er.com/sqlite/198844.html