Summary: In this tutorial we will learn just basic concept of MySQL database like how to create Database, Table and User. Here User can just revision of MySQL Queries i.e. SELECT, INSERT and more operation.
For learn MySQL in details, See our MySQL Tutorial.
Creating a database in MySQL
CREATE DATABASE mydb;
Return value:
Query OK, 1 row affected (0.05 sec)
Using the created database mydb
USE mydb;
Return value:
Database Changed
Creating a table in MySQL
CREATE TABLE mytable ( id int unsigned NOT NULL auto_increment, username varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id) );
CREATE TABLE mytable will create a new table called mytable.
id int unsigned NOT NULL auto_increment creates the id column, this type of field will assign an unique numeric ID to each record in the table (meaning that no two rows can have the same id in this case), MySQL will automatically assign a new, unique value to the record’s id field (starting with 1).
Return value:
Query OK, 0 rows affected (0.10 sec)
Inserting a row into a MySQL table
INSERT INTO mytable ( username, email ) VALUES ( "myuser", "myuser@example.com" );
Example return value:
Query OK, 1 row affected (0.06 sec)
The varchar a.k.a strings can be also be inserted using single quotes
INSERT INTO mytable ( username, email ) VALUES ( 'username', 'username@example.com' );
Selecting rows based on conditions in MySQL
SELECT * FROM mytable WHERE username = "myuser";
Return value:
| id | username | |
| 1 | myuser | myuser@example.com |
3 rows in set (0.00 sec)
Show tables in an existing database
SHOW tables;
Return value
| Tables_in_mydb |
| mytable |
1 row in set (0.00 sec)
Show list of existing databases
SHOW databases;
Return value:
| Databases |
| information_schema |
| mydb |
2 rows in set (0.00 sec)
You can think of “information_schema” as a “master database” that provides access to database metadata.
Creating user
First you need to create a user, and then give the user permissions on certain databases/tables. While creating the user, you also need to specify where this user can connect from.
CREATE USER 'user'@'localhost' IDENTIFIED BY 'some_password';
Will create a user that can only connect on the local machine where the database is hosted.
CREATE USER 'user'@'%' IDENTIFIED BY 'some_password';
Will create a user that can connect from anywhere (except the local machine).
Example return Value:
Query OK, 0 rows affected (0.00 sec)
Grant the user privileges:
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' WITH GRANT OPTION;
The *.* should be replaced with the database and table in the form database.table if the user should only be allowed the privileges on that specific table/database.
The WITH GRANT OPTION can be left out if the user should not be able to grant other users privileges.
Privileges can be one of or a combination of: (comma separated)
ALL
SELECT
INSERT
UPDATE
DELETE
CREATE
DROP
Piyush Gupta Blog 