Category: mysql

Khóa học miễn phí MySQL – VARCHAR nhận dự án làm có lương

MySQL – VARCHAR

Table of content

The MySQL Varchar Data Type

The MySQL VARCHAR data type is used to store variable-length character strings, having a length up to 65,535 bytes.

In MySQL, when you store text in a VARCHAR column, it needs a little extra space to keep track of how long the text is. This extra space can be either 1 or 2 bytes, depending on the length of the text. If the text is short (less than 255 characters), it uses 1 byte for length. For longer text, it uses 2 bytes.

The total size of data plus the length info cannot exceed 65,535 bytes for a row in a table.

Example

In the following query, we are creating a new table named test_table that has two columns column1 and column2.

As we can see in the below code block, the columns (column1 = 32765 and column2 = 32766) makes 65531 bytes. These columns will take 2 bytes each as a length prefix. Therefore, the columns totally make 32765+2+32766+2 = 65535 bytes −

CREATE TABLE test_table (
   column1 VARCHAR(32765) NOT NULL,
   column2 VARCHAR(32766) NOT NULL
)CHARACTER SET ''latin1'' COLLATE LATIN1_DANISH_CI;

Output

Following is the output of the above code −

Query OK, 0 rows affected (0.03 sec)

Example

Now, let us create another table test_table2 and provide 32766 and 32766 to both the columns (column1 and column2) −

CREATE TABLE test_table2 (
   column1 VARCHAR(32766) NOT NULL, --error
   column2 VARCHAR(32766) NOT NULL
)CHARACTER SET ''latin1'' COLLATE LATIN1_DANISH_CI;

Output

As we can see in the output below, an error is generated because the row size (32766 +2 +32766 +2 = 65536) exceeds the maximum limit (65,535) −

ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

Example

Here, we are creating another table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID int PRIMARY KEY AUTO_INCREMENT,
   NAME VARCHAR(3)
);

Following is the output obtained −

Query OK, 0 rows affected (0.03 sec)

Now, we are inserting a string into NAME column where the length is greater than the length of VARCHAR column −

INSERT INTO CUSTOMERS (NAME) VALUES (''Rahul'');

Output

As a result, MySQL will generate an error given below −

ERROR 1406 (22001): Data too long for column ''NAME'' at row 1

Example

MySQL does not count the trailing spaces when inserting a value. Instead it truncates the trailing spaces.

Let us insert a value into the NAME column that has trailing spaces −

INSERT INTO CUSTOMERS (NAME) VALUES (''ABC '');

Output

As we can see in the output below, MySQL issued a warning −

Query OK, 1 row affected, 1 warning (0.02 sec)

Example

In the following query, we are trying to check the length of the values in NAME column −

SELECT ID, NAME, length(NAME) FROM CUSTOMERS;

The result produced is as follows −

ID	NAME	length(NAME)
1	ABC	3

Now, let us execute the below query to display the warnings that issued on the above insertion operation −

SHOW warnings;

The result produced is −

Level	Code	Message
Note	1265	Data truncated for column ”NAME” at row 1

Varchar Datatypes Using a Client Program

In addition to performing datatypes using mysql query, we can also create column of the Varchar datatypes using the client program.

Syntax

To create a column of Varchar datatypes through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −

$sql ="CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) ";
$mysqli->query($sql);

To create a column of Varchar datatypes through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −

sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))";
con.query(sql);

To create a column of Varchar datatypes through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))";
statement.execute(sql);

To create a column of Varchar datatypes through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −

sql = ''CREATE TABLE test_table (column1 VARCHAR(32765) NOT NULL,  column2 VARCHAR(32766) NOT NULL)CHARACTER SET ''latin1'' COLLATE LATIN1_DANISH_CI''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');

//create a customer table and use varchar data type with differenet size
$sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) ";
if ($mysqli->query($sql)) {
    echo "Table created successfully with varchar data!n";
}
if ($mysqli->errno) {
    printf("table could not create table: %s
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Table created successfully with varchar data!

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a customer table and use varchar data type with differenet size
  sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50)) ";
  con.query(sql);

  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''customers'' AND COLUMN_NAME = ''cust_Name''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[ { DATA_TYPE: ''varchar'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Varchar {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //varchar data types...!;
         String sql = "CREATE TABLE customers (cust_Name VARCHAR(30), cust_Address varchar(50))";
         statement.execute(sql);
         System.out.println("column of a varchar type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customers");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
column of a varchar type created successfully...!
cust_Name varchar(30)
cust_Address varchar(50)

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with varchar column
sql = ''''''
CREATE TABLE test_table (
column1 VARCHAR(32765) NOT NULL,
column2 VARCHAR(32766) NOT NULL
)
CHARACTER SET ''latin1'' COLLATE LATIN1_DANISH_CI
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Insert data into the created table
insert_query = "INSERT INTO test_table (column1, column2) VALUES (%s, %s)"
data_to_insert = ("History", "Geography")
cursorObj.execute(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM test_table"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

The table is created successfully!
Rows inserted successfully.
Table Data:
(''History'', ''Geography'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – ENUM nhận dự án làm có lương

MySQL – ENUM

Table of content

ENUM (Enumerator) is a user defined datatype which stores a list of values as strings. These values are specified when you define the ENUM column. The user can choose values from this predefined list while inserting values into this column.

Each string value defined in an ENUM column is implicitly assigned a numerical value starting from 1. These numerical values are used internally by MySQL to represent the ENUM values.

The MySQL ENUM Data Type

The MySQL ENUM data type allow you to select one or more values from a predefined list during insertion or update operations. The selected values are stored as strings in the table, and when you retrieve data from the ENUM column, the values are presented in a human-readable format.

ENUM columns can accept values of various data types, including integers, floating-point numbers, decimals, and strings. However, internally, MySQL will convert these values to the closest matching ENUM value based on its predefined list.

Syntax

Following is the syntax to define the ENUM data type on a column −

CREATE TABLE table_name (
   Column1,
   Column2 ENUM (''value1'',''value2'',''value3'', ...),
   Column3...
);

Note: An enum column can have maximum 65,535 values.

Attributes of ENUM

The ENUM datatype in MySQL has three attributes. The same is described below −

Default − The default value of enum data type is NULL. If no value is provided for the enum field at the time of insertion, Null value will be inserted.
NULL − It works the same as the DEFAULT value if this attribute is set for the enum field. If it is set, the index value is always NULL.
NOT NULL − MySQL will generate a warning message if this attribute is set for the enum field and no value is provided at the insertion time.

Example

First of all, let us create a table named STUDENTS. In this table, we are specifying ENUM string object in the BRANCH column using the following query −

CREATE TABLE STUDENTS (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(30) NOT NULL,
   BRANCH ENUM (''CSE'', ''ECE'', ''MECH''),
   FEES int NOT NULL,
   PRIMARY KEY (ID)
);

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

Now, we retrieve the structure of the STUDENTS table, revealing that the “BRANCH” field has an enum data type −

DESCRIBE STUDENTS;

The output indicates that the BRANCH field”s data type is ENUM, which stores the values (”CSE”, ”ECE”, ”MECH”) −

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(30)	NO		NULL
BRANCH	enum(”CSE”,”ECE”,”MECH”)	YES		NULL
FEES	int	NO		NULL

Now, let us insert records into the STUDENTS table using the following INSERT query −

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
(''Anirudh'', ''CSE'', 500000),
(''Yuvan'', ''ECE'', 350000),
(''Harris'', ''MECH'', 400000);

In these insertion queries, we have used values (”CSE”, ”ECE”, and ”MECH”) for the “BRANCH” field, which are valid enum values. Hence, the queries executed without any errors −

Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Using the below query, we can display all the values in the table −

SELECT * FROM STUDENTS;

Following are the records of STUDENTS table −

ID	NAME	BRANCH	FEES
1	Anirudh	CSE	500000
2	Yuvan	ECE	350000
3	Harris	MECH	400000

Inserting Records with Numerical ENUM Values

We can insert the enum list values to the ENUM column of table using the respective numeric index. The numeric index starts from 1 but not from 0.

Example

In the query below, we are inserting the value ”CSE” from the ENUM list into the ”BRANCH” column using its numeric index. Since ”CSE” is located at position 1 in the ENUM list, we use 1 as the numeric index in the query.

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
(''Devi'', 1, 380000);

Output

The insertion query executes without any errors −

Query OK, 1 row affected (0.01 sec)

Verification

Let us verify whether the above insertion is successful or not by retrieving all the records of the table using the below query −

SELECT * FROM STUDENTS;

The STUDENTS table displayed is as follows −

ID	NAME	BRANCH	FEES
1	Anirudh	CSE	500000
2	Yuvan	ECE	350000
3	Harris	MECH	400000
4	Devi	CSE	380000

Inserting Invalid Records

In MySQL, if we try to insert a value into a column with an ENUM data type that does not match any of the specified enum values, it will result in an error.

Example

In the following query, we are referring to the 6^th value in enum list, which does not exist. So, the following query will generate an error −

INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES
(''Thaman'', 6, 200000);

Output

As we can see the output, an error is generated, and no new record has been inserted −

ERROR 1265 (01000): Data truncated for column ''BRANCH'' at row 1

Filtering Records by Numeric ENUM Value

In MySQL, you can retrieve records from an ENUM column based on either the string value or the numeric index. The numeric index starts from 1, not 0.

Example

The numeric index of 1 in enum list is ”CSE”. So, the following query will fetch the records where the BRANCH column contains the value as ”CSE”.

SELECT * FROM STUDENTS WHERE BRANCH = 1;

Output

The resulting output displays records where the ”BRANCH” column contains the value ”CSE” −

ID	NAME	BRANCH	FEES
1	Anirudh	CSE	500000
4	Devi	CSE	380000

Filtering Records by Human-Readable ENUM Value

There can be some instances where the enum list will have large number of values. It can be difficult to remember the numeric index for every value in the list. In such cases, it is more convenient to use the human-readable string value of the ENUM item in your query to retrieve records based on the ENUM field”s value.

Example

In the following query, we are filtering the records where the BRANCH column contains the value “Mech”.

SELECT * FROM STUDENTS WHERE BRANCH = "MECH";

Output

Following is the output obtained −

ID	NAME	BRANCH	FEES
3	Harris	MECH	400000

Disadvantages of ENUM Data Type

Following are the disadvantages of ENUM data type in MySQL −

If we wish to modify the values in enum list, we need to re-create the complete table using the ALTER TABLE command, which is quite expensive in terms of used resources and time.
It is very complex to get the complete enum list because we need to access the inform_schema database.
Expressions cannot be used with enumeration values. For instance, the following CREATE statement will return an error because it used the CONCAT() function for creating enumeration value −

CREATE TABLE Students (
   ID int PRIMARY KEY AUTO_INCREMENT,
   NAME varchar(30),
   BRANCH ENUM(''CSE'', CONCAT(''ME'',''CH''))
);

User variables cannot be used for an enumeration value. For instance, look at the following query −

mysql> SET @mybranch = ''EEE
mysql> CREATE TABLE Students (
   ID int PRIMARY KEY AUTO_INCREMENT,
   NAME varchar(30),
   BRANCH ENUM(''CSE'', ''MECH'', @mybranch)
);

It is recommended to not use the numeric values as enum values.

Enum Datatypes Using a Client Program

We can also create column of the Enum datatypes using the client program.

Syntax

To create a column of Enum datatypes through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −

$sql = ''CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))
$mysqli->query($sql);

To create a column of Enum datatypes through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −

sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM (''CSE'', ''ECE'', ''MECH''), FEES int NOT NULL, PRIMARY KEY (ID) )";
con.query(sql);

To create a column of Enum datatypes through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))";
statement.execute(sql);

