Category: mysql

MySQL − Insert on Duplicate Key Update Table of content MySQL Insert on Duplicate Key Update Statement INSERT or UPDATE multiple records at once Client Program ”; Previous Next The INSERT INTO statement in MySQL is used to insert new records into a specific table. MySQL Insert on Duplicate Key Update Statement When we are trying to insert a new row into a MySQL table column with a UNIQUE INDEX or PRIMARY KEY, MySQL will issue an error, if the value being inserted already exists in the column. This will happen because these constraints require unique values, and duplicate values are not allowed. However, if we use the MySQL ON DUPLICATE KEY UPDATE clause with with the INSERT INTO statement, MySQL will update the existing rows with the new values instead of showing an error. Syntax Following is the basic syntax of ON DUPLICATE KEY UPDATE clause in MySQL − INSERT INTO my_table (col1, col2, …) VALUES (val1, val2), (val3, val4), … ON DUPLICATE KEY UPDATE <col1>=<val1>, <col2>=<val2>,…; Example First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS char (25), SALARY decimal (18, 2), PRIMARY KEY (ID) ); Here, we are inserting some records into the above-created table using the INSERT INTO statement as shown below − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ); Execute the following query to display the records present in the above created CUSTOMERS table − SELECT * FROM CUSTOMERS; Following are the records in CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 Here, we are inserting another row into the CUSTOMERS table with an ID value 3 using the INSERT INTO statement − INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3, ”Chaitali”, 25, ”Mumbai”, 6500.00); As a result, MySQL will issue an error because we are inserting a duplicate ID value − ERROR 1062 (23000): Duplicate entry ”3” for key ”customers.PRIMARY” We can avoid the above error and update the existing row with the new information using the ON DUPLICATE KEY UPDATE clause along with INSERT INTO statement as shown below − INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3, ”Chaitali”, 25, ”Mumbai”, 6500.00) ON DUPLICATE KEY UPDATE NAME = “Chaitali”, AGE = 25, ADDRESS = “Mumbai”, SALARY = 6500.00; Output As we can see in the output, the above query updated the existing row in the CUSTOMERS table. As a result, it returns two affected-rows. Query OK, 2 rows affected (0.01 sec) Verification Execute the following query to verify whether the existing row got updated with new information or not − SELECT * FROM CUSTOMERS; As we observe the third row in the table, the records got updated. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Chaitali 25 Mumbai 6500.00 Example In the following query, we are trying to insert a new row into the CUSTOMERS table using the INSERT INTO statement along with the ON DUPLICATE KEY UPDATE clause − INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (4, ”Hardik”, 27, ”Bhopal”, 8500.00) ON DUPLICATE KEY UPDATE NAME = “Hardik”, AGE = 27, ADDRESS = “Bhopal”, SALARY = 8500.00; Output As we can see in the output, there is no conflict occurred while inserting the new row. As a result, it returns one affected-row. Query OK, 1 row affected (0.01 sec) Verification We can verify whether the new row is inserted in the CUSTOMERS table or not using the following query − SELECT * FROM CUSTOMERS; As we observe the output below, the new row has been inserted. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Chaitali 25 Mumbai 6500.00 4 Hardik 27 Bhopal 8500.00 INSERT or UPDATE multiple records at once While inserting or updating multiple records at the same time in MySQL, the value to set for each column may vary depending on which record or records have a conflict. For example, if we are trying to insert four new rows, but the third has an ID column that conflicts with an existing record, we most likely want to update the existing row based on the data you had in mind for the third row. Example Before we perform the next operation, let”s look into the records of updated CUSTOMERS table − SELECT * FROM CUSTOMERS; Following is the updated CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Chaitali 25

MySQL – CREATE VIEW Table of content MySQL Create View Statement The With Check Option Creating a MySQL View Using Client Program ”; Previous Next MySQL views are a type of virtual tables. They are stored in the database with an associated name. They allow users to do the following − Structure data in a way that users or classes of users find natural or intuitive. Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more. Summarize data from various tables which can be used to generate reports. A view can be created from one or more tables, containing either all or selective rows from them. Unless indexed, a view does not exist in a database. MySQL Create View Statement Creating a view is simply creating a virtual table using a query. A view is an SQL statement that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query. Syntax Following is the syntax of the CREATE VIEW Statement − CREATE VIEW view_name AS select_statements FROM table_name; Example Assume we have created a table using the SELECT statement as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(15) NOT NULL, AGE INT NOT NULL, ADDRESS VARCHAR(25), SALARY DECIMAL(10, 2), PRIMARY KEY(ID) ); Let us insert 7 records in the above created table − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2500), (4, ”Chaitali”, ”26”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”MP”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); Following query creates a view based on the above create table − CREATE VIEW first_view AS SELECT * FROM CUSTOMERS; Verification You can verify the contents of a view using the select query as shown below − SELECT * FROM first_view; The view will be created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 With REPLACE and IF NOT EXISTS Clauses Usually, if you try to create a view with the name same as an existing view an error will be generated as shown as − CREATE VIEW first_view AS SELECT * FROM CUSTOMERS; As the view already exists, following error is raised − ERROR 1050 (42S01): Table ”first_view” already exists So, you can use the REPLACE clause along with CREATE VIEW to replace the existing view. CREATE OR REPLACE VIEW first_view AS SELECT * FROM CUSTOMERS; With WHERE Clause We can also create a view using the where clause as shown below − CREATE VIEW test_view AS SELECT * FROM CUSTOMERS WHERE SALARY>3000; Following are the contents of the above created view − ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 The With Check Option The WITH CHECK OPTION is an option used with CREATE VIEW statement. The purpose of this WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) in the query. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error. Syntax Following is the syntax − CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WITH CHECK OPTION; Example In the following example, we are creating a view using CREATE VIEW statement along with the WITH CHECK OPTION − CREATE VIEW NEW_VIEW AS SELECT * FROM CUSTOMERS WHERE NAME IS NOT NULL WITH CHECK OPTION; The view is created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Creating a MySQL View Using Client Program In addition to creating a view in MySQL Database using the SQL queries, we can also do so using a client program. Syntax Following are the syntaxes of the Create View into MySQL in various programming languages − PHP NodeJS Java Python The MySQL PHP connector mysqli provides a function named query() to execute a CREATE VIEW query in the MySQL database. $sql=”CREATE VIEW views_name AS SELECT col_1, col_2, col_3 FROM table_name”; $mysqli->query($sql); The MySQL

MySQL – Select Query Table of content MySQL Select Statement Fetching Data Using SELECT from Command Prompt Computing using SELECT in Command Prompt Aliasing a Column in SELECT Statement Select Query into MySQL Database Using a Client Program ”; Previous Next Now that we have learned how to create tables in MySQL and insert values into it in the previous tutorials, the next step is to check whether the values are recorded in this table or not. To do this, one must use the SELECT statement to retrieve and view the records in that specific table.” MySQL Select Statement The MySQL SELECT command is used to fetch data from the MySQL database in the form of a result table. These result tables are called result-sets. Note − We can use this command at ”mysql>” prompt as well as in any script like PHP, Node.js, Java, python, etc. Syntax Here is generic SQL syntax of SELECT command to fetch data from the MySQL table − SELECT field1, field2,…fieldN FROM table_name1, table_name2… [WHERE Clause] [OFFSET M ][LIMIT N] You can use one or more tables separated by comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command. We can fetch one or more fields in a single SELECT command. We can specify star (*) in place of fields. In this case, SELECT will return all the fields. We can specify any condition using the WHERE clause. We can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero. We can limit the number of returns using the LIMIT attribute. Fetching Data Using SELECT from Command Prompt This will use SQL SELECT command to fetch data from an MySQL table. Example First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); The following query inserts 7 records into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); To Retrieve All Fields Now, to retrieve all the data present in a CUSTOMERS table, we use the following SELECT statement − SELECT * from CUSTOMERS; Following are the records present in the CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 To Retrieve Selective Fields Here, we are retrieving only three columns/fields, ID, NAME, and ADDRESS present in the CUSTOMERS table, using the following query − SELECT ID, NAME, ADDRESS FROM CUSTOMERS; On executing the given query, the output is displayed as follows − ID NAME ADDRESS 1 Ramesh Ahmedabad 2 Khilan Delhi 3 Kaushik Kota 4 Chaitali Mumbai 5 Hardik Bhopal 6 Komal Hyderabad 7 Muffy Indore Computing using SELECT in Command Prompt The SELECT statement is not only used to fetch data from tables but can also be used to get the results of mathematical computations in a tabular format. In these cases, you don”t have to mention a specific database table in the SELECT statement. Following is the syntax to do so − SELECT [math_computation]; Example In the following example, let us solve a mathematical computation using the SELECT statement − SELECT 46475*453; Output The output for the program query is produced as given below − 46475*453 21053175 Aliasing a Column in SELECT Statement MySQL database provides a method to alias column names into a more understandable and relative name when being displayed. This is done using the ”AS” keyword. This keyword is used in the SELECT statement as well. Following is the syntax to do so − SELECT column_name AS alias_name FROM table_name; You can also use an alias to display select expressions with the same syntax; you should use a select expression instead of column_name in the syntax. Example In the example below, we are retrieving the ID column from the previously created CUSTOMERS table. We aliased the ID column as “Identity_Document” − SELECT ID AS Identity_Document FROM CUSTOMERS; As we can see the output, the alias name ”Identity_Document” has been used instead of ”ID”. Identity_Document 1 2 3 4 5 6 7 Select Query into MySQL Database Using a Client Program Besides getting

MySQL – Insert Ignore Table of content MySQL Insert Ignore Statement MySQL INSERT IGNORE and STRICT mode Insert Ignore Query Using a Client Program ”; Previous Next In MySQL, the INSERT INTO statement can be used to insert one or more records into a table. In some scenarios, if a particular column has a UNIQUE constraint and if we are trying to add duplicates records into that particular column using the INSERT INTO statement, MySQL will terminate the statement and returns an error. As the result, no rows are inserted into the table. MySQL Insert Ignore Statement However, if we use the MySQL INSERT IGNORE INTO statement, it will not display an error. Instead, it allows us to insert valid data into a table and ignores the rows with invalid data that would cause errors. Following are some scenarios where the INSERT IGNORE INTO statement avoid errors: When we insert a duplicate value in the column of a table that has UNIQUE key or PRIMARY key constraints. When we try to add NULL values to a column where it has NOT NULL constraint on it. Syntax Following is the syntax of the INSERT IGNORE statement in MySQL − INSERT IGNORE INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …); Example First of all, let us create a table named CUSTOMERS using the following query below − Note: The UNIQUE constraint ensures that no duplicate value can be stored or inserted in the NAME column. CREATE TABLE CUSTOMERS ( ID int NOT NULL, NAME varchar(20) NOT NULL UNIQUE, PRIMARY KEY (ID) ); The following query inserts three records into the CUSTOMERS table − INSERT INTO CUSTOMERS (ID, NAME) VALUES (1, “Ajay”), (2, “Vinay”), (3, “Arjun”); Execute the following query to display the records present in the CUSTOMERS table − SELECT * FROM CUSTOMERS; Following are the records of CUSTOMERS table − ID NAME 1 Ajay 2 Arjun 3 Vinay Now, let us insert a duplicate record into the NAME column of CUSTOMERS table using the below query − INSERT INTO CUSTOMERS (NAME) VALUES (2, “Arjun”); It returns an error because the NAME “Arjun” is already present in the column and hence it violates the UNIQUE constraint. ERROR 1062 (23000): Duplicate entry ”Arjun” for key ”customers.NAME” Now, let us use the INSERT IGNORE statement as shown below − INSERT IGNORE INTO CUSTOMERS (NAME) VALUES (2, “Arjun”); Output Though we are inserting a duplicate value, it do not display any error, instead it gives a warning. Query OK, 0 rows affected, 1 warning (0.00 sec) We can find the details of the above warning using the following query − SHOW WARNINGS; Following is the warnings table − Level Code Message Warning 1062 Duplicate entry ”Arjun” for key ”customers.NAME” Verification If we try to verify the CUSTOMERS table, we can find that the duplicate row which we tried to insert will not be present in the table. SELECT * FROM CUSTOMERS; The output for the program above is produced as given below − ID NAME 1 Ajay 2 Arjun 3 Vinay MySQL INSERT IGNORE and STRICT mode The strict mode controls how MySQL handles the invalid, missing, or out of range values that are going to be added into a table through data-change statements such as INSERT or UPDATE. So, if the strict mode is ON, and we are trying to insert some invalid values into a table using the INSERT statement, MySQL terminates the statement returns an error message. However, if we use the INSERT IGNORE INTO statement, instead of returning an error, MySQL will adjust those values to make them valid before adding the value to the table. Example Let us create a table named CUSTOMERS using the following query − Note: The NAME column accepts only strings whose length is less than or equal to five. CREATE TABLE CUSTOMERS ( ID int NOT NULL, NAME varchar(5), PRIMARY KEY (ID) ); Here, we are trying to insert a value into NAME column whose length is greater than 5. INSERT INTO CUSTOMERS (NAME) VALUES (1, “Malinga”); It returns an error as shown below − ERROR 1406 (22001): Data too long for column ”NAME” at row 1 Now, we are trying to use the INSERT IGNORE statement to insert the same string − INSERT IGNORE INTO CUSTOMERS (NAME) VALUES (1, “Malinga”); Output As we can see in the output, instead of returning an error, it displays an warning − Query OK, 1 row affected, 1 warning (0.01 sec) Let us find the details of the above warning using the following command − SHOW WARNINGS; As we can see in the output below, MySQL truncated the data before inserting it into the CUSTOMERS table. Level Code Message Warning 1265 Data truncated for column ”NAME” at row 1 Verification Execute the following query to verify the records of the CUSTOMERS table − Select * from CUSTOMERS; As we can see in the CUSTOMERS table below, the value has been truncated to 5 characters and inserted into the table. ID NAME

MySQL – REPLACE Query Table of content MySQL REPLACE Statement Inserting records using REPLACE statement Replace Query Using a Client Program ”; Previous Next MySQL REPLACE Statement In general, if we want to add records into an existing table, we use the MySQL INSERT statement. Likewise, we can also add new records or replace an existing records using the MySQL REPLACE statement. The replace statement is similar to the insert statement. The only difference is, while inserting a record using the insert statement if a existing column has a UNIQUE or PRIMARY KEY constraint, if the new record has same value for this column an error will be generated. In the case of the REPLACE statement if you try to insert a new column with duplicate value for the column with UNIQUE or PRIMARY KEY constraints the old record will be completely replaced by the new record. Syntax Following is the syntax of the MySQL REPLACE statement − REPLACE INTO table_name (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN); Where, table_name is the name of the table into which you need to insert data, (column1, column2, column3,…columnN) are the names of the columns and (value1, value2, value3,…valueN) are the values in the record. Example Let us start with creating a table with name CUSTOMERS in MySQL database with primary key constraint on the ID column as shown below − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); The following query adds two records into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ), (2, ”Khilan”, 25, ”Delhi”, 1500.00 ); Execute the following query to display all the records present in the CUSTOMERS table − select * FROM CUSTOMERS; Following are the records in CUSTOMERS table − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 Now, let us try to insert another record with ID value 2 − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Kaushik”, 23, ”Kota”, 2000.00 ); Since the ID column has a primary key constraint, an error will be generated as shown below − ERROR 1062 (23000): Duplicate entry ”2” for key ”customers.PRIMARY” Now, use the REPLACE statement to replace the existing record in the table − REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Kaushik”, 20, ”Kota”, 2000.00 ); Output Executing the query above will produce the following output − Query OK, 2 rows affected (0.01 sec) Verification Execute the following SELECT statement to verify whether the new record has been replaced or not − select * from CUSTOMERS; As we can observe the output below, the existing record has been replaced with the new record − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Kaushik 20 Kota 2000.00 Inserting records using REPLACE statement When you use the REPLACE statement to insert a record, if that record doesn”t match any existing records in the table, it will be added as a new record. Example The following query uses REPLACE statement to add three new records into the above CUSTOMERS table − REPLACE INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (4, ”Hardik”, 27, ”Bhopal”, 8500.00 ), (5, ”Komal”, 22, ”Hyderabad”, 4500.00 ); Output Executing the query above will produce the following output − Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 Verification Execute the following query to verify whether the above records has been inserted into CUSTOMERS table or not − SELECT * FROM CUSTOMERS; As we can observe the CUSTOMERS below, the above records are inserted as new records into the table. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Kaushik 20 Kota 2000.00 3 Chaitali 25 Mumbai 6500.00 4 Hardik 27 Bhopal 8500.00 5 Komal 22 Hyderabad 4500.00 Replacing a Record Using a Client Program Besides replacing records of a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPLACE operation. Syntax Following are the syntaxes to use REPLACE query in various programming languages − PHP NodeJS Java Python To replace a record in a table from MySQL Database through a PHP program we need to execute the Alter statement using the mysqli function query() as − $sql=”REPLACE INTO TABLE_NAME SET COLUMN_NAME1 = NEW_VALUE, COLUMN_NAME2 = NEW_VALUE…”; $mysqli->query($sql); To replace a record in a table from MySQL Database through a Node.js program we need to execute the Alter statement using the query() function of the mysql2 library as − sql=”REPLACE INTO table_name (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN)” con.query(sql); To replace a record in a table from MySQL Database through a Java program we need to execute the Alter statement using the JDBC function executeUpdate() as − String sql=”REPLACE INTO TABLE_NAME SET COLUMN_NAME1 =

