目录
6.安装好MySQL后在windows系统中哪些位置可以看到MySQL？
7.MySQL5.7在配置完后为何要修改配置文件my.ini？
1.最基本的SELECT语句： SELECT 字段1，字段2…… FROM 字段所在的表名
③ BETWEEN 条件下界1 AND 条件上界2：选择[ 条件下界1 ，条件上界2 ]范围的数据（包含条件1，2）
④ IN(set)，NOT IN(set)——set指某个集合
3>8.0新特性：LIMIT 条目数 OFFSET 位置偏移数
举例：计算各个department_id ,job_id的平均工资
一、MySQL的使用演示
（一）包含指令
在搜索框中输入：cmd即可出现“命令提示符”。
注：MySQL是数据库，SQL是一种语言
1.数据库的连接（登录）与退出
不推荐的输入密码的方法：mysql -u用户名 -P端口号 -h主机名 -p密码，p后面不加英文分号，不然会以为是密码的一部分。
推荐的输入密码的方式：mysql -u用户名 -P端口号 -h主机名 -p，p后面不加英文分号，不然相当于密码只有一个分号，回车输入密码也没有意义了。回车后提示你要输入密码，按提示来即可。
注：①端口号前为大写的P，小写的p后面跟的是密码②电脑中只有一个MySQL版本时，P（端口号）可以省略；如果要用本机连接数据库，h（主机名）可以省略。
退出：quit，不加分号。
2.登录后查看当前版本信息
mysql> select version();
注：select的意思是挑选，选择；version的意思是版本。
3.展示数据库、表
展示所有数据库：show databases;
展示某个数据库：show create database 数据库名; //显示某个数据库
展示表前必须确定表所在的数据库：use 数据库名（注意！此命令后无分号！）回车后输入以下指令：
展示所有表：show tables;
展示某个表：show create table 表名;
注：展示表时，显示出来的是表格形式。
4.数据库的创建与删除
数据库的创建：create database 数据库名;
数据库的删除：drop database 数据库名;
5.表的创建与删除
表创建前必须先确定将这个表创建到哪个数据库中：
use 数据库名（注意！此命令后无分号！）回车后输入以下指令：
表的创建：create table 表名(表的属性);
6.表中数据的添加与显示
显示表内数据：select * from employees;回车即可得结果。
注：*：表中的所有字段（或列）
向表中添加数据：insert into employees values(输入与表属性对应的内容即可);
表创建后直接显示：show create table 表名;
注：显示表中数据时，显示出来的是数据形式。
7.服务的重启
停止服务：net stop mysql服务名;
启动服务：net start mysql服务名;
二、Navicat的使用
1.连接名与主机名保持一致
2.进行连接测试时，如果出现：using password：YES时，说明密码输入错误。
3.进行连接测试时，出现以下情况：
出现原因：MySQL新版本（8以上版本）的用户登录账户
的方式是caching_sha2_password，而所用的Navicat不支持这种用户登录账户加密方式。
修改方式：
1>登录数据库。
2>使用mysql数据库：USE mysql;
3>修改" root'@" localhost'用户的密码规则和密码：
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123 ';
其中，“BY”后是连接的密码，按照自己的密码修改即可。
4>刷新权限：FLUSH PRIVILEGES;
三、阶段性习题1
1.说说你了解的常见的数据库
oracle、MySQl、SQL Server、DB2、PGSQL; Redis、MongoDB、ES……
2.谈谈你对MySQL历史、特点的理解
1>历史:
由瑞典的MySQL AB公司创立;
1995开发出的MySQL;
2008年，MySQL被SUN公司收购;
2009年，Oracle收购SUN公司，进而Oracle就获取了MySQL;
2016年，MySQL8.0.0版本推出.
2>特点:
开源的、关系型的数据库;
支持千万级别数据量的存储，大型的数据库.
3.说说你对DB、DBMS、SQL的理解
DB: database，看做是数据库文件。(类似于: .doc、.txt、.mp3、.avi……)
DBMS:数据库管理系统。(类似于word工具、 wps工具、记事本工具、qq影音播放器等)
MySQL数据库服务器中安装了MySQL DBMS，使用MySQL DBMS来管理和操作DB，使用的是SQL语言。
4.你知道哪些非关系型数据库的类型？（了解）
键值型数据库:Redis
文档型数据库:MongoDB
搜索引擎数据库:ES、Solr
列式数据库:HBase
图形数据库:InfoGrid
5.表与表的记录之间存在哪些关联关系？
ORM思想；
表与表的记录之间的关系：一对一关系、一对多关系、多对多关系、自关联
6.安装好MySQL后在windows系统中哪些位置可以看到MySQL？
MySQL DBMS软件的安装位置。
MySQL数据库文件的存放位置。
MySQL DBMS的配置文件——my .ini
MySQL的服务（要想通过客户端能够访问MySQL的服务器，必须保证服务是开启状态的)
MysQL的path环境变量
控制面板
7.MySQL5.7在配置完后为何要修改配置文件my.ini？
默认的数据库使用的字符集是Iatin1，而我们需要修改为：utf8.
四、基本的SELECT语句
（一）铺垫知识
1.SQL的三条主线
1>DDL：数据定义语言——对数据库对象进行的
CREATE 创建xx/ ALTER 修改xx/ DROP 删除xx / RENAME 重命名xx /TRUNCATE 清空xx
2>DML：数据操作语言——针对表中一条记录而言的
INSERT 添加记录/ DELETE 删除记录 / UPDATE 修改记录 / SELECT 查询记录（！重中之重！）
3> DCL：数据控制语言——对数据操作进行控制
COMMIT 提交操作 / ROLLBACK 撤销操作 / SAVEPOINT 保存点 / GRANT 赋予权限 / REVOKE 回收权限
2.SQL大小写规范
1>MysQL在Windows环境下是大小写不敏感的，MysQL在 Linux环境下是大小写敏感的
2>推荐采用统一的书写规范:
数据库名、表名、表别名、字段名、字段别名等都小写
SQL关键字、函数名、绑定变量等都大写
3.注释
单行注释：#注释文（MySQL特有的方式）或者 -- 注释文字（--后面必须包含一个空格）
多行注释：/*注释文字*/（/* */此符号不可以嵌套使用）
4.导入现有数据表、表的数据
1>方式一（在命令提示符中采取此方法）
source 文件名的全路径名
2>基于具体的图形化界面的工具可以导入数据
5.命名规则（暂时了解）
1>数据库、表名不得超过30个字符，变量名限制为29个。必须只能包含A-Z,a-z,0-9，_共63个字符
2>数据库名、表名、字段名等对象名中间不要包含空格
3>同一个MysQL软件中，数据库不能同名;同一个库中，表不能重名;同一个表中，字段不能重名
4>必须保证你的字段没有和保留字，数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用 ` (着重号）引起来
5>保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了
（二）SELECT语句
1.最基本的SELECT语句： SELECT 字段1，字段2…… FROM 字段所在的表名
举例：SELECT 1 + 1，3 * 2；↔ SELECT 1 + 1，3 * 2 FROM DUAL;
输出结果均为：
注：① DUAL：伪表。之所以二者等价，是因为单纯计算结果不需要找出某个表来。
2.别名
1>通过SELECT语句查找出来的结果称为：结果集(例如上图)
2>列的别名可以用一对 “ ” 引起来，当别名中不含空格时，可以不加 “ ”，但是含有空格时必须加。
举例：SELECT employees_id "部门 id",last_name AS lname,department_id "部门 号",employeemoney money FROM employees;
注：① as：全称为alias(别名),可以省略 ② 别名可以采用中文 ③ 字符串、日期实践类型的变量需要使用一对 ‘ ’表示。
3.去除重复行
1>只查某一需求的方法：SELECT DISTINCT 字段 FROM 含有字段的表名;
举例：（有多少部门 id）SELECT DISTINCT department_id FROM employees;
2>查两个条件同时不重复的方法：SELECT DISTINCT 字段1，字段2 FROM 含有字段的表名;
举例：（各个部门中工资不同的人的department_id 和 salary）SELECT DISTINCT department_id,salary FROM employees;
4.空值参与运算
1>空值：null。null不等同于0，‘ ’，‘null’
2>空值参与运算，结果一定也为空（null）
注意！在MySQL里空值≠空字符串。一个空字符串的长度是0，而一个空值的长度是空。在MySQL里空值是占用空间的。
3>在实际应用中如何解决：引入IFNULL（ 1 ，2 ）
IFNULL（ 1 ，2 ）：如果1不为null，则按1位置处的数值计算；如果1为null，将1处的数值替换成0来进行计算
5.着重号 （` `）
使用情况：当字段名、表名与关键字（保留字）重名时，用 ` `引起来。
举例：SELECT * FROM `order`;
6.查询常数
1>用途
SELECT查询可以对常数进行查询，即在SELECT查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
举例：SELECT ‘大厂’,123,last_name FROM employees;
结果如下：
7.显示表结构
1>语法：DESCRIBE 表名; 或者 DESC 表名;
2>作用：显示表中的字段的详细信息：包括类型、是否可以为null、约束等等
显示结果如下：
8.过滤数据
1>格式：SELECT 字段1,字段2 FROM 含有字段的表名 WHERE 过滤条件（注意！WHERE子句紧跟着FROM子句）
2>运行逻辑：先运行SELECT 字段1,字段2 FROM 含有字段的表名，接着将对应数据与过滤条件比较，如果满足则返回1，不满足返回0，其他情况返回NULL，则整条语句的结果为返回值为1的数值。
五、阶段性习题2
1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY
SELECT employee_id,salary * (1 + IFNULL(commission_pct,0)) "ANNUAL SALARY"
FROM employees;2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;3.查询工资大于12000的员工姓名和工资
SELECT first_name,last_name,salary
FROM employees
WHERE salary > 12000;
4.查询员工号为176的员工的姓名和部门号
SELECT first_name,last_name,department_id
FROM employees
WHERE employee_id = 176;5.显示表departments 的结构，并查询其中的全部数据
DESCRIBE departments;
SELECT *
FROM departments;六、运算符
（一）算术运算符
1>包含：+ - * /(div) %(mod)
① +、-运算符
2>在SQL中，+没有连接的作用，只表示加法运算。此时会将字符串转换为数值，此转换方法称为：隐式转换
举例：SELECT 100 + '1' FROM DUAL; 的结果为101
3>进行 100 + ‘某字母 ’等类似运算时，字母相当于0
举例：SELECT 100 + 'a' FROM DUAL;的结果为100
SELECT 100 + 'ab' FROM DUAL;的结果为100
SELECT 100 + 'A100' FROM DUAL;的结果为100
4>与NULL进行运算时，结果均为null
举例SELECT 100 + NULL FROM DUAL;的结果为null
5>一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数
② *、/运算符
1>/运算符使用时，其结果不论除尽与否，都是浮点型，小数点后保留4位
2>/运算符使用时，分母若为0，结果为NULL
③ 取模运算：%
1>其结果为模数，即所谓的余数。
2>A % B，其中A 为被模数，B为模数
2>取模运算的结果与%前面位置的数（被模数）的正负号一致，与模数无关。
（二）比较运算符
用法：用来对表达式左边的操作数和右边的操作数比较，比较的结果为真则返回1，结果为假则返回0，其他情况返回NULL
① = 等于
1>非字符串与字符串之间的比较存在隐式转换，如果转换不成功，则看作0
举例：SELECT 1 = 2,1 != 2,1 = '1',1 = 'a',0 = 'a' FROM DUAL;
结果为：0，1，1，0，1
2>字符串之间的比较不存在隐式转换，按照字符串原本的ASCII码进行比较
举例：SELECT 'A' = 'A',‘ab’ = 'ab','a' = 'b' FROM DUAL;
结果为：1，1，0
3>只要有NULL参与判断，结果就为NULL
举例：SELECT 1 = NULL,NULL = NULL FROM DUAL;
结果为：NULL,NULL
举例：SELECT last_name , salary , commission_pct FROM employees
WHERE commission_pct = NULL;
不会有任何的结果。
② <=>：安全等于
1>用法
安全等于运算符（<=>）与等于运算符（=）唯一的区别是<=>可以用来对NULL进行判断。
在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。
举例：SELECT 1 <=> 2,1 <=> '1',1 <=> a,0 <=> 'a' FROM DUAL;
结果为：0，1，0，1
SELECT 1 <=> NULL,NULL <=> NULL FROM DUAL;
结果为：0，1
（三）比较运算符（词语型）
① IS NULL、IS NOT NULL、ISNULL
1>在employees表中查询commission_pct为NULL的数据
※推荐 方式一：IS NULL
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;方式二：ISNULL( )函数
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);2>在employees表中查询commission_pct不为NULL的数据
※推荐 方式一：IS NOT NULL
SELECT last_name , salary , commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;方式二：<=>的变形
SELECTlast_name , salary , commission pct
FROM employees
WHERE NOT commission_ pct <=> NULL;② LEAST( )、GREATEST( )
1>选择B，c，m，t中最小的和最大的数据
SELECT LEAST('B','c','m','t'),GREATEST('B','c','m','t')
FROM DUAL;
2>选择first_name，last_name中最小的数据
SELECT LEAST('first_name','last_name')
FROM employees;3>选择first_name，last_name中长度最小的数据
SELECT LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;③ BETWEEN 条件下界1 AND 条件上界2：选择[ 条件下界1 ，条件上界2 ]范围的数据（包含条件1，2）
1>选择salary在[6000,8000]中的数据
方式一：
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
方式二：
SELECT employee_id,last_name,salary
FROM employees
WHERE salary >= 6000 && salary <= 8000;
2>选择salary不在[6000,8000]中的数据
方式一：
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;方式二：
SELECT employee_id,last_name,salary
FROM employees
WHERE salary < 6000 OR salary > 800;④ IN(set)，NOT IN(set)——set指某个集合
1>选择是某个确定的值的数据
方式一：逻辑运算符or
SELECT last_name, salary,department_id
FROMemployees
WHERE department id = 10 or department_id = 20 or department_id = 30;方式二：IN(set)
SELECT last_name,salary,department_id
FROM employees
WHERE department_ id IN(10,20,30);
2>选择不是某集合的值的数据
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN(6000,7000,8000);⑤ LIKE：模糊查询
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
其中，%指不确定个数的字符（0个，1个，或者多个）
1>查找last_name中含有字符a的数据
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';2>查找last_name中以a开头的数据
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';3>查找last_name中含有a和e的数据
方式一：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';方式二：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a% ';⑥ _：一个_（下划线）代表一个不确定的字符
1>选择第二个字符为a的数据
SELECT last name
FROM employees
WHERE last_name LIKE ' a% ';2>选择第二个字符为_且第三个字符为a的数据
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';
注意！'\'为转义字符，'\_'的意思是'_'不再代表一个不确定的字符了，而是一个确定的'_'符号
⑦ REGEXP、RLIKE 正则表达式——更精确的查询
1>语法
(1)‘^′匹配以该字符后面的字符开头的字符串。
(2)‘$'匹配以该字符前面的字符结尾的字符串。
(3)‘.'匹配任何一个单字符。即一个'.'是一个字符
(4)“[...]"匹配在方括号内的任何字符。例如，"[abc]"匹配"a"或"b"或"c"。为了命名字符的范围，使用一个'-'。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
(5)‘*'匹配零个或多个在它前面的字符。例如，“x*"匹配任何数量的'x'字符，“[0-9]*"匹配任何数量的数字，而"*"匹配任何数量的任何字符。
举例：
SELECT 'shkstart' REGEXP '^shk', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;
结果为：1，1，1。
意思分别为：以shk开头，以t结尾，包含hk
SELECT 'atguigu' REGEXP 'gu.gu ' , 'atguigu' REGEXP ' [ab] '
FROM DUAL;
结果为：1，1
意思分别为：包含结构gu与任意一个字符与gu，包含a或者b
（四）逻辑运算符
注：逻辑异或：A XOR B，A与B 的逻辑值不同则返回真，反之返回假。即满足A不满足B，或者满足B不满足A
1>优先级
OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
2>XOR运算规则
逻辑异或 (XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL;如果两个非NULL的值都是o或者都不等于o时，则返回o;如果一个值为o，另一个值不为o时，则返回1。
（五）位运算符
1>
SELECT 12 & 5,12 | 5,12 ^ 5
FROM DUAL;解析：
12 = 8 + 4 = 2的3次方 + 2的2次方（对应二进制为：2的2次方、2的3次方的对应位置为1，其余位置为0）
5 = 4 + 1 = 2的2次方 + 2的0次方（对应二进制为：2的2次方、2的0次方的对应位置为1，其余位置为0）
2>
SELECT 10 & ~1
FROM DUAL;解析：
3>
SELECT 4<<1, 8 >>1
FROM DUAL;
解析：↓ 左移一位 ↓
注意！在一定范围内，每向左移一位，相当于乘以2；每向右移动一位，相当于除以2
七、阶段练习3
1>选择工资不在5000到12000的员工的姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
2>选择在20或50号部门工作的员工姓名和部门号
SELECT last_name,department_id
FROM employees
WHERE department_id IN(20,50);
3>选择公司中没有管理者的员工姓名及job_id
SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;
4>选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
5>选择员工姓名的第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
6>选择姓名中有字母a和k的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' && last_name LIKE '%k%';
7>显示出表 employees表中 first_name 以 'e '结尾的员工信息
SELECT first_name,last_name,employee_id
FROM employees
WHERE first_name LIKE '%e';
8>显示出表 employees 部门编号在80-100之间的姓名、工种
SELECT last_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;
9>显示出表 employees 的 manager_id是100,101,110 的员工姓名、工资、管理者id
SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN(100,101,110);八、排序与分页
① 排序
1>排序的语法：使用ORDER BY 进行排序
2>升降序的语法：升序为：ASC（ascend）；降序为：DESC（descend）
3>如果没有在ORDER BY后指明升序或者降序的话，则默认按照升序ASC排列
举例：将员工信息按照salary降序排列
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;
4>我们可以使用列的别名进行排序。
注意！别名只能在ORDER BY中使用，不能在WHERE中使用！
举例：将员工信息按照年工资升序排列
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;
5>当WHERE与ORDER BY相遇
上图执行顺序概述为：
先按照WHERE对表进行信息筛选
接着按照字段将表进一步缩减
最后进行ORDER BY排序
当执行WHERE时还没有别名！
所以：别名只能在ORDER BY中使用，不能在WHERE中使用！
6>二级排序
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。
如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
② 分页
1>使用LIMIT进行分页操作：
需求：每页显示PageSize条记录，此时显示第PageNo页
公式：
LIMIT 位置偏移数，条目数;
LIMIT (PageNo — 1) * PageSize,PageSize;
注：当位置偏移数为0时，可以直接写为 LIMIT 条目数;
即：LIMIT 0,33; 等价于： LIMIT 33；
2>位置顺序：
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 1000
ORDER BY salary ASC
LIMIT 0,10;3>8.0新特性：LIMIT 条目数 OFFSET 位置偏移数
举例：
表里有107条数据，我们只想显示第33，34条数据怎么办？
方式一：
SELECT employee_id,last_name
FROM employees
#偏移32位后正好为第33位的数据，要显示的33，34位数据共2条。
LIMIT 32,2;方式二：
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 32;4>应用范围
LIMIT可以使用在MysQL、PGSQL、MariaDB、sQLite等数据库中使用，表示分页。
不能使用在SQL Server、DB2、 oracle
九、阶段练习4
1>查询员工的姓名和部门号和年薪，按年薪降序,按姓名
升序显示
SELECT last_name,department_id,salary * (1 + IFNULL(commission_pct,0)) annul_salary
FROM employees
ORDER BY annul_salary 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 last_name,employee_id
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;十、多表查询
（一）笛卡尔积（或交叉连接）
1>笛卡尔积的理解
笛卡尔乘积是一个数学运算。假设我有两个集合X和Y，那么X和Y的笛卡尔积就是X和Y的所有可能组合，也就是第一个对象来自于X，第二个对象来自于Y的所有可能。组合的个数即为两个集合中元素个数的乘积数。
举例：查找员工的id和公寓名称。以下为错误的方法：
SELECT employee_id, department_name
FROM employees,departments;
发生了笛卡尔积的错误，每一个员工都与每个部门匹配了一遍。
错误原因：缺少了夺标的连接条件。
2>多表查询的正确方式：需要有连接条件
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#结果为106条，理应107条。
#原因为：缺少的一条的department_id为NULL3>要求与建议
要求：若查询语句中出现多个表中都存在的字段，则必须指明此字段所在的表。
建议：从sql优化的角度建议多表查询时每个字段前都指明其所在的表。
#此情况下在SELECT处就指明它在哪个表中筛选，否则系统不知道从哪张表中筛选。出错：ambitious
SELECT employees.employee_id,departments.department_name,employees.manager_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;4>给表起别名
SELECT t1.employee_id,t2.department_name,t1.manager_id
FROM employees t1,departments t2
WHERE t1.department_id = t2.department_id;只要给表起了别名，在SELECT或者WHERE中使用表名时，必须使用表的别名，否则会报错。
5>多个表之间相连：AND
如果有n个表实现多表的查询，则需要至少n-1个连接条件
举例：查询员工的employee_id, last_name , department_name , city
SELECT t1.employee_id,t1.last_name,t2.department_name,t3.city
FROM employees t1,departments t2,locations t3
WHERE t1.department_id = t2.department_id AND t2.location_id = t3.location_id;（二） 多表查询的分类
① 等值连接、非等值连接
1>等值连接：WHERE中条件为 =
2>非等值连接
举例：根据员工的工资判断其工资等级
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;② 自连接、非自连接
1>自连接：自己连接自己
查询员工的id、名字及其管理者的id和名字
SELECT e1.employee_id,e1.last_name,e1.manager_id,e2.last_name
FROM employees e1,employees e2
WHERE e1.manager_id = e2.employee_id;③ 内连接、外连接
1>内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
2>外连接： 题目中涉及关键字：所有！即可以显示数据为NULL的数据信息
定义：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表 或 右表中不匹配的行。
分类：左外连接，右外连接，满外连接
左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行
右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行
3>SQL92实现内外连接 MySQL不支持SQL92语法中外连接的写法！
外连接：使用 +
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id(+);4>SQL99语法实现内外连接 JOIN...ON...
两个表的内连接：
SELECT e.last_name,d.department_name
FROM employees e INNER JOIN departments d
#INNER可以省略
ON e.department_id = d.department_id;多个表的内连接：
SELECT e.last_name,d.department_name,l.city
FROM employees e INNER JOIN departments d
#INNER可以省略
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;左外连接：即左边数据更多，右边数据包含NULL
查询所有员工的相关信息
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
#OUTER可以省略
ON e.department_id = d.department_id;
右外连接：即右边数据更多，左边数据包含NULL
查询所有部门的相关信息
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
#OUTER可以省略
ON e.department_id = d.department_id;
满外连接：MySQL不支持FULL OUTER JOIN 语法，Oracle支持
④ UNION 和 UNION ALL操作符
1>区别和联系：UNION和NUION ALL 都可以把表联合起来，但是UNION会执行数据去重操作，UNION ALL 不会。
若明确知道合并数据后的结果不存在重复数据or不需要去除重复的数据，则能用UNION ALL，不用NUION
⑤ 7种JOIN的实现
内连接
SELECT employee_id, department_name
FROM employees e JOIN departments d
ON e. department_id = d. department_id;
左外连接
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
右外连接
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d. department_id;
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
满外连接
方式一：左外连接 + 右外连接去除左边含有NULL的
SELECT employee_id, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
方式二：右外连接 + 左外连接去除右边含有NULL的
SELECT employee_id, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;⑤ SQL99新特性：自然连接（了解）
1>概念：NATURAL JOIN用来表示自然连接。我们可以把自然连接理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
2>缺点：不够灵活，一旦使用就会将表中所有相同的字段都连接上，不一定符合我们的需求。
3>代码对照
SELECT employee_id, last_name , department_name
FROM employees e JOIN departments d
ON e.department_id = d. department_id
AND e. manager_id = d . manager_id ;上述代码使用自然连接等价为：
SELECT employee_id, last_name,department_name
FROM employees e NATURAL JOIN departments d;注意！连接两张表中某种相同的字段和连接表中所有相同的字段查询出来的结果是不同的
⑥ USING连接（了解）
1>特点：USING 指定了具体的相同的字段名称，你需要在USING的括号()中填入要指定的同名字段。同时使用JOIN...USING可以简化JOIN ON的等值连接。
2>代码对照
上述代码使用自然连接等价为：
SELECT employee_ id, last_name , department_name
FROMemployees e JOIN departments d
USING (department_id);注意！超过三个表禁止join。需要join 的字段，数据类型保持绝对一致;多表关联查询时，保证被关联的字段需要有索引。
十、阶段练习5
1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id = 90;
3.选择所有有奖金的员工的 last_name , dapartment_name , location_id , city
SELECT e.commission_pct,e.last_name,d.department_name,l.location_id,l.city
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL;4 .选择city在Toronto工作的员工的 last_name , job_id ,department_id , dapartment_name
SELECT e.last_name,e.job_id,d.department_id,d.department_name,l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE l.city = 'Toronto';
5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为Executive
SELECT d.department_name,d.location_id,e.last_name,e.job_id,e.salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Executive';6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，并起别名
SELECT e1.last_name "employees",e1.employee_id "Emp#",e2.last_name "manager",e2.employee_id "Mgr#"
#公司老板也有员工号，但是没有管理者了，所以用左外连接
FROM employees e1 LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;7.查询哪些部门没有员工
SELECT d.department_id,d.department_name
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;8.查询哪个城市没有部门
SELECT l.city
FROM locations l LEFT JOIN departments d
ON d.location_id = l.location_id
WHERE d.department_id IS NULL;9.查询部都门名为sales或IT的员工信息
SELECT last_name,employee_id
FROM departments d JOIN employees e
ON d.department_id = e.department_id
WHERE d.department_name = 'Sales' OR d.department_name = 'IT';十一、单行函数
① 函数的定义及分类
1>内置函数：系统内置的通用函数
2>自定义函数：按照我们的需求自己编写的
② MySQL的内置函数
MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MysQL信息函数、聚合函数等。
这些丰富的内置函数再分为两类:单行函数、聚合函数（或分组函数）。
③ 单行函数
特点：操作数据对象；只对一行进行变换，且每行返回一个结果；可以嵌套；参数是一列或者一个值。
1>数值函数
ABS(X)：取绝对值函数
SING(X)：取数值的符号函数→正数返回1，负数返回-1，0返回0
PI()：返回常数Π→3.14159……
MOD(x,y)：返回x除以y后的余数
LEAST(e1，e2……)：返回列表中最小值
GREATEST(e1，e2……)：返回列表中最大值
SQRT(X)：当x为正数时，返回x的平方根。当x为负数时，返回NULL
RAND()：返回0~1的随机值
RAND(X)：返回0~1的随机值，且当两个此函数中的X相同时，两个函数的返回值相同且不变
ROUND(X)：四舍五入
ROUND(x,y)：当y为正数时，四舍五入到小数点后y位；当y为负数时，四舍五入到整数y位
TRUNCATE(x,y)：取小数点后y位，后面的直接删去不管
SELECT ROUND(123.456,0),ROUND(123.456,-1),TRUNCATE(123.456,2),TRUNCATE(123.456,0)
FROM DUAL;
CEIL(X)[或者为 CEILING(X) ]：返回大于or等于某值的最小整数
FLOOR(X)：返回小于or等于某值的最大整数
2>角度与弧度互换函数
3>三角函数
4>指数和对数函数
5>进制间的转换函数
CONV(x,f1,f2) ：x是f1进制数，此结果为x在f1进制数下转换成f2进制数后的数值
举例：COVN（10，2，8）：结果为2.2进制下的10在八进制下为2的1次方，即2
6>字符串函数
ASCII(S)：返回字符串s中首个字符的ASCII码
CHAR_LENGTH(s)：返回字符串s中字符的总个数
LENGTH(s)：返回字符串s底层逻辑中的字符串长度
SELECT ASCII('abcd'),ASCII('Abcdef'),CHAR_LENGTH('我们'),CHAR_LENGTH('hello'),
LENGTH('我们'),LENGTH('hello')
FROM DUAL;
CONCAT(s1,s2,s3……)：将字符连接起来
SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "detials"
FROM employees emp JOIN employees mgr
WHERE emp. manager_id = mgr.employee_id;
CONCAT_WS(x,s1,s2,s3……)将s1，s2，s3……用字符x连接起来
INSERT(str,idx,len,replacestr)：选中str中第idx位置的数据，数len个数据，将这len个数据替换成replacestr
注意！字符串的索引是从1开始的！
REPLACE(str,a,b)：将字符串str中的a数据替换成b
UPPER：将字符串转换成大写英文
LOWER：将字符串转换成小写英文
LEFT(str,n)：输出str字符串左边的n位数据
RIGHT(str,n)：输出str字符串右边的n位数据
LPAD(str,len,pad)：将str字符串左边用pad补齐，使字符串str共len位数据
RPAD(str,len,pad)：将str字符串右边用pad补齐，使字符串str共len位数据
TRIM(s)：去掉字符串s开始与结尾的空格
LTRIM(s)：去掉字符串s左边的空格
RTRIM(s)：去掉字符串s右边的空格
TRIM(s1 FROM s)：去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)：去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)：去掉字符串s结尾处的s1
REPEAT(str,n)：str重复n次
SPACE(n)：输出n个空格
STRCMP(s1,s2)： 比较字符串s1，s2的ASCII码大小。s1大于s2时，返回正值，小于s2时返回负值，相等时返回0
SUBSTR(s，index，len)：返回从字符串s的index位置取len个字符
LOCATE('substr','str')：返回字符串substr在字符串str中首次出现的位置。若未找到，则返回0
ELT(m,s1,s2……,sn)：返回指定位置的字符串。若m=1，则返回s1；若m=2，则返回2……
FIELD(s,s1,s2,……sn)：返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)：返回字符串s1在字符串s2中出现的位置。其中字符串s2时一个以逗号分隔的字符串
REVERSE(s)：将字符串s反转
NULLIF(s1,s2)：比较两个字符串，若二者相等返回NULL。若不相等返回s1
7>日期和时间类型
CURDATE()：返回当前年月日
CURTIME()：返回当前时分秒
NOW()：返回当前系统日期和时间
UTC_DATE()：返回UTC(世界标准时间)日期
UTC_TIME()：返回UTC(世界标准时间)时间
type根据个人需要选择上述提及的函数
↓重要
其中fmt·的格式可以如下：
8>流程控制函数
CASE WHEN举例：
其中ELSE的条件是salary < 8000 ；ELSE语句可以省略
CASE ……WHEN…… 举例：
9>加密与解密函数
作用：主要用于对数据库中的数据进行加密和解密处理，以防数据被他人窃取
10>MySQL信息函数
11>其他函数
注意！FORMAT(value，n)中如果n的值小于或者等于0，则只保留整数部分
十二、阶段练习6
①查询当前：NOW()
②查询……，并作为一个列输出：利用CONCAT（）进行连接
③查询员工的工作天数：DATEDIFF（CURDATE（），hire_date ）
④查询员工的工作年数：YEAR（NOW()）- YEAR（hire_date）
⑤想让数据保留到整数：TRUNCATE（salary，0）其中0指的是保留到小数点后几位
十三、多行函数（聚合函数、组函数）
（一）常见的聚合函数
1、AVG、SUM
只适用于数值类型的字段（或者变量）
2、MAX、MIN
适用于数值类型、字符串类型、日期时间类型的字段（或者变量）
3、COUNT
作用：计算指定字段在查询结构中出现的个数
计算表中多少条记录：COUNT（*）或者COUNT（任意一个常数）或者COUNT（具体字段）→不一定对,因为计算指定字段出现的个数时，是不计入指定字段中为NULL值的数据的
AVG、SUM、MAX、MIN、COUNT（具体字段）在计算时都会忽略数值为NULL的数据
4、GROUP BY
1>将某列数据进行分组
举例：计算每个部门的平均工资及最高工资
SELECT department_id,AVG(salary),MAX(salary)
FROM employees
GROUP BY department_id;2>多个列进行分组
举例：计算各个department_id ,job_id的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;注意！对多个列进行分组时，先分谁后分谁结果是一样的
3>规定
#错误的！！！！
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id;
#错误的！！！！①在SELECT语句中包含组函数与数据列，数据列必须写在GROUP BY 中；
在GROUP BY 中写出的数据列，在SELECT语句中可以不出现。
②GROUP BY声明在FROM、WHERE后面，ORDER BY、LIMIT前面
③GROUP BY中使用WITH ROLLUP：在计算出需求结果后将每一列的所有结果计算个平均值
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
注意！WITH ROLLUP和ORDER BY是互相排斥的，当使用WITH ROLLUP时，不能同时使用ORDER BY进行结果排序。
5>HAVING的使用（作用：过滤数据）
举例：查询各个部门中最高工资比10000高（过滤条件）的部门信息
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;①如果过滤条件中使用了聚合函数，则必须用HAVING替换WHERE
如果过滤条件中没有使用聚合函数，则过滤条件声明在WHERE 或者HAVING 中都可以，
但是建议声明在WHERE 中
②HAVING必须声明在GROUP BY的后面
③HAVING的使用前提是：SQL中使用了GROUP BY
6>HAVING 与 WHERE 的对比
①适用范围上：HAVING的适用范围更广
②若过滤条件中没有聚合函数，WHERE 的执行效率更高
7>SQL底层执行原理
①SELECT 语句的完整结构
②SELECT 语句的执行过程
执行过程：
FROM -> ON ->LEFT/RIGHT ->WHERE ->GROUP BY -> HAVING ->SELECT -> DISTINCT(去重) ->ORDER BY -> LIMIT
十四、阶段练习7
1.where子句可否使用组函数进行过滤?
不可以！HAVING子句可以使用组函数进行过滤。
2.查询公司员工工资的最大值,最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;3.查询各job_id的员工工资的最大值，最小值,平均值,总和
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;5.查询员工最高工资和最低工资的差距（DIFFERENCE)
SELECT MAX(salary) - MIN(salary) "DIFFERENCE"
FROM employees;6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6008，没有管理者的员工不计算在内
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT department_name,location_id,COUNT(employee_id),AVG(salary)
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name,location_id
ORDER BY AVG(salary) DESC;8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.department_name,e.job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name,job_id十五、子查询
1、基本使用
1>概念
子查询：查询里边套查询，相当于双重for循环
称谓：外查询（或主查询）、内查询（或子查询）
格式：
2>注意事项
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询使用。
子查询腰包含在括号内，且在比较条件的右侧
3>分类
角度1：内查询返回的结果的条数
单行子查询：子查询的结果只有一条
多行子查询：子查询的结果有多条
角度2：内查询是否被执行多次
相关子查询：内查询返回的结果与外查询相关
举例：查询工资大于本部门平均工资的员工信息（每个员工对应的部门不一样）
不相关子查询：内查询返回的结果与外查询不相关
举例：查询工资大于本公司平均工资的员工信息（公司平均工资是固定的，与员工部门等信息无关）
2、单行子查询
1>操作符
> ， >=，<，<=，!=，=
2>实操
题目一：
题目二：
题目三：
SELECT employee_id,last_name,CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800 THEN 'Canada'
ELSE 'USEA' END)"location"3、多行子查询
1>操作符
2>实操
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
注意：只要比任以一个小，就可以输出这个结果。相当于小于最大的数值。
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
);
注意：比任意一个都小才可以输出这个结果。相当于小于最小的数值。
方法一：（麻烦）
#最低的平均工资是多少
SELECT MIN( avgsal)
FROM(
SELECT AVG(salary) avgsal
FROM employees
GROUP BY department_id
) avgsalary;
#查询平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
SELECT MIN( avgsal)
FROM(
SELECT AVG(salary) avgsal
FROM employees
GROUP BY department_id
) avgsalary
);方法二：（推荐）
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avgsal
FROM employees
GROUP BY department_id
);注意：MySQL中的聚合函数不可以嵌套使用，即：MIN（AVG（salary））是不允许的。
上题的错误写法：（FROM 后面是新的表，新表中没有department_id）
SELECT department_id,MIN( avgsal)
FROM(
SELECT AVG(salary) avgsal
FROM employees
GROUP BY department_id
) avgsalary
GROUP BY department_id;4、相关子查询（另一个角度）
1>操作
方法一：
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE department_id = e1.department_id
);方法二：
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
SELECT department_id,AVG(salary) avgsal
FROM employees
GROUP BY department_id
) di
WHERE e.department_id = di.department_id
AND e.salary > di.avgsal;
SELECT e.employee_id,e.salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.employee_id = d.department_id
) ASC;
2>结论
GROUP BY 与 LIMIT 处不可以用子查询
5、EXISTS与 NOT EXISTS 关键字
① 应用
方法一：
注意：其中DISTINCT是为了去重
方法二：
方法三：
②效率问题
选择：题目中可以使用查询，也可以用自连接，一般情况下建议使用自连接，因为在许多DBMS的处理过程中，对于自连接的处理速度比子查询快得多
原因：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分DBMS中都对自连接处理进行了优化。
十六、阶段练习8
1.查询和Zlotkey相同部门的员工姓名和工资
2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name，job_id，salary
4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
5.查询在部门的location_id为1788的部门工作的员工的员工号
6.查询管理者是King的员工姓名和工资
7.查询工资最低的员工信息: last_name, salary
8.查询平均工资最低的部门信息
9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询)
10.查询平均工资高于公司平均工资的部门有哪些?
11.查询出公司中所有manager 的详细信息
13.各个部门中最高工资中最低的那个部门的最低工资是多少?
14.查询平均工资最高的部门的 manager 的详细信息: last_name，department_id，email,salary
15．查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
十七、创建和管理表
1、数据存储的过程
2、表的操作
1>创建表
点击新建表
其余具体操作可见下篇
https://blog.youkuaiyun.com/lindai329/article/details/97105359
扫一扫
40万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
