-- id
-- email: user1@vlrtraining.in, user2@vlrtraining.in, ...
-- random date
-- enabled (random boolean)
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null,
active boolean default false);
use classicmodels;
select round(rand());
select date(now()) - interval floor(10000*rand()) day;
drop table if exists users;
drop procedure testdata;
delimiter $$
create procedure testdata()
begin
declare NUMROWS int default 500;
declare count int default 0;
declare registered_value date default null;
declare email_value varchar(40) default null;
declare active_value boolean default false;
drop table if exists users;
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
while count < NUMROWS do
set registered_value := date(now()) - interval floor(10000*rand()) day;
set active_value := round(rand());
set email_value := concat("user", count, "@vlrtraining.com");
insert into users (email, registered, active) values (email_value, registered_value, active_value);
set count := count + 1;
end while;
end$$
delimiter ;
drop procedure testdata;
delimiter $$
create procedure testdata()
begin
drop table if exists users;
create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
end$$
delimiter ;
-----------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> select Now();
+---------------------+
| Now() |
+---------------------+
| 2021-09-28 19:23:31 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date(Now());
+-------------+
| date(Now()) |
+-------------+
| 2021-09-28 |
+-------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval 365 ;
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 '' at line 1
mysql> select date(Now()) - interval 365 day;
+--------------------------------+
| date(Now()) - interval 365 day |
+--------------------------------+
| 2020-09-28 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval rand() day;
+-----------------------------------+
| date(Now()) - interval rand() day |
+-----------------------------------+
| 2021-09-27 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval rand() day;
+-----------------------------------+
| date(Now()) - interval rand() day |
+-----------------------------------+
| 2021-09-28 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-09-23 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-01-12 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-06-08 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2019-11-12 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2021-06-27 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-08-19 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2019-09-11 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval (1000 * rand()) day;\
+--------------------------------------------+
| date(Now()) - interval (1000 * rand()) day |
+--------------------------------------------+
| 2020-04-21 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-09-14 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-11-12 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((1000 * rand())) day;
+---------------------------------------------------+
| date(Now()) - interval round((1000 * rand())) day |
+---------------------------------------------------+
| 2020-06-23 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2012-02-18 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2011-08-20 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 2000-01-11 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date(Now()) - interval round((10000 * rand())) day;
+----------------------------------------------------+
| date(Now()) - interval round((10000 * rand())) day |
+----------------------------------------------------+
| 1998-04-02 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> delimiter $$
mysql> create procedure testdata()
-> begin
-> drop table if exists users;
-> create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
->
-> end$$
ERROR 1304 (42000): PROCEDURE testdata already exists
mysql> delimiter ;
mysql> drop procedure testdata;
Query OK, 0 rows affected (0.27 sec)
mysql> delimiter $$
mysql> create procedure testdata()
-> begin
-> drop table if exists users;
-> create table users (id int auto_increment primary key, email varchar(40) not null, registered date not null, active boolean default false);
->
-> end$$
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter ;
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts |
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
| users |
+-------------------------+
10 rows in set (0.44 sec)
mysql>
mysql> drop table users;
Query OK, 0 rows affected (1.39 sec)
mysql> call testdata();
Query OK, 0 rows affected (1.29 sec)
mysql> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| accounts |
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
| users |
+-------------------------+
10 rows in set (0.00 sec)
mysql> desc users;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| email | varchar(40) | NO | | NULL | |
| registered | date | NO | | NULL | |
| active | tinyint(1) | YES | | 0 | |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.07 sec)
mysql>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.