educational

An Introduction to MySQL: Part 3

In Part 1 of his MySQL series, Cracker provided us with a basic understanding of how to connect to the server, select the database, and perform some basic commands. In Part 2, he covered the concepts and techniques needed to setup up the database for manipulation. In today's final installment, he will discuss how to actually manipulate the database.

Manipulating the Database
A MySQL database can be manipulated in four possible ways: addition, deletion, modification, and search. These topics will all be briefly covered in the following two sections. However, before we begin, I would like to highlight the fact that SQL, like many computer languages, is particular about command syntax. The slightest error in placement of a parentheses, comma, or semicolon will almost surely end in error. As a result, take care to be attentive of command syntax.

• Insertion of records
Note: The originally created table, test, created in the last section will be used to illustrate the examples in this section. Here it is again, for quick reference:

mysql> CREATE TABLE test (
> name VARCHAR (15),
> email VARCHAR (25),
> phone_number INT,
> ID INT NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (ID));

Insertion of data into the table is accomplished, logically enough, using the INSERT command.

mysql> INSERT INTO test VALUES
mysql> ('Bugs Bunny', 'carrots@crackersoft.ru',
mysql> 5554321, NULL);

Result, assuming the command was correctly entered:

Query OK, 1 row affected (0.02 sec)
mysql>

So what really happened here? Single quotations were placed around the datatypes VARCHAR. All datatypes of type STRING (i.e. char, varchar, text, blob, etc.) must be surrounded in single quotes, or an error will occur. There were no single quotes surrounding the phone number. Datatypes of type INT do not require single quotes.

NULL? A NULL allows any datatype with the characteristic AUTO_INCREMENT to be automatically assigned a value. If it is the first record inserted into the database, it is assigned the value '1'. Otherwise, it is assigned the previously inserted value + 1 (i.e. if the previously inserted value was '2', then the next would be '3'). In addition, the insertion of NULL into a variable of type TIMESTAMP causes that variable to be given the value of the current date.

Note: It is of importance to remember that the same number of values must be inserted as datatypes are contained within a record. In the above example, if one attempted to insert only three values instead of four, the insertion would fail. The same result applies if one attempted to insert five values. Example:

mysql> insert into test values('doggy');
ERROR 1058: Column count doesn't match value count
mysql>

Note: One of the advantageous aspects of MySQL is it's ability to convert without any trouble between datatypes. MySQL automatically converts between integers, strings, and dates.

• Selection
A database would not be much use if one was not able to search and extract data from it. In MySQL terms, this is accomplished through the SELECT statement.

mysql> SELECT * FROM test
mysql> WHERE (name = "Bugs Bunny");

Result:

name email phone ID
Bugs Bunny carrots@crackersoft.ru 5554321 1

Let's assume we have inserted four differing records, all bearing the same name "Bugs Bunny", yet having different email addresses and phone numbers. The table test, would look somewhat like the following:

name email phone ID
Bugs Bunny carrots@crackersoft.ru 5554321 1
Bugs Bunny peppers@crackersoft.ru 5554331 2
Bugs Bunny lettuce@crackersoft.ru 5554341 3
Bugs Bunny celery@crackersoft.ru 5554351 4

• Deletion
One can also delete records previously inserted into the table.
This is accomplished through the use of the DELETE command.

mysql> DELETE FROM test
mysql> WHERE (name = "Bugs Bunny");

Result: This would result in the deletion of all records within the table test containing the name "Bugs Bunny". Another example:

mysql> DELETE FROM test
mysql> WHERE (phone_number = 5554321);

Result: (Using the previously illustrated example)

name email phone ID
Bugs Bunny peppers@crackersoft.ru 5554331 2
Bugs Bunny lettuce@crackersoft.ru 5554341 3
Bugs Bunny celery@crackersoft.ru 5554351 4

• Modification
MySQL has the capability of modifying data already entered into the table. This is accomplished through the UPDATE command.

mysql> UPDATE test SET name = 'Daffy Duck'
mysql> WHERE name = "Bugs Bunny";

Result: (Using the previously illustrated example)

name email phone ID
Daffy Duck peppers@crackersoft.ru 5554331 2
Daffy Duck lettuce@crackersoft.ru 5554341 3
Daffy Duck celery@crackersoft.ru 5554351 4

In this section, we covered the core MySQL database manipulation functions, basic insertion, deletion, modification, and search. The next section will elaborate on these capabilities, providing extended functioning and flexibility when manipulating the database.

Advanced MySQL Commands
What we have covered so far is but a small part of what MySQL is capable of. Let's delve a little deeper into the language, exploring some of the more advanced commands of the language.

• Logical Operations

MySQL includes full support of all basic logical operations.

AND (&&)

mysql> SELECT * FROM test WHERE
mysql> (name = "Bugs Bunny") AND
mysql> (phone_number = 5554321);

Result: All records containing the name "Bugs Bunny" AND the phone number '5554321' will be displayed to the screen.

OR ( || )

mysql> SELECT * FROM test WHERE
mysql> (name = "Bugs Bunny") OR
mysql> (phone_number = 5554321);

Result: All records containing the name "Bugs Bunny" OR the phone number '5554321' will be displayed to the screen.

NOT ( ! )

mysql> SELECT * FROM test WHERE
mysql> (name != "Bugs Bunny");

Result: All records NOT containing the name "Bugs Bunny" will be displayed to the screen.

Order By

mysql> SELECT * FROM test WHERE mysql> (name = "Bugs Bunny") ORDER BY mysql> phone_number;

