|
Here are some simple sample scripts
You can get the necessary information to connect from your hosting company. Don't worry about that until you combine it with the PHP in the next lesson.
First you will need to learn to create a database, create a table in it, create columns and properties for your table and add data.
Next you will need to run queries on your table with various outputs.
The 4 items that you must always know when dealing with a database on the local server (Apache installed on your machine) are:
1. Host is localhost
2. User Name is root
3. Password is can be left blank on the local machine so don't enter one initially.
4. Database name is whatever you choose and Table is also. One database may have many tables.
They can be the same on the remote server (The server set up by HostMonster or your current hosting company), except ALWAYS USE A PASSWORD AND SET YOUR USER PERMISSIONS AS STRICTLY AS POSSIBLE!!!!!
You can go through all of the examples in the Sams Teach Yourself MySQL in 10 Minute pretty rapidly, but it will probably take considerably more than 10 minutes! To save you time, I have compiled most of them in one document below. Each statement is meant to be executed individually. You can also use DreamCoder to speed things up but hold off until you know how to wite the code directly first. You will use the Exact Same Code in your PHP code later.
Comments are in parentheses and pink. Do Not Type the comments!!!
Don't bother with the DOS window.
To execute SQL commands in DreamCoder: Tools/SQL editor/Choose database.
Type in the code and Run SQL (The first icon on the right just above the window.) You will see the results in the window below it.
You will need to create the ijdb database and joketable with the properties below or run the script below.
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
DROP TABLE IF EXISTS `ijdb`.`joketable`;
CREATE TABLE `ijdb`.`joketable` (
`id` int(11) NOT NULL auto_increment,
`joketext` text,
`jokedate` date default '0000-00-00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
Then try the following MySQL queries. Statements always end in a semicolon ; just like C++ and Java
USE ijdb;
SELECT joketext FROM joketable;
SELECT joketext, jokedate FROM joketable;
SELECT id,joketext, jokedate FROM joketable;
SELECT * FROM joketable;(shows all data)
DESCRIBE joketable;(shows design properties)
SHOW DATABASES;
SHOW TABLES; (shows tables in that database)
SHOW TABLES FROM addressbook; (shows tables in different database)
SHOW COLUMNS FROM joketable;
You will need to create the addressbook database and contacts table
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
DROP TABLE IF EXISTS `addressbook`.`contacts`;
CREATE TABLE `addressbook`.`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 KEY `id` (`id`),
KEY `id_2` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=38 DEFAULT CHARSET=latin1;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
USE addressbook; (to switch databases)
SHOW COLUMNS FROM contacts
SELECT * FROM contacts WHERE last='Ross';
SELECT * FROM contacts WHERE last!='Doe';
SELECT * FROM contacts WHERE last>'F';
SELECT * FROM contacts WHERE id>=10;
SELECT * FROM contacts WHERE last>first;
SELECT first,last FROM contacts ORDER BY last,first;
SELECT first,last FROM contacts ORDER BY last DESC;
SELECT * FROM contacts WHERE last>'F' AND id<25 OR first='Donald';
SELECT * FROM contacts WHERE last='Freeloader' OR last='Snowman';
SELECT * FROM contacts WHERE last IN('Freeloader','Snowman'); (same as above)
SELECT * FROM contacts WHERE last NOT IN('Freeloader','Snowman'); (everyone except)
SELECT * FROM contacts WHERE last BETWEEN 'Freeloader' AND 'Snowman';
SELECT * FROM contacts WHERE last NOT BETWEEN 'Freeloader' AND 'Snowman';
SELECT * FROM contacts WHERE last='Ross' and id<10 OR first = 'George'; (AND has higher precedence than OR which is higher than NOT)
SELECT * FROM contacts WHERE last='Ross' and id<10 OR first = 'George' LIMIT 2;
FROM contacts WHERE last='Ross' and id<10 OR first = 'George' LIMIT 4,2; (skips 4 rows before returning max. of 2 rows)
There are many more advanced queries in the reference book Sams Teach Yourself MySQL in 10 Minutes by Chris Newman. You will quickly get the hang of the syntax.
Imprtant Tip: Even though MySQL code is not case sensitive nor does it require indenting, make sure to follow the conventions for readibility, especially for yourself.
|
