CBSE CS and IP: MySQL

Showing posts with label MySQL. Show all posts

MYSQL ORDER BY

MYSQL ORDER BY clause

When you use the SELECT statement to query data from a table, the result set is not ordered. It means that the rows in the result set can be in any order.

To sort the result set, you add the ORDER BY clause to the SELECT statement.

The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.

The syntax for using ORDER BY -:

SELECT expressions

FROM tables

[WHERE conditions]

[GROUP BY <COL_NAME>

HAVING <CONDITION ON GROUPS>]

ORDER BY COL1,COL2,.. [ ASC | DESC ];

ASC –:

Ascending order by COLUMNS [Default]

DESC – :

Descending order by COLUMNS

Note -:

ORDER BY clause is always evaluated after the FROM and SELECT clause.

MYSQL GROUP BY and HAVING

cbsecsip Class 12 CS , Class 12 IP

MySQL GROUP BY

The GROUP BY statement in SQL is used to arrange data into groups with the help of some functions. GROUP BY statement is used to retrieve grouped records based on one or more columns.

The aggregate function on SELECT Statement can be used with the GROUP BY Statement.

Aggregate Functions : AVG(),COUNT(),MAX(),MIN(),SUM().

The syntax for GROUP BY Statement in SQL -:

SELECT <COL_NAME>

FROM <TABLE>

WHERE <CONDITION>

GROUP BY <COL_NAME>

HAVING <CONDITION ON GROUPS>;

Description MySQL GROUP BY -:

The GROUP BY clause combines all those records (Rows) that have identical values in a particular field (Column) or a group of fields.

This grouping results in one summary record per group.

If you want to total salary for all employees then we used the SUM function and you will get a sum of the total salary.

If you want to total salary department wise like D1 or D2 then we have used GROUP BY.

Now you have also used the SUM function and using GROUP BY tHen you will get the department-wise total salary You can see all D1 and D2 columns combine in one row.

GROUP BY(Nested Grouping) using more than one column -

If you want to GROUP BY using more than one column, you can give all columns by using comma separation.

Syntex -

SELECT Col1, Col2,AGG_FUN(Col3)

FROM Tab1

[WHERE <Condition>]

GROUP BY Col1, Col2 ;

GROUP BY Condition -

The GROUP BY clause is used with Aggregate Functions.

All the fields (Columns) other than used in aggregate function should be put in GROUP BY clause.

Syntex -

SELECT AGG_FUN(Col1), Col2, Col3

FROM Tab1

WHERE <Condition>

GROUP BY Col2, Col3;

MySQL Aggregate Functions

cbsecsip Class 12 IP , MySQL

An aggregate function performs a calculation on multiple values and returns a single value.

1. MAX ()

MAX function returns the maximum value of an expression.

Syntax:

SELECT MAX(aggregate_expression)

FROM tables

[WHERE conditions];

Parameter or Arguments:

aggregate_expression: This is the column or expression from which the maximum value will be returned.

2. MIN ()

MIN function returns the minimum value of an expression.

Syntax:

SELECT MIN(aggregate_expression)

FROM tables

[WHERE conditions];

Parameter or Arguments:

aggregate_expression: This is the column or expression from which the minimum value will be returned.

3. AVG ()

AVG function returns the average value of an expression.

Syntax:

SELECT AVG(aggregate_expression)

FROM tables

[WHERE conditions];

Parameter or Arguments:

aggregate_expression: This is the column or expression that will be averaged.

4. SUM ()

SUM function returns the summed value of an expression.

Syntax:

SELECT SUM(aggregate_expression)

FROM tables

[WHERE conditions];

Parameter or Arguments:

aggregate_expression: This is the column or expression that will be summed.

5. COUNT () and COUNT (*)

COUNT function returns the count of an expression.

Syntax:

SELECT COUNT(aggregate_expression)

FROM tables

[WHERE conditions];

Parameter or Arguments:

aggregate_expression: This is the column or expression whose non-null values will be counted.

MySQL Date Functions

cbsecsip Class 12 IP , MySQL

DAYNAME function returns the weekday name for a date.

Syntax:

