One of the most widely used and adaptable database management system platforms is MySQL. Regardless of your position in the database industry or amount of development experience, it is imperative that you are familiar with MySQL and its querying features. The fundamentals of MySQL querying will be covered in this tutorial, along with easy to use techniques for connecting to your databases.

A few simple SQL queries
CHOOSE Information from one or more columns of a table can be obtained using this technique. You enter the desired columns, separated by commas, after the SELECT keyword.

SELECT column1, column2 FROM tablename;
SELECT * FROM tablename;

WHERE Records can be filtered using this clause depending on specific requirements. You can only obtain the rows that match the given criteria.
SELECT *FROM tablename
WHERE columnname = value;

INSERT To add new records (rows) to a table, use this statement. The values you wish to add to each column and the name of the table are also specified.

INSERT INTO tablename (column1, column2)
VALUES (value1, value2);

UPDATE A table's existing records can be changed using this statement. The table name, the columns you wish to change, and the new values are all specified.
UPDATE tablename
SET column1 = newvalue
WHERE condition;

DELETE  Depending on specific requirements, this statement is used to remove records from a table. You can use it to remove particular rows that fit the requirements.
DELETE FROM tablename
WHERE condition;

JOIN This joins rows from one or more tables together according to a shared column. With just one query, you can obtain data from several tables.
SELECT *
FROM table1
JOIN table2 ON table1.columnname = table2.columnname;

GROUP BY Rows with the same values are grouped together into summary rows using this clause. It is frequently used to calculate values on grouped data together with aggregate functions such as COUNT, SUM, AVG, etc.
SELECT columnname, COUNT(*)
FROM tablename
GROUP BY columnname;

ORDER BY Depending on one or more columns, this clause can be used to sort the result set either ascending (ASC) or descending (DESC).
SELECT *
FROM tablename
ORDER BY columnname ASC;

LIMIT This has the function of limiting how many rows are returned in a result set. It is frequently used to increase query efficiency by obtaining only a subset of rows, or for pagination.
SELECT *
FROM tablename
LIMIT 10;

DISTINCT This term pulls distinct rows out of a column. Duplicate rows are removed from the result set.
SELECT DISTINCT columnname
FROM tablename;

Data Manipulation Functions
COUNT This function counts the number of rows that satisfy a certain criteria or the total number of rows in a table.
SELECT COUNT(*)
FROM tablename;

MAX/MIN The maximum and minimum values from a column can be obtained, respectively, using these functions.
SELECT MAX(columnname), MIN(columnname)
FROM tablename;

AVG The average value of a numeric column can be determined with this function.
SELECT AVG(columnname)
FROM tablename;

SUM The total of the values in a numeric column can be determined using this function.
SELECT SUM(columnname)
FROM tablename;

Constraints
PRIMARY KEY The primary key constraint is responsible for uniquely identifying every record within a table. It guarantees that every table row has a distinct identity.
CREATE TABLE tablename (
    columnname INT PRIMARY KEY,
    ...
);

FOREIGN KEY  By referencing the primary key or unique key of another table, this constraint creates a relationship between tables.
CREATE TABLE tablename1 (
    columnname INT,
    FOREIGN KEY (columnname) REFERENCES table_name2(columnname)
);

INDEX  By based an index on one or more columns, this technique helps to speed up data retrieval operations on a table. Based on the indexed columns, it enables the database engine to find rows rapidly.
CREATE INDEX indexname
ON tablename (columnname);

Conclusion
The SQL cheat sheet offers a quick reference for effectively carrying out crucial database practices. It includes a wide range of operations, from simple commands like SELECT, INSERT, UPDATE, and DELETE for data retrieval, addition, and modification to more complex techniques like JOIN for merging data from various tables and GROUP BY for data summary. The cheat sheet also contains constraints like PRIMARY KEY, FOREIGN KEY, and INDEX, as well as data manipulation methods like COUNT, MAX, MIN, AVG, and SUM, which are essential for maintaining the accuracy of data and maximising database performance.

When filtering data based on a particular set of values, MySQL's IN operator is a potent tool. You may use it to supply a list of values to check if a column's value is present in that list. This article will examine the syntax and application of MySQL's IN operator and offer examples of its use in various contexts.

What is IN operator in MySQL?
In MySQL, the IN operator enables you to provide several values in a WHERE clause, which is shorter than using multiple OR conditions. In your database queries, you can quickly and simply specify a set of values to match against a specific column by using IN.

Syntax
SELECT columnName(s)
FROM tableName
WHERE columnName IN (value1, value2, ...);

OR
SELECT columnName(s)
FROM tableName
WHERE columnName IN (SELECT STATEMENT);

