首页 > 代码库 > MySQL的基础知识

MySQL的基础知识

一,MySQL查询的五种子句:

1,where(条件查询),常用的运算符:

①比较运算符

> , < , = , != , >= , <= , in( ) , between val1 and val2

②逻辑运算符

逻辑非:not / ! , 逻辑或:or / || , 逻辑与:and / &&

③模糊查询“like”

通配符:% 任意字符,例如:where name like ‘苹果%’

_ 匹配单个字符,例如:where name like ‘苹果__’

 

2,group by(分组),一般情况下和统计函数(聚合函数)一起使用

mysql五种统计函数:

①max:求最大值 例如查询每个分类下面最高价格的商品:

select cat_id max(price) from goods group_by cat_id

②min:求最小值

③sum:求总和,例如:select sum(goods_number) from goods

④avg:求平均值,例如:求每个分类的商品平均价格

select cat_id avg(price) from goods group_by cat_id

⑤count:求商品条数,例如:select count(*) from goods

可以把每个字段名当成变量来理解,它可以进行运算:

例:查询本店每个商品价格比市场价低多少;select goods_id,goods_name,goods_price-market_price from goods;

可以用as给字段名起别名,group by前面如果没有使用聚合函数,默认就取每个分组的第一行数据

 

3,having与where的异同

having与where类似,可以筛选数据,where后的表达式怎么写,having后的表达式就怎么写

where针对表中的列发挥作用,查询数据

having针对查询结果中的列发挥作用,筛选数据

#查询本店商品价格比市场价低多少钱,输出低200元以上的商品

select goods_id,good_name,market_price - shop_price as s from goods having s>200 ;

#如果用where的话则是:select goods_id,goods_name from goods where market_price - shop_price > 200;

#同时使用where与having

select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s > 200;

 

4,order by

1) order by price  //默认升序排列

2)order by price desc //降序排列

3)order by price asc //升序排列,与默认一样

4)order by rand() //随机排列,效率不高

#按栏目号升序排列,每个栏目下的商品价格降序排列: select * from goods where cat_id !=2 order by cat_id,price desc;

 

5,limit:limit [offset,] N

offset 偏移量,可选,不写则相当于limit 0,N。N取出条目

#取价格4-6高的商品select good_id,goods_name,goods_price from goods order by good_price desc limit 3,3;

 

二,mysql子查询

1,where型子查询

(把内层查询结果当作外层查询的比较条件)

#不用order by 来查询最新的商品

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

#取出每个栏目下最新的产品(goods_id唯一)

select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);

 

2,from型子查询(把内层的查询结果供外层再次查询)

#用子查询查出挂科两门及以上的同学的平均成绩

思路:#先查出哪些同学挂科两门以上select name,count(*) as gk from stu where score < 60 having gk >=2;

#以上查询结果当做一个表放入下面的查询中去select name from (select name,count(*) as gk from stu having gk >=2) as t;

#找出这些同学了,那么再计算他们的平均分

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

 

3,exists型子查询(把外层查询结果拿到内层,看内层的查询是否成立)

#查询哪些栏目下有商品,栏目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

如果商品表中的商品的栏目id等于某个栏目的id那么证明该栏目下面存在商品,所以这些栏目在条件下成立。会被查询出来。

 

三,union的用法

UNION用于把来自许多SELECT语句的结果组合到一个结果集合中

 

四,内连接,左连接,右连接,全连接

T1:                     T2:

id name passwd        id jifen dengji

1  jack   jpw             1   20    3

2  tom   tpw      3   50    4

1,内连接

如果想把用户信息、积分、等级都列出来,那么一般会这样写:

普通:select * from T1, T2 where T1.id = T2.id

内连:select * from T1 inner join T2 on T1.id = T2.id

后者的效率比前面的效率高多了,建议使用内连

 

2,左连接

显示左表T1中的所有行,并把右表T2中符合条件加到左表T1中;

右表T2中不符合条件,就不用加入结果表中,并且NULL表示。

SQL语句:select * from T1 left join T2 on T1.id = T2.id

运行结果 :

T1.id name  passwd  T2.id  jifen   dengji   

1     jack    jpw      1     20    3   

2       tom    tpw      NULL   NULL   NULL   

 

3,右连接

显示右表T2中的所有行,并把左表T1中符合条件加到右表T2中;

左表T1中不符合条件,就不用加入结果表中,并且NULL表示。

SQL语句:select * from T1 right join T2 on T1.id = T2.id

运行结果:

T1.id   name   passwd   T2.id   jifen   dengji   

1        jack      jpw         1        20      3   

NULL   NULL   NULL        3        50      4

 

4,全连接

显示左表T1、右表T2两边中的所有行,即把左联结果表 + 右联结果表组合在一起,然后过滤掉重复的。SQL语句:

select * from T1 full join T2 on T1.id = T2.id

运行结果   

T1.id   name   passwd   T2.id   jifen   dengji   

1         jack      jpw        1         20    3   

2         tom      tpw     NULL   NULL   NULL   

NULL   NULL   NULL        3      50      4 

 

四,MySQL的索引

1,什么是索引

一个索引是存储的表中一个特定列的值数据结构(最常见的是B-Tree)。索引是在表的列上创建。所以,要记住的关键点是索引包含一个表中列的值,并且这些值存储在一个数据结构中。说到底索引就是对数据列的值进行结构化排序的一个东西。记住这一点:索引是一种数据结构

