Basic concepts of the 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.

    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.

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. Download and install

12345

a：官网下载mysql安装程序b：安装mysql服务端c：安装mysql客户端d：客户端连接服务端e：通过客户端向服务器发送命令，执行对数据库文件的增删改差操作。

Windows Edition installation　

#1. Download: MySQL Community Server 5.7.20
https://dev.mysql.com/downloads/mysql/

#2. Unzip
Download the extract package to the designated directory (e.g. d://mysql-5.7.20-winx64)

#3. Adding environment variables
"Right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the second content box to find the variable named path of a row, double-click"-"the MySQL Bin directory path appended to the variable value, with;

C:\Program Files (x86) \parallels\parallels Tools\applications;d:\mysql-5.7.16-winx64\bin

#4. Initialization
Mysqld--initialize-insecure

#5. Start the MySQL service
Mysqld

#6. Connect to MySQL Service
Mysql-uroot-p Enter, have password input password, not continue to enter

Decompression mode installation

Decompression mode installation

#1. Download: MySQL Community Server 5.7.20    https://dev.mysql.com/downloads/mysql/#2. Extract the download package from the flag network to the specified directory (for example, d:// mysql-5.7.20-winx64) #3. Add environment variable "right-click Computer"-"Properties"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the second content box, find a row with the variable named path, double-click Append the MySQL bin directory path to the variable value, and split the C:\Program Files (x86) \parallels\parallels tools\applications;d:\mysql-5.7.16-winx64\ Bin#4. Initialize mysqld--initialize-insecure#5. Start the MySQL service mysqld#6. Connect the MySQL service mysql-uroot-p enter  , have password input password, do not continue to enter decompression mode installation

PS: The production service must use the full path approach
# Create a Windows service for MySQL and execute this command at the terminal:
"C:\mysql-5.7.16-winx64\bin\mysqld"--install

# to remove the MySQL Windows service, execute this command at the terminal:
"C:\mysql-5.7.16-winx64\bin\mysqld"--remove '

# Start the MySQL service
net start MySQL (no semicolon appended)

# Turn off MySQL service
net stop MySQL (with no semicolon appended)

Production Services

PS: The production service must use the full path method # to make the MySQL Windows service, execute this command at the terminal: "C:\mysql-5.7.16-winx64\bin\mysqld"--install# remove the MySQL Windows service, Execute this command on terminal: "C:\mysql-5.7.16-winx64\bin\mysqld"--remove ' # start MySQL service net start MySQL (back without semicolon) # Close MySQL service net stop MySQL ( With no semicolon appended)

After starting the MySQL service, open the Windows Task Manager, there will be a process called mysqld.exe run, so Mysqld.exe is the MySQL server program.

Exit Login: Quit or exit or \q;

Nextnextnext....ps:mis way installation, will be installed more than the whole, easy to use.

Nextnextnext....ps:mis way installation, will be installed more than the whole, easy to use.

Linux version

1,: Http://dev.mysql.com/downloads/mysql/5.6.html#downloads

You can also directly copy the 64-bit, download by command: wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz

2. Decompression
#解压
TAR-ZXVF mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz
#复制解压后的mysql目录
Cp-r Mysql-5.6.33-linux-glibc2.5-x86_64/usr/local/mysql

3. Adding user groups and users
#添加用户组
Groupadd MySQL
#添加用户mysql to the user group MySQL
useradd-g MySQL MySQL

4. Installation
Cd/usr/local/mysql/<br>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

#修改启动脚本
Vi/etc/init.d/mysqld

#修改项:
basedir=/usr/local/mysql/
Datadir=/usr/local/mysql/data/mysql

#启动服务
Service mysqld Start

#测试连接
./mysql/bin/mysql-uroot

#加入环境变量, edit the/etc/profile so that you can use MySQL command from anywhere.
Export path= $PATH:/usr/local/mysql//bin<br>source/etc/profile


#启动mysql
Service mysqld Start
#关闭mysql
Service Mysqld Stop
#查看运行状态
Service MYSQLD Status

5. Error

5.1 SQLyog 1130 error when connecting, due to no user rights 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;

5.2 Some errors during installation
-bash:./scripts/mysql_install_db:/usr/bin/perl:bad Interpreter: No file or directory
FIX: yum-y Install Perl perl-devel

Installing MySQL system Tables..../bin/mysqld:error while loading shared libraries:libaio.so.1:cannot open shared objec T file:no such file or directory
Solution: Yum-y Install Libaio-devel

Linux version installation

1,: Http://dev.mysql.com/downloads/mysql/5.6.html#downloads can also be directly copied 64-bit, through the command download: wget http://dev.mysql.com/get/ DOWNLOADS/MYSQL-5.6/MYSQL-5.6.33-LINUX-GLIBC2.5-X86_64.TAR.GZ2, Unzip # unzip TAR-ZXVF Mysql-5.6.33-linux-glibc2.5-x86_ 64.tar.gz# Copy the extracted MySQL directory cp-r mysql-5.6.33-linux-glibc2.5-x86_64/usr/local/mysql3, add user groups and users # Add user group Groupadd mysql# Add user MySQL to user group mysqluseradd-g MySQL mysql4, install Cd/usr/local/mysql/<br>mkdir./data/mysqlchown-r mysql:mysql././ scripts/mysql_install_db--user=mysql--DATADIR=/USR/LOCAL/MYSQL/DATA/MYSQLCP support-files/mysql.server/etc/ Init.d/mysqldchmod 755/ETC/INIT.D/MYSQLDCP support-files/my-default.cnf/etc/my.cnf #修改启动脚本vi/etc/init.d/mysqld # Modified: Basedir=/usr/local/mysql/datadir=/usr/local/mysql/data/mysql #启动服务service mysqld start #测试连接./mysql/bin/mysql- Uroot #加入环境变量, edit the/etc/profile so that you can use MySQL command from anywhere export path= $PATH:/usr/local/mysql//bin<br>source/etc/ Profile #启动mysqlservice mysqld start# close Mysqlservice mysqld stop# view running status service mysqld STATUS5, error 5.1 SqlyoG connection, reported 1130 error, is due to no user rights to 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;5.2 when installing some errors-bash:./scrip TS/MYSQL_INSTALL_DB:/usr/bin/perl:bad Interpreter: No file or directory resolution: yum-y install Perl perl-develinstalling mysql system ta Bles..../bin/mysqld:error while loading shared libraries:libaio.so.1:cannot open Shared object file:no such file or di Rectory Solution: Yum-y Install Libaio-devel

Reference Blog: http://www.cnblogs.com/wangfengming/articles/7880595.html

Six. 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. Action Folder
Add: Create Database db1 charset UTF8;
Check: show databases;
Change: Alter DATABASE DB1 CharSet latin1;
Delete: Drop database db1;

#2. Manipulating files
Switch to the folder first: Use DB1
Add: 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 file
Add: INSERT INTO T1 values (1, ' Egon1 '), (2, ' Egon2 '), (3, ' Egon3 ');
Check: SELECT * from T1;
Change: Update t1 set name= ' SB ' where id=2;
Delete: delete from T1 where id=1;

Clear the table:
Delete from T1; #如果有自增id, the new data is still the same as the beginning of 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 says: self-increment
Primary key means: constraint (cannot be repeated and cannot be empty); Accelerated Lookup

....

#1. Operation Folder        add: Create Database db1 charset UTF8;        Check: show databases;        Change: Alter DATABASE DB1 charset latin1;        Delete: Drop database db1; #2. The action file is    first switched to the 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/Record in action file        : INSERT into T1 values (1, ' Egon1 '), (2, ' Egon2 '), (3, ' Egon3 ');        Check: SELECT * from T1;        Change: Update t1 set name= ' SB ' where id=2;        Delete: delete from T1 where id=1;        Empty table:            delete from T1; #如果有自增id, the new data is still the same 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 be repeated and cannot be empty); Accelerated Lookup

Basic concepts of the database