Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQL Articles
Page 308 of 355
Can I write my own MySQL functions to use in MySQL queries?
Yes, you can write own MySQL function to use in MySQL queries. Following is the syntax:DELIMITER // CREATE FUNCTION yourFunctionName(optional parameters)) RETURNS yourDataType DETERMINISTIC NO SQL BEGIN yourStatements1 . . . . N END // DELIMITER ;We have used the CREATE FUNCTION above to create a custom function.Let us create a custom MySQL function to use in MySQL query:mysql> DELIMITER // mysql> CREATE FUNCTION get_First_Name(Name VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC NO SQL BEGIN RETURN LEFT(Name, LOCATE(' ', Name) - 1); END // Query OK, 0 rows affected (0.20 sec) mysql> DELIMITER ;Now call ...
Read MoreHow to create MySQL user with limited privileges?
To create MySQL user with limited privileges, following is the syntax −CREATE USER 'yourUserName'@'yourHostName' IDENTIFIED BY 'yourPassword';Following is the syntax to set limited privileges for user −GRANT SELECT, INSERT, UPDATE, etc. REFERENCES ON yourDatabaseName.* TO 'yourUserName'@'yourHostName';Let us implement the above syntaxes in order to create a MySQL user with limited privileges −mysql> CREATE USER 'David'@'localhost' IDENTIFIED BY 'david'; Query OK, 0 rows affected (0.20 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE, REFERENCES ON test.* TO 'David'@'localhost'; Query OK, 0 rows affected (0.21 sec)Let us check the user has been created with name ‘David’ or not.mysql> ...
Read MoreHow to SELECT records if the absolute value of the difference between two values is greater than a certain number?
To SELECT records if the absolute value of the difference between two values is greater than a certain number, following is the syntax:select *from yourTableName where abs(yourColumnName1-yourColumnName2) >= yourCertainNumber;Let us first create a table:mysql> create table DemoTable ( Number1 int , Number2 int ); Query OK, 0 rows affected (0.59 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(10, 20); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(100, 200); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(400, 300); Query OK, 1 ...
Read MoreCan I find out the next auto_increment to be used?
Yes, you can find out the next auto_increment with SELECT AUTO_INCREMENT as shown in the below syntax −SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= yourDatabaseName AND TABLE_NAME=yourTableName;Let us first create a table −mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), ClientAge int ); Query OK, 0 rows affected (1.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ClientName, ClientAge) values('John', 23); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable(ClientName, ClientAge) values('Carol', 21); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable(ClientName, ClientAge) values('Bob', ...
Read MoreHow to display only 200 characters from total value in MySQL?
You can use LEFT() from MySQL to display some character from the entire value in MySQL. Following is the syntax:select left(yourColumnName ,200 ) AS anyAliasName from yourTableName;Let us first create a table:mysql> create table DemoTable (Paragraph longtext); Query OK, 0 rows affected (0.71 sec)Following is the query to insert records in the table using insert command:mysql> insert into DemoTable values('Introduction to Java, Introduction to C, Introduction to C++, Introduction to Spring, Introduction to Hibernate, Introduction to Python, Introduction to MySQL, Introduction to MongoDB, Introduction to SQL Server, Introduction to ASP.net, Introduction to JSF'); Query OK, 1 row affected (0.13 sec)Following ...
Read MoreHow do I use `SHOW COLUMNS` as a valid data source for a table?
For this, you can use INFORMATION_SCHEMA.COLUMNS as shown in the following syntax −SELECT *FROM (SELECT *FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= 'yourTableName') anyAliasName;Let us first create a table:mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(20), StudentLastName varchar(20), StudentAge int ); Query OK, 0 rows affected (1.51 sec)Here is the query to use `SHOW COLUMNS` as a valid data source −mysql> SELECT *FROM (SELECT *FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= 'DemoTable')tbl;This will produce the following output −+---------------+--------------+-------------+------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+-------------+------------+----------------+---------------------------------+----------------+-----------------------+--------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | ...
Read MoreCan we select second largest record from a table without using LIMIT clause in MySQL?
Yes, we can select second largest record from a table without using LIMIT clause. Let us first see an example and create a table −mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (0.66 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(78); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(67); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values(92); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(98); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(88); ...
Read MoreHow to skip blank and null values in MySQL?
To skip blank and null in MySQL, use the following syntax:select *from yourTableName where yourColumnName IS NOT NULL AND yourColumnName '';Let us first create a table:mysql> create table DemoTable (Id int, FirstName varchar(20)); Query OK, 0 rows affected (0.66 sec)Following is the query to insert records in the table using insert command:mysql> insert into DemoTable values(100, 'Larry'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(101, ''); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(102, 'Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(103, null); Query OK, 1 ...
Read MoreHow to count null values in MySQL?
To count null values in MySQL, you can use CASE statement. Let us first see an example and create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(20) ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstName) values('John'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(FirstName) values(null); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable(FirstName) values(''); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(FirstName) values('Larry'); Query OK, 1 row affected (0.17 ...
Read MoreMySQL Stored Procedure DEFINER=`root`@`%` is not working in localhost?
First of all, you need to check the host. The host can be ‘localhost’ or ‘%’. Check the existence of user accounts with host −mysql> select user, host from MySQL.user;This will produce the following output −+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | User2 | % | | mysql.infoschema | % | ...
Read More