Database (concept, syntax, DBMS, SQL language: Creating databases, tables, adding, modifying, deleting data records)

relational database: a data structure that takes a table as an entity, with primary key and foreign key relationship as the relation.

PRIMARY key: in a relational database, each row is marked with a unique identifier, which is the primary key. The primary key has two features: non-null and cannot be duplicated.

foreign key: in the relational database, the foreign key is used to express the relationship between the table and the table, contact, through the primary foreign key relations, establish the relationship between entities.

Three basic relationship models between tables:

① one-to-many relationship: a master table record corresponds to multiple entries from the table record, one for the main table record from the table record.

② one to one relationship: a Main table record corresponding to a record from the table, a record from the table records corresponding to a main table.

③ Many-to-many relationships: one main table record corresponds to multiple entries from the table record, and one record from the table records corresponding to the main table.

relational database management System (DBMS):

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

The current popular relational database server management system is:

① Microsoft's MS SQL Server

Oracle ② Oracle

③IBM Company's DB2

④ MySQL, PostgreSQL for open source database

Sql:

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

SQL statements:

① Data Definition language (DDL): used to create, modify, and delete data structures within a database.

1--CREATE DATABASE DB2 CREATE DATABASE db;3--Delete Database db4 DROP DATABASE db;5--set up table T_student6 CREATE TABLE t_student (7--the PRIMARY key flag is listed as the primary key column8--auto_increment Set the autogrow column, the value of which is allocated by the DBMS without the developer's maintenance9ID INT PRIMARY KEY auto_increment,--numberingTenStudentname VARCHAR ( -),--name, 20 means first name 20 words OneSex ENUM ('male','female'),--Gender, enum represents enumeration ABirthday DATE,--Birthday -Tel VARCHAR ( One) --Telephone -);

② Data Query Language (DQL): queries for data in the database

1 -- query All genders DISTINCT represents the removal of duplicate columns 2SELECT DISTINCT sex from t_student; 3 4 --Querying all the data in the table, * representing all columns in the query table 5 SELECT * from t_student; 6 7 -- Query the first start of the data, showing 3 records 80,3;

③ Data Manipulation Language (DML): used to modify data in a database, including inserting, updating, and deleting data

1--Add a record2 INSERT into T_student (Studentname,sex,birthday,tel)3VALUES ('Zhang San','male','1983-09-30','13987879898');4 5--Delete a record with an ID of 176DELETE from T_student WHERE id= -;7 8--modify John Doe's phone9UPDATE t_student SET tel='13966666666'WHERE studentname='John Doe';

④ Data Control Language (DCL): Controlling database access rights

1 -- GRANT statement that grants access to the user 2 --The REVOKE statement that cancels the user's access rights

FOREIGN KEY constraints:

foreign key: refers to the existence of an attachment relationship from a column of a table to a column of a primary key.

FOREIGN KEY constraint: refers to forcing a constraint on the primary key associated with an external key, and if the constraint is violated, the modification of the piece of data is not allowed.

Note: No FOREIGN KEY constraint is established without a foreign key.

PRIMARY KEY constraint:

A primary key is one or more columns in a table, the primary key column cannot be empty, nor can it be duplicated, and only one primary key can be used in a table.

Aggregation functions:

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

Count: counts the number of rows.

SUM: Gets the sum of individual columns and metering.

AVG: calculates the average of a column.

Max: calculates the maximum value of a column.

min: calculates the minimum value of a column.

Order of execution of SQL statements:

First step: Execute from

Step two: where conditional filtering

Step three: Group by group

Fourth step: Execute the Select projection column

Fifth step: Having conditional filtering

Sixth step: Execute the ORDER by sort statement, the default is ASC ascending, and Desc is descending.

Join:

differences between inner joins and outer joins:

Inner joins: can only query the records of the relevant data in two tables;

outer joins: You can query all records in a table, regardless of whether the record has associated data.

1 CREATE TABLE T_man (2 ID INT PRIMARY KEY auto_increment,3Manname VARCHAR ( -),4 Birthday DATE5 );6 INSERT into T_man (manname,birthday)7VALUES ('Zhang San','1980-02-03'),('John Doe','1994-01-05'),8('Harry','1991-07-30'),('Zhao Liu','1995-11-18');9SELECT *From T_man;Ten   One CREATE TABLE t_bike ( A ID INT PRIMARY KEY auto_increment, -Biketype VARCHAR ( -), - Money INT, the Manid INT -  ); -   ---Add a FOREIGN key constraint, so that the primary key and foreign key one by one correspond to prevent the generation of garbage data + ALTER TABLE t_bike ADD CONSTRAINT fk_mb FOREIGN KEY (Manid) - REFERENCES T_man (ID); +   A INSERT into T_bike (Biketype,money,manid) atVALUES ('Phoenix', -,1),('Permanent', -,1),('Fire Kylin', -,1), -('Big Luck', +,2),('Harlech', -,2),('Czech security', -,3), -('Mobike', $,3),('BMW', -,3),('Mercedes', -,3); -   ---Check all bikes and show the owner name of the bike -SELECT B.*,m.manname from T_bike b joins T_man m on m.id=B.manid; in   ---Query John Doe all bikes (internal connection: showing two tables with linked data) toSELECT b.*, m.manname from T_bike B joins T_man m on M.id=b.manid WHERE m.manname='John Doe'; +SELECT b.*, M.manname from T_man m joins T_bike B on M.id=b.manid WHERE m.manname='John Doe'; -   theSELECT b.* from T_bike b,t_man m WHERE b.manid=m.id and M.manname='John Doe'; *   $--Show all the user's bike information, external connection can not be used, plus where the form to writePanax Notoginseng--Outer connection: Right outer join, contains all the information in the right table, left outer connection, contains all the information in the table on the left.  -SELECT M.*,b.biketype,b.money from T_bike b right joins T_man m on b.manid=m.id; the   +--show the number of bikes for all users A--Left outer connection theSELECT m.*,count (biketype) num from T_man m left joins T_bike B on m.id=B.manid GROUP by m.id; +--Right Outer connection -SELECT m.*, COUNT (biketype) number from T_bike B right joins T_man m on B.manid=m.id GROUP by M.id;

Database (concept, syntax, DBMS, SQL language: Creating databases, tables, adding, modifying, deleting data records)