MY SQL DATABASE

My SQL Database

It is an organized collection of data so that it can be easily accessed. To mannage these database, DBMS are used.

Type of DBMS

1. Relational DBMS
2. Non-Relational DBMS

Relational DBMS: In this, data will be stored in table format.

Example: MYSQL, Oracle

Non-Relational DBMS: In this, data will be stored in key-value pair.

like:

{

“RollNo”:1,

“Name”: “abc”,

“Class”: 2

}

Example: MongoDB, Redis

SQL: 

It stand for structure query language. It is used for update, delete, insert the datain table.

Data Type in MYSQL:

1. Numeric Data Types

INT: Integer type, ranges from -2147483648 to 2147483647.

FLOAT: Floating-point number, with optional precision.

DOUBLE: Double-precision floating-point number.

DECIMAL: Fixed-point number with defined precision and scale.

TINYINT: Small integer, ranges from -128 to 127.

SMALLINT: Smaller integer, ranges from -32768 to 32767.

MEDIUMINT: Medium-sized integer, ranges from -8388608 to 8388607

2.String Data Types

CHAR: Fixed-length character string.

VARCHAR: Variable-length character string.

TEXT: Large string data.

TINYTEXT: Smaller string data.

MEDIUMTEXT: Medium-sized string data.

LONGTEXT: Very large string data

SQL commands:

1. SQL CREATE command: Used for creating the tables.

Syntax:

CREATE TABLE table_name(

column1 datatype()

column1 datatype()

);

2. SQL INSERT INTO command: Used to insert the data into table.

Syntax:

INSERT INTO table_name ( column1, column2)

VALUES (value1, value2);

(value3, value4)

Note: A row of database table is known as record or a tuple.

Note: A column of database table is known as an attribute.

3. SQL SELECT command: Used to retrieves data from the table.

Syntax:

SELECT column1, column2

FROM table_name;

Note: To select complete table,* will be used.

Syntax:

SELECT *

FROM table_name

Constraints in SQL:

Constraints are the rules and restrictions applied on the data in table.

NOT NULL: value can’t be empty in a column.

UNIQUE: Value can’t be same in a column.

PRIMARY KEY: Used to uniquely identify a row.

FOREIGN KEY: References a row in another table.

CHECK: Satisfies a specific condition.

DEFAULT: Set default value.

CREATE INDEX: Used to speedup the read process.

SQL Query execution order: FROM--->JOIN--->WHERE--->GROUP BY---->HAVING---->SELECT--------->ORDER BY----->LIMIT

SQL SELECT DISTINCT: Used to return only unique values from a specified column in a table.

Syntax:

SELECT DISTINCT column_name

FROM table_name

SQL WHERE CLAUSE: Used to filter row in table based on a specified condition.

Syntax:

SELECT column1, column2

FROM table_name

WHERE condition

Example: nitin_table

SELECT Firstname, Lastname

FROM nitin_table

WHERE Age>18

OUTPUT:

def singh

SQL AND: It returns true if both condition are true and otherwise it will return the false.

Syntax: WHERE condition1 AND condition2

SQL OR: It return true if either condition is true and will return false if both condition are false.

Syntax: WHERE condition1 OR condition2

SQL NOT: It returns the opposite of condition.

Syntax: WHERE NOT condition

SQL UPDATE: Used to modify the existing data in table.

Syntax:

UPDATE table_name

SET column1=value1, column2=value2

WHERE same_column=same_value;

Note: SET is used to specify the column and values to update.

Example: nitin_table

UPDATE nitin_table

SET Age=Age+1;

OUTPUT:

SQL DELETE: Used to remove the existing record from a table in a SQL database.

Syntax:

DELETE FROM table_name

WHERE condition;

SQL DROP TABLE: To delete the particular table.

Syntax:

DROP TABLE table_name;

Basics commands of MYSQL database;

To login into mysql through the local host-----> mysql –u user_name -p

To login into mysql through the another host-----> mysql –u user_name –h database_ip –p

To list all databases in MYSQL---> SHOW DATABASES;

To select any database ------> USE database_name

To show the tables in the selected database------> SHOW TABLES;

To get the structure of a specific table---> DESCRIBE table_name;

To crate database----> CREATE DATABASE database_name

To create the user in mysql: CREATE USER ‘user_name ‘@’ localhost’ IDENTIFIED BY 'password'; # localhost:-hostname where the user is allowed to connect from localhost only.

CREATE USER ‘user_name ‘@’ %’ IDENTIFIED BY ‘password’

To grant the necessary priviliges to the user:

GRANT ALL PRIVILEGES ON database_name.* TO ‘user_name ‘@’ localhost’;

 Npte: %:- Allow the user to connect from any host.

GRANT ALL PRIVILEGES ON database_name.* TO ‘user_name ‘@’ %’;

To grant the super privileges to user:

GRANT SUPER ON *.* TO ‘user_name ‘@’ localhost’;

To apply the changes after giving the privileges----->FLUSH