Panagiotis Panagiotis - 6 months ago 18
MySQL Question

MySQL and SQL scripting for dummies

I have postponed writing SQL code for university, and now that I want to start learning it, I have no idea how to.

In C I'd define headers and begin with coding main, but in SQL classes all I have is a plain example

CREATE TABLE Sailors(
sid INTEGER,
sname VARCHAR(30) NOT NULL,
rating INTEGER DEFAULT 0,
age REAL DEFAULT 18
)


and some commands for using the table I created.

My questions are: How is a correct script supposed to look? How do I run it to create a database? (MySQL) How do I use MySQL to run scripts and where do I type commands in real time to do stuff I haven't scripted?

I just can't wrap my head around it. All tutorials I've seen use a terminal I can't find, or another I did find and I can't use because I get errors using any command (can't create file in some directory and some modules report errors so it shuts down)

Answer

The following is a very vague description of Mysql to get an idea of it:

Mysql (or SQL) is separated in 3 types of language:

DML : Data Manipulation Language
DDL : Data Definition Language
DCL : Data Control Language

Read about them to find out which kind of command belongs where. You will find out that you almost exclusively need DML and DDL to work with Data in MySQL. While DCL is mostly used to keep the database running, control user privileges , etc. Also when running code there will be only one command of your script executed at a time without a possibility to point somewhere else in your script. Loops and Cursors can be used , but have to be stored in a special form of script called stored procedure. Usually you execute your code in a sequence without a code based relation between the different commands (the relation comes from the context of the commands).

Get Data into your Database:
(Consider installing the community edition for MySQL if you have problems running MySQL correctly)

To get Data into your Database , you should import data from files into your database. The MySQL-GUIs available (Workbench, Toad, Navicat, HeidSQL...) usually provide an Import Wizard that makes it easy to import Data from all kind of Files (txt, Excel, Database Files ..). You can create an excel spreadsheet and import it into your database for example. here is a picture of the Workbench SQL Editor: https://dev.mysql.com/doc/workbench/en/images/wb-getting-started-tutorial-adding-data-movies.png Workbench (or any other GUI) will be your IDE. Getting into it will answer many of your questions.

Regarding the correct script:
A complete MySQL command is called a query.
A Query is defined by a ; at the end (default) .
A chain of MySQL commands is called a script.
Therefore, a correct script consists of correct querys.
To solve more complex problems, use stored procedures in MySQL (this should come close to your usage of the word script).

some MYSQL commands you will have to be familiar with:
select
update
insert into
delete
create table
drop table
alter table

You have a lot to read. But make sure that your Database is running and you have some data in it to test code. As you already have programming experience, you should understand this really fast with the right setup.