.ipynb .pdf

Contents

MySQL Practice 3

Setup

1. Start by installing MySQL from here: https://dev.mysql.com/downloads/

2. Follow the instructions from here to setup MySQL on your local machine.

A sample database will be used for the demonstrations of each command. You can find the two SQL scripts DLL.sql and InsertStatements.sql by clicking the links.

After saving the files to your local machine, open your terminal and log in to MySQL console using the following command.

mysql -u root -p

Then you will be prompted to enter your password.

Now execute the following commands. We shall name our database as university.

CREATE DATABASE university;
USE university;
SOURCE <path_of_DLL.sql_file>;
SOURCE <path_of_InsertStatements.sql_file>;

MYSQL with Terminal

Now let’s start refreshing the SQL commands we have learned before.

Database Related Commands

1. See currently available databases

SHOW DATABASES;

1. Create a new database

CREATE DATABASE <database_name>;

1. Select a database to use

USE <database_name>;

1. Import SQL commands from .sql file

SOURCE <path_of_.sql_file>;

1. Delete a database

DROP DATABASE <database_name>;

Table Related Commands

1. See currently available tables in a database

SHOW TABLES;

1. Create a new table

CREATE TABLE <table_name1> (
    <col_name1> <col_type1>, 
    <col_name2> <col_type2>, 
    <col_name3> <col_type3>
    PRIMARY KEY (<col_name1>),
    FOREIGN KEY (<col_name2>) REFERENCES <table_name2>(<col_name2>)
);

Integrity Constraints in CREATE TABLE

You may need to set constraints for certain columns of a table. Following constraints can be imposed while creating a table.

• NOT NULL

• PRIMARY KEY (col_name1, col_name2,…)

• FOREIGN KEY (col_namex1, …, col_namexn) REFERENCES table_name(col_namex1, …, col_namexn)

You can include more than one primary key which will create a composite or concatenated primary key.

Example

Create the table instructor.

CREATE TABLE instructor (
    ID CHAR(5),
    name VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary NUMERIC(8,2), 
    PRIMARY KEY (ID),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name))

1. Describe columns of a table

You can view the columns of a table with details such as the type and key, using the command given below. Figure 3 shows a few examples.

DESCRIBE <table_name>;

1. Insert into a table

INSERT INTO <table_name> (<col_name1>, <col_name2>, <col_name3>, …)
    VALUES (<value1>, <value2>, <value3>, …);

If you are inserting values to all the columns of a table, then there is no need to specify the column names at the beginning.

INSERT INTO <table_name>
    VALUES (<value1>, <value2>, <value3>, …);

1. Update a table

UPDATE <table_name>
    SET <col_name1> = <value1>, <col_name2> = <value2>, ...
    WHERE <condition>;

1. Delete all contents of a table

DELETE FROM <table_name>;

1. Delete a table

DROP TABLE <table_name>;

1. SELECT

The SELECT statement is used to select data from a particular table.

SELECT <col_name1>, <col_name2>, …
     FROM <table_name>;

You can select all the contents of a table as follows.

SELECT * FROM <table_name>;

1. SELECT DISTINCT

A column of a table can often contain duplicate values. SELECT DISTINCT allows you to retrieve the distinct values.

SELECT DISTINCT <col_name1>, <col_name2>, …
     FROM <table_name>;

previous

MySQL Practice 2

next

Statistic Descriptive in SQL