Oracle一些基础语法(一)

2024年 6月 30日 46.3k 0

-- 首先,以超级管理员的身份登录oracle  

     sqlplus sys/bjsxt as sysdba  

   

 --然后,解除对scott用户的锁  

     alter user scott account unlock;  

 --那么这个用户名就能使用了。  

 --(默认全局数据库名orcl)  

   

 1、select ename, sal * 12 from emp; --计算年薪  

 2、select 2*3 from dual;  --计算一个比较纯的数据用dual表 

 3、select sysdate from dual;  --查看当前的系统时间  

 4、select ename, sal*12 anuual_sal from emp; --给搜索字段更改名称(双引号 keepFormat 别名有特殊字符,要加双引号)。  

 5、--任何含有空值的数学表达式,最后的计算结果都是空值。  

 6、select ename||sal from emp;  --(将sal的查询结果转化为字符串,与ename连接到一起,相当于Java中的字符串连接)  

 7、select ename||'afasjkj' from emp;   --字符串的连接  

 8、select distinct deptno from emp;   --消除deptno字段重复的值  

 9、select distinct deptno , job from emp; --将与这两个字段都重复的值去掉  

 10、select * from emp where deptno=10;   --(条件过滤查询)               

 11、select * from emp where empno > 10;  --大于 过滤判断  

 12、select * from emp where empno 10  --不等于  过滤判断  

 13、select * from emp where ename > 'cba';  --字符串比较,实际上比较的是每个字符的AscII值,与在Java中字符串的比较是一样的  

 14、select ename, sal from emp where sal between 800 and 1500;  --(between and过滤,包含800 1500)  

 15、select ename, sal, comm from emp where comm is null;  --(选择comm字段为null的数据)  

 16、select ename, sal, comm from emp where comm is not null;  --(选择comm字段不为null的数据)  

 17、select ename, sal, comm from emp where sal in (800, 1500,2000);  --(in 表范围)  

 18、select ename, sal, hiredate from emp where hiredate > '02-2月-1981'; --(只能按照规定的格式写)  

 19、select ename, sal from emp where deptno =10 or sal >1000;  

 20、select ename, sal from emp where deptno =10 and sal >1000;  

 21、select ename, sal, comm from emp where sal not in (800, 1500,2000);  --(可以对in指定的条件进行取反)  

 22、select ename from emp where ename like '%ALL%';   --(模糊查询)  

 23、select ename from emp where ename like '_A%';    --(取第二个字母是A的所有字段)  

 24、select ename from emp where ename like '%/%%';   --(用转义字符/查询字段中本身就带%字段的)  

 25、select ename from emp where ename like '%$%%' escape '$';   --(用转义字符/查询字段中本身就带%字段的)  

 26、select * from dept order by deptno desc; (使用order by  desc字段 对数据进行降序排列 默认为升序asc);  

 27、select * from dept where deptno 10 order by deptno asc;   --(我们可以将过滤以后的数据再进行排序)    

 28、select ename, sal, deptno from emp order by deptno asc, ename desc;   --(按照多个字段排序 首先按照deptno升序排列,当detpno相同时,内部再按照ename的降序排列)  

 29、select lower(ename) from emp;  --(函数lower() 将ename搜索出来后全部转化为小写);  

 30、select ename from emp where lower(ename) like '_a%';  --(首先将所搜索字段转化为小写,然后判断第二个字母是不是a)  

 31、select substr(ename, 2, 3) from emp;    --(使用函数substr() 将搜素出来的ename字段从第二个字母开始截,一共截3个字符)  

 32、select chr(65) from dual;  --(函数chr() 将数字转化为AscII中相对应的字符)   

 33、select ascii('A') from dual;  --(函数ascii()与32中的chr()函数是相反的 将相应的字符转化为相应的Ascii编码)                                                                                                                                                                                                                                                                                                                                             )  

 34、select round(23.232) from dual;  --(函数round() 进行四舍五入操作)  

 35、select round(23.232, 2) from dual;  --(四舍五入后保留的小数位数 0 个位 -1 十位)  

 36、select to_char(sal, '$99,999.9999')from emp;  --(加$符号加入千位分隔符,保留四位小数,没有的补零)  

 37、select to_char(sal, 'L99,999.9999')from emp;  --(L 将货币转化为本地币种此处将显示¥人民币)  

 38、select to_char(sal, 'L00,000.0000')from emp;  --(补零位数不一样,可到数据库执行查看)  

 39、select to_char(hiredate, 'yyyy-MM-DD HH:MI:SS') from emp;  --(改变日期默认的显示格式)  

 40、select to_char(sysdate, 'yyyy-MM-DD HH:MI:SS') from dual;  --(用12小时制显示当前的系统时间)  

 41、select to_char(sysdate, 'yyyy-MM-DD HH24:MI:SS') from dual;  --(用24小时制显示当前的系统时间)  

 42、select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:24:45','YYYY-MM-DD HH24:MI:SS');   --(函数to-date 查询公司在所给时间以后入职的人员)  

 43、select sal from emp where sal > to_number('$1,250.00', '$9,999.99');   --(函数to_number()求出这种薪水里带有特殊符号的)  

 44、select ename, sal*12 +  nvl(comm,0) from emp;   --(函数nvl() 求出员工的"年薪 + 提成(或奖金)问题")  

 45、select max(sal) from emp;  -- (函数max() 求出emp表中sal字段的最大值)  

 46、select min(sal) from emp;  -- (函数max() 求出emp表中sal字段的最小值)  

 47、select avg(sal) from emp;  --(avg()求平均薪水);  

 48、select to_char(avg(sal), '999999.99') from emp;   --(将求出来的平均薪水只保留2位小数)  

 49、select round(avg(sal), 2) from emp;  --(将平均薪水四舍五入到小数点后2位)  

 50、select sum(sal) from emp;  --(求出每个月要支付的总薪水)  

   

 ------------------------/组函数(共5个):将多个条件组合到一起最后只产生一个数据------min() max() avg() sum() count()----------------------------/  

 51、select count(*) from emp;  --求出表中一共有多少条记录  

 52、select count(*) from emp where deptno=10;  --再要求一共有多少条记录的时候,还可以在后面跟上限定条件  

 53、select count(distinct deptno) from emp;   --统计部门编号前提是去掉重复的值  

 ------------------------聚组函数group by() --------------------------------------  

 54、select deptno, avg(sal) from emp group by deptno;  --按照deptno分组,查看每个部门的平均工资  

 55、select max(sal) from emp group by deptno, job; --分组的时候,还可以按照多个字段进行分组,两个字段不相同的为一组  

 56、select ename from emp where sal = (select max(sal) from emp); --求出  

 57、select deptno, max(sal) from emp group by deptno; --搜素这个部门中薪水最高的的值  

 --------------------------------------------------having函数对于group by函数的过滤 不能用where--------------------------------------  

 58、select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; (order by )--求出每个部门的平均值,并且要 > 2000  

 59、select avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;--求出sal>1200的平均值按照deptno分组,平均值要>1500最后按照sal的倒序排列  

 60、select ename,sal from emp where sal > (select avg(sal) from emp);  --求那些人的薪水是在平均薪水之上的。  

 61、select ename, sal from emp join (select max(sal) max_sal ,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno=t.deptno);  --查询每个部门中工资最高的那个人  

 ------------------------------/等值连接--------------------------------------  

 62、select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;  --自连接,把一张表当成两张表来用  

 63、select ename, dname from emp, dept;  --92年语法 两张表的连接 笛卡尔积。  

 64、select ename, dname from emp cross join dept; --99年语法 两张表的连接用cross join  

 65、select ename, dname from emp, dept where emp.deptno = dept.deptno; -- 92年语法 表连接 + 条件连接  

 66、select ename, dname from emp join dept on(emp.deptno = dept.deptno); -- 新语法  

 67、select ename,dname from emp join dept using(deptno); --与66题的写法是一样的,但是不推荐使用using :假设条件太多  

 --------------------------------------/非等值连接------------------------------------------/  

 68、select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); --两张表的连接 此种写法比用where更清晰  

 69、select ename, dname, grade from emp e  

     join dept d on(e.deptno = d.deptno)  

     join salgrade s on (e.sal between s.losal and s.hisal)  

     where ename not like '_A%';  --三张表的连接  

 70、select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); --自连接第二种写法,同62  

 71、select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); --左外连接 把左边没有满足条件的数据也取出来  

 72、select ename, dname from emp e right join dept d on(e.deptno = d.deptno); --右外连接  

 73、select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s  on    (t.avg_sal between s.losal and s.hisal);--求每个部门平均薪水的等级  

 74、select ename from emp where empno in (select mgr from emp); -- 在表中搜索那些人是经理  

 75、select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal)); -- 面试题 不用组函数max()求薪水的最大值  

 76、select deptno, max_sal from  

     (select avg(sal) max_sal,deptno from emp group by deptno)  

         where max_sal =  

         (select max(max_sal) from  

          (select avg(sal) max_sal,deptno from emp group by deptno)  

     );--求平均薪水最高的部门名称和编号。  

 77、select t1.deptno, grade, avg_sal from  

       (select deptno, grade, avg_sal from  

     (select deptno, avg(sal) avg_sal from emp group by deptno) t  

         join salgrade s on(t.avg_sal between s.losal and s.hisal)  

       ) t1  

     join dept on (t1.deptno = dept.deptno)  

     where t1.grade =   

       (  

         select min(grade) from  

           (select deptno, grade, avg_sal from  

     (select deptno, avg(sal) avg_sal from emp group by deptno) t  

     join salgrade s on(t.avg_sal between s.losal and s.hisal)  

      )  

    )--求平均薪水等级最低的部门的名称 哈哈 确实比较麻烦  

 78、create view v$_dept_avg_sal_info as  

     select deptno, grade, avg_sal from  

        (select deptno, avg(sal) avg_sal from emp group by deptno) t  

     join salgrade s on(t.avg_sal between s.losal and s.hisal);  

     --视图的创建,一般以v$开头,但不是固定的  

   

   

 79、select t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1  

     join dept on (t1.deptno = dept.deptno)  

     where t1.grade =   

       (  

         select min(grade) from  

          v$_dept_avg_sal_info t1  

      )  

    )--求平均薪水等级最低的部门的名称 用视图,能简单一些,相当于Java中方法的封装  

   

 80、---创建视图出现权限不足时候的解决办法:  

     conn sys/admin as sysdba;  

         --显示:连接成功 Connected  

     grant create table, create view to scott;  

        -- 显示: 授权成功 Grant succeeded  

 81、-------求比普通员工最高薪水还要高的经理人的名称 -------  

     select ename, sal from emp where empno in  

        (select distinct mgr from emp where mgr is not null)  

     and sal >  

     (  

        select max(sal) from emp where empno not in  

          (select distinct mgr from emp where mgr is not null)  

     )  

 82、---面试题:比较效率  

        select * from emp where deptno = 10 and ename like '%A%';--好,将过滤力度大的放在前面  

        select * from emp where ename like '%A%' and deptno = 10;  

 83、-----表的备份  

        create table dept2 as select * from dept;  

 84、-----插入数据  

         insert into dept2 values(50,'game','beijing');  

       ----只对某个字段插入数据  

         insert into dept2(deptno,dname) values(60,'game2');  

 85、-----将一个表中的数据完全插入另一个表中(表结构必须一样)  

     insert into dept2 select * from dept;  

 86、-----求前五名员工的编号和名称(使用虚字段rownum 只能使用 必须使用子查询)  

     select empno,ename from emp where rownum 10;  

 87、----求薪水最高的前5个人的薪水和名字---------  

     select ename, sal from (select ename, sal from emp order by sal desc) where rownum =6 and r=2);  

     3、  

         select sname from s where sno in (select sno, from sc where cno=1 and cno in  

                             (select distinct sno from sc where cno = 2);  

                          )  

   

 94、--------------创建表--------------  

        create table stu  

     (  

     id number(6),  

     name varchar2(20) constraint stu_name_mm not null,  

     sex number(1),  

     age number(3),  

     sdate date,  

     grade number(2) default 1,  

     class number(4),  

     email varchar2(50) unique  

     );  

 95、--------------给name字段加入 非空 约束,并给约束一个名字,若不取,系统默认取一个-------------  

        create table stu  

     (  

     id number(6),  

     name varchar2(20) constraint stu_name_mm not null,  

     sex number(1),  

     age number(3),  

     sdate date,  

     grade number(2) default 1,  

     class number(4),  

     email varchar2(50)  

     );  

 96、--------------给nameemail字段加入 唯一 约束 两个 null值 不为重复-------------  

        create table stu  

     (  

     id number(6),  

     name varchar2(20) constraint stu_name_mm not null,  

     sex number(1),  

     age number(3),  

     sdate date,  

     grade number(2) default 1,  

     class number(4),  

     email varchar2(50) unique  

     );  

 97、--------------两个字段的组合不能重复 约束:表级约束-------------  

        create table stu  

     (  

     id number(6),  

     name varchar2(20) constraint stu_name_mm not null,  

     sex number(1),  

     age number(3),  

     sdate date,  

     grade number(2) default 1,  

     class number(4),  

     email varchar2(50),  

     constraint stu_name_email_uni unique(email, name)  

     );  

 98、--------------主键约束-------------  

        create table stu  

     (  

     id number(6),  

     name varchar2(20) constraint stu_name_mm not null,  

     sex number(1),  

     age number(3),  

     sdate date,  

     grade number(2) default 1,  

     class number(4),  

     email varchar2(50),  

     constraint stu_id_pk primary key (id),  

     constraint stu_name_email_uni unique(email, name)  

     );  

   99、--------------外键约束   被参考字段必须是主键 -------------  

        create table stu  

     (  

     id number(6),  

     name varchar2(20) constraint stu_name_mm not null,  

     sex number(1),  

     age number(3),  

     sdate date,  

     grade number(2) default 1,  

     class number(4) references class(id),  

     email varchar2(50),  

     constraint stu_class_fk foreign key (class) references class(id),  

     constraint stu_id_pk primary key (id),  

     constraint stu_name_email_uni unique(email, name)  

     );  

       

     create table class   

     (  

     id number(4) primary key,  

     name varchar2(20) not null  

     );  

 100、---------------修改表结构,添加字段------------------  

     alter table stu add(addr varchar2(29));  

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论