Useful MySQL Database Commands

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database's field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

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

mysql> show columns from [table name];

Show certain selected rows with the value "whatever".

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

Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

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;

Show all records starting with the letters 'bob' AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.

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

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

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

Return number of rows.

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

Sum column.

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

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

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

# mysql -u root -p mysql> use mysql; mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password')); mysql> flush privileges;

Change user’s password from unix shell.

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

Change user’s 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;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop # mysqld_safe --skip-grant-tables & # mysql -u root mysql> use mysql; mysql> update user set password=PASSWORD("newrootpassword") where User='root'; mysql> flush privileges; mysql> quit # /etc/init.d/mysql stop # /etc/init.d/mysql start

Delete a column.

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

Add a new column to db.

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

Change column name.

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

Make a unique column so you get no dupes.

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

Make a column bigger.

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

Delete unique from table.

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

Load a CSV file into a table.

mysql> 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

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

Read More

How to Create a Database in MySQL

MySQL can be an intimidating program. All of the commands have to be entered through a command prompt; there is no visual interface. Because of this, having basic knowledge of how to create and manipulate a database can save you a lot of time and headaches. Follow this guide to create a database of US states and their populations

Creating and Manipulating a Database

1. 
   1. Create the database.  From the MySQL command line, enter the command CREATE DATABASE <DATABASENAME>;. Replace <DATABASENAME> with the name of your database. It cannot include spaces.

   For example, to create a database of all the US states, you might enter CREATE DATABASE us_states;
   Note: Commands do not have to be entered in upper-case.
   Note: All MySQL commands must end with ";". If you forgot to include the semicolon, you can enter just ";" on the next line to process the previous command.
   
2. 
   2. Display a list of your available databases. Enter the command SHOW DATABASES; to list all of the databases you have stored. Besides the databse you just created, you will also see a mysql database and a test database. You can ignore these for now.   

   
   
3. 
   3. Select your database. Once the database has been created, you will need to select it in order to begin editing it. Enter the command USE us_states;. You will see the message Database changed, letting you know that your active database is now us_states.
4. 
   

   
   4. Create a table. A table is what houses your database’s information. To create one, you will need to enter all of your table formatting in the initial command. To create a table, enter the following command: CREATE TABLE states (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, state CHAR(25), population INT(9));. This will create a table named "states" with three fields: id, state, and population.
   • The INT command will make the id field contain only numbers (integers).
   • The NOT NULL command makes sure that the id field cannot be left blank.
   • The PRIMARY KEY designates the id field as the key field in the table. The key field should be set to a field that cannot contain any duplicates.
   • The AUTO_INCREMENT command will automatically assign increasing values into the id field, essentially automatically numbering each entry.
   • The CHAR(characters) and INT(integers) commands designate the types of data allowed in those fields. The number next to the commands indicated how many characters or integers can fit in the field.
5. 
   

   
   5. Create an entry in the table. Now that the table has been created, it’s time to start entering your information. Use the following command to input your first entry: INSERT INTO states (id, state, population) VALUES (NULL, ‘Alabama’, ‘4822023’);
   • This is essentially telling the database to enter the information provided into the three corresponding fields in the table.
   • Since the id field contains the command NOT NULL, entering NULL as the value will force it to automatically increment to 1, thanks to the AUTO_INCREMENT command.
6. 
   
   6. Create more entries. You can create multiple entries using a single command. To enter the next three states, use the following command:INSERT INTO states (id, state, population) VALUES (NULL, ‘Alaska’, ‘731449’), (NULL, ‘Arizona’, ‘6553255’), (NULL, ‘Arkansas’, ‘2949131’);. This will create a table that looks like the following:
7. 

   
   

   

source: wikihow

Read More