mysql函数

count函数

count返回行的总数：

select count(*)|count(列名) from table_name where……

-- 统计一个班有多少个学生
select count(*) from student;
-- 统计数学成绩大于90分有多少个
select count(*) from student where math>90;
-- 统计总分大于250分的人有多少个
select count(*) from student where (chinese+math+english) > 250;
-- count（*）count(列)的区别
-- 解释：count(*) 返回满足条件的记录的行数
-- count(列)：统计满足条件的某列有多少个，但是会排除为null

sum合计函数

sum函数返回满足where条件的行，只对数值起作用，否则会报错

-- 数学总成绩
select sum(math) from student;
-- 各科的总成绩
select sum(math),sum(english),sum(chinese) from student;
-- 统计语文成绩的平均分
select sum(chinese)/count(*) from student;

avg函数

返回满足where条件的一列的平均值，也是只对数值才有意义

select avg(chinese) from student;

Max，min

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

分组查询

• 使用group by子句对列进行分组
  • select column1,colum2 from table group by column
• 使用having子句对分组后的结果进行过滤
  • select column1,colum2 from table group by column having...

group by用于对查询的结果分组统计，having子句用于限制分组显示结果

初始化表

use mmysql;

create table dept(
	-- 部门表
	deptno mediumint unsigned not null default 0,
	dname varchar(20) not null default "",
	loc varchar(13) not null default ""
);

insert into dept values(10,'ACCONTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON');

# 创建表EMP雇员工
CREATE TABLE `emp` (
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `ename` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '""',
  `job` varchar(9) COLLATE utf8_bin NOT NULL DEFAULT '""',
  `mgr` mediumint(8) unsigned DEFAULT NULL,
  `hiredate` date NOT NULL,
  `sal` decimal(7,2) NOT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` mediumint(8) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),
    (7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),
    (7521,'WARD','SALESMAN',7968,'1991-2-22',1250.00,500.00,30),
    (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
    (7654,'MARTIN','SALESMAN',7968,'1991-9-28',1250.00,1400.00,30),
    (7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),
    (7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),
    (7788,'SCOTT','ANALYST',7566,'1991-4-19',3000.00,NULL,20),
    (7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
    (7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),
    (7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),
    (7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),
    (7934,'MILLER','CLERK',7782,'1991-1-23',1300.00,NULL,10);
-- 工资级别
CREATE TABLE salgrade(
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    losal DECIMAL(17,2) NOT NULL,
    hisal DECIMAL(17,2) NOT NULL
);
INSERT INTO salgrade VALUES(1,700,1200),
    (2,1201,1400),
    (3,1401,2000),
    (4,2001,3000),
    (5,3001,9999);

1. 如何显示每个部门的平均工资和最高工资？
2. 如何显示每个部门的每种岗位的平均工资和最低工资
3. 显示平均工资低于2000的部门和他的平均工资

  -- 显示平均工资低于2000 的部门号和他的平均工资
  select avg(sal),deptno from emp group by deptno having avg(sal) < 2000;

字符串函数

常用的字符串函数

练习：以首字母小写的方式显示所有员工emp的姓名str.sql

select concat(ename,'工作是',job) from emp;

select charset(ename) from emp;

数学函数

 select abs(-10) from dual; 
 select bin(10) from dual;
 select ceiling (3.2) from dual;-- 向上取整
 select floor (3.9) from dual; -- 向下取整
 

时间相关函数

时间戳——日期+时间

加密函数

1、PASSWORD()：创建一个经过加密的密码字符串，适合于插入到MySQL的安全系
统。该加密过程不可逆，和unix密码加密过程使用不同的算法。主要用于MySQL的认证系统。

2、ENCRYPT(，)：使用UNIX crypt()系统加密字符串，ENCRYPT()函数接收要加密的字符串和（可选的）用于加密过程的salt（一个可以唯一确定口令的字符串，就像钥匙一样），注意，windows上不支持

3、ENCODE(,) DECODE(,)：加密解密字符串。该函数有两个参数：被加密或解密的字符串和作为加密或解密基础的密钥。Encode结果是一个二进制字符串，以BLOB类型存储。加密程度相对比较弱

加密 INSERT INTO users (username, password) VALUES (‘joe’, ENCODE(‘guessme’, ‘abracadabra’));

解密 SELECT DECODE(password, ‘abracadabra’) FROM users WHERE username=‘joe’;

4、MD5()：计算字符串的MD5校验和（128位）

SHA5()：计算字符串的SHA5校验和（160位）

以上两个函数返回的校验和是16进制的，适合与认证系统中使用的口令。

5、AES_ENCRYPT AES_DECRYPT示例

insert into users(test) values(AES_ENCRYPT(‘teststr’,‘salt’))；

select AES_DECRYPT(test,‘salt’) from users；

ps：需要Linux 且 AES_ENCRYPT 加密结果最好也以BLOB类型存储

加密 select aes_encrypt(name, 'password ');
解密 select aes_decrypt(aes_encrypt(name, 'password '), 'password ');