在MySql中,如果数据表中数据量非常庞大的时候,查询某条记录会非常的浪费时间,鉴于此,MySql提供了一种提高查询效率的工具——索引,有了索引查询速率大大的提高了。
之前有提到MySql提供的函数,其实MySql还可以自定义函数,这样的话就不会局限于之前提供的这几个函数了。
/*************************索引************************************************/
一、管理索引
1、普通索引创建
mysql> create INDEX ind_name ON newInfo(name);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo add index ind_name(name);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
2、唯一索引创建
mysql> create unique index ind_subjectid on newInfo(subjectid);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo add index ind_subjectid(subjectid);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
3、主键索引创建
mysql> create table testInfo(id int, name varchar(20), primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> create table testInfo(id int, name varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> alter table testInfo add PRIMARY KEY(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
二、查看索引
mysql> show index from newInfo;
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| newInfo | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | |
| newInfo | 1 | ind_name | 1 | name | A | NULL | NULL | NULL | YES | BTREE | |
| newInfo | 1 | ind_subjectid | 1 | subjectid | A | NULL | NULL | NULL | YES | BTREE | |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
三、删除索引
mysql> drop index ind_name on newInfo;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> alter table newInfo drop index ind_subjectid;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
/*****************************************************************************/
/*************************自定义函数******************************************/
1、创建自定义函数
mysql> delimiter $$
mysql> create function myfunc(a INT, b INT)
-> RETURNS INT
-> BEGIN
-> return a+b;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> select myfunc(2,3)$$
+-------------+
| myfunc(2,3) |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
2、删除函数
mysql> drop function myfunc;
Query OK, 0 rows affected (0.00 sec)
3、注释
创建函数的语句中含有分号,但我们并没有写完这条语句,mysql默认分号为止为一行语句,可以使用delimiter解决
mysql> delimiter $$//到 "$$" 为止是一条语句
恢复分号
mysql> delimiter ;//到;为止是一条语句
/*****************************************************************************/