NeilSys

To login;

> mysql -u username -p

Create a database;

> create database [databasename];

List all databases;

> show databases;

Switch to a particular database.

> use [db name];

To see all the tables in the db.

> show tables;

To see tables property/column details;

> describe [table name];

To delete a db;

> drop database [database name];

To delete a table;

> drop table [table 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];

Show unique records.

> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

> SELECT COUNT(*) FROM [table name];

Sum column.

> SELECT SUM(*) FROM [table name];

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p

> use mysql;
> INSERT INTO user (Host,User,Password) VALUES(‘%’,’username’,PASSWORD(‘password’));

> flush privileges;

Change a users password from unix shell.
# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password ‘new-password’

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
> flush privileges;

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
> use mysql;
> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’databasename’,’username’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
> flush privileges;

or

> grant all privileges on databasename.* to username@localhost;
> flush privileges;

To update info already in a table.

> UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;

Delete a row(s) from a table.

> DELETE from [table name] where [field name] = ‘whatever’;

Update database permissions/privilages.

> flush privileges;

Delete a column.

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

Add a new column to db.

> alter table [table name] add column [new column name] varchar (20);

Change column name.

> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

> alter table [table name] add unique ([column name]);

Make a column bigger.

> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’(field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword –-databases databasename >/tmp/databasename.sql

Dump a table from a database.

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

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql