MySQL复习2 20110909

　　操作数据(查询)

1.1. DISTINCT 

语法

SELECT [DISTINCT] 列名[, 列名]... FROM 表名

注意事项

*可以替代列名, 表示所有列, 但是通常我们为了提高代码的可读性, 不使用*

DISTINCT为过滤重复记录

如果DISTINCT后面跟多列, 是过滤掉多列合并之后的重复

练习

查询employee表中所有记录

select id,username,gender,birthday,position,salary,resume from user;

查询employee表中所有人的薪水

select username,salary from user;

查询employee表中包含哪些岗位

1.2. 列名表达式

语法

SELECT 列名|表达式[,列名|表达式]... FROM 表名

注意事项

表达式只是显示时起作用, 不会改变数据库中的值

练习

导入student.sql

source .....

查询所有学生的总分

select name,chinese+math+english from student;

查询employee表, 将所有员工薪水*2显示

select username,salary*2 from user;

1.3. AS 

SELECT 列名 AS 别名 FROM 表名

注意事项

起别名时AS可以省略

不会改变数据库中的值

练习

查询所有学生总分, 别名为总分

select name '姓名',chinese+math+english '总分' from student;

1.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 !	非, 将表达式结果取反

1.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;

1.6. COUNT函数

语法

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

注意事项

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

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

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

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

1.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;

1.8. AVG函数

语法

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

注意事项

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

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

1.9. MAX / MIN函数

语法

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

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

注意事项

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

1.10. GROUP BY(重点)

语法

SELECT 列名 FROM 表名 GROUP BY 列名 [HAVING 条件语句]

注意事项

按照某列归类

HAVING和WHERE类似, 但HAVING是作用于组, 其中可以使用组函数

SELECT列表中未包含在组函数中的列名, 只能是GROUP BY中的列名

HAVING中可以使用组函数, WHERE不能.

函数

1.11. 时间函数

注意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');

1.12. 字符串函数

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   '), '--');

1.13. 数学函数

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();