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 137 of 355
Get maximum age from records with similar student names in MySQL
For this, you can use GROUP BY along with aggregate function MAX(). Let us first create a table −mysql> create table DemoTable1964 ( StudentName varchar(20), StudentAge int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1964 values('Chris', 23); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1964 values('David', 34); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1964 values('Chris', 27); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1964 values('Sam', 31); Query OK, 1 row affected (0.00 sec) mysql> ...
Read MoreMySQL query to create user and grant permission
To create a user and grant permission, the syntax is as follows −create database yourDatabaseName DEFAULT CHARACTER SET utf8; create user `yourUserName` identified by yourPassword; GRANT SELECT ON yourDatabaseName .* TO `yourUserName`; GRANT INSERT ON yourDatabaseName .* TO `yourUserName`; GRANT UPDATE ON yourDatabaseName .* TO `yourUserName`; GRANT DELETE ON yourDatabaseName .* TO `yourUserName`; GRANT EXECUTE ON yourDatabaseName .* TO `yourUserName`;Here is the query to create user and grant permission −mysql> create database demo_app DEFAULT CHARACTER SET utf8; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> create user `John_123` identified by '123456'; Query OK, 0 rows affected (0.00 sec) ...
Read MoreMySQL query to separate and select string values (with hyphen) from one column to different columns
For this, you can use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable1962 ( EmployeeInformation text ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1962 values('101-John-29'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1962 values('102-David-35'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1962 values('103-Chris-28'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1962;This will produce the following output −+---------------------+ | EmployeeInformation | +---------------------+ | 101-John-29 ...
Read MoreParse a string to get a number from a large string separated by underscore
Let us first create a table −mysql> create table DemoTable1961 ( Title text ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1961 values('You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1961;This will produce the following output −+------------------------------------------------------------------------------------+ | Title ...
Read MoreA single MySQL query to combine strings from many rows into a single row and display the corresponding User Id sum in another column?
For this, you can use GROUP_CONCAT(). Use SUM() to add the User Id. Let us first create a table −mysql> create table DemoTable1960 ( StudentId int, StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1960 values(100, 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1960 values(101, 'Bob'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1960 values(102, 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1960 values(103, 'Mike'); Query OK, 1 row affected (0.00 sec)Display ...
Read MoreCreate a dynamic table name from current year in MySQL like 2019
To create a table name like year (2019), use PREPARE statement. Let us first create a table −mysql> create table DemoTable1959 ( UserName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1959 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1959 values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1959 values('Bob'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1959;This will produce the following output −+----------+ | UserName ...
Read MoreDisplay auto increment user id sequence number to begin from 001 in MySQL?
For this, use ZEROFILL and alter the table to begin from the same sequence −alter table yourTableName change yourColumnName yourColumnName int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY;To understand the above syntax, let us first create a table −mysql> create table DemoTable1958 ( UserId int, UserName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Here is the query to alter generated sequence number to begin from 001:mysql> alter table DemoTable1958 change UserId UserId int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0Let ...
Read MoreDivide a column to get monthly salary of employees in a MySQL Query?
Let us first create a table −mysql> create table DemoTable1957 ( EmployeeId int, EmployeeName varchar(20), EmployeeSalary int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1957 values(1, 'Chris', 240000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(2, 'Bob', 120000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(3, 'David', 180000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(4, 'Mike', 650000); Query OK, 1 row affected (0.00 sec)Display all records from the table using ...
Read MoreSelect a column if condition is met in MySQL to fetch records from current date and current date + 1
Let us first get the current date −mysql> select curdate();This will produce the following output −+------------+ | curdate() | +------------+ | 2019-12-15 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1956 ( ProductId int, ProductName varchar(20), CustomerName varchar(20), ShippingDate date ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1956 values(101, 'Product-1', 'Sam', '2019-10-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1956 values(102, 'Product-2', 'Carol', '2018-12-01'); Query OK, 1 row affected (0.00 sec) ...
Read MoreMySQL - How can I fix an auto increment field with deleted rows from 1,2,3,4,5 to 1,3,5). Now we want it to be 1,2,3
Let us first create a table −mysql> create table DemoTable1955 ( UserId int NOT NULL AUTO_INCREMENT , PRIMARY KEY(UserId) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec)Display ...
Read More