02 MySQL基础（二）

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;

posted @ 2020-09-13 10:04 许大仙阅读(301) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Loading

不为往事扰，余生只爱笑。

02 MySQL基础（二）

1 条件查询

1.1 语法

1.2 分类

1.3 按条件表达式筛选

1.4 按逻辑表达式筛选

1.5 模糊查询

2 排序查询

2.1 语法

2.2 应用示例

3 常见函数

3.1 概念

3.2 好处

3.3 分类

3.4 单行函数之字符函数

3.4.1 length()

3.4.2 concat(str1,str2,……)

3.4.3 upper(str)

3.4.4 lower(str)

3.4.5 substr(str,position,[length])

3.4.6 instr(str,substr)

3.4.7 trim(str)

3.4.8 replace(str,from_str,to_str)

3.4.9 lpad(str,len,padstr)

3.4.10 rpad(str,len,padstr))

3.5 单行函数之数学函数

3.5.1 round(x,d)

3.5.2 ceil(x)

3.5.3 floor(x)

3.5.4 truncate(x,d)

3.5.5 mod(n,m)

3.6 单行函数之日期函数

3.6.1 now()

3.6.2 curdate()

3.6.3 curtime()

3.6.4 获取指定的部分，年、月、日、小时、分钟、秒

3.6.5 str_to_date(str,format)

3.6.6 date_format(date,format)

3.7 单行函数之其他函数

3.7.1 version()

3.7.2 database()

3.7.3 user()

3.8 单行函数之流程控制函数

3.8.1 if(expr1,expr2,expr3)

3.8.2 case

3.9 分组函数

4 分组查询

4.1 语法

4.2 应用示例

5 连接查询

5.1 概念

5.2 准备工作

5.3 笛卡尔积

5.4 连接查询的分类

5.5 SQL92标准

5.5.1 等值连接

5.5.2 非等值连接

5.5.3 自连接

5.6 SQL99标准

5.6.1 语法

5.6.2 内连接

5.6.3 外连接

5.6.4 交叉连接

公告