MySQL

MySQL学习

\(author:Sheldon\)

这是\(Sheldon\)的MySQL学习笔记，侵删

视频来源(尚硅谷的个人空间_哔哩哔哩_bilibili)

目录

SQL分类

DDL：数据定义语言。CREATE \ ALTER \ DROP \ RENAME \ TRUNCATE

DML：数据操作语言。 INSERT \ DELETE \ UPDATE \ SELECT

DCL：数据控制语言。 COMMIT \ ROLLBACK \ SAVEPOINT \ GRANT \ REVOKE

5.2表的关联关系

  • 表于表之间的数据记录有关系。现实世界中的实体以及实体之间的各种练习均用关系模型来表示。
  • 四种：一对一关联、一对多关联、多对多关联、自我引用
5.2.1 一对一关联
  • 在实际的开发中应用不多，因为一对一可以创建成一张表。
  • 举例：设计学生表：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人、...
    • 拆分为两个表：两个表的记录是一一对应关系。
    • 基础信息表（常用信息）：学号、姓名、手机号码、班级、系别
    • 档案信息（不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、...

第2章SQL语言的规则和规范

2.1基本规则

  • SQL可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
  • 每条命令可以以;或\g或\G结束
  • 关键字不能被缩写不能被分行
  • 关于标点符号
    • 必须保证所有的()、单引号、双引号是成对结束的
    • 字符串型和日期时间类型的数据可以使用单引号('')表示
    • 列的别名，尽量使用双引号("")，而且不建议省略as

2.2SQL大小写规范

  • MYSQL在windows环境下是大小写不敏感的
  • MYSQL在Linux环境下是大小写敏感的
  • 推荐采用统一的书写格式
    • 数据库名、表名、表别名、字段名、字段别名都小写
    • SQL关键字、函数名、绑定变量等都大写

2.3注释

可以使用如下格式的注释结构

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

2.4命名规则

  • 必须保证你的字段没有和保留字、数据库系统同或常用方法冲突。如果坚持使用，请在SQL语句中使用`(着重号)引起来

2.5数据导入

在命令行客户端登录mysql，使用source指令导入

mysql> source d:\mysqldb.sql

第3章 基本SELECT查询

3.1SELECT...FROM

SELECT 字段1,字段2,... FROM 表名
*:表示表中所有的字段(或列)

3.2列的别名

# AS全称:alias(别名),可以省略
# 列的别名可以使用一对""引起来
SELECT employee_id emp_id,last_name AS lname,department_id
FROM employees;

3.4去除重复行

# 查询员工表中一共有哪些部门id呢?
SELECT DISTINCT department_id # 使用DISTINCT去重
FROM employees;

# 没有实际意义
SELECT DISTINCT department_id,salary
FROM employee;

3.4空值参与运算

# 1.空值:null
# 2.null不等同与0,''
# 3.空值参与运算:结果一定也为空
SELECT employee_id,salary "月工资",salary * (1 + commision_pct) * 12 "年工资"
FROM employees;
# 如果想把它当成0则使用IFNULL(字段,0)
SELECT employee_id,salary "月工资",salary * (1 + IFNULL(commsion_pct)) * 12 "年工资"
FROM employees;

3.5着重号

# `
SELECT * FROM `ORDER`

3.6查询常数

# 类似于广播机制
SELECT 123,employee_id,last_name
FROM employees;

4.显示表结构

# 显示表结构
DESCRIBE employees; # 显示了表中字段的详细信息
DESC employees;# 同上

5.过滤数据

# 查询90号部门的员工信息
SELECT *
FROM employees
# 过滤条件，声明在FROM结构的后面
WHERE department_id = 90;
# 练习：查询last_name为'King'的员工信息
SELECT *
FROM employees
WHERE last_name = 'King'; # SQL在Window不区分大小写

LeetCode练习题

595. 大的国家
# Write your MySQL query statement below
SELECT name,population,area
FROM World
WHERE area >= 3000000
OR population >= 25000000;
1757. 可回收且低脂的产品
# Write your MySQL query statement below
SELECT product_id
FROM Products
WHERE low_fats = 'Y' 
AND recyclable = 'Y';
584. 寻找用户推荐人
# Write your MySQL query statement below
SELECT name 
FROM customer 
WHERE referee_id <> 2 
OR referee_id IS NULL;

