A SQL database is an organized collection of data. Or at least that’s what they taught me in college. In real life, it’s only as organized as the people putting data into the database. Databases contain schemas, tables, stored procedures, reports, views and other objects. Most databases will contain multiple tables. Tables contain rows that have data in them. I like to think of a database kinda’ like an Excel spreadsheet. Each tab on a spreadsheet is similar to a table; each row is similar to a row in a database and each column in the spreadsheet is somewhat similar to a column, or attribute. The headers are kinda’ like the schema.
These are overly simplistic explanations. And whenever you oversimplify something, you run the risk of miscommunication, but it helps as a starting place. This page is meant to be a short and easy guide to get started writing SQL queries. More links will appear throughout the page that point to other posts on my site, so stay tuned. Throughout my exercises in this page, I will use the following sample database, which has five records (one for each ID) and seven columns (ID,Site,Contact,Address,City,Zip, and Country).
Below is a selection from the “Customers” table (note that when querying data, SQL commands are NOT case sensitive)
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
Most tasks you will execute against a database are done with SQL statements. Think of this 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;
The SELECT statement is the most common command I run in SQL. This is how you query data, build reports, derive the layout of a database and so, so much more.
Other Important SQL Commands covered in this series (if there is no link, I haven’t written that article yet):
krypted January 26th, 2016
Posted In: SQL
I have another article up on the world webs. This one is on cloud use in small businesses, with IT Business Edge. Check it out at http://www.itbusinessedge.com/slideshows/6-ways-small-businesses-can-master-the-cloud-in-2016-08.html.
krypted January 20th, 2016
Ever since the kids from Silicon Valley went to TechCrunch, I’ve been thinking that at some point I’d want to put a piece there. Luckily, I recently got the chance. Today, 16 Apple Security Advances To Take Note Of In 2016 went up on TechCrunch. You can access the article here.
The original article actually listed the year that each was introduced in order. It was a lot of work to go back in time and piece the timeline together, so since the years didn’t make it through editorial, I list them here (not that anyone actually cares):
And yes, since I was there for each of these, I did feel old writing this… :-/
And yes, thank you for asking, I did just publish another book on Mac Security, which you can buy here.
krypted January 18th, 2016
It can be tough to get information about larger Mac deployments. I’ve written a few books on it. Apple has built some pages on it. But many prefer to consume their content through video. As such, Sean Collins has teamed up with Lynda.com to put together an IT Administrator’s Guide for El Capitan. With topics ranging from SIP to DEP, and all the acronyms in the middle, Sean’s soothing voice will guide you through what you need to get started with a new Mac deployment.
Many a job can seem daunting, but with this latest addition to our arsenal, you’ll instantly feel less intimidated. It’s like the Sun A of the Mac world. But afterwards, when you go into corpse pose, you won’t fall asleep, because the content is too good. Check it out here:
krypted January 15th, 2016
If you’re interested in Mac Security, the next edition of my Enterprise Mac Security book is now shipping. You can get it here http://www.amazon.com/Enterprise-Mac-Security-OS/dp/148421711X. The book is shipping from 3rd party sellers, but should ship directly from Amazon soon at the regular price. I don’t usually know exactly when, but it should also appear for Kindle and on the Apple Books store as well. Hope you enjoy!
krypted January 12th, 2016
krypted January 11th, 2016
Posted In: personal
We really just stopped for chips and a Coke (everything carbonated was a Coke to me back then, being from the South). At the cash register, we thought that a cassette by a band called The Sex Pistols was about the funniest thing you could buy. I think we were 13 or 14 years old at the time. It was Nevermind the Bollocks. We started a band that week. Eventually, we all kinda’ lost interest (there wasn’t a Jody, but if there was he would have quit and got married).
I now have drums in the basement again. But it has been a very long time since I played with an actual band, and a very, very long time since I penned an original song. But during that time, I’ve been lucky to see some pretty awesome shows. And some terrible ones. Few were as bad as whatever the name of the band was the first time I walked into CBGB in New York. Few beers were as watered down. And no bathroom has smelled as bad.
But this was the birthplace of The Police, The Talking Heads, Blondie, The Ramones, Iggy Pop, and so, so, so many more bands. I went there three more times. And each time I think I saw a progressively worse band play. I would have kept going anyway, just to be there (I probably just went on bad nights). I just enjoyed looking at torn concert posters on the wall. I’ve gotten to hang out at venues all over the world, from the 40 Watt Club (I went to college in Athens, after all – thank this place for REM, the B-52s, and many more), to the Troubadour, the Whisky a Go Go, and the Roxy, in Los Angeles, and everything in between.
But CBGB was one of my favorites. It was always a weird cab ride (although every cab driver knew exactly how to get there, without me having to say anything other than CBGB). I always hoped I’d happen to walk in and see one of those magical nights where Patti Smith or some other idol of their heyday was on stage randomly. That never happened. But you could feel the energy of something amazing that had happened there. The launch of something special. After seeing some pretty amazing shows at places like the Troubadour and Whisky in LA, I always felt like that feeling wore off. But it never did with CBGB. I’d go back to my hotel room before I went and change out of a suit and into jeans and a plain black shirt and find my way there whenever I could. I used to always pack a plain black shirt no matter where I was going. I never got to tap into that magic that they had. But I was happy enough to bask in its glow (er, aura) for awhile.
These days, I see a CBGB shirt and it puts me right back in my garage, playing my drums. I can close my eyes and see that cassette, randomly in a convenience store in Dahlonega, Georgia. And I can remember every word to songs we wrote. Colors we dyed our hair. Weird haircuts. Broken drum heads. Cracked cymbals. Chains snapping off of bass drum pedals. Double bass drum pedals during that weird Slayer phase. I can remember that first drum set. The way it felt to sit in that stool after saving up all summer working my first job. The way I bled all over the drums when I cut my finger while playing. The way I could sit and jam with my parents to Never Been To Spain and other classic rock goodness.
All those feelings, and this article, from a shirt I saw today. Sorry if this isn’t your jam.
krypted January 11th, 2016
Posted In: personal
Linux and OS X come with the makekey command installed, usually in /usr/libexec/makekey. You can use this binary to create /etc/passwd file entries of hashed passwords. To use the command, simply pipe some text into the command. Here, we’ll echo testpassword into makekey:
echo testpassword | /usr/libexec/makekey
And we’ll get a simple output, such as:
There are certainly other ways to do something like this, but when writing a script you may use in either a Linux or OS X environment, this is one place where you should have a modicum of success crossing platforms.
krypted January 9th, 2016
One of the options thats a tad bit hidden in OS X is the Secure Erase option, which runs a multi-pass erase on a volume. Additionally, there’s no option to Secure Erase free space on a volume. But you can still securely erase whatever you’d like (other than you boot volume obviously), when needed. To do so, use the diskutil command along with the secureErase option.
The format of the command to secureErase freespace is:
diskutil secureErase freespace [level] [device]
The levels are as follows (per the man page as not all of these are specified in Disk Utility):
So for example, let’s say you had a volume called Seldon and you wanted to do a standard Single-pass zero-fill erase. In this example you would use the following:
diskutil secureErase freespace 0 /Volumes/Seldon
If you were to automate the command then you would want to dump the output into a log file. For example:
diskutil secureErase freespace 0 /Volumes/Seldon > /var/log/secureeraselog.tmp
You can also secureErase a volume itself. To erase a volume called /Volumes/Seldon, use the same structure of the command, but this time without the freespace option:
diskutil secureErase 0 /Volumes/Seldon
The latest update to Disk Utility removes a lot of options from the GUI, but overall, I have yet to find a scenario where a task I need to perform isn’t still available, if only from the command line.
krypted January 7th, 2016