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 309 of 355
How 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 MoreTurn on the general log in MySQL?
To turn ON the general log, you need to use SET command. Following is the syntax −set global general_log=ON;Let us check the general log status with the help of SHOW command −mysql> SHOW variables like '%general_log%';This will produce the following output −+------------------+---------------------+ | Variable_name | Value | +------------------+---------------------+ | general_log | OFF | | general_log_file | DESKTOP-QN2RB3H.log | +------------------+---------------------+ 2 rows in set ...
Read More