首页 > 数据库技术 > 详细

02 MySQL基础（二）

时间：2020-09-13 10:44:46      阅读：56      评论：0      收藏：0      [点我收藏+]

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;

02 MySQL基础（二）

原文：https://www.cnblogs.com/xuweiweiwoaini/p/13660425.html
踩
(0)
赞
(0)
   
举报
评论 一句话评论（0）
分享档案
更多>
2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)
最新文章
更多>
教程昨日排行
更多>
友情链接
汇智网   PHP教程   插件网  
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣，分享程序人生！