Usage instructions for CentOS-based MySQL

Please state the source:

This article is a record of some of the commands and operations used by people to use MySQL under the CentOS system.

1 Check that you have installed MySQL

#rpm –qa | grep MySQL

2 Check mysqld whether the service is turned on

#service mysqld Status

3 Start mysqld Service

#service mysqld Start

The first boot will be initialized, the time will be a bit long ...

4 Set up Users Root the password

#/usr/bin/mysqladmin–u root password ' dragonwake '

5 Local Connection Database

#mysql –u Root-pdragonwake

6 Show All Databases

Mysql>show databases;

7 Use MySQL Database

Mysql>use MySQL;

8 Show Current Database (MySQL) all the Tables

Mysql>show tables;

9 View Table (Mysql.user) structure

Mysql>describe user;

There are other ways to do it:

a) Mysql>desc user;

b) mysql>show columns from user;

c) mysql>show create tables user;

Ten Add a MySQL User

Mysql>insert into Mysql.user (Host,user,password) VALUES (' localhost ', ' MySQL ', password (' MySQL '));

Refresh System Permissions Table

Mysql>flush privileges;

Host is ' localhost ', indicating that only log on locally, to Telnet, the host changed to '% '.

One Create a database Smartdb

Mysql>create database Smartdb;

A Authorized MySQL User owned Database Smartdb All permissions (full permissions for a database)

Mysql>grant all privileges in smartdb.* to [e-mail protected] identified by ' MySQL ';

Refresh System Permissions Table

Mysql>flush privileges;

Above is the local authorization @localhost, for non-local authorization @ "%".

- Exit Connection

mysql>quit;

A) mysql>exit;

- Use MySQL User Login

#mysql –u Mysql–pmysql

It is the same way as the root user above.

the Create a database Smartdb the table P2p_tb_camera

Switch to Database Smartdb

Mysql>use Smartdb;

Create a database table P2p_tb_camera

Mysql>create Table P2p_tb_camera (

ipc_id Char (7) NOT null primary key,

SN varchar (+) is not NULL,

Entid varchar () NOT NULL,

ENC varchar (+) NOT NULL

);

Show all tables below the selected database Smartdb

Mysql>show tables;

Show the structure of the table P2p_tb_camera

Mysql>desc P2p_tb_camera;

- Inserting Data

Mysql>insert P2p_tb_camera values (' 758871 ', ' 01ae465d08141280 ', ' 1426822572_e3575b

18208b ');

Of course, the above is written because all the data is inserted, if you want to specify a field to insert data, only insert the value of ipc_id:

Mysql>insert P2p_tb_camera (ipc_id) VALUES (' 123456 ');

In fact, there is no way to insert data into a table because the table limits the value of SN,ENTID,ENC to non-null.

- Querying Data

Mysql>select * from P2p_tb_camera;

+ Update Data

Update the value of the field SN in table P2p_tb_camera to 111, update the value of ipc_id to 758871 and the value of Entid 1

Mysql>update P2p_tb_camera set sn= ' 111 ' where ipc_id= ' 758871 ' and entid= ' 1 ';

Querying for updated data

Mysql>select * from P2p_tb_camera;

- Delete Data

Delete the data record in table P2p_tb_camera, delete the value of ipc_id 758871 and SN value 111

Mysql>delete from P2p_tb_camera where ipc_id= ' 758871 ' and sn= ' 111 ';

Querying for updated data

Mysql>select * from P2p_tb_camera;

There is no data in table P2p_tb_camera

+ Delete a table

Delete Table P2p_tb_camera

Mysql>drop table P2p_tb_camera;

Querying the current database Smartdb table after deleting a table

Mysql>show tables;

After you delete the table P2p_tb_camera, the database smartdb has no tables.

A Execution SQL Script

What the script create_table_p2p_tb_camera.sql:

Use Smartdb;

CREATE TABLE P2p_tb_camera (

ipc_id Char (7) NOT null primary key,

SN varchar (+) is not NULL,

Entid varchar () NOT NULL,

ENC varchar (+) NOT NULL

);

Execute Script/opt/smartcare/p2pserver/tools/mysql/create_p2p_tb_camera.sql

Mysql>source/opt/smartcare/p2pserver/tools/mysql/create_p2p_tb_camera.sql

at Deleting a database

Delete Database Smartdb

Mysql>drop database Smartdb;

- Modify MySQL User Password

Modify the user MySQL password to dragonwake

Mysql>update mysql.user ser password-password (' Dragonwake ') where user= ' MySQL ';

- Delete User

Delete User MySQL

Mysql>delete form mysql.user where user= ' MySQL ';

- Remove user Rights

Delete User MySQL permissions

Mysql>drop user [email protected];

Usage instructions for CentOS-based MySQL