MYSQL COMMANDS

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

***************************************************************
2)Create a database on the sql server.
—-> create database [databasename];
—->eg :- create database college;

****************************************************************
3)List all databases on the sql server.
—-> show databases;

*****************************************************************
4)Switch to a database.
—–> use [databasename];

*****************************************************************
5)To see all the tables in the database.
——->show tables

******************************************************************
6) To see database’s field formats.
—–> desc [table name];

*******************************************************************
7)To delete a db.
—> drop database [database name];
eg:- drop database college;

******************************************************************
8)To create a table.
—–>CREATE table tablename(field_one datatype(size),fiels_two datatype(size));

eg1:-mysql> CREATE TABLE student( id INT(20),name VARCHAR(100));
Query OK, 0 rows affected (0.03 sec)

eg:-2 mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
*******************************************************************
9)Creating a table with auto_increment

—> CREATE TABLE tablename(field_one datatype(size) NOT NULL AUTO_INCREMENT PRIMARY KEY , field_two datatype(size));

eg:-mysql> CREATE TABLE student (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) );
Query OK, 0 rows affected (0.01 sec)

*******************************************************************
10)Creating a table with the current timestamp
—> CREATE TABLE student ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100), cur_timestamp TIMESTAMP(7) );

*******************************************************************
11Creating a table with TIMESTAMP DEFAULT NOW()

—> CREATE TABLE student (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100), created TIMESTAMP DEFAULT NOW());

*******************************************************************
12)Viewing a table definition
—-> DESC tablename;

*********************************************************************
13)To delete a table.
—–> drop table [table name];
eg) drop table student;

*********************************************************************
14)INSERT values into table
—–>INSERT INTO [tablename] (filed_one,field_two,feild_three) values(value1,value2,value3);

eg:-INSERT INTO student (id,name,branch) values(1,’Amit’,'computer science’);

*******************************************************************
15)Change a users password.(from unix shell).
—->mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password ‘new-password’

******************************************************************
16)Change a users password.(from MySQL prompt).
—-> SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);

******************************************************************
17)GRANT privileges
—–>grant all privileges on [databasename].* to user@’localhost’ identified by ‘passwd’;

******************************************************************
18)GRANT specific privileges
—-> GRANT SELECT ON databasename.* TO username@’localhost’ IDENTIFIED BY ‘password’;

To enable more options you would separate them with a comma. So to enable SELECT, INSERT, and DELETE your syntax would look like this;

—–>GRANT SELECT, INSERT, DELETE ON database.* TO username@’localhost’ IDENTIFIED BY ‘password’;

Once you have given the desired privileges for your user, you will need to run this command within the MySQL command prompt;

—–>FLUSH PRIVILEGES;

To see a list of the privileges that have been granted to a specific user;

—–> select * from MySQL.user where User=’user’ \G

This is a list of privileges that you can grant;

Privilege	Meaning
ALL [PRIVILEGES]	Sets all simple privileges except GRANT OPTION
ALTER	Enables use of ALTER TABLE
CREATE	Enables use of CREATE TABLE
CREATE TEMPORARY TABLES	Enables use of CREATE TEMPORARY TABLE
DELETE	Enables use of DELETE
DROP	Enables use of DROP TABLE
EXECUTE	Not implemented
FILE	Enables use of SELECT … INTO OUTFILE and LOAD DATA INFILE
INDEX	Enables use of CREATE INDEX and DROP INDEX
INSERT	Enables use of INSERT
LOCK TABLES	Enables use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS	Enables the user to see all processes with SHOW PROCESSLIST
REFERENCES	Not implemented
RELOAD	Enables use of FLUSH
REPLICATION CLIENT	Enables the user to ask where slave or master servers are
REPLICATION SLAVE	Needed for replication slaves (to read binary log events from the master)
SELECT	Enables use of SELECT
SHOW DATABASES	SHOW DATABASES shows all databases
SHUTDOWN	Enables use of MySQLadmin shutdown
SUPER	Enables use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL statements, the MySQLadmin debug command; allows you to connect (once) even if max_connections is reached
UPDATE	Enables use of UPDATE
USAGE	Synonym for “no privileges”
GRANT OPTION	Enables privileges to be granted

********************************************************************
19)To update info already in a table.
—-> Update table tablename SET fieldname=newvalue where fieldname=oldvalue;
eg1:- Update table student set id=2 where id=2;
eg2:- Update table student set name=’Arun’ where name=’Amit’;

********************************************************************
20) Modify an existing field in a table
—-> update table table name MODIFY fieldname(new size);
eg:- Update table tablename MODIFY id(30);

********************************************************************
21)Delete a row(s) from a table.
—> DELETE from [table name] where [field name] = ‘value’;

********************************************************************
22)Delete a column.
—->alter table [table name] drop column [column name];

*******************************************************************
23)Add a new column to db.
—–> alter table [table name] add column [new column name] varchar (20);

***************************************************************
24)Change column name.
—-> alter table [table name] change [old column name] [new column name] varchar (50);

*****************************************************************
25)Make a unique column so you get no dupes.
—-> alter table [table name] add unique ([column name]);

******************************************************************
26)Make a column bigger.
—-> alter table [table name] modify [column name] VARCHAR(3);

*****************************************************************
27)Select values from a table

To see alla values of a table use

SELECT * from tablename;

To select specific values from a table use

SELECT fieldname_one,fieldname_two from tablename;

To select fields based on condition

SELECT fieldname_one,fieldname_two from tablename where [condition];

eg:-SELECT id from student where name=’Arun’;

28 ) Dump a table from a database

[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

29)Restore database (or database table) from backup.
[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql