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`;
  2. CREATE TABLE `admin` (
  3. `id` int(11) NOT NULL AUTO_INCREMENT,
  4. `username` varchar(10) NOT NULL,
  5. `password` varchar(10) NOT NULL,
  6. PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
  8. insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666');
  9. DROP TABLE IF EXISTS `beauty`;
  10. CREATE TABLE `beauty` (
  11. `id` int(11) NOT NULL AUTO_INCREMENT,
  12. `name` varchar(50) NOT NULL,
  13. `sex` char(1) DEFAULT '女',
  14. `borndate` datetime DEFAULT '1987-01-01 00:00:00',
  15. `phone` varchar(11) NOT NULL,
  16. `photo` blob,
  17. `boyfriend_id` int(11) DEFAULT NULL,
  18. PRIMARY KEY (`id`)
  19. ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
  20. 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);
  21. DROP TABLE IF EXISTS `boys`;
  22. CREATE TABLE `boys` (
  23. `id` int(11) NOT NULL AUTO_INCREMENT,
  24. `boyName` varchar(20) DEFAULT NULL,
  25. `userCP` int(11) DEFAULT NULL,
  26. PRIMARY KEY (`id`)
  27. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  28. 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;