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

Foreword:

No matter what industry you are engaged in, you may have heard the term database. In today's era of rapid development of the Internet, the database is closely related to our lives. For example, you need to enter the information of registered employees in the company into the database. The database is not so profound and difficult to understand. The database I understand is that it is a kind of excel table program, but it is better than excel. This article will give you a brief description of the basic operations of the MySQL database.

Introduction to MySQL:

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

Relational Beauty [r'lenl]

Database Beauty [detbes]

Management Beauty [mndmnt]

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.

Dual licensing policy: open source version and commercial version. For example, many companies sell applications such as Microsoft, Apple, and Oracle;, such as Red Hat and IBM, and make money by providing sales support, hosting, or consulting services for their open source software. But little is known about the fact that companies can release their software through open source or sell commercial versions of the software at the same time.

Hello, dear reader: you have entered the practical operation area--

Before installing the software, the author has a habit of looking at the software description information, which will help us to understand the use of the software and go to the official website to find the solution to the problem because the official website has the QA of most software.

Yum info mysql-server

Name: package name mysq-server

Arch: system architecture X86room64

Version: package version 5.1.73

Release: publisher version number 7.e16

Size: the package size is 25m

Repo: this package comes from the base repository

Summary: software Overview this is a file about the MySQL service

URL: official website address http://www.mysql.com

License: licensing is subject to the GPL v2 license agreement

Description: detailed description

MySQL is a multi-user, multi-threaded SQL database server. MySQL is a client / server composed of server daemons (mysqld) to do the work, as well as many different libraries and client programs. This software includes the MySQL server and some accompanying file directories.

Yum info mysql / / this is the description of viewing the software information and faces of the MySQL client, which is more or less the same. readers can translate it themselves and will not introduce it any more.

-- the above is my habit of working and studying.

Install softwar

Yum install mysql-server mysql / / the environment used in this article is the Centos6.8 system mysql-server / / MySQL server mysql / / MySQL client

* the installation process is brief *

In order to avoid all kinds of strange mistakes, please turn off the firewall and selinux first.

Service iptables stop / / turn off the firewall service this time chkconfig iptables off / / turn off the firewall permanently next time setenforce 0 / / temporarily close SELinuxgetenforce / / check the current status of selinux Disabled / / closed Permissive / / closed Enforcing / / enable sed-I'/ SELINUX/s/enforcing/disabled/' / etc/selinux/config / / turn off selinux permanently next time

Start the MySQL service

Service mysqld startStarting mysqld: [OK]

Hardening the mysql server and running the mysql_secure_installation command after the installation is complete

Mysql_secure_installationEnter current password for root (enter for none): directly enter the current root (where the root is the database administrator name has nothing to do with the system) the user's password defaults to empty direct enter Set root password? [root] y set password for root user New password: set password for root user Re-enter new password: enter the password you set again Remove anonymous users? Do you want to delete anonymous users? it is recommended to delete anonymous users in production environment, so enter Disallow root login remotely directly? Do you want to turn off root remote login, select Yzone and enter according to your own needs, and it is recommended to prohibit Remove test database and access to it? Do you want to delete the test library of test, so enter y or enter Reload privilege tables now directly? Whether to reload the authorization form immediately, enter y or enter directly and now you can use mysql-h 127.0.0.1-uroot-p to access your database.

Common options:

-u,-- user=username: user name. Default is root.

-h,-- host=hostname: the address of the remote host (that is, the mysql server). By default, the localhost; client connects to the server. The server will reverse the customer's IP hostname and disable this function (skip_name_resolve=ON).

-p,-- password [= PASSWORD]: password of the user represented by USERNAME; default is empty

Note: the user account of mysql consists of two parts:

'USERNAME'@'HOST'

HOST is used to limit the remote hosts through which this user can connect to the current mysql service; the representation of HOST, which supports the use of wildcards

%: matches any character of any length

172.16%.%, 172.16.0.0Univer 16

_: match any single character

-P,-- the port that the port=#:mysql server listens to; default is 3306/tcp

-S,-- socket=/PATH/TO/mysql.sock: socket file path

-D,-- database=DB_name: after connecting to the server, set the database specified there as the default database

-e,-- execute='SQL STATEMENT': connects to the server and asks it to execute this command and returns directly

System management

Connect MySQL

Command format: mysql-h host address-u user name-p user password

Example 1: connect the local host

[root@node2] # mysql-uroot-p

Example 2: connect to the remote host library

[root@node2] # mysql-h 127.0.0.1-uroot-p111111

Add new users

Command format: grant select on database. * to user name @ login host identified by 'password'

For example:

Example 1: add a user's dev1 password to dev123 so that he can log in on any host and have

Permissions to query, insert, modify, and delete.

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

Mysql > grant select,insert,update,delete on *. * to dev1@localhost identified by'dev123'; or grant all privileges on *. * to dev1@localhost identified by'dev123'

Then refresh the permission settings.

Flush privileges

Example 2: if you do not want root to have a password to manipulate the data table in the database "class", you can issue another command to eliminate the password.

Grant select,insert,update,delete on class.* to root@localhost identified by''

Delete user

Command format: DROP USER 'username'@'host'

For example:

Mysql > drop user 'dev1'@'localhost';mysql > flush privileges

Delete the user's database

Mysql > drop database dbname

Library operation

Show all databases

Mysql > show databases; (Note: there is an s at the end)

Create a database

Mysql > create database newdb

Delete database

Mysql > drop database newdb

Connect to the database

Mysql > use newdb; can also log in to specify which library to use mysql-h 127.0.0.1-uroot-p newdb; (Note:-p followed by a space followed by the name of the library you want to use)

View the database currently in use

Mysql > select database ()

Table information contained in the current database

Mysql > show tables; (Note: there is an s at the end)

Table operation

Note: you should connect to a database using the use database name before the operation.

Create a tabl

Command format: create table table name (field name I type I field name II type II)

Example:

Mysql > create table dev (id int (4) not null primary key auto_increment, > name char (20) not null,sex int (4) not null default'0, > joinyear DATE not null)

Get table structure

Command format: desc table name, or show columns from table name

Ex.: check out the dev table we just created

Mysql > desc dev; or mysql > show columns from dev

Delete tabl

Command format: drop table

For example: delete a table named m23info

Mysql > drop table m23info

Insert data

Command format: insert into VALUES ('setting value 1' setting value 2 'setting value' setting value 3 'setting value 4')

Example:

Mysql > INSERT INTO dev VALUES ('2, 12-25, 2012-12-25)

Query the data in the table

Query all rows

Mysql > select * from dev

Query the first few rows of data

For example: view the first two rows of data in table dev

Mysql > select * from dev order by id limit 0Pol 2

Or

Mysql > select * from dev limit 0Pol 2

Delete data from the table

Command format: delete from table name where expression

Example: delete the record numbered 1 in table dev

Mysql > delete from dev where id=1

It is obvious that the record with id 1 has been deleted.

Modify the data in the table

Command: update table name set field = new value,... Where condition

Mysql > update dev set name='Boss' where id=2

Changed the jeck with the original ID number 2 to Boss.

Add fields to the table

Command format: alter table table name add field type other

Example: add a field to the table dev, Duties type is int (5), default value is 0

Mysql > alter table dev add Duties int (5) default'0'

Change the table name

Command format: rename table original table name to new table name

For example: change the name of dev to opt in the table

Mysql > rename table dev to opt

Database import and export

Export a database file from a database

Use the "mysqldump" command

1) Export newdb database

Command format: mysqldump-u [database user name]-p-A > [save path of backup file]

The mysqldump-uroot-p password newdb > / sqlbackup/ newdb`date +% Fashi% T`.sql / / backup newdb to / sqlbackup/ is named newdb+ timestamp. sql

2) Export data and data structures

Format: mysqldump-u [database user name]-p [database name to be backed up] > [save path of backup file]

For example:

Example 1: export the database mydb to mysqldb.sql under / sqlbackup.

Mysqldump-h localhost-uroot-p mysql > / sqlbackup/mysqldb.sql

Then enter the password and wait for a while for the export to be successful. You can check whether it is successful in the target file.

3) only export data, not data structure

Format:

Mysqldump-u [database user name]-p-t [database name to be backed up] > [save path of backup file]

4) Export the Events in the database

Format: mysqldump-u [database user name]-p-E [database user name] > [save path of backup file]

5) Export stored procedures and functions in the database

Format: mysqldump-u [database user name]-p-R [database user name] > [save path of backup file]

Import from an external file into the database

1) use the "source" command

First go to the "mysql" command console, then create the database, and then use it. Finally, do the following.

Mysql > source [path to save backup files]

2) use "

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.