MySQL Basic Management, Database Operations, and Table Alteration Tutorial

This article provides a step‑by‑step guide for basic MySQL administration, including setting the root password, connecting to the server, listing built‑in databases, switching databases, creating and dropping databases, and inspecting tables.

SET PASSWORD FOR 'root'@'localhost'=PASSWORD('1234567');

mysql -u root -p -h 127.0.0.1 -P 3306

Built‑in databases: information_schema, mysql, performance_schema, test.

(2) Switch/Use a specified database

Switch to the test database:

USE test;

Confirm the current database:

SELECT DATABASE();

Check the current user:

SELECT USER();

(3) Create a new database

Create a database named mydb and verify:

CREATE DATABASE mydb;

SHOW DATABASES;

After creating a database, MySQL creates a same‑named directory under /var/lib/mysql/ which can be listed from the command line.

(4) Delete a database

DROP DATABASE newdb;

(5) List tables in a specified database

Show tables in the test database:

USE test;

SHOW TABLES;

(6) View the structure of a specific table

Describe the columns_priv table in vertical format:

DESCRIBE columns_priv\G

Or in tabular format:

DESCRIBE columns_priv;

(7) Delete a specified table

Drop the pwlist table and confirm:

DROP TABLE pwlist;

SHOW TABLES;

(8) Create a student table in the test database

To store Chinese characters, set the character set to UTF‑8 when creating the table:

CREATE TABLE test.student(

学号 CHAR(9) NOT NULL,

姓名 VARCHAR(4) NOT NULL,

性别 ENUM('男','女') NOT NULL,

手机号 CHAR(11) DEFAULT '',

通信地址 VARCHAR(64),

PRIMARY KEY(学号)

) DEFAULT CHARSET=utf8;

To change MySQL server’s default character set, edit /etc/my.cnf and add character_set_server=utf8 under the [mysqld] section, then restart the service and verify:

service mysql restart

SHOW VARIABLES LIKE 'character%';

II: Practice Various Time Functions

Use now() to get current date and time:

SELECT now();

Use sysdate() to get system date and time:

SELECT sysdate();

Use curdate() to get the current date without time:

SELECT curdate();

Use curtime() to get the current time without date:

SELECT curtime();

Extract year, month, and day from the current timestamp:

SELECT year(now()), month(now()), day(now());

Extract month and day from the system date:

SELECT month(sysdate()), day(sysdate());

Extract the time part from the system date:

SELECT time(sysdate());

III: Table Structure Adjustments

(1) Add a column:

ALTER TABLE tea6 ADD address VARCHAR(48);

(2) Add a column after an existing column:

ALTER TABLE tea6 ADD gender ENUM('boy','girl') AFTER age;

(3) Rename a column and change its definition:

ALTER TABLE tea6 CHANGE gender sex ENUM('boy','girl') NOT NULL;

(4) Drop a column:

ALTER TABLE tea6 DROP sex;