MySQL – Important and Basic Commands To Know

After Installation of MySQL on Linux System, start it by,

# /etc/init.d/mysql restart or # service mysqld start

Default MySQL configuration file my.cnf is located in /etc/ or in /etc/mysql..

# cat /etc/my.cnf

Generally, root user of MySQL does not have password. To assign password,

# mysqladmin -u root password “LinuxArticles#123″

To login to your MySQL database you need to specify the username, “-p” will ask you the password

# mysql -u root -p

After successfully login, you will be in mysql command line interface.

mysql>

Before starting any project or after fresh installation, we required to create database to store data.

mysql> create database LinuxArticles.org;

After creating databases, list all by,

mysql> show databases;

To use particular database from above list,

mysql> use LinuxArticles.org;

To list all tables of selected DB,

mysql> show tables;

To describe the single table name

mysql> describe name_ of__one_table;

For some reason, if you want to delete created database,

mysql> drop database LinuxArticles.org;

To delete Table from selected Database,

mysql> drop database Table_Name;

Create a new User for Database and grant permission,

mysql> grant all on LinuxArticles.org.* to linuxart@localhost identified by ‘UserPassword@123′;

Create a new User for Database and grant permission for select,insert,update,delete,create,drop,index,alter

mysql>grant on select,insert,update,delete,create,drop,index,alter on LinuxArticles.org.*  to linuxart@localhost IDENTIFIED BY 'UserPassword@123';

To take MySQL single Database backup,

# mysqldump -u linuxart -p LinuxArticles.org > Backup_File.sql

For some reason, to restore a MySQL Database backup,

# mysql -u linuxart -p LinuxArticles.org < Backup_File.sql

Backup all the databases:
#mysqldump -u root -pmypasswd --all-databases > /opt/alldatabase.sql

Restore:
#mysql -uroot -pmypasswd < /opt/alldatabases.sql

###################################################################

1. Creating a database
create database DBNAME;

2. Displaying all available databases on the server
show databases;

3. Selection a database for usage
use DBNAME;

4. Creating a table inside the selected database

create table users(
name varchar(30),
password int,
email varchar(30)
);

5. Displaying all tables inside a database
show tables;

6. Getting information about the table (columns, key, NULL values, etc)
describe TABLENAME; or desc TABLENAME;

7. Inserting an entry into a table
insert into users(name, password, email) values('daniel',12345,'daniel@test.com');

8. Deleting an entry from a table
delete from users where name='daniel';

9. Adding a new column
alter table users add facebook varchar(30);

10. Adding a new id column to work as the primary key
alter table users add id int not null auto_increment first, add primary key(id);

11. Changing the default value of a column
alter table users modify column email varchar(30) default 'test@test.com';

12. Updating the value of a column
update users set email='test@test.com' where id=5;

13. Displaying the last 5 rows of a table
select * from users order by id desc limit 5;

14. Disconnecting
quit

###############################################################

I am assuming your MySQL server is on your remote server (i.e., on the hosting account you rent from a hosting company). In that case you need to login to your server (via cPanel for example) and create a database and a user with all privileges for that database (make sure to write down the names and password). After that make sure to add your IP address to the list of IPs allowed to access the MySQL server remotely.

Now you need to download a MySQL client on your computer. If you are using Ubuntu you can do this with the following command:

sudo apt-get/yum install mysql-client

Once the client is installed you can connect to your MySQL server with the following command:

mysql -h HOSTNAME -u USERNAME -p

You’ll be asked to type the password of the user. If the connection is successful you’ll see a welcome message and the mysql prompt.

###############################################################################

IMPORTANT Rules 

Rule:1

Load data from LOCAL file to tablename of database

mysql> load data local infile 'TOTAL_PATH_FILE' into Table_Name;

example

mysql> load data local infile '/home/naveen/Desktop/itss.txt' into itss-table;

Note:DATA in itss.txt file seperate with TAB

Rule:2

7. Inserting an entry into a table
insert into users(name, password, email) values('daniel',12345,'daniel@test.com');




here daniel and daniel@test.com are strings.

strings MUST be enclose with " " or ' '

For Numbers No Need of " " or ' '

###########################################################

Different ways to login to Mysql Server

1)login to 192.168.0.1 remotely

#mysql -h hostname -u root -p
#mysql -h 192.168.0.1 -u root -p 

2)Giving PASSWORD in Command mode

#mysql -u root -pMYPASSWORD

Note: No gap between p and MYPASSWORD

3)If we want to directly login to DATABASE Name as DB1

#mysql -u root -p DB1
#mysql -u root -pMYPASSWORD DB1


################################################################

How to get a list of mysql user accounts ?

SELECT User FROM mysql.user;

Here mysql is a Database user is a table. We can retrive information like this

how do I see which user I am logged in as in MySQL?

SELECT USER();

SELECT CURRENT_USER();

Link-1