MySQL – Clustered Index ”; Previous Next Indexes in MySQL are used to retrieve the data much faster from the database. We (users) cannot see the indexes, but they work behind to speed up searches and queries. They are categorized into two types: clustered and non-clustered indexes. A clustered index can sort the data in a table manually. When data is inserted into the column with clustered index, the records are automatically sorted in a specified order. So, each table can only have one clustered index since it determines the sort order of the data. MySQL Clustered Indexes MySQL database does not have separate provisions for Clustered indexes. They are automatically created when PRIMARY KEY is defined on a table. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is treated as a Clustered index. If a table has no Primary Key or UNIQUE index, MySQL will internally create a hidden clustered index named GEN_CLUST_INDEX on a column that contains the row ID values. The rows of a table are ordered using row ID values generated by InnoDB. Example Let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (20, 2), PRIMARY KEY(ID) ); Now, we will insert some values in to the above created table using the INSERT statement − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, ”32”, ”Ahmedabad”, 2000), (2, ”Khilan”, ”25”, ”Delhi”, 1500), (3, ”Kaushik”, ”23”, ”Kota”, 2500), (4, ”Chaitali”, ”26”, ”Mumbai”, 6500), (5, ”Hardik”,”27”, ”Bhopal”, 8500), (6, ”Komal”, ”22”, ”MP”, 9000), (7, ”Muffy”, ”24”, ”Indore”, 5500); The table will be created as follows − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Using the following query, we can list all the indexes created on the CUSTOMERS table − SHOW INDEX FROM CUSTOMERSG Output As we can see in the output below, the PRIMARY KEY is created on the ID column of CUSTOMERS table. *************************** 1. row *************************** Table: customers Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: ID Collation: A Cardinality: 7 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 1 row in set (0.01 sec) Print Page Previous Next Advertisements ”;

MySQL – Drop Tables Table of content The MySQL DROP TABLE statement Dropping Tables from a database The IF EXISTS clause Dropping Table Using a Client Program ”; Previous Next The MySQL DROP TABLE statement The MySQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table”s definition, and its data, indexes, triggers, constraints and permission specifications (if any). In simple terms, this statement will delete the entire table from the database. However, while using DROP TABLE command, we need make note of the following − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever. To drop a table in a database, one must require ALTER permission on the specified table and CONTROL permissions on the table schema. Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely removes the table from the database. Syntax Following is the syntax of MySQL DROP TABLE statement − DROP TABLE table_name ; Dropping Tables from a database To drop tables from a database, we need to use the MySQL DROP TABLE command. But we must make sure that the table exists in the database, before dropping it. If we try to drop a table that does not exist in the database, an error is raised. Example Let us start by creating a database TUTORIALS by executing below statement − CREATE DATABASE TUTORIALS; Using the following query, change the database to TUTORIALS − USE TUTORIALS; Create a a table CUSTOMERS using the following CREATE TABLE statement − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); To verify whether the above created is created in the TUTORIALS database or not, execute the following SHOW TABLES command − SHOW TABLES IN TUTORIALS; As we can see in the output below, the CUSTOMERS table is created in the TUTORIALS database. Tables_in_tutorials customers Now, let us use the MySQL DROP TABLE statement to delete the above created CUSTOMERS table − DROP TABLE CUSTOMERS; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.02 sec) Verification Since we have removed the table CUSTOMERS, if we try to verify the list of tables again, using the “SHOW TABLES” query it will display an Empty Set as shown below − Empty set (0.00 sec) The IF EXISTS clause Instead of constantly checking whether a table exists or not in a database before deleting it, we can use the IF EXISTS clause along with the DROP TABLE statement. When we specify this clause in the DROP TABLE query, it will automatically verify if the table exists in the current database. If it exists, it will then delete the table. If the table doesn”t exist, the query will be ignored. Syntax Following is the basic syntax of DROP TABLE IF EXISTS statement − DROP TABLE [IF EXISTS] table_name; Example Here, we are using just DROP TABLE statement to drop the CUSTOMERS table which has been deleted already in the previous example. DROP TABLE CUSTOMERS; Output Since, we are not using IF EXISTS with the DROP TABLE command, it will display an error as follows − ERROR 1051 (42S02): Unknown table ”tutorials.customers” Example If we try to drop CUSTOMERS that does not exist in the database, using the IF EXISTS clause, the query will be ignored without issuing any error − DROP TABLE IF EXISTS CUSTOMERS; Executing the query above will produce the following output − Query OK, 0 rows affected, 1 warning (0.01 sec) Dropping Table Using a Client Program In addition to dropping a table from MySQL Database using the MySQL query, we can also perform the DROP TABLE operation on a table using a client program. Syntax Following are the syntaxes to drop a table from MySQL in various programming languages − PHP NodeJS Java Python To drop a table from MySQL database through a PHP program, we need to execute the Drop statement using the mysqli function query() as − $sql=”DROP TABLE Table_name”; $mysqli->query($sql); To drop a table from MySQL database through a Node.js program, we need to execute the Drop statement using the query() function of the mysql2 library as − sql = “DROP TABLE Table_name”; con.query(sql); To drop a table from MySQL database through a Java program, we need to execute the Drop statement using the JDBC function executeUpdate() as − String sql=”DROP TABLE Table_name”; statement.execute(sql); To drop a table from MySQL database through a Python program, we need to execute the Drop statement using the execute() function of the MySQL Connector/Python as − sql=”DROP TABLE Table_name”; cursorObj.execute(sql); Example Following are the programs − PHP NodeJS Java Python $dbhost = ”localhost”; $dbuser = ”root”; $dbpass = ”root@123”; $dbname = ”TUTORIALS”; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if($mysqli->connect_errno ) { printf(“Connect failed: %s<br />”, $mysqli->connect_error); exit(); } printf(”Connected successfully.<br />”); if ($mysqli->query(“Drop Table tutorials_tbl”)) { printf(“Table tutorials_tbl dropped successfully.<br />”); } if ($mysqli->errno) { printf(“Could not drop table: %s<br />”, $mysqli->error); } $mysqli->close();

