This article explains the basic administration commands for MySQL. No interface like phpmyadmin/mysqlworkbench will be used since one depends on a web server and the other can be very problematic depending on the scenario.
Create a database:
CREATE DATABASE prueba;
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| prueba |
| sys |
+--------------------+
5 rows in set (0.00 sec)
Create a table:
USE prueba;
CREATE TABLE tabla_prueba (campo1 varchar(80), campo2 int(11));
DESCRIBE tabla_prueba;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| campo1 | varchar(80) | YES | | NULL | |
| campo2 | int | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
Insert records:
INSERT INTO prueba_table (field1, field2) values ('AAAA', '69');
Select records:
SELECT * FROM tabla_prueba;
+--------+--------+
| campo1 | campo2 |
+--------+--------+
| AAAA | 69 |
+--------+--------+
1 row in set (0.00 sec)
Update records:
UPDATE tabla_prueba SET campo1='BBBB' WHERE campo1='AAAA';
SELECT * FROM tabla_prueba;
+--------+--------+
| campo1 | campo2 |
+--------+--------+
| BBBB | 69 |
+--------+--------+
1 row in set (0.00 sec)
Delete records:
DELETE FROM tabla_prueba WHERE campo1='BBBB';
SELECT * FROM tabla_prueba;
Empty set (0.00 sec)
Alter table:
ALTER TABLE tabla_prueba ADD campo3 VARCHAR(60);
DESCRIBE tabla_prueba;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| campo1 | varchar(80) | YES | | NULL | |
| campo2 | int | YES | | NULL | |
| campo3 | varchar(60) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
Count records:
INSERT INTO tabla_prueba (campo1, campo2) values ('AAAA', '69');
INSERT INTO tabla_prueba (campo1, campo2) values ('BBBB', '96');
SELECT COUNT(*) FROM tabla_prueba;
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.01 sec)
Sort output:
SELECT * FROM tabla_prueba ORDER BY campo2;
+--------+--------+--------+
| campo1 | campo2 | campo3 |
+--------+--------+--------+
| AAAA | 69 | NULL |
| BBBB | 96 | NULL |
+--------+--------+--------+
2 rows in set (0.00 sec)
We can also get only the last record:
SELECT * FROM tabla_prueba ORDER BY campo2 DESC LIMIT 1;
+--------+--------+--------+
| campo1 | campo2 | campo3 |
+--------+--------+--------+
| BBBB | 96 | NULL |
+--------+--------+--------+
1 row in set (0.00 sec)
If we want to select a specific record:
SELECT * FROM tabla_prueba LIMIT 1 OFFSET 0;
+--------+--------+--------+
| campo1 | campo2 | campo3 |
+--------+--------+--------+
| AAAA | 69 | NULL |
+--------+--------+--------+
1 row in set (0.00 sec)
SELECT * FROM tabla_prueba LIMIT 1 OFFSET 1;
+--------+--------+--------+
| campo1 | campo2 | campo3 |
+--------+--------+--------+
| BBBB | 96 | NULL |
+--------+--------+--------+
1 row in set (0.00 sec)
Truncate table:
TRUNCATE tabla_prueba;
SELECT * FROM tabla_prueba;
Empty set (0.00 sec)
Delete table:
SHOW TABLES;
+------------------+
| Tables_in_prueba |
+------------------+
| tabla_prueba |
+------------------+
1 row in set (0.01 sec)
DROP TABLE tabla_prueba;
SHOW TABLES;
Empty set (0.00 sec)
Delete database:
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| prueba |
| sys |
+--------------------+
5 rows in set (0.00 sec)
DROP DATABASE prueba;
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
With this, we conclude the introduction to MySQL. I hope you enjoyed it ;)