IT评测·应用市场-qidao123.com

标题: mysql 基础知识 [打印本页]

---

作者: 瑞星    时间: 2022-12-3 00:17
标题: mysql 基础知识
学习中的思考

在 mysql 学习和使用中，我遇到了不少的难题，我觉得我应该形成一套逻辑思考体系，可以让我在初识 mysql 的过程中加入理性思考，从一开始就探求原理，了解所学内容的核心和关键点，做到一叶知秋而不是只见树木不见森林。

• mysql 几种数据类型的底层是如何存储的，不同的 sql 语句对其有何限制？
  
• 学习 sql 语句时，需要思考：这个 sql 语句的底层是如何实现的，对哪些数据类型起作用，有什么限制，如果这个 sql 语句对单字段可以使用，可否在多字段中使用，在多字段使用的过程中，可否做到只对一个字段起作用或者对多个字段同时起作用？
  
• 隔离级别是如何实现的？
  

MySQL 基础

一、为什么要学习数据库

二、数据库的相关概念：

• DB：数据库(database)，存储数据的“仓库”，它保存了一系列有组织的数据。
  
• DBMS：数据库管理系统(Database Management System)，数据库是通过 DBMS 创建和操作的容器。
  
• SQL：结构化查询语言(Structure Query Language)，专门用来与数据库通信的语言。
  

SQL的优点：

• 不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持 SQL 。
  
• 简单易学。
  
• 强有力，灵活使用。
  

三、数据库存储结构的特点：

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

四、初识MySQL

MySQL产品的介绍

DBMS分为两类：

• 基于共享文件系统的DBMS(Access)
  
• 基于客户机-服务器的DBMS(MySQL、Oracle、SqlServer)
  

MySQL产品的安装

MySQL安装

MySQL卸载

• 控制面板卸载
  
• 安装路径删除 mysql 文件夹 + 删除 C 盘根目录下 ProgramData下 mysql 文件夹；
  
• 清理注册表：
  

1. A. HKEY_LOCAL_MAACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL 目录
2. B. HKEY_LOCAL_MAACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL 目录
3. C. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL 目录
4. D. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControl001\Services\MYSQL 目录
5. E. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControl002\Services\MYSQL 目录
6. F. HKEY_LOCAL_MAACHINE\SYSTEM\CurrentControlSet\Services\MYSQL 目录

复制代码

• 删除 C：\Document and Settings\All Users\Application Data\MySQL 目录（隐藏的目录）
  

MySQL服务的启动和停止

• 方式一：计算机 -> 管理 -> 服务
  
• 方式二：命令行(管理员)
  net start 服务名(启动服务)
  net stop 服务名(停止服务)
  

MySQL服务端的登录和退出

• 方式一：通过 mysql 自带的客户端；只限于 root 用户
  
• 方式二：通过 windows 自带的客户端
  登录：mysql [-h 主机名 -P 端口号] -u 用户名 -p密码
  退出：exit 或者 ctrl + c
  

MySQL常见的命令

• 查看当前所有数据库 show databases;
  
• 打开指定的库 use 库名;
  
• 查看当前库的所有表 show tables;
  
• 查看其它库的所有表 show tables from 库名;
  
• 查看目前位于哪个库 select database();
  
• 创建表
  

1. create table 表名(
2.     列名     列类型，
3.     列名     列类型，
4.     ......
5. );

复制代码

• 查看表结构 desc 表名;
  
• 查看服务器的版本
  

• 方式一：登录到mysql服务器 select version();
  
• 方式二：没有登录到mysql服务器 mysql --version
  

MySQL语法规范

• 不区分大小写，但建议关键字大写，表名、列名小写。
  
• 每条命名最好用分号或 \G、\g 结尾（会格式化输出数据）
  
• 每条命令根据需要，可以进行缩进或换行。
  
• 注释
  

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

• 试说出查询语句中涉及到的所有关键字，以及执行先后顺序
  

1. SELECT 查询列表             7
2. FROM 表1 别名                第1步
3. 连接类型 join 表2             2
4. ON 连接条件                 3
5. WHERE 筛选条件                 4
6. GROUP BY 分组列表             5
7. HAVING 分组后的筛选            6
8. ORDER BY 排序列表             8
9. LIMIT 偏移，条目数;            9

复制代码

五、DQL(Data Query Language)语言的学习

基础查询

语法：

1. SELECT 查询列表 FROM 表名;

复制代码

特点：

1. 1.查询列表可以是：表中的字段、常量值、表达式、函数；
2. 2.查询的结果是一个虚拟的表格；查询表中的某个字段

复制代码

• 查询表中的所有字段
  

1. > 方式一：
2.     SELECT `employee_id`,
3.         `first_name`,
4.     FROM
5.         employees;
6. > 方式二：
7.     SELECT * FROM employees;

复制代码

• 查询常量值
  

1. SELECT 100;
2. SELECT 'john';    //字符和日期型的常量值必须用单引号，数值型不需要；

复制代码

• 查询表达式
  

1. SELECT 100*99;

复制代码

• 查询函数
  

1. SELECT VERSION();
2. SELECT 函数名(实参列表);

复制代码

• 起别名
  

• 便于理解；
  
• 如果要查询的字段有重名的情况，使用别名可以区分开来；
  
• 如果别名中含有关键词，用 单引号 或者 双引号 将别名括起来；
  

1. 方式一：使用 AS
2.     SELECT 100%98 AS 结果;
3.     SELECT last_name AS 姓, first_name AS 名 FROM employees;
5. 方式二：使用空格
6.     SELECT last_name 姓,first_name 名 FROM employees;

复制代码

• 去重 distinct
  
• DISTINCT 需要放到所有列名的前面。
  
• DISTINCT 其实对后面所有列名的 组合 进行去重。
  

1. SELECT DISTINCT 字段名 FROM 表名;
3. 案例：查询员工表中涉及到的所有部门编号
4.     SELECT DISTINCT department_id FROM employees;

复制代码

• concat 函数：和null拼接结果为null
  

1. 案例：查询员工名和姓连接成一个字段，并显示为 姓名
2.   SELECT
3.       CONCAT(last_name,first_name) AS 姓名
4.   FROM
5.       employees;
7. 案例：显示出表employee的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
8.   SELECT
9.       CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct，0)) AS out_put
10.   FROM
11.       employees;

复制代码

• ifnull 函数
  

1. 功能：判断某字段或表达式是否为null，如果为null，返回指定值，否则返回原本的值
2. SELECT ifnull(commissiop_pct,0) from employees;

复制代码

空值参与运算

• 所有运算符或列值遇到 null 值，运算的结果都为 null。
  
• 在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
  

运算符

算数运算符

• 加法与减法运算符
  

mysql 中的加号，只有一个功能：运算符，做加法运算。

