{"id":851,"date":"2021-02-05T17:48:29","date_gmt":"2021-02-05T17:48:29","guid":{"rendered":"https:\/\/dft.wiki\/?p=851"},"modified":"2021-03-24T23:12:26","modified_gmt":"2021-03-24T23:12:26","slug":"sql-cheat-sheet","status":"publish","type":"post","link":"https:\/\/dft.wiki\/?p=851","title":{"rendered":"SQL (Structured Query Language) Cheat Sheet"},"content":{"rendered":"<p>VARIABLE TYPES<\/p>\n<ul>\n<li>INT\n<ul>\n<li>Whole numbers, no decimals.<\/li>\n<\/ul>\n<\/li>\n<li>DECIMAL(M,N)\n<ul>\n<li>M is the total number of numbers and N is the number of decimals.<\/li>\n<\/ul>\n<\/li>\n<li>VARCHAR(N)\n<ul>\n<li>Stores stings up to N characters.<\/li>\n<\/ul>\n<\/li>\n<li>BLOB\n<ul>\n<li>Binary Large Objects, used to store files, for example.<\/li>\n<\/ul>\n<\/li>\n<li>DATE\n<ul>\n<li>Format YYYY-MM-DD<\/li>\n<\/ul>\n<\/li>\n<li>TIMESTAMP\n<ul>\n<li>Format YYYY-MM-DD HH:MM:SS<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>COMPARISON OPERATORS<\/p>\n<ul>\n<li><strong>=<\/strong>\n<ul>\n<li>Equal to<\/li>\n<\/ul>\n<\/li>\n<li><strong>&lt;&gt;<\/strong>\n<ul>\n<li>Not equal to<\/li>\n<\/ul>\n<\/li>\n<li><strong>AND<\/strong>\n<ul>\n<li>And<\/li>\n<\/ul>\n<\/li>\n<li><strong>OR<\/strong>\n<ul>\n<li>Or<\/li>\n<\/ul>\n<\/li>\n<li><strong>&lt;<\/strong>\n<ul>\n<li>Less than<\/li>\n<\/ul>\n<\/li>\n<li><strong> &lt;=<\/strong>\n<ul>\n<li>Less than or equal to<\/li>\n<\/ul>\n<\/li>\n<li><strong> &gt;<\/strong>\n<ul>\n<li>More than<\/li>\n<\/ul>\n<\/li>\n<li><strong>&gt;=<\/strong>\n<ul>\n<li>More than or equal to<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>COMMENTING<\/p>\n<pre>--<\/pre>\n<p>CREATING DATABASE<\/p>\n<pre>CREATE DATABASE name_of_database;<\/pre>\n<p>CREATING TABLES (note the &#8220;<strong>;&#8221;<\/strong> at the end of the command and the &#8220;<strong>,&#8221;<\/strong> separating attributes):<\/p>\n<pre><strong>CREATE TABLE<\/strong> name_of_the_table (\r\nattribute_one INT <strong>PRIMARY KEY,<\/strong>\r\nattribute_two VARCHAR(10) <strong>NOT NULL<\/strong>\r\n)<strong>;<\/strong>\r\n\r\n<strong>CREATE TABLE<\/strong> name_of_the_table (\r\nattribute_one INT <strong>PRIMARY KEY,<\/strong>\r\nattribute_two VARCHAR(N) <strong>FOREIGN KEY<\/strong>\u00a0<strong>REFERENCE<\/strong> name_of_the_table(attribute_one) <strong>ON DELETE SET NULL<\/strong>\r\n)<strong>;<\/strong>\r\n\r\n<strong>CREATE TABLE<\/strong> name_of_the_table (\r\nattribute_one INT <strong>AUTO_INCREMENT,<\/strong>\r\nattribute_two VARCHAR(N) <strong>UNIQUE<\/strong>,\r\n<strong>PRIMARY KEY<\/strong>(attribute_one, attribute_two),\r\n<strong>FOREIGN KEY<\/strong>(attribute_two) <strong>REFERENCE<\/strong> name_of_the_table(attribute_one) <strong>ON DELETE SET<\/strong> <strong>CASCADE<\/strong>\r\n)<strong>;<\/strong><\/pre>\n<p>MODIFYING TABLES<\/p>\n<pre><strong>ALTER TABLE<\/strong> name_of_the_table <strong>ADD<\/strong> attibute_three DATE;\r\n<strong>ALTER TABLE<\/strong> name_of_the_table <strong>ADD<\/strong> attibute_three DECIMAL(3,1) <strong>DEFAULT<\/strong> 'default_value';\r\n<strong>ALTER TABLE<\/strong> name_of_the_table <strong>DROP COLUMN<\/strong> attribute_three;<\/pre>\n<p>DESCRIBING, DELETING, AND WHIPPING TABLES<\/p>\n<pre><strong>DESCRIBE<\/strong> name_of_the_table;\r\n<strong>DROP<\/strong> name_of_the_table;\r\n<strong>TRUNCATE<\/strong> name_of_the_table;<\/pre>\n<p>INSERTING DATA<\/p>\n<pre><strong>INSERT INTO<\/strong> name_of_the_table <strong>VALUES<\/strong>(1, 'text_one', 'text_two');\r\n<strong>INSERT INTO<\/strong> name_of_the_table<strong>(attribute_one, attribute_two)<\/strong> <strong>VALUES<\/strong>(1, 'text_one');<\/pre>\n<p>DELETING DATA<\/p>\n<pre><strong>DELETE FROM<\/strong> name_of_the_table <strong>WHERE<\/strong> attribute_one = 3;<\/pre>\n<p>CHANGING TABLES<\/p>\n<pre><strong>UPDATE<\/strong> name_of_the_table <strong>SET<\/strong> attribute_one = '3' <strong>WHERE<\/strong> attribute_one = '1' <strong>OR<\/strong> attribute_two = '2';\r\n<strong>UPDATE<\/strong> name_of_the_table <strong>SET<\/strong> attribute_one = '3', attribute_two = 'ABC' <strong>WHERE<\/strong> attribute_one = '1' <strong>AND<\/strong> attribute_two = 'XYZ';\r\n<strong>DELETE FROM<\/strong> name_of_the_table <strong>WHERE<\/strong> attribute_one = '1';<\/pre>\n<p>ACQUIRING DATA<\/p>\n<pre><strong>SELECT<\/strong> * <strong>FROM<\/strong> name_of_the_table <strong>WHERE<\/strong> 1;\r\n<strong>SELECT<\/strong> attibute_one <strong>AS<\/strong> id <strong>FROM<\/strong> name_of_the_table;\r\n<strong>SELECT<\/strong> attribute_two, attribute_three <strong>FROM<\/strong> name_of_the_table <strong>ORDER BY<\/strong> attribute_two;\r\n<strong>SELECT<\/strong> name_of_the_table.attribute_two <strong>FROM<\/strong> name_of_the_table <b>ORDER BY<\/b> attribute_two <strong>ASC<\/strong>, attribute_one <strong>DESC<\/strong>;\r\n<strong>SELECT<\/strong> attribute_two <strong>FROM<\/strong> name_of_the_table <b>LIMIT<\/b> 2;\r\n<strong>SELECT<\/strong> * <strong>FROM<\/strong> name_of_the_table <strong>WHERE<\/strong> attribute_one <strong>IN<\/strong> (1, 5, 10, 15);\r\n<strong>SELECT<\/strong> <strong>DISTINCT<\/strong> attribute_two <strong>FROM<\/strong> name_of_the_table;<\/pre>\n<p>UNION<\/p>\n<pre><strong>SELECT<\/strong> attribute_one <strong>FROM<\/strong> name_of_the_table\r\n<strong>UNION<\/strong>\r\n<strong>SELECT<\/strong> attribute_three <strong>FROM<\/strong> name_of_the_table;<\/pre>\n<p>JOINS (translate the codes from one table into another)<\/p>\n<pre><strong>SELECT<\/strong> name_of_the_table.attibute_one, name_of_other_table.attibute_two\r\n<strong>FROM<\/strong> name_of_the_table <strong>JOIN<\/strong> name_of_other_table\r\n<strong>ON<\/strong> name_of_the_table.attibute_one = name_of_other_table.attribute_two;<\/pre>\n<p>LEFT JOIN (also returns the null value from the left table)<\/p>\n<pre><strong>SELECT<\/strong> name_of_the_table.attibute_one, name_of_other_table.attibute_two\r\n<strong>FROM<\/strong> name_of_the_table <strong>LEFT JOIN<\/strong> name_of_other_table\r\n<strong>ON<\/strong> name_of_the_table.attibute_one = name_of_other_table.attribute_two;<\/pre>\n<p>RIGHT JOIN(also returns the null value from the right table)<\/p>\n<pre><strong>SELECT<\/strong> name_of_the_table.attibute_one, name_of_other_table.attibute_two\r\n<strong>FROM<\/strong> name_of_the_table <strong>RIGHT JOIN<\/strong> name_of_other_table\r\n<strong>ON<\/strong> name_of_the_table.attibute_one = name_of_other_table.attribute_two;<\/pre>\n<p>FULL JOIN<\/p>\n<pre>SELECT name_of_the_table.attibute_one, name_of_other_table.attibute_two\r\n<strong>FROM<\/strong> name_of_the_table <strong>FULL JOIN<\/strong> name_of_other_table\r\n<strong>ON<\/strong> name_of_the_table.attibute_one = name_of_other_table.attribute_two;<\/pre>\n<p>INNER JOIN<\/p>\n<pre>SELECT name_of_the_table.attibute_one, name_of_other_table.attibute_two\r\n<strong>FROM<\/strong> name_of_the_table <strong>INNER JOIN<\/strong> name_of_other_table\r\n<strong>ON<\/strong> name_of_the_table.attibute_one = name_of_other_table.attribute_two;<\/pre>\n<p>SQL FUNCTIONS<\/p>\n<pre><strong>SELECT COUNT<\/strong>(attribute_one) <strong>FROM<\/strong> name_of_the_table;\r\n<strong>SELECT AVG<\/strong>(attribute_one) <strong>FROM<\/strong> name_of_the_table;\r\n<strong>SELECT SUM<\/strong>(attribute_one) <strong>FROM<\/strong> name_of_the_table;\r\n<strong>SELECT COUNT<\/strong>(attribute_one), attribute_one <strong>FROM<\/strong> name_of_the_table <strong>GROUP BY<\/strong> attribute_one;\r\n<strong>SELECT SUM<\/strong>(attibrute_three), attribute_one <strong>FROM<\/strong> name_of_the_table <strong>GROUP BY<\/strong> attribute_one;<\/pre>\n<p>WILDCARDS<\/p>\n<ul>\n<li>%\n<ul>\n<li>any number of characters.<\/li>\n<\/ul>\n<\/li>\n<li>_\n<ul>\n<li>one character.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<pre><strong>SELECT<\/strong> * <strong>FROM<\/strong> name_of_the_table <strong>WHERE<\/strong> attribute_two <strong>LIKE<\/strong> '%word_';<\/pre>\n<p>NESTED QUERIES (fetched data from one query feeds another query)<\/p>\n<pre><strong>SELECT<\/strong> name_of_the_table.attribute_two\r\n<strong>FROM<\/strong> name_of_the_table\r\n<strong>WHERE<\/strong> name_of_the_table.attibute <strong>IN<\/strong> (\r\n<strong>  SELECT<\/strong> name_of_other_table.attribute_three\r\n<strong>  FROM<\/strong> name_of_other_table\r\n);\r\n\r\n<strong>SELECT<\/strong> name_of_the_table.attribute_two\r\n<strong>FROM<\/strong> name_of_the_table\r\n<strong>WHERE<\/strong> name_of_the_table.attibute <strong>=<\/strong> (\r\n<strong>  SELECT<\/strong> name_of_other_table.attribute_three\r\n<strong>  FROM<\/strong> name_of_other_table <strong>LIMIT<\/strong> 1\r\n);<\/pre>\n<p>TRIGGERS<\/p>\n<pre><strong>DELIMITER $$<\/strong>\r\n\r\n<strong>CREATE TRIGGER<\/strong> trigger_name <strong>BEFORE INSERT<\/strong>\r\n<strong>ON<\/strong> name_of_the_table\r\n<strong>FOR EACH ROW BEGIN<\/strong>\r\n<strong>IF<\/strong> NEW.attribute_two = 1 <strong>THEN<\/strong>\r\n<strong>  INSERT INTO<\/strong> name_of_the_table <strong>VALUES<\/strong>('info 1');\r\n<strong>ELSEIF<\/strong> NEW.attribute = 2 <strong>THEN<\/strong>\r\n<strong>  INSERT INTO<\/strong> name_of_the_table <strong>VALUES<\/strong>('info 2');\r\n<strong>ELSE<\/strong>\r\n<strong>  INSERT INTO<\/strong> name_of_the_table <strong>VALUES<\/strong>('info 3');\r\n<strong>END IF;<\/strong>\r\n<strong>END$$<\/strong>\r\n\r\n<strong>DELIMITER ;<\/strong><\/pre>\n<p>Instead, BEFORE INSERT, try also AFTER, UPDATE, DELETE&#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>VARIABLE TYPES INT Whole numbers, no decimals. DECIMAL(M,N) M is the total number of numbers [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-851","post","type-post","status-publish","format-standard","hentry","category-web"],"_links":{"self":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/851","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=851"}],"version-history":[{"count":18,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/851\/revisions"}],"predecessor-version":[{"id":1603,"href":"https:\/\/dft.wiki\/index.php?rest_route=\/wp\/v2\/posts\/851\/revisions\/1603"}],"wp:attachment":[{"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dft.wiki\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}