mysql(二)

一、数据库操作

1.使用order by子句排序查询结果

-- 对grade排序后输出(升序)
select*from Stu
order by grade;
-- 对总分用降序输出
select `name`,(grade+grade1+grade2)as total_grade from Stu
order by total_grade desc;
-- 对姓刘的同学总分以升序输出
select `name`,(grade+grade1+grade2)as total_grade from Stu
where `name` like '刘%' 
order by total_grade;

2.合计/统计函数

count 返回行的总数

select count(*)/count(列名) from 表名

[where...]

-- 统计学生总数
select count(*) from Stu;
-- 统计学生grade大于60的人数
select count(*) from Stu
where grade>60;
-- count(*)与count(列)的区别
-- count(*)返回满足条件的记录的行数
-- count(列) 统计满足条件的某列有多少个，但是辉排除为null的情况


-- sum函数的应用
-- 统计所有学生grade成绩总合
select sum(grade) from Stu;
-- 统计一个班grade平均分
select sum(grade)/count(*) from Stu;


-- avg函数的应用
-- 求grade的平均分
select avg(grade) from Stu;

-- max和min函数分别是求最高分和最低分

3.使用group by子句对列进行分组

select column1,column2,column3...

from table

group by column;

使用having子句对分组后的结果进行过滤

select column1,column2,column3...

from table

group by column having...;

-- group by 用于对查询的结果分组统计

-- having子句用于限制分组显示结果

4.字符串相关函数

charset(str)	返回字符串字符集
concat(string,substring)	连接字符串
instr(string,substring)	返回substring在string中出现的位置，没有则返回0
ucase(string1)	转换成大写
lcase(string1)	转换成小写
left(string1,length)	从string1中的左边起取length个字符
legth(string)	string长度[按照字节]
replace(str,search_str,replace_str)	在str中用replace_str替换search_str
strcmp(string1,string2)	逐字比较凉字串大小
substring(str,posion[,length])	从str的posion开始[从一开始算]，取length个字符
ltrim(string2)  rtrim(string2) trim	去除前端或后端空格

#例：以首字母大写的方式显示所有学生的姓名
select concat(ucase(left(`name`,1)),substring(`name`,2))as Name
from Stu;

5.数学相关函数

abs(num)	绝对值
bin(num)	十进制转二进制
conv(number2,from_base,to_base)	进制转换
ceiling(number2)	向上取整，得到比num2大的最小整数
floor(number2)	向下取整，得到比num2小的最大整数
format(number,decimal_places)	保留小数位数
hex(number)	转十六进制
least(number1,number2.....)	求最小值
mod(number,deno)	求余
rand([seed])	rand([seed])随机数，范围为0-1

注：使用rand([seed])返回随机数，范围为0-1，若seed不变，则随机数也不变。

6.时间日期相关函数

current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回datetime的日期部分
date_add(date2,interval d_value d_type)	在date2中加上日期或时间
date_sub(date2,interval d_value d_type)	在date2中减去日期或时间
datediff(date1,date2)	两个日期差（结果是天）
timediff(date1,date2)	两个时间差（多少小时、分钟、秒）
now()	当前时间
year|month|date(datetime)  from_unixtime()	年月日

-- 日期时间相关函数
-- current_date() 当前日期
select current_date() from dual;
-- current_time() 当前时间
select current_time() from dual;
-- current_timestamp() 当前时间戳
select current_timestamp() from dual;

-- 创建测试表
CREATE TABLE mes(
id IN,
content VARCHAR(30), 
send_time DATETIME);
-- 添加一条记录
INSERT INTO mes
VALUES(1, '北京新闻', CURRENT_TIMESTAMP());
INSERT INTO mes VALUES(2, '上海新闻', NOW());
INSERT INTO mes VALUES(3, '广州新闻', NOW());
SELECT * FROM mes;
SELECT NOW() FROM DUAL;

