Getting started with MySQL! Talk about the related concepts of database and MySQL

One, the database (database)

  • Concept: A warehouse that organizes, stores, and manages data in accordance with the data structure
  • Database development: four stages
  • Manual management stage: In the 1950s, hardware storage only consisted of punched cards, paper, and tape, and there was no operating system for software.
  • File system stage: In the 1950s and 1960s, computers had operating systems, hard disks and magnetic drums appeared, and data was stored in external storage as files and managed by the operating system
  • Database system stage: In the 1960s, a data model was used to manage and control data in a unified manner
  • Advanced database technology stage: modern
  • Types of databases : different types of databases are linked and organized according to different data structures
  • Hierarchical database
  • Network database
  • Relational database
  • Features of the database:
  • Realize data sharing and reduce data redundancy
  • Use specific data types
  • Have high data independence
  • Has a unified data control function
  • Common databases:
  • Qracel: Super Large/Charging ===>Oracle Corporation
  • DB2: Super Large/Charging ===>IBM (International Business Company)
  • SQL Server: Small/Commercial Charge ===>Microsoft (MS)
  • MySQL: Small/Free and Open Source ===>Sweden MySQL AB (acquired by Sun on January 16, 2008 (acquired by Qracel on September 20, 2009))
  • Sybase
  • Access
  • SQLite

Second, the database system

  • Definition: The database system is composed of a hardware part and a software part.
  • Hardware, mainly used to store data in the database, including computers, storage devices, etc.
  • The software part mainly includes the DBMS, the operating system that supports the operation of the DBMS, and the access technology that supports multiple languages ​​for application development, etc.
  • Composition: It is composed of data, DB, DBMS, application programs for operating the database, software platform, and related personnel of the database. Roughly divided into 3 main components
  • Database (DB): A place for storing data. A database may contain many files, and a database system usually contains many databases
  • Database Management System (DBMS): Software used to manage databases. Located between the user and the operating system. Able to define data storage structure, provide data operation mechanism, and maintain the security, integrity and reliability of the database
  • Database application program: In order to improve the processing capacity of the database system, the software supplement used to manage the database. Although there is already a DBMS, in many cases, the DBMS cannot meet the requirements for data management. The use of database applications can meet the higher requirements of logarithmic database management, which is more intuitive and friendly.
  • Several concepts:
  • DBA (database system administrator): database design, authority control, etc.
  • RDBMS (Relational Database Management System)

Three, data model

  • Roughly divided into three types: hierarchical data model, network data model, relational data model
  • Level: tree structure
  • Mesh: directed graph mesh model
  • Relationship: Use a two-dimensional table (relational table) to represent the data model between entities
  • RDBMS terminology
  • Data table: a table is a matrix of data
  • Record (tuple): row in the table
  • Field (attribute): column in the table
  • Primary key: also known as the primary code, used to uniquely identify each record in the table. You can define one or more columns in the table as the primary key. There cannot be two rows with the same value on the primary key column, nor can it be a null value
  • Domain: the value range of the attribute (for example: gender is only male and female)
  • Redundancy: Store twice the data, reducing performance, but improving data security
  • Super keywords, candidate keywords, primary keywords

table

  • In a relational database, a database table is a collection of a series of two-dimensional arrays used to store data and manipulate the logical structure of the data. It is composed of vertical columns and horizontal rows. The rows are called records , which are the units of data organization; the columns are called fields . Each column represents an attribute of the record and has corresponding description information, such as data type, data width, etc.
  • For example, the following is an authros table about author information

type of data

  • The data type determines the storage format of the data in the computer and represents different types of information. Commonly used data types are integer data types, floating-point data types, etc.
  • Each field in the table is a certain specified data type. For example, the "number" field in the above figure is an integer type, and the "gender" field is a character data type.

Primary key

  • Primary Key (Primary Key), also known as the primary code, is used to uniquely identify each record in the table. You can define one or more columns in the table as the primary key. The primary key column cannot have two rows with the same value, nor can it be a null value .
  • If the authros table is defined above, the table assigns an "author number" to each author, and the code is used as the primary key of the data table . If the same value appears, an error will be prompted, and the system cannot determine which record is being queried; The author's "name" is used as the primary key, and no duplicate names can appear. This is not consistent with the actual situation, so the "name" field is not suitable for the primary key.

