mysql 基础知识

瑞星  金牌会员 | 2022-12-3 00:17:11 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 953|帖子 953|积分 2859

学习中的思考

在 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
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

瑞星

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表