MySQL − Queries Table of content MySQL Create Database MySQL Use Database MySQL Create Query MySQL Insert Query MySQL Update Query MySQL Alter Query MySQL Delete Query MySQL Truncate Table Query MySQL Drop Query ”; Previous Next MySQL is an open-source relational management system (RDBMS) that allows us to store and manage data or information. The queries in MySQL are commands that are used to retrieve or manipulate the data from a database table. Following are the commonly used commands in MySQL: SELECT, UPDATE, DELETE, INSERT INTO, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE DATABASE, ALTER DATABASE, CREATE INDEX, DROP INDEX, etc. Note: These keywords are not case-sensitive. For instance, create table is the same as CREATE TABLE. MySQL Create Database The create database query in MySQL can be used to create a database in the MySQL server. Syntax Following is the syntax for the query − CREATE DATABASE databasename; Example In the following query, we are creating a database named tutorials. CREATE DATABASE tutorials; MySQL Use Database The MySQL use database query is used to select a database to perform operations such as creating, inserting, updating tables or views, etc. Syntax Following is the syntax for the query − USE database_name; Example The following query selects a database named tutorials − USE tutorials; MySQL Create Query The MySQL create query can be used to create databases, tables, indexes, views, etc. Syntax Following is the syntax for the query − CREATE [table table_name |index index_name | view view_name]; Example Here, we are creating a table named STUDENTS using the following CREATE query − CREATE TABLE CUSTOMERS ( ID int, NAME varchar(20), AGE int, PRIMARY KEY (ID) ); MySQL Insert Query The MySQL insert query can be used to insert records within a specified table. Syntax Following is the syntax for the query − INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …); Example In the following query, we are inserting some records into a table named CUSTOMERS − INSERT INTO CUSTOMERS (ID, NAME, AGE) VALUES (1, “Nikhilesh”, 28); INSERT INTO STUDENTS (ID, NAME, AGE) VALUES (2, “Akhil”, 23); INSERT INTO STUDENTS (ID, NAME, AGE) VALUES (3, “Sushil”, 35); MySQL Update Query The MySQL update query can be used to modify the existing records in a specified table. Syntax Following is the syntax for the query − UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; Example UPDATE CUSTOMERS SET NAME = “Nikhil” WHERE ID = 1; MySQL Alter Query The ALTER query in MySQL can be used to add, delete, or modify columns in an existing table. Syntax Following is the syntax for the query − ALTER TABLE table_name [ADD|DROP] column_name datatype; Example Here, we are trying to add a column named ADDRESS to the existing CUSTOMERS table. ALTER TABLE CUSTOMERS ADD COLUMN ADDRESS varchar(50); MySQL Delete Query The Delete query in MySQL can be used to delete existing records in a specified table. Syntax Following is the syntax for the query − DELETE FROM table_name WHERE condition; Example In the following query, we are deleting a record from CUSTOMERS table where the ID is equal to 3. DELETE FROM CUSTOMERS WHERE ID = 3; MySQL Truncate Table Query The MySQL truncate table query can be used to remove all the records but not the table itself. Syntax Following is the syntax for the query − TRUNCATE [TABLE] table_name; Example In the following query, we are removing all the records from the CUSTOMERS table using the truncate table query − TRUNCATE TABLE CUSTOMERS; MySQL Drop Query The MySQL drop query is used to delete an existing table in a database. Syntax Following is the syntax for the query − DROP TABLE table_name; Example Here, we are trying to delete the table named CUSTOMERS using the drop table query. DROP TABLE CUSTOMERS; Print Page Previous Next Advertisements ”;

