亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

python全棧開發基礎知識學習——數據庫表操作

系統 1522 0

表單操作

  • 一、表結構操作
    • 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.索引簡介

  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指定升序或降序的索引值存儲

            
          

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

  1. 創建普通索引示例:
            
                  CREATE TABLE emp1 (
        id INT,
        name VARCHAR(30) ,
        INDEX index_name (name)
        );

            
          
  1. 創建唯一索引示例:
            
                  CREATE TABLE emp2 (
        id INT,
        name VARCHAR(30) ,
        UNIQUE INDEX index_name (name)
        );

            
          
  1. 創建全文索引示例:
            
                  CREATE TABLE emp3 (
        id INT,
        name VARCHAR(30) ,
        resume VARCHAR(50),
        FULLTEXT INDEX index_resume (resume)
        );

            
          
  1. 創建多列索引示例:
            
                  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);

  1. 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)

            
          
  1. 為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

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

    select * from t1 where id=300000;

  2. 刪除普通索引,創建多列索引

                    
                      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)

            
          
  1. 測試多列索引需要消耗的時間。
    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元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 欧美亚洲日本国产综合网 | 成人精品一区二区久久久 | 国产久视频 | 日本波多野结衣在线 | 亚洲成人在线视频观看 | 国产精品久久久久久久久免费观看 | 欧美久色 | 国产aav| 免费观看一级特黄欧美大片 | 久久综合99re88久久爱 | 国产综合社区 | jazz欧美人免费xxxxxx | 久久久久国产成人精品亚洲午夜 | 天天爱天天爽 | 一本一道久久 | 亚洲视频在线免费播放 | 牛牛本精品99久久精品 | 奇米影视在线视频8888 | 97超精品视频在线观看 | 五月婷婷在线观看 | 99热在线播放 | 天天摸夜夜添 | 一区二区三区日韩精品 | 精品综合 | 国产一区二区三区乱码网站 | 天天干天天操天天做 | 亚洲国产成a人v在线观看 | 日本大片久久久高清免费看 | 久久综合九色综合欧洲 | 一区二区在线免费观看 | jizzz亚洲美女| 久久久久女人精品毛片九一 | 米奇精品一区二区三区 | 一区二区三区在线免费 | 欧美在线观看视频 | 特级毛片www欧美 | 福利视频自拍 | 在线精品福利 | 国产在线视频自拍 | 天天干天天射天天插 | 国产欧美一区二区三区视频 |