1 条件查询

1.1 语法

  1. SELECT 查询列表
  2. FROM 表名
  3. WHERE 条件表达式;

1.2 分类

  • ①按条件表达式筛选：条件运算符：>、<、=、<>、!=、>=、<=。
  • ②按逻辑表达式筛选：逻辑运算符：&&（and）、||（or）、!（not）。
  • ③模糊查询：like、between and、in、is null、is not null。

1.3 按条件表达式筛选

  • 查询员工工资>12000的员工信息。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. WHERE
  6.     salary > 12000;
  • 查询部门编号不等于90号的员工名和部门编号。
  1. SELECT
  2.     last_name,
  3.     department_id 
  4. FROM
  5.     employees 
  6. WHERE
  7.     department_id != 90;

1.4 按逻辑表达式筛选

  • 查询工资在10000到20000之间的员工名、工资以及奖金。
  1. SELECT
  2.     last_name AS '员工名',
  3.     salary AS '工资',
  4.     commission_pct AS '奖金' 
  5. FROM
  6.     employees 
  7. WHERE
  8.     salary >= 10000 
  9.     AND salary <= 20000;
  • 查询部门编号不是在90到110之间，或者工资高于15000的员工信息。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. WHERE
  6.     ( department_id < 90 OR department_id > 110 ) 
  7.     OR ( salary > 15000 );
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. WHERE
  6.     NOT ( department_id >= 90 AND department_id <= 110 ) 
  7.     OR ( salary > 15000 );

1.5 模糊查询

  • 查询员工名中包含字符a的员工信息。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. WHERE
  6.     last_name LIKE '%a%';
  • 查询员工名中第二个字符为_的员工信息。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. WHERE
  6.     last_name like '_\_%'
  • 查询工资在10000到20000之间的员工名、工资以及奖金
  1. SELECT
  2.     last_name AS '员工名',
  3.     salary AS '工资',
  4.     commission_pct AS '奖金' 
  5. FROM
  6.     employees 
  7. WHERE
  8.     salary BETWEEN 10000 
  9.     AND 20000;
  • 查询员工的工种编号是IT_PROG、AD_VP的员工信息。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. WHERE
  6.     job_id IN ( 'IT_PROG', 'AD_VP' );
  • 查询没有奖金的员工信息。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. WHERE
  6.     commission_pct IS NULL;

2 排序查询

2.1 语法

  1. SELECT 查询列表
  2. FROM 表名
  3. WHERE 条件表达式
  4. ORDER BY 排序列表(字段 [asc],字段 [desc],……);

2.2 应用示例

  • 查询员工信息，要求工资从高到低排序。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. ORDER BY
  6.     salary DESC;
  • 查询部门编号>=90的员工信息，按入职时间的先后进行排序。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. WHERE
  6.     department_id >= 90 
  7. ORDER BY
  8.     hiredate ASC;
  • 按年薪的高低显示员工的信息和年薪。
  1. SELECT
  2.     *,
  3.     salary * 12 * ( IFNULL( commission_pct, 0 ) + 1 ) AS '年薪' 
  4. FROM
  5.     employees 
  6. ORDER BY
  7.     年薪 ASC;
  • 查询员工信息，要求先按工资排序，再按员工编号排序。
  1. SELECT
  2.     * 
  3. FROM
  4.     employees 
  5. ORDER BY
  6.     salary,
  7.     employee_id;

3 常见函数

3.1 概念

  • 函数类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。

3.2 好处

  • 隐藏了实现细节。
  • 提高代码的重用性。

3.3 分类

  • 单行函数：将一个数据进行处理，返回一个值，如length()、concat()等。
  • 分组函数：将虚拟表看做一个组，处理一组数据，返回一个值。

3.4 单行函数之字符函数

3.4.1 length()

  • 作用：获取参数值的字节个数。
  • 示例：
  1. SELECT LENGTH( 'john' );
  1. SELECT
  2.     LENGTH( '张三丰hahaha' ); -- 15

