SQL 基础笔记

创建用户:

CREATE USER username IDENTIFIED BY 'password';

授权:

GRANT privileges ON databasename.tablename TO username;

flush privileges; 刷新权限表

查询 最后一条语句

select * from TABLE where (select max(ID) from TABLE)

删除 id 为 1的行

delete from TABLE where id=1;

distinct 仅找出不同的选项

SQL函数

avg函数：计算查询中某一特定字段资料的算术平均值。

count函数：计算符合查询条件的记录数。

min, max函数：传回指定字段之中符合查询条件的第一条、最末条记录的资料。

first, last函数：传回指定字段之中符合查询条件的最小值、最大值。

stdev函数：计算指定字段之中符合查询条件的标准差。

sum函数：计算指定字段之中符合查询条件的资料总和。

var,函数：计算指定字段之中符合查询条件的变异数估计值。

存储过程与预处理的不同：

摘自：http://stackoverflow.com/questions/7296417/difference-between-stored-procedures-and-prepared-statements

Stored procedures are a sequence of instructions in PL/SQL language. Is a programming language implemented by some DBMS, that lets you store sequences of queries frequently applied to your model, and share the processing load with the application layer.

Prepared statements are queries written with placeholders instead of actual values. You write the query and it is compiled just once by the DBMS, and then you just pass values to place into the placeholders. The advantage of using prepared statements is that you enhance the performance considerably, and protect your applications from SQL Injection.

The difference is you cant store prepared statements. You must "prepare" them every time you need to execute one. Stored procedures, on the other hand, can be stored, associated to a schema, but you need to know PL/SQL to write them.