首页 > 代码库 > 【symfoware OPEN】数据库基本操作
【symfoware OPEN】数据库基本操作
Symfoware OPEN系
【DDL】（data definition language）
数据定义语言，用于定义和管理SQL数据库中的所有对象的语言。主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。
1、CREATE：创建；
2、ALTER：修改；
3、DROP：删除；
4、TRUNCATE：删除；（与drop、delete查看Truncate-delete-drop.txt）
5、COMMENT：注释；
【DML】（data manipulation language）
数据操作语言，SQL中处理数据等操作统称为数据操纵语言。主要是SELECT、UPDATE、INSERT、DELETE，这4条命令是用来对数据库里的数据进行操作。
1、SELECT：查询；
2、INSERT:插入；
3、UPDATE:更新；
4、DELETE:删除；
【DCL】（Data Control Language）
数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。
1、关键字和引用符不区分大小写，习惯上关键字使用大写，引用符使用小写；
2、注释：以/*开始，以*/结束；
【数据定义】
创建表
CREATE TABLE products (
product_no integer,
name text,
price numeric
);
删除表
DROP TABLE products;
设置默认值：
CREATE TABLE products (
product_no integer,
name text,
price numeric DEFAULT 9.99
);
或序号自增1
CREATE TABLE products (
product_no integer DEFAULT nextval(‘products_product_no_seq‘),
name text,
price numeric
);
其中：products_product_no_seq为
CREATE SEQUENCE products_product_no_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
对于特殊省略形式：
CREATE TABLE products (
product_no SERIAL,
...
);
【制约】
1、检查制约
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price > 0)
);
2、非NULL制约
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric
);
3、唯一性制约
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
);
4、主键制约
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
5、外键制约
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
6、排他制约
【表操作】
1、追加列
ALTER TABLE products ADD COLUMN description text;
列初始值为null。
2、删除列
ALTER TABLE products DROP COLUMN description;
如果存在依赖，级联删除
ALTER TABLE products DROP COLUMN description CASCADE;
3、追加制约
ALTER TABLE products ADD CHECK (name <> ‘‘);
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
4、制约的消除
（共通）ALTER TABLE products DROP CONSTRAINT some_name;
（非null）ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
5、修改默认值
设置列默认值：ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
取消默认值：ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
取消默认值，则默认为null。
6、修改数据类型
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
7、修改列名
ALTER TABLE products RENAME COLUMN product_no TO product_number;
【权限】
1、授予权限
GRANT UPDATE ON accounts TO joe;
2、收回已授予的权限
REVOKE ALL ON accounts FROM PUBLIC;
【模式】
1、创建模式
CREATE SCHEMA myschema;
访问某个模式下的表时：
database.myschema.table
因为默认情况下使用public模式，所以下面两句相同
CREATE TABLE products ( ... );
CREATE TABLE public.products ( ... );
2、删除模式
DROP SCHEMA myschema;
如果模式中还有对象的话，级联删除
DROP SCHEMA myschema CASCADE;
【数据操作】
插入数据
INSERT INTO user_tbl(name, signup_date) VALUES(‘张三‘, ‘2013-12-22‘);
选择记录
SELECT * FROM user_tbl;
更新数据
UPDATE user_tbl set name = ‘李四‘ WHERE name = ‘张三‘;
删除记录
DELETE FROM user_tbl WHERE name = ‘李四‘；
【复杂查询】
结构
select * from语句 where语句 Group by语句 having语句 order by语句
SELECT
[ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
FROM语句
FROM table_reference [, table_reference [, ...]]
表结合种类
1、结果集运算
a. 并集UNION ：SELECT column1, column2 FROM table1 UNION SELECT column1, column2 FROM table2
注意：UNION ALL同UNION操作符一样，但是【不会】去掉结果集中的重复条目。
b. 交集JOIN ：SELECT * FROM table1 AS a JOIN table2 b ON a.name=b.name
c. 差集NOT IN ：SELECT * FROM table1 WHERE name NOT IN(SELECT name FROM table2)
d. 笛卡尔积CROSS JOIN ：SELECT * FROM table1 CROSS JOIN table2
2、3种连接
1）交叉连接 CROSS JOIN:
如果不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积；
FROM T1 CROSS JOIN T2
与以下两种写法相同
FROM T1, T2
FROM T1 INNER JOIN T2 ON TRUE
一般不建议使用该方法，因为如果有WHERE子句的话，往往会先生成两个表行数乘积的行的数据表然后才根据WHERE条件从中选择。 因此，如果两个需要求交际的表太大，将会非常非常慢，不建议使用。
2）内连接 INNER JOIN：
SELECT * FROM table1 INNER JOIN table2
-- 等值连接(=号应用于连接条件, 不会去除重复的列)；
SELECT * FROM table1 AS a INNER JOIN table2 AS b on a.column=b.column；
-- 不等连接(>,>=,<,<=,!>,!<,<>)；
SELECT * FROM table1 AS a INNER JOIN table2 AS b on a.column<>b.column
-- 自然连接 NATURAL JOIN:(会去除重复的列)。
select * from employees natural join departments;
自然连接只能是同名属性的等值连接，而内连接可以使用using或on子句来指定连接条件，连接条件中指出某两字段相等（可以不同名）。
注意：
内连接如果没有指定连接条件的话，和笛卡尔积的交叉连接结果一样，但是不同于笛卡尔积的地方是，没有笛卡尔积那么复杂地要先生成行数乘积的数据表，内连接的效率要高于笛卡尔积的交叉连接。
3）外连接 OUTER JOIN:
1)左外连接LEFT [OUTER] JOIN ：
显示符合条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有对应的条目显示NULL
例如 SELECT * FROM table1 AS a LEFT [OUTER] JOIN ON a.column=b.column
2)右外连接RIGHT [OUTER] JOIN：
显示符合条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有对应的条目显示NULL
例如 SELECT * FROM table1 AS a RIGHT [OUTER] JOIN ON a.column=b.column
3)全外连接：
显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL
例如 SELECT * FROM table1 AS a FULL JOIN ON a.column=b.column
注意：
指定条件的内连接，仅仅返回符合连接条件的条目。外连接则不同，返回的结果不仅包含符合连接条件的行，而且包括左表(左外连接时), 右表(右连接时)或者两边连接(全外连接时)的所有数据行。
WHERE语句
SELECT ... FROM fdt WHERE c1 > 5
SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
LIMIT和OFFSET
ELECT select_list
FROM table_expression
[LIMIT { number | ALL }] [OFFSET number]
如果给出了一个限制计数，那么返回不超过那么多的行，LIMIT ALL和省略 LIMIT子句一样。
OFFSET说明在开始返回行之前忽略多少行。
OFFSET 0和省略OFFSET子句是一样的。 如果OFFSET和LIMIT都出现了，那么在计算LIMIT个行之前忽略OFFSET行。
如果使用LIMIT，那么用ORDER BY 子句把结果行约束成一个唯一的顺序是一个好主意。 否则你就会拿到一个不可预料的该查询的行的子集（你要的可能是第十到二十行，但以什么顺序的十到二十？ 除非你声明了ORDER BY，否则顺序是不知道的）。
OFFSET子句忽略的行仍然需要在服务器内部计算；因此，一个很大的OFFSET可能还是不够有效率的。
【序列】
序列对象（也叫序列生成器）就是用CREATE SEQUENCE创建的特殊的单行表。一个序列对象通常用于为行或者表生成唯一的标识符。
创建序列
方法一：直接在表中指定字段类型为serial类型；
方法二：先创建序列名称，然后在新建的表中列属性指定序列就可以了，该列需int类型创建序列的语法：
CREATE
[ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table.column | NONE } ]
序列函数
nextval(regclass)：递增序列对象到它的下一个数值并且返回该值（bigint类型，下同）。
currval(regclass)：
在当前会话中返回最近一次nextval抓到的该序列的数值。
(如果在本会话中从未在该序列上调用过 nextval，那么会报告一个错误。)
setval(regclass, bigint)：
重置序列对象的计数器数值。设置序列的last_value字段为指定数值并且将其is_called字段设置为true，表示下一次nextval将在返回数值之前递增该序列。
setval(regclass, bigint, boolean)：
重置序列对象的计数器数值。功能等同于上面的setval函数，只是is_called可以设置为true或false。如果将其设置为false，那么下一次nextval将返回该数值，随后的nextval才开始递增该序列。
实例
=# create sequence id_seq increment by 1 minvalue 1 no maxvalue start with 1;
create table tbl (id int4 not null default nextval(‘id_seq‘),name text);
【PL/pgsql】
使用PL/pgSQL的优点
SQL是PostgreSQL和大多数其它关系型数据库用做命令语言的语言。 它是可以移植的，并且容易学习使用。但是所有 SQL 语句都必须由数据库服务器独立地执行。
这就意味着你的客户端应用必须把每条命令发送到数据库服务器，等待它处理这个命令，接收结果，做一些运算，然后给服务器发送另外一条命令。所有这些东西都会产生进程间通讯，并且如果你的客户端在另外一台机器上甚至还会导致网络开销。
如果使用了PL/pgSQL，那么你可以把一块运算和一系列命令在数据库服务器里面组成一个块，这样就拥有了过程语言的力量并且简化SQL的使用，因而节约了大量的时间，因为你用不着付出客户端/服务器通讯的过热。 这样可能产生明显的性能提升。
结构
create or replace function myfunction() returns integer
AS
$$
declare
myvar integer:=30;
begin
raise notice ‘myvar is %‘,myvar; --print 30
--创建子块
declare
myvar varchar:=‘hello world‘;
begin
raise notice ‘myvar is %‘,myvar; --print hello world
end;
raise notice ‘myvar is %‘,myvar; --print 30
return myvar;
end;
$$
language plpgsql;
开始和结束函数可以使用$$，也可以使用单引号；
一个declare….begin….end可以看做一个块，如果一个子块在另外一个块中，end结尾以;结束，整个函数结束最后的end可以没有;结尾。
“--”表示注释；
提示：使用pgAdmin创建函数时，代码选项中只需要填入$$之间的代码即可。
两种传递参数的方法
1、CREATE FUNCTION sales_tax(REAL)
RETURNS real AS
$$
BEGIN
RETURN $1 * 0.06;
END;
$$
LANGUAGE plpgsql;
2、
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS
$$
BEGIN
RETURN v1 + v2 + v3;
END;
$$
LANGUAGE plpgsql;
拷贝类型
variable%TYPE；
比如，假如你在users表里面有一个字段叫user_id。要声明一个和 users.user_id 类型相同的变量，你可以写：user_id users.user_id%TYPE;
行类型
name table_name%ROWTYPE；
一个行变量可以声明为和一个现有的表或者视图的行类型相同，方法是使用 table_name%ROWTYPE 表示法； 也可以声明它的类型是一个复合类型的名字。
CREATE FUNCTION merge_fields(t_row tablename)
RETURNS text AS
$$
DECLARE
t2_row table2name%ROWTYPE;
BEGIN
SELECT * INTO t2_row FROM table2name WHERE ... ;
RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$
LANGUAGE plpgsql;
SELECT merge_fields(t.*) FROM tablename t WHERE ... ;
控制结构
从函数返回：
1、RETURN
RETURN expression;
带表达式的RETURN是用于终止函数, 然后expression的值返回给调用者。
2、RETURN NEXT
RETURN NEXT expression;
RETURN NEXT实际上并不从函数中返回； 它只是简单地把表达式的值保存起来。 然后执行继续执行PL/pgSQL函数里的下一条语句。随着后继的RETURN NEXT命令的执行，结果集就建立起来了。最后的一个不需要参数的RETURN，导致控制退出该函数。
条件
· IF ... THEN
IF boolean-expression THEN
Statements
END IF;
· IF ... THEN ... ELSE
IF boolean-expression THEN
Statements
ELSE
Statements
END IF;
· IF ... THEN ... ELSE IF
IF demo_row.sex = ‘m‘ THEN
pretty_sex := ‘man‘;
ELSE
IF demo_row.sex = ‘f‘ THEN
pretty_sex := ‘woman‘;
END IF;
END IF;
· IF ... THEN ... ELSIF ... THEN ... ELSE
IF boolean-expression THEN
Statements
[ ELSIF boolean-expression THEN
statements
[ ELSIF boolean-expression THEN
statements
...]]
[ ELSE
statements]
END IF;
· IF ... THEN ... ELSEIF ... THEN ... ELSE
ELSEIF 是 ELSIF 的别名。
简单循环
LOOP
[<<label>>]
LOOP
Statements
END LOOP;
LOOP 定义一个无条件的循环，无限循环，直到由EXIT或者RETURN语句终止。
EXIT
EXIT [label] [ WHEN expression];
如果没有给出label， 那么退出最内层的循环，然后执行跟在END LOOP后面的语句。 如果给出label，那么它必须是当前或者更高层的嵌套循环块或者块的标签。 然后该命名块或者循环就会终止，而控制落到对应循环/块的END语句*后面*的语句上。
WHILE
WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
-- 可以在这里做些计算
END LOOP;
FOR
FOR i IN 1..10 LOOP
-- 这里可以放一些表达式
RAISE NOTICE ‘i IS %‘, i;
END LOOP;
捕获错误
INSERT INTO mytab(firstname, lastname) VALUES(‘Tom‘, ‘Jones‘);
BEGIN
UPDATE mytab SET firstname = ‘Joe‘ WHERE lastname = ‘Jones‘;
x := x + 1;
y := x / 0;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE ‘caught division_by_zero‘;
RETURN x;
END;
提示: 进入和退出一个包含EXCEPTION子句的块要比不包含的块开销大的多。因此，不必要的时候不要使用EXCEPTION。
错误和消息
RAISE level ‘format‘ [, variable [, ...]];
可能的级别有DEBUG(向服务器日志写信息)，LOG（向服务器日志写信息，优先级更高），INFO，NOTICE和WARNING（把信息写到服务器日志以及转发到客户端应用,优先级逐步升高）和EXCEPTION抛出一个错误（通常退出当前事务）。
在格式字串里，%被下一个可选参数的外部表现形式代替。要发出一个文本的%，你要写%%。请注意可选的参数必须是简单的变量，不能是表达式，而且格式必须是一个简单的字串文本。
RAISE NOTICE ‘Calling cs_create_job(%)‘,v_job_id;
v_job_id的值将代替字串中的%
【触发器函数】
触发器函数定义与普通函数定义看上去非常相似，除了它有一个返回值类型trigger，且不带任何参数。
CREATE FUNCTION emp_stamp () RETURNS trigger AS $$...
当一个PL/pgSQL 函数作为一个触发器被调用时，系统自动在最外层的块创建一些特殊的变量。这些变量分别是：
NEW
数据类型是RECORD。对于行级触发器，它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器，它的值是NULL。
OLD
数据类型是RECORD。对于行级触发器，它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器，它的值是NULL。
TG_NAME
数据类型是name，它保存实际被调用的触发器的名字。
TG_WHEN
数据类型是text，根据触发器定义信息的不同，它的值是BEFORE 或AFTER。
TG_LEVEL
数据类型是text，根据触发器定义信息的不同，它的值是ROW或STATEMENT。
TG_OP
数据类型是text，它的值是INSERT、UPDATE或DELETE，表示触发触发器的操作类型。
TG_RELID
数据类型是oid，表示触发器作用的表的oid。
TG_RELNAME
数据类型是name，表示触发器作用的表的名字。它与下面的变量TG_TABLE_NAME的作用是一样的。
TG_TABLE_NAME
数据类型是name，表示触发器作用的表的名字。
TG_TABLE_SCHEMA
数据类型是name，表示触发器作用的表所在的模式。
TG_NARGS
数据类型是integer，表示CREATE TRIGGER命令传给触发器过程的参数的个数。
TG_ARGV[]
数据类型是text类型的数组。表示CREATE TRIGGER命令传给触发器过程的所有参数。下标从0开始。TG_ARGV[0]表示第一个参数。如果下标小于0或大于等于tg_nargs，将会返回一个空值。
创建触发器
定义TRIGGER简化语法
CREATE TRIGGER name
{BEFORE | AFTER |INSTEAD OF}{event [OR...]
ON table_name
[FOR [EACH] {ROW | STATEMENT}]
ECECUTE PROCEDURE function_name()
示例：
员工工资表
CREATE TABLE emp (
empname text,
salary integer,
last_date timestamp,
last_user text
);
触发器函数
CREATE OR REPLACE FUNCTION emp_stamp() RETURNS trigger AS
$$
BEGIN
-- 检查是否给出了empname和salary
IF NEW.empname ISNULL THEN
RAISE EXCEPTION ‘empname cannot be null‘;
END IF;
IF NEW.salary ISNULL THEN
RAISE EXCEPTION ‘% cannot have null salary‘, NEW.empname;
END IF;
-- 不能给员工负的薪水
IF NEW.salary < 0 THEN
RAISE EXCEPTION ‘% cannot have a negative salary‘, NEW.empname;
END IF;
-- 记住何时谁修改了薪水
NEW.last_date := ‘now‘;
NEW.last_user := current_user;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;
触发器
任何时候表中插入或更新了行，当前的用户名和时间都记录入行中，并且它保证给出了雇员名称并且薪水是一个正数。
CREATE TRIGGER emp_stamp
BEFORE INSERT OR UPDATE
ON emp
FOR EACH ROW
EXECUTE PROCEDURE emp_stamp();
提示：pgAdmin中创建触发器是在表中操作，触发器与表关联。
测试：
insert into emp_stamp(empname,salary) values(‘wangsj‘,10000);
select * from emp;
结果：
"wangsj";1000;"2014-12-04 07:10:24.941";"postgres"
insert into emp_stamp(empname,salary) values(‘wangsj‘,-10000);
结果：
********** 错误 **********
错误: wangsj cannot have a negative salary
SQL 状态: P0001
参考：
http://blog.sina.com.cn/s/blog_4c6e822d0102dsqz.html
http://blog.csdn.net/johnny_83/article/details/2223147
http://blog.163.com/dazuiba_008/blog/static/363349812012102133028849
本文出自 “暗夜” 博客，请务必保留此出处http://icyore.blog.51cto.com/8486958/1596294
【symfoware OPEN】数据库基本操作