一、表結構操作

1.創建表

            
                      create table tab_name(
            field1 type[完整性約束條件],
            field2 type,
            ...
            fieldn type
        )[character set xxx];

            
          

創建表頭時，我們要注意：在最后一列時，不需要加逗號，其余的都以逗號進行分隔。

舉例：我們需要創建一個員工表employee

            
                       create table employee(
            id int primary key auto_increment ,
            name varchar(20),
            gender bit default 1,   -- gender char(1)  default 1 
            birthday date,
            entry_date date,
            job varchar(20),
            salary double(4,2) unsigned  -- 注意，這里作為最后一個字段不加逗號 
          );

            
          

常用的約束條件有以下這些，約束條件為可選的條件。

            
                     primary key (非空且唯一)  :能夠唯一區分出當前記錄的字段稱為主鍵！
       unique
       not null
       auto_increment 主鍵字段必須是數字類型。

            
          

2.查看表信息

語法	描述
desc tab_name	查看表結構
show columns from tab_name	查看表結構
show tables	查看當前數據庫中的所有的表
show create table tab_name	查看當前數據庫表建表語句

3.修改表結構

語法	描述
alter table tab_name add [column] 列名 類型 ［完整性約束條件］［first｜after 字段名］;	關鍵字：add ；添加列（字段），可同時添加多個列（字段）
alter table tab_name modify 列名 類型 [完整性約束條件]［first｜after 字段名］;	關鍵字：modify ；修改一列類型
alter table tab_name change [column] 列名 新列名 類型 [完整性約束條件]［first｜after 字段名］;	關鍵字：change ;修改列名
alter table tab_name drop [column] 列名 ;	關鍵字：drop ;刪除一列
rename table 表名 to 新表名 ;	關鍵字：to 修改表名
alter table tab_name add primary key (字段名稱,…)	關鍵字：add primary key ；添加主鍵
drop table tab_name;	刪除表， 需要區分刪除字段

            
              添加主鍵的易錯點：
		1. 主鍵的作用主要確定該數據的唯一性。一個表中只有一個主鍵，當有主鍵時，不能再添加；
		2. eg:
    mysql> create table test5(num int auto_increment);
    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
    create table test(num int primary key auto_increment);
    -- 思考，如何刪除主鍵？這就需要用到下面的兩句
    alter table test modify id int;   -- auto_increment沒了，但這樣寫主鍵依然存在，所以還要加上下面這句
    alter table test drop primary key;-- 僅僅用這句也無法直接刪除主鍵

            
          

除了單字段主鍵，還有多字段主鍵：

            
              單字段主鍵的創建
 create table users(
            id INT primary key,
            name varchar(20),
            city varchar(20)
          );
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| city  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


多字段主鍵的創建：
 create table users2(
            id INT,
            name varchar(20),
            city varchar(20),
            primary key(name,id)
          );
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   | PRI | NULL    |       |
| city  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

            
          

二、表記錄操作

創建一個表單，以下例子都將使用該表單。
CREATE TABLE ExamResult(
id INT PRIMARY KEY auto_increment,
name VARCHAR (20),
Chinese DOUBLE,
Math DOUBLE ,
English DOUBLE
);

表操作的關鍵字

關鍵字	描述
WHERE	子句指定應更新哪些行。如沒有WHERE子句，則更新所有的行。
SET	子句指示要修改哪些列和要給予哪些值
UPDATE	語法可以用新值更新原有表行中的各列

1.表紀錄之增，刪，改

語法	描述
insert ［into］ tab_name (field1,filed2,…) values (value1,value2,…);	關鍵字：insert ；添加一條表單記錄
delete from tab_name [where …]	關鍵字:delete ;delete語句只能刪除表中的內容，不能刪除表本身，想要刪除表，用drop
truncate table tab_name	關鍵字:truncate ;TRUNCATE TABLE也可以刪除表中的所有數據，詞語句首先摧毀表，再新建表。此種方式刪除的數據不能在事務中恢復。
update tab_name set field1=value1,field2=value2,…[where 語句]	關鍵字:update ;修改表記錄

增加一條記錄insert：

            
              
insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......);

	insert into examresult (id,name,Chinese,Math,English) values (1,"小明",80,90,100);
	
	如果需要對每一個都需要賦值時，可以不加字段名，直接寫values。
    insert into Examresult values(2,"曉燕",90,92,96);
    
