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
Database Articles
Page 329 of 547
Set NOT NULL attribute to an existing column in MySQL
To set NOT NULL attribute to an existing column, use ALTER TABLE command. Let us first create a table −mysql> create table DemoTable1949 ( UserId int, UserName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Here is the query to set NOT NULL attribute to an existing column −mysql> alter table DemoTable1949 modify UserName varchar(20) not null; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0Let us check the description of table −mysql> desc DemoTable1949;This will produce the following output −+----------+-------------+------+-----+---------+-------+ | Field | Type | Null | ...
Read MoreSort only numbers from alphanumeric string in MySQL?
To sort only numbers from alphanumeric string, use ORDER BY RIGHT(). Let us first create a table −mysql> create table DemoTable1948 ( StudentCode varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1948 values('121John_567'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1948 values('Adam_101'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1948 values('Bob_563'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1948 values('Sam_346'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> ...
Read MoreMySQL SELECT from a subquery and then perform DELETE?
Let us first create a table −mysql> create table DemoTable1947 ( Id int, Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1947 values(101, 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1947 values(102, 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1947 values(103, 'Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1947 values(104, 'Bob'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1947 values(105, 'Sam'); Query OK, 1 row affected (0.00 sec)Display ...
Read MoreFetch the count of a specific date in a MySQL table
Let us first create a table −mysql> create table DemoTable1946 ( ShippingDate date ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1946 values('2019-12-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1946 values('2018-12-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1946 values('2017-12-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1946 values('2019-04-22'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1946 values('2019-12-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1946 values('2015-12-11'); Query OK, 1 ...
Read MoreInsert records from multiple tables in MySQL
To insert records from multiple tables, use INSERT INTO SELECT statement. Here, we will insert records from 2 tables.Let us first create a table −mysql> create table DemoTable1943 ( Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1943 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1943 values('Robert'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1943;This will produce the following output −+--------+ | Name | +--------+ | Chris ...
Read MoreHow to fetch all databases with name having upper case character after some word using MySQL?
For this, use regular expression. The syntax is as follows −select * from information_schema.schemata WHERE SCHEMA_NAME REGEXP '^yourValue_+[A-Z]';Let us create some databases −mysql> create database bank_APP1; Query OK, 1 row affected (0.00 sec) mysql> create database bank_APP2; Query OK, 1 row affected (0.00 sec) mysql> create database bank_APP3; Query OK, 1 row affected (0.00 sec)Here is the query to get all databases having upper case character after some word −mysql> select * from information_schema.schemata WHERE SCHEMA_NAME REGEXP '^bank_+[A-Z]';This will produce the following output −+--------------+-------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+-------------+----------------------------+------------------------+----------+ | def ...
Read MoreReturn the field with highest count in MySQL
To return the field with highest count, use ORDER BY COUNT(*). Let us first create a table −mysql> create table DemoTable1940 ( FirstName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1940 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Adam'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1940 values('Chris'); Query OK, 1 row ...
Read MoreMySQL query to select everything to left of last space in a column with name records
For this, you can use LEFT(). Let us first create a table −mysql> create table DemoTable1939 ( FullName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1939 values('Adam Smith'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Robert Downey, Jr.'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Sylvester Stallone'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1939 values('Chris Hemsworth'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> ...
Read MoreDisplay all fields of a table in MySQL?
To display all fields, set the database with table_schema and specific table with table_name as in the below syntax −select column_name as anyAliasName from information_schema.columns where table_schema=database() and table_name=’yourTableName’\GLet us first create a table −mysql> create table DemoTable1938 ( StudentId int, StudentName varchar(20), StudentAge int, StudentCountryName varchar(20), StudentMobileNumber bigint ); Query OK, 0 rows affected (0.00 sec)Here is the query to display all fields of a table −mysql> select column_name as ALL_FIELDS from information_schema.columns where table_schema=database() and table_name='DemoTable1938'\GThis will produce the following output −*************************** 1. row *************************** ALL_FIELDS: StudentId ...
Read MoreMySQL ORDER BY ASC and display NULLs at the bottom?
For this, use CASE statement with ORDER BY. Let us first create a table −mysql> create table DemoTable1937 ( Name varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1937 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values(NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values('Adam'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values('John'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1937 values(''); Query OK, 1 row affected (0.00 sec) ...
Read More