SQL is the name of the language (or set of code words) that interfaces with databases. SQL stands for Structured Query Language. The word database is more or less self-explanatory. It's a computer software program (app) that is able to hold and manage a large amount of information such as names, addresses, phone numbers, products, employees, etc. Databases are most commonly used by companies that need to store, manage and keep track of large amounts of information. There are many different database programs but we will be learning on the database program called MySQL, which is probably the most popular and commonly used database used today. MySQL is maintained by Oracle, the same company that maintains Java. The database program MySQL is not the same thing as SQL, the language, or set of code words used to access the database.
Databases consist of tables. Tables resemble a spreadsheet with rows and columns and is what the database information is stored on. When you create a database, you will give it a name and you will give a different name for each table you create in the database. For example you could have a table with all of the employee names and a different table of the different products a company makes. A database can have more than one table.
One of the columns in each table needs to be what they call the primary key which you will set with SQL when creating your database table. Each entry of the primary key has to be unique, different than any other entry in the column. For example if you have a column with 2 entries with the same name, such as John Smith and then another John Smith, the primary key will differentiate between the 2 entries. An example of a primary key could be a column that contains an id number, a clock number or an account number.
The primary key column can use what they call a 'surrogate key' or it can use what they call a 'natural key'. A surrogate key would have numbers or letters that have no representation of the item in the real world, for example an account number. A natural key has identifiers that can be related in the real world such as social security numbers.
There can also be a column in the table that is what they call a foreign key. A foreign key column has entries that corresponds to entries of the primary key from another table in the database.
Sometimes a primary key consists of 2 columns in order to uniquely identify an item. This is referred to as a composite key.
SQL (Structured Query Language) is the 'language' used to access MySQL databases (and others). Databases made by different companies may use slightly different variations of SQL. SQL commands are tradionally written in all caps, though not necessary. SQL is considered a hybrid of 4 types of languages:
Data Query Language (DQL)
Data Definition Language (DDL)
Data Control Language (DCL)
Data Manipulation Language (DML)
Getting information from or changing information on a database is done through queries. Queries are statements written in SQL, the language that the database can understand
This tutorial is not going to go over how to install mysql on your computer. It's a free program, download it and install it according to the site's instructions. Here's a link to a site that shows detailed information on how to install mysql. Link
Also this tutorial is about writing SQL instructions from the command line prompt to directly access the database. It is assuming you know how to open a command prompt window.
To make sure mysql is running on your computer open a command prompt window and type in:
services.msc
This will bring up a list of services running on your computer. Go down the list and find mysql. Right click on it, and start it if it's not already running. If you can't find it then it's probably not installed.
Depending on how you installed MySQL you may have a couple different ways of getting to the command line prompt. On Windows you should be able to click on the Start menu button and scroll down to MySQL. Click on MySQL 8.0 Command Line Client in the separate menu and that should bring you up to the 'mysql>' prompt. It will request your password.
Another way is to open a command prompt and change directory to:
C:/program files/mysql/mysql server 8.0/bin and type in:
mysql -u root -p
This will give you a password prompt. The password I'm using is password.
Type in your password, hit enter and you should now have a prompt that looks like:
mysql>
At the prompt, type in SHOW DATABASES; <Enter>
(NOTE: DON'T FORGET THE SEMI-COLON!) This will show you what default databases are installed. The semi-colon is required on most sql commands...
To exit the program just type QUIT or \q.
If you're using xampp, you can get to the directory from the command line with C:/xampp/mysql/bin You only need to type in mysql -u (enter) to start mysql from the command line because xampp by default leaves the password blank. It will also probably show MariaDB> which is what xampp uses but MariaDB uses the same commands as MySQL.
To create a new database type in the following SQL command:
CREATE DATABASE db1; (where db1 is the random name you are giving it, could be anything)
DO NOT FORGET THE SEMICOLON! ALMOST ALL SQL COMMANDS END IN A SEMICOLON.
Hit Enter (you will hit enter after you type the semi-colon on most commands)
Database entries need to be identified if they are going to be text, numbers, date, etc. There are many different kinds of data types but the 6 main ones we will be using are:
INT(size) (integer, size being a number denoting how big of an interger)
DECIMAL(M,N) (M being total number of digits and N being number of digits after the decimal)
VARCHAR(10) (text, number representing how long the text is )
BLOB (binary large object like images)
DATE
TIMESTAMP (used for recording when an item got entered or stored)
So, to create a table named student which has 3 columns: student_id, name, major; with the student_id being the primary key, the syntax would be:
(First, tell it which database you are using, for example, db2...)
USE db2; (enter, then type...)
CREATE TABLE student(
student_id INT(2) PRIMARY KEY,
name VARCHAR(20),
major VARCHAR(20)
);
or
CREATE TABLE student(
student_id INT(2),
name VARCHAR(20),
major VARCHAR(20),
PRIMARY KEY(student_id)
);
You can type in a line and hit the enter key to go to the next line. The code will not execute until it reaches a semicolon which will be in the last line after the parentheses.
Again, using our student table as an example:
INSERT INTO student VALUES(1, 'Jack', 'Biology');
This line will insert data into the student table, to view what you have so far type in:
SELECT * FROM student;
In order to type in a partial data entry where one of the values is not known:
INSERT INTO student(student_id, name) VALUES(1, 'Jack');
Using student as an example table name:
DESCRIBE student; (self explanatory)
DROP TABLE student; (deletes table)
ALTER TABLE student ADD gpa DECIMAL(3,2); (adds another column named gpa to the table)
ALTER TABLE student DROP COLUMN gpa; (deletes a column in the table)
Here's a few notes from the MySQL manual...
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> SELECT SIN(PI()/4), (4+1)*5;
mysql> SELECT VERSION(); SELECT NOW();
Queries can be written over multiple lines...
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
\c = cancel what you're typing and return to prompt but make sure all quotes are completed
(see next section)
Prompt Meaning
mysql> Ready for new query
-> Waiting for next line of multiple-line query
'> Waiting for completion of a string that began with a single quote (')
"> Waiting for completion of a string that began with a double quote (")
`> Waiting for completion of an identifier that began with a backtick (`)
/*> Waiting for completion of a comment that began with /*
UPDATE student
SET major = 'Bio'
WHERE major = 'Biology';
UPDATE student
SET major = 'Comp Sci'
WHERE student_id = 4;
UPDATE student
SET major = 'Biochemistry'
WHERE major = 'Bio' OR major = 'Chemistry';
UPDATE student
SET name = 'Tom' , major = 'undecided'
WHERE student_id = 1;
UPDATE student
SET major = 'undecided'
(no WHERE, all majors will be undecided)
DELETE FROM student
WHERE student_id = 5;
DELETE FROM student
WHERE name = 'Tom' AND major = 'undecided';
DELETE FROM student; (deletes all entries in table)
SELECT * FROM student; (selects and displays everything from table 'student')
SELECT name FROM student; (selects everything from the column 'name', table student)
SELECT name
FROM student; (same as above only split on two lines)
SELECT name, major
FROM student;(selects and displays everything from the columns 'name' and 'major')
SELECT student.name, student.major
FROM student;(same thing only prepends the table name to the column)