Simple MySql Reference

Simple mySQL Reference by Travis Whitton

(Please forgive me for typos)

To Create a database:

create database databasename;

Example:

create database cancels;

To Create a table:

create table cancels (name VARCHAR(20), address VARCHAR(20));

To Load data from a textfile:

load data local infile "cancels.txt" INTO TABLE cancels;

This assumes that a file called cancels.txt exists with one record per
line and fields that are separated by tabs i.e.,

bob 603 NE 4th Ave
sue 303 NW 56th Ave

To Insert from the command line:

insert into cancels
-> values ('Travis','404 NE 5th Place');

To Insert only into a given field:

insert into cancels
-> (name, phone)
-> values ('Travis', '335-3816');

This would only change the name and phone fields of the table cancels;

To Update a Given Field:

update tablename set fieldname = value where fieldname = criteria;

Example:

update cancels set zip = 32608 where zip = 0;

This would set the zipcode to 32608 everywhere the zipcode is zero.

To Delete a row from a table:

delete from tblname where fieldname='value';
If you leave off the where clause, all rows are deleted.

To Change a field type:

alter table tablename change name newname datatype;

Example:

To change the datatype of a field called name to varchar(30)

alter table cancels change name name varchar(30);

To rename a field called name while keeping varchar(30) as the datatype

alter table cancels change name first_name varchar(30);

Working with null values:

Use the is null and is not null keywords.

Example:

To select all the fields where name is null:

select * from cancels where name is null;

Selecting data:

To select all fields:

select * from tablename;

Example:

select * from cancels;

To select a particular row:

select * from table where row = 'data';

Example:

To select all the rows where the name is 'travis':

select * from cancels where name = 'travis';

To select a particular column:

select field1, field2 from table;

Example:

To show only the name and phone fields of the cancels table:

select name, phone from cancels;

Inserting a new column in an existing table alter table logins add column logged char(1) after last_login;

Wildcards:

Use the % symbol.

select * from cancels where name like '%on';

would match ron, don, jon...

notice the use of the like keyword instead of the '=' symbol.

Send all your flames here.