·	除了對每一個賦值，還可以選擇性的為某個字段賦值。
	insert into Examresult (name,Chinese) value ("Jon",40);

	插入多條記錄
	insert into Examresult value (4,"Jerry",90,71,99),
    (5,"alivin",30,40,66);


		+----+--------+---------+------+---------+
		| id | name   | Chinese | Math | English |
		+----+--------+---------+------+---------+
		|  1 | 小明   |      80 |   90 |     100 |
		|  2 | 曉燕   |      90 |   92 |      96 |
		|  3 | Jon    |      40 | NULL |    NULL |
		|  4 | Jerry  |      90 |   71 |      99 |
		|  5 | alivin |      30 |   40 |      66 |
		+----+--------+---------+------+---------+

            
          

修改表記錄

            
              
	update tab_name set field1=value1,field2=value2,......[where 語句]
	  update examresult set English=40 WHERE id=1;
	修改表記錄時，我們可以添加限制條件--WHERE，WHERE子句指定應更新哪些行。如沒有WHERE子句，則更新所有的行。
	+----+-------+---------+------+---------+
	| id | name  | Chinese | Math | English |
	+----+-------+---------+------+---------+
	|  1 | 小明  |      80 |   90 |      40 |
	|  2 | 曉燕  |      90 |   92 |      96 |
	|  3 | Jon   |      40 | NULL |    NULL |
	|  4 | Jerry |      90 |   71 |      99 |
	|  5 | alivin |      30 |   40 |      66 |
	+----+-------+---------+------+---------+

            
          

刪除表記錄

            
              
delete from tab_name [where ....]

如果不跟where語句則刪除整張表中的數據
            delete只能用來刪除一行記錄
            delete語句只能刪除表中的內容，不能刪除表本身，想要刪除表，用drop
            TRUNCATE TABLE也可以刪除表中的所有數據，詞語句首先摧毀表，再新建表。此種方式刪除的數據不能在事務中恢復。

-- 刪除id為5的記錄
 delete from examresult where id=5;
 
 -- 刪除表中所有記錄。
 delete from employee_new;-- 注意auto_increment沒有被重置:alter table employee auto_increment=1;
auto_increment是用于主鍵自動增長的，從1開始增長，當你把第5條記錄刪除時，再插入一條數據時，主鍵值是6，不是5,主鍵值只會遞增,不會因為刪除了一條記錄,主鍵值就發生改變.

 -- 使用truncate刪除表中記錄。
 truncate table emp_new;

            
          

2.表記錄之查

表記錄查詢之關鍵字	描述
where	用來添加限制條件
distinct	用來剔除重復行
as	取別名作為顯示使用
between (xx and xx)	用來匹配值在某個范圍內
in (xx,xx,xx);	用來匹配某個值的信息.
%	表示任意多字符
_	表示一個字符
Order by	指定排序的列，排序的列即可是表中的列名，也可以是select 語句后指定的別名
Asc	升序,其中asc為默認值
Desc	降序

查詢表達式

            
                 SELECT *|field1,filed2 ...   FROM tab_name
        -- 其中from指定從哪張表篩選，*表示查找所有列，也可以指定一個列
        -- 表明確指定要查找的列，distinct用來剔除重復行。
                  WHERE 條件
                  GROUP BY field
                  HAVING 篩選
                  ORDER BY field
                  LIMIT 限制條數
    查詢表單記錄時,我們可以添加限制條件.

            
          

select子句查詢

            
              1.查看完整的數據表單:
select * from ExamResult;	
2.查詢完整的表單,并顯示總成績
select *,(Chinese+Math+English) as 總成績 from Examresult;
3.過濾表中重復數據。
select distinct * from ExamResult;

4.思考題:查找的列之間沒有用逗號隔開,會發生什么情況?
select name Chinese from ExamResult;

            
          

where子句,進行過濾查詢.

            
              1.查詢學生姓名為曉燕的成績.
