Records Fundamentals
Introduction
|
|
Creating values for a database, also referred to as adding
values, also referred to as data entry, consists of inserting values into a
table. The DDL command to perform data entry is INSERT combined with VALUES. The primary formula to follow is:
|
INSERT table-name VALUES(column1-value, column2-value, column_n-value);
You can use the INTO keyword between the INSERT keyword and the table-name. The formula to follow is:
INSERT INTO table-name VALUES(column1-value, column2-value, column_n-value)
The table-name must be a valid name of an existing table in the database you are using. The values of the columns must be included in the parentheses of VALUES.
To make the code easy to read, you can distribute the statement on different lines.
Data Entry in PHP
To perform data entry using a scripting language like PHP,
create the INSERT statement and pass it to it to either the mysqli_query() function
or the mysqli::query() method. Here is an example:
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
$link->query("INSERT table-name(column1-value, column2-value, column_n-value)
VALUES(statement);");
$link->close();
?>
Data Entry For Text
As mentioned already, a text-based column takes a letter, a
word, or any type of text as value. To specify the value of a VARCHAR-based column, include the value in quotes. Here are examples:
<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
mysqli_connect_errno();
$link->query("CREATE TABLE GradesScales
(
LetterGrade VARCHAR(1),
Descriptor VARCHAR(25)
);");
$link->query("INSERT INTO GradesScales
VALUES('A', 'Excellent');");
$link->close();
?>
</body>
</html>
Techniques of Performing Data Entry
Adjacent Data Entry
The most common technique of performing data entry requires that you know the
order of the columns of the table. In this case, enter the value of
each column in its correct position. During data entry, if you don't have a value for a number-based column, you should type 0 as its value. For a
string field whose data you don't have and cannot provide, type two
single-quotes '' to specify an empty field. Here are examples:
<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
mysqli_connect_errno();
$link->query("CREATE TABLE WorkSheets
(
WorkNumber VARCHAR(10),
EmployeeNumber VARCHAR(20),
Monday VARCHAR(4),
Tuesday VARCHAR(4),
Wednesday VARCHAR(4),
Thursday VARCHAR(4),
Friday VARCHAR(4),
Saturday VARCHAR(4),
Sunday VARCHAR(4)
);");
$link->query("INSERT TimeSheets
VALUES('1001', '422581', '8', '8', '8', '8', '8', '0', '0');");
$link->close();
?>
</body>
</html>
Random Data Entry
Instead of using the adjacent data entry that requires that you know the position of each column of the table, you can provide the values of columns in an order of your choice. The formula to follow is:
INSERT [ INTO ] table-name(column-a, column-b, column-n)
VALUES(column-a-value, column-b-value, column-n-value)
This time, add a set of parentheses after the name of the table. In those parentheses, provide
a list of the desired columns, in any order of your choise, whose values you want to create. In the parentheses of VALUES, provide the values of columns in the order they are listed after the name of the table. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
mysqli_connect_errno();
$link->query("INSERT WorkSheets(Wednesday, Monday, Friday,
EmployeeNumber,
Thursday, TimeSheetNumber )
VALUES('6', '8.5', '7', '974288', '8', '1002');");
$link->close();
?>
</body>
</html>
Creating Multiple Records
Instead of one, you can add various records with one call to INSERT. If you are adding a value to each column of the table, omit the parentheses after the name of the table. In the parentheses of VALUES, include a value
for each column. To add another record, type a comma after the closing
parenthesis, followed by new parentheses. In the new parentheses, create the values of the new record. Do this as many times as you need to add records. Here is an
example:
CREATE TABLE Videos
(
Title VARCHAR(50),
Director VarChar(50),
Rating varchar(10),
YearReleased VARCHAR(4)
);
INSERT INTO Videos
VALUES('Her Alibi', 'Bruce Beresford', 'PG-13', 1998),
('Memoirs of a Geisha', 'Rob Marshall', 'PG-13', 2006),
('Two for the Money', 'D.J. Caruso', 'R', 2008);
If you want to follow your own order of columns, on the right side of the name of the table, include a list of columns in parentheses. Then, when giving the values, for each record, follow the value in the order their columns are listed in the parentheses of the name of the table.
Data Entry Based on Type
Characters and Strings
To specify the value of a CHAR or a VARCHAR-based column, include the value in quotes. Here are examples:
<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
mysqli_connect_errno();
$link->query("CREATE TABLE GradesScales
(
LetterGrade CHAR,
Descriptor VARCHAR(25)
);");
$link->query("INSERT INTO GradesScales(LetterGrade, Descriptor)
VALUES('A', 'Excellent'),
('B', 'Good'),
('C', 'Satisfactory'),
('D', 'Acceptable'),
('F', 'Failed'),
('W', 'Withdrawn');");
$link->close();
?>
</body>
</html>
The Bit Type
When performing data entry for a BIT-based column, you can assign any type of number to the column. A value of 0 would be applied "as-is". Any other number would be converted to 1. Here are examples:
<!DOCTYPE html>
<html>
<head>
<title>Metro System - Stations</title>
</head>
<body>
<h2>Metro System - Stations</h2>
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
mysqli_connect_errno();
$link->query("CREATE TABLE MetroStations
(
StationName VARCHAR(40),
ParkingAvailability BIT
);");
$link->query("INSERT INTO MetroStations(StationName, ParkingAvailability)
VALUES('New Carrollton', 1),
('Anacostia', 241),
('Addison Road-Seat Pleasant', 100),
('Archives-Navy Memorial-Penn Quarter', 0),
('Capitol Heights', -1),
('Arlington Cemetery', 0),
('Greenbelt', 33188),
('Huntington', -911),
('Benning Road', 0),
('Bethesda', 0),
('Shady Grove', -100);");
$link->close();
?>
</body>
</html>
Integers
When specifying the value of a number-based column, simply provide its value. Here are examples
for two columns that use small numbers:
<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
mysqli_connect_errno();
$link->query("CREATE TABLE GradesScales1
(
LetterGrade CHAR(2),
MinPercent TINYINT UNSIGNED,
MaxPercent TINYINT UNSIGNED,
Descriptor VARCHAR(25)
);");
$link->query("INSERT INTO GradesScales1(LetterGrade,
MinPercent,
MaxPercent,
Descriptor)
VALUES('A', 95, 100, 'Excellent'),
('A-', 90, 94, 'Excellent'),
('B+', 85, 89, 'Good'),
('B', 80, 84, 'Good'),
('B-', 75, 79, 'Good'),
('C+', 70, 74, 'Satisfactory'),
('C', 65, 69, 'Satisfactory'),
('C-', 60, 64, 'Satisfactory'),
('D+', 55, 59, 'Satisfactory'),
('D', 50, 54, 'Satisfactory'),
('F', 0, 49, 'Unsatisfactory'),
('W', 0, 0, 'Withdrawn');");
$link->close();
?>
</body>
</html>
Small Integers
If a column needs small integers in the range of -32,768 to 32,767 (2 bytes), set its data type as SMALLINT or smallint. To indicate that the values of the column must always be positive, add UNSIGNED to the data type.
Medium Integers
If you want a column to carry signed numbers between -8388608 and 8388607 or unsigned numbers between 0 and 16777215 (3 bytes), set its data type as MEDIUMINT. To indicate that the values of the column must always be positive, add UNSIGNED to the data type.
Integers
If the values of a column would range from -2,147,483,648 to 2,147,483,647, (4 bytes) set its data type to INT or INTEGER (or int or integer. To indicate that the values of the column must always be positive, add UNSIGNED.
Long Integers
If you want a column that can hold large numbers (8 bytes) between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807, set its type as BIGINT or bigint
Binary Values
The binary type is used for a column that would hold natural numbers in series of bits. Each bit is treated, checked or used individually. When creating a column for such values, apply the BIT data type that uses parentheses. In the parentheses, type the number of bits necessary to store the value. The number is between 1 and 64.
Decimal Numbers
Introduction
Remember that the NUMERIC (or numeric) and the DECIMAL (or decimal) types let you create a column for values that use a fixed precision. To provide their values, simply type them. Here are examples:
CREATE TABLE Distances
(
FromCity varchar(40),
ToCity varchar(40),
Distance decimal
);
INSERT INTO Distances VALUES('Baltimore, MD', 'Richmond, VA', 130);
INSERT INTO Distances VALUES('New York, NY', 'Las Vegas, NV', 2232);
This indicates that you can use the decimal types where you would use an integer. In the same way, to specify the value of a floating-point number-based column, simply provide it. Here are examples:
<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
mysqli_connect_errno();
$link->query("CREATE TABLE GradesScales24
(
LetterGrade CHAR(2),
MinRange float UNSIGNED,
MaxRange float UNSIGNED,
MinPercent TINYINT UNSIGNED,
MaxPercent TINYINT UNSIGNED,
Descriptor VARCHAR(25)
);");
$link->query("INSERT INTO GradesScales24(LetterGrade,
MinRange,
MaxRange,
MinPercent,
MaxPercent,
Descriptor)
VALUES('A', 4.00, 0.00, 95, 100, 'Excellent'),
('A-', 3.67, 3.99, 90, 94, 'Excellent'),
('B+', 3.33, 3.66, 85, 89, 'Good'),
('B', 3.00, 3.32, 80, 84, 'Good'),
('B-', 2.67, 2.99, 75, 79, 'Good'),
('C+', 2.33, 2.66, 70, 74, 'Satisfactory'),
('C', 2.00, 2.32, 65, 69, 'Satisfactory'),
('C-', 1.67, 1.99, 60, 64, 'Satisfactory'),
('D+', 1.33, 1.66, 55, 59, 'Satisfactory'),
('D', 1.99, 1.32, 50, 54, 'Satisfactory'),
('F', 1.00, 1.32, 0, 49, 'Unsatisfactory');");
$link->close();
?>
</body>
</html>
The Precision of a Decimal Number
A precision is the number of digits used to display a numeric value. If the data type is specified as an integer (the int and its variants), the precision is fixed by the database and you can just accept the value. For a decimal number type (real, float, double, decimal or numeric), you have the option of specifying or not indicating a precision. If you don't specify a precision, the decimal and numeric data types are treated as integers. For example, 3.33 would be converted to 3 and 3.67 would be converted to 4. Consider the following examples:
<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>
<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");
mysqli_connect_errno();
$link->query("CREATE TABLE GradesScales25
(
LetterGrade CHAR(2),
MinRange decimal UNSIGNED,
MaxRange decimal UNSIGNED,
MinPercent TINYINT UNSIGNED,
MaxPercent TINYINT UNSIGNED,
Descriptor VARCHAR(25)
);");
$link->query("INSERT INTO GradesScales25(LetterGrade,
MinRange,
MaxRange,
MinPercent,
MaxPercent,
Descriptor)
VALUES('A', 4.00, 0.00, 95, 100, 'Excellent'),
('A-', 3.67, 3.99, 90, 94, 'Excellent'),
('B+', 3.33, 3.66, 85, 89, 'Good'),
('B', 3.00, 3.32, 80, 84, 'Good'),
('B-', 2.67, 2.99, 75, 79, 'Good'),
('C+', 2.33, 2.66, 70, 74, 'Satisfactory'),
('C', 2.00, 2.32, 65, 69, 'Satisfactory'),
('C-', 1.67, 1.99, 60, 64, 'Satisfactory'),
('D+', 1.33, 1.66, 55, 59, 'Satisfactory'),
('D', 1.99, 1.32, 50, 54, 'Satisfactory'),
('F', 1.00, 1.32, 0, 49, 'Unsatisfactory');");
$link->close();
?>
</body>
</html>
