MySQL Data Types and Operators | Chapter 3
Fundamentals of SQL | Deep dive into SQL theory! Chapter 1
Getting started with Database, SQL and MySQL installation! Chapter 2
Most of the actions you need to perform on a database are done with SQL statements.
Keep in mind while writing SQL queries;
- SQL keywords are NOT case sensitive: select is the same as SELECT In this tutorial we will write all SQL keywords in upper-case.
- Semicolon after SQL Statements? Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server
Data Types in SQL:
When we create any table or variable, it is required to specify the type of data it will store in addition to the name of each column. The data type specification also prevents the user from entering any unexpected or invalid information. o When we assign the appropriate data type to a column, we can make efficient use of memory by allocating only the amount of system memory required for the data in the relevant column.
Why use data types?
We can understand the importance of data type in by taking a simple Sign-up page to create a new account. This page shows the following input fields: First Name, Last Name, Mobile Number, User Name, and Password. These fields require the following data type characteristics:
- First name and Last name: These fields should require only alphabets.
- Mobile Number: This field can accept only numbers
- Username: This field can accept letters, numbers, and periods.
- Password: This field must contain alphabets, numbers, and special characters.
MySQL Data Types:
In MySQL there are three main data types: string, numeric, and date and time.
1. String Data Type
2. Numeric Data Types
All the numeric data types may have an extra option: UNSIGNED or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for the column. If you add the ZEROFILL option, MySQL automatically also adds the UNSIGNED attribute to the column.
3. Date and Time Data Types
SQL Operators:
The manipulation and retrieving of the data are performed with the help of reserved words and characters, which are used to perform arithmetic operations, logical operations, comparison operations, compound operations, etc.
What is SQL Operator?
The SQL reserved words and characters are called operators, which are used with a WHERE clause in a SQL query. SQL operators are used for filtering the table’s data by a specific condition in the SQL statement. In SQL, an operator can either be a unary or binary operator. The unary operator uses only one operand for performing the unary operation, whereas the binary operator uses two operands for performing the binary operation.
SQL operators are categorized in the following categories:
a) SQL Arithmetic Operators:
The Arithmetic Operators perform the mathematical operation on the numerical data of the SQL tables.
— SQL Addition Operator (+)
— SQL Subtraction Operator (-)
— SQL Multiplication Operator (+)
— SQL Division Operator (-)
— SQL Modulus Operator (+)
b) SQL Comparison Operators:
The Comparison Operators in SQL compare two different data of SQL table and check whether they are the same, greater, and lesser. The SQL comparison operators are used with the WHERE clause in the SQL queries. — SQL Equal Operator (=)
— SQL Not Equal Operator (!=)
— SQL Greater Than Operator (>)
— SQL Greater Than Equals to Operator (>=)
— SQL Less Than Operator (< = )
c) SQL Logical Operators:
The Logical Operators in SQL perform the Boolean operations, which give two results True and False. These operators provide True value if both operands match the logical condition.
- SQL AND operator: The AND operator in SQL would show the record from the database table if all the conditions separated by the AND operator evaluated to True. It is also known as the conjunctive operator and is used with the WHERE clause.
SELECT column1, …., columnN FROM table_Name WHERE condition1 AND conditi on2 AND condition3 AND ……. AND conditionN; - SQL OR operator: The OR operator in SQL shows the record from the table if any of the conditions separated by the OR operator evaluates to True.
SELECT column1, …., columnN FROM table_Name WHERE condition1 OR conditio n2 OR condition3 OR ……. OR conditionN; - SQL BETWEEN operator: The BETWEEN operator in SQL shows the record within the range mentioned in the SQL query. This operator operates on the numbers, characters, and date/time operands. If there is no value in the given range, then this operator shows NULL value.
- SQL IN operator: The IN operator in SQL allows database users to specify two or more values in a WHERE clause.
- SQL NOT operator: The NOT operator in SQL shows the record from the table if the condition evaluates to false. It is always used with the WHERE clause.
- SQL ANY operator: The ANY operator in SQL shows the records when any of the values returned by the sub-query meet the condition. The ANY logical operator must match at least one record in the inner query.
- SQL LIKE operator: The LIKE operator in SQL shows those records from the table which match with the given pattern specified in the sub-query.
SQL Wildcard Characters:
The percentage (%) sign is a wildcard which is used with this logical operator “Like”. This operator is used in the WHERE clause with the following three statements:
1. SELECT statement
2. UPDATE statement
3. DELETE statement
Reference:
1. w3schools.com
