MySQL Basic Learning （一）

最新推荐文章于 2025-09-13 19:07:06 发布

weixin_34037515

最新推荐文章于 2025-09-13 19:07:06 发布

阅读量108

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 java 操作系统

原文链接：http://www.cnblogs.com/xpz-python/p/10935138.html

MySQL核心技术基础篇

一、数据库相关概念

1、DB：数据库，保存一组有组织的数据的容器
2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3、SQL:结构化查询语言，用于和DBMS通信的语言

二、数据库存储数据的特点

1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5、表中的数据是按行存储的，每一行类似于java中的“对象”。

三、初始化MySQL

MySQL产品的介绍和安装

熟稔于心，不再赘述

MySQL服务的启动和停止

1.方式一：计算机——右击管理——服务
2.方式二：通过管理员身份运行

    net start 服务名（启动服务）
    net stop 服务名（停止服务）

MySQL服务的登录和退出

1.方式一：通过mysql自带的客户端，只限于root用户。
2.方式二：通过windows自带的客户端

    登录：mysql 【-h主机名 -P端口号 】-u用户名 -p密码 。简写：mysql -uroot -proot
    退出：exit或ctrl+C

MySQL的常见命令（ps：命令结尾用分号）

1.查看当前所有的数据库：show databases;
2.打开指定的库：use 库名
3.查看当前库的所有表：show tables;
4.查看其它库的所有表：show tables from 库名;
5.创建表

create table 表名(
        列名 列类型,
        列名 列类型，
        。。。
    );

6.查看表结构：desc 表名
7.查看服务器的版本

方式一：登录到mysql服务端：select version();
方式二：没有登录到mysql服务端：mysql --version 或 mysql --V

MySQL的语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释

    单行注释：#注释文字
    单行注释：-- 注释文字
    多行注释：/* 注释文字  */

SQL的语言分类

1.DQL（Data Query Language）：数据查询语言 select
2.DML(Data Manipulate Language):数据操作语言 insert 、update、delete
3.DDL（Data Define Languge）：数据定义语言 create、drop、alter
4.TCL（Transaction Control Language）：事务控制语言 commit、rollback

SQL的常见命令

1.show databases；查看所有的数据库
2.use 库名；打开指定的库
3.show tables ; 显示库中的所有表
4.show tables from 库名;显示指定库中的所有表
5.create table 表名( 字段名字段类型, 字段名字段类型 ); 创建表
6.desc 表名; 查看指定表的结构
7.select * from 表名;显示表中的所有数据

DQL语言的学习

进阶1：基础查询

1.语法：SELECT 要查询的东西【FROM 表名】; 类似于Java中 :System.out.println(要打印的东西);
2.特点：
①通过select查询完的结果，是一个虚拟的表格，不是真实存在
② 要查询的东西可以是常量值、可以是表达式、可以是字段、可以是函数

进阶2：条件查询

1.条件查询：根据条件过滤原始表的数据，查询到想要的数据
2.语法：select 要查询的字段|表达式|常量值|函数 from 表 where 条件 ;
3.分类：

一、条件表达式示例：salary>10000 条件运算符：> < >= <= = != <>
二、逻辑表达式示例：salary>10000 && salary<20000 逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true
三、模糊查询示例：last_name like 'a%'

进阶3：排序查询（ DESC降序；ASC升序；默认升序）

1.语法：select 要查询的东西 from 表 where 条件 order by 排序的字段|表达式|函数|别名【asc|desc】

如：SELECT * FROM employees ORDER BY salary DESC;

进阶4：常见函数

