mysql

notes on mysql

1-All MySQL commands end with a semicolon; if the phrase does not end with a semicolon, the command will not execute. Also, although it is not required, MySQL commands are usually written in uppercase and databases, tables, usernames, or text are in lowercase to make them easier to distinguish. However, the MySQL command line is not case sensitive.

Creating a database is very easy:

CREATE DATABASE database name; In this case, for example, we will call our database "events." mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | events | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)

create a new MySQL table:

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), food VARCHAR(30), confirmed CHAR(1), signup_date DATE);

In MySQL, the phrase most often used to delete objects is Drop. You would delete a MySQL database with this command:

DROP DATABASE database name;

Use this format to insert information into each row:

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');

Let's add a couple more people to our group:

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Sandy", "Key Lime Tarts","N", '2012-04-14'); INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tom", "BBQ","Y", '2012-04-18'); INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "Tina", "Salad","Y", '2012-04-10');

Once you input that in, you will see the words

Query OK, 1 row affected (0.00 sec)

he table's organization with this command:

SCRIBE potluck; Keep in mind throughout that, although the MySQL command line does not pay attention to cases, the table and database names are case sensitive: potluck is not the same as POTLUCK or Potluck. mysql>DESCRIBE potluck; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | food | varchar(30) | YES | | NULL | | | confirmed | char(1) | YES | | NULL | | | signup_date | date | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)

You can quickly check what databases are available by typing

SHOW DATABASES; mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.01 sec)

In the same way that you could check the available databases, you can also see an overview of the tables that the database contains

SHOW tables;

This command accomplishes a number of things

This command accomplishes a number of things: It has created a table called potluck within the directory, events. We have set up 5 columns in the table—id, name, food, confirmed, and signup date. The "id" column has a command (INT NOT NULL PRIMARY KEY AUTO_INCREMENT) that automatically numbers each row. The "name" column has been limited by the VARCHAR command to be under 20 characters long. The "food" column designates the food each person will bring. The VARCHAR limits text to be under 30 characters. The "confirmed" column records whether the person has RSVP'd with one letter, Y or N. The "date" column will show when they signed up for the event. MySQL requires that dates be written as yyyy-mm-dd

Let's open up the database we want to use:

USE events;

We can take a look at our table:frmo 13 point before

mysql> SELECT * FROM potluck; +----+-------+----------------+-----------+-------------+ | id | name | food | confirmed | signup_date | +----+-------+----------------+-----------+-------------+ | 1 | John | Casserole | Y | 2012-04-11 | | 2 | Sandy | Key Lime Tarts | N | 2012-04-14 | | 3 | Tom | BBQ | Y | 2012-04-18 | | 4 | Tina | Salad | Y | 2012-04-10 | +----+-------+----------------+-----------+-------------+ 4 rows in set (0.00 sec)

Let's take a look at how the table appears within the database using the "SHOW TABLES;" command:

mysql> SHOW TABLES; +------------------+ | Tables_in_events | +------------------+ | potluck | +------------------+ 1 row in set (0.01 sec)