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

This article mainly gives you a brief introduction to some knowledge of MySQL in linux. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on some knowledge of MySQL in linux can bring you some practical help.

Introduction of related concepts of MySQL

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently belongs to the products of Oracle. MySQL is one of the most popular relational database management systems. In terms of WEB applications, MySQL is the best RDBMS (relational database management system) application software.

MySQL is a relational database management system in which relational databases store data in different tables instead of all data in one large warehouse, which increases speed and flexibility.

The so-called "relational" can be understood as the concept of "table". The concept of relationship here can be understood as the relationship between people and things in life, which is a general concept. A relational database consists of one or more tables, taking the student as an example. For example, at the beginning of the new semester, when the teacher goes to class for the first time, every student is an unfamiliar face. How to identify every student in the class? apart from facial recognition, the teacher only confirms that a student relies on a student's student number. A student number represents a student, and the student number is not repeated. Only one student can answer a student number at a time, so every student in the class can be identified, so id is the key. Uniquely identify a specific student. However, after confirming the students, it is not enough. Each student has his own name, sex, age and contact information. Although it is impossible to uniquely identify a specific student by relying on this information, it is the information that every student needs. This is the attribute of the student entity, and the attributes cannot rely on each other to deduce the information logically. For example, only knowing the student's gender can not infer the information of the student's age, name, and other attributes according to the gender. If there is a phenomenon that the information of other attributes can be inferred according to a certain attribute except the primary key, it is called data redundancy. It is necessary to merge or delete attributes, so as to achieve reasonable and comprehensive data storage.

A table shown in the figure:

Header (header): the name of each column is the attribute

Row: a collection of data with the same data type

Col: the specific information used by each line to describe a person / thing

Value (value): specific information about the row, each value must be the same as the data type of the column

Key: the method used in a table to identify a particular person / object. The value of the key is unique in the current column.

MariaDB

Representative manufacturers moving from MySQL to MariaDB: Google (September 2013), RedHat (June 2013), Wikipedia (April 2013)

MySQL was bought by Sun for $1 billion in 2008. Michael Widenius, the founder of MySQL, was dissatisfied with the slow pace of the Sun development team and angrily left to form the Open Source Database Alliance, and developed another extended branch version of the existing MySQL program code, that is, the enterprise open source database called Maria Database.

Maria database is like the shadow version of MySQL. Maria database is a branch version of MySQL (branch), not a derivative version (folk), and provides features that are fully compatible with MySQL.

Script implementation of centos 7.3 and centos 6.9 binary installation mariadb

#! / bin/bash

# determine whether the system has installed mariadb database software, remind you if so, and exit the script.

Rpm-Q mariadb > > / dev/null&& echo `rpm-Q Mariadb` is installed & & exit 0

# Note: rpm query can only query software installed with rpm mechanism, but software installed in other ways may not be queried.

# users who create database programs, and specify their working directory and login method

Useradd-d / app/mysqldb-r-m-s/sbin/nologin mysql

# switch back to the root user directory

Cd

# download the mariadb package and extract it to the specified directory / usr/local

Wget ftp://172.17.0.1/pub/Sources/6.x86_64/mariadb/mariadb-5.5.57-linux-x86_64.tar.gz

Tar-xf/root/mariadb-5.5.57-linux-x86_64.tar.gz-C / usr/local/

Cd / usr/local

# create soft links

Ln-s mariadb-5.5.57-linux-x86_64mysql

Cd / usr/local/mysql

Mkdir / etc/mysql/

# sample copy configuration file

Cp support-files/my-huge.cnf/etc/mysql/my.cnf

# use sed to directly edit the sample / etc/mysql/my.cnf to meet your own needs

Sed-I'/\ [mysqld\] / a\ datadir\ =\ / app\ / mysqldb\ ninnodb_file_per_table\\ =\ on\ nskip_name_resolve\\ =\ on'/etc/mysql/my.cnf

# initialize the database

Scripts/mysql_install_db--user=mysql-datadir=/app/mysqldb

# create a service program for mariadb, and set under which runlevels you can start it yourself

Cp support-files/mysql.server/etc/init.d/mysqld

Chkconfig-add mysqld

# determine the system version in order to create different database log files

If [``sed-r's eq. * ([0-9] +)\.. * /\ 1Accord'/ etc/centos- release-eq 7]; then

Mkdir / var/log/mariadb/

Chown mysql / var/log/mariadb/

Systemctl start mysqld

