Category: mysql

MySQL – Insert Query Table of content The MySQL INSERT Statement Inserting Data from the Command Prompt Inserting Data Into a Table Using Another Table INSERT… SET Inserting Data Using a Client Program ”; Previous Next After creating a table in a MySQL database with the CREATE TABLE statement, we will only have an empty table that only has its structure defined. To populate it with data, we need to add records manually using separate queries. The MySQL INSERT Statement To insert data into a MySQL table, we would need to use the MySQL INSERT statement. We can insert data into the MySQL table by using the ”mysql>” prompt or by using any client program such as PHP, Java etc. Since the structure of a table is already defined, the MySQL INSERT statement will only accept the data which is according to the structure of the table. Data inserted into a table must have same data types, satisfy the constraints (if any), etc. If the inserted data does not satisfy these conditions, the INSERT INTO statement displays an error. Syntax Following is the syntax of the MySQL INSERT statement − INSERT INTO TABLE_NAME (column1, column2, column3,…columnN) VALUES (value1, value2, value3,…valueN); To insert string values, it is required to keep all the values into double or single quotes. For example “value”. Inserting Data from the Command Prompt To insert data from the command prompt, we will use SQL INSERT INTO statement to insert data into 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) ); Now, we will insert a single record into the above created table − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00 ); We can also insert multiple records simultaneously using the following query − INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, ”Khilan”, 25, ”Delhi”, 1500.00 ), (3, ”Kaushik”, 23, ”Kota”, 2000.00 ), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00 ), (5, ”Hardik”, 27, ”Bhopal”, 8500.00 ); Inserting records into a database is also possible even if you do not specify the column name if the comma separated values in the query match the attributes of corresponding columns as shown below − INSERT INTO CUSTOMERS VALUES (6, ”Komal”, 22, ”Hyderabad”, 4500.00 ), (7, ”Muffy”, 24, ”Indore”, 10000.00 ); Verification We can verify whether the the data is inserted using this statement as shown below − SELECT * FROM CUSTOMERS; The CUSTOMERS table produced is as shown below − 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 Inserting Data Into a Table Using Another Table Sometimes, we just need to copy the data from one existing table in a database to another table in the same database. And there are various ways to do so − Using INSERT… SELECT Using INSERT… TABLE INSERT… SELECT Statement We can populate the data into a table through the select statement over another table; provided the other table has a set of fields, which are required to populate the first table. Here is the syntax − INSERT INTO table_name1 [(column1, column2, … columnN)] SELECT column1, column2, …columnN FROM table_name2 [WHERE condition]; Example In the following query, we are creating another table CUSTOMERS_Copy with the same structure as CUSTOMERS table − CREATE TABLE CUSTOMERS_Copy ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, let us use the INSERT…INTO statement to insert the records into the CUSTOMERS_Copy table from CUSTOMERS table. INSERT INTO CUSTOMERS_Copy SELECT * from CUSTOMERS; Output This will generate the following output − Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 Verification Execute the following query to verify whether the the records are inserted from CUSTOMERS table or not − SELECT * FROM CUSTOMERS_Copy; The CUSTOMERS_Copy table obtained is as shown below − 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 INSERT…TABLE Statement On the other hand, instead of selecting specific columns, we can insert the contents of one table into another using the INSERT…TABLE statement. Following is the syntax to do so − INSERT INTO table1 TABLE table2; Example In this example, let us use the same CUSTOMERS table we have created in the previous example