1. SELECT 100+90; 两个操作数都为数值型，则做加法运算
3. SELECT '123'+90;只要其中一方为字符型，试图将字符型数值转成数值型，
4.                 如果转换成功，则继续做加法运算。
5.                 如果转换失败，则将字符型数值转换成0
7. SELECT null+10;    只要其中一方为null，则结果肯定为null

复制代码

• 乘法与除法运算符
  

• 一个数乘以整数1和除以整数1后仍得原数；
  
• 个数乘以浮点数1 和除以浮点数1后变成浮点数，数值与原数相等；
  
• 一个数除以整数后，不管是否能除尽，结果都为一个 浮点数；
  
• 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
  
• 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
  
• 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
  

• 求模（求余）运算符
  

结果的符号与被模数的符号一致。
比较运算符

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

• 等号运算符
  

• 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
  
• 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
  
• 使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他返回结果与等于运算符相同。
  

• 不等于运算符
  

• LEAST(值1，值2，...，值n)：在有两个或多个参数的情况下，返回最小值。当比较值列表中有NULL时，不能判断大小，返回值为NULL。
  
• GREASTEST：最大值运算符。
  

逻辑运算符

• 逻辑非运算符 逻辑非（NOT或!）运算符表示当给定的值为0时返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL。
  
• 逻辑与运算符 逻辑与（AND或&&）运算符是当给定的所有值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL。
  
• 逻辑或运算符 逻辑或（OR或||）运算符是当给定的值都不为NULL，并且有任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL。
  
• 逻辑异或运算符 逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1。
  
• AND 的优先级高于OR。
  

位运算符

条件查询

<ul>语法：

1. SELECT
2.     查询列表
3. FROM
4.     表名
5. WHERE
6.     筛选条件;

复制代码

分类：
<ul>按条件表达式筛选

条件运算符： > < = !=  >= 12000的员工信息    SELECT        *    FROM        employees    WHERE        salary>120000;#案例2：查询部分编号不等于90的员工名和部门编号    SELECT        last_name,        department_id    FROM        employees;    WHERE        department_id90;[/code]

• 按逻辑表达式筛选
  

1. #案例1：查询工资>12000的员工信息
2.     SELECT
3.         *
4.     FROM
5.         employees
6.     WHERE
7.         salary>120000;
9. #案例2：查询部分编号不等于90的员工名和部门编号
10.     SELECT
11.         last_name,
12.         department_id
13.     FROM
14.         employees;
15.     WHERE
16.         department_id<>90;

复制代码

常见函数

1. #案例一：查询工资在10000到20000之间的员工名、工资以及奖金
2.     SELECT
3.         last_name,
4.         salary,
5.         commission_pct
6.     FROM
7.         employees
8.     WHERE
9.         salary >= 10000
10.     AND
11.         salary <= 20000

复制代码

单行函数

• 操作数据对象
  
• 接受参数返回一个结果
  
• 只对一行进行变换
  
• 每行返回一个结果
  
• 可以嵌套
  
• 参数可以是一列或一个值
  

字符函数

注意：MySQL中，字符串的位置是从1开始的。
函数作用ASCII(S)返回字符串 S 中的第一个字符的 ASCII 码值CHAR_LENGTH(s)返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同LENGTH(s)返回字符串s的个数，和字符集有关CONCAT(s1,s2,...,sn)连接s1,s2,......sn 为一个字符串CONACT_WS(x,s1,s2,......,sn)同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上xINSERT(str,idx,len,replacestr)将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestrREPLACE(str,a,b)用字符串b替换字符串str中所有出现的字符串aUPPER(s) 或 UCASE(s)将字符串s的所有字母都转换成大写字母LOWER(s) 或 LCASE(s)将字符串s的所有字母都转成小写字母LEFT(str,n)返回字符串str最左边的n个字符RIGHT(str,n)返回字符串str最右边的n个字符LPAD(str,len,pad)用字符串pad对str最左边进行填充，直到str的长度为len个字符RPAD(str,len,pad)用字符串pad对str最右边进行填充，直到str的长度为len个字符LTRIM(s)去掉字符串s左侧的空格RTRIM(s)去掉字符串s右侧的空格TRIM(s)去掉字符串s开始与结尾的空格TRIM(s1 FROM s)去掉字符串s开始与结尾的s1TRIM(LEADING s1 FROM s)去掉字符串s开始处的s1TRIM(TRAILING s1 FROM s)去掉字符串s结尾处的s1REPEAT(str,n)返回str重复n次的结果SPACE(n)返回n个空格STRCMP(s1,s2)比较字符串s1,s2的ASCII码值的大小SUBSTR(s,index,len)返回从字符串s的index位置其len个字符，作用与SUBSTING(s,n,len)相同LOCATE(substr,str)返回字符串 substr 在字符串 str 中首次出现的位置，作用与 POSITION(substr IN str)、INSTR(str,substr) 相同。未找到，返回0ELT(m,s1,s2,......,sn)返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回snFIELD(s,s1,s2,...,sn)返回字符串s在字符串列表中第一次出现的位置FIND_IN_SET(s1,s2)返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串REVERSE(s)返回s反转后的字符串NULLIF(value1,value2)比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value

• length()：获取参数值的字节个数
  1. #案例1：查询员工名中包含符a的员工信息
  2.     SELECT
  3.         *
  4.     FROM
  5.         employees
  6.     WHERE
  7.         last_name LIKE '%a%';
  9. #案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
  10.     SELECT
  11.         last_name,
  12.         salary,
  13.     FROM
  14.         employees
  15.     WHERE
  16.         last_name LIKE '__e_a%';
  18. #案例3：查询员工名中第二个字符为_的员工名
  19.     SELECT
  20.         last_name
  21.     FROM
  22.         employees
  23.     WHERE
  24.         last_name LIKE '_\_%';    或者        last_name LIKE '_$_%' ESCAPE '$';

  复制代码
• concat()：拼接字符串
  1. 案例1：查询员工编号在100到120之间的员工信息
  2.     SELECT
  3.         *
  4.     FROM
  5.         employees
  6.     WHERE
  7.         employee_id BETWEEN 100 AND 120;

  复制代码
• upper()、lower()
  
• substr()、substring()：
  注意：索引从1开始
  1. 案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号。
  2.     SELECT
  3.         last_name,
  4.         job_id
  5.     FROM
  6.         employees
  7.     WHERE   
  8.         job_id IN ('IT_PROT','AD_VP','AD_PRES');

  复制代码
• instr()：返回字串第一次出现的索引，如果找不到返回0
  
• trim()：
  1. 案例1：查询没有奖金的员工名和奖金率
  2.     SELECT
  3.         last_name,
  4.         commission_pct
  5.     FROM
  6.         employees
  7.     WHERE
  8.         commission_pct IS NULL;        IS NOT NULL

  复制代码
