- SQL syntax can differ from one RDBMS to another - However, they are all required to follow the [ISO standard](https://en.wikipedia.org/wiki/ISO/IEC_9075) for Structured Query Language - The following examples use the MySQL/MariaDB syntax - SQL can be used to perform the following actions: - Retrieve data - Update data - Delete data - Create new tables and databases - Add / remove users - Assign permissions to these users - DBMS stores data in the form of tables - A table is made up of horizontal rows and vertical columns - The intersection of a row and a column is called a cell - Every table is created with a fixed set of columns, where each column is of a particular data type - A data type defines what kind of value is to be held by a column - Common examples are `numbers`, `strings`, `date`, `time`, and `binary data` - There could be data types specific to DBMS as well - Complete list of data types in MySQL can be found [here](https://dev.mysql.com/doc/refman/8.0/en/data-types.html) # `mysql` utility - login with empty password ```bash mysql -u <user> -p ``` - login with password - NOTE: no space following the `-p` flag ```bash mysql -u <user> -p<password> ``` - remote host and non-standard port ```bash mysql -u root -h docker.hackthebox.eu -P 3306 -p ``` ![[images/Pasted image 20251202195702.png]] - creating a db using the `mysql` utility - NOTE: SQL statements are NOT case sensitive but database and table names are case sensitive ```mysql mysql> CREATE DATABASE users; Query OK, 1 row affected (0.02 sec) ``` - listing dbs and selecting a different db ```mysql mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | users | +--------------------+ mysql> USE users; Database changed ``` ![[images/Pasted image 20251202195817.png]] - creating a table ```mysql mysql> CREATE TABLE logins ( -> id INT, -> username VARCHAR(100), -> password VARCHAR(100), -> date_of_joining DATETIME -> ); Query OK, 0 rows affected (0.03 sec) ``` - showing and describing tables ```mysql mysql> SHOW TABLES; +-----------------+ | Tables_in_users | +-----------------+ | logins | +-----------------+ 1 row in set (0.00 sec) mysql> DESCRIBE logins; +-----------------+--------------+ | Field | Type | +-----------------+--------------+ | id | int | | username | varchar(100) | | password | varchar(100) | | date_of_joining | date | +-----------------+--------------+ 4 rows in set (0.00 sec) ``` ![[images/Pasted image 20251202202356.png]]