Home

MySQL

Introduction to MySQL(Database)

Copy

What is database?

It is a collection of data.
A method for accessing and manipulating that data.
A structured set of computerized data with an accessible interface.

Difference between database and database management system:-

Database is a gigantic collection of data.
DBMS allows to interact with database.

Difference between SQL and MySQL:-

SQL stands for Structured Query Language. SQL is the language we use

to "talk" to our databases(Insert, Delete, Update, etc...). Working

with MySQL is primarily writing SQL.

There are n numbers of Relational Databases(RDBMS) who uses SQL:

MySQL
SQLite
PostgreSQL
Oracle
Many Others

MySQL Commands:-

show databases;

This commands will show you total databases present in your database.

for example:

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

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

6 rows in set (0.00 sec)

SELECT database;

SELECT is used to retrieve rows selected from one or more tables, and

can include UNION statements and subqueries. See [HELP UNION], and

https://dev.mysql.com/doc/refman/8.0/en/subqueries.html. A SELECT

statement can start with a WITH clause to define common table

expressions accessible within the SELECT. See

https://dev.mysql.com/doc/refman/8.0/en/with.html.

The most commonly used clauses of SELECT statements are these:

o Each select_expr indicates a column that you want to retrieve. There

must be at least one select_expr.

o table_references indicates the table or tables from which to retrieve

rows. Its syntax is described in [HELP JOIN].

o SELECT supports explicit partition selection using the PARTITION

clause with a list of partitions or subpartitions (or both) following

the name of the table in a table_reference (see [HELP JOIN]). In this

case, rows are selected only from the partitions listed, and any

other partitions of the table are ignored. For more information and

examples, see

https://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html.

o The WHERE clause, if given, indicates the condition or conditions

that rows must satisfy to be selected. where_condition is an

expression that evaluates to true for each row to be selected. The

statement selects all rows if there is no WHERE clause.

In the WHERE expression, you can use any of the functions and

operators that MySQL supports, except for aggregate (group)

functions. See

https://dev.mysql.com/doc/refman/8.0/en/expressions.html, and

https://dev.mysql.com/doc/refman/8.0/en/functions.html.

SELECT can also be used to retrieve rows computed without reference to

any table.

URL: https://dev.mysql.com/doc/refman/8.0/en/select.html

> select @@hostname;

This command tells the hostname i.e. username of your database. For example:

mysql> select @@hostname;

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

| @@hostname |

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

| Aakash |

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

1 row in set (0.00 sec)

mysql> CREATE DATABASE Aakash;

Query OK, 1 row affected (0.03 sec)

CREATE DATABASE creates a database with the given name. To use this

statement, you need the CREATE privilege for the database. CREATE

SCHEMA is a synonym for CREATE DATABASE.

Deleting Database:-

Syntax:

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Example:

mysql> DROP DATABASE aakash;

Query OK, 0 rows affected (0.08 sec)

DROP DATABASE drops all tables in the database and deletes the

database. Be very careful with this statement! To use DROP DATABASE,

you need the DROP privilege on the database. DROP SCHEMA is a synonym

for DROP DATABASE.

Note:-

When a database is dropped, privileges granted specifically for the

database are not automatically dropped. They must be dropped manually.

See [HELP GRANT].

IF EXISTS is used to prevent an error from occurring if the database

does not exist.

Select a database to use:-

Syntax:

USE db_name

Example:

mysql> use my_db;

Database changed

The USE statement tells MySQL to use the named database as the default

(current) database for subsequent statements. This statement requires

some privilege for the database or some object within it.

The named database remains the default until the end of the session or

another USE statement is issued:

USE db1;

SELECT COUNT(*) FROM mytable; # selects from db1.mytable

USE db2;

SELECT COUNT(*) FROM mytable; # selects from db2.mytable

The database name must be specified on a single line. Newlines in

database names are not supported.

mysql> select database();

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

| database() |

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

| my_db |

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

1 row in set (0.00 sec)

This command tells about the database which is currently using.

If you delete your current working database then run this command to lookout your database you will find empty database.

mysql> select database();

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

| database() |

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

| NULL |

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

1 row in set (0.00 sec)

Tables:-

A database is just a bunch of tables.

TABLE is a DML(Data Manipulation Language) statement introduced in MySQL 8.0.19 which returns rows

and columns of the named table.

Syntax:

TABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]

The TABLE statement in some ways acts like SELECT. Given the existance

of a table named t, the following two statements produce identical

output:

TABLE t;

SELECT * FROM t;

