最常用SQL快速参考（其中某些部分只适合Oracle）

关系型数据库模型由Codd博士在1970年提出

SQL成为ANSI标准是在1986年

 

最基本查询

    select * from table1 where name=’ Lincoln ’;

    select col1,col2 from table1;

无重复查询

    select distinct amount from checks;

    (distinct在一个语句中只能用一次，放在所有字段之前)

MySQL的一些常用命令

    show databases; 显示所有数据库

    show tables; 显示当前数据库的表

    use db1; 当前数据库切换到db1

    describe table1; 显示table1表的表结构

运用表达式和别名

    select item, wholesale+0.15 from price;

    select item, (wholesale+0.15) retail from price;

    select * from friends where state<=’la’;

    select * from friends where firstname<>’al’;

    select * from parts where location like ’%back%’;

    select * from friends where st like ’C_’;

    select firstname||lastname entirename from friends; (mysql中不能用)

    (mysql可以用select concat(name1, ’ ’, name2) name from friends;)

    select lastname from vacation where years<=5 and leavetaken>20;

    select * from vacation where lastname not like ’B%’;

集合运算，MySQL不一定能用

    select name from softball union select name from football; 合集

    select name from softball union all select name from football; 全合集

    select * from football intersect select * from softball; 交集

    select * from football minus select * from softball; 差集

In与Between运算符

    select * from friends where areacode in(100,381,204);

    select * from price where wholesale between 0.25 and 0.75;

内置函数的使用

    count   求总数

    sum 求总和

    avg 求平均

    max 求最大

    min 求最小

    variance    标准方差

    stddev  标准差

    add_month   给日期类型增加一月

    add_date    给日期类型增加一天

    last_day    返回月份的最后一天

    month_between   日期相差的月份

    sysdate 系统时间

    abs 绝对值

    ceil    “天花板”

    floor   “地板”

    exp 指数函数

    log 上面的反函数

    pow a^b

    sign    正1零0负-1

    sqrt    根号

    chr 转换成字符

    concat  连接字符串

    initcap 首字符大写，其他小写

    lower   小写

    upper   大写

    lpad    左填充，原始，长度，字符

    rpan    右填充

    ltrim   左剪切

    rtrim   右剪切

    trim    剪切

    replace 字符替换，原始，替换，替换成

    substr  获取子字符串，原始，起始，长度

    instr   查找字符串，原始，查找，开始，序号

    length 字符串长度

    to_char 转换成字符串

    to_number   转换成数字

子句的运用(Where, Group by, Order by, Having)

    select * from checks where amount>100;

    select * from checks order by check; 可以加上asc代表升序

    select * from checks order by payee desc;

    select * from checks order by remarks, payee;

    select * from checks order by 1;

    select payee, sum(amount), count(payee) from checks group by payee;

    select sum(amount), count(payee) from checks group by payee, remarks;

    where之句中不允许用合计函数，因此下面的语句是错误的。

    select team, avg(salary) from orgchart where avg(salary)<3800 group by team;

    这时候需要having子句：

    select team, avg(salary) from orgchart group by team having avg(salary)<3800;

    …… having team in(’pr’,’research’);

交叉连接（笛卡尔积）

    select * from table1, table2;

    select o.orderedon, o.name, o.partnum, p.partnum, p.description

        from orders o, part p

        where o.partnum=p.partnum;

内部连接

    select p.partnum, p.price, o.name, o.partnum

        from part p inner join orders o on orders.partnum=54;

    除了使用了“on”来代替“where”之外，和交叉连接没多少区别。

外部连接

    左连接：左边的表全部显示

    select p.partnum, p.description, p.price, o.name, o.partnum

        from part p left outer join orders o on o.partnum=54;

    右连接：右边的表全部显示

    select p.partnum, p.description, p.price, o.name, o.partnum

        from part p right outer join orders o on o.partnum=54;

子查询（MySQL不支持）

    select * from orders

    where partnum=(select partnum from part where description like “ROAD%”);

    下面是较复杂查询示例：

    select o.name, o.orderedon, o.quantity * p.price total

        from orders o, part p

        where o.partnum=p.partnum and o.quantity * p.price >

        (select avg(o.quantity * p.price)

        from orders o, part p

        where o.partnum=p.partnum);

Exist使用（判断集合是否存在）

    select name, orderedon from orders where exists

        (select * from orders where name=’Mostly harmless’)

数据库三范式

    1、数据集合分成多张表而不只是一张大表，分成的每张表都有主键；主键

    2、找出仅仅依赖于主键的列，将其存储在另一个表中；仅依赖抽出

    3、从一个表中删除不依赖于主键的列。不依赖抽出

    缺点：降低性能。

