Common operating commands of MySQL in Linux system

Services:

# chkconfig-list lists all system services

# chkconfig-- list | grep on lists all started system services

# chkconfig-list mysqld

# whereis mysql View File installation path

# which mysql query the path where the running file is located (folder address)

Usr/bin/mysql refers to the running path of mysql

Var/lib/mysql refers to the path where mysql database files are stored.

Usr/lib/mysql refers to the installation path of mysql

Add environment variables:

# vi / etc/profile

# export MYSQL_HOME=/usr/local/mysql

# export PATH=$PATH:$MYSQL_HOME/bin

1. Database directives:

# service mysqld start starts MySQL

# service mysqld restart restart MySQL

# service mysqld stop stop MySQL

two。 Enter the MySQL form action

#-u root-p / mysql-h localhost-u root-p DatabaseName; enter MySQL

MySQL > show databases; lists databases

MySQL > create database XXX; create database XXX

MySQL > use databaseName; using database databaseName

MySQL > show tables; list form

MySQL > create table mytablename (ID int auto_increment not null primary key,usename varchar (20), password varchar (64), sex varchar (10), address varchar (20); create forms

MySQL > drop table mytablename; delete the form

MySQL > drop database databasename; delete database

3. Add, delete, modify and check

MySQL > insert into mytablename values (', 'zhangsan','123456','fomale','guiyanag'); insert

MySQL > select * from mytablename; find the verification result

MySQL > select * from mytablename where ID = '1regions; precise search

MySQL > update mytablename set address = 'shanghai' where username =' zhangsan'; modify the address of zhangsan to shanghai

MySQL > delete from mytablename where ID = '1records; Delete records

Add universal users

Grant select On database.* to username@localhost identity by 'password'

User name user_1 password is 123456

You can log in to this user from any PC to operate on the database

MySQL > grant select,insert update,delete on *. * to user_1@ "%" identity by "123456"

Create a user who can operate the database only locally

User name user_2 password is 123456

MySQL > grant select,insert update,delete on *. * to user_2@localhost identity by "123456"

Log in to the database

MySQL >-u user_1-p-h IP address

In addition, some commonly used commands are attached. I will list them for reference only:

Other mysql database-related operations are as follows

(1) create database TestDB mysql > create database TestDB

(2) make the TestDB database as the current default database mysql > use TestDB

(3) create the table customers mysql > create table customers (userid int not null, username varchar (20) not null) in the TestDB database

(4) display database list mysql > show databases

(5) display the table mysql > show tables in the database

(6) Delete table customers mysql > drop table customers

(7) display the structure of customers table mysql > desc customers

(8) insert a record mysql > insert into customers (userid, username) values (1, 'hujiahui') into the customers table

(9) make the operation take effect in time; mysql > commit

(10) query records in customers mysql > select * from customers

(11) update the data in the table mysql > update customers set username='DennisHu' where userid=1

(12) delete records in the table mysql > delete from customers

(13) Grant likui users access to the database # grant select, insert, update, delete on *. * to likui@localhost indentified by "123456"