数据库:管理数据的仓库
库 :用于存放表的仓库
表 :数据存格式以表的形式 一张表由若干字段 和 若干记录组成
记录:
Sqlite
ddl:数据表结构定义语句<CREATE创建 ALTER修改表 DROP删除>
dml:数据操作<增INSERT 删DELETE 改UPDATE 查SELECT>
创建一个表并指定字段
CREATE TABLE "main"."t_studentInfo" (
"Name" text NOT NULL,
"Id" integer(5,0) NOT NULL,
"Score" real NOT NULL,
PRIMARY KEY("Id") //指定key字段
);
—————带条件的———————
CREATE TABLE if not exists "main"."t_studentInfo" (
"Name" text NOT NULL,
"Id" integer(5,0) NOT NULL PRIMARY KEY AUTOINCREMENT, //指定为key并自动增加
"Score" real NOT NULL
);
完整的复制一张表
CREATE TABLE IF NOT EXISTS ‘NewTab' as SELECT * FROM 't_studentScore'
只复制表结构
CREATE TABLE 'newTab' as SELECT * FROM 't_studentScore' WHERE 0;
修改表:(在以上表中加一个字段other)
//先备份
ALTER TABLE "main"."t_studentInfo" RENAME TO "_t_studentInfo_old_20150816";
CREATE TABLE "main"."t_studentInfo" (
"Name" text NOT NULL,
"Id" integer(5,0) NOT NULL,
"Score" real NOT NULL,
"other" blob NOT NULL //新增的字段
);
在表的最后添加一个字段 ADD [COLUMN]column-def
ADD [COLUMN]语法用于在已有表中添加新的字段。新字段总是添加到已有字段列表的末尾。Column-def可以是CREATE TABLE中允许出现的任何形式,且须符合如下限制:
- 字段不能有主键或唯一约束。
- 字段不能有这些缺省值:CURRENT_TIME, CURRENT_DATE或CURRENT_TIMESTAMP
- 若定义了NOT NULL约束,则字段必须有一个非空的缺省值
ALTER TABLE t_student ADD address text(10)
ALTER TABLE t_student ADD yuwen real(10,5) NOT NULL DEFAULT 0 如果指定NOT NULL 必须要有默认值DEFAULT 0
修改表名:
ALTER TABLE “oldName” RENAME TO “newName”
删除表:
DROP TABLE "main"."t_temp" 或者 DROP TABLE "t_temp"
插入一行数据(记录)
--INSERT INTO 表名(字段名,字段名..) VALUES(对应的字段值)
INSERT INTO "main”.”t_student”(Name,Id,Score) VALUES('king',123,55.7)
INSERT INTO 't_student' VALUES('赵勇,3,78.6);//values与表中的字段一一对应
删除一行记录:<如果不带条件 直接删除该表的所有记录>
delete from 表名 where 条件
如 DELETE FROM t_student WHERE name="to"
修改数据:<如果不带条件 则影响表中所有记录的该字段>
update 表名 set 字段名=值,字段名=值....
或者
update 表名 set 字段名=值,字段名=值.... where 条件
如: UPDATE t_student set Score=66.5 WHERE Name=“tom”
UPDATE t_student set Score=55.5 WHERE Name=“king” AND Id=1342
UPDATE t_student set Score=55.5 WHERE Name=“king” OR Id=1342
查询
select 字段名 from 表名 where 条件 或者order by 字段名
列出该表中所有记录 SELECT * from t_student
//列出该表的所有记录的name id字段
SELECT name,id FROM "t_student"
找名字为tom的记录 SELECT name from t_student WHERE name='tom'
找出名字为tom的并显示为姓名 SELECT name as 姓名 from t_student WHERE name=‘tom’
找出姓张的同学 SELECT * from t_student WHERE name LIKE ‘张%’
找出数学小于80分的 SELECT * from t_student WHERE score<80
根据数学分数排序 SELECT * from t_student ORDER by Score
根据数学分数反向排序 SELECT * from t_student ORDER by Score DESC
把大于70分的提取出来并排序 SELECT * from t_student WHERE Score>70 ORDER by Score
查询中的一些简单函数 sum求和 count计数 avg平均值
求总分 :SELECT sum(score) as 总分 FROM t_student
求平均 SELECT avg(score) as 平均分 FROM t_student
把每个学生的各科成绩累加形成一个新的 叫 ‘总分的’字段
SELECT *,yuwen+shuxue+yingyu as 总分 FROM t_student
SELECT count(*) as 总人数,sum(yuwen) as 总分,avg(yuwen) as 平均总分 FROM 't_student'
找出语文最高分的学生
SELECT * FROM 't_student' WHERE yuwen=(SELECT max(yuwen) FROM 't_student')
CREATE INDEX 创建索引
sql-statement ::= | CREATE [UNIQUE] INDEX [IF NOT EXISTS][database-name .]index-name ON table-name ( column-name[, column-name]* ) |
column-name ::= | name[ COLLATE collation-name][ ASC | DESC ] |
CREATE INDEX命令由“CREATE INDEX”关键字后跟新索引的名字,关键字“ON”,待索引表的名字,以及括弧内的用于索引键的字段列表构成。每个字段名可以跟随“ASC”或“DESC”关键字说明排序法则,但在当前版本中排序法则被忽略。排序总是按照上升序。
每个字段名后跟COLLATE子句定义文本记录的比较顺序。缺省的比较顺序是由CREATE TABLE语句说明的比较顺序。若不定义比较顺序,则使用内建的二进制比较顺序。
附加到单个表上的索引数目没有限制,索引中的字段数也没有限制。
若UNIQUE关键字出现在CREATE和INDEX之间,则不允许重名的索引记录。试图插入重名记录将会导致错误。
每条CREATE INDEX语句的文本储存于sqlite_master或sqlite_temp_master表中,取决于被索引的表是否临时表。 每次打开数据库时,所有的CREATE INDEX语句从sqlite_master表中读出,产生SQLite的索引样式的内部结构。
若使用可选的IF NOT EXISTS子句,且存在同名索引,则该命令无效。
使用DROP INDEX命令删除索引
DROP INDEX
sql-command ::= | DROP INDEX [IF EXISTS][database-name .]index-name |
DROP INDEX语句删除由CREATE INDEX语句创建的索引。索引将从数据库结构和磁盘文件中完全删除,唯一的恢复方法是重新输入相应的CREATE INDEX命令。
DROP TABLE语句在缺省模式下不减小数据库文件的大小。空间会留给后来的INSERT语句使用。要释放删除产生的空间,可以使用VACUUM命令。若AUTOVACUUM模式开启,则空间会自动被DROP INDEX释放。
CREATE TRIGGER 创建触发器
sql-statement ::= | CREATE [TEMP | TEMPORARY] TRIGGER trigger-name[ BEFORE | AFTER ] database-event ON [database-name .]table-name trigger-action |
sql-statement ::= | CREATE [TEMP | TEMPORARY] TRIGGER trigger-name INSTEAD OF database-event ON [database-name .]view-name trigger-action |
database-event ::= | DELETE | INSERT | UPDATE | UPDATE OF column-list |
trigger-action ::= | [ FOR EACH ROW | FOR EACH STATEMENT ][ WHEN expression] BEGIN trigger-step ; [trigger-step ; ]* END |
trigger-step ::= | update-statement|insert-statement| delete-statement|select-statement |
CREATE TRIGGER语句用于向数据库schema中添加触发器。触发器是一些在特定的数据库事件(database-event)发生时自动进行的数据库操作(trigger-action)。
触发器可由在特殊表上执行的DELETE、INSERT、UPDATE等语句触发,或UPDATE表中特定的字段时触发。
现在SQLite仅支持FOR EACH ROW触发器,不支持FOR EACH STATEMENT触发。因此可以不用明确说明FOR EACH ROW。FOR EACH ROW的意思是由trigger-steps说明的SQL语句可能在(由WHEN子句决定的)数据库插入,更改或删除的每一行触发trigger。
WHEN子句和trigger-steps可以使用“NEW.column-name”和“OLD.column-name”的引用形式访问正在被插入,更改或删除的行的元素,column-name是触发器关联的表中的字段名。OLD和NEW引用只在触发器与之相关的trigger-event处可用,例如:
INSERT | NEW可用 |
UPDATE | NEW和OLD均可用 |
DELETE | OLD可用 |
当使用WHEN子句,trigger-steps只在WHEN子句为真的行执行。不使用WHEN时则在所有行执行。
trigger-time决定了trigger-steps执行的时间,它是相对于关联行的插入、删除和修改而言的。
作为的一部分trigger-step的UPDATE或INSERT可以使用ON CONFLICT子句。但若触发trigger的语句使用了ON CONFLICT子句,则覆盖前述的ON CONFLICT子句所定义的冲突处理方法。
关联表被撤销时触发器被自动删除。
不仅在表上,在视图上一样可以创建触发器,在CREATE TRIGGER语句中使用INSTEAD OF即可。若视图上定义了一个或多个ON INSERT、ON DELETE、ON UPDATE触发器,则相应地对视图执行INSERT、DELETE或UPDATE语句不会出错,而会触发关联的触发器。视图关联的表不会被修改。(除了由触发器进行的修改操作)。
例子:
假设“customers”表存储了客户信息,“orders”表存储了订单信息,下面的触发器确保当用户改变地址时所有的关联订单地址均进行相应改变:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
定义了该触发器后执行如下语句:
UPDATE customers SET address = ’1 Main St.’ WHERE name = ’Jack Jones’;
会使下面的语句自动执行:
UPDATE orders SET address = ’1 Main St.’ WHERE customer_name = ’Jack Jones’;
注意,目前在有INTEGER PRIMARY KEY域的表上触发器可能工作不正常。若BEFORE触发器修改了一行的INTEGER PRIMARY KEY域,而该域将由触发该触发器的语句进行修改,则可能根本不会修改该域。可以用PRIMARY KEY字段代替INTEGER PRIMARY KEY字段来解决上述问题。
一个特殊的SQL函数RAISE()可用于触发器程序,使用如下语法:
raise-function ::= | RAISE ( ABORT, error-message ) | RAISE ( FAIL, error-message ) | RAISE ( ROLLBACK, error-message ) | RAISE ( IGNORE ) |
当触发器程序执行中调用了上述前三个之一的形式时,则执行指定的ON CONFLICT进程(ABORT、FAIL或者ROLLBACK)且终止当前查询,返回一个SQLITE_CONSTRAINT错误并说明错误信息。
当调用RAISE(IGNORE),当前触发器程序的余下部分,触发该触发器的语句和任何之后的触发器程序被忽略并且不恢复对数据库的已有改变。若触发触发器的语句是一个触发器程序本身的一部分,则原触发器程序从下一步起继续执行。
使用DROP TRIGGER删除触发器。
例如:当t_student年纪和名字发生改变时 person表的名字和所纪也发生改变
DROP TRIGGER "main"."change_age”;//加上一行表示以下为修改原有的一个触发器
CREATE TRIGGER "main"."change_age"
BEFORE UPDATE OF age,name ON t_student
BEGIN
UPDATE person SET age=new.age WHERE name=old.name;
UPDATE person SET name=new.name WHERE name=old.name;
END;
创建一个触发器:当在t_studentScore表中删除一个记录 则触发触发器事件执行在newTab表中插被删除的这条记录
CREATE TRIGGER IF NOT EXISTS 'back_data' DELETE ON 't_studentScore'
BEGIN
INSERT INTO 'newTab' VALUES(old.id,old.name,old.age,old.shuxue,old.yuwen);
END;
DROP TRIGGER
sql-statement ::= | DROP TRIGGER [database-name .]trigger-name |
DROP TRIGGER语句删除由CREATE TRIGGER创建的触发器。触发器从数据库的schema中删除。注意当关联的表被撤消时触发器自动被删除。
xcode编程使用sqlite数据库
1.工程配制文件Build Phases -> Link Binary With Libraries 增加libsqlite3.dylib
2.导入头文件 #import <sqlite3.h>
//
CREATE TABLE IF NOT EXISTS "main"."temp"(
id integer NOT NULL PRIMARY KEY,
name text(32) NOT NULL,
age integer(3) NOT NULL,
yuwen real DEFAULT 0
);
ALTER TABLE "main"."temp" RENAME TO t_student;
ALTER TABLE "main"."t_student" ADD shuxue real DEFAULT 0
————————————————————————————————
//将表t_student中的name yawed字段取出存入到一个新表t_temp
CREATE TABLE "main"."t_temp"(
name text NOT NULL,
yuwen real DEFAULT 0
);
INSERT INTO "main"."t_temp" SELECT name,yuwen FROM "main"."t_student"