Basic concepts of mysql-database

I. Overview

　　Databases (database, abbreviated DB)

Database technology is a very important technology in the field of computer application, it is produced in the the late 1960s, it is the newest technology of data management and also an important branch of software technology.

Simply put, the database is a repository of data, which is organized and stored according to a certain data structure (data structure refers to the organization or data), we can use the database to provide a variety of methods to manage the data in the database. A simpler image to understand, the database and the storage of our lives in the warehouse nature of the same, the difference is just storage of things different.

Database tables (table)

A data table is a very important object in a relational database, the basis of other objects, and a collection of two-dimensional arrays, which are used to store and manipulate the logical structure of the data. According to the classification of information.
A database may contain several data tables, each table is composed of rows and columns, record one data, the data table adds a row, each column consists of a field name and a collection of field data, the column is called a field,
Each column also has its own multiple properties, such as whether to allow Nulls, default values, lengths, types, storage encodings, annotations, and so on. For example

(data)

The information stored in the table is called data.

　　There are 3 main components of a database system

1. Database System: Where data is stored.

2. Database Management System,dbms: Software for user management database.

3. Database application: A software supplement to the management database used to improve the processing power of the database system.

　History of the database (five phases)

1. The embryonic stage of the file system database system accesses data through files.          File system is the embryonic stage of database system, appearing in the 560 's, can provide simple data access function, but can't provide complete and unified data management functions, such as complex query. So when you manage less, simpler data, or simply access simple data, without complex operations, you use a file system
2. Hierarchical database Database system really started stage, the data is stored in the form of a tree-like structure, so also called the tree database.
3. Mesh database data is stored in a form similar to a network structure.          Since the 1960s, the first generation database system (hierarchical model database system, network model database system) has come out, they provide a strong support for the unified management and sharing of data at this stage, the network model database due to its complexity, specificity, not widely used. In the hierarchical model database, IBM's IMS (Information Management system, information management systems) hierarchical model database system has been greatly developed, once became the largest database management system, with a huge customer base 4. Relational database        The early 1970s, the relational database system began to embark on the historical stage, and has maintained a vigorous vitality. Relational database systems use Structured Query Language (structured query Language, SQL) as a database definition language DDL and Database operations language DML  5. Object-oriented database        combining object-oriented method and database technology can make the analysis and design of database system accord with people's understanding of the objective world to the greatest extent. And it can effectively provide better database support for object-oriented programs.

Two. Characteristics of the database ⑴ for data sharingData sharing includes the ability for all users to access data in the database at the same time, as well as the ability for users to use the database in various ways and to provide data sharing. ⑵ reduce redundancy of dataCompared with the file system, because the database realizes the data sharing, thus avoids the user to establish the application file separately. It reduces the amount of duplicate data, reduces data redundancy, and maintains data consistency. ⑶ data consistency and maintainability to ensure data security and reliabilityMainly include: ① security Control: To prevent data loss, error update and unauthorized use; ② Integrity Control: Ensure the correctness, validity and compatibility of data; ③ concurrency control: Enables multiple access to data during the same time period, and prevents abnormal interaction between users. ⑷ Failure RecoveryThe database management system provides a set of methods to detect faults and repair faults in time, thus preventing data from being destroyed. The database system can recover the failure of the database system as soon as possible, it may be a physical or logical error.  such as the wrong operation of the system caused by data errors. Three. Database classification

In today's Internet, the most commonly used database model is mainly two kinds, namely relational database and non-relational data base.

1 Introduction to relational databases

(1) relational database : The complex data structure is attributed to a simple two-yuan relationship (i.e., two-dimensional tabular form).

(2) The current mainstream relational database: MySQL, Oracle, SQL Server, DB2 ...

Oracle is the world's most widely used database, the end of the last century with the advent of the network, Oracle launched 9i This version, I represents the Internet, fully support network reference. 10g,g is a grid grid, a database solution provided by Oracle to meet the advent of the "grid computing" era.    MySQL is an open-source, free relational database. Because of its small size, fast speed, low total cost of ownership, especially the open source of this feature, the general development of small and medium-sized web sites have chosen MySQL as the site database.    SQL Server Microsoft Corporation's relational database, general. NET platform as the first choice.    DB2 is a series of relational database management systems produced by IBM, which serve on different operating system platforms.

Introduction to the mainstream database

2 Introduction to non-relational databases

(1) Background of the birth of non-relational database

non-relational databases have also become NoSQL databases, and NoSQL is meant to be "not just SQL." Rather than "no SQL", so NoSQL doesn't have to completely negate the non-relational database, But as an effective complement to the traditional relational database. NoSQL databases can perform unimaginable efficiencies and high performance in specific scenarios.

With the rise of Internet Web2.0 Web site, the traditional relational database in dealing with web2,0 website, especially for the large scale of the massive data, ultra-large-scale and high-concurrency microblogging, SNS type of web2.0 pure dynamic website has become inadequate, exposing a lot of difficult to overcome problems.

For example: Traditional relational database Io bottleneck, performance bottleneck are difficult to break through effectively, so there are a large number of specific scenarios, high performance and ease of use for the purpose of the function of the unique features of the database products. The data of the NOSQL (non-relational) class is born in such a situation and has developed very rapidly.

(2) A summary of NoSQL non-relational databases:
1, NoSQL is not a negative relational database, but as an important complement to the relational database
2, NoSQL for high performance, high concurrency, but the requirements for data consistency requirements are not high
3. NoSQL Typical product memcached (pure memory, k-v), Redis (persistent cache, k-v), MongoDB (Database of documents, Xml-json)

Four. mysql Introduction

