The basic operation syntax of database is summarized

Relational database: A data structure with tables as entities and primary and foreign key relationships as associations.

Primary key: In a relational database, each row is identified by a unique 1 identifier, which is the primary key. Primary keys have two characteristics: they are non-null and cannot be repeated.

Foreign key: In relational database, foreign key is used to express the relationship between tables, and establish the relationship between entities through the primary foreign key relationship.

There are three basic relational models between tables:

One-to-many relationship: one master table record corresponds to several slave table records, and one slave table record corresponds to one master table record.

1-to-1 relationship: 1 master table record corresponds to 1 slave table record, 1 slave table record corresponds to 1 master table record.

Many-to-many relationship: 1 master table record corresponds to multiple slave table records, 1 slave table record corresponds to multiple master table records.

Relational database Management System (DBMS) :

A relational database is just a container for storing data, and most databases rely on a piece of software called a database management system (DBMS) to manage the data in the database.

At present, the popular relational database server management system includes:

Microsoft MS SQL Server

Oracle Oracle

IBM company'S DB2

Open source database of MySQL, PostgreSQL

SQL:

SQL is a structured query language, a computer language for managing relational databases and communicating with the data in the database.

SQL statements:

Data definition language (DDL) : used to create, modify and delete data structures in the database.

--  Creating a database db
CREATE DATABASE db;
--  Delete database db
DROP DATABASE db;
--  Set up table t_student
CREATE TABLE t_student(
 -- PRIMARY KEY Identifies the column as the primary key column 
 -- AUTO_INCREMENT Sets the automatic growth of the column by the value of the column DBMS Distribution without developer maintenance 
 id INT PRIMARY KEY AUTO_INCREMENT,--  Serial number 
 studentName VARCHAR(20),--  Name, 20 Say the name 20 A word 
 sex ENUM(' male ',' female '),--  Gender, enum Said the enumeration 
 birthday DATE, --  birthday 
 tel VARCHAR(11) --  The phone 
);

Data query language (DQL) : For the query of data in the database

--  Query for all genders  DISTINCT Represents the removal of duplicate columns 

SELECT DISTINCT sex FROM t_student;

--  Querying all the data in the table, * Represents all the columns in the query table 
SELECT * FROM t_student;

--  Query the first 1 article   Start the data by showing 3 records 
SELECT * FROM t_student LIMIT 0,3;

Data manipulation language (DML) : used to modify the data in the database, including insert, update and delete data

--  Add records 
INSERT INTO t_student(studentName,sex,birthday,tel)
VALUES(' zhang 3',' male ','1983-09-30','13987879898');

--  delete id for 17 The record of 
DELETE FROM t_student WHERE id=17;

--  Modify the li 4 The phone 
UPDATE t_student SET tel='13966666666' WHERE studentName=' li 4';

Data control language (DCL) : control database access rights


-- 给用户授予访问权限的GRANT语句

 -- 取消用户访问权限的REVOKE语句

Foreign key constraints:

Foreign key: A dependency relationship between a column of a slave table and a column of a primary key.

Foreign key constraint: it means to impose a constraint on the primary key of foreign key association. If the constraint is violated, the modification of the data is not allowed.

Note: No foreign key constraint is not the same as no foreign key.

Primary key constraints:

A primary key is one to more than one column in a table. Primary key columns cannot be empty or repeated. There can be only one primary key in a table.

Aggregate function:

Statistical analysis of a set of data is performed using aggregate functions. Common aggregation functions are as follows:

COUNT: Count the rows.

SUM: Gets the sum of a single column.

AVG: Calculate the average value of a column.

MAX: Calculates the maximum value for a column.

MIN: Calculates the minimum value for a column.

Order of SQL statement execution:

Step 1: Perform FROM

Step 2: where conditional filtering

Step 3: GROUP BY grouping

Step 4: Execute the select projection column

Step 5: HAVING conditional filtering

Step 6: Execute the ORDER BY sort statement, which defaults to asc ascending and DESC descending.

Connection:

Differences between inner and outer connections:

Inner join: Only records that have relevant data in both tables can be queried;

External join: You can query all records in a table, whether or not the records have associated data.

CREATE TABLE t_man(
 id INT PRIMARY KEY AUTO_INCREMENT,
 manName VARCHAR(20),
 birthday DATE
);
INSERT INTO t_man(manName,birthday)
VALUES(' zhang 3','1980-02-03'),(' li 4','1994-01-05'),
  (' The king 5','1991-07-30'),(' zhao 6','1995-11-18');
SELECT * FROM t_man;
 
 CREATE TABLE t_bike(
 id INT PRIMARY KEY AUTO_INCREMENT,
 bikeType VARCHAR(20),
 money INT,
 manId INT
 );
 
 --  Add a foreign key constraint , Let's make primary and foreign keys 11 Corresponding, prevent the generation of garbage data 
 ALTER TABLE t_bike ADD CONSTRAINT fk_mb FOREIGN KEY (manId)
 REFERENCES t_man(id);
 
 INSERT INTO t_bike(bikeType,money,manId)
 VALUES(' phoenix ',400,1),(' permanent ',500,1),(' Fire kirin ',250,1),
  (' The universiade ',1000,2),(' Xia Li ',600,2),(' giant ',1200,3),
  (' The worship ',200,3),(' BMW ',2000,3),(' Mercedes ',600,3);
 
 --  Query all bikes and display the name of the owner of the bike 
 SELECT b.*,m.manName FROM t_bike b JOIN t_man m ON m.id=b.manId;
 
 --  Query li 4 All bikes (inner connection : Show related data in two tables) 
 SELECT b.* ,m.manName FROM t_bike b JOIN t_man m ON m.id=b.manId WHERE m.manName=' li 4';
 SELECT b.* ,m.manName FROM t_man m JOIN t_bike b ON m.id=b.manId WHERE m.manName=' li 4';
 
 SELECT b.* FROM t_bike b,t_man m WHERE b.manId=m.id AND m.manName=' li 4';
 
 --  Display the bike information of all users , External connection not available, plus where Let me write it in the form 
 --  Outer join: RIGHT  Right outer join, containing all the information in the table on the right, left  Left outer join, containing all the information in the left table.  
 SELECT m.*,b.bikeType,b.money FROM t_bike b RIGHT JOIN t_man m ON b.manId=m.id;
 
 --  Displays the number of bikes for all users 
 --  The left outer join 
 SELECT m.*,COUNT(bikeType) num FROM t_man m LEFT JOIN t_bike b ON m.id=b.manId GROUP BY m.id;
 --  Right connection 
 SELECT m.*, COUNT(bikeType) number FROM t_bike b RIGHT JOIN t_man m ON b.manId=m.id GROUP BY m.id;

I hope you found this article helpful