MySQL Commands

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.

mysql> create database [databasename];

3.List all databases on the sql server.

mysql> show databases;

4.Switch to a database.

mysql> use [db name];

5.To see all the tables in the db.

mysql> show tables;

6.To see database's field formats.

mysql> describe [table name];

7.To delete a db.

mysql> drop database [database name];

8.To delete a table.

mysql> drop table [table name];

9.Show all data in a table.

mysql> SELECT * FROM [table name];

10.Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

11.Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

12. Update database permissions/privilages.

mysql> flush privileges;

13.Delete a column.

mysql> alter table [table name] drop column [column name];

14.Describe the fields (columns) of the "products" table.

mysql> DESCRIBE products;

15.create a new MySQL table:

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1),
signup_date DATE);