系统函数与查询

系统函数与查询

1、  count函数

SELECT COUNT(*)|COUNT(列名) from 表名 [WHERE 条件语句]

注意事项

       COUNT(列名)的方式是统计指定列中有多少条记录, 不包括值为NULL的

       COUNT(*)则是统计表中有多少条数据

       COUNT(DISTINCT 列名) 统计不重复的记录数

       如果加上WHERE子句, 则是统计满足条件的记录

练习

       统计student表中有多少条记录

       select count(*) from student;

       统计学生语文成绩大于80的有多少人

       select count(*) from student where chinese>80;

       统计总分大于250的有多少人

       select count(*) from student where english+math+chinese>250;

       统计参加英语考试的有多少人

       select count(english) from student;

2、SUM函数

语法

       SELECT SUM(列名) FROM 表名 [WHERE 条件语句];

注意事项

       计算指定列中所有记录的和, 如果有WHERE子句则计算满足条件的记录

练习

       计算所有学生的数学成绩总和

       select sum(math) from student;

       显示所有学生的语文成绩总和, 数学成绩总和, 英语成绩总和

       select sum(chinese),sum(math),sum(english) from student;

       计算所有学生的分数总和

       select sum(chinese)+sum(math)+sum(english) from student;

       统计英语平均分

       select sum(english)/count(*) from student;

select sum(english)/count(english) from student;

3、AVG函数

语法

       SELECT AVG(列名) FROM 表名 [WHERE 条件语句];

注意事项

       计算指定列的平均值, 如果有WHERE子句, 则计算满足条件的记录

       AVG()统计平均数不包含NULL值

练习

       计算英语平均分

       select avg(english) from student;

       计算总分平均分, MySQL不支持组函数嵌套使用.

       select sum(english+math+chinese)/count(*) from student;

4、MAX / MIN函数

语法

       SELECT MAX(列名) FROM 表名 [WHERE 条件语句];

       SELECT MIN(列名) FROM 表名 [WHERE 条件语句];

注意事项

       获取指定列最高/最低值, NULL不参与统计

练习

       统计总分最高分和最低分

       select max(english+math+chinese),min(english+math+chinese) from student;

5、时间数函

注意date, datetime, timestamp之间的区别

ADDTIME(原时间, 增加值)       在某个时间上增加一段时间

       select addtime('18:23:01', '01:01:01');

select addtime(now(),'3:0:0');

CURRENT_DATE()                    当前日期

       select current_date();

CURRENT_TIME()                    当前时间

       select current_time();

CURRENT_TIMESTAMP()         当前时间戳

       select current_timestamp();

DATE(时间)                               返回制定时间的日期部分

       select date('2011-02-14 18:00:00');

DATE_ADD(日期,INTERVAL 增加值 类型)           在指定日期上对某个字段增加

       select date_add('2011-02-14 23:00:00', interval 10 month);

DATE_SUB(日期,INTERVAL 减少值 类型)            在指定日期上对某个字段减少

       select date_sub('2011-02-14 23:00:00', interval 1 year);

DATEDIFF(日期1, 日期2)        计算两个日期之间的差值

       select datediff('2000-02-14', '2001-02-14');

NOW()          当前时间

       select now();

YEAR|MONTH|DATE|HOUR|MINUTE|SECOND(时间)          获取指定时间的某个字段

       select year('2011-02-14 23:00:00');

       select hour('2011-02-14 23:00:00');

6、字符串函数

CHARSET(字符串)                                                返回字符串字符集

       select charset(name) from student;

CONCAT(字符串1[, 字符串2]... )                         连接字符串

       select concat('aaa', 'bbb', 'ccc');

INSTR(字符串, 子字符串)                                     查找子字符串出现位置, 注意序号从1开始

       select instr('abc', 'a');

UCASE(字符串)                                                     将字符串转为大写

       select ucase('aBc');

LCASE(字符串)                                                     将字符串转为小写

       select lcase('aBc');

LEFT(字符串, 长度)                                              从字符串左边取指定长度个字符

       select left('aBc',2);

