My Software Development Articles

To run a script:

>mysql [dbName] < batch-file

--force will cause the script to continue if errors are encountered

Language help:

CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;


ENGINE is the preferred term, but cannot be used before MySQL 4.0.18. TYPE is available beginning with MySQL 3.23.0, the first version of MySQL for which multiple storage engines were available.
If you omit the ENGINE or TYPE option, the default table type is usually MyISAM. This can be changed by setting the table_type system variable.
To convert a table from one type to another, use an ALTER TABLE statement that indicates the new type:


ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;

CREATE DATABASE [IF NOT EXISTS] db_name
??? [create_specification [, create_specification] ...]

create_specification:
??? [DEFAULT] CHARACTER SET charset_name
? | [DEFAULT] COLLATE collation_name

GRANT ALL ON db_name.* TO username@localhost IDENTIFIED BY "userpasswd"

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options] [select_statement]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(] LIKE old_tbl_name [)];

create_definition:
    column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | KEY [index_name] [index_type] (index_col_name,...)
  | INDEX [index_name] [index_type] (index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX]
        [index_name] [index_type] (index_col_name,...)
  | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) [reference_definition]
  | CHECK (expr)

column_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
        [reference_definition]

type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
  | TEXT
  | MEDIUMTEXT
  | LONGTEXT
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)
  | spatial_type
Other column types that are supported in MS SQL 
Server that must be changed to work for MySQL
include:
money->real (or double)
varchar[n]->text (or blob) where n > 255
index_col_name: col_name [(length)] [ASC | DESC] reference_definition: REFERENCES tbl_name [(index_col_name,...)] [MATCH FULL | MATCH PARTIAL] [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT table_options: table_option [table_option] ... table_option: {ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM} | AUTO_INCREMENT = value | AVG_ROW_LENGTH = value | CHECKSUM = {0 | 1} | COMMENT = 'string' | MAX_ROWS = value | MIN_ROWS = value | PACK_KEYS = {0 | 1 | DEFAULT} | PASSWORD = 'string' | DELAY_KEY_WRITE = {0 | 1} | ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED } | RAID_TYPE = { 1 | STRIPED | RAID0 } RAID_CHUNKS = value RAID_CHUNKSIZE = value | UNION = (tbl_name[,tbl_name]...) | INSERT_METHOD = { NO | FIRST | LAST } | DATA DIRECTORY = 'absolute path to directory' | INDEX DIRECTORY = 'absolute path to directory' | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name] select_statement: [IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)
?As of version 5
CREATE PROCEDURE sp_name ([parameter[,...]])
    [characteristic ...] routine_body

CREATE FUNCTION sp_name ([parameter[,...]])
    [RETURNS type]
    [characteristic ...] routine_body

parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement(s)

My Articles On Other Sites

If you still need to support some Classic ASP and you'd like to use page caching to improve performance, see my article at  www.4guysfromrolla.com

Books

 


This book is essential for anyone wanting to write re-usable libraries for .NET


An excellent book to help you really learn to think about object oriented design and the trade offs of different implementation approaches.

One of the important concepts of this book is how to think about the natural joints in your object hierarchy. A lot of developers think they are doing object oriented design but are in fact doing data driven design. If you find that you always have a 1 to 1 correspondence between classes and tables in the database you probably fall into this camp. This book will help you take your thinking about objects to the next level.


Of all the books I've ever read, this one had the most direct impact in helping me improve the organization and readability of my code. I read the original and then when the second edition came out I read it again. Every serious developer should read this book.


>The famous Gang of Four book is the classic tomb on design patterns. Understanding design patterns separates the men from the boys in software development. Its not the easiest stuff to digest but those who can learn to recognize and use patterns will write much more elegant and maintainable code.


This book is great for a C# developer who wants to learn the Gang of Four design patterns. I struggle with some of the uml diagrams in the Gang of Four book and seeing the C# code really helps me understand the patterns.


Often we inherit the maintenance of poorly structured code. This book can help you learn to improve the design of existing code. You will learn as Fowler says to sniff out bad smells in your code and fix them so as to remove any offending odors.


This is a good reference for ASP.NET and C# if you are moving into the new 2.0 .NET way of doing things.
Donate Money to support the mojoPortal Project. View Joe Audette's profile on LinkedIn View Joe Audette's profile on The Guild of Accessible Web Designers site