Gallery
Share
Explore

SQL Instructional Manual


megaphone

You can import CSV files into tables using MySQL Workbench. Here's a step-by-step guide on how to do it:

Importing CSV Files into Tables using MySQL Workbench:

Open MySQL Workbench and connect to your database.
In the Navigator pane on the left, right-click on the schema (database) where you want to import the table, and select Set as Default Schema.
Click on the Server tab in the main menu and select Data Import.
In the Data Import/Restore view, select Import from Self-Contained File. Then, click on the ellipsis button (...) to navigate and select your CSV file.
Under Default Target Schema, select the schema (database) where you want to import the table.
Under Format of Imported Data, choose CSV.
In the Tables to Import section, you'll see the tables detected in the CSV file. Select the tables you want to import.
Click on the Start Import button at the bottom right to begin the import process.
Once the import is complete, you should see the imported table in the selected schema, and you can start querying it.
Note: Before importing, ensure that the CSV file is formatted correctly and matches the structure of the table you're importing into (or create a new table structure based on the CSV). It's also a good idea to back up your database before performing any import operations to prevent any unintended data loss or corruption.
If you're creating a new table from the CSV, you might need to adjust data types, set primary keys, or make other modifications after the import to ensure the table structure is optimal for your use case.

Introduction: Programming Relational Databases with SQL

Structured Query Language (SQL) is a domain-specific language used to manage and query data in relational databases.
step 1:
Start MySql Server Start your Mysql Workbench
step 2: find a database to connect to SHOW DATABASES
use <databasename>
** then you can start doing SQL stuff

Table of Contents

Basics
Show Databases
DROP Database Databasename
Show Tables
DROP Table Tablename
Data Query Language (DQL)
SELECT
JOINs
Subqueries
SORT
GROUP BY
Aggregation Functions
Data Manipulation Language (DML)
CRUD Operations
Data Control Language (DCL)
GRANT
REVOKE
⁠

1. Basics

Show Databases

To view all databases:
sqlCopy code
SHOW DATABASES;

Show Tables

To view all tables in a specific database:
sqlCopy code
USE your_database_name;
SHOW TABLES;

⁠

megaphone

To list the fields (columns) of a table in MySQL, you can query the INFORMATION_SCHEMA.COLUMNS table. Here's how you can do it:

sqlCopy code
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

Replace your_database_name with the name of your database and your_table_name with the name of your table.
This SQL query will return a list of column names for the specified table in the specified database.
If you're using MySQL Workbench, you can also see the fields of a table by navigating to the table in the Navigator pane and clicking on it. The columns (fields) of the table will be displayed in the main pane.

2. Data Query Language (DQL)

SELECT

To retrieve all columns from a table:
sqlCopy code
SELECT * FROM table_name;

JOINs

INNER JOIN

Returns rows when there is a match in both tables.
sqlCopy code
SELECT A.column1, B.column2
FROM tableA A
INNER JOIN tableB B ON A.common_column = B.common_column;

LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table, and the matched rows from the right table.
sqlCopy code
SELECT A.column1, B.column2
FROM tableA A
LEFT JOIN tableB B ON A.common_column = B.common_column;

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table, and the matched rows from the left table.
sqlCopy code
SELECT A.column1, B.column2
FROM tableA A
RIGHT JOIN tableB B ON A.common_column = B.common_column;

FULL JOIN (or FULL OUTER JOIN)

Returns rows when there is a match in one of the tables.
sqlCopy code
SELECT A.column1, B.column2
FROM tableA A
FULL OUTER JOIN tableB B ON A.common_column = B.common_column;

Subqueries

Correlated Subqueries

A correlated subquery is a subquery that uses values from the outer query.
sqlCopy code
SELECT column1, column2
FROM tableA A
WHERE column1 = (SELECT column1 FROM tableB B WHERE B.column2 = A.column2);

SORT

To sort results by a specific column:
sqlCopy code
SELECT column1, column2
FROM table_name
ORDER BY column1 ASC; -- or DESC for descending

GROUP BY

Groups rows that have the same values in specified columns.
sqlCopy code
SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1;

Aggregation Functions

COUNT

sqlCopy code
SELECT COUNT(column_name)
FROM table_name;

SUM

sqlCopy code
SELECT SUM(column_name)
FROM table_name;

AVG

sqlCopy code
SELECT AVG(column_name)
FROM table_name;

MAX

sqlCopy code
SELECT MAX(column_name)
FROM table_name;

MIN

sqlCopy code
SELECT MIN(column_name)
FROM table_name;

⁠

3. Data Manipulation Language (DML)

CRUD Operations

CREATE

Inserts new data into a table.
sqlCopy code
INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

READ

Queries data from a table.
sqlCopy code
SELECT column1, column2
FROM table_name;

UPDATE

Modifies existing data in a table.
sqlCopy code
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE

Removes data from a table.
sqlCopy code
DELETE FROM table_name
WHERE condition;

⁠

4. Data Control Language (DCL)

GRANT

Gives user-specific privileges to a database.
sqlCopy code
GRANT privilege_name
ON object_name
TO user_name;

REVOKE

Removes user-specific privileges.
sqlCopy code
REVOKE privilege_name
ON object_name
FROM user_name;

⁠
This is a basic overview of SQL commands and their functionalities. Depending on the database system you're using (e.g., MySQL, PostgreSQL, SQL Server), there might be slight variations or additional features. Always refer to the official documentation of the specific database system for comprehensive details.
⁠
image.jpeg failed to upload
⁠
⁠
what other sql queries can I include
ChatGPT
Certainly! SQL is a vast language with many functionalities. Here are some additional SQL queries and concepts you might consider including in your instructional manual:
⁠

Data Definition Language (DDL)

CREATE TABLE

Creates a new table.
sqlCopy code
CREATE TABLE table_name (
column1 datatype1,
column2 datatype2,
...
);

ALTER TABLE

Modifies an existing table.
sqlCopy code
ALTER TABLE table_name
ADD column_name datatype;

DROP TABLE

Deletes an existing table.
sqlCopy code
DROP TABLE table_name;

⁠

Advanced Query Techniques

DISTINCT

Eliminates duplicate rows from the result.
sqlCopy code
SELECT DISTINCT column_name
FROM table_name;

LIMIT

Limits the number of rows returned.
sqlCopy code
SELECT column_name
FROM table_name
LIMIT number;

UNION

Combines the result of two or more SELECT statements.
sqlCopy code
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

CASE

Adds conditions within the query.
sqlCopy code
SELECT column1,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result3
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.