Usage instructions for CentOS-based Mysql

Usage instructions for CentOS-based Mysql

This article mainly records some commands and operations on Mysql under my CentOS system.

1. Check whether mysql is installed.

# Rpm-qa | grep mysql

 

2. Check whether the mysqld service is enabled.

# Service mysqld status

 

3. Start the mysqld service

# Service mysqld start

The first startup will be initialized, and the time will be a little longer...

4. Set the User root Password

#/Usr/bin/mysqladmin-u root password 'dragonwak'

 

5. Connect to the database locally

# Mysql-u root-pdragonwake

 

6. display all databases

Mysql> show databases;

 

7. Use the mysql database

Mysql> use mysql;

 

8. display all tables in the current database (mysql)

Mysql> show tables;

 

9. view the table (mysql. user) Structure

Mysql> describe user;

There are other methods:

A) mysql> desc user;

B) mysql> show columns from user;

C) mysql> show create tables user;

10 Add a mysql user

Mysql> insert into mysql. user (Host, User, password) values ('localhost', 'mysql', password ('mysql '));

Refresh system permission list

Mysql> flush privileges;

The host is 'localhost', indicating that you can only log on locally. to log on remotely, change the host to '% '.

 

11 create a database smartDB

Mysql> create database smartDB;

 

12. Authorize a mysql user to have all permissions for the database smartDB (all permissions for a database)

Mysql> grant all privileges on smartDB. * to mysql @ localhost identified by 'mysql ';

Refresh system permission list

Mysql> flush privileges;

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

 

13. Disconnect

Mysql> quit;

A) mysql> exit;

 

14 log on with a mysql user

# Mysql-u mysql-pmysql

It is the same as the above root User Logon method.

 

15 create the table p2p_tb_camera of the smartDB Database

Switch to the 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 (16) not null,

Entid varchar (20) not null,

Enc varchar (30) not null

);

 

Show all tables under the selected database smartDB

Mysql> show tables;

Displays the structure of the p2p_tb_camera table.

Mysql> desc p2p_tb_camera;

 

17. insert data

Mysql> insert p2p_tb_camera values ('20140901', '01ae1_d08141280 ', '192 _ e3575b

18208b ');

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

Mysql> insert p2p_tb_camera (ipc_id) values ('20140901 ');

In fact, there is no way to insert data into the table because the table limits the value of sn, entid, and enc to be non-empty.

 

18. query data

Mysql> select * from p2p_tb_camera;

 

19. Update Data

In the p2p_tb_camera table, the sn value of the field is 111, and the update condition is ipc_id value 758871 and entid value 1.

Mysql> update p2p_tb_camera set sn = '000000' where ipc_id = '000000' and entid = '1 ';

Query updated data

Mysql> select * from p2p_tb_camera;

 

20. delete data

Delete the data records in the p2p_tb_camera table. The deletion conditions are ipc_id value 758871 and sn value 111.

Mysql> delete from p2p_tb_camera where ipc_id = '000000' and sn = '000000 ';

Query updated data

Mysql> select * from p2p_tb_camera;

Table p2p_tb_camera does not contain any data

 

21. delete a table

Delete p2p_tb_camera

Mysql> drop table p2p_tb_camera;

Queries the table after the table is deleted in the current database smartDB.

Mysql> show tables;

After the p2p_tb_camera table is deleted, the smartDB database does not have a table.

 

22. Execute the SQL script

Content of 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 (16) not null,

Entid varchar (20) not null,

Enc varchar (30) not null

);

Run the script/opt/smartcare/p2pserver/tools/mysql/create_p2p_tb_camera. SQL

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

 

23 delete a database

Delete database smartDB

Mysql> drop database smartDB;

 

24. Change the mysql user password

Change the mysql password to dragonwake.

Mysql> update mysql. user ser password-password ('dragonwake ') where User = 'mysql ';

 

25. delete a user

Delete user mysql

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

 

26. Delete User Permissions

Delete the user's mysql permission

Mysql> drop user mysql @ localhost;