MySQL索引及视图习题练习

一、索引

通过观察有无索引查询的速度来了解索引

SQL语句

-- Active: 1714203732007@@127.0.0.1@3306@db_index
--1、创建测试表
create table test1(
	id int,num int,pass varchar(50)
);
create table test2(
    id int,num int,pass varchar(50),
    index idIdx (id)
);

create table test3(
	id int,num int,pass varchar(50)
);
--2、向表test1里插入300000条数据
    CREATE PROCEDURE InsertTestData()  
    BEGIN  
        DECLARE v_counter INT DEFAULT 1;  
    
        WHILE v_counter <= 300000 DO  
            INSERT INTO test1 (id, num, pass) VALUES (v_counter, FLOOR(RAND() * 1000000), CONCAT('password', v_counter));  
            SET v_counter = v_counter + 1;  
        END WHILE;  
    END 
    -- 调用存储过程来插入数据  
    CALL InsertTestData();

--为test1添加索引
ALTER TABLE test1 ADD PRIMARY KEY (id);


-- 3、在有索引和没有索引的情况下执行查询
-- 1）没有创建索引时查询
-- 2）创建索引后再次查询
-- mysql>explain select * from test1 where id>=1 and id<=10000;



-- 4、在有索引和没有索引的情况下新增数据
-- 1）没有创建索引时插入数据
-- 	mysql> insert into test2 select * from test1;
	
--   2）创建索引后再次插入数据
-- 	mysql> insert into test3 select * from test1;

测试截图

1，2.创建表后插入数据

没有创建索引时查询

创建索引后查询

对比两表后发现创建索引后，使用了优化查询。

二、索引与视图练习

SQL语句

-- Active: 1714203732007@@127.0.0.1@3306@db_stu_2
-- 学生表：Student (Sno, Sname, Ssex , Sage, Sdept)
-- 学号，姓名，性别，年龄，所在系 Sno为主键
-- 课程表：Course (Cno, Cname,)
-- 课程号，课程名 Cno为主键
-- 学生选课表：SC (Sno, Cno, Score)
-- 学号，课程号，成绩 Sno,Cno为主键

-- 1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是 “计算机”。
CREATE TABLE Student (  
    Sno INT PRIMARY KEY,  
    Sname VARCHAR(50) UNIQUE NOT NULL,  
    Ssex CHAR(1) CHECK (Ssex IN ('男', '女')),  
    Sage SMALLINT,  
    Sdept VARCHAR(50) DEFAULT '计算机'  
);
CREATE TABLE Course (  
    Cno INT PRIMARY KEY,  
    Cname VARCHAR(100) NOT NULL  
);
CREATE TABLE SC (  
    Sno INT,  
    Cno INT,  
    Score DECIMAL(5,2), -- 假设成绩为带两位小数的十进制数  
    PRIMARY KEY (Sno, Cno),  
    FOREIGN KEY (Sno) REFERENCES Student(Sno),  
    FOREIGN KEY (Cno) REFERENCES Course(Cno)  
);
-- 2.修改student 表中年龄（age）字段属性，数据类型由int 改变为smallint。
ALTER TABLE Student MODIFY COLUMN Sage SMALLINT;
-- 3.为SC表建立按学号（sno）和课程号（cno）组合的升序的主键索引，索引名为SC_INDEX 。
CREATE INDEX SC_INDEX ON SC (Sno ASC, Cno ASC);
-- 4.创建一视图 stu_info,查询全体学生的姓名，性别，课程名，成绩。
CREATE VIEW stu_info AS  
SELECT   
    S.Sname,   
    S.Ssex,   
    C.Cname,   
    SC.Score  
FROM   
    Student S  
JOIN   
    SC ON S.Sno = SC.Sno  
JOIN   
    Course C ON SC.Cno = C.Cno;

测试截图

创建student表

创建course表

创建sc表

查看视图