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

I. Preface

The last article of ​ mainly introduced the related concepts of database. As we know, databases are mainly divided into relational databases and non-relational databases, and some mainstream databases are also listed in the last article. So, this time we will introduce the relevant knowledge of MySQL database. Database introduction: https://blog.51cto.com/14557673/2463928

II. MySQL introduction 2.1MySQL Overview

​ MySQL database is a kind of relational database. It is a popular open source relational database. It is a product of Oracle company. It complies with GPL agreement and can be used and modified free of charge.

2.2MySQL database is characterized by excellent performance, stable service, open source, no copyright restrictions, low cost, multi-threading and multi-user support, client / server based architecture, secure and reliable 2.3MySQL version.

​ MySQL is divided into the commercial version and the community version, in which the community version can be used for free, while the commercial version is developed and maintained by MySQL AB and needs to be used for a fee.

Third, MySQL manual compilation and installation process (example)

Manual compilation and installation of MySQL has been installed before building LAMP (MySQL5.6) and LNMP (MySQL5.7+boost) architectures, this time we manually compile and install the complete version 5.7 is actually similar to what we did when LNMP was built.

LNMP architecture building

1. Install the necessary tools such as environment packages and compilers

Resource links: https://pan.baidu.com/s/1mkqaEcxrXkTKi7Klq--JXw

Extraction code: i4ak

Version 5.7 is installed

[root@localhost mysql] # lsboost_1_59_0.tar.gz mysql-5.7.17.tar.gz [root@localhost mysql] # yum install-y gcc gcc-c++ ncurses ncurses-devel bison cmake

2. Create mysql users

[root@localhost mysql] # useradd-s / sbin/nologin mysql

3. Decompress

[root@localhost mysql] # tar zxf mysql-5.7.17.tar.gz-C / opt/ [root@localhost mysql] # tar zxf boost_1_59_0.tar.gz-C / usr/local [root@localhost mysql] # cd / usr/local [root@localhost local] # mv boost_1_59_0/ boost/ [root@localhost local] # cd / opt/mysql-5.7.17

4. Configure relevant parameters

[root@localhost mysql-5.7.17] # cmake\-DCMKAE_INSTALL_PREFIX=/usr/local/mysql\-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock\-DSYSCONFDIR=/etc\-DSYSTEMD_PID_DIR=/usr/local/mysql\-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_ARCHIVE_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITH_PERFSCHEMA_STORAGE_ENGINE=1\-DMYSQL_DATADIR=/usr/ Local/mysql/data\-DWITH_BOOST=/usr/local/boost\-DWITH_SYSTEMD=1...# omits the configuration process

5. Compilation and installation

[root@localhost mysql-5.7.17] # make... # omit the compilation process [root@localhost mysql-5.7.17] # make install... # omit the installation process

6. Set up owners and groups

[root@localhost mysql-5.7.17] # cd / usr/local/ [root@localhost local] # chown-R mysql.mysql mysql/

7. Modify the configuration file

[root@localhost local] # vim / etc/my.cnf [root@localhost local] # cat / etc/ my.cnf [client] port = 3306default-character-set=utf8socket = / usr/local/mysql/ mysql.sock [MySQL] port = 3306default-character-set=utf8socket = / usr/local/mysql/ mysql.sock [mysqld] user = mysqlbasedir = / usr/local/mysqldatadir = / usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = / usr/local/mysql/mysqld.pidsocket = / usr/local/mysql/mysql .sockserver-id = 1sql_mode=NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

8. Set environment variables

[root@localhost local] # echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' > > / etc/profile [root@localhost local] # echo' export PATH' > > / etc/profile [root@localhost local] # source / etc/profile

9. Initialize the database

[root@localhost mysql] # lsbin docs man mysql.sock.lock shareCOPYING include mysqld.pid mysql-test support-filesdata lib mysql.sock README usr [root@localhost mysql] # bin/mysqld\ >-- initialize-insecure\ >-- user=mysql\ >-- basedir=/usr/local/mysql\ >-- datadir=/usr/local/mysql/data...# omits part of the content

10. Service file settings to facilitate systemctl management

[root@localhost mysql] # cp usr/lib/systemd/system/mysqld.service / lib/systemd/system/

11. Enable the service and set boot self-startup.

[root@localhost mysql] # systemctl start mysqld.service [root@localhost mysql] # netstat-antp | grep 3306tcp6 0 0:: 3306: * LISTEN 13105/mysqld [root@localhost mysql] # systemctl enable mysqld.service Created symlink from / etc/systemd/system/multi-user.target.wants/mysqld.service to / usr/lib/systemd/system/mysqld.service.

