MySQL Notes

Basic Functions

Start MySQL: (as root) /etc/init.d/mysql start (or status/stop/restart)

Open Database: (as carl) mysql -u carl -p databasename

Create new database: create database databasename;

Show list of existing databases: show databases;

Use a specific database: use databasename;

Create a new table: create table tablename (fieldname1 datatype, fieldname2 datatype);

• List as many fieldname/datatype pairs as needed separated by commas
• In addition to datatype for each fieldname, may also specify the following:
  • NULL/NOT NULL: Use NOT NULL to require the field to be filled
  • Unsigned: prevents negative numbers
  • Primary Key or Foreign Key
  • Auto-increment: fills in an integer automatically when data for the record is NULL

Add 1 record: insert into tablename (fieldname1, fieldname2) values ('Bob', 'Jones');

Add 3 records: insert into tablename (fieldname1, fieldname2) values ('Bob', 'Jones'), ('Sam', 'Adams'), ('Bill', 'Schmatz');

Select all records unsorted: select * from tablename;

Select specific records and sort: select fieldname1, fieldname2 from tablename order by fieldname2;
Add desc at the end to reverse sort order

Quit MySQL: \q;

Data Types

Data Type	Description
CHAR[length]	Fixed length field from 0 to 255 characters long
VARCHAR[length]	Variable length field from 0 to 65,535 characters long
TINYTEXT	A string with a maximum of 255 characters
TEXT	A string with a maximum of 65,535 characters
MEDIUMTEXT	A string with a maximum of 16,777,215 characters
LONGTEXT	A string with a maximum of 4,294,967,295 characters
TINYINT[Length]	Range of -128 to 127 or 0 to 255 unsigned
SMALLINT[Length]	Range of -32,768 to 32,767 or 0 to 65,535 unsigned
MEDIUMINT[Length]	Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned
INT[Length]	Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned
BIGINT[Length]	Range of -9,233,372,036,854,775,808 to 9,233,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned
FLOAT[Length, Decimals]	Small number with a floating point decimal
DOUBLE[Length, Decimals]	Large number with a floating point decimal
DECIMAL[Length, Decimals]	DOUBLE stored as a string, allowing for a fixed decimal point
DATE	In the format of YYYY-MM-DD
DATETIME	In the format of YYYY-MM-DD HH:MM:SS
TIMESTAMP	In the format of YYYYMMDDHHMMSS; acceptable range ends in the year 2037
TIME	In the format HH:MM:SS
ENUM	Short for enumeration, which means that each column can have one of several possible values
SET	Line ENUM except that each column can have more then one of several possible values