3.4.2 concat(str1,str2,……)

  • 作用：拼接字符串。
  • 示例：
  1. SELECT
  2.     CONCAT( last_name, '_', first_name ) AS '姓名' 
  3. FROM
  4.     employees;

3.4.3 upper(str)

  • 作用：将字符变为大写。
  • 示例：
  1. SELECT
  2.     UPPER(last_name)
  3. FROM
  4.     employees;

3.4.4 lower(str)

  • 作用：将字符变为小写。
  • 示例：
  1. SELECT
  2.     LOWER( last_name ) 
  3. FROM
  4.     employees;

3.4.5 substr(str,position,[length])

  • 作用：截取字符。
  • 示例：
  1. -- 截取从指定索引处后面所有字符
  2. SELECT
  3.     SUBSTR( '李莫愁爱上了陆展元', 7 );
  1. -- 截取从指定索引处指定字符长度的字符
  2. SELECT
  3.     SUBSTR( '李莫愁爱上了陆展元', 1,3 );

3.4.6 instr(str,substr)

  • 作用：用于返回子串在大串中的第一次出现的索引，如果找不到返回0。
  • 示例：
  1. SELECT
  2.     INSTR( '杨不悔爱上了殷六侠', '殷六侠' );

3.4.7 trim(str)

  • 作用：去除左右空格。
  • 示例：
  1. SELECT
  2.     trim( '           杨不悔爱上了殷六侠           ' );

3.4.8 replace(str,from_str,to_str)

  • 作用：替换。
  • 示例：
  1. SELECT 
  2. REPLACE ( '杨不悔爱上了殷六侠', '爱上了', '怎么可能爱上' );

3.4.9 lpad(str,len,padstr)

  • 作用：用指定的字符实现左填充指定长度。
  • 示例：
  1. SELECT
  2.     LPAD( '杨不悔爱上了殷六侠', 20, '*' );

3.4.10 rpad(str,len,padstr))

  • 作用：用指定的字符实现右填充指定长度。
  • 示例：
  1. SELECT
  2.     RPAD( '杨不悔爱上了殷六侠', 20, '*' );

3.5 单行函数之数学函数

3.5.1 round(x,d)

  • 作用：四舍五入。
  • 示例：
  1. SELECT
  2.     ROUND(1.65)
  1. SELECT
  2.     ROUND(1.45)
  1. SELECT
  2.     ROUND(1.567,2)

3.5.2 ceil(x)

  • 作用：向上取整。
  • 示例：
  1. SELECT
  2.     CEIL(1.11)

3.5.3 floor(x)

  • 作用：向下取整。
  • 示例：
  1. SELECT
  2.     FLOOR(1.567)

3.5.4 truncate(x,d)

  • 作用：截断。
  • 示例：
  1. SELECT
  2.     TRUNCATE(1.567,2)

3.5.5 mod(n,m)

  • 作用：取余。mod(a,b)=a-a/b*b。
  • 示例：
  1. SELECT
  2.     MOD(3,1)

3.6 单行函数之日期函数

3.6.1 now()

  • 作用：返回当前系统日期+时间。
  • 示例：
  1. select NOW();

3.6.2 curdate()

  • 作用：返回当前系统日期。
  • 示例：
  1. select CURDATE()

3.6.3 curtime()

  • 作用：返回当前时间。
  • 示例：
  1. select CURTIME();

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

  • 示例：
  1. SELECT YEAR(NOW());
  2. SELECT MONTH(NOW());
  3. SELECT DAY(NOW());
  4. SELECT HOUR(NOW());
  5. SELECT MINUTE(NOW());
  6. SELECT SECOND(NOW());

3.6.5 str_to_date(str,format)

  • 作用：将日期格式的字符转换成指定格式的日期。
  • 示例：
  1. SELECT STR_TO_DATE('9-13-1999','%m-%d-%y');

