Shulou(Shulou.com)06/01 Report--

MySQL (Relational Database Management system)

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently belongs to the products of Oracle. MySQL is the most popular relational database management system. MySQL is one of the best RDBMS (Relational Database Management System, relational database management system) applications in WEB applications.

MySQL is an associated database management system that stores data in different tables instead of all data in a large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the dual licensing policy, which is divided into community version and commercial version. Because of its small size, high speed and low total cost of ownership, especially open source, the development of small and medium-sized websites generally choose MySQL as the website database.

Because of the excellent performance of its community version, it can form a good development environment with PHP and Apache.

What is a database?

Database (Database) is a warehouse that organizes, stores and manages data according to data structure.

Each database has one or more different API for creating, accessing, managing, searching, and replicating saved data.

We can also store data in a file, but it is relatively slow to read and write data in the file.

So now we use a relational database management system (RDBMS) to store and manage large amounts of data. The so-called relational database is a database based on the relational model, which deals with the data in the database with the help of mathematical concepts and methods such as set algebra.

RDBMS is the characteristics of relational database management system (Relational Database Management System):

1. The data appears in tabular form.

two。 Various record names for each behavior

3. The data field corresponding to the record name of each column

4. Many rows and columns form a form

5. Several forms make up database

RDBMS terminology

Before we move on to the MySQL database, let's take a look at some of the terms of RDBMS:

Database: a database is a collection of associated tables. .

Data table: a table is a matrix of data. A table in a database looks like a simple spreadsheet.

Column: a column (data element) contains the same data, such as postcode data.

Row: a row (= tuple, or record) is a set of related data, such as a piece of data that a user subscribes to.

Redundancy: storing twice as much data, redundancy can make the system faster.

Primary key: the primary key is unique. There can be only one primary key in a data table. You can use the primary key to query data.

Foreign keys: foreign keys are used to associate two tables.

Compound key: compound key (compound key) uses multiple columns as a single index key, which is generally used for composite indexes.

Indexes: use indexes to quickly access specific information in database tables. An index is a structure that sorts the values of one or more columns in a database table. A catalogue similar to a book.

Referential integrity: referential integrity requires that references to entities that do not exist are not allowed in the relationship. And entity integrity is the integrity constraint that the relational model must meet in order to ensure the consistency of the data.

Mysql database

MySQL is a relational database management system developed by MySQL AB Company of Sweden and currently belongs to Oracle Company. MySQL is an associated database management system that stores data in different tables instead of all data in a large warehouse, which increases speed and flexibility.

Mysql is open source, so you don't have to pay extra.

Mysql supports large databases. It can handle large databases with tens of millions of records.

MySQL uses the standard SQL data language form.

Mysql can be allowed on multiple systems and supports multiple languages. These programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl.

Mysql has good support for PHP, and PHP is the most popular Web development language at present.

MySQL supports large databases, data warehouses with 50 million records, 32-bit system table files up to 4GB, 64-bit system supports the largest table file is 8TB.

Mysql can be customized, using the GPL protocol, you can modify the source code to develop your own Mysql system.

MariaDB

MariaDB database management system is a branch of MySQL and is mainly maintained by the open source community. The purpose of licensing MariaDB with GPL is to be fully compatible with MySQL, including API and the command line, making it an easy replacement for MySQL. In terms of storage engine, XtraDB (English: XtraDB) is used instead of MySQL's InnoDB. MariaDB was developed by Michael Widenius (English: Michael Widenius), the founder of MySQL, who earlier sold his company, MySQL AB, to SUN for $1 billion. Since then, with the acquisition of SUN by Oracle, the ownership of MySQL has also fallen into Oracle's hands. The name MariaDB comes from the name of Michael Widenius's daughter Maria.

MariaDB's transaction-based Maria storage engine replaces MySQL's MyISAM storage engine, which uses a variant of Percona's XtraDB,InnoDB, and branch developers want to provide access to the upcoming MySQL 5.4 InnoDB performance. This version also includes PrimeBase XT (PBXT) and the FederatedX storage engine.

Develop

Founded in 2009, Michael "Monty" Widenius, the father of MySQL, completed a counterattack against MySQL with his new project MariaDB. One of the reasons for developing this branch is that after Oracle acquired MySQL, there is a potential risk of shutting down MySQL, so the community uses a branching approach to avoid this risk. Over the past year, large Internet users and Linux publishers have abandoned MySQL and joined the MariaDB camp. MariaDB is currently the most popular derivative version of MySQL database, and it is also regarded as an alternative to the open source database MySQL.

