MySQL高级特性

墨蓝 2022-05-09 07:38 325阅读 0赞

1.  集合函数  
    ![在这里插入图片描述][70]  
    1） 行列计数  
    计算查询语句返回的的记录行数，直接计算函数count(*)的值  
    比如：select count(*) from user where gender=’男’;

2） 统计字段值的数目

比如：查询user表中name列的数目  
select count(name) from user;

比如：查询user表中name为admin的数目  
select count(name) from user where name=’admin’;  
或者是：select count(\*) from user where name=’admin’;

比如：查询user表中有几个性别  
select count(distinct gender) from user;

注意：使用count()时，字段中的空值通常会被忽略

当count()和group by配合使用  
比如：根据user表中的name分组并显示数量  
select name,count(\*) from user group by name;

3） 计算字段的平均值

比如：查询用户的平均年龄  
select avg(age) from user;

注意：avg()函数在计算平均值时也可忽略空值。

比如：查询用户各个性别的平均年龄  
select gender,avg(age) from user group by gender;

4） 计算字段值的和

比如：计算所有用户年龄总和  
select sum(age) from user;

比如：计算用户各性别的年龄总和  
select gender,sum(age) from user group by gender;

5） 计算字段值的极值

比如：查询用户的最小和最大的年龄  
select min(age),max(age) from user;

1.  操作日期和时间

1） 返回当前日期和时间

curdate() 返回当前日期  
curtime() 返回当前时间  
now() 返回当前日期和时间

用法：  
select CURDATE();  
select curtime();  
select now();

2） 自动记录数据的改变时间

TIMESTAMP列类型提供的一种类型。它的值时从1970的某时的开始一直到2037年，精度为一秒，其值作为数字显示。

咱们可以用TIMESTAMP自动地用当前的日期和时间标记insert或者update的操作。

注意：如果有多个TIMESTAMP的列，只有第一个自动更新。

自动更新第一个TIMESTAMP列发生的条件：  
 列没有明确地在一个INSERT或LOAD DATAI INFILE语句中指定。  
 列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。  
 明确地设定TIMESTAMP列为NULL。

3） 返回日期和时间范围

后续加上…

4） 比较日期和时间

函数：to\_days(date)  
说明：给出一个日期date，返回一个天数（从0年的天数），date可以是一个数字，也可以是一个串值，当然更可以是包含日期的时间类型。

比如：  
select TO\_DAYS(NOW())-TO\_DAYS(‘2018-09-01’);  
select TO\_DAYS(NOW())-TO\_DAYS(‘20180901’);

1.  字符串模式匹配

1） 标准的SQL模式匹配

SQL的模式匹配允许使用“\_”匹配任何单个字符，而“%”匹配任意数目字符（包括零个字符）。

在MySQL中，SQL模式缺省是忽略大小写的。

使用SQL模式时，不能使用=或者!=。但可以使用LIKE或NOT LIKE比较操作符。

比如：查找姓李的用户  
select \* from user where name like “李%”;

2）扩展正则表达式模式匹配  
后续加上……

1.  深入select的查询功能

1） 列和表的别名  
精选输出的可以用列名、列名或列位置在order by和group by子句引用，咧位置从1开始。

比如：从user表中检索出用户名字和年龄  
select name,age from user order by name,age;  
或者：select name,age from user order by 1,2;  
又或者：select name as 姓名,age as 年龄 from user order by name,age;

也可以给表起个好看的别名：  
比如：select [u.name][] from user u where age=13;

2） 取出互不相同的记录

比如：select distinct \* from tb\_name;

3） NULL值的问题  
后续加上……

4） 大小写敏感性

数据库和表名：在unix上是区分大小写的，而在win32上忽略大小写。但是尽量区分大小写。

列名：列名在所有情况下都是忽略大小写的

表和别名：表的别名是区分大小写的。

列的别名：列的别名是忽略大小写的。

字符串比较和模式匹配：

5） 检索语句与多个表的连接

全连接：  
含义：在检索时指定多个表，将每个表用都好分隔，这样每个表的数据行都和其他表的每行交叉产生所有可能的组合，这样就是一个全连接。所有可能的组和数即每个表的行数之和。  
比如：select \* from user,document;  
假如user表有m行，document表有n行，那么，总记录数就是m\*n

左连接：全连接给出 FROM 子句中所有表都有匹配的行。对于左连接，不仅匹配类似前面的行记录，而且还显示左边的表有而右边的表中无匹配的行。对于这样的行，从右边表选择的列均被显示为 NULL。这样，每一匹配的行都从左边的表被选出，而如果右边表有一个匹配的行，则被选中，如果不匹配，行仍然被选中，不过，其中右边相应的列在结果集中均设为 NULL。即， LEFT JOIN 强制包含左边表的每一行，而不管右边表是否匹配。

1.  索引属性

1） 索引的特点  
所有的MySQL列类型都可以被索引。在相关的列上的使用索引是改进select操作性能的最好办法。  
注意：  
a) 一个表最多可以有16个索引。最大的索引长度是256.  
b) 对于char和varchar列，可以索引列的前缀。对于BLOB和TEXT列，必须索引列的前缀。  
c) MySQL可以在多个列上创建索引。一个索引可以由最多15个列组成。

索引有如下几种情况：  
a) Index索引：这是通常意义的索引。某些情况下，它与key相同。索引的列可以包括重复的值。  
b) unique索引：唯一索引，保证了列不包含重复的值，对于多列唯一索引，它保证值的组合不重复。  
c) primary key索引：也unique索引非常类似。事实上，primary key索引仅是一个具有primary名称的unique索引。这表示一个表只能含有一个primary key。  
2） 用alter table语句创建与删除索引  
可以使用alter table或者create index来创建索引。  
alter table最常用，因为可以用它来创建普通索引、unique索引或primary key索引，如：  
alter table tb\_name add index index\_name (column\_list)  
alter table tb\_name add unique index\_name (column\_list)  
alter table tb\_name add primary key index\_name (column\_list)  
删除索引如下：  
Alter table tb\_name drop index index\_name;  
Alter table tb\_name drop primary key;

注意：如果从表中删除了列，则索引也是会受到影响。比如，所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

查看某表的索引情况：show index from user;  
3） 用create/drop index创建/删除索引  
创建语法：  
create unique index index\_name on tb\_name (column\_list);  
create index index\_name on tb\_name (column\_list);

注意：create index无法创建primary key索引。

删除语法：  
drop index index\_name on tb\_name;  
4） 在创建表时指定索引  
比如：  
CREATE TABLE tbl\_name  
(  
…  
INDEX index\_name (column\_list),  
KEY index\_name (column\_list),  
UNIQUE index\_name (column\_list),  
PRIMARY KEY index\_name (column\_list),  
…  
)

5） 索引最重要的功能  
通过使用索引加速表的检索。后面在数

[70]: /images/20220509/2077b86ef18c4fc1aa07f25802af3358.png
[u.name]: http://u.name