MySQL – Full Join Table of content MySQL Full Join Full Join with WHERE Clause ”; Previous Next MySQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fill in NULLs for missing matches on either side. In short, full join is a type of outer join that combines the results of both left and right joins. MySQL Full Join In MySQL, there is no provision to perform full join operation. We can, however, imitate this operation to produce the same results. The result-set obtained from performing full join is a union of result-sets obtained from left join and right join. Thus, we can first retrieve result-sets from left and right join operations and combine them using the UNION keyword. But, this method only works for cases where duplicate records are non-existent. If we want to include the duplicate rows, using UNION ALL keyword to combine the result-sets is preferred. Syntax Following is the basic syntax to emulate Full Join − SELECT table1.column1, table2.column2… FROM table1 LEFT JOIN table2 ON table1.common_field = table2.common_field [UNION | UNION ALL] SELECT table1.column1, table2.column2… FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field; Example In this example, we are imitating the full join operation using UNION or UNION ALL keyword. First, 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) ); Now insert values into this table using the INSERT statement 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 Let us create another table ORDERS, containing the details of orders made and the date they are made on. CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, CUSTOMER_ID INT NOT NULL, AMOUNT DECIMAL (18, 2), ); Using the INSERT statement, insert values into this table as follows − INSERT INTO ORDERS VALUES (102, ”2009-10-08 00:00:00”, 3, 3000.00), (100, ”2009-10-08 00:00:00”, 3, 1500.00), (101, ”2009-11-20 00:00:00”, 2, 1560.00), (103, ”2008-05-20 00:00:00”, 4, 2060.00); The table is displayed as follows − OID DATE CUSTOMER_ID AMOUNT 102 2009-10-08 00:00:00 3 3000.00 100 2009-10-08 00:00:00 3 1500.00 101 2009-11-20 00:00:00 2 1560.00 103 2008-05-20 00:00:00 4 2060.00 Full Join Query − On executing the following query, we will produce the union of two tables CUSTOMERS and ORDERS. SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID UNION SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID; Output The resultant table is produced as follows − ID NAME AMOUNT DATE 1 Ramesh NULL NULL 2 Khilan 1560 2009-11-20 00:00:00 3 Kaushik 3000 2009-10-08 00:00:00 3 Kaushik 1500 2009-10-08 00:00:00 4 Chaitali 2060 2008-05-20 00:00:00 5 Hardik NULL NULL 6 Komal NULL NULL 7 Muffy NULL NULL Full Join with WHERE Clause With Joins, we are filtering records using the ON clause, by default. Let us suppose there is a further requirement to filter records based on a certain condition, we can make use of WHERE clause with the Joins. Syntax The syntax of Full Join when used with WHERE clause is given below − SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name = table_name2.column_name WHERE condition Example Consider the previous two tables CUSTOMERS and ORDERS, and join them using the following Full Join query by applying some constraints using the WHERE clause. SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID WHERE ORDERS.AMOUNT > 2000.00 UNION SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID WHERE ORDERS.AMOUNT > 2000.00; Output The resultant table after applying the where clause with full join contains the rows that has amount values greater than 2000.00 − ID NAME DATE AMOUNT 3 Kaushik 2009-10-08 00:00:00 3000.00 4 Chaitali 2008-05-20 00:00:00 2060.00 Print Page Previous Next Advertisements ”;