DAYNAME( date_value )

Parameter or Arguments:

date_value: The date or datetime value from which to extract the weekday name.

MySQL Text Function

cbsecsip Class 12 IP , MySQL

For preforming the operations on strings, MySQL has provided some function:

1. UCASE ()/UPPER ()

It converts all characters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

Syntax:

UCASE( string )

Parameter or Arguments:

string: The string to convert to uppercase.

2. LCASE ()/LOWER ()

It converts all characters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

Syntax:

LCASE( string )

Parameter or Arguments:

string: The string to convert to lowercase.

3. MID ()/SUBSTRING ()/SUBSTR ()

This function allows you to extract a substring from a string.

Syntax:

MID( string, start_position, [length] )

SUBSTR( string, start_position, [ length ] )

SUBSTRING( string, start_position, [ length ] )

Parameter or Arguments:

string: The string from which Substring is required.

start_position: The position to begin extraction. The first position in the string is always 1.

Length: The number of characters to extract

LTRIM function removes all space characters from the left-hand side of a string.

Syntax:

LTRIM( string )

Parameter or Arguments:

string: The string to trim the space characters from the left-hand side.

9. RTRIM ()

RTRIM function removes all space characters from the right-hand side of a string.

Syntax:

RTRIM( string )

Parameter or Arguments:

string: The string to trim the space characters from the right-hand side.

10. TRIM ()

TRIM function removes all specified characters either from the beginning or the end of a string.

Syntax:

TRIM( [ LEADING | TRAILING | BOTH ] [ trim_character FROM ] string )

Parameter or Arguments:

LEADING: Optional. Removes the trim_character from the front of the string.

TRAILING: Optional. Removes the trim_character from the end of the string.

BOTH: Optional. Removes the trim_character from the front and end of string.

trim_character: Optional. The character that will be removed from the string. If this parameter is omitted, it will remove space characters from the string.

string: The string to trim.

MySQL Math Function

cbsecsip Class 12 IP , MySQL

MySQL provides different Mathematics functions to perform Math operations. In your syllabus we have to learn these three functions:

1. POWER ( ) or POW( )

The MySQL POWER function returns m raised to the nth power. (mⁿ)

Ex:- 3² = 9

Syntax:

POWER( m, n ) OR POW( m, n)

Parameters or Arguments:

m: Numeric value. It is the base used in the calculation.

n: Numeric value. It is the exponent used in the calculation.

2. ROUND ( )

Returns a number rounded to a certain number of decimal places.

– Ex:- 12.265 rounded to 2 decimal points 12.27 .

Syntax:

ROUND( number, [ decimal_places ] )

Parameters or Arguments:

number: The number to round.

decimal_places: The number of decimal places to round. This value must be a positive or negative integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places.

3. MOD ( )

Returns the remainder of n divided by m.

– Ex:- 5 % 2 = 1

Syntax:

MOD( n, m ) OR n MOD m OR n % m

Parameters or Arguments:

n: Number.

m: Devisor.

Python MySQL Connector - Download, Install and Use with Examples

cbsecsip Class 12 CS , MySQL

When you want to create a real-life application it is required to interact with Database through your program. This can be done through an interface, which provides you with the facility to connect to Database directly through your Python program. As per class 12 CBSE computer science, it is required that a student must know the python and MySQL connection for the project creation.

Prerequisite

Python and MySQL both should be installed on your computer.

To know how to install Python: Click Here
To know how to install MySQL watch the following Video:

After completing the above two steps follow below-given 6 steps to connect your python program with MySQL and start working on it:

STEP-1: Start Python

The First step is to start Python. Create a ".py" file in which you want to write the python code for MySQL connection.

STEP-2: install and import mysql-connector

The next step is to install mysql-connector. mysql-connector is available for Python that is used for connecting the python program with the MySQL Database.
This connector can be installed in python using the following command (run it in cmd):
pip install mysql-connector
If you are facing any error while using this command click here to resolve your environment Path related error. After that, you will not face any problem.
After installing the mysql-connector, import it in your program by using the following command:
import mysql.connector
or
import mysql.connector as sql