3.6.6 date_format(date,format)

  • 作用：将日期转换为字符。
  • 示例：
  1. SELECT DATE_FORMAT(NOW(),'%Y年-%m月-%d日');

20200328055712.png

3.7 单行函数之其他函数

3.7.1 version()

  • 作用：显示当前数据库的版本。
  • 示例：
  1. SELECT version();

3.7.2 database()

  • 作用：显示当前在那个数据库中。
  • 示例：
  1. SELECT DATABASE ();

3.7.3 user()

  • 作用：显示当前登录的用户。
  • 示例：
  1. SELECT USER();

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

3.8.1 if(expr1,expr2,expr3)

  • 作用：如果expr1是true，则返回expr2，否则返回expr3。
  • 示例：
  1. SELECT IF(10 > 5,'大','小');

3.8.2 case

  • 作用：类似于java中的switch-case语句或if-else语句。

  • 语法：

    • 类似于java中的switch-case语句。

      1. case 要判断的字段或表达式
      2. when 常量1 then 要显示的值1或语句1;
      3. when 常量2 then 要显示的值2或语句2;
      4. ……
      5. else 要显示的值n或语句n;
      6. end;

    • 类似于java中的if-else语句。

      1. case 
      2. when 条件1 then 要显示的值1或语句1
      3. when 条件2 then 要显示的值2或语句2
      4. ……
      5. else 要显示的值n或语句n
      6. end;

  • 示例：

  1. SELECT
  2.     *,
  3.     CASE
  4.         commission_pct 
  5.     WHEN NULL THEN '没有奖金' 
  6.     ELSE '有奖金' 
  7.     END 
  8. FROM
  9.     employees;

3.9 分组函数

  • 常见的分组函数是：
    • avg()：求平均值。
    • count()：求总数。
    • max()：求最大值。
    • min()：求最小值。
    • sum()：求和。
  • 特点：
    • 一般而言，sum和avg用于处理数值型。max、min、count可以处理任何类型。
    • avg、count、max、min、sum都忽略null值。
    • count函数一般单独使用，一般使用count(*)来统计行数。
    • 和分组函数一同查询的字段要求是group by后的字段。
  • 示例：
  1. SELECT
  2.     avg( salary ), -- 平均值
  3.     count(*),  -- 总数
  4.     MAX( salary ), -- 最大值
  5.     MIN( salary ),　-- 最小值
  6.     sum( salary )  -- 求和
  7. FROM
  8.     employees;

4 分组查询

4.1 语法

  1. SELECT 分组函数,列[要求出现在group by后面]
  2. FROM 表名
  3. WHERE 条件表达式
  4. group by 分组表达式
  5. having 分组条件表达式
  6. ORDER BY 排序列表(字段 [asc],字段 [desc],……);

