o
To login (from unix shell) use -h only if needed.
# [mysql
dir]/bin/mysql -h hostname -u root -p
o Create a database on the sql server.
mysql>
create database [databasename];
o List all databases on the sql server.
mysql>
show databases;
o Switch to a database.
mysql>
use [db name];
o To see all the tables in the db.
mysql>
show tables;
o To see database's field formats.
mysql>
describe [table name];
o To delete a db.
mysql>
drop database [database name];
o To delete a table.
mysql>
drop table [table name];
o Show all data in a table.
mysql>
SELECT * FROM [table name];
o Returns the columns and column information pertaining to the designated table.
mysql>
show columns from [table name];
o Show certain selected rows with the value "whatever".
mysql>
SELECT * FROM [table name] WHERE [field name] = "whatever";
o Show all records containing the name "Bob" AND the phone number '3444444'.
mysql>
SELECT * FROM [table name] WHERE name = "Bob" AND phone_number =
'3444444';
o Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.
mysql>
SELECT * FROM [table name] WHERE name != "Bob" AND phone_number =
'3444444' order by phone_number;
o Show all records starting with the letters 'bob' AND the phone number '3444444'.
mysql>
SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number =
'3444444';
o Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.
mysql>
SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number =
'3444444' limit 1,5;
o Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
mysql>
SELECT * FROM [table name] WHERE rec RLIKE "^a";
o Show unique records.
mysql>
SELECT DISTINCT [column name] FROM [table name];
o Show selected records sorted in an ascending (asc) or descending (desc).
mysql>
SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
o Return number of rows.
mysql>
SELECT COUNT(*) FROM [table name];
o Sum column.
mysql>
SELECT SUM(*) FROM [table name];
o Join tables on common columns.
mysql>
select lookup.illustrationid, lookup.personid,person.birthday from lookup left
join person on lookup.personid=person.personid=statement to join birthday in
person table with primary illustration id;