• lpad()：用指定的字符实现左填充指定长度
  1. 案例1：查询没有奖金的员工名和奖金率
  2.     SELECT
  3.         last_name,
  4.         commision_pct
  5.     FROM
  6.         employees
  7.     WHERE
  8.         commission_pct <=> NULL;
  10. 案例2：查询工资为12000的员工名和奖金率
  11.     SELECT
  12.         last_name,
  13.         commision_pct
  14.     FROM
  15.         employees
  16.     WHERE
  17.         salary <=> 12000;

  复制代码
• rpad()：用指定的字符实现右填充指定长度
  1. SELECT     查询列表
  2. FROM     表
  3. [WHERE     筛选条件]
  4. order by 排序列表 [asc/desc]

  复制代码
• replace()：替换
  

数学函数

• round()：四舍五入
  1. #案例1：查询愚弄信息，要求工资从高到低排序
  2. SELECT * FROM employees
  3. ORDER BY salary DESC;
  5. #案例2：查询部门编号>=90的员工信息，按入职时间的先后顺序进行排序
  6. SELECT *
  7. FROM employees
  8. WHERE department_id>=90
  9. ORDER BY hiredate ASC;
  11. #案例3：按年薪的高低显示员工的信息和年薪[按表达式排序]
  12. SELECT *,salary*12*(1+IFNULL(commission_prt,0)) 年薪
  13. FROM employees
  14. ORDER BY salary*12*(1+IFNULL(commission_prt,0)) DESC; 或 ORDER BY 年薪 DESC;
  16. #案例5：按姓名的长度显示员工的姓名和工资[按函数排序]
  17. SELECT LENGTH(last_name) 字节长度,last_name,salary
  18. FROM employees
  19. ORDER BY LENGTH(last_name) DESC;
  21. #案例6：查询员工信息，要求先按工资升序，再按员工编号降序[按多个字段排序]
  22. SELECT *
  23. FROM employees
  24. ORDER BY salary ASC,employee_id DESC;

  复制代码
• ceil()：向上取整，返回 >= 该参数的最小整数
  
• 
  floor()：向下取整，返回  20000，显示A级别                        如果工资 > 15000，显示B级别                        如果工资 > 10000，显示C级别                        否则，显示D级别SELECT salary,CASEWHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别FROM employees;[/code]
  

加密与解密函数

函数用法PASSWORD(str)返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用语用户的密码加密。在mysql8.0中被弃用。MD5(str)返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL。不可逆。SHA(str)从原明文密码str计算并返回加密后的密码字符串，当参数为 NULL 时，返回 NULL。不可逆。ENCODE(value,password_seed)返回使用 password_seed 作为加密密码加密value。mysql8.0后被弃用。DECODE(value,password_seed)返回使用password_seed作为加密密码解密valuemysql 信息函数

函数用法VERSION()返回当前MySQL的版本号CONNECTION_ID()返回当前MySQL服务器的连接idDATABASE()，SCHEMA()返回MySQL命令行当前所在的数据库USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名”CHARSET(value)返回字符串value自变量的字符集COLLATION(value)返回字符串value的比较规则其他函数

函数用法FORMAT(value,n)返回对数字value进行格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位CONV(value,from,to)将value的值进行不同进制之间的转换INET_ATON(ipvalue)将以点分隔的IP地址转化为一个数字INET_NTOA(value)将数字形式的IP地址转化为以点分隔的IP地址BENCHMARK(n,expr)将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间CONVERT(value USING char_code)将value所使用的字符编码修改为char_code分组函数

• 功能：用作统计使用，又称为聚合函数或统计函数或组函数。输入的是一组数据的集合，输出的是单个值。
  
• 分类：sum 求和、 avg 平均值、 max 最大值、 min 最小值、 count 计算个数；
  
• 特点：
  
• sum、avg一般用于处理数值型；max、min、count 可以处理任何类型；
  
• 以上分组函数都忽略 null 值；
  
• 可以和 distinct 搭配实现去重的运算；
  

1. select 函数名(实参列表) [from 表]
2. (1) 单行函数
3.     concat、length、ifnull等
4. (2) 分组函数
5.     做统计使用，又称为统计函数、聚合函数、组函数

复制代码

• count函数的详细介绍，一般使用 COUNT(*) 统计行数；
  

1. SELECT LENGTH('张三丰hahaha');
3. SHOW VARIVALE LIKE '%char%'; 查看字符集

复制代码

• 和分组函数一同查询的字段要求是 group by 后面的字段；
  

1. SELECTV CONCAT(last_name,'_',first_name) 姓名 FROM employees;

复制代码

分组查询

• 语法
  

1. # 截取从指定索引处后面所有字符
2.     SELECT SUBSTR('李莫愁爱上了路站元',7) out_put;
4. # 截取从指定所引处指定字符长度的字符
5.     SELECT SUBSTR('李莫愁爱上了路站元',1,3) out_put;
7. #案例：姓名中首字符大写，其他字符小写，然后用 _ 拼接，显示出来
8. SELECT CONTACT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name),2)) out_put;

复制代码

• 明确：WHERE 一定放在 FROM 后面；
  
• 注意：查询列表必须特殊，要求是分组函数和 group by 后出现的字段；
  
• 当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 是互相排斥的。
  

1. SELECT TRIM('a' FROM 'aaaaa张aa翠山aaaaaa') AS out_put;

复制代码

• 特点：
  
• 分组查询中的筛选条件分为两类：
  

1. SELECT LPAD('殷素素',10,'*') AS out_put;     //10为总字符数
2. SELECT LPAD('殷素素',2,'*') AS out_put;     //殷素

复制代码

• group by 子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开，没有顺序要求)，表达式或函数(用的较少)；
  
• 也可以添加排序(排序放在整个分组查询的最后)；
  
• 从 mysql8.0 开始，group by不再支持隐式排序。
  
• 当 group by 这列有 null 值时，group 会把他们当成是同一个直接聚合。
  
• 如果过滤条件中使用了聚合函数，则必须使用 HAVING 来替换 WHERE。否则，报错。
  
• 案例：
  

1. SELECT RPAD('殷素素',12,'ab') AS out_put;

复制代码

1. SELECT ROUND(1.65);
2. SELECT ROUND(1.567,2);         // 1.57

复制代码

连接查询

• 含义：又称多表查询，当查询的字段来自多个表时；
  
• 笛卡尔乘积现象：表1有 m 行，表2有 n 行，结果 m*n 行
  

发生原因：没有有效的连接条件；
如何避免：添加有效的连接条件；
分类：

• 按年代分类：
  

sql92标准：仅仅支持内连接
    等值
    非等值
    自连接
    也支持一部分外连接(用于 oracle、sqlserver，mysql 不支持)
     sql99标准(推荐)：支持内连接+外连接(左外和右外)+交叉连接

• 按功能分类：
  

• 内连接
  等值连接
  非等值连接
  自连接
  
• 外连接
  左外连接
  右外连接
  全外连接(mysql不支持)
  
• 交叉连接
  

