MySQL

How to write MySQL queries in command prompt:-

********** First start XAMPP's- Apache & MySQL***********

1st. go to this path like above pic----> C:\xampp\mysql\bin>

2nd. write this -----> mysql -u root -p -h localhost

3rd.---> there is no need to write any password

4th. you can see this prompt -- mysql>

5th. write this command---> show databases;

6th. you can see all the DataBase same as above pic.

7th. write this command --> use demo; [my created database name is demo, you should write your own DataBase name]

8th. mysql> select * from emp; 

you can see all the data from emp table :)

In Brief:-

write all these commands step by step:

1. C:\xampp\mysql\bin>mysql -u root -p -h localhost  [no need ;] 

[user name-root, password-null, host- localhost]

2. show databases;

3. use demo; [demo is database name]

4. select * from emp; [emp table name]

5. describe demo.emp; [see table structure]

https://www.mysql.com/

https://www.w3schools.com/sql/default.asp      [for tutorial]

mysql> help List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. resetconnection(\x) Clean session context. For server side help, type 'help contents'

***************************************************************************

Set language à utf8_unicode_ci [Multilanguage]

.frm à table structure FoRMat

.ibd à Data of the Table InnoDB

RDBMS à Relational Data Base Management System

Oracle, Microsoft SQL Server, MySQL (Open Source)

SQL à Structured Query Language

SQL is language of RDBMS

Category of Statement

1.       DDL (Data Definition Language)

2.       DML (Data Manipulation Language)

DDL:

Create an Object, deals with object

i>                   CREATE table, data base.

ii>                 ALTER table, Database.

iii>                DROP table, Database.

iv>               RENAME table, Database.

DML:

Related to Data.

i>                   INSERT statement.

ii>                 UPDATE statement.

iii>                DELETE statement.

Create a Database :

                CREATE DATABASE demo;

ADDING new column in table:

                ALTER TABLE emp ADD mobile VARCHAR(10);

MODIFY:

ALTER TABLE emp MODIFY mobile VARCHAR(15), MODIFY email VARCHAR(60);

CHANGE column Name:

ALTER TABLE emp CHANGE email email_id VARCHAR(60)

DESC à Reserved Keyword

REMOVING column:

                ALTER TABLE emp DROP column salary, DROP column email;

RENAMING table:

                ALTER TABLE emp RENAME TO employee;

DROPPING a Table:

                DROP TABLE employee;

DROP DATABASE database_name;

QUERY PART:-

 INSERT INTO table_name(fname,lname,salary,email) VALUES (‘Ajay’,’Kumar’,4000,’ajay@gmail.com’), (‘Bijay,’Singh’,9000,’bijay@gmail.com’);

SELECT column_name FROM table_name;

ORDERBY ASC(Default) DESC

DISTINCT

CONDITION/ FILTER:

WHERE  state = ‘MP’

                Salary >3000

LOGICAL OPERATOR:

NOT, AND, OR

COMPARISON OPERATOR:

=

!=

< 

> 

<=

>=

Between

In

BETWEEN:

                WHERE salary BETWEEN 2000 AND 5000; [inclusive, range >=2000 <=4000]

IN:

                WHERE state IN (‘AP’,’MP’,’WB’);   [similar OR operator]

FUNCTIONS:

                AVG()

                MIN()

    MAX()

    SUM()

SELECT  AVG (salary) AS “Average Salary” [column alias]

LIMIT: [one of use Pagination]

                SELECT * FROM employee LIMIT 2;

               

Offset	Id
0	1
1	2

                By default offset 0;

                LIMIT 4(Offset),1(How many row)

DML:

UPDATE  [means transaction] Admin rollback the previous value

                UPDATE emp SET salary = 8000, email = ‘abc@gmail.com’ WHERE ID=2;

                UPDATE emp SET salary = 8000, email = ‘abc@gmail.com’ WHERE name=’Amit’;

               

DELETE:

                DELETE FROM table_name;

                DELETE FROM emp WHERE id=5;

                DELETE FROM emp WHERE salary=4000;

TRUNCATE: [not a DML statement, can’t rollback previous data]

                TRUNCATE table_name;

                TRUNCATE emp; equivalent to DELETE FROM emp;

                Delete whole Records

                Empty the Table,

                We can’t apply condition

Every DML statement has the Roll Back property.

NULL à nothing is there

UPDATE emp SET email=NULL WHERE id=3;

PRIMARY KEY:

                NOT NULL,

                NON- Repeating

Q. How to find all column having NULL values

                SELECT * FROM emp WHERE mobile ISNULL;

Q. To find RECORDS HAVING NOT-NULL values.

                SELECT * FROM emp WHERE mobile ISNOT NULL;

Q. Find the record having Highest salary?

                SELECT * FROM emp ORDERBY salary DESC LIMIT 0,1;

Q. 2^nd Highest Salary.

                SELECT * FROM emp ORDERBY salary DESC LIMIT 1,1;

               

                LIMIT 0,2 0à offset, where to start 2àno. of records

GROUPBY-Clause:

                [WHERE clause BEFORE GROUPBY ]

Q. Fetch total salary gives to each department.

Q. Fetch total salary given to HR Department?

Q. Fetch total salary given to each department for the MGR position only.

SELECT dept, SUM (salary) FROM user GROUPBY dept. HAVING SUM(salary)>10,000;

HAVINGàsame as WHERE

SELECT dept, SUM(salary) AS “Total Salary” FROM user WHERE POSITION=’MGR’ GROUPBY dept HAVING SUM(salary)>10,000;

MIN()

MAX()

AVG()

COUNT()

JOIN:

1.       INNER JOIN      2. OUTER JOIN ài)LEFT JOIN(Left Outer Join), ii) RIGHT JOIN (Left Outer Join)

INNER JOIN:

i)                    SELECT * FROM dept(left table) INNER JOIN emp(right table) ON dept.dept_id = emp.dept_id;

ii)                   SELECT * FROM dept JOIN emp ON dept.dept_id = emp.dept_id;

iii)                 SELECT * FROM dept, emp WHERE dept.dept_id = emp.dept_id; [SELF JOIN]

  2.i) LEFT JOIN :

a)      SELECT * FROM dept LEFT JOIN emp ON dept.dept_id = emp.dept_id;

b)      SELECT * emp.name, dname FROM dept LEFT JOIN emp ON dept.dept_id = emp.dept_id;

c)       SELECT  e.name, d.name FROM dept d LEFT JOIN emp e (table alias) ON d.dept_id =e.dept_id;

          2.ii)RIGHT JOIN : (not use in real time)

a)      SELECT * FROM  dept(left table) RIGHT JOIN emp(right table) ON emp.dept_id = dept.dept_id;

b)      SELECT * FROM emp LEFT JOIN dept ON dept.dept_id = emp.dept_id;

INNER TABLE:

                Returns records that have matching values in both tables.

LEFT (OUTER JOIN):

                Returns all records from the LEFT TABLE and matching records from the RIGHT TABLE.

RIGHT (OUTER JOIN):

                Returns all records from the RIGHT TABLE and matching records from the LEFT TABLE.