SQL for Data Management

Study and consider deploy SQL for data management

SQL basics

Install and launch

Install mysql with:

sudo apt-get install mysql-server

Try launch sql as such:

mysql -u root -p

If access denied, try:

$ sudo sql
mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'NewPassword';

Then relaunch mysql with the new password.

Structure

mysqld is the background server whereas mysql is the command line client. Default port is 3306. Access remote sql server with:

mysql -h 10.0.1.99 -u root -p

Basic Operations

CREATE DATEBASE name;: create database
USE name;: switch to database
DROP DATABASE name;: delete database, remove all tables

CREATE TABLE; create table, basic syntax as:

CREATE TABLE name (column_0 type_def, ...);

SHOW TABLES; show all tables of current databse
DESC name; show columns, type def and properties of table
ALTER TABLE; alter definition a table (not records)

ALTER TABLE name ADD COLUMN column_1 type_def;
ALTER TABLE name CHANGE COLUMN column_1 column_1_new type_def_new;
ALTER TABLE name DROP COLUMN column_1;

EXIT; log out from mysql client

INSERT INTO name (columns) VALUES (values), (values) insert record into table; columns with autofill and default values can be left out;
UPDATE name columns=value, columns=value (WHERE condition)
DELETE FROM name (WHERE condition)

Operation Stack

Options for table creation
Conditions