Common Operation 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 Running File Path (Folder Address)
usr/bin/mysql refers to the running path of mysql
var/lib/mysql refers to the storage path of mysql database files
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 Start MySQL
# service mysqld restart Restart MySQL
# service mysqld stop Stop MySQL

2. Enter the MySQL form action

#-u root-p/mysql-h localhost-u root-p DatabaseName; Enter MySQL
MySQL > show databases; List databases
MySQL > create database XXX; Create database XXX

MySQL > use databaseName; Using the database databaseName
MySQL > show tables; List forms

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 a form
MySQL > drop table mytablename; Delete a form
MySQL > drop database databasename; Delete a database

3. Add, delete and check

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

MySQL > select * from mytablename; Find validation results
MySQL > select * from mytablename where ID = '1'; Accurate search

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

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

Add universal users

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


User name user_1 password is 123456

You can log in from any PC for this user 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";

Login database library

MySQL> -u user_1 -p -h IP地址;


In addition, I attach some commonly used commands, which are listed under 1 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 in the TestDB database > create table customers(userid int not null, username varchar(20) not null);
(4) Display database list mysql > show databases;
(5) Display the table mysql in the database > show tables;
(6) Delete table customers mysql > drop table customers;
(7) Showing the structure of the customers table mysql > desc customers;
(8) Insert a record mysql into the customers table > insert into customers(userid, username) values(1, 'hujiahui');
(9) Let the operation take effect in time; mysql > commit;
(10) Query the record mysql in customers > select * from customers;
(11) Update the data in the table mysql > update customers set username='DennisHu' where userid=1;
(12) Delete the record mysql in the table > delete from customers;
(13) Grant likui users access to the database # grant select, insert, update, delete on *. * to likui @ localhost indentified by "123456";