SQL语句相关

2019-03-28 本文已影响0人低吟浅唱1990

iOS系统集成了一个轻量级数据库:SQLite，SQLite支持绝大部分SQL语法，也可以使用SQL语句操作数据库中的数据。SQLite内部支持NULL，INTEGER，REAL（浮点数），TEXT（文本）和BLOB（大二进制对象）这5种数据类型。下面以SQL语法为例介绍操作数据库的语法，在实际中可能需要相应改动以适应SQLite。

以下内容主要来自“Learning SQL，Second Edition”。

建表

新建一种存放个人信息的表描述个人的信息类型：
姓名，性别，出生日期，地址，最喜爱的食物

分析一般来说让一个人有多个喜欢的食物，因此这个食物需要单独建议一张表存储person的foods。这里包含一对多的关系。在表的关系中还有多对多的关系，不过在实际中就应用程序而言内置的存储多对多这种负责的关系比较少见。

列	类型	允许值
person_id	smallint(unsigned)
f_name	varchar(20)
l_name	varchar(20)
gender	char(1)	M，F
birth_date	date
street	varchat(30)
city	varchat(20)
state	varchat(20)
country	varchat(20)
postal_code	varchat(20)

列	类型	允许值
person_id	smallint(unsigned)
food

CREATE TABLE person (person_id SMALLINT UNSIGNED,
fname VARCHAR(20),
lname VARCHAR(20),
gender ENUM('M','F'),
birth_date DATE,
street VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
country VARCHAR(20),
postal_code VARCHAR(20),
 CONSTRAINT pk_person PRIMARY KEY (person_id)
);

CONSTRAINT 建立主键约束
gender ENUM('M','F') 表示gender只接受M和F两种值。
当然还可以设置默认值，例如
`fname`  varchar(20) NOT NULL DEFAULT '' COMMENT '名字' 
表示fname字段不能为NULL ，默认是为空字符串，注释这个表示名字。


CREATE TABLE favorite_food
(person_id SMALLINT UNSIGNED,
food VARCHAR(20),
CONSTRAINT pk_favorite_food PRIMARY KEY (person_id,food),
CONSTRAINT fk_fav_food_person_id FOREIGN KEY(person_id)
REFERENCES person (person_id) ON DELETE CASCADE ON UPDATE CASCADE) ;

CONSTRAINT pk_favorite_food PRIMARY KEY (person_id,food), 表示person_id和food是联合主键
表示这个表中同一个person_id，不能有多个相同的food

CONSTRAINT fk_fav_food_person_id FOREIGN KEY(person_id)
REFERENCES person (person_id)) ON DELETE CASCADE ON UPDATE CASCADE;
表示给favorite_food建一个外键 与person表中的person_id相关联。同时当父表中（person）中相关记录删除时，先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录。

插入

在插入数据之间，需要改动一下person表，一般情况下，插入一条记录的时候，大多数情况下，不会所有字段都赋值，当主键相同时插入数据会出错，所以一般在插入记录的时候不会对主键赋值，而是设置主键自增.

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

插入语法

INSERT INTO 表明 （字段名1，字段名2，...） VALUES （值1，值2，...）;

//插入一条person信息
INSERT INTO person (person_id,fname,lname,gender,birth_date)
VALUES (null,'Willian','Turner','M','1972-05-27');

//为刚刚插入的person添加一条记录 person最喜爱的food
INSERT INTO favorite_food (person_id,food) VALUES
(1,'pizza');

注意如果 在插入food记录之前没有相应的person记录，会报错
INSERT INTO favorite_food (person_id,food) VALUES
(3,'pizza');此语句会报错。

删除

删除语法

delete from where 条件

DELETE FROM person WHERE person_id = 1;

删除person_id = 1；同时相应的food表中的记录也会被删除。如果不是N DELETE CASCADE ON UPDATE CASCADE 而是其他选项则可能不会删除，甚至可能报错，表示删除不了父表中的信息。

更新

更新语法

update 表明 set 字段名1 = '值1'，字段名2 = '值2' where 条件

UPDATE person SET street = '1225 Tremont St.',
city = 'Boston',
state = 'USA',
postal_code = '02138'
WHERE person_id =1;
表示给person_id=1的记录更新street，city，state，postal_code字段的值。

查询

为了更好的演示数据库操作，需要复杂一点的数据库多表来完成。下面以书中Bank数据模型为例演示。

show tables 显示数据中的表

