| MySQL Manual Page |
| The Manual |
| Before we begin. |
|
Before we begin we're going to set a table we're going to use.
How we make the table you'll learn in the next step, but this is pure for the easyness. First we have the table with the column info. Here you can see which variables are used and which type they use. mysql> show columns from test; +-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | | PRI | 0 | auto_increment |
| name | text | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+
Now we see what info we have put in the fields. This is important for the examples given
in the next steps of learning MySQL. mysql> select * from test; +----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | Cindy | 25 |
| 2 | Jerom | 19 |
| 3 | Simon | 32 |
+----+-------+------+
Note that when you're looking at the commands you'll see that some words are between ( and ).
These commands are optional so it's not necessary to use them. |
| How to create a table. |
|
To create a table you must use the CREATE TABLE command.
Before creating a table you must know exactly what you want to put in it the table. You must also know what range you want to give a column. For instance: if you put a column in a table like "human age" don't use a variabletype like bigint Because no one will be that old. (18446744073709551615) For a complete overview of the various columntypes you can click here. Syntax: mysql> create table tablename (columnname columntype extra parameters, columnname columntype extra parameters, etc.); Example: mysql> create table test (id int auto_increment primary key, name text, age tinyint); |
| How to rename a table. |
|
To rename a table you must use the ALTER TABLE - RENAME command.
Syntax: mysql> alter table oldtablename rename newtablename; Example: mysql> alter table test rename usertable; |
| How to delete a table. |
|
To delete a table you must use the DROP TABLE command.
Syntax: mysql> drop table tablename; Example: mysql> drop table test; |
| How to add a column to a table |
|
To add a column to a table you must use the ALTER TABLE - ADD COLUMN command.
Syntax: mysql> alter table tablename add column columnname columntype extra parameters; Example: mysql> alter table test add column date text not null; |
| How to delete a column from a table |
|
To delete a column from a table you must use the ALTER TABLE - DROP COLUMN command.
Syntax: mysql> alter table tablename drop column columnname; Example: mysql> alter table test drop column date; |
| How to change the columnname from a column in a table |
|
To change the columnname from a column in a table you must use the ALTER TABLE - CHANGE COLUMN command.
Syntax: mysql> alter table tablename change column oldcolumnname newcolumnname newcolumntype extra parameters; Example: mysql> alter table test change column date birthday text not null; |
| How to change the columntype from a column in a table |
|
To change the columntype from a column in a table you must use the ALTER TABLE - CHANGE COLUMN command.
Syntax: mysql> alter table tablename change column oldcolumnname newcolumnname newcolumntype extra parameters; Example: mysql> alter table test change column date date int not null; |
| How to insert a specific item in a column |
|
To insert a specific item in a column you must use the INSERT INTO - VALUES command.
Syntax: mysql> insert into tablename (columnname, columnname, etc.) values("value", "value", etc.); Example: mysql> insert into test(name, age) values("Simon", "32"); |
| How to update a specific item in a column |
|
To update a specific item in a column you must use the UPDATE - SET - (,) - WHERE - (AND) command.
Syntax: mysql> update tablename set columnname="value" , columnname="value" where columnname="value" and columnname="value"; Example: mysql> update test set name="Jerom" , age="19" where name="Simon" and age="32"; |
| How to delete a row from a table |
|
To delete a row from a table you must use the DELETE FROM - WHERE - (AND) command.
Syntax: mysql> delete from tablename where columnname="value" and columnname="value"; Example: mysql> delete from test where name="Simon" and age="32"; |
| How to delete more rows from a table |
|
To delete more rows from a table you must use the DELETE FROM - WHERE - LIKE command.
In this example we will delete everybody who's age begins with a 2. So in this case Cindy will be deleted cause she is 25. Syntax: mysql> delete from tablename where columnname like "value%"; Example: mysql> delete from test where age like "2%"; before after
+----+-------+------+ +----+-------+------+
| id | name | age | | id | name | age |
+----+-------+------+ +----+-------+------+
| 1 | Cindy | 25 | | 2 | Jerom | 19 |
| 2 | Jerom | 19 | | 3 | Simon | 32 |
| 3 | Simon | 32 | +----+-------+------+
+----+-------+------+
|
| How to show the current table(s) in the database |
|
To show the current table(s) the database you must use the SHOW TABLES command.
Syntax: mysql> show tables; Example: mysql> show tables;
+--------------------+
| Tables in database |
+--------------------+
| test |
+--------------------+
|
| How to show specific data from a table |
|
To show specific data from a table you must use the SELECT - FROM - WHERE - (AND) command.
Syntax: mysql> select value from tablename where columnname="value" and columnname="value"; Example: mysql> select * from test where name="Simon" and age="32";
+----+-------+------+
| id | name | age |
+----+-------+------+
| 3 | Simon | 32 |
+----+-------+------+
|
| How to sort a table by a specific column |
|
To show specific data in a specific order from a table you must use the SELECT - FROM - (WHERE) - (AND) - ORDER BY - (DESC) command. Syntax: mysql> select value from tablename where columnname="value" and columnname="value" order by columnname desc, columnname desc; Example 1: mysql> select * from test order by name;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | Cindy | 25 |
| 2 | Jerom | 19 |
| 3 | Simon | 32 |
+----+-------+------+
Example 2:mysql> select * from test order by name desc;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 3 | Simon | 32 |
| 2 | Jerom | 19 |
| 1 | Cindy | 25 |
+----+-------+------+
|
| How to group tables and create summarized columns (BETA VERSION) |
|
To group data from a table and summarize data from columns you must use the SELECT - FROM - (WHERE) - (AND) - GROUP BY - (ORDER BY) - (DESC) command. Syntax: mysql> select columnname, etc. from tablename where columnname="value" and columnname="value" group by columnname order by columnname desc, etc.; Example 1: mysql> select page, sum(clicked) from forum order by "sum(clicked)"; |
| Links to get you started |
|
The MySQL Homepage ( with the full documentation ) The PHP Homepage ( with the full documentation ) The HTML full documentation page My own written documentation pages of PHP, MySQL and HTML |