Four, SQL (Structured Query Language)

  • What you can do: You can retrieve data from the database, delete data, add data, create new tables, set tables, stored procedures, and view permissions, etc.
  • There are many different types of SQL, there are 3 main standards
  • ANSI (American National Standards Institute) SQL
  • SQL-92 or SQL2 : The standard adopted in 1992 after modification of ANSI SQL
  • SQL-99: Expanded from SQL2 and added object-relational features and many other new functions
  • Major database vendors provide different versions of SQL. These versions of SQL not only include the original ANSI standard, but also support the SQL-92 standard to a large extent
  • SQL contains the following 4 parts
  • Data Query Language ( DQL ): SELECT statement
  • Data manipulation language ( DML ): INSERT, UPDATE, DELETE, etc.
  • Data Definition Language ( DDL ): DROP, CREATE, ALTER and other statements
  • Data Control Language ( DCL ): GRANT, REVOKE, COMMIT, ROLLBACK, etc.
  • Note: SQL is not case-sensitive , but distinguishes between "" and '', and the statement ends with a semicolon;

Five, database access interface

  • Different programming languages ​​will have different database access interfaces. Through these interfaces, programming languages ​​can execute SQL statements and perform database management.
  • The main database access interfaces are: ODBC, JDBC, AOD.NET, PDO

ODBC

  • ODBC (Open Database Interconnection) technology provides a common interface for accessing different SQL databases
  • DOBC uses SQL as the standard for accessing data . This interface provides maximum interoperability. An application can access different SQL database management systems (DBMS) through a common set of codes
  • An ODBC-based application does not rely on any DBMS for database operations, and does not directly deal with DBMS. All database operations are completed by the corresponding DBMS ODBC driver. In other words, no matter it is Access, MySQL or Oracle database, it can be accessed with ODBC API
  • The biggest advantage: can handle all databases in a unified way

JDBC

  • The standard method for java applications to connect to the database is a java API for executing SQL statements, composed of a set of classes and interfaces written in java language

AOD.NET

  • It is a set of object-oriented class libraries developed and designed by Microsoft under the .NET framework to interact with data sources
  • AOD.NET provides access to relational data, XML and application data

PDO

  • PDO defines a lightweight and consistent interface for PHP to access databases. It provides a data access abstraction layer, so that no matter what database is used, queries and data can be executed through consistent functions.
  • PDO is a major feature newly added to PHP 5

Six, MySQL overview

  • MySQL is an open source database management system developed, released and supported by MySQL AB.
  • The cross-platform open source relational database management system is widely used in the development of small and medium-sized websites.
  • Small size, fast speed and low cost.

Seven, MySQL version

  • MySQL Community Server ( Community Edition ): This version is completely free, but the official does not provide technical support.
  • MySQL Enterprise Server ( Enterprise Edition Server ): It can provide enterprises with data warehouse applications at a very high cost performance, supports ACID transaction processing, and provides complete submission, rollback, crash recovery and row-level locking functions. Need to pay.

Version naming convention

  • MySQL's naming mechanism consists of 3 numbers and 1 suffix .
  • For example: MySQL-5.7.10
  • (1) The first number 5 is the main version number, which describes the file format. All version 5 hair styles have the same file format.
  • (2) The second number 7 is the release level. The combination of the major version number and the release level constitutes the release serial number.
  • (3) The third number 10 is the version number of this release series, which increases with each new distribution. Usually choose the latest version that has been released.

8. Advantages of MySQL

  • Speed: Fast running speed.
  • Price: Free for most people.
  • Easy to use: Compared with the setup and management of other large databases, its complexity is lower and it is easy to learn.
  • Portability: It can work on many system platforms, such as: Windows, Linux, Unix, Mac OS, etc.
  • Rich interfaces: APIs for languages ​​such as C and C++ are provided.
  • Support query statements: MySQL can use standard SQL syntax and applications that support ODBC.
  • Security and connectivity: a very flexible and secure authority and password system, allowing host-based authentication. When connecting to the server, all password transmissions are in encrypted form.

