Comandos útiles mySQL

Comandos útiles OpenSSL

11 de mayo de 2023

Comandos útiles firewalld

22 de junio de 2023

The command throughout the article is divided into the following categories

Users Management Commands
Database Management Commands
Table Commands
Keys in MySQL
Constraints in MySQL
Views
Manipulation Commands
Joins
Index
Data Control Commands
Triggers
Transaction Commands
Extra Commands

Users Management Commands

User management commands are those commands which create users for them to access the database and perform various actions. The commands are:

CREATE USER
DROP USER
SHOW USER

1. CREATE USER

With the help of this command, you can create users in the database server.

Syntax:

CREATE USER AccountName IDENTIFIED BY 'PASSWORD';

Example:

CREATE USER SahitiKappagantula IDENTIFIED BY 'InfoEdge@123';

2. DROP USER

Used to delete a user from the database server permanently.

Syntax:

DROP USER AccountName;

Example:

DROP USER SahitiKappagantula;

3. SHOW USER

Used to display the list of users having access to a particular database server.

Syntax:

SELECT USER from MYSQL.DATABASENAME;

Example:

SELECT USER from MYSQL.Patients;

Database Management Commands

Database management commands are those commands which are used to create a database and view them. The commands are:

CREATE DATABASE
SHOW DATABASE
DROP DATABASE
USE DATABASE

Must read: What are the Advantages of DBMS?

4. CREATE DATABASE

Used to create a database.

Syntax:

CREATE SCHEMA SchemaName;

Example:

CREATE SCHEMA PatientsDatabase;

5. SHOW DATABASE

Used to list all the databases created till date.

Syntax:

SHOW DATABASES;
-–OR
SHOW SCHEMAS;

Example:

SHOW DATABASES;

Also read: What are the Applications of DBMS?

6. DROP DATABASE

Used to delete a database along with the data present in the database.

Syntax:

DROP SCHEMA SchemaName;
–OR
DROP DATABASE DatabaseName;

Example:

DROP SCHEMA PatientsDatabase;
 
DROP DATABASE PatientsDatabase;

7. USE DATABASE

Used by the clients to inform which database to be chosen to perform all data manipulation tasks.

Syntax:

USE SchemaName;

Example:

USE PatientsDatabase;

Once the database is created, the next step is to create tables to build relations between them and generate meaningful insights. So, in the next section of MySQL Commands, let us understand the table commands.

Table Commands

In this section of commands, let us understand the various commands used to create tables and manipulate data in the tables. The following commands will be discussed here:

CREATE TABLE
SHOW TABLE
ALTER TABLE
DESCRIBE TABLE
TRUNCATE TABLE
DROP TABLE
RENAME TABLE
INSERT INTO
UPDATE TABLE
DELETE TABLE
ADD COLUMNS
DELETE COLUMNS
SHOW COLUMNS
RENAME COLUMNS

8. CREATE TABLE

Used to create a new table in a database.

Syntax:

CREATE TABLE TableName (
	Column1 Datatype,
	Column2 Datatype,
	Column3 Datatype,
   ....
);

Example:

CREATE TABLE Patients
(
PatientID int,
PatientName varchar(255),
Sex varchar(255),
Age int,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);

9. CREATE TABLE AS

Used to create a new table from an existing table.

Syntax:

CREATE TABLE NewTableName AS
    SELECT Column1, Column2,...
    FROM ExistingTableName
    WHERE ....;

Example:

CREATE TABLE SamplePatientsTable AS
SELECT PatientID, PatientName, Age
FROM Patients;

10. SHOW TABLE

Used to list all the tables created in a database. Here, please note that you have to use the database first before displaying the database

Syntax:

SHOW TABLES;

Example:

USE PatientsDatabase;
SHOW TABLES;

11. ALTER TABLE

Used to manipulate columns or constraints by addition, modification or deletion.

Syntax:

ALTER TABLE TableName
ADD ColumnName datatype;

Example:

ALTER TABLE Patients
ADD INT BP;

12. DESCRIBE TABLE

Used to display the structure of the tables. It will display the column names, their data types, default values, and keys.

Syntax:

DESCRIBE TableName;

Example:

DESCRIBE Patients;

13. TRUNCATE TABLE

Used to delete the data present in the tables, without deleting the table.

Syntax:

TRUNCATE TABLE TableName;

