Xcode使用sql语句

数据库: 数据的集散地，有效的存储和管理数据

关系型数据库：1. 用二维表组织数据; 2. 结构化查询语言(SQL - Structured Query Language)

行：一条记录
列：一个字段
主键：能够唯一标识一条记录的字段
外键：其他表的主键(外来的主键)

SQL语句

SQL全称结构化查询语言，是数据库的编程语言，可以分为DDL（数据定义语言）、DML（数据操纵语言）、DQL（数据查询语言）和DCL（数据控制语言）。

• DDL: create、drop、alter
• DML: insert、delete、update
• DQL: select
• DCL: grant、revoke

说明: 不区分大小写

先创建一张学生表

格式: 名称 类型 约束

create table TbStudent
(
stuid integer primary key, --学号, 整型,作为主键(不能重复)
stuname varchar(20) not null, -- 姓名,可变字符串,要求非空
stusex char(1) default '男', --性别,字符串,默认男
stuaddr varchar(50) --家庭住址,可变字符串
);

• 删除表
  drop table TbStudent;
• 修改表(添加出生日期的字段)
  alter table TbStudent add stubirth date;
• 插入记录
  insert into TbStudent values (1001, '帅哥', '男', '四川成都', '1980-11-28');
  // 如果输入的只有部分信息需要指明
  insert into TbStudent (stuid, stuname) values (1002, '王大锤');
  // 可以输入两个王大锤，但是stuid不能重复，因为stuid是主键
  insert into TbStudent (stuid, stuname) values (1003, '王大锤');
  insert into TbStudent (stuid, stuname, stusex, stubirth) values (1004, '李莫愁', '女', '1986-12-3');
  insert into TbStudent values (1005, '张三丰', '男', '四川成都', '1975-5-5');
  insert into TbStudent values (1006, '李二王', '女', '中国北京', '1988-6-6');
  insert into TbStudent values (1007, '王丽', '女', '四川南充', '1992-2-2');
• 删除数据(指定条件)
  delete from TbStudent where stuid=1003;
  delete from TbStudent where stuname='王大锤';
• 删除所有数据
  delete from TbStudent;
• 更新数据(修改数据)
  update TbStudent set stuaddr='湖南长沙' where stuid=1004;
• 查询所有行所有列
  select * from TbStudent;
• 投影操作(只查看姓名、性别)
  select stuname, stusex from TbStudent;
• 在投影时别名
  select stuname as 姓名, stusex as 性别 from TbStudent;
• 数据筛选
  从表中查看所有的男性
  select * from TbStudent where stusex='男';
  从表中查看所有的男性且地址在四川成都
  select * from TbStudent where stusex='男' and stuaddr='四川成都';
  从表中查看所有的男性或地址在四川成都
  select * from TbStudent where stusex='男' or stuaddr='四川成都';
  从表中查看出生日期在1985-1-1和1989-12-31之间的
  select * from TbStudent where stubirth between '1985-1-1' and '1989-12-31';
  从表中查看出生日期在1980-1-1之前的
  select * from TbStudent where stubirth<'1980-1-1';
  从表中查看姓王的
  select * from TbStudent where stuname like '王%';
  从表中查看姓王的且名字只有两个字的
  select * from TbStudent where stuname like '王';
  从表中查看姓王的且名字有三个字的
  select * from TbStudent where stuname like '王_';
  从表中查看名字中有王字的
  select * from TbStudent where stuname like '%王%';
• 排序
  select * from TbStudent order by stubirth asc;(默认升序,可以不写asc)
  select * from TbStudent order by stubirth desc;
  select * from TbStudent order by stusex, stuid desc;
• 分组
  select stusex, count(stusex) from TbStudent group by stusex;
  select stuaddr, count(stuaddr) from TbStudent group by stuaddr;
  select stuaddr as 家庭住址, count(stuaddr) as 总人数 from TbStudent group by stuaddr order by 总人数;
  select count(stuid) from TbStudent;
• 聚合函数/分组函数
  count
  max
  min
  avg
  sum
  查找出生日期最小的
  select min(stubirth) from TbStudent;
