mysql(2)
-- 省市县
-- 湖南省 对应的市
SELECT * FROM provinces WHERE province = '湖南省';
SELECT * FROM cities WHERE provinceid = 430000;
-- 一个语句查询
SELECT * FROM cities WHERE provinceid = (SELECT provinceid FROM provinces WHERE province = '湖南省')
SELECT * FROM cities c INNER JOIN provinces p ON c.provinceid = p.provinceid HAVING p.province= '湖南省';
-- 通过areas 查询湖南省下的所有市
-- 1. 湖南省的id
SELECT * FROM areas WHERE NAME = '湖南';
-- 2. 在查询pid为湖南省id的数据
SELECT * FROM areas WHERE pid = (SELECT id FROM areas WHERE NAME = '湖南')
-- 查区
SELECT * FROM areas WHERE pid = (SELECT id FROM areas WHERE NAME = '长沙')
-- 连接查询实现
SELECT * FROM areas p INNER JOIN areas c ON p.id = c.pid HAVING p.name = '湖南';
SELECT * FROM areas p INNER JOIN areas c ON p.id = c.pid HAVING p.name = '长沙';
-- 外键 --------------------
-- 表的特殊字段
-- 创建班级表
CREATE TABLE classes(
id INT(4) NOT NULL PRIMARY KEY,
NAME VARCHAR(36)
);
-- 创建外键
ALTER TABLE student ADD CONSTRAINT fk_cid FOREIGN KEY (cid) REFERENCES classes (id);
-- 删除外键约束
ALTER TABLE student DROP FOREIGN KEY fk_cid;
-- 1.创建分类表
CREATE TABLE goods_cates(
id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
NAME VARCHAR(40) NOT NULL
);
-- 2. 给分类表添加分类名称
-- 2.1 商品表cate_name 字段进行分组
SELECT cate_name FROM goods GROUP BY cate_name;
SELECT DISTINCT cate_name FROM goods;
-- 2.2 查询出来的结果 怎么插入到goods_cate表里
-- 注意 此处不是使用的是values 而是目标表的字段名+ 查询结果
INSERT INTO goods_cates (NAME) SELECT cate_name FROM goods GROUP BY cate_name;
SELECT * FROM goods;
-- 3. 需要将goods 表里的cate_name 换成对应的id
UPDATE goods g INNER JOIN goods_cates c ON g.cate_name = c.`name` SET g.cate_name = c.`id`;