Basic concepts of database MySQL

Browse Catalogs

• Overview

• Features of the database

• Classification of databases

• Reasons to choose MySQL & MariaDB Introduction

• Download and install

• SQL Introduction

I. Overview

1. Data

The information stored in the table is called data.

2. Databases (database, abbreviated DB)

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 manage the data in the database through various methods provided by 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.

3. 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.

4. The database system has 3 main components

1). Database System: Where data is stored.

2). Database Management System,dbms: The software that the user manages the database.

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

5, the history of the database (five stages)

1). File system database system in the embryonic stage, through the file to access data.   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). The Hierarchical database database system is really beginning, and the data is stored in a tree-like structure, so it is also called a tree database.
3). Mesh database data is stored in a form similar to a mesh 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 Manipulation language (DML5). 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.

Second, the 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. Iii. Classification of databases

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

1. Introduction of relational database

(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)

Iv. reasons to choose MySQL & MariaDB introduction 1, the reason to choose MySQL

• MySQL performance is excellent, service is stable, and few abnormal outages occur
• MySQL open source code with no copyright restrictions, autonomy and low cost of use
• MySQL has a long history, community and users active, encountered problems can be solved
• MySQL software is small in size, easy to install and easy to maintain, low cost of installation and maintenance
• MySQL brand reputation effect, so that enterprises do not have to consider the direct use
• 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

2, 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. 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; Download and installation 1, the overall download installation steps:

• Download MySQL installer on official website
• Installing the MySQL server
• Installing the MySQL Client
• Client Connection Service Side
• Through the client to the server to send commands to perform additions and deletions to the database file operation.

2, Windows installation (ZIP archive mode installation)

• Download: MySQL Community Server 5.7.20 (https://dev.mysql.com/downloads/mysql/)
• Decompression: Download the extract package to the designated directory (e.g. d://mysql-5.7.20-winx64)
• Add environment variable: Adds the extracted folder under the Bin directory to the System environment variable (for example: D:\mysql-5.7.20-winx64\bin)
• Initialize: Enter command at cmd command line: mysqld–initialize-insecure
• Start MySQL service: input command at cmd command line: mysqld
• Connect MySQL service: Also open cmd command line and enter: Mysql-uroot-p Enter, password entered password, no continue to enter
• Now that the installation is successful, then manually make Windows system services for MySQL (PS: Production service must use the full path approach)
• To create a MySQL Windows service, enter this command on the cmd command line: "D:\mysql-5.7.20-winx64\bin\mysqld" –install
• To remove the MySQL Windows service, enter this command on the cmd command line: "D:\mysql-5.7.20-winx64\bin\mysqld" –remove
• Start MySQL service: in cmd command line input: net start MySQL
• Turn off MySQL service: in cmd command line input: net stop MySQL
• PS: Completely uninstall MySQL reference: http://www.cnblogs.com/wangfengming/articles/7879074.html

3. Linux Installation

• : Https://dev.mysql.com/downloads/mysql/5.7.html#downloads,
  can also be downloaded directly from the command: wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz
• Decompression: TAR-ZXVF mysql-5.7.11-linux-glibc2.5-x86_64.tar.gz
  Copy the extracted MySQL directory: Cp-r mysql-5.7.11-linux-glibc2.5-x86_64/usr/local/mysql
• Add user groups and users: Groupadd MySQL
• Add user MySQL to user group mysql:useradd-g MySQL MySQL
• Installation: Cd/usr/local/mysql/mkdir./data/mysql
  Chown-r Mysql:mysql./
  ./scripts/mysql_install_db–user=mysql–datadir=/usr/local/mysql/data/mysql
  CP Support-files/mysql.server/etc/init.d/mysqld
  chmod 755/etc/init.d/mysqld
  CP SUPPORT-FILES/MY-DEFAULT.CNF/ETC/MY.CNF
• Modify Startup script: Vi/etc/init.d/mysqld
• To modify an item:
  basedir=/usr/local/mysql/
  Datadir=/usr/local/mysql/data/mysql
• Start Services: Service mysqld start
• Test connection:./mysql/bin/mysql-uroot
• Add environment variables and edit/etc/profile so you can use MySQL command anywhere.
  Export path= $PATH:/usr/local/mysql//bin
  Source/etc/profile
• Start Mysql:service mysqld Start
• Close Mysql:service mysqld Stop
• View running Status: Service mysqld status
• Some workarounds for installation errors:
• Error 1:sqlyog when connecting, reported 1130 error due to no user permissions issue for remote connection
• Resolution 1: Change the ' MySQL ' database ' user ' table ' host ' entry from ' localhost ' to '% '
• Use MySQL;
• Select ' Host ' from user where user= ' root ';
• Update user Set host = '% ' where user = ' root ';
• Flush privileges;
• Resolution 2: Direct authorization
• GRANT all privileges on . To ' root ' @ '% ' identified by ' Youpassword ' with GRANT OPTION;
• Error 2:-bash:./scripts/mysql_install_db:/usr/bin/perl:bad Interpreter: No file or directory
• FIX: yum-y Install Perl perl-devel
• Error 3:installing MySQL system tables..../bin/mysqld:error while loading shared libraries:libaio.so.1:cannot open shared obj ECT file:no such file or directory
• Solution: Yum-y Install Libaio-devel

VI. Introduction of 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 folder Increase:Create Databasedb1 CharSet UTF8;        Check: show databases; Change:Alter DatabaseDB1 CharSet latin1; Delete:Drop Databasedb1;#2The . Operation files are first switched to the folder: UseDB1 Increase:Create TableT1 (IDint, nameChar); Check: Show tables change:Alter TableT1 Modify NameChar(3); Alter TableT1 Change name name1Char(2); Delete:Drop TableT1; #3. Manipulating the contents of a file/Record Increase:Insert  intoT1Values(1,'Egon1'),(2,'Egon2'),(3,'Egon3'); Check:Select *  fromT1; Change:UpdateT1SetName='SB' whereId=2; Delete:Delete  fromT1whereId=1; Clear the table:Delete  fromT1; #如果有自增id, the new data is still the same as the beginning of the last one before deletion. truncate TableT1, large data volume, deletion speed is faster than the previous one, and directly from zero, Auto_increment said: self-incrementPrimary KeyExpression: Constraint (cannot be repeated and cannot be empty);

View Code

Basic concepts of database MySQL