Database
● An organized collection of data
● Data - any specific piece of information (ex: torino.jewel@psdqatar.com)
○ Manual - pen & paper
○ Computerized - on a digital device
Elements of a Database:
1. Field - single piece of data
2. Record - collection of related fields
3. Table - collection of records
Classifications:
● simple database - only maintains one category/table of data
● relational database - maintains two or more related categories/tables of data
(R)DBMS - (relational) database management system
MySQL - structure query language
Commands:
● mysql -p ←to open mysql
● exit← type twice to exit the app
● show databases; ← to show ur db
● use dbname; ← to activate ur database file
● describe tbname; ← shows table structure
● create table tbname (fldname type(#), fldname type(#)); ← creates
table w/ fieldnames/columns
● create database [IF NOT EXISTS] dbname; ← prevents you from creating a
new database that already exists
● insert into tbname (fldname, fldname) values (’val’,’val’); ← to
insert a value under a column
Select
● select * from tbname; ← displays all records in ur table, * is the short hand for
selecting all the columns
○ where fldname=’#’ ← selects a specific value from a field name in a table
○ order by fldname asc; or desc; ← arranges values from ascending to
descending
● limit # ← to limit the number or records you want to see
● select fieldname, fieldname from tbname; ← selects a specific column
General order for the select query:
○ SELECT: Specifies the columns to retrieve.
○ FROM: Specifies the table from which to retrieve the data.
○ WHERE: Filters the rows based on specified conditions.
○ ORDER BY: Specifies the order in which the rows should be returned.
○ LIMIT: limits the no. of rows you’re seeing
editing
→ modifying the table structure
→ modifying record
● alter table tbname add fieldname typ(#); ← to put another record.
ALTER TABLE tbname CHANGE old_column_name new_column_name column_type; ← to
change a field/column & width
● alter table tbname drop fdname; ← to delete a fdname
● alter table tbname rename new_tbname; ← to delete a fdname
● update *tbname* set fdname=#; ←changes all values under a field/column
● update tbname set fdname=# where fdname='#'; ← changes a specific value
under a field
Primary Key - info only exclusive to 1 user (ex: email, username, etc)
● alter table tbname add primary key (fdname); ← to add a primary key to a
field/column
● delete from tbname;←deletes all records from the table
delete from tbname where fdname=’#’; ← deletes a value from a field
delete from tbname ← deletes all values from a table
torino567comporg23
passtorino567comporg23
<body> <h1>Searching of Record</h1> <?php $id=$_POST['id'];
$con=mysqli_connect("localhost", "torino567comporg23", "passtorino567comporg23",
"torino567comporg23"); if (!$con) { die("Failed to connect."); } $query=mysqli_query($con,
"select * from Student where stud_id='$id'"); $num=mysqli_num_rows($query);
if ($num<1) { echo "No record found."; } else { echo "<table border='1'>"; echo "<tr>"; echo
"<th>Student ID</th> <th>Family Name</th><th>Given Name</th> <th>Middle Name
</th><th>Gender</th> <th>Age</th> </tr>";
while ($row=mysqli_fetch_array($query))
{
$a=$row['stud_id'];
$b=$row['fname'];
$c=$row['gname'];
$d=$row['mname'];
$e=$row['gender'];
$f=$row['age'];
echo "<tr>";
echo "<td>$a</td>";
echo "<td>$b</td>";
echo "<td>$c</td>";
echo "<td>$d</td>";
echo "<td>$e</td>";
echo "<td>$f</td>";
echo "</tr>";
}
echo "</table>";
echo "<p>There are $num records.</p>";
} ?>
Data Types:
int - integer
float - # w/ decimal
text - can hold 250 letters & u can set the max of letters it can hold but it will save for the max #
of char it has (ex: fname text(30) - “Barde” ← will save 30)
varchar - like text but will save the number of characters in the word (ex: fname text(30) -
“Barde” ← will save 5)
u can press the up arrow & it will put in ur previous command. you can also retype ; if you forgot
to in ur previous command.
sql keywords are not case sensitive, so select will be the same as SELECT