select * from ExamResult where name='yuan';	
2.查詢語文分數大于90分(含90分)的學生.
select id,name,Chinese from ExamResult where Chinese>=90;
3.查詢總分在250分以上的學生的所有同學成績.
 select name,Chinese+Math+English as 總成績 from ExamResult where Chinese+Math+English>200 ;
 ------->注意點:where后面跟的子句不能用as的結果來代替,這與執行的優先級相關.
 4.查詢Chinese分數在80-95之間的同學.
 select id,name,Chinese from examresult where Chinese between 80 and 95;
 5.查詢Math分數為90,91,92分的同學.
 select id,name,Math from examresult where Math in (90,91,92);
 6.查詢缺考Math的學生的姓名.
 select name from examresult where Math is null;
 7.查詢所有以J開頭的同學的名字及所有成績.
 select * from examresult where name like "J%";

            
          

order by 進行排序

• Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 語句后指定的別名。
• Asc 升序、Desc 降序，其中asc為默認值 ORDER BY 子句應位于SELECT語句的結尾。

            
                 -- select *|field1,field2... from tab_name order by field [Asc|Desc]
1.對所有的同學以語文成績,從高到低排序
 select name,chinese from examresult order by Chinese;
 2.對所有同學的總成績從高到低排序
select *,(Chinese+Math+English) as 總成績 from examresult order by 總成績 desc;,
如果直接相加時,我們發現任何值與null相加都為null,所以我們遇到null應將值轉化成0.

 改進方法:ifnull()
 select *,(ifnull(Chinese,0)+ifnull(MAth,0)+ifnull(English,0)) as 總成績 from examresult order by 總成績 desc;


            
          

having和where的區別:
having 和 where兩者都可以對查詢結果進行進一步的過濾，差別有：
<1>where語句只能用在分組之前的篩選，having可以用在分組之后的篩選；
<2>使用where語句的地方都可以用having進行替換
<3>having中可以用聚合函數，where中就不行。

聚合函數

聚合函數	描述
COUNT(列名)	統計行的個數,count(字段)不統計null值
SUM(列名)	統計滿足條件的行的內容和,sum僅對數值起作用，否則會報錯
AVG(列名)	統計滿足條件的行的內容的平均數
Max(列名)	求滿足條件的行的最大值
Min(列名)	求滿足條件的行的最小值

Mysql在執行sql語句時的執行順序： from where select group by having order by

分析:
select Chinese as Chinese成績 from ExamResult where Chinese成績 >70; ---- 不成功
select Chinese as Chinese成績 from ExamResult having Chinese成績 >90; — 成功

使用正則表達式查詢
匹配名字以 小 開頭的同學所有成績
SELECT * FROM ExamResult WHERE name REGEXP ‘^小’;

            
              	匹配名字以 燕 結尾的同學所有成績
    SELECT * FROM ExamResult  WHERE name REGEXP '燕$';

    SELECT * FROM ExamResult  WHERE name REGEXP 'J{2}';

            
          

三、外鍵約束

語法	描述
ALTER TABLE 表名 ADD FOREIGN KEY [CONSTRAINT 外鍵名字] (外鍵字段) REFERENCES 父表 (主鍵字段);	如果沒有定義外鍵名,默認為 “表名_ibfk_1”
ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名字;

innodb支持的四種方式	描述
ON DELETE CASCADE	-cascade方式 在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄----外鍵的級聯刪除：如果父表中的記錄被刪除，則子表中對應的記錄自動被刪除
ON DELETE SET NULL	set null方式 在父表上update/delete記錄時，將子表上匹配記錄的列設為null-- 要注意子表的外鍵列不能為not null
ON DELETE Restrict	拒絕對父表進行刪除更新操作(了解)
ON DELETE No action	在mysql中同Restrict,如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作（了解）

創建外鍵
使用MySQL關聯數據表時，創建外鍵的兩種方式：創建表時增加外鍵，已存在表增加外鍵。
方式一:

            
              ---  每一個班主任會對應多個學生 , 而每個學生只能對應一個班主任

----主表

CREATE TABLE ClassCharger(

       id TINYINT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       age INT ,
       is_marriged boolean  -- show create table ClassCharger: tinyint(1)

);

INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",12,0),
                                                       ("丹丹",14,0),
                                                       ("歪歪",22,0),
                                                       ("姍姍",20,0),
                                                       ("小雨",21,0);


----子表

CREATE TABLE Student(

       id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       charger_id TINYINT,     --切記:作為外鍵一定要和關聯主鍵的數據類型保持一致
       -- [ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)

) ENGINE=INNODB;

INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),
                                            ("alvin2",4),
                                            ("alvin3",1),
                                            ("alvin4",3),
                                            ("alvin5",1),
                                            ("alvin6",3),
                                            ("alvin7",2);


DELETE FROM ClassCharger WHERE name="冰冰";
INSERT student (name,charger_id) VALUES ("yuan",1);
-- 刪除居然成功,可是 alvin3顯示還是有班主任id=1的冰冰的;

-----------增加外鍵和刪除外鍵---------

ALTER TABLE student  ADD CONSTRAINT abc
                     FOREIGN KEY(charger_id)
                     REFERENCES  classcharger(id);


ALTER TABLE student DROP FOREIGN KEY abc;

            
          

四、多表查詢

準備兩張表

            
              -- 準備兩張表
-- company.employee
-- company.department

      create table employee(
      emp_id int auto_increment primary key not null,
      emp_name varchar(50),
      age int,
      dept_id int
      );

      insert into employee(emp_name,age,dept_id) values
        ('A',19,200),
        ('B',26,201),
        ('C',30,201),
        ('D',24,202),
        ('E',20,200),
        ('F',38,204);


    create table department(
       dept_id int,
       dept_name varchar(100)
      );

    insert into department values
      (200,'人事部'),
      (201,'技術部'),
      (202,'銷售部'),
      (203,'財政部');

mysql> select * from employee;
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
|      6 | F        |   38 |     204 |
+--------+----------+------+---------+
rows in set (0.00 sec)

mysql> select * from department;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|     200 | 人事部    |
|     201 | 技術部    |
|     202 | 銷售部    |
|     203 | 財政部    |
+---------+-----------+
rows in set (0.01 sec)

            
          

笛卡爾積查詢

語法:SELECT * FROM employee,department;
笛卡爾積查詢的結果為多張表的記錄數的乘積.因為例子中的employee有6條記錄,department有4條記錄,所以笛卡兒積有24條記錄.

            
              +--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      1 | A        |   19 |     200 |     201 | 技術部    |
|      1 | A        |   19 |     200 |     202 | 銷售部    |
|      1 | A        |   19 |     200 |     203 | 財政部    |
|      2 | B        |   26 |     201 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技術部    |
|      2 | B        |   26 |     201 |     202 | 銷售部    |
|      2 | B        |   26 |     201 |     203 | 財政部    |
|      3 | C        |   30 |     201 |     200 | 人事部    |
|      3 | C        |   30 |     201 |     201 | 技術部    |
|      3 | C        |   30 |     201 |     202 | 銷售部    |
|      3 | C        |   30 |     201 |     203 | 財政部    |
|      4 | D        |   24 |     202 |     200 | 人事部    |
|      4 | D        |   24 |     202 |     201 | 技術部    |
|      4 | D        |   24 |     202 |     202 | 銷售部    |
|      4 | D        |   24 |     202 |     203 | 財政部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
|      5 | E        |   20 |     200 |     201 | 技術部    |
|      5 | E        |   20 |     200 |     202 | 銷售部    |
|      5 | E        |   20 |     200 |     203 | 財政部    |
|      6 | F        |   38 |     204 |     200 | 人事部    |
|      6 | F        |   38 |     204 |     201 | 技術部    |
|      6 | F        |   38 |     204 |     202 | 銷售部    |
|      6 | F        |   38 |     204 |     203 | 財政部    |
+--------+----------+------+---------+---------+-----------+

            
          

關鍵字	描述
inner join	內連接
left join	左連接
right join	右連接
full join	全外連接,但是 MySQL中不支持全外連接 ,可通過其他方式間接實現全外連接

內連接查詢

語法: select * from 表單1,表單2 where 表單1.字段名 = 表單2.字段名;
------->等價于: select * from 表單1 inner join 表單2 on 表單1.字段名 = 表單2.字段名;

            
              select * from employee,department where employee.dept_id = department.dept_id;

      +--------+----------+------+---------+---------+-----------+
      | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
      +--------+----------+------+---------+---------+-----------+
      |      1 | A        |   19 |     200 |     200 | 人事部    |
      |      2 | B        |   26 |     201 |     201 | 技術部    |
      |      3 | C        |   30 |     201 |     201 | 技術部    |
      |      4 | D        |   24 |     202 |     202 | 銷售部    |
      |      5 | E        |   20 |     200 |     200 | 人事部    |
      +--------+----------+------+---------+---------+-----------+

            
          

