[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

mysql database view

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

Database entry - wiki
SQL Language - A Beginner's Tutorial
SQL Language - w3cschool
Reference book "SQL must know must know"
Reference Manual "Reference Manuals for Each RDBMS"
SQL Language - A Beginner's Tutorial
SQL Language - w3cschool
Reference book "SQL must know must know"
Difference Between Relational and Non-Relational Databases - Blog
Database Basic Concepts - Blog

Tags: Database nosql

Posted by scooterlibby on Tue, 27 Sep 2022 05:36:30 +0930

Programs Wiki

[Database]Basic introduction and operation of database