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 438 of 547
How to implement Count (*) as variable from MySQL to display the number of records in a table?
The alias name can be used as a variable name in MySQL as shown in the below syntax −select count(*) AS anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable695 ( FirstName varchar(100) ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable695 values('Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable695 values('Robert'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable695 values('David'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable695 values('Mike'); Query OK, 1 row affected (0.16 ...
Read MoreHow to get the first and last record of the table in MySQL?
To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.Let us first create a table −mysql> create table DemoTable694 ( EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeName varchar(100), EmployeeSalary int ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable694(EmployeeName, EmployeeSalary) values('Chris', 457647); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable694(EmployeeName, EmployeeSalary) values('Robert', 90883); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable694(EmployeeName, EmployeeSalary) values('David', 123532); Query OK, 1 row ...
Read MoreCan we use "When" as column name in CREATE TABLE statement?
Before beginning, let us try to set ‘when’ as column name while using CREATE TABLE statement −mysql> create table DemoTable693( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), When datetime );This will produce the following output. An error would be visible:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'When datetime at line 5You need to wrap the reserved word using backticks, for example, `when`. Let us first create a table and implement the same:mysql> create table ...
Read MoreGet a list of MySQL databases and version?
To get a list of MySQL databases, following is the syntax -show databases;To get the server version, you can use the below syntax -select version();Let us implement the above syntax to get a list of MySQL databases and version -mysql> show databases;This will produce the following output displaying all the databases -+---------------------------+ | Database | +---------------------------+ | bothinnodbandmyisam | | business | | commandline | | customer-tracker | | customer_tracker_database ...
Read MoreHow to group by date regardless of time in MySQL?
When you have identical dates in a table with different time values for each, you can group them easily with GROUP BY DATE.Let us first create a table -mysql> create table DemoTable692 (DueDatetime datetime); Query OK, 0 rows affected (0.97 sec)Insert some records in the table using insert command:mysql> insert into DemoTable692 values('2019-07-21 10:20:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable692 values('2019-06-11 11:00:10'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable692 values('2019-07-21 11:00:10'); Query OK, 1 row affected (1.97 sec) mysql> insert into DemoTable692 values('2019-07-21 12:10:10'); Query OK, 1 row affected (0.18 sec)Display ...
Read MoreAdd a column to a MySQL table which is the result of concatenation of text and value from another auto increment column?
For this, you can use LAST_INSERT_ID(). Let us first create a table −mysql> create table DemoTable ( UserId int(6) unsigned zerofill NOT NULL AUTO_INCREMENT, UserAutoIncrement char(100) default null, PRIMARY KEY(UserId) ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+--------+-------------------+ | UserId | UserAutoIncrement | +--------+-------------------+ | 000001 | NULL | +--------+-------------------+ ...
Read MorePrint pyramid of tutorialspoint in PL/SQL
PL/SQL stands for “Procedural Language extension to SQL” . It is the mixture of SQL and Procedural features provided by programming language. It was developed by Oracle Corporation in the late 1980s as procedural extension language for SQL and the Oracle relational database.PL/SQL programs consists of blocks that can be nested and a block structure look likes this −DECLARE -- it contains declaration statements BEGIN -- It contains executable statements EXCEPTIONS -- It contains exception handling statements END;ExampleIn PL/SQL single-line comments begin with double hyphen(--) and Multi-line comments begin with a slash-asterisk ( /* ) and end ...
Read MoreSet a filter on field type to fetch MySQL columns with type text?
To set a filter for type, you can use below syntax −SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'yourDataTypeName';Let us implement the above syntax to show fields only with field type text −mysql> SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'text';This will produce the following output −+---------------------------------------------+--------------------------------+ | TABLE_NAME | COLUMN_NAME | +---------------------------------------------+--------------------------------+ | COLUMNS ...
Read MoreDisplay all deadlock logs in MySQL?
First of all, you need to enable innodb_print_all_deadlocks. Following is the syntax −set global innodb_print_all_deadlocks=1;After executing the above statement, let us execute the below syntax in order to display all deadlock logs −show engine innodb status;Let us implement the above syntax −mysql> set global innodb_print_all_deadlocks=1; Query OK, 0 rows affected (0.00 sec) mysql> show engine innodb status;This will produce the following output −+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Type | Name | Status ...
Read MoreSelect last 3 rows from database order by id ASC?
You can use subquery. Following is the syntax −SELECT * FROM ( SELECT * FROM yourTableName ORDER BY yourIdColumnName DESC LIMIT 3 ) anyAliasName ORDER BY yourIdColumnName;Let us first create a table −mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(100) ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ClientName) values('Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(ClientName) values('Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ClientName) values('Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(ClientName) values('David'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ClientName) values('Carol'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(ClientName) values('Robert'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(ClientName) values('Sam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(ClientName) values('Mike'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce ...
Read More