MySQL
From Devpit
MySQL Reference
Useful commands to check on mysql
mysqladmin -u root -p status systat -vmstat
- Setting root password
SET PASSWORD FOR root@localhost=PASSWORD('rubberchicken');
- Creating a Database
# mysqladmin create [databasename] or mysql> create database [databasename] Example: # mysqladmin create mydatabase [Enter]
- Dropping (Removing) a Database
# mysqladmin drop [databasename] Example: # mysqladmin drop mydatabase [Enter]
- Populating an Existing Database from a *.sql File
# mysql [databasename] < [databasedumpfile.sql] Example: # mysql mydatabase < mydatabase.sql [Enter]
- Dumping Database Structure and Data to a *.sql file
mysqldump --opt [database] > [databasefilename.sql] Example: mysqldump --opt techmanual > techmanual.sql;
From within the MySQL interface
- Starting MySQL from the Command Line
# mysql Example: mysql [Enter] You will be welcomed with the following message: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is ## to server version: #.##.## Type 'help;' or '\h' for help. Type '\c' to clear the buffer. The prompt changes to "mysql>", which will be shown in each example below.
- Seeing What Databases are Available
mysql> show databases; Example: >mysql show databases; [Enter] (be sure to use the semi-colon to terminate the command)
- Telling MySQL to Use a Specific Database
mysql> use [databasename]; Example: mysql> use mydatabase; [Enter]
- Seeing What Tables are Available Within a Database
mysql> show tables; Example: mysql> show tables; [Enter]
- Looking at the Data in a Particular Table
mysql> select * from [tablename]; Example: mysql> select * from lastname; [Enter]
- Adding a Database User with Password
mysql> grant all privileges on [databasename].* to [dbusername]@localhost identified by '[dbpassword]'; Example: grant all privileges on mydatabase.* to joeuser@localhost identified by 'supersecretpasswd';
- Removing a Database User
mysql> DELETE FROM mysql.user WHERE user='username' and host='localhost';
FLUSH PRIVILEGES;
Example: DELETE FROM mysql.user WHERE user='techgeek' and host='localhost';
FLUSH PRIVILEGES;
* Performing Search-and-Replace Actions on a Table
mysql> update DatabaseName set TableName = REPLACE(TableName,"[String to Search For]","[String Replacement]");
Example: mysql> update roesublist set Grades = REPLACE(Grades,"Grades: Any","K,1,2,3,4,5,6,7,8,9,10,11,12");
- Add a column to an existing table
mysql> alter table foo add column bar char(100); mysql> alter table foo add column bar char(100) after foo;
