mysql day05 ---没用

day05_mysql

1. 今日目标

多表查询【重要】

mysql 索引 【理解】

mysql8.0 开窗函数【理解】

mysql 常用函数【了解】

mysql with...as 【了解】

2. 多表查询

2.1. 数据的准备

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),

CONSTRAINT products_fk 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 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','雷

神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK

JONES',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','花花公

子',440,'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');2.2. 多表查询

2.2.1. 语法格式

交叉连接

左外连接

案例

1.交叉连接查询(基本不会使用-得到的是两个表的乘积) [了解]

语法：select * from A,B;

2.内连接查询(使用的关键字 inner join -- inner可以省略)

隐式内连接：select * from A,B where 条件;

显示内连接：select * from A [inner] join B on 条件;

3.外连接查询(使用的关键字 outer join -- outer可以省略)

左外连接：left outer join

select * from A left [outer] join B on 条件;

右外连接：right outer join

select * from A right [outer]join B on 条件;

#1.查询所有分类的商品已经上架

#隐式内连接

select p.pname as 商品名称,c.cname as 商品分类

from category c,products p

where c.cid=p.category_id

and p.flag=1;

#显示内连接

select p.pname as 商品名称,c.cname as 商品分类

from category c [inner]

join products p on c.cid=p.category_id结果集

2.2.2. 子查询

定义 一张select表的结果集可以做为另外select 的一部分(可以是表、条件)。

案例

and p.flag=1;

##2.查询所有分类商品的个数

#左外连接

INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

select c.cid,count(p.category_id)

from day05.category c

left join day05.products p on c.cid=p.category_id

group by c.cid;

# 子查询, 查询“化妆品”分类上架商品详情

select p.pname,p.price,p.flag,p.pid

from day05.products p

where p.category_id =(select cid from day05.category c where c.cname='化妆

品')

and p.flag=1;

# 子查询，查询“化妆品”分类上架商品详情，做为表的一部分

select p.pname,p.price,p.flag,p.pid

from day05.products p

inner join

(select * from day05.category c where c.cname='化妆品') tmp on

p.category_id=tmp.cid

and p.flag=1;

# 子查询, 查询“化妆品”或“家电”分类上架商品详情

select p.pname,p.price,p.flag,p.pid

from day05.products p

where p.category_id in (select cid from day05.category c where c.cname='化妆

品' or

c.cname='家电')

and p.flag=1;3. 索引

定义：索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实

质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

存储和扫描方式

访问数据方式： 顺序访问和索引列访问。

3.1. 索引的分类

3.1.1. 索引的存储方式分类

\1. B-tree 索引

\2. hash (哈希)索引

3.1.2. 索引的逻辑上分类

\1. 普通索引

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。

\2. 唯一索引

在当前列值不能有重复，建立唯一索引，值可以为空

\3. primary key 主键索引

不能为空而且不能重复创建表是 primary key 自动创建主键索引。3.1.3. 索引的操作

\1. 普通索引

\2. 唯一索引

# 直接创建普通索引

CREATE INDEX indexName ON mytable(username([length]));

# 修改表结构来创建普通索引

ALTER table tableName ADD INDEX indexName(columnName)

# 创建表的时候直接创建普通索引

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX indexName(username(length))

);

# 如何查询表中的普通索引

#1、查看表中所有索引

SHOW INDEX FROM table_name;

#2、查看某一表索引，通过元数据表查询到指定表中的所有索引信息

SELECT * FROM mysql.innodb_index_stats a WHERE a.database_name = '数据库

名' and a.table_name like '%表名%';

# 如何删除普通索引

DROP INDEX [indexName] ON mytable;

alter table mytable drop index indexName;

案例：

create index idx_products_categoryid on products(category_id);

show index from products;

drop index idx_products_categoryid on products;

# 方式1-直接创建

CREATE UNIQUE INDEX indexName ON mytable(username(length))

方式2-修改表结构(添加索引)

ALTER table mytable ADD UNIQUE [indexName] (username(length))

方式3-创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);

# 删除索引

DROP INDEX [indexName] ON mytable;

alter table mytable drop index indexName;

案例：

create unique index uidx_category_cname on category(cname);

show index from category;