• Appgen
• Backup Edge
• Eudora
• FTP
• MySQL
• PowerTerm
• Samba
• uniBasic
• Business Pro & Yes I Can

MySQL 101

Syntax

<sample> should be replaced with actual content and drop the <>'s

Note: -p option prompts for password

Databases

Create

mysqladmin -p create <databasename>

CREATE DATABASE <databasename>

Drop

mysqladmin -p drop <databasename>

DROP DATABASE <databasename>

USE

Use <databasename>

top of page

Tables

Create

CREATE TABLE <tablename> (<fieldname> <type> <null option>, PRIMARY KEY (<fieldname>))

Null Option

• Not Null

Field Types

• Numeric
  • INT - signed or unsigned
  • REAL - floating point value
  • DECIMAL(length,decimal) - floating point value - treated as text fields
• Text
  • CHAR(length) - fixed length text, padded with spaces, max size=255
  • TEXT(length) - Variable length text, max size=65535
  • VARCHAR(length) - Variable ength text, max size=255
• Date/Time
  • DATE - Date only
  • TIME - Time only
  • DATETIME - Date and Time
  • TIMESTAMP - Updates every time record is modified
  • YEAR - Year only
• AUTO_INCREMENT - Only one field per table

Each table can only have one primary key.

Drop

DROP TABLE <tablename>

Indexes

CREATE <indexname> ON <tablename> (<fieldname>, <fieldname>, etc)

DROP INDEX <indexname>

Alter

ALTER TABLE <tablename> <OPTION>

Options

• ADD COLUMN <create clause>
• ADD INDEX <indexname> <fieldname>
• ADD UNIQUE <keyname> <fieldname>
• ALTER <fieldname> SET DEFAULT = <value>
• ALTER <fieldname> DROP DEFAULT
• CHANGE <fieldname> <create clause>
• DROP <fieldname>
• DROP INDEX <indexname>
• DROP PRIMARY KEY
• MODIFY <fieldname> <create cluase>
• RENAME AS <newtablename>

Data

Insert

INSERT INTO <tablename> (field1, field2, etc) VALUES (value1, value2, etc)

Update

UPDATE <tablename> SET field1=value1, field2=value2, etc)

Delete

DELETE FROM <tablename> (will delete all data)

DELETE FROM <tablename> WHERE <value or fieldname> = <value or fieldname>

Query

SELECT field1, field2, etc FROM <tablename> WHERE <value or fieldname> = <value or fieldname>

Alias

SELECT <fieldname> AS <aliasfieldname>

Sort

ORDER BY <field1, field2, etc>

Group (totals)

GROUP BY <field1, field2, etc>

JOIN (Left outer)

LEFT JOIN <tablename> ON <fieldname> = <fieldname>

Length

LENGTH(<fieldname>)

top of page

Administration

Help

mysqladmin -?

Change root password

mysqladmin -u root password '<password>'

Server Status

mysqladmin -p status

Version

mysqladmin -p version

Displays version and status info

Shutdown

mysqladmin -p shutdown

top of page

Utilities

Dump table

mysqldump <databasename> <tablename>

top of page