MySQL – DESCRIBE Tables Table of content DESCRIBE Statement DESC Statement SHOW COLUMNS Statement EXPLAIN Statement Describe Tables in Different Formats Describing Table Using a Client Program ”; Previous Next Describing a MySQL table refers to retrieving its definition or structure. When we describe a table, it basically includes the fields present, their datatypes, and if any constraints defined on them. We can get the information about the table structure using the following SQL statements − DESCRIBE Statement DESC Statement SHOW COLUMNS Statement EXPLAIN Statement All these statements are used for the same purpose. Let us learn about them in detail, one by one, in this tutorial. DESCRIBE Statement The MySQL DESCRIBE statement is used to retrieve a table-related information, which consists of field names, field data types, and constraints (if any). This statement is a shortcut for the SHOW columns statement (they both retrieve the same information from a table). Apart from retrieving a table”s definition, this statement can be used to get the information of a particular field in a table. Syntax Following is the syntax of MySQL DESCRIBE statement − DESCRIBE table_name [col_name | wild]; Example In the following example, we are creating a table named CUSTOMERS using the CREATE TABLE statement − CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, execute the following query to get the information about columns of the CUSTOMERS table − DESCRIBE CUSTOMERS; Output Following is the columns information of CUSTOMERS table − Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18, 2) YES NULL Describing a specific column By default, the DESCRIBE statement provides information about all the columns in the specified table. But you can also retrieve information about a particular column of a table by specifying the name of that column. For example, the following query displays information about NAME column of CUSTOMERS table, which we created in the previous example. DESCRIBE CUSTOMERS NAME; Output Following is the description of NAME column in CUSTOMERS table − Field Type Null Key Default Extra NAME varchar(20) NO NULL DESC Statement We can also retrieve the table information using the MySQL DESC statement instead of DESCRIBE. They both give the same results, so DESC is just a shortcut for DESCRIBE statement. Syntax Following is the syntax of the MySQL DESC statement − DESC table_name [col_name | wild]; Example In this example, we are trying to get the information of CUSTOMERS table using DESC statement. DESC CUSTOMERS; Following is the columns information of CUSTOMERS table − Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL Describing a specific column We can also get the information of a specific column in a given table, similar to using DESCRIBE. Instead of DESCRIBE, we use DESC. For example, the following query displays information about NAME column of CUSTOMERS table. DESC CUSTOMERS NAME; Output Following is the description of NAME column in CUSTOMERS table − Field Type Null Key Default Extra NAME varchar(20) NO NULL SHOW COLUMNS Statement The MySQL SHOW COLUMNS Statement is used to display the information of all the columns present in a table. The DESCRIBE statement is a shortcut for this statement. Note: This statement will not display information of a specific field. Syntax Following is the syntax of the SHOW COLUMNS statement − SHOW COLUMNS FROM table_name; Example Here, we are retrieving column information of the same CUSTOMERS table using the SHOW COLUMNS statement. SHOW COLUMNS FROM CUSTOMERS; Following is the columns information of CUSTOMERS table − Field Type Null Key Default Extra ID int NO PRI NULL auto_increment NAME varchar(20) NO NULL AGE int NO NULL ADDRESS char(25) YES NULL SALARY decimal(18,2) YES NULL EXPLAIN Statement The MySQL EXPLAIN Statement is a synonym of DESCRIBE Statement which retrieves the information of a table”s structure such as column names, column data types, and constraints (if any). Syntax Following is the syntax of the SHOW COLUMNS statement − EXPLAIN table_name; Example In the following query, we are retrieving column information of the CUSTOMERS table using the EXPLAIN statement. EXPLAIN

MySQL – ORDER BY CLAUSE Table of content MySQL ORDER BY Clause ORDER BY with DESC ORDER BY with Multiple Columns ORDER BY with ASC and DESC ORDER BY with LENGTH() Order By Clause Using a Client Program ”; Previous Next MySQL ORDER BY Clause The MySQL ORDER BY clause is used to sort one or more columns of a table in provided order that can be either ascending or descending order. By default, it sorts the column(s) in ascending order if the sort order is not specified. The sort is specified with two keywords; ASC for ascending order and DESC for descending order. Using the ORDER BY clause, we can sort multiple columns of a table and provide different sort orders for each column. For instance, we can sort the result set first by one column, and then by another column to the first column, and so on. Syntax Following is the syntax of ORDER BY clause in MySQL − SELECT column-list FROM table_name [ORDER BY column1, column2, …, columnN] [ASC|DESC] Here, column-list are the names of the columns that we want to retrieve from the table_name. column1, column2,…columnN are the column(s) that we want to order (sort). ASC will sort the columns in ascending order. DESC will sort the columns in descending order. By default, the ORDER BY clause sorts the provided column in Ascending order. Example Firstly, 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 ); Execute the following query to verify whether the CUSTOMERS table is created or not − Select * from CUSTOMERS; The CUSTOMERS table has been created successfully − 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 Now, let us display all the columns from the CUSTOMERS table, sorted by the NAME column − By default, the ORDER BY clause sorts the provided column in Ascending order. SELECT * FROM CUSTOMERS ORDER BY NAME; Output As we can see in the output below, the NAME column is sorted in Ascending order. ID NAME AGE ADDRESS SALARY 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 3 Kaushik 23 Kota 2000.00 2 Khilan 25 Delhi 1500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 1 Ramesh 32 Ahmedabad 2000.00 ORDER BY with DESC We can sort a particular column of a table in descending order by using the ORDER BY clause along with the DESC keyword. Let us understand with the following example. Example In the following query, we are displaying all the columns from the CUSTOMERS table, sorted by the NAME column in descending order − SELECT * FROM CUSTOMERS ORDER BY NAME DESC; Output As we can see in the output below, the NAME column is sorted in descending order. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 7 Muffy 24 Indore 10000.00 6 Komal 22 Hyderabad 4500.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 5 Hardik 27 Bhopal 8500.00 4 Chaitali 25 Mumbai 6500.00 ORDER BY with Multiple Columns We can also sort multiple columns of a MySQL table. To do so, we need to specify all the column names in the ORDER BY clause. Example Here, we are selecting all the columns from the CUSTOMERS table, sorted by the ADDRESS and NAME columns. SELECT * FROM CUSTOMERS ORDER BY ADDRESS, NAME; Output The above query first sorts the ADDRESS column in ascending order, and for any rows that have the same ADDRESS value, they will be sorted by the NAME column in ascending order. This means, all the rows with the same ADDRESS value will be grouped together and sorted by NAME. ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 5 Hardik 27

