SQL01
一、进入数据库
ll utlsampl.sql
sqlplus / as sysdba
connected to an idle instance. 说明数据库没有启动
SQL>startup;
Database open.
SQL>@脚本名字,脚本目录。
@utlsampl.sql (建了几张表,创建样例)(@此处是相对路径)
Disconnected from Oracle Database (最小化)
SQL> conn scott/tiger
Connected. (连进去了,密码是Tiger)
SQL>@
二、使用select语句查询
–查询数据
–select语句中算数表达式和NULL
–SELECT * | {[] column | expression[alias]…} (*代表所有列,|或者) FROM
SQL> select * form dept;
SQL> select department_id, location_id from departments;
SQL> select deptno , dname from dept;
···DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SQL> select dname , deptno from dept;
DNAME DEPTNO
ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40
1)select 按照输出按照列名顺序
2)SQL> select * (子句)
2 from dept (字句)
3 ; (字句)
三、编写SQL语句
–SQL 语言大小写不敏感
–SQL 可以写在一行或多行
–关键字不能被缩写也不能分行
–使用缩进提高语句的可读性
–SQLPlus中必须;结尾
SQL> select *
2 from dept;
SQL>
选择全部列
SQL> select *
2 from emp;
选择特定列
SQL> select EMPNO , ENAME
2 from emp;
SQL>
去重
SQL> select distinct deptno (distinct 去重)
2 from emp;
DEPTNO
30
20
10
SQL>SQL> select ename , deptno , job
2 from emp;
ENAME DEPTNO JOB
SCLARK 10 MANAGER
KING 10 PRESIDENT
MILLER 10 CLERK
JONES 20 MANAGER
SQL>
SQL> select distinct job , deptno
2 from emp;
DEPTNO JOB
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
两个数据完全一样,不被选入。 例如:10 CLERK 和 10 CLERK
SQL> select ename , sal , comm
2 from emp;
四、使用算术运算符
SQL> select ename , sal , sal + 2000 ,comm
2 from emp; 能做运算的是数字列
ENAME SAL SAL+2000 COMM
SMITH 800 2800
ALLEN 1600 3600 300
SQL> select ename , sal , sal+2000 , 200 +300 , comm
ENAME SAL SAL+2000 (数字加在每一列)
SQL> select ename , sal , comm , comm + 10000
2 form emp;
SQL>
SQL> select ename , sal , 12*sal + comm
2 from emp;
SQL> select ename , sal , 12*ql + nvl (comm,0)
2 from emp;
五、定义列别名
–重命名一个列标题
–便于计算
–紧跟列名(也可以在列名和别名之间加入关键字’AS’)
SQL> select ename emp_name sal * 12 as “Sal”
2 from emp;
SQL> select ename , sal , 12*ql + nvl(comm,0) as
连接运算符
SQL> select 'My name is ’ , ename
2 from emp;
SQL>
SQL> select 'My name is ’ | | ename
2 from emp;
SQL>
SQL>
SQL> select 'My name is ’ || ename as “Ename”
2 from emp;
数据库中双引号只在起别名的时候用到。
Ename
My name is SMITH
My name is ALLEN
My name is WARD
My name is JONES
14 rows selected.
SQL>
SQL> select ‘a’ , 200 , ‘2019-5-10’
2 from dept;
’ 200 '2019-05-10
a 200 2019-5-10
a 200 2019-5-10
a 200 2019-5-10
a 200 2019-5-10
表有几行输出几行。
SQL> select ename || ‘:1 Month salary=’ || sal as “monthly”
2 from emp;
SQL> select distince comm from emp;
COMM
1400
500
300
0
SQL>
SQL>
SQL>
SQL> desc emp
NAME NULL Type
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)字符类型
HIREDATE DATE
SQL>
SQL>
SQL> 变长varchar2(10) ,cat 判断需要几个字符。
SQL> 定长char(10),cat ,10个字符,存的快,占用空间多。
SQL>desc dept
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
BONUS TABLE
DEPT TABLE
EMPNO TABLE
SALGRADE TABLE
SQL>