Create a table named StudentData.
CREATE TABLE StudentData (
stuDataId INT AUTO_INCREMENT,
stuId INT NOT NULL,
studentName VARCHAR(50) NOT NULL,
Address VARCHAR(50) NOT NULL,
City VARCHAR(50) NOT NULL,
Country VARCHAR(50) NOt NULL,
PRIMARY KEY(stuDataId)
);

Now, examining the StudentData table
select * from StudentData;

Using IN operator with a list of values in MySQL

In MySQL, you may define a list of values to search for in a column by using the IN operator. Instead of creating separate OR conditions for each value, you can use this to search for many values at once.

Example
SELECT * FROM StudentData
WHERE City IN ('London', 'UK');

IN in MySQL
Example shows how to filter data from MySQL's "StudentData" table using the IN operator. In this instance, the query is choosing all rows where either "Noida" or "Delhi" is the value of the "City" column.
Using the NOT IN operator with a list of values in MySQL

The NOT IN operator in MySQL is used to exclude results that match a list of given values. When you wish to exclude particular values from the results of your query, this is helpful.

Example
SELECT * FROM StudentData
WHERE City NOT IN ('London', 'UK');

IN in MySQL
The SQL query shown above makes use of MySQL's NOT IN operator. It chooses every row from the "StudentData" table that has neither "Noida" nor "Delhi" in the "City" column.
Using IN operator with a subquery in MySQL

To compare data returned by a subquery to a column in a table, the IN operator can also be used with a subquery. Parentheses surround the subquery, which is then put within the IN operator.

Example
SELECT * FROM StudentData
WHERE City IN (SELECT City FROM OtherStudentData WHERE Enrolled = 'Yes');

In this example, the subquery (SELECT City FROM OtherStudentData WHERE Enrolled = 'Yes') returns a list of all cities where students are enrolled in some other data table. This list of cities is then used in the WHERE clause with the IN operator to select all records from StudentData where the city matches any of the cities returned by the subquery.

Performance Considerations of IN Operator in MySQL
When using the IN operator in MySQL, there are some performance considerations to remember. Using IN with a large list of values can slow down the query, especially if the list is not indexed.

To optimize queries that use IN, you can consider the following strategies:
Use indexes: Indexes can greatly improve the performance of queries that use IN. Make sure to create indexes on the columns used in the IN condition.
Use EXISTS: In some cases, using EXISTS instead of IN can be more efficient. EXISTS returns true if a subquery returns at least one row, whereas IN returns true if a value matches any value in a list. EXISTS can be faster than IN because it stops evaluating the subquery when it finds a match.
Use JOINs: Another way to optimize queries that use IN is to use JOINs instead. This can be especially useful if the list of values is obtained from another table.
Limit the list of values: If possible, limit the list of values used in the IN condition. This can be done by adding additional conditions to the WHERE clause or by filtering the data before it is passed to the query.

Using these strategies, you can optimize your queries and improve the performance of your MySQL database.

To sum it up, the IN operator in MySQL is a strong tool that lets you select query results based on a set of values or a subquery. However, it's important to take into account any possible effects on query speed, especially when working with huge datasets. You may make effective and efficient use of the IN operator by following best practises.

Thanks for reading this article. I hope this helped you to grasp the topic of Use In in MySQL.

FAQs
Q. What is the difference between IN and EXISTS in MySQL?

A. The IN operator matches a column value to a list of possible values, while the EXISTS operator checks if a subquery returns any rows. IN is used to filter data based on a set of possible values, while EXISTS is used to check if data exists in another table.

Q. Can I use IN with a subquery in MySQL?
A. Yes, you can use IN with a subquery in MySQL. The subquery must return a set of values that can be matched with the column being filtered.

Q. How can I optimize queries that use the IN operator in MySQL?
A. One way to optimize queries that use IN is to ensure that the column being filtered is indexed. This can significantly improve query performance. Another strategy is to use EXISTS instead of IN if possible, as EXISTS may perform better in some cases. Additionally, it's important to use the most specific data type possible for the values being compared, as this can also improve performance.

Requirements
Microsoft SQL Server. If not installed, go to the official site and download and install the setup file.

STEP 1

    Go to Start and search for Microsoft SQL Server.
    You can find an option for Microsoft SQL Server Management Studio.
    Click on it to open the SQL Management Studio.

