DB Intro - MySQL and MongoDB

 

 

mysql> CREATE TABLE tutorials_tbl(
 tutorial_id INT,
 tutorial_title VARCHAR(100),
 tutorial_author VARCHAR(40),
 submission_date DATE,
 PRIMARY KEY ( tutorial_id )
);

create table tt2(
  tid int not null auto_increment,
  name varchar(100) not null,
  sdata date,
  primary  key(tid)
);

create table commodity
(
id int unsigned not null auto_increment primary key,
commodity_name varchar(50) not null,
formatI varchar(50) not null,
price double not null,
quantity double not null,
period int(20),
purchase_sel varchar(6),
remark varchar(100),
write_time timestamp default current_timestamp
)charset utf8;

create table tt3(
  tid int not null auto_increment,
  name varchar(100) not null,
  sdata date,
  tdata date,
  primary  key(tid)
);

CREATE TABLE IF NOT EXISTS tasks (
task_id INT(11) NOT NULL AUTO_INCREMENT,
subject VARCHAR(45) DEFAULT NULL,
start_date DATE DEFAULT NULL,
end_date DATE DEFAULT NULL,
description VARCHAR(200) DEFAULT NULL,
PRIMARY KEY(task_id)
);

INSERT INTO tasks(subject,start_date,end_date,description) VALUES('Learn MySQL INSERT','2010-01-01','2010-01-02','Start learning..');

INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (4,'Test ON DUPLICATE KEY UPDATE','2010-01-01','2010-01-02','Next Priority')
ON DUPLICATE KEY UPDATE
task_id=task_id+1,
subject='Test ON DUPLICATE KEY UPDATE';

INSERT INTO tasks(task_id,subject,start_date,end_date,description)
VALUES (5,'Test ON DUPLICATE KEY UPDATE','2016-01-01','2016-01-06','Next Priority03')
ON DUPLICATE KEY UPDATE
task_id=task_id+1,
subject='Test ON DUPLICATE KEY UPDATE';

UPDATE tasks SET
task_id=task_id+1,
subject='Test ON DUPLICATE KEY UPDATE06'
WHERE
task_id=5;

CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

CREATE TABLE event (name VARCHAR(20), date DATE,
type VARCHAR(15), remark VARCHAR(255));

SELECT pet.name, TIMESTAMPDIFF(YEAR,birth,date) AS age, remark
FROM pet INNER JOIN event ON pet.name = event.name
WHERE event.type = 'litter';

db.createUser({
user: "admin",
pwd: "admin@mongodb",
roles:[{role:"root",db:"admin"}]
});

mongo --port 27017 -u "admin" -p "admin@mongodb" --authenticationDatabase "admin"