Oracle Database lab handouts-Reading Notes (1), oracle Handouts

Oracle Database lab handouts-Reading Notes (1), oracle Handouts

1. Activate the Locked User
Alter user scott account unlock identified by tiger;
2. Use the embedded script to create a scott user
@ % Oracle_home % \ rdbms \ admin \ utlsampl. SQL
3. view all tables of the current user
Select * from tab;
4. input the screen display to a text file.
Spool D: 1.txt
Select * from emp;
Spool off
============================================

Spool D: 1.txt append
Select * from emp;
Spool off
5. run SQL statements in the buffer zone
Run or/
6. @ Script: run the specified script.
@ % Oracle_home % \ rdbms \ admin \ utlxplan. SQL
@ Is the script running in the relative path. It is generally used when the big tutorial calls a small script.
7. Save: Save the SQL statement in the current SQLPLUS buffer to the specified file.
Save c: \ 2.txt
8. Get: Call the SQL statement in the file into the SQLPLUS buffer.
Get D: \ 2.txt
9. Edit: Edit the SQL statement in the current SQlPlus Buffer
Such as ed
10. Annotations
Line comment: -- Comments the current line
Block comment:/**/comments multiple lines
11. Clear screen command
Clear screen
12. Change the user's password.
Alter user sys identified by sys;
13. Remove duplicate rows from distinct
Select distinct deptno from emp;
14. where and order
Select deptno, ename from emp where deptno = 10 order by deptno;
15. relational operations
=, <> ,! =, ^ =, >=, <=, >,<, Between... and... (including upper and lower bounds)
16. in And like
Select deptno, ename, sal from emp where deptno in (10, 20 );
Like
-One wildcard, matching only one character,
% Wildcard no or multiple characters
Select ename, deptno from emp where ename like 'J % ';
Select ename from emp where ename like '% s _ % 'escape s ';
17. Priority
Arithmetic Operations
Connection operation
Relational operation
Is [not] null, like, [not] In
Between
Not
And
Or
Bracket force priority
18. order by clause
The default value is asc in ascending order.
Desc must be specified in descending order.
19. Operation Date Functions
Sysdate:
Eg: select sysdate from dual;
Which day is six months later:
Select add_months (hiredate, 6), hiredate from emp;
Take the interval of two months:
Select months_between (sysdate, hiredate), sysdate, hiredate from emp;
Which day is the next Friday from the current date (if it is a Chinese client, it will be expressed on 'Friday ):
Select next_day (hiredate, 'Friday'), hiredate from emp;
Which day is the end of the date:
Select last_day (hiredate), hiredate from emp;
Carry and trunc of a Date ):
Select hiredate, round (hiredate, 'mm'), round (hiredate, 'month') from emp;
Select hiredate, round (hiredate, 'yyyy'), round (hiredate, 'Year') from emp;
Select hiredate, trunc (hiredate, 'mm'), trunc (hiredate, 'month') from emp;
Select hiredate, trunc (hiredate, 'yyyy'), trunc (hiredate, 'Year') from emp;
The carry and truncation of numbers are centered on the decimal point and the values before or after the decimal point, while the carry and truncation of dates are centered on the year, month, day, hour, minute, and second.
20. Display and conversion of data types
To_char, To_date, to_number
To convert a date to a string, you must describe the format of the string.
Select ename. to_char (hiredate, 'yyyy/mm/dd') from emp;
FM clears the front zero and space.
Select ename, to_char (hiredate, 'fmyyyy/mm/dd') from emp;
Other formats: years, month, mon, day, dy, am, ddsp, and ddspth (double quotation marks are required for adding strings in the format)
Select to_char (hiredate, 'fmyyyy "year" mm "month" ') from emp;
The number of seconds from the current zero point:
Select sysdate, to_char (sysdate, 'ssss') ss from dual;
Select to_char (sysdate, 'yyyy year mm month mon dd day dy ddsp ddspth') from dual;

The oracle Database experiment allows you to quickly design a database system.

At this point, I can only see the second line, and the rest will not be able to be seen.
 
Oracle Database Integrated Design lab report

The table is not properly created and does not meet the database standardization requirements. The course is not determined by the student ID. The course can be used as an independent table. In addition, the score is determined by the student ID and the course number, therefore, the tables Table should also be listed as an independent table.
It should be divided into three tables to create a database
1. Student Information table (student ID, name, gender, date of birth, contact number)
2. course schedule (course No., course name)
3. Student ID, course number, and score)
The mid-term student ID is the primary key of the student table, and the course number is the primary key of the Course table. The combination of the student ID and course number is the primary key of the student table. The two jointly determine the score.
After splitting, you can avoid data redundancy and eliminate the dedicated delivery dependency!
Integrity reference is very simple. You can solve it yourself.