Connecting and Managing Sessions

-- Connect using full parameters
mysql -h 127.0.0.1 -P 3306 -u root -p
-- Short form when connecting locally
mysql -u root -p
-- -h : target host address (IP)
-- -P : TCP port number
-- -u : username for authentication
-- -p : prompts for password if none provided inline

-- Discard current malformed statement before execution
\c

-- Change account password via admin utility
mysqladmin -u root -pOldPass password NewPass

-- Terminate session
exit
quit
Ctrl + d

Schema Management

Schemas correspond to directories in the file system.

-- Create new schema
CREATE SCHEMA inventory_db;
CREATE SCHEMA client_info CHARACTER SET utf8mb4;

-- List all schemas
SHOW SCHEMAS;

-- Inspect definition of a specific schema
SHOW CREATE SCHEMA inventory_db;

-- Alter schema properties
ALTER SCHEMA inventory_db CHARACTER SET utf8mb4;

-- Remove a schema permanently
DROP SCHEMA client_info;

Table Management

Tables equate to files within a schema. Always specify the active schema first.

-- Identify current default schema
SELECT DATABASE();

-- Switch to another schema context
USE inventory_db;

-- Enumerate tables inside the selected schema
SHOW TABLES;

-- Define a new table
CREATE TABLE staff (
    emp_id INT,
    full_name VARCHAR(50),
    stature FLOAT
);

-- Add a column to an existing table
ALTER TABLE staff ADD gender ENUM('M','F');

-- Create a table residing in a different schema
CREATE TABLE sales_data.record_log (entry_no INT);

-- Rename and retype a column
ALTER TABLE staff CHANGE stature height DECIMAL(5,3) NOT NULL;

-- Review table creation details
SHOW CREATE TABLE staff;

-- Display column definitions
DESCRIBE staff;
-- Shorthand:
DESC staff;

-- Modify column attributes
ALTER TABLE staff MODIFY full_name VARCHAR(70) NOT NULL;

-- Delete entire table
DROP TABLE staff;

-- Remove a single column
ALTER TABLE staff DROP COLUMN height;

Row-Level Data Manipulation

Operations apply to records within an existing table.

-- Insert complete row
INSERT INTO staff VALUES (101,'Alice Zhang',1.68,'F');
-- Omit optional INTO keyword
INSERT staff VALUES (102,'Bob Li',1.82,'M');

-- Insert partial columns
INSERT INTO staff (full_name, height) VALUES ('Cathy Wu',1.73);

-- Bulk insert multiple rows
INSERT INTO staff (full_name, height) VALUES 
('David Chen',1.75),
('Eva Sun',1.66);

INSERT INTO staff VALUES 
(105,'Frank Zhou',1.79,'M'),
(106,'Grace Lin',1.63,'F');

-- Retrieve all fields from table
SELECT * FROM staff;
-- Prefer explicit columns for large datasets
SELECT full_name FROM staff;

-- Update records conditionally
UPDATE staff SET gender = 'F' WHERE height > 1.70;

-- Delete rows matching condition
DELETE FROM staff WHERE emp_id > 104;

-- Purge all rows without dropping table
DELETE FROM staff;

GUI-Based Workflow with Navicat

• Create Schema: Use the graphical interface to define a new schema, specifying character set and collation.
• Create Table: Initiate table design, assign columns with types and constraints, then save.
• Modify Structure: Open table designer to adjust columns, indexes, or keys.
• Insert Records: Navigate to data view, enput values directly into grid cells, and commit changes.