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.

Alternatively, you can use mysql -u root -p then enter password at prompt.

Perform SQL dump from CLI

mysqldump -u username -ppassword -h hostname databasename > filename.sql

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 table status;

show databases;

drop database <db_name>;

create database <db_name>;

use <db_name>;

show tables;

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.

mysql>SHOW DATABASES;

mysql>DROP DATABASE [databasename];

mysql>CREATE DATABASE [databasename];

mysql>\h Among other things, \h produces this output:

help    (\h)    Display this help.
?       (\?)    Synonym for `help'.
clear   (\c)    Clear command.
connect (\r)    Reconnect to the server. Optional arguments are db and host.
edit    (\e)    Edit command with $EDITOR.
ego     (\G)    Send command to mysql server, display result vertically.
exit    (\q)    Exit mysql. Same as quit.
go      (\g)    Send command to mysql server.
nopager (\n)    Disable pager, print to stdout.
notee   (\t)    Don't write into outfile.
pager   (\P)    Set PAGER [to_pager]. Print the query results via PAGER.
print   (\p)    Print current command.
prompt  (\R)    Change your mysql prompt.
quit    (\q)    Quit mysql.
rehash  (\#)    Rebuild completion hash.
source  (\.)    Execute a SQL script file. Takes a file name as an argument.
status  (\s)    Get status information from the server.
system  (\!)    Execute a system shell command.
tee     (\T)    Set outfile [to_outfile]. Append everything into given outfile.
use     (\u)    Use another database. Takes database name as argument.

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...);

Table Commands

mysql>CREATE TABLE table_name ( column1_name column1_type column1_details,
    -> column2_name  column2_type column2_details, ... );


mysql>SHOW TABLES;
mysql>DESCRIBE table_name;
mysql>DROP table_name;


mysql>UPDATE table_name SET

    -> column_name = new_value, ...

    -> WHERE conditions;


mysql>DELETE FROM table_name WHERE conditions;

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

Changing root password

Replace "newpassword" with password of your choice.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("newpassword") where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Set operations

Set Operations

To MySQL