Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "the basic concepts of MySQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Start: mysql-uroot-p enter

Enter password

Show database: show databases

1. Database

A database (DB (abbreviation of databases)) is a warehouse for storing data, but the data are related to each other and stored on the computer in a certain format. In a broad sense, data contains not only numbers, but also text, images, audio, video and so on.

The database consists of some persistent sets of interrelated data, which are stored in the storage medium of the computer in a certain organizational form.

Www.2cto.com

two。 Database management system

Database management system (DBMS) is a system that manages databases, which organizes data according to certain data models. DBMS shall provide the following functions:

(1) the data definition function can define data objects in the database.

(2) the data manipulation function can perform basic operations on database tables, such as insert, delete, modify and query.

(3) the data integrity check function ensures that the data input by the user should meet the corresponding constraints.

(4) the security protection function of the database ensures that only the authorized users can access the data in the database.

(5) the concurrency control function of the database enables multiple applications to access the data of the database concurrently at the same time.

(6) the fault recovery function of the database system enables the database to recover when there is a failure in the database operation, so as to ensure the reliable operation of the database.

(7) the function of accessing the database in the network environment.

(8) interfaces and tools to access database information conveniently and effectively. Programmers write database applications through the interface between program development tools and databases. The database administrator (DBA,DataBase Adminitrator) manages the database through the tools provided.

3. Data, database, database management system and applications that operate the database, together with the hardware platform, software platform and database-related personnel supporting them, constitute a complete database system. The following figure describes the composition of the database system.

4. The database management system stores and manages the data according to the data model. The main data models used in the database management system are hierarchical model, mesh model and relational model. Www.2cto.com

● hierarchical model

Organize data in a tree hierarchy.

● mesh model

Each data is represented by a node, and each node is associated with other nodes, so that all the data nodes in the database form a complex network.

● relation model

Organize the data in the database in the form of two-dimensional tables (relational tables). For example, the main information involved in the student table is the student number, name, professional name, gender.

A row in a table is called a record, a column is called a field, and the title of each column is called the field name. If you give each relational table a name, the structure of the relational table with n fields can be expressed as: relational table name (field name 1, … , field name n), the structure of a relational table is usually called a relational schema.

? In a relational table, a field or combination of fields is called a code if the value of a field or combination of fields uniquely identifies its corresponding record.

? Sometimes a table may have multiple codes, and one code can usually be specified as the "master code" for each relational table, and in the relational schema, the master code is usually marked with an underscore.

The data expression organized by relational model is simple and intuitive, and the operation of insertion, deletion and modification is convenient, while the data expression organized by hierarchical and mesh model is complex, and the operation of insertion, deletion and modification is complex. Therefore, relational model has been widely used. MySQL is a database management system that supports relational data model.

5. The main task of relational database design is to abstract the data in the real world to get a data model that meets the requirements of the real world and can be supported by RDBMS.

That is to determine which tables should be included in the database used by the application system and what the structure of each table is.

There are three kinds of relational database design: conceptual structure design, logical structure design and database physical design.

6. Design steps

Needs analysis: through investigation, collection and analysis, clear users' requirements for the database.

Conceptual structure design: synthesize, induce and abstract the user requirements obtained from the requirement analysis to form a conceptual model independent of the specific DBMS. Conceptual structure design-establishing entity-relation model (Entity-Relationship,E-R)

The Emurr model abstracts the entity types and the relationships between entities directly from the real world, and then uses the entity relation diagram (Emurr diagram) to represent the data model. It is a practical tool to describe the conceptual world and establish the conceptual model.

Entity: things that exist objectively and can be distinguished from each other are called entities. From concrete people, things and events to abstract states and concepts, they can be expressed abstractly by entities. It is represented by a rectangle, and the entity name is written in the rectangular box.

Entity set: a collection of things that share a common nature.

-attribute: the information item involved in each entity set is called an attribute, which is a property of the entity set. It is represented by an oval and its corresponding entities are connected by undirected edges. Www.2cto.com

-the entities in the entity set are distinguishable from each other. If the value of the attribute or minimum attribute combination in the entity set can uniquely identify its corresponding entity, the attribute or attribute combination is called a code. For each entity set, a code can be specified as the master code.

