mysql notes

some of these notes may have been created from other online sources.

they are just my home schooling notes.

 

The rule of First Normal Form states that all repeating columns should be eliminated and put into separate
tables.
The rule of Second Normal Form states that all partial dependencies must be eliminated and separated into
their own tables.A partial dependency is a term to describe data that doesn’t rely on the table key to
uniquely identify it.

Showing table information:

SHOW COLUMNS FROM Customers;  //customers is the table in the database

or you can use this:

DESCRIBE Customers;           //customers is the table in the database

the output is the same for both commands

create a database:
CREATE DATABASE Temp;

selecting the database:
use myDatabase;

showing tables:
show tables from mydatabase;

creating tables:

CREATE TABLE tablename(columnsname data type);

example:
CREATE TABLE Test_Table
(Test_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Test_Name VARCHAR(30),
Test_Date DATETIME,
Test_Giver VARCHAR(30));

creating temporary tables:
CREATE TEMPORARY TABLE tablename(columnsname data type);

altering existing tables:

ALTER TABLE Customers CHANGE First_Name FirstName VARCHAR(20);  //changes column First_Name to FirstName
you can also change the columb type: example (varchar(30) to varchar(50) )

ALTER TABLE Customers RENAME Customer_Table; //changes the tablename

create copies of a table:
CREATE TABLE tablename SELECT * FROM Customers; //copies all data into tablename

deleting tables:
DROP tablename;

dropping columns:
ALTER TABLE tablename DROP columnname;

adding columns:
ALTER TABLE tablename ADD columnname data type; //example:ALTER TABLE Customer ADD Last_Name VARCHAR(30);

By default, MySQL creates an index for you if you declare a column as a primary key.

creating a primary key:
ALTER TABLE Customers ADD PRIMARY KEY (Customer_ID);  //makes Customer_ID the primary key

adding an index:
ALTER TABLE Customers ADD INDEX (IDX_Last_Name);

52

removing an index:

DROP INDEX indexname ON tablename;

or

ALTER TABLE tablename DROP INDEX indexname;

removing a primary key:

ALTER TABLE tablename DROP PRIMARY KEY;

data types:
FLOAT(4,2)  //float number holds number with 4 before the decimal point  and 2 after the decimal point (1234.12)
FLOAT(5,3) //float number holds number with 5 before the decimal point  and 3 after the decimal point (12345.123)

TINYINT           //a signed number holds less than an unsigned number as it has negative number support
UNSIGNED TINYINT  //an unsigned number will hold more as it doesnt need negative number support

Use DECIMAL for currency to retain accuracy.

The AUTO_INCREMENT, UNSIGNED, and ZEROFILL modifiers can only be used with numeric data
types.

example:
CREATE TABLE Test (Auto_Test int NOT NULL AUTO_INCREMENT); //auto_test is the column name

There can be only one AUTO_INCREMENT column in a table

The ENUM Data type:
only one value can be selected at a time
CREATE TABLE Test(
Return ENUM(‘Y’,’N’) DEFAULT ‘N’,   //creates an enum with y and n possible values
Size ENUM(‘S’,’M’,’L’,’XL’,’XXL’),  //creates an enum
Color ENUM(‘Black’,’Red’,’White’)   //creates an enum
)

The SET datatype:
A SET type can contain up to 64 items
more than one value can be selected at a time

CREATE Table Test(
Advertiser SET(‘Web Page’,’Television’,’Newspaper’)
)
INSERT INTO Test (Advertiser) values(‘Web Page, Television’)  //selects 2 values into the set

SELECT * FROM Test WHERE Advertiser = 1  //set and enum types are stored as numbers starting from 0

SELECT Advertiser, Advertiser +0 FROM Test  //shows what values each has

——————————————————————————————————
SQL understands the following verbs: CREATE, DROP, ALTER, DELETE, INSERT, UPDATE, and SELECT. SQL
also understands the following words: SET, FROM, INTO, WHERE, JOIN, LEFT JOIN, CROSS JOIN,
RIGHT JOIN, FULL JOIN, INNER JOIN, ON, ORDER BY, and GROUP BY

SELECTING WITH LIMITS SET:
SELECT * FROM Customers LIMIT 10  //limits query to 10 records returned

Leave a Reply