专注于 JetBrains IDEA 全家桶，永久激活，教程
持续更新 PyCharm，IDEA，WebStorm，PhpStorm，DataGrip，RubyMine，CLion，AppCode 永久激活教程
当前位置：搜云库技术团队 Java汇总 正文

MySQL基础（二）

2020-08-13 分类：Java汇总 阅读(510)

条件查询

语法：

SELECT 查询列表
FROM 表名
WHERE 条件表达式;

分类：

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

按条件表达式筛选：

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

按逻辑表达式筛选:

  • 查询工资在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 );

模糊查询：

  • 查询员工名中包含字符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;

排序查询

语法：

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

应用示例：

  • 查询员工信息，要求工资从高到低排序。
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;

常见函数

概念:

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

好处：

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

分类：

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

单行函数之字符函数

length()

  • 作用：获取参数值的字节个数。
  • 示例：
SELECT LENGTH( 'john' );


SELECT
    LENGTH( '张三丰hahaha' ); -- 15

concat(str1,str2,……)

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

upper(str)

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

lower(str)

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

substr(str,position,[length])

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


-- 截取从指定索引处指定字符长度的字符
SELECT
    SUBSTR( '李莫愁爱上了陆展元', 1,3 );

instr(str,substr)

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

trim(str)

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

replace(str,from_str,to_str)

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

lpad(str,len,padstr)

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

rpad(str,len,padstr))

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

单行函数之数学函数

round(x,d)

  • 作用：四舍五入。
  • 示例：
SELECT
    ROUND(1.65)


SELECT
    ROUND(1.45)


SELECT
    ROUND(1.567,2)

ceil(x)

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

floor(x)

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

truncate(x,d)

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

mod(n,m)

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

单行函数之日期函数

now()

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

curdate()

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

curtime()

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

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

  • 示例：
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

str_to_date(str,format)

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

date_format(date,format)

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

89_1.png

单行函数之其他函数

version()

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

database()

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

user()

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

单行函数之流程控制函数

if(expr1,expr2,expr3)

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

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;

分组函数

  • 常见的分组函数是：
    • 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;

分组查询

语法：

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

应用示例：

  • 查询每个部门的平均工资。
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;

连接查询

概念：

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

准备工作：

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);

笛卡尔积：

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

连接查询的分类：

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

SQL92标准：

等值连接：

  • 示例：查询女神名和其对应的男神名。
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;

非等值连接：

  • 工资等级的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;

自连接：

  • 查询员工的名称和其上级的名称。
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;

SQL99标准：

语法：

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

内连接：

  • 示例：查询女神名和其对应的男神名。
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;

外连接：

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

交叉连接：

  • 示例：
SELECT
    beauty.`name`,
    boys.boyName 
FROM
    beauty
    CROSS JOIN boys;

文章永久链接：https://tech.souyunku.com/42664

AD：【JetBrains 全家桶，激活、破解、教程】获取 IDEA 激活码、PyCharm 激活码、WebStorm 激活码和 DataGrip 激活码，提供详细破解教程与永久激活方法。支持 IDEA 永久激活与破解，免费获取注册码与激活码，解决 2024/2025 版本激活问题，轻松实现所有 JetBrains 工具的激活。
未经允许不得转载：搜云库技术团队 » MySQL基础（二）

相关推荐

热门专题

JetBrains 全家桶，激活、破解、教程

提供 JetBrains 全家桶激活码、注册码、破解补丁下载及详细激活教程，支持 IntelliJ IDEA、PyCharm、WebStorm 等工具的永久激活。无论是破解教程，还是最新激活码，均可免费获得，帮助开发者解决常见激活问题，确保轻松破解并快速使用 JetBrains 软件。获取免费的破解补丁和激活码，快速解决激活难题，全面覆盖 2024/2025 版本！

联系我们联系我们