MySQL basics

Getting Started with MySQL

Connecting to MySQL

To access a MySQL db you need:
- user name and password
- host name or IP
- name of db

Two ways to access MySQL directl

• command line:
  • telnet
  • SSH (recommended due to enhanced security)
• MySQL client programs [mysql, mysqladmin, mysqldump]

To access a database from the command line

mysql -u username -ppassword -h hostname databasename

Note: There is no space between the -p and the password. Password after -p is optional here. If left blank, you will be prompted to enter your password.

Using MySQL Client Programs

Install the client programs

Basic Commands

Virtually all commands are terminated with a semicolon. The prompt '->' means MySQL is waiting for more instructions or the semicolon is missing.

Cancel current command \c and press Enter.

MySQL will ignore anything in a command that ends with \c and go back to beginning.

Type exit or quit and press Enter anytime you want to exit. These commands do not require a semicolon.

show databases;

drop database <db_name>;

create database <db_name>;

use <db_name>;

show <table_name>;

describe <table_name>;

drop table <table_name>;

DISTINCT informs query to eliminate duplicate result rows

Inserting data into a table

INSERT : used to set column values

Two formats:

INSERT INTO table_name SET

-> columnName1 = value1,

-> columnName2 = value2,

-> etc...

->;

OR

INSERT INTO table_name -> (columnName1, columnName2, etc...) -> VALUES (value1, value2, etc...);

Viewing stored data

SELECT : used to view column data

mysql>SELECT * FROM table_name;

mysql>SELECT columnName1, columnName2, ... FROM table_name;

Modify Columns with Functions

Functions:

LEFT : displays maximum number of characters per column

COUNT : count the number of results returned

WHERE Clause

LIKE : named column must contain the given pattern match

ALTER table

ALTER query alters the table columns.

mysql> ALTER TABLE <table_name> ADD COLUMN -> <column_name> <VAR_TYPE>;

Modify Stored Data

UPDATE command : modifies and views data

general format:

mysql> UPDATE table_name SET -> column_name = new_value, ... -> WHERE conditions;

Delete Stored Data

WARNING: Deleting data is easy.

DELETE : Deletes stored data

general format:

mysql> DELETE FROM table_name WHERE conditions;

NOTE: You more than likely want to a WHERE condition in all situations. If you use "DELETE FROM table_name" without conditions your table will be empty in one command.