Alters & Indices

mysql> ALTER TABLE customer ADD PRIMARY KEY cust_id(cust_id);
#this obviously is not an FK



#This is how you create foreign keys to speed up your databases
mysql> ALTER TABLE customer ADD INDEX state_id(state_id);
mysql> ALTER TABLE customer ADD INDEX phone_id(phone_id);
#Tells SQL that state_id can have the same value multiple times

mysql> DROP INDEX phone_id ON customer;
#phone_id is no longer an index


mysql> ALTER TABLE customer ADD UNIQUE state_id(state_id);
#Unique Indexes: All the values within them are unique


mysql> CREATE INDEX index_name ON tbl_name(columns);
/*Create an index for a column that doesn't currently exist, this would automatically create indexes for specified column(s)*/




mysql> ALTER TABLE customer ADD COLUMN comments VARCHAR(40);
/*Lets say that we added this column but then decided that we needed for more than 40 characters*/
mysql> ALTER TABLE customer CHANGE COLUMN comments comments text;
/*Now we can type as much text as we want*/

mysql> ALTER TABLE customer RENAME AS people_who_owe_me_money;
/*You can even change the name of your table if you really want to*/


mysql> ALTER TABLE customer ADD FULLTEXT INDEX(comments);
/*Makes searching faster and more precise. Comments column can now contain multiple values*/


_________________________________________________________
mysql> UPDATE customer SET comments="Customer is crazy person"
    -> WHERE cust_id=5;

mysql> SELECT comments FROM customer WHERE cust_id=5;
#or
mysql> SELECT first_name,last_name,comments FROM customer WHERE MATCH(comments) AGAINST
    -> ('crazy' in boolean mode);
#or
mysql> SELECT first_name,last_name,comments FROM customer WHERE MATCH(comments) AGAINST
    -> ('crazy*' in boolean mode);

#or
mysql> SELECT first_name,last_name,comments FROM customer WHERE MATCH(comments) AGAINST
    -> ('+crazy -good*' in boolean mode);

Joins

Inner Join: join the information you want into a new temporary table

Distinct guarantees that you only have values printed out once.

mysql>SELECT DISTINCT Manufacturer_ID.Manufacturer,Manufacturer_ID.Man_ID
/*The table is on the left side of the "." and it's column is on the right side of the dot. All the tables with their respective columns that we want to join are separated by commas.*/

    ->FROM Manufacturer_ID, Model_Numbers
/*Define the tables that I want to pull this from. Ending with a colon at this point would make a full join, but we are going to perform an innerjoin*/

    ->WHERE ((Manufacturer_ID.Man_ID = Model_Numbers.Man_ID) AND (Model_Numbers.PT_ID = 32))
/*An innerjoin has clauses that will define what information is needed*/

    ->ORDER BY Manufacturer;
/*Print all that to screen in alphabetiacl order based off of the manufacturer */

Queries

Queries

mysql>SELECT * FROM customers;#everything

mysql>SELECT first_name FROM customers;
mysql>SELECT last_name, birth_date FROM customers;
mysql>SELECT * FROM customers WHERE money_owed > 10.00;
mysql>SELECT first_name FROM customers WHERE (money_owed > 10.00) AND (state='PA');
/*These are not referencing any other databases that I have mentioned so far. These are just how to examples*/

mysql>SELECT manufacturer FROM manufacturer_id;
#select record (column) from table

mysql>SELECT manufacturer FROM manufacturer_id ORDER BY manufacturer;
#select returns the column: alphabetized a - z

mysql>SELECT manufacturer FROM manufacturer_id ORDER BY manufacturer DESC;
#z - a

mysql>SELECT * FROM manufacturer_id WHERE man_id=5;
#Stipulates equals 5. This is not asking for everything that equals 5. This is asking to return everything from row that has the ID of 5

mysql>SELECT * FROM manufacturer_id WHERE man_id BETWEEN 5 AND 10;
#Will show manufacturers 5 through 10

mysql>SELECT * FROM manufacturer_id WHERE man_id=2 OR man_id=10;
#You can add as many OR commands as you want

mysql>SELECT * FROM manufacturer_id WHERE man_id >= 280;
#

mysql>SELECT * FROM manufacturer_id WHERE man_id >= 100 AND man_id <=110;
#If you don't know what this is doing you have read too far

mysql>SELECT * FROM manufacturer_id WHERE manufacturer LIKE 'Data%';
# any manufacturer that starts with Data + ending with whatever

mysql>SELECT * FROM manufacturer_id WHERE manufacturer LIKE 'Data_____';
# Data + 5 underscores will return manufacturers that start with data + ending in 5 more characters

mysql>SELECT * FROM manufacturer_id WHERE IN ("Alden","Addmaster");
# More specific

mysql>SELECT * FROM manufacturer_id WHERE manufacturer LIKE '%nics';
# ending with nics


      R E G E X  

mysql>SELECT * FROM manufacturer_id WHERE manufacturer LIKE 'Data%';
# any manufacturer that starts with Data + ending with whatever

mysql>SELECT DESCRIP from model_numbers WHERE Descrip REGEX "^.*[8.5\11]+.*UNIVERSAL";
/* ^ means the beginning of the string of information

   .* any single character except \n

   * means any number of series of characters

   [8.5\11] means that it will be followed by 8.5 or 11. 

   + expected to match for any one or more of the characters that precede it (8.5 or 11)
   
   *followed by any number of characters

   *followed by any number of UNIVERSAL
*/

mysql>SELECT DESCRIP from model_numbers WHERE Descrip REGEX "^.{1,3}8{2}.*$";
/* ^ means the beginning of the string of information

   .* any single character except \n

   {1,3} means a string of 1 to 3 characters that are going to start off the string


   8{2} followed by an 2 8s (same as 88)

   .* Any single of characters

   $ This is where the string should end
*/

