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
-
Economics & Finance
MySQL Articles
Page 182 of 355
MySQL permissions to view all databases?
For this, the syntax is as follows −revoke show databases on *.* from 'yourUserName'@'yourHostName';Let us display all usernames along with host name −mysql> select user, host from MySQL.user;This will produce the following output −+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | Charlie | % | | Robert | % | | User2 | % ...
Read MoreCreate variables in MySQL stored procedure with DECLARE keyword
Use MySQL DECLARE for variables in stored procedure −DECLARE anyVariableName int DEFAULT anyValue;Let us implement the above syntax in order to create variables in stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE variable_Demo() -> BEGIN -> DECLARE lastInsertedId int DEFAULT -1; -> select lastInsertedId; -> set @providedLastId=10001; -> select @providedLastId; -> END -> // Query OK, 0 rows affected (0.32 sec) mysql> DELIMITER ;Now you can call the above stored procedure using CALL command −mysql> call variable_Demo();This will produce the following output −+----------------+ | lastInsertedId | ...
Read MoreTreat a MySQL column field as NULL if it is blank?
Let us first create a table −mysql> create table DemoTable1362 -> ( -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ClientName varchar(40) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1362(ClientName) values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1362(ClientName) values(' '); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable1362(ClientName) values('Bob'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1362(ClientName) values(' '); Query OK, 1 row affected (0.12 sec) mysql> insert ...
Read MoreDelete URLs with specific domains from MySQL database?
To delete URLs with specific domains, use DELETE and LIKE clause.Let us first create a table −mysql> create table DemoTable1361 -> ( -> URL text -> ) ; Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1361 values('Https://www.google.com//?id=1'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1361 values('Https://www.facebook.com//?id=2&name=John'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1361 values('Https://www.yahoo.com//?id=3'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable1361 values('Https://www.google.com//?id=1'); Query OK, 1 row affected (0.16 sec)Display all records from ...
Read MoreGet current year in MySQL WHERE clause?
To get current year, use YEAR() along with CURDATE(). Let us first create a table −mysql> create table DemoTable1360 -> ( -> JoiningYear int -> ) -> ; Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1360 values(1998); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1360 values(2018); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable1360 values(2016); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1360 values(2019); Query OK, 1 row affected (0.13 sec) mysql> ...
Read MoreUsing backticks in CONTACT() gives an error in MySQL
Do not use backticks, you can use single quotes in CONCAT(). Following is the syntax −select concat(yourColumnName1, ' ', yourColumnName2) from yourTableName;Let us first create a table −mysql> create table DemoTable1359 -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1359 values(101, 'Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1359 values(102, 'Adam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1359 values(103, 'Mike'); Query OK, 1 row ...
Read MoreSum values in MySQL from similar day records
Use GROUP BY and DATE() for this. Let us first create a table −mysql> create table DemoTable1358 -> ( -> PurchaseDate datetime, -> ProductPrice int -> ); Query OK, 0 rows affected (1.59 sec)Insert some records in the table using insert command. Here, we have inserted date records, with some records with similar dates −mysql> insert into DemoTable1358 values('2019-09-20 12:34:00', 450); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1358 values('2019-09-21 11:00:00', 1050); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1358 values('2018-09-21 02:10:00', 2050); Query OK, 1 ...
Read MoreDisplay the result with not null value first and then with null value in MySQL
Let us first create a table −mysql> create table DemoTable1357 -> ( -> StudentName varchar(40), -> StudentCountryName varchar(30) -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1357 values('Chris', 'US'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1357 values('David', NULL); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1357 values('David', 'AUS'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1357 values('Carol', NULL); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1357 values('Mike', ...
Read MoreAlter a table column from VARCHAR to NULL in MySQL
To alter, use the ALTER command with CHANGE as in the below syntax −alter table yourTableName change yourColumnName yourColumnName datatype NULL DEFAULT NULL;Let us first create a table −mysql> create table DemoTable1356 -> ( -> FirstName varchar(30) -> ); Query OK, 0 rows affected (0.56 sec)Let us implement the above syntax to alter a table column to NULL −mysql> alter table DemoTable1356 change FirstName FirstName varchar(30) NULL DEFAULT NULL; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1356 ...
Read MoreMySQL query to return all items in a single row
For this, use GROUP_CONCAT(). Let us first create a table−mysql> create table DemoTable1355 -> ( -> Location text -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1355 values('E:'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1355 values('AllPrograms'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1355 values('ChatApplicationInJava'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable1355 values('MainFolder'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select * ...
Read More