Mysql学习笔记 - PorcoBlog

本文最后更新于29 天前，其中的信息可能已经过时，如有错误请留言

本文使用到的数据库的建库代码，附录在本文末尾

1、Mysql视图（view）

创建视图：

create or replace view invoices_with_balance as
select 
	invoice_id, number, client_id, invoice_total, payment_total,
	invoice_total - payment_total as balance,
	invoice_date, due_date, payment_date
from invoices
where (invoice_total - payment_total) > 0
with check option

可更新视图（可以对视图使用insert、update、delete）：

1、不含distinct；

2、不含聚合函数（min、max、sum、avg）

3、不含goupy / having

4、不含union

with check option：如果对于可更新视图使用insert、update、delete，会导致视图里面某些行不符合条件而消失，那么相应的语句会报错：check option failed。用于防止更新语句导致视图里面的某些行消失。例如执行下面的语句将会报错：

update invoices_with_balance
set payment_total = invoice_total
where invoice_id = 3

删除视图：

drop view invoices_with_balance

2、Mysql存储过程（procedure）

存储过程在nuvicat里面存储在函数的位置；

创建存储过程

delimiter $$
create procedure get_invoices_with_balance()
begin
	select *
	from invoices_with_balance
	where balance > 0;
end$$
delimiter ;

调用存储过程

call get_invoices_with_balance()

删除存储过程

drop procedure if exists get_invoices_with_balance

参数

delimiter $$
create procedure get_clients_by_state(state CHAR(2))
begin
	select * from clients c
	where c.state = state;
end $$
delimiter ;

call get_clients_by_state('CA')

带默认值的参数

drop procedure if exists get_clients_by_state;

delimiter $$
create procedure get_clients_by_state(state CHAR(2))
begin
	if state is null then
		set state = 'CA';
	end if;
	select * from clients c
	where c.state = state;
end $$
delimiter ;

call get_clients_by_state(null)

设置无参数输入时，选择所有clients数据

drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state(state CHAR(2))
begin
	if state is null then
		select * from clients;
	else
		select * from clients c
		where c.state = state;
	end if;
end $$
delimiter ;

call get_clients_by_state(null)

上面这种写法比较搓，下面这种写法更简洁、更推荐

drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state(state CHAR(2))
begin
		select * from clients c
		where c.state = ifnull(state, c.state);
end $$
delimiter ;

call get_clients_by_state(null)

参数验证

drop procedure if exists make_payment;
delimiter $$
create procedure make_payment
(
	invoice_id int,
	payment_amount decimal(9, 2),
	payment_date date
)
begin
	if payment_amount <= 0 then
		signal sqlstate '22003'
			set message_text = 'Invalid payment payment_amount';
	end if;

	update invoices i
	set
		i.payment_total = payment_amount,
		i.payment_date = payment_date
	where i.invoice_id = invoice_id;
end$$
delimiter ;

call sql_invoicing.make_payment(2, -100, '2019-01-01')

执行上面的语句，sql会报错：Invalid payment payment_amount

输出参数

drop procedure if exists get_unpaid_invoices_for_client;
delimiter $$
create procedure get_unpaid_invoices_for_client
(
	client_id int,
	out invoices_count int,
	out invoices_total decimal(9, 2)
)
begin
	select count(*), sum(invoice_total)
	into invoices_count, invoices_total
	from invoices i
	where i.client_id = client_id and payment_total = 0;
end$$
delimiter ;

set @invoices_count = 0;
set @invoices_total = 0;
call sql_invoicing.get_unpaid_invoices_for_client(3, @invoices_count, @invoices_total);
select @invoices_count, @invoices_total;

变量

用户变量（user variable）

set @invoices_count = 0;

本地变量（local variable）

drop procedure if exists get_risk_factor;
delimiter $$
create procedure get_risk_factor()
begin
-- risk_factor = invoices_total / invoices_count * 5
	declare risk_factor decimal(9, 2) default 0;
	declare invoices_count int;
	declare invoices_total decimal(9, 2);

	select count(*), sum(invoice_total)
	into invoices_count, invoices_total
	from invoices;

	SET risk_factor = invoices_total / invoices_count * 5;
	select risk_factor;
