Oracle Database BASICS (2): oracle Database Basics

Oracle Database BASICS (2): oracle Database Basics

1. Table Name naming rules: The table name must start with a letter and cannot exceed 30 characters. do not have any Oracle reserved words.
2. Data Type
Character Type:
Char: 2000 characters long, efficient
Varchar2: 4000 characters long
Clob: Large Object 4G
Number Type:
Number range:-10 ^ 38 ~ 10 ^ 38
Sal number (5)-99999 ~ 99999
Sal number (7,2)-99999.99 ~ 99999.99 7-digit valid number, 2 decimal places
Date type:
Date year month day hour minute second
Extended millisecond of timesatmp for date type
Image
Blob binary data stores images, audios, etc.
Generally, images and audios are not stored, but their paths are stored.

3. Table creation statement

Create table student (id number (2), name varchar2 (10), sex char (4), birthday date, sal number (7, 2), comm number (3 ), class_number number (2 ));

Add field:
Alter table tableName add (field name data type (length )))
Alter table student add (score number (3 ));

Modify the field length, data type, and field name (premise: Ensure that data cannot be stored in the modified table)
Alter table tableName modify (field name data type (length ));
Alter table student modify (comm number (4 ));
Alter table student modify (class_number varchar2 (4 ));

Delete field:
Alter table tableName drop column field name

Rename
Rename oldName to newName
Rename student to stu

Delete table
Drop table tableName

4. Data Operations
4.1 Add data:
Each field has data: insert into tableName (id, name, age, sex, score, sal, comm, class_number, birthday) values (2, 'and', 30, 'Female ', 21,567, February, '21-August 92 ');

Modify the time format: alter session set nls_date_format = 'yyyy-mm-dd ';

Some fields have data: insert into student (id, name, age, sex, birthday, sal) values (7, 'bob', 30, 'male ', '2014-4-24 ', 1983 );
Inset into student (id, name, age, sex, birthday, sal) values (8, 'ku ', 29, 'mal', '2017-3-12', null );

Find the person with blank salary: select * from student where sal is null;
Not select * from student where sal is not null;

4.2 modify data
Modify a single field: update student set id = 2 where name = 'James ';
Modify multiple fields: update student set score = 80, sal = 10000 where name = 'James ';
Cut female salaries by half: update student set sal = sal/2 where sex = 'female ';

4.3 Delete data
Delete from student; delete all data to roll back

Savepoint
Rollback to set a node to roll back the deleted operation

Truncate table student cannot be rolled back

Drop table student Delete table data and table Structure

Delete froom student where name = 'ku ';

4.4 query data

Table Structure: desc table student;
Query columns: select * from student
Query the specified column: select name from student;

Query multiple rows: select name, age from student;

Set timming on;



Query the gai salary and age:
Select sal, age from student where name = 'gai ';

Query the gai annual salary:
Select sal * 12 from student where name = 'gai ';
Alias for annual salary
Select sal * 12 "annual salary" from student where name = 'gai ';
Field concatenation:
Select name | age from student;
Select name | 'Age is '| age from student;
Query the year-end salary of all students:
Select name | 'payroll '| sal * 12 + comm * 12 from student;
(The bonus is blank, and the annual salary is blank) =>
Processing null value: nvl (comm, 0) * 12
Select name | 'payroll '| sal * 12 + nvl (comm, 0) * 12 from student;

Where clause
Query students whose salaries are less than 5000:
Select name from student where sal <= 5000;

Query the students whose birthdays are after:
Select name from student where birthday> '2017-1-1 ';

Like (% can represent 0-N arbitrary characters, _ can represent any single character)
Select sal, name from student where name like's % ';

In statement:
Query the names of all students in Class 3:
Select name from student where class_number in 3;