一、单行函数
1、字符函数
    concat拼接：SELECT CONCAT(first_name,last_name) AS 姓名 FROM employees;
    substr截取子串：SELECT SUBSTR('哈哈，我是平凡之路',4);
    upper转换成大写：SELECT UPPER('guet');
    lower转换成小写：SELECT LOWER('GUET');
    trim去前后指定的空格和字符：SELECT TRIM('  哈哈  123 ');
    ltrim去左边空格：SELECT LTRIM('  guet   ');
    rtrim去右边空格：SELECT LTRIM('  guet   ');
    replace替换：SELECT REPLACE('abc','a','x');
    lpad左填充：SELECT LPAD('abc',5,'xx');
    rpad右填充：SELECT RPAD('abc',5,'xx');
    instr返回子串第一次出现的索引
    length 获取字节个数：SELECT  LENGTH('guet');
2、数学函数
    round 四舍五入：SELECT   LENGTH('guet');
    rand 随机数：SELECT RAND();
    floor向下取整：SELECT FLOOR(1.5);
    ceil向上取整：SELECT CEIL(1.5);
    mod取余：SELECT MOD(5,2);
    truncate截断：SELECT TRUNCATE(1.23456,3);
3、日期函数
    now当前系统日期+时间：SELECT NOW();
    curdate当前系统日期：SELECT CURDATE();
    curtime当前系统时间：SELECT CURTIME();
    str_to_date 将字符转换成日期：SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
    date_format将日期转换成字符：SELECT TIME_FORMAT('11:11:11','%r');
4、流程控制函数
    if 处理双分支
    case语句 处理多分支
        情况1：处理等值判断
        情况2：处理条件判断
5、其他函数
    version版本
    database当前库
    user当前连接用户

二、分组函数

        sum 求和
        max 最大值
        min 最小值
        avg 平均值
        count 计数
        特点：
            1、以上五个分组函数都忽略null值，除了count(*)
            2、sum和avg一般用于处理数值型；max、min、count可以处理任何数据类型
            3、都可以搭配distinct使用，用于统计去重后的结果
            4、count的参数可以支持：字段、*、常量值，一般放1 ； 建议使用 count(*)

进阶5：分组查询

1.语法：select 查询的字段，分组函数 from 表 group by 分组的字段

如：查询每个工种的最高工资：SELECT MAX(salary),job_id FROM employees GROUP BY job_id;
如：查询每个位置上的部门个数：SELECT COUNT(*) ,location_id FROM departments GROUP BY location_id;

2.特点：

可以按单个字段分组
和分组函数一同查询的字段最好是分组后的字段
分组筛选
针对的表位置关键字
分组前筛选：原始表 group by的前面 where
分组后筛选：分组后的结果集 group by的后面 having
可以按多个字段分组，字段之间用逗号隔开
可以支持排序
having后可以支持别名

#添加筛选条件 （根据原始表筛选，用 WHERE ）
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary) ,department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;
#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary) ,manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

#添加复杂的筛选条件 （根据新的结果集筛选，用 HAVING）
#案例1：查询那个部门的员工个数>2  
SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>2;

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
#①查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id 
FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id;
#②根据①的结果继续筛选，最高工资大于12000
SELECT MAX(salary),job_id 
FROM employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING MAX(salary)>12000

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及最低工资
#①查询领导编号>102的每个领导手下的最低工资
SELECT MIN(salary),manager_id FROM  employees WHERE manager_id>102   GROUP BY manager_id;
#②最低工资>5000
SELECT MIN(salary),manager_id FROM  employees WHERE manager_id>102   GROUP BY manager_id HAVING MIN(salary)>5000;

#按表达式分组
#案例1：按员工姓名的长度分组，查询每一组的员工个数>5的有哪些
#①查询每个长度的员工个数
SELECT COUNT(*),LENGTH(last_name) len_name FROM employees GROUP BY LENGTH(last_name); 
#②添加筛选条件
SELECT COUNT(*) ,LENGTH(last_name) len_name FROM employees GROUP BY LENGTH(last_name) HAVING COUNT(*)>5;

进阶6：多表连接查询

1.笛卡尔乘积：如果连接条件省略或无效则会出现
2.解决办法：添加上连接条件