end$$
delimiter ;
call get_risk_factor()

函数

函数与存储过程相似，区别在于：存储过程可以返回多行多列的结果集，函数只能返回单一值；

函数的属性

deterministic：同样的输入永远会有同样的输出，即输出结果与数据库的数据无关

reads sql data：读取数据

modifies sql data：插入、更新或删除数据

drop function if exists get_risk_factor_for_client;
delimiter $$
CREATE FUNCTION get_risk_factor_for_client(client_id int)
	RETURNS int
reads sql data
BEGIN
-- risk_factor = invoices_total / invoices_count * 5
	declare risk_factor decimal(9, 2) default 0;
	declare invoices_count int;
	declare invoices_total decimal(9, 2);

	select count(*), sum(invoice_total)
	into invoices_count, invoices_total
	from invoices i
	where i.client_id = client_id;

	SET risk_factor = invoices_total / invoices_count * 5;
	RETURN ifnull(risk_factor, 0);
END$$
delimiter ;

select client_id, name, get_risk_factor_for_client(client_id) as risk_factor
from clients

3、触发器（triggers）

创建触发器

当往表中插入数据，或者更新数据时，触发其他表中的数据随之更新的功能。例如下面的demo，当往payments表中插入数据的时候，会同步更新invoices表格。注意：当监控payments表格的更新时，则不能更新payments表中的数据，否则将无限套娃。

drop trigger if exists payments_after_insert;
delimiter $$
create trigger payments_after_insert
	after insert on payments
	for each row
begin
	update invoices
	set payment_total = payment_total + new.amount
	where invoice_id = new.invoice_id;
end $$
delimiter ;

insert into payments
values (default, 5, 3, '2019-01-01', 10, 1)

查看triggers

show triggers like 'payments%'

删除triggers

drop trigger if exists payments_after_insert;

使用triggers做日志记录

create table payments_audit
(
	client_id int not null,
	date DATE not null,
	amount decimal(9, 2) not null,
	action_type varchar(50) not null,
	action_date datetime not null
)

当插入数据时

drop trigger if exists payments_after_insert;
delimiter $$
create trigger payments_after_insert
	after insert on payments
	for each row
begin
	update invoices
	set payment_total = payment_total + new.amount
	where invoice_id = new.invoice_id;
	
	insert into payments_audit
	values (new.client_id, new.date, new.amount, 'Insert', now());
end $$
delimiter ;

当删除数据时

drop trigger if exists payments_after_delete;
delimiter $$
create trigger payments_after_delete
	after delete on payments
	for each row
begin
	update invoices
	set payment_total = payment_total - old.amount
	where invoice_id = old.invoice_id;
	
	insert into payments_audit
	values (old.client_id, old.date, old.amount, 'Delete', now());
end $$
delimiter ;

插入和删除数据

insert into payments
values (default, 5, 3, '2019-01-01', 10, 1)

delete from payments where payment_id IN (9, 10, 11, 12)

payment_audit表格的结果

4、事件（events）

设置事件变量

show variables like 'event%';
set global event_scheduler = ON;

创建事件

drop event if exists yearly_delete_stale_audit_rows;
delimiter $$
create event yearly_delete_stale_audit_rows
on schedule
	-- at '2019-05-01'
	every 1 year starts '2019-01-01' ends '2029-01-01'
do begin
	delete from payments_audit
	where action_date < now() - interval 1 year;
end $$
delimiter ;

启用/关闭事件

alter event yearly_delete_stale_audit_rows disable;
alter event yearly_delete_stale_audit_rows enable;

5、事务（transcations）

事务具有以下属性（ACID）

1、atomicity（原子性）

2、consistency（一致性）：won’t end with orders without items

3、Isolation（隔离性）：不同事务是相互隔离的，如果不同事务更新相同数据行时，将同时只有一个事务能进行操作，其他事务将暂停等待

