MySQL basics

PHP information

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

\ tells MySQL to treat the next character as a character string instead of any special meaning that it may have.

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;

join allows you to treat data in multiple tables as if they were one

mysql>SELECT columnName1, columnName2, ... FROM tableName1, tableName2, ... WHERE condition(s) for data to be related;

Sorting SELECT results:

Sorts by column alphabetically

mysql>SELECT columnName1, columnName2, ... FROM table_name ORDER BY columnName;

Sorts by column alphabetically descending

mysql>SELECT columnName1, columnName2, ... FROM table_name ORDER BY columnName DESC;

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