一、sql92 传统模式下的连接：等值连接——非等值连接（推荐使用sql99）

1.等值连接的结果 = 多个表的交集
2.n表连接，至少需要n-1个连接条件
3.多个表不分主次，没有顺序要求
4.一般为表起别名，提高阅读性和性能

#案例1：查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments WHERE employees.`department_id` = departments.`department_id`

#2.为表起别名(如果为表起了别名，则不能用表名起限定)
#案例2：查询员工名、工种号、工种名
SELECT last_name,job_title,e.`job_id` FROM employees AS e,jobs WHERE e.`job_id` = jobs.`job_id`

#3.可以加筛选吗？
#案例3：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct FROM employees e,departments d WHERE e.`department_id` = d.`department_id` 
AND e.`commission_pct` IS NOT NULL

#案例4：查询城市名中第二个字符为O的部门名和城市名
SELECT department_name,city FROM departments d,locations l WHERE d.location_id = l.location_id AND l.city LIKE '%_o%'

#5.可以加分组吗？
#案例5：查询每个城市的部门个数
SELECT COUNT(*) 个数,city FROM departments d,locations l WHERE d.location_id = l.location_id GROUP BY city

#案例6：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
#①查询每个部门的部门名和部门的领导编号
SELECT department_name,d.manager_id,d.department_id FROM departments d,employees e
WHERE d.`department_id` = e.`department_id` GROUP BY d.department_id
#②有奖金
SELECT department_name,d.manager_id,MIN(e.`salary`)FROM departments d,employees e
WHERE d.`department_id` = e.`department_id` AND e.`commission_pct` IS NOT NULL GROUP BY department_name

#7.可以实现三表连续？
#案例：查询员工名、部门名和所在城市
SELECT last_name,department_name,city FROM employees e,departments d,locations l WHERE e.`department_id` = d.`department_id` 
AND d.`location_id` = l.`location_id`;

非等值连接

#案例1：查询员工的工资和工资级别   
SELECT salary,grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`;

自连接

#案例：查询 员工名和上级的名称
 SELECT e.employee_id,e.last_name ,m.employee_id ,m.last_name FROM employees e,employees m WHERE e.`manager_id` = m.`employee_id`;

二、sql99语法：通过join关键字实现连接

含义：1999年推出的sql语法
支持：等值连接、非等值连接（内连接）外连接交叉连接
语法：select 字段，...from 表1

    【inner 内连接|left outer  左外连接|right outer  右外连接|cross  交叉连接】join 表2 on  连接条件
    【inner|left outer|right outer|cross】join 表3 on  连接条件
    【where 筛选条件】
    【group by 分组字段】
    【having 分组后的筛选条件】
    【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

（一）内连接

1.等值连接

#1.等值连接
#案例1：查询员工名、部门名
SELECT last_name,department_name 
FROM employees e 
INNER JOIN departments d 
ON e.`department_id` = d.`department_id`

#案例2：查询名字中包含e的员工名和工种名（筛选）
SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e.`job_id` = j.`job_id` WHERE last_name LIKE '%a%';

#案例3：查询部门个数>3的城市名和部门个数。（分组+筛选）
#①查询每个城市的部门个数
SELECT COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY l.`city`
#②再①的基础上部门个数>3
SELECT COUNT(*) 部门个数 FROM departments d INNER JOIN locations l ON d.`location_id` = l.`location_id` GROUP BY l.`city` HAVING COUNT(*)>3

#案例4：查询那个部门的部门员工个数>3的部门名和员工个数，并按个数降序
#①查询部门名和员工个数
SELECT department_name ,COUNT(*) 员工个数 FROM departments d INNER JOIN employees e ON d.`department_id` = e.`department_id` GROUP BY department_name;
#②在①的结果集上  部门的部门员工个数>3 降序
SELECT department_name ,COUNT(*) 员工个数 FROM departments d INNER JOIN employees e ON d.`department_id` = e.`department_id` GROUP BY department_name 
HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC;

#案例5：查询员工名、部门名、工种名，并按部门名降序(三表连接)
SELECT last_name,department_name,job_title FROM employees e INNER JOIN departments d  ON e.`department_id` = d.`department_id`
INNER JOIN jobs j ON j.`job_id` = e.`job_id` ORDER BY d.department_id DESC;

2.非等值连接

#案例1：查询员工的工资级别
SELECT last_name,salary,grade_level FROM employees e INNER JOIN job_grades  j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`