Although MariaDB is regarded as an alternative to MySQL database, it is better than MySQL in terms of extended functionality, storage engine, and some new functional improvements. And migrating from MySQL to MariaDB is also very simple:

1. Data and table definition files (.frm) are binary compatible

2. All client API, protocols and structures are identical.

3. All file names, binaries, paths, ports, etc., are the same

4. All MySQL connectors, such as PHP, Perl, Python, Java, .NET, MyODBC, Ruby and MySQL C connector, remain unchanged in MariaDB.

5. The mysql-client package can also run normally in the MariaDB server

6. The shared client library is also binary compatible with MySQL

That is, in most cases, you can uninstall MySQL and install MariaDB, and then run as before.

Origin

Why did you change the name? there are some allusions.

After Mr. Widenius, the father of MySQL, left Sun, he felt that it was very unreliable to rely on Sun/Oracle to develop MySQL, so he decided to open another branch, which is called MariaDB.

MariaDB and MySQL are compatible in most ways, and developers can hardly feel any difference. At present, MariaDB is the fastest growing branch version of MySQL, and the new version has been released faster than the official MySQL version of Oracle.

There are two main problems in MySQL development under the control of Oracle: 1. The core development team of MySQL is closed and there are no members outside Oracle at all. Even if many experts are willing to contribute, they are unable to do so. 2. The release speed of the new version of MySQL has greatly slowed down after Oracle acquired Sun. Widenius has a ppt that uses data to compare the speed of new releases before and after the acquisition. There are a lot of bugfix and new feature that have not been added to the release in time.

The above two problems have led to the development of their own customized versions of MySQL by major companies, including Yahoo.com, Alibaba, Facebook and Google / Facebook + Taobao, etc.

MySQL is an asset of the open source community, and no individual / organization has the right to own it. In order to rely on the strength of the vast MySQL community to develop MySQL more rapidly, it is necessary to open branches.

The default storage engine for MariaDB is Maria, not MyISAM. Maria can support transactions, but transaction support is not turned on by default because it has an impact on performance. You can convert to a Maria engine that supports transactions through the following statement. ALTER TABLE `tablename` ENGINE=MARIA TRANSACTIONAL=1

Difference

What is the essential difference between these two databases? I think there is still mysql*.exe in the mariadb folder BIN, except that MySQL will be closed by ORACLE, while mariadb will be open source. is there any essential difference between them?

Difference one:

MariaDB is not just a substitute for Mysql, its main purpose is to innovate and improve the technology of Mysql.

Difference two:

After Mr. Widenius, the father of MySQL, left Sun, he felt that it was very unreliable to rely on Sun/Oracle to develop MySQL, so he decided to open another branch, which is called MariaDB.

MariaDB and MySQL are compatible in most ways, and developers can hardly feel any difference. At present, MariaDB is the fastest growing branch version of MySQL, and the new version has been released faster than the official MySQL version of Oracle.

MariaDB is a branch version of MySQL that uses the Aria storage engine. It is a free and open source database server developed by the company founded by Michael Widenius, the author of the original MySQL.

More of the code for this project is adapted from MySQL 6.0. for example, the "pool of threads" function provides a solution to multiple data connection problems. MariaDB 5.1.41 RC can be downloaded here, 32-bit and 64-bit compiled Linux versions, as well as source code packages. MariaDB is released based on GPL 2.0.

So for most MySQL users, it should not be difficult to switch from the mainstream MySQL to MariaDB.

Difference 3:

LAMP architecture is very popular for a time, which is inseparable from the free and easy-to-use of MySQL, but after Oracle acquired Sun, many companies began to worry about the open source prospect of MySQL, and the recent move to further close the source of Oracle is even more reassuring, and many Internet companies have begun to seek alternatives to MySQL.

I have to mention the vision of Apple, which announced the move to PostgreSQL at the beginning of Oracle's acquisition of Sun. But PostgreSQL was designed to be different from MySQL and is not a suitable solution for most Internet companies that use MySQL. In addition to Apple,Google, Facebook, Twitter also made extensive use of MySQL, have released their own MySQL branch / patch set, and have been adopted by many companies. At the same time, MariaDB, Percona and other MySQL branches are also gradually coming into the public's field of vision.

According to Wikipedia, MariaDB is a community-driven branch version of MySQL using the XtraDb storage engine, led by MySQL founder Michael Widenius, and open source under the GPL v2.0 protocol. Because of the intervention of the founder of MySQL, MariaDB has attracted a lot of attention. Many applications such as Drupal, MediaWiki, phpMyAdmin, WordPress and so on have announced that they support MariaDB.