4、durability（持久性）：事务一旦提交，事务产生的更改是永久性的

创建事务

start transaction;

insert into orders (customer_id, order_date, status)
values (1, '2019-01-01', 1);

insert into order_items
values (last_insert_id(), 1, 1, 1);

commit;

mysql执行单条语句时，会自动启动一个新的事务，并且如果事务没有返回错误，就会将该事务提交，这是mysql的自动提交功能，由变量autocommit控制

show variables like 'autocommit'

并发和锁定（concurrency and locking）

打开两个会话，同时运行下面的代码，第一个会话不要commit，则第二个会话会被默认锁定，除非第一个会话提交，否则第二个会话的任务将阻塞，直至第一个会话的事务提交。

start transaction;
update customers
set points = points + 10
where customer_id = 1;
commit;

并发可能导致的问题：

1、更改丢失（lost updates）

没有使用锁的情况下，后commit的事务覆盖前面commit事务的更新

2、脏读（dirty read）

没有提交的事务，其更新的数据被其他事务读取，mysql有4个隔离级别，其中READ COMMITED可以避免脏读的问题。

3、不可重复读（non-repeating reads）

在同一个事务中，两次读取同一个数据，得到的结果不同（被其他事务更新），隔离级别 REPEATABLE READ可以避免不可重复读的问题。

4、幻读（phantom reads）

事务中选取的记录中缺失某条记录，因为这条记录是事务B在事务A执行期间更新的，隔离级别SERIALIZABLE（序列化）可以避免幻读的问题，序列化隔离级别当其他事务的更新可能影响到当前查询的结果时，当前事务会等待其他事务完成才会返回结果，是应用于事务中的最高隔离级别。

隔离级别

	Lost Updates 更改丢失	Dirty Reads 脏读	Non-repeating Reads 不可重复读	Phantom Reads 幻读
READ UNCOMMITED 读未提交
READ COMMITED 读已提交		✅
REPEATABLE READ 可重复读（默认隔离级别）	✅	✅	✅
SERIALIZABLE 串行化	✅	✅	✅	✅

查看与修改隔离级别

show variables like 'transaction_isolation';
set transaction isolation level read uncommitted;
set session transaction isolation level serializable;
set global transaction isolation level serializable;

1、读未提交

最低的隔离级别，不能解决任何并发问题，会出现脏读现象；如下面的案例，第一段代码中会读取到第二段代码中未提交的points = 20的数据。

set transaction isolation level read uncommitted;
select points from customers where customer_id = 1;

start transaction;
update customers
set points = 20
where customer_id = 1;
commit;
rollback;

2、读已提交

读已提交，可以解决脏读的问题；但是会出现不可重复读的问题，例如在第二段sql中，启动事务，并把points更新为30（points原本为20），这样，第一段sql中，重复读取的两次points数据，第一次是20，第二次是30，重复读结果不一样，也就是出现了不可重复读的并发问题。

set transaction isolation level read committed;
start transaction;
select points from customers where customer_id = 1;
select points from customers where customer_id = 1;
commit;

start transaction;
update customers
set points = 30
where customer_id = 1;
commit;

3、可重复读

可重复读，可以解决脏读、不可重复读的问题（更改丢失，暂时我还没试），但是可重复读不能解决幻读的问题。例如下面的案例，第一个transaction读取Virginia的客户列表，第二个transaction相当于把客户1移动到Virginia，这个时候就算第2个transcation提交，第一个transcation也不会读取到customer_id = 1的这条数据，这也就是幻读。

set transaction isolation level repeatable read;
start transaction;
select * from customers where state = 'VA';
commit;

start transaction;
update customers
set state = 'VA'
where customer_id = 1;
commit;

4、串行化

串行化可以解决幻读的问题。例如下面的案例，如果执行第一段sql，开始一个新的transaction，在另一段sql中，更新一条数据，但是没有commit，此时来执行第一段sql中的select语句，这时候，第一段sql会发生阻塞，等待第二段sql完成。第二段sql提交之后，第一段将把第二段中更新的数据一并查询出来。