MySQL – Update View Table of content MySQL UPDATE View Statement Updating Multiple Rows and Columns Updated a View Using a Client Program ”; Previous Next The MySQL UPDATE statement is used on various database objects to update the existing data in them. This is a DML (Data Manipulation language) command. We need to be careful while using the UPDATE statement as it can modify all the records in an object, if not selected beforehand. To avoid losing or re-inserting correct data, we use clauses to filter the records that need to be updated. This way, we can update either a single row or multiple rows selectively. MySQL UPDATE View Statement In MySQL, a view is a database object that can contain rows (all or selected) from an existing table. It can be created from one or many tables which depends on the provided SQL query to create a view. There is no direct statement to update a MySQL view. We use the UPDATE statement to modify all or selective records in a view. The results are reflected back in the original table as well. Syntax The basic syntax of the UPDATE query with a WHERE clause is as follows − UPDATE view_name SET column1 = value1, column2 = value2…., columnN = valueN WHERE [condition]; Note: We can combine N number of conditions using the AND or the OR operators. Example First of all, let us create a table with the name CUSTOMERS using the following query − CREATE TABLE CUSTOMERS( ID int NOT NULL, NAME varchar(20) NOT NULL, AGE int NOT NULL, ADDRESS varchar(25), SALARY decimal(18, 2), PRIMARY KEY (ID) ); Now, we are inserting some records into this table using the INSERT statement as follows − 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); Creating a view − Following query creates a view based on the above created table − CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS; Using the following query, we can verify the contents of a view − SELECT * FROM CUSTOMERS_VIEW; The view will be displayed 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 Updating this view − Now, through the view we created, we are trying to update the age of Ramesh to 35 in the original CUSTOMERS table, using the following query − UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = ”Ramesh”; This will ultimately update the base table CUSTOMERS and the same would reflect in the view itself. Verification Using a SELECT query, we can retrieve the actual CUSTOMERS table containing following records − ID NAME AGE ADDRESS SALARY 1 Ramesh 35 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 Updating Multiple Rows and Columns In MySQL, we can update multiple rows and columns of a table using the UPDATE statement. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it. To update multiple columns, set the new values to all the columns that need to be updated. In this case, using the WHERE clause would narrow down the records of the table and not using the clause would change all the values in these columns. Syntax Following is the syntax to update multiple rows and columns − UPDATE table_name SET column_name1 = new_value, column_name2 = new_value… WHERE condition(s) Example In the following query, we are trying to modify the NAME and AGE column values in the CUSTOMERS table for WHERE ID = 3: UPDATE CUSTOMERS_VIEW SET NAME = ”Kaushik”, AGE = 24 WHERE ID = 3; Verification Using the SELECT query, we can retrieve the CUSTOMERS table with following records − ID NAME AGE ADDRESS SALARY 1 Ramesh 35 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 24 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 Example If we want to modify all the records of AGE column in the CUSTOMERS table, we can use the following query − UPDATE CUSTOMERS_VIEW

