这两天想系统地学习下sql,先是下载了《Sams Teach Yourself SQL in 10 Minutes》,讲得很初级,也很详细,用了大概两个小时,粗粗地看了一边, 对系统学习初级东西还是很有帮助的,但对cursor和存储过程帮助了了。
笔记如下:
1. like字句。例如 :"[^JM]_A% " 这里的^在access下要写成!
2. 字符串连接符。
· sqlserver/access/sybase: +
· db2/oracle/postgressql/sybase: ||
3. 字符串截取函数。
· Access:mid()
· MYSQL/sqlserver/ sybase: substring()
· db2/oracle/postgressql: substr()
4. 类型转换。
· Access/oracle:每种类型有自己的转换函数
· Mysql/sqlserver/ sybase: convert()
· db2/postgressql: cast()
5. 获取现在时间。
· Access:now()
· sqlserver/ sybase: getdate()
· db2/postgressql: current_date
· mysql: curdate()
· oracle:sysdate
6. 事务。
· sqlserver/ sybase: save transaction pointname;rollback transaction pointname;
l Mysql/oracle:savepoint pointname;rollback to pointname
BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,' 2001/12/1 ','1000000010');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20010, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20010, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION
@@ERROR为零时表示上一句sql有误。
7. 触发器。它可用于inser,delete,update.
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;