🍅 Programmer Xiao Wang's blog: Programmer Xiao Wang's blog
🍅 Welcome to like 👍 Collection ⭐ Leaving a message. 📝
🍅 If you have an editor's error and contact the author, if you have a good article, welcome to share it with me, I will extract the essence and discard the dross.
🍅 Learning route of java self-study: Learning route of java self-study
A view is a statement that encapsulates a complex query.
1. Syntax 1.: CREATE VIEW subview name
Example: create a view that includes all employee information of 20 departments
--Example: create a view that includes 20 All employee information of the Department create view empdept20 as select * from emp where deptno=20;
After the view is created, you can use the view to query, and the queried employees are all employees of 20 departments
--Query view select * from empdept20;
2. Syntax 2: CREATE OR replace VIEW view name AS subquery
If the view already exists, we can use syntax 2 to create the view, so that the existing view will be overwritten.
--Replace view create or replace view empde20 as select * from emp where deptno=20; select * from empde20;
View is to define the name of SQL query statement, which is convenient for reuse and simplifies SQL
Security, shield the information of tables and fields for developers
The view is equivalent to a query statement and does not store actual data. The view basically does not occupy hard disk space, and the efficiency has not been improved
Delete view: drop view view name;
1. What is an index?
Understanding: similar to the table of contents in front of the book, it is used to improve query efficiency. Indexes are data objects used to speed up data access. Reasonable use of index can greatly reduce the number of i/o, so as to improve the performance of data access.
Note: the data (index column data and rowid) is stored in the index, and is in ascending order according to the index column
CREATE [UNIQUE] | [BITMAP] INDEX index_name --unique Represents a unique index ON table_name([column1 [ASC|DESC],column2 --bitmap，create bitmap index [ASC|DESC],...] | [express]) [TABLESPACE tablespace_name] [PCTFREE n1] --Specifies that the index has free space in the data block [STORAGE (INITIAL n2)] [NOLOGGING] --Indicates that changes to the table are allowed when creating and rebuilding indexes DML Operation, which should not be used by default [NOLINE] [NOSORT]; --It means that the index is not sorted when it is created. It is not applicable by default. If the data is already arranged according to the index order, it can be used
Use: it does not need to be used manually. By default, when the index field is used as the condition for query, the database will automatically use the index to find data
1. The more indexes you create, the better,Index space(Have data);When adding, deleting or modifying data,At the same time, the data in the index needs to be maintained 2. Indexes are usually created on columns that are frequently queried 3. Columns with primary keys and unique constraints,The database is automatically indexed. 4. Delete index: drop index Index name;
2. Single column index
Single column index is an index based on a single column, such as:
CREATE index Index name on Table name(Listing)
Case: index the name of the person table
create index pname_index on person(name);
3. Composite index
A composite index is an index based on two or more columns. There can be multiple indexes on the same table, but
The combination of columns must be different, for example:
Create index emp_idx1 on emp(ename,job); Create index emp_idx1 on emp(job,ename);
Case: create an index of name and gender for the person table
create index pname_gender_index on person(name,gender)
4. Use principle of index:
It makes sense to build an index on a large table
Index the fields after the where clause or on the join condition
It is not recommended to establish an index when the data in the table is modified frequently
5. Index principle
If there is no index, you need to search all records when searching for a record (for example, find name='whj '), because there is no guarantee that there is only one whj, you must search all records
If the index is established on name, oracle will search the whole table once, and arrange the name value of each record in ascending order. Then build index entries (name and rowid) and store them in the index segment. When the query name is whj, you can directly find the corresponding place
The index may not be used after it is created. After oracle automatically counts the information of the table, it decides whether to use the index. When there is little data in the table, the scanning speed of the whole table is very fast, so it is not necessary to use the index
1. Primary key constraint: primary key
Function: used to uniquely identify a row of data in the table, such as student number and job number
Features: unique, non empty
Syntax: field name data type primary key
Note: during development, each table usually has a primary key constraint
2. Unique constraint: unique
Function: the value identifying this field cannot be repeated. Such as ID number, mobile phone number.
Features: unique and can be empty
Syntax: field name data type unique
3. Non NULL constraint: not null
Function: the field must have content and cannot be null; Such as student name
Features: non empty, repeatable
Syntax: field name data type not null
4. Check constraints: check (expression)
Role: limit the content of this field according to user-defined rules. For example, the gender can only be male or female
Syntax: field name data type check (expression for checking constraints)
Common usage scenarios:
Gender can only be Male or female: check( sex in ('male','female')) or check(sex = 'male' or sex='female') Mobile phone number must be 11 digits: phone char(11) check(phone like '_____')or phone char(11) check(length(pone)=11) Mailbox must have@: check (email like '%@%')
5. Foreign key constraint
Function: the value identifying this field cannot be entered arbitrarily. It must be the value existing in the primary key or unique key of another table. (identify the relationship between the current table [slave table] and another table [master table])
Features: repeatable and null
Syntax: field name data type references main table name (field name of primary key / unique key)
6. Default constraint
Function: identify the content assigned by default when no content is filled in this field
Syntax: field name data type DefaultValue constraint
Note: the type of the given default value must be consistent with the data type of the field
7. Joint constraint
Union constraint: when a primary key or unique constraint cannot be identified by any field in the table, the combination of multiple keys is used for constraint
Common union constraints: Union primary key, union unique constraint
1. Federated primary key: primary key(Field name 1 , Field name 2) 2. Joint unique constraint: unique(Field name 1 , Field name 2)
(1) Create class table
--Student list create table t_class( cls_id number(10) primary key, cls_name varchar2(20) not null );
(2) Create student table
Student ID number - primary key, name, email box (must have @), date of birth (default date), ID number - unique, class number.
--Student list --Student number-Primary key, name, email(Must have@),date of birth(Default current date),ID number-Number and unique class create table t_student( stu_id number(5) primary key, stu_name varchar2(20) not null, email varchar2(50) not null check(email like '%@%'), birthday date default sysdate, card_id char(18) unique, cls_id number(5) references t_class(cls_id) );
(3) Query all tables under the user
Note: query all tables in the Library: select * from tabs;
--Query all tables in the database select * from tabs;
(4) Joint constraint case
-- Student list create table t_student( s_id number(5) primary key, s_name varchar2(30) not null ); -- Class Schedule Card create table t_course( c_id number(5) primary key, c_name varchar2(30) not null ); -- Description: a student can choose multiple courses and define the relationship table create table t_sc( s_id number(5) references t_student(s_id), c_id number(5) references t_course(c_id), primary key(s_id,c_id) ); -- insert data create sequence stud_seq insert into t_student values(stud_seq.nextval,'Big baby') select * from t_student create sequence cour_seq start with 100 insert into t_course values(cour_seq.nextval,'oracle') select * from t_course -- Insert data into relational tables insert into t_sc values(2,101); -- Query students' course selection select s.s_id,s_name,c.c_name from t_student s join t_sc sc on s.s_id=sc.s_id join t_course c on sc.c_id=c.c_id where s.s_name='Jiaming'
1. What is a transaction?
Concept: the smallest execution unit in the database, which is usually composed of one or more sql. Only when all sql is executed successfully can the transaction succeed, then the transaction is committed; As long as one sql fails to execute, the transaction fails and the transaction is rolled back
The size of the transaction depends on the business
(1) In actual development, a business corresponds to different numbers of transactions sql，These are many sql The composition of is called a transaction (To realize business needs, control transactions) (2) Case: Transfer business: money from one account is transferred to another account update account set balance=balance-money where id = 894893 update account set balance=balance+money where id = 242546 Note: there are at least 2 transfer businesses sql，Constitute a transaction(2 strip sql) Account opening: to account Add information to table insert into ..... Note: there is one business for opening an account sql，Constitute a transaction(1 strip sql)
(1) Start: from day one sql Start execution, transaction start (2) end: a. DML sentence(insert/delete/update)The end boundary needs to be clearly specified: I. Encountered: commit，Commit transaction, transaction succeeded II.encounter: rollback，Rollback transaction, transaction failed b. DDL Statements: statements that create tables(create table/create sequence),Auto commit transaction
2. Rollback segment:
Database server DBServer will open up a small memory space (called rollback segment) for each connected client to temporarily store the execution results of sql statements. If all sql statements are executed successfully, commit and write the rolled back data into the DB; If an sql fails to execute, the data in the rollback segment will be cleared when the transaction is rolled back, and the result will be restored to that before the transaction.
(1) The bottom layer of transaction adopts the mechanism of lock to ensure the security of data
(2) The database assigns a lock to each data. If a client performs the insert/delete/update operation, it will obtain the lock mark of the current operation data. The lock mark will not be released until it is commit ted or rollback, and other clients can operate the current data
Note: the select query operation does not need to consider transactions.
4. Transaction characteristics: (ACID)
(1)Atomicity of transactions(Atomic): Multiple in a transaction sql The statement is a whole, Or all succeed,Or lose it all Defeat. (2)Consistency of transactions(Consistency): (Rationality of data),Before and after transaction execution(Whether failure or success),most The final data is reasonable. (3) Isolated type of transaction(Isolation): When multiple transactions are concurrent, the transactions are independent and do not affect each other (4) Transaction persistence(Durability): End of transaction(Success or failure),Data modification to the database is permanent.
5, Database paradigm
1. What is paradigm?
In short, the paradigm is some norms that should be followed when designing the database, which can play a guiding role in creating tables. Following the normal form when designing database tables can reduce redundant data and make the management of data more scientific.
2. First paradigm
The key point of the first paradigm is that fields cannot be subdivided
Case: for the hobby attribute, the actual stored data may be:
|1||Wang Hengjie||Play basketball, sing songs and play games|
|2||Yang Fujun||Singing, watching movies, dancing|
For example, now there is a need to modify all film hobbies to film and television, and then there is a need to delete all dance hobbies. These operations are bound to be converted into string splicing and query operations, which is very inconvenient.
The solution is to split the attributes again and separate them into another table
3. Second paradigm
The second paradigm means that attributes cannot be partially dependent on primary attributes
Student number Student name Course number Course name achievement s001 Zhang San c001 java 90 s001 Zhang San c002 oracle 80 s002 Li Si c002 oracle 60 s003 Li Si c001 java 50 s003 Wang Wu c002 oracle 100
If a grade sheet is designed to represent the course selection of students, the attribute that can play a unique identification in the sheet is called the main attribute. The secondary number in this sheet cannot be used as the main attribute alone, and the course number cannot be used as the main attribute alone. Therefore, there are two main attributes: student number and course number.
The grade attribute depends on these two main attributes at the same time, while the student name and course name can only partially depend on the main attributes. Calling the attributes of student name and course name as partial dependency should be separated from this table.
There are some problems with the existing table structure. For example, adding new students will have problems. If the new students have no elective courses, some columns must be left blank; There will also be problems in modifying students. Zhang San is renamed Zhang Si, and multiple records need to be modified
These problems are called adding exceptions, modifying exceptions and deleting exceptions respectively. If these partially dependent attributes are separated, these problems can be avoided, such as:
Student number Student name s001 Zhang San s002 Li Si s003 Wang Wu Course number Course name c001 java c002 oracle Student number Course number achievement s001 c001 90 s001 c002 80 s002 c002 60 s003 c001 50 s003 c002 100
Firstly, some redundant information of students and courses is removed. Secondly, adding, modifying and deleting students will not cause exceptions, which is the same for courses.
4. Third paradigm
The third paradigm means that attributes cannot be indirectly dependent on primary attributes
empno ename deptno dname 7369 Smith 10 Finance Department 7499 Scott 10 Finance Department 7869 King 10 Finance Department
If the above table structure exists, dname directly depends on the deptno attribute and deptno directly depends on the main attribute of empno. In this case, dname indirectly depends on empno.
The problem caused by this dependency is where to add a new department. Similarly, there are modification exceptions and deletion exceptions. The solution is to separate the attribute (dname) indirectly dependent on the primary attribute (empno) from this table. As follows:
empno ename deptno 7369 Smith 10 7499 Scott 10 7869 King 10 deptno dname 10 Finance Department