Oracle database concepts and some basic SQL statements

1. Data

Definition: A symbol that describes a thing. For example: text, audio, and video are data.

2. Database

Storage of Data Warehouse, stored in the computer, in a certain format, can be shared for users.

3, the development stage of the database

1. Mesh Database

2. Hierarchical database

3. Relational database (current mainstream database)

4. Object Database

4. NoSQL Database

4. Oracle Database System User (administrator)

Scott account: Test Account, Practice account

Login mode: dos command line---->sqlplus---> Input account: Scott---> Enter password----> (login successful)

If the account is locked, it needs to be unlocked in the SYSDBA super account

SYSDBA account: Super account with the highest privileges, Oracle default system administrator. Lock or unlock other accounts under this account.

Login mode: Directly in the DOS command line input sqlplus/as SYSDBA (note the space) can

Locking: Alter user account name accounts lock;

Unlock: Alter user account name accounts unlock;

View current account: Show user;

5. Table name and column name specification and data type

1. Must start with a letter

2, can only contain letters, numbers, _#$

3. Cannot use reserved word Oracle

4. Cannot duplicate the name of other user-defined objects

Data type See table

Type	Meaning	Storage description	Note
CHAR	Fixed-length strings	Maximum length 2000bytes
VARCHAR2	Variable-length strings ,	Maximum length 4000bytes	Maximum length of index can be made 749
NCHAR	Fixed-length strings based on character set	Maximum length 2000bytes
NVARCHAR2	Variable-length strings based on character set	Maximum length 4000bytes
DATE	Date (Day - month - year)	Dd-mm-yy (HH-MI-SS), after rigorous testing, no problem of thousand worms
TIMESTAMP	Date (Day - month - year)	Dd-mm-yy (HH-MI-SS:FF3), after rigorous testing, no problem of thousand worms	Compared to date, timestamp has a decimal-seconds message
LONG	Super Long string	Maximum length 2G, enough to store tome
RAW	Fixed-length binary data	Maximum length 2000bytes	can store multimedia image sound, etc.
LONG RAW	Variable-length binary data	Maximum length 2G	can store multimedia image sound, etc.
Blob	Binary data	Maximum length 4G
Clob	Character data	Maximum length 4G
NCLOB	Character data based on the character set	Maximum length 4G
BFILE	Binary data stored outside the database	Maximum length 4G
ROWID	Unique line number recorded in the datasheet	10bytes	. ****.**** format,* is 0 or 1
Nrowid	Unique line number recorded in a Binary data table	Maximum length 4000bytes
Number (P,s)	Number Type	P is the integer digit, S is the decimal digit
DECIMAL (P,s)	Number Type	P is the integer digit, S is the decimal digit
INTEGER	Integer type	A small integer
FLOAT	Floating-point type	Number (38), double precision
REAL	Real type	Number (63), higher accuracy

6. Build table syntax and common SQL statements

(1), Build tables: Create TABLE table name (column name data type, column name data type,...);

Example: Create TABLE Stus (stu_id number), Stu_name varchar2 (20));

(2), view table structure: DESC indicates

Example: Desc stus;

(3), add new column: ALTER TABLE name Add (column name data type);

Example: Alter TABLE Stus Add (stu_class varchar2 (20));

(4), delete column: Alter TABLE indicates the DROP column name

Example: ALTER TABLE Stua drop column stu_class;

(5) Table change name: ALTER TABLE old table name rename to new table name

Example: ALTER TABLE Stus rename to Stu;

(6), column change name: ALTER TABLE name rename old column name to new column name;

Example: Alter TABLE Stu rename stu_id to Stu_no;

(7), modify the data type of the column: ALTER TABLE name modify (column name data type);

Example: Alter TABLE Stu Modify (stu_name number (20));

(8), add primary key:

After the table is established, specify the column as the primary key
The premise is that there is no null data && no duplicate data.
ALTER TABLE name add constraint (constraint, limit) pk_ table name _ specified column name primary key (the column specified by the primary key);

Example: ALTER TABLE Stu add constrain Pk_stu+stu_no primary key (STU_NO);

You can also add a primary key when you create a table

CREATE TABLE Stu (stu_id number () primary key not NULL);

(9), delete table

Method 1:drop Table Table name example: Drop table stu (remove the whole completely)

Method 2:truncate Table name (delete data from table);

(10), add data to the table: INSERT into table name (column 1, column 2:) VALUES (value 1, value 2 ....);

Example: INSERT into Stu (Stu_id,stu_name) VALUES (1, ' Zhang San ');

Oracle Database concepts and some basic SQL statements