LENGTH(字符串)                                                  计算字符串长度

       select length('aBc');

REPLACE(字符串, 搜索字符串, 替换字符串)        将字符串中指定字符串替换为其他字符串

       select replace('abbcbbd', 'bb', 'ee');

STRCMP(字符串1, 字符串2)                                逐个字符比较两个字符串, 如果是包含关系, 则返回长度差值   

       select strcmp('abcc', 'abde');

       select strcmp('abc', 'ab');

SUBSTRING(字符串, 开始坐标[, 个数])         从字符串中截取

       select substring('abcdef', 3);

       select substring('abcdef', 3, 2);

LTRIM(字符串)                                                      去掉左边空白

       select ltrim('    abc   ');

       select concat('--', ltrim('    abc   '), '--');

RTRIM(字符串)                                                      去掉右边空白

       select concat('--', rtrim('    abc   '), '--');

TRIM(字符串)                                                        去掉左右两边空白

       select concat('--', trim('    abc   '), '--');

7、数学函数

ABS(数字)                                        求绝对值

       select abs(10);

       select abs(-10);

BIN(十进制数)                                  将十进制转换为二进制

       select bin(5);

HEX(十进制数)                                将十进制转换为十六进制

       select hex(10);

CONV(数字, 原进制, 目标进制)       转换进制

       select conv(12, 10, 16);

       select conv(12, 10, 2);

       select conv(12, 16, 2);

CEILING(小数)                                 向上取整

       select ceiling(3.4);

FLOOR(小数)                                   向下取整

       select floor(3.4);

ROUND(小数)                                  四舍五入

       select round(3.4);

select round(3.5);

FORMAT(小数, 保留位数)                保留小数位

       select format(3.1415926, 2);

LEAST(值,值[,值]...)                         取最小值

       select least(1,2,3,4);

       select least('a', 'b', 'c', 'd');

GREATEST(值,值[,值]...)                  取最大值

       select greatest(1,2,3,4);

       select greatest('a', 'b', 'c', 'd');

MOD(数字, 数字)                             取余

       select mod(3,2);

       select 3%2;

RAND()                                            生成随机数, 14位小数, 0 <= n <= 1

       select rand();

 

1、多表连接查询

姓名  课程名   成绩

xs     kc       xs_kc

 

(1)全连接--------不是标准的sql语句

select 列名1,列名2...       from 表名1,表名2...        where 表名1.列名=表名2.列名

查看：学生学号、姓名、选修的课程号、得分

       xs xs_kc  xs     xs_kc       xs_kc

select xs.xid,name,kid,score     from xs,xs_kc     where xs.xid=xs_kc.xid 

 

查看：学号、课程名、课程号、学时

select xid,xs_kc.kid,kname,ktime

from xs_kc,kc

where xs_kc.kid=kc.kid; 

 

查看：学生姓名、课程名、得分

       xs       kc     xs_kc

select name,kc.kid,score      from xs,xs_kc,kc       where xs.xid=xs_kc.xid and  kc.kid=xs_kc.kid;

 

(2)————标准的sql语句，推荐使用

 

内连接、外连接、自然连接、交叉连接

 

1）、内连接：多个表通过连接条件中共享列的值进行比较连接

from 表名1 [inner] join 表名2 on 连接条件

select 列表名

from 表名1 join 表名2      //从第一张表连接到第二张表

on 连接条件

 

查看：学生学号、姓名、选修的课程号、得分

       xs xs_kc  xs     xs_kc       xs_kc

select xs.xid,name,kid,score       from xs join xs_kc        on xs.xid=xs_kc.xid;

 

查看：学生姓名、课程名、得分

       xs       kc     xs_kc

select name,kc.kid,score        from xs join xs_kc        on xs.xid=xs_kc.xid       join kc on xs_kc.kid=kc.kid;

 

 

2）外连接：包含一张表的所有记录行和另一张的匹配记录行

左外连接from表名1 left [outer] join 表名2 on 连接条件

右外连接from表名1 right [outer] join 表名2 on 连接条件

查询：所有学生的学号，姓名以及他们所选课程的课程号，得分

         xs  xs_kc    xs                   xs_kc   xs_kc