4.2 应用示例

  • 查询每个部门的平均工资。
  1. SELECT
  2.     avg( salary ) ,department_id
  3. FROM
  4.     employees 
  5. GROUP BY
  6.     department_id;
  • 查询每个工种的最高工资。
  1. SELECT
  2.     max( salary ),
  3.     job_id 
  4. FROM
  5.     employees 
  6. GROUP BY
  7.     job_id;
  • 查询每个位置上的部门个数。
  1. SELECT
  2.     count(*),
  3.     location_id 
  4. FROM
  5.     departments 
  6. GROUP BY
  7.     location_id;
  • 邮箱中包含a字符的，每个部门的平均工资。
  1. SELECT
  2.     AVG( salary ),
  3.     department_id 
  4. FROM
  5.     employees 
  6. WHERE
  7.     email LIKE '%a%' 
  8. GROUP BY
  9.     department_id;
  • 查询有奖金的每个领导手下员工的最高工资。
  1. SELECT
  2.     max(salary),manager_id
  3. from employees
  4. where commission_pct is not null
  5. GROUP BY manager_id;
  • 查询那个部门的员工个数>2。
  1. SELECT
  2.     department_id,
  3.     count(*) AS `count` 
  4. FROM
  5.     employees 
  6. GROUP BY
  7.     department_id 
  8. HAVING
  9.     `count` > 2
  • 查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资。
  1. SELECT
  2.     job_id,
  3.     max( salary ) AS `max` 
  4. FROM
  5.     employees 
  6. WHERE
  7.     commission_pct IS NOT NULL 
  8. GROUP BY
  9.     job_id 
  10. HAVING
  11.     `max` > 12000;
  • 按员工姓名的长度分组，查询每一组的员工个数，筛选出员工个数>5的。
  1. SELECT
  2.     count(*) ,LENGTH( last_name ) 
  3. FROM
  4.     employees 
  5. GROUP BY
  6.     LENGTH( last_name ) 
  7. HAVING
  8.     count(*) > 5;
  • 查询每个部门每个工种的员工的平均工资。
  1. SELECT
  2.     AVG( salary ),
  3.     department_id,
  4.     job_id 
  5. FROM
  6.     employees 
  7. GROUP BY
  8.     department_id,
  9.     job_id;

5 连接查询

5.1 概念

  • 连接查询，又称为多表查询，当查询的子弹来自于多个表时，就会用到连接查询。