外連接查詢

左外連接 :在內連接得到的匹配結果的基礎上增加 左邊有右邊沒有 的結果.
語法: select * from 表單1 left join 表單2 on 表單1.字段名 = 表單2.字段名;

            
               select * from employee left join department on employee.dept_id = department.dept_id;

    +--------+----------+------+---------+---------+-----------+
    | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
    +--------+----------+------+---------+---------+-----------+
    |      1 | A        |   19 |     200 |     200 | 人事部    |
    |      5 | E        |   20 |     200 |     200 | 人事部    |
    |      2 | B        |   26 |     201 |     201 | 技術部    |
    |      3 | C        |   30 |     201 |     201 | 技術部    |
    |      4 | D        |   24 |     202 |     202 | 銷售部    |
    |      6 | F        |   38 |     204 |    NULL | NULL      |
    +--------+----------+------+---------+---------+-----------+

            
          

右外連接 ：在內連接的基礎上增加 右邊有左邊沒有 的結果
語法: select * from 表單1 right join 表單2 on 表單1.字段名 = 表單2.字段名;

            
               select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;

        +--------+----------+------+---------+---------+-----------+
        | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
        +--------+----------+------+---------+---------+-----------+
        |      1 | A        |   19 |     200 |     200 | 人事部    |
        |      2 | B        |   26 |     201 |     201 | 技術部    |
        |      3 | C        |   30 |     201 |     201 | 技術部    |
        |      4 | D        |   24 |     202 |     202 | 銷售部    |
        |      5 | E        |   20 |     200 |     200 | 人事部    |
        |   NULL | NULL     | NULL |    NULL |     203 | 財政部    |
        +--------+----------+------+---------+---------+-----------+

            
          

全外連接 ：在內連接的基礎上 增加左邊有右邊沒有的和右邊有左邊沒有 的結果
語法:
select * from 表單1 left join 表單2 on 表單1.字段名 = 表單2.字段名;
Union
select * from 表單1 right join 表單2 on 表單1.字段名 = 表單2.字段名;

            
                 select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
   UNION
   select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;

        

        +--------+----------+------+---------+---------+-----------+
        | emp_id | emp_name | age  | dept_id | dept_id | dept_name |
        +--------+----------+------+---------+---------+-----------+
        |      1 | A        |   19 |     200 |     200 | 人事部    |
        |      2 | B        |   26 |     201 |     201 | 技術部    |
        |      3 | C        |   30 |     201 |     201 | 技術部    |
        |      4 | D        |   24 |     202 |     202 | 銷售部    |
        |      5 | E        |   20 |     200 |     200 | 人事部    |
        |   NULL | NULL     | NULL |    NULL |     203 | 財政部    |
        |      6 | F        |   38 |     204 |    NULL | NULL      |
        +--------+----------+------+---------+---------+-----------+

            
          

五、索引

1.索引簡介

1. 什么是索引？

   一般的應用系統，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現性能問題，在生產環境中，我們遇到最多的，也是最容易出問題的，還是一些復雜的查詢操作，因此對查詢語句的優化顯然是重中之重。說起加速查詢，就不得不提到索引了。

2. 為什么要有索引？
   索引在MySQL中也叫做“鍵”，是存儲引擎用于快速找到記錄的一種數據結構。索引對于良好的性能非常關鍵，尤其是當表中的數據量越來越大時，索引對于性能的影響愈發重要。索引優化應該是對查詢性能優化最有效的手段了。索引能夠輕易將查詢性能提高好幾個數量級。
   打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設計和使用索引的MySQL就是一個人力三輪車。

   拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查找到需要的字。

3. 索引分類
   索引分單列索引和組合索引（又稱多列索引）。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。

4. 索引的應用
   創建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄。

5. 索引的缺點
   上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。

6. 索引的特點
   索引特點：創建與維護索引會消耗很多時間與磁盤空間，但查詢速度大大提高！

2. 索引語法

創建索引也有兩種方法：1.在創建表時創建索引；2.對已存在的表創建索引。

1.創建表時創建索引

1. 創建索引語法

            
                  CREATE TABLE 表名 (
                字段名1  數據類型 [完整性約束條件…],
                字段名2  數據類型 [完整性約束條件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(長度)]  [ASC |DESC])
                );

            
          

            
              對語法各選項的說明：