-- 显示所有新闻信息，发布日期只显示日期，不用显示时间
select id,content,date(send_time)
from mes;
-- 请查询在 10 分钟内发布的新闻
select*from mes
where timediff(now(),send_time)<10;
select*from mes
where current_time()<=date_add(send_time,interval 10 minute);
select*from mes
where date_add(send_time,interval 10 minute)>=current_time();
-- unix_timestamp():返回的是1970-1-1到现在的秒数

7.加密和系统函数

user()	查询用户
database()	数据库名称
md5(str)	为字符串算出一个MD5 32的字符串，（用户密码）加密
passwor(str)	从原文密码str计算并返回密码字符串，通常用于对mysql数据库的用户密码加密

-- 加密函数和系统函数
-- user()查询用户
-- 可以查看登录到mysql的有哪些用户，以及登陆的ip
select user() from dual;
-- database()查询当前使用数据库名称
select database()
-- MD5(str)为字符串算出一个MD5 32的字符串，常用（用户密码）加密
-- 假如root密码是1234，在数据库中存放的是加密后的密码
select md5('12345') from dual;

-- 测试表建立
create table empTest(
id int,
`name` varchar(50) not null default'',
pwd varchar(50) not null default''
);

-- 插入一条记录
inser into empTest
values(1,jack,md5('123'))
select*from empTest
where `name`='jack' and pwd=md5('123');

-- password(str) 加密函数，mysql数据库的用户密码就是password函数加密
select password('123') from dual;

8.流程控制函数

if(expr1,expr2,expr3)	如果expr1为true，则返回expr2，否则返回expr3
ifnull(expr1,expr2)	如果expr1不为空则返回expr1，否则返回expr2
select case when expr1 then expr2 when expr3 then expr4 else expr5 end;(类似多重分支)	若expr1为true，则返回expr2，如果expr3为true返回expr4，否则返回expr5

给empTest添加一列job
alter table empTest
add job varchar(50) not null default'';

-- 2. 如果 emp 表的 job 是 CLERK 则显示 职员，
-- 如果是 MANAGER 则显示经理
-- 如果是 SALESMAN 则显示 销售人员，其它正常显示
select `name`,(select case
when job='CLERK'then'职员'
when job='MANAGER'then'经理'
when job='SALESMAN'then'销售人员'
)as 'job'
from emp;

9.表查询-加强

-- 在mysql中，日期类型可以直接比较
-- like操作符的使用
-- %表示0到多个字符，_表示单个任意字符
-- 显示首位为s的员工姓名和工资
select `name`,sal 
from empTest
where `name` like '%s'
-- 如何显示第二个字符为o的所有员工的姓名和工资
select `name`,sal 
from empTest
where `name` like'_o%'


-- 分页查询
-- 基本语法：select... limit start,rows
-- (表示从start+1行开始取，取出rows行，start从0开始计算)
-- selec*from empTest
-- order by id
-- limit 每页显示的记录数*(第几页-1)，每页显示记录数
-- 分页查询
-- 按雇员的 id 号升序取出， 每页显示 3 条记录，请分别显示 第 1 页，第 2 页，第 3 页
-- 第 1 页
SELECT * FROM emp
ORDER BY id
LIMIT 0, 3; -- 第 2 页
SELECT * FROM emp
ORDER BY id
LIMIT 3, 3; -- 第 3 页
SELECT * FROM emp
ORDER BY id
LIMIT 6, 3;

10.使用分组函数和分组子句group by

-- 显示每种岗位的雇员总数、平均工资
SELECT COUNT(*), AVG(sal), job
FROM emp
GROUP BY job;


-- 给empTest添加一列comm，假如职员没有获得补助则为null，获得补助则不为null
alter table empTest
add comm varchar(50);

-- 统计没有获得补助的人数
select count(if(comm is null,1,null))
from empTest;
select count(*),count(*)-count(comm)
from empTest;  -- count()不会统计null

-- 显示岗位总数
select count(distinct job)
from empTest;

        如果select语句同时包含有group by,having,limit,order by那么他们的顺序是group by,having,order by,limit.

select column1,column2,column3...from table

group by column

having condition

order by column

limit start,rows;