Useful MySql Commands

Here Are A List of Useful SQL Commands for Mysql Database

start mysql:
mysql
or mysql -p

show databases;
use mysql;
show tables from mysql;
show columns from user;
describe user;
show columns from db;

mysqladmin ping
mysql -u username -p
create database sample_db;
use sample_db;
drop database sample_db;

mysqladmin create sample_db
mysqladmin drop sample_db

insert into host values('localhost', 'sample_db', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y')
insert into user values ('localhost', 'testuser', password('pass123'), 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y')
insert into db values ('localhost', 'sample_db', 'testuser', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y')

create table Customers (Customer_ID INT Not Null Primary Key AUTO_INCREMENT, First_Name VARCHAR(20) Not Null, Last_Name VARCHAR(30) Not Null, Address VARCHAR(50) , City VARCHAR(20) , State VARCHAR(2) , Zip VARCHAR(20) , E_mail VARCHAR(20) , Age INT, Race VARCHAR(20) , Gender ENUM('M', 'F') DEFAULT 'F', Eye_Color VARCHAR(10) , Hair_Color VARCHAR(10) , Favourite_Activity ENUM('Programming', 'Eating', 'Biking', 'Running', 'None') DEFAULT 'None', Favourite_Movie VARCHAR(50), Occupation VARCHAR(30), Smoker Char(0));

create temporary table temp select * from Customers;

create table perm select * from Customers;

create table if not exists perm2 (num int);

create database temp;
use database temp;
create table test_table
(Test_ID int Not Null Primary Key AUTO_INCREMENT,
Test_Name Varchar(30),
Test_Date DateTime,
Test_Giver Varchar(30));
Insert into test_table
(Test_ID, Test_Name, Test_Date, Test_Giver)
VALUES
(Null, 'test', '2003-01-01', 'Me');

Alter table customers
change First_Name FirstName Varchar(20);
describe customers;

Alter table customers
change LastName Last_Name Varchar(50);
describe customers;

Alter table customers rename customer_table;
show tables from meet_a_greek;

drop table perm2;

Alter table customer_table drop Last_Name;

Alter table customer_table add Last_Name varchar(30);

Alter table customer_table add index ( Last_Name) ;

Alter table customer_table drop index Last_Name;

drop index Last_Name on customer_table;

alter table customer_table drop primary key;

alter table customer_table add primary key(customer_id);

create table test (auto_test int(8) zerofill not null Primary Key auto_increment);
insert into test (auto_test) values (null);
insert into test (auto_test) values (0);

create table test
(auto_test int not null Primary Key auto_increment auto_increment = 9000,
column2 int);

create table test2 (fixed_col char(5), var_col varchar(15));

create table test3 (var_col varchar(3));

create table test4 (advertiser set('web page', 'tv', 'newspaper'));

insert into test4 (advertiser) values('web page, newspaper');

select * from test4 where advertiser = 1;

select advertiser, advertiser +0 from test4;

load data local infile "test2.txt" into table test2;

load data local infile "test2.txt" replace into table test2;

load data local infile "test2.txt" replace into table test2 fields terminated by ',' enclosed by '"';

mysqldump meet_a_greek > outfile1.txt

mysqldump meet_a_greek test2 > outfile2.txt

select * into outfile 'outfile4.txt'
from customer_table;