1、unique|fulltext|spatial為可選參數，分別表示唯一索引、全文索引和空間索引；

2、index和key為同義詞，兩者作用相同，用來指定創建索引

3、col_name為需要創建索引的字段列，該列必須從數據表中該定義的多個列中選擇；

4、index_name指定索引的名稱，為可選參數，如果不指定，MYSQL默認col_name為索引值；

5、length為可選參數，表示索引的長度，只有字符串類型的字段才能指定索引長度；

6、asc或desc指定升序或降序的索引值存儲

            
          

特別注意：主鍵索引是一種特殊的唯一索引，不允許有空值。

2. 創建普通索引示例：

            
                  CREATE TABLE emp1 (
        id INT,
        name VARCHAR(30) ,
        INDEX index_name (name)
        );

            
          

3. 創建唯一索引示例：

            
                  CREATE TABLE emp2 (
        id INT,
        name VARCHAR(30) ,
        UNIQUE INDEX index_name (name)
        );

            
          

4. 創建全文索引示例：

            
                  CREATE TABLE emp3 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        FULLTEXT INDEX index_resume (resume)
        );

            
          

5. 創建多列索引示例：

            
                  CREATE TABLE emp4 (
        id INT,
        name VARCHAR(30) ,
        INDEX index_id_name (id_name)
        );


            
          

2.已存在的表創建索引

已存在的表創建索引也有兩種方式：1.alter；2.create

1. create
   CREATE在已存在的表上創建索引

            
                    CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名
              ON 表名 (字段名[(長度)]  [ASC |DESC]) ;

            
          

舉例： create index index_name on emp1(name);

2. alter
   ALTER TABLE在已存在的表上創建索引

            
                    ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                    索引名 (字段名[(長度)]  [ASC |DESC]) ;

            
          

舉例： alter table emp1 add unique index index_name(name);

3.刪除索引

            
              語法：DROP INDEX 索引名 on 表名

            
          

舉例： drop index index_name on t1;

3.索引測試實驗

1. 創建一個表單
   create table t1(id int,name varchar(20));

2. 創建一個函數，自動生成50萬條數據

                   
                         delimiter $$  #修改結束符為$$,避免函數的提示符與MySQL的結束符混亂。
       
       create procedure autoinsert()   #mysql自定義函數以Begin開頭，end結尾
       BEGIN
       declare i int default 1;
       while(i<200000)do
       insert into t1 values(i,'jie');
       set i=i+1;
       end while;
       END$$
       delimiter ;
       
       call autoinsert()  #調用自定義函數
   
                   
                 

3. 測試創建索引前需要消耗的時間。

                   
                     select * from t1 where id=160000;
   
                   
                 

            
              	mysql> select * from t1 where id = 160000;
	+--------+------+
	| id     | name |
	+--------+------+
	| 160000 | yuan |
	+--------+------+
	1 row in set (0.23 sec)

            
          

4. 為id創建索引

                   
                     create index index_id on t1(id);
   create index index_name on t1(name);
   
                   
                 

創建索引也是需要消耗時間的，創建索引需要消耗資源

            
              	mysql> create index index_id on t1(id);
	Query OK, 0 rows affected (1.44 sec)
	Records: 0  Duplicates: 0  Warnings: 0

            
          

5. 測試創建索引后查詢內容需要消耗的時間。

   select * from t1 where id=300000;

6. 刪除普通索引，創建多列索引

                   
                     drop index index_id on t1;
   drop index index_name on t1;
   create index index_id_name on t1(id, name) ;
   
                   
                 

            
              	mysql> select * from t1 where id = 160000;
	+--------+------+
	| id     | name |
	+--------+------+
	| 160000 | yuan |
	+--------+------+
	1 row in set (0.01 sec)

            
          

7. 測試多列索引需要消耗的時間。
   select * from t1 where id=300000;

            
              mysql> create index index_id_name on t1(id, name) ;
Query OK, 0 rows affected (1.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t1 where id = 160000;
+--------+------+
| id     | name |
+--------+------+
| 160000 | yuan |
+--------+------+
1 row in set (0.00 sec)

            
          

從實驗上我們可以發現，創建多列索引的效率會比普通索引分別創建的效率要高，并且消耗資源比較少。

參考資料

數據庫基礎