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 directly

• 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

mysql> SELECT LEFT (columnName, <integer>) FROM tableName;

COUNT : count the number of results returned. COUNT(*) will include NULL values unless you specify the tableName and columnName.

mysql> SELECT COUNT (*) FROM tableName;

mysql> SELECT COUNT (tableName.columnName) FROM tableName;

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.

Locking Tables

You lock a table to gain exclusive access so other processes cannot alter the table.

Syntax: LOCK TABLES table_name <READ or WRITE>

Example syntax: LOCK TABLES merchandise READ, inventory WRITE, sales WRITE

WRITE prevents anyone from viewing or changing tables.

READ prevents changes to tables and allows viewing.

You must unlock tables after you are done.

Syntax: UNLOCK TABLES