sql92 标准

等值连接

• 多表等值连接的结果为多表的交集部分；
  
• n表的连接，至少需要n-1个连接条件；
  
• 多表的顺序没有要求；
  
• 一般需要为表起别名；
  
• 可以搭配前面介绍的所有子句使用，比如排序、筛选、分组；
  

语法：

1. SELECT TRUNCATE(1.6999,1);    //1.6

复制代码

案例：

1. mod(a,b) = a-a/b*b

复制代码

• 为表起别名
  
• 提高语句的简洁度；
  
• 区分多个重名的字段；
  
• 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定；
  

1. SELECT NOW();

复制代码

• 两个表的顺序可以调换
  
• 加筛选
  

1. SELECT YEAR(now());
2. SELECT YEAR('1998-1-1') 年;

复制代码

• 加分组
  

1. STR_TO_DATE('9-13-1999','%m-%d-%Y')        1999-09-13

复制代码

• 加排序
  

1. DATE_FORMAT('2018/6/6','%Y年%m月%d日')    2018年06月06日

复制代码

• 三表连接
  

1. SELECT VERSION();
2. SELECT DATABASE();
3. SELECT USER();
4. SELECT password('字符')：返回该字符的密码形式 自动加密
5. MD5('字符')：返回该字符的md5加密形式

复制代码

非等值连接

1. if(条件表达式,表达式1,表达式2)：如果条件表达式成立，返回表达式1;否则返回表达式2

复制代码

1. SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
2. FROM employees;

复制代码

自连接

1. case 要判断的字段或表达式
2. when 常量1 then 要显示的值1或语句1;
3. when 常量2 then 要显示的值2或语句2;
4. ...
5. else 要显示的值n或语句n;
6. end

复制代码

sql99 语法

1. #案例：要查询员工的工资，要求   
2. 部门号=30，显示的工资为1.3倍
3. 部门号=40，显示的工资为1.4倍
4. 部门号=50，显示的工资为1.5 倍
5. 其他部门，显示的工资为原工资
7. SELECT salary 原始工资,department_id,
8.     CASE department_id
9.     WHEN 30 THEN salary*1.3
10.     WHEN 40 THEN salary*1.4
11.     WHEN 50 THEN salary*1.5
12.     ELSE salary
13.     END AS 新工资
14.     FROM employees;

复制代码

内连接：inner

1. case
2. when 条件1 then 要显示的值1或语句1
3. when 条件2 then 要显示的值2或语句2
4. ...
5. else 要显示的值n或语句n
6. end

复制代码

分类：等值、非等值、自连接

• 等值连接
  特点：
  
  
  
  • 添加排序、分组、筛选；
    
  • inner 可以省略；
    
  • 筛选条件放在 where 后面，连接条件放在 on 后面，提高分离性，便于阅读；
    
  • inner join 连接和 sql92 语法中的等值连接效果是一样的，都是查询多表；
    
  1. #案例：查询员工的工资的情况    如果工资 > 20000，显示A级别
  2.                         如果工资 > 15000，显示B级别
  3.                         如果工资 > 10000，显示C级别
  4.                         否则，显示D级别
  5. SELECT salary,
  6. CASE
  7. WHEN salary>20000 THEN 'A'
  8. WHEN salary>15000 THEN 'B'
  9. WHEN salary>10000 THEN 'C'
  10. ELSE 'D'
  11. END AS 工资级别
  12. FROM employees;

  复制代码
• 非等值连接
  1. ```sql
  2. SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
  3. ```

  复制代码
• 自连接
  1. ```sql
  2. SELECT COUNT(salary) FROM employees;
  3. SELECT COUNT(*) FROM employees;    //只要某一行有一个不为null，就相当于+1(不包含NULL值)
  4. SELECT COUNT(1) FROM employees; //统计1的个数，相当于计算行数
  6. 效率：
  7.     如果使用的是 MyISAM 存储引擎，则三者效率相同，都是O(1)
  8.     如果使用的是 InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(字段)
  9. ```

  复制代码

外连接

• 左外: left [outer]
  
• 右外: right [outer]
  
• 全外: full [outer]
  

应用场景：用于查询一个表中有，另一个表中没有的记录；
特点：

• 外连接的查询结果为主表中的所有记录；
  

如果从表中有和它匹配的，则显示匹配的值；
如果从表中没有和它匹配的，则显示 null；
外连接查询结果=内连接结果 + 主表中有而从表中没有的记录；

• 左外连接，left join 左边的是主表；
  右外连接，right join 右边的是主表；
  

全外连接，两边都是主表。

• 左外和右外交换两个表的顺序，可以实现同样的效果；
  
• 全外连接=内连接的结果+表1中有但表2没有的+表2中有的但表1没有的；
  

1. ```
2. SELECT AVG(salary),employee_id FROM employees; 有问题，AVG(salary)只有一行，employee_id有很多行
3. ```

复制代码

交叉连接：cross

1.                                                  #执行顺序
2. SELECT         column，group_function(column)           5
3. FROM         table                                    1
4. [WHERE         condition]                                2
5. GROUP BY     group_by_expression                     3
6. [HAVING     分组后的筛选]                                4
7. [ORDER BY     column];                                 6

复制代码

sql99 语法新特性

自然连接

NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ，然后进行等值连接 。

1. 为了更好的理解 grouy by 多个列和聚合函数的应用，我们可以假设在group by执行之后，生成了一个虚拟的中间表。相同的group by字段合并成一行，其余的字段分别写到一个单元格里。
2. 对于id和number里面的单元格有多个数据的情况，使用聚合函数。聚合函数就是用来输入多个数据，输出一个数据的。
3. #对name字段进行分组
4. id    name    number
5. 1    aa        2
6. 2            3
8. 3            4
9. 4    bb        5
10. 8            6

复制代码

USING 连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的 同名字段 进行等值连接。但是只能配合JOIN一起使用。比如：

1. ```
2.             数据源            位置                    关键字
3. 分组前筛选    原始表            group by子句的前面        where
4. 分组后筛选    分组后的结果集    group by子句的后面         having
5. ```

复制代码

子查询

含义：出现在其他语句中的 select 语句，称为子查询或内查询；
外部的查询语句，称为主查询或外查询。
外面的语句可以是 insert、update、delete、select 等，一般 select 作为外面语句较多。
分类：

• 按子查询出现的位置：
  

• select 后面：
  
  
  
  • 仅仅支持标量子查询
    
  
  
• from 后面：
  
  
  
  • 支持表子查询
    
  
  
• where 或 having 后面
  
  
  
  • 标量子查询(单行)
    
  • 列子查询 (多行)
    
  • 行子查询
    
  
  
• exists 后面(相关子查询)（返回的结果1或0，类似布尔操作）
  
  
  
  • 表子查询
    
  
  

• 按结果集的行列数不同：
  

• 标量子查询(单行子查询)(结果集是一个数据：一行一列)
  
