catalogue
1, Three paradigms of database
1. First paradigm (ensure that each column remains atomic)
2. Second normal form (ensure that every column in the table is related to the primary key)
1. Relationship with creating user
6, Relationship between tables
b. Create table according to ER drawing
1, Three paradigms of database
1. First paradigm (ensure that each column remains atomic)
The first paradigm is the most basic paradigm. If all field values in the database table are non decomposable atomic values, it means that the database table meets the first normal form.
2. Second normal form (ensure that every column in the table is related to the primary key)
The second paradigm goes further on the basis of the first paradigm. The second paradigm needs to ensure that every column in the database table is related to the primary key, not only a part of the primary key (mainly for the joint primary key). In other words, in a database table, only one kind of data can be saved in one table, and multiple kinds of data cannot be saved in the same database table.
3. The third paradigm (ensure that each column is directly related to the primary key column, not indirectly)
The third paradigm needs to ensure that each column of data in the data table is directly related to the primary key, not indirectly.
2, Tablespace
-
The data warehouse can be divided into several blocks, and each block is called a table space;
-
Each table space can correspond to a user;
-
When using the database initially, first create a user, and then assign a table space to this user;
-
If you do not create a table space for users, the default table space of Oracle will be used by default;
1. Relationship with creating user
-
Create a tablespace first;
-
Associate user06 with this tablespace;
-
Create a new table (at this time, the new table will be in the newly created table space);
2. Create a tablespace
--name: The name of this tablespace create tablespace name; --Properties for this tablespace datafile 'E:\TableSpaceFile\user_01\user01.dbf'size 10M; --Must first drop Then delete the disk file manually drop tablespace user06 tbs;
3, User
1. Create user
-
Only administrators can create users;
-
Enter in PL/SQL as administrator;
--create: establish identified: password create user user06 identified by tiger;
The user has been created successfully, but lacks a certain permission (lack of login permission), resulting in login failure;
Next, you need to give this user a login permission;
2. Grant authority
a. Login permission
--grant:grant grant connect to user06;
Then you can log in:
After logging in, it is found that there is no operation permission:
b. Operation authority
--Give permission to an operation table grant resource to user06; --Give an administrator permission grant dba to user06;
3. Recycling authority
--Reclaim all permissions --Recycle administrator privileges revoke dba from user06; --Recycle operation permission revoke resource from user06; --Reclaim login permissions revoke connect from user06;
4. Delete user
-
If this user has a table, it cannot be deleted by default;
-
Delete the table first, and then delete the user;
drop user user06;
4, Oracle data type
-
Character type: corresponding to string in Java;
-
Numeric value: corresponding to the number in Java (integer, decimal);
-
Date type: corresponds to the date class (date) in Java;
-
Rarely used (reason): the front end is string type, but saving in the database needs to be converted into date type;
-
Take the data from the database to the display page, convert the date type data into a string, and simply save the date in the database as a string;
-
If there are a lot of display data and less Date operation, we need to save the Date as a string in the database. When we need to calculate the Date, take the Date out of the database and convert it into Date type by using SimpleDateFormat for Date operation;
-
-
Big data types (the last two): generally used to store large files such as pictures, audio and video, which are basically abandoned (because there are special picture and video servers);
1. Characters
a.char
Fixed length string. For example, the length of the string saved in char(6) is 6. If the length of the string is less than 6, it will be filled with spaces by default.
b.varchar2
Variable length strings are not filled with spaces. For example, the length of the string saved in varchar(10) can be up to 10 bits, but it can be less than 10 bits.
2. Numerical value
a. Integer
int: integer
Long: long integer, less used;
number: can represent integer and decimal;
b. Decimals
number: can represent integer and decimal;
c. number demo
number(10,0) 10 represents the total length, and 0 represents the number of decimal places (0 is an integer)
number(10,2) 10 represents the total length (integer digits + decimal digits), and 2 represents the decimal length (2 is two decimal places, such as 2.22)
3. Date
-
date is only mm / DD / yy;
-
The format of date is: 'yyyy MM DD';
-
Timestamp: timesmap is the hour, minute and second of month, year and day;
-
The timestamp format is: 'yyyy MM DD hh24: Mi: Ss';
--date Only year, month and day, timestamp: timesmap Is the hour, minute and second of month, day and year
insert into students values(2023,'james','m',
to_date('2022-2-3','yyyy-mm-dd'),'Three classes in four years','School of economics and management');Demo
--Build table
create table students(id char(6),name varchar2(25),inschool date,score int);
--Insert data, error demonstration (the inserted date data is not of date type): insert into students values('202202','unclebird','2022-09-01',90);
insert into students values('202202','unclebird',to_date('2022-09-01','yyyy-mm-dd'),90);
--query
select * from students;
--Add a new column
alter table students add(address varchar2(50));
--Modify a field
alter table students modify (address varchar2(60));
--When doing the above operations, it is better to have no data in the table, otherwise it is easy to make mistakes
alter table students modify (id char(3));Precautions for operation:
Try to do some operations before writing data;
--modify id The length of the is 3 alter table students modify (id char(3));
result:
5, Data integrity
1. Entity integrity
a. Primary key constraint
-
When creating a table, each table must have a primary key, which plays the role of unique identification;
-
When inserting data, the primary key value cannot be repeated, and a value cannot be null;
-
When we add a primary key constraint to a column, the database will add a binary tree index to this column;
-
Rowid: it can be understood as the directory of a book, which aims to improve the efficiency of query;
-
Note: when frequently adding, deleting, modifying and querying data, Oracle will regenerate a new binary tree index;
For example, students' student numbers are different, so they can be used as primary keys;
Demo:
As long as the primary key constraint is added to the cid, the cid value cannot be repeated when inserting data, so as to ensure the uniqueness of the data;
Adding an existing value will report an error;
b. Unique constraint
A Unique constraint is added to name. When inserting data, name cannot be repeated, but a value can be null;
c. add constraints
Right click table, click Edit, and then add in checks:
2. Domain integrity
It is defined that the id length is 6, and it is not allowed to enter 10 bits, which is domain integrity;
3. Referential integrity
EG :
--towards doctor Insert data into insert into doctor values(2022,'Nicholas','doctor','internal medicine',35); --because doctor_id:'2222'Does not exist, so an error will be reported insert into patient values(101,'tom','m',2222,203);
6, Relationship between tables
The relationship between tables is divided into four types:
-
one-on-one
-
One to many
-
Many to one
-
Many to many
1. Analyze ER diagram
a. Many to many
-
A student can take multiple courses;
-
A course can be selected by multiple students;
-
In this case, the student schedule and curriculum are many to many;
Demo
After two tables are created:
-
At this time, there is no corresponding relationship between the two tables. You can create an intermediate table to reflect the corresponding relationship;
-
Create fields in the intermediate table: there are at least two fields corresponding to the primary key of each table. You can add other fields as needed;
-
Intermediate table: stuc;
-
Fields: stuid, cid, mark (score);
After the intermediate table is created:
-
At this time, the intermediate table does not have a primary key;
-
Suppose you want to set a primary key, there are two schemes:
-
Set the joint primary key to make stuid and cid become one primary key;
-
Add a field to play the role of primary key;
-
b. Many to one + one to many
-
A doctor treats multiple patients, and a patient corresponds to only one doctor;
-
This relationship is one to many, many to one;
-
From doctor to patient is one to many, from patient to doctor is many to one;
c. One on one
-
A private doctor corresponds to a patient;
-
A patient has only one private doctor;
2. How to create a table
a. Table design
When designing tables, if there is a many to many relationship between two tables, it is best to create an intermediate table and program the original many to many relationship into two one to many and many to one relationships;
b. Create table according to ER drawing
--Look at the data dictionary(Every company is different) Doctor table --constraint pk_doctor_id primary key(doctor_id) Create as primary key doctor_id --jobtitle varchar2(20) default 'doctor' The default value is"doctor" create table doctor(doctor_id int not null,doctor_name varchar2(20), jobtitle varchar2(20) default 'doctor',department varchar2(20),age int, constraint pk_doctor_id primary key(doctor_id)); --Patient table create table patient(patient_id int not null,patient_name varchar2(20), patient_sex varchar2(2),doctor_id int,room_id int, constraint pk_patient_id primary key(patient_id));
At present, there is no association relationship between the two tables, and the association relationship needs to be established through foreign keys;
c. Set foreign keys
The foreign key of the patient table corresponds to the primary key of the doctor table
Violation (Reference) of integrity constraints
When inserting data, the value of the foreign key will be used to find out whether this value exists in the table where the primary key is located. If it exists, the insertion is successful, otherwise an error will be reported;
--Insert data into patient table insert into patient values(101,'tom','m',null,203); --It is recommended that you first doctor Insert data into So delete it first patient Data delete from patient; --towards doctor Insert data into insert into doctor values(2022,'Nicholas','doctor','internal medicine',35); --because doctor_id:'2222'Does not exist, so an error will be reported insert into patient values(101,'tom','m',2222,203); --Add correct patient information insert into patient values(101,'lucy','m',2022,203); insert into patient values(102,'jack','m',2022,203);
How to identify table relationship types (several to several)
-
Look at the ER diagram;
-
Look at the data;
For example, Lucy and Tom belong to the same doctor, so they are one to many and many to one;
Those people are treated by doctors
--Which patients do doctors see select patient_name,doctor_name from doctor,patient where doctor.doctor_id=patient.doctor_id and doctor.doctor_id=2022;
