sql语句

SQL语句:
常用sql语句:
查询: select * from emp;
增加: insert into emp values (xx , xx, xx);
删除: delete from emp where deptno = 50;
修改: update emp set sal = sal*2 where detpno = 10;

提交: commit;
回滚: rollback;

创建表: create table emp(……);
删除表: drop table emp;
查看表: desc emp;

注: sysdate   //当前系统时间  select sysdate from dual;
 dual    //oracle 提供的空的表
 ||    //字符串连接符,相当于java中的 +
 ‘中’ ‘Beijing’  //字符串的引用需要使用单引号
 ‘Xi’’an’   //字符串中有单引号的需使用2个单引号代替1个单引号

1. 查询语句:  select <列名> from <表名>;
eg: select * from emp;      //  * 表示所有列

1.1 修饰词 distinct : 消除重复值
eg: select distinct deptno from emp;   //消除deptno 列中的重复值

1.2加过滤条件: select <列名> from <表名> where <过滤条件>
eg: select * from emp where deptno = 10;   //查询 deptno = 10 的所有列
过滤条件有:  where a >= 10 and a <= 100;  //10到100之间
    where a between 10 and 100; //10 到100 之间
    where a <= 10 or a >= 100  //小于10 或者大于 100
    where a in (10, 20, 30);   //10, 20, 30 其中之一
    where a <> 10;    //不等于10
    where a is null;    //空值.
where a is not null;    //非空值.

1.3操作符 like: 进行制定模式   通配符%:表示0个或多个 ;_代表1个字母
    where a like ‘%A%’;    //包含A
    where a like ‘A%’;    //以A开头
    where a like ‘%A’;    //以A结尾
    where a like ‘%[0-9]%’;   //包含 0-9 的数字
    where a like ‘%[a-z]%’;   //包含 a-z 的小写字母
where a like ‘_A%’;    //第2个字母是A的

1.4 数据的排序: order by <列名> desc/asc;   //desc 降序; asc 升序(默认)
eg:select * from emp where deptno = 10 order by sal desc;

1.5 常用函数:
lower()  eg: select lower(ename) from emp; //将 ename 中的字段转为小写
substr()  eg: substr(ename, 2, 3)  //从 ename 的第2个字母开始截取3个字母
chr()         //将编码转为字符
ascii()        //将 ASCII 转为编码
round()  eg: round(23.653, 1)  //四舍五入,保留1位小数

to_char()       //将数字或日期转为特定格式
eg: to_char(sal, ‘$99,999.9999’)  //以该类型显示数字,9代表1个数字,该位无则不显示,而小数点后强制显示为0; $ 美元货币 L 本地货币/
eg: to_char(sal, ‘$00,000.0000’)  //0代表1个数字,该位没有则强制显示0
eg: to_char(sysdate, ‘YYYY-MM-DD HH:MI:SS’) //以该格式显示 当前时间, HH24  24小时制

to_date()       //将字符串转为时间类型
to_number()      //将特定数字转为数字

nvl()   eg: nvl(comm, 0)  // comm 列的数据为 空值时,用0来代替
注: 空值和 0 不一样.
任何含有控制的数学表达式进行运算都为空值.

1.6 组函数: 多条输入产生一条输出
max()  eg: select max(sal) from emp;   //sal 列中最大值
min()  eg: select min(sal) from emp;   //sal 列中的最小值
avg()   eg: select avg(sal) from emp;   //sal 列的平均值
sum()  eg: select sum(sal) from emp;   //sal 列的和
count()  eg: select count(*) from emp;   //返回emp的记录数

1.7 分组: group by <列名>
eg: select * from emp where sal > 1000 group by deptno;  //按deptno 分组,薪水大于1000
注: 出现在 select 列表中的字段,如果没有出现在 组函数里,则必须出现在groub by 语句里.

1.8 分组过滤: having<过滤条件>
注: having 对 group 进行过滤, where 只能对单组进行过滤

1.9 别名: 以列名后跟 空格<别名> 或者 as <别名> 来个类命名
eg: select sal*12 allsal from emp;     //将 sal*12 命名为 sal
 select sal*12 as allsal from emp;    
注: 表单的别名只能使用  空格<别名> 来命名,不能用as

eg: select e.ename from emp e;     //将 emp 表命名为e
注: 表单命名别名后,要使用 别名.列 来访问,否则报错.

1.10  select 汇总:
薪水>1200的雇员,按部门编号分组,分组后的平均薪水大于1500,查询分组内的平均工资并按工资倒叙排列
select avg(sal), deptno from emp where sal > 1200
group by deptno having avg(sal) > 1500 order by avg(sal) desc;

1.11 表链接:  join <表>别名 on <连接条件>
select ename, sal from emp
join (select deptno, max(sal) max_sal  from emp group by deptno)  t
on (emp.sal = t.max_sal and emp.deptno = t.deptno)
将两张表关联在一起
子查询的关键: 把它当成一张表