STEP 2

    To create a local database, you need a Server first.
    While installing the SQL Server, you would have set a user which will act as the Server.
    Select the Server and also ensure that the credentials you are providing in the authentication processes are right.
    After entering all the details, click on the "Connect" button.
    If the connection fails, check if the Server is in running state.
    If it is not running, just right click on the Local Sever and click on the Start option.
    You can find the Server in the Local Server option of the Registered Servers.
    To view the registered servers, go to VIEW -> REGISTERED SERVERS.

STEP 3

1. Now, you are connected to the Server, so can you create a database.
2. In the Object Explorer, find the Databases folder.
3. Simply right click on it and select new database option.
4. This will initiate the new database creation.

Step 4

1. You will see a window when clicked on the new database option.There, you can add the new database.
2. You can add more than one database on the same Server.
3. Click on the "Add" button to add a new database.
4. If you want to remove any database, just click on the "Remove" button.
5. Once you are finished with adding your required number of databases, click on the OK button.

So, this was the process for local database creation using MS SQL
In my next write-up, I will explain the steps for writing queries and creating tables. Thank you.

Scenario
I have a small need where I have to compare 2 JSON Records in MySql. The query will check both JSONs and will return some count if there is any modification in the new JSON record.

To help you understand easily, I am adding small JSON although I have some large JSON to compare.

To start, first create a test table.Compare JSON In MySql
CREATE TABLE `table1` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `json_col` json DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

To insert dummy JSON record, please use Insert query.

INSERT INTO `iaptsdb`.`table1`
(`json_col`)
VALUES('{"Unit": "109", "LastName": "Smith", "PersonId": "t0077508", "FirstName": "Karleen"}');

Now you have one record in table1.
Check by selecting this table1.

Now we will add our JSON Compare query to check JSON with above record.
We will update FirstName in new JSON and then we will compare it with above record.

I have SET FirstName to "John" and compare it to Record Id 1 in table1.

As FirstName is not equal to FirstName Record Id 1, hence it will reflect no record.**

set @some_json = '{"Unit": "109", "LastName": "Smith", "PersonId": "t0077508", "FirstName": "John"}';
select * from  table1
 WHERE
 CAST(json_col as JSON) = CAST(@some_json as JSON);

If we add "Karleen" as FirstName in JSON, the query will compare and as record is same, so it reflect one record.

P.S: Sequence in JSON Key:Value doesn't matter. :)
If you have implemented other approaches, please share them in the comments as well.

1. I could fetch all the required tables and data from that database and create my own database and dump those details into mine and then use it.
   
   
2. I could create a link between my database and the existing database, so that I could fire queries directly on that database.
   
   Using the first approach will not give me updated records at any point of time but my scenario was real time data. So I thought of using a second approach. This approach gave me real time data as I was querying the existing data and using it. But there is one drawback: It might be somewhat slow. In this article I will explain how to create a linked server in SQL Server. Here I will be using ODBC Driver to fetch data from MYSQL database.
   
   The following are the steps to connect a MySQL database to a SQL Server:
   
   First of all we need to install the appropriate MySQL ODBC Driver based on the operating system from the below link. After the driver has been installed go to Control Panel, Administrative Tools, then Data Sources(ODBC). After that click System DSN. Now Press Add Button .
   
   Select MYSQL Driver Listed(MYSQL(ODBC) 5.3 ANSI Driver) and click finish. This will open up MySQL Configuration window.
   
   Fill Data Source Name as MYSQL (this can be anything). TCP/IP Server as localhost. Port as 3306(default port for mysql), User Name-root, Password -your database password and click test. This will show a success message. Now select database and click ok.

We are done with MYSQL System DSN. Now we will set Linked Server to MYSQL in SQL Server. Open SQL Server, Server Objects, then Linked Server. Right Click on Linked Servers, then Add New Linked Server.

This will Open up a Linked Server Properties Dialog. Fill Linked Server as MYSQL_LINKED, select Provider as Microsoft OLEDB Provider For ODBC Drivers. Product Name as MySQl, DataSource as MySQL_Linked (whatever name is given while creating DSN). Provider String as,

Leave location as blank and Catalog as database name (in mysql). 

Drill down to Server Object, then Linked Servers, Providers, right-click MSDASQL, and select “Properties”. The Provider Options for Microsoft OLE DB Provider for ODBC Drivers dialog box will open allowing you to configure several options. Ensure the following four options are checked:
Nested queries

Level zero only

Allow inprocess

Supports ‘Like’ Operator

All other options should be unchecked. When done, click “OK”.

In addition to this, you can enable provider options on the SQL OLEDB, In my case I select the Dynamic Parameter and Allow in process.
We are done with setting up a linked server. Now we have to test it by firing some basic queries. There are three ways by which we can query a linked server.

    Open Query
    Select using 4 part notation.
    Execute Function

