9.9随笔

来源：互联网发布：奥尔巴赫知乎编辑：程序博客网时间：2024/05/22 13:59

今天偶已经驾驭的炉火纯青的学习重点 hoho

8.4. WHERE
语法
SELECT 列名 FROM 表名 [WHERE 条件语句]
WHERE子句中的运算符

比较运算符 >, <, >=, <=, =, <>,
<=> 注意不等于和Java中不同, 是<>
BETWEEN ... AND ... 某一区间内的值, 从 ... 到 ...
IN(列表) 在列表之中, 例: in(1,2,3) 代表1或2或3
LIKE(表达式) 模糊查询, %代表多个字符, _代表单个字符
IS NULL 判断是否为NULL

逻辑运算符 AND && 与, 两边都为TRUE结果为TRUE
OR || 或, 一边为TRUE结果就为TRUE
NOT ! 非, 将表达式结果取反
练习
查询英语分数在80-90分之间的
select name,english from student where english>=80 and english<=90;
查询语文分数为81,82,83的学生
select name,chinese from student where english in(80,90,82);
查询所有姓张的学生的成绩
select name,english,math,chinese from student where name like '张%';
查询除了姓张和姓李的学生总分
select name,english,math,chinese
from student
where name not like '张%'
and name not like '李%';

select name,english,math,chinese
from student
where name like '张%'
or name like '李%';

8.5. ORDER BY
语法
SELECT 列名 FROM 表名 ORDER BY 列名 ASC|DESC;
注意事项
ORDER BY 指定排序的列名可以是表中的列名, 也可以是SELECT语句后面起的别名
ASC为升序, DESC为降序
ORDER BY应在查询语句的结尾
练习
对数学成绩排序后输出
select name,math from student order by math;
查询总分, 从高到低显示
select name '姓名',chinese+math+english '总分' from student order by 总分 desc;
选择所有姓张的学生的英语成绩, 并从高到低排序
select name,english from student where name like '张%' order by english desc;
查询学生成绩, 按照语文从高到低排序, 如果语文相同, 按照英语从高到低排序
select * from student order by chinese desc,english desc;
8.6. 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;
等价于select count(*) from student where english is not null;
8.7. 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;

8.8. AVG函数
语法
SELECT AVG(列名) FROM 表名 [WHERE 条件语句];
注意事项
计算指定列的平均值, 如果有WHERE子句, 则计算满足条件的记录
AVG()统计平均数不包含NULL值
练习
计算英语平均分
select avg(english) from student;
计算总分平均分, MySQL不支持组函数嵌套使用.
select sum(english+math+chinese)/count(*) from student;
8.9. MAX / MIN函数
语法
SELECT MAX(列名) FROM 表名 [WHERE 条件语句];
SELECT MIN(列名) FROM 表名 [WHERE 条件语句];
注意事项
获取指定列最高/最低值, NULL不参与统计
练习
统计总分最高分和最低分
select max(english+math+chinese),min(english+math+chinese) from student;
8.10. GROUP BY(重点)
语法
SELECT 列名 FROM 表名 GROUP BY 列名 [HAVING 条件语句]
注意事项
按照某列归类
HAVING和WHERE类似, 但HAVING是作用于组, 其中可以使用组函数
SELECT列表中未包含在组函数中的列名, 只能是GROUP BY中的列名
HAVING中可以使用组函数, WHERE不能.
练习
导入order.sql
对订单表归类, 显示购买过哪些商品
select product from orders group by product;
select distinct product from orders;
对订单表归类, 显示购买过哪些商品, 并显示每种购买了几个, 以及总价
select product,count(product),sum(price) from orders group by product;
查询总价大于5000的商品有哪几类
select product,count(product),sum(price) sum_price from orders group by product having sum_price>5000;

9. 函数
9.1. 时间函数
注意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');
9.2. 字符串函数
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   '), '--');
9.3. 数学函数
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