• 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日');

    image-20200328055527562

    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;
  • 相关阅读:
    Oracle sql的基本优化写法和思路。
    Linux的简单介绍和开发基本运维时候用到的命令
    Nginx的使用（反向代理，负载均衡）
    Mybatis传值为空需要配置JdbcType来解决吗？（XML文件不需要配置JdbcType）
    Mybatis Blob和String互转，实现文件上传等。
    Ckeditor上传图片返回的JS直接显示出来，未执行！！！
    学习中的错误——ubuntu 14.04 LTS 启动eclipse报错
    2016计算机大会后记——机器学习：发展与未来
    2016计算机大会后记——大数据时代的模式识别
    近期编程问题——epoll failed:bad file descriptor
  • 原文地址：https://www.cnblogs.com/xuweiweiwoaini/p/13660425.html
Copyright © 2020-2023  润新知