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函数返回满足where条件的行,只对数值起作用,否则会报错
-- 数学总成绩
select sum(math) from student;
-- 各科的总成绩
select sum(math),sum(english),sum(chinese) from student;
-- 统计语文成绩的平均分
select sum(chinese)/count(*) from student;
返回满足where条件的一列的平均值,也是只对数值才有意义
select avg(chinese) from student;
-- max,min
select max(math+chinese+english),min(math+chinese+english) from student;
select column1,colum2 from table group by column
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);
-- 显示平均工资低于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 ');