Oracle initial (day 1)

Time:2021-5-3

1、 Database classification

1. Small database: access, FOXBASE

2. Medium sized databases: Informix, SQL server, MySQL

3. Large databases: Sybase, DB2, Oracle

2notes

  1./*

November 6, 2020

This is a multiline comment in Oracle

  */

2. — this is a single line comment for Oracle

 

3、 Common commands

1. Data type

(1) Character type: VARCHAR2 (20) has variable length and can hold up to 4000 characters.

(2) number type: numberRange-From the 38th power of 10 to the 38th power of 10, it can be an integer or a decimal.

(3) date type: DateIncluding month, year, day, hour, minute and secondThe default format of Oracle is January January 1999.

2. Using SQL statements to create tables

For example: student table

      create table student(

sno number(6),  — Student number

sname varchar2(20),  — full name

birthday date,  — date of birth

sex varchar2(3),  — Gender

tel varchar2(11)  — Telephone

     )

 

Class table

    create table class(

CNO number (6), — course number

CNAME VARCHAR2 (20), — course name

Credit VARCHAR2 (30), — course credits

Teacher VARCHAR2 (20)

       )

3. Modify the table

(1) — add a field
sql>alter table student add (classid number(2));

(2) — delete field

sql>alter table student drop(sname);

(3)  — Modify the type, name or length of the field (no data)

sql>alter table class MODIFY (cno varchar2(46));

sql>alter table class modify(credit number(11) ,teacher     number(3));

(4) — rename and modify the name of the table

sql>rename student to stu;

 

4. – delete table

drop table stu;

drop table class;

 

4、 Constraints

stayIn Oracle, constraints include not null, unique, primary key, foreign Ke and check.

(1) , not null (not empty)
If theWhen inserting data, data must be provided for the column.
(2) , unique
When a unique constraint is defined, the column value cannot be repeated, but it can benull。
(3) , primary key (primary key)
It is used to uniquely identify the data of a table row. After the primary key constraint is defined, the column cannot be duplicated and cannot be emptynull。
It should be noted that a table can only have one primary key at most, but it can have multiple primary keysUnqiue constraint.
(4) , foreign key
Used to define the relationship between the master and slave tables. The foreign key constraint must be defined on the slave table, while the master table must have a primary key constraint or a secondary key constraintUnique constraint: after defining foreign key constraint, foreign key column data must exist or be null in primary key column of main table.
(5) , check
Used to enforce the condition that row data must satisfy, assuming thatCheck constraint is defined on Sal column, and the value of SAL column is required to be between 1000-2000. If it is not between 1000-2000, an error will be prompted.

(6)   The practical operation is as follows:

1) Method 1

create table student(

  sno number(7) primary key,

  sname varchar2(20),

Sex VARCHAR2 (18) default ‘female’,

  idcard varchar(30) constraint uq_card unique,

  age number(3)

);

2) Method 2

create table student(

  sno number(7),

  sname varchar2(20) not null,

Sex VARCHAR2 (18) default ‘female’,

  idcard varchar2(18),

  age number(3),

  constraint pk_sno primary key (sno),

  constraint uq_card unique (idcard),

  constraint ck_age check(age between 18 and 60)

);

3) Method 3

alter table student add(tel number(11) constraint ck_tel check(tel like ‘1%’));

 

Recommended Today

Looking for frustration 1.0

I believe you have a basic understanding of trust in yesterday’s article. Today we will give a complete introduction to trust. Why choose rust It’s a language that gives everyone the ability to build reliable and efficient software. You can’t write unsafe code here (unsafe block is not in the scope of discussion). Most of […]