/*****************************************

• .* 0 or more of any single character
• .+ 1 or more of any single character
• .? 0 or 1 of any single character
• .+ 1 or more of any single character
• 8{2} two 8s (same as 88)
• 8{2,} Two or more eights
• 8{2,4} Two to four eights
• 8{,4} max 4 eights

****************************************************/

mysql>SELECT DESCRIP AS Description FROM model_numbers WHERE Descrip REGEX "^.{1,3}8{2}.*$";
#Aliasing will change the outputted column name from DESCRP to description
     
mysql>SELECT manufacturer FROM manufacturer_id WHERE manufacturer REGEX "^[^a]*$";
#[^a] negates the letter 'a.' This means that the letter a should not be present at all

mysql>SELECT manufacturer FROM manufacturer_id WHERE manufacturer REGEX "^[^aei]*$" LIMIT 5;
#returns the first 5

mysql>SELECT manufacturer FROM manufacturer_id WHERE manufacturer REGEX "^[^aei]*$" LIMIT 5,10;
#retursn the 5th through 10th

Insert Data

To see customers table
mysql>desc customers;

To insert data

first_name | last_name | state | brith_date | sex | CUST_ID | last_meeting | money_owed

mysql>INSERT INTO customers VALUES ('Paul', 'Jones', 'PA', '1972-10-2', 'M', NULL, NULL, '54.96' );
#NULL is used for CUST_ID because it is set to AUTO_INC 

To insert data in specified columns

mysql>INSERT INTO customers VALUES(last_name,first_name) values ('Paul', 'Jones');

mysql>INSERT INTO customers VALUES(last_name,first_name) values ('Paul', 'Jones');

mysql> insert into phone values (NULL, '444-555-6666', '');

CSV

Comma Separated Value file

Create the file in excel

put values in quotes and separated with commas.


<?php
//Setup the constants
DEFINE ('DBUSER', 'username');
DEFINE ('DBPW', 'password');
DEFINE ('DBHOST', 'localhost');
DEFINE ('DBNAME', 'customer');

if ($dbc = mysql_connect(DBHOST, DBUSER, DBPW)) 
   {
      if (!mysql_select_db(DBNAME))
         {
            trigger_error("Couldn't select database<br>MySQL Error: " . mysql_error());
            exit();
         }
   } else {
   trigger_error("Couldn't select database<br>MySQL Error: " . mysql_error());
}

$file = fopen("myfile.csv", "r");

while(($info = fgetcsv($file, 1000, ',')) != FALSE)
{
   $getdata = implode("," $info);
   $query = "INSERT INTO manufacturer (man_id,manifacturer_name) VALUES (' . $getdata . ')";
   echo $query . "<br>";

   $result = mysql_query($query) or trigger_error("Query<br>MySQL Error" .  mysql_error());
}

mysql_close();
?>

Normalization

Each column must contain only 1 value

Bad:

mysql> CREATE TABLE customer
    -> (
    -> name VARCHAR(45) NOT NULL, Jose Mayorquin....

No repeating values

Bad:

mysql> CREATE TABLE customer
    -> (
    -> name VARCHAR(45) NOT NULL,
    -> address VARCHAR(1000) NOT NULL,
    -> phone1 VARCHAR(30) NOT NULL,
    -> phone2 VARCHAR(30) NOT NULL...........


It would be better to create another table that just contains phone numbers since people are likely to have multiple phone numbers, and break address up into 'address', 'street', 'city', 'state', and 'zip.'

If you want to escape from a mistake:

mysql> CREATE TABLE customer
    -> (
    -> name VARCHAR(45) NOT NULL,
    -> address VARCHAR(1000o0o0o0o) NOT NULL,
    -> phone1 VARCHAR(30) NOT NULL,
    -> phone2 VARCHAR(30) NOT NULL,#What do I do?
    -> \c

Better:

mysql> CREATE TABLE customer
    -> (
    -> first_name VARCHAR(15) NOT NULL,
    -> Last_name VARCHAR(25) NOT NULL,
    -> address VARCHAR(25) NOT NULL,
    -> street VARCHAR(40) NOT NULL,
    -> city VARCHAR(20) NOT NULL,
    -> state VARCHAR(2) NOT NULL,
    -> zip SMALLINT(5) NOT NULL,
    -> phone_id TINYINT(1) UNSIGNED NOT NULL,#Foreign Key
    -> order_id SMALLINT(5)#Another Foreign Key
    -> cust_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY(cust_id)#Notify SQL what the primary key is
    -> );
This would be even better if the address columns were taken out and made into their own table. You can not have anything in  table that does not directly relate to the primary key: that is what the foreign keys are for. City, State and Zip are not personal to the customer and should therefore be in a separate table that is related by the address foreign key.

mysql> CREATE TABLE phone
    -> phone_id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
    -> number VARCHAR(40) NOT NULL,
    -> number VARCHAR(40) NOT NULL,
    -> PRIMARY KEY(phone_id)
    -> );

mysql> CREATE TABLE orders
    -> product number VARCHAR(40) NOT NULL,
    -> prod_quant SMALLINT(5) UNSIGNED NOT NULL,
    -> order_date DATE NOT NULL,
    -> price FLOAT NOT NULL,
    -> order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY(order_id)
    -> );#This is a bad example of an orders table

Load from SQL file

Import this file create_customer.sql

In notepad++ just rightclick on the filetab to copy the absolute path

------------------------------------------------------------- mysql>quit;
C:\Users\SupahMan\mysql customer < C\:wamp\www\php\create_customer.sql -u root -p
Enter password: *****************************************