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 314 of 355
Set MySQL int column to auto increment by 1 beginning at 10000?
Let us first create a table. Here, we have set UserId as AUTO_INCREMENT PRIMARY KEY −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY ); Query OK, 0 rows affected (0.72 sec)Following is the query to set int column to auto increment by 1 beginning at 10000 −mysql> alter table DemoTable AUTO_INCREMENT=10000; Query OK, 0 rows affected (0.31 sec) Records: 0 Duplicates: 0 Warnings: 0Insert records in the table using insert command −mysql> insert into DemoTable values(); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.19 sec) ...
Read MoreWhat is the easiest way to store date in MySQL database?
To store date in MySQL, use the STR_TO_DATE() method −insert into yourTableName values(STR_TO_DATE('yourDate', '%d/%m/%Y'));Let us first create a table −mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.62 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(STR_TO_DATE('10/01/2013', '%d/%m/%Y')); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(STR_TO_DATE('31/01/2015', '%d/%m/%Y')); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(STR_TO_DATE('23/04/2019', '%d/%m/%Y')); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(STR_TO_DATE('01/03/2019', '%d/%m/%Y')); Query OK, 1 row affected (0.48 sec)Display records from the table using select ...
Read MoreHow to subtract by 1 if the field value > 0 in MySQL?
You can use CASE statement with UPDATE command for this. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value int ); Query OK, 0 rows affected (1.44 sec)Insert records in the table using insert command −mysql> insert into DemoTable(Value) values(100); Query OK, 1 row affected (0.47 sec) mysql> insert into DemoTable(Value) values(0); Query OK, 1 row affected (4.16 sec) mysql> insert into DemoTable(Value) values(104); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(Value) values(0); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Value) values(5); Query ...
Read MoreOrdering alphabetically in MySQL except for one entry?
You can use ORDER BY clause for this. Let us first create a table −mysql> create table DemoTable ( FirstName varchar(200) ); Query OK, 0 rows affected (0.93 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('Larry'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Carol'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.18 sec)Display records from the ...
Read MoreHow to select data from a table where the table name has blank spaces in MYSQL?
You need to use backticks around the table name where the table name has blank space. Let us first create a table. Here, we have used backtick −mysql> create table `Demo Table138` ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Price int ); Query OK, 0 rows affected (0.47 sec)Insert records in the table using insert command −mysql> insert into `Demo Table138`(Price) values(450); Query OK, 1 row affected (0.18 sec) mysql> insert into `Demo Table138`(Price) values(499); Query OK, 1 row affected (0.16 sec) mysql> insert into `Demo Table138`(Price) values(199); Query OK, 1 row affected (0.17 sec) mysql> insert into ...
Read MoreHow to align a column right-adjusted in MySQL?
You can use LPAD() from MySQL for this. Let us first create a table −mysql> create table DemoTable ( FullName varchar(100) ); Query OK, 0 rows affected (0.81 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('John Smith'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values('David Miller'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('Sam Williams'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Carol Taylor'); Query OK, 1 row affected (0.47 sec)Display records from the table using select command −mysql> select *from DemoTable;This ...
Read MoreSelect MySQL rows where column contains same data in more than one record?
Use MySQL JOIN to select MySQL rows where column contains same data in more than one record. Let us first create a table −mysql> create table DemoTable ( UserId int, UserName varchar(20) ); Query OK, 0 rows affected (0.54 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(10, 'John'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(11, 'Sam'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(12, 'Larry'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(13, 'David'); Query OK, 1 row affected (0.17 ...
Read MoreMySQL query to select column where value = one or value = two, value = three, etc?
Let us first create a table −mysql> create table DemoTable ( UserId int, UserName varchar(10), UserAge int ); Query OK, 0 rows affected (0.73 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(101, 'Chris', 23); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(102, 'Robert', 33); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(103, 'David', 25); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(104, 'Carol', 35); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable values(105, 'Bob', 29); Query OK, ...
Read MoreMySQL order by 0 first and then display the record in descending order?
In order to order by 0 first and then largest, you can use the below syntax −select *from yourTableName order by yourColumnName=0 DESC, yourColumnName DESC;Let us first create a table −mysql> create table DemoTable ( Value int ); Query OK, 0 rows affected (0.65 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(90); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.12 ...
Read MoreWhat MySQL databases do I have permissions on?
To check this, you can use SHOW command. The syntax is as follows −show grants\GLet us implement the above syntax to display the permissions you have −mysql> SHOW GRANTS\GThis will produce the following output −*************************** 1. row *************************** Grants for root@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION *************************** 2. row *************************** Grants for root@%: ...
Read More