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 🙂

Now that you’ve seen how easy it is to use MySQL and created your first database, it’s time to look at how you create users, as you probably won’t want to grant your PHP scripts root access to MySQL—it could cause a real headache should you get hacked :).

To create a user, issue the GRANT command, which takes the following form (don’t type this in; it’s not an actual working command):
GRANT PRIVILEGES ON database.object TO ‘username’@’hostname’
IDENTIFIED BY ‘password’;
This should all look pretty straightforward, with the possible exception of the database.object part, which refers to the database itself and the objects it contains, such as tables.

• *.* All databases and all their objects
• database.* Only the database called database and all its objects
• database.object Only the database called database and its object called object

So, let’s create a new user who can access just the new tietokantadb database and all its objects, by entering the following (replacing the username asentaja and also the password passwordi with ones of your choosing):
GRANT ALL ON tietokantadb.* TO ‘asentaja’@’localhost’
IDENTIFIED BY ‘passwordi’;
What this does is allow the user asentaja@localhost full access to the tietokantadb database using the password passwordi. You can test whether this step has worked by entering
quit to exit and then rerunning MySQL the way you did before, but instead of entering ->
-u root -p, type -u asentaja -p, or whatever username you created.

a new database called tietokantadb:
CREATE DATABASE tietokantadb;
A successful command will return a message that doesn’t mean much yet—Query OK,
1 row affected (0.00 sec)—but will make sense soon. Now that you’ve created the database, you want to work with it, so issue the following command:
USE tietokantadb;

Yleiset komennot:
ALTER Alter a database or table
BACKUP Back up a table
\c Cancel input
CREATE Create a database
DELETE Delete a row from a table
DESCRIBE Describe a tables columns
DROP Delete a database or table
EXIT (Ctrl-C) Exit
GRANT Change user privileges
HELP (\h, \?) Display help
INSERT Insert data
LOCK Lock table(s)
QUIT (\q) Same as EXIT

RENAME Rename a table
SHOW List details about an object
SOURCE Execute a file
STATUS (\s) Display the current status
TRUNCATE Empty a table
UNLOCK Unlock table(s)
UPDATE Update an existing record
USE Use a database

Tietokanta voidaan poistaa jos on esimerkiksi tehnyt väärän nimisen tietokannan. Tietokannan poistaminen tapahtuu Mysql:ssä ja sinne kirjaudutaan ensiksi mysql -u root -p.
Kirjautumisen jälkeen voi katsella mitä tietokantoja on show databases; komennolla.

Tietokannan poistaminen tapahtuu komennolla DROP DATABASE tietokannan nimi. Jos esimerkiksi on tietokanta blogi ja se halutaan poistaa se onnistuu komennolla DROP DATABASE blogi;

Tietokannat voidaan listata seuraavalla komennolla:
show databases;

Jotta tietokantaa voidaa käyttää käyttäjätunnuksella ja salasanalla, ne tulee liittää oikeuksilla tietokantaan. Se tehdään seuraavasti ->
GRANT ALL PRIVILEGES ON tietokannan-nimi.* to ‘käyttäjätunnus’ identified by ‘salasana’; Esimerkiksi tehdään wpdata tietokantaan liittäminen dbmekaanikolle. -> GRANT ALL PRIVILEGES ON wpdata.*to ‘dbmekaanikko’ identified by ‘passwordi’;

Tietokanta käyttäjään täytyy asettaa salasana ja se tehdään seuraavasti -> SET PASSWORD FOR käyttäjätunnus=password(‘salasana’);
Esimerkiksi asetetaan salasana passwordi dbmekaanikko käyttäjälle ->
SET PASSWORD FOR dbmekaanikko=password(‘passwordi’);