数据库sqlite3的基本语句

/*创建表格*/

/*CREATE TABLE IFNOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT,  name TEXT, score REAL);*/

/*CREATE TABLE IFNOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT,  name TEXT, score REAL, PRIMARY KEY (id));*/

/*CREATE TABLE IFNOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT,  name TEXT, score REAL);*/

/*删除数据*/

/*DROP TABLE IF EXISTS t_student;*/

/*插入数据*/

/*INSERT INTO t_student(name, score) VALUES('hxj',55);

INSERT INTO t_student( score, name) VALUES('kk',55);

INSERT INTO t_student(name, score) VALUES('gf',55);

INSERT INTO t_student(name, score) VALUES('rr',55);*/

/*

INSERT INTO t_student(name, score) VALUES('huangxiao0',70);

INSERT INTO t_student(name, score) VALUES('huangxiao1',98);

*/

/*更新数据*/

/*UPDATE t_student SET name = 'MJ', score = 30;*/

/*删除数据*/

/*DELETE FROM t_student;*/

/*条件语句的使用*/

/*

UPDATE t_student SET score = 100 WHERE name = 'huangxiao0';

UPDATE t_student SET name = 'jack' WHERE score < 40;*/

/*DELETE FROM t_student WHERE score < 100;

DELETE FROM t_student ;*/

/*查询语句*/

/*查询name，属性和score属性的*/

/*SELECT name, score FROM t_student;*/

/*SELECT * FROM t_student;*/ /*查询所有的语句*/

/*SELECT * FROM t_student WHERE score < 50;*/

/*起别名*/

/*SELECT name AS myname, score AS myscore FROM t_student;*/

/*SELECT name xxname, score yyscore FROM t_student;*/

/*SELECT stu.name, stu.score FROM t_student AS stu;*/

/*SELECT stu.name, stu.score FROM t_student  stu;*/

/* *号是统配符 */

/*统计*/

/*SELECT count(*) FROM t_student WHERE score > 80;*/

/*排序*/

/*默认是升序 ASC是升序  DESC是降序 */

/*SELECT  * FROM t_student ORDER BY score;*/

/*SELECT  * FROM t_student ORDER BY score DESC;*/

/*SELECT  * FROM t_student ORDER BY score ASC;*/

/*SELECT  * FROM t_student ORDER BY score DESC , id DESC;*/

/*分页*/

/*SELECT *FROM t_student LIMIT 10,10;*/

/*SELECT *FROM t_student LIMIT 10;*/

/*简单约束*/

CREATE TABLE IFNOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,  name TEXT NOT NULL, score REAL DEFAULT 2);

/*外键的使用*/

/*创建一个班级的表格*/

/*CREATE TABLE if NOT EXISTS t_class (id INTEGER PRIMARY KEY AUTOINCREMENT, nametext NOT NULL ); */

/*创建学生与班级进行关联*/

/*CREATE TABLE if NOT EXISTS t_student (id INTEGER PRIMARY KEY AUTOINCREMENT, nametext NOT NULL, age integer NOT NULL ,class_id integer NOT NULL ,CONSTRAINT fk_student_class_id_class_id FOREIGN KEY (class_id) REFERENCES t_class(id)  );*/

/*多表查询,记得在后面在约束条件来查询，答案才正确*/

SELECT s.name, c.name FROM t_student s, t_class c WHERE s.class_id = c.id;