mysql
    
創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE mydb;

使用數(shù)據(jù)庫
use mydb;


創(chuàng)建表格
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;


查詢到數(shù)據(jù)的來源（如在那個(gè)表，標(biāo)的類型）
 EXPLAIN SELECT name,age FROM students WHERE age>= 15;  

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

 
查詢數(shù)量   計(jì)算學(xué)號(hào)count(stuid)  
SELECT count(stuid) FROM students;


創(chuàng)建縮影，
CREATE INDEX name ON students(name);
查看縮影
SHOW INDEXES FROM students---生成stuid主鍵縮影和name縮影

再次查詢name=stu1002的內(nèi)容；

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



查詢包含100的行數(shù)有多少；
 EXPLAIN SELECT * FROM students WHERE name LIKE 'stu100%';


創(chuàng)建name和age的縮影
CREATE INDEX name_and_age ON students(name,age);
查看生成的縮影類型；
 EXPLAIN SELECT * FROM students WHERE name LIKE 'STU100%'

單個(gè)條件搜索時(shí)；
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)

兩個(gè)條件搜索時(shí)；
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);


聯(lián)合查詢；判斷年齡與序號(hào)
 SELECT * FROM students WHERE age >=99 UNION SELECT * FROM  students WHERE  name LIKE ;stu;NION SELECT * FROM  students WHERE  name LIKE 'stu100%';