iOS学习 数据库 SQL语句

实用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);