MySQL − Constraints Table of content MySQL Constraints MySQL NOT NULL Constraint MySQL UNIQUE Constraint MySQL PRIMARY KEY Constraint MySQL FOREIGN KEY Constraint MySQL CHECK Constraint MySQL DEFAULT Constraint MySQL CREATE INDEX Constraint MySQL AUTO_INCREMENT Constraint ”; Previous Next MySQL Constraints The MySQL constraints can be used to set certain rules to the column(s) in a table. These constraints can restrict the type of data that can be inserted or updated in a particular column. This helps you to maintain the data accuracy and reliability in a table. There are two types of MySQL constraints. Column level constraints: These type of constraints will only apply to a column in a table. Table level constraints: These constraints will apply to the complete table. The commonly used constraints in MySQL are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, CREATE INDEX, AUTO_INCREMENT, etc. Syntax Following is the basic syntax to add a constraint for the column(s) in a table − CREATE TABLE table_name ( Column_name1 datatype constraint, Column_name2 datatype constraint, Column_name3 datatype constraint, ……… ); MySQL NOT NULL Constraint By default, a column in a MySQL table can contain NULL values. In some scenarios, we may want a particular column to not accept or contain NULL values. To do so, we can use the MySQL NOT NULL constraint. This constraint enforces a specific field to always contain a value, which means that we cannot insert or update a record without adding a value to this field. Example In the following query, we are adding the NOT NULL constraint on the ID and NAME columns of the CUSTOMERS table. As a result, the ID and NAME columns will not accept NULL values at the time of record insertion. CREATE TABLE CUSTOMERS ( ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int ); Let”s try inserting records into this table. The following statement will insert a record into the CUSTOMERS table − INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ”Nikhil”, 18); But, if we try to insert records with NULL values as ID as − INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(Null, ”Varun”, 26); An error will be generated saying “Column ”ID” cannot be null”. ERROR 1048 (23000): Column ”ID” cannot be null In the same way if we try to pass NULLs as values to the NAME column, similar error will be generated. INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(3, Null, 19); This will generate the following error − ERROR 1048 (23000): Column ”NAME” cannot be null As we can see in the above queries, the first record is successfully inserted because it does not have null values in the ID and Name columns. Whereas, the second and third records are not inserted because we are trying to insert NULL values in the columns which shouldn”t be NULL. MySQL UNIQUE Constraint The UNIQUE constraint in MySQL ensures that every value in a column must be distinct. This means the column with the UNIQUE constraint cannot have the same value repeated; each value must be unique. Note: We can have one or more UNIQUE constraints on a single table. Example The following query creates a UNIQUE constraint on the ID column of the CUSTOMERS table − CREATE TABLE CUSTOMERS ( ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int, UNIQUE (ID) ); Now, let us insert the following records into the above-created table − INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ”Nikhil”, 18); INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES(1, ”Varun”, 26); In the above code block, the second insert statement returned an error saying “Duplicate entry ”1” for key ”customers.ID” because the ID value we are inserting already exists in the table. Therefore, it is a duplicate and the query generates the following error − ERROR 1062 (23000): Duplicate entry ”1” for key ”customers.ID” MySQL PRIMARY KEY Constraint The PRIMARY KEY constraint in MySQL is used to uniquely identify each record in a table. This means that, if we define primary key on a particular column in a table, it must contain UNIQUE values, and cannot contain NULL values. Note: We can have only a single primary key on a table. Example The following query creates a PRIMARY KEY on the ID column of the CUSTOMERS table − CREATE TABLE CUSTOMERS ( ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int, PRIMARY KEY (ID) ); Once the table is created, insert the following record into the above-created table − INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (1, ”Nikhil”, 18); Query OK, 1 row affected (0.01 sec) Since we added the PRIMARY KEY constraint on the ID column, if you try to insert a record with duplicate ID value or NULL value, it will generate an error. INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (1, ”Varun”, 26); ERROR 1062 (23000): Duplicate entry ”1” for key ”customers.PRIMARY” INSERT INTO CUSTOMERS(ID, NAME, AGE) VALUES (NULL, ”Datta”, 19); ERROR 1048 (23000): Column ”ID” cannot be null As we can see in the above queries, the first insert statement is successfully inserted into the table. Whereas the second and third statements returned an error because they contain a duplicate and a NULL value in the primary key column i.e. (ID). MySQL FOREIGN KEY Constraint The FOREIGN KEY constraint in MySQL is used to link a field or collection of fields in one table to the primary key of another table. A table with the foreign key is called a child table and

