Editing MySQL basics

[[PHP]] information

== Getting Started with MySQL ==

=== Connecting to MySQL ===
To access a MySQL db you need:<br>
- user name and password<br>
- host name or IP<br>
- name of db

=== Two ways to access MySQL directly ===

* command line:
** telnet
** SSH (recommended due to enhanced security)
* MySQL client programs [<code>mysql, mysqladmin, mysqldump</code>]

=== To access a database from the command line ===

<code>mysql -u username -ppassword -h hostname databasename</code>

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 <code>mysql -u root -p</code> then enter password at prompt.

Perform SQL dump from CLI

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

=== 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.<br>

Cancel current command <code>\c</code> and press Enter.<br>

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

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

<code>show table status;</code>

<code>show databases;</code>

<code>drop database <db_name>;</code>

<code>create database <db_name>;</code>

<code>use <db_name>;</code>

<code>show tables;</code>

<code>show <table_name>;</code>

<code>describe <table_name>;</code>

<code>drop table <table_name>;</code>

<code>DISTINCT</code> informs query to eliminate duplicate result rows

<code>\</code> 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:

<pre>
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.
</pre>

== Inserting data into a table ==

<code>INSERT</code> : used to set column values

Two formats:

<pre>
INSERT INTO table_name SET

-> columnName1 = value1,

-> columnName2 = value2,

-> etc...

->;

OR

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

Table Commands
<pre>
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;
</pre>

== Viewing stored data ==

<code>SELECT</code> : 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
<pre>
mysql>SELECT columnName1, columnName2, ... FROM table_name ORDER BY columnName;
</pre>

Sorts by column alphabetically descending
<pre>
mysql>SELECT columnName1, columnName2, ... FROM table_name ORDER BY columnName DESC;
</pre>

== Modify Columns with Functions ==

Functions:

LEFT : displays maximum number of characters per column

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

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

<pre>
mysql> SELECT COUNT (*) FROM tableName;

mysql> SELECT COUNT (tableName.columnName) FROM tableName;
</pre>



WHERE Clause

LIKE : named column must contain the given pattern match

== ALTER table ==

<code>ALTER</code> query alters the table columns.

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

== Modify Stored Data ==

<code>UPDATE</code> 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.

<code>DELETE</code> : 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:
<code>LOCK TABLES table_name <READ or WRITE>

Example syntax:
LOCK TABLES merchandise READ, inventory WRITE, sales WRITE</code>

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:
<code>UNLOCK TABLES</code>

== Changing root password ==

Replace "newpassword" with password of your choice.

<pre>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></pre>

== Set operations ==

[http://en.wikipedia.org/wiki/Set_operations_%28SQL%29 Set Operations]



<center>[[MySQL | To MySQL]]</center>