Getting started with MySQL Introduction Topic

Examples

• 11

  Getting Started

  edit move example delete share

  11

  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", "[email protected]" );
  

  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', '[email protected]' );
  

  ---

  Selecting rows based on conditions in MySQL

  SELECT * FROM mytable WHERE username = "myuser";
  

  Return value:

  +----+----------+---------------------+
  | id | username | email               |
  +----+----------+---------------------+
  |  1 | myuser   | [email protected]  |
  +----+----------+---------------------+
  

  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
  

  ---

  edited Jan 19 at 17:03

  

  

  

  

  

  +9