Migration from MySQL to MariaDB Wikipedia strives for openness

LAMP architecture is very popular for a time, which is inseparable from the free and easy-to-use of MySQL, but after Oracle acquired Sun, many companies began to worry about the open source prospect of MySQL, and the recent move to further close the source of Oracle is even more reassuring, and many Internet companies have begun to seek alternatives to MySQL.

The mysql and mariaDB development teams agree, except that mysql has been shut down by oracle while mariaDB is open source, but it certainly doesn't rule out the possibility that oracle will spend billions of dollars to buy the father of mysql a few years later.

Download MySql

Official website: http://dev.mysql.com/downloads/

Turn off the firewall

Example:

First, concept: data: data database: DB database management system: DBMS database system: DBS MySQL: database mysql: client commands (used to connect to services or send sql instructions) SQL: structured query language, which MySQL supports. SQL language is divided into four parts: DDL, DML, DQL, DCL II, Connect to the database: mysql-h hostname-u username-p password library name C:\ > mysql-login local service C:\ > mysql-h localhost-u root-proot using anonymous account and password-login local service C:\ > mysql-u root-p-- recommended login local Enter password: * C:\ > mysql-u root-p lnmp61-- access the lnmp61 database directly. Authorization: format: grant allows manipulation of on library names. Table name to account @ source identified by 'password';-- example: create zhangsan account, password 123, authorize add / delete / modify / query data of all tables under lnmp library, source is unlimited mysql > grant select,insert,update,delete on lnmp61.* to zhangsan@'%' identified by '123; Query OK, 0 rows affected (0.00 sec) 4. Basic operation of SQL mysql > show databases -- View all databases under the current user mysql > create database database name;-- create database mysql > use test;-- Select to enter test database mysql > drop database database name;-- Delete a database mysql > show tables;-- View all tables under the current database mysql > select database ();-- View the current database mysql > desc tb1 -- View the table structure of tb1. Mysql > create table demo (--create demo table-> name varchar (16) not null,-> age int,-> sex enum ('wicked sec') not null default'm'); Query OK, 0 rows affected (0. 05 sec) mysql > desc demo -- View the table structure +-+-+ | Field | Type | Null | Key | Default | Extra | +-- -- + | name | varchar (16) | NO | | NULL | | age | int (11) | YES | | NULL | | sex | enum ('w') 'm') | NO | | m | | +-+-+ 3 rows in set (0.00 sec) mysql > drop table if exists mytab -- try to delete the mytab table-- add a data mysql > insert into demo (name,age,sex) values ('zhangsan',20,'w'); Query OK, 1 row affected (0.00 sec) mysql > insert into demo values (' lisi',22,'m') -- add data Query OK without specifying field name, 1 row affected (0.00 sec) mysql > insert into demo (name,age) values ('wangwu',23);-- specify partial field name to add data Query OK, 1 row affected (0.00 sec)-- batch add data mysql > insert into demo (name,age,sex) values (' aaa',21,'w'), ("bbb", 22) Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql > select * from demo;-query data mysql > update demo set age=24 where name='aaa';-modify Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > delete from demo where name='bbb' -- Delete Query OK, 1 row affected (0.00 sec) mysql >\ h-- Quick help mysql >\ c-- cancel command entry mysql >\ s-- View status of current database mysql >\ Q-- exit mysql command line 5. Data types of MySQL database: data types of MySQL are divided into four categories: numeric type, string type, date type and NULL. 5.1Numeric type: * tinyint (1 byte) smallint (2 bytes) mediumint (3 bytes) * int (4 bytes) bigint (8 bytes) * float (4 bytes) float (6 refine 2) * double (8 bytes) decimal (custom) string value 5.2 string type Normal string * char fixed length string char (8) * varchar variable string varchar (8) binary type tinyblob blob mediumblob longblob text type tinytext * text is commonly used in mediumtext longtext * enum enumerations Set collection 5.3time and date type: date year, month, day, time, hour, second, datatime, year, month, day, hour, second, timestamp timestamp, year year, 5.4NULL value, NULL means "no value" or "unknown value". You can test whether a value is NULL and cannot enter a value for NULL. To perform arithmetic operations on null values. The result is either NULL 0 or NULL means false, and the rest means the operator of true MySQL: arithmetic operator: +-* /% comparison operator: = >

< >

< stu.sql 多表查询： -- 多表查询：-- 1. 嵌套方式的多表查询-- 2. where关联查询-- 3. 左联和右联，内联的查询。=================================================-- 已知条件：mysql>

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.