Result: All records containing the name "Bugs Bunny" will be displayed to the screen, ordered in respect to the phone_number. The slightest error in placement of a parentheses, comma, or semicolon will almost surely end in error.

• Search functions
MySQL offers the user the ability to perform both general and specific searches on data.

mysql> SELECT * FROM test WHERE
mysql> (name LIKE "%gs Bunny");

Result: All records containing the partial string "gs Bunny" will be displayed to the screen. This would include such names as: "Bugs Bunny", "ags Bunny", "gs Bunny", and "234rtgs Bunny".

Notice that "LIKE" has been used instead of the equals sign (=). "LIKE" signifies that one is searching for an estimate of the data requested, and not necessarily an exact copy. The '%' sign could be placed anywhere within the string. The method in which the server searches for a string is dependent upon where one places the '%' sign.

mysql> SELECT * FROM test WHERE
mysql> (name LIKE "Bugs Bunny%");

Result: All records containing the partial string "Bugs Bunny" will be displayed to the screen. This would include such names as: "Bugs Bunnys", "Bugs Bunnyyyy453", "Bugs Bunnytrtrtrtrtr", but not "gs Bunny".

• Focused Search Results
One can also perform searches and display only certain columns.

mysql> SELECT name FROM test WHERE
mysql> (name = "Bugs Bunny");

Result: name Bugs Bunny

• Alter table
Another very important function of MySQL is the ability to modify all previously created tables. This is accomplished via the ALTER statement. This function allows one to add, modify, and delete columns, as well as rename the table, among other functions:

Example: Rename table:

mysql> ALTER table test RENAME mytest;

Example: Add a column

mysql> ALTER table mytest ADD birthday DATE;

Example: Modify a column

mysql> ALTER table mytest CHANGE
mysql> name newname VARCHAR (25);

Example: Delete a column

mysql> ALTER table mytest DROP newname;

Executing the above four functions would modify test, creating the following table:

mysql> TABLE mytest (
> email VARCHAR (25),
> phone_number INT,
> ID INT AUTO_INCREMENT,
> birthday DATE );

Simple, yes? Don't worry; all it takes is practice!

The topics covered within this article are but a short introduction of the capabilities of MySQL. However, these functions form the basis of almost all advanced commands to be found in the language. Above all, the most important lesson that one can remember is to practice, study the documentation, and learn as much as possible on your own, or through formal training. Only by taking an enthusiastic, and even an "aggressive" approach to the language can one master it.

Copyright © 2025 Adnet Media. All Rights Reserved. XBIZ is a trademark of Adnet Media.
Reproduction in whole or in part in any form or medium without express written permission is prohibited.

More Articles

profile

WIA Profile: Reba Rocket

As chief operating officer and chief marketing officer of Takedown Piracy, long at the forefront of intellectual property protection in adult entertainment, Rocket is dedicated to safeguarding the livelihoods of content creators and producers while fostering a more ethical and sustainable industry.

Women In Adult ·
opinion

Protecting Content Ownership Rights When Using AI

In today’s digital age, content producers have more tools at their disposal than ever before. Among these tools, artificial intelligence (AI) content generation has emerged as a game changer, enabling creators to produce high-quality content quickly and efficiently.

Corey D. Silverstein ·
opinion

How Payment Orchestration Can Help Your Business

An emerging payment solution is making waves in the merchant world: the payment orchestration platform (POP). It’s quickly gaining traction as a powerful tool for managing online payments — but questions abound.

Cathy Beardsley ·
opinion

Fine-Tuning Refund and Cancellation Policies

For adult websites, managing refunds and cancellations isn’t just about customer service. It’s a crucial factor in maintaining compliance with the regulations of payment processors and payment networks such as Visa and Mastercard.

Jonathan Corona ·
profile

WIA Profile: Laurel Bencomo

Born in Cambridge, England but raised in Spain, Laurel Bencomo initially chose to study business at the University of Barcelona simply because it felt familiar — both of her parents are entrepreneurs. She went on to earn a master’s degree in sales and marketing management at the EADA Business School, while working in events for a group of restaurants in Barcelona.

Women In Adult ·
profile

Gregory Dorcel on Building Upon His Brand's Signature Legacy

“Whether reflected in the storyline or the cast or even the locations, the entertainment we deliver is based on fantasy,” he elaborates. “Our business is not, and never has been, reality. People who are buying our content aren’t expecting reality, or direct contact with stars like you can have with OnlyFans,” he says.

Jeff Dana ·
opinion

How to Turn Card Brand Compliance Into Effective Marketing

In the adult sector, compliance is often treated as a gauntlet of mandatory checkboxes. While it’s true that those boxes need to be ticked and regulations must be followed, sites that view compliance strictly as a chore risk missing out on a bigger opportunity.

Jonathan Corona ·
opinion

A Look at the Latest AI Tools for Online Safety

One of the defining challenges for adult businesses is helping to combat the proliferation of illegal or nonconsensual content, as well as preventing minors from accessing inappropriate or harmful material — all the more so because companies or sites unable or unwilling to do so may expose themselves to significant penalties and put their users at risk.

Gavin Worrall ·
opinion

Know When to Drop Domains You Don't Need

Do you own too many domains? If so, you’re not alone. Like other things we accumulate, every registered domain means something to us. Sometimes a domain represents a dream project we have always wanted to do but have never quite gotten around to.

Juicy Jay ·
opinion

Understanding 'Indemnification' in Business Contracts

Clients frequently tell me that they didn’t understand — or sometimes, even read — certain portions of a contract because those sections appeared to be just “standard legalese.” They are referring, of course, to the specialized language used in legal documents, including contracts.

Corey D. Silverstein ·
Show More