Open Query function requires 2 parameters: 1)Linked Server Name, 2)Query
    select * from openquery (MYSQL_LINKED, 'select * from test.user_details');  
    INSERT OPENQUERY (MYSQL_LINKED, 'select name,address from test.user_details') VALUES ('Peter','Amsterdam');  
    UPDATE OPENQUERY (MYSQL_LINKED, 'select name from test.user_details WHERE user_id = 100006413534648') SET name = 'Scott';  
    DELETE OPENQUERY (MYSQL_LINKED, 'select name from test.user_details WHERE user_id = 100006413534648')  

Note:

For Update/Delete on Linked Server we need to set RPC and RPC OUT properties of Linked Server as true (Right click Linked Server, Properties, Server Option Tab, RPC-True, then set RPC OUT -True.
 
Part Notation: We can also execute queries on linked server using four-part notations like:

SELECT * FROM linkedserver...tablename but for this we need to change MSDASQL Provider property. Check the box that says “level zero only” in providers.
    select * from MYSQL_LINKED...user_details  
    INSERT into MYSQL_LINKED...user_details(name,address) VALUES ('Rajeev','Bangalore');  
    UPDATE MYSQL_LINKED...user_details set name='Akash' where USER_ID='100006413534649';  
    DELETE from MYSQL_LINKED...user_details where USER_ID='100006413534649';  

Execute Function can also be used for querying linked server.
    EXECUTE('delete from test.user_details WHERE user_id = 100006413534647') AT MYSQL_LINKED 

In this tutorial, I am going to explain about Character Sets and Collations in MySQL with examples. This article will cover the following topics. Let’s see.

CHARACTER SETS
A character set is a set of symbols and their encoding. It is a set of behavior; it is used for analyzing the characters in a character set. MySQL also includes the character set support in which it prepares for storing data by using a collection of character sets and it also performs the estimation according to a variety of collations. MySQL chooses the database character set and the collation of the database and it can become the point on character sets at the server, database, table, and at the column level. Each character set contains one or more collations that determine a set of character comparison rules within the character set.
 
Character sets have developed through history but that history is beyond the bounds of this article. During this time the Unicode standard developed and achieves identification of the older character sets which are closed throughout. It is important to be conscious of the difference between Unicode and local character sets.
MySQL supports various character sets that allow almost any character to be stored in a string. Below is the CHARACTER SET Statement, by which the user can get all available character sets in the MySQL database.
SHOW CHARACTER SET;

In the above-given character set, it must have one collation. And, the given character set, it has several collations. To the collations list for a given character set, it includes all variable character sets by using the following statement.
 
In MySQL, you can use the "SHOW COLLATION" statement to get all the collations for a given character set.
 
Syntax
SHOW COLLATION LIKE 'character_set_name%';
 
Example

    SHOW COLLATION LIKE 'latin1%';

Setting Character Sets and Collations at Database Level
 
When you create a database, you can specify the default character set and the collation for a database. But, if you don’t specify it, MySQL will use the default character sets and collation.
 
Syntax
CREATE DATABASE <database_name>
CHARACTER SET <character_set_name>
COLLATE <collation_name>

Setting Character Sets and Collations at the Table Level
 
When you create a table, you can also specify the default character set and the collation for a table. But, if you don’t specify it, MySQL will use the default character sets and collation.
 
Syntax
CREATE TABLE <table_name> (
<column_name1> datatype,
<column_name2> datatype, …
<column_nameN> datatype
)
CHARACTER SET <character_set_name>
COLLATE <collation_name>;
 
For Example
 
1) Define the column and table with the collate and its character
 
Example - In the following example we have to define the column and table with the collation and its character.

CREATE TABLE test(  
  C_ID INT,  
  C_Name VARCHAR(50)  
)  
DEFAULT CHARACTER SET LATIN1  
COLLATE latin1_general_ci;  
  
DESCRIBE TEST; 

2) Setting Collation and Character Set a Table Level
 
Example - Here we have to define the table has a table character set and a table collation. Create a table “RACEWINNER” with R_ID and First_name column and set the collation.

    Create table RACEWINNER(  
      R_ID INT,  
      First_name varchar(30)  
    ) CHARACTER SET latin1  
    COLLATE latin1_danish_ci;  
      
    DESCRIBE RACEWINNER;

3) Setting Collation and character set at Column Level
 
Example - In this example create a table "Company" and show the collation on the column character set.

    Create table Company(  
      C_column varchar(50) CHARACTER SET latin1 COLLATE latin1_german1_ci  
    );  
      
    DESCRIBE Company;

HostForLIFE.eu SQL Server 2019 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.