Basic commands for interacting with MySQL
Show all available databases
SHOW DATABASES;
Create a new database
CREATE DATABASE BD_Name;
Delete database
DROP DATABASE BD_Name;
Select a database to use
USE BD_Name;
View available tables
SHOW TABLES;
Create table
CREATE TABLE TB_Name1(
id INT AUTO_INCREMENT PRIMARY KEY,
field VARCHAR(255) NOT NULL);Table with associated foreign key
CREATE TABLE TB_Name2(
id INT AUTO_INCREMENT PRIMARY KEY,
field2 VARCHAR(255) NOT NULL,
forkey INT NOT NULL,
FOREIGN KEY (forkey) REFERENCES TB_Name1(id));In tables TB_Name1 and TB_Name2, the id fields are different (unique), a foreign key is used for connection
View table columns/fields
SHOW COLUMNS FROM TB_Name;
Insert fields into table
INSERT INTO TB_Name(field1,field2) values (“value 1”,”value 2”);
View all table contents
SELECT * FROM TB_Name;
View selected table fields
SELECT ColumnName1, ColumnName2 FROM TB_Name;
View unique table fields
SELECT DISTINCT ColumnName FROM TB_Name;
View records with condition
added… WHERE condition;
example:
SELECT ColumnName1, ColumnName2 FROM TB_Name WHERE id>10;
View the limit on the number of records
added… LIMIT value;
example (output of 2 records):
SELECT * FROM TB_Name LIMIT 2;
Replacing column names in output
SELECT ColumnName1 AS Name1, ColumnName2 AS Value FROM TB_Name;
Sorting
is added… ORDER BY param; – sorting in ascending order
is added… ORDER BY param DESC; – sort in descending order
SELECT * FROM TB_Name ORDER BY ColumnName1;
Adding Columns
Add a FieldName field of type type to the TB_Name1 table
ALTER TABLE TB_Name1 ADD FieldName type;
Example
ALTER TABLE TB_Name1 ADD Numbers INT;
default wound values are NULL
Updating values in a table
UPDATE TB_Name SET Field=Value where id=x;
example:
UPDATE TB_Name1 SET Numbers=123456 where id=1;
UPDATE TB_Name1 SET Numbers=125556 where id=1;
Sampling with wildcard searches
SELECT * FROM TB_Name WHERE ColumnName1 LIKE “%Tmp%”;
The % symbol means any number of characters (like * in linux)
example:
SELECT * FROM TB_Name1 WHERE Numbers LIKE “%23%”;
Selection with logical operators
SELECT * FROM TB_Name WHERE ColumnName1 >X LogOperator ColumnName2 <Y;
example:
SELECT * FROM TB_Name1 WHERE ColumnName1 >X AND ColumnName2 <Y;
AND = &&
OR=||
NOT = !, example – (SELECT * FROM TB_Name1 WHERE NOT id =1)
Sampling values between 1 and 5
BETWEEN (SELECT * FROM TB_Name1 WHERE id BETWEEN 1 AND 5)
Deleting table fields
DELETE FROM TB_Name WHERE ColumnName1 >X;
example:
DELETE FROM TB_Name1 WHERE id =2;
Bulk adding values to a table
INSERT INTO TB_Name (ColumnName1, ColumnName2) VALUES (“Value 1”, “Value 2”), (“Value 3”, “Value 4”);
Database joins If one table contains keys — links to the 2nd table, then they can be combined ; let table TB_Name1 have links to TB_Name2
There are 3 types of joins:
INNER JOIN (simply JOIN)
those records where the field from TB_Name1 has a match in TB_Name2 will be included in the final table
(unmatched records will not be included)
OUTER JOIN (LEFT OUTER JOIN and RIGHT OUTER JOIN)
LEFT OUTER JOIN – all records from TB_Name1 will be included in the final table and, where possible, a match from TB_Name2 will be placed
RIGHT OUTER JOIN – all records from TB_Name2 will be included in the final table and, where possible, will be included match from TB_Name1
where there is no match — NULL is put
FULL JOIN — full join of tables taking into account keys
General view of the request
select tb1.field1 tb.2field3 from tbX combine with tbY by field tbX.id = tbY.forkey_id;
SELECT TB_Name1.Field1 TB_Name2.Field3 FROM TB_NameX INNER_TYPE TB_NameY ON TB_NameX.id= TB_NameY.forkey_id;
examples:
SELECT TB_Name1.Field1 TB_Name2.Field3 FROM TB_Name INNER JOIN TB_Name2 ON TB_Name1.id=TB_Name2.forkey_id;
SELECT TB_Name1.Field1 TB_Name2.Field3 FROM TB_Name LEFT OUTER JOIN TB_Name2 ON TB_Name1.id=TB_Name2.forkey_id;
SELECT TB_Name1.Field1 TB_Name2.Field3 FROM TB_Name RIGHT OUTER JOIN TB_Name2 ON TB_Name1.id=TB_Name2.forkey_id;
SELECT TB_Name1.Field1 TB_Name2.Field3 FROM TB_Name FULL JOIN TB_Name2 ON TB_Name1.id=TB_Name2.forkey_id;
UNION – combining tables
with all values included in the sample (just 2 tables in a row without taking into account the keys)
SELECT * FROM TB_Name1 UNION SELECT * FROM TB_Name2;
Aggregate functions
SELECT AVG(field) FROM TB_Name1;| search for average
SELECT MIN(field) FROM TB_Name1;
SELECT MAX(field) FROM TB_Name1;
SELECT MIN(field), MAX(field) FROM TB_Name1;| request for 2 conditions
SELECT SUM(field) FROM TB_Name1;
SELECT field, СOUNT(field) FROM TB_Name1;
Grouping by field
When making queries (for example, counting the number of values (COUNT)), the table may produce incorrect data — you must group by the
GROUP BY parameter
SELECT field, СOUNT(field) FROM TB_Name1 GROUP BY field;