首页 > 代码库 > MySQL 学习总结2

MySQL 学习总结2

 function 函数

函数的作用比较大,一般多用在select查询语句和where条件语句之后。按照函数返回的结果,
可以分为:多行函数和单行函数;所谓的单行函数就是将每条数据进行独立的计算,然后每条数据得到一条结果。
如:字符串函数;而多行函数,就是多条记录同时计算,得到最终只有一条结果记录。如:sum、avg等
多行函数也称为聚集函数、分组函数,主要用于完成一些统计功能。MySQL的单行函数有如下特征:
    单行函数的参数可以是变量、常量或数据列。单行函数可以接受多个参数,但返回一个值。
    单行函数就是它会对每一行单独起作用,每一行(可能包含多个参数)返回一个结果。
    单行函数可以改变参数的数据类型。单行函数支持嵌套使用:内层函数的返回值是外层函数的参数。
 
单行函数可以分为:
    类型转换函数;
    位函数;
    流程控制语句;
    加密解密函数;
    信息函数

单行函数

 
1、    char_length字符长度
select char_length(tel) from user;
 
2、    sin函数
select sin(age) from user;
select sin(1.57);
 
3、    添加日期函数
select date_add(‘2010-06-21‘, interval 2 month);
interval是一个关键字,2 month是2个月的意思,2是数值,month是单位
select addDate(‘2011-05-28‘, 2);
在前面的日期上加上后面的天数
 
4、    获取当前系统时间、日期
select curdate();
select curtime();
 
5、    加密函数
select md5(‘zhangsan‘);
 
6、    Null 处理函数
select ifnull(birthday, ‘is null birthday‘) from user;
如果birthday为null,就返回后面的字符串
 
select nullif(age, 245) from user;
如果age等于245就返回null,不等就返回age
 
select isnull(birthday) from user;
判断birthday是否为null
 
select if(isnull(birthday), ‘birthday is null‘, ‘birthday not is null‘) from user;
如果birthday为null或是0就返回birthday is null,否则就返回birthday not is null;类似于三目运算符
 
7、    case 流程函数
case函数是一个流程控制函数,可以接受多个参数,但最终只会返回一个结果。
select name, 
age, 
(case sex
    when 1 then ‘男‘
    when 0 then ‘女‘
    else ‘火星人‘
    end
) sex
from user;

 

组函数

组函数就是多行函数,组函数是完成一行或多行结果集的运算,最后返回一个结果,而不是每条记录返回一个结果。

1、    avg平均值运算
select avg(age) from user;
select avg(distinct age) from user;
 
2、    count 记录条数统计
select count(*), count(age), count(distinct age) from user;
 
3、    max 最大值
select max(age), max(distinct age) from user;
 
4、    min 最小值
select min(age), min(distinct age) from user;
 
5、    sum 求和、聚和
select sum(age), sum(distinct age) from user;
select sum(ifnull(age, 0)) from user;
 
6、    group by 分组
select count(*), sex from user group by sex;
select count(*) from user group by age;
select * from user group by sex, age;
 
7、    having进行条件过滤
不能在where子句中过滤组,where子句仅用于过滤行。过滤group by需要having
不能在where子句中用组函数,having中才能用组函数
select count(*) from user group by sex having sex <> 2;

 

? 多表查询和子查询

数据库的查询功能最为丰富,很多时候需要用到查询完成一些事物,而且不是单纯的对一个表进行操作。而是对多个表进行联合查询,
MySQL中多表连接查询有两种规范,较早的SQL92规范支持,如下几种表连接查询:
    等值连接
    非等值连接
    外连接
    广义笛卡尔积
SQL99规则提供了可读性更好的多表连接语法,并提供了更多类型的连接查询,SQL99支持如下几种多表连接查询:
    交叉连接
    自然连接
    使用using子句的连接
    使用on子句连接
    全部连接或者左右外连接
 
SQL92的连接查询
SQL92的连接查询语法比较简单,多将多个table放置在from关键字之后,多个table用“,”隔开;
连接的条件放在where条件之后,与查询条件直接用and逻辑运算符进行连接。如果条件中使用的是相等,
则称为等值连接,相反则称为非等值,如果没有任何条件则称为广义笛卡尔积。
广义笛卡尔积:select s.*, c.* from student s, classes c;
等值:select s.*, c.* from student s, classes c where s.cid = c.id;
非等值:select s.*, c.* from student s, classes c where s.cid <> c.id;
select s.*, c.name classes from classes c, student s where c.id = s.classes_id and s.name is not null;
 
SQL99连接查询
1、交叉连接cross join,类似于SQL92的笛卡尔积查询,无需条件。如:
select s.*, c.name from student s cross join classes c;
 
2、自然连接 natural join查询,无需条件,默认条件是将2个table中的相同字段作为连接条件,如果没有相同字段,查询的结果就是空。
select s.*, c.name from student s natural join classes c;
 
3、using子句连接查询:using的子句可以是一列或多列,显示的指定两个表中同名列作为连接条件。
如果用natural join的连接查询,会把所有的相同字段作为连接查询。而using可以指定相同列及个数。
select s.*, c.name from student s join classes c using(id);
 
4、    join … on连接查询,查询条件在on中完成,每个on语句只能指定一个条件。
select s.*, c.name from student s join classes c on s.classes_id = c.id;
 
5、    左右外连接:3种外连接,left [outer] join、right [outer] join,连接条件都是通过用on子句来指定,条件可以等值、非等值。
select s.*, c.name from student s left join classes c on s.classes_id = c.id;
select s.*, c.name from student s right join classes c on s.classes_id = c.id;
 
    子查询
    子查询就是指在查询语句中嵌套另一个查询,子查询可以支持多层嵌套。子查询可以出现在2个位置:
    from关键字之后,被当做一个表来进行查询,这种用法被称为行内视图,因为该子查询的实质就是一个临时视图
    出现在where条件之后作为过滤条件的值
 
子查询注意点:
    子查询用括号括起来,特别情况下需要起一个临时名称
    子查询当做临时表时(在from之后的子查询),可以为该子查询起别名,尤其是要作为前缀来限定数据列名时
    子查询用作过滤条件时,将子查询放在比较运算符的右边,提供可读性
    子查询作为过滤条件时,单行子查询使用单行运算符,多行子查询用多行运算符
 
将from后面的子查询当做一个table来用:
select * from (select id, name from classes) s where s.id in (1, 2);
当做条件来用:
select * from student s where s.classes_id in (select id from classes);
select * from student s where s.classes_id = any (select id from classes);
select * from student s where s.classes_id > any (select id from classes);

MySQL 学习总结2