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`;