{"id":1911,"date":"2014-05-08T22:17:58","date_gmt":"2014-05-08T22:17:58","guid":{"rendered":"http:\/\/putridparrot.com\/blog\/?p=1911"},"modified":"2014-05-08T22:17:58","modified_gmt":"2014-05-08T22:17:58","slug":"sql-basics-part-3","status":"publish","type":"post","link":"https:\/\/putridparrot.com\/blog\/sql-basics-part-3\/","title":{"rendered":"SQL Basics (Part 3)"},"content":{"rendered":"

CRUD<\/strong><\/p>\n

In previous SQL Basics posts we’ve looked at querying the database, but we actually need to get some data into the database. So let’s go over the C<\/strong>reate, R<\/strong>etrieve, U<\/strong>pdate, D<\/strong>elete commands.<\/p>\n

Create<\/strong><\/p>\n

To create data in SQL we use the INSERT keyword, for example<\/p>\n

\r\nINSERT INTO Plants (CommonName, Type_Id) VALUES ('Strawberry', 2)\r\n<\/pre>\n
This creates a new plant with the CommonName Strawberry and the Type_Id 2.<\/p>\n
Retreieve<\/strong><\/p>\n
We’re not going to go through this again as we’ve dealt with the SELECT query already (this is the way we retrieve data using SQL). <\/p>\n
Update<\/strong><\/p>\n
The UPDATE keyword is used to alter data with SQL. <\/p>\n
Beware, if no WHERE clause is used then ALL rows will be updated<\/strong><\/p>\n
\r\nUPDATE Plants p SET p.CommonName = 'Strawberry' where p.Id = 123\r\n<\/pre>\n
Delete<\/strong><\/p>\n
DELETE is used to remove items from the database using SQL. We can delete one or more rows at a time but only from one table at a time, for example<\/p>\n
\r\nDELETE FROM Plants\r\n<\/pre>\n
Beware you can easily delete ALL rows from a table if you do not specify a WHERE clause<\/strong><\/p>\n
Transactions<\/strong><\/p>\n
We’ve finished looking at the CRUD operations but you’ve probably noted some of the pitfalls of not correctly forming your DELETE or UPDATE queries. So let’s look a transactions which allow us to make changes which are not permanent until the transaction is completed.<\/p>\n
First off, let’s look at the concept of ACID…<\/p>\n
ACID stands for A<\/strong>tomic, C<\/strong>onsistent, I<\/strong>solated and finally D<\/strong>urable.<\/p>\n
A transaction is said to be atomic in that it either happens or doesn’t happen, i.e. we cannot have a partially altered set of data. It’s consistent if the transaction leaves the database in a consistent state. It’s isolated in that it occurs in a serial way and finally it’s durable if it’s “permanently” stored, i.e. it’s not kept in memory but stored on disc so it will still be available after a reboot (for example). <\/p>\n
Transaction syntax requires we tell the database we’re beginning a transaction, then we run our SQL command before either committing the transaction or rolling it back.  For example<\/p>\n
\r\nBEGIN TRANSACTION\r\nDELETE FROM Plants\r\n--ROLLBACK TRANSACTION\r\n--COMMIT TRANSACTION\r\n<\/pre>\n
Now in the sample above I have started a transaction and deleted all Plants. If I now try to get all rows from the Plants table I’ll find they’re all gone. But uncommenting the ROLLBACK will allow us to cancel the transaction and return all the rows we seemed to have deleted.<\/p>\n
Obviously had this been our intention then we could alternatively just uncomment the COMMIT transaction and commit out changes.<\/p>\n","protected":false},"excerpt":{"rendered":"
CRUD In previous SQL Basics posts we’ve looked at querying the database, but we actually need to get some data into the database. So let’s go over the Create, Retrieve, Update, Delete commands. Create To create data in SQL we use the INSERT keyword, for example INSERT INTO Plants (CommonName, Type_Id) VALUES (‘Strawberry’, 2) This […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[22,10],"tags":[],"class_list":["post-1911","post","type-post","status-publish","format-standard","hentry","category-database","category-sql"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/1911","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/comments?post=1911"}],"version-history":[{"count":8,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/1911\/revisions"}],"predecessor-version":[{"id":1974,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/posts\/1911\/revisions\/1974"}],"wp:attachment":[{"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/media?parent=1911"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/categories?post=1911"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/putridparrot.com\/blog\/wp-json\/wp\/v2\/tags?post=1911"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}