MySQL – Administration Table of content Start MySQL Server Stop, Pause, Restart MySQL Server Setting Up a MySQL User Account Administrative MySQL Command ”; Previous Next MySQL Server is the program that mainly executes the SQL queries in the database system. Hence it becomes necessary to optimize the working of this server. The general MySQL administration usually includes concepts like: Starting and Stopping the Server User Security Database Maintenance Backup & Restore Start MySQL Server We need to first start the MySQL server on the device in order to use it. One way to do so, is by executing the following command on the command prompt (run as an administrator) − mysqld We can also start the server by going through the services provided by the Windows and follow the steps below − Open the ”Run” Window using the ”Windows+R” shortcut and run ”services.msc” through it. Then, select the “MySQL80” service click “start” to start the server. Stop, Pause, Restart MySQL Server Now, if you want to pause, stop or restart an already running MySQL server, then you can do it by opening the Windows Services and selecting the desired action − To stop the MySQL Server, select the ”stop” option as shown in the image below − To pause the MySQL Server, select the ”pause” option as shown in the image below − We can also restart the MySQL server as needed, by selecting the ”restart” option as shown below − Setting Up a MySQL User Account For adding a new user to MySQL, you just need to add a new entry to the user table in the database mysql. In the following example, we are creating a new user guest with the password guest123 on the ”localhost”. We are also granting all privileges required to executing SQL queries − CREATE USER ”guest”@”localhost” IDENTIFIED BY ”guest123”; Now, execute the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don”t use it, then you won”t be able to connect to MySQL using the new user account at least until the server is rebooted. FLUSH PRIVILEGES; Finally, you need to grant all privileges to this new user to execute SQL queries. GRANT ALL PRIVILEGES ON * . * TO ”sample”@”localhost”; You can also specify other privileges to a new user by setting the values of following columns in ”user” table to ”Y” using the UPDATE query. Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv NOTE − MySQL does not terminate a command until you give a semi colon (;) at the end of the SQL command. The /etc/my.cnf File Configuration In most of the cases, you should not touch this file. By default, it will have the following entries − [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock [mysql.server] user = mysql basedir = /var/lib [safe_mysqld] err-log = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid Here, you can specify a different directory for the error log, otherwise you should not change any entry in this table. Administrative MySQL Commands Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database − USE database_name − This will be used to select a database in the MySQL. SHOW DATABASES − Lists out the databases that are accessible by the MySQL DBMS. SHOW TABLES − Displays the list of the tables in the current database. SHOW COLUMNS FROM table_name: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table. SHOW INDEX FROM table_name − Presents the details of all indexes on the table, including the PRIMARY KEY. SHOW TABLE STATUS LIKE table_nameG − Reports details of the MySQL DBMS performance and statistics. Print Page Previous Next Advertisements ”;

MySQL – Repair Tables Table of content MySQL Repair Table Statement Repairing multiple tables REPAIR TABLE Options Repairing table Using a Client Program ”; Previous Next MySQL Repair Table Statement There can be scenarios where tables in databases can become corrupted due to various reasons such as hardware failures, software bugs, or unexpected server crashes. When this situation happens, we cannot be able to access or manipulate the data in those tables because of data inconsistencies or errors. In such situations, to repair those currupted tables, we use the MySQL REPAIR TABLE statement. This statement works for only certain engines such as MyISAM, etc. Syntax Following is the syntax of MySQL REPAIR TABLE Statement − REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] … [QUICK] [EXTENDED] [USE_FRM] Example Let us start by creating a table named CUSTOMERS using the following query − CREATE TABLE CUSTOMERS ( ID INT AUTO_INCREMENT, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Here, we are inserting 7 records into the above created table using the below INSERT statement − 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 ); Assume the above created table is corrupted and we are using the REPAIR TABLE statement to repair it. REPAIR TABLE CUSTOMERS; The above query displays an error as: “The storage engine for the table doesn”t support repair” because the REPAIR TABLE statement won”t work with the default InnoDB engine. Table Op Msg_type Msg_text tutorials.customers repair note The storage engine for the table doesn”t support repair To repair the table, We need to change the table”s engine to MyISAM because it supports the REPAIR TABLE statement. ALTER TABLE CUSTOMERS ENGINE = MyISAM; Now, to repair the CUSTOMERS table, execute the following query − REPAIR TABLE CUSTOMERS; Output We can see in the output below, it says OK which indicates that the table CUSTOMERS is in good condition, and there are no issues or corruption. Table Op Msg_type Msg_text tutorials.customers repair status OK Repairing multiple tables In MySQL, we can also repair multiple tables and get the results using the REPAIR TABLE Statement. To do this, we just need to list the names of the tables we want to repair, separating them with commas. Example Let us create three different tables with the names Test1, Test2, and Test3 using the following CREATE TABLE statements − CREATE TABLE Test1(ID INT, Name VARCHAR(255)); CREATE TABLE Test2(ID INT, Name VARCHAR(255)); CREATE TABLE Test3(ID INT, Name VARCHAR(255)); Assume the above three tables are corrupted. Change the engine of these tables to MyISAM to repair them with REPAIR TABLE statement − ALTER TABLE Test1 ENGINE = MyISAM; ALTER TABLE Test2 ENGINE = MyISAM; ALTER TABLE Test3 ENGINE = MyISAM; Now, to repair these tables, execute the following query − REPAIR TABLE Test1, Test2, Test3; As we can see in the output below, all three tables are in good condition, and there are no issues or corruption. Table Op Msg_type Msg_text tutorials.test1 repair status OK tutorials.test2 repair status OK tutorials.test3 repair status OK Repair Table Options We have various optional clauses to use with REPAIR TABLE such as QUICK, EXTENDED, and, USE_FRM clause. Let us discuss them one by one with suitable examples. QUICK Clause The QUICK clause is the is the default and it is most commonly used with REPAIR TABLE. If you specify the QUICK clause, MySQL will repair the table without re-creating it. − Example In the following example, we are using the QUICK clause with the REPAIR TABLE statement to repair the CUSTOMERS table. REPAIR TABLE CUSTOMERS QUICK; Output Executing the query above will produce the following output − Table Op Msg_type Msg_text tutorials.customers repair status OK EXTENDED Clause If we specify the EXTENDED clause, MySQL not only repairs the table but also rebuilds the index and optimizes the table structure. Note: The EXTENDED clause is a more time-consuming compared to QUICK clause. Example In the following example, we are using the EXTENDED clause with the REPAIR TABLE statement to repair the CUSTOMERS table. REPAIR TABLE CUSTOMERS EXTENDED; Output Executing the query above will produce the following output − Table Op Msg_type Msg_text tutorials.customers repair status OK USE_FRM clause We can use the USE_FRM clause, in case the MYI index file is missing. If you provide this clause the .NYI file will be recreated using information from the data dictionary − Example Here, we are using the USE_FRM clause with the REPAIR TABLE statement to repair the CUSTOMERS table. REPAIR TABLE CUSTOMERS USE_FRM; Output Executing the query above will produce the following output − Table Op Msg_type Msg_text tutorials.CUSTOMERS repair warning Number of rows changed from 0 to 7 tutorials.customers repair status OK