建立数据库

    很难吧？很难！不难吧？不难。仅仅告诉你最简单情况：

    create database PAYMENTS;

建立表

    关于数据类型请参考《Oracle数据类型.doc》。

    create table bills(name char(30), amount number, account_id number);

    create table empname(id number not null, ename char(30));

    create table newtable as select * from oldtable;

改变表：增加列

    alter table emp add new_col_name char(20);

改变表：修改列

    alter table emp modify new_col_name char(21);

改变表：改列名(Oracle 9i Release 2才能用)

    alter table emp rename column new_col_name to old_col_name;

改变表：删除列

    alter table emp drop column old_col_name;

删除表

    drop table tablename;

删除数据库

    drop database databasename;

主键、非空、唯一约束

    主键primary key

    非空not null

    唯一unique 除了排序，和primary key功能一致

    create table emp

        (emp_id char(9) primary key,

        emp_name varchar2(40) not null,

        phone number(10) null unique);

外键约束foreign key

    create table emp_pay

        (emp_id char(9) not null,

        position varchar2(15) not null,

        pay_rate number(4,2) not null);

    alter table emp_pay add constraint emp_id_fk foreign key(emp_id)

        references emp(emp_id);

校验约束check

    create table emp

        (emp_id char(9) not null primary key,

        emp_name varchar2(40) not null,

        emp_rate number(4,2) not null,

        zip number(5) not null);

    alter table emp add constraint chk_zip check(emp_zip = '46234');

    alter table emp add constraint chk_zip check(emp_zip in ('47634', '13451'));

    alter table emp add constraint chk_zip check(emp_rate < 12.5);

    emp_rate为校验约束名。

删除约束

    alter table emp drop constraint emp_no_constraint;

    emp_no_constraint为约束名。

更新记录

    update collection set worth = 555, price = 666 where itemid = 1110;

删除记录

    delete from collection where itemid = 1113;

事务处理

    ……

    commit;

    ……

    rollback;

    ……

    savepoint save_it;

    ……

    rollback to savepoint save_it;

日期时间

    掌握两个函数就可以了。

    to_char(empdate, 'YYYY/MM/DD HH24:MI:SS');

    将date转变成字符串：“2004/11/12 16:30: 02 ”

    to_date('1981/11/12 00:03:16', 'YYYY/MM/DD HH24:MI:SS');

    将字符串“1981/11/12 00:03: 16 ” 转变成日期类型。

    还有要注意的事项，在中文Oracle中，'26-JAN-03'并不被认为是合法的日期，

    '26-1月-03'才是合法的，真别扭，也蛮郁闷的。

建立视图

    create view debts as select * from bills;

删除视图

    drop view debts;

建立索引

    create index empno_index on emp(empno);

删除索引

    drop index empno_index;

创建用户

    create user jguogang identified by mypassword;

授予用户角色

    grant connect to jguogang;

    grant resource to jguogang;

    grant dba to jguogang;

删除用户的角色

    revoke resource from jguogang;

给予系统特权

    grant alter any type to public;

    其中“alter any type”为一种系统特权，将被授予全部用户。

    grant drop any trigger to jguogang;

    将系统特权“drop any trigger”授予用户“jguogang”。

    grant create user to connect;

    将系统特权“create user”授予角色“connect”。

收回系统特权

    revoke alter any type from public;

给予对象特权

    grant select on emp to jack;

    授予用户“jack”：对“emp”的“select”对象特权。

    grant select, update(salary) on emp to jill;

    授予用户“jill”：对“emp”的“select”、“salary”列的“update”对象特权。

收回对象特权

    revoke select on emp from jack;

字典：我是谁

    select * from user_users;

字典：我们是谁

    select * from all_users;

字典：我能干什么

    select * from user_sys_privs;

字典：我充当什么角色

    select * from user_role_privs;

字典：我拥有什么表、视图和“SEQUENCE”

    select * from user_catalog;

字典：我可访问什么表、视图和“SEQUENCE”（多）

    select * from all_catalog;

字典：我拥有什么对象

    select * from user_object;

字典：我可访问什么对象（多）

    select * from all_catalog;

……关于字典，暂时就介绍那么点了。

表的注释

    comment on table emp is 'Employee';

列的注释

    comment on column emp.ename is '名字';

列出所有包含注释的表

    select * from user_tab_comments where comments is not null;

系统时间取得

    select sysdate from dual;

 

20050906笔记

查看未提交的事务

    select * from v$transaction;

    select * from v$locked_object;

通过locked_object视图获知被锁定的对象的ID（object_id）

那么通过下面的语句能获知到底哪个对象被锁定

    select * from sys.all_objects t where object_id = ###;

alter system  kill session 30;

 

修改用户密码相关

select username,password from dba_users;

alter user aaa identified by aaaspwd;