课后练习题

查询员工12个月的工资总和，并起别名为ANNUAL SALARY

SELECT employee_id,last_name,salary * 12 "ANNUAL SALARY"
FROM employees;

SELECT employee_id,last_name,salary * 12 * (1 + IFNULL(commission_pct,0)) "ANNUAL SALARY"
FROM emplyees;

查询employees表中去除重复的job_id以后的数据

SELECT DISTINCT job_id
FROM employees;

查询工资大于12000的员工姓名和工资

SELECT last_name,salary
FROM employees
WHERE salary > 12000;

查询员工的工号为176的姓名和部门号

SELECT last_name,department_id
FROM employees
WHERE employee_id = 176;

显示表departments的结构，并查询其中的全部数据

DESCRIBE departments;
SELECT * FROM departments;

第04章 运算符

1.算术运算符

算术运算符：+ - * / div % mod

SELECT 100,100 + 0, 100 - 0, 100 + 50, 100 + 50 - 30, 100 + 35.5, 100 - 35.5
FROM DUAL;

# 在SQL中，+没有连接的作用，就表示加法运算。此时会将字符串转换为数值（隐式转换）
SELECT 100 + '1' 
FROM DUAL 

SELECT 100 + 'a' # 此时将'a'看作0处理
FROM DUAL

SELECT 100 + NULL # null值参与运算，结果为null
FROM DUAL
1.乘法与除法运算符
SELECT 100,100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,
100 + 2 * 5 / 2, 100 / 3, 100 DIV 0 # 分母如果为0，则结果为NULL
FROM DUAL;

一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；

一个数除以整数后，不管是否能除尽，结果都为一个浮点数；

一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；

乘法和除法的优先级相同

2.求模（取余）运算符
# 取模运算：% mod
SELECT 12 % 3, 12 % 5, 12 MOD -5, 12 % 5, -12 % -5
FROM DUAL;
>>0 2 2 -2 -2

#练习：查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 == 0;

结果的符号仅与被模数有关

2.比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

#2.1 = <=> <> != < <= > >= 
SELECT 1 = 2, 1 != 2,1 = '1', 1 = 'a',0 = 'a'# 字符串存在隐式转换。如果转换数值不成功，则看做0
FROM DUAL;
>> 0 1 1 0 1

SELECT 'a' = 'a','ab' = 'ab', 'a' = 'b' # 两边都是字符串的话，则按照ANSI的比较规则进行比较
FROM DUAL;
>>1 1 0

SELECT 1 = NULL, NULL = NULL # 只要有NULL参与判断，结果就为null
FROM DUAL;
>>0 NULL

SELECT last_name,salary
FROM employees
#WHERE salary = 6000;
WHERE commssion_pct = NULL; #此时执行不会有任何一条结果满足条件
>>无数据输出

# <=> 安全等于 为NULL而生
SELECT 1 <=> NULL,NULL <=> NULL
FROM DUAL;
>> 0 1

#查询表中commmssion_pct为NULL的数据有哪些
SELECT last_name,salary,commssion_pct
FROM employees
WHERE commssion_pct <=> NULL; #此时执行不会有任何一条结果满足条件
>>有数据输出

#2.2
# IS NULL\ IS NOT NULL \ ISNULL
SELECT last_name,salary,commssion_pct
FROM employees
WHERE commssion_pct IS NULL;

SELECT last_name,salary,commssion_pct
FROM employees
WHERE commssion_pct IS NOT NULL;

SELECT last_name,salary,commssion_pct
FROM employees
WHERE ISNULL(commssion_pct)

# LEAST()\GREATEST
SELECT LEAST('a','b','t','m'),GREATEST('g','b','t','m')
FROM DUAL;
>> b t

# BETWEEN 条件下界1 AND 条件上界2 （查询条件1和条件2范围内的数据，包含边界）
# 查询工资在6000到8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
#WHERE salary BETWEEN 6000 AND 8000;
WHERE salary >= 6000 && salary <= 8000;

# 交换6000和8000后无数据
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 8000 AND 6000;
>>无返回值

# 查询不在6000到8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
#WHERE salary < 6000 OR salary > 8000;
WHERE salary NOT BETWEEN 6000 AND 8000;

