Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

MySQL Commands – Part 2

Wednesday, June 18th, 2014 by Servage

www

Let us continue from part 1.

You also need to be aware that if you create a new user but do not specify an IDENTIFIED BY clause, the user will have no password, a situation that is very insecure and should be avoided.

Creating a table

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

USE publications;

Now enter the commands one line at a time.

Creating a table called classics

CREATE TABLE classics (

author VARCHAR(128),

title VARCHAR(128),

type VARCHAR(16),

year CHAR(4)) ENGINE MyISAM;

You could also issue this command on a single line, like this:

CREATE TABLE classics (author VARCHAR(128), title VARCHAR(128),

type VARCHAR(16), year CHAR(4)) ENGINE MyISAM;

but MySQL queries can be long and complicated, so I recommend entering one part of a query per line until you are comfortable with longer ones.

MySQL should then issue the response “Query OK, 0 rows affected,” along with a note of how long it took to execute the command. If you see an error message instead, check your syntax carefully. Every parenthesis and comma counts, and typing errors are easy to make. In case you are wondering, the ENGINE MyISAM tells MySQL the type of database engine to use for this table.

To check whether your new table has been created, type:

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

MySQL supports keys or indexes, which are quick ways to look up and search for data. The Key heading shows what type of key (if any) has been applied.

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.

Data Types

You may have noticed that three of the table’s fields were given the data type VARCHAR, and one was given the type CHAR. The term VARCHAR stands for VARiable length CHARacter string and the command takes a numeric value that tells MySQL the maximum length allowed for a string stored in this field.

This data type is very useful, as it allows MySQL to plan the size of a database and perform lookups and searches more easily. The downside is 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.

Sources for further reading

MySQL Commands - Part 2, 3.7 out of 5 based on 3 ratings
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.