MySQL – Python Syntax Table of content Installing “python-mysql” connector Python Functions to Access MySQL Basic Example ”; Previous Next The MySQL-Python connector specifically refers to a library in Python that enables communication between a Python program and a MySQL database. It acts as a bridge, allowing Python programs to interact with and manipulate data stored in a MySQL database. Essentially, the MySQL-Python connector simplifies the process of connecting, querying, and managing databases, enabling developers to seamlessly integrate their Python applications with MySQL databases. Installing “python-mysql” connector To use MySQL with Python, you typically need to install a MySQL connector or library. Here are the general steps to install it − Step 1: Install MySQL Server Make sure you have MySQL Server installed on your machine or have access to a remote MySQL server. Step 2: Install MySQL Connector for Python Open a command prompt or terminal and use the following command to install the MySQL Connector for Python using pip, which is the package installer for Python: pip install mysql-connector-python If you are using Python 3, you might need to use ”pip3” instead of ”pip”. Step 3: Verify Installation After the installation is complete, you can verify that the library is installed by opening a Python interactive shell and trying to import the connector: import mysql.connector Python Functions to Access MySQL When working with MySQL in Python, the ”mysql-connector-python” library provides various functions to interact with a MySQL database. Here are some important functions commonly used − S.No Function & Description 1 connect() Establishes a connection to the MySQL server. 2 cursor() Creates a cursor object to execute SQL queries. 3 execute(query, params=None) Executes a SQL query. ”params” is an optional parameter for query parameters. 4 fetchone() Fetches the next row from the result set. 5 fetchall() Fetches all rows from the result set. 6 commit() Commits the current transaction to the database. 7 rollback() Rolls back the current transaction, undoing any changes since the last commit. 8 close() Closes the cursor and the connection to the database. 9 executemany() Executes a SQL command against all parameter sequences in the provided list. Basic Example To connect and communicate with a MySQL database using Python, you can follow these steps − Use ”pip install mysql-connector-python” to install the MySQL Connector for Python. Import the MySQL Connector module in your Python script: “import mysql.connector”. Create a connection using “mysql.connector.connect()” with your database details. Create a cursor using “connection.cursor()”. Use the cursor”s “execute()” method to run SQL queries. If applicable, use “fetchone()” or “fetchall()” to retrieve query results. If you modify data, commit changes using “connection.commit()”. Close the cursor and connection with “cursor.close()” and “connection.close()”. The following example shows a generic syntax of a Python program to call any MySQL query − import mysql.connector # Establish connection connection = mysql.connector.connect(host=”localhost”, user=”user”, password=”pass”, database=”db”) # Create cursor cursor = connection.cursor() # Execute query cursor.execute(“SELECT * FROM table”) # Fetch and print results rows = cursor.fetchall() print(rows) # Close cursor and connection cursor.close() connection.close() Print Page Previous Next Advertisements ”;