There are various relationships between entity set An and entity set B, which are usually called "connections".

The contact is represented by a diamond and is connected to the entity by a straight line. The graph constructed in this way is the Emurr graph, and the Emurr graph is the description method of the Emurr model.

7. The link between two entity sets An and B may be one of the following three situations.

One-to-one contact (1:1): one entity in An is associated with at most one entity in B, and one entity in B is associated with at most one entity in A.

One-to-many connections (1-to-many): one entity in A can be associated with multiple entities in B, while one entity in B is associated with at most one entity in A.

Many-to-many connections: an entity in A can be associated with multiple entities in B, and an entity in B can also be associated with multiple entities in A.

8. Logical structure design

Entities: each entity is mapped to a table.

Attribute: maps to a field in the corresponding table.

? Relational mapping

(1:1) the transformation from the Emurr diagram of the contact to the relational schema: it can correspond to either a relational schema alone or not. Www.2cto.com

If the connection corresponds to a relation pattern alone, the relation pattern is composed of the connection attribute and the main code attribute of each entity set participating in the connection, and the main code can be selected as the main code of either party of the entity set participating in the connection.

The relation does not correspond to a relation pattern alone, and the attribute of the connection and the master code of one party are added to the relationship pattern corresponding to the entity set of the other party.

(1) the transformation from the Emurr diagram of a connection to a relational schema: it can correspond to either a relational schema alone or not.

If the relation corresponds to a relation pattern alone, the relation pattern is composed of the attribute of the relation and the main code attribute of each entity set participating in the connection, and the master code on the n side is the main code of the relation pattern.

If the relation does not correspond to a relation schema alone, the attribute of the connection and the master code of the 1 end are added to the relation pattern corresponding to the n-terminal entity set, and the main code is still the main code of the n-terminal.

The transformation from the Emurr diagram of the connection to the relational schema: it corresponds to a relational schema alone, which includes the attribute of the connection and the master code attribute of each entity set participating in the connection. The master code of the relational schema is composed of the master code attributes of each entity set.

The main code of relational schema XS_KC is composed of two attributes: "student number" and "course number". A relational schema can only have one master code.

After designing the relational schema of a project, you can create databases, relational tables and other database objects in the database management system environment, enter the corresponding data, and perform various operations on the data according to the needs.

10. The physical model of data refers to the storage structure of data, such as the organization of database physical files and index files, the access path of files, the management of memory and so on. The physical model is not only related to the database management system, but also related to the operating system and even hardware. The physical model is invisible to users.

11. How to connect the database

1)。 ODBC database interface

ODBC, the Open Database Interconnection (Open DataBase Connectivity), is an interface standard developed by Microsoft to realize the communication between applications and relational databases. The database that conforms to the standard can operate on the database through commands written in SQL language, but only for relational databases. At present, all relational databases comply with this standard, such as Oracle, SQL Server, MySQL, DB2, Sybase, Access and so on. ODBC is essentially a set of database access API (application programming interface), composed of a set of function calls, the core of which is SQL statements. Www.2cto.com

In the specific operation, we must first use the ODBC manager to register a data source. According to the database location, database type and ODBC driver information provided by the data source, the manager establishes the relationship between ODBC and the specific database. In this way, as long as the application provides the data source name to the ODBC,ODBC, it can establish a connection to the corresponding database.

MySQL is supported for ODBC through MySQL Connector/ODBC (family of MyODBC drivers). The ODBC driver package Manager is a library used to manage communication between ODBC applications and drivers. ODBC.INI is an ODBC configuration file that holds the driver information and database information needed to connect to the server. The ODBC driver manager will use it to determine which driver to load (using the data source name).

2)。 ADO database interface

ADO (ActiveX Data Object) is a database application program interface based on COM developed by Microsoft. By connecting to the database through ADO, you can manipulate the data in the database flexibly.

There are two ways to access a relational database using ADO: one is through the ODBC driver, and the other is through the database-specific OLE DB Provider, which has higher access efficiency.

3)。 ADO.NET database interface

