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
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 ALL operation DOES NOT remove duplicate rows.
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. CREATE SCHEDULED EVENT
Activate a scheduled event.
Syntax:
CREATE EVENT reset_login
ON SCHEDULE EVERY 1 MINUTE
DO
UPDATE userdb AS u
JOIN loginA AS l ON u.username = l.username
SET u.attempts = 0
WHERE u.attempts > 0 AND l.last_attempt < DATE_SUB(NOW(), INTERVAL 1 MINUTE);
76. 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;
