SQL Reference

These commands can be used from anything that has a connection to an SQL server (MySQL), such as the mysql command line tool (typing 'mysql' at the command line in your SQL server's directory - for Win), or in java (using the java.sql.* package in your code). All commands end with a semicolon ';'.

Working with databases:
CREATE DATABASE [dbname];
SHOW DATABASES; (command line, shows what databases are in the server)
DROP DATABASE [dbname]; (deletes db)
USE [dbname]; (which db to manipulate)

Working with tables and their attributes:
CREATE TABLE [table name] ([field name] [field attributes], [field name] [field attributes], etc..);
ALTER TABLE [table name] DROP [field name];
ALTER TABLE [table name] ADD ([field name] [field attributes]);
ALTER TABLE [table name] CHANGE [old field name] [new field name] [field attributes];
ALTER TABLE [table name] ALTER [field name] SET [field attributes];
ALTER TABLE [table name] MODIFY [field name] [field attributes];

Working with tables and data:
INSERT INTO [table name] ([field name1], [field name2]) VALUES ([value1], [value2]); (if you separate value () blocks with ','s you can insert multiple records at once
UPDATE [table name] SET [field name] = [data] WHERE [another field name] = [data];
UPDATE [table name] SET [field name] = [data], [field name2] = [data2] WHERE [another field name] = [data];
DELETE FROM [table name] WHERE [field name] = [data];

Querying dbs:
SELECT [field1], [field2], [* is for all] FROM [table name];
SELECT [field1], [field2], [* is for all] FROM [table name] WHERE [field name] = [data, or text in 's];
SELECT [field1], [field2], [* is for all] FROM [table name] WHERE [field name] [>= or <= or > or < or <>] [data, or text in 's];
SELECT [field1], [field2], [* is for all] FROM [table name] WHERE [field name] = [data, or text in 's] AND|OR [field name] = [data, or text in 's]; (to do ANDs before ORs, use ()s)
SELECT [field1], [field2], [* is for all] FROM [table name] WHERE NOT ([field name] = [data, or text in 's]);
SELECT [field1], [field2], [* is for all] FROM [table name] WHERE [field name] IN ([data1], [data2]);
SELECT [field1], [field2], [* is for all] FROM [table name] WHERE [field name] BETWEEN [data1] and [data2];
SELECT [field1], [field2], [* is for all] FROM [table name] WHERE [field name] NOT BETWEEN [data1] and [data2];
SELECT [field1], [field2], [* is for all] FROM [table name] WHERE [field name] LIKE [text in 's];
SELECT [table1].[field1], [table1].[field2], [table2].[field1], [table2].[field2] FROM [table1], [table2] WHERE [table1].[field1] = [table2].[field1]; (multiple tables)
SELECT * FROM [table] ORDER BY [field] [ASC/DESC] LIMIT [integer number];

SELECT [field1], [field2], [* is for all] FROM [table name] WHERE [field name] IS NULL;

extra SQL features:
functions - SUM([field name]) - AVG([field name]) - MAX([field name]) - MIN([field name]) - COUNT(*) - each performs function on field that was specified for 'WHERE' conditions specified
SELECT [field1], [field2], [* is for all] FROM [table name] GROUP BY [field name];
SELECT [field1], [field2], [* is for all] FROM [table name] ORDER BY [field name];
SELECT [field1], [field2], [* is for all] FROM [table name] ORDER BY [field name] DESC;
SELECT [field1], [field2], [* is for all] FROM [table name] ORDER BY [field name] ASC;

Extra notes for future additions:

do ResultSet java notes too? (use code from 2k pro box)
do java mm.mysql connection code notes (direct from java to mysql - should work over network).. also JDBC.ODBC connection notes (uses OS where sql server located, have to have client OS set up for network access)... (use code from 2k pro box, notes)
do transaction notes? (get this set up before more than one person has access to web app before prior arrangments, i.e. when coaches and/or other office staff help input data to apps on their time)
look at Ch.16 code example from book to jumpstart scheduling?