Nine , MySQL command line use program

MySQL server-side utility program

  • mysqld: SQL daemon (that is, MySQL server process). The program must be run before the client can access the database by connecting to the server
  • mysqld_safe: server startup script. It is recommended to use mysql_safe to start the mysqld server in UNIX and NetWare
  • mysql.server: server startup script. The MySQL distribution in UNIX includes the mysql.server script. This script is used to use a system that contains a specific level, a script to start the service, and a running directory. It calls mysqld_safe to start the MySQL server
  • mysql_multi: server startup script that can start or stop multiple servers installed on the system
  • myisamchk: a practical tool for describing, checking, optimizing and maintaining MyISAM tables
  • mysqlbug: MySQL bug report script. It can be used to send defect reports to the MySQL mail system
  • mysql_install_db: The MySQL authorization table created by this script with default permissions. Usually only executed once when MySQL is first installed on the system

MySQL Client Utility Program

  • myisampack: A tool to compress MyISAM tables to produce smaller read-only tables
  • mysql: A command line tool for interactively inputting SQL statements or executing them in batch mode from a file
  • mysqlaccess: A script that checks the permissions to access the host name, user name, and database combination
  • MySQLadmin: A client program that performs management operations. For example, create or delete the database, reload the authorization table, refresh the table to the hard disk, and reopen the log file. Can also be used to retrieve version, process, and server status information
  • mysqlbinlog: A tool for reading statements in a binary log. Include executed statements in the binary log file, which can be used to help the system recover from a crash
  • mysqlcheck: Check, repair, analyze and optimize the table maintenance client program
  • mysqldump: A client program that dumps the MySQL database to a file (such as SQL statements or tab-delimited text files)
  • mysqlhotcopy: A tool for quickly backing up MyISAM or ISAM tables when the server is running
  • mysql import: A client program that uses LOAD DATA INFILE to import text files into related tables
  • mysqlshow: a client program that displays information about databases, tables, columns, and indexes
  • perror: A tool that displays the meaning of system or MySQL error codes

Ten, the default database

  • information_schema: Information database (stores the database name, table, table column data type and access permissions, etc.)
  • mysql: Responsible for storing database users, permission settings, keywords, etc.
  • performance_schema: Collect database server performance parameters
  • text: test database, an empty database, without any tables, can be deleted

11. Search Engine

  • Commonly used are MyISAM, InnoDB, MEMORY, MERGE
  • The default is MyISAM, to be changed to InnoDB (provides transaction security tables)

12. Common commands

  • End a command:; or \g
  • Switch/use database: use database name;
  • Enter MySQL: mysql -u root -p;
  • List database tables: show databases;
  • List all the tables in the current database: show tables;
  • Display the attributes of the data table: show cloumns from data table;
  • Refresh privileges: flush privileges;
  • Exit MySQL: quit; or exit;
  • Change password: update .uesr set authentication_string=password('new password');
  • Help: help or \h

Thirteen, the historical changes of MySQL

Version 3.23 (2001)

  • It is generally believed that the release of this version is the real "birth" moment of MySQL, and it has begun to gain widespread use. In this version, MySQL is still just a system that implements SQL queries on flat files. But an important improvement is the introduction of MyISAM to replace the old and restrictive ISAM engine. The InnoDB engine is also available, but it is not included in the default binary distribution because it is too new. So if you want to use InnoDB, you must compile it manually. Version 3.23 also introduced full-text indexing and replication. Replication is a key feature of MySQL as a database system for Internet applications (killer feature)

Version 4.0 (2003)

  • Support new syntax, such as UNION and multi-table DELETE syntax. The replication is rewritten. Two threads are used to implement replication in the standby database, which avoids the problems caused by task switching in the mode where one thread does all the replication work. InnoDB has become standard equipment, including all the features: row-level locks, foreign keys, and so on. Version 4.0 also introduced the query cache (this part has not changed much since then), and it also supports connections via SSL