B-Tree 是最常用的用于索引的数据结构。因为它们是时间复杂度低, 查找、删除、插入操作都可以可以在对数时间内完成。另外一个重要原因存储在B-Tree中的数据是有序的。数据库管理系统(RDBMS)通常决定索引应该用哪些数据结构。但是,在某些情况下,你在创建索引时可以指定索引要使用的数据结构。

 

2,索引是怎么提升性能的?

因为索引基本上是用来存储列值的数据结构,这使查找这些列值更加快速。如果索引使用最常用的数据结构-B-Tree-那么其中的数据是有序的。有序的列值可以极大的提升性能。下面解释原因。

假设我们在 Employee_Name这一列上创建一个B-Tree索引。这意味着当我们用之前的SQL查找姓名是‘Jesus’的雇员时,不需要再扫描全表。而是用索引查找去查找名字为‘Jesus’的雇员,因为索引已经按照按字母顺序排序。索引已经排序意味着查询一个名字会快很多,因为首字母为‘J’的员工都是排列在一起的。另外重要的一点是,索引同时存储了表中相应行的指针以获取其他列的数据。

 

3,数据库索引里究竟存的是什么?

你现在已经知道数据库索引是创建在表的某列上的,并且存储了这一列的所有值。但是,需要理解的重点是数据库索引并不存储这个表中其他列(字段)的值。举例来说,如果我们在Employee_Name列创建索引,那么列Employee_Age和Employee_Address上的值并不会存储在这个索引当中。

索引存储了指向表中某一行的指针

如果我们在索引里找到某一条记录作为索引的列的值,如何才能找到这一条记录的其它值呢?这是很简单 - 数据库索引同时存储了指向表中的相应行的指针。指针是指一块内存区域, 该内存区域记录的是对硬盘上记录的相应行的数据的引用。因此,索引中除了存储列的值,还存储着一个指向所在行数据的索引。也就是说,索引中的Employee_Name这列的某个值(或者节点)可以描述为 (“Jesus”, 0x82829), 0x82829 就是包含 “Jesus”那行数据在硬盘上的地址。如果没有这个引用,你就只能访问到一个单独的值(“Jesus”),而这样没有意义

 

4,哈希表索引是怎么工作的?

哈希表是另外一种你可能看到用作索引的数据结构-这些索引通常被称为哈希索引。使用哈希索引的原因是,在寻找值时哈希表效率极高。所以,如果使用哈希索引,对于比较字符串是否相等的查询能够极快的检索出的值。例如之前我们讨论过的这个查询(SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’) 就可以受益于创建在Employee_Name 列上的哈希索引。哈系索引的工作方式是将列的值作为索引的键值(key),和键值相对应实际的值(value)是指向该表中相应行的指针。因为哈希表基本上可以看作是关联数组,一个典型的数据项就像“Jesus => 0x28939″,而0x28939是对内存中表中包含Jesus这一行的引用。在哈系索引的中查询一个像“Jesus”这样的值,并得到对应行的在内存中的引用,明显要比扫描全表获得值为“Jesus”的行的方式快很多。

 

5,哈希索引的缺点

哈希表是无顺序的数据结构,对于很多类型的查询语句哈希索引都无能为力。举例来说,假如你想要找出所有小于40岁的员工。你怎么使用哈希索引进行查询?因为哈希表只适合查询键值对-也就是说查询相等的查询(例:like “WHERE name = ‘Jesus’)。哈希表的键值映射也暗示其键的存储是无序的。这就是为什么哈希索引通常不是数据库索引的默认数据结构-因为在作为索引的数据结构时,它没有B-Tree那么灵活

 

6,聚簇索引:

实际存储的顺序结构与数据存储的物理结构是一致的,所以通常来说物理顺序结构只有一种,那么一个表的聚簇索引也只能有一个,通常默认都是主键,设置了主键,系统默认就为你加上了聚簇索引,当然有人说我不想拿主键作为聚簇索引,我需要用其他字段作为索引,当然这也是可以的,这就需要你在设置主键之前自己手动的先添加上唯一的聚簇索引,然后再设置主键,这样就木有问题啦。总而言之,聚簇索引是顺序结构与数据存储物理结构一致的一种索引,并且一个表的聚簇索引只能有唯一的一条。

 

7,非聚簇索引:

非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系;一个表对应的非聚簇索引可以有多条,根据不同列的约束可以建立不同要求的非聚簇索引。

 

8,主键索引和唯一索引的区别

主键创建后一定包含一个唯一性索引,唯一性索引不一定就是主键。

唯一性索引列允许空值,而主键列不允许为空值。

主键列在创建时,已经默认为空值 + 唯一索引了。

主键可以被其他表引用为外键,而唯一索引不能。

一个表最多只能创建一个主键,但可以创建多个唯一索引。

主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。

 

总结索引使用原则:

1:不要索引数据量不大的表,对于小表来讲,表扫描的成本并不高。

2:不要设置过多的索引,在没有聚集索引的表中,最大可以设置249个非聚集索引,过多的索引首先会带来更大的磁盘空间,而且在数据发生修改时,对索引的维护是特别消耗性能的。

3:合理应用复合索引,有某些情况下可以考虑创建包含所有输出列的覆盖索引。

4:对经常使用范围查询的字段,可能考虑聚集索引。

5:避免对不常用的列,逻辑性列,大字段列创建索引。

MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)

 

来源[http://www.cnblogs.com/rollenholt/archive/2012/05/15/2502551.html],[http://blog.csdn.net/weiliangliang111/article/details/51333169]

感谢以上博友分享!

MySQL的基础知识