#案例2：查询每个工资级别的个数，并且按工资级别降序
SELECT COUNT(*),j.`grade_level` FROM employees e INNER JOIN job_grades j ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal` GROUP BY j.`grade_level`
ORDER BY j.`grade_level` DESC;

3.自连接

#案例1：查询员工的名字，上级的名字
SELECT e.last_name ,m.last_name FROM employees e INNER JOIN employees m ON e.`manager_id` = m.`employee_id`;

（二）外连接

应用场景：用于查询一个表中有，另一个表没有的记录
特点：
- 外连接查询结果为主表中的所有记录。如果从表中有和他匹配的，显示匹配的值。没有匹配的，则显示null 。外连接的查询结果=内连接结果+主表中有而从表中没有的记录。
- 左外连接，left左边的是主表。右外连接，right join 右边的是主表
- 左外和右外交换两个表的顺序，可以实现同样的效果

#引入：查询没有男朋友的女神名 (左外连接)
SELECT  b.name FROM beauty b  LEFT  OUTER JOIN boys bo  ON  b.`boyfriend_id` = bo.`id` WHERE bo.id IS  NULL
#案例：查询那个部门没有员工
#左外 主表部门
SELECT d.* ,e.employee_id FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.employee_id WHERE e.employee_id IS NULL;
#右外
SELECT d.*,e.employee_id FROM employees e RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL;

进阶7：子查询

1.含义：一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询在外面的查询语句，称为主查询或外查询
2.特点：

子查询都放在小括号内
子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
子查询优先于主查询执行，主查询使用了子查询的执行结果
子查询根据查询结果的行数不同分为以下两类：
- 单行子查询，结果集只有一行，一般搭配单行操作符使用：> < = <> >= <=
  非法使用子查询的情况：
  a、子查询的结果为一组值
  b、子查询的结果为空
- 多行子查询
  结果集有多行，一般搭配多行操作符使用：any、all、in、not in。in：属于子查询结果中的任意一个就行。any和all往往可以用其他查询代替

（一） where 或 having 后面 ※

标量子查询（单行子查询）

#标量子查询（单行子查询）
#案例一：查询工资大于Abel的员工
#①查询Abel工资
SELECT salary FROM employees WHERE last_name = 'Abel'
#②查询员工的信息，满足salary>①结果
SELECT * 
FROM employees 
WHERE salary > (
    SELECT salary 
    FROM employees 
    WHERE last_name = 'Abel'
);

#案例2：查询job_id 与141号员工相同，salary 比 143号员工多的员工姓名，job_id和工资
#①查询141号员工的job_id
SELECT job_id FROM employees WHERE employee_id = 141
#②查询143号员工的salary
SELECT salary FROM employees WHERE employee_id = 143
#③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
SELECT last_name,job_id,salary 
FROM employees 
WHERE job_id = (
    SELECT job_id 
    FROM employees 
    WHERE employee_id = 141
)
AND salary > (
    SELECT salary 
    FROM employees 
    WHERE employee_id = 143
)

#案例3：返回公司工资最少的员工的last_name,job_id和salary
#①查询最小工资
SELECT MIN(salary) FROM employees
#②查询last_name,job_id和salary
SELECT last_name,job_id,salary 
FROM  employees
WHERE salary = (
    SELECT MIN(salary) 
    FROM employees
)

#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
#① 查询10号部门的最低工资
SELECT MIN(salary) FROM employees WHERE department_id = 50
#②查询每个部门的最低工资
SELECT MIN(salary),department_id FROM employees GROUP BY department_id
#③筛选②满足min（salary）> ①
SELECT MIN(salary),department_id 
FROM employees 
GROUP BY department_id 
HAVING MIN(salary) > (
    SELECT MIN(salary) 
    FROM employees 
    WHERE department_id = 50
)

列子查询（多行子查询）

#2.列子查询（多行子查询）
#案例1：返回location_id 是 1400 或 1700的部门中的所有员工姓名
#①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id 
IN (1400,1700)
#②查询员工姓名，部门id是①
SELECT  last_name 
FROM employees
WHERE department_id 
IN (
    SELECT  DISTINCT department_id
    FROM departments
    WHERE location_id 
    IN (1400,1700)
)

#案例2：返回其他工种中job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id 以及 salary
#①查询job_id为'IT_PROG'部门任一工资
SELECT DISTINCT salary 
FROM employees e 
WHERE  e.`job_id` = 'IT_PROG'
#②查询员工号、姓名、job_id 以及 salary，salary < ①中任意一个
SELECT last_name,employee_id,salary
FROM employees 
WHERE salary < ANY(
    SELECT DISTINCT salary 
    FROM employees e 
    WHERE  e.`job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG'