Version 4.1 (2005)

  • Introduced more new syntax, such as subquery and INSERT ON DUPLICATE KEY UPDATE. Begin to support UTF-8 character set. Support new binary protocol and prepared statement

Version 5.0 (2006)

  • This version has some "enterprise-level" features: views, triggers, stored procedures, and stored functions. The code of the old ISAM engine was completely removed, and new engines such as Federated were introduced.

Version 5.1 (2008)

  • This is the first version released after Sun's acquisition of MySQL AB, and the development time is as long as five years. Version 5.1 introduced partitioning, row-based replication, and plugin APIs (including pluggable storage engine APIs). Removed the BerkeyDB engine, which is MySQL's earliest transactional storage engine. Other engines such as Federated will also be abandoned. At the same time, InnoDB Oy acquired by Oracle (Oracle has also acquired MerkeyDB) released the InnoDB plugin

Version 5.5 (2010)

  • This is the first version released after Oracle acquired Sun. The main improvements in version 5.5 focus on performance, scalability, replication, partitioning, support for Microsoft Windows systems, and some other aspects. InnoDB becomes the default storage engine. More legacy features and deprecated features have been removed. Added the PERFORMANCE_SCHEMA library, which includes some measurable performance indicators enhancements. Added replication, authentication and auditing APIs. Semisynchronous replication (semisynchronous replication) plug-in enters the practical stage. Oracle also released commercial certification plug-ins and thread pooling in 2011. InnoDB has also made major improvements in its architecture, such as multiple sub-buffer pools.

Version 5.6

  • Version 5.6 will contain some major updates. For example, for the first time in many years, large-scale improvements have been made to the query optimizer, more plug-in APIs (such as full-text indexing), replication improvements, and the PERFORMANCE_SCHEMA library have added more performance indicators. The InnoDB team has also done a lot of improvement work, these improvements have been included in the released milestone version and laboratory version. MySQL 5.5 mainly focuses on the improvement and enhancement of the basic part, and introduces some new features. And MySQL 5.6 improves the development and performance of the server on the basis of MySQL 5.5.

14. MySQL development model

  • The MySQL development process and release model have changed a lot at different stages, but they have basically stabilized. In the new milestone development version released by Oracle on a regular basis, it will contain the new features that will be released in the next GA (meaning generally available version) version. This is done to test and get feedback. Please do not use this version in a production environment, although Oracle claims that the quality of each milestone version is reliable and can be officially released at any time (so far there is no reason to disprove this statement) . Oracle also regularly releases the lab preview version, which mainly contains some specific features that need to be evaluated. These features are not guaranteed to be included in the next official version. Eventually, Oracle will release a new GA version with stable features
  • MySQL still follows the GPL open source agreement, and all the source code (except for some commercial version plug-ins) will be open to the community. Oracle also seems to understand that offering different versions to the community and paying users is not a wise move. MySQL AB has tried different versions of the strategy. As a result, paying users have become "eyes blind", unable to benefit from community testing and feedback. The different versions of the strategy were not welcomed by enterprise users, so they were later abolished by Sun.
  • Now Oracle provides separate server plug-ins for paid users, while MySQL itself still follows the open source model. Although there are some complaints about the release of proprietary server plug-ins, these are only a few voices, and they are slowly subsiding. Most MySQL users don’t care about this, and users who need it can also accept commercial licensed plug-ins
  • In any case, extensions that are not open source are just extensions, and will not turn MySQL into a restricted, non-open source model. Without these extensions, MySQL is also a fully functional database. Frankly speaking, we also appreciate Oracle's development of plug-ins with more features. If the feature is directly included in the server instead of the API, then there is no choice: users can only accept this implementation, and lose the opportunity to choose a more suitable implementation for the business. For example, if Oracle implements InnoDB's full-text indexing function in an API, it may implement Sphinx or Lucene plug-ins with the same API, which may be more useful to some users. The internal API design of the server is also very clean, which is very helpful for improving the quality of the code. Who doesn't want this?