To create a column of Enum datatypes through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −

sql = ''CREATE TABLE STUDENTS( ID int NOT NULL AUTO_INCREMENT,  NAME varchar(30) NOT NULL,  BRANCH ENUM (''CSE'', ''ECE'', ''MECH''),  FEES int NOT NULL,  PRIMARY KEY (ID)  )''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');

//create table with boolean column
$sql = ''CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
//insert data into created table
$q = "INSERT INTO STUDENTS (NAME, BRANCH, FEES)
      VALUES (''Anirudh'', ''CSE'', 500000), (''Yuvan'', ''ECE'', 350000)";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT BRANCH FROM STUDENTS";
if ($r = $mysqli->query($s)) {
    printf("Select query executed successfully...!n");
    printf("following records belongs to Enum datatypes: n");
    while ($row = $r->fetch_assoc()) {
        printf(" Branch Name: %s", $row["BRANCH"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Output

The output obtained is as follows −

Table created successfully...!
Data inserted successfully...!
Select query executed successfully...!
following records belongs to Enum datatypes:
 Branch Name: CSE
 Branch Name: ECE

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a customers that accepts one column enum type.
  sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM (''CSE'', ''ECE'', ''MECH''), FEES int NOT NULL, PRIMARY KEY (ID) )";
  con.query(sql);

  //insert data into created table
  sql ="INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES (''Anirudh'', ''CSE'', 500000),  (''Yuvan'', ''ECE'', 350000)";
  con.query(sql);
  //select datatypes of branch
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''STUDENTS'' AND COLUMN_NAME = ''BRANCH''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[ { DATA_TYPE: ''enum'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Enum {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //ENUM data types...!;
         String sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))";
         statement.execute(sql);
         System.out.println("column of a ENUM type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE STUDENTS");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
column of a ENUM type created successfully...!
ID int
NAME varchar(30)
BRANCH enum(''CSE'',''ECE'',''MECH'')
FEES int

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with enum column
sql = ''''''
CREATE TABLE STUDENTS (
ID int NOT NULL AUTO_INCREMENT,
NAME varchar(30) NOT NULL,
BRANCH ENUM (''CSE'', ''ECE'', ''MECH''),
FEES int NOT NULL,
PRIMARY KEY (ID)
);
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (''Anirudh'', ''CSE'', 500000),
    (''Yuvan'', ''ECE'', 350000),
    (''Harris'', ''MECH'', 400000)
]
# Insert data into the created table
insert_query = "INSERT INTO STUDENTS (NAME, BRANCH, FEES) VALUES (%s, %s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM STUDENTS"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''Anirudh'', ''CSE'', 500000)
(2, ''Yuvan'', ''ECE'', 350000)
(3, ''Harris'', ''MECH'', 400000)

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – DECIMAL nhận dự án làm có lương

MySQL – DECIMAL

Table of content

The MySQL Decimal Data Type

The MySQL DECIMAL data type is used to store numeric values with decimal points. It allows for precise calculations and can be configured to store a specified number of digits before and after the decimal point.

We often use this datatype for the columns that require exact precision such as salaries of employees, employee PF balances, etc.

Internally, MySQL stores DECIMAL values using a binary format that allocates storage for the integer and fractional parts of the number separately. This binary format efficiently packs 9 digits into 4 bytes of storage.

Syntax

Following is the syntax to define a column whose data type is DECIMAL −

column_name  DECIMAL(P,D);

Where,

P is called precision which specifies the total number of significant digits that can be stored in the column, both to the left and right of the decimal point. The range of P is 1 to 65.
D is a scale that specifies the maximum number of digits that can be stored after the decimal point. The range of D should be between 0 and 30 and D is less than or equal to (<=) P.

For instance, if we define a column as DECIMAL(10,2), it can store numbers with up to 10 digits, and up to 2 digits to the right of the decimal point.

In MySQL, instead of the DECIMAL keyword, we can also use the “DEC”, “FIXED” and “NUMERIC” keywords because they are synonyms for DECIMAL.

Attributes

The DECIMAL keyword has two attributes: UNSIGNED and ZEROFILL.

UNSIGNED − When used, it indicates that the column does not accept negative values.
ZEROFILL − If used, it pads the number with zeros to the specified width.

Precision and Scale

In the following query, we define a SALARY column with the DECIMAL data type, specifying a precision of 5 and a scale of 3 −

SALARY decimal(5,3)

This definition means that the SALARY column can store values with up to 5 digits in total, including 3 digits to the right of the decimal point. The range for this column would be from 99.999 to -99.999.

No Decimal Places

In here, the SALARY column contains no fractional part or decimal point. The following two queries are same −

SALARY DECIMAL(5);
SALARY DECIMAL(5,0);

Both declarations indicate that the SALARY column can store values as whole numbers without decimal places.

Default Precision

If you omit the precision value, the default precision P will be 10 −

SALARY DECIMAL;

MySQL DECIMAL Storage

MySQL stores values of the “DECIMAL” data type using a binary format that optimizes storage. Specifically, MySQL packs 9 digits into 4 bytes. Storage is allocated separately for the integer and fractional parts, with 4 bytes used for each set of 9 digits. Any remaining digits require additional storage.

The storage required for remaining (leftover) digits is demonstrated in the following table:

Leftover Digits	Bytes
0	0
1-2	1
3-4	2
5-6	3
7-9	4

Consider a DECIMAL(30,9) column, which has 9 digits for the fractional part and 30 – 9 = 21 digits for the integer part. In this case, the fractional part takes 4 bytes. The integer part takes 8 bytes for the first 18 digits, and for the leftover 3 digits, it requires an additional 2 bytes. Therefore, the DECIMAL(30,9) column requires a total of 14 bytes.

Example

To further understand this, let us create a table named EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   ID int NOT NULL AUTO_INCREMENT,
   NAME varchar(30) NOT NULL,
   SALARY decimal(14,4) NOT NULL,
   PRIMARY KEY (ID)
 );

