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.