Basic MySQL Examples

sudo apt install mysql-server

sudo mysql

#Create DataBase and
SHOW DATABASES;
CREATE DATABASE office;
SHOW DATABASES;
USE office;
CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT , fname char(255) NOT NULL, lname char(255) NOT NULL, address char(255) NOT NULL, age int(100), phone char(20), PRIMARY KEY (id));
DESCRIBE employees;

# Enter Data
INSERT INTO employees VALUES(null,'John','Smith','123 Pine St',35,'555-555-5555');
SELECT * FROM employees;
INSERT INTO employees VALUES(null,'Jake','Simpson','123 Oak Lane',26,'555-555-5551');
INSERT INTO employees VALUES(null,'John','Peterson','564 1st Ave',45,'555-555-5531');
INSERT INTO employees VALUES(null,'Sally','Smith','123 Oak Lane',29,'555-555-5871');
INSERT INTO employees VALUES(null,'Jake','Ericsson','543 Oak Lane',29,NULL);
INSERT INTO employees VALUES(null,'Jim','Johnson','423 Pine Lane',38,'555-555-5341');
INSERT INTO employees VALUES(null,'Tom','Ericsson','543 Oak Lane',29,'555-555-2351');

# Search Data
SELECT * FROM employees;
SELECT * FROM employees WHERE lname = 'Smith';
SELECT * FROM employees WHERE address LIKE '%Oak%';
SELECT * FROM employees WHERE age > 30;
SELECT * FROM employees WHERE age < 30 ORDER BY age;
SELECT * FROM employees WHERE age < 30 ORDER BY age DESC;
SELECT * FROM employees WHERE age < 30 ORDER BY age LIMIT 2;
SELECT * FROM employees WHERE age < 30 ORDER BY age DESC LIMIT 2;

SELECT fname FROM employees WHERE lname = 'Smith';
SELECT fname,lname FROM employees WHERE address LIKE '%Oak%';

#update entry
SELECT * FROM employees WHERE phone is NULL;
UPDATE employees SET phone="555-555-3333" WHERE id=5;
SELECT * FROM employees;

# Delete Entry
DELETE FROM employees WHERE id = '2';
SELECT * FROM employees;

# Delete Table
DROP TABLE employees;
SELECT * FROM employees;

# Delete DATABASE
DROP DATABASE office;
SHOW DATABASES;

# EXIT
quit