Using the following query, we are inserting some records into the above created table −

INSERT INTO EMPLOYEES (NAME, SALARY) VALUES
("Krishna", 150050.34),
("Kalyan", 100000.65);

The EMPLOYEES table obtained is as follows −

ID	NAME	SALARY
1	Krishna	150050.3400
2	Kalyan	100000.6500

Using the following query, we are including the ZEROFILL attribute in the “SALARY” column −

ALTER TABLE EMPLOYEES
MODIFY SALARY decimal(14, 4) zerofill;

Following is the output of the above query −

Query OK, 2 rows affected, 1 warning (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 1

Here, we are trying to fetch all the records from the EMPLOYEES tables after including the ZEROFILL attribute on SALARY column −

SELECT * FROM EMPLOYEES;

The records will display zeros padded based on the range specified in the “SALARY” column −

ID	NAME	SALARY
1	Krishna	0000150050.3400
2	Kalyan	0000100000.6500

Decimal Datatype Using a Client Program

We can also create column of the decimal datatype using the client program.

Syntax

To create a column of decimal datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −

$sql = ''CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )
$mysqli->query($sql);

To create a column of decimal datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −

sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )";
con.query(sql);

To create a column of decimal datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))";
statement.execute(sql);

To create a column of decimal datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −

sql = ''CREATE TABLE EMPLOYEES (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID))''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');

//create table with boolean column
$sql = ''CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
//insert data into created table
$q = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (''Krishna'', 150050.34), (''Kalyan'', 100000.65)";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT ID, Salary FROM EMPLOYEES";
if ($r = $mysqli->query($s)) {
    printf("Table Records: Where Salary is decimal type! n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Salary: %s", $row["ID"], $row["Salary"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Output

The output obtained is as follows −

Table created successfully...!
Data inserted successfully...!
Table Records: Where Salary is decimal type!
 ID: 1, Salary: 150050.3400
 ID: 2, Salary: 100000.6500

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a employees table, that accepts one column of decimal type.
  sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )";
  con.query(sql);

  //insert data into created table
  sql = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (''Krishna'', 150050.34), (''Kalyan'', 100000.65)";
  con.query(sql);

  //select datatypes of salary
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''EMPLOYEES'' AND COLUMN_NAME = ''SALARY''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[ { DATA_TYPE: ''decimal'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Decimal {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Decimal data types...!;
         String sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID))";
         statement.execute(sql);
         System.out.println("column of a Decimal type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE EMPLOYEES");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
column of a Decimal type created successfully...!
ID int
NAME varchar(30)
SALARY decimal(14,4)

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with decimal column
sql = ''''''
CREATE TABLE EMPLOYEES (
ID int NOT NULL AUTO_INCREMENT,
NAME varchar(30) NOT NULL,
SALARY decimal(14,4) NOT NULL,
PRIMARY KEY (ID)
)''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (''Krishna'', 150050.34),
    (''Kalyan'', 100000.65)
]
# Insert data into the created table
insert_query = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (%s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM EMPLOYEES"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''Krishna'', Decimal(''150050.3400''))
(2, ''Kalyan'', Decimal(''100000.6500''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – BOOLEAN nhận dự án làm có lương

MySQL – Boolean Datatype

Table of content

A Boolean data type is used to represent truth values of logic and Boolean algebra. It has two possible values: either true or false.

For example, if a customer wants to see all the bikes that are black in colour, we can filter them using BOOLEAN operator, as given in the following table −

Here, ”IS_BLACK” is the BOOLEAN column that returns either true or false values based on the colours of the bikes.

Boolean in MySQL

In MySQL, there is no built-in Boolean or Bool data type. Instead MySQL provides us with the TINYINT datatype to store the Boolean values.

MySQL considers the value 0 as FALSE and 1 as TRUE. We can also store NULL values using the TINYINT datatype.

The Boolean values (such as TRUE and FALSE) are not case-sensitive.

Syntax

Following is the syntax of the BOOLEAN operator in MySQL −

CREATE TABLE table_name (
   Column_name BOOLEAN
);

Example

In MySQL, 0 is defined as FALSE and any non-zero values are defined as TRUE −

SELECT TRUE, FALSE;

Output

As we can see in the output below, TRUE and FALSE are represented as 1 and 0 −

TRUE	FALSE
1	0

Example

In MySQL, the Boolean values (TRUE and FALSE) are case-insensitive −

SELECT true, false, TRUE, FALSE, True, False;

Output

The output produced is as given below −

true	false	TRUE	FALSE	True	False
1	0	1	0	1	0

Example

Now, let”s create a table with the name CUSTOMERS using the following query. Here, the AVAILABILITY column specifies whether the customer is available or not. If the bit value is 0 (FALSE), the customer is not available. If it is 1(TRUE), the customer is available −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   NAME VARCHAR(40),
   AVAILABILITY BOOLEAN
);

Following is the output produced −

Query OK, 0 rows affected (0.02 sec)

To get the information about the CUSTOMERS table, use the following query −

DESCRIBE CUSTOMERS;

If we look at the AVAILABILITY column, which has been set to BOOLEAN while creating the table, it now shows type of TINYINT −

Field	Type	Null	Key	Default	Extra
ID	int	NO	PRI	NULL	auto_increment
NAME	varchar(40)	YES		NULL
AVAILABILITY	tinyint(1)	YES		NULL

Now, let us insert some records into the CUSTOMERS table using the following INSERT query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', FALSE),
(2, ''Khilan'', TRUE),
(4, ''Kaushik'', NULL);

Using the below query, we can display all the values in the table −

SELECT * FROM CUSTOMERS;

We can see the values in the AVAILABILITY column are set to 0 and 1 respectively. −

ID	NAME	AVAILABILITY
1	Ramesh	0
2	Khilan	1
4	Kaushik	NULL

Replacing BOOLEAN 0,1 with TRUE and FALSE

As we can see in the above CUSTOMERS table, the BOOLEAN data type shows 0 and 1 values instead of TRUE and FALSE. In MySQL, we can convert BOOLEAN data type to TRUE and FALSE values using the CASE statement.

The MySQL CASE statement is a conditional statement that goes through conditions and return a values when the first condition is met. Therefore, once a condition is true, it will stop reading the next piece of code and return the result.

If no conditions are true, it will return the value in the ELSE clause. If no ELSE clause is present and no conditions are true, it returns NULL.

Syntax

Following is the syntax of CASE statement in MySQL −

CASE
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   ...
   WHEN conditionN THEN resultN
   ELSE result
END;

Example

To get a better understanding, let us consider the BIKES table created using the query below −

CREATE TABLE BIKES (
   S_NO INT AUTO_INCREMENT PRIMARY KEY,
   COMPANY VARCHAR(40) NOT NULL,
   PRICE INT NOT NULL,
   COLOUR VARCHAR(40) NOT NULL,
   IS_BLACK BOOLEAN
);

Example

Output of the above code is as follows −

Query OK, 0 rows affected (0.03 sec)

Now, let us insert values into the BIKES table using the INSERT statement as shown below −

INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK)
VALUES (''Royal Enfield'', 300000, ''Black'', 1);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK)
VALUES (''BMW'', 900000, ''Blue'', 0);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK)
VALUES (''Jawa'', 150000, ''Black'', 1);
INSERT INTO BIKES (COMPANY, PRICE, COLOUR, IS_BLACK)
VALUES (''Triumph'', 1200000, ''Red'', 0);

The BIKES table obtained is as follows −

S_NO	COMPANY	PRICE	COLOUR	IS_BLACK
1	Royal Enfield	300000	Black	1
2	BMW	900000	Blue	0
3	Jawa	150000	Black	1
4	Triumph	1200000	Red	0

Now, let us display all the records from the BIKES table, where the colour BLACK is represented by either TRUE or FALSE −

SELECT *,
CASE IS_BLACK
WHEN 1 THEN ''TRUE''
WHEN 0 THEN ''FALSE''
END AS IS_BOOLEAN
FROM BIKES;

Output

The output is displayed as follows −

S_NO	COMPANY	PRICE	COLOUR	IS_BLACK	IS_BOOLEAN
1	Royal Enfield	300000	Black	1	TRUE
2	BMW	900000	Blue	0	FALSE
3	Jawa	150000	Black	1	TRUE
4	Triumph	1200000	Red	0	FALSE

Example

In the following query, we are filtering the records from the BIKES table where colour black is TRUE −

SELECT * FROM BIKES WHERE IS_BLACK IS TRUE;

Output

As we can see the output below, Royal Enfield and Jawa are black in color (true) −

S_NO	COMPANY	PRICE	COLOUR	IS_BLACK
1	Royal Enfield	300000	Black	1
3	Jawa	150000	Black	1

Example

In here, we are filtering the records from the BIKES table where color black is FALSE −

