cd c:\xampp\mysql\bin
mysql.exe -u root
> create database database01;
> show databases
> use database01
SHOW DATABASES;
SHOW TABLES;
con = mysql_connect("localhost","peter","abc123");
CREATE TABLE dummy ( first_name CHAR(20) , last_name CHAR(20) );
show columns from table01
insert into table01 (first_name, field02) values (1, 'first');
INSERT INTO books VALUES ("My Life", "Mickey Mouse", "Disney", "Biography", 9.95);
select * from table01
$selectSQL=("SELECT field_names FROM tablename");
alter table table01 add column field03 char(20); //add fields
$deleteSQL=("DELETE * FROM tablename WHERE condition");
UPDATE example SET age='22' WHERE age='21'
Numeric Data Types Description
int(size)
smallint(size)
tinyint(size)
mediumint(size)
bigint(size) Hold integers only. The maximum number of digits can be specified in the size parameter
decimal(size,d)
double(size,d)
float(size,d) Hold numbers with fractions. The maximum number of digits can be specified in the size parameter. The maximum number of digits to the right of the decimal is specified in the d parameter
Textual Data Types Description
char(size) Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis
varchar(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis
tinytext Holds a variable string with a maximum length of 255 characters
text
blob Holds a variable string with a maximum length of 65535 characters
mediumtext
mediumblob Holds a variable string with a maximum length of 16777215 characters
longtext
longblob Holds a variable string with a maximum length of 4294967295 characters
Date Data Types Description
date(yyyy-mm-dd)
datetime(yyyy-mm-dd hh:mm:ss)
timestamp(yyyymmddhhmmss)
time(hh:mm:ss) Holds date and/or time
Misc. Data Types Description
enum(value1,value2,ect) ENUM is short for ENUMERATED list. Can store one of up to 65535 values listed within the ( ) brackets. If a value is inserted that is not in the list, a blank value will be inserted
set SET is similar to ENUM. However, SET can have up to 64 list items and can store more than one choice
create database game;
use game;
create table map(xcoord int, ycoord int, terrain varchar(15));
load data local infile 'c:/server/map.txt' into table map;
create table terrain(type varchar(15), movechange int);
load data local infile 'c:/server/terrain.txt' into table terrain;
create table units(name varchar(15), move int, attack int, hitpoints int);
load data local infile 'c:/server/units.txt' into table units;
create table players(name varchar(15), unitname varchar(15), unitxcoord int, unitycoord int);
load data local infile 'c:/server/players.txt' into table players;
select * from map inner join players where map.xcoord = players.xcoord;
SELECT*FROM players p INNER JOIN map m ON p.unitxcoord = m.xcoord AND p.unitycoord = m.ycoord;
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1 INNER JOIN pet AS p2
-> ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
MYSQL thru PHP
<?
$user="username";
$password="password";
$database="database";
mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="CREATE TABLE contacts (id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,phone varchar(20) NOT NULL,mobile varchar(20) NOT NULL,fax varchar(20) NOT NULL,email varchar(30) NOT NULL,web varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))";
mysql_query($query);
mysql_close();
?>
APPENDIX A MySQL Datatypes
Ty p e
S i z e
D e s c r i p t i o n
CHAR[Length]
Length bytes
A fixed-length field from 0 to 255 characters long.
VARCHAR(Length)
String length + 1 bytes
A fixed-length field from 0 to 255 characters long.
TINYTEXT
String length + 1 bytes
A string with a maximum length of 255 characters.
TEXT
String length + 2 bytes
A string with a maximum length of 65,535 characters.
MEDIUMTEXT
String length + 3 bytes
A string with a maximum length of 16,777,215 characters.
LONGTEXT
String length + 4 bytes
A string with a maximum length of 4,294,967,295 characters.
TINYINT[Length]
1 byte
Range of -128 to 127 or 0 to 255 unsigned.
SMALLINT[Length]
2 bytes
Range of -32,768 to 32,767 or 0 to 65535 unsigned.
MEDIUMINT[Length]
3 bytes
Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.
INT[Length]
4 bytes
Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.
BIGINT[Length]
8 bytes
Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned.
FLOAT
4 bytes
A small number with a floating decimal point.
DOUBLE[Length, Decimals]
8 bytes
A large number with a floating decimal point.
DECIMAL[Length, Decimals]
Length + 1 or Length + 2 bytes
A DOUBLE stored as a string, allowing for a fixed decimal point.
DATE
3 bytes
In the format of YYYY-MM-DD.
DATETIME
8 bytes
In the format of YYYY-MM-DD HH:MM:SS.
TIMESTAMP
4 bytes
In the format of YYYYMMDDHHMMSS; acceptable range ends inthe year 2037.
TIME
3 bytes
In the format of HH:MM:SS
ENUM
1 or 2 bytes
Short for enumeration, which means that each column can haveone of several possible values.
SET
1, 2, 3, 4, or 8 bytes
Like ENUM except that each column can have more than one ofseveral possible values.
Many of the types can take an optional Length attribute, limiting their size (the square brackets, [], indicate an optional parameter to be put in parentheses, while parentheses themselves indicate required arguments). Further, the number types can be UNSIGNED-limiting the column to positive numbers or zero-or be defined as ZEROFILL, which means that any extra room will be padded with zeroes (ZEROFILLs are also automatically UNSIGNED).