SQL

Connecting to mysql

mysql -u $username -p$password

<aside> 💡

There shouldn't be any spaces between '-p' and the password.

</aside>

Connecting to a specified host

mysql -u $username -h $hostname -P $port -p$password

Creating a Database

CREATE DATABASE $databsename;

SHOW DATABASES;

Tables

CREATE TABLE $tablename (
		id INT,
    username VARCHAR(100),
    password VARCHAR(100),
    date_of_joining DATETIME
		);

SHOW TABLES;

DESCRIBE $tablename

shows table structure

INSERT Statement

INSERT INTO $table_name VALUES (column1_value, column2_value, column3_value, ...);

INSERT INTO logins VALUES(1, 'admin', 'p@ssw0rd', '2020-07-02');

INSERT INTO $table_name(column2, column3, ...) VALUES (column2_value, column3_value, ...);

INSERT INTO logins(username, password) VALUES('administrator', 'adm1n_p@ss');

INSERT INTO logins(username, password) VALUES ('john', 'john123!'), ('tom', 'tom123!');

SELECT Statement

SELECT * FROM $table_name;

SELECT column1, column2 FROM $table_name;

DROP Statement

DROP TABLE $table_name;

ALTER Statement

ALTER TABLE $table_name ADD newColumn INT;

ALTER TABLE $table_name RENAME COLUMN newColumnt TO newerColumn;

We can also change a column's datatype with MODIFY:

ALTER TABLE $table_name MODIFY newerColumn DATE;

ALTER TABLE $table_name DROP newerColumn;

UPDATE Statement

UPDATE $table_name SET column1 = 'changeme' WHERE id > 1;USE