5.2 准备工作

  1. DROP TABLE IF EXISTS `admin`;
  3. CREATE TABLE `admin` (
  4.   `id` int(11) NOT NULL AUTO_INCREMENT,
  5.   `username` varchar(10) NOT NULL,
  6.   `password` varchar(10) NOT NULL,
  7.   PRIMARY KEY (`id`)
  8. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  10. insert  into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
  12. DROP TABLE IF EXISTS `beauty`;
  14. CREATE TABLE `beauty` (
  15.   `id` int(11) NOT NULL AUTO_INCREMENT,
  16.   `name` varchar(50) NOT NULL,
  17.   `sex` char(1) DEFAULT '女',
  18.   `borndate` datetime DEFAULT '1987-01-01 00:00:00',
  19.   `phone` varchar(11) NOT NULL,
  20.   `photo` blob,
  21.   `boyfriend_id` int(11) DEFAULT NULL,
  22.   PRIMARY KEY (`id`)
  23. ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
  25. 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);
  27. DROP TABLE IF EXISTS `boys`;
  29. CREATE TABLE `boys` (
  30.   `id` int(11) NOT NULL AUTO_INCREMENT,
  31.   `boyName` varchar(20) DEFAULT NULL,
  32.   `userCP` int(11) DEFAULT NULL,
  33.   PRIMARY KEY (`id`)
  34. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  36. insert  into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300);

5.3 笛卡尔积

  • 笛卡尔积产生的条件：
    • ①省略连接条件。
    • ②连接条件无效。
    • ③所有表中的所有行互相连接。
  • 为了避免笛卡尔积，可以在WHERE加入有效的连接条件。
  • 示例：笛卡尔积：
  1. SELECT
  2.     `name`,
  3.     boyName 
  4. FROM
  5.     beauty,
  6.     boys;

5.4 连接查询的分类

  • 按年代分类：
    • SQL92标准。
    • SQL99标准（推荐）。
  • 按功能分类：
    • 内连接：
      • 等值连接。
      • 非等值连接。
      • 自连接。
    • 外连接。
      • 左外连接。
      • 左右连接。
      • 全外连接（MySQL不支持）。
    • 交叉连接。

5.5 SQL92标准

5.5.1 等值连接

  • 示例：查询女神名和其对应的男神名。
  1. SELECT
  2.     `name`,
  3.     boyName 
  4. FROM
  5.     beauty,
  6.     boys 
  7. WHERE
  8.     beauty.boyfriend_id = boys.id;
  • 示例：查询员工名和其对应的部门名。
  1. SELECT
  2.     last_name,
  3.     department_name 
  4. FROM
  5.     employees,
  6.     departments 
  7. WHERE
  8.     employees.department_id = departments.department_id;
  • 查询有奖金的员工名、部门名。
  1. SELECT
  2.     e.last_name,
  3.     d.department_name 
  4. FROM
  5.     employees e,
  6.     departments d 
  7. WHERE
  8.     e.department_id = d.department_id 
  9.     AND e.commission_pct IS NOT NULL;

5.5.2 非等值连接

  • 工资等级的sql:
  1. CREATE TABLE job_grades ( grade_level VARCHAR ( 3 ), lowest_sal INT, highest_sal INT );
  2. INSERT INTO job_grades
  3. VALUES
  4.     ( 'A', 1000, 2999 );
  5. INSERT INTO job_grades
  6. VALUES
  7.     ( 'B', 3000, 5999 );
  8. INSERT INTO job_grades
  9. VALUES
  10.     ( 'C', 6000, 9999 );
  11. INSERT INTO job_grades
  12. VALUES
  13.     ( 'D', 10000, 14999 );
  14. INSERT INTO job_grades
  15. VALUES
  16.     ( 'E', 15000, 24999 );
  17. INSERT INTO job_grades
  18. VALUES
  19.     ( 'F', 25000, 40000 );
  • 示例：查询员工的工资等级
  1. SELECT
  2.     e.salary,
  3.     jg.grade_level 
  4. FROM
  5.     employees e,
  6.     job_grades jg 
  7. WHERE
  8.     e.salary BETWEEN jg.lowest_sal 
  9.     AND jg.highest_sal;

5.5.3 自连接

  • 查询员工的名称和其上级的名称。
  1. SELECT
  2.     e.last_name as last_name ,
  3.     m.last_name as  manager_name
  4. FROM
  5.     employees as e,
  6.     employees as m 
  7. WHERE
  8.     e.manager_id = m.employee_id;

5.6 SQL99标准

5.6.1 语法

  1. SELECT 查询列表
  2. FROM 表1 别名 [连接类型 inner|left|right] join 表2 别名 
  3. ON 连接条件
  4. WHERE 筛选条件
  5. group by 分组字段
  6. having 分组筛选条件
  7. order by 排序列表;
  • 备注：
    • 内连接：inner join
    • 外连接：
      • 左外连接：left [outer] join
      • 右外连接：right [outer] join
      • 全外连接：full [outer] join
    • 交叉连接：cross join

5.6.2 内连接

  • 示例：查询女神名和其对应的男神名。
  1. SELECT
  2.     `name`,
  3.     boyName 
  4. FROM
  5.     beauty
  6.     INNER JOIN boys 
  7.     ON beauty.boyfriend_id = boys.id;
  • 示例：查询员工的工资等级。
  1. SELECT
  2.     e.salary,
  3.     jg.grade_level 
  4. FROM
  5.     employees e
  6.     INNER JOIN job_grades jg ON e.salary BETWEEN jg.lowest_sal 
  7.     AND jg.highest_sal;
  • 示例：查询员工的名称和其上级的名称。
  1. SELECT
  2.     e.last_name AS last_name,
  3.     m.last_name AS manager_name 
  4. FROM
  5.     employees AS e
  6.     INNER JOIN employees AS m ON e.manager_id = m.employee_id;

5.6.3 外连接

  • 示例：查询没有男朋友的女神名。
  1. SELECT
  2.     beauty.`name`,
  3.     boys.boyName 
  4. FROM
  5.     beauty
  6.     LEFT JOIN boys ON beauty.boyfriend_id = boys.id 
  7. WHERE
  8.     boys.boyName IS NULL;

5.6.4 交叉连接

  • 示例：
  1. SELECT
  2.     beauty.`name`,
  3.     boys.boyName 
  4. FROM
  5.     beauty
  6.     CROSS JOIN boys;