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);