{"id":18461,"date":"2024-07-17T23:01:01","date_gmt":"2024-07-18T06:01:01","guid":{"rendered":"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/"},"modified":"2024-12-12T01:35:25","modified_gmt":"2024-12-12T09:35:25","slug":"essential-sql-commands-for-every-developer","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/essential-sql-commands-for-every-developer\/","title":{"rendered":"Essential SQL Commands for Every Developer"},"content":{"rendered":"\n

In today’s data-driven world, SQL stands as a cornerstone for modern development. With over 7 million developers<\/em> worldwide leveraging SQL, its importance cannot be overstated. Whether you’re building applications or analyzing data, SQL provides the essential tools to manage and manipulate databases efficiently. In fact, 89% of developers<\/em> use SQL at work, underscoring its ubiquity and necessity. This guide emphasizes practical application and best practices, ensuring you can harness the full power of SQL in your projects. For those seeking a quick reference, our SQL cheat sheet will be invaluable.<\/p>\n\n\n\n

<\/span>Basic SQL Commands<\/span><\/h2>\n\n\n\n

SELECT Statement<\/h3>\n\n\n\n

The SELECT<\/code> statement is the cornerstone of SQL, enabling you to retrieve data from your database. Mastering this command is essential for any developer working with SQL.<\/p>\n\n\n\n

Basic Syntax<\/h4>\n\n\n\n

The basic syntax of a SELECT<\/code> statement is straightforward:<\/p>\n\n\n\n

\nSELECT column1, column2, ...\nFROM table_name;\n<\/code>\n<\/pre>\n\n\n\n
For instance, to fetch all columns from a table named employees<\/code>, you would use:<\/p>\n\n\n\n
\nSELECT * FROM employees;\n<\/code>\n<\/pre>\n\n\n\n
This command retrieves every row and column from the employees<\/code> table, making it a powerful tool for data exploration.<\/p>\n\n\n\n
Filtering Results with WHERE<\/h4>\n\n\n\n
To narrow down your results, the WHERE<\/code> clause is indispensable. It allows you to specify conditions that the data must meet to be included in the result set.<\/p>\n\n\n\n
\nSELECT column1, column2, ...\nFROM table_name\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
For example, if you want to find employees who work in the ‘Sales’ department, you would write:<\/p>\n\n\n\n
\nSELECT * FROM employees\nWHERE department = 'Sales';\n<\/code>\n<\/pre>\n\n\n\n
This command filters the rows to include only those where the department is ‘Sales’.<\/p>\n\n\n\n
Sorting Results with ORDER BY<\/h4>\n\n\n\n
The ORDER BY<\/code> clause lets you sort the result set by one or more columns, either in ascending (ASC<\/code>) or descending (DESC<\/code>) order.<\/p>\n\n\n\n
\nSELECT column1, column2, ...\nFROM table_name\nORDER BY column1 [ASC|DESC], column2 [ASC|DESC];\n<\/code>\n<\/pre>\n\n\n\n
To sort employees by their hire date in descending order, you would use:<\/p>\n\n\n\n
\nSELECT * FROM employees\nORDER BY hire_date DESC;\n<\/code>\n<\/pre>\n\n\n\n
This ensures that the most recently hired employees appear first in the result set.<\/p>\n\n\n\n
INSERT Statement<\/h3>\n\n\n\n
The INSERT<\/code> statement is used to add new rows to a table. This command is fundamental for populating your database with data.<\/p>\n\n\n\n
Basic Syntax<\/h4>\n\n\n\n
The basic syntax for inserting a single row is:<\/p>\n\n\n\n
\nINSERT INTO table_name (column1, column2, ...)\nVALUES (value1, value2, ...);\n<\/code>\n<\/pre>\n\n\n\n
For example, to add a new employee to the employees<\/code> table, you might write:<\/p>\n\n\n\n
\nINSERT INTO employees (name, department, hire_date)\nVALUES ('John Doe', 'Engineering', '2023-01-15');\n<\/code>\n<\/pre>\n\n\n\n
This command inserts a new row with the specified values into the employees<\/code> table.<\/p>\n\n\n\n
Inserting Multiple Rows<\/h4>\n\n\n\n
You can also insert multiple rows in a single INSERT<\/code> statement, which can be more efficient than inserting each row individually.<\/p>\n\n\n\n
\nINSERT INTO table_name (column1, column2, ...)\nVALUES (value1, value2, ...), (value3, value4, ...), ...;\n<\/code>\n<\/pre>\n\n\n\n
For instance, to add multiple employees at once:<\/p>\n\n\n\n
\nINSERT INTO employees (name, department, hire_date)\nVALUES \n  ('Alice Smith', 'Marketing', '2023-02-01'),\n  ('Bob Johnson', 'Sales', '2023-02-05');\n<\/code>\n<\/pre>\n\n\n\n
This command adds two new rows to the employees<\/code> table in a single operation.<\/p>\n\n\n\n
UPDATE Statement<\/h3>\n\n\n\n
The UPDATE<\/code> statement is used to modify existing records in a table. This command is crucial for keeping your data up-to-date.<\/p>\n\n\n\n
Basic Syntax<\/h4>\n\n\n\n
The basic syntax for updating a single row is:<\/p>\n\n\n\n
\nUPDATE table_name\nSET column1 = value1, column2 = value2, ...\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
For example, to update the department of an employee with ID 1, you would write:<\/p>\n\n\n\n
\nUPDATE employees\nSET department = 'HR'\nWHERE id = 1;\n<\/code>\n<\/pre>\n\n\n\n
This command changes the department of the employee with ID 1 to ‘HR’.<\/p>\n\n\n\n
Updating Multiple Rows<\/h4>\n\n\n\n
You can also update multiple rows that meet a specific condition. This is useful for making bulk changes to your data.<\/p>\n\n\n\n
\nUPDATE table_name\nSET column1 = value1, column2 = value2, ...\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
For instance, to give all employees in the ‘Sales’ department a new title, you might write:<\/p>\n\n\n\n
\nUPDATE employees\nSET title = 'Senior Sales Associate'\nWHERE department = 'Sales';\n<\/code>\n<\/pre>\n\n\n\n
This command updates the title for all employees in the ‘Sales’ department.<\/p>\n\n\n\n
By mastering these basic SQL commands, you’ll be well-equipped to handle a wide range of data manipulation tasks in your TiDB database. Whether you’re retrieving, inserting, or updating data, these commands form the foundation of your SQL toolkit.<\/p>\n\n\n\n
DELETE Statement<\/h3>\n\n\n\n
The DELETE<\/code> statement is a powerful command used to remove rows from a table. This operation is essential for maintaining the accuracy and relevance of your data by eliminating obsolete or incorrect records.<\/p>\n\n\n\n
Basic Syntax<\/h4>\n\n\n\n
The basic syntax for a DELETE<\/code> statement is straightforward:<\/p>\n\n\n\n
\nDELETE FROM table_name\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
For example, to delete an employee record with a specific ID from the employees<\/code> table, you would use:<\/p>\n\n\n\n
\nDELETE FROM employees\nWHERE id = 10;\n<\/code>\n<\/pre>\n\n\n\n
This command removes the row where the id<\/code> is 10 from the employees<\/code> table. It’s crucial to include a WHERE<\/code> clause to specify which rows should be deleted; otherwise, all rows in the table will be removed.<\/p>\n\n\n\n
Conditional Deletion with WHERE<\/h4>\n\n\n\n
Using the WHERE<\/code> clause, you can perform conditional deletions, targeting only the rows that meet specific criteria. This is particularly useful for cleaning up data without affecting the entire table.<\/p>\n\n\n\n
\nDELETE FROM table_name\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
For instance, if you want to delete all employees who were hired before a certain date, you might write:<\/p>\n\n\n\n
\nDELETE FROM employees\nWHERE hire_date < '2020-01-01';\n<\/code>\n<\/pre>\n\n\n\n
This command deletes all rows where the hire_date<\/code> is earlier than January 1, 2020, ensuring that only outdated records are removed.<\/p>\n\n\n\n
By mastering the DELETE<\/code> statement, you can efficiently manage and maintain the integrity of your data within the TiDB database<\/strong>. Proper use of this command helps ensure that your database remains clean, accurate, and up-to-date, which is vital for any data-driven application.<\/p>\n\n\n\n
<\/span>Intermediate SQL Commands<\/span><\/h2>\n\n\n\n
As you advance in your SQL journey, mastering intermediate commands will significantly enhance your ability to manipulate and analyze data. These commands build on the basics and introduce more complex operations that are essential for robust database management.<\/p>\n\n\n\n
JOIN Operations<\/h3>\n\n\n\n
JOIN<\/strong> operations are fundamental when working with relational databases, enabling you to combine rows from two or more tables based on a related column between them. Understanding different types of joins is crucial for effective data retrieval and manipulation.<\/p>\n\n\n\n
INNER JOIN<\/h4>\n\n\n\n
The INNER JOIN<\/code> keyword selects records that have matching values in both tables. This is the most common type of join.<\/p>\n\n\n\n
\nSELECT a.column1, b.column2\nFROM table1 a\nINNER JOIN table2 b ON a.common_field = b.common_field;\n<\/code>\n<\/pre>\n\n\n\n
For example, to retrieve employees and their corresponding department names:<\/p>\n\n\n\n
\nSELECT employees.name, departments.department_name\nFROM employees\nINNER JOIN departments ON employees.department_id = departments.id;\n<\/code>\n<\/pre>\n\n\n\n
This query returns only the employees who have a matching department.<\/p>\n\n\n\n
LEFT JOIN<\/h4>\n\n\n\n
The LEFT JOIN<\/code> keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL<\/code> from the right side if there is no match.<\/p>\n\n\n\n
\nSELECT a.column1, b.column2\nFROM table1 a\nLEFT JOIN table2 b ON a.common_field = b.common_field;\n<\/code>\n<\/pre>\n\n\n\n
To get all employees and their departments, even if some employees are not assigned to any department:<\/p>\n\n\n\n
\nSELECT employees.name, departments.department_name\nFROM employees\nLEFT JOIN departments ON employees.department_id = departments.id;\n<\/code>\n<\/pre>\n\n\n\n
This ensures that employees without a department are still included in the results.<\/p>\n\n\n\n
RIGHT JOIN<\/h4>\n\n\n\n
The RIGHT JOIN<\/code> keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL<\/code> from the left side if there is no match.<\/p>\n\n\n\n
\nSELECT a.column1, b.column2\nFROM table1 a\nRIGHT JOIN table2 b ON a.common_field = b.common_field;\n<\/code>\n<\/pre>\n\n\n\n
For instance, to list all departments and their employees, including departments without employees:<\/p>\n\n\n\n
\nSELECT employees.name, departments.department_name\nFROM employees\nRIGHT JOIN departments ON employees.department_id = departments.id;\n<\/code>\n<\/pre>\n\n\n\n
This query ensures that all departments are listed, even those without employees.<\/p>\n\n\n\n
FULL OUTER JOIN<\/h4>\n\n\n\n
The FULL OUTER JOIN<\/code> keyword returns all records when there is a match in either left (table1) or right (table2) table records. It combines the results of both LEFT JOIN<\/code> and RIGHT JOIN<\/code>.<\/p>\n\n\n\n
\nSELECT a.column1, b.column2\nFROM table1 a\nFULL OUTER JOIN table2 b ON a.common_field = b.common_field;\n<\/code>\n<\/pre>\n\n\n\n
To get a complete list of employees and departments, including those without matches in either table:<\/p>\n\n\n\n
\nSELECT employees.name, departments.department_name\nFROM employees\nFULL OUTER JOIN departments ON employees.department_id = departments.id;\n<\/code>\n<\/pre>\n\n\n\n
This query includes all employees and all departments, regardless of whether they have matches in the other table.<\/p>\n\n\n\n
Aggregate Functions<\/h3>\n\n\n\n
Aggregate functions perform calculations on a set of values and return a single value. They are essential for summarizing data.<\/p>\n\n\n\n
COUNT<\/h4>\n\n\n\n
The COUNT<\/code> function returns the number of rows that match a specified condition.<\/p>\n\n\n\n
\nSELECT COUNT(*)\nFROM table_name\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
To count the number of employees in the ‘Sales’ department:<\/p>\n\n\n\n
\nSELECT COUNT(*)\nFROM employees\nWHERE department = 'Sales';\n<\/code>\n<\/pre>\n\n\n\n
SUM<\/h4>\n\n\n\n
The SUM<\/code> function returns the total sum of a numeric column.<\/p>\n\n\n\n
\nSELECT SUM(column_name)\nFROM table_name\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
To calculate the total salaries of all employees:<\/p>\n\n\n\n
\nSELECT SUM(salary)\nFROM employees;\n<\/code>\n<\/pre>\n\n\n\n
AVG<\/h4>\n\n\n\n
The AVG<\/code> function returns the average value of a numeric column.<\/p>\n\n\n\n
\nSELECT AVG(column_name)\nFROM table_name\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
To find the average salary of employees in the ‘Engineering’ department:<\/p>\n\n\n\n
\nSELECT AVG(salary)\nFROM employees\nWHERE department = 'Engineering';\n<\/code>\n<\/pre>\n\n\n\n
MIN and MAX<\/h4>\n\n\n\n
The MIN<\/code> and MAX<\/code> functions return the smallest and largest values in a column, respectively.<\/p>\n\n\n\n
\nSELECT MIN(column_name), MAX(column_name)\nFROM table_name\nWHERE condition;\n<\/code>\n<\/pre>\n\n\n\n
To find the minimum and maximum hire dates of employees:<\/p>\n\n\n\n
\nSELECT MIN(hire_date), MAX(hire_date)\nFROM employees;\n<\/code>\n<\/pre>\n\n\n\n
GROUP BY and HAVING Clauses<\/h3>\n\n\n\n
The GROUP BY<\/code> clause groups rows that have the same values into summary rows, like “find the number of employees in each department.” The HAVING<\/code> clause was added to SQL because the WHERE<\/code> keyword could not be used with aggregate functions.<\/p>\n\n\n\n
Grouping Data<\/h4>\n\n\n\n
The GROUP BY<\/code> statement is used to arrange identical data into groups.<\/p>\n\n\n\n
\nSELECT column_name, aggregate_function(column_name)\nFROM table_name\nWHERE condition\nGROUP BY column_name;\n<\/code>\n<\/pre>\n\n\n\n
To group employees by department and count the number of employees in each:<\/p>\n\n\n\n
\nSELECT department, COUNT(*)\nFROM employees\nGROUP BY department;\n<\/code>\n<\/pre>\n\n\n\n
Filtering Groups with HAVING<\/h4>\n\n\n\n
The HAVING<\/code> clause allows you to filter groups based on a condition.<\/p>\n\n\n\n
\nSELECT column_name, aggregate_function(column_name)\nFROM table_name\nWHERE condition\nGROUP BY column_name\nHAVING aggregate_function(column_name) condition;\n<\/code>\n<\/pre>\n\n\n\n
To find departments with more than 10 employees:<\/p>\n\n\n\n
\nSELECT department, COUNT(*)\nFROM employees\nGROUP BY department\nHAVING COUNT(*) > 10;\n<\/code>\n<\/pre>\n\n\n\n
By mastering these intermediate SQL commands, you can perform more sophisticated data analysis and manipulation within the TiDB database<\/strong>. These skills are essential for any developer looking to leverage the full power of SQL in their projects.<\/p>\n\n\n\n
<\/span>Advanced SQL Commands<\/span><\/h2>\n\n\n\n
As you delve deeper into SQL, mastering advanced commands will significantly enhance your ability to perform complex data manipulations and analyses. These commands build on the basics and introduce more sophisticated operations essential for robust database management.<\/p>\n\n\n\n
Subqueries<\/h3>\n\n\n\n
Subqueries, also known as nested queries, are queries within another SQL query. They allow you to perform more complex operations by breaking them down into simpler, manageable parts.<\/p>\n\n\n\n
Inline Subqueries<\/h4>\n\n\n\n
Inline subqueries are used within the SELECT<\/code>, FROM<\/code>, or WHERE<\/code> clauses of an SQL statement. They are executed once for each row processed by the outer query.<\/p>\n\n\n\n
\nSELECT name, (SELECT department_name FROM departments WHERE departments.id = employees.department_id) AS department\nFROM employees;\n<\/code>\n<\/pre>\n\n\n\n
In this example, the inline subquery retrieves the department name for each employee, allowing you to display both the employee’s name and their department in the result set.<\/p>\n\n\n\n
Correlated Subqueries<\/h4>\n\n\n\n
Correlated subqueries reference columns from the outer query, making them dependent on the outer query for their values. They are executed once for each row considered by the outer query.<\/p>\n\n\n\n
\nSELECT name\nFROM employees e1\nWHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);\n<\/code>\n<\/pre>\n\n\n\n
This query finds employees whose salary is above the average salary of their respective departments. The subquery calculates the average salary for each department, correlated with the outer query’s current row.<\/p>\n\n\n\n
Window Functions<\/h3>\n\n\n\n
Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate functions, window functions do not collapse rows into a single result but instead provide a result for each row.<\/p>\n\n\n\n
ROW_NUMBER<\/h4>\n\n\n\n
The ROW_NUMBER<\/code> function assigns a unique sequential integer to rows within a partition of a result set.<\/p>\n\n\n\n
\nSELECT name, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS row_num\nFROM employees;\n<\/code>\n<\/pre>\n\n\n\n
This query assigns a unique row number to each employee within their department, ordered by their hire date.<\/p>\n\n\n\n
RANK and DENSE_RANK<\/h4>\n\n\n\n
The RANK<\/code> and DENSE_RANK<\/code> functions assign ranks to rows within a partition of a result set, with RANK<\/code> allowing gaps in ranking and DENSE_RANK<\/code> providing consecutive ranks.<\/p>\n\n\n\n
\nSELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank\nFROM employees;\n<\/code>\n<\/pre>\n\n\n\n
This query ranks employees within their departments based on their salaries, with the highest salary receiving the top rank.<\/p>\n\n\n\n
LEAD and LAG<\/h4>\n\n\n\n
The LEAD<\/code> and LAG<\/code> functions access data from subsequent or preceding rows in the result set without using a self-join.<\/p>\n\n\n\n
\nSELECT name, salary, LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary\nFROM employees;\n<\/code>\n<\/pre>\n\n\n\n
This query retrieves the salary of the next employee based on the hire date, allowing you to compare salaries between consecutive employees.<\/p>\n\n\n\n
Common Table Expressions (CTEs)<\/h3>\n\n\n\n
Common Table Expressions (CTEs) provide a temporary result set that can be referenced within a SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, or DELETE<\/code> statement. They improve readability and maintainability of complex queries.<\/p>\n\n\n\n
Basic Syntax<\/h4>\n\n\n\n
CTEs are defined using the WITH<\/code> clause, followed by the CTE name and the query that defines the CTE.<\/p>\n\n\n\n
\nWITH DepartmentCTE AS (\n    SELECT department_id, COUNT(*) AS employee_count\n    FROM employees\n    GROUP BY department_id\n)\nSELECT d.department_name, c.employee_count\nFROM departments d\nJOIN DepartmentCTE c ON d.id = c.department_id;\n<\/code>\n<\/pre>\n\n\n\n
This query creates a CTE to count the number of employees in each department and then joins it with the departments<\/code> table to display the department names along with their employee counts.<\/p>\n\n\n\n
Recursive CTEs<\/h4>\n\n\n\n
Recursive CTEs are used to perform hierarchical or recursive queries, such as traversing organizational charts or tree structures.<\/p>\n\n\n\n
\nWITH RECURSIVE EmployeeHierarchy AS (\n    SELECT id, name, manager_id\n    FROM employees\n    WHERE manager_id IS NULL\n    UNION ALL\n    SELECT e.id, e.name, e.manager_id\n    FROM employees e\n    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id\n)\nSELECT * FROM EmployeeHierarchy;\n<\/code>\n<\/pre>\n\n\n\n
This query creates a recursive CTE to build an employee hierarchy, starting from the top-level managers and recursively including their subordinates.<\/p>\n\n\n\n
By mastering these advanced SQL commands, you can perform intricate data manipulations and analyses within the TiDB database. These skills are indispensable for any developer aiming to leverage the full potential of SQL in their projects.<\/p>\n\n\n\n
<\/span>TiDB SQL Cheat Sheet<\/span><\/h2>\n\n\n\n
Navigating the intricacies of the TiDB database<\/strong> can be simplified with a handy SQL cheat sheet<\/strong>. This section provides essential commands and best practices for managing databases, tables, indexes, data manipulation, and user permissions in TiDB.<\/p>\n\n\n\n
Essential TiDB Commands<\/h3>\n\n\n\n
Creating and Managing Databases<\/h4>\n\n\n\n
Creating and managing databases in TiDB is straightforward and follows standard SQL syntax with some TiDB-specific enhancements.<\/p>\n\n\n\n
    \n
  • Show Databases:<\/strong>SHOW DATABASES;<\/code>Lists all databases in the TiDB instance.<\/p><\/li>\n\n\n\n
  • Create Database:<\/strong>CREATE DATABASE IF NOT EXISTS sample_db;<\/code>Creates a new database named sample_db<\/code>. The IF NOT EXISTS<\/code> clause prevents an error if the database already exists.<\/p><\/li>\n\n\n\n
  • Drop Database:<\/strong>DROP DATABASE sample_db;<\/code>Deletes the database named sample_db<\/code>.<\/p><\/li>\n<\/ul>\n\n\n\n
    Creating and Managing Tables<\/h4>\n\n\n\n
    Tables are the backbone of any database. Here’s how to create and manage them in TiDB:<\/p>\n\n\n\n
      \n
    • Create Table:<\/strong>CREATE TABLE employees (
        id INT PRIMARY KEY,
        name VARCHAR(255),
        department VARCHAR(255),
        hire_date DATE
      );<\/code>Creates a table named employees<\/code> with columns for ID, name, department, and hire date.<\/p><\/li>\n\n\n\n
    • Show Create Table:<\/strong>SHOW CREATE TABLE employees;<\/code>Displays the CREATE TABLE<\/code> statement used to create the employees<\/code> table.<\/p><\/li>\n\n\n\n
    • Drop Table:<\/strong>DROP TABLE employees;<\/code>Deletes the employees<\/code> table.<\/p><\/li>\n<\/ul>\n\n\n\n
      Creating and Managing Indexes<\/h4>\n\n\n\n
      Indexes enhance query performance by allowing faster data retrieval.<\/p>\n\n\n\n
        \n
      • Create Index:<\/strong>CREATE INDEX idx_department ON employees (department);<\/code>Creates an index on the department<\/code> column of the employees<\/code> table.<\/p><\/li>\n\n\n\n
      • Create Unique Index:<\/strong>CREATE UNIQUE INDEX idx_unique_id ON employees (id);<\/code>Creates a unique index on the id<\/code> column of the employees<\/code> table.<\/p><\/li>\n\n\n\n
      • Show Indexes:<\/strong>SHOW INDEX FROM employees;<\/code>Lists all indexes on the employees<\/code> table.<\/p><\/li>\n\n\n\n
      • Drop Index:<\/strong>DROP INDEX idx_department ON employees;<\/code>Deletes the index named idx_department<\/code> from the employees<\/code> table.<\/p><\/li>\n<\/ul>\n\n\n\n
        Data Manipulation in TiDB<\/h3>\n\n\n\n
        Data manipulation involves inserting, updating, and deleting data, as well as querying it efficiently.<\/p>\n\n\n\n
        Inserting, Updating, and Deleting Data<\/h4>\n\n\n\n
          \n
        • Insert Data:<\/strong>INSERT INTO employees (id, name, department, hire_date)
          VALUES (1, 'John Doe', 'Engineering', '2023-01-15');<\/code>Adds a new row to the employees<\/code> table.<\/p><\/li>\n\n\n\n
        • Update Data:<\/strong>UPDATE employees
          SET department = 'HR'
          WHERE id = 1;<\/code>Updates the department of the employee with ID 1 to ‘HR’.<\/p><\/li>\n\n\n\n
        • Delete Data:<\/strong>DELETE FROM employees
          WHERE id = 1;<\/code>Deletes the row where the id<\/code> is 1 from the employees<\/code> table.<\/p><\/li>\n<\/ul>\n\n\n\n
          Querying Data<\/h4>\n\n\n\n
          Efficient querying is crucial for data analysis and reporting.<\/p>\n\n\n\n
            \n
          • Select All Data:<\/strong>SELECT * FROM employees;<\/code>Retrieves all rows from the employees<\/code> table.<\/p><\/li>\n\n\n\n
          • Select Specific Columns:<\/strong>SELECT name, department FROM employees;<\/code>Retrieves the name<\/code> and department<\/code> columns from the employees<\/code> table.<\/p><\/li>\n\n\n\n
          • Conditional Query:<\/strong>SELECT * FROM employees
            WHERE department = 'Sales';<\/code>Retrieves rows where the department<\/code> is ‘Sales’.<\/p><\/li>\n<\/ul>\n\n\n\n
            User and Permission Management in TiDB<\/h3>\n\n\n\n
            Managing users and their permissions ensures secure and controlled access to the database.<\/p>\n\n\n\n
            Creating Users<\/h4>\n\n\n\n
              \n
            • Create User:<\/strong>
              CREATE USER 'tiuser'@'localhost' IDENTIFIED BY 'password123';<\/code>Creates a new user named tiuser<\/code> with the password password123<\/code>.<\/pre><\/li>\n<\/ul>\n\n\n\n
              Granting Privileges<\/h4>\n\n\n\n
                \n
              • Grant Privileges:<\/strong>
                GRANT SELECT, INSERT ON sample_db.* TO 'tiuser'@'localhost';<\/code>Grants SELECT<\/code> and INSERT<\/code> privileges on the sample_db<\/code> database to tiuser<\/code>.<\/pre><\/li>\n<\/ul>\n\n\n\n
                Dropping Users<\/h4>\n\n\n\n
                  \n
                • Drop User:<\/strong>
                  DROP USER 'tiuser'@'localhost';<\/code>Deletes the user named tiuser<\/code>.<\/pre><\/li>\n<\/ul>\n\n\n\n
                  This SQL cheat sheet<\/strong> for TiDB covers the fundamental commands necessary for efficient database management. By mastering these commands, you can ensure your TiDB environment is well-organized, secure, and optimized for performance.<\/p>\n\n\n\n
                  \n\n\n\n
                  Mastering SQL commands is crucial for any developer aiming to efficiently manage and manipulate data. By practicing and applying these commands in real-world scenarios, you can significantly enhance your database management skills. Remember, continuous learning is key. Explore additional resources, such as the TiDB Documentation, to further deepen your understanding and stay updated with the latest advancements in SQL and the TiDB database.<\/p>\n","protected":false},"excerpt":{"rendered":"
                  Master essential SQL commands like SELECT, INSERT, UPDATE, and DELETE. Dive into JOINs, aggregate functions, subqueries, and TiDB SQL cheat sheet for developers.<\/p>","protected":false},"author":8,"featured_media":0,"template":"","class_list":["post-18461","article","type-article","status-publish","hentry"],"acf":[],"yoast_head":"\nEssential SQL Commands for Every Developer<\/title>\n<meta name=\"description\" content=\"Master essential SQL commands like SELECT, INSERT, UPDATE, and DELETE. Dive into JOINs, aggregate functions, subqueries, and TiDB SQL cheat sheet for developers.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pingcap.com\/ko\/article\/essential-sql-commands-for-every-developer\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Essential SQL Commands for Every Developer\" \/>\n<meta property=\"og:description\" content=\"Master essential SQL commands like SELECT, INSERT, UPDATE, and DELETE. Dive into JOINs, aggregate functions, subqueries, and TiDB SQL cheat sheet for developers.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/article\/essential-sql-commands-for-every-developer\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:modified_time\" content=\"2024-12-12T09:35:25+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1440\" \/>\n\t<meta property=\"og:image:height\" content=\"714\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"\uc608\uc0c1 \ub418\ub294 \ud310\ub3c5 \uc2dc\uac04\" \/>\n\t<meta name=\"twitter:data1\" content=\"15\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/\",\"url\":\"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/\",\"name\":\"Essential SQL Commands for Every Developer\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"datePublished\":\"2024-07-18T06:01:01+00:00\",\"dateModified\":\"2024-12-12T09:35:25+00:00\",\"description\":\"Master essential SQL commands like SELECT, INSERT, UPDATE, and DELETE. Dive into JOINs, aggregate functions, subqueries, and TiDB SQL cheat sheet for developers.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Articles\",\"item\":\"https:\/\/www.pingcap.com\/article\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Essential SQL Commands for Every Developer\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Essential SQL Commands for Every Developer","description":"Master essential SQL commands like SELECT, INSERT, UPDATE, and DELETE. Dive into JOINs, aggregate functions, subqueries, and TiDB SQL cheat sheet for developers.","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:\/\/www.pingcap.com\/ko\/article\/essential-sql-commands-for-every-developer\/","og_locale":"ko_KR","og_type":"article","og_title":"Essential SQL Commands for Every Developer","og_description":"Master essential SQL commands like SELECT, INSERT, UPDATE, and DELETE. Dive into JOINs, aggregate functions, subqueries, and TiDB SQL cheat sheet for developers.","og_url":"https:\/\/www.pingcap.com\/ko\/article\/essential-sql-commands-for-every-developer\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_modified_time":"2024-12-12T09:35:25+00:00","og_image":[{"width":1440,"height":714,"url":"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_site":"@PingCAP","twitter_misc":{"\uc608\uc0c1 \ub418\ub294 \ud310\ub3c5 \uc2dc\uac04":"15\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/","url":"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/","name":"Essential SQL Commands for Every Developer","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"datePublished":"2024-07-18T06:01:01+00:00","dateModified":"2024-12-12T09:35:25+00:00","description":"Master essential SQL commands like SELECT, INSERT, UPDATE, and DELETE. Dive into JOINs, aggregate functions, subqueries, and TiDB SQL cheat sheet for developers.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/article\/essential-sql-commands-for-every-developer\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Articles","item":"https:\/\/www.pingcap.com\/article\/"},{"@type":"ListItem","position":3,"name":"Essential SQL Commands for Every Developer"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]}]}},"card_markup":"        <a class=\"card-article\" href=\"https:\/\/www.pingcap.com\/ko\/article\/essential-sql-commands-for-every-developer\/\">            <h3>Essential SQL Commands for Every Developer<\/h3>            <p>Master essential SQL commands like SELECT, INSERT, UPDATE, and DELETE. Dive into JOINs, aggregate functions, subqueries, and TiDB SQL cheat sheet for developers.<\/p>        <\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article\/18461","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/article"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/8"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=18461"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}