[Database]Basic introduction and operation of database
This article is mainly used for recording, introduction and use of database technology.[Database] (1) -- database foundation
This article is mainly used to introduce the relevant knowledge of the database.
The development of the information society is very rapid, which is inseparable from the role of database software. For modern information systems, database software is an indispensable system component.
database?
What is a database? refer to Database entry - wiki
Database classification
- Relational Database: DBMS (SQL)
- Relational data is inherently tabular, so it is stored in the rows and columns of the data table. Data tables can be stored cooperatively in association with each other, and data can be easily extracted.
- Non-relational database: NoSQL
- Non-relational data is not suitable to be stored in the rows and columns of the data table, but is grouped together in large chunks. Non-relational data is usually stored in datasets, like documents, key-value pairs, or graph structures. Your data and its characteristics are the primary influencers in choosing how to store and extract your data.
The main difference between relational and non-relational databases is the way data is stored.
Relational Database
The most typical data structure of a relational database is a table, a data organization composed of two-dimensional tables and the connections between them.
advantage:
- Easy to maintain: all use the table structure and the format is consistent;
- Easy to use: The SQL language is universal and can be used for complex queries;
- Complex operations: SQL is supported and can be used for very complex queries between one table and multiple tables.
shortcoming:
- The read and write performance is relatively poor, especially the efficient read and write of massive data;
- Fixed table structure, less flexibility;
- High concurrent read and write requirements, for traditional relational databases, hard disk I/O is a big bottleneck.
non-relational database
A non-relational database is not strictly a database, but a collection of data structured storage methods, which can be documents or key-value pairs.
advantage:
- Flexible format: The format of stored data can be key,value form, document form, image form, etc., document form, image form, etc., flexible use and wide application scenarios, while relational databases only support basic types.
- Fast speed: nosql can use hard disk or random access memory as a carrier, while relational database can only use hard disk;
- high scalability;
- Low cost: nosql database deployment is simple, and it is basically open source software.
shortcoming:
- No sql support is provided, and the cost of learning and use is high;
- no transaction;
- The data structure is relatively complex, and the complex query is slightly less.
Basic Concepts of Structured Database
database
table
schema
column
datatype
row
primary key
Examples of database applications
- Account Information Management in the Portal
- Customer Information Records in E-commerce
- Enterprise Management Information System
- School registration management, file management
[Database] (2) -- SQL structured language
Posted on 2022-09-08 Updated on 2022-09-14 Category in computer , basic technology , software engineering Number of characters: 733 Reading time ≈ 1 minute
This article is mainly used to introduce the relevant knowledge of SQL.
Introduction to SQL
SQL (Structured Query Language: Structured Query Language) is used to manage relational database management systems (RDBMS). The scope of SQL includes data insertion, query, update and delete, database schema creation and modification, and data access control.
What is SQL?
- SQL stands for Structured Query Language, the full name is Structured Query Language.
- SQL lets you access and manipulate databases, including inserting, querying, updating, and deleting data.
- SQL became an ANSI (American National Standards Institute) standard in 1986 and an International Organization for Standardization (ISO) standard in 1987.
What can SQL do?
- SQL executes queries against the database
- SQL to retrieve data from database
- SQL to insert new record in database
- SQL to update data in database
- SQL to delete records from database
- SQL to create a new database
- SQL to create new table in database
- SQL to create stored procedures in a database
- SQL to create views in the database
- SQL can set permissions on tables, stored procedures and views
SQL is a standard - but...
Although SQL is an ANSI (American National Standards Institute) standard computer language, there are still many different versions of the SQL language.
However, to be compatible with the ANSI standard, they must collectively support some major commands (such as SELECT, UPDATE, DELETE, INSERT, WHERE, etc.) in a similar manner.
Note: In addition to the SQL standard, most SQL database programs have their own proprietary extensions!
[Database] (3) -- mysql database operation application
Instructions
Use navicat to use sql for mysql, which runs in the application container of the local docker environment.
For syntax and keywords, see "SQL Must Know" or online tutorials. Here, only the actual operation results of SQL are tested.
Create a database (CREATE)
Basic way
Design database
database view model
Create database SQL
-- If the database mysql_db_test Delete if it exists mysql_db_test database operations DROP DATABASE IF EXISTS mysql_db_test; -- If the database mysql_db_test Create if it doesn't exist mysql_db_test database operations CREATE DATABASE IF NOT EXISTS mysql_db_test; -- Use the created database mysql_db_test USE mysql_db_test;
Create database table SQL
-- Sams Teach Yourself SQL in 10 Minutes, 5th Edition -- http://forta.com/books/0135182794/ -- Example table creation scripts for MySQL & MariaDB -- Modify the script appropriately -- ---------------------- -- Create Customers table -- Create a table about customers -- ---------------------- CREATE TABLE Customers ( cust_id char(10) NOT NULL , cust_name char(50) NOT NULL , cust_address char(50) NULL , cust_city char(50) NULL , cust_state char(5) NULL , cust_zip char(10) NULL , cust_country char(50) NULL , cust_contact char(50) NULL , cust_email char(255) NULL ); -- ----------------------- -- Create OrderItems table -- Create an order item detail sheet -- ----------------------- CREATE TABLE OrderItems ( order_num int NOT NULL , order_item int NOT NULL , prod_id char(10) NOT NULL , quantity int NOT NULL , item_price decimal(8,2) NOT NULL ); -- ------------------- -- Create Orders table -- Create a purchase order -- ------------------- CREATE TABLE Orders ( order_num int NOT NULL , order_date datetime NOT NULL , cust_id char(10) NOT NULL ); -- --------------------- -- Create Products table -- Create product listings -- --------------------- CREATE TABLE Products ( prod_id char(10) NOT NULL , vend_id char(10) NOT NULL , prod_name char(255) NOT NULL , prod_price decimal(8,2) NOT NULL , prod_desc text NULL ); -- -------------------- -- Create Vendors table -- Create a business listing -- -------------------- CREATE TABLE Vendors ( vend_id char(10) NOT NULL , vend_name char(50) NOT NULL , vend_address char(50) NULL , vend_city char(50) NULL , vend_state char(5) NULL , vend_zip char(10) NULL , vend_country char(50) NULL );
Create database constraints
-- ------------------- -- Define primary keys -- define some primary keys -- ------------------- ALTER TABLE Customers ADD PRIMARY KEY (cust_id); ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item); ALTER TABLE Orders ADD PRIMARY KEY (order_num); ALTER TABLE Products ADD PRIMARY KEY (prod_id); ALTER TABLE Vendors ADD PRIMARY KEY (vend_id); -- ------------------- -- Define foreign keys -- define some foreign keys -- ------------------- ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num); ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id); ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id); ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);
Insert data (INSERT)
Basic way
INSERT is used to insert (or add) rows to a database table.
There are several ways to insert:
- insert complete row;
- insert part of a row;
- Insert the results of some queries.
Database Data Insertion SQL Script
-- ------------------------ -- Populate Customers table -- Generate test data for customer table -- ------------------------ INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com'); INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green'); INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com'); INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com'); INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard'); -- ---------------------- -- Populate Vendors table -- Generate test data for shop table -- ---------------------- INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA'); INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA'); INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA'); INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA'); INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England'); INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France'); -- ----------------------- -- Populate Products table -- Generate data for product table -- ----------------------- INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket'); INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket'); INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket'); INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it'); INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included'); INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots'); INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll'); INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown'); INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown'); -- --------------------- -- Populate Orders table -- Generate data for the order table -- --------------------- INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20005, '2020-05-01', '1000000001'); INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20006, '2020-01-12', '1000000003'); INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20007, '2020-01-30', '1000000004'); INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20008, '2020-02-03', '1000000005'); INSERT INTO Orders(order_num, order_date, cust_id) VALUES(20009, '2020-02-08', '1000000001'); -- ------------------------- -- Populate OrderItems table -- Generate data for order item list table -- ------------------------- INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 1, 'BR01', 100, 5.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 2, 'BR03', 100, 10.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20006, 1, 'BR01', 20, 5.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20006, 2, 'BR02', 10, 8.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20006, 3, 'BR03', 10, 11.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 1, 'BR03', 50, 11.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 2, 'BNBG01', 100, 2.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 3, 'BNBG02', 100, 2.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 4, 'BNBG03', 100, 2.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 5, 'RGAN01', 50, 4.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 1, 'RGAN01', 5, 4.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 2, 'BR03', 5, 11.99); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 3, 'BNBG01', 10, 3.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 4, 'BNBG02', 10, 3.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 5, 'BNBG03', 10, 3.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 1, 'BNBG01', 250, 2.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 2, 'BNBG02', 250, 2.49); INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 3, 'BNBG03', 250, 2.49);
Generate test data using navicat
Don't do performance-related tests first, so don't do a lot of data test samples first! ! !
Update and delete data (UPDATE & DELETE)
Basic way
To update (modify) data in a table, you can use the UPDATE statement.
To delete (remove) data from a table, use the DELETE statement.
There are two ways to change table data:
- update or delete a specific row in a table;
- Update or delete all rows in the table.
There are two ways to use DELETE:
- delete a specific row from the table;
- Delete all rows from the table.
The basic UPDATE statement consists of three parts:
- the table to update;
- column names and their new values;
- A filter that determines which rows to update.
Update datasheet data
Example:
Customer 1000000005 now has an email address, so his record needs to be updated with the following statement:
-- query customers ID Record data for 10000005 customers SELECT * FROM Customers WHERE cust_id = 1000000005; -- update customer ID Customer email address data for 10000005 UPDATE Customers SET cust_email = 'kim@thetoystore.com ' WHERE cust_id = 1000000005; -- query customers ID Record data for 10000005 customers SELECT * FROM Customers WHERE cust_id = 1000000005;
The result is as follows:
SELECT * FROM Customers WHERE cust_id = 1000000005 > OK > Time: 0s UPDATE Customers SET cust_email = 'kim@thetoystore.com ' WHERE cust_id = 1000000005 > Affected rows: 1 > Time: 0s SELECT * FROM Customers WHERE cust_id = 1000000005 > OK > Time: 0s
delete data table data
Example:
Customer information change needs to delete the inserted new data record with customer ID 1000006
Insert a piece of mock data
-- Insert a new customer data INSERT INTO Customers ( cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact ) VALUES ( '1000000006', 'The cs Store', '1111 53rd Street', 'jiaozuo', 'IL', '54545', 'CHINA', 'cs' );
[
Delete record data for customer ID 100000006
-- delete customer ID Record data for 100000006 DELETE FROM Customers WHERE cust_id= 1000000006; -- query customers ID Record data for 1000000006 customers SELECT * FROM Customers WHERE cust_id = 1000000006; DELETE FROM Customers WHERE cust_id= 1000000006 > Affected rows: 1 > Time: 0s -- query customers ID Record data for 1000000006 customers SELECT * FROM Customers WHERE cust_id = 1000000006 > OK > Time: 0s
Query data (SELECT)
Basic way
Query is the way that the database is often used, and there are different usage methods according to different scenarios;
- retrieve data
- Data sorting
- filter data
- function
- Data aggregation and grouping
- junction table
- Combined query
query filter
query column with data filter
-- Query the product name and product price with a price less than 10 in the product table SELECT prod_name,prod_price FROM Products WHERE prod_price < 10;
The result set is:
prod_name prod_price ------------------ ---------- Fish bean bag toy 3.49 Bird bean bag toy 3.49 Rabbit bean bag toy 3.49 8 inch teddy bear 5.99 12 inch teddy bear 8.99 Raggedy Ann 4.99 King doll 9.49 Queen doll 9.49
The operators for the WHERE clause are
operator | illustrate | operator | illustrate |
---|---|---|---|
= | equal | > | more than the |
<> | not equal to | >= | greater or equal to |
!= | not equal to | !> | no greater than |
< | less than | BETWEEN | between the two specified values |
<= | less than or equal to | IS NULL | is a NULL value |
!< | not less than |
Aggregate data
We often need to summarize data without actually retrieving it, and SQL provides specialized functions for this. Using these functions, SQL queries can be used to retrieve data for analysis and report generation.
Examples of this type of search are:
- Determine the number of rows in the table (or the number of rows that meet a certain condition or contain a certain value);
- get the sum of some rows in the table;
- Find the maximum, minimum, average value of a table column (or all rows or some specific rows).
The above examples all need to summarize the data in the table, but do not need to find out the data itself.
query column with data filter
-- Query the average, maximum, and minimum values of the total number of products and product prices in the product table SELECT COUNT(*) AS num_items, MIN( prod_price ) AS price_min, MAX( prod_price ) AS price_max, AVG( prod_price ) AS price_avg FROM Products;
The result set is:
num_items price_min price_max price_avg --------- --------- --------- --------- 9 3.49 11.99 6.823333
The operators for the WHERE clause are
SQL aggregate function (aggregate function) | illustrate |
---|---|
AVG() | Returns the average of a column |
COUNT() | Returns the number of rows in a column |
MAX() | Returns the maximum value of a column |
MIN() | Returns the minimum value of a column |
SUM() | Returns the sum of a column's values |
junction table
If the data is stored in multiple tables, how can the data be retrieved with a single SELECT statement? The answer is to use joins. Simply put, a join is a mechanism used to associate tables in a SELECT statement, hence the name join. Using a special syntax, it is possible to join multiple tables to return a set of outputs, joining the correct rows in the associated tables at runtime.
Query related table data
-- In three different tables (order table, product table, store table), query the product name, store name, product price, quantity that meet (the same store number, the same product number, and the order number is 20007) SELECT prod_name, vend_name, prod_price, quantity FROM OrderItems, Products, Vendors WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;
The result set is:
prod_name vend_name prod_price quantity --------- -------------- ---------- -------- 18 inch teddy bear Bears R Us 11.99 50 Fish bean bag toy Doll House Inc. 3.49 100 Bird bean bag toy Doll House Inc. 3.49 100 Rabbit bean bag toy Doll House Inc. 3.49 100 Raggedy Ann Doll House Inc. 4.99 50
This example shows the items in order 20007. Order items are stored in the OrderItems table.
Each product is stored by its product ID, which references the product in the Products table.
These products are linked to the corresponding vendors in the Vendors table by vendor ID s, which are stored in each product's record.
Here the FROM clause lists three tables, the WHERE clause defines these two join conditions, and the third join condition is used to filter out the items in order 20007.
Combined query
Most SQL queries consist of only a single SELECT statement that returns data from one or more tables.
However, SQL also allows executing multiple queries (multiple SELECT statements) and returning the results as a query result set.
These combined queries are often called union or compound queries.
There are two main situations where a combined query needs to be used:
- Return structured data from different tables in one query;
- Execute multiple queries against a table, returning data by one query.
UNION rule
There are a few rules UNION needs to be aware of when combining.
- UNION must consist of two or more SELECT statements separated by the keyword UNION (so, if four SELECT statements are combined, three UNION keywords will be used).
- Each query in a UNION must contain the same columns, expressions, or aggregate functions (however, the columns need not be listed in the same order).
- The column data types must be compatible: the types do not have to be exactly the same, but must be a type that the DBMS can implicitly convert (for example, a different numeric type or a different date type).
Query combined data
-- Query customer information in the customer table, using different filter conditions select statement and combine the result sets SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ( 'IL', 'IN ', 'MI' ) UNION SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4All' ORDER BY cust_name, cust_contact;
The result set is:
cust_name cust_contact cust_email --------- ------------ ---------- Fun4All Denise L. Stephens dstephens@fun4all.com Fun4All Jim Jones jjones@fun4all.com The Toy Store Kim Howard Village Toys John Smith sales@villagetoys.com
Transaction processing (TRANSACTION)
There are several recurring keywords when working with transactions.
Here are a few terms you need to know about transaction processing:
- A transaction refers to a set of SQL statements;
- Rollback refers to the process of undoing the specified SQL statement;
- Commit refers to writing unstored SQL statement results to database tables;
- A savepoint is a temporary placeholder set in a transaction to which a rollback can be issued (as opposed to rolling back the entire transaction).
Basic way
The SQL transaction syntax of each database is different, so you also need to refer to the DBMS documentation of the corresponding database
Cursor
Using cursors involves several explicit steps.
- Before a cursor can be used, it must be declared (defined). This process doesn't actually retrieve data, it just defines the SELECT statement and cursor options to use.
- Once declared, the cursor must be opened for use. This process actually retrieves the data using the SELECT statement defined earlier.
- For cursors filled with data, rows are fetched (retrieved) as needed.
- When ending use of the cursor, the cursor must be closed and, if possible, freed (depending on the specific DBMS).
After a cursor is declared, the cursor can be opened and closed as often as needed. While the cursor is open, fetches can be performed as frequently as needed.
Basic way
The SQL cursor syntax of each database is different, so you also need to refer to the DBMS documentation of the corresponding database
Advanced SQL
Using cursors involves several explicit steps.
- Before a cursor can be used, it must be declared (defined). This procedure doesn't actually retrieve the data, it just defines the SEL statement and cursor options to use.
- Once declared, the cursor must be opened for use. This process actually retrieves the data using the SEL E CT statement defined earlier.
- For cursors filled with data, rows are fetched (retrieved) as needed.
- When ending use of the cursor, the cursor must be closed and, if possible, freed (depending on the specific DBMS).
After a cursor is declared, the cursor can be opened and closed as often as needed. While the cursor is open, fetches can be performed as frequently as needed.
Basic way
The SQL cursor syntax of each database is different, so you also need to refer to the DBMS documentation of the corresponding database
constraint
DBMS enforces referential integrity by imposing constraints on database tables.
Primary and foreign keys.
index
Indexes are used to sort data to speed up search and sorting operations.
Before you start creating an index, you should keep the following in mind.
- Indexes improve the performance of retrieval operations, but reduce the performance of data insertion, modification, and deletion. When performing these operations, the DBMS must dynamically update the index.
- Index data can take up a lot of storage space.
- Not all data is suitable for indexing. Data with few values (such as state) does not benefit as much from indexing as data with more possible values (such as first or last name).
- Indexes are used for data filtering and data sorting. If you frequently sort data in a particular order, the data may be suitable for indexing.
- Multiple columns can be defined in the index (for example, state plus city). Such an index is only useful when sorting by state and city. Such an index is not useful if you want to sort by city.
Indexes are created with the CREATE INDEX statement (the statements that create indexes vary widely from DBMS to DBMS).
The following statement creates a simple index on the product name column of the Products table.
CREATE INDEX prod_name_ind ON Products ( prod_name );
trigger
Triggers are special stored procedures that execute automatically when specific database activity occurs.
Triggers can be associated with INSERT , UPDATE , and DELETE operations (or combinations) on specific tables.
Unlike stored procedures (which are simply stored SQL statements), triggers are associated with a single table. The trigger associated with the INSERT operation on the Orders table executes only when a row is inserted in the Orders table. Similarly, triggers for INSERT and UPDATE operations on the Customers table execute only when those operations occur on the table.
The code inside the trigger has access to the following data:
- All new data in INSERT operations;
- All new and old data in UPDATE operations;
- Data deleted in a DELETE operation.
The trigger creation syntax of each database is different, so you also need to refer to the DBMS documentation of the corresponding database
Reference documentation
-
Reference book "SQL must know must know"
-
Reference Manual "Reference Manuals for Each RDBMS"
-
Reference book "SQL must know must know"
-
Difference Between Relational and Non-Relational Databases - Blog