Mention useful MySQL Commands.

Back
To login (from unix shell) use -h only if needed.
# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.
create database [database_name];

Display list all databases on the sql server.
show databases;

Switch to a database (db).
use [database_name];

Display all the tables in the database (db).
show tables;

To see database's field formats.
describe [table_name];

To delete a database (db).
drop database [database_ name];

To delete a table.
drop table [table_name];

To delete a row(s) from a table.
Delete  from [table_name] where [field_name] = 'whatever';

To delete a column.
alter table [table_name] drop column [column_name];

Show all data in a table.
select * from [table_name];

Returns the columns and column information pertaining to the designated table.
show columns from [table_name];

Display selected rows with the value "quizplaza".
select * from [table_name] where [field_name] = "quizplaza";

Display all records containing the name "Serge" and the house number '121212'.
Select * from [table_name] where name = "Serge" AND house_number = '121212';

Display all records not containing the name "Serge" and the house number '121212' order by the house_number field.
select * from [table_name] where name != "Serge" AND house_number = 121212 order by house_number;

Display all records starting with the letters 'serge' AND the house number 121212.
select * from [table_name] where name like "serge%" AND house_number = 121212;

Show all records starting with the letters 'serge' AND the house number '121212' limit to records 1 through 5.
select * from [table_name] where  name like "serege%" AND house_number = '121212' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
select * from [table_name] where rec RLIKE "^a";

Show unique records from a table.
select DISTINCT [column_name] from [table_name];

Show selected records sorted in an ascending or descending order.
select [column1],[column2] from  [table_name] order by [column2] desc;

Return number of rows from a table.
select COUNT(*) from [table_name];

Sum column.
select SUM(*) from [table_name];

To update database permissions or privilages.
flush privileges;

Add a new column to a table.
alter table [table_name] add column [new column_name] varchar (20);

Change a column name.
alter table [table_name] change [old_column_name] [new_column_name] varchar (50);

Make a unique column.
alter table [table_name] add unique ([column_name]);

Change the size of column.
alter table [table_name] modify [column_name] varchar(3);

Delete unique from table.
alter table [table_name] drop index [colmn_name];

Dump one database (db) for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword --databases database_name >/tmp/database_name.sql

Dump all databases for backup.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt
/tmp/alldatabases.sql

Dump a table from a database.
# [mysql dir]/bin/mysqldump -c -u username -ppassword database_name table_name
 /tmp/database_name.table_name.sql

To restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword database_name  
/tmp/database_name.sql

Create table.
creat table [table_name] (firstname VARCHAR(100), middleinitial VARCHAR(50), lastname varchar (80),suffix varchar (3),officeid varchar (10), id varchar(15),username VARCHAR(8),email varchar (100),phone varchar (25), groups varchar (15),datestamp DATE,timestamp time,pgpemail varchar (255));
OR
create table [table_name] (id int(50) not null auto_increment primary key,firstname varchar(100),middlename varchar(50),lastname varchar(100));

Command to creating a new user, login as root, switch to the MySQL database, make the user and update privs.
# mysql -u root -p
use mysql;
insert into user (HOST,USER,PASSWORD)VALUES('%','username',PASSWORD('password'));
flush privileges;

Command to change a users password from MySQL prompt, login as root, set the password and update privs.
# mysql -u root -p
set PASSWORD for  'user'@'hostname' = PASSWORD('newpassword');
flush privileges;

Command to recover a MySQL root password, stop the MySQL server process, restart with no grant tables, login to MySQL as root, set new password, exit MySQL and restart MySQL server.
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("new_root_password") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

To set a root password if there is on root password.
# mysqladmin -u root password new_password

Update a root password.
# mysqladmin -u root -p old_password new_password

Allow the user "serge" to connect to the server from localhost using the password "sergepassword", login as root, switch to the MySQL database, give privs and update privs.
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to serge@localhost identified by sergepassword;
mysql> flush privileges;


Share your requirement with me and give your business a much needed thrust.
CONTACT HERE