Oracle语法
查询：
一.查看所有信息
select * from 表名;
select * from stu;
二.显示指定字段
select 字段名1， 字段名2 ... from 表名
select first_name, age from stu;
SELECT DISTINCT id, last_name FROM stu;
三.给字段取别名: as 可以省略 ，如果别名为关键字必需加双引号
select first_name as 姓, age as 年龄 , sex as "select" from stu 表名;
四.给表取别另：as不加
select 表别名.字段名， 表别名.字段名，... from 表名 表的别名
select s.id , s.last_name from stu s;
五.按条件查
select * from 表名 where 条件表达式
条件表达式: 真、假
1.比较运算符：= , < , > ,<=,>=, !=/<>
select * from stu where sex='女';
select * from stu where age=20;
select * from stu where mathe>60;
select * from stu where first_name <> '王';// !=
2.通配符 (模糊查询)like / not like
%:0---多个字符
select * from 表名 where 字段名 like '含有通配符的字符串'
select * from stu where last_name like '%w%';
-：一个字符
select * from stu where last_name like 'w_';
select * from stu where last_name not like 'w_';
3.算数运算符 (可以出现在where，但要与比较运算符一起）
+,-,*,/
select * from stu where (mathe+chinese) > 120;
4.逻辑运算符：and 、or、 in ()、between... and
select * from stu where mathe>60 and mathe<80;//在60与80之间的
select * from stu where chinese < 60 or chinese >80; //小于60 或 大于80
select * from stu where grades in (1,3);//查出括号列出的取值
select * from stu where mathe between 60 and 80;//小值在前大值在后
5.null, 值未知
select * from 表名 where 字段名 is null; //is not null
select * from stu where first_name is not null;
select * from stu where first_name is null;
六、排序
select * from 表名 where 条件表达式 order by 字段名 asc/desc
select * from 表名 order by 列数 asc/desc
select * from stu order by 字段名1 asc,字段名2 desc,... ;
select * from 表名 whele 条件表达式 order by 字段名 asc/desc
select * from stu order by age;
select * from stu order by 7 desc;
select * from stu order by mathe, chinese desc ;//当第一个字段相同时，按第二个字段排
select * from stu where sex='女' order by mathe desc;
七、聚合函数:from的前面, select 后面, 数值类型, 字段只能有一个
count(), max(), min(),sum(), avg()
1.查表的所有记录个数 count(字段名)或count(*)
select count(sex) 总数 ，from stu;
select count(*) 总数 from stu;
select count(*) "1班人数" from stu where grades=1;
2.max(数值类型字段),min(数值类型字段)
select max(mathe) from stu where sex='男';
select min(mathe) , max(mathe)from stu where sex='男';
3.sum(数值类型字段), avg(数值类型字段)
select sum(mathe) from stu;
select sum(mathe)/count(*) from stu;
select avg(mathe) from stu;
八、rownum：用于从查询返回的行的编号，
1. = 1
2. <= 数值
3. < 数值
select rownum, last_name, id from stu where age=20 and rownum<=2 ;
select * from stu where age=20 and rownum =1;
select * from stu where age =20 and rownum<=2;
select * from stu where rownum<5;
分组查询
select 字段1,字段2 , 聚合函数() from 班表名 group by 字段1,字段2 having 条件 order by 字段/聚合函数;
select 字段1,字段2 , 聚合函数() from 班表名 where 条件表达式 group by 字段1,字段2 order by 字段/聚合函数;
条件表达式：不能出现聚合函数
条件 ：可以出殃聚合函数
select grades, sex, count(*) from stu group by grades, sex;
select grades, max(mathe) from stu group by grades;
select grades, avg(mathe) from stu group by grades order by avg(mathe) desc;
数学的平均分大于75人
select last_name,avg(mathe) from stu group by last_name having avg(mathe)>75;
数学平均大于75分的班级
select grades,avg(mathe) from stu group by grades having avg(mathe)>75;
按班级分组，并且将数学成绩大于50分的求平均并排序
select grades,avg(mathe) from stu where mathe>50 group by grades order by avg(mathe) desc;
九、函数
1、lower(字段), upper(), initcap()
select * from stu where upper(last_name)='w';
select upper(last_name), first_name from stu where upper(last_name)='W';
select lower(last_name), sex from stu where lower(last_name)='w';
select * from stu where lower(last_name)='w';
select upper(last_name), lower(last_name) from stu;
2、截取
substr(字符串的字段, 开始截取的位置， 截取长度)
select substr(last_name,2, 3) from stu;
3、字符串长度
length(字符串的字段)
select last_name, length(last_name) from stu;
4、round()四舍五入，trunc()截取 数值
select round(avg(mathe), 2) avg from stu;
select trunc(avg(mathe), 2) avg from stu;
十、子查询： 一个select 的查询结果要做为另一个select的条件
1、select * from 表名 where 字段 （select 字段 from 表名）
select * from stu where chinese > (select avg(mathe) from stu);
select * from stu where mathe= (select max(mathe) from stu );
select * from stu where mathe< (select max(mathe) from stu where sex='男' );
2、all, any , in
all:所有
select * from stu where id >= all (select id from stu where mathe>60); // 1,3,4,12
any:一个
select * from stu where id <= any (select id from stu where mathe>60);
select * from stu where id in (select id from stu where mathe>60); // 1,3,4,12
select * from stu where id =any (select id from stu where mathe>60); // 1,3,4,12
select * from stu where mathe >any (select mathe from stu where sex='男');
select * from stu where mathe > (select max(mathe) from stu where sex='男');
select grades from stu1 group by grades having count(*)<any (select count(*) from stu1 group by grades);
十一、多表查询 （有关联）
1. select * from 表名1 别名1， 表名2 别名2 where 连接的查询条件
n个表，条件有n-1个
select * from stu, course; //两两的相积
select * from stu , course where stu.id = course.id ;
select s.last_name, s.first_name, c.cname from stu s , course c where s.id = c.id ;
select s1.last_name, s1.age , s1.id from stu s1, stu s2 where s1.age>s2.age and s2.last_name='海' ;
2、子查询可以转换为多表查询
select * from stu where age > (select age from stu where last_name='小红');
select s1.* from stu s1,stu s2 where s1.age>s2.age and s2.last_name='小红';
3、内联接inner join
select * from 表名1 inner join 表名2 on 联接的条件 where 查询查条件
select * from stu inner join course on stu.id=course.id;
select * from stu , course where stu.id=course.id;
select * from stu inner join course on stu.id=course.id where stu.id>1;
4、外联接
select * from 表名1 left join 表名2 on 联接的条件 where 查询查条件
1)左联接 left join
select * from stu left join course on stu.id = course.id;
2)右联接 right join
select * from stu right join course on stu.id = course.id;
3）全联按 full join
select * from stu full join course on stu.id = course.id;
显示数学排序后的第4名到第6名的学生
select * from (select * from (select * from (select * from stu order by mathe desc) where rownum<=6) order by mathe) where rownum<=3 order by mathe desc ;
select * from (select * from (select * from (select * from (select * from stu order by mathe desc ) where rownum<=6) order by mathe) where rownum<=3) order by mathe desc;
十二、创建表：
create table 表名(
字段名1 数据类型 约束条件，
字段名2 数据类型 约束条件，
.。。。
字段名n 数据类型 约束条件
);
表名: 开头必是字母，1--30字符， 字母，数字，下划线，$ ,#
字段名1 表名唯一, 关键字不能为表名
1、插入表记录
1. insert into 表名 values(值1， 值2， 值3，值4,...)
insert into myTA values(1000, '李四张山', '男');
2.insert into 表名（字段名1， 。。。） values(值1，。。。)
insert into myTA(name) values('赵武');
2、修改表数据
update 表名 set 字段名1=值（表达式）， 字段名2=值（表达式），。。。 where 修改条件
update myTA set sex='女';//
update myTA set sex='F' where name='abcdefse' ;
update myTA set id = id+11, sex='MF' where name='李四张山';
3、删除
1.delete from 表名 where 删除条件 ;//删除表记录
truncate table 表名; //重新建立表结构,
delete from 表名；//没有修改表结构
2.drop table 表名; //删除整个表的
4、约束
1、主键 primary key 唯一值，并不能为空
create table myB(
id number(4) primary key,
name varchar2(8),
sex char(2)
);
2、非空约束 not null , 不能为空
create table myC(
id int primary key,
name varchar2(8) not null,
sex char(2) null
);
3、唯一约束 unique,赋值时值是唯一的，但空值可以出现多次
create table myD(
id int primary key,
name varchar2(8) unique ,
sex char(2) null
);
primary key == unique not null
create table myD(
id int primary key,
name varchar2(8) unique not null,
sex char(2) null
);
4、检查约束：check(),满足条件的才可以赋值成功
create table myE(
id int primary key,
name varchar2(8) unique ,
age int check(age>20 and age<60),
age int check(age beween 20 and 60),
sex char(2) check( sex in ('M','F') )
);
5、外键, 外键的值必需是主键中以存在的值
create table mySoct(
sid int,
mathc number(4),
chinec number(4),
constraint 外键名 foreign key(从表字段名) references 主表名(主表的字段名)
);
先删除从表，再删除主表
5、表结构1.加添表结构
alter table 表名 add(新字段名 数据类型, sex char(2) 。。。);
2.修改表结构：
alter table 表名 modify (字段名 数据类型，。。。);
数据类型：类型， 类型的取值范围
3.删除表结构
alter table 表名 drop column 字段名;//删除一列
alter table 表名 drop ( 字段名, 字段名，。。。);//删除多列
4.修字段名
alter table 表名 rename column 旧字段名 to 新字段名;
5.修改表名
rename 旧表名 to 新表名;
六、日期类型：date
1、to_date('日期字符串','格式字符串')
select * from gg where to_char(tt,'yy-mm-dd')='2018-2-6';
to_date('2000-02-5 12:30:05','yy-mm-dd hh24:mi:ss')
2、months_between(日期数据, 日期数据)，两个日期之间的月份
select months_between(日期数据, 日期数据) from myGg;
3、to_char( 日期字段名, '字符串的格式')
select * from myGg where to_char(eb,'dd-mm-yyyy' )='03-10-2018';
select to_char(mm,'$99999,999,999') from gg;
4、to_char( 数字段名, '字符串的格式')
select to_char(pr, '$999,999,999.99') from mygf;
L:当地货币符号
$：美元符号
9：一个数字
0：强制0显示
十三、用户： system / sys
1、创建用户: create user A identified by 123456;
2、权限分配：
DBA:所有全部
resource:可以创建实体，不可以创建数据库
connect :可以登录
分配权限：
grant connect, resource to 用户名;
grant select on 表名 to 用户名;
select * from 用户名.表名
commit;提交
删除权限
revoke connect from 用户名;
3、用户的切换
conn 用户/密码
4、修改用户密码
alter user 用户名 identified by 新密码;
5、删除用户
drop user 用户名;
注：在用户名下以有表，或。。, 在删除用户前必需先将表删除,再删除用户
十四、视图:用来保存查询结束
1、创建视图
create view 视图名 as 查询语句;
create view stu_mathe as select first_name||''||last_name as "姓名", mathe from stu where mathe>60;
select * from stu_mathe;
如果用户是普通用户，需要先赋权限
grant create view to 用户名;
2、修改视图
create or replace view 视图名 as 查询语句;
3、删除视图
drop view 视图名;
十五、索引
用于提高查询效率 , 表记录多时（10W）
查询结束在 5%，使用
create index 索引名 on 表名(字段名);
create index stu_index_first on stu(first_name);
select * from stu where first_name='王';
十六、序列：是一个计数器
1、定义
create sequence 序列名
start with 1 //开始值
increment by 1 //步长
minvalue 1 //最小值
maxvalue // 最大值 , nomaxvalue
cycle //循环，nocycle
cache //缓存， nocache
2.使用, 主键
insert
update
序列名.nextval
十七、同义词:另一个用户的表 的别名 .
两个普通用户,一个用户使用另一个用户中的表,视图...
1.第一个普通用户,需要具有connect, resource ,create synonym , 管理员授权
2.以第一个用户登录，创建同义词
create synonym 同义词名 for 第二个用户名.表名
3.以第二个用户登录，将对表的操作权限给第一个用户
gran select on 表名 to 第一个用户名
grant all on 表名 to 第一个用户名
4.以第一个用户登录，使用同义词
select * from 同义词名