MySQL database: Is the Internet domain in one of the most important, by the vast number of users welcome open source relational database software, by the Swedish MySQL AB company development and maintenance. 2008. MySQL AB was acquired by Sun, and in 2009, Sun was acquired by the Big Guy Oracle (Oracle) in the traditional data database field, so MySQL database software is now owned by Oracle and becomes another database product of the traditional database domain. Oracle after the acquisition of MySQL, making itself in the business database and open-source software market share are the first position, such a pattern, caused a lot of people worry, this worry directly led to later MySQL branch database mariadb born in the development.

MySQL Development History

• In 1985, several like-minded lads in Sweden (David Axmark, Allan Larsson and Monty Widenius) set up a company that was the predecessor of MySQL AB.
• In 1990, TCX's customers began asking for SQL support for his API. It was proposed to use commercial databases directly, but Monty felt that the speed of commercial databases was unsatisfactory. As a result, he uses the mSQL code directly to integrate it into its own storage engine. Disappointingly, the effect was not very satisfying, so Monty ambitious and determined to rewrite a SQL support.
• In 1996, MySQL 1.0 was released, and it was only for a small set of people, equivalent to internal publishing.
• In October 1996, MySQL 3.11.1 was released (MySQL does not have a 2.x version) and was initially provided only in a binary version under Solaris. One months later, the Linux version appeared.
• For 1999-2000 years, MySQL AB was founded in Sweden. Monty hired several people to work with Sleepycat to develop the Berkeley DB engine because BDB supports transactional processing, so MySQL has since started supporting transactions.
• In December 2003, MySQL version 5.0 was released, providing features such as views, stored procedures, and so on.
• January 16, 2008, Sun (Solar micro System) formally acquired MySQL.
• On April 20, 2009, Oracle announced the acquisition of Sun computers at $9.50 a share and a total of $7.4 billion.
• MySQL 5.5 was released in December 2010, and its main new features include semi-synchronous replication and support for Signal/resignal's exception handling, and most importantly, the INNODB storage engine has finally become the default storage engine for MySQL today.
• June 18, 2013, Oracle revised the MySQL license agreement to remove the GPL. But then there was a message that it was a bug.

Why choose MySQL database?

(1) MySQL performance is excellent, service is stable, there are few abnormal downtime
(2) MySQL open source code without copyright restrictions, autonomy and low cost of use
(3) MySQL has a long history, community and user active, encountered problems can be solved
(4) MySQL software small size, easy to install and use, and easy to maintain, installation and maintenance costs are low
(5) MySQL brand reputation effect, so that enterprises do not have to consider the direct use
(6) MySQL support multi-use operating system, provide a variety of API interface, support multi-use development language, especially for popular languages have very good support

MariaDB Introduction:

Appeared in 2009. MARIADB database management System is a branch of MySQL database, mainly maintained by the open source community, with GPL license. One of the reasons for developing this mariadb is that when Oracle acquires MySQL, MySQL has a potential risk of being closed, so the MySQL open source community uses branching to avoid this risk. Click to view Details

MARIADB The default storage engine is Maria, not MyISAM. Maria can support transactions, but transaction support is not turned on by default because transactional support has a performance impact. You can convert to the Maria engine that supports transactions by using the following statement. ALTER TABLE ' tablename ' Engine=maria transactional=1;

Five. What is SQL?

Above we introduced the database components, wherein the database management system can receive some commands, the data file to add, delete, modify, query and other operations. Then these commands are SQL.

SQL: (Structured query Language) is a Structured Query language abbreviation. is a language specialized in dealing with database management systems.

SQL language: Is the standard language of relational database, it is mainly used for accessing data, querying data, updating data and managing database system and so on.

Specifically, SQL can be divided into 4 parts:

　　　　Data Control Language (DCL): Primarily used to control the user's access rights. Where the grant statement is used to add permissions to the user, the REVOKE statement is used to reclaim the user's permissions

　　　Data Definition language (DDL): DROP, CREATE, Alter, and so on; Database definition language. Primarily used to define databases, tables, views, indexes, triggers, and so on. The CREATE statement is used primarily for creating databases, creating tables, and creating views. The ALTER statement is primarily used to modify the definition of a table and modify the definition of a view. Drop statements are primarily used to delete a database, delete a table, delete a view, and so on.

　　　　Data Manipulation Language (DML): INSERT, UPDATE, DELETE statement, database manipulation language. Mainly used to insert data, update data, delete data. The INSERT statement is used to insert data, and the UPDATE statement is used to update the data, and the DELETE statement deletes the data.

　　　　data Query Language (DQL): SELECT statement. Used primarily for querying data.

#1. Operating folderadd: Create Database db1 charset UTF8;        Check: show databases;        Change: Alter DATABASE DB1 CharSet latin1; Delete: Drop database db1;#2. Operating FilesFirst switch to folder: Use DB1: CREATE TABLE t1 (id int,name char); Check: Show tables change: ALTER TABLE t1 modify name char (3); ALTER TABLE T1 change name name1 char (2);    Delete: drop table T1; #3. Contents/Records in the action fileAdd: INSERT INTO T1 values (1,'Egon1'), (2,'Egon2'), (3,'Egon3'); Check: Select* fromT1; Change: Update T1 set name='SB'where id=2; Delete: Delete fromT1 where id=1; Empty table: Delete fromT1;#If there is a self-increment ID, the new data is still started as the last one before deletion. truncate table T1; The data volume is large, the deletion speed is faster than the previous one, and starts directly from zero, auto_increment means: self-increment primary key means: constraint (Cannot re- and cannot be empty); speed up the search

Operation

Basic concepts of mysql-database