• 列子查询(多行子查询)(结果集是一列：一列多行)：一般搭配着多行操作符使用 in、any/some、 all
  
• 行子查询(结果集是一行：一行多列)
  
• 表子查询(结果集一般为多行多列)
  

where 或 haing 后面

• 标量子查询
  
• 列子查询
  
• 行子查询
  

特点：
<ul>子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用 > < >= (1)结果    SELECT *    FROM employees    WHERE salary>(        SELECT salary        FROM employees        WHERE last_name = 'Abel'    );#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资#(1)查询141号员工的job_id    SELECT job_id    FROM employees    WHERE employee_id = 141#(2)查询143号员工的salary    SELECT salary    FROM employees    WHERE employee_id = 143#(3)查询员工的姓名、job_id和工资，要求job_id=(1)并且salary>(2)    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#(1)查询公司的最低工资    SELECT MIN(salary)    FROM employees;#(2)查询last_name，job_id和salary，要求salary=(1)    SELECT last_name,job_id,salary    FROM employees    WHERE salary = (        SELECT MIN(salary)        FROM employees;    );#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资#(1)查询50号部门的最低工资    SELECT MIN(salary)    FROM employees    WHERE department_id =50#(2)查询每个部门的最低工资    SELECT MIN(salary),department_id    FROM employees    GROUP BY department_id#(3)在(2)的基础上，满足min(salary)>(1)    SELECT MIN(salary),department_id    FROM employees    GROUP BY department_id    HAVING MIN(salary)>(        SELECT MIN(salary)        FROM employees        WHERE department_id =50    );[/code]非法使用标量子查询：

1. #案例1：查询每个工种的最高工资
2.     SELECT MAX(salary),job_id
3.     FROM employees
4.     GROUP BY job_id;
6. #案例2：查询每个位置上的部门个数
7.     SELECT COUNT(*),location_id
8.     FROM employees
9.     GROUP BY location_id;
11. #案例3：查询邮箱中包含a字符的，每个部门的平均工资
12.     SELECT AVG(salary),department_id
13.     FROM employees
14.     WHERE email LIKE '%a%'
15.     GROUP BY department_id;
17. #案例4：查询有奖金的每个领导手下员工的最高工资
18.     SELECT MAX(salary),manager_id
19.     FROM employees
20.     WHERE commission_pct IS NOT NULL
21.     GROUP BY manager_id;
23. #案例5：查询哪个部门的员工个数>2
24. (1) 查询每个部门员工个数
25.     SELECT COUNT(*),department_id
26.     FROM employees
27.     GROUP BY department_id;
28. (2) 根据(1)的结果进行筛选，查询哪个部门的员工个数>2
29.     SELECT COUNT(*),department_id
30.     FROM employees
31.     GROUP BY department_id
32.     HAVING COUNT(*)>2;

复制代码

列子查询(多行子查询)

• 返回多行
  
• 使用多行比较操作符
  

操作符含义IN / NOT IN等于列表中的任意一个ANY | SOME和子查询返回的某一个值比较ALL和子查询返回的所有值比较

1. #案例：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
2. (1)查询每个工种有奖金的员工的最高工资
3.     SELECT MAX(salary),job_id
4.     FROM employees
5.     WHERE commission_pct IS NOT NULL
6.     GROUP BY job_id;
8. (2)根据(1)结果继续筛选，最高工资>12000
9.     SELECT MAX(salary),job_id
10.     FROM employees
11.     WHERE commission_pct IS NOT NULL
12.     GROUP BY job_id
13.     HAVING MAX(salary)>12000;
15. #案例：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资；
16. (1)查询每个领导手下的员工最低工资
17.     SELECT MIN(salary),manager_id
18.     FROM employees
19.     GROUP BY manager_id;
20. (2)添加筛选条件：编号>102
21.     SELECT MIN(salary),manager_id
22.     FROM employees
23.     WHERE manager_id > 102
24.     GROUP BY manager_id;
25. (3)添加筛选条件：最低工资>5000
26.     SELECT MIN(salary),manager_id
27.     FROM employees
28.     WHERE manager_id > 102
29.     GROUP BY manager_id
30.     HAVING MIN(salary)>5000;
32.      > 按表达式或函数分组
33.              > 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的的有哪些
34.                  (1)查询每个长度的员工个数
35.                      SELECT COUNT(*),LENGTH(last_name)
36.                      FROM employees
37.                      GROUP BY LENGTH(last_name);
38.                  (2)添加筛选条件
39.                      SELECT COUNT(*),LENGTH(last_name)
40.                      FROM employees
41.                      GROUP BY LENGTH(last_name)
42.                      HAVING COUNT(*)>5;
43.      > 按多个字段分组
44.          #案例：查询每个部门每个工种的员工的平均工资
45.              SELECT AVG(salary),department_id,job_id
46.              FROM employees
47.              GROUP BY job_id,department_id;
49.          #添加排序
50.         #案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示
51.             SELECT AVG(salary),department_id,job_id
52.              FROM employees
53.              WHERE department_id IS NOT NULL
54.              GROUP BY job_id,department_id
55.              ORDER BY AVG(salary) DESC;

复制代码

exists后面(相关子查询)

1. SELECT 查询列表
2. FROM 表1 别名, 表2 别名
3. WHERE 表1.key = 表2.key
4. [AND 筛选条件]
5. [GROUP BY 分组字段]
6. [HAVING 分组后的筛选]
7. [ORDER BY 排序字段]

复制代码

1. #案列1：查询女生名和对应的男生名
2. SELECT NAME,boyName
3. FROM boys,beauty
4. WHERE beauty.boyfriend_id = boys.id;
6. #案例2：查询员工名和对应的部门名
7. SELECT last_name,department_name
8. FROM employees,departments
9. WHERE employees.`department_id` = departments.`department_id`;

复制代码

子查询经典案例

1. #案例：查询员工名、工种号、工种名
2.     SELECT e.last_name,e.job_id,j.job_title     #employees.last_name错误
3.     FROM employees AS e,jobs j
4.     WHERE e.`job_id` = j.`job_id`;

复制代码

分页查询

应用查询：当要显示的数据，一页显示不全，需要分页提交 sql 请求。
语法：

1. #案例：查询有奖金的员工名、部门名
2.     SELECT last_name,department_name
3.     FROM employees e,departements d
4.     WHERE e.`department_id` = d.`department_id`
5.     AND e.`commission_pct` IS NOT NULL;
7. #案例2:查询城市名中第二个字符为o的部门名和城市名
8.     SELECT dapartment_name,city
9.     FROM departments d,location l
10.     WHERE d.`location_id` = l.`location_id`
11.     AND city LIKE `_o%`;

复制代码

特点：

• limit 语句放在查询语句的最后
  
• 公式
  