set transaction isolation level serializable;
start transaction;
select * from customers where state = 'VA';
commit;

start transaction;
update customers
set state = 'VA'
where customer_id = 1;
commit;

死锁

死锁与隔离级别无关，mysql中如果更新一行数据，mysql会把这行数据锁定，如果有其他事务同时更新这条数据，必须等待当前事务更新完成，如果A事务在更新数据行1、2，而B事务同时在更新数据行2、1，那么A事务更新数据化1锁定数据行1，B事务更新数据行2锁定数据行2，然后A接下来无法更新数据行2，因为被B锁住了，B也无法更新数据行1，因为被A锁住了，这个锁永远无法释放，也就是死锁。mysql会把执行时发生死锁的那个事务回退掉。

start transaction;
update customers set state = 'VA' where customer_id = 1;
update orders set status = 1 where order_id = 1;
commit;

start transaction;
update orders set status = 1 where order_id = 1;
update customers set state = 'VA' where customer_id = 1;
commit;

6、索引

创建索引

explain select customer_id from customers where state = 'CA';

create index idx_state on customers (state);

查看索引

analyze table customers;

show indexes in customers;

索引分类

聚簇索引（Clustered Index）

主键索引，每个表最多一个；

二级索引（Secondary Index）

非主键索引，一个表可以有多个二级索引；

二级索引里面存放的是二级索引对应的列以及主键；

mysql会自动给外键创建二级索引，以便快速连接表；

前缀索引

索引对应的列必须要很小，所以对于CHAR或者VARCHAR，以及TEXT、BLOB格式，可以指定用前20个字符来创建索引，CHAR和VARCHAR是可选的，TEXT和BLOB是必须指定的

create index idx_lastname on customers (last_name(20));

创建数据库的代码

DROP DATABASE IF EXISTS `sql_invoicing`;
CREATE DATABASE `sql_invoicing`; 
USE `sql_invoicing`;

SET NAMES utf8 ;
SET character_set_client = utf8mb4 ;

