The initial MySQL

1.1 DataBasse

A database is a repository for storing data. It is a collection of data stored in a form that can be easily obtained.

What is the difference between a database and a data structure?

Data structure is a subject.

Database is a piece of software, and the data structure is also used at the bottom of the database, and it is very dependent on this data structure.

Advanced data structure B Tree B+Tree

1. Data storage mode

① Manual management stage

Stored in paper bags, on tape, or recorded by hand.

② File system stage

These files are managed through the file system, but it is not structured and is not easy to query. File saving data has the following disadvantages:

• File security issues
• Files are not conducive to data query and management
• Files are not conducive to storing massive amounts of data
• Files are not easily controlled in the program

③ Database system stage

Database storage media:

• disk
• memory

In order to solve the problem of the file system stage, experts designed more conducive to the management of data software – database, it can manage data more effectively.

A database can provide remote services, that is, use a database over a remote connection, so it is also called a database server.

1.2 Database classification

Database can be divided into relational database and non – relational database

1. Relational Database (RDBMS)

A database that uses a relational model to organize data.

To put it simply, a relational model is a two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and their relationships. Standard BASED SQL, but some internal implementation differences.

Common relational databases are:

1. Oracle: Oracle products, suitable for large-scale projects, suitable for complex business logic, such as ERP, OA and other enterprise information system

System. Charge, best!

2. MySQL: belongs to Oracle, not suitable for complex business. Open source and free.

3. SQL Server: Microsoft product, installed on Windows Server, suitable for medium – and large-sized projects. Collect fees.

2. Non-relational databases

SQL based implementation is not specified. Now it refers more to NoSQL databases, such as:

1. Based on key-value pairs, such as memcached and Redis
2. Document-based: for example, mongodb
3. Based on column family: for example, hbase
4. Graph-based: for example, neo4j

3. Difference and contrast

Note: OLTP (On-Line Transaction Processing) refers to on-line Analytical Processing (OLAP).

1.3 Database System

The scope of database system is much larger than database, database system is not a simple database, but by database, database management system, application development tools and so on.

client:

MySQL > install MySQL

CMD to configure environment variables

Step 1: This computer -> right-click properties -> Advanced System Settings -> Environment Variables

Step 2: New –> variable name: MYSQL_HOME –> Select browse directory, find C:\Program Files\MySQL, select your download –> OK

Step 3: In system variables, select Path -> Edit -> Create -> Add the bin directory of your selected database -> click OK three times

Win + R –> CMD –> run the mysql -u root -p command to check whether the configuration is successful

Microsoft Windows [Version10.019041.985.[c] Microsoft Corporation. All rights reserved. C:\Users\Jin>mysql -u root -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.717.-log MySQL Community Server (GPL)

Copyright (c) 2000.2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
Copy the code

3. Navicat, etc

1.4 SQL language

Database management system through SQL language to manage the data in the database

1. Concepts of SQL

SQL language is a database query and programming language. It is mainly used to access data, query data, update data and manage relational database system. It is the standard for relational data languages.

2. Classification of SQL

• DDL Data definition language, used to maintain the structure in which data is stored

Representative commands: create, drop, alter (for those of us who just joined the company, we will not use this at the beginning of the project)

• DML data manipulation language, used to manipulate data

Insert, delete, update DML, DQL, select (CURD, most frequently used)

• DCL data control language, mainly responsible for permission management and transactions

Delegate commands: Grant, REVOKE, commit (operations, not often used for back-end development)

Storage engines

Storage engine: database management system how to store data, how to build index for the stored data and how to update, query data and other technologies.

Query existing search engines in mysql

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+-- ----------+
9 rows in set (0.00 sec)

mysql>
Copy the code

To understand:

MyISAM: The default table type, which is based on the traditional ISAM type, ISAM is short for Indexed Sequential Access Method, which is the standard way to store records and files. It is not transactionally safe and does not support foreign keys. Insert MyISAM is suitable if you perform a large number of select operations.

② InnoDB: support transaction security engine, support foreign key, row lock, transaction is his biggest characteristic. If you have a lot of updates and inserts, InnoDB is recommended, especially for multiple concurrency and high QPS.

(3) the difference between

1.5 Database access technology

In application system, program language needs to use database access technology to access database. The SQL statements embedded in the program work only if you use database access technology.

Different programming languages access databases differently.

① The early database access technology ODBC provided a common interface for accessing different relational databases.

ADO.NET is a component provided by Microsoft. C# is mainly used to access the database through methods.

JDBC is a Java API for executing SQL statements. The Java language provides access to a variety of relational databases through JDBC. JDBC consists of a set of classes and interfaces written in the Java language.