SELECT * FROM BIKES WHERE IS_BLACK IS FALSE;

Output

The output says that BMW and Triumph are not black in colour(false) −

S_NO	COMPANY	PRICE	COLOUR	IS_BLACK
2	BMW	900000	Blue	0
4	Triumph	1200000	Red	0

Boolean Operator Using a Client Program

In addition to perform the Boolean Operator in MySQL table using MySQL query, we can also perform the another operation on a table using a client program.

MySQL provides various Connectors and APIs using which you can write programs (in the respective programming languages) to communicate with the MySQL database. The connectors provided are in programming languages such as, Java, PHP, Python, JavaScript, C++ etc. This section provides programs to execute Boolean Operator in MySQL Table.

Syntax

Following are the syntaxes of the MySQL Boolean Operator in various programming languages −

The MySQL PHP connector mysqli provides a function named query() to execute an SQL query in the MySQL database. Depending on the type of query, it retrieves data or performs modifications within the database.

This function accepts two parameters namely −

$sql: This is a string value representing the query.
$resultmode: This is an optional parameter which is used to specify the desired format of the result. Which can be MYSQLI_STORE_RESULT (buffered result set object) or, MYSQLI_USE_RESULT (unbuffered result set object) or, MYSQLI_ASYNC.

To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −

$sql = "CREATE TABLE table_name ( Column_name BOOLEAN )";
$mysqli->query($sql);

The MySQL NodeJS connector mysql2 provides a function named query() to execute an SQL query in the MySQL database. This function accepts a string value as a parameter representing the query to be executed.

To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −

sql= "CREATE TABLE table_name ( Column_name BOOLEAN )";
con.query(sql);

We can use the JDBC type 4 driver to communicate to MySQL using Java. It provides a function named execute() to execute an SQL query in the MySQL database. This function accepts a String value as a parameter representing the query to be executed.

To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −

String sql = "CREATE TABLE table_name ( Column_name BOOLEAN )";
statement.execute(sql);

The MySQL Connector/Python provides a function named execute() to execute an SQL query in the MySQL database. This function accepts a string value as a parameter representing the query to be executed.

To perform the Boolean operation in MySQL table, we need to execute the CREATE TABLE statement using this function as −

query = "CREATE TABLE table_name (Column_name BOOLEAN)"
cursorObj.execute(query);

Example

Following are the implementations of this operation in various programming languages −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
//create table with boolean column
$sql = ''CREATE TABLE CUSTOMERS ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(40), AVAILABILITY BOOLEAN);
$result = $mysqli->query($sql);
if($result){
   printf("Table created successfully...!n");
}
//insert data into created table
$q = "INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (1, ''Ramesh'', TRUE)";
If($res = $mysqli->query($q)){
   printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM CUSTOMERS";
If($r = $mysqli->query($s)){
   printf("Select query executed successfully...!n");
   printf("Table records: n");
   while($row = $r->fetch_assoc()) {
       printf("Id %d, Name: %s, AVAILABILITY: %s",
       $row["ID"],
       $row["NAME"],
       $row["AVAILABILITY"]);
       printf("n");
    }
}
else {
   printf(''Failed'');
}
$mysqli->close();

Output

The output obtained is as follows −

Table created successfully...!
Data inserted successfully...!
Select query executed successfully...!
Table records:
Id 1, Name: Ramesh, AVAILABILITY: 1

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "Nr5a0204@123",
});

//Connecting to MySQL
con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Select database
  sql = "CREATE DATABASE TUTORIALS";
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS";
  con.query(sql);

  //Creating CUSTOMERS table
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(40), AVAILABILITY BOOLEAN);"
  con.query(sql , function(err){
    if (err) throw err;
    console.log("Table created Successfully...");
  });

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (1, ''Ramesh'', 0), (2, ''Khilan'', 1), (4, ''Kaushik'', NULL);"
  con.query(sql);

  //Displaying all the records of the CUSTOMERS table
  sql = "SELECT * FROM CUSTOMERS;"
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
Table created Successfully...
[
  { ID: 1, NAME: ''Ramesh'', AVAILABILITY: 0 },
  { ID: 2, NAME: ''Khilan'', AVAILABILITY: 1 },
  { ID: 4, NAME: ''Kaushik'', AVAILABILITY: null }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BooleanOperator {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create a table(having boolean field/column)
            String sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,NAME VARCHAR(40),AVAILABILITY BOOLEAN)";
            st.execute(sql);
            System.out.println("Table created successfully...!");
            //now lets insert some records
            String sql1 = "INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (1, ''Ramesh'', 0), (2, ''Khilan'', 1), (4, ''Kaushik'', NULL)";
            st.executeUpdate(sql1);
            System.out.println("Records inserted successfully...!");
            //lets display the records....
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records are: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String is_available = rs.getString("AVAILABILITY");
              System.out.println("Id: " + id + ", Name: " + name + ", Is_available: " + is_available);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table created successfully...!
Records inserted successfully...!
Table records are:
Id: 1, Name: Ramesh, Is_available: 0
Id: 2, Name: Khilan, Is_available: 1
Id: 4, Name: Kaushik, Is_available: null

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''customer''
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with boolean column
sql = '''''' CREATE TABLE CUSTOMER(
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(40),
    AVAILABILITY BOOLEAN)''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Insert data into the created table
insert_query = ''INSERT INTO CUSTOMER(ID, NAME, AVAILABILITY) VALUES (1, "Ramesh", TRUE);''
cursorObj.execute(insert_query)
print("Row inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM CUSTOMER"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Tutorial Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

The table is created successfully!
Row inserted successfully.
Tutorial Table Data:
(1, ''Ramesh'', 1)

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – FLOAT nhận dự án làm có lương

MySQL – FLOAT

Table of content

The FLOAT data type is a part of the numeric data type. Numeric data types are used to store numbers, and they can be categorized into various subtypes based on their characteristics, such as storage size and precision.

The MySQL FLOAT Data Type

The MySQL FLOAT datatype is a floating-point number type that stores approximate numeric values. It stores approximate numeric values in 4 bytes and represents single-precision values.

FLOAT is suitable for a wide range of numeric values but stores them in an approximate manner due to the IEEE 754 standard limitations.

FLOAT data type can represent both signed and unsigned attributes of a data value in versions prior to MySQL 8.0.17, but the unsigned FLOAT is deprecated in MySQL 8.0.17 and later versions.

Syntax

Following is the basic syntax to set the datatype of a field as FLOAT −

CREATE TABLE (column_name FLOAT, ...);

Example

In this example, let us create a new database table named ”datatype_demo” using CREATE TABLE statement with columns representing FLOAT values −

CREATE TABLE datatype_demo(
   ID INT,
   NAME VARCHAR(50),
   HEIGHT FLOAT,
   WEIGHT FLOAT
);

Following is the output obtained −

Query OK, 0 rows affected (0.03 sec)

Verification

Once the table is created, we can verify the data types of the ”HEIGHT” and ”WEIGHT” fields by retrieving the table”s definition as shown below −

DESC datatype_demo;

The result of the DESC command will show that the ”HEIGHT” and ”WEIGHT” fields have the FLOAT data type −

Field	Type	Null	Default
ID	int	YES	NULL
NAME	varchar(50)	YES	NULL
HEIGHT	float	YES	NULL
WEIGHT	float	YES	NULL

To verify further, let us insert some values into the table using the following INSERT statement −

INSERT INTO datatype_demo VALUES
(1, ''John'', 171.3, 65.7),
(2, ''Rob'', 45, 75),
(3, ''Salman'', 12.74839, 54.262),
(4, ''Arush'', NULL, NULL),
(5, ''James'', ''h'', ''w'');

Following is the output obtained −

ERROR 1265 (01000): Data truncated for column ''HEIGHT'' at row 1

As expected, the FLOAT fields accept single precision floating-point numbers without any issues. However, when attempting to insert non-numeric values into these fields, such as ”h” and ”w,” MySQL raises an error, indicating data truncation.

Finally, to view the data that has been inserted into the table, we can use the SELECT statement as shown below −

SELECT * FROM datatype_demo;

The resultant table is as follows −

ID	NAME	HEIGHT	WEIGHT
1	John	171.3	65.7
2	Rob	45	75
3	Salman	12.7484	54.262
4	Arush	NULL	NULL

Other Representations of MySQL FLOAT

MySQL has a provision to specify the range of precision (not the exponent) for the FLOAT datatype in the form of bits. These bits are specified within the parenthesis following the keyword FLOAT, i.e. FLOAT(p).

However, this precision value is only used to determine the storage size and only holds up to 7 decimal places, with the range from 0 to 23 bits. If the precision bit exceeds 23, the data type becomes DOUBLE.

Example

First, we will drop the existing ”datatype_demo” table −

DROP TABLE datatype_demo;

The output obtained is as follows −

Query OK, 0 rows affected (0.01 sec)

Then, we will create a new table ”datatype_demo” specifying a precision of 20 bits for the ”HEIGHT” column −

CREATE TABLE datatype_demo(
   ID INT,
   NAME VARCHAR(50),
   HEIGHT FLOAT(20)
);

Following is the output of the above code −

Query OK, 0 rows affected (0.02 sec)

Verification

Even though we specified a precision of 20 bits, the ”HEIGHT” column will still store float values within the single-precision range, holding up to 7 decimal places. To verify the table”s definition, we can use the DESC command as shown below −

DESC datatype_demo;

The table produced is as follows −

Field	Type	Null	Default
ID	int	YES	NULL
NAME	varchar(50)	YES	NULL
HEIGHT	float	YES	NULL

If the precision bit exceeds 23, the datatype becomes DOUBLE. Look at the query below −

CREATE TABLE datatype_demo1(
   ID INT,
   NAME VARCHAR(50),
   HEIGHT FLOAT(30)
);

we get the following output −

Query OK, 0 rows affected (0.02 sec)

Again, we can verify the table”s definition using the DESC command −

DESC datatype_demo1;

Following is the table obtained −

Field	Type	Null	Default
ID	int	YES	NULL
NAME	varchar(50)	YES	NULL
HEIGHT	double	YES	NULL

Float Datatype Using a Client Program

We can also create column of the Float datatype using the client program.

Syntax

To create a column of Float datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −

$sql = ''CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))
$mysqli->query($sql);

