Found 4219 Articles for MySQLi

As we know that while creating a view, providing the list of columns is optional. But if we are providing the name of the columns while creating the view then the number of names in the list of columns must be the same as the number of columns retrieved by the SELECT statement.ExampleThe following example will illustrate by creating the views with column list −mysql> Select * from student_detail; +-----------+-------------+------------+ | Studentid | StudentName | address    | +-----------+-------------+------------+ | 100       | Gaurav      | Delhi      | | 101       | Raman ... Read More

In MySQL, we can create a view that is based on another existing view. To make it understand we are having the view ‘Info’ with the following data −mysql> Create view info AS Select Id, Name, Subject FROM student_info; Query OK, 0 rows affected (0.11 sec) mysql> Select * from Info; +------+---------+------------+ | Id | Name | Subject | +------+---------+------------+ | 101 | YashPal | History | | 105 | Gaurav | Literature | | 125 | Raman | Computers | | NULL | Ram | Computers | +------+---------+------------+ 4 rows in set (0.00 sec)Now, with the help of ... Read More

As we know that we can see the list of tables in a database with the help of SHOW TABLES statement. But MySQL temporary tables are not stored in this list or in other words we can say that we cannot see the temporary tables with the help of SHOW TABLES statement. To illustrate it we are using the following example −ExampleIn this example, we are trying to get the temporary table named ‘SalesSummary’ from SHOW TABLES statement as follows −mysql> SHOW TABLES LIKE '%Sales%'; Empty set (0.00 sec) mysql> SHOW TABLES LIKE '%SalesSummary%'; Empty set (0.00 sec)The above ... Read More

While creating a view, providing the list of columns is optional. The following example will illustrate by creating the views without any column list −mysql> Select * from student_detail; +-----------+-------------+------------+ | Studentid | StudentName | address    | +-----------+-------------+------------+ |       100 | Gaurav      | Delhi      | |       101 | Raman       | Shimla     | |       103 | Rahul       | Jaipur     | |       104 | Ram         | Chandigarh | |     ... Read More

Following privileges are required for a different kind of CREATE, REPLACE, DROP, ACCESS, UPDATE etc. of usage of views − CREATE VIEW Privilege − CREATE VIEW privilege is required to create a view. Along with this we must have sufficient privileges, like SELECT, INSERT or UPDATE, for accessing the tables to which the view definition refers. DROP VIEW Privilege − We require DROP VIEW privileges for using OR REPLACE clause, DROP VIEW statement and also for using ALTER VIEW statement. SELECT Privilege − We must have SELECT privileges for selecting from a view. INSERT, DELETE or UPDATE Privileges − Actually for an updateable view ... Read More

MySQL VersionAs we know that MySQL 5 introduced views, hence, first of all, we need to check for the version of MySQL before starting writing and using stored procedures. It can be done with the following query −mysql> Select VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.20    | +-----------+ 1 row in set (0.10 sec)Privileges for current userActually CREATE VIEW statement requires the CREATE VIEW privilege. Privileges for the current user can be checked with the following query −mysql> SHOW PRIVILEGESSelecting a databaseBefore creating a view we must have to select a database from the available databases. It can ... Read More

In spite of various benefits of using views there are following limitations on using MySQL views − Can’t create an index of views − In MySQL, we cannot create an index on views. It is because indexes are not utilized when we query data against the views. MySQL invalidates the view − Suppose, if we drop or rename tables to which a view references, rather than issuing an error MySQL invalidate the view. We can use the CHECK TABLE statement to check whether the view is valid or not. MySQL views cannot be updateable in some situations − Actually, the simple view can ... Read More

As we know that views are definitions built on the top of other tables or views and stored in the database. Followings are benefits of using MySQL views as compared to selecting data directly from MySQL base tablesSimplify data accessThe use of views simplifies the data access because of the following reasons −A view can be used to perform a calculation and display its result. For example, a view definition that invokes aggregate functions can be used to display a summary.With the help of views, we can select a restricted set of rows by means of an appropriate WHERE clause ... Read More

A database view is nothing more than an SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.A view can contain all rows of a table or select rows from a table. A MySQL view can be created from one or many tables which depend on the written MySQL query to create a view.Views, which are a type of virtual tables allow users to do the following −Structure data in a way that users or classes of users find natural or ... Read More

As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec)Creating BEFORE UPDATE trigger to emulate CHECK CONSTRAINT for updating the values −Now, suppose if we will try to update the table ... Read More