STEP-3: Connect Python with MySQL database using connect()

connect() function is used to make the connection from python to MySQL and returns a connection object.
It is available inside mysql.connector module. Hence to use this function we have to import mysql.connector.
Prototype of connect( ) Function:
<conn_obj> = <mysql.connector>.connect(host="localhost", user="yourusername", password="yourpassword", database ="test“)
You can check for successful connection using is_connected( ) Function with connected Object.
```
if <conn_object>.is_connected()==False:
    print("Not Conneted")
```

STEP-4: Create a Cursor instance using cursor()

A Database Cursor is a special control structure that facilitates the row by row processing of records in the result set.

<cursor_obj> = <conn_obj>.cursor()

STEP-5: Execute SQL Query by Using execute( ) Function

Now you can execute MySQL Queries using execute( ) Function with cursor object as per following syntax:

<cursor_obj>.execute(<sql query string>)

The retrieved record will be available in cursor Object.

STEP-6: Close the Connection

Use the following command to close the connection:

<conn_obj>.close()

Full code for Python-MySQL Connection

##MYSQL connection with python 
import mysql.connector as sql
con = sql.connect(host='127.0.0.1',user='root',passwd='password')
if con.is_connected() == True:
    mycursor = con.cursor()
    mycursor.execute("create database student")
    mycursor.execute("use student")
    mycursor.execute("create table xiicom (name varchar(20))")
    for i in range(5):
        name = input("Enter a Name:")
        s3 = "insert into xiicom values ('{}')".format(name)
        mycursor.execute(s3)
    con.commit()
    con.close()
else:
    print("Not Connected")

[SOLVED] mysql.connector.errors.NotSupportedError while executing Python Program

cbsecsip MySQL ,

Are you facing error "mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported" or "authentication plugin 'caching_sha2_password' is not supported workbench" while executing the Python program and trying to connect the Python program with MySQL.

Follow the below steps to remove the error:

Step 1: Go To Start and Search MySQL

Authentication plugin 'caching_sha2_password' is not supported

Step 2: Inside MySQL click on MySQL Installer Community.

Step 3: Now click on Reconfigure on MySQL Server

Step 4: In the High Availability Menu click on "standalone MySQL Server / Classic MySQL Replication" and click Next

Step 5: Click Next on Type and Networking Menu

Step 6: Now on Authentication Method choose Use Legacy Authentication Method (Retain MySQL 5.x Compatibility) and click Next

Step 7: On Account and Roles give the Root Password of your MySQL and click check

Step 8: ITS DONE. NOW CHECK YOUR PYTHON CODE. IT WILL NOT HAVE ANY ERROR.

MYSQL ORDER BY clause

The syntax for using ORDER BY -:

Note -:

MySQL GROUP BY

The syntax for GROUP BY Statement in SQL -:

Description MySQL GROUP BY -:

GROUP BY(Nested Grouping) using more than one column -

Syntex -

GROUP BY Condition -

Syntex -

1. MAX ()

2. MIN ()

3. AVG ()

4. SUM ()

5. COUNT () and COUNT (*)

1. NOW ()

2. DATE ()

3. MONTH ()

4. MONTHNAME ()

5. YEAR ()

6. DAY ()

7. DAYNAME ()

1. UCASE ()/UPPER ()

2. LCASE ()/LOWER ()

3. MID ()/SUBSTRING ()/SUBSTR ()

4. LENGTH ()

5. LEFT ()

6. RIGHT ()

7. INSTR ()

8. LTRIM ()

9. RTRIM ()

10. TRIM ()

1. POWER ( ) or POW( )

2. ROUND ( )

3. MOD ( )

Prerequisite

STEP-1: Start Python

STEP-2: install and import mysql-connector

STEP-3: Connect Python with MySQL database using connect()

STEP-4: Create a Cursor instance using cursor()

STEP-5: Execute SQL Query by Using execute( ) Function

STEP-6: Close the Connection

Full code for Python-MySQL Connection

Follow the below steps to remove the error:

Total Visitors

Subscribe Us

Categories

Recent

Featured

Blog Archive

Recent Post

Tags

Recent Comments