Master MySQL: Installation, Configuration, and Advanced Query Techniques

What Is a Database?

A database is a structured repository that stores, organizes, and manages data. It ranges from simple tables to large‑scale systems that support diverse data‑management needs.

MySQL Overview

MySQL is an open‑source relational database management system (RDBMS) that uses SQL for data manipulation. It is widely used in web applications.

Installation

Install the MySQL server (service daemon).

Install the MySQL client.

Start the MySQL service (e.g., mysql.server start on Linux/macOS or the Windows service manager).

Download URL (for reference): http://dev.mysql.com/downloads/mysql/

Connecting to MySQL

mysql -u user -p
# Example: mysql -u root -p

Common Connection Error

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2). This usually means the MySQL server daemon is not running.

Exiting a Session

QUIT   # or press Ctrl+D

Database Operations

Show databases: SHOW DATABASES; Typical system databases:

mysql               # system metadata
information_schema # internal schema information
test               # user testing data

Create a database (UTF‑8 example):

CREATE DATABASE db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# For GBK charset:
CREATE DATABASE db1 CHARACTER SET gbk COLLATE gbk_chinese_ci;

Select a database: USE db1; Show tables in the current database:

SHOW TABLES;

User Management

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
DROP USER 'username'@'host';
RENAME USER 'oldname'@'host' TO 'newname'@'host';
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

Permission Management

Key privilege categories (examples):

ALL PRIVILEGES – everything except GRANT OPTION

SELECT – read‑only access

INSERT, UPDATE, DELETE – data‑modification rights

CREATE, ALTER, DROP – schema‑modification rights

GRANT OPTION – ability to grant privileges to others

View a user's grants: SHOW GRANTS FOR 'username'@'host'; Grant privileges:

GRANT SELECT, INSERT ON db1.* TO 'username'@'host';

Revoke privileges:

REVOKE SELECT ON db1.tb1 FROM 'username'@'host';

Table Operations

Create a table (basic example):

CREATE TABLE tab1 (
  nid INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT 'zhangyanlin',
  email VARCHAR(255),
  PRIMARY KEY (nid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Important column attributes:

DEFAULT – value used when none is supplied.

AUTO_INCREMENT – automatically increments; must be indexed (usually primary key).

PRIMARY KEY – unique identifier, cannot be NULL.

Delete a table: DROP TABLE tab1; Truncate a table (remove all rows): TRUNCATE TABLE tab1; Alter table examples:

ALTER TABLE tab1 ADD column_name VARCHAR(100);
ALTER TABLE tab1 DROP COLUMN column_name;
ALTER TABLE tab1 MODIFY column_name INT;
ALTER TABLE tab1 CHANGE old_name new_name VARCHAR(50);
ALTER TABLE tab1 ADD PRIMARY KEY (column_name);
ALTER TABLE tab1 DROP PRIMARY KEY;
ALTER TABLE tab1 ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent_table(parent_col);
ALTER TABLE tab1 DROP FOREIGN KEY fk_name;

Data Manipulation (CRUD)

Insert rows:

INSERT INTO tab1(name,email) VALUES('zhangyanlin','[email protected]');
INSERT INTO tab1(name,email) VALUES('a','[email protected]'),('b','[email protected]');

Delete rows:

DELETE FROM tab1;  # remove all rows
DELETE FROM tab1 WHERE nid=1 AND name='zhangyanlin';

Update rows: UPDATE tab1 SET name='zhangyanlin' WHERE nid>1; Select queries (basic and advanced):

SELECT * FROM tab1;
SELECT * FROM tab1 WHERE nid>1;
SELECT nid, name AS alias FROM tab1 WHERE nid>1;
SELECT * FROM tab1 WHERE name LIKE 'zhang%';   # prefix match
SELECT * FROM tab1 WHERE name LIKE 'zhang_';   # single‑character wildcard
SELECT * FROM tab1 LIMIT 5;                     # first 5 rows
SELECT * FROM tab1 LIMIT 4,5;                  # 5 rows starting from offset 4
SELECT * FROM tab1 ORDER BY col ASC;
SELECT * FROM tab1 ORDER BY col DESC, col2 ASC;
SELECT col FROM tab1 GROUP BY col;
SELECT col, COUNT(*), SUM(score), MAX(score), MIN(score) FROM tab1 GROUP BY col;
SELECT col FROM tab1 GROUP BY col HAVING MAX(id)>10;

Data Types

Numeric types:

BIT[(M)] – binary bits, M 1‑64.

TINYINT[(M)] [UNSIGNED] – -128..127 or 0..255.

INT[(M)] [UNSIGNED] – -2,147,483,648..2,147,483,647 or 0..4,294,967,295.

BIGINT[(M)] [UNSIGNED] – -9.22e18..9.22e18 or 0..1.84e19.

DECIMAL[(M,D)] – exact fixed‑point numbers (M≤65, D≤30).

FLOAT[(M,D)], DOUBLE[(M,D)] – approximate floating‑point numbers.

String types:

CHAR(M) – fixed‑length, pads with spaces.

VARCHAR(M) – variable‑length up to 255 characters.

TEXT, MEDIUMTEXT, LONGTEXT – large variable‑length text.

ENUM – list of predefined values (max 65,535 distinct).

SET – up to 64 distinct members.

Date and time types:

DATE – YYYY‑MM‑DD (1000‑01‑01 to 9999‑12‑31).

TIME – HH:MM:SS (‑838:59:59 to 838:59:59).

YEAR – four‑digit year (1901‑2155).

DATETIME – combined date and time.

TIMESTAMP – seconds since 1970‑01‑01 (up to 2037).

These commands and concepts provide a solid foundation for using MySQL in development and operations.