informix 的sql语句

zp7412

　　查询登陆帐户的用户名称
　　SELECT * FROM cust_calls WHERE user_id = USER
　　查询当前日期
　　SELECT * FROM orders WHERE order_date = TODAY
　　数据库主机的服务器名称
　　SELECT FIRST 1 DBINFO(’dbhostname’) FROM systables
　　SELECT name, NVL(address, "address is unknown") AS address ROM student
　　LEFT JOIN， LEFT OUTER JOIN，RIGHT JOIN 和 RIGHT OUTER JOIN 与on 的连接
　　SELECT c.customer_num, c.lname, c.company, c.phone,
　　u.call_dtime, u.call_descr
　　FROM customer c LEFT OUTER JOIN cust_calls u
　　ON c.customer_num = u.customer_num；
　　SELECT *
　　FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
　　ON t1.c1=t3.c1) JOIN (t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1)
　　ON t1.c1=t4.c1;
　　SELECT *
　　FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
　　ON t1.c1=t3.c1),
　　(t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1)
　　WHERE t1.c1 = t4.c1;
　　SELECT *
　　FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
　　ON t1.c1=t3.c1) LEFT OUTER JOIN (t4 JOIN t5 ON t4.c1=t5.c1)
　　ON t1.c1=t4.c1;
　　SELECT *
　　FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
　　ON t1.c1=t2.c1;
　　SELECT *
　　FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
　　ON t1.c1=t3.c1;
　　SELECT *
　　FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1)
　　LEFT OUTER JOIN t3 ON t2.c1=t3.c1;
　　SELECT *
　　FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1)
　　LEFT OUTER JOIN t3 ON t1.c1=t3.c1;
　　SELECT *
　　FROM t9, (t1 LEFT JOIN t2 ON t1.c1=t2.c1) ,
　　(t3 LEFT JOIN t4 ON t3.c1=10), t10, t11 ,
　　(t12 LEFT JOIN t14 ON t12.c1=100);
　　//满足所有的相等 且 右边有值条件的结果
　　SELECT c.customer_num, c.fname, c.lname, o.order_num,
　　o.order_date, o.customer_num
　　FROM customer c RIGHT OUTER JOIN orders o
　　ON (c.customer_num = o.customer_num);
　　WHERE 字查询 的条件语句
　　ALL
　　ANY
　　IN
　　EXISTS
　　SELECT order_num, stock_num, manu_code, total_price
　　FROM items
　　WHERE total_price < ALL
　　(SELECT total_price FROM items
　　WHERE order_num = 1023)
　　SELECT DISTINCT order_num
　　FROM items
　　WHERE total_price > ANY
　　(SELECT total_price
　　FROM items
　　WHERE order_num = 1005)
　　SELECT order_num FROM items
　　WHERE stock_num = 9
　　AND quantity =
　　(SELECT MAX (quantity)
　　FROM items
　　WHERE stock_num = 9)
　　SELECT UNIQUE manu_name, lead_time
　　FROM manufact
　　WHERE EXISTS
　　(SELECT * FROM stock
　　WHERE description MATCHES ’*shoe*’
　　AND manufact.manu_code = stock.manu_code)
　　SELECT UNIQUE manu_name, lead_time
　　FROM stock, manufact
　　WHERE manufact.manu_code IN
　　(SELECT manu_code FROM stock
　　WHERE description MATCHES ’*shoe*’)
　　AND stock.manu_code = manufact.manu_code
　　SELECT customer_num, company FROM customer
　　WHERE customer_num NOT IN
　　(SELECT customer_num FROM orders
　　WHERE customer.customer_num = orders.customer_num)
　　SELECT customer_num, company FROM customer
　　WHERE NOT EXISTS
　　(SELECT * FROM orders
　　WHERE customer.customer_num = orders.customer_num)
　　CREATE TABLE accounts (
　　acc_num SERIAL primary key,
　　acc_type INT,
　　acc_descr CHAR(20));
　　CREATE TABLE sub_accounts (
　　sub_acc INTEGER primary key,
　　ref_num INTEGER REFERENCES accounts (acc_num)
　　ON DELETE CASCADE,
　　sub_descr CHAR(20));
　　删除将回导致级联删除
　　DELETE FROM accounts WHERE acc_num = 2
　　CREATE INDEX ix_cust ON orders (customer_num);
　　ALTER INDEX ix_cust TO CLUSTER;//以物理方式排序
　　ALTER TABLE items
　　ADD (item_weight DECIMAL(6,2) NOT NULL BEFORE total_price)
　　//添加字段
　　ALTER TABLE cust_calls
　　ADD ref_order INTEGER REFERENCES orders (order_num) BEFORE user_id
　　//添加外键
　　ALTER TABLE catalog DROP CONSTRAINT aa 删除外键约束
　　ALTER TABLE catalog ADD CONSTRAINT //添加新的外键约束
　　(FOREIGN KEY (stock_num, manu_code) REFERENCES stock
　　ON DELETE CASCADE CONSTRAINT ab)
　　ALTER TABLE items
　　ADD (unit_price MONEY (6,2) CHECK (unit_price > 0) )
　　CREATE TABLE tab1 (i1 int, i2 int, i3 int);
　　CREATE TABLE tab2 (i4 int, i5 int);
　　CREATE TRIGGER col1trig UPDATE OF i2 ON tab1
　　BEFORE(INSERT INTO tab2 VALUES(1,1));
　　ALTER TABLE tab2 DROP i4; 删除键
　　ALTER TABLE items MODIFY (quantity SMALLINT DEFAULT 1 NOT NULL) 修改
　　ALTER TABLE stock MODIFY (description LVARCHAR(3072))
　　CREATE DATABASE vehicles（数据库） IN research（数据库空间）
　　CREATE DATABASE vehicles WITH BUFFERED LOG 缓存日志
　　CREATE DATABASE employees WITH LOG MODE ANSI 模式
　　如何查看一个表的段数目。
　　在informix的sysmasters库中，通过执行
　　select count(*) from sysextends where tabname="表名"
　　表sysextend记录了各个表每个段的大小和地址信息。
　　dbaccess - -99 and tabtype='T'　(OK)
　　例子2：返回任何表中插入的最后一个SERIAL值
　　select dbinfo('sqlca.sqlerrd1') from systables where tabid = 1
　　例子3：返回最后一个SELECT，INSERT，UPDATE，DELETE或EXECUTE PROCEDURE语句处理的行数；
　　select dbinfo('sqlca.sqlerrd2') from systables where tabid=1;