1. #案例1：查询每个城市的部门个数
2.     SELECT COUNT(*) 个数,city
3.     FROM departments d,locations l
4.     WHERE d.`location_id` = l.`location_id`
5.     GROUP BY city;
7. #案例2：查询有奖金的部门的部门名和部门的领导编号和该部门的最低工资
8.     SELECT department_name,d.`manager_id`,MIN(salary)
9.     FROM dapartment d,employees e
10.     WHERE d.`department_id` = e.`department_id`
11.     AND commission_pct IS NOT NULL
12.     GROUP BY department_name,d.mamager_id;

复制代码

1. #案例：查询每个工种的工种名和员工的个数，并且按员工的个数降序
2.     SELECT job_title,COUNT(*)
3.     FROM employees e,jobs j
4.     WHERE e.`job_id` = j.`job_id`
5.     GROUP BY job_title
6.     ORDER BY COUNT(*) DESC;

复制代码

union 联合查询

union：将多条查询语句的结果合并成一个结果
语法：

1. #案列：查询员工名、部门名和所在的城市
2.     SELECT last_name,department_name,city
3.     FROM employees e,department d,location l
4.     WHERE e.`department_id` = d.`department_id`
5.     AND d.`location_id` = l.`location_id`
7.     AND city LIKE 's%'
8.     ORDER BY department_name DESC;

复制代码

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。
特点：

• 要求多条查询语句的查询列数是一致的；
  
• 要求多条查询语句的查询的每一列的类型和顺序最好一致；
  
• union 关键字默认去重，如果使用 union all 可以包含重复项；
  

SELECT 的执行过程

查询的结构

1. SELECT     查询列表
2. FROM     表1 别名, 表2 别名
3. WHERE     非等值的连接条件
4. [AND     筛选条件]
5. [GROUP BY 分组字段]
6. [HAVING 分组后的筛选]
7. [ORDER BY 排序字段]

复制代码

SELECT 执行顺序

1. #案例1：查询员工的工资和工资级别
2.     SELECT salary,grade_level
3.     FROM employess e,job_grades g
4.     WHERE salary BETWEEN g.`lowest_sal` AND g.`hightest_sal`
5.     //AND g.`grade_level` = 'A';

复制代码

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个 虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。
SQL 的执行原理

• 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table） 1-1；
  
• 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
  
• 添加外部行。如果我们使用的是左连接、右连接或者全连接，就会涉及到外部行。也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
  
• 当我们拿到了查询数据表的原始数据，也就是最终的虚拟表 vt1。就可以在此基础上再进行 WHERE 阶段。在这个阶段中，会根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2.
  
• ......
  

六、DML语言的学习

插入语句

方式一：经典的插入

语法：

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

复制代码

特点：

• 插入的值的类型要与列的类型一致或兼容；
  
• 不可以为 null 的列必须插入值，可以为 Null 的列如何插入值：
  

• 方式一：对应的 value 填 null；
  
• 方式二：字段和值都省略；
  

• 字段的个数和顺序不一定与原始表中的字段个数和顺序一致，但必须保证值和字段一一对应；
  
• 列数和值的个数必须一致；
  
• 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致；
  

方式二：

1. SELECT 查询列表
2. FROM 表1 别名 [连接类型]
3. JOIN 表2 别名
4. ON     连接条件
5. [WHERE 筛选条件]
6. [GROUP BY 分组]
7. [HAVING 筛选条件]
8. [ORDER BY 排序列表]

复制代码

两种方式大pk

• 方式一支持插入多行，方式二不支持
  

1. SELECT 查询列表
2. FROM 表1 别名
3. INNER JOIN 表2 别名
4. ON 连接条件;

复制代码

• 方式一支持子查询，方式二不支持
  

1. #案例1：查询员工名、部门名
2.     SELECT last_name,department_name
3.     FROM employees e
4.     INNER JOIN departments d
5.     ON e.`department_id` = d.`department_id`;
7. #案例2：查询员工名字中包含e的员工名和工种名(添加筛选)
8.     SELECT last_name,job_title
9.     FROM employees e
10.     INNER JOIN jobs j
11.     ON e.`job_id` = j.`job_id`
12.     WHERE e.`last_name` LIKE '%e%';
14. #案例3：查询部门个数>3的城市名称和部门个数(添加分组+筛选)
15. #(1)查询每个城市部门的个数
16. #(2)在(1)结果上筛选满足条件的
17.     SELECT city,COUNT(*) 部门个数
18.     FROM departments d
19.     INNER JOIN locations l
20.     ON d.`location_id` = l.`location_id`
21.     GROUP BY city
22.     HAING COUNT(*)>3;
24. #案例4：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序(添加排序)
25. #(1)查询每个部门的员工个数
26.     SELECT COUNT(*),department_name
27.     FROM employees e
28.     INNER JOIN departments d
29.     ON e.`department_id` = d.`department_id`
30.     GROUP BY department_name;
31. #(2)在(1)结果上筛选员工个数>3的记录，并排序
32.     SELECT COUNT(*),department_name
33.     FROM employees e
34.     INNER JOIN departments d
35.     ON e.`department_id` = d.`department_id`
36.     GROUP BY department_name
37.     HAVING COUNT(*)>3
38.     ORDER BY COUNT(*) DESC;
40. #案例5：查询员工名、部门名、工种名，并按部门名排序
41.     SELECT last_name,department_name,job_title
42.     FROM employees e
43.     INNER JOIN departments d ON e.`department_id` = d.`department_id`
44.     INNER JOIN jobs j ON e.`job_id`=j.`job_id`
45.     ODER BY department_name DESC;

复制代码

修改语句

修改单表的记录

语法：

1. #案例1：查询员工的工资级别
2.     SELECT salary,grade_level
3.     FROM employees e
4.     JOIN job_grade e
5.     ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
7. #案例2：查询工资级别的个数>20的个数，并且按工资级别降序
8.     SELECT COUNT(*),grade_level
9.     FROM employees e
10.     JOIN job_grade e
11.     ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
12.     GROUP BY grade_level
13.     HAVING COUNT(*)>20
14.     ORDER BY grade_level DESC;

复制代码

修改多表的记录(补充)

• sql92 语法：
  1. #案例：查询姓名中包含字符k的员工的名字、上级的名字
  2.     SELECT e.last_name,m.last_name
  3.     FROM employees e
  4.     JOIN employees m
  5.     ON e.`manager_id` = m.`manager_id`
  6.     WHERE e.`last_name` LIKE '%k%';

  复制代码