TABLE differs from SELECT in two key respects:

o TABLE always displays all columns of the table.

o TABLE does not allow for any arbitrary filtering of rows; that is,

TABLE does not support any WHERE clause.

For limiting which table columns are returned, filtering rows beyond

what can be accomplished using ORDER BY and LIMIT, or both, use SELECT.

TABLE can be used with temporary tables.

URL: https://dev.mysql.com/doc/refman/8.0/en/table.html

Data Types:-

AUTO_INCREMENT
BIGINT
BINARY
BIT
BLOB
BLOB DATA TYPE
BOOLEAN
CHAR
CHAR BYTE
DATE
DATETIME
DEC
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INTEGER
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMTEXT
SET DATA TYPE
SMALLINT
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYTEXT
VARBINARY
VARCHAR
YEAR DATA TYPE

Creating Table:-

Note:- Table name must be pluralized

CREATE TABLE creates a table with the given name. You must have the

CREATE privilege for the table.

By default, tables are created in the default database, using the

InnoDB storage engine. An error occurs if the table exists, if there is

no default database, or if the database does not exist.

MySQL has no limit on the number of tables. The underlying file system

may have a limit on the number of files that represent tables.

Individual storage engines may impose engine-specific constraints.

InnoDB permits up to 4 billion tables.

Example:

mysql> CREATE TABLE cats (

-> name VARCHAR(100),

-> age INT

-> );

Query OK, 0 rows affected (0.11 sec)

This is how you will create database, don't think much about spaces it does not matter at all. for readability I write this way you can choose your one.

Now we have created table but how to check?

mysql> show tables;

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

| Tables_in_cats_janamkundali |

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

| cats |

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

1 row in set (0.04 sec)

This is one way to find your table.

mysql> show columns from cats;

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

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

| age | int | YES | | NULL | |

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

2 rows in set (0.05 sec)

This is another way to access the table.

mysql> DESC cats;

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

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

| age | int | YES | | NULL | |

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

2 rows in set (0.04 sec)

This is also the same as above but more short and easy to understand.

Deleting Tables:-

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

[RESTRICT | CASCADE]

DROP TABLE removes one or more tables. You must have the DROP privilege

for each table.

Be careful with this statement! For each table, it removes the table

definition and all table data. If the table is partitioned, the

statement removes the table definition, all its partitions, all data

stored in those partitions, and all partition definitions associated

with the dropped table.

Dropping a table also drops any triggers for the table.

DROP TABLE causes an implicit commit, except when used with the

TEMPORARY keyword. See

https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html.

Note:-

When a table is dropped, privileges granted specifically for the table

are not automatically dropped. They must be dropped manually. See [HELP

GRANT].

If any tables named in the argument list do not exist, DROP TABLE

behavior depends on whether the IF EXISTS clause is given:

o Without IF EXISTS, the statement fails with an error indicating which

nonexisting tables it was unable to drop, and no changes are made.

o With IF EXISTS, no error occurs for nonexisting tables. The statement

drops all named tables that do exist, and generates a NOTE diagnostic

for each nonexistent table. These notes can be displayed with SHOW

WARNINGS. See [HELP SHOW WARNINGS].

IF EXISTS can also be useful for dropping tables in unusual

circumstances under which there is an entry in the data dictionary but

no table managed by the storage engine. (For example, if an abnormal

server exit occurs after removal of the table from the storage engine

but before removal of the data dictionary entry.)

The TEMPORARY keyword has the following effects:

o The statement drops only TEMPORARY tables.

o The statement does not cause an implicit commit.

o No access rights are checked. A TEMPORARY table is visible only with

the session that created it, so no check is necessary.

Including the TEMPORARY keyword is a good way to prevent accidentally

dropping non-TEMPORARY tables.

The RESTRICT and CASCADE keywords do nothing. They are permitted to

make porting easier from other database systems.

DROP TABLE is not supported with all innodb_force_recovery settings.

See

https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html.

URL: https://dev.mysql.com/doc/refman/8.0/en/drop-table.html

Example:

mysql> drop table cats;

Query OK, 0 rows affected (0.07 sec)

To check you can use above methods or commands:

mysql> show tables;

Empty set (0.00 sec)

If you want more briefs about each queries then in MySQL command type help WHAT_YOU_WANT_TO_KNOW and enter you will get brief about your topic.

Most of them were from there only in this blog so you can get genuine definitions.

CodeWithDevops

Introduction to MySQL(Database)

Post a Comment

CodeWithDevops