09数据库复习03

1.索引

-- 创建普通索引
CREATE TABLE test4(
    id TINYINT UNSIGNED,
    username VARCHAR(20),
    INDEX in_id(id),
    KEY in_user(username)
);

-- 创建唯一性索引
CREATE TABLE test5(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    card CHAR(18) NOT NULL,
    UNIQUE KEY uni_card(card)
);

-- 创建全文索引
CREATE TABLE test6(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    userdesc VARCHAR(200) NOT NULL,
    FULLTEXT INDEX full_userDesc(userdesc)
);

-- 创建单列索引
CREATE TABLE test7(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    t1 VARCHAR(20) NOT NULL,
    t2 VARCHAR(20) NOT NULL,
    t3 VARCHAR(20) NOT NULL,
    t4 VARCHAR(20) NOT NULL,
    INDEX in_t1(t1)
);

-- 创建多列索引
CREATE TABLE test8(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    t1 VARCHAR(20) NOT NULL,
    t2 VARCHAR(20) NOT NULL,
    t3 VARCHAR(20) NOT NULL,
    t4 VARCHAR(20) NOT NULL,
    INDEX mul_t1_t2_t3(t1,t2,t3)
);

-- 创建唯一性的多列索引
CREATE TABLE test9(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    t1 VARCHAR(20) NOT NULL,
    t2 VARCHAR(20) NOT NULL,
    t3 VARCHAR(20) NOT NULL,
    t4 VARCHAR(20) NOT NULL,
    UNIQUE KEY  mul_t1_t2_t3(t1,t2,t3)
);

DROP INDEX in_id ON test4;

DROP INDEX in_user ON test4;

-- 在已存在的表上添加索引
CREATE INDEX in_id ON test4(id);

ALTER TABLE test4 ADD INDEX in_username(username);

2.视图

2.1视图的概念

视图又被称为虚拟表，视图（view）是sql的查询结果。

2.2作用

1.权限控制时可以使用:
比如某几个列，允许用户查询，其他列不允许。可以通过视图，开放其中一列或几列，起到权限控制的作用

CREATE VIEW v2 AS SELECT goods.`goods_id`,goods.`goods_name`,goods.`shop_price` FROM goods;

SELECT * FROM v2;

2.简化复杂的查询:

一个查询语句本来就很复杂了，还要在外层继续嵌套。
查询每个栏目下商品的平均价格，并按平均价格排序，查出平均价格前三的商品:

CREATE VIEW v3 AS  
    SELECT cat_id,AVG(shop_price)  AS pj
    FROM goods GROUP BY cat_id;

SELECT * FROM v3 ORDER BY pj DESC LIMIT 0,3;