MySQL (1), MySQL (

MySQL (1), MySQL (
1 MySQL learning route

• Basic stage: Basic MySQL database operations (add, delete, modify, and query) and some advanced operations (Views, triggers, functions, stored procedures, etc ).
• Optimization stage: How to Improve database efficiency, such as indexes and table shards.
• Deployment phase: How to build real environment systems, such as server clusters and Server Load balancer.

 

2. Database basics 2.1 What is a database?

• Database: database, the data storage warehouse.
• Database (defined by professionals): The medium for efficient data storage and processing (mainly two types of media: disk and memory ).

 

2.2 database category?

• Based on different storage media
  • Relational Database Service (SQL)
  • Non-Relational Database Service (NoSQL)

 

2.3 what are the products in different database camps?

• Relational Database:
  • Large Databases: Oracle and DB2.
  • Medium-sized databases: SQL Server and MySQL.
  • Small databases: access.
• Non-relational databases:
  • Memached
  • Mogodb
  • Redis

 

2.4 What are the differences between the two database camps?

• Relational Database: Secure (storage of disks is basically impossible to lose), easy to understand, and a waste of space.
• Non-relational databases: High Efficiency and Security (loss due to power failure ).

 

3. What is RDS 3.1?

• Relational Database: a database built on a relational model (mathematical model.
• Relational Model: A relational model is called a relational model. A relational model consists of the following three aspects:
  • Data Structure: data storage problem. Two-dimensional table (with rows and columns ).
  • Operation Command set: All SQL statements.
  • Integrity constraints: Data constraints in a table, constraints between tables (Foreign keys ).

 

3.2 relational data design?

• From the analysis of the data requirements to be stored, if it is a type of data (entities, such as people, books, etc.), it should be designed into a two-dimensional table, the table is composed of the header (field name) and the data part (actually stored data unit), as shown in.

Header	Field 1	Field 2
Data Unit	Data 1	Data 2

 

 

 

• Case studies: An Analysis of a teaching system where instructors are responsible for teaching, teaching students, and teaching in classrooms.
  • ① Identify the entities in the system: the lecturer table, the student table, and the class table.
  • ② Identify the data information that should exist in the object:
    • INSTRUCTOR: name, gender, age, height, salary, etc.
    • Student: name, gender, learner, subject, etc.
    • Class: Class Name, classroom number, etc.

 

• • • Relational Database: maintains the relationships between entities and entities.
    • Internal Contact: Each student has information such as name, gender, student ID, subject, and age.

Name	Gender	Student ID	Subject	Age
Zhang San	Male	001	Java	23
Li Si	Male	002	MySQL
Wang 'er		003	Linux	17

 

 

 

 

• • • All fields in the second row describe Michael (internal contact). The second column can only store gender (internal constraint ).
    • One of the characteristics of relational databases: If a field in the table does not have a value (data), but the system still needs to allocate space, it is a waste of space.

 

• • • The relationship between entities: each student belongs to a class, and each class must have multiple students (one to multiple ).
    • Student table:

Name	Gender	Student ID	Subject	Age
Zhang San	Male	001	Java	23
Li Si	Male	002	MySQL
Wang 'er		003	Linux	17

 

 

 

 

• • • Class table:

Class Name	Classroom no.
Java001	A001
Linux002	B005

 

 

 

 

• • • Solution: Add a class field to the student table to point to the class (a unique class information must be found)

Name	Gender	Student ID	Subject	Age	Class
Zhang San	Male	001	Java	23	Java001
Li Si	Male	002	MySQL		MySQL005
Wang 'er		003	Linux	17	Linux002

 

 

 

 

• • • The relationship between student entities and class entities: the relationship between entity and entity.

 

4. Keyword description

• Database: database.
• Database system: database system (DBS): a virtual system that associates multiple types of content. DBS = DBMS + DB.
• DBMS: database management system, which is used to manage databases.
• DBA: database administrator.
• Row/record: row/record. In essence, it refers to a row (a record) in a table ). The row is from the structure point of view, and the record is from the data point of view.
• Column/Field: column/Field, which is essentially a thing.

 

5 SQL

• SQL: Structured Query Language, Structured Query Language (data is mainly queried ).
• SQL is divided into three parts:
  • DDL: Data Definition Language. It is used to maintain the structure of the stored data (database, table), representing commands such as create, drop, and alter.
  • DML: Data Manipulation Language, Data operation Language, used to operate Data (Data table content), representing commands: Insert, delete, update, etc. DML has a separate classification: DQL (Data Query Language: Data Query Language, such as select ).
  • DCL: Data Control Language, Data Control Language, mainly responsible for permission management, representing commands such as grant and revoke.
• SQL is the operation instruction of relational databases. SQL is a constraint, but it is not mandatory (similar to W3C). Therefore, there may be some minor differences within different relational database products.