First part of the database foundation
First, basic concept
1. What is SQL?
SQL is a structured query language, which is a standard language for relational database. With SQL users to express their own needs.
2. What is client / server technology?
In the client / server, the host is called a server, and the network is usually a local area network (LAN) or a wide area network (WAN). The server is usually accessed by a personal computer or other server or personal computer instead of terminal. Each PC is Called a client, there is a function of accessing the network, allowing communication between clients and servers and thus called client / server technology.
Second, the development phase of data processing
1. Hand-working stage:
In the mid-1950s, computer memory, deposit capacity is small, exemplified only tape, no disk, hardware performance is very poor; there is no data management section in software, so mainly, this stage mainly uses manual data. deal with.
2. File system phase: Data and procedures have caused the birth of the file system.
The order is organized with a random file;
Batch and real-time processing coexist;
Physical and logical structures have a simple transformation;
Software provides access methods;
More data redundancy, data cannot be shared;
Document or data belongs to a user;
Data independence, data structure and complications are integrated;
There is no centralized management, and it is only possible to use global views only for local applications.
3, the database system phase:
The goal of database technology is to overcome the disadvantage of the file system, solve redundancy and data independence issues, and collect all files through a software system (DBMS) to achieve data sharing, the essence is through the layered, the first layer face The user, the second level to the overall, third level, by two-stage transformation, and the layer and the layer are completely separated by the user application file and the physical file, respectively, respectively, and physical independence.
Model: To completely separated data and programs, divide three models:
Inner model or physical model: physical storage for computer;
Conceptual model: overall system; (DBMS)
Outer model: User.
Mode: Exactly define the program of the data model with DDL;
Internal mode: define the mode of the model;
Concept mode: defining the pattern of concept model;
Outer mode: Define the mode of the external model (user-oriented).
Data Model: Model representing the entity (entity) and the real relationship; there are four forms:
E-R Model: The most intuitive description method, the entity set rectangular representation, the attribute is indicated by an ellipse, and the contact is expressed;
Hierarchy model: 1-m relationship can be represented, there is a unique root (and parent) node;
Net model: can represent M-M relationship;
Relationship Model: In the table, the relationship between the two entities is expressed and implemented by the table data instead of the pointer link.
Third, Oracle Introduction
Since 1970 CODD issued a relationship theory, the relational database has been developed rapidly, such as Informix, DB2, etc .;
Attempt is a well-developed relational database that has been developed by a single database to a variety of services, and the Oracle RDBMS is the core of the Oracle system, which is managed by the database and help users and database administrators. DBA maintenance, monitoring, and some utilities of the database. The core module of RDBMS is the SQL implementation module, which is the foundation of all utilities. Q: Oracle is corresponding to which mode in three modes (internal, concept, and external mode);
Which of the four data models (E-R, hierarchy, mesh, relational)?
Fourth, SQL Command Introduction
1, DDL data definition language.
Drop Table; Drop Table; DROP TABLE;
Create View; ALTER VIEW; DROP View;
Create Index; ALTER INDEX; Drop Index;
2, DML data manipulation language.
Insert inserts new data lines to the table;
Update updates existing data in the table;
DELETE removes several lines of data from the table;
3, DQL data query language.
SELECT returns data from one or more tables;
4, DCL data control language.
Alter Password changes user password;
GRANT gives the user privilege;
Revoke reclaims user privilege;
Create synonym creates synonyms
5. Data management command.
Start Audit
Stop Audit
6, transactional control command.
Commits save the transaction to the database;
ROLLBACK revokes a database transaction;
SavePoint creates the save point of the transaction when necessary;
Set Transaction Settings Transactions.
Part 2 establishing a database
First, basic concept
1. Database and database objects?
The database is the aggregate of the data;
Database objects can be any object defined in the database. For storage and reference data, the table is the simplest storage form of the relational database, and there are views, indexes, etc.
2, what is the pattern?
The collection of the pattern database object and has a specific username associated with it, and a pattern can include a table and a number of objects without quantity.
3, data structure
Pixaban string char beam long string varchar2 digital value Number
Digital point value decimal integer Integer floating point decimal Float
Date and time Date text string string NULL data.
Second, the table and its basic operation
In the relational database, the table is the primary storage object of the data, the table contains rows and columns, stores entities and their contacts, and the physical space occupied in the database can be permanent, or temporary.
1. The basic syntax of the creation table is:
Create Table Table_Name
(Field1 DataType [not null]
Field2 DataType [not null]
Field3 DataType [not null])
STORAGE
(Initial x initial space
Next X Additional Space);
example:
CREATE TABLE Employee_TBL
(EMP_ID Char (9) Not null,
EMP_NAME VARCHAR (40) Not null,
EMP_ZIP NUMBER (6) NULL,
EMP_PHONE NUMBER (10) NULL)
STORAGE
(Initial 3K
NEXT 2K);
2. The syntax for modifying the table is:
Alter Table Table_name
[MODIFY] [COLUMN COLUMN_NAME] [DataType NOT NULL | NULL)] [Restrict | Cascade] [DROP] [constraint constraint_name]
[Add] [colorn] column definition;
example:
ALTER TABLE Employee_TBL
MODIFY (EMP_PHONE NUMBER (11) NULL);
3. Create a new table from existing tables
Create Table new_table_name as
SELECT [* | Column1 colum2]
From table_name
[WHERE CONDITION]
example:
Create Table Phone_TBL AS
SELECT (EMP_ID, EMP_PHONE)
From Employee_TBL;
4. The syntax of the delete table is:
DROP TABLE TABLE_NAME
example:
DROP TABLE Phone_TBL;
5, Orcale]
DESCRIBE shows the structure of the table and the summary of all list columns;
Third, integrity constraints.
In the relational database, integrity constraints are used to ensure that the accuracy and consistency of the data are implemented by reference integrity.
1. Primary key constraints: Primary Key identifies a column or more columns in the table to keep a line of data unique, used to query the reference, table;
example:
ALTER TABLE Employee_TBL
MODIFY (EMP_ID Char (9) Not Null Primary Key;
2, uniqueness constraint: unique corresponds to the same value;
example:
ALTER TABLE Employee_TBL
Modify (EMP_PHONE NUMBER (11) null unique);
3, foreign key constraints:
Foreign Key Key_Name_fk (key_name)
Feferences TBL_NAME (Key_Name)
example:
Create Table Employee_Pay_TBL
(EMP_ID Char (9) Not null,
Position varchar2 (15) Not null,
Pay_Rate Number (4, 2) Not null)
Foreign Key EMP_ID_FK (EMP_ID)
References Employee_TBL (EMP_ID));
Note: To insert a value in the sub-table, you must first exist the corresponding value in the parent table;
4, NOT NULL constraint: no null value is allowed;
Note: When changing the table properties, NOT NULL -> NULL,
Null-> NOT NULL, you want to see if there is a NUL value;
5. Use the Check Constraint: Use to check the data validity;
example:
CREATE TABLE CUSTOMER_TBL
(Cust_id Char (9) Not Null,
Cust_name varchar2 Not Null,
Cust_city varchar2 not null,
Cust_zip Number (5) NULL,
Cust_Phone Number (11)),
PRIMARY Key (Cust_ID),
ConsTRAINT CHK_EMP_ZIP CHECK (EMP_ZIP = '430079);
Fourth, standardization process.
1, what is standardized?
Standardization is the process of reducing data redundancy in the database. In addition to the database, the name, object name, and forms need normalization. 2, set database standardization
In the process of database specification, three common paradigms are:
The first paradigm: divide the basic data according to the primary key to the logical unit called the table;
Second paradigm: Use only data that is only partially dependent on the primary key to other tables;
Third paradigm: Remove the data of the primary key from the table;
example:
Company_DATABASE
(EMP_ID LAST_NAME FIRST_NAME MIDDLE_NAME
Address City Zip Phone
Position position_desc Date_Hire Pay_Rate
DATE_LAST_RAISE CUST_ID CUST_NAME CUST_ADDRESS
CUST_CITY CUST_ZIP CUST_FAX Qty
ORD_NUM ORD_DATE PROD_ID PROD_DESC
COST)
According to the requirements of the first paradigm, it can be divided into three tables:
Employee_TBL
(EMP_ID LAST_NAME FIRST_NAME MIDDLE_NAME
Address City Zip Phone
Position date_hire pay_rate date_last_raise)
Customer_TBL
(CUST_ID CUST_NAME CUST_ADDRESS CUST_CITY
CUST_ZIP CUST_PHONE CUST_FAX Qty
ORD_NUM ORD_DATE)
Products_TBL
(PROD_ID PROD_DESC COST)
According to the second paradigm, EMPLOYEE_TBL can be divided into two tables:
Employee_TBL
(EMP_ID LAST_NAME FIRST_NAME MIDDLE_NAME
Address City Zip Phone)
Employee_pay_tbl
(EMP_ID POSITION POSION_DESC DATE_HIRE
Pay_rate Date_Last_Raise)
According to the third paradigm Employee_Pay_TBL can be divided into two tables:
Employee_pay_tbl
(EMP_ID POSITION DATE_HISRE PAY_RATE
Date_last_raise)
Position_TBL
(position position_desc)
3, standard benefits and drawbacks
Large overall database organization,
Reduce data redundancy,
Data consistency in the database,
More flexible database design,
Better database security processing;
Its main drawback is to reduce the operability of the database and thus make appropriate non-standardization.
Part III. Manipulate data.
I. Summary of data manipulation.
Data Manipulation Language (DML) is part of SQL that allows database users to change data in relational databases.
There are three basic DML commands in SQL.
1, INSERT (insert)
2, Update (update)
3. Delete (delete)
Second, fill the data in the table.
1. Insert new data in the table
INSERT INTO TABLE_NAME
VALUES ('VALUES1', 'VALUES2')
example:
INSERT INTO Employee_TBL
VALUES ('1', 'Wu Jianjun', '430079', '027111')
2. Insert the data in the finite column in the table.
INSERT INTO TABLE_NAME ('Column1', 'Column2')
VALUES ('VALUES1', 'VALUES2')
example:
INSERT INTO EMPLOYEE_TBL ('EMP_ID', 'EMP_NAME')
VALUES ('2', 'Da Liu');
3, insert the data in another table
INSERT INTO TABLE_NAMESELECT [* | ('colorn1', 'column2')]
From table_name [where conditions (s)];
example:
INSERT INTO CUSTOMER_TBL
SELECT ('EMP_ID', 'EMP_NAME', 'EMP_ZIP', 'EMP_PHONE')
From Employee_TBL
WHERE EMP_ZIP = '430079';
4, insert a null value.
The basic syntax is: INSERT INTO TABLE_NAME ('Column1', NULL, 'Column3')
Thinking:
When inserted, do you have a lot of values in the list?
Is NULL and 'NULL'?
,,,,?
Third, update the existing data.
1, only update the data of a single column
Update Table_name
SET column_name = 'Value'
[WHERE CONDition];
2, update multiple columns in one or more lines of records
Update Table_name
Set column1 = 'value',
[Column2 = 'VLAUE'];
4. Remove data from the table
Remove a separate record or selected multiple records from the table
Delete from table_name
WHERE [CONDition];
The fourth part. Database transaction management.
First, transaction control command
Transaction Control is the management capability of different transactions that may happen in relational databases.
Command for transaction control:
1, commit command
The commit command is a transaction command that saves the change in the database. The transaction caused by transactions. Thecommit command saves all the transactions since the last commit or rollback command.
The syntax is: commit [word];
2, rollback command
The commit command is used to undo transaction control commands that have not been saved to the database. ROLLBACK commands can only be used to undo the last commit command or the ROLLBACK command execution after execution.
The syntax is: rollback [word];
3. SavePoint command
SavePoint Command Save Point is in the transaction, you can make a point where the transaction is rolled back, do not need to roll back to the entire transaction
The syntax is: SavePoint Point_name;
Rollback to point_name;
Second, transaction control and database performance
The bad transaction control will damage the performance of the database and even give the database harm. Database repeated low performance may be because transaction control is lacking in a large number of insertions, updates, or delete operations; when performing a large number of transactions, frequent use of the commit and rollback commands should be used to release the database space.
Part 5 Data Query Language DQL
I. Basic SELECT statement
Select [all | * | [distinct] column1, colorn2 ...]
From table1 [, Table2 ...]
[WHERE CONDITONS]
[GROUP BY Column1, Column2, ...
[Having Conditions]
[Order by Column1, Colunm2 ...]
Second, use the operator to classify data
1, compare operators (=, <>, <,>)
example:
SELECT *
From Employee_TBL
WHERE EMP_ZIP = '430079';
2, Logic Operation (IS NULL, BETWEEN, IN, LIKE, EXISTS, UNIQUE, ALL, ANY)
Is NULL: It is used to compare values with NULL values;
BetWeen: Used to take value in a range;
IN: Used to return the result in a series of specified values; LIKE: Take a value similar to the specified value (% represents any character, and _ represents one);
EXISTS: Does the query have a line that meets a certain condition;
UNIQUE: Tests if there are duplicate data;
All: Index index of each value in the concentration;
Any: Indicates any of the values of the concentration;
example:
SELECT *
From Employee_TBL
WHERE EMP_SALARY> ALL
(SELECT EMP_SALARY
From EMP_SALARY
WHERE EMP_CITY = 'Wuhan');
3, connect (AND, OR)
example:
SELECT *
From Employee_TBL
WHERE EMP_ZIP = '430079'
OR EMP_ZIP = '430074';
4, combination of mathematics operators
example:
SELECT EMP_SALARY * 12
From Employee_TBL;
Third, statistical query results data
1. COUNT: Used for statistics;
example:
SELECT Count (EMP_ID)
From Employee_TBL;
2, SUM: Used to return the sum of a column value in a set of data lines;
example:
SELECT SUM (EMP_SALARY)
From Employee_TBL;
3, AVG: Used to determine the average of a set of data;
example:
SELECT AVG (EMP_SALARY)
From Employee_TBL;
4, Max: Used to return a maximum of a set of data;
example:
SELECT MAX (EMP_SALARY)
From Employee_TBL;
5, min: Used to return a minimum of a set of data;
example:
SELECT MIN (EMP_SALARY)
From Employee_TBL;
Fourth, sorting and grouping
1, group: group by
example:
Select Employee_name, EMP_CITY
From Employee_TBL
GROUP BY EMP_CITY, EMPLOYEE_NAME;
Note: If the column name is referenced in the SELECT clause, the column name will also be used in Group By;
You cannot sort by using an integer column name like ORDER BY, unless you use UNION, and the column names are different.
2, sort: Order by
example:
SELECT EMP_CITY, COUNT (*)
From Employee_TBL
Group by EMP_CITY
ORDER BY 2, 1;
3, HAVING clause: following the group by, set the conditions before the ORDER BY;
example:
SELECT EMP_CITY, AVG (Pay_Rate), AVG (EMP_SALARY)
From EMP_PAY_TBL
WHERE City <> 'Wuhan'
Group by EMP_CITY
Having avg (emp_salaric)> 2000;
Sixth Part of exercises
1 Can the SQL command can be divided into which type of class, what specific commands have each category?
2 Establish the following five tables and check them with the DESC command.
Table EMPLOYEE_TBL structure
EMP_ID VARCHAR2 (9) Not null, Last_name varcha2 (15) Not null,
First_name varchar2 (15) Not null,
Middle_name varcha2 (15),
Address varchar2 (30) Not null,
City varchar2 (15) Not null,
State Char (2) Not Null,
Zip Number (5) Not null,
Phone char (10),
Pager char (10),
ConsTRAINT EMP_PK PRIMARY Key (EMP_ID)
Table EMPLOYEE_PAY_TBL structure
EMP_ID VARCHAR2 (9) Not Null,
Position varchar2 (15) Not null,
Date_Hire Date,
Pay_Rate Number (4, 2),
Date_last_raise Date,
Salary Number (8, 2),
Bonus Number (6, 2),
ConsTRAINT EMP_FK Foreign Key (EMP_ID) References Employee_TBL (EMP_ID)
Table Customer_TBL structure
Cust_id varchar2 (10) Not Null Primary Key,
Cust_name varchar2 (30) Not null,
Cust_address varchar (20) Not null,
Cust_city varchar2 (15) Not null,
Cust_State Char (2) Not NULL,
Cust_zip Number (5) Not null,
Cust_Phone Number (10),
Cust_fax number (10)
Table ORDERS_TBL Structure
ORD_NUM VARCHAR2 (10) Not Null Primary Key,
Cust_id varchar2 (10) Not null,
PROD_ID VARCHAR2 (10) Not null,
Qty Number (6) Not null,
ORD_DATE DATE
Table products_tbl structure
PROD_ID VARCHAR2 (10) Not Null Primary Key,
PROD_DESC VARCHAR2 (40) Not null,
Cost Number (6,2) Not null