select xs.xid,name,kid,score       from xs left outer join xs_kc      on xs.xid=xs_kc.xid;

 

查询：学生选修课程的信息（学号、课程号、课程名），还看没有被学生选修的课程的情况

select xid,xs_kc.kid,kname      from xs_kc right outer join kc        on xs_kc.kid=kc.kid;

 

3）自然连接

 

natural join自然内连接

natural left join自然左外连接

natural right join自然右外连接

 

select xs.xid,name,kid,score       from xs natural join xs_kc;//自然连接不需要再用on指定条件了

 

4）交叉连接————笛卡尔积[不用指定条件，如果愿意也可以指定]

 

select xs.xid,name,kid,score          from xs cross join xs_kc;

 

引用一张表

(1)use数据库;

select ...

from 表名;

(2)select...

from 数据库.表名

 

 

引用多张表

select...

from 表名1,表名2...

where 多表的连接条件

select...

from 表名1 join 表名2

on 连接条件

 

select p.productid,productname

from productid p join productname pn

from 子句可以给表起别名，这个表的别名只在当前的select语句中有效，并且一旦给表起别名，该select语句的其他子句如果要使用表名的话一定要用别名

2.嵌套查询

子查询一般都可以写成多表的连接查询

(1)

查询是选修了计算机基础的课程的学生的学号

xs_kc   kc

select kname,kid

from xs_kc natural join kc

where kname='计算机基础';

换一种思路：

select xid

from xs_kc

where kid=(

       select kid

       from kc

       where kname='计算机基础'

);

第一步：执行子查询，101

第二步：执行外部查询，外部查询的查询条件是依赖于子查询

查询：所有成绩比平时成绩高的学生的学号、课程号

select xid,kid

from xs_kc

       where score>(

       select avg(score)

       from xs_kc

);

子查询只做一次

(2)子查询的结果不是单行单列,而是多行单列

解决办法：使用in关键字

=>  <   在使用关系运算符，在子查询的结果集前加all或者any

 

选修了101的课程的学生的学号和姓名

 

xs:学号、姓名

xs_kc:学号、课程号

 

select name

from xs join xs_kc

on xs.xid=xs_kc.xid       //连接的条件

where kid='101';

 

select name

from xs

where xid in(select xid   from xs_kc   where kid='101');

先执行子查询、也只执行一次

 

select name

from xs

where xid not in(select xid   from xs_kc   where kid='101');

 

select name

from xs

where xid <>all(select xid   from xs_kc   where kid='101');//查找的是kid不等于101的

 

select name

from xs

where xid =any (select xid   from xs_kc   where kid='101');

 

where 列名>ALL(子查询)    (1,8,9)    列的取值>9   找的是比最大值还要大的记录行     <ALL(子查询)    (1,8,9)    列的取值<=1 找的是比最小值还要小的记录行

>=all(子查询)                          <=all(子查询)                   =all(子查询)                 <>all(子查询)

where 列名>any(子查询)    (1,8,9)    列的取值>1  找的是比最小值还大的记录行     <any(子查询)    (1,8,9)    列的取值<=9 找的是比最大值要小的记录行

>=any(子查询)                          <=any(子查询)                =any(子查询)               <>any(子查询)

any和某一个值去进行判定

 

select xid,name,zhuanye,bir

from xs

where bir<all(

select bir

from xs

where zhuanye='计算机');//用all比最小值还要小

 

(3)where exists(子查询)//子查询结果只要不为空就表示存在，这个表达式返回就为真，否则该判定的结果为false

 

查询:选修了101课程的学生姓名

select name

from xs

where exists(select * from xs_kc where xid=xs.xid and kid='101');

 

3、分组group by

select zhuanye,count(*) as 人数

from xs

group by zhuanye desc;

查询：每个学生的总成绩

查询：每门功课的平均分

 

select xid,sum(score)

from xs_kc

group by xid;

 

select kid,xid,avg(score)

from xs_kc

group by kid;

 

select zhuanye,sex,count(*)

from xs

group by zhuanye,sex;