krypted.com

Tiny Deathstars of Foulness

Sometimes you need to write a record into a table in a SQL database. The INSERT INTO statement creates new records in a table and can work in one of two ways. The first form does not specify the column names where the data will be inserted, only their values. When doing so, each value needs to be inserted in the columned order they appear, here the table being the name of the table you’re adding a record into and each value would be replaced with the contents of your value (don’t insert the string ‘value’ into each!): INSERT INTO table VALUES (value,value,value,...); If you don’t have every value to insert, you can also list the columns to insert data in and then include the values in the same order that the columns are listed in. The second form specifies both the column names and the values to insert: INSERT INTO table (column,column,column,...) VALUES (value,value,value,...); Below is a selection from the “Customers” table that I put in the first article. We will use it to run some SQL statements using the INSERT keyword so that we can add data to our little database: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US To insert a new row in the “Customers” table, we’ll add a row for Huffington Post with Arianna Huffington as the owner, her address as “The Library, with a city of Los Angeles, a zip of 90077, and a country of the US. INSERT INTO Customers (Site, Contact, Address, City, Zip, Country) VALUES ('Huffington Post','Arianna Huffington','The Library','Los Angeles','90077','US'); The selection from the “Customers” table will now look like this, after the ID record incremented on its own, taking the next available integer: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 6 Huffington Post Arianna Huffington The Library Los Angeles 90077 US As mentioned, you can also Insert Data Only in Specified Columns It is also possible to only insert data in specific columns. The following SQL statement will insert a new row, but only insert data in the Site, Contact, and Country columns: INSERT INTO Customers (Site, Contact, Country) VALUES ('Spotify', 'Daniel Elk', 'SE'); The selection from the “Customers” table will now look like this: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 6 Huffington Post Arianna Huffington The Library Los Angeles 90077 US 7 Spotify Daniel Elk SE Overall, adding rows to SQL tables is really straight forward. I mean, you’re not replacing anything… Yet…

February 3rd, 2016

Posted In: SQL

Tags: , , , , , , , ,

Most tasks you will execute against a database are done with SQL statements. Think of statements as a query, an insert, a change, or a delete operating. For example, to see all of your data, you would select all of the records from a database using the SELECT statement. Then we’ll ask for all, or *, and tell the command to show us where the data is coming from, which is the Customers table. Finally, we’ll be nice and tidy and put a semi-colon at the end; although if you forget, you can always do so after you hit return: SELECT * FROM Customers; As can be seen above, the SELECT statement is used to select data from a database. Results are stored in a result table that is simply called the result-set. The syntax to run a select is to run SELECT followed by a list of columns separated by commas and then a FROM statement to indicate which table of a database you’ll query followed by the name of the table and then a semi-colon (;). SELECT column name,column name FROM table name; The initial SELECT that we ran used an * instead of any column names. This worked because the * is a wild card that pulls all data. As indicated in the above syntax though, we can constrain our output to only the columns we care about. Below is the “Customers” table from our first article: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US This SQL statement selects the “ID” and “Site” columns from the “Customers” table: SELECT ID,Site FROM Customers; The result would be as follows: ID Site 1 Krypted 2 Apple 3 Microsoft 4 Facebook 5 JAMF In a table, each column can contain duplicate values (for example, multiple parties could be in the same city or multiple items for sale can have the same price. The DISTINCT keyword can be used to return only unique values, similar to the uniq command in bash. The syntax for the DISTINCT statement is as follows: SELECT DISTINCT column,column FROM table; The following SQL statement selects only the distinct values from the “City” columns from the “Customers” table in our example database: SELECT DISTINCT City FROM Customers; You can also search for data The WHERE clause is used to extract only those records that fulfill a specified criterion. SQL WHERE Syntax SELECT column_name,column_name FROM table_name WHERE column_name operator value; SELECT * FROM Customers WHERE Country=’US’; As you can see in the above, we used single quotes around text. We could have also used double-quotes. You do not need to quote numbers, as seen below: SELECT * FROM Customers WHERE ID=1; We used an = operator, but it’s worth noting that there are a number of others that can be super-helpful. The following operators are available when using a WHERE clause:
  • = Equal
  • <>  or != Not equal to
  • > Greater than
  • IN Indicates multiple potential values for a column
  • < Less than
  • >= Greater than or equal
  • <= Less than or equal
  • BETWEEN Between an inclusive range
  • LIKE Looks for a provided pattern

January 30th, 2016

Posted In: SQL

Tags: , , , , ,