# IN \ NOT IN
# 练习 查询部门为10,20,30部门的员工信息
SELECT last_name,salary,department_id
FROM employees
#WHERE deparment_id = 10 or department_id = 20 or department_id = 30;
WHERE department_id IN (10,20,30);

# 练习 查询工资不是6000,7000,8000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN (6000,7000,8000)

# LIKE:模糊查询
# 练习:查询last_name中包含字符'a'的员工信息
# %:代表不确定个数的字符
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

# 练习:查询last_name中以字符'a'开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';

# 练习:查询last_name中包含字符'a'且包含字符'e'的员工信息
# 写法1
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

# 写法2
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

# _:代表一个不确定的字符
# 查询第2各字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';

# 查询第2个字符是_且第3个字符是'a'的员工信息
# 需要使用转义字符:\
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';

#了解
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';# 告诉我们的编译器'$'是转义字符

# REGEXP \ RLIKE:正则表达式

3.逻辑运算符

在MySQL中，罗i就运算符的返回结果为1、0或者NULL。

MySQL中支持4种逻辑运算符如下:

image-20220924220236193

# 逻辑运算符: OR || AND && NOT ! XOR
# OR AND
SELECT last_name,salary,department_id
FROM employees
#WHERE department_id = 10 OR department_id = 20;
WHERE department_id = 50 AND salary > 6000;

# NOT
SELECT last_name,salary,department_id
FROM employees
#WHERE salary NOT BETWEEN 6000 AND 8000;#工资不在6000到8000之间
WHERE commssion_pct IS NOT NULL;

# XOR:追求的"异"

OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再对OR中的操作数进行结合。

4.位运算符

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制，然后进行位运算，最后将计算结果从二进制变回十进制数。

5.运算符的优先级

image-20220925101832694

数字编号越大，优先级越高，优先级高的运算符先进行计算。可以看到，赋值运算符最低，使用"()"括起来的表达式优先级最高

第4章课后练习

#[题目]
#1．选择工资不在5000到12000的员工的姓名和工资
#2．选择在20或50号部门工作的员工姓名和部门号
#3．选择公司中没有管理者的员工姓名及job＿id
#4．选择公司中有奖金的员工姓名，工资和奖金级别
#5．选择员工姓名的第三个字母是a的员工姓名
#6．选择姓名中有字母a和k的员工姓名
#7．显示出表 employees 表中 first＿name 以＇e＇结尾的员工信息
#8．显示出表 employees 部门编号在80—100之间的姓名、工种
#9．显示出表 employees 的 manager＿id 是100,101,110的员工姓名、工资、管理者id

1．选择工资不在5000到12000的员工的姓名和工资

SELECT last_name,salary
FROM employeees
# 法1
WHERE salary NOT BETWEEN 5000 AND 12000;
# 法2
WHERE salary < 5000 OR salary > 12000;

2．选择在20或50号部门工作的员工姓名和部门号

SELECT last_name,department_id
FROM employees
# 法1
WHERE department_id IN (20,50);
# 法2
WHERE department_id = 20 OR department_id = 50;

3．选择公司中没有管理者的员工姓名及job＿id

SELECT last_name,job_id
FROM employees
# 法1
WHERE manager_id IS NULL;
# 法2
WHERE manager_id <=> NULL;

4．选择公司中有奖金的员工姓名，工资和奖金级别

SELECT last_name,job_id,commssion_pct
FROM employees
WHERE commssion_pct IS NOT NULL;

5．选择员工姓名的第三个字母是a的员工姓名

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

6．选择姓名中有字母a和k的员工姓名

SELECT last_name
FROM employees
# 法1
WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';
# 法2
WHERE last_name LIKE '%a' AND last_name LIKE '%k%';

7．显示出表 employees 表中 first＿name 以＇e＇结尾的员工信息

SELECT first_name,last_name
FROM employees
# 法1
WHERE first_name LIKE '%e';
# 法2
WHERE first_name REGEXP 'e$';

8．显示出表 employees 部门编号在80—100之间的姓名、工种

SELECT last_name,job_id
FROM employees
# 法1
WHERE department_id BETWEEN 80 AND 100;
# 法2
WHERE department_id >= 80 AND department_id <= 100;

