数据库的常规多表使用

-- 创建一个主键约束的表
-- 一
CREATE TABLE m1(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
)AUTO_INCREMENT=1;
-- 二
-- drop table m3
CREATE TABLE m2(
	id INT AUTO_INCREMENT,
	NAME VARCHAR(20),
	-- 进行指定主键的字段
	PRIMARY KEY(id)
)AUTO_INCREMENT=1;
-- 三
CREATE TABLE m3(
	id INT ,
	NAME VARCHAR(20)
)AUTO_INCREMENT=1;
-- 创建时不指定主键，然后通过语句进行设置添加
ALTER TABLE m3 ADD PRIMARY KEY(id)

-- 使用DDL语句 删除表中的主键
ALTER TABLE m2 DROP PRIMARY KEY
-- 非空约束 字段名 字段类型 not null 
CREATE TABLE m4(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL
)
-- 唯一约束
CREATE TABLE m5(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) UNIQUE
)
-- 测试唯一约束
INSERT INTO m5 VALUES (NULL,'萱萱')
-- 外键约束  字段名 字段类型 default 默认值
CREATE TABLE m6(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) DEFAULT '再见了'
)
-- 测试
INSERT INTO m6 VALUES(NULL,DEFAULT)
INSERT INTO m6 VALUES(NULL,'我爱你')

-- 事务
CREATE TABLE m7(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	money DOUBLE
)
-- 添加两个用户
INSERT INTO m7 (NAME, money) VALUES ('tom', 1000), ('jack', 1000);

-- 事务操作
/*
开启事务，start transaction  begin
提交事务  commit
回滚事务  rollback
*/
BEGIN
UPDATE account SET money = money - 500 WHERE NAME = 'tom';
哈哈哈哈哈
UPDATE account SET money = money + 500 WHERE NAME = 'jack';
ROLLBACK
COMMIT
-- 自动提交事务，查看qutocommit状态
SHOW VARIABLES LIKE 'autocommit';
/*on ：自动提交
off : 手动提交*/
SET @@autocommit=off;
-- 查看隔离命令
SELECT @@tx_isolation
/*设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化
set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串 行 化
例如: 修改隔离级别为 读未提交
set global transaction isolation level read uncommitted*/



-- 多表使用
-- 外键约束 就是一个表的主键 和另一个表的字段产生关系，
-- 那样这个有主键的表就成为，主表，另一个为从表
-- [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
CREATE TABLE department(   -- 主表
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);
-- [CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
DROP TABLE employee
DROP TABLE department 
-- 重新创建 employee表,添加外键约束  从表
CREATE TABLE employee(
  eid INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(20),
  age INT,
  dept_id INT,
-- 添加外键约束
  CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
);

-- 删除 alter table 从表 drop foreign key 外键约束名称
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
--  添加 ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES主表(主 键字段名);
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)



-- 一对多关系
#创建演员表
CREATE TABLE actor(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
#创建角色表
CREATE TABLE role(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
#创建中间表
CREATE TABLE actor_role(
  -- 中间表自己的主键
  id INT PRIMARY KEY AUTO_INCREMENT,
  -- 指向actor 表的外键
  aid INT,
  -- 指向role 表的外键
  rid INT 
);
DROP TABLE actor_role
-- 为中间表的aid字段,添加外键约束 指向演员表的主键
ALTER TABLE actor_role ADD FOREIGN KEY(aid) REFERENCES actor(id);
-- 为中间表的rid字段, 添加外键约束 指向角色表的主键
ALTER TABLE actor_role ADD FOREIGN KEY(rid) REFERENCES role(id);






-- 笛卡尔积
# 分 类 表 ( 一 方 主 表 )
CREATE TABLE category (
	cid VARCHAR(32) PRIMARY KEY ,
	cname VARCHAR(50)
);
#商品表 (多方 从表)
CREATE TABLE products(
	pid VARCHAR(32) PRIMARY KEY ,
	pname VARCHAR(50),
	price INT,
	flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
	category_id VARCHAR(32),
	-- 添加外键约束
	FOREIGN KEY (category_id) REFERENCES category (cid)
);
#分类数据
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','鞋服');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','汽车');
#商品数据
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','小米电视机',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','格力空调',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','美的冰箱',4500,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','篮球鞋',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','运动裤',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','T恤',300,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','冲锋衣',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','神仙水',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','大宝',200,'1','c003');

#查询 格力空调是属于哪一分类下的商品
SELECT p.`pname`AS'商品',c.`cname`AS '种类' FROM products p,category c WHERE p.`category_id`=c.`cid` AND pid='p002'