• 子查询(把一个查询的结果作为另一个查询的一部分来使用)
  查出年龄最大的学生的姓名和出生日期
  select stuname, stubirth from TbStudent where stubirth=(select min(stubirth) from TbStudent);
• 连接查询
  • 内连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接

下面通过例子来说明sqlite的使用##

创建一张部门表

create table TbDept
(
deptno tinyint primary key,
dname varchar(10) not null,
dloc varchar(20) not null
);

插入数据

insert into TbDept values (10, '会计部', '北京');
insert into TbDept values (20, '研发部', '成都');
insert into TbDept values (30, '销售部', '重庆');
insert into TbDept values (40, '运维部', '深圳');

创建一张员工表

create table TbEmp
(
empno int primary key,
ename varchar(20) not null,
job varchar(10) not null,
mgr int,
sal int not null,
dno tinyint,
foreign key (dno) references TbDept(deptno)
);

插入数据

insert into TbEmp values (7800, '张三丰', '总裁', null, 9000, 20);
insert into TbEmp values (2056, '乔峰', '分析师', 7800, 5000, 20);
insert into TbEmp values (3088, '李莫愁', '设计师', 2056, 3500, 20);
insert into TbEmp values (3211, '张无忌', '程序员', 2056, 3200, 20);
insert into TbEmp values (3233, '丘处机', '程序员', 2056, 3400, 20);
insert into TbEmp values (3251, '张翠山', '程序员', 2056, 4000, 20);
insert into TbEmp values (5566, '宋远桥', '会计师', 7800, 4000, 10);
insert into TbEmp values (5234, '郭靖', '出纳', 5566, 2000, 10);
insert into TbEmp values (3344, '黄蓉', '销售主管', 7800, 3000, 30);
insert into TbEmp values (1359, '胡一刀', '销售员', 3344, 1800, 30);
insert into TbEmp values (4466, '苗人凤', '销售员', 3344, 2500, 30);
insert into TbEmp values (3244, '欧阳锋', '程序员', 3088, 3200, 20);
insert into TbEmp values (3577, '杨过', '会计', 5566, 2200, 10);
insert into TbEmp values (3588, '朱九真', '会计', 5566, 2500, 10);

完成下面的练习

1.查询最高工资及其对应员工

select ename, max(sal) from TbEmp;

select ename, sal from TbEmp where sal=(select max(sal) from TbEmp);

2.计算每位员工的年薪

select ename, sal*12 from TbEmp;

3.统计有员工部门的人数

select dno, count(dno) from TbEmp group by dno;

select dname, count(dno) from TbEmp, TbDept where deptno = dno group by dno;

4.求挣最高薪水的员工(boss除外)的姓名

select ename, max(sal) from TbEmp where mgr is not null;

select ename, sal from TbEmp where sal=(select max(sal) from TbEmp where mgr is not null);

5.查询薪水超过平均薪水的员工的姓名和工资

select ename, sal from TbEmp where sal > (select avg(sal) from TbEmp);

6.查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资

select ename, deptno, sal from TbEmp, TbDept where deptno = dno and (sal > (select avg(sal) from TbEmp group by dno));

select ename, t1.dno, sal from TbEmp as t1,
    (select dno, avg(sal) as avgSal from TbEmp group by dno) as t2 where    t1.dno=t2.dno and t1.sal>t2.avgSal;

select ename, t1.dno, sal from TbEmp as t1 inner join
    (select dno, avg(sal) as avgSal from TbEmp group by dno) as t2 on 
    t1.dno=t2.dno and t1.sal>t2.avgSal;

7.查询部门中薪水最高的人姓名、工资和所在部门名称

select ename, max(sal), dname from TbEmp, TbDept where deptno = dno group by dno ;

8.哪些人是主管

select ename, job from TbEmp where empno in (select distinct mgr from TbEmp where mgr is not null);

select ename as '主管' from TbEmp where empno in (select distinct mgr from TbEmp);

9.求平均薪水最高的部门的名称和平均工资

select dname, avg(sal) as avg1 from TbEmp, TbDept where deptno = dno  group by dno order by avg1 desc limit 1 offset 0;

10.求薪水最高的前3名雇员

select ename, sal from TbEmp order by sal desc limit 3;