Example:

TRUNCATE TABLE Patients;

14. DROP TABLE

Used to delete a table including its data permanently.

Syntax:

DROP TABLE TableName;

Example:

DROP TABLE Patients;

15. RENAME TABLE

Used to rename a table.

Syntax:

RENAME TABLE TableName1 TO TableName2;

Example:

RENAME TABLE Patients TO PatientsInfo;

16. INSERT INTO

Used to insert new records into a table.

Syntax:

INSERT INTO TableName (Column1, Column2, ...)
VALUES (Value1, Value2,...);
 
--OR
 
INSERT INTO TableName
VALUES (Value1, Value2);

Example:

INSERT INTO Patients
(
PatientID,PatientName,Sex,Age,Address,PostalCode,State,Country,RegDate date
) VALUES ('06', 'Suhana','F', ‘12’, 'House No 34C Jubilee Hills', '500046', ‘Telangana’, 'India', ‘02/09/2021’);
 
–OR
INSERT INTO Patients
VALUES ('06', 'Suhana','F', ‘12’, 'House No 34C Jubilee Hills', '500046', ‘Telangana’, 'India', ‘02/09/2021’);

17. UPDATE TABLE

Used to modify the existing data items in a table.

Syntax:

UPDATE TableName
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE Patients
SET PatientName = 'Afsana', PostalCode= '500034'
WHERE PatientID = 1;

18. DELETE TABLE

Used to delete the existing data items in a table.

Syntax:

DELETE FROM TableName
WHERE condition;

Example:

DELETE FROM Patients
WHERE PatientName='Anay';

19. ADD COLUMNS IN A TABLE

Used to add new columns in a table.

Syntax:

ALTER TABLE TableName   
    ADD COLUMN NewColumnName ColumnDataType;
–OR 
–To add after a particular column
ALTER TABLE TableName   
    ADD COLUMN NewColumnName ColumnDataType AFTER ColumnName;
–OR
–To add multiple columns
ALTER TABLE TableName   (
    ADD COLUMN NewColumnName ColumnDataType AFTER ColumnName,
    ADD COLUMN NewColumnName ColumnDataType AFTER ColumnName );

Example:

ALTER TABLE Patients   
    ADD COLUMN BP int;
–OR 
–To add after a particular column
ALTER TABLE Patients   
    ADD COLUMN BP int AFTER Age;
–OR
–To add multiple columns
ALTER TABLE TableName (  
    ADD COLUMN BP int AFTER Age,
    ADD COLUMN Weight int AFTER BP);

20. DELETE COLUMNS IN A TABLE

Used to delete columns from a table.

Syntax:

ALTER TABLE TableName   
    DROP COLUMN ColumnName;
–OR
–To drop multiple columns
ALTER TABLE TableName   (
    DROP COLUMN ColumnName,
    DROP COLUMN ColumnName);

Example:

ALTER TABLE Patients   
    ADD COLUMN BP int;
–OR 
–To add after a particular column
ALTER TABLE Patients   
    ADD COLUMN BP int AFTER Age;
–OR
–To add multiple columns
ALTER TABLE TableName (  
    ADD COLUMN BP int AFTER Age,
    ADD COLUMN Weight int AFTER BP);

21. SHOW COLUMNS IN A TABLE

Used to display all the columns present in the database.

Syntax:

SHOW COLUMNS FROM TableName FROM DatabaseName;  
--OR  
SHOW COLUMNS FROM DatabaseName.TableName;

Example:

SHOW COLUMNS FROM Patients FROM PatientsDatabase;  
--OR  
SHOW COLUMNS FROM PatientsDatabase.Patients;

22. RENAME COLUMNS IN A TABLE

Used to rename columns present in a table.

Syntax:

ALTER TABLE TableName   
CHANGE COLUMN OldColumnName NewColumnName DataType;
--OR
–To change multiple column names with CHANGE query
ALTER TABLE TableName  (
CHANGE COLUMN OldColumnName NewColumnName DataType,
CHANGE COLUMN OldColumnName NewColumnName DataType);
--OR
ALTER TABLE TableName   
RENAME COLUMN OldColumnName TO NewColumnName;  
--OR
–To change multiple column names with RENAME query
ALTER TABLE TableName   (
RENAME COLUMN OldColumnName TO NewColumnName,
RENAME COLUMN OldColumnName TO NewColumnName );

