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