12. Set the initialization password and enter mysql to start using.

[root@localhost mysql] # mysqladmin-uroot-p passwordEnter password: New password: Confirm new password: Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. [root@localhost mysql] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 4Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > exitBye [root@localhost mysql] #

These are all the operations of the manual compilation and installation of the mysql5.7 version. The specific meaning and commands can be found in the article just mentioned.

4. Common operation commands of MySQL

​ MySQL database system is also a typical application of MySQL S architecture. Special client software is needed to access MySQL database. In Linux system, the simplest and easy-to-use MySQL client software is its own mysql tool.

4.1 access to the database

​ MySQL operation statements end with a semicolon and can be case-insensitive

​ has described how to log in to the database according to the steps shown above, so how do we change the login password?

4.1.1 set login password and change password mysql > set password=password ('123123') # set the new password Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > exitBye [root@localhost ~] # mysql-u root-pEnter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) # enter the old password invalid [root@localhost ~] # mysql-u root-p # re-login Enter password: # New password succeeded Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 7. # omit part 4.1.2 View database mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4 rows in set (0.00 sec). # 4 databases included by default .1.3 exit database mysql > exitBye [root@localhost ~] # 4.2 use MySQL database

​ here describes the operation statements related to viewing the database and table structure.

4.2.1 View and use the database contained in the server mysql > show databases # View database +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4 rows in set (0.00 sec) mysql > use mysql # use database Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changed4.2.2 to view tables in the database

Command format: show tables

Mysql > show tables +-- + | Tables_in_mysql | +-+ | columns_priv | | db | | engine_cost |. # omit part of the content | time_zone_ Transition_type | | user | +-+ 31 rows in set (0.00 sec) 4.2.3 View the structure of the table

Command format: describe datasheet name

Mysql > describe db +-- +-+ | Field | Type | Null | Key | Default | Extra | +-- + -+ | Host | char (60) | NO | PRI | | Db | char (64) | NO | PRI | | Select_priv | enum ('N') |'Y') | NO | | N | | Insert_priv | enum ('Nickel dagger Y') | NO | | N | | Update_priv | enum (' Noble dagger Y') | NO | | N | | Delete_priv | enum ('N') | |'Y') | NO | | N | | Create_priv | enum ('Nickel dagger Y') | NO | | N | | Drop_priv | enum (' Noble dagger Y') | NO | | N | | Grant_priv | enum ('N') | |'Y') | NO | | N | | References_priv | enum ('Nickel dagger Y') | NO | | N | | Index_priv | enum (' Noble dagger Y') | NO | | N | | Alter_priv | enum ('N') | |'Y') | NO | | N | | Create_tmp_table_priv | enum ('Nickel dagger Y') | NO | | N | | Lock_tables_priv | enum (' Noble dagger Y') | NO | | N | | Create_view_priv | enum ('N') | |'Y') | NO | | N | | Show_view_priv | enum ('Nickel dagger Y') | NO | | N | | Create_routine_priv | enum (' Noble dagger Y') | NO | | N | | Alter_routine_priv | enum ('N') | |'Y') | NO | | N | | Execute_priv | enum ('Nickel dagger Y') | NO | | N | | Event_priv | enum (' Noble dagger Y') | NO | | N | | Trigger_priv | enum ('N') | 'Y') | NO | | N | | +-+-+ 22 rows in set (0.00 sec) 4.3SQL language introduction

The ​ SQL language is a collection of current standard instructions in the database. SQL, and structured query language. It consists of the following parts:

4.3.1 DDL

​ DDL--Data Definition Language, data definition language: used to build databases, database objects and define their columns, such as create, alter, drop.

4.3.2DML

​ DML--Data Manipulation Language, data manipulation language: used to query, insert, delete and modify data in a database, such as select, insert, update, delete.

4.3.3DCL

​ DCL--Date Control Language, data control language: used to control the access permission and access rights of database components, such as commit, rollback, grant, revoke.

4.4 create and delete libraries and tables 4.4.1 create new libraries

Command format: create database library name

Example:

Mysql > create database book;Query OK, 1 row affected (0.00 sec) mysql > show databases-> +-+ | Database | +-+ | information_schema | | book | | mysql | | performance_schema | | sys | +-+ 5 rows in set (0.00 sec) mysql > use book;Database changedmysql > show tables Empty set (0.00 sec) 4.4.2 create a new table