Example:

ALTER TABLE Patients   
CHANGE COLUMN Address PatientAddress VARCHAR(255);
--OR
To change multiple column names with CHANGE query
ALTER TABLE Patients   (
CHANGE COLUMN Address PatientAddress VARCHAR(255),
CHANGE COLUMN BP PatientBP INT);
—OR
ALTER TABLE Patients    
RENAME COLUMN Address TO PatientAddress;  
–OR
ALTER TABLE Patients    (
RENAME COLUMN Address TO PatientAddress,
RENAME COLUMN BP TO PatientBP);

Keys in MySQL

Multiple tables are uniquely identified with the help of the various keys present in the tables. These keys help the users build relationships between multiple tables.

The following are 5 different types of keys used commonly.

Candidate Key
Primary Key
Super Key
Alternate Key
Foreign Key

Candidate Key

A set of attributes that can uniquely identify a tuple. A single relation in the database can have multiple candidate keys, which can either be a simple or composite key.

Super Key

A set of attributes that can uniquely identify a tuple is known as Super Key. You should know that a candidate key is a superkey, but a super key is not a candidate key isn’t true.

23. Primary Key

A set of attributes that can be used to uniquely identify every tuple is a primary key. If there are multiple candidate keys present in a relationship, out of those, only one can be chosen as a primary key.

Syntax:

CREATE TABLE TableName (
	Column1 Datatype,
	Column2 Datatype PRIMARY KEY,
	Column3 Datatype,
   ....
);

Example:

CREATE TABLE Patients
(
PatientID int PRIMARY KEY,
PatientName varchar(255),
Sex varchar(255),
Age int,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);

Alternate Key

All the candidate keys other than the primary key are called alternate keys.

24. Foreign Key

Attributes that can only take the data values present as the value of some other attribute are known as the foreign key to the attribute to which it refers.

Syntax:

CREATE TABLE TableName (
	Column1 Datatype,
	Column2 Datatype PRIMARY KEY,
	Column3 Datatype,
     CONSTRAINT FKeyName FOREIGN KEY (CurrenTableColumn)  
     REFERENCES ParentTableColumn  
   ....
);

Example:

CREATE TABLE Doctors
(
DoctorID int PRIMARY KEY,
DoctorName varchar(255),
CONSTRAINT DocPatient FOREIGN KEY (DoctorID)  
REFERENCES PatientID
);

Constraints in MySQL

Constraints are used in the database to enforce certain data rules while creating tables. They are also used to check values in the database. The most popular constraints are:

UNIQUE
NOT NULL
CHECK
DEFAULT

25. UNIQUE Constraint

Make sure all the data values in that particular column are unique. More than one column can have the UNIQUE constraint

Syntax:

CREATE TABLE TableName (
	Column1 Datatype,
	Column2 Datatype UNIQUE,
	Column3 Datatype,
   ....
);

Example:

CREATE TABLE Patients
(
PatientID int UNIQUE,
PatientName varchar(255),
Sex varchar(255),
Age int,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);

26. NOT NULL Constraint

Make sure that no null value can be stored in a column of a table. More than one column can have the NOT NULL constraint

Syntax:

CREATE TABLE TableName (
	Column1 Datatype,
	Column2 Datatype NOT NULL,
	Column3 Datatype,
   ....
);

Example:

CREATE TABLE Patients
(
PatientID int NOT NULL,
PatientName varchar(255),
Sex varchar(255) NOT NULL,
Age int,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);

27. CHECK Constraint

Make sure that all the values in a column satisfy the mentioned condition

Syntax:

CREATE TABLE TableName (
	Column1 Datatype,
	Column2 Datatype CHECK (Column2 Condition),
	Column3 Datatype,
   ....
);

Example:

