Learned the simplest kinds of statements, but also installed a few times the database, was looking at that high-level programming, but there is a primer did not see, and then read the first to say,
More practice, familiar with the most basic operation.
I used to log in before using Conn carriage return, and then enter the password, this document is the first one to introduce another method of login.
Conn Scott/xx
This will be directly logged in,/before the user name,/after the password
Conn Sys/xx as Sysdba
This is the login with the administrator
2. Then save the SQL you just entered as a file with the. sql suffix
SELECT * from EMP;
Save/home/oracle/sql/1.sql;
This will save this file to the path, open 1.sql can see the above command.
If you want to re-save a different statement, you want to replace it in this file.
Save/home/oracle/sql/1.sql Replace;
If you want to run a statement in a file, you can
@/home/oracle/sql/1.sql;
This will run.
3. Then there are some actions under the user
Show Current User
Show user;
Show all tables under this user
SELECT * from tab;
Show the structure of one of the tables
Describe EMP;
Or
DESC EMP;
Under this user to view the other user's table, the front to add that user's name separated by Dot
SELECT * from Oe.orders;
Of course, you have to have permission to look at other people's tables, generally use SYS to see anything else, and if you use Scott, you can't see it.
4. Some basic exercises, based on the table scott.emp
Bonuses and wages for each employee and
Select Ename, Sal+comm from EMP;
One problem here is that the bonus is null and cannot be obtained. Need to be solved.
Here the function of NVL () is used
Select Ename, NVL (Sal+comm, Sal) from EMP;
This means that if Sal+comm is null, the SAL is returned, if not NULL, the Sal+comm is returned, and NULL is returned if NULL
If you want to practise more, look for something, and now find a way to do it.
Select Ename, SAL+NVL (comm,0) from EMP; This is what I saw when I practiced the next chapter and came back to add.
Identify employees in department 10 who are neither ' MANAGER ' nor ' clerk ' and pay more than 2000
Select ename from emp
where deptno=10 and job not in (' MANAGER ', ' Clerk ') and sal>2000;
Find out what job there is a bonus
SELECT DISTINCT job from emp
Where Comm is not null and comm>0;
Here's the beginning of the function exercise
Character function of one-line function
Here to use the dual this virtual table, the relevant introduction to see this
Http://www.cnblogs.com/qiangqiang/archive/2010/10/15/1852229.html
Select Upper (' AAAA ') from dual;
Turn AAAA all into uppercase
Like what:
SELECT * from emp where Ename=upper (' Smith ');
Equivalent
SELECT * from emp where ename= ' SMITH ';
And, of course, all of them are lowercase lower.
Select lower (' FFF ') from dual;
and a capital letter, like when you get a name.
Select Initcap (ename) from EMP;
The next one's connected. Concat and | |
Select Concat (ename, Job) from EMP;
Select (Ename | | "| | Job) from EMP;
Calculating string Lengths
Select ename, Length (ename) from EMP;
Output sub-string
Select Ename, substr (ename, 2, 2) from EMP;
Replace character
Select ename, replace (ename, ' S ', ' SS ') from EMP;
Find substring Location
Select InStr (' Hello World ', ' or ') from EMP;
The result is where the or is located index
You can also set where to start looking, and the first few times you find
Select InStr (' Hello World OR and ', ' or ', 6, 2) from EMP;
This is the position value that starts with the 6th character and encounters the second time
Lpad and Rpad are restricted character lengths and are insufficient to be filled with specific symbols
Select Lpad (ename, 8, ' * ') from EMP;
Select Rpad (ename, 8, ' * ') from EMP;
Remove the edge of the top and tail of the space trim LTrim RTrim
Select Trim (' DDD ') from dual;
Select LTrim (' DDD ') from dual;
Select RTrim (' DDD ') from dual;
Rounding function Round
Select Round (454,-2) from dual;
Select Round (454.3456, 2) from dual;
The second parameter is negative, which is a positive number rounded from the first
The second argument is positive, which is to keep a few decimals, rounded back
Find more MoD
Select mod (3,4) from dual;
Select mod (4,3) from dual;
Truncation function trunc and round similar, but not rounded
Select Trunc (455666,-2) from dual;
Select Trunc (45.6666, 2) from dual;
Http://database.51cto.com/art/201004/197703.htm
Calculates the number of months between two dates Months_between
Select Months_between (sysdate, HireDate) from EMP;
You can use the round on it.
Select Round (Months_between (sysdate,hiredate), 0) from EMP;
Http://blog.sina.com.cn/s/blog_63c5e4a80100o7pl.html
Add a number of months to a date to get a new date
Select Add_months (sysdate, 2) from dual;
Select Add_months (Sysdate,-2) from dual;
Http://blog.sina.com.cn/s/blog_4cbadf5a0100fafh.html
The next specified date for a given date
Select Next_day (sysdate, ' Sunday ') from dual;
Next Sunday of the current date
http://blog.csdn.net/chenjinlin1/article/details/6582761
But the question in this article, he says, is that what's going to return is the cycle of the week, that's because it's coming back, not about going back to the next week.
Get the last day of the month Last_day
Select Last_day (sysdate) from dual;
Http://www.360sdn.com/oracle/2013/0615/401.html
Oracle Learning record six more learning materials exercises