Linux: SQL - Paiet/Tech-Journal-for-Everything GitHub Wiki

Managing Data with SQL

• Install MySQL
  • yum install mysql mysql-server
  • service mysqld start
  • /usr/bin/mysql_secure_installation
  • mysql -ppassword
• Install MariaDB
  • yum install mariadb mariadb-server
  • systemctl start mariadb
  • /usr/bin/mysql_secure_installation
  • mysql -u root -p
• insert - Add a new row to a table
• update - Modify an existing row in a table
• select - Return the values in a selected range of columns
• delete - Remove a row from a table
• from - Specifies the table whose data you want to manipulate
• where - Specifies criteria that define which elements to work with
• group by - Combines rows according to a common criteria
• order by - Sorts returned information
• join - Combines data from multiple tables to create a single set of data

Example

SHOW DATABASES;
USE test;

Example

CREATE DATABASE Company;
USE Company;
SHOW TABLES;
CREATE TABLE employees (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname varchar(50), lastname varchar(50), department int);
INSERT INTO employees (firstname, lastname, department) VALUES ('Bob', 'Smith', '1'), ('John', 'Doe', '2'), ('Alan', 'Johnson', '1'), ('Alice', 'Smith', '2');

Example

SELECT * FROM employees;
SELECT * FROM employees WHERE lastname LIKE 'S%';
SELECT * FROM employees GROUP BY department;
SELECT * FROM employees ORDER BY lastname;

Example

CREATE TABLE departments (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(50));
INSERT INTO departments (name) VALUES ('Marketing'), ('Sales');

Example

SELECT employees.firstname, employees.lastname, departments.name FROM employees JOIN departments ON employees.department=departments.id;
SELECT * FROM employees WHERE id='4';
UPDATE employees SET department='1' WHERE id='4';
SELECT * FROM employees WHERE id='4';