day12--Database (Mysql)

1. Introduction to the databaseWhat is a database? (https://www.cnblogs.com/alex3714/articles/5950372.html)

A database is a warehouse that organizes, stores, and manages data according to its structure.
Each database has one or more different APIs for creating, accessing, managing, searching, and replicating the saved data.
We can also store the data in a file, but it is relatively slow to read and write data in the file.
So, now we're using 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 uses mathematical concepts and methods such as set algebra to process data in the database.
The RDBMS is the feature of the relational database management system (relational Management Systems):
1. The data appears in tabular form
2. Various record names for each behavior
3. Data fields corresponding to record names for each column
4. A number of rows and columns form a single sheet
5. A number of forms form database

is a database

RDBMS terminology

Before we start learning the MySQL database, let's look at some of the terminology of the following RDBMS:

Database: database is a collection of some related 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 the ZIP code data;

row: a row (= tuple, or record) is a set of related data, such as a user's subscribed data

Redundancy : stores twice times the data, redundancy can make the system faster. (The higher the degree of normalization of a table, the more the relationship between the table and the tables may often require a connection query between multiple tables, and a connection operation can slow down the query.) For example, the student's information is stored in the student table, and the faculty information is stored in the Department table. An association relationship is established with the Department table by using the dept_id field in the student table. If you want to query the name of a student's department, you must find the student's department number (dept_id) from the student table, and then department find the name of the department based on that number. If you often need to do this, the connection query will waste a lot of time. You can therefore add a redundant field, Dept_name, to the student table, which is used to store the names of the students ' faculties. This will not have to be connected every time. )

PRIMARY KEY : The primary key is unique. Only one primary key can be included in a data table. You can use the primary key to query the data;

foreign Key: A foreign key is used to correlate two tables;

Composite key : Composite key (key combination) multiple columns as an index key, commonly used for composite indexes;

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

referential integrity: referential integrity requires that references to non-existent entities are not allowed in the relationship. and entity integrity are the integrity constraints that the relational model must satisfy in order to ensure the consistency of the data.

MySQL Database

MySQL is the most popular relational database management system, and in Web applications MySQL is one of the best RDBMS (relational database Management system: relational databases Management systems) application software. Developed by the Swedish MySQL AB company, currently owned by Oracle Corporation. MySQL is an associated database management system that keeps data in separate tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility.

1.Mysql is open source, so you don't have to pay an extra fee;

The 2.Mysql supports large databases. A large database with thousands records can be processed;

3.MySQL uses the standard SQL data language form;

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

5.Mysql has a good support for PHP, PHP is currently the most popular web development language;

6.MySQL support large database, support 50 million records of Data Warehouse, 32-bit system table file maximum support 4gb,64 bit system support the largest table file for 8TB;

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

2. Installation and use of the MYSQ database

Install MySQL on Linux/unix

The following RPM packages are recommended for installing Mysql,mysql AB on Linux platforms using the RPM package:

1.MySQL -MySQL server. You need this option unless you only want to connect to a MySQL server that is running on another machine;

2.mysql-client -MySQL client program to connect and operate the MySQL server;

3.Mysql-devel -Library and include files, if you want to compile other MySQL client, such as Perl module, you need to install the RPM package;

4.mysql-shared -This package contains some languages and applications that require dynamic loading of shared libraries (libmysqlclient.so*), using MySQL;

5.Mysql-bench -Benchmark and performance testing tool for MySQL database server.

The following example of installing MySQL RMP is on the SuSE Linux system, which is also suitable for other RPM-enabled Linux systems, such as Centos.

The installation steps are as follows:

Log in to your Linux system using the root user.

Download the MySQL RPM package for: MySQL download.

Perform the MySQL installation with the following command, RPM package for the RPM package you downloaded:

[Email protected]]# rpm-i mysql-5.0. 9-0. i386.rpm

The process of installing the MySQL server above will create a MySQL user and create a MySQL profile my.cnf.

You can find all of the MySQL-related binaries in/usr/bin and/usr/sbin. All data tables and databases will be created in the/var/lib/mysql directory.

Here are some of the installation procedures for MySQL optional packages that you can install according to your needs:

[Email protected]]# rpm-i mysql-client-5.0. 9-0-I mysql-devel-5.0. 9-0-I mysql-shared-5.0. 9-0-I mysql-bench-5.0. 9-0. i386.rpm

Install MySQL on window

Installing MySQL on Windows is relatively straightforward, and you only need to download the MySQL installation package from the Windows version of MySQL download and unzip the installation package.

Double click on the Setup.exe file, next you only need to install the default configuration click "Next", by default, the installation information will be in the C:\mysql directory.

Next you can enter "cmd" command in the search box by "start" = "Switch to the C:\mysql\bin directory at the command prompt and enter the command:

mysqld.exe --console

If the installation succeeds the above command will output some MySQL boot and innodb information.

Verifying MySQL Installation

After the successful installation of MySQL, some basic tables will be initialized, after the server starts, you can verify that MySQL is working properly with simple tests.

Use the Mysqladmin tool to get the server status:

Use the mysqladmin command to check the version of the server, on Linux the binary is located on the/usr/bin on Linux, and on the window the binary file is located in C:\mysql\bin.

[Email protected]]# mysqladmin--version

On Linux, the command outputs the following results, based on your system information:

Mysqladmin  8.235.0.  9-0 for Redhat-linux-gnu on i386

If you do not enter any information after the above command is executed, your MySQL is not installed successfully.

Execute a simple SQL command using MySQL client

You can use MySQL command to connect to MySQL server on MySQL client (MySQL clients), by default the MySQL server password is empty, so this instance does not need to enter a password.

The command is as follows:

[Email protected]]# MySQL

The above command will output the mysql> prompt, which means that you have successfully connected to the MySQL server and you can execute the SQL command at the mysql> prompt:

mysql> SHOW DATABASES; +----------+| Database |+----------+| MySQL    | | Test     |+----------+2inset (0.13 sec)

day12--Database (Mysql)