One of the most important aspects of searching for objects is to be able to define multiple values in a search. We looked at searching using text globbing. But the IN operator goes a step and allows you to search The IN operator allows you to specify multiple values in a WHERE clause. SELECT column FROM table WHERE column IN (value,value,...); In this article, we’ll use the same “Customers” table from our first articles: 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…
-
-
Moar About SQL Wildcards
Previously we looked at using wildcards in conjunction with the SQL LIKE operator. Wildcards allow you to search for data in a defined table. Think of them as text globbing for SQL. The wildcards available include the following: [list]: Define a ranges of characters for pattern matching [!charlist]: Matches only a character NOT specified within the brackets %: Require a single character/object in a pattern _: Allow any single character in a pattern In this article, we’ll use the same “Customers” table from our first articles: 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…
-
Update Existing SQL Records Using the UPDATE Statement
Previously we looked at finding data in a SQL database and The UPDATE statement is used to update records in a table. You can also use the UPDATE statement to update existing records in a table. When using the SQL UPDATE statement, we’ll also use the WHERE clause, as we used previously to constrain output of a SELECT statement. The WHERE locates the record(s) to be updated with syntax as follows: UPDATE table SET column=value,column=value,... WHERE column=value; The WHERE clause indicates the record(s) to update. I’ve forgotten to put it in in the past and updated every record of a database. That’s bad (unless you mean to do it). So…
-
Add New Records To MySQL Databases With The INSERT Statement
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…
-
Compound Searches With SQL Using AND && OR
Previously, we looked at the SQL SELECT and WHERE options to obtain output and then constrain what would be displayed in a query. The AND & OR operators are used to add additional logic so you can filter records based on more than one condition. Which is to say to search based on the contents of multiple columns within a single table. AND is used to show information if two different conditions are true and OR is used to show information if either condition is true. Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting…
-
Constrain SQL Queries Using WHERE and LIKE
Previously, we covered the SQL SELECT Statement, to pull data from a SQL database. Here, we’ll constrain our search for items that match a given string, or pattern using the WHERE clause to filter search results, rather than getting all of the records and parsing the output. The WHERE clause extracts records that fulfill a specified string and follows the general syntax as follows, replacing the word column with the name of the column in one of your tables and the word table with the name of a table that you’d like to search within: SQL WHERE Syntax SELECT column,column FROM table WHERE column operator value; Below is a selection…
-
Us the ORDER BY Keyword With Your SQL SELECT Statements
The ORDER BY keyword in a SQL SELECT statement is used to sort a given result-set based on the contents of one or more columns of data. By default, results are in ascending order, but you can use either ASC or DESC to indicate that you’d like results sorted in ascending or descending order, respectively. Below is a selection from the “Customers” table that we will use to run some SQL sorting statements using the ORDER BY keyword: 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…
-
Count Log Entries for a Pattern
You see a lot of entries for various things in log files. Here, we’re going to print out the number of entries with backupd in them: awk '/backupd/{print NR}' /var/log/system.log
-
FuelBand Bug or Feature?
I’ve been using a FuelBand on and off for about a year. I upgraded to the newer FuelBand SE as soon as it became available, and immediately noticed that Nike built a bunch of new badges in for users of the newer devices. As you obtained more Fuel, you can unlock the ability to earn these ever increasingly difficult to earn badges. But, I noticed something kinda’ strange. Not all of the badges were available to me that should be. In fact, only a few badges were available. But, as I continued to obtain more Fuel, all the new badges unlocked. All of the badges from the Nike Running app…
-
Working with Postgres from the Command Line in Lion Server
Mac OS X Server 10.7, Lion Server, comes with a few substantial back-end changes. One of these is the move from SQLite3 to PostgreSQL for many of the back-end databases, including Wiki and Podcast Producer (collab), Webmail (roundcubemail), iCal Server and Address Book Server (caldav) and as the back-end to the newest service in Lion Server, Profile Manager (device_management). As such, it’s now important to be able to use PostgreSQL the way we once used SQLite3, when trying to augment the data that these databases contains, as there currently aren’t a lot of options for editing this data (aside from manually of course). Postgres has a number of commands that…