Mysql知识汇总
1.基本操作知识:
-
用root用户登录:mysql -u root -p;
-
创建数据库:create database bank(库名);
-
退出:quit;
-
创建用户gp,并赋予bank数据库权限:grant all privileges on bank.* to ‘gp’@‘localhost’ identified by ‘xyz’;(xyz为密码)
-
查询当前日期:select now();
-
查看服务器所支持的字符集:show character set;
2.操作与修改表:
-
创建表:create table 表名(变量名 数据类型 【其他设定】);
-
查看表:desc 表名;
-
插入数据:insert into 表名 (变量名) values (对应的值);
-
查看表的数据:select *(变量名)from 表名 条件;
-
排序检索数据:
-
过滤数据:(where语句)
-
使用between检测时,注意:select * from account where price between 5 and 10;
-
使用空值时,注意:select * from account where price is null;
-
and操作符和or操作符连接where (例如:select * from account where price = 5 and account_id = 10;);同时注意and操作符的优先级更高;
-
in操作符,说明:select * from account where price in (5, 10);作用与or相同。
-
not 操作符,例如和in联合使用:select * from account where price not in (5,10);
-
-
通配符过滤数据:
1. like 操作符:%表示任何字符出现的任意次数(例子:select id from test where id like ‘1%’; 此例表示搜索任何以1开头的词);
-
用正则表达式进行搜索:
-
REGEXP字符匹配:select id from test where id REGEXP ‘.000’; ’ . '表示匹配任何一个字符,与文字正文1000或2000等匹配的正则表达式;
-
OR进行匹配:| 为正则表达式的OR操作符;select id from test where id EXGEXP ‘1000 | 2000’;
-
另一种or语句,匹配一组字符:用 [ ] 将想要包含的字符括起来;select id from test where id EXGEXP ‘[ 123 ]’ ;表示匹配1或2或3;[^123]则表示匹配除了123以外的任何东西;
-
匹配范围:[1-9]表示匹配数字1到9;
-
匹配特殊字符,必须用 \ \ 为前导,例如 \ \ -表示查找 - ,\ \ . 表示查找 . 。
-
-
匹配字符类:
8. 匹配多个实例:
例子:[ [ : digit ] ] { 4 } 匹配连在一起的任意4个数字;又如:\ \ ([0 - 9 ] sticks ?\ \ ) 其中的 \ \ (匹配 )[ 0 - 9 ] 匹配任意数字,sticks?匹配stick和sticks;
3.创建计算字段:
3.1概念
计算字段不是从数据库中直接提取出来的数据,而是将从数据库中检索出来的数据进行转换,计算或者格式化而形成的数据。
3.2拼接字段
在Mysql的select语句中,可使用Concat()函数进行拼接两个列。例如:select Concat( RTrim(vend_name), ’ (’ , RTrim(vend_country), ‘)’ ) 其中的RTrim()函数去掉右边的所有空格,还有LTrim(),Trim。
上面的例子表示的是将vend_name所包含的数据与用()连接的vend_country进行拼接;
3.3使用别名
使用AS关键字,例如:select pcp as name from 表,则将查找出来的pcp数据以name作为标题显示出来;
3.4执行算术计算
支持 + ,- , * , / 四则运算。例如:select pid , price , price * pid as Allprice from 表名;
4.使用数据处理函数:
4.1文本处理函数
Upper()将文本转换为大写,例如:select name , Upper(usernaem) as LBJ from 表名;
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法(例子:select cust_name from customers where Soundex(cust_contact) = Soundex(‘Y Lie’);
4.2 日期和时间处理函数
4.3 数值处理函数
5.汇总数据:
5.1聚集函数
- AVG()函数:select AVG(prod_price) AS avg_price from products;求出包含在products中的所有产品的平均价格;
- COUNT()函数:select COUNT(*) AS num_cust from customers;表示从customers表中取出num_cust的所有列的总和,无论是否为NULL;
- 使用COUNT(参数)则会将值为NULL的省略掉不加;
- MAX(),MIN()以及SUM()这三个函数的用法和前面就基本一样;
5.2聚集不同值
可以使用DISTINCT于COUNT()上,但不可用于COUNT(*),使用DISTINCT时后面必须带参数;
5.3组合聚集函数
select语句中可以使用多个聚集函数;
6 分组数据:
6.1 创建分组
分组是在SELECT语句的GROUP BY字句中建立的。
select pid , COUNT(*) AS num_prods from products GROUP BY vend_id; 该语句指示了按vend_id为计算字段,对不同的vend_id分别进行计算,实现多组分行;
注意:GROUP BY字句必须出现在WHERE字句后面,ORDER BY字句之前;
6.2 过滤分组
使用HAVING字句对组的数据进行过滤;WHERE用于过滤行;
例子:select cust_id , COUNT() AS orders FROM orders GROUP BY cust_id HAVING COUNT( * ) >= 2;
该例子过滤掉了COUNT(* )>=2 的那些分组;
6.3 分组和排序
ORDER BY 排序产生的输出,GROUP BY分组行,输出可能不是分组的顺序(不排序,仅分组);
例子:SELECT order_num , SUM(quality * price) AS ordertotal from orderitems GROUP BY order_num HAVING SUM(quality * price)>= 50 ORDER BY ordertotal;
该例子中,GROUP BY字句按照订单号分组数据,以便SUM(*)函数算出总计订单价格,HAVING字句过滤数据,使得只返回总订单价格大于等于50的订单,最后用ORDER BY字句排序出来。
7.使用子查询:
7.1利用子查询进行过滤
例子:SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = ’TNT');
首先执行括号里面的SELECT语句,即为子查询语句,查询完后将值代入IN()函数中进行另一个查询;
7.2作为计算字段使用子查询
例子:SELECT cust_name, cust_state, (SELECT COUNT(*)FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;
注意到WHERE子句中使用了完全限定的方法,将子查询放在了外部,成为相关子查询。
8.联结表:
8.1 关系表
相同的数据出现多次并不好,关系表的设计就是保证把信息分解成多个表,一类数据一个表,各表通过某些常用的值互相联系;
外键:为某一个表中的一列,它包含另一个表的主键值。1表的主键即使2表的外键。
8.2 创建联结
例子:SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name,prod_name;
vend_name属于其中一张表,而prod_name,prod_price属于另一张表,所以在使用WHERE过滤语句时需要使用完全限定的方法。 即通过WHERE字句进行创建联结;
8.3 内部联结
之前的联结都为等值联结,它基于两个表间的相等测试,这种联结也称为内部联结,可以使用下面的方法实现和之前一样的作用:
SELECT vend_name ,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
此处用INNER JOIN,则后面需要用ON进行联结,不能用WHERE。
8.4 联结多个表
例子:SELECT prod_name , vend_name,prod_price,quantity FROM orderitems,products,vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prof_id = products.prod_id AND order_num = 20005;
使用多表联动查询比多个子查询的结合效率要高。
9.创建高级联动:
9.1使用表别名
可以为表起别名,方便使用,例如:SELECT cust_contact,cust_name FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id = o.cust_id AND…;
9.2 使用不同类型的联动
9.2.1 自联动
例子:SELECT p1.prod_id , p1.prod_name FROM products AS p1, products AS p2 WHERE p1.prod_id = p2.prod_id AND p2.prod_id = ‘DTNTR’;
9.2.2 自然联动(返回所有的数据,甚至相同多次出现的数据,用自然联结可排除多次出现)
例子:SELECT c.* ,order_num FROM customers AS c, orders AS o WHERE c.cust_id = o.cust_id…;
9.2.3 外部联结(联结包含了那些在相关表中没有关联行的行)
例子:SELECT customer.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
在使用OUTER JOIN语法时,必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表,LEFT则为左边的表);
9.3 使用带聚集函数的联动
例子:SELECT customers.cust_name ,COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id…;
10.组合查询
10.1创建组合查询
使用UNION可给出多条SELECT语句;例子:SELECT vend_id , prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id , prod_price FROM products WHERE vend_id IN (1001,1002);
给出每条SELECT语句,然后用UNION进行组合连接,并把结果输出组合成单个查询结果集;
10.2 包含或取消重复行
UNION从查询结果中自动去除了重复的行,这是UNION的默认属性,如需要改变,可以使用UNION ALL返回所有匹配行;
10.3 对组合查询结果进行排序
UNION组合查询是,只使用一条ORDER BY字句,其必须出现在SELECT语句的最后面,不允许使用多条ORDER BY字句;
例如:SELECT id, price from products WHERE price <= 5 UNION SELECT prod_id from products WHERE vend_id IN (1001,1002) order BY vend_id,price;
11.全文本搜索
11.1 概念
在使用全文本搜索时,Mysql不需要分别查询每个行,不需要分析和处理每个词,Mysql创建指定列中的各词的一个索引,搜索可以针对这些词进行。
11.2使用全文本搜索
在索引后,SELECT 可与Match()和Against()一起使用以实际执行搜索
11.2.1 启用全文本搜索支持
一般在创建表时启用全文本搜索。CREATE TABLE 语句接受FULLTEXT字句;
例子:CREATE TABLE products
(note_id int NOT NULL ,
prod_id char(10) NOT NULL,
FULLTEXT(note_id)
)ENGINE=MyISAM;
Mysql对note_id进行索引创建,在增加,更新或删除行时,索引会随之自动更新;
11.2.2 进行全文本搜索
索引后,使用Match()和Against()执行全文本搜索,其中Match()指定被搜索的咧,Against()指定要使用的搜索表达式;
例子:SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘rabbit’);
搜索指定的note_text这一列,然后指定词rabit作为搜索文本。全文本搜索还对结果进行了一个排序的作用;具有较高等级的行先返回。
11.2.3 查询拓展搜索
例子:SELECT note FROM product WHERE Match(note_text)Against(‘anvils’ WHITH QUERY EXPANSION)
不仅返回含有’anvils‘的行,还会返回其他有相关性词的行等;
11.2.4 布尔文本搜索
没有FULLTEXT索引也可以使用,不过操作缓慢;
例子:ELECT note FROM product WHERE Match(note_text)Against(‘anvils’ IN BOOLEAN MODE);
该语句没有指定布尔操作符,所以结果和不使用的一样;
例子:SELECT note_text FROM productnote WHERE Match(note_text) Against(’+rabbit+bait’ IN BOOLEAN MODE);
该语句匹配包含词rabbit,bait的行;
12.插入数据
12.1 插入完整的行
INSERT INTO Customers VALUES (NULL,‘Pepper’…);
也可以指定列名进行插入,例如:INSERT INTO customers(cust_name, cust_email)VALUES (‘PEP’,‘USA’);
·
12.2 插入多个行
例子:INSERT INTO customers(cust_name, cust_email)VALUES (‘PEP’,‘USA’),(‘KCP’,‘CHINA’);
12.3 插入检索出的数据
例子:INSERT INTO customers(cust_name, cust_email)SELECT 变量名 from 表名;
13. 更新和删除数据
13.1 更新数据
UPDATE可以更新表中的行或列的数据;
例子:UPDATE 表名 SET 变量名 = 值 ,变量名 = 值 WHERE…;
IGNORE关键字:更新时发生错误,也继续更新,例子:UPDATE IGNORE 表名…;
13.2 删除数据
例子:DELETE FROM 表名 WHERE 变量名 = 值;
使用TRUNCATE TABLE语句删除整个表的数据,其原理是删除整个表并重新建立一个新的表;
14.创建和操作表
14.1创建表的基础
例子:CREATE TABLE 表名(变量 类型 其他指令,变量… , PRIMARY KEY(变量名))ENGINE = InnoDB;
PRIMARY KEY(变量名)创建了该表的主键(指定列的唯一性);可以有多个主键;
NOT NULL将会阻止插入没有值得列;
AUTO_INCREMENT 可以使得 每当增加一行时自动增量,可以用于主键值,每个表只允许一个AUTO_INCREMENT列,而且必须被索引(通过它成为主键);
SELECT last_insert_id()函数可以获得AUTO_INCREMENT列的最后一个值;
指定默认值:插入行时没哟给有给出指定值,使用DEFAULT关键词(例如:变量 类型 DEFAULT 值);
14.2 引擎类型
例子:ENGINE = InnoDB;
InnoDB是一个可靠的事务处理引擎,不支持全文本搜索;
MEMORY数据存储在内存,而不是磁盘,速度快(适合临时表);
MyISAM支持全文本搜索,不支持事务处理;(默认引擎)
14.3 更改表
ALTER TABLE 表名 ADD 变量名 类型;(增加表列)
ALTER TABLE 表名 DROP COLUMN 变量名;(删除表列)
ALTER TABLE 定义外键,例子:ALTER TABLE 表名 ADD CONSTRAINT 变量名 FOREIGN KEY (变量名1)REFERENCES 表名(变量名1);
14.4 删除表
DROP TABLE 表名;
14.5 重命名表
例子:RENAME TABLE 新表名 TO 原表名;
15.视图
15.1概念
视图是虚拟的表,只包含使用时动态检索数据的查询,用来查询存储在别处的数据;
15.2 视图基本操作
CREATE VIEW(创建视图);
SHOW CREATE VIEW 视图名;(查看试图)
DROP VIEW 视图名(删除视图);
CREATE OR REPLACE VIEW(更新试图);
例子:CREATE VIEW 视图名 AS SELECT 变量名,变量名… FROM 表名,表名…WHERE 条件;
视图同表一样,可以过滤数据等等;
16.使用存储过程
16.1使用存储过程
CALL productpricing(@变量名,@变量名… );(使用存储过程)
CREATE PROCEDURE 存储过程名(
OUT 变量名 数据类型(OUT表示从存储过程传出),
IN 变量名 数据类型 (IN 传递给存储过程),
INOUT 变量名 数据类型 (传入和传出),
)
BEGIN
SELECT Avg(price) AS priceaverage
FROM products;
END;(创建存储过程)
DROP PROCEDURE 存储过程名(删除存储过程);
检索出存储的数据。使用SELECT @变量名;
16.2 检查存储过程
SHOW CREATE PROCEDURE ordertotal;获取存储过程列表的详细信息;
17.游标
17.1创建游标
例子:CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
其中的DECLARE语句用于定义和命名游标;、
17.2 打开和关闭游标
OPEN CURSOR 打开游标;例子:OPEN ordernumber;
关闭游标:CLOSE ordernumber;
17.3 使用游标数据(FETCH)
例子:CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT;
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
OPEN ordernumbers;
FETCH ordernumber INTO o;
CLOSE ordernumbers;
END;
18.使用触发器
只有DELETE,INSERT,UPDATE这三个语句才能使用触发器;
18.1创建触发器
例子:CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH SELECT 'Product added ';
CREATE TRIGGER 表示创建触发器,AFTER INSERT 表示此触发器将在INSERT语句执行成功后再执行,FOR EACH ROW 对每一插入行执行;
18.2 删除触发器
DROP TRIGGER 触发器名;
18.3 使用触发器
18.3.1 INSERT触发器
AFTER(BEFORE) TRIGGER;
可以使用一个名为NEW的虚拟表访问被插入的行;
18.3.2 DELETE触发器
可以使用一个名为OLD 的虚拟表访问被删除的行;
19.管理事务处理
控制事务处理
使用ROLLBACK来回退(撤销)sql语句(可用来管理INSERT,UPDATE和DELETE语句);
提交使用COMMIT;语句;
回退部分事务处理:可以使用占用符(保留点),可以使用SAVEPOINT语句来创建占位符;
例SAVEPOINT delete;
ROLLBACK TO delete;(使用一条ROLLBACK后自动释放保留点,COMMIT也可以);
set autocommit = 0表示不自动提交,可以进行更改以实现自动提交;
20.全球化与本地化
使用字符集和校对顺序
查看字符集完整列表:SHOW CHARACTER SET;
查看所支持校对的完整列表:SHOW COLLATION;
给表指定字符集和校对:例子:CREATE TABLE table(column INT )DEFAULT CHARACTER SET Hebrew COLLATE hebrew_general_ci;
如果既不指定CHARACTER SET,也不指定COLLATE,则使用数据库默认。还可指定每个列设置他们(name varchar(10)CHARACTER SET Latin1 COLLATE Latin1_general_ci;
21.安全管理
21.1 创建用户账号
CREATE USER ben IDENTIFIED BY password 使用后面的口令对该账号进行加密;
21.2 删除用户账号
DROP USER username;
21.3设置访问权限
使用SHOW GRANT FOR用户名 (可以查看用户权限);
USAGE 表示没有任何权限;
GRANT SELECT ON crashcourse . * TO 用户名;(crashcourse表示数据库上的所有表)有使用SELECT的权限;
REVOKE SELECT ON crashcourse . * FROM 用户名;(撤销特定权限);
21.4 更改口令
set PASSWORD for 用户名 = PASSWORD(‘密码’);此句用于更换密码。
SET PASSWORD = Password(‘密码’)用于更新当前登陆用户的口令;
22.数据库维护
22.1进行维护
ANALYZE TABLE 数据库名;用于检查表键是否正确;
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190711215304755.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FkZGl0aW9uR1A=,size_16,color_FFFFFF,t_70)
若访问产生不正确和不一致的结果,需要使用REPAIR TABLE 来修复相应的表;
如果需要从一个表中删除大量数据,应该使用OPTIMIZE TABLE来收回所用的空间,从而优化表的性能;
22.2 查看日志文件
使用FLUSH LOGS语句来刷新和重新开始所有的日志文件;
23.改善性能
使用SHOW PROCESSLIST显示所有进程,用KILL终结某个特定的进程;
24.数据库索引(重点)
24.1概念
索引是寻找资源中特定项目的一种机制(作用就是便捷化检索表中行和列的子集,而不需要检查表中的每行数据)
24.2创建索引
在数据库中添加索引:ALTER TABLE department ADD INDEX dept_name_idx(name);
索引所在列的数据具有唯一性;
24.3 索引类型
1.聚簇索引:
也叫簇类索引,是一种对磁盘上的数据重新组织以按指定的一个或多个列的值排序,聚簇索引的索引页面指针指向数据页面,所以速度快。同时每张表只能建立一个聚簇索引,所需的空间大;
1.1.特点:聚簇索引也称为聚集索引,聚类索引,簇集索引,聚簇索引确定表中数据的物理顺序。聚簇索引类似于电话簿,后者按姓氏排列数据。由于聚簇索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚簇索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。汉语字典也是聚簇索引的典型应用,在汉语字典里,索引项是字母+声调,字典正文也是按照先字母再声调的顺序排列。
聚簇索引对于那些经常要搜索范围值的列特别有效。使用聚簇索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚簇(物理排序),避免每次查询该列时都进行排序,从而节省成本。
优点:
- 聚簇索引的可以把相关的数据保存在一起;
- 使得数据访问更快;
缺点:
- 虽然最大限度地提高了I/O密集型应用的性能,但如果数据全部都放在内存中,则访问的顺序就没那么重要了,此时聚簇索引也就没什么优势;
- 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置;
- 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候;
1.2.注意点:在一个频繁发生插入操作的表上建立聚簇索引时,不要建在具有单调上升值的列(如IDENTITY)上,否则会经常引起封锁冲突;
使用AUTO_INCREMENT自增列,这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好;
1.3. mysql中,不同的存储引擎对索引的实现方式不同,InnoDB引擎采用的便是聚簇索引;
InnoDB 的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,这是聚簇索引。
2. 二级索引:
也称为非聚簇索引(辅助索引),叶子节点上的data是主键 (所以聚簇索引的key,不能过长)。为什么存放的主键,而不是记录所在地址呢,因为记录所在地址并不能保证一定不会变,但主键可以保证。
``Mysql中的MyISAM引擎便是采用该索引,MyISAM的B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。在MyISAM引擎中的主索引和辅助索引没有什么大区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。
MyISAM还采用压缩机制存储索引,比如,第一个索引为“her”,第二个索引为“here”,那么第二个索引会被存储为“3,e”,这样的缺点是同一个节点中的索引只能采用顺序查找。
3.联合索引:(最左前缀原理)
又称复合索引,Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 .当最左侧字段是常量引用时,索引就十分有效
联合索引(复合索引)
联合索引相对于一般索引只有一个字段,联合索引可以为多个字段创建一个索引。它的原理也很简单,比如,我们在(a,b,c)字段上创建一个联合索引,则索引记录会首先按照A字段排序,然后再按照B字段排序然后再是C字段,因此,联合索引的特点:
-
第一个字段一定是有序的
-
当第一个字段值相等的时候,第二个字段又是有序的,比如下表中当A=2时所有B的值是有序排列的,依次类推,当同一个B值得所有C字段是有序排列的
| A | B | C |
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 1 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 6 |
| 2 | 5 | 5 |其实联合索引的查找就跟查字典是一样的,先根据第一个字母查,然后再根据第二个字母查,或者只根据第一个字母查,但是不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理。
4.哈希索引
哈希索引(hash index)基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash
code),哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指
针。
哈希索引的限制:
1.哈希索引只支持等值比较,包括=,<>,IN()。不支持任何范围的查询;
2.哈希索引并不是按照索引值顺序存储的,所以也就无法用于排序;
3。哈希索引只包含哈希值和行指针,而不存储字段,所以不能使用索引的值来避免读取行
24.4 各索引的应用
除复合索引外,什么情况下索引被使用,什么情况下不会被使用
- 建立索引的目的就是帮助查询,如果查寻用不到则索引就没有必要建立。
- 如果表是经常需要更新的也不适合做索引 。频繁更新会导致索引也会频繁更新,降低写的效率。
- 唯一性差的字段不适合创建索引。
- 当给一个字段创建了索引的话,而这个字段要进行like模糊查询的话,那么这个值左边不可以有%,因为索引查询是要从左到右的,你如果给它加上%后,左边的值不是确定的话,它会找不到这个索引。所以在使用like模糊查询的时候,值得左边不可以有%。
- order by 不可以使用在索引当中;
- or 当前后2个字段都有索引时才可以索引出来 否则不可以。
- 如果数据表过大(5w以上)则有些字段(字符型长度超过(40))不适合作为索引。查询大量数据时,索引有效,但是慢
场景:
- 当数据多而字段值有相同的值的时候建议使用非聚簇索引;
- 当数据多而字段值没有重复的值的时候建议使用聚簇索引;
- 当有多个字段名都经常被查询的话建议使用复合索引;
- 普通索引不支持空值,唯一索引支持空值;
- 若是一张表的增删改查较多而且查询较少的话,则推荐不要创建引擎;
- 不会出现在where条件中的字段不该建立索引;
24.5 聚簇索引和非聚簇索引的区别:
注:聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC)和余下的列(如col2)。
InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此,通过二级索引查询首先查到是主键值,然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。
24.6高效的索引策略
- 查询的列如果不是独立的,那么Mysql不会使用索引。“独立的列”指的是索引列不能是表达式的一部分,也不能是函数的参数。(例如:WHERE 后面的表达式不能有像prod + 1 = 5 这样的表达式,必须独立);
- 选择索引的列顺序的法则:将选择性最高的列放在索引最前列;
24.7 案例分析
我们选择将SEX以及COUNTRY作为索引,虽然这两个变量的选择性不高,但是可能很多查询会用到,还有,即使用户查询没有使用SEX列,也可以通过
在查询条件中新增AND SEX IN(‘m’,‘f’)来让Mysql选择该索引。这样写并不会过滤掉任何行;然后再考虑其他常见的WHERE条件的组合,像(SEX,
country,age)上的索引,可能还需要(SEX,country,region, age)等这样的组合索引,而如果想减少这样的组合索引,可以使用前面的IN()的
技巧来避免同时需要像(SEX,country,region, age)和(SEX,country,age)这样的索引;对于那些生僻的搜索条件,则可以忽略它们,让Mysql
多扫描一些额外的行即可
25 数据库设计的三大范式及ER图四个成分
25.1 概念
- 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
- 数据库的实体属性和关系:实体(表),属性(表中的数据),关系(各个表之间的关系);
- 第一范式:当关系模式R的所有属性不能再被分解成更基本的数据单位(原子性)时,则称R满足第一范式(1NF);
- 第二范式:如果关系模式R满足第一范式,并且R的所有非主属性都完全依赖于R的每一个候选关键属性,称R满足第二范式,简记为2NF;
- 第三范式:设R是一个满足第一范式条件的关系模式,X是R的任意属性集,如果X非传递依赖于R的任意一个候选关键字,称R满足第三范式,简记为3NF;
25.2 第一范式
1.每一列的属性都是不可再分的属性值,确保每一列的原子性(例如:联系人的电话,邮箱等等都是不可再分的属性);
2.对两列相近的属性,尽量进行合并,(例如:可以将联系人的所在省份,城市等合并成地址);
25.3 第二范式
1.满足1NF后,要求表的所有列都必须依赖于主键,而不能有任何一列与主键无关,及一张表描述一件事;
2.例如:订单只描述订单的信息,所有的字段都与订单的id有关,就不能出现像产品信息这样的与订单无关的数据;
3.还有像一个人提交了多份订单,则在订单中就会出现该联系人信息过多重复的情况,此时可以将该联系人的编号提出来,和该联系人的具体信息分开在 两张表上,才不会造成数据冗杂;
25.4 第三范式
1.第三范式需要确保数据表中的每一列数据都和主键存在直接相关,而不能是间接相关;
2.比如说:Student表(学号,姓名,年龄,性别,所在院校,院校地址,院校电话)这样一个表结构,就存在上述关系。 学号–> 所在院校 --> (院校地 址,院校电话) 这样的表结构,我们应该拆开来,如下,(学号,姓名,年龄,性别,所在院校)–(所在院校,院校地址,院校电话);
3.再比如说:对于一份订单而言,我们需要知道的是订单号,订单数目,卖家等,而不需要将客户的信息添加上去,至少需要一个客户的订单号即可直接 与客户信息进行连接;
25.5 ER图的四个成分
在ER图中有如下四个成分:
矩形框:表示实体,在框中记入实体名。(譬如:公司,厂商等)
菱形框:表示联系,在框中记入联系名。(譬如:属于,拥有等)
椭圆形框:表示实体或联系的属性,将属性名记入框中。对于主属性名,则在其名称下划一下划线。(譬如:厂家地址,公司代码等)
连线:实体与属性之间;实体与联系之间;联系与属性之间用直线相连,并在直线上标注联系的类型。(对于一对一联系,要在两个实体连线方向各写 1; 对于一对多联系,要在一的一方写1,多的一方写N;对于多对多关系,则要在两个实体连线方向各写N,M。)