[Vertex practice] oracle study 2

2012121923: 08by lingyue (ps: Master, please pass by, new users can smile) Instructor: Professor Lin Yu content: SQL basics 1. what are SQL statements? 2. understanding SQL statements about table operations 3. understanding SQL statement query commands 4. master common SQL functions 5. master the operations recorded in the data table by SQL statements. 6. understand other database objects

By lingyue (ps: Master, please pass by, new users can smile) Instructor: Professor Lin Yu content: SQL basics 1. what are SQL statements? 2. understanding SQL statements about table operations 3. understanding SQL statement query commands 4. master common SQL functions 5. master the operations recorded in the data table by SQL statements. 6. understand other database objects

By lingyue
(Ps: You must pass by. You can laugh at it)

INSTRUCTOR: Lin Miao

Professor content:

SQL Basics

1. Understand what SQL statements are

2. Understand SQL statements about table operations

3. Understand SQL statement query commands

4. Master common SQL Functions

5. Understand the operations recorded in the data table by SQL statements.

6. Understand other database objects: views, sequences, indexes, and synonyms

7. Unbind database Permissions

Personal Learning:

Today is really fast enough. This is tough for me to get started with oracle!

Write down several knowledge points that you do not understand in the classroom:

1. Insert '25-January 1, December-01 'For the period date ';

2. number (8, 2) 123456.12 the integer is only 6 digits;

3. Differences between char and varchar2. In terms of understanding, the fixed length must occupy resources, and the resolution speed is fast. The variable length may occupy less resources, and the resolution speed is slow;

4. Several constraint keys: check, default, and unique;

5. to_char ();

6. Cancel duplicate row distinct

7. wildcard % _

8. escape/escape Character

9. Connection query (this content is too large)

Learning step by step blog article http://blog.163.com/yuxiangtong0524@126/blog/static/8008616320103624845309/

Connecting ---------------------------------------------------------- ORACLE left and right connections

14:48:45 | category: Oracle | large font size, small/medium subscription

1.

Left join
A. a = B. B (+)
Right join
A. a (+) = B. B

2.

Create table dali. test1 (a int, B int );
Create table dali. test2 (a int, B int );

Insert into dali. test1 values (1,456 );
Insert into dali. test1 values (2,427 );
Insert into dali. test2 values (1,45456 );
Insert into dali. test2 values (3,45656 );

--- Internal connection
Select * from dali. test1 a, dali. test2 B where a. a = B.;

--- Left join
Select * from dali. test1 a, dali. test2 B where a. a = B. a (+ );

--- Right join
Select * from dali. test1 a, dali. test2 B where a. a (+) = B.;

--- Full connection
Select * from dali. test1 a, dali. test2 B where a. a = B. a (+)
Union
Select * from dali. test1 a, dali. test2 B where a. a (+) = B.;

--- Dikar
Select * from dali. test1, dali. test2;

3. Data Table connections include:
1. Inner join (natural join): Only the rows matching the two tables can appear in the result set.
2. external connections: including
(1) left Outer Join (the table on the left is not restricted)
(2) Right Outer Join (the table on the right is not restricted)
(3) Full outer join (no restrictions are imposed on both the left and right tables)
3. Self-join (the connection occurs in a base table)
Select a. studentno, a. studentname, B. classname
From students a, classes B
Where a. classid (+) = B. classid;
STUDENTNO STUDENTNAM CLASSNAME
--------------------------------------------------
1. Zhou Hu, Class 1, Grade 1
2. Class 2, Grade 1, Zhou Lin
3. Class 3, Grade 1
The preceding statements are right join:
That is, the other side of the "(+)" is the connection direction.
The record is displayed, regardless of whether it is matched on the left. That is to say, in the above example, none
Will there be a situation where no student exists in a class, and the class name will be
The query structure appears.
Otherwise:
Select a. studentno, a. studentname, B. classname
From students a, classes B
Where a. classid = B. classid (+ );
STUDENTNO STUDENTNAM CLASSNAME
--------------------------------------------------
1. Zhou Hu, Class 1, Grade 1
2. Class 2, Grade 1, Zhou Lin
3 zhonglinda
This is left join. no matter whether the student has a department number that can be matched in a class,
The student's record is displayed.
Select a. studentno, a. studentname, B. classname
From students a, classes B
Where a. classid = B. classid;
This is a commonly used internal connection, displaying records that both tables meet the conditions
In short,
The left join shows all on the left and the right are the same as those on the left.
The right connection shows all and the left are the same as the right
Only matching conditions are displayed for internal connections! ......

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

10. A small problem occurred while learning the view. Baidu solved the problem of scott having no permission to establish the view.

Introduction to piranha Blog A http://piranha.iteye.com/blog/847877

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If you grant DBA permissions directly when creating A user, you can directly query user A's table in user B. However, when creating A view, no permissions are reported, in this case, you need to authorize user B to access the table in user.

-- Create view permission. Generally, this statement is used for searching on the Internet, but it still cannot be created.
Grant create view to B;

-- Grant query permission
Grant select any table to B;

-- Grant Permissions
Grant select any dictionary to B;


After the preceding three locations, the view can be created normally.

Certificate ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

It's too late. Hurry up and go to bed!