Difference between revisions of "MySQL basics"
Line 54: | Line 54: | ||
<code>\</code> tells MySQL to treat the next character as a character string instead of any special meaning that it may have. | <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: | |||
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 == | == Inserting data into a table == |
Revision as of 22:38, 16 December 2007
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.
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...);
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