Else

Touch / var/log/mysqld.log

Chown mysql / var/log/mysqld.log

Service mysqld start

Fi

# set environment variables

Echo'PATH=$PATH:/usr/local/mysql/bin' > / etc/profile.d/mysql.sh

. / etc/profile.d/mysql.sh

# run the security initialization script

Echo-e "\ ny\ n123456\ n123456\ ny\ nn\ nn\ ny" | mysql_secure_installation

# run mysql service

Mysql-uroot-p

The basic components of MySQL scripts

Similar to conventional scripting languages, MySQL also has a set of rules for the use of characters, words, and special symbols. MySQL completes the operation on the database by executing a SQL script, which consists of one or more MySQL statements (SQL statements + extension statements), and the script file is generally .sql when saved.

Identifier

Identifiers are used to name objects, such as databases, tables, columns, variables, and so on, for reference elsewhere in the script. The naming convention for MySQL identifiers is a bit cumbersome, and here we use a universal naming convention: identifiers consist of letters, numbers, or underscores (_), and the first character must be a letter or underscore.

Case sensitivity of identifiers depends on the current operating system, which is insensitive under Windows, but for most linux\ unix systems, these identifiers are case-sensitive.

Keywords:

MySQL has many keywords, which are not listed here, but will be learned in learning. It is important to note that these keywords have their own specific meanings and should be avoided as identifiers as far as possible.

Statement:

MySQL statement is the basic unit of MySQL script, each statement can complete a specific operation, it is composed of SQL standard statement + MySQL extension statement.

Function:

MySQL function is used to realize some advanced functions of database operation. These functions are roughly divided into the following categories: string function, mathematical function, date-time function, search function, encryption function, information function.

Data types in MySQL

MySQL has three major data types, namely, number, date / time, and string. Many subtypes are divided in more detail in these three categories:

Numeric type

Integers: tinyint, smallint, mediumint, int, bigint

Floating point numbers: float, double, real, decimal

Date and time: date, time, datetime, timestamp, year

String type

String: char, varchar

Text: tinytext, text, mediumtext, longtext

Binary (can be used to store pictures, music, etc.): tinyblob, blob, mediumblob, longblob

(due to the limited length of articles due to the large number of data types, please check the professional books for specific data types.)

Common basic operations 1. Display database

Show databases

2. Select a database

Use database name

3. Display the tables in the database

Show tables

4. Display the structure of the data table

Describe table name

5. Display the records in the table

SELECT * FROM table name

6. Build a database

Create databse library name

7. Build a table

Create table table name (list of field settings)

Mysql > create table name (- > id int auto_increment not null primary key,-> uname char (8),-> gender char (2),-> birthday date); Query OK, 0 rows affected (0.03 sec) mysql > show tables +-+ | Tables_in_userdb | +-+ | name | +-+ 1 row in set (0.00 sec)

Mysql > describe name +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | id | int (11) | NO | PRI | NULL | auto_increment | | uname | char (8) | YES | | NULL | | gender | char (2) | YES | | NULL | | birthday | date | YES | | NULL | +-| -- +-+ 4 rows in set (0.00 sec)

Note: auto_increment self-increase primary key primary key 8, add record

Insert into name (uname,gender,birthday) values ('Zhang San', 'male', '1971-10-01')

9. Modify the record

Update name setbirthday='1971-01-10 'where uname=' Zhang San'

10. Delete the record

Delete from namewhere uname=' Zhang San'

11. Delete the table

Drop table table name

12. Delete the library

Drop database library name

13. Back up the database

Mysqldump-uroot-p-- opt database name > backup name; / / enter to the library directory

14. Restore

Mysql-u root-p database name grant select,insert,update,delete on *. * to user001@ "%" Identified by "123456"

Example 2. Add a user's user002 password of 123456, so that this user can only log in on localhost, or set a specified IP, and query, insert, modify and delete the database test (localhost refers to the local host, that is, the host where the MySQL database is located).

/ / in this way, even if the user knows the password of user_2, he cannot access the database directly from the Internet, so he can only operate the test library through the MYSQL host.

/ / first use the root user to connect to MySQL, and then type the following command:

Mysql > grant select,insert,update,delete on test.* to user002@localhost identified by "123456"

Note: secondly, you can also modify the table to deal with the user's login mode:

Database: Mysql

Table: User

Modify the value of the Host column in the User table to reality the login entry

This is the end of some knowledge of MySQL in linux. If you want to know other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.