{"id":310,"date":"2015-12-26T22:52:37","date_gmt":"2015-12-26T22:52:37","guid":{"rendered":"http:\/\/wiki.thomasandsofia.com\/?p=310"},"modified":"2022-10-24T18:14:48","modified_gmt":"2022-10-24T18:14:48","slug":"mysql-commands","status":"publish","type":"post","link":"https:\/\/wiki.thomasandsofia.com\/?p=310","title":{"rendered":"MySQL Commands"},"content":{"rendered":"<h2>Databases<\/h2>\n<h3>Create New Database<\/h3>\n<pre>CREATE DATABASE <span class=\"variable\">database<\/span>;<\/pre>\n<h3>Set default database<\/h3>\n<pre>USE DATABASE database;<\/pre>\n<h3>Show Databases<\/h3>\n<pre>SHOW DATABASES;<\/pre>\n<h2>Tables<\/h2>\n<h3>Create<\/h3>\n<pre>CREATE TABLE [IF NOT EXISTS] tablename(\r\n  col1 AS STRING,\r\n  col2 AS INT,\r\n  col3 AS VARCHAR(255)\r\n)<\/pre>\n<p><strong>Example:<\/strong><\/p>\n<pre>CREATE TABLE [IF NOT EXISTS] users(\r\n  id INT NOT NULL AUTO_INCREMENT,\r\n  name STRING,\r\n  age INT,\r\n  color VARCHAR(25),\r\n  PRIMARY KEY(id)\r\n)<\/pre>\n<p>&nbsp;<\/p>\n<h2>INSERT<\/h2>\n<p><strong>Example<\/strong><\/p>\n<pre>INSERT INTO users (name, age, color) VALUES \r\n  ('Mike', 25, 'green'),\r\n  ('Nancy', 43, 'pink'),\r\n  ('Jacob', 33, 'red'),\r\n  ('Larry', 54, 'purple')\r\n;<\/pre>\n<h2><\/h2>\n<h2>Queries<\/h2>\n<p>Standard Query<\/p>\n<p>SELECT col1, col2 FROM tablename WHERE col1 = &#8216;some string&#8217; and col2 &lt;= n<\/p>\n<h2>Users<\/h2>\n<h3>Creating Users and Adding Permissions<\/h3>\n<h4>Create Local User<\/h4>\n<pre>GRANT ALL PRIVILEGES ON <span class=\"variable\">database<\/span>.* TO '<span class=\"variable\">username<\/span>'@'localhost' IDENTIFIED BY '<span class=\"variable\">password<\/span>' WITH GRANT OPTION;<\/pre>\n<h4>Create a remote user &#8211; Unknown source IP:<\/h4>\n<pre>GRANT ALL PRIVILEGES ON <span class=\"variable\">database<\/span>.* TO '<span class=\"variable\">username<\/span>'@'%' IDENTIFIED BY '<span class=\"variable\">password<\/span>' WITH GRANT OPTION;<\/pre>\n<h4>Create a remote user &#8211; Known source IP:<\/h4>\n<pre>GRANT ALL PRIVILEGES ON <span class=\"variable\">database<\/span>.* TO '<span class=\"variable\">username<\/span>'@'IP.ADD.RE.SS' IDENTIFIED BY '<span class=\"variable\">password<\/span>' WITH GRANT OPTION;<\/pre>\n<p>&nbsp;<\/p>\n<h3>Plesk MySQL Login<\/h3>\n<pre>mysql -uadmin -p`cat \/etc\/psa\/.psa.shadow`<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Databases Create New Database CREATE DATABASE database; Set default database USE DATABASE database; Show Databases SHOW DATABASES; Tables Create CREATE TABLE [IF NOT EXISTS] tablename( col1 AS STRING, col2 AS INT, col3 AS VARCHAR(255) ) Example: CREATE TABLE [IF NOT EXISTS] users( id INT NOT NULL AUTO_INCREMENT, name STRING, age INT, color VARCHAR(25), PRIMARY KEY(id) ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/wiki.thomasandsofia.com\/?p=310\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,17],"tags":[],"class_list":["post-310","post","type-post","status-publish","format-standard","hentry","category-databases","category-mysql"],"_links":{"self":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts\/310","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=310"}],"version-history":[{"count":5,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts\/310\/revisions"}],"predecessor-version":[{"id":3822,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=\/wp\/v2\/posts\/310\/revisions\/3822"}],"wp:attachment":[{"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=310"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=310"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wiki.thomasandsofia.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=310"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}