mysqlnote

to start:

sudo service mssql start

sudo serv ice mssql stop

to login:

mysql -u test -p

to execute sql:

\h
show databases;
use test
show tables;
help user;

select user();

help create user;
CREATE USER ‘test’@’localhost’ IDENTIFIED with ‘yourpasword’;
help grant
create database test;
grant all on test.* to ‘test’@’localhost’;
use test;
show tables;
CREATE TABLE item (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
description VARCHAR(30) NOT NULL,
price DOUBLE NOT NULL,
stock INTEGER NOT NULL);
CREATE TABLE item (       id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,       name VARCHAR(30) NOT NULL,       description VARCHAR(30) NOT NULL,       price DOUBLE NOT NULL,       stock INTEGER NOT NULL);
show tables;
describe item;
select * from item;
select count(*) from item;
INSERT INTO item (name, description, price, stock)
VALUES (‘Straw Hat’, ‘The best in town’, 78.99, 9012);
INSERT INTO item (name, description, price, stock)       VALUES (‘Straw Hat’, ‘The best in town’, 78.99, 9012);
select * from item;
INSERT INTO item (name, description, price, stock)
VALUES (‘Polo Shirt’, ‘The latest fashion’, 49.99, 99);
INSERT INTO item (name, description, price, stock)       VALUES (‘Polo Shirt’, ‘The latest fashion’, 49.99, 99);
select * from item;
insert item (name, description, price, stock)
values (‘shoe’, ‘a shoe’, 90.00, 1);
insert item (name, description, price, stock) values (‘shoe’, ‘a shoe’, 90.00, 1);
select * from item;
insert item (name, description, price, stock) values (‘shoe’, ‘a shoe’, 90.00);

select * from item;
delete from item where id=3;
show Databases;
use test;
show tables;
use item;
show * from item;
show item;
;
show * from item;
select * from item;
create table customer{
id integer not null primary key auto_increment,
name varchar(30) not null,
address varchar(200) not null,
tel VARCHAR(15) NOT NULL,
email VARCHAR(100) NOT NULL);
create table customer{ id integer not null primary key auto_increment, name varchar(30) not null, address varchar(200) not null, tel VARCHAR(15) NOT NULL,  email VARCHAR(100) NOT NULL);
CREATE TABLE customer (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
address VARCHAR(200) NOT NULL,
tel VARCHAR(15) NOT NULL,
email VARCHAR(100) NOT NULL);
CREATE TABLE customer (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  name VARCHAR(30) NOT NULL,  address VARCHAR(200) NOT NULL,  tel VARCHAR(15) NOT NULL,  email VARCHAR(100) NOT NULL);
describe customer;
insert into customer(name,address,tel,email) values(‘Jake’,’10 Street’,’4167777777′,’jake@gmail.com’);
select * from customer;
update customer
set tel = ‘4163333333’ where id = 1;
update customer set tel = ‘4163333333’ where id = 1;
select * from customer;
CREATE TABLE orders (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
itemid INTEGER NOT NULL,
saleprice DOUBLE NOT NULL,
gst DOUBLE NOT NULL,
qty INTEGER NOT NULL,
customerid INTEGER NOT NULL,
status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,
orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE orders (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL,  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL,  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
describe orders;
select * from orders;
alter table orders add FOREIGN KEY (itemid) REFERENCES item(id);
describe orders;
alter table orders add FOREIGN KEY (itemid) REFERENCES item(id);
insert into orders(itemid,saleprice,gst,qty,customerid) values (1000,23.0,3.0,3,20);
select * from orders;
select * from items;
select * from item;
alter table orders drop FOREIGN KEY (itemid) REFERENCES item(id);
alter table orders drop FOREIGN KEY (itemid);
alter table orders add FOREIGN KEY (itemid) REFERENCES item(id);
alter table orders drop FOREIGN KEY (itemid);
select * from orders;
show index;
show index from orders;
drop index orders(itemid);
help drop index;
drop index itemid on orders;
show index;
show index on orders;
show index from orders;
alter table orders add FOREIGN KEY (itemid) REFERENCES item(id);
show index from orders;
help alter table
update orders set itemid=10
;
select * from orders;
help alter table add constraints
help alter table
drop table orders;
CREATE TABLE orders (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
itemid INTEGER NOT NULL REFERENCES item(id),
saleprice DOUBLE NOT NULL,
gst DOUBLE NOT NULL,
qty INTEGER NOT NULL,
customerid INTEGER NOT NULL REFERENCES customer(id),
status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,
orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE orders (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL REFERENCES item(id),  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL REFERENCES customer(id),  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
describe orders;
describe item;
show create table orders;
alter table orders add foreign key itemid references item(id);
alter table orders engine=InnoDB;
alter table orders add foreign key (itemid) references item(id);
select * from orders;
drop table orders;
CREATE TABLE orders (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL REFERENCES item(id),  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL REFERENCES customer(id),  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP) engine=InnoDB;
insert into orders(itemid,saleprice,gst,qty,customerid) values(100,34.0,2.0,2,430);
select * from orders;
create table vendors(
id int unsigned primary key not null auto_increment,
vendor varchar(255) not null unique
) engine = InnoDB;
create table vendors( id int unsigned primary key not null auto_increment, vendor varchar(255) not null unique ) engine = InnoDB;
create table phones(
id int unsigned primary key not null auto_increment,
model varchar(255) not null,
vid int unsigned not null,
foreign key(vid) references vendors(id)
delete cascade
update cascade
) engine = InnoDB;
create table phones( id int unsigned primary key not null auto_increment, model varchar(255) not null, vid int unsigned not null, foreign key(vid) references vendors(id)  delete cascade  update cascade ) engine = InnoDB;
create table phones(
id int unsigned primary key not null auto_increment,
model varchar(255) not null,
vid int unsigned not null,
foreign key(vid) references vendors(id)
delete cascade
update cascade
) engine = InnoDB;
create table phones( id int unsigned primary key not null auto_increment, model varchar(255) not null, vid int unsigned not null, foreign key(vid) references vendors(id)  delete cascade  update cascade ) engine = InnoDB;
create table phones(
id int unsigned primary key not null auto_increment,
model varchar(255) not null,
vid int unsigned not null,
foreign key(vid) references vendors(id)
delete cascade
update cascade
asdfasdfasd;
create table phones( id int unsigned primary key not null auto_increment, model varchar(255) not null, vid int unsigned not null, foreign key(vid) references vendors(id)  delete cascade  update cascade  asdfasdfasd;
create table phones(
id int unsigned primary key not null auto_increment,
model varchar(255) not null,
vid int unsigned not null,
foreign key(vid) references vendors(id)
on delete cascade
on update cascade
) engine = InnoDB;
create table phones( id int unsigned primary key not null auto_increment, model varchar(255) not null, vid int unsigned not null, foreign key(vid) references vendors(id)  on delete cascade on update cascade ) engine = InnoDB;
insert into vendors(vendor) values (‘Nokia’),(‘Sony-Ericson’),(‘Samsung’),(‘Siemens’);
select * from vendors
;
insert into phones (model,vid) values (‘6600’,1),(‘6630’,1),(‘3650’,1),(‘p800’,2),(‘p900’,2);
select * from phones;
insert into phones(model,vid) values (‘unexistant’,5);
select * from orders;
alter table orders add FOREIGN KEY (itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE;
drop table orders;
show databases;
use test;
CREATE TABLE orders (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
itemid INTEGER NOT NULL,
saleprice DOUBLE NOT NULL,
gst DOUBLE NOT NULL,
qty INTEGER NOT NULL,
customerid INTEGER NOT NULL,
status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,
orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
engine = InnoDB;
CREATE TABLE orders (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL,  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL,  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP)  engine = InnoDB;
alter table orders add FOREIGN KEY (itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE;
alter table orders add FOREIGN KEY (customerid) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE;
alter table orders add FOREIGN KEY (itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE engine = InnoDB;
drop table orders;
CREATE TABLE orders (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
itemid INTEGER NOT NULL REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
saleprice DOUBLE NOT NULL,
gst DOUBLE NOT NULL,
qty INTEGER NOT NULL,
customerid INTEGER NOT NULL REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE,
status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,
orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
engine = InnoDB;
CREATE TABLE orders (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE,  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP)  engine = InnoDB;
insert into orders (itemid,saleprice,gst,qty,customerid) values(123,100,23,3,19299);
drop table orders;
CREATE TABLE orders (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
itemid INTEGER NOT NULL,
saleprice DOUBLE NOT NULL,
gst DOUBLE NOT NULL,
qty INTEGER NOT NULL,
customerid INTEGER NOT NULL,
status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,
orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(customerid) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE)
engine = InnoDB;
CREATE TABLE orders (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL,  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL,  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(customerid) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE)  engine = InnoDB;
describe item;
help flush;
flush;
CREATE TABLE orders1 (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL,  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL,  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(customerid) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE)  engine = InnoDB;
CREATE TABLE orders1 (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL,  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL,  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE)  engine = InnoDB;
drop table items;
drop table item;
create CREATE TABLE item (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
description VARCHAR(30) NOT NULL,
price DOUBLE NOT NULL,
stock INTEGER NOT NULL) engine=InnoDB;
create CREATE TABLE item (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  name VARCHAR(30) NOT NULL,  description VARCHAR(30) NOT NULL,  price DOUBLE NOT NULL,  stock INTEGER NOT NULL) engine=InnoDB;
CREATE TABLE item (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  name VARCHAR(30) NOT NULL,  description VARCHAR(30) NOT NULL,  price DOUBLE NOT NULL,  stock INTEGER NOT NULL) engine=InnoDB;
drop table customers;
drop table customer;
show tables
;
CREATE TABLE customer (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
address VARCHAR(200) NOT NULL,
tel VARCHAR(15) NOT NULL,
email VARCHAR(100) NOT NULL) engine=InnoDB;
CREATE TABLE customer (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  name VARCHAR(30) NOT NULL,  address VARCHAR(200) NOT NULL,  tel VARCHAR(15) NOT NULL,  email VARCHAR(100) NOT NULL) engine=InnoDB;
CREATE TABLE orders (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
itemid INTEGER NOT NULL,
saleprice DOUBLE NOT NULL,
gst DOUBLE NOT NULL,
qty INTEGER NOT NULL,
customerid INTEGER NOT NULL,
status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,
orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(customerid) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE)
engine = InnoDB;
CREATE TABLE orders (  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,  itemid INTEGER NOT NULL,  saleprice DOUBLE NOT NULL,  gst DOUBLE NOT NULL,  qty INTEGER NOT NULL,  customerid INTEGER NOT NULL,  status ENUM(‘RECEIVED’,’PROCESSED’,’SHIPPED’,’COMPLETED’) DEFAULT ‘RECEIVED’,  orderdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(itemid) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(customerid) REFERENCES customer(id) ON DELETE CASCADE ON UPDATE CASCADE)  engine = InnoDB;
INSERT INTO item (name, description, price, stock)
VALUES (‘Straw Hat’, ‘The best in town’, 78.99, 9012);
INSERT INTO item (name, description, price, stock)  VALUES (‘Straw Hat’, ‘The best in town’, 78.99, 9012);
INSERT INTO item (name, description, price, stock)
VALUES (‘Polo Shirt’, ‘The latest fashion’, 49.99, 99);
INSERT INTO item (name, description, price, stock)  VALUES (‘Polo Shirt’, ‘The latest fashion’, 49.99, 99);
INSERT INTO item (name, description, price, stock)
VALUES (‘Lady Hat’, ‘The latest fashion’, 69.99, 100);
INSERT INTO item (name, description, price, stock)  VALUES (‘Lady Hat’, ‘The latest fashion’, 69.99, 100);
insert into customer (name,address,tel,email) values (‘jake’,’10 street’,’4163333333′,’jake@gmail.com’);
insert into orders (itemid, saleprice, gst, qty, customerid) values (100, 100, 32, 100, 100);
insert into orders (itemid, saleprice, gst, qty, customerid) values (3, 100, 32, 100, 100);
insert into orders (itemid, saleprice, gst, qty, customerid) values (3, 100, 32, 100, 1);
select * from orders;
ALTER TABLE item ADD CONSTRAINT chk_stock CHECK (stock >=0);
describe items;
describe item;
insert into item (name, description, price, stock) value (‘boot’,’b’,123,-2);
select * from item;
show create table;
show create table item;
ALTER TABLE item ADD CONSTRAINT chk_stock CHECK (stock >=0);
insert into item (name, description, price, stock) value (‘boot’,’b’,123,-2);
select * from item;
delete from item where stock < 0;
update item set price = price-20;
select * from item;
select * from item order by name;
select * from item order by name desc;
select count(*) from item;
select count(*) from item where id > 2;
select count(*) from item where description like ‘%fashion%’;
select count(*), description  from item group by description;
select count(*), description  from item group by description having count(*) > 1;
select count(*), description  from item group by description having description like ‘%fa%’;
select count(*), description  from item group by description where  description like ‘%fa%’;
insert item (name, description,price,stock
)
select name,description, price,stock from item;
insert item (name, description,price,stock ) select name,description, price,stock from item;
select * from item;
insert item (name, description,price,stock ) select name,description+” 2012″, price+200,stock from item;
select * from item;
select count(*), description  from item group by description having count(*) > 1;
select count(*), name  from item group by name having count(*) > 1;
delete from item where description like ‘2012’;
select * from item;
select * from item where description like ‘2012’;
insert item (name, description,price,stock ) select name,description & ” 2012″, price+200,stock from item;
select * from item where description like ‘2012’;
select * from item;
select * from item where description like ‘0’;
create table mydelete select * from item where description like ‘0’;
delete from item where description like ‘0’;
select * from item;
insert item (name, description,price,stock ) select name,description, price,stock from mydelete;
select * from item;
drop table mydelete;
REATE TRIGGER itemupdate BEFORE INSERT ON orders FOR EACH ROW
UPDATE item SET sadsfasdfasdfasdfasdfasdfasdftock = stock – NEW.qty WHERE item.id=NEW.itemid;
REATE TRIGGER itemupdate BEFORE INSERT ON orders FOR EACH ROW UPDATE item SET sadsfasdfasdfasdfasdfasdfasdftock = stock – NEW.qty WHERE item.id=NEW.itemid;
CREATE TRIGGER itemupdate BEFORE INSERT ON orders FOR EACH ROW
UPDATE item SET stock = stock – NEW.qty WHERE item.id=NEW.itemid;
CREATE TRIGGER itemupdate BEFORE INSERT ON orders FOR EACH ROW UPDATE item SET stock = stock – NEW.qty WHERE item.id=NEW.itemid;
select * from orders;
select * from orders where id=1;
select * from item where id=1;
insert orders (itemid,saleprice,gst,qty,customerid) values (1,78.99,78.99*0.13,1,1);
select * from item where id=1;
insert orders (itemid,saleprice,gst,qty,customerid) values (1,78.99,78.99*0.13,10000,1);
select * from item where id=1;
use test;
drop table item;
drop table orders;
show tables
;
use test;
show tables
;
select * from orders;
use test;
show tables
;
create table example{
id INT NOT NULL AUTO_INCREMENT,
name varchar(30),
description varchar(30),
price int,
stock int);
create table example{ id INT NOT NULL AUTO_INCREMENT, name varchar(30), description varchar(30), price int, stock int);
create table example(
id int not null auto_increment,
name varchar(30),
description varchar(30),
price int,
stock int);
create table example( id int not null auto_increment, name varchar(30), description varchar(30), price int, stock int);
create table example( id int not null auto_increment, primary key(id),  name varchar(30), description varchar(30), price int, stock int);
show tables;
insert into example(name, description, price stock) value(‘land’,’hohoho’,3204,999);
insert into example(name, description, price stock) values(‘land’,’hohoho’,3204,999);
insert into example(name, description, price, stock) values(‘land’,’hohoho’,3204,999);
select * from example;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s