DATA BASE - MySQL Server

What is SQL?

- SQL stands for Structured Query Language.

DATA BASE

- A database is a collection of data that is organized in a manner that facilitates easy access, as well as efficient management and updating.

- A database is made up of tables that store relevant information.

Let's See How can we connect with the database using CLI.

- Here I am using an XMPP server so I am going to connect the MySQL server through XAMPP.

- Before we proceed make sure your Apache and MySQL services must be started.

- You can see the web view of the server  - "http://localhost:8080/phpmyadmin/".

Let's started  -

1.    Open CMD Prompt and type cd\ to come to parent drive i.e C:\

2.    Type "cd xampp/mysql/bin" to come to the SQL folder to run MySQL.

3.    To execute the server type the command: "mysql -u root -p"

    Here,

• "mysql" is the server (data management).
• -u means username and root is the user's name.
• -p for password    

    and then enter your password if you have or hit enter remain the password as blank.

You will directly connect to the server of MySQL and if not like mine you as you can on above image, now i am connected to the MySQL by typing connect mysql:

CREATE A DATABASE

- As you can see by default there some databases.

- To display databases type the command: show databases; [as per above image]

Note: Command end with semicolon(;).

Lets create a database: -

To create a database type the cmd - "create database database_name;"

To use the the database "jyoti" type :   "use database_name;"

                                                        

DDL STATEMENTS

- Create Table and Describe Tables


CREATE TABLE

create table anil(Enroll int, Name varchar(6), branch char(3));

ALTER TABLE

Add new field

alter table anil add result int;

alter table anil add Sl_No int first;

alter table anil add Remarks int after result;

Drop a column

Alter table table_name drop column_name;

Alter table table_name drop column_name, drop column_name;


Delete a database

DROP DATABASE jyoti;

MODIFY, CHANGE, RENAME

Change the data type of existing column.

alter table table_name modify column_name data_type;

alter table table_name change column_name new_column_name data_type;

alter table table_name rename new_name;


ADDING DATA TO TABLE IN A EXISTING DATABASE


insert into anil (Sl_No, Enroll, Name) values ('01', '17968475', 'Jyoti Bhusan Raul');


update anil set Name="Jyoti Bhusan Raul" where Sl_No=1;


DELETE ROW TABLE


DELETE FROM TABLE_NAME WHERE ID=1;


DELETE COLUMN

alter table anil drop Username;


DELETE DATABASE

DROP DATABASE `test`;


select * from anil LILMIT 1;

select * from anil where Password="pass";

select * from anil where in ("pass");

select distinct Username,Password from anil;


--DATA TYPES--

1. int

2. deciaml (WIDTH,DECIMAL)

Ex. price decimal(7,2)

3. char and varchar

4. Date