Create MySQL DataBase - Hsanokklis/2023-2024-Tech-journal GitHub Wiki

Basic Operations with MySQL

Here are some basic operations with the MySQL server. SQL Statement Syntax explains in detail the rich syntax and functionality of the SQL statements that are illustrated below.

Log into your MySQL Server:

  • #> mysql -u root -p

image

Showing existing databases

Use a SHOW DATABASES statement:

image

Creating a new database.

Use a CREATE DATABASE statement:

image

Creating a table inside a database.

First, pick the database in which you want to create the table with a USE statement:

image

The USE statement tells MySQL to use pets as the default database for subsequent statements. Next, create a table with a CREATE TABLE statement:

image

Data types you can use in each column are explained in Data Types. Using Primary Keys explains the concept of a primary key. What follows a # on each line is a comment, which is ignored by the mysql client; see Comment Syntax for other comment styles.

Check if the table has been created with a SHOW TABLES statement:

image

Use DESCRIBE

DESCRIBE shows information on all columns of a table:

image

Adding records into a table

Use, for example, an INSERT...VALUESLinks to an external site. statement:

image

See Literal Values for how to write string, date, and other kinds of literals in MySQL.

Retrieving records from a table.

Use a SELECT statement, and “*” to match all columns:

image

To select specific columns and rows by a certain condition using the WHERE clause:

image

Deleting a record from a table

Use a DELETE statement to delete a record from a table, specifying the criterion for deletion with the WHERE clause:

image

The blue entry was deleted

image

Adding or deleting a column from a table.

Use an ALTER TABLE...ADD statement to add a column. You can use, for example, an AFTER clause to specify the location of the new column:

mysql> ALTER TABLE cats ADD gender CHAR(1) AFTER name;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

image

  • use DESCRIBE to check the results

image

SHOW CREATE TABLE

shows a CREATE TABLE statement, which provides even more details on the table:

image

Alter Table

Use ALTER TABLE...DROP to delete a column:

image

  • Check to see that it works with DESCRIBE

image