Deleting a table is very easy indeed. But, because I don’t want you to have to reenter all the data for the classics table, let’s quickly create a new table, verify its existence, and then delete it. You can do this by typing the commands:

CREATE TABLE datatiedot(trash INT);
DESCRIBE datatiedot;
DROP TABLE datatiedot;
SHOW tables;

Actually, upon reflection, you might decide that the page count column pages isn’t actually all that useful for this particular database, so here’s how to remove that column by using the DROP keyword:
ALTER TABLE classics DROP pages;

Remember that DROP is irreversible. You should always use it with
caution, because you could inadvertently delete entire tables (and
even databases) with it if you are not careful!

Rename example command is: ALTER TABLE classics CHANGE type category VARCHAR(16);

Note the addition of VARCHAR(16) on the end of this command. That’s because the CHANGE keyword requires the data type to be specified, even if you don’t intend to change it, and VARCHAR(16) was the data type specified when that column was initially created as type.

Changing a column’s data type also makes use of the ALTER command, this time in conjunction with the MODIFY keyword. To change the data type of the column year from CHAR(4) to SMALLINT (which requires only 2 bytes of storage and so will save disk space):D, enter the following command:
ALTER TABLE classics MODIFY year SMALLINT;
When you do this, if the conversion of data type makes sense to MySQL, it will automatically change the data while keeping the meaning. In this case, it will change each string to a comparable integer, so long as the string is recognizable as referring to an integer.

Renaming a table, like any other change to the structure or meta-information about a table, is achieved via the ALTER command. So, for example, to change the name of the table classics to klassikot, you would use the following command: ALTER TABLE classics RENAME klassikot;

If you tried that command, you should revert the table name by entering the following, so that later examples in this chapter will work as printed:
ALTER TABLE klassikot RENAME classics;

To add data to a table, use the INSERT command. Let’s see this in action by populating the table classics with the data.

Example 🙂 Populating the classics table
INSERT INTO classics(author, title, type, year)
VALUES(‘Markku Aro’,’30 suosikkia’,’POP’,’1998′);
INSERT INTO classics(author, title, type, year)
VALUES(‘Lenni Kalle’,’parhaat’,’klassikko’,’1811′);
INSERT INTO classics(author, title, type, year)
VALUES(‘Esa Mäkelä”,’Ketsuppia saksasta’,’POP’,’1990′);
INSERT INTO classics(author, title, type, year)
VALUES(‘Matti ja Teppo”,’kautta aikain parhaat’,’iskelmä’,’1975′);
INSERT INTO classics(author, title, type, year)
VALUES(‘Suvi Teräsniska’,’30 parhainta ‘,’POP’,’2020′);

After every second line, you should see a Query OK message. Once all lines have been entered, type the following command, which will display the table’s contents.

Use next command to read content:
SELECT * FROM classics;

BINARY(n) Exactly n (<= 255) As CHAR but contains binary data
VARBINARY(n) Up to n (<= 65535) As VARCHAR but contains binary data

The BINARY data types store strings of bytes that do not have an associated character set.

The term VARCHAR stands for VARiable length CHARacter string, and the command takes a numeric value that tells MySQL server the maximum length allowed for a string stored in this field.
Both CHAR and VARCHAR accept text strings and impose a limit on the size of the field. The difference is that every string in a CHAR field has the specified size. If you put in a smaller string, it is padded with spaces. A VARCHAR field does not pad the text; it lets the size of the field vary to fit the text that is inserted. But VARCHAR requires a small amount of overhead to keep track of the size of each value. So, CHAR is slightly more efficient if the sizes are similar in all records, whereas VARCHAR is more efficient if sizes can vary a lot and get large. In addition, the overhead causes access to VARCHAR data to be slightly slower than to CHAR data.

VARCHAR is useful in our example, because it can accommodate author names and titles of different lengths, while helping MySQL plan the size of the database and perform lookups and searches more easily. Just be aware that if you ever attempt to assign a string value longer than the length allowed, it will be truncated to the maximum
length declared in the table definition.

The year field, however, has predictable values, so instead of VARCHAR we use the more efficient CHAR(4) data type. The parameter of 4 allows for 4 bytes of data, supporting all years from –999 to 9999; a byte comprises 8 bits and can have the values 00000000 through 11111111, which are 0 to 255 in decimal. You could, of course, just store two-digit values for the year, but if your data is going to still be needed in the following century, or may otherwise wrap around, it will have
to be sanitized first—think of the “millennium bug” 😀 that would have caused dates beginning on January 1, 2000, to be treated as 1900 on many of the world’s biggest computer installations.

CHAR(n) Exactly n (<= 255) CHAR(5) “Hello” uses 5 bytes and
CHAR(57) “Goodbye” uses 57 bytes.

VARCHAR(n) Up to n (<= 65535) VARCHAR(7) “Hello” uses 5 bytes
VARCHAR(100) “Goodbye” uses 7 bytes.

The DESCRIBE command is an invaluable debugging aid when you need to ensure that you have correctly created a MySQL table. You can also use it to remind yourself about a table’s field or column names and the types of data in each one. Let’s look at each of the headings in detail:

Field
The name of each field or column within a table
Type
The type of data being stored in the field
Null
Whether the field is allowed to contain a value of NULL
Key
What type of key, if any, has been applied (keys or indexes in MySQL are quick
ways to look up and search for data)
Default
The default value that will be assigned to the field if no value is specified when a new row is created
Extra
Additional information, such as whether a field is set to auto-increment

Command is: DESCRIBE classics;

At this point, you should now be logged into MySQL with ALL privileges granted for the database tietokantadb (or a database that was created for you), so you’re ready to create your first table. Make sure the correct database is in use by typing the following
(replacing tietokantadb with the name of your database if it is different):
USE tietokantadb;

The final two words in this command require a little explanation.
MySQL can process queries in many different ways internally, and
these different ways are supported by different engines. From version
5.6 onwards InnoDB is the default storage engine for MySQL,
and we use it here because it supports FULLTEXT searches. So long
as you have a relatively up-to-date version of MySQL :), you can omit
the ENGINE InnoDB section of the command when creating a table,
but I have kept it in for now to emphasize that this is the engine
being used.

CREATE TABLE classics (author VARCHAR(128), title
VARCHAR(128), type VARCHAR(16), year CHAR(4)) ENGINE
InnoDB;

But MySQL commands can be long and complicated, so I recommend
using the format shown 🙂