MySQL User Guide - Scrum-Lords/User-Guides GitHub Wiki

                                         ## User Guide for MySQL

DROP COMMANDS

mysql> drop database [database name]; = To delete a database

mysql> drop table [table name]; = To delete a table

SELECT COMMANDS

mysql> SELECT * FROM [table name]; To show all data in atable
mysql> SELECT COUNT(*) FROM [table name]; = Return number of rows
mysql> SELECT SUM(*) FROM [table name]; = Number of columbs
mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user'; = Update information in a table
mysql> DELETE from [table name] where [field name] = 'whatever'; = Delete a row from a table

ALTER TABLES COMMANDS

mysql> alter table [table name] drop column [column name]; = Delete a column
mysql> alter table [table name] add column [new column name] varchar (20); = Add a new column
mysql> alter table [table name] change [old column name] [new column name] varchar (50); = Change column name
mysql> alter table [table name] modify [column name] VARCHAR(3); = Make a column bigger
mysql> alter table [table name] drop index [colmn name];= Delete unique from table.

ROOT COMMANDS

mysqladmin -u root -p oldpassword newpassword = Update a root password
mysql -u root -pmysql> use mysql;mysql> grant usage on . to bob@localhost identified by 'passwd';mysql> flush privileges;= Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.
mysqladmin -u root password newpassword=Set a root password if there is on root password.

****BIN COMMANDS ****

[mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql = Dump all databases for backup. Backup file is sql commands to recreate all db's.
[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql = Dump one database for backup.
[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql = Dump a table from a database.
[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql = Restore database (or database table) from backup.

OTHER COMMANDS