CREATE TABLE Patients
(
PatientID int,
PatientName varchar(255),
Sex varchar(255,
Age int CHECK (Age >=20),
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);

28. DEFAULT Constraint

Make sure that the mentioned default value is taken automatically when no value is specified for that data record.

Syntax:

CREATE TABLE TableName (
	Column1 Datatype,
	Column2 Datatype DEFAULT DefaultValue,
	Column3 Datatype,
   ....
);

Example:

CREATE TABLE Patients
(
PatientID int,
PatientName varchar(255),
Sex varchar(255),
Age int DEFAULT ‘0’,
Address varchar(255),
PostalCode int,
State varchar(255),
Country varchar(255),
RegDate date
);

VIEWS

Views are used to create a virtual table based on the output of the SQL query given by the users.

Views have a similar outlook as that of a table. They have rows and columns and can have data fields from multiple tables in the database.

29. CREATE VIEW

Used to create a view from single or multiple tables.

Syntax:

CREATE VIEW ViewName AS
	SELECT Column1, Column2, …
	FROM TableName
     WHERE Condition;

Example:

CREATE VIEW Kids
(
PatientID int,PatientName varchar(255),Sex varchar(255),Age int
FROM Patients
WHERE Age <=18;
);

30. UPDATE VIEW

A view can be updated by using the CREATE OR REPLACE VIEW command.

Syntax:

CREATE OR REPLACE VIEW ViewName AS
	SELECT Column1, Column2, …
	FROM TableName
     WHERE Condition;

Example:

CREATE OR REPLACE VIEW Kids
(
PatientID int,PatientName varchar(255),Sex varchar(255),Age int
FROM Patients
WHERE Age <=18;
);

31. DROP VIEW

Used to delete a view.

Syntax:

DROP VIEW ViewName;

Example:

DROP VIEW Kids;

Also Explore:

Next in this MySQL commands article, let us understand the data manipulation commands.

Manipulation Commands

Data Manipulation commands are used to manipulate, alter and update the data according to the client requirements. The commands in this section are:

SELECT
CLAUSES
CONDITIONAL OPERATORS
AGGREGATE FUNCTIONS
SET OPERATIONS

32. SELECT

Used to select a set of data points from a database. It stores the data returned as output in the result set.

Syntax:

SELECT column1, column2, ...
FROM TableName;
--(*)-> select all from the table
SELECT * FROM TableName;

Example:

SELECT PatientName, State FROM Patients;
SELECT * FROM Patients;

MySQL Clauses

MySQL Clauses used with the SELECT statement are as follows:

WHERE
DISTINCT
FROM
ORDER BY
GROUP BY
HAVING

33. WHERE

Used to filter the data by mentioning conditions. This clause is generally used with the SELECT, INSERT, UPDATE, and DELETE commands.

Syntax:

SELECT column1, column2, ...
FROM TableName WHERE Conditions;

Example:

SELECT PatientName, State FROM Patients WHERE Age > 20;

34. DISTINCT

Used to return only the distinct values from a dataset.

Syntax:

SELECT DISTINCT Column1, Column2, ...
FROM TableName;

Example:

SELECT DISTINCT State FROM Patients;

35. FROM

Used to retrieve data from a table.

Syntax:

SELECT Column1, Column2, ...
FROM TableName;

Example:

SELECT PatientID, State FROM Patients;

36. ORDER BY

Used to sort the results in an ascending or descending order. Here, you should know that by default, the results are sorted in ascending order. If you wish to sort the data in descending order, then you have to use the DESC keyword.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
ORDER BY Column1, Column2, ... DESC;

Example:

SELECT * FROM Patients
ORDER BY Age;  
 
SELECT * FROM Patients
ORDER BY Age DESC;
 
SELECT * FROM Patients
ORDER BY Age, PatientName;
 
SELECT * FROM Patients
ORDER BY Age DESC, PatientName ASC;

37. GROUP BY

Most commonly used with the aggregate functions to group the output by one or more columns.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE Conditions
GROUP BY Column1, Column2, ..., 
ORDER BY Column1, Column2, ... DESC;

Example:

SELECT COUNT(PatientID), State
FROM Patients
GROUP BY State
ORDER BY COUNT(PatientID);

38. HAVING

Used with the aggregate functions to mention conditions, and filter data.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE Conditions
GROUP BY Column1, Column2, ..., 
HAVING Conditions
ORDER BY Column1, Column2, ...;

Example:

SELECT COUNT(PatientID), State
FROM Patients
GROUP BY State
ORDER BY COUNT(Age) > 40;

CONDITIONAL OPERATORS

This section of MySQL commands consists of the various conditional operators used to manipulate data. Commands covered in this section of the article are as follows:

AND
OR
NOT
BETWEEN
LIKE
IS NULL
IS NOT NULL
IN
EXISTS
ALL
ANY

39. AND

Used to filter records based on one or more conditions. It displays all those records that satisfy all the conditions separated by AND and generates an output as TRUE.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE Condition1 AND Condition2 ...;

Example:

SELECT * FROM Patients
WHERE State='Telangana' AND Age > 23;

40. OR

Used to filter records that satisfy either of the conditions and generate output as TRUE.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE Condition1 OR Condition2 ...;

Example:

SELECT * FROM Patients
WHERE State='Telangana' OR Age > 23;

41. NOT

Used to display those records which do not satisfy a condition.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE NOT Condition;

Example:

SELECT * FROM Patients
WHERE NOT State='Telangana';

42. BETWEEN

Used to select values from a given range.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE ColumnName BETWEEN Value1 AND Value2;

Example:

SELECT * FROM Patients
WHERE Age BETWEEN 15 AND 18;

43. LIKE

Most commonly used in a WHERE clause to search for a pattern in a column of a table with the help of wildcards.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE ColumnName LIKE Pattern;

Example:

SELECT * FROM Patients
WHERE PatientName LIKE ‘A%’;

44. IS NULL

Used to mention NULL as a value for a single column or multiple columns.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE ColumnName IS NULL;

Example:

SELECT * FROM Patients
WHERE State IS NULL;

45. IS NOT NULL

Used to restrict a single column or multiple columns having NULL values.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE ColumnName IS NOT NULL;

Example:

SELECT * FROM Patients
WHERE State IS NOT NULL;

46. IN

Used to specify multiple values in a WHERE clause.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE ColumnName IN (Value1, Value2, . . .);

Example:

SELECT * FROM Patients
WHERE State IN (‘Rajasthan’, ‘Telangana’);

47. EXISTS

Used to test if the required record exists or not.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE EXISTS
(SELECT ColumnName FROM TableName WHERE Conditions);

48. ALL

Used with a WHERE or HAVING clause to return all the records which meet conditions.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE ColumnName Operator ALL
(SELECT ColumnName FROM TableName WHERE Conditions);

Example:

SELECT PatientName
FROM Patients
WHERE PatientID = ALL (SELECT PatientID FROM Patients WHERE Age < 35);

49. ANY

Used with a WHERE or HAVING clause to return only those records which meet conditions.

Syntax:

SELECT Column1, Column2, ...
FROM TableName
WHERE ColumnName Operator ANY
(SELECT ColumnName FROM TableName WHERE Conditions);

Example:

SELECT PatientName
FROM Patients
WHERE PatientID = ANY (SELECT PatientID FROM Patients WHERE BETWEEN 20 AND 35);

50. NOT EQUAL

Used for returning a set of rows that are not equal.

Syntax:

SELECT Column1, Column2, ...
FROM TableName,
WHERE ColumnName <> Conditions; 
–OR
SELECT Column1, Column2, ...
FROM TableName,
WHERE ColumnName != Conditions;

Example:

SELECT PatientName
FROM Patients
WHERE Age <> ‘35’;
–OR
SELECT PatientName
FROM Patients
WHERE Age != ‘35’;

AGGREGATE FUNCTIONS

Aggregate functions as the name suggests, help the users perform simple operations in MySQL as follows

SUM()
AVG()
MIN()
MAX()
COUNT()
FIRST()
LAST()

51. SUM

Used to return the addition of a numeric column from a table.

Syntax:

SELECT SUM(ColumnName)
FROM TableName;

Example:

SELECT SUM(Age)
FROM Patients;

52. AVG

Used to return the average value of a numeric column from a table.

Syntax:

SELECT AVG(ColumnName)
FROM TableName;

Example:

SELECT AVG(Age)
FROM Patients;

53. MIN

Used to return the smallest value from a column of a table.

Syntax:

SELECT MIN(ColumnName)
FROM TableName;

Example:

SELECT MIN(Age)
FROM Patients;

54. MAX

Used to return the largest value from a column of a table.

Syntax:

SELECT MAX(ColumnName)
FROM TableName;

Example:

SELECT MAX(Age)
FROM Patients;

55. COUNT

Used to return the count of elements in a column of a table.

Syntax:

SELECT COUNT(ColumnName)
FROM TableName;

Example:

SELECT COUNT(Age)
FROM Patients;

56. FIRST

Used to return the first value of a column from a table.

Syntax:

SELECT ColumnName
FROM TableName
LIMIT 1;
–Select first 3 records
SELECT ColumnName
FROM TableName
LIMIT 3;

Example:

SELECT PatientID, Age
FROM Patients
LIMIT 1;
– Select first 3 records
SELECT PatientID, Age
FROM Patients
LIMIT 3;

57. LAST

Used to return the last value of a column from a table.

Syntax:

SELECT ColumnName
FROM TableName
ORDER BY ColumnName DESC
LIMIT 1;

Example:

SELECT PatientID, Age
FROM Patients
ORDER BY PatientID DESC
LIMIT 1;

SET OPERATIONS

There are three set operations used on a daily basis in manipulating databases. They are:

UNION
INTERSECT
MINUS

58. UNION

Used to combine the output of two or more SELECT queries. Here, you should remember that the data types and the number of columns must be the same. Also, once the rows are combined, the UNION operation removes duplicate rows.

Syntax:

SELECT ColumnName FROM Table1
UNION
SELECT ColumnName FROM Table2;

Example:

SELECT PatientID FROM Patients
UNION
SELECT PatientID FROM Patients2;

59. UNION ALL

Syntax:

SELECT ColumnName FROM Table1
UNION ALL
SELECT ColumnName FROM Table2;

Example:

SELECT PatientID FROM Patients
UNION ALL
SELECT PatientID FROM Patients2;

60. INTERSECT

Used to return the common records from two or more SELECT queries. Here, you should remember that the data types and the number of columns must be the same. Also, the INTERSECT operation will sort the data in ascending order automatically.

Syntax:

SELECT ColumnName FROM Table1
INTERSECT
SELECT ColumnName FROM Table2;

Example:

SELECT PatientID FROM Patients
INTERSECT
SELECT PatientID FROM Patients2;

61. MINUS

Used to return rows either from the first query and not return the second query.

Syntax:

SELECT ColumnName FROM Table1
MINUS
SELECT ColumnName FROM Table2;

Example:

SELECT PatientID FROM Patients
MINUS
SELECT PatientID FROM Patients2;

Joins

JOINS in MySQL are used to combine records from two or more tables, based on a relationship between tables. Let’s consider the following table apart from the above Patients table, to understand the syntax of joins.

62. LEFT JOIN:

Returns all records from the left table along with all the records from the right table, which satisfy the condition.

Syntax:

SELECT Column1, Column2 …(s)
FROM Table1
LEFT JOIN  Table2 ON Table1.ColumnName = Table2.ColumnName;

Example:

SELECT Diseases.DiseaseID, Patients.PatientName
FROM Diseases
LEFT JOIN Patients ON Diseases.PatientID = Patients.PatientID
ORDER BY Diseases.DiseaseID;

63. RIGHT JOIN:

Returns all records from the right table along with all the records from the left table, which satisfy the condition.

Syntax:

SELECT Column1, Column2 …(s)
FROM Table1
RIGHT JOIN  Table2 ON Table1.ColumnName = Table2.ColumnName;

Example:

SELECT Diseases.DiseaseID, Patients.PatientName
FROM Diseases
RIGHTJOIN Patients ON Diseases.PatientID = Patients.PatientID
ORDER BY Diseases.DiseaseID;

64. INNER JOIN:

Returns records that have matching values in both the tables.

Syntax:

SELECT Column1, Column2 …(s)
FROM Table1
INNER JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;

Example:

SELECT Diseases.DiseaseID, Patients.PatientName
FROM Diseases
INNER JOIN Patients ON Diseases.PatientID = Patients.PatientID;

65. FULL JOIN:

Returns records that either have a match in the left or the right table.

Syntax:

SELECT Column1, Column2 …(s)
FROM Table1
FULL OUTER JOIN  Table2 ON Table1.ColumnName = Table2.ColumnName;

Example:

SELECT Diseases.DiseaseID, Patients.PatientName
FROM Diseases
FULL OUTER JOIN Patients ON Diseases.PatientID = Patients.PatientID;

Moving further with the SQL commands, let us understand the

Indexes

Indexes are lookup tables used to retrieve data. An index as the name suggests acts as a pointer to the data present in the table.

66. CREATE INDEX

Used to create an index for a table.

Syntax:

CREATE INDEX IndexName ON TableName;

Example:

CREATE INDEX SampleIndex ON Patients;

67. ALTER INDEX

Used to alter an index

Syntax:

ALTER INDEX IndexName ON ObjectName;

Example:

ALTER INDEX SampleIndex ON PatName;

68. DROP INDEX

Used to delete or remove an index

Syntax:

DROP INDEX IndexName;

Example:

DROP INDEX SampleIndex;

Data Control Commands

The Data Control commands are used to control the privileges of a database. The commands are:

GRANT
REVOKE

69. GRANT

Used to provide users with the privileges for a database.

Syntax:

GRANT Privileges ON Objects TO user;

Example:

GRANT CREATE ANY TABLE TO localhost;

70. REVOKE

Used to withdraw the privileges given to the users.

Syntax:

REVOKE privileges ON object FROM user;

Example:

REVOKE INSERT ON *.* FROM Patients;

Triggers

Triggers are a set of SQL statements that are invoked automatically in response to an event. Every trigger, associated with a table, is activated by DML commands such as INSERT, UPDATE, and DELETE.

There are two types of triggers:

Row-Level Trigger: Activated for each row, affected by INSERT, UPDATE or DELETE commands.
Statement-Level Trigger: Activated for each of the events which occurs, irrespective of any of the DML commands.

Types of Actions in Triggers

Before Insert: Activated before the data is inserted into the table.
After Insert: Activated after the data is inserted into the table..
Before Delete: Activated before the removal of data from the table.
After Delete: Activated after the removal of data from the table.
Before Update: Activated before the data being updated in the table.
After Update: Activated after the data being updated in the table

71. CREATE TRIGGER

Used to create a trigger in MySQL.

Syntax:

CREATE TRIGGER TriggerName    
    (AFTER | BEFORE) (INSERT | UPDATE | DELETE)  
         ON TableName FOR EACH ROW    
         BEGIN    
        --Declarations    
        --Trigger Code    
        END;

Transaction Commands

Transaction commands deal with all the transactions related to the database. The commands are:

COMMIT
ROLLBACK
SAVEPOINT
- RELEASE SAVEPOINT
SET TRANSACTION

72. COMMIT

Used to save all the transactions of the database since the last COMMIT or ROLLBACK command.

Syntax:

COMMIT;

Example:

DELETE FROM Patients WHERE Age > 45;
COMMIT;

73. ROLLBACK

Syntax:

ROLLBACK;

Example:

Used to undo all the transactions since the last COMMIT or ROLLBACK.

DELETE FROM Patients WHERE Age > 45;
ROLLBACK;

74. SAVEPOINT

Used to roll the transaction back to a certain point without actually rolling back the entire transaction.

Syntax:

--Save the SAVEPOINT
SAVEPOINT SAVEPOINTNAME; 
--Rollback to the Savepoint
ROLLBACK TO SAVEPOINTNAME;

Example:

SAVEPOINT EX1;
DELETE FROM Patients WHERE Age > 45;
SAVEPOINT EX2;

74.1 RELEASE SAVEPOINT

Used to remove a SAVEPOINT created previously.

Syntax:

RELEASE SAVEPOINT SAVEPOINTNAME;

Example:

RELEASE SAVEPOINT EX1;

75. SET TRANSACTION

Used to give a name to the transaction.

Syntax:

SET TRANSACTION [ READ WRITE | READ ONLY ];

ARTICULO ORIGINAL AQUÍ: https://www.shiksha.com/online-courses/articles/most-popular-mysql-commands/

Extra Commands

71. DELETE DUPLICATE FIELDS

Syntax:

Duplicates in single column
---------------------------
SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;

Duplicates in multiple columns
------------------------------
SELECT 
    col1, COUNT(col1),
    col2, COUNT(col2),
    ...

FROM
    table_name
GROUP BY 
    col1, 
    col2, ...
HAVING 
       (COUNT(col1) > 1) AND (COUNT(col2) > 1)

Show max allowed packet (extend in 50-server.conf when get error "Got an error reading communication packets")
------------------------
SHOW VARIABLES LIKE 'max_allowed_packet';

Get databases sizes in Mb
------------------------
SELECT table_schema AS 'DB Name', ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS 'DB Size in MB' FROM information_schema.tables GROUP BY table_schema;

Comandos útiles mySQL

Autor

Deja una respuesta Cancelar la respuesta