1 条件查询
1.1 语法
SELECT 查询列表
FROM 表名
WHERE 条件表达式;
1.2 分类
- ①按条件表达式筛选:条件运算符:>、<、=、<>、!=、>=、<=。
- ②按逻辑表达式筛选:逻辑运算符:&&(and)、||(or)、!(not)。
- ③模糊查询:like、between and、in、is null、is not null。
1.3 按条件表达式筛选
- 查询员工工资>12000的员工信息。
SELECT
*
FROM
employees
WHERE
salary > 12000;
- 查询部门编号不等于90号的员工名和部门编号。
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id != 90;
1.4 按逻辑表达式筛选
- 查询工资在10000到20000之间的员工名、工资以及奖金。
SELECT
last_name AS '员工名',
salary AS '工资',
commission_pct AS '奖金'
FROM
employees
WHERE
salary >= 10000
AND salary <= 20000;
- 查询部门编号不是在90到110之间,或者工资高于15000的员工信息。
SELECT
*
FROM
employees
WHERE
( department_id < 90 OR department_id > 110 )
OR ( salary > 15000 );
SELECT
*
FROM
employees
WHERE
NOT ( department_id >= 90 AND department_id <= 110 )
OR ( salary > 15000 );
1.5 模糊查询
- 查询员工名中包含字符a的员工信息。
SELECT
*
FROM
employees
WHERE
last_name LIKE '%a%';
- 查询员工名中第二个字符为_的员工信息。
SELECT
*
FROM
employees
WHERE
last_name like '_\_%'
- 查询工资在10000到20000之间的员工名、工资以及奖金
SELECT
last_name AS '员工名',
salary AS '工资',
commission_pct AS '奖金'
FROM
employees
WHERE
salary BETWEEN 10000
AND 20000;
- 查询员工的工种编号是IT_PROG、AD_VP的员工信息。
SELECT
*
FROM
employees
WHERE
job_id IN ( 'IT_PROG', 'AD_VP' );
- 查询没有奖金的员工信息。
SELECT
*
FROM
employees
WHERE
commission_pct IS NULL;
2 排序查询
2.1 语法
SELECT 查询列表
FROM 表名
WHERE 条件表达式
ORDER BY 排序列表(字段 [asc],字段 [desc],……);
2.2 应用示例
- 查询员工信息,要求工资从高到低排序。
SELECT
*
FROM
employees
ORDER BY
salary DESC;
- 查询部门编号>=90的员工信息,按入职时间的先后进行排序。
SELECT
*
FROM
employees
WHERE
department_id >= 90
ORDER BY
hiredate ASC;
- 按年薪的高低显示员工的信息和年薪。
SELECT
*,
salary * 12 * ( IFNULL( commission_pct, 0 ) + 1 ) AS '年薪'
FROM
employees
ORDER BY
年薪 ASC;
- 查询员工信息,要求先按工资排序,再按员工编号排序。
SELECT
*
FROM
employees
ORDER BY
salary,
employee_id;
3 常见函数
3.1 概念
- 函数类似于java中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名。
3.2 好处
- 隐藏了实现细节。
- 提高代码的重用性。
3.3 分类
- 单行函数:将一个数据进行处理,返回一个值,如length()、concat()等。
- 分组函数:将虚拟表看做一个组,处理一组数据,返回一个值。
3.4 单行函数之字符函数
3.4.1 length()
- 作用:获取参数值的字节个数。
- 示例:
SELECT LENGTH( 'john' );
SELECT
LENGTH( '张三丰hahaha' ); -- 15
3.4.2 concat(str1,str2,……)
- 作用:拼接字符串。
- 示例:
SELECT
CONCAT( last_name, '_', first_name ) AS '姓名'
FROM
employees;
3.4.3 upper(str)
- 作用:将字符变为大写。
- 示例:
SELECT
UPPER(last_name)
FROM
employees;
3.4.4 lower(str)
- 作用:将字符变为小写。
- 示例:
SELECT
LOWER( last_name )
FROM
employees;
3.4.5 substr(str,position,[length])
- 作用:截取字符。
- 示例:
-- 截取从指定索引处后面所有字符
SELECT
SUBSTR( '李莫愁爱上了陆展元', 7 );
-- 截取从指定索引处指定字符长度的字符
SELECT
SUBSTR( '李莫愁爱上了陆展元', 1,3 );
3.4.6 instr(str,substr)
- 作用:用于返回子串在大串中的第一次出现的索引,如果找不到返回0。
- 示例:
SELECT
INSTR( '杨不悔爱上了殷六侠', '殷六侠' );
3.4.7 trim(str)
- 作用:去除左右空格。
- 示例:
SELECT
trim( ' 杨不悔爱上了殷六侠 ' );
3.4.8 replace(str,from_str,to_str)
- 作用:替换。
- 示例:
SELECT
REPLACE ( '杨不悔爱上了殷六侠', '爱上了', '怎么可能爱上' );
3.4.9 lpad(str,len,padstr)
- 作用:用指定的字符实现左填充指定长度。
- 示例:
SELECT
LPAD( '杨不悔爱上了殷六侠', 20, '*' );
3.4.10 rpad(str,len,padstr))
- 作用:用指定的字符实现右填充指定长度。
- 示例:
SELECT
RPAD( '杨不悔爱上了殷六侠', 20, '*' );
3.5 单行函数之数学函数
3.5.1 round(x,d)
- 作用:四舍五入。
- 示例:
SELECT
ROUND(1.65)
SELECT
ROUND(1.45)
SELECT
ROUND(1.567,2)
3.5.2 ceil(x)
- 作用:向上取整。
- 示例:
SELECT
CEIL(1.11)
3.5.3 floor(x)
- 作用:向下取整。
- 示例:
SELECT
FLOOR(1.567)
3.5.4 truncate(x,d)
- 作用:截断。
- 示例:
SELECT
TRUNCATE(1.567,2)
3.5.5 mod(n,m)
- 作用:取余。mod(a,b)=a-a/b*b。
- 示例:
SELECT
MOD(3,1)
3.6 单行函数之日期函数
3.6.1 now()
- 作用:返回当前系统日期+时间。
- 示例:
select NOW();
3.6.2 curdate()
- 作用:返回当前系统日期。
- 示例:
select CURDATE()
3.6.3 curtime()
- 作用:返回当前时间。
- 示例:
select CURTIME();
3.6.4 获取指定的部分,年、月、日、小时、分钟、秒
- 示例:
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
3.6.5 str_to_date(str,format)
- 作用:将日期格式的字符转换成指定格式的日期。
- 示例:
SELECT STR_TO_DATE('9-13-1999','%m-%d-%y');
3.6.6 date_format(date,format)
- 作用:将日期转换为字符。
- 示例:
SELECT DATE_FORMAT(NOW(),'%Y年-%m月-%d日');
3.7 单行函数之其他函数
3.7.1 version()
- 作用:显示当前数据库的版本。
- 示例:
SELECT version();
3.7.2 database()
- 作用:显示当前在那个数据库中。
- 示例:
SELECT DATABASE ();
3.7.3 user()
- 作用:显示当前登录的用户。
- 示例:
SELECT USER();
3.8 单行函数之流程控制函数
3.8.1 if(expr1,expr2,expr3)
- 作用:如果expr1是true,则返回expr2,否则返回expr3。
- 示例:
SELECT IF(10 > 5,'大','小');
3.8.2 case
- 作用:类似于java中的switch-case语句或if-else语句。
语法:
类似于java中的switch-case语句。
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
……
else 要显示的值n或语句n;
end;
类似于java中的if-else语句。
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else 要显示的值n或语句n
end;
示例:
SELECT
*,
CASE
commission_pct
WHEN NULL THEN '没有奖金'
ELSE '有奖金'
END
FROM
employees;
3.9 分组函数
- 常见的分组函数是:
- avg():求平均值。
- count():求总数。
- max():求最大值。
- min():求最小值。
- sum():求和。
- 特点:
- 一般而言,sum和avg用于处理数值型。max、min、count可以处理任何类型。
- avg、count、max、min、sum都忽略null值。
- count函数一般单独使用,一般使用count(*)来统计行数。
- 和分组函数一同查询的字段要求是group by后的字段。
- 示例:
SELECT
avg( salary ), -- 平均值
count(*), -- 总数
MAX( salary ), -- 最大值
MIN( salary ), -- 最小值
sum( salary ) -- 求和
FROM
employees;
4 分组查询
4.1 语法
SELECT 分组函数,列[要求出现在group by后面]
FROM 表名
WHERE 条件表达式
group by 分组表达式
having 分组条件表达式
ORDER BY 排序列表(字段 [asc],字段 [desc],……);
4.2 应用示例
- 查询每个部门的平均工资。
SELECT
avg( salary ) ,department_id
FROM
employees
GROUP BY
department_id;
- 查询每个工种的最高工资。
SELECT
max( salary ),
job_id
FROM
employees
GROUP BY
job_id;
- 查询每个位置上的部门个数。
SELECT
count(*),
location_id
FROM
departments
GROUP BY
location_id;
- 邮箱中包含a字符的,每个部门的平均工资。
SELECT
AVG( salary ),
department_id
FROM
employees
WHERE
email LIKE '%a%'
GROUP BY
department_id;
- 查询有奖金的每个领导手下员工的最高工资。
SELECT
max(salary),manager_id
from employees
where commission_pct is not null
GROUP BY manager_id;
- 查询那个部门的员工个数>2。
SELECT
department_id,
count(*) AS `count`
FROM
employees
GROUP BY
department_id
HAVING
`count` > 2
- 查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资。
SELECT
job_id,
max( salary ) AS `max`
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
`max` > 12000;
- 按员工姓名的长度分组,查询每一组的员工个数,筛选出员工个数>5的。
SELECT
count(*) ,LENGTH( last_name )
FROM
employees
GROUP BY
LENGTH( last_name )
HAVING
count(*) > 5;
- 查询每个部门每个工种的员工的平均工资。
SELECT
AVG( salary ),
department_id,
job_id
FROM
employees
GROUP BY
department_id,
job_id;
5 连接查询
5.1 概念
- 连接查询,又称为多表查询,当查询的子弹来自于多个表时,就会用到连接查询。
5.2 准备工作
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
DROP TABLE IF EXISTS `beauty`;
CREATE TABLE `beauty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`sex` char(1) DEFAULT '女',
`borndate` datetime DEFAULT '1987-01-01 00:00:00',
`phone` varchar(11) NOT NULL,
`photo` blob,
`boyfriend_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1);
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);
5.3 笛卡尔积
- 笛卡尔积产生的条件:
- ①省略连接条件。
- ②连接条件无效。
- ③所有表中的所有行互相连接。
- 为了避免笛卡尔积,可以在WHERE加入有效的连接条件。
- 示例:笛卡尔积:
SELECT
`name`,
boyName
FROM
beauty,
boys;
5.4 连接查询的分类
- 按年代分类:
- SQL92标准。
- SQL99标准(推荐)。
- 按功能分类:
- 内连接:
- 等值连接。
- 非等值连接。
- 自连接。
- 外连接。
- 左外连接。
- 左右连接。
- 全外连接(MySQL不支持)。
- 交叉连接。
- 内连接:
5.5 SQL92标准
5.5.1 等值连接
- 示例:查询女神名和其对应的男神名。
SELECT
`name`,
boyName
FROM
beauty,
boys
WHERE
beauty.boyfriend_id = boys.id;
- 示例:查询员工名和其对应的部门名。
SELECT
last_name,
department_name
FROM
employees,
departments
WHERE
employees.department_id = departments.department_id;
- 查询有奖金的员工名、部门名。
SELECT
e.last_name,
d.department_name
FROM
employees e,
departments d
WHERE
e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
5.5.2 非等值连接
- 工资等级的sql:
CREATE TABLE job_grades ( grade_level VARCHAR ( 3 ), lowest_sal INT, highest_sal INT );
INSERT INTO job_grades
VALUES
( 'A', 1000, 2999 );
INSERT INTO job_grades
VALUES
( 'B', 3000, 5999 );
INSERT INTO job_grades
VALUES
( 'C', 6000, 9999 );
INSERT INTO job_grades
VALUES
( 'D', 10000, 14999 );
INSERT INTO job_grades
VALUES
( 'E', 15000, 24999 );
INSERT INTO job_grades
VALUES
( 'F', 25000, 40000 );
- 示例:查询员工的工资等级
SELECT
e.salary,
jg.grade_level
FROM
employees e,
job_grades jg
WHERE
e.salary BETWEEN jg.lowest_sal
AND jg.highest_sal;
5.5.3 自连接
- 查询员工的名称和其上级的名称。
SELECT
e.last_name as last_name ,
m.last_name as manager_name
FROM
employees as e,
employees as m
WHERE
e.manager_id = m.employee_id;
5.6 SQL99标准
5.6.1 语法
SELECT 查询列表
FROM 表1 别名 [连接类型 inner|left|right] join 表2 别名
ON 连接条件
WHERE 筛选条件
group by 分组字段
having 分组筛选条件
order by 排序列表;
- 备注:
- 内连接:inner join
- 外连接:
- 左外连接:left [outer] join
- 右外连接:right [outer] join
- 全外连接:full [outer] join
- 交叉连接:cross join
5.6.2 内连接
- 示例:查询女神名和其对应的男神名。
SELECT
`name`,
boyName
FROM
beauty
INNER JOIN boys
ON beauty.boyfriend_id = boys.id;
- 示例:查询员工的工资等级。
SELECT
e.salary,
jg.grade_level
FROM
employees e
INNER JOIN job_grades jg ON e.salary BETWEEN jg.lowest_sal
AND jg.highest_sal;
- 示例:查询员工的名称和其上级的名称。
SELECT
e.last_name AS last_name,
m.last_name AS manager_name
FROM
employees AS e
INNER JOIN employees AS m ON e.manager_id = m.employee_id;
5.6.3 外连接
- 示例:查询没有男朋友的女神名。
SELECT
beauty.`name`,
boys.boyName
FROM
beauty
LEFT JOIN boys ON beauty.boyfriend_id = boys.id
WHERE
boys.boyName IS NULL;
5.6.4 交叉连接
- 示例:
SELECT
beauty.`name`,
boys.boyName
FROM
beauty
CROSS JOIN boys;