创建mysql数据库索引；

mysql
    
创建数据库
CREATE DATABASE mydb;

使用数据库
use mydb;


创建表格
CREATE TABLE students (stuid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(200),age TINYINT UNSIGNED,gender ENUM('F','M'),major VARCHAR(200));


添加键值
 INSERT INTO students (name,age,gender,major) VALUES('jia baoyu',17,'M','pixie jianfa'),('linchong',37,'M','xianglong shiba zhang'),('XImen',31,'M','kuihua baodian'),('linchong',27,'F','wuxiang shengong');


列出表格
 SELECT * FROM students;


添加键值
 for i in {5..1000};do mysql -e "INSERT INTO mydb.students VALUES ('$i','stu$i',$[$RANDOM%100+1],'F','major$i')";done


for i in {1001..2000};do mysql -e "INSERT INTO mydb.students VALUES ('$i','stu$i',$[$RANDOM%100+1],'M','major$i')";done

for i in {2001..3000};do mysql -e "INSERT INTO mydb.students VALUES ('$i','stu$i',$[$RANDOM%100+1],'M','major$i')";done



查看大于等于15年龄的段落

SELECT name,age FROM students WHERE age>= 15;


查询到数据的来源(如在那个表，标的类型)
 EXPLAIN SELECT name,age FROM students WHERE age>= 15;  

 
EXPLAIN SELECT *FROM students  WHERE name = 'stu1002';

 
查询数量   计算学号count(stuid)  
SELECT count(stuid) FROM students;


创建缩影，
CREATE INDEX name ON students(name);
查看缩影
SHOW INDEXES FROM students---生成stuid主键缩影和name缩影

再次查询name=stu1002的内容；

EXPLAIN SELECT *FROM students WHERE name= 'stu1002';



查询包含100的行数有多少；
 EXPLAIN SELECT * FROM students WHERE name LIKE 'stu100%';


创建name和age的缩影
CREATE INDEX name_and_age ON students(name,age);
查看生成的缩影类型；
 EXPLAIN SELECT * FROM students WHERE name LIKE 'STU100%'

单个条件搜索时；
MariaDB [mydb]> EXPLAIN SELECT * FROM students WHERE age >= 50;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL | 3714 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

两个条件搜索时；
MariaDB [mydb]> EXPLAIN SELECT * FROM students WHERE name LIKE 'stu%' AND age >=50;
+------+-------------+----------+------+-------------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys     | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+-------------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | name,name_and_age | NULL | NULL    | NULL | 3714 | Using where |
+------+-------------+----------+------+-------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
CREATE INDEX name_and_age ON students(name,age);


联合查询；判断年龄与序号
 SELECT * FROM students WHERE age >=99 UNION SELECT * FROM  students WHERE  name LIKE ;stu;NION SELECT * FROM  students WHERE  name LIKE 'stu100%';