Command format: create table Datasheet name (field 1 name type, field 2 name type,... Primary key (primary key name)

Example:

Mysql > create table tech_book (id int,price decimal, bookname varchar (10), primary key (id)); Query OK, 0 rows affected (0.01 sec) mysql > show tables;+-+ | Tables_in_book | +-+ | tech_book | +-+ 1 row in set (0.00 sec) mysql > 4.4.3 Delete a table

Command format: drop table database name. Table name; or drop table table name

Example:

Mysql > drop table tech_book;Query OK, 0 rows affected (0.01 sec) mysql > show tables;Empty set (0.00 sec) 4.4.4 delete a database

Command format: drop database database name

Case study

Mysql > drop database book;Query OK, 0 rows affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | +-+ 4 rows in set (0.00 sec) mysql > 4.5 manage the data records in the table

First, let's create a new database and a table to introduce the command operations for managing table data records.

Mysql > create database fruit;Query OK, 1 row affected (0.00 sec) mysql > use fruit;Database changedmysql > create table fruit_info (id int (4) not null,price decimal (3) not null,type char (5) not null); Query OK, 0 rows affected (0.01 sec) mysql > show tables +-+ | Tables_in_fruit | +-+ | fruit_info | +-+ 1 row in set (0.00 sec) mysql > desc fruit_info +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -id | int (4) | NO | | NULL | | price | decimal (3) | NO | | NULL | | type | char (5) | NO | | NULL | | +- -+ 3 rows in set (0.01sec) 4.5.1 insert data record

Statement structure: insert into table name (field 1, field 2) values (field 1 value, field 2 value,...)

Example:

Mysql > insert into fruit_info (id,price,type) values; Query OK, 1 row affected (0.00 sec) mysql > insert into fruit_info values; Query OK, 1 row affected (0.00 sec) mysql > insert into fruit_info values (3 Magi 5.5 sec) mysql > insert into fruit_info values; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 04.5.2 query data records

Statement format: select field 1, field 2. From table name where conditional expression

Example:

Mysql > select * from fruit_info # directly view the table information +-+ | id | price | type | +-+ | 1 | 5.50 | apple | 2 | 3.50 | pear | 3 | 5.50 | grape | 4 | 8.00 | peach | +-+ 4 rows in set (0.00 sec) mysql > select id Type from fruit_info where price=5.5 +-+ | id | type | +-+-+ | 1 | apple | | 3 | grape | +-+-- + 2 rows in set (0.00 sec) mysql > select id,type from fruit.fruit_info where price=5.5 +-+-+ | id | type | +-+-+ | 1 | apple | | 3 | grape | +-+-- + 2 rows in set (0.00 sec) 4.5.3 modify data record

Statement format: update table name set field 1 = field value 2 [, field 2 = field value 2.] Where conditional expression

Example:

Mysql > update fruit_info set price = 4.5where id=3;Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select * from fruit_info +-mysql > update fruit_info set price = 4.5 where type='apple' or id = 2. Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0mysql > select * from fruit_info +-+ | id | price | type | +-+ | 1 | 4.50 | apple | 2 | 4.50 | pear | 3 | 4.50 | grape | 4 | 8.00 | peach | +-+ 4 rows in set (0.00 sec) 4.5.4 Delete data record

Statement format: delete from table name where conditional expression

Example:

Mysql > delete from fruit_info where type='apple';Query OK, 1 row affected (0.01sec) mysql > select * from fruit_info +-+ | id | price | type | +-+ | 2 | 4.50 | pear | | 3 | 4.50 | grape | | 4 | 8.00 | peach | +-+ 3 rows in set (0.00 sec) mysql > delete from fruit_info where price=4.5 and type='pear' Query OK, 1 row affected mysql > select * from fruit_info;+----+ | id | price | type | +-- + | 3 | 4.50 | grape | | 4 | 8.00 | peach | +-+ 2 rows in set (0.00 sec) mysql > delete from fruit_info where price=4.5 or type='peach' Query OK, 2 rows affected (0.00 sec) mysql > select * from fruit_info;Empty set (0.00 sec) 5. Summary of this article

​ this article mainly demonstrates the whole process of manual compilation and installation of mysql5.7 version, and introduces the common commands related to mysql database, the format of SQL language and related examples. Its basic management mainly includes looking at the database structure, creating and deleting databases and tables, and managing the records of data tables (add, delete, modify and search operations-insert, delete, update, select).

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.