MySQL – Installation Table of content Installing MySQL on Windows Installing MySQL on Linux/UNIX ”; Previous Next All downloads for MySQL are located at MySQL Downloads. Pick the version number of MySQL Community Server which is required along with the platform you will be running it on. Installing MySQL on Windows In this tutorial, we are installing the latest version of MySQL (8.0.34) on Windows 11. Follow the given steps to do so − Step 1: Firstly, choose the MySQL version and operating system. Then, we download the desired MSI installer on your system by clicking the ”Download” button shown in the image below. This installer is suitable for both 32-bit and 64-bit systems. Step 2: Then, we will be redirected to another file download page. Here, ignore the prompts asking to log in or sign up and directly start downloading by clicking on the link as shown in the image. Step 3: Once the installer is downloaded, run it to start the MySQL installation. Step 4: Now, we can see the installer community window, asking to choose a Setup type for our MySQL products. Choose Custom and click Next to decide what products we want to actually install. Step 5: In the next step, select MySQL Server, MySQL Workbench, MySQL Shell (all latest versions) to be installed. We can also choose more products available as per necessity. Click Next. Step 6: The installation process will now begin. However, path conflicts might arise if there exists a path directory with the same name. After the installation is done, click Next. Step 7: In this step, we will be asked to set Type and Networking of MySQL. Unless there is any particular change we want to make, it is recommended to keep the settings as they are, and click Next. Step 8: Then, we need to set the Authentication method to access MySQL root user. So, choose the strong password encryption method (as it is recommended) and click Next. Step 9: Set a password for the root account. This password must always be used to log into the root account in every session. After setting password, click Next. Step 10: In this step, MySQL Server Instance will be configured as a Windows Service. The default name will be set as “MySQL80”, which can be changed if needed. Click Next. Step 11: Now, set the file permissions as required and click Next. Step 12: As shown in the image below, the specified configuration steps will be applied on clicking Execute. Once it is completed, click Finish. The next window will display the products on which the configuration is applied. Click Next to finish the installation. Step 12: The installation is now complete. Uncheck the options asking to start MySQL Workbench and Shell after setup, so that they will not run after setup. Click Finish. The MySQL Server is now installed in the Windows Operating System. We can now access it via the Command Prompt or the UI products we installed with it (Shell and Workbench). Installing MySQL on Linux/UNIX The recommended way to install MySQL on a Linux system is via RPM. MySQL AB makes the following RPMs available for download on its website − MySQL − The MySQL database server manages the databases and tables, controls user access and processes the SQL queries. MySQL-client − MySQL client programs, which make it possible to connect to and interact with the server. MySQL-devel − Libraries and header files that come in handy when compiling other programs that use MySQL. MySQL-shared − Shared libraries for the MySQL client. MySQL-bench − Benchmark and performance testing tools for the MySQL database server. The MySQL RPMs listed here are all built on a SuSE Linux system, but they will usually work on other Linux variants with no difficulty. Now, you will need to adhere to the steps given below, to proceed with the installation − Login to the system using the root user. Switch to the directory containing the RPMs. Install the MySQL database server by executing the following command. Remember to replace the filename in italics with the file name of your RPM. [root@host]# rpm -i MySQL-5.0.9-0.i386.rpm The above command takes care of installing the MySQL server, creating a user of MySQL, creating necessary configuration and starting the MySQL server automatically. You can find all the MySQL related binaries in /usr/bin and /usr/sbin. All the tables and databases will be created in the /var/lib/mysql directory. The following code box has an optional but recommended step to install the remaining RPMs in the same manner − [root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm [root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm Print Page Previous Next Advertisements ”;

