{"id":949,"date":"2023-08-31T14:20:37","date_gmt":"2023-08-31T06:20:37","guid":{"rendered":"https:\/\/five.co\/?p=949"},"modified":"2024-08-09T13:43:40","modified_gmt":"2024-08-09T05:43:40","slug":"crud-operations-in-sql","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/crud-operations-in-sql\/","title":{"rendered":"CRUD Operations in SQL"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\" id=\"performing-crud-operations-in-sql-create-read-update-and-delete\">Performing CRUD Operations in SQL: Create, Read, Update, and Delete<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>CRUD<\/strong> is a commonly used acronym in software and application development. The acronym describes the four basic operations that can be performed on a database: <strong>C<\/strong>reate, <strong>R<\/strong>ead, <strong>U<\/strong>pdate, and <strong>D<\/strong>elete. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">As developers or database administrators, we are often tasked with creating user roles that carry different CRUD permissions. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img width=\"1024\" height=\"384\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-1024x384.png\" alt=\"CRUD operations in SQL: Create, Read, Update, Delete\" class=\"wp-image-1628\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-1024x384.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-300x113.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-768x288.png 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-1536x576.png 1536w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002.png 1600w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<div style=\"height:18px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Let&#8217;s explore how to perform CRUD operations in MySQL. <\/strong>We will first speak about how to perform CRUD operations on the <em>table <\/em>level, before also looking at operations on the <em>database <\/em>level. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For the purposes of this blog post, we are assuming that you are using MySQL Workbench as your MySQL GUI. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re not using MySQL Workbench, don&#8217;t worry: the same principles apply to other database administration tools.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"download-five-to-learn-how-to-implement-a-many-to-many-relationship\">Implement CRUD Operations in a Web Application<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Follow our FREE code-along tutorial and learn how to implement CRUD operations by building a web application on a MySQL database.<\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build a CRUD App on a SQL Database<\/strong><br \/><span style=\"font-size: 14pt;\">Get Started with Five's Online SQL CRUD App Builder For Free<\/span><br \/><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Started<\/strong><\/button><br \/><\/a><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-f0l57ld wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-f0l57ld \"><div class=\"eb-toc-container eb-toc-f0l57ld  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Performing CRUD Operations in SQL: Create, Read, Update, and Delete&quot;,&quot;text&quot;:&quot;Performing CRUD Operations in SQL: Create, Read, Update, and Delete&quot;,&quot;link&quot;:&quot;performing-crud-operations-in-sql-create-read-update-and-delete&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Implement CRUD Operations in a Web Application&quot;,&quot;text&quot;:&quot;Implement CRUD Operations in a Web Application&quot;,&quot;link&quot;:&quot;implement-crud-operations-in-a-web-application&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;What Are CRUD Operations in SQL?&quot;,&quot;text&quot;:&quot;What Are CRUD Operations in SQL?&quot;,&quot;link&quot;:&quot;what-are-crud-operations-in-sql&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Permissions in a CRUD App&quot;,&quot;text&quot;:&quot;Permissions in a CRUD App&quot;,&quot;link&quot;:&quot;permissions-in-a-crud-app&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;How to Perform CRUD Operations in MySQL Workbench&quot;,&quot;text&quot;:&quot;How to Perform CRUD Operations in MySQL Workbench&quot;,&quot;link&quot;:&quot;how-to-perform-crud-operations-in-mysql-workbench&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;1. Creating Roles&quot;,&quot;text&quot;:&quot;1. Creating Roles&quot;,&quot;link&quot;:&quot;1-creating-roles&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;2. Assigning CRUD Permissions to Roles and Database Tables&quot;,&quot;text&quot;:&quot;2. Assigning CRUD Permissions to Roles and Database Tables&quot;,&quot;link&quot;:&quot;2-assigning-crud-permissions-to-roles-and-database-tables&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;3. CRUD Operations for Databases and Tables&quot;,&quot;text&quot;:&quot;3. CRUD Operations for Databases and Tables&quot;,&quot;link&quot;:&quot;3-crud-operations-for-databases-and-tables&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion&quot;,&quot;text&quot;:&quot;Conclusion&quot;,&quot;link&quot;:&quot;conclusion&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Performing CRUD Operations in SQL: Create, Read, Update, and Delete&quot;,&quot;value&quot;:&quot;performing-crud-operations-in-sql-create-read-update-and-delete&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Implement CRUD Operations in a Web Application&quot;,&quot;value&quot;:&quot;implement-crud-operations-in-a-web-application&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;What Are CRUD Operations in SQL?&quot;,&quot;value&quot;:&quot;what-are-crud-operations-in-sql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Permissions in a CRUD App&quot;,&quot;value&quot;:&quot;permissions-in-a-crud-app&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;How to Perform CRUD Operations in MySQL Workbench&quot;,&quot;value&quot;:&quot;how-to-perform-crud-operations-in-mysql-workbench&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;1. Creating Roles&quot;,&quot;value&quot;:&quot;1-creating-roles&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;2. Assigning CRUD Permissions to Roles and Database Tables&quot;,&quot;value&quot;:&quot;2-assigning-crud-permissions-to-roles-and-database-tables&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;3. CRUD Operations for Databases and Tables&quot;,&quot;value&quot;:&quot;3-crud-operations-for-databases-and-tables&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Conclusion&quot;,&quot;value&quot;:&quot;conclusion&quot;,&quot;isDelete&quot;:true}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#what-are-crud-operations-in-sql\">What Are CRUD Operations in SQL?<\/a><ul class=\"eb-toc__list\"><li><a href=\"#permissions-in-a-crud-app\">Permissions in a CRUD App<\/a><li><a href=\"#how-to-perform-crud-operations-in-mysql-workbench\">How to Perform CRUD Operations in MySQL Workbench<\/a><ul class=\"eb-toc__list\"><li><a href=\"#1-creating-roles\">1. Creating Roles<\/a><li><a href=\"#2-assigning-crud-permissions-to-roles-and-database-tables\">2. Assigning CRUD Permissions to Roles and Database Tables<\/a><li><a href=\"#3-crud-operations-for-databases-and-tables\">3. CRUD Operations for Databases and Tables<\/a><\/li><\/ul><\/li><\/ul><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"what-are-crud-operations-in-sql\">What Are CRUD Operations in SQL?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">CRUD refers to Create, Read, Update, and Delete. Assigning and managing CRUD permissions is a very common task in programming. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">CRUD describes the four basic operations that a user can perform in a database. Users can<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><em>Create<\/em> or <em>insert <\/em>new records into a table,<\/li>\n\n\n\n<li><em>Read<\/em> or <em>select<\/em> existing records from a table,<\/li>\n\n\n\n<li><em>Update<\/em> or <em>modify <\/em>existing records stored inside a table, or<\/li>\n\n\n\n<li><em>Delete<\/em> or <em>remove <\/em>existing records from a table.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"permissions-in-a-crud-app\">Permissions in a CRUD App<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">CRUD permissions refer to the permissions granted to anyone having access to a database to perform all or part of the four basic operations on data: Create, Read, Update, and Delete. By assigning CRUD permissions, developers control and govern access to data stored in a database. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In SQL, CRUD permissions are often managed by granting or revoking permissions to specific users or roles. These permissions are typically set at the database level or on individual tables within the database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">It&#8217;s essential to carefully manage CRUD permissions in a web app to protect data from unauthorized access, modification, or deletion.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">By granting the appropriate permissions to trusted users or roles and denying them to others, application developers ensure that only authorized users can interact with the data in the database. This approach helps to maintain data integrity, security, and privacy.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"how-to-perform-crud-operations-in-my-sql-workbench\">How to Perform CRUD Operations in MySQL Workbench<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"1-creating-roles\">1. Creating Roles<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Before we assign permissions or manage access, we must first define Roles. Roles are a collection of privileges. They save you from the tedious work of assigning CRUD permissions to individual users. Instead, individual users are assigned to Roles, and by default, they will be given the CRUD permissions that are associated with the role.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Typical roles include, for example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>database administrators with full CRUD permissions,<\/li>\n\n\n\n<li>data analysts with partial access, typically <code>READ<\/code> only, or<\/li>\n\n\n\n<li>database developers with CREATE or ALTER access.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">To create Roles, use this command:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE ROLE 'database_administrator', 'data_analyst', 'database_developer';\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #E5C07B\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E5C07B\">ROLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;database_administrator&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;data_analyst&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;database_developer&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In case you would like to delete a role instead, use <code>DROP ROLE<\/code>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"2-assigning-crud-permissions-to-roles-and-database-tables\">2. Assigning CRUD Permissions to Roles and Database Tables<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">In MySQL Workbench, CRUD operations can be performed on tables, or sets of tables. To manage CRUD permissions, MySQL offers granular access control features through its standard syntax. We&#8217;ve already looked at <code>CREATE ROLE<\/code>. So now, let&#8217;s move on and grant our roles permissions by using <code>GRANT<\/code>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To assign CRUD permissions for tables, and to the records contained inside of them, developers can use the <code>GRANT<\/code> statement.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>GRANT<\/code> statement allows administrators to grant specific privileges to users or to roles on specific database objects. Permissions can be assigned in point-and-click by using Workbench&#8217;s Role Editor.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img width=\"760\" height=\"410\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/image-32.png\" alt=\"To perform CRUD operations in MySQL, we must first define roles and grant them privileges.\" class=\"wp-image-1607\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/image-32.png 760w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/image-32-300x162.png 300w\" sizes=\"(max-width: 760px) 100vw, 760px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To assign CRUD permissions in MySQL Workbench, follow these steps:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Connect to your MySQL database using MySQL Workbench.<\/li>\n\n\n\n<li>Navigate to the &#8220;<strong>Server Administration<\/strong>&#8221; tab and select the &#8220;<strong>Users and Privileges<\/strong>&#8221; option.<\/li>\n\n\n\n<li>Select the user or role to whom you want to assign CRUD permissions.<\/li>\n\n\n\n<li>Click on the &#8220;<strong>Schema Privileges<\/strong>&#8221; tab and select the database schema where the table resides.<\/li>\n\n\n\n<li>Find the table for which you want to assign CRUD permissions, and select the checkboxes for the appropriate privileges: <code>INSERT<\/code>, <code>SELECT<\/code>, <code>UPDATE<\/code>, and <code>DELETE<\/code>. Note that <\/li>\n\n\n\n<li>Click &#8220;<strong>Apply<\/strong>&#8221; to save the changes and assign the CRUD permissions to the user or role.<\/li>\n\n\n\n<li>Once equipped with the right set of permissions, different roles can perform CRUD operations in SQL. <\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Alternatively, you can also assign CRUD permissions using SQL commands. For example, to grant a user permission to perform all CRUD operations on a specific table in a MySQL database, you can use the following command:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"GRANT SELECT, INSERT, UPDATE, DELETE ON database.table TO 'database_administrator'@'localhost';\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #E5C07B\">GRANT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E5C07B\">SELECT<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E5C07B\">INSERT<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E5C07B\">UPDATE<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E5C07B\">DELETE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E5C07B\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E5C07B\">database<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #E06C75\">table<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E5C07B\">TO<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;database_administrator&#39;<\/span><span style=\"color: #ABB2BF\">@<\/span><span style=\"color: #98C379\">&#39;localhost&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This command grants the user &#8220;database_administrator&#8221; permission to perform all four CRUD operations on the table named &#8216;table&#8217; in the database named &#8216;database&#8217;. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The &#8216;@&#8217; symbol followed by &#8216;localhost&#8217; indicates that the permission applies only to connections made from the same computer as the MySQL server. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Note how we have used the <code>SELECT, INSERT, UPDATE, <\/code>and<code> DELETE<\/code> operations in SQL. Why doesn&#8217;t MySQL use CREATE, READ, UPDATE, and DELETE to describe these operations? <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">That&#8217;s because CREATE is already reserved for another purpose. Let&#8217;s look at this next.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"3-crud-operations-for-databases-and-tables\">3. CRUD Operations for Databases and Tables<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Why does the <code>GRANT<\/code> statement not include <code>CREATE<\/code> and <code>READ<\/code>? Well, this is a bit of a trick question, because there is no <code>READ<\/code> operation in MySQL. There is, however, a <code>CREATE<\/code> statement. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The correct syntax of the <code>CREATE <\/code>statement is this: <code>CREATE DATABASE<\/code>. It is used to create a new SQL database, and <em>not <\/em>to create database records.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">So, on a database level, the syntax is as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>CREATE: used to create a database like so: <code>CREATE DATABASE<\/code><\/li>\n\n\n\n<li>READ: This only applies to the table level.<\/li>\n\n\n\n<li>UPDATE: <code>ALTER TABLE<\/code>. Altering a table could, for example, mean adding or deleting a field inside a table. This is different from the <code>UPDATE <\/code>statement above, which only updates an existing record inside a table.<\/li>\n\n\n\n<li>DELETE: <code>DROP DATABASE<\/code> or <code>DROP TABLE<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"conclusion\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s say you are a restaurant owner and you&#8217;d like to make sure your staff can enter the kitchen, but your guests can only stay in the dining room. How would you solve this problem? Easy! You have two roles: staff and guest. Each role has its own permission to enter different parts of the restaurant.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is what CRUD permissions do for databases: they are used to govern access. By using CRUD operations in SQL, developers can make sure that data stays safe and is only accessed as intended. CRUD permissions are a useful tool in a developer&#8217;s access control toolbox!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To build CRUD web applications on a MySQL database, check out Five. Five is an easy way to manage a MySQL database, as well as its front end all from one platform. To sign up for a free trial, <a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\" target=\"_blank\" data-type=\"URL\" data-id=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" rel=\"noreferrer noopener\">click here<\/a>!        <\/p>\n","protected":false},"excerpt":{"rendered":"<p>Performing CRUD Operations in SQL: Create, Read, Update, and Delete CRUD is a commonly used acronym in software and application development. The acronym describes the four basic operations that can be performed on a database: Create, Read, Update, and Delete. As developers or database administrators, we are often tasked with creating user roles that carry [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":1628,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-949","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002.png",1600,600,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-150x150.png",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-300x113.png",300,113,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-768x288.png",768,288,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-1024x384.png",1024,384,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002-1536x576.png",1536,576,true],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/08\/MySQL-CRUD-002.png",1600,600,false]},"uagb_author_info":{"display_name":"Dominik Keller","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed3_my\/"},"uagb_comment_info":0,"uagb_excerpt":"Performing CRUD Operations in SQL: Create, Read, Update, and Delete CRUD is a commonly used acronym in software and application development. The acronym describes the four basic operations that can be performed on a database: Create, Read, Update, and Delete. As developers or database administrators, we are often tasked with creating user roles that carry&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/949","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=949"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/949\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/1628"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=949"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=949"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=949"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}