How to Create a Database, Table and authorize a user with MySQL for Windows
Language(s):MySQL
Category(s):MySQL

This article will show how to create a database, define a table and add a user using the Windows version of MySQL. This article assumes you have installed MySQL.

1)  Start with a command prompt:

Start|Run|Cmd

2) Run the MySQL Command Line:

C:\>mysql  -hlocalhost -uroot -pmypassword

Note that you would have specified the root password during MySQL installation. For your system, the root password may be blank, which is allowed.

If you get the following error:

C:\>mysql

'mysql' is not recognized as an internal or external command,

operable program or batch file.

 

It is because you didn’t check the option to add MySQL.exe to the dos path. This is not a big problem, just navigate to the folder with mysql.exe and try again. On my system, the path is the following:

C:\Program Files\MySQL\MySQL Server 5.0\bin\

3) You should see the welcome message:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 7

Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

4) Add a database named ‘McBean’:

mysql> Create Database McBean;

5) Tell MySQL we are using the new database:

mysql> Use McBean;

6) We will create a table named Yammer with the following fields:

ID: unique identifier

Name: varchar(25) – indexed

Description: text

Here is the code to do that:

Create Table Yammer

 (

   ID int not null AUTO_INCREMENT Primary Key,

   Name varchar(25),

      Index(Name),

   Description Text

 );

Here’s what it will look like on the screen. Just hit the enter key after each line. MySQL won’t respond until it sees the semi-colon character:

mysql> Create Table Yammer

    -> (

    -> ID int not null AUTO_INCREMENT Primary Key,

    -> Name varchar(30),

    ->   Index(Name),

    -> Description text

    -> );

Query OK, 0 rows affected (0.13 sec)

7) Add a user with UserID = Hoser and Password = Yoser:

mysql> Create User 'Hoser'@'localhost' Identified By 'Yoser';

Query OK, 0 rows affected (0.00 sec)

8) Grant Hoser authority to the McBean database:

mysql> use McBean;

Database changed

mysql> Grant All on *.* to 'Hoser'@'localhost';

Query OK, 0 rows affected (0.00 sec)

9) Now we should be able to exit and restart MySQL using this user and password:

mysql> exit

Bye

C:\>mysql -hlocalhost -uHoser -pYoser

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

10) The new user should be able to use the database and add records:

mysql> Use McBean;

Database changed

mysql> Insert Into Yammer

    -> (Name, Description)

    -> Values('Hoser', 'Hoser Yoser McBean at your service.')

    -> ;

Query OK, 1 row affected (0.27 sec)

11) And of course select:

mysql> Select * From Yammer;

+----+-------+-------------------------------------+

| ID | Name  | Description                         |

+----+-------+-------------------------------------+

|  1 | Hoser | Hoser Yoser McBean at your service. |

+----+-------+-------------------------------------+

1 row in set (0.06 sec)

 

This article has been viewed 0 times.
The examples on this page are presented "as is". They may be used in code as long as credit is given to the original author. Contents of this page may not be reproduced or published in any other manner what so ever without written permission from Idioma Software Inc.