#案例2：返回其他部门中比job_id为'IT_PROG'部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
SELECT last_name,employee_id,salary
FROM employees 
WHERE salary < ALL(
    SELECT DISTINCT salary 
    FROM employees e 
    WHERE  e.`job_id` = 'IT_PROG'
) AND job_id <> 'IT_PROG'

行子查询（多行多列用的较少操作符一样）

#案例1：查询员工编号最小并且工资最多的员工信息
SELECT * 
FROM employees
WHERE (employee_id,salary) = (
    SELECT  MIN(employee_id),MAX(salary)
    FROM employees
)

（二） select 后面的子查询 [可以用其他方式代替]

仅仅支持标量子查询

#二、select 后面
#案例1：查询每个部门的员工个数
SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE d.`department_id` = e.`department_id`
) 个数
FROM departments d

#案例2：查询员工号=102的部门名
SELECT(
    SELECT department_name 
    FROM departments d
    INNER JOIN employees e
    ON d.`department_id` = e.`department_id`
    WHERE e.employee_id = 102
) 部门名

（三） from 后面的子查询

将子查询结果充当一张表，要求必须起别名

#案例1：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②链接1的结果集和job_gredes表，筛选条件平均工资正在。。中间
SELECT ag_dep.*,g.grade_level
FROM (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal

（四） exists后面的子查询使用

语法：EXISTS（完整的查询语句）；结果：0或1

#四、 exists后面的子查询使用（相关子查询）
SELECT EXISTS(
    SELECT employee_id FROM employees WHERE salary = 300000
)

进阶8：分页查询

应用场景：实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：

    select 字段|表达式,...
    from 表
    【where 条件】
    【group by 分组字段】
    【having 条件】
    【order by 排序的字段】
    limit 【起始的条目索引，】条目数;

特点：
- 1.起始条目索引从0开始
- 2.limit子句放在查询语句的最后
- 3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
  假如:
  每页显示条目数sizePerPage
  要显示的页数 page

#案例1：查询前5条员工信息
SELECT * FROM employees LIMIT 0,5;
#案例2：查询11-25条
SELECT * FROM employees LIMIT 10,15
#案例3：有奖金的员工信息，并且工资较高的前10名
SELECT
  *
FROM
  employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10

进阶9：联合查询

引入：union 联合、合并
语法：

    select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
    select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
    select 字段|常量|表达式|函数 【from 表】 【where 条件】 union  【all】
    .....
    select 字段|常量|表达式|函数 【from 表】 【where 条件】