表名	定义
account	为特定顾客开放的特定产品
branch	开展银行交易业务的场所
busniess	公司顾客(customer)的子类型
customer	与银行有业务来往的个人或公司
department	执行特定银行职能的雇员分组
employee	银行的工作人员
individual	个人顾客
officer	允许为公司顾客发起商务交易的人
product	向顾客提供的银行服务
product_type	具备相似功能的产品的分组
transaction	改变账户余额的操作

DESC customer 显示customer的表结构信息

customer表结构

子句	使用目的
select	确定结果集中应该包含哪些列
from	指明索要提取数据的表，以及这些表示如何连接的
where	过滤不需要的数据
group by	用于对具有相同列值得进行分组
having	过滤掉不需要的组
order by	按一个或多个列，对最后结果集中的行进行排序

select字句

select * from department;
显示所有department表中的列（通过*表示）。
在此表中相当于下列语句
select dept_id,name from departmant;

select 子句中可以跟内建函数，对列名、字符等运算。

SELECT emp_id,'ACTIVE',emp_id * 3.14159,UPPER(lname)
FROM employee

结果

去重在查询select关键字之后加上distinct关键字

select distinct cust_id from account;

from子句
from子句定义了查询中所使用的表，以及连接这些表的方式。
这里的表包括：
1、永久表（使用create table 创建的表）
2、临时表（子查询所返回的表）
3、虚拟表（使用create view 子句创建的视图）

子查询产生的表

SELECT
    e.emp_id,
    e.fname,
    e.lname
FROM
    (
        SELECT
            emp_id,
            fname,
            lname,
            start_date,
            title
        FROM
            employee
    ) e;
第一个from 后面就是子查询创建的表

虚拟视图视图是存储在数据字典中的查询，表现得像一个表，但实际上并不拥有任何数据。

//创建视图
CREATE VIEW employee_vw AS
SELECT emp_id,fname,lname,
YEAR(start_date) start_year
FROM employee;
//从视图中查询
SELECT emp_id,start_year FROM employee_vw;

where子句
where子句用于在结果集中过滤掉不需要的行。

SELECT emp_id,fname,lname,start_date,title
FROM employee
WHERE title = 'Head Teller'；
最终的查询结构满足是 title =  'Head Teller'

//同时满足两个条件
SELECT emp_id,fname,lname,start_date,title
FROM employee
WHERE title = 'Head Teller'
AND start_date > '2016-01-01'；
//只需满足一个条件
SELECT emp_id,fname,lname,start_date,title
FROM employee
WHERE title = 'Head Teller'
OR start_date > '2016-01-01'；

image.png

group by 和 having子句
group by子句可以根据列值对数据进行分组，同时一般用having子句对分组后的数据过滤


SELECT d.name,COUNT(e.emp_id) num_employees
FROM department d INNER JOIN employee e ON
d.dept_id = e.dept_id
GROUP BY d.name
HAVING count(e.emp_id) > 2;

//根据d.name的中进行分组，同时过滤count(e.emp_id) > 2的选项

order by子句
order by用于对结果集中的原始列数据或是根据列数据计算的表达式结果进行排序。


SELECT emp_id,lname,fname,start_date FROM
employee
ORDER BY start_date DESC;

//DESC表示降序

多表查询
尽管我们已经学习了单表查询，但是有时候需要获得的结果集需要查询两个或以上的表才能得到，特别是有外键的时候。join连接能够帮我们解决这个问题。

SELECT e.fname,e.lname,d.name FROM
employee e （INNER）JOIN department d ON
e.dept_id = d.dept_id;

//JOIN ON表示内连接，employee中dept_id在department有相同的dept_id相匹配时才是我们需要的选项。如果在employee中dept_id存在某个值而在department中不存在，这就会连接失败。在没有指定连接类型(INNER)时一般默认为内连接。

如果两个表中的列名相同可是使用

SELECT e.fname,e.lname,d.name FROM
employee e INNER JOIN department d USING(dept_id);

//多表连接 并伴有where过滤

SELECT a.account_id,a.cust_id,a.open_date,a.product_cd 
FROM account a 
INNER JOIN employee e
ON a.open_emp_id = e.emp_id
INNER JOIN branch b
ON e.assigned_branch_id = b.branch_id
WHERE e.start_date < '2007-01-01'
AND (e.title = 'Teller' OR e.title = 'Head Teller')
AND b.name = 'Woburn Branch';

更过关于SQL语句的知识请查看专业数据。
下一篇介绍FMDB的基本使用

SQL语句相关

建表

插入

删除

更新

查询

猜你喜欢

热点阅读