MySQL Tutorial Table of content MySQL Tutorial MySQL Examples MySQL Online Editor Why to Learn MySQL? MySQL Jobs and Opportunities Who Should Learn MySQL Prerequisites to Learn MySQL Frequently Asked Questions about MySQL PDF Version Quick Guide Resources Job Search Discussion MySQL Tutorial MySQL is the most popular and a free Open Source Relational Database Management System (RDBMS). An RDBMS system stores the data in the form of tables that might be related to each other. MySQL uses Structured Query Language (SQL) to store, manage and retrieve data, and control the accessibility to the data. It is one of the best RDBMS being used for developing web-based software applications. MySQL is written in C and C++. Its SQL parser is written in yacc, but it uses a home-brewed lexical analyzer. MySQL works on many system platforms, such as, Linux, macOS, Microsoft Windows, AIX, BSDi, FreeBSD, HP-UX, ArcaOS, eComStation, IBM i, IRIX, NetBSD, Novell NetWare, OpenBSD, OpenSolaris, OS/2 Warp, QNX, Oracle Solaris, Symbian, SunOS, SCO OpenServer, SCO UnixWare, Sanos and Tru64. This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming. MySQL Examples Consider an example table CUSTOMERS created in the MySQL database. This table contains the details of customers like ID, NAME, AGE, ADDRESS, SALARY. 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 Since MySQL uses SQL to manage data, it also uses almost all DDL, DML and DCL statements. For instance, the following DML statement lists the records of all customers who are 25 years old. SELECT * FROM CUSTOMERS WHERE AGE = 25; Following records are displayed as a result-set − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 4 Chaitali 25 Mumbai 6500.00 MySQL Online Editor In this tutorial, we provide a MySQL Online Editor which helps you to Edit and Execute the MySQL code directly from your browser. Click on the icon to run the following MySQL code to be executed on the CUSTOMERS table and retrieve the records matching with the given condition. SELECT * FROM CUSTOMERS WHERE NAME = ”Chaitali”; This Online Editor will save you the time to install the MySQL setup in order to execute any query. Try our MySQL Online Editor now. Why to Learn MySQL? MySQL is generally faster, more secure and efficient than other relational databases. Some of world”s fastest growing organizations make use of MySQL to efficiently power their high-volume web sites, business-critical systems and packaged software. However, whether you choose MySQL for your application or not, totally depends on your organization”s resources and aim. Usually, MySQL is used by smaller businesses that do not have large data sets, because of its cost efficiency and simple setup. MySQL Jobs and Opportunities MySQL professionals are in high demand as more and more organizations are using this open-source database system for efficient data management. If you have the skills, you could earn an average salary of around $150,000 per year, but it can vary depending on your location, experience, and job role. Here are some of the top companies actively looking for MySQL experts for roles like Database Administrator, Database Developer, Database Tester, Data Scientist, ETL Developer, Database Migration Expert, Cloud Database Expert, and more. They need people who can manage and optimize their databases, build data-driven applications, and extract insights from large datasets − Google Amazon Netflix Infosys Tata Consultancy Services (TCS) Tech Mahindra Wipro Pinterest Uber Wipro Trello And many more… To get started, you can use our user-friendly tutorials, which are designed to help you learn MySQL and prepare for technical interviews or certification exams. You can learn at your own pace, anytime and anywhere. With the right MySQL skills and knowledge, you can kickstart a rewarding career in the ever-expanding field of data management and analytics. You could be the part of the professionals who are driving innovation and data-driven decision-making in some of the world”s most renowned companies. Who Should Learn MySQL This MySQL tutorial has been prepared for beginners to help them understand the basics to advanced concepts related to MySQL database. Prerequisites to Learn MySQL Before you start doing practice with various types of examples given in this reference, I”m making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language. Frequently Asked Questions about MySQL Following are very Frequently Asked Questions(FAQ) about MySQL, and this section tries to answer them briefly. What is MySQL and how it works? MySQL is a popular open-source relational database management system (RDBMS). It organizes data into tables with rows and columns. Users can interact with MySQL using SQL (Structured Query Language) to perform operations like inserting, updating, and querying data. The system works by processing SQL commands to manage and retrieve data efficiently. Who developed MySQL? MySQL was developed by Swedish