sql语句

1.数据表中添加一个字段的标准SQL语句写法为：

alter table  表名  add (字段  字段类型)  [ default  '输入默认值']  [null/not null]  ;

举例：ALTER TABLE employee  ADD  spbh varchar(20) NOT NULL Default 0

意思就是在表employee 中加入 字段spbh，该字段的类型是varchar，大小20，并且不允许为空，初始默认值是0。

2、删除数据表中的某字段。

语句格式：alter table  表名  drop (字段);

示例：删除表student中的字段age，可以用如下sql：

alter table student drop age；

3.复合索引

4.DELETE 语句

用于删除表中的行。

DELETE FROM 表名称 WHERE 列名称 = 值

可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

DELETE FROM table_name

或者：

DELETE * FROM table_name

FMDB

[db executeUpdate:@"DELETE FROM pin"];

[db executeUpdate:@"DELETE FROM pin WHERE send_dt < ? " withArgumentsInArray:@[ @(timestamp) ]];

5,插入

FMDB

@"INSERT OR REPLACE INTO pin_outbox (uid, creator_id, title, content, create_dt, confirmed, confirm_dt, "

@"send_dt, delayed, delayed_dt, sendSms,type,commentCt,unreadCommentCt,attachCt) values (?, ?, ?, ?, ?, "

 @"?, ?, ?, ?, ?, ?,?,?,?,?)"

[[RCEDBHelper sharedInstance]  executeUpdate:@"INSERT OR REPLACE INTO pin_attachments (uid, pin_id, name, mime_type, size, url, "

 @"path, create_dt,sender_net) values (?, ?, ?, ?, ?, ?, ?, ?,?)"

  withArgumentsInArray:array];

6.update

FMDB

[[RCEDBHelper sharedInstance] executeUpdate:@"update friend_request set read_state = '1'" withArgumentsInArray:nil];

 [[RCEDBHelper sharedInstance] executeUpdate:@"UPDATE pin_attachments SET path = ? WHERE  pin_id = ? AND uid = ? "

 withArgumentsInArray:@[path,pid,aid]];

7.销毁drop

FMDB

[db executeUpdate:[NSString stringWithFormat:@"DROP TABLE IF EXISTS %@", table]; withArgumentsInArray:nil];

8.IN 操作符

IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT column_name(s)

FROM table_name

WHERE column_name IN (value1,value2,...)

FMDB

[[RCEDBHelper sharedInstance] executeUpdate:[NSString stringWithFormat:@"DELETE FROM favorites WHERE favoriteUId IN (?,?)",deleteStr] withArgumentsInArray:@[@(1), @(2)]];