表單操作
- 一、表結構操作
- 1.創建表
- 2.查看表信息
- 3.修改表結構
- 二、表記錄操作
- 1.表紀錄之增，刪，改
- 2.表記錄之查
- 三、外鍵約束
- 四、多表查詢
- 準備兩張表
- 笛卡爾積查詢
- 內連接查詢
- 外連接查詢
- 五、索引
- 1.索引簡介
- 2. 索引語法
- 1.創建表時創建索引
- 2.已存在的表創建索引
- 3.刪除索引
- 3.索引測試實驗
一、表結構操作
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.索引簡介
-
什么是索引？
一般的應用系統，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現性能問題，在生產環境中，我們遇到最多的，也是最容易出問題的，還是一些復雜的查詢操作，因此對查詢語句的優化顯然是重中之重。說起加速查詢，就不得不提到索引了。
-
為什么要有索引？
索引在MySQL中也叫做“鍵”，是存儲引擎用于快速找到記錄的一種數據結構。索引對于良好的性能非常關鍵，尤其是當表中的數據量越來越大時，索引對于性能的影響愈發重要。索引優化應該是對查詢性能優化最有效的手段了。索引能夠輕易將查詢性能提高好幾個數量級。
打個比方，如果合理的設計且使用索引的MySQL是一輛蘭博基尼的話，那么沒有設計和使用索引的MySQL就是一個人力三輪車。拿漢語字典的目錄頁（索引）打比方，我們可以按拼音、筆畫、偏旁部首等排序的目錄（索引）快速查找到需要的字。
-
索引分類
索引分單列索引和組合索引（又稱多列索引）。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是組合索引。組合索引，即一個索引包含多個列。 -
索引的應用
創建索引時，你需要確保該索引是應用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)。實際上，索引也是一張表，該表保存了主鍵與索引字段，并指向實體表的記錄。 -
索引的缺點
上面都在說使用索引的好處，但過多的使用索引將會造成濫用。因此索引也會有它的缺點：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。 -
索引的特點
索引特點：創建與維護索引會消耗很多時間與磁盤空間，但查詢速度大大提高！
2. 索引語法
創建索引也有兩種方法：1.在創建表時創建索引；2.對已存在的表創建索引。
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指定升序或降序的索引值存儲
特別注意：主鍵索引是一種特殊的唯一索引，不允許有空值。
- 創建普通索引示例：
CREATE TABLE emp1 (
id INT,
name VARCHAR(30) ,
INDEX index_name (name)
);
- 創建唯一索引示例：
CREATE TABLE emp2 (
id INT,
name VARCHAR(30) ,
UNIQUE INDEX index_name (name)
);
- 創建全文索引示例：
CREATE TABLE emp3 (
id INT,
name VARCHAR(30) ,
resume VARCHAR(50),
FULLTEXT INDEX index_resume (resume)
);
- 創建多列索引示例：
CREATE TABLE emp4 (
id INT,
name VARCHAR(30) ,
INDEX index_id_name (id_name)
);
2.已存在的表創建索引
已存在的表創建索引也有兩種方式：1.alter；2.create
-
create
CREATE在已存在的表上創建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(長度)] [ASC |DESC]) ;
舉例：
create index
index_name
on
emp1(name);
-
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.索引測試實驗
-
創建一個表單
create table t1(id int,name varchar(20)); -
創建一個函數，自動生成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() #調用自定義函數 -
測試創建索引前需要消耗的時間。
select * from t1 where id=160000;
mysql> select * from t1 where id = 160000;
+--------+------+
| id | name |
+--------+------+
| 160000 | yuan |
+--------+------+
1 row in set (0.23 sec)
-
為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
-
測試創建索引后查詢內容需要消耗的時間。
select * from t1 where id=300000; -
刪除普通索引，創建多列索引
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)
-
測試多列索引需要消耗的時間。
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)
從實驗上我們可以發現，創建多列索引的效率會比普通索引分別創建的效率要高，并且消耗資源比較少。
參考資料
數據庫基礎
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
