MySQL Command Line - zhamri/MyClass-MySQL GitHub Wiki

Login

mysql -u root -p
Enter password:

After successful login:

mysql>

Show All Databases

SHOW DATABASES;

Example output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| studentdb          |
+--------------------+

Create Database

CREATE DATABASE studentdb;

Select Database

USE studentdb;

Show Tables

SHOW TABLES;

Create Table

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

Describe Table Structure

DESCRIBE students;

or

DESC students;

Insert Data

INSERT INTO students(name, email)
VALUES ('Ali', '[email protected]');

Retrieve Data

SELECT * FROM students;

Update Data

UPDATE students
SET email='[email protected]'
WHERE id=1;

Delete Data

DELETE FROM students
WHERE id=1;

Delete Table

DROP TABLE students;

Delete Database

DROP DATABASE studentdb;

Exit MySQL

EXIT;

or

QUIT;

Important SQL Data Types

Data Type	Description
INT	Integer
VARCHAR(n)	Text
DOUBLE	Decimal number
DATE	Date
BOOLEAN	True/False

Important Constraints

Constraint	Purpose
PRIMARY KEY	Unique identifier
AUTO_INCREMENT	Auto number
NOT NULL	Cannot be empty
UNIQUE	No duplicate
FOREIGN KEY	Relationship

SQL Query Order

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Filtering Data

WHERE

SELECT * FROM students
WHERE id = 1;

Sorting Data

SELECT * FROM students
ORDER BY name ASC;

Search Using LIKE

SELECT * FROM students
WHERE name LIKE 'A%';

Meaning:

• names starting with A

Aggregate Functions

COUNT

SELECT COUNT(*) FROM students;

AVG

SELECT AVG(mark) FROM students;

MAX

SELECT MAX(mark) FROM students;

Import and Export

Export Database

mysqldump -u root -p studentdb > backup.sql

Import SQL File

mysql -u root -p studentdb < backup.sql

Simple Complete Example

CREATE DATABASE schooldb;

USE schooldb;

CREATE TABLE students(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    program VARCHAR(100)
);

INSERT INTO students(name, program)
VALUES ('Ali', 'Software Engineering');

SELECT * FROM students;