1.12 自连接: 自己和自己连接,为同一张表起不同的别名,把它当成2张表来用
eg: select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.deptno;

1.13 外链接: 将不匹配项列出来
右外连接: right join
eg: select ename, dname from emp e right join dept d on (e.deptno = d.deptno);
左外连接: left join
eg: select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno)
全外连接: full join
eg: select ename, dname from emp e full join dept d on (e.deptno = d.deptno);

2. 插入语句: insert into <表名> values <值>  //插入值,需按照表的字段和类型
eg: insert into dept2 values (50, ‘GAME’, ‘Beijing’);   //默认插入
eg: insert into dept2 (deptno, dname) values (60, ‘ENJOY’);  //给指定字段插入值
eg: insert into dept2 select * from dept;   //插入子查询结果,2张表结构需一致

3. 修改语句: update <表名> set <列名=新值> where <过滤>
eg: update emp2 set sal = sal*12, ename = ename||’--’ where deptno = 10;

4. 删除语句: delete from <表> where <过滤>
eg: delete from emp2;      //删除 emp2 表中的所有数据
eg: delete from emp2 where deptno = 50;  //删除 emp2 表 deptno = 50 的数据

5. 提交: commit;
在执行 insert, delete, update 语句之后,需要执行 commit , 否则不会再数据库中找到数据

6. 回滚: rollback;
在执行 insert, delete, update 语句之后,执行rollback,会回滚到插入数据之前的状态.

7. 表/视图的操作

7.1 表的创建: create table <表名> (………)

约束条件: (只能加再字段后,约束单个字段)

1 .非空  not null  constraint stn_name_not_null  not null 
//constraint 命名,将非空约束命名为 stu_name_not_null
2.唯一  unique    //不能往该字段添加重复值
3.主键  primary key   //主键约束,非空,唯一的组合.
4.外键  references <表名>(字段)  eg: references class(id)
//外键约束,参考class表的 id 字段, 注:被参考对象一定为主键约束
5.check  //检查约束
6 default  eg: default 1  //默认为1,

表级约束:在表的末尾添加  constraint <命名> 约束条件(字段1, 字段2)  表示该 字段1和字段2 的组合不能为 约束条件.

1. constraint <命名> primary key(字段)
eg: constraint stu_id primary key(id)  
//主键约束: id字段为主键,命名为stu_id
2. sonstraint <命名> foreign key(字段) references <父表>(字段)
eg: constraint stu_class_fk foreign key(class) references class(id)  
//外键约束: 本表的 class 字段,参考 class 表单的 id 字段.
3. constrain <命名> unique(字段)
eg: constraint name_email_unique unique(name, email)
 //姓名和email的组合唯一,不能重复
4. constraint <唯一约束名> default(<约束表达式>)[for 字段名]

eg:

 

7.2 表的删除: drop table <表名>

create table stu  
(id number(6) primary key,    //主键约束,非空,唯一的组合.
name  varchar2(20)  constraint stu_name_nn  not null,  //限制条件:非空约束(字段约束)
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,   //默认为1
class number(4) references class(id),   //外键约束,参考另一张表 class 的 id 
email varchar2(50) unique,    		//唯一约束, 不能插重复值.
constraint stu_id_pk primary key (id),
constraint stu_ class_fk foreign key(class) reference class(id) 	//外键约束,被参考对象一定为主键约束
constraint stu_name_email unique(name, email)    			//姓名和email 的组合不能重复(表级约束.).
)
create table class
(id number(5) primary key,
name varchar2(20) not null
)

7.3 修改标的结构:  alter  修改  修改约束条件.

增加一个字段: alter table <表名> add(字段 类型)
eg: alter table stu add(addr varchar2(100));     //增加一个字段
删除一个字段: alter table <表名> drop(字段)
eg: alter table stu drop(addr)               //删除一个字段
修改字段精度: alter table <表名> modify(字段 类型精度)
eg: alter table stu modify (addr varchar2(150))    //修改字段精度

7.4 视图的创建: create view <视图名> as (子查询)   //名称以v$开头,as后接select语句

eg: sreate view v$_dept_avg as select avg(sal) from dept;  //将平均薪水创建成1张视图

7.5 删除视图: drop view <视图名>

7.6 查询视图: select <视图名> from user_views  //user_views 是数据库视图存放位置

8. 自动递增序列:

create sequence seq;		//1.首先建立一个自动化序列 seq
create table user			//2.建立1个表
(
id number(6) primary key,
name varchar2(20),
......
)
insert into user values(seq.nextval, '水电费', ....);	//3.插入数据时,使用seq.nextval,获取值并自动+1
select seq.currval from dual;				//4.获取当前 seq 的值