9．显示出表 employees 的 manager＿id 是100,101,110的员工姓名、工资、管理者id

SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN (100,101,110);

第05章 排序与分页

1.排序数据

# 如果没有使用排序操作，默认情况下查询返回的数据是按照添加数据的顺序显示的
SELECT * FROM emplyees;

# 练习：按照salary从高到低的顺序显示员工信息
# 使用ORDER BY 对查询的数据进行排序操作
# 升序：ASC(ascend)
# 降序：DESC(descend)
SELECT employees_id,last_name,salary
FROM employees
ORDER BY salary DESC;

# 练习：按照salary从低到高的顺序显示员工信息
SELECT employees_id,last_name,salary
FROM employees
ORDER BY salary ASC; # 如果在ORDER BY 后没有显示指明排序的方式的话，则默认按照升序排列

# 我们可以使用列的别名，进行排序
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;

# 列的别名只能在ORDER BY 中使用，不能再WHERE中使用
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
WHERE annual_sal > 81600; # 报错

# 强调格式：WHERE需要声明再FROM后，ORDER BY 之前
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;

# 二级排序
# 练习：显示员工信息，按照department_id降序排列，salary的升序排列
SELECT employee_id,salary
FROM employees
ORDER BY department_id DESC,salary ASC;

2.分页

2.1背景

背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？

背景2：表里有4条数据，我们只想要显示第2，3条数据怎么办呢？

# 2.1mysql使用limit实现数据的分页显示
# 需求1：每页显示20条记录，此时显示第1页
SELECT employee_id,last_name
FROM emplyees
LIMIT 0,20;

# 需求2：每页显示20条记录，此时显示第2页
SELECT employee_id,last_name
FROM emplyees
LIMIT 20,20;

# 需求3：每页显示20条记录，此时显示第3页
SELECT employee_id,last_name
FROM emplyees
LIMIT 40,20;

# 需求4：每页显示pageSize条记录，此时显示第pageNo页
# 公式：LIMIT (pageNo - 1) * pageSize,pageSize;
SELECT employee_id,last_name
FROM emplyees
LIMIT 40,20;


# 2.2 WHERE ... ORDER BY ...LIMIT 声明顺序如下
# LIMIT的格式：严格来说:LIMIT 位置偏移量,条目数
# 结构"LIMIT 0,条目数"等价于"LIMIT 条目数"
SELECT employee_id,last_name,salary
FROM emplyees
WHERE salary > 6000
ORDER BY salary DESC
LIMIT 0,10;

# 练习：表里有107条数据，我们只想要显示第32、33条数据怎么办?
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;

#2.3 MySQL8.0新特性：LIMIT ... OFFSET
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;

# 练习：查询员工表中工资最高的员工信息
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC
LIMIT 0,1;

第5章课后练习

题目

#1．查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序显示
#2．选择工资不在8000到17000的员工的姓名和工资，按工资降序，显示第21到40位置的数据
#3．查询邮箱中包含 e的员工信息，并先按邮箱的字节数降序，再按部门号升序

1．查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序显示

SELECT last_name,department_id,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal DESC,last_name ASC;

2．选择工资不在8000到17000的员工的姓名和工资，按工资降序，显示第21到40位置的数据

SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

3．查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT employee_id,last_name,email,department_id
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id;

第06章多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

# 查询员工名为'Abel'的人在哪个城市工作？
SELECT *
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;

SELECT * 
FROM locations;

如果我们这么做的话，我们需要看3张表，非常的不方便。

# 因此我们口语使用如下方法操作
SELECT employees.employee_id,departments.department_name,employees.`department_id`
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

#建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在表名

#可以给表起别名，在SELECT和WHERE中使用表的别名
SELECT emp.employee_id,dept.department_name,dept.`department_id`
FROM employees emp,departments dept
WHERE emp.`department_id`=dept.`department_id`;
#如果给表起了别名，一旦在SELECT或WHERE中使用表的别名，则必须使用表的别名而不能使用原名

#6 如果有n个表实现多表的查询，则需要至少n-1个连接条件
#练习：查询员工的employee_id,last_name,department_name,city

SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;
posted @ 2022-09-24 09:17  Sheldon2  阅读(80)  评论(0)    收藏  举报