select ename, sal from TbEmp where mgr is not null order by sal desc limit 3 offset 0;

11.求薪水排在第4-6名雇员

select ename, sal from TbEmp order by sal desc limit 3 offset 3;

select ename, sal from TbEmp where mgr is not null order by sal desc limit 3 offset 3;

12.求薪水最低的部门经理所在部门的名称

select ename, dname, max(sal) as max1 from TbEmp, TbDept where deptno = dno group by dno order by max1 desc limit 1 offset 2;

如何在Xcode中使用sql语句

说明: 这里是将已经创建好的数据库文件导入项目中

-(BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions {
    NSFileManager *fileManager = [NSFileManager defaultManager];
    // 用户数据必须保存在Documents文件夹中
    NSString *filePath = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/company.db"];
    // 打印沙箱路径
    //NSLog(@"%@",NSHomeDirectory());
    
    // 判断应用程序沙箱中是否有数据库文件
    if (![fileManager fileExistsAtPath:filePath]) {
        // 如果没有数据库文件就要从Bundle复制到沙箱
        // 对数据库进行操作时要操作沙箱中的数据库文件
        // 对于只读的数据库也可以直接从Bundle中读取
        NSString *sourceFile = [[NSBundle mainBundle] pathForResource:@"company" ofType:@"db"];
        [fileManager copyItemAtPath:sourceFile toPath:filePath error:nil];
    }
    return YES;
}

说明：这里需要需要导入第三方库 1.导入第三方库FMDB 2.在项目文件 Build Phases, Link Binary With Libraies 添加 libsqlite3.0.dylib 3.导入头文件#import "FMDB.h"

// 从数据库中读取数据
- (void) loadDaModel{
    if (!dataArray) {
        dataArray = [NSMutableArray array];
    }
    // 从嵌入式数据库sqlite3中获取数据
    NSString *filePath = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/company.db"];
    FMDatabase *db = [FMDatabase databaseWithPath:filePath];
    if (db && [db open]) {
        NSString *sql = @"select deptno, dname, dloc, total from tbdept as t1 left outer join (select dno, count(dno) as total from TbEmp group by dno) as t2 on t1.deptno = t2.dno";
        // 执行查询的SQL语句获得结果(游标)
        FMResultSet *rs = [db executeQuery:sql];
        while ([rs next]) {
            // 对查询结果的行进行迭代 从行中取出列
            GPDept *model = [[GPDept alloc] init];
            model.no = [rs intForColumn:@"deptno"];
            model.name = [rs stringForColumn:@"dname"];
            model.location = [rs stringForColumn:@"dloc"];
            model.total = [rs intForColumn:@"total"];
            [dataArray addObject:model];
        }
        [myTableView reloadData];
    }
    else{
        NSLog(@"创建或打开数据库失败");
    }
}

// 向数据库中添加数据
- (IBAction)okButtonClicked:(UIButton *)sender {
    // trim去掉textField输入的空格
    NSString *name = [_nameLabel.text trim];
    NSString *location = [_locationLabel.text trim];
    NSString *filePath = [NSHomeDirectory() stringByAppendingPathComponent:@"Documents/temp.db"];
    FMDatabase *db = [FMDatabase databaseWithPath:filePath];
    if (db && [db open]) {
        FMResultSet *rs = [db executeQuery:@"select max(deptno) as maxDno from tbdept"];
        if ([rs next]) {
            int no = [rs intForColumn:@"maxDno"];
            no += 10;
            // 插入数据时通过textField输入的信息传入 这里使用问号
            NSString *sql = @"insert into tbdept values(?,?,?)";
            // 注意:给问号传参数时要传对象 否则会崩在第三方库
            if ([db executeUpdate:sql,@(no),name,location]) {
                GPDept *model = [[GPDepts alloc] init];
                model.no = no;
                model.name = name;
                model.location = location;
                NSNotification *note = [[NSNotification alloc] initWithName:@"addDept" object:model userInfo:nil];
                [[NSNotificationCenter defaultCenter] postNotification:note];
                [self.navigationController popViewControllerAnimated:YES];
            }
            else{
                NSLog(@"添加失败");
            }
        }
    }
}