To create a column of Float datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −

sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
con.query(sql);

To create a column of Float datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
statement.execute(sql);

To create a column of Float datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −

sql = ''CREATE TABLE datatype_demo(ID INT, NAME VARCHAR(50), HEIGHT FLOAT, WEIGHT FLOAT)''''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');

//creating a table temp where we are inserting the celsius and Fahrenheit values in float
$sql = ''CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
// insert data into created table
$q = " INSERT INTO temp(Celsius, Fahrenheit) VALUES ( 36.2, 97.16), ( 35.8, 96.44), ( 37.32, 99.17), ( 35.89, 96.602);";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM temp";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Celsius: %f, Fahrenheit: %f", $row["Id"], $row["Celsius"], $row["Fahrenheit"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Output

The output obtained is as follows −

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Celsius: 36.200000, Fahrenheit: 97.160000
 ID: 2, Celsius: 35.800000, Fahrenheit: 96.440000
 ID: 3, Celsius: 37.320000, Fahrenheit: 99.170000
 ID: 4, Celsius: 35.890000, Fahrenheit: 96.602000

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a student table, that accepts one column of float type.
  sql =
    "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
  con.query(sql);

  //insert data into created table
  sql =
    "INSERT INTO temp(Celsius, Fahrenheit) VALUES ( 36.2, 97.16), ( 35.8, 96.44), ( 37.32, 99.17), ( 35.89, 96.602)";
  con.query(sql);
  //select datatypes of salary
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''temp'' AND COLUMN_NAME = ''Celsius''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[ { DATA_TYPE: ''float'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Float {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Float data types...!;
         String sql = "CREATE TABLE temp(Id INT AUTO_INCREMENT, Celsius FLOAT, Fahrenheit FLOAT, PRIMARY KEY(Id))";
         statement.execute(sql);
         System.out.println("column of a Float type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE temp");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
column of a Float type created successfully...!
Id int
Celsius float
Fahrenheit float

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with float column
sql = ''''''
CREATE TABLE datatype_demo(ID INT,
NAME VARCHAR(50),
HEIGHT FLOAT,
WEIGHT FLOAT
)
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (1, ''John'', 171.3, 65.7),
    (2, ''Rob'', 45, 75),
    (3, ''Salman'', 12.74839, 54.262),
    (4, ''Arush'', None, None),
]
# Insert data into the created table
insert_query = "INSERT INTO datatype_demo (ID, NAME, HEIGHT, WEIGHT) VALUES (%s, %s, %s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM datatype_demo"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''John'', 171.3, 65.7)
(2, ''Rob'', 45.0, 75.0)
(3, ''Salman'', 12.7484, 54.262)
(4, ''Arush'', None, None)

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – Create Trigger nhận dự án làm có lương

MySQL – CREATE TRIGGER

Table of content

Triggers are generally defined as responses to an event. For instance, when we hover a mouse-pointer on a drop-down menu of a website, a various set of options to navigate through this website are then displayed. Here, the hovering of the mouse-pointer is an event while the display of options in the drop-down menu is a result of trigger execution. This concept is also introduced in MySQL.

Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked.

Triggers are, in fact, written to be executed in response to any of the following events −

A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
A database definition (DDL) statement (CREATE, ALTER, or DROP).
A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Creating Trigger in MySQL

You can create a trigger using the CREATE TRIGGER Statement.

Syntax

Following is the syntax of the MySQL CREATE TRIGGER Statement.

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
...
END;

Where,

trigger_name is the name of the trigger you need to create
trigger_time is the time of trigger activation
trigger_event can be INSERT, UPDATE, or DELETE. This event causes the trigger to be invoked.
table_name is the name of the table to which the trigger is associated with.

Example

Assume we have created a table with name student as shown below −

CREATE TABLE STUDENT(
   Name varchar(35),
   Age INT,
   Score INT
);

Following query creates a trigger, this will set the score value 0 if you enter a value that is less than 0 as score.

DELIMITER //
CREATE TRIGGER sample_trigger BEFORE INSERT ON STUDENT FOR EACH ROW
BEGIN
IF NEW.Score < 0 THEN SET NEW.Score = 0;
END IF;
END //
DELIMITER ;

Verification

If you try to insert records in the student table and if you use a value that is less than 0 as age it will be automatically set to 0.

INSERT INTO STUDENT VALUES
(''Jeevan'', 22, 8),
(''Raghav'', 26, -3),
(''Pooja'', 21, -9),
(''Devi'', 30, 9);

The STUDENT table created will have the following records −

Name	Age	Score
Jeevan	22	8
Raghav	26	0
Pooja	21	0
Devi	30	9

As we can see, there are no negative values inserted in the table as they are all replaced with zeroes.

Creating Trigger Using a Client Program

We can also Create a trigger using a client program.

Syntax

To Create a Trigger through a PHP program, we need to execute the CREATE statement using the mysqli function named query() as follows −

$sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score";
$mysqli->query($sql);

To Create a Trigger through a JavaScript program, we need to execute the CREATE statement using the query() function of mysql2 library as follows −

sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score"
con.query(sql);

To Create a Trigger through a Java program, we need to execute the CREATE statement using the JDBC function execute() as follows −

String sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score";
statement.execute(sql);

To Create a Trigger through a python program, we need to execute the CREATE statement using the execute() function of the MySQL Connector/Python as follows −

create_trigger_query = ''CREATE TRIGGER sample_trigger
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
   IF NEW.Score < 0 THEN
      SET NEW.Score = 0;
   END IF;
END''
cursorObj.execute(create_trigger_query)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score";
if($mysqli->query($sql)){
   printf("Trigger created successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Trigger created successfully...!

var mysql = require(''mysql2'');
var con = mysql.createConnection({
   host:"localhost",
   user:"root",
   password:"password"
});

//Connecting to MySQL
con.connect(function(err) {
   if (err) throw err;
   //console.log("Connected successfully...!");
   //console.log("--------------------------");
   sql = "USE TUTORIALS";
   con.query(sql);
   sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score"
   con.query(sql);
   console.log("Trigger created successfully....!");
   sql = "SHOW TRIGGERS";
   con.query(sql, function(err, result){
      if (err) throw err;
      console.log(result);
   });
});

Output

The output produced is as follows −

Trigger created successfully....!
[
  {
    Trigger: ''testTrigger'',
    Event: ''UPDATE'',
    Table: ''student'',
    Statement: "INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score",
    Timing: ''AFTER'',
    Created: 2023-08-01T05:21:18.540Z,
    sql_mode: ''IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'',
    Definer: ''root@localhost'',
    character_set_client: ''utf8mb4'',
    collation_connection: ''utf8mb4_unicode_ci'',
    ''Database Collation'': ''utf8mb4_0900_ai_ci''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CreateTrigger {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection con = DriverManager.getConnection(url, user, password);
         Statement st = con.createStatement();
         //System.out.println("Database connected successfully...!");
         String sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score";
         st.execute(sql);
         System.out.print("Trigger created successfully....!");

      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Output

The output obtained is as shown below −

Trigger created successfully....!

import mysql.connector
#Establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
table_name = ''Student''
trigger_name = ''sample_trigger''
# Creating a cursor object
cursorObj = connection.cursor()
# Create table
create_table_query = ''''''
CREATE TABLE Student(
   Name VARCHAR(35),
   age INT,
   Score INT
)
''''''
cursorObj.execute(create_table_query)
print("The table is created successfully!")
# Creating a trigger
create_trigger_query = f''''''CREATE TRIGGER {trigger_name}
BEFORE INSERT ON {table_name}
FOR EACH ROW
BEGIN
   IF NEW.Score < 0 THEN
      SET NEW.Score = 0;
   END IF;
END''''''
cursorObj.execute(create_trigger_query)
print(f"Trigger ''{trigger_name}'' is created successfully.")
# Commit the changes and close the cursor and connection
connection.commit()
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

The table is created successfully!
Trigger ''sample_trigger'' is created successfully.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – BIT nhận dự án làm có lương
MySQL – BIT
Table of content
A bit represents the basic unit of data in programming languages. It can store only two values, represented as 0 or 1.

The MySQL BIT Data Type

The MySQL BIT data type is used to store binary values within a specified range. The range is determined by the number of bits you allocate to the BIT column.

If we try to insert an integer value instead of BIT values, MySQL automatically converts them into BIT values. We have to ensure that the integer value we are adding must be within the range for conversion to BIT values.

For instance, if you have a BIT(3) column, it can store values from 000 to 111 in binary, which corresponds to 0 to 7 in integer format. If you try to insert the integer 8 into this BIT(3) column, you”ll get an error because 8 in binary is 1000, which is outside the valid range of the column.

Syntax

Following is the syntax of the MySQL BIT datatype −

BIT(n)

Here, the range of n value is from 1 to 64. If you don”t provide the “n” value, the default is 1, resulting in a single-bit BIT column. Hence, the following queries will give the same output −

Column_name BIT(1); and Column_name BIT;

Bit Value Literal

To specify bit value literals, you can use the b”val or 0bval notations, where val is a binary value containing only 0s and 1s. The leading ”b” is case-insensitive.

b01 B11
Note that the 0b notation is case-sensitive, so 0B”1000” is an invalid bit literal value.

0B''1000''

Example

Let us create a table named STUDENTS and use the BIT data type for the AGE column as shown below −

CREATE TABLE STUDENTS(
   ID int auto_increment,
   NAME varchar(40),
   AGE BIT(3),
   primary key (ID)
);

Following is the output obtained −

Query OK, 0 rows affected (0.01 sec)

Now, we are inserting the values “5” and “3” into the AGE column of the STUDENTS table −

INSERT INTO STUDENTS (NAME, AGE) VALUES
(''Varun'', 5),
(''Akash'', 3);

Output of the above query is as shown below −

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

We can use the following query to display the inserted values in the AGE column of the STUDENTS table −

SELECT * from STUDENTS;

We can see in the output below that the values “5” and “3” are stored in binary format −

ID	NAME	AGE
1	Varun	0x05
2	Akash	0x03

Now, let us insert another value “10”. In binary format, “10” is represented as “1010”. However, we defined the AGE column to have a range of only three bits. Therefore, the following query will generate an error because the value 10 is greater than 7 −

INSERT INTO STUDENTS (NAME, AGE) VALUES (''Priya'', 10);

The output indicates that the data is too long for the AGE column.

ERROR 1406 (22001): Data too long for column ''AGE'' at row 1

To insert bit value literals into the “AGE” column, you can use the B”val notation. Here, we are inserting “110” which is equivalent to the integer value “6” as shown below −

INSERT INTO STUDENTS (NAME, AGE) VALUES(''Priya'', B''110'');

The result obtained is as follows −

Query OK, 1 row affected (0.01 sec)

Let us display all the records in the “STUDENTS” table using the following query −

SELECT * from STUDENTS;

We can see in the output below that the value “6” has been inserted in binary format as “0x06” −

ID	NAME	AGE
1	Varun	0x05
2	Akash	0x03
3	Priya	0x06

Verification

To verify and display the inserted values in the “AGE” column in binary/bit format, you can use the MySQL BIN() function −

SELECT ID, NAME, BIN(AGE) FROM STUDENTS;

The output shows the values in binary format −

ID	NAME	BIN(AGE)
1	Varun	101
2	Akash	11
3	NULL	110

In the above output, we can see that the leading zeros are removed. If we want to display them, we can use the LPAD function as shown below −

SELECT ID, NAME, LPAD(BIN(AGE), 5, "0") FROM STUDENTS;

Following is the output obtained −

ID	NAME	LPAD(BIN(AGE), 5, “0”)
1	Varun	00101
2	Akash	00011
3	NULL	00110

BIT Datatype Using a Client Program

We can also create column of the BIT datatype using the client program.

Syntax

To create a column of BIT datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −

$sql = ''CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )
$mysqli->query($sql);

To create a column of BIT datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −

sql = "CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )";
con.query(sql);

To create a column of BIT datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID))";
statement.execute(sql);

To create a column of BIT datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −

sql = ''CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3),  primary key (ID) )''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');

//creating a table student where age is bit data types
$sql = ''CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
// insert data into created table
$q = " INSERT INTO students (NAME, AGE) VALUES (''Varun'', 5), (''Akash'', 3)";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM students";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Age: %d", $row["ID"], $row["AGE"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Output

The output obtained is as follows −

Table created successfully...!
Data inserted successfully...!
Table Records:
ID: 1, Age: 5
ID: 2, Age: 3

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a students table, that accepts one column of bit type.
  sql = "CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID) )";
  con.query(sql);

  //insert data into created table
  sql = "INSERT INTO students (NAME, AGE) VALUES (''Varun'', 5), (''Akash'', 3)";
  con.query(sql);

  //select datatypes of salary
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''students'' AND COLUMN_NAME = ''AGE''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[ { DATA_TYPE: ''bit'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Bit {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Bit data types...!;
         String sql = "CREATE TABLE students(ID int auto_increment, NAME varchar(40), AGE BIT(3), primary key (ID))";
         statement.execute(sql);
         System.out.println("column of a BIT type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE students");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
column of a BIT type created successfully...!
ID int
NAME varchar(40)
AGE bit(3)

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with BIT column
sql = ''''''
CREATE TABLE students(
ID int auto_increment,
NAME varchar(40),
AGE BIT(3),
primary key (ID)
)
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (''Varun'', 5),
    (''Akash'', 3)
]
# Insert data into the created table
insert_query = "INSERT INTO students (NAME, AGE) VALUES (%s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM students"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''Varun'', 5)
(2, ''Akash'', 3)

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – Triggers nhận dự án làm có lương

MySQL – Triggers

Table of content

Generally, a Trigger is defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, that is executed automatically (without being called explicitly like regular stored procedures) whenever an event is performed. These events include statements like INSERT, UPDATE and DELETE etc.

To run a MySQL trigger, the user must have admin/superuser privileges.

As per the SQL standard, triggers are usually divided into two categories −

Row-level Trigger: Triggers that are only executed when each row is either inserted, updated or deleted in a database table. MySQL only supports these type of triggers.
Statement-level Trigger: Triggers like these are executed on the transaction level, once, no matter how many rows are modified in a table. MySQL does not support these trype of triggers.

Types of Triggers in MySQL

There are six types of row-level triggers in MySQL. They are:

Before Insert Trigger

The Before Insert Trigger is performed before any value is inserted into the table. Whenever an INSERT statement is executed, the Before Insert trigger goes off, followed by the insertion transaction.

After Insert Trigger

The After Insert Trigger works opposite to the Before Insert Trigger. As implied by its name, it is performed after any value is inserted into the table. Whenever an INSERT statement is executed, the value is inserted into the table first followed by the execution of the trigger.

Before Update Trigger

The Before Update Trigger is performed before any value is updated or modified in the table. Whenever an UPDATE statement is executed, the Before Update trigger goes off, followed by the update transaction.

After Update Trigger

The After Update Trigger works opposite to the Before Update Trigger. As implied by its name, it is performed after any value is updated in the table. Whenever an UPDATE statement is executed, the value is updated in the table first followed by the execution of the trigger.

Before Delete Trigger

The Before Delete Trigger is performed before any value is deleted from the table. Whenever a DELETE statement is executed, the Before Delete trigger goes off, followed by the deletion transaction.

After Delete Trigger

The After Delete Trigger works opposite to the Before Delete Trigger. As implied by its name, it is performed after any value is deleted from the table. Whenever an DELETE statement is executed, the value is deleted from the table first followed by the execution of the trigger.

Advantages of Triggers

Triggers hold a lot of advantages in MySQL database. They are listed as follows −

Triggers help the database to maintain the integrity of the data stored.
Triggers are also a means to handle errors from the database layer itself.
As triggers are invoked automatically without being called explicitly, you don”t have to wait for the scheduled events to run.
Triggers can be useful to track the data changes made in the tables, by logging the events.
MySQL Triggers can also prevent invalid transactions from being executed.

Disadvantages of Triggers

However, there are disadvantages of using triggers in a MySQL database. Some of them are listed as follows −

Triggers cannot replace all validations, and only provide extended validations. For simple validations, you can use the NOT NULL, UNIQUE, CHECK and FOREIGN KEY constraints.
As triggers are invisible to the client application, it is impossible to understand what goes on in the database layer. Hence, making it difficult to troubleshoot.
Triggers are not beneficial for use with high-velocity data i.e. the data when a number of events per second are high.
Triggers may increase the overhead of the MySQL Server.

Restrictions on Triggers

Following are some of the restrictions that apply to MySQL triggers −

One trigger for each event − Each table can have only one trigger for each event combination, i.e. you can”t define two same triggers for the same table.
RETURN statement is not permitted − As triggers don”t return any values, the RETURN statement is not permitted.
Foreign key restriction − Triggers are not activated by foreign key actions.
Outdated metadata − Suppose, if a trigger is loaded into cache, it is not automatically reloaded when the table metadata changes. In this case, a trigger can operate using outdated metadata.
Cannot use ”CALL” statement − We cannot use the CALL statement in triggers.
Cannot create a TEMPORARY table or a view − We cannot create a view for a temporary table or a view.
Not activated by changes in INFORMATION_SCHEMA − Actually, triggers are not activated by changes made in INFORMATION_SCHEMA or performance_schema tables. It is because these tables are views and triggers are not permitted on views.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – INT nhận dự án làm có lương

MySQL – INT

Table of content

The MySQL INT Data Type

The MySQL INT data type is used to store whole numbers without the decimal places (integers).

However, MySQL provides various integer data types, such as TINYINT, SMALLINT, MEDIUMINT, and BIGINT to cater to different ranges of whole numbers.

The following table illustrates the characteristics of different integer types in MySQL, including storage in bytes, minimum value, and maximum value for both signed and unsigned options −

Type	Storage (Bytes)	Minimum value (Signed/Unsigned)	Maximum value (Signed/Unsigned)
TINYINT	1	-128/ 0	127/ 255
SMALLINT	2	-32768/ 0	32767/ 65535
MEDIUMINT	3	-8388608/ 0	8388607/ 16777215
INT	4	-8388607/ 16777215	2147483647/ 4294967295
BIGINT	8	– 9223372036854775808 / 0	9223372036854775807 / 18446744073709551615

We have to choose the data types based on the kind (type) of data being stored. If possible, we need to use smaller data types to minimize the database size. TINYINT can be used for small numbers, while INT or BIGINT is used for large numbers like phone numbers in a country.

Auto Increment with MySQL INT

In MySQL, you can use the AUTO_INCREMENT attribute with an INT column to automatically generate unique values for that column. Here”s how it works −

Initial Value − When you create a table with an AUTO_INCREMENT INT column, the sequence starts with 1.
Inserting NULL or 0 − When you insert a record with a NULL or 0 value for the AUTO_INCREMENT column, MySQL sets the value to the next sequence value. This means it assigns the next available integer starting from 1.
Inserting Non-NULL Values − If you insert a non-NULL value into the AUTO_INCREMENT column, MySQL accepts that value and continues the sequence based on the new value inserted.

Example

First of all, we are creating a table named STUDENTS with an AUTO_INCREMENT INT column named “ID” −

CREATE TABLE STUDENTS (
   ID int auto_increment,
   NAME varchar(20),
   primary key (ID)
);

When we insert records into this table without specifying values for the “ID” column, MySQL automatically generates unique values for “ID” starting from 1.

Here, we are inserting three rows into the STUDENTS table using the below INSERT query −

INSERT INTO STUDENTS (NAME) VALUES
(''Tilak''), (''Akash''), (''Surya''), (''Deepak'');

The STUDENTS table created is as follows −

ID	NAME
1	Tilak
2	Akash
3	Surya
4	Deepak

Now, let us insert a row where we provide an explicit value for the “ID” column −

INSERT INTO STUDENTS (ID, NAME) VALUES (15, ''Arjun'');

Following is the output obtained −

Query OK, 1 row affected (0.01 sec)

Since we specified the “ID” as 15, MySQL resets the sequence to 16. If we insert a new row without specifying the “ID,” MySQL will use 16 as the next AUTO_INCREMENT value −

INSERT INTO STUDENTS (NAME) VALUES (''Dev'');

The output obtained is as follows −

Query OK, 1 row affected (0.01 sec)

Now, let us retrieve the records from the “STUDENTS” table −

SELECT * FROM STUDENTS;

The table produced is −

ID	NAME
1	Tilak
2	Akash
3	Surya
4	Deepak
15	Arjun
16	Dev

From MySQL 5.1 version and onwards, the AUTO_INCREMENT column accepts only positive values and does not allow negative values.

MySQL INT UNSIGNED

In MySQL, when you define an UNSIGNED INT on a column, that column is restricted to storing only non-negative values (i.e., positive values). Negative values are not allowed in such columns.

Example

Let us create a table with the name EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   ID int auto_increment,
   NAME varchar(30) not null,
   AGE int UNSIGNED,
   Primary key(ID)
);

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

Now, let us insert a row with a non-negative value into the “AGE” column −

INSERT INTO EMPLOYEES (NAME, AGE) VALUES (''Varun'', 32);

The above query will execute successfully since the value provided for the “AGE” column is non-negative.

Query OK, 1 row affected (0.01 sec)

However, if we attempt to insert a negative value into the “AGE” column, MySQL will generate an error −

INSERT INTO EMPLOYEES (NAME, AGE) VALUES (''Sunil'', -10);

MySQL will issue an error as shown below −

ERROR 1264 (22003): Out of range value for column ''AGE'' at row 1

MySQL INT with Display Width Attribute

In MySQL, you can specify a display width for the INT data type by using parentheses after the INT keyword. For instance, using INT(5) sets the display width to five digits.

It”s important to note that the display width attribute for INT in MySQL doesn”t affect the range of values that can be stored in the column. It formats integer values in applications, and is included as metadata in the result set.

For example, if you insert the value 12345 into the id column of the EMPLOYEES table, it will be stored as is. When you retrieve it, some applications may choose to pad it with leading zeros to ensure it is displayed as five digits (e.g., 012345).

MySQL INT with ZEROFILL Attribute

In MySQL, the ZEROFILL attribute is a non-standard attribute that can be applied to numeric data types. It adds leading zeros to the displayed values, making sure the number is displayed with a fixed width, especially useful for numerical codes.

Example

Let us create a table with the name ZEROFILL_TABLE with ZEROFILL applied to INT columns using the query below −

CREATE TABLE ZEROFILL_TABLE (
   col1 int(4) ZEROFILL,
   col2 int(6) ZEROFILL,
   col3 int(8) ZEROFILL
);

The output obtained is as follows −

Query OK, 0 rows affected, 6 warnings (0.02 sec)

Now, we are inserting a new row into the above-created table −

INSERT INTO ZEROFILL_TABLE (col1, col2, col3)
VALUES (1, 7, 3);

Following is the output of the above code −

Query OK, 1 row affected (0.00 sec)

Now, let us display the records from the ZEROFILL_TABLE table −

SELECT * FROM ZEROFILL_TABLE;

We can see in the output below, the values are displayed with the specified width, and leading zeros are added to maintain that width, as determined by the ZEROFILL attribute −

col1	col2	col3
0001	000007	00000003

INT Datatype Using a Client Program

We can also create column of the INT datatype using the client program.

Syntax

To create a column of INT datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −

$sql = ''CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )
$mysqli->query($sql);

To create a column of INT datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −

sql = "CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )";
con.query(sql);

To create a column of INT datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE students(Id INT AUTO_INCREMENT primary key not null, reg_No INT)";
statement.execute(sql);

To create a column of INT datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −

sql = ''CREATE TABLE students (ID int auto_increment, NAME varchar(20), primary key (ID) )''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
//create table with boolean column
$sql = ''CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )
$result = $mysqli->query($sql);
if ($result) {
   printf("Table created successfully...!n");
}
//insert data into created table
$q = "INSERT INTO students (reg_No) VALUES (101), (102)";
if ($res = $mysqli->query($q)) {
   printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT Id, reg_No FROM students";
if ($r = $mysqli->query($s)) {
   printf("Table Records: n");
   while ($row = $r->fetch_assoc()) {
      printf(" ID: %d, Reg: %s", $row["Id"], $row["reg_No"]);
      printf("n");
   }
} else {
   printf(''Failed'');
}
$mysqli->close();

Output

The output obtained is as follows −

Table Records:
ID: 1, Reg: 101
ID: 2, Reg: 102

var mysql = require("mysql2");
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
   if (err) throw err;
   //console.log("Connected successfully...!");
   //console.log("--------------------------");
   sql = "USE TUTORIALS";
   con.query(sql);

   //create a students table, that accepts one column of int type.
   sql = "CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )";
   con.query(sql);

   //insert data into created table
   sql = "INSERT INTO students (reg_No) VALUES (101), (102)";
   con.query(sql);

   //select datatypes of salary
   sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''students'' AND COLUMN_NAME = ''reg_No''`;
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
   });
});

Output

The output produced is as follows −

[ { DATA_TYPE: ''int'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Int {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Int data types...!;
         String sql = "CREATE TABLE students(Id INT AUTO_INCREMENT primary key not null, reg_No INT)";
         statement.execute(sql);
         System.out.println("column of a INT type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE students");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
column of a INT type created successfully...!
Id int
reg_No int

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with int column
sql = ''''''CREATE TABLE students (
ID int auto_increment,
NAME varchar(20),
primary key (ID)
)''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (1, ''Tilak''),
    (2, ''Akash''),
    (3, ''Surya''),
    (4, ''Deepak''),
    (15, ''Arjun''),
    (16, ''Dev'')
]
# Insert data into the created table
insert_query = "INSERT INTO STUDENTS (ID, NAME) VALUES (%s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM Students"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''Tilak'')
(2, ''Akash'')
(3, ''Surya'')
(4, ''Deepak'')
(15, ''Arjun'')
(16, ''Dev'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – Show Trigger nhận dự án làm có lương

MySQL – SHOW TRIGGERS

Table of content

MySQL provides a statement to list out all the existing triggers present in a database. Knowing the trigger information can be useful while creating new triggers, so that a user wouldn”t use the same name for multiple triggers.

Show Triggers in MySQL

The SHOW TRIGGERS Statement is used in MySQL to display information about all the triggers defined in the current database.

Syntax

Following is the syntax of the MySQL SHOW TRIGGERS Statement −

SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE ''pattern'' | WHERE expr]

Example

In this example, we are creating a table named STUDENT using the query below −

CREATE TABLE STUDENT(
   Name varchar(35),
   Age INT,
   Score INT
);

Following query creates a trigger sample_trigger which will set the score value to 0 if you enter a negative score value into the table.

DELIMITER //
CREATE TRIGGER sample_trigger
BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
 IF NEW.score < 0 THEN SET NEW.score = 0;
END IF;
END //
DELIMITER ;

Assume we have created another trigger using the AFTER clause −

DELIMITER //
CREATE TRIGGER testTrigger
AFTER UPDATE ON Student
FOR EACH ROW
BEGIN
 INSERT INTO Student
 SET action = ''update'',
 Name = OLD.Name,
 Age = OLD.age,
 Score = OLD.score;
END;
END //

DELIMITER ;

Following query shows the existing triggers in the current database −

SHOW TRIGGERS G;

Output

The list of triggers will be displayed as follows −

*************************** 1. row ***************************
Trigger: sample_trigger
Event: INSERT
Table: student
Statement: BEGIN
           IF NEW.score < 0 THEN SET NEW.score = 0;
           END IF;
           END
Timing: BEFORE
Created: 2021-05-12 19:08:04.50
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
Trigger: testTrigger
Event: UPDATE
Table: student
Statement: INSERT INTO Student
           SET Name = OLD.Name,
               Age = OLD.age,
               Score = OLD.score
Timing: AFTER
Created: 2021-05-12 19:10:44.49
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.00 sec)

With FROM or IN Clause

You can retrieve the information of triggers from a specific database using the FROM clause.

Example

Assume that the current database is named demo. Following query shows the triggers present in the database demo −

SHOW TRIGGERS FROM demoG

You can also use the IN clause instead of FROM, to get the same output.

SHOW TRIGGERS IN demoG

Output

The existing triggers present in the demo database −

*************************** 1. row ***************************
             Trigger: sample_trigger
               Event: INSERT
               Table: student
           Statement: BEGIN
 IF NEW.score < 0 THEN SET NEW.score = 0;
END IF;
END
              Timing: BEFORE
             Created: 2023-09-29 11:42:33.58
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
             Trigger: testTrigger
               Event: UPDATE
               Table: student
           Statement: BEGIN
 INSERT INTO Student
 SET action = ''update'',
 Name = OLD.Name,
 Age = OLD.age,
 Score = OLD.score;
END
              Timing: AFTER
             Created: 2023-09-29 11:43:10.27
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.00 sec)

With WHERE Clause

You can use the WHERE clause of the SHOW TRIGGERS statements to retrieve info about the triggers which match the specified condition.

Example

Following query retrieves the triggers in the current database whose event is update −

SHOW TRIGGERS FROM demo WHERE Event = ''UPDATE'' G;

Output

The required list of triggers is displayed as follows −

*************************** 1. row ***************************
             Trigger: testTrigger
               Event: UPDATE
               Table: student
           Statement: BEGIN
 INSERT INTO Student
 SET action = ''update'',
 Name = OLD.Name,
 Age = OLD.age,
 Score = OLD.score;
END
              Timing: AFTER
             Created: 2023-09-29 11:43:10.27
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Showing Trigger Using Client Program

We can also Show a trigger using a client program.

Syntax

To show a trigger through a PHP program, we need to execute the SHOW TRIGGERS statement using the mysqli function query() as follows −

$sql = "Show TRIGGER";
$mysqli->query($sql);

To show a trigger through a JavaScript program, we need to execute the SHOW TRIGGERS statement using the query() function of mysql2 library as follows −

sql = "Show TRIGGER";
con.query(sql);

To show a trigger through a Java program, we need to execute the SHOW TRIGGERS statement using the JDBC function executeQuery() as follows −

String sql = "Show TRIGGER";
statement.executeQuery(sql);

To show a trigger through a python program, we need to execute the SHOW TRIGGERS statement using the execute() function of the MySQL Connector/Python as follows −

Show_trigger_query = ''SHOW TRIGGER''
cursorObj.execute(Show_trigger_query)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
printf(''Connected successfully.
'');

// Create a trigger
$sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score";

if ($mysqli->query($sql)) {
    printf("Trigger created successfully...!
");
} else {
    printf("Trigger creation failed: %s
", $mysqli->error);
}

// Show created trigger details
$sql = "SHOW TRIGGERS";
$res = $mysqli->query($sql);
if ($res) {
    while ($row = $res->fetch_assoc()) {
        // Print trigger details
        foreach ($row as $key => $value) {
            printf("%s: %s
", $key, $value);
        }
        printf("
");
    }
    $res->free();
} else {
    printf("Failed to retrieve triggers: %s
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Connected successfully.
Trigger created successfully...!
Trigger: testTrigger
Event: UPDATE
Table: student
Statement: INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score
Timing: AFTER
Created: 2023-09-08 12:16:27.54
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SHOW TRIGGERS";
 con.query(sql);
 console.log("show trigger query executed successfully..!");
 console.log("Triggers: ");
 sql = "SHOW TRIGGERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Output

The output produced is as follows −

show trigger query executed successfully..!
Triggers:
[
  {
    Trigger: ''testTrigger'',
    Event: ''UPDATE'',
    Table: ''student'',
    Statement: "INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score",
    Timing: ''AFTER'',
    Created: 2023-08-01T05:21:18.540Z,
    sql_mode: ''IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'',
    Definer: ''root@localhost'',
    character_set_client: ''utf8mb4'',
    collation_connection: ''utf8mb4_unicode_ci'',
    ''Database Collation'': ''utf8mb4_0900_ai_ci''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowTrigger {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "Create Trigger sample_trigger BEFORE INSERT ON student FOR EACH ROW BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END";
            st.execute(sql);
            System.out.println("Trigger created successfully...!");
            String sql1 = "SHOW TRIGGERS";
            rs = st.executeQuery(sql1);
            System.out.println("Triggers: ");
            while(rs.next())
            {
               String triggers = rs.getNString(1);
               System.out.println(triggers);
            }

      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Output

The output obtained is as shown below −

Trigger created successfully...!
Triggers:
sample_trigger
testTrigger

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''Student''
trigger_name = ''sample_trigger''
# Creating a cursor object
cursorObj = connection.cursor()
# show trigger
show_triggers_query = "SHOW TRIGGERS"
cursorObj.execute(show_triggers_query)
result = cursorObj.fetchall()
print("Triggers in the database:")
for row in result:
    print(row)
# close the cursor and connection
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Triggers in the database:
(''sample_trigger'', ''INSERT'', ''student'', b''BEGINn    IF NEW.Score < 0 THENn        SET NEW.Score = 0;n    END IF;nEND'', ''BEFORE'', datetime.datetime(2023, 7, 31, 11, 38, 5, 880000), {''STRICT_TRANS_TABLES'', ''NO_ENGINE_SUBSTITUTION''}, ''root@localhost'', ''utf8mb4'', ''utf8mb4_0900_ai_ci'', ''utf8mb4_0900_ai_ci'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024