MySQL – Add/Delete Columns Table of content Adding Columns to a MySQL table Adding Multiple Columns Deleting Columns from a MySQL table Adding/Deleting column in a table Using a Client Program ”; Previous Next A column in a table is a series of vertical cells that are used to store different types of data such as text, numbers, images, etc. Every column can contain one or more rows, where each row can store a single value. Adding Columns to a MySQL table In MySQL, we can add one or multiple columns in a table using the ALTER TABLE ADD statement. Adding columns to a table can be useful when we need to add new data. Syntax Following is the syntax to add a column in a MySQL table − ALTER TABLE table_name ADD [COLUMN] column_1_definition [FIRST|AFTER existing_column], ADD [COLUMN] column_2_definition [FIRST|AFTER existing_column], …; Where, The FIRST keyword is used to add a specific column at the beginning of the table. The AFTER keyword is used to add a column after a particular existing column in the table. Example First of all, let us create a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL ); Execute the following query to retrieve the columns list in above created table − DESCRIBE CUSTOMERS; Following are the columns that are present in the CUSTOMERS table at the moment − Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO NULL Now, we are adding a column named AGE to the CUSTOMERS table using the below query − ALTER TABLE CUSTOMERS ADD COLUMN AGE INT NOT NULL; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification After adding the AGE column to the CUSTOMERS table, we can check to confirm if the AGE column has been added or not, using the following query − DESCRIBE CUSTOMERS; As we can see in the colums list of CUSTOMERS table, the column AGE is added successfully. Field Type Null Key Default Extra ID int NO NULL NAME varchar(20) NO NULL AGE int NO NULL Example In the following query, we are using the FIRST keyword to add the S_NO column at the beginning of the previosly created CUSTOMERS table − ALTER TABLE CUSTOMERS ADD COLUMN S_NO INT NOT NULL FIRST; Output On executing the given query, the output is displayed as follows − Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Now, let us verify whether the S_NO column is added first or not by executing the below query − DESCRIBE CUSTOMERS; As we can see in the output table, the S_NO column is added successfully at the beginning of the table. Field Type Null Key Default Extra S_NO int NO NULL ID int NO NULL NAME varchar(20) NO NULL AGE int NO NULL Example At the moment, the CUSTOMERS table has 4 columns in it. Now, we are using the AFTER keyword to add a new column GENDER after the column named ID − ALTER TABLE CUSTOMERS ADD COLUMN GENDER VARCHAR(10) AFTER ID; Output Executing the query above will produce the following output − Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Verification Using the following DESCRIBE statement, we can verify whether the column GENDER is added after the ID column or not − DESCRIBE CUSTOMERS; The GENDER column is successfully added after the ID column. Field Type Null Key Default Extra S_NO int NO NULL ID int NO NULL GENDER varchar(10) YES NULL NAME varchar(20) NO NULL AGE int NO NULL Adding Multiple Columns We can add multiple columns into a specified table using the ALTER TABLE…ADD command. To do this, we just need to specify the new columns that we want to add, separating them with commas. Example In the below query, we are adding multiple columns (ADDRESS and CONTACT) to the CUSTOMERS table with a single ALTER statement − ALTER TABLE CUSTOMERS ADD COLUMN ADDRESS CHAR (25), ADD COLUMN CONTACT INT; Output The output for the program above is produced as given below − Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 We can verify whether the columns MARKS and GRADES are added or not using the following query − DESCRIBE CUSTOMERS; The following output show that the MARKS and GRADES columns are added into CUSTOMERS table −