• sql99 语法:
  1. #案例：查询男朋友 不在男明星表的女明星名
  2.     SELECT b.name,bo.*
  3.     FROM beauty b
  4.     LEFT OUTERF JOIN boys bo
  5.     ON b.`boyfriend_id` = bo.`id`
  6.     WHERE bo.`id` IS NULL;  //问题：为什么用boys.id为NULL就可以了呢，实际上的boys表里的id是主键，根本不可能为NULL啊？
  8. #案例1：查询哪个部门没有员工
  9. #左外
  10.     SELECT d.*,e.employee_id
  11.     FROM departments d
  12.     LEFT OUTER JOIN employees e
  13.     ON d.`department_id`=e.`department_id`
  14.     WHERE e.`employee_id` IS NULL;
  16. #案例2：查询哪个城市没有部门
  17.     SELECT city
  18.     FROM department d
  19.     RIGHT OUTER JOIN locations l
  20.     ON d.location_id = l.location_id
  21.     WHERE d.department_id IS NULL;
  23. #案例3：查询部门为SAL或IT的员工信息
  24. SELECT e.*,d.department_name
  25. FROM departments d
  26. LEFT OUTER JOIN employees e
  27. ON d.department_id = e.department_id
  28. WHERE d.department_name IN ('SAL','IT');

  复制代码

1. SELECT 查询列表
2. FROM 表1 别名
3. CROSS JOIN 表2 别名;
5. #特点：类似于笛卡尔乘积

复制代码

删除语句

方式一：delete

单表的删除

1. #sql92中：
2. SELECT employee_id,last_name,department_name
3. FROM employees e JOIN departments d
4. ON e.`department_id` = d.`department_id`
5. AND e.`manager_id` = d.`manager_id`;
7. #在sql99中可写成：
8. SELECT employee_id,last_name,department_name
9. FROM employees e NATURAL JOIN departments d;

复制代码

多表的删除

• sql92 语法
  1. SELECT employee_id,last_name,department_name
  2. FROM employees e JOIN departments d
  3. USING (department_id);

  复制代码
• sql99 语法
  1. #案例1：谁的工资比Abel高？
  2. #(1)查询Abel的工资
  3.     SELECT salary
  4.     FROM employees
  5.     WHERE last_name = 'Abel'
  6. #(2)查询员工信息，满足 salary>(1)结果
  7.     SELECT *
  8.     FROM employees
  9.     WHERE salary>(
  10.         SELECT salary
  11.         FROM employees
  12.         WHERE last_name = 'Abel'
  13.     );
  15. #案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
  16. #(1)查询141号员工的job_id
  17.     SELECT job_id
  18.     FROM employees
  19.     WHERE employee_id = 141
  20. #(2)查询143号员工的salary
  21.     SELECT salary
  22.     FROM employees
  23.     WHERE employee_id = 143
  24. #(3)查询员工的姓名、job_id和工资，要求job_id=(1)并且salary>(2)
  25.     SELECT last_name,job_id,salary
  26.     FROM employees
  27.     WHERE job_id = (
  28.         SELECT job_id
  29.         FROM employees
  30.         WHERE employee_id = 141
  31.     ) AND salary > (
  32.         SELECT salary
  33.         FROM employees
  34.         WHERE employee_id = 143
  35.     );
  37. #案例3：返回公司工资最少的员工的last_name，job_id和salary
  38. #(1)查询公司的最低工资
  39.     SELECT MIN(salary)
  40.     FROM employees;
  41. #(2)查询last_name，job_id和salary，要求salary=(1)
  42.     SELECT last_name,job_id,salary
  43.     FROM employees
  44.     WHERE salary = (
  45.         SELECT MIN(salary)
  46.         FROM employees;
  47.     );
  49. #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
  50. #(1)查询50号部门的最低工资
  51.     SELECT MIN(salary)
  52.     FROM employees
  53.     WHERE department_id =50
  54. #(2)查询每个部门的最低工资
  55.     SELECT MIN(salary),department_id
  56.     FROM employees
  57.     GROUP BY department_id
  58. #(3)在(2)的基础上，满足min(salary)>(1)
  59.     SELECT MIN(salary),department_id
  60.     FROM employees
  61.     GROUP BY department_id
  62.     HAVING MIN(salary)>(
  63.         SELECT MIN(salary)
  64.         FROM employees
  65.         WHERE department_id =50
  66.     );

  复制代码

1.     SELECT MIN(salary),department_id
2.     FROM employees
3.     GROUP BY department_id
4.     HAVING MIN(salary)>(
5.         SELECT salary
6.         FROM employees
7.         WHERE department_id =50
8.     );

复制代码

方式二：truncate (清空)

1. #案例1：返回 location_id 是 1400 或 1700 的部门中所有员工姓名
2. #(1)查询location_id是1400或1700的部门编号
3.     SELECT DISTINCT department_id
4.     FROM departments
5.     where location_id IN(1400,1700)
6. #(2)查询员工姓名，要求部门号是(1)列表中的某一个
7.     SELECT last_name
8.     FROM employees
9.     WHERE deapartment_id IN(
10.         SELECT DISTINCT department_id
11.         FROM departments
12.         where location_id IN(1400,1700)
13.     );
15. #案例2：查询其它工种中比job_id为`IT_PROG`工种任一工资低的员工的员工号、姓名、job_id 和 salary
16. #(1)查询job_id为`IT_PROG`部门任一工资
17.     SELECT DISTINCT salary
18.     FROM employees
19.     WHERE job_id='IT_PROG'
20. #(2)查询员工号、姓名、job_id以及salary，salary<(1)的任意一个
21.     SELECT last_name,employee_id,job_id,salary
22.     FROM employees
23.     WHERE salary < ANY(
24.         SELECT DISTINCT salary
25.         FROM employees
26.         WHERE job_id='IT_PROG'
27.     )AND job_id<>'IT_PROG';
28.     或
29.     SELECT last_name,employee_id,job_id,salary
30.     FROM employees
31.     WHERE salary < (
32.         SELECT MAX(salary)
33.         FROM employees
34.         WHERE job_id='IT_PROG'
35.     )AND job_id<>'IT_PROG';

复制代码

delete PK truncate (重点)

• delete 可以添加 where 条件，truncate 不能加；
  
• truncate 删除，效率高一丢丢；
  
• 假如要删除的表中有自增长列，如果用 delete 删除后，再插入数据，自增长列的值从断点开始，而 truncate 删除后，再插入数据，自增长列的值从1开始；
  
• truncate 删除没有返回值，delete 删除有返回值；
  
• truncate 删除不能回滚，delete 删除可以回滚；
  

MySQL8 新特性：计算列

什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。

1. #案例：查询员工编号最小且工资最高的员工信息
2. #(1)查询最小的员工编号
3.     SELECT MIN(employee_id)
4.     FROM employees
5. #(2)查询最高工资
6.     SELECT MAX(salary)
7.     FROM employees
8. #(3)查询员工信息
9.     SELECT *
10.     FROM employees
11.     WHERE employee_id=(
12.         SELECT MIN(employee_id)
13.         FROM employees
14.     )AND salary=(
15.         SELECT MAX(salary)
16.         FROM employees
17.     );
19.     SELECT *
20.     FROM employees
21.     WHERE (employee_id,salary)=(
22.         SELECT MIN(employee_id),MAX(salary)
23.         FROM employees
24.     );

