Some Mysql commands

Posted: May 2, 2013 in Mysql

1. To select a database

mysql> USE database;

Mysql cpanel – mysql -uroot -p`grep pass /root/.my.cnf | cut -d ‘”‘ -f 2`

2. To list databases

mysql> SHOW DATABASES;

3. To list tables in a db

mysql> SHOW TABLES;

4. To describe the format of a table

mysql> DESCRIBE table;

5. To create a database

mysql> CREATE DATABASE db_name;

6. To create a table

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));

Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

7. To Load tab-delimited data into a table

mysql> LOAD DATA LOCAL INFILE “infile.txt” INTO TABLE table_name;

(Use \n for NULL)

8. To insert one row at a time into the table

mysql>l;

(Use NULL for NULL)

9. To retrieve information

mysql> SELECT from_columns FROM table WHERE conditions;

All values: SELECT * FROM table;

Some values: SELECT * FROM table WHERE rec_name = “value”;

Multiple critera: SELECT * FROM TABLE WHERE rec1 = “value1” AND rec2 = “value2”;

10. To reload a new data into the existing table

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table

mysql> DELETE FROM pet;

mysql> LOAD DATA LOCAL INFILE “infile.txt” INTO TABLE table;

11. To fix all records with a certain value

mysql> UPDATE table SET column_name = “new_value” WHERE record_name = “value”;

12. To select specific column

mysql> SELECT column_name FROM table;

13. To retrieve unique output records

mysql> SELECT DISTINCT column_name FROM table;

14. To sort columns

mysql> SELECT col1, col2 FROM table ORDER BY col2;

15. For data calculations

mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];

MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

16. To count number of rows

mysql> SELECT COUNT(*) FROM table;

17. To select from multiple tables

mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;

(You can join a table to itself to compare by using ‘AS’)

18.For Auto incrementing rows

mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);

mysql> INSERT INTO table (name) VALUES (“tom”),(“dick”),(“harry”);

19. To add a column to the already created table

mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

20. To backup a database with mysqldump

# mysqldump –opt -u username -p database > database_backup.sql

(Use ‘mysqldump –opt –all-databases > all_backup.sql’ to backup everything.)

21. To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

22. To delete a db.

mysql> drop database [database name];

23. To delete a table.

mysql> drop table [table name];

24. To Show all data in a table.

mysql> SELECT * FROM [table name];

25. To returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

26. To Show certain selected rows with the value “whatever”.

mysql> SELECT * FROM [table name] WHERE [field name] = “whatever”;

27. To Show all records containing the name “Bob” AND the phone number ‘3444444’.

mysql> SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444’;

28. To Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by

the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444’ order by phone_number;

29. To Show unique records.

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

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

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

31. To Sum column.

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

32. To Change a users password from unix shell.

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

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

privs.

# mysql -u root -p

mysql> SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);

mysql> flush privileges;

34. To Set a root password if there is on root password.

# mysqladmin -u root password newpassword

35. To Update a root password.

# mysqladmin -u root -p oldpassword newpassword

36. To Delete a row(s) from a table.

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

37. To Delete a column.

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

38. To Delete unique from table.

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

39. To Dump one database for backup.

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

40. To Dump a table from a database.

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

41. To Restore database (or database table) from backup.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s