实用SQL语句大全:
http://www.php100.com/html/webkaifa/database/Mysql/2012/0720/10713.html
http://www.cr173.com/html/29311_1.html
/*简单约束*/
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER); //id自动增加
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, ageINTEGER NOTNULL); //名称不为空
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXTUNIQUE, age INTEGER); //名称唯一
CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, ageINTEGER DEFAULT 1); //年龄默认值为:1
/*分页*/
SELECT * FROM t_student ORDER BY id ASC LIMIT 30,10;
/*排序*/
SELECT * FROM t_student WHERE score > 50 ORDER BY age DESC;
SELECT * FROM t_student WHERE score < 50 ORDER BY age ASC , score DESC;
/*计量*/
SELECT COUNT(*) FROM t_student WHERE age > 50;
/*别名*/
SELECT name as myName, age as myAge, score as myScore FROM t_student;
SELECT name myName, age myAge, score myScore FROM t_student;
SELECT s.name myName, s.age myAge, s.score myScore FROM t_student s WHERE s.age >50;
/*查询*/
SELECT name, age, score FROM t_student;
SELECT * FROM t_student;
/*连表查询*/
SELECT * FROM t_student1 s,t_class c WHERE s.class_id = c.id AND c.name = 'java';
SELECT s.name 姓名,c.name 班级名 FROM t_student1 s,t_class c WHERE s.class_id = c.id AND c.name = 'java';
/*修改指定数据*/
UPDATE t_student SET name = 'MM' WHERE age =10;
UPDATE t_student SET name = 'WW' WHERE age is7;
UPDATE t_student SET name = 'XXOO' WHERE age <20;
UPDATE t_student SET name = 'NNMM' WHERE age <50 and score >10;
/*删除数据*/
DELETE FROM t_student;
/*更新数据*/
UPDATE t_student SET name = 'LNJ';
/*插入数据*/
INSERT INTO t_student(age, score, name) VALUES ('28',100,'jonathan');
INSERT INTO t_student(name, age) VALUES ('lee','28');
INSERT INTO t_student(score) VALUES (100);
/*插入数据*/
INSERT INTO t_student(name, age, score) VALUES ('lee','28',100);
/*添加主键*/
CREATE TABLE IF NOT EXISTS t_student (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, score REAL);
CREATE TABLE IF NOT EXISTS t_student (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, score REAL);
CREATE TABLE IF NOT EXISTS t_student (id INTEGER, name TEXT, age INTEGER, score REAL, PRIMARY KEY(id));
/*删除表*/
DROP TABLE t_student;
DROP TABLE IF EXISTS t_student;
/*创建表*/
CREATE TABLE t_student(id INTEGER , name TEXT, age , score REAL);
CREATE TABLE IF NOT EXISTS t_student(id INTEGER , name TEXT, age , score REAL);