复制代码

七、DDL语言的学习

标识符命名规则

• 数据库名、表名不得超过30个字符，变量名限制为29个。
  
• 必须只能包含 A-Z，a-z，0-9，_ 共63个字符。
  
• 数据库名、表名、字段名等对象中间不要包含空格。
  
• 同一个 MySQL 软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名。
  
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在 SQL 语句中使用着重号引起来。
  
• 保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可别变成字符型了。
  

库的管理

1. #案例1：查询每个部门的员工个数
2.     SELECT d.*,(
3.         SELECT COUNT(*)
4.         FROM employees
5.         WHERE e.department_id = d.`department_id`
6.         ) 个数
7.     FROM departments d;
9. #案例2：查询员工号=102的部门名
10.     SELECT (
11.         SELECT department_name
12.         FROM departments d
13.         INNER JOIN employees e
14.         ON d.department_id = e.department_id
15.         WHERE e.employee_id = 102
16.     ) 部门名;

复制代码

1. 库的创建

1. #案例1：查询每个部门的平均工资的工资等级
2. #(1)查询每个部门的平均工资
3.     SELECT AVG(salary),department_id
4.     FROM employees
5.     GROUP BY department_id
6. #(2)连接(1)的结果集和job_grade表，筛选条件平均工资between lowest_sal and highest_sal
7.     SELECT ag_dep.*,g.`grade_level`
8.     FROM(
9.         SELECT AVG(salary) ag,department_id
10.         FROM employees
11.         GROUP BY department_id
12.     ) ag_dep
13.     INNER JOIN job_grade g
14.     ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
16. #案例2：查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
17. #(1)查询各部门的平均工资
18.     SELECT AVG(salary),department_id
19.     FROM employees
20.     GROUP BY department_id;
21. #(2)连接(1)结果集和 employees 表，进行筛选
22.     SELECT employee_id,last_name,salary,e.department_id
23.     FROM employees e
24.     INNER JOIN (
25.         SELECT AVG(salary) ag,department_id
26.         FROM employees
27.         GROUP BY department_id;
28.     )ag_dep
29.     ON e.department_id = ag_dep.department_id
30.     WHERE salary > ag_dep.ag;

复制代码

2. 库的修改

1. 语法：
2.     exists(完整的查询语句)            问题：exists(NULL)？
3. 结果：
4.     1或0

复制代码

3. 库的删除

1. #案例1：查询有员工的部门名
2.     SELECT department_name
3.     FROM departments d
4.     WHERE EXISTS(
5.         SELECT *
6.         FROM employees e
7.         WHERE e.`department_id`= d.`department_id`
8.     );
9. #in
10.     SELECT department_name
11.     FROM departments d
12.     WHERE d.`deparment_id` IN (
13.         SELECT department_id
14.         FROM employees
15.     );
18. #案例2：查询没有女朋友的男生信息
19. #in
20.     SELECT bo.*
21.     FROM boys bo
22.     WHERE bo.id NOT IN(
23.         SELECT boyfriend_id
24.         FROM beauty
25.     );
26. #exists
27.     SELECT bo.*
28.     FROM boys bo
29.     WHERE NOT EXISTS(
30.         SELECT boyfriend_id
31.         FROM beauty b
32.         WHERE bo.id = b.boyfriend_id
33.     );

复制代码

表的管理

1. #案例1：查询每个部门的员工个数
2.     SELECT d.*,(
3.         SELECT COUNT(*)
4.         FROM employees
5.         WHERE e.department_id = d.`department_id`
6.         ) 个数
7.     FROM departments d;
9. #案例2：查询员工号=102的部门名
10.     SELECT (
11.         SELECT department_name
12.         FROM departments d
13.         INNER JOIN employees e
14.         ON d.department_id = e.department_id
15.         WHERE e.employee_id = 102
16.     ) 部门名;

复制代码

1. 表的创建

1.     SELECT 查询列表                    7
2.     FROM 表                          1
3.     [JOIN type join 表2              2
4.     ON 连接条件                        3
5.     WHERE 筛选条件                    4
6.     GROUP BY 分组字段                5
7.     HAVING 分组后的筛选               6
8.     ORDER BY 排序的字段]                8
9.     LIMIT offset,size;               9
10.     # offset 要显示条目的起始索引(起始索引从0开始)
11.     # size 要显示的条目个数
13. 根据执行顺讯，可以得出：
14. 1. order by 后面的列必须是在 select 后面存在的；
15. 2. select、having 或 order by 后面存在的非聚合列必须全部在 group by 中存在。

复制代码

2. 表的修改

1. #要显示的页数page    每页的条目数size
2.     SELECT 查询列表
3.     FROM 表
4.     LIMIET (page-1)*size,size;

复制代码

3. 表的删除

1. #案例1：查询前五条员工信息
2.     SELECT * FROM employees LIMIT 0,5;
3.     或
4.     SELECT * FROM employees LIMIT 5;
6. #案例2：查询第11条——第25条
7.     SELECT * FROM employees LIMIT 10,15;

复制代码

4. 表的复制

1. 查询语句1
2. union [all]
3. 查询语句2
4. union [all]
5. ......
9. #案例：查询部门编号>90或邮箱包含a的员工信息
10.     SELECT * FROM employees WHERE email LIKE '%a%'
11.     UNION
12.     SELECT * FROM employees WHERE department_id >90;  

复制代码

常见数据类型介绍

• 数值型：
  
  
  
  • 整型
    
  • 小数：
    
    
    
    • 定点数
      
    • 浮点数
      
    
    
  
  
• 字符型：
  
  
  
  • 较短的文本：char、varchar
    
  • 较长的文本：text、blob(较长的二进制数据)
    
  
  
• 日期型
  

类型类型举例整数类型TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT浮点类型FLOAT、DOUBLE定点数类型DECIMAL位类型BIT日期时间类型YEAR、TIME、DATE、DATETIME、TIMESTAMP文本字符串类型CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT枚举类型ENUM集合类型SET二进制字符串类型BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOBJSON类型JSON对象、JSON数组空间数据类型单值：GEOMETRY、POINT、LINESTRING、POLYGON；集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION其中，常用的几类类型介绍如下：
[table][tr]数据类型描述[/tr][tr][td]INT[/td][td]从-231到231-1的整型数据。存储大小为 4个字节[/td][/tr][tr][td]CHAR(size)[/td][td]定长字符数据。若未指定，默认为1个字符，最大长度255[/td][/tr][tr][td]VARCHAR(size)[/td][td]可变长字符数据，根据字符串实际长度保存，必须指定长度[/td][/tr][tr][td]FLOAT(M,D)[/td][td]单精度，占用4个字节，M=整数位+小数位，D=小数位。 D

---

欢迎光临 IT评测·应用市场-qidao123.com (https://dis.qidao123.com/)

Powered by Discuz! X3.4