The ADO.NET data model is developed from ADO, but it is not only an improvement on ADO, but also adopts an entirely new technology. It is mainly shown in the following aspects:

● ADO.NET is not a product of ActiveX technology, but a product of tight integration with the .NET Framework.

● ADO.NET includes full support for the XML standard, which is of great significance for data exchange across platforms.

● ADO.NET can work both in an environment connected to a data source and disconnected from a data source. In particular, the latter is very suitable for the needs of network applications. Because in the network environment, maintaining a connection with the data source does not meet the requirements of the website, which is not only inefficient and costly, but also often leads to conflicts caused by multiple users visiting at the same time. Therefore, the ADO.NET system focuses on solving the problem of data processing under the condition of disconnection from the data source. Www.2cto.com

ADO.NET provides an object-oriented view of the database and encapsulates many database properties and relationships in ADO.NET objects. More importantly, ADO.NET encapsulates and hides a lot of database access details in many ways, so you can have no idea that objects are interacting with ADO.NET objects, and you don't have to worry about the details of moving data to or getting data from another database.

The data layer is the core of ADO.NET disconnected connection. The data read from the data source is first cached to the data set, and then called by the program or control. The data source can be a database or XML data.

The data provider is used to establish the relationship between the data source and the dataset, it can connect various types of data, and can provide the data from the data source to the dataset as required, or return the edited data from the dataset to the data source.

MySQL implements the required ADO.NET interface using MySQL Connector/NET and integrates it into the ADO.NET aware tool. This enables developers to easily create .NET applications that require secure and high-performance data connections (with MySQL). MySQLConnector/NET is a fully manageable ADO.NET driver written in the pure C # language.

4)。 JDBC database interface

There are two layers of interfaces in JDBC API: the application layer, which enables developers to call the database and get results through SQL, and the driver layer, which handles all communications with specific database drivers.

Using the JDBC interface has the following advantages for database operations:

(1) JDBC API is very similar to ODBC, which is convenient for users to understand.

(2) it frees programmers from complex driver calling commands and functions and devotes themselves to the realization of application functions.

(3) JDBC supports different relational databases, which enhances the portability of the program.

The main disadvantage of using JDBC: the speed of accessing data records will be affected to a certain extent. In addition, because the JDBC structure contains products from different manufacturers, this brings great trouble to the change of the data source.

MySQL implements the JDBC interface through the MySQL Connector/J driver, which provides connectivity to client applications developed using the Java programming language. MySQL Connector/J is a pure Java program that implements version 3.0 of the JDBC specification and can communicate directly with MySQL servers using the MySQL protocol. Www.2cto.com

5)。 Database connection Pool Technology

For the database application in the network environment, due to the large number of users, the traditional JDBC method is used to connect the database, and the excessive cost of system resources has become a bottleneck restricting the efficiency of large-scale enterprise applications. Using database connection pool technology to manage database connections can greatly improve the efficiency and stability of the system.

twelve。 For the general database application system, in addition to the database management system, it is necessary to design an interface suitable for ordinary people to operate the database.

When the application needs to process the data in the database, it first sends a data processing request to the database management system. After receiving the request, the database management system analyzes it, then performs the data operation, and returns the operation result to the application program. Because the application directly deals with the user, and the database management system does not deal with the user directly, so the application is called "foreground", and the database management system is called "background". Because the application program makes a service request to the database management system, which is usually called the client program (Client), while the database management system provides services for other applications, which is usually called the server program (Server), this mode of operating the database is also called the client / server mode.

The application program and the database management system can run on the same computer (stand-alone mode) or in the network mode. In the network mode, the database management system runs on one host on the network, and the application can run on multiple hosts on the network, that is, in an one-to-many way. For MySQL, in addition to installing the database management system on the server, you also need to install the database client on the client side.

13. The database application based on Web adopts three-tier client / server model, which is also known as BPX S structure. The first layer is the browser, the second layer is the Web server, and the third layer is the database server. The browser is an interactive interface for users to input data and display results. The user enters data in the browser form, then submits the data in the form and sends it to the Web server. The Web server application receives and processes the user's data, and through the database server, queries the required data from the database (or enters the data into the database) to the Web server, and the Web server inserts the returned results into the HTML page. Send it to the client and display it in the browser.

14. SQL (structured query language) is the standard language of relational database. SQL language is a structured language for relational database query, which was first proposed by Boyce and Chambedin in 1974. It is called SEQUEL language. In 1976, when the San Jose Institute of IBM Company developed the relational database management system System R, it was modified to SEQUEL2, that is, the current SQL language. The function of SQL language includes four parts: data query, data manipulation, data definition and data control. The SQL language is simple, convenient and practical, and only six commands are used to complete its core functions: SELECT, CREATE, INSERT, UPDATE, DELETE, GRANT (REVOKE). Now it has become the most widely used relational database language. SQL language is concise and easy to learn and use.

15. Introduction to mySQL:

MySQL is a small relational database management system developed by the Swedish company MySQL AB. At present, MySQL is widely used in small and medium-sized websites on Internet. Because of its small size, high speed and low total cost of ownership, especially open, many small and medium-sized websites choose MySQL as the website database in order to reduce the total cost of ownership.

Compared with other large databases, MySQL still has some shortcomings, but this does not reduce its popularity at all. MySQL provides more than enough functionality for the average individual user and small and medium-sized business, and because MySQL is open source software, it can greatly reduce the total cost of ownership.

At present, the popular way of website construction on Internet is LAMP (Linux+Apache+MySQL+PHP), even if Linux is used as the operating system, Apache as the Web server, MySQL as the database and PHP as the server-side script interpreter. Because these four software are all GPL-compliant open source software, you can build a stable and free website system without spending a penny in this way.

The main characteristics of MySQL database are as follows:

● uses the full multithreading service of core threads, which means that a multi-CPU architecture can be adopted.

● can run on different platforms. Www.2cto.com

● is written in C and C++ and tested with a variety of compilers to ensure the portability of the source code.

● supports AIX, FreeBSD, HP-UX, Linux, Mac OS, Novell Netware, OpenBSD, OS/2 Wrap, Solaris, Windows and other operating systems.

● provides API for multiple languages. These programming languages include C, C++, Eiffel, Java, Perl, PHP, Ruby and Tcl.

● supports multithreading and makes full use of CPU resources.

● optimized SQL query algorithm can effectively improve the query speed.

● can be used not only as a separate application in the client server network environment, but also as a library embedded in other software to provide multi-language support. Common codes such as Chinese GB2312, BIG5, Japanese Shift_JIS can be used as data table names and data column names.

● provides a variety of connections such as TCP/IP, ODBC and JDBC.

● provides management tools that can be used to manage, check, and optimize database operations.

● can handle large databases with tens of millions of records.

In 2008, sun acquired mySql AB.

For the installation and configuration of 16.mySql, please see install word file under the resource file, and reconfigure the server, as follows:

If you want to reconfigure the server, you can open the "start" → program "→" MySQL "→" MySQLServer 5.1 "→" MySQLServer Instance Config Wizard "and reconfigure the server in the configuration wizard that appears.

(1) options file

In the MySQL home directory of C:\ Program Files\ MySQL\ MySQL Server 5.1, there is a my.ini file, which is the options file for MySQL, and some options in the file are automatically loaded when MySQL starts. You can modify some of the default settings for MySQL by modifying the options file, as you'll see later. Www.2cto.com

(2) data catalog

"destination Folder" is the directory where MySQL is located, and the default directory is: C:\ Program\ Files\ MySQL\ MySQL Server5.1 "DataFolder" is the directory where the MySQL database and table files are located.

There is a my.ini file in the MySQL home directory, which is the options file for MySQL. Some options in the file are automatically loaded when MySQL starts. You can modify some of the default settings of MySQL by modifying the options file.

MySQL has a data directory for storing database files. The default path for MySQL 5.1 is C:\ Documents and Settings\ All Users\ Application Data\ MySQL\ MySQLServer 5.1\ data. MySQL creates a folder for each database in the data directory, and all table files are stored in the corresponding database folder.

This is the end of the introduction to the basic Concepts of MySQL Database. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.