CREATE TABLE `payment_methods` (
  `payment_method_id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `payment_methods` VALUES (1,'Credit Card');
INSERT INTO `payment_methods` VALUES (2,'Cash');
INSERT INTO `payment_methods` VALUES (3,'PayPal');
INSERT INTO `payment_methods` VALUES (4,'Wire Transfer');

CREATE TABLE `clients` (
  `client_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `clients` VALUES (1,'Vinte','3 Nevada Parkway','Syracuse','NY','315-252-7305');
INSERT INTO `clients` VALUES (2,'Myworks','34267 Glendale Parkway','Huntington','WV','304-659-1170');
INSERT INTO `clients` VALUES (3,'Yadel','096 Pawling Parkway','San Francisco','CA','415-144-6037');
INSERT INTO `clients` VALUES (4,'Kwideo','81674 Westerfield Circle','Waco','TX','254-750-0784');
INSERT INTO `clients` VALUES (5,'Topiclounge','0863 Farmco Road','Portland','OR','971-888-9129');

CREATE TABLE `invoices` (
  `invoice_id` int(11) NOT NULL,
  `number` varchar(50) NOT NULL,
  `client_id` int(11) NOT NULL,
  `invoice_total` decimal(9,2) NOT NULL,
  `payment_total` decimal(9,2) NOT NULL DEFAULT '0.00',
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `payment_date` date DEFAULT NULL,
  PRIMARY KEY (`invoice_id`),
  KEY `FK_client_id` (`client_id`),
  CONSTRAINT `FK_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `invoices` VALUES (1,'91-953-3396',2,101.79,0.00,'2019-03-09','2019-03-29',NULL);
INSERT INTO `invoices` VALUES (2,'03-898-6735',5,175.32,8.18,'2019-06-11','2019-07-01','2019-02-12');
INSERT INTO `invoices` VALUES (3,'20-228-0335',5,147.99,0.00,'2019-07-31','2019-08-20',NULL);
INSERT INTO `invoices` VALUES (4,'56-934-0748',3,152.21,0.00,'2019-03-08','2019-03-28',NULL);
INSERT INTO `invoices` VALUES (5,'87-052-3121',5,169.36,0.00,'2019-07-18','2019-08-07',NULL);
INSERT INTO `invoices` VALUES (6,'75-587-6626',1,157.78,74.55,'2019-01-29','2019-02-18','2019-01-03');
INSERT INTO `invoices` VALUES (7,'68-093-9863',3,133.87,0.00,'2019-09-04','2019-09-24',NULL);
INSERT INTO `invoices` VALUES (8,'78-145-1093',1,189.12,0.00,'2019-05-20','2019-06-09',NULL);
INSERT INTO `invoices` VALUES (9,'77-593-0081',5,172.17,0.00,'2019-07-09','2019-07-29',NULL);
INSERT INTO `invoices` VALUES (10,'48-266-1517',1,159.50,0.00,'2019-06-30','2019-07-20',NULL);
INSERT INTO `invoices` VALUES (11,'20-848-0181',3,126.15,0.03,'2019-01-07','2019-01-27','2019-01-11');
INSERT INTO `invoices` VALUES (13,'41-666-1035',5,135.01,87.44,'2019-06-25','2019-07-15','2019-01-26');
INSERT INTO `invoices` VALUES (15,'55-105-9605',3,167.29,80.31,'2019-11-25','2019-12-15','2019-01-15');
INSERT INTO `invoices` VALUES (16,'10-451-8824',1,162.02,0.00,'2019-03-30','2019-04-19',NULL);
INSERT INTO `invoices` VALUES (17,'33-615-4694',3,126.38,68.10,'2019-07-30','2019-08-19','2019-01-15');
INSERT INTO `invoices` VALUES (18,'52-269-9803',5,180.17,42.77,'2019-05-23','2019-06-12','2019-01-08');
INSERT INTO `invoices` VALUES (19,'83-559-4105',1,134.47,0.00,'2019-11-23','2019-12-13',NULL);

CREATE TABLE `payments` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `amount` decimal(9,2) NOT NULL,
  `payment_method` tinyint(4) NOT NULL,
  PRIMARY KEY (`payment_id`),
  KEY `fk_client_id_idx` (`client_id`),
  KEY `fk_invoice_id_idx` (`invoice_id`),
  KEY `fk_payment_payment_method_idx` (`payment_method`),
  CONSTRAINT `fk_payment_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`invoice_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_payment_method` FOREIGN KEY (`payment_method`) REFERENCES `payment_methods` (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `payments` VALUES (1,5,2,'2019-02-12',8.18,1);
INSERT INTO `payments` VALUES (2,1,6,'2019-01-03',74.55,1);
INSERT INTO `payments` VALUES (3,3,11,'2019-01-11',0.03,1);
INSERT INTO `payments` VALUES (4,5,13,'2019-01-26',87.44,1);
INSERT INTO `payments` VALUES (5,3,15,'2019-01-15',80.31,1);
INSERT INTO `payments` VALUES (6,3,17,'2019-01-15',68.10,1);
INSERT INTO `payments` VALUES (7,5,18,'2019-01-08',32.77,1);
INSERT INTO `payments` VALUES (8,5,18,'2019-01-08',10.00,2);


DROP DATABASE IF EXISTS `sql_store`;
CREATE DATABASE `sql_store`;
USE `sql_store`;

CREATE TABLE `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `quantity_in_stock` int(11) NOT NULL,
  `unit_price` decimal(4,2) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `products` VALUES (1,'Foam Dinner Plate',70,1.21);
INSERT INTO `products` VALUES (2,'Pork - Bacon,back Peameal',49,4.65);
INSERT INTO `products` VALUES (3,'Lettuce - Romaine, Heart',38,3.35);
INSERT INTO `products` VALUES (4,'Brocolinni - Gaylan, Chinese',90,4.53);
INSERT INTO `products` VALUES (5,'Sauce - Ranch Dressing',94,1.63);
INSERT INTO `products` VALUES (6,'Petit Baguette',14,2.39);
INSERT INTO `products` VALUES (7,'Sweet Pea Sprouts',98,3.29);
INSERT INTO `products` VALUES (8,'Island Oasis - Raspberry',26,0.74);
INSERT INTO `products` VALUES (9,'Longan',67,2.26);
INSERT INTO `products` VALUES (10,'Broom - Push',6,1.09);


CREATE TABLE `shippers` (
  `shipper_id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`shipper_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `shippers` VALUES (1,'Hettinger LLC');
INSERT INTO `shippers` VALUES (2,'Schinner-Predovic');
INSERT INTO `shippers` VALUES (3,'Satterfield LLC');
INSERT INTO `shippers` VALUES (4,'Mraz, Renner and Nolan');
INSERT INTO `shippers` VALUES (5,'Waters, Mayert and Prohaska');


CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `birth_date` date DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  `points` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `customers` VALUES (1,'Babara','MacCaffrey','1986-03-28','781-932-9754','0 Sage Terrace','Waltham','MA',2273);
INSERT INTO `customers` VALUES (2,'Ines','Brushfield','1986-04-13','804-427-9456','14187 Commercial Trail','Hampton','VA',947);
INSERT INTO `customers` VALUES (3,'Freddi','Boagey','1985-02-07','719-724-7869','251 Springs Junction','Colorado Springs','CO',2967);
INSERT INTO `customers` VALUES (4,'Ambur','Roseburgh','1974-04-14','407-231-8017','30 Arapahoe Terrace','Orlando','FL',457);
INSERT INTO `customers` VALUES (5,'Clemmie','Betchley','1973-11-07',NULL,'5 Spohn Circle','Arlington','TX',3675);
INSERT INTO `customers` VALUES (6,'Elka','Twiddell','1991-09-04','312-480-8498','7 Manley Drive','Chicago','IL',3073);
INSERT INTO `customers` VALUES (7,'Ilene','Dowson','1964-08-30','615-641-4759','50 Lillian Crossing','Nashville','TN',1672);
INSERT INTO `customers` VALUES (8,'Thacher','Naseby','1993-07-17','941-527-3977','538 Mosinee Center','Sarasota','FL',205);
INSERT INTO `customers` VALUES (9,'Romola','Rumgay','1992-05-23','559-181-3744','3520 Ohio Trail','Visalia','CA',1486);
INSERT INTO `customers` VALUES (10,'Levy','Mynett','1969-10-13','404-246-3370','68 Lawn Avenue','Atlanta','GA',796);


CREATE TABLE `order_statuses` (
  `order_status_id` tinyint(4) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`order_status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `order_statuses` VALUES (1,'Processed');
INSERT INTO `order_statuses` VALUES (2,'Shipped');
INSERT INTO `order_statuses` VALUES (3,'Delivered');


CREATE TABLE `orders` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `comments` varchar(2000) DEFAULT NULL,
  `shipped_date` date DEFAULT NULL,
  `shipper_id` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`order_id`),
  KEY `fk_orders_customers_idx` (`customer_id`),
  KEY `fk_orders_shippers_idx` (`shipper_id`),
  KEY `fk_orders_order_statuses_idx` (`status`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_order_statuses` FOREIGN KEY (`status`) REFERENCES `order_statuses` (`order_status_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_shippers` FOREIGN KEY (`shipper_id`) REFERENCES `shippers` (`shipper_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `orders` VALUES (1,6,'2019-01-30',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (2,7,'2018-08-02',2,NULL,'2018-08-03',4);
INSERT INTO `orders` VALUES (3,8,'2017-12-01',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (4,2,'2017-01-22',1,NULL,NULL,NULL);
INSERT INTO `orders` VALUES (5,5,'2017-08-25',2,'','2017-08-26',3);
INSERT INTO `orders` VALUES (6,10,'2018-11-18',1,'Aliquam erat volutpat. In congue.',NULL,NULL);
INSERT INTO `orders` VALUES (7,2,'2018-09-22',2,NULL,'2018-09-23',4);
INSERT INTO `orders` VALUES (8,5,'2018-06-08',1,'Mauris enim leo, rhoncus sed, vestibulum sit amet, cursus id, turpis.',NULL,NULL);
INSERT INTO `orders` VALUES (9,10,'2017-07-05',2,'Nulla mollis molestie lorem. Quisque ut erat.','2017-07-06',1);
INSERT INTO `orders` VALUES (10,6,'2018-04-22',2,NULL,'2018-04-23',2);


CREATE TABLE `order_items` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(4,2) NOT NULL,
  PRIMARY KEY (`order_id`,`product_id`),
  KEY `fk_order_items_products_idx` (`product_id`),
  CONSTRAINT `fk_order_items_orders` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_order_items_products` FOREIGN KEY (`product_id`) REFERENCES `products` (`product_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `order_items` VALUES (1,4,4,3.74);
INSERT INTO `order_items` VALUES (2,1,2,9.10);
INSERT INTO `order_items` VALUES (2,4,4,1.66);
INSERT INTO `order_items` VALUES (2,6,2,2.94);
INSERT INTO `order_items` VALUES (3,3,10,9.12);
INSERT INTO `order_items` VALUES (4,3,7,6.99);
INSERT INTO `order_items` VALUES (4,10,7,6.40);
INSERT INTO `order_items` VALUES (5,2,3,9.89);
INSERT INTO `order_items` VALUES (6,1,4,8.65);
INSERT INTO `order_items` VALUES (6,2,4,3.28);
INSERT INTO `order_items` VALUES (6,3,4,7.46);
INSERT INTO `order_items` VALUES (6,5,1,3.45);
INSERT INTO `order_items` VALUES (7,3,7,9.17);
INSERT INTO `order_items` VALUES (8,5,2,6.94);
INSERT INTO `order_items` VALUES (8,8,2,8.59);
INSERT INTO `order_items` VALUES (9,6,5,7.28);
INSERT INTO `order_items` VALUES (10,1,10,6.01);
INSERT INTO `order_items` VALUES (10,9,9,4.28);

CREATE TABLE `sql_store`.`order_item_notes` (
  `note_id` INT NOT NULL,
  `order_Id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `note` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`note_id`));

INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES ('1', '1', '2', 'first note');
INSERT INTO `order_item_notes` (`note_id`, `order_Id`, `product_id`, `note`) VALUES ('2', '1', '2', 'second note');


DROP DATABASE IF EXISTS `sql_hr`;
CREATE DATABASE `sql_hr`;
USE `sql_hr`;


CREATE TABLE `offices` (
  `office_id` int(11) NOT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) NOT NULL,
  PRIMARY KEY (`office_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `offices` VALUES (1,'03 Reinke Trail','Cincinnati','OH');
INSERT INTO `offices` VALUES (2,'5507 Becker Terrace','New York City','NY');
INSERT INTO `offices` VALUES (3,'54 Northland Court','Richmond','VA');
INSERT INTO `offices` VALUES (4,'08 South Crossing','Cincinnati','OH');
INSERT INTO `offices` VALUES (5,'553 Maple Drive','Minneapolis','MN');
INSERT INTO `offices` VALUES (6,'23 North Plaza','Aurora','CO');
INSERT INTO `offices` VALUES (7,'9658 Wayridge Court','Boise','ID');
INSERT INTO `offices` VALUES (8,'9 Grayhawk Trail','New York City','NY');
INSERT INTO `offices` VALUES (9,'16862 Westend Hill','Knoxville','TN');
INSERT INTO `offices` VALUES (10,'4 Bluestem Parkway','Savannah','GA');



CREATE TABLE `employees` (
  `employee_id` int(11) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `job_title` varchar(50) NOT NULL,
  `salary` int(11) NOT NULL,
  `reports_to` int(11) DEFAULT NULL,
  `office_id` int(11) NOT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `fk_employees_offices_idx` (`office_id`),
  KEY `fk_employees_employees_idx` (`reports_to`),
  CONSTRAINT `fk_employees_managers` FOREIGN KEY (`reports_to`) REFERENCES `employees` (`employee_id`),
  CONSTRAINT `fk_employees_offices` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `employees` VALUES (37270,'Yovonnda','Magrannell','Executive Secretary',63996,NULL,10);
INSERT INTO `employees` VALUES (33391,'D\'arcy','Nortunen','Account Executive',62871,37270,1);
INSERT INTO `employees` VALUES (37851,'Sayer','Matterson','Statistician III',98926,37270,1);
INSERT INTO `employees` VALUES (40448,'Mindy','Crissil','Staff Scientist',94860,37270,1);
INSERT INTO `employees` VALUES (56274,'Keriann','Alloisi','VP Marketing',110150,37270,1);
INSERT INTO `employees` VALUES (63196,'Alaster','Scutchin','Assistant Professor',32179,37270,2);
INSERT INTO `employees` VALUES (67009,'North','de Clerc','VP Product Management',114257,37270,2);
INSERT INTO `employees` VALUES (67370,'Elladine','Rising','Social Worker',96767,37270,2);
INSERT INTO `employees` VALUES (68249,'Nisse','Voysey','Financial Advisor',52832,37270,2);
INSERT INTO `employees` VALUES (72540,'Guthrey','Iacopetti','Office Assistant I',117690,37270,3);
INSERT INTO `employees` VALUES (72913,'Kass','Hefferan','Computer Systems Analyst IV',96401,37270,3);
INSERT INTO `employees` VALUES (75900,'Virge','Goodrum','Information Systems Manager',54578,37270,3);
INSERT INTO `employees` VALUES (76196,'Mirilla','Janowski','Cost Accountant',119241,37270,3);
INSERT INTO `employees` VALUES (80529,'Lynde','Aronson','Junior Executive',77182,37270,4);
INSERT INTO `employees` VALUES (80679,'Mildrid','Sokale','Geologist II',67987,37270,4);
INSERT INTO `employees` VALUES (84791,'Hazel','Tarbert','General Manager',93760,37270,4);
INSERT INTO `employees` VALUES (95213,'Cole','Kesterton','Pharmacist',86119,37270,4);
INSERT INTO `employees` VALUES (96513,'Theresa','Binney','Food Chemist',47354,37270,5);
INSERT INTO `employees` VALUES (98374,'Estrellita','Daleman','Staff Accountant IV',70187,37270,5);
INSERT INTO `employees` VALUES (115357,'Ivy','Fearey','Structural Engineer',92710,37270,5);


DROP DATABASE IF EXISTS `sql_inventory`;
CREATE DATABASE `sql_inventory`;
USE `sql_inventory`;


CREATE TABLE `products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `quantity_in_stock` int(11) NOT NULL,
  `unit_price` decimal(4,2) NOT NULL,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `products` VALUES (1,'Foam Dinner Plate',70,1.21);
INSERT INTO `products` VALUES (2,'Pork - Bacon,back Peameal',49,4.65);
INSERT INTO `products` VALUES (3,'Lettuce - Romaine, Heart',38,3.35);
INSERT INTO `products` VALUES (4,'Brocolinni - Gaylan, Chinese',90,4.53);
INSERT INTO `products` VALUES (5,'Sauce - Ranch Dressing',94,1.63);
INSERT INTO `products` VALUES (6,'Petit Baguette',14,2.39);
INSERT INTO `products` VALUES (7,'Sweet Pea Sprouts',98,3.29);
INSERT INTO `products` VALUES (8,'Island Oasis - Raspberry',26,0.74);
INSERT INTO `products` VALUES (9,'Longan',67,2.26);
INSERT INTO `products` VALUES (10,'Broom - Push',6,1.09);