31 Aug 2022 Show
Beginner 4.8K Views SQL commands are a set of instructions that are used to interact with the database like Sql Server, MySql, Oracle etc. SQL commands are responsible to create and to do all the manipulation on the database. These are also responsible to give/take out access rights on a particular database Sql Commands CategoryWe have different sql commands for different-different purpose. We can grouped Sql Commands into five major categories depending on their functionality.
SQL is the Structured Query Language used to store, manipulate, and retrieve data present in a database server. MySQL is a relational database management system. It supports large databases and is customizable. This article will discuss the important commands in SQL. SQL commands are instructions that are used to communicate with the database. It is used to perform specific
tasks, work, and functions with data in the database. Data Definition Language helps you to define the database structure or schemas. DDL commands are capable of creating, deleting, and modifying data. Here are some commands that come under DDL: It is used to create a new table in the database. Syntax: CREATE TABLE TableName ( Column1 datatype, Column2 datatype, Column3 datatype, ColumnN datatype ); Example: CREATE TABLE Employees { Emp_Id int(3), Emp_Name varchar(20) }; This will create a table Employees with Emp_ID and Emp_Name. ALTERAlter is used to alter the structure of the database. Syntax: ALTER TABLE TableName ADD ColumnName Datatype; ALTER TABLE TableName DROP COLUMN ColumnName; Example: ALTER TABLE Employees ADD BloodGroup varchar(255); This will add a column BloodGroup to the existing table Employees. ALTER TABLE Employees DROP BloodGroup varchar(255); This will drop the column BloodGroup from the existing table. DROPIt is used to delete both the structure and record in the table. Syntax: DROP TABLE TableName; Example: DROP TABLE Employees; This SQL command will remove the table structure along with its data from the database. TRUNCATEThis truncate command is used to delete all the rows from the table and free the space. Syntax: TRUNCATE TABLE TableName; Example: TRUNCATE TABLE Employees; Data Manipulation LanguageData Manipulation Language (DML) is the language that gives users the ability to access, or manipulate the condition that the appropriate data model has inherited. Here are some SQL commands that come under DML:
INSERTINSERT command is used to insert new rows or records in a table. Syntax: INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN); Example: INSERT INTO Employees(Emp_Id, Emp_Name) VALUES(04, “Sam Tully”); UPDATEThis command is used to update or modify the value of a column in the table. Syntax: UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition]; Example: UPDATE Employees SET Salary = 1000 WHERE Emp_Id = 04; The above code will modify the salary of the employee with Emp_ID= 04; DELETEDELETE is used for removing one or more rows from the table. Syntax: DELETE FROM TableName WHERE Condition; Example: DELETE FROM Employees WHERE Emp_Id = 04; This will delete the record of the Employees whose Emp_ID is 4. Data Control LanguageData Control Language is used to manage roles, permissions, and referential integrity on the database. Here are some commands that come under DCL:
GRANTGRANT command is used to give access or permission to specific users. Syntax: GRANT object_privileges ON table_name TO user_name1; Example: GRANT SELECT, UPDATE ON Student TO John Doe; REVOKEREVOKE is used for taking back permission, which is given to the user. Syntax: REVOKE object_privileges ON table_name FROM user1, user2,… userN; Example: REVOKE UPDATE ON Student FROM John Doe; Transaction Control LanguageTCL manages the issues and matters related to the transactions in any database. They are used to rollback or commit the changes in the database. Here are some commands that come under TCL:
COMMITThe COMMIT command is used to save all the transactions to the database. Syntax: COMMIT; Example: DELETE FROM Employees WHERE AGE = 21; COMMIT; ROLLBACKThe rollback command is used to undo transactions that have not already been saved to the database. Syntax: ROLLBACK; Example: DELETE FROM CUSTOMERS WHERE AGE = 21; ROLLBACK; GROUP BYThe SQL GROUP BY command is used with the SELECT command to group the rows which have the same value. Syntax: SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2; Example: Consider the following data: If you want to know the total points of each customer, then the GROUP BY query would be used as follow: This would produce the following results- HAVINGThe HAVING clause allows you to filter the results based on the groups. The SQL HAVING clause is used with the GROUP BY clause. Consider the following dataset: Following is an example, which would display a record for a similar city that would be equal to ‘Hampton’ This would produce the following result: ORDER BYThe ORDER BY keyword in SQL is used to sort the result in ascending or descending order. Note: By default, the results are sorted in ascending order. Syntax: SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC Example: SELECT first_name, last_name Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now! ConclusionIt is crucial to understand the important commands in SQL to understand the basics of how to use and manipulate the dataset. The commands help us in various functions which include writing, updating, modifying, and deleting the data from the tables. Check out our tutorial on replace function in SQL. Now that you know about the SQL commands, it’s time for you to start querying and manipulating this updated and existing data in different datasets. This is an essential step to moving forward in your journey to becoming an SQL expert. If you liked this article, and want to get certified, check out Simplilearn’s Business Analyst Master’s Program. This comprehensive program covers SQL in-depth, and earning your certification in this field can help to jumpstart your career. Do you have any questions for us? Please leave them in the comments section, and we’ll have our experts in the field answer them for you! Which command category are commands you apply to the database itself?SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and queries of data.
How do I create a SQL database?Create a database on the Cloud SQL instance. In the Google Cloud console, go to the Cloud SQL Instances page. ... . To open the Overview page of an instance, click the instance name.. Select Databases from the SQL navigation menu.. Click Create database.. In the New database dialog, specify the name of the database.. Click Create.. How do I create a SQL database from the command line?Create a MySQL Database Using CLI. SSH into your server as root.. Log into MySQL as root: Copy. mysql -u root.. Create a new database user: Copy. GRANT ALL PRIVILEGES ON *. ... . Log out of MySQL by typing: \q .. Log in as the new database user you just created: Copy. mysql -u db_user -p. ... . Create a new database: Copy.. Which type of SQL is used for query data from databases?Data Query Language (DQL): This refers to SQL commands used to retrieve data from databases. The commonly used SQL command here is SELECT. Data Manipulation Language (DML): These refer to SQL commands used to manipulate data and perform critical operations on databases, such as INSERT, UPDATE, and DELETE.
|