{"id":1003,"date":"2023-11-18T23:15:34","date_gmt":"2023-11-18T23:15:34","guid":{"rendered":"https:\/\/s-oneill.com\/sov02\/?p=1003"},"modified":"2023-11-18T23:19:49","modified_gmt":"2023-11-18T23:19:49","slug":"sql-cheatsheet","status":"publish","type":"post","link":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/","title":{"rendered":"SQL Cheatsheet"},"content":{"rendered":"

CREATE TABLE<\/strong> table_name (col_1_name col_1_type, column_2_name column_2_type…);
\ne.g.
\nCREATE TABLE items (id INTEGER, name TEXT);<\/code><\/p>\n

add PRIMARY KEY<\/strong> and AUTOINCREMENT<\/strong> keywords to the id field while creating the table, like so:<\/p>\n

CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);<\/code><\/p>\n

To automatically increment ids on each insertion, no need to define id when inserting items.
\nPrimary key means the column is for unique ids<\/p>\n

INSERT INTO <\/strong>table_name (column_1_name, column_2_name) VALUES (value_1, value_2);
\ne.g.
\nINSERT INTO <\/strong>items (id, name) VALUES (2, \"Book\");<\/code><\/p>\n

items table:
\n\tid \t|\tname
\n\t2 \t|\tBook<\/p>\n

UPDATE<\/strong> table_name SET column_n_name = value_1 WHERE column_n_name = value_2;
\ne.g.
\nUPDATE<\/strong> items SET name = \"Novel\" WHERE id = 2;<\/code><\/p>\n

items table
\n\tid \t|\tname
\n\t2 \t|\tNovel<\/p>\n

SELECT<\/strong> column_n_name, column_n_name… FROM table_name;
\ne.g.
\nSELECT id, name FROM items;<\/code><\/p>\n

This will return all id and name values from all rows in the items table:<\/p>\n

a ‘*’ wildcard also represents all columns:<\/p>\n

SELECT * FROM items;<\/code>
\nreturns all of the rows:<\/p>\n

Use the WHERE<\/strong> statement to narrow the returned values<\/p>\n

SELECT * FROM items WHERE name = \"Novel\";<\/code>
\nreturns all data from row(s) where the name col value is is “Novel”<\/p>\n

ORDER BY <\/strong>will sort the results by the column you choose (default ASC<\/strong>).
\nTo reverse the order add DESC<\/strong> after the column name (ascending or descending).<\/p>\n

SELECT * FROM items WHERE name = \"Novel\" >ORDER BY id DESC;<\/code>
\nYou can also use logical operators like AND and OR to connect more statements.<\/p>\n

SELECT * FROM items WHERE name = “Novel” AND id = 2;<\/p>\n

DELETE<\/strong> FROM table_name WHERE column_n_name = value;
\ne.g.
\nDELETE FROM items WHERE id = 2;<\/code><\/p>\n

DROP TABLE<\/strong> table_name;
\ne.g.
\nDROP TABLE items;<\/code><\/p>\n

You can also create a condition to only delete the table if it exists.
\nDROP TABLE IF EXISTS items;<\/code><\/p>\n

————————–<\/p>\n

FOREIGN KEYS<\/strong>
\nWe can define relationships between tables.
\nAn items table may have multiple items that have the same category .<\/p>\n

items table
\n\tid\t|\t name \t|\tcategory
\n\t1\t|\t Apple\t|\t Fruit
\n\t2\t|\t Hammer\t|\t Tools
\n\t3\t|\t Wrench\t|\t Tools<\/p>\n

Instead of storing the same category string multiple times,
\nwe can create another table which will store all of the categories.<\/p>\n

categories table
\n\tid\t|\tcategory
\n\t1\t|\t Fruit
\n\t2\t|\t Tools<\/p>\n

Now we can just create a reference column inside of the items table, instead of storing strings, we can now store id ‘s from the categories table.<\/p>\n

items table
\n\tid\t|\t name \t|\tcategory_id
\n\t1\t|\t Apple\t|\t 1
\n\t2\t|\t Hammer\t|\t 2
\n\t3\t|\t Wrench\t|\t 2<\/p>\n

We can connect this category_id column with the id column from the categories database
\nwith the help of FOREIGN KEYS. <\/p>\n

FOREIGN KEY<\/strong> will define the connection. <\/p>\n

Here is the SQL for creating these tables:<\/p>\n

CREATE TABLE categories (
\n\tid INTEGER PRIMARY KEY AUTOINCREMENT,
\n\tname TEXT
\n);
\nCREATE TABLE items (
\n\tid INTEGER PRIMARY KEY AUTOINCREMENT,
\n\tname TEXT,
\n\tcategory_id INTEGER,
\n\tFOREIGN KEY(category_id) REFERENCES categories(id)
\n);<\/code><\/p>\n

Joins<\/strong> (Inner, Left, Right and Full Joins)
\nA JOIN clause combines rows from two or more tables based on related columns.<\/p>\n

INNER JOIN <\/strong> selects records that have matching values in multiple tables.<\/p>\n

Review: This SELECT will print out the rows from the items table created earlier<\/p>\n

SELECT id, name, category_id FROM items;<\/code><\/p>\n

items table
\n\tid\t|\t name \t|\tcategory_id
\n\t1\t|\t Apple\t|\t 1
\n\t2\t|\t Hammer\t|\t 2
\n\t3\t|\t Wrench\t|\t 2<\/p>\n

But we want to see the category name not the category id.
\nINNER JOIN<\/strong> can join two tables together using the ON keyword with a foreign key
\nfor example:<\/p>\n

SELECT *
\nFROM items
\nINNER JOIN categories ON items.category_id = categories.id;<\/code><\/p>\n

… will join the tables together on the appropriate columns.<\/p>\n

items table joined with categories
\n\tid \t|\tname \t|\tcategory_id \t|\tid \t|\tname
\n\t1 \t|\tApple\t\t|\t\t 1\t\t|\t 1\t|\t Fruit
\n\t2\t|\tHammer\t|\t\t 2\t\t|\t 2\t|\tTools
\n\t3\t|\tWrench\t|\t\t 2\t\t|\t 2\t|\tTools<\/p>\n

Instead of printing all of the columns, you can pick which ones to print.
\nColumns with the same name in both tables have to be prefixed with the table name.
\nSince this is the case, it’s better to prefix all of the columns when doing INNER JOIN , just to be sure.<\/p>\n

SELECT items.id, items.name, categories.name
\nFROM items
\nINNER JOIN categories ON items.category_id = categories.id;<\/code><\/p>\n

items table joined with categories
\n\titems.id \t|\t items.name \t|\t categories.name
\n\t\t1 \t|\t Apple \t\t|\tFruit
\n\t\t2 \t|\t Hammer \t\t|\tTools
\n\t\t3 \t|\t Wrench \t\t|\tTools<\/p>\n

Now we are listing the category name next to items –
\nwithout repeating category name strings in the items table.<\/p>\n

Since joins can involve many tables, queries can get long.
\nYou can use AS<\/strong> to create aliases<\/strong> for each table and use those aliases throughout the query.<\/p>\n

SELECT i.id, i.name, c.name
\nFROM items AS i
\nINNER JOIN categories AS c ON i.category_id = c.id;<\/code><\/p>\n

LEFT (OUTER) JOIN: <\/strong>Returns all records from the left table, and the matched records from the right table
\n For the rows for which there is no matching row on the right side, the result-set will contain null. <\/p>\n

SELECT *
\nFROM Orders
\nLEFT JOIN Customers
\nON items.category_id = categories.category_id
\n;<\/strong><\/p>\n

RIGHT (OUTER) JOIN:<\/strong> Returns all records from the right table, and the matched records from the left table
\nFor the rows for which there is no matching row on the left side, the result-set will contain null. <\/p>\n

FULL (OUTER) JOIN:<\/strong> Returns all records when there is a match in either left or right table
\nThe result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.<\/p>\n

—————————<\/p>\n

COUNT<\/strong>
\nCalculates the number of rows returned by a query.<\/p>\n

SELECT COUNT(*) FROM items WHERE name = \"Apple\"<\/code><\/p>\n

This will return 1 <\/p>\n

LIKE<\/strong>
\nThe LIKE<\/strong> keyword can be used with WHERE<\/strong> to find substrings in some column.
\nFor example, if you want all of the rows which have the name that starts with App.<\/p>\n

SELECT id, name FROM items WHERE name LIKE 'App%';<\/code><\/p>\n

items\t table
\n\tid\t|\t name
\n\t1\t|\t Apple<\/p>\n

* see
\nhttps:\/\/www.geeksforgeeks.org\/sql-join-set-1-inner-left-right-and-full-joins\/
\nhttps:\/\/www.w3schools.com\/sql\/sql_join.asp
\nhttps:\/\/en.wikipedia.org\/wiki\/Join_(SQL)
\nhttps:\/\/www.tutorialspoint.com\/sql\/sql-using-joins.htm<\/p>\n","protected":false},"excerpt":{"rendered":"

CREATE TABLE table_name (col_1_name col_1_type, column_2_name column_2_type…); e.g. CREATE TABLE items (id INTEGER, name TEXT); add PRIMARY KEY and AUTOINCREMENT keywords to the id field while creating the table, like so: CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); To automatically increment ids on each insertion, no need to define id when inserting […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1003","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\nSQL Cheatsheet - s-oneill.com<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Cheatsheet - s-oneill.com\" \/>\n<meta property=\"og:description\" content=\"CREATE TABLE table_name (col_1_name col_1_type, column_2_name column_2_type…); e.g. CREATE TABLE items (id INTEGER, name TEXT); add PRIMARY KEY and AUTOINCREMENT keywords to the id field while creating the table, like so: CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); To automatically increment ids on each insertion, no need to define id when inserting […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/\" \/>\n<meta property=\"og:site_name\" content=\"s-oneill.com\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-18T23:15:34+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-18T23:19:49+00:00\" \/>\n<meta name=\"author\" content=\"Me(sean)\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Me(sean)\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/\"},\"author\":{\"name\":\"Me(sean)\",\"@id\":\"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/ab11148c6ea273be80dddd8baec1a545\"},\"headline\":\"SQL Cheatsheet\",\"datePublished\":\"2023-11-18T23:15:34+00:00\",\"dateModified\":\"2023-11-18T23:19:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/\"},\"wordCount\":801,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/a9217b5797251a243dfd504d7ad7d00a\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/\",\"url\":\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/\",\"name\":\"SQL Cheatsheet - s-oneill.com\",\"isPartOf\":{\"@id\":\"https:\/\/s-oneill.com\/sov02\/#website\"},\"datePublished\":\"2023-11-18T23:15:34+00:00\",\"dateModified\":\"2023-11-18T23:19:49+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/s-oneill.com\/sov02\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Cheatsheet\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/s-oneill.com\/sov02\/#website\",\"url\":\"https:\/\/s-oneill.com\/sov02\/\",\"name\":\"s-oneill.com\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/a9217b5797251a243dfd504d7ad7d00a\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/s-oneill.com\/sov02\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/a9217b5797251a243dfd504d7ad7d00a\",\"name\":\"sean o\",\"logo\":{\"@id\":\"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/image\/\"},\"description\":\"Web dev over 15 years.\",\"sameAs\":[\"http:\/\/s-oneill.com\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/ab11148c6ea273be80dddd8baec1a545\",\"name\":\"Me(sean)\",\"url\":\"https:\/\/s-oneill.com\/sov02\/author\/user01\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Cheatsheet - s-oneill.com","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/","og_locale":"en_US","og_type":"article","og_title":"SQL Cheatsheet - s-oneill.com","og_description":"CREATE TABLE table_name (col_1_name col_1_type, column_2_name column_2_type…); e.g. CREATE TABLE items (id INTEGER, name TEXT); add PRIMARY KEY and AUTOINCREMENT keywords to the id field while creating the table, like so: CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); To automatically increment ids on each insertion, no need to define id when inserting […]","og_url":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/","og_site_name":"s-oneill.com","article_published_time":"2023-11-18T23:15:34+00:00","article_modified_time":"2023-11-18T23:19:49+00:00","author":"Me(sean)","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Me(sean)","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/#article","isPartOf":{"@id":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/"},"author":{"name":"Me(sean)","@id":"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/ab11148c6ea273be80dddd8baec1a545"},"headline":"SQL Cheatsheet","datePublished":"2023-11-18T23:15:34+00:00","dateModified":"2023-11-18T23:19:49+00:00","mainEntityOfPage":{"@id":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/"},"wordCount":801,"commentCount":0,"publisher":{"@id":"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/a9217b5797251a243dfd504d7ad7d00a"},"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/","url":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/","name":"SQL Cheatsheet - s-oneill.com","isPartOf":{"@id":"https:\/\/s-oneill.com\/sov02\/#website"},"datePublished":"2023-11-18T23:15:34+00:00","dateModified":"2023-11-18T23:19:49+00:00","breadcrumb":{"@id":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/s-oneill.com\/sov02\/sql-cheatsheet\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/s-oneill.com\/sov02\/"},{"@type":"ListItem","position":2,"name":"SQL Cheatsheet"}]},{"@type":"WebSite","@id":"https:\/\/s-oneill.com\/sov02\/#website","url":"https:\/\/s-oneill.com\/sov02\/","name":"s-oneill.com","description":"","publisher":{"@id":"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/a9217b5797251a243dfd504d7ad7d00a"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/s-oneill.com\/sov02\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/a9217b5797251a243dfd504d7ad7d00a","name":"sean o","logo":{"@id":"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/image\/"},"description":"Web dev over 15 years.","sameAs":["http:\/\/s-oneill.com"]},{"@type":"Person","@id":"https:\/\/s-oneill.com\/sov02\/#\/schema\/person\/ab11148c6ea273be80dddd8baec1a545","name":"Me(sean)","url":"https:\/\/s-oneill.com\/sov02\/author\/user01\/"}]}},"_links":{"self":[{"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/posts\/1003","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/comments?post=1003"}],"version-history":[{"count":6,"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/posts\/1003\/revisions"}],"predecessor-version":[{"id":1009,"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/posts\/1003\/revisions\/1009"}],"wp:attachment":[{"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/media?parent=1003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/categories?post=1003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/s-oneill.com\/sov02\/wp-json\/wp\/v2\/tags?post=1003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}