--Create a test tableCreate or Replace TableStudent (XH Number(4),--School NumberXmvarchar2(Ten),--nameSexChar(2),--SexBirthday date,--DateSal Number(7,2)--Scholarships);--Add a fieldAlter TableStudentAdd(StudentID Number(Ten));--Add multiple
All knowledge is better than DEMO,SOThe test table required by the demo CREATE TABLE" SCOTT". " TEST "(" ID " Number(6,0) not NULLENABLE, "USERNAME"VARCHAR2( -BYTE), "Userpwd"VARCHAR2( -BYTE), "New_email"VARCHAR2( -BYTE), "RegDate" DATEDEFAULT NULL,
1. To optimize the query, avoid full-table scanning as far as possible, and first consider establishing an index on the columns involved in the Where and order by.2. Avoid null-valued fields in the WHERE clause, which will cause the engine to
In Oracle, users can be established and authorized to operate.
To create the user's syntax:
CREATE USER by password;Example: Create a test user with a password of test123CREATE USER by test123;If you want to create a user, you should
PL/SQL--Indicates a comment| | | ' | | Stitching stringsAliasNull value and all numeric calculations result in nullEmpty value conversion function:NVL (column, number to convert)In equivalent to link with or.Not is equivalent to linking with
The data are as follows:Name IDZhang SanRequired implementations:Name IDZhang 31Zhang 32Zhang 33--Create a temporary tableCreate TableTmp as(Select 'Zhang San'Name' the'Id fromdual);--notation 1SelectName,regexp_substr (ID,'[^,]+',1, Level) asID
To see if the XXX table space is auto-extended:Select file_name,autoextensible,increment_by from Dba_data_files;Select file_id,file_name,tablespace_name,autoextensible,increment_by from Dba_data_files order by file_id Desc;To open the table space
User ManagementSYS System (Administrator)Scott (normal user)Premise: On Oracle, assume that you are the administrator of the Oracl, and when you need to create a user, you operate.1, create user (SYS system user)Create user username identified by
In a recent new project, you tried to use NCLOB in an Oracle database to hold large XML strings.In the Code Auto-generation tool (through the JDBC driver, read the database table structure, automatically generate the corresponding Java code,
Create userCreate user username identified by password;Authorized Login PermissionGrant Connect,resource to user name;Authorize User Table Query permissionsGrant Select on table name to user name;A user accesses the data table under B User, does not
1. Equivalent connection: Take the same record as the relationship columnSELECT * from emp e,dept d where E.deptno=d.deptno;Find the same records as the Deptno column in the EMP and dept tables2, non-equivalent connection: Take a record of the
A) UsersUsers in Oracle are divided into two major categories1) when the Oracle database server is created, users that are automatically created by the system are called system users, such as Sys.2) users created by the system users, called ordinary
--condition Comparison/*=,! =,<>,,=,any,some,allis Null,is not Nullbetween x and Yin (list), not In (list) exists (sub-query) Like _,%,escape ' ' _\% escape ' \ '*/--any some can select E.ename, e.job,e.sal from EMP E where e.sal as long as any one
Common functions:• Statistics: Count (), returns the result based on the actual amount of data in the table;• Sum: Sum (), which is a statistic for numbers, sums• Average: Avg (), various data types support• Max: Max (), various data types support•
In the recent work has encountered the production Environment database table was deleted, in fact, such things should not have happened.A few small suggestions:
Production environment database developers can only have query permissions,
First, System log and database log Patrol1. System LogTail-500f/var/log/messagesSee if there are any alarms or errors2. Database log10G DatabaseCD $ORACLE _base/admin/{sid}/bdump---{SID} is a DB instance nameTAIL-500F Alert_{sid}.logCheck the
Updating a field is simple and easy to understand: Update TableA a set a.name= (select name from TableB B where a.id=b.id) is more verbose when updating multiple fields: Update TableA a set A.name= (select name from TableB b where a.id=b.id), a.age=
Oracle query OVER (partition by...) usage, oraclepartition
All examples are created under Scott, an Oracle user.
Note: The Red order by in the title indicates that order by must be included when this method is used.
1. rank ()/dense_rank () over
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service