SQLite常用语句

/*分页*/

/*

 SELECT * FROM t_sutdent LIMIT  10;

 SELECT * FROM t_sutdent LIMIT 0, 10;

 */

/*排序 默认升序*/

/*先按照分数降序排序, 如果分数相同再按照id升序排序*/

/*

 SELECT * FROM t_sutdent ORDER BY score DESC, id DESC;

 

 SELECT * FROM t_sutdent ORDER BY score ASC;

 */

/*统计*/

/*

 SELECT count(*) FROM t_sutdent;

 

 SELECT count(score) FROM t_sutdent WHERE score >= 60;

 */

/*

 给字段起别名" 隐藏内部真实存储的细节

 给表起别名的用途: 多表查询时, 如果多张表中都有相同名称的字段, 可以明确字段属于哪张表

 */

/*

 SELECT stu.name , stu.score FROM t_sutdent AS stu;

 SELECT name  myName2, score  myScore2 FROM t_sutdent;

 SELECT name AS myName, score AS myScore FROM t_sutdent;

 */

/*查询*/

/*

 SELECT * FROM t_sutdent WHERE score >= 60;

 SELECT * FROM t_sutdent;

 SELECT score FROM t_sutdent;

 SELECT name, score FROM t_sutdent;

 */

/*条件语句*/

/*

 UPDATE t_sutdent SET name = 'xxx'  WHERE score = 38 AND name ='ww';

 = /is / != / is not / > /< />= /<=

 UPDATE t_sutdent SET score = 99 WHERE name IS 'tt';

 UPDATE t_sutdent SET score = 0 WHERE name = 'tt';

 

 */

/*删除*/

/*

 DELETE FROM t_sutdent;

 */

/*更新*/

/*

 UPDATE t_sutdent SET name = 'lnj';

 */

/*插入*/

/*

 INSERT INTO t_sutdent(score) VALUES(38);

 INSERT INTO t_sutdent(score, name) VALUES(100, 'tt');

 INSERT INTO t_sutdent(name, score) VALUES('zs', 99);

 */

/*销毁表*/

/*

 DROP TABLE IF EXISTS t_sutdent;

 DROP TABLE  t_sutdent;

 */

/*创建表*/

/*

 CREATE TABLE IF NOT EXISTS t_sutdent(id INTEGER PRIMARY KEY AUTOINCREMENT, name, score);

 CREATE TABLE IF NOT EXISTS t_sutdent(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, score REAL);

 CREATE TABLE t_student(id INTEGER PRIMARY KEY, name TEXT, score REAL);

 CREATE TABLE t_student(id INTEGER, name TEXT, score REAL, PRIMARY KEY(id));

 CREATE TABLE t_student(id INTEGER, name TEXT, score REAL);

 */