数据库和MySQL
1 - 数据库管理软件的由来
基于我们之前所学，数据要想永久保存，都是保存于文件中，毫无疑问，一个文件仅仅只能存在于某一台机器上。
如果我们暂且忽略直接基于文件来存取数据的效率问题，并且假设程序所有的组件都运行在一台机器上，那么用文件存取数据，并没有问题。
很不幸，这些假设都是你自己意淫出来的，上述假设存在以下几个问题：
1 程序所有的组件就不可能运行在一台机器上
因为这台机器一旦挂掉则意味着整个软件的崩溃，并且程序的执行效率依赖于承载它的硬件，而一台机器机器的性能总归是有限的，受限于目前的硬件水平，就一台机器的性能垂直进行扩展是有极限的。
于是我们只能通过水平扩展来增强我们系统的整体性能，这就需要我们将程序的各个组件分布于多台机器去执行。
2 数据安全问题
根据1的描述，我们将程序的各个组件分布到各台机器，但需知各组件仍然是一个整体，言外之意，所有组件的数据还是要共享的。但每台机器上的组件都只能操作本机的文件，这就导致了数据必然不一致。
于是我们想到了将数据与应用程序分离：把文件存放于一台机器，然后将多台机器通过网络去访问这台机器上的文件（用socket实现），即共享这台机器上的文件,共享则意味着竞争，会发生数据不安全，需要加锁处理
3 并发
根据2的描述，我们必须写一个socket服务端来管理这台机器（数据库服务器）上的文件，然后写一个socket客户端，完成如下功能：
/* 1.远程连接（支持并发） 2.打开文件 3.读写（加锁） 4.关闭文件 */
4 总结
我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写
2 - 数据库相关概念
DB → 数据库（ database ）：存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS → 数据库管理系统（ Database Management System ）。数据库是通过 DBMS 创 建和操作的容器
SQL → 结构化查询语言（ Structure Query Language ）：专门用来与数据库通信的语言
1 DBMS
常见的数据库管理系统(DBMS)：MySQL、Oracle、DB2、SqlServer等
3 - 数据库优点及存储数据的特点
优点：
1 实现数据持久化
2 使用完整的管理系统统一管理，易于查询(结构化查询)
数据库存储数据的特点：
1 - 数据存放到表中，然后表再放到库中
2 - 一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3 - 表中有一个或多个列，列又称为“字段”，相当于java中“属性”
4 - 表中的每一行数据，相当于java中“对象”
4 - MySQL介绍与安装
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一
1 mysql是什么 ？
// mysql就是一个基于socket编写的C/S架构的软件 /* 客户端软件 mysql自带：如mysql命令，mysqldump命令等 python模块：如pymysql */
2 mysql优点
成本低：开放源代码，一般可以免费试用
性能高：执行很快
简单：很容易安装和使用
3 数据库管理软件分类
/* 分两大类： 关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用 非关系型：mongodb，redis，memcache 可以简单的理解为： 关系型数据库需要有表结构 非关系型数据库是key-value存储的，没有表结构 */
4 Window版本下载安装
// ① 下载 mysql-8.0.20-winx64.zip 下载地址：https://dev.mysql.com/downloads/file/?id=494993 // ② 解压 如果想要让MySQL安装在指定目录，那么就将解压后的文件夹移动到指定目录，如：D:\MySQL\mysql-8.0.19-winx64 // ③ 添加环境变量 右键[计算机]->[属性]-->[高级系统设置]-->[高级]-->[环境变量]-->[在第二个内容框中找到 变量名为Path 的一行，双击] --> [将MySQL的bin目录路径追加到变值值中，用 ； 分割] // ④ 初始化 在命令提示符里面输入 mysqld --initialize-insecure 1、mysqld --initialize-insecure 这种方法初始化后没有设置密码，密码为空。 2、mysqld --initialize 这种方法初始化后会随机生成密码，如果要找得到密码就需要去生成得data文件夹中后缀名为.err的文件中去找（ctrl+f搜password） (其中localhost:后面就是你的密码) // ⑤ 启动MySQL服务 在命令提示符里面输入 mysqld // 启动MySQL服务 在执行【mysqd】启动MySQL服务器时，当前终端会被hang住，那么做一下设置即可解决此问题： 注意：--install前，必须用mysql启动命令的绝对路径 // 制作MySQL的Windows服务，找到安装路径的bin目录在终端执行此命令： D:\MySQL\mysql-8.0.19-winx64\bin\mysqld install // 移除MySQL的Windows服务，在终端执行此命令： D:\MySQL\mysql-8.0.19-winx64\bin\mysqld remove 注册成服务之后，以后再启动和关闭MySQL服务时，仅需执行如下命令： // 启动MySQL服务 net start mysql // 此时提示启动成功 mysql就安装好了 // 关闭MySQL服务 net stop mysql // ⑥ 启动MySQL客户端并连接MySQL服务 mysql -u root -p # 连接MySQL服务器
5 修改密码
// 方法1 命令行格式输入：mysql -u root -p回车进入mysql 输入： mysql>set password =password('你的密码'); mysql>flush privileges; // 方法2 输入：mysqladmin -u root -p password回车 设置密码即可。
6 再次介绍 启动和停止MySQL服务
方式1：通过计算机管理方式
右击计算机—管理—服务—启动或停止MySQL服务
方式2：通过命令行方式
启动：net start mysql服务名
停止：net stop mysql服务名
5 - MySQL数据库的使用
1 有了mysql这个数据库软件，就可以将程序员从对数据的管理中解脱出来，专注于对程序逻辑的编写
2 SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为4种类型：
// DDL(*)（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等； // DML(**)（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）； // DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别； // DQL(*****)（Data Query Language）：数据查询语言，用来查询记录（数据）。
3 MySQL语法规范
✔ 不区分大小写
✔ 每句话用;或\g结尾
✔ 各子句一般分行写
✔ 关键字不能缩写也不能分行
✔ 用缩进提高语句的可读性
单行注释：-- 注释内容 或 # 注释内容
多行注释：/*注释内容 */
4 MySQL 常见命令
/* MySQL常见命令 */ mysql [-h 主机名 -P 端口号] -u 用户名 -p密码; --登录，其中主机名和端口号可以省略不写 -- mysql -h localhost -P 3306 -u root -p 123456; show databases; -- 查看 mysql 中有哪些个数据库 create database 数据库名; -- 新建一个数据库 -- create database test; use 数据库名; -- 使用此数据库 -- use test show tables; -- 查看指定的数据库中有哪些数据表 -- use test; -- show tables; --建表 create table 表名( 字段 字段类型, 字段 字段类型; ... ) -- 例如 create table user( id int, age int, name varchar(20) ) desc user; -- 查看表(user)的结构 drop 表名; -- 删除表 -- drop user; select * from 表名; -- 查看表中所有的记录 -- 查看服务器的版本 -- 方式1：登录到mysql select version(); -- 方式2：dos命令即没有登录到mysql服务端 mysql --version
6 - DQL (Data Query Language) 查询语言
1 - 基础查询
/* 一、语法 select 查询列表 from 表名; 二、特点 1、查询列表可以是字段、常量、表达式、函数，也可以是多个 2、查询结果是一个虚拟表 三、示例 1、查询单个字段 select 字段名 from 表名; 2、查询多个字段 select 字段名，字段名 from 表名; 3、查询所有字段 select * from 表名 4、查询常量 select 常量值; 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要 5、查询函数 select 函数名(实参列表); 6、查询表达式 select 100/1234; 7、起别名 ①as ②空格 8、去重 select distinct 字段名 from 表名; 9、+ 作用：做加法运算 select 数值+数值; 直接运算 select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算 select null+值;结果都为null 10、【补充】concat函数 功能：拼接字符 select concat(字符1，字符2，字符3,...); 11、【补充】ifnull函数 功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值 select ifnull(commission_pct,0) from employees; 12、【补充】isnull函数 功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0 */
# 进阶1：基础查询 /* 语法： select 查询列表 from 表名; 类似于：System.out.println("打印东西"); 特点： 1 - 查询列表可以是：表中的字段、常量值、表达式、函数 2 - 查询的结果是一个虚拟的表格 */ # 1、查询表中的单个字段 select last_name from employees; # 2、查询表中的多个字段 select last_name,salary,email from employees; # 3、查询表中的 所有字段 select * from employees; # 4、查询常量值 select 100; select "hello world"; # 5、查询表达式 select 20 + 50; select 100%98; # 6、查询函数 select now(); select version(); # 7、为字段起别名 /* ① 便于理解 ② 如果要查询的字段有重名的情况，使用别名可以区分开来 */ # 方式1 select 100%98 as "结果"; select last_name as 姓, first_name as 名 from employees; # 方式2 select last_name 姓,first_name 名 from employyees; # 8、去重 # 案例：查询员工表中涉及到的所有的部门编号 select distinct department_id from employees; # 9、+号的作用 /* java中的+号 ① 运算符，两个操作数都为数值型 ② 连接，只要有一个操作数为字符串 mysql中的+号： 仅仅只有一个功能：运算符 select 100+90; 两个操作符数都为数值型，则做加法运算 select '123'+90;其中一方为字符型，视图将字符型数值转换成数值型，如果转换成成功，则继续做加法运算 select 'a' + 1; 此时转换不成功，将字符型数值转换成0 select null + 10; 只要其中一方为null,则结果肯定为null */ #案例：查询员工和姓名连接成一个字段，并显示为 姓名 # 错误写法 select last_name + first_name as 姓名 from employees; # 正确写法 select concat('a','b','c') as 结果; select concat(last_name,first_name) as 姓名 from employees;
2 - 条件查询
/* 1 - 语法 select 查询列表 from 表名 where 筛选条件 2 - 筛选条件的分类 1、简单条件运算符 > < = <> != >= <= <=>安全等于 2、逻辑运算符 && and || or ! not 3、模糊查询 like:一般搭配通配符使用，可以判断字符型或数值型 通配符：%任意多个字符，_任意单个字符 between and in is null /is not null：用于判断null值 is null PK <=> 普通类型的数值 null值 可读性 is null × √ √ <=> √ √ × */
# 进阶2：条件查询 /* 语法： select 查询列表 from 表名 where 筛选条件; 分类： 1 - 按条件表达式筛选 条件运算符：> < = != <> >= <= 2 - 按逻辑表达式筛选 逻辑运算符：and or not && || ! 3 - 模糊查询 like betweem and in is null */ -- 1 - 按条件表达式筛选 # 案例1：查询工资 > 12000的员工 select * from employees where salary > 12000; # 案例2：查询部门编号不等于90号的员工姓名和部门编号 select last_name,department_id from employeees where department_id != 90; # select last_name,department_id from employees where department_id <> 90; -- 2 - 按条件表达式筛选 # 案例1：查询工资在10000到20000之间的员工名，工资以及奖金 select last_name,salary,commission_pct from employees where salary >= 10000 and salary <= 20000; # 案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息 select * from employees where department_id < 90 or department_id > 110 or salary > 15000; -- 3 - 模糊查询 # ① like /* 特点： ① 一般和通配符搭配使用 通配符： % 任意多个字符，包含0个字符 _ 任意单个字符 */ # 案例1：查询员工名中包含字符a的员工信息 select * from employees where last_name like '%a%'; # 案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资 select last_name,salary from employees where last_name like '__e_a%'; # 案例3：查询员工名中第二个字符为_的员工名 select last_name from employees where last_name like '_\_%'; # mysql 支持取消转义 # select last_name from employees where last_name like '_$_%' escape '$'; escape关键字的作用类似于 \ # ② between and /* 1、使用between and 可以提高语句的简洁度 2、包含临界值 3、两个临界值不能交换位置 */ #案例1：查询员工编号在100到120之间的员工信息 select * from employees where employee_id between 100 and 120; # ③ in /* 含义：判断某字段的值是否属于in列表中的某一项 特点： 1 使用in 提高语句简洁度 2 in列表的值类型必须一致或兼容('123' 123) */ # 案例：查询员工的工种编号是 IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号 select last_name,job_id from employees where job_id in ('IT_PROG','AD_VP','AD_PRES'); # ④ is null /* =或<> 不能用于判断null值 is null 或 id not null 可以判断null值 */ # 案例1：查询没有奖金的员工名和奖金率 select last_name,commission_pct from employees where commission_pct is null; # 案例2：查询有奖金的员工名和奖金率 select last_name,commission_pct from employees where commission_pct is not null; # 安全等于：<=> 也可以判断null值 select last_naem,commission_pct from employees where commission_pct <=> null; # 没有奖金 # 案例3：查询工资为12000的员工信息 select * from employees where salary <=> 12000; # 如果等于12000，则返回true
面试题
试问：select * from employees; 和 select * from employees where commission_pct like '%%' and last_name like '%%'; 结果是否一样，并且说明原因。
/* 不一样 如果判断的字段有null值，where 关键字筛选不了有null值的字段。所以结果可能不一样。 */
3 - 排序查询
/* 1 - 语法 select 查询列表 from 表 where 筛选条件 order by 排序列表 【asc}desc】 2 - 特点 1、asc ：升序，如果不写默认升序 desc：降序 2、排序列表 支持 单个字段、多个字段、函数、表达式、别名 3、order by的位置一般放在查询语句的最后（除limit语句之外） */
# 进阶3：排序查询 /* 语法：select 查询列表 from 表 [where 筛选条件] order by [asc | desc] 特点： 1、asc代表的是升序，desc代表的是降序，如果不写，默认是升序 2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名 3、order by子句一般是放在查询语句的最后面，limit子句除外 */ #案例1：查询员工信息，要求工资从高到低排序 select * from employees order by salary desc; # 降序 select * from employees order by salary asc; # 升序[asc可以省略] #案例2：[添加筛选条件]查询部门编号>=90的员工信息，按入职时间的先后进行排序 select * from employees where department_id >= 90 order by hiredate asc; #案例3：[按表达式排序] 按年薪的高低显示员工的信息和 年薪 select *,salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by salary*12*(1+ifnull(commission_pct,0)) desc; #案例4：[按别名排序] 按年薪的高低显示员工的信息和 年薪 select *,salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc; #案例5：[按函数排序]按姓名的长度显示员工的姓名和工资 select length(last_name) 字节长度,salary from employees order by (last_name) desc; #案例6：[按多个字段排序]查询员工信息，要求先按工资升序，在按员工编号降序 select * from employees order by salary asc,employee_id desc;
4 - 常见函数之单行函数
/* 1 - 概述 功能：类似于java中的方法 好处：提高重用性和隐藏实现细节 调用：select 函数名(实参列表); 2 - 单行函数 1、字符函数 concat:连接 substr:截取子串 upper:变大写 lower：变小写 replace：替换 length：获取字节长度 trim:去前后空格 lpad：左填充 rpad：右填充 instr:获取子串第一次出现的索引 2、数学函数 ceil:向上取整 round：四舍五入 mod:取模 floor：向下取整 truncate:截断 rand:获取随机数，返回0-1之间的小数 3、日期函数 now：返回当前日期+时间 year:返回年 month：返回月 day:返回日 date_format:将日期转换成字符 curdate:返回当前日期 str_to_date:将字符转换成日期 curtime：返回当前时间 hour:小时 minute:分钟 second：秒 datediff:返回两个日期相差的天数 monthname:以英文形式返回月 4、其他函数 version 当前数据库服务器的版本 database 当前打开的数据库 user当前用户 password('字符')：返回该字符的密码形式 md5('字符'):返回该字符的md5加密形式 5、流程控制函数 ①if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2 ②case情况1 case 变量或表达式或字段 when 常量1 then 值1 when 常量2 then 值2 ... else 值n end ③case情况2 case when 条件1 then 值1 when 条件2 then 值2 ... else 值n end */
# 进阶4：常见函数 /* 函数：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名 好处： 1、隐藏了实现细节 2、提高代码的重用性 调用：select 函数名(实参列表) [from 表]; 特点： ① 叫什么 ② 干什么(功能) 分类： 1、单行函数 如 concat、length、ifnull等 2、分组函数(聚合函数) 功能：做统计使用 */ /* 1 - 字符函数 */ # 1. length 获取参数值的字节个数 select length('alex'); -- 4 select length('李白'); -- 6 -- 查看字符集 show variables like '%char%'; # 2. concat 拼接字符 select concat(last_name,'_',first_name) 姓名 from employees; # 3. upper lower select upper('alex'); -- ALEX select lower('ABC'); -- abc # 案例：将姓变大写，名变小写，然后拼接 select concat(upper(last_name),lower(first_name)) 姓名 from employees; # 4. substr、substring # 截取从指定索引处后面所有的字符 select substr('代码改变世界',5) out_put; # 世界 -- 注意这里索引是从1开始 # 截取从指定索引处指定字符长度的字符 select substr('代码改变世界',1,2) out_put; # 代码 # 案例1：姓名中首字母大写，其他字符小写然后用_拼接，显示出来 select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) out_put from employees; # 5. instr 返回子串第一次出现的索引，入个股找不到返回0 select instr('昨夜西风凋碧树，望尽天涯路','天涯路') out_put; -- 11 # 6. trim select length(trim(' 李白 ')) out_put; -- 6 select trim('a' from 'aaaaaaaa韩a信aaaaaaaaaa') out_put; -- 韩a信 # 7. lpad 用指定的字符实现左填充指定长度 select lpad('李白',10,'*') as out_put; -- ********李白 # 8. rpad 用指定的字符实现右填充指定长度 select rpad('李白','10','+') out_put; -- 李白++++++++ # 9. replace 替换 select replace('张无忌爱上了周芷若','周芷若','赵敏') as out_put; -- 张无忌爱上了赵敏 /* 2 - 数学函数 */ # round 四舍五入 select round(1.55) as number; -- 2 select round(-1.3) as number; # -1 select round(1.456,2) as number; -- 1.46 # ceil 向上取整，返回 >= 该参数的最小整数 select ceil(1.22) as number; -- 2 # floor 向下取整，返回<=该参数的最大整数 select floor(3.22) as num; -- 3 # truncate 截断 select truncate(1.654,2) as num; -- 1.65 # mod 取余 select mod(-10,-3) as num; # -1 select 10%3; -- 1 /* 3 - 日期函数 */ # now 返回当前系统日期 + 时间 select now(); # curdate 返回当前系统日期，不包含时间 select curdate(); # curtime 返回当前时间，不包含日期 select curtime(); # 可以获取指定的部分，年、月、日、小时、分钟、秒 select year(now()) as 年; select year('2020-1-1') as 年; select month(now()) as 月; select monthname(now()) as 月; # str_to_date 将字符通过指定的格式转换成日期 select str_to_date('2020-5-20','%Y-%c-%d') as new_date; # date_format 将日期转换成成字符 select date_format(now(),'%y年%m月%d日') as out_put; # 查询有奖金的员工名和入职日期(xx月/xx日 xx年) select last_name,date_format(hiredate,'%m月/%d %y年') 入职日期 from employees where commission_pct is not null; /* 4 - 其他函数 */ select version(); select database(); select user(); /* 5 - 流程控制函数 */ # 1. if函数： if else 的效果 select if(10 > 5,'大','小'); select last_name,commission_pct,if(commission_pct is null,'没奖金','有奖金') 备注; # 2. case函数的使用1：类似于Java switch case语句 /* java中 switch(变量或表达式){ case 常量1：语句1;break; ... default:语句n;break; } mysql中 case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; when 常量1 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end */ /*案例：查询员工的工资，要求 部门号=30，显示的工资为1.1倍 部门号=40，显示个工资为1.2倍 部门号=50，显示的工资为1.3倍alter其他部门，显示的工资为原工资 */ select salary 原始工资,department_id, case department_id when 30 then salary * 1.1 when 40 then salary * 1.2 when 50 then salary * 1.3 else salary end as 新工资 from employees; # 2. case 函数的使用2；类似于java 多重if /* case when 条件1 then 要显示的值1或语句1; when 条件2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end */ /* 案例：查询员工的工资的情况 如果工资 > 20000,显示A级别 如果工资 > 15000,显示B级别 如果工资 > 10000,显示C级别 否则，显示D级别 */ select salary, case when salary > 20000 then 'A' when salary > 15000 then 'B' when salary > 10000 then 'C' else 'D' end as 工资级别;
5 - 分组函数
/* 1、分类 max 最大值 min 最小值 sum 和 avg 平均值 count 计算个数 2、特点 ①语法 select max(字段) from 表名; ②支持的类型 sum和avg一般用于处理数值型 max、min、count可以处理任何数据类型 ③以上分组函数都忽略null ④都可以搭配distinct使用，实现去重的统计 select sum(distinct 字段) from 表; ⑤count函数 count(字段)：统计该字段非空值的个数 count(*):统计结果集的行数 案例：查询每个部门的员工个数 1 xx 10 2 dd 20 3 mm 20 4 aa 40 5 hh 40 count(1):统计结果集的行数 效率上： MyISAM存储引擎，count(*)最高 InnoDB存储引擎，count(*)和count(1)效率>count(字段) ⑥ 和分组函数一同查询的字段，要求是group by后出现的字段 */
# 分组函数 /* 功能：用作统计使用，又称为聚合函数或统计函数或函数 分类： sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数 特点： 1.sum avg 一般用于处理数值型、max、min、count、可以处理任何类型 2.以上分组函数都忽略null值 3.可以和distinct搭配实现去重的运算 4.count函数的单独介绍 一般使用count(*) 统计行数 5.和分组函数一同查询的字段要求是group by 后的字段 */ # 1. 简单的使用 select sum(salary) from employees; select avg(salary) from employees; select min(salary) from employees; select max(salary) from employees; select count(salary) from employees; select sum(salary) 和,avg(salary) 平均,max(salary) 最高,min(salary) 最低,count(salary) 个数 from employees; select sum(salary) 和,round(avg(salary),2) 平均,max(salary) 最高,min(salary) 最低,count(salary) 个数 from employees; # 2. 参数支持哪些类型 select sum(last_name),avg(last_name) from employees; select sum(hiredate),avg(hiredate) from employees; # 3. 是否忽略null # 4. 和distinct搭配 select sum(distinct salary),sum(salary) from employees; # 去重之后求和，与不去重求和 select count(distinct salary),count(salary) from employees; # 5.count函数的详细介绍 select count(salary) from employees; select count(*) from employees; # 统计行数 select count(1) from employees; # 统计行数 # 6. 和分组函数一同查询的字段有限制 select avg(salary),employee_id from employees;
6 -分组查询
/* # 进阶5：分组查询 # 引入：查询 每个部门的平均工资 /* 语法： select 分组函数,字段(要求出现在group by的后面) from 表 [where 筛选条件] group by 分组的列表 [order by 子句] 注意：查询列表必须特殊，要求是分组函数和group by 后出现的字段 特点： 1. 分组查询中的筛选条件分为两类 数据源 位置 关键字 分组前筛选 原始表 group by子句的前面 where 分组后筛选 分组后的结果集 group by子句的后面 having 2. 分组函数做条件肯定是放在having子句中 3. 能用分组前筛选,就优先考虑使用分组前筛选 4. group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求)，表达式 5. 也可以添加排序(排序放在整个分组查询的最后) */ */
# 案例1：(简单的分组查询)查询每个工种的最高工资 select max(salary),job_id from employees group by job_id; # 案例2：查询每个位置上的部门个数 select count(*),location_id from departments group by location_id; # 添加分组前的筛选条件 # 案例1：查询邮箱中包含a字符的，每个部门的平均工资 select email,avg(salary),department_id from employees where email like '%a%' group by department_id; # 案例2：查询有奖金的每个领导手下员工的最高工资 select max(salary),manager_id from employees where commission_pct is not null group by manager_id; # 添加分组后的筛选条件 # 案例1：查询那个部门的员工个数 > 2 select count(*),department_id from employees group by department_id having count(*) > 2; # 案例2：查询每个工种有奖金的员工的最高工资 > 12000的工种编号和最高工资 select max(salary),job_id from employees where commission_pct is not null group by job_id having max(salary) > 12000; # 案例3：查询领导编号 > 102的每个领导手下的最低工资 > 5000的领导编号是那个，以及其最低工资 select min(salary),manager_id from employees where manager_id > 120 group by manager_id having min(salary) > 5000; # 按表达式或函数分组 # 案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数 > 5 的有哪些 select count(*),length(last_name) len_name from employees group by length(last_name) having count(*) > 5; # 按多个字段分组 # 案例：查询每个部门每个工种的员工的平均工资 select avg(salary),department_id,job_id from employees group by department_id,job_id; # 添加排序 # 案例：查询每个部门每个工种的员工的平均工资，并且按平均工资的高低显示 select avg(salary),job_id,department_id from employees where department_id is not null group by job_id,department_id order by avg(salary) desc;
7 - 多表查询
# 进阶6：多表查询 /* 含义：又称连接查询，当查询的字段来自于多个表时，就会用到连接查询 笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行 发生原因：没有有效的连接条件 如何避免：添加有效的连接条件 分类： 按年代分类： sql192标准(仅仅支持内连接) sql199标准(推荐) 按功能分类： 内连接： 等值连接 非等值连接 自连接 外连接： 左外连接 右外连接 全外连接 交叉连接： */ # 1 - sql192标准 # 等值连接 /* 1 - 多表等值连接的结果为多表的交集部分 2 - n表连接，至少需要n-1个连接条件 3 - 多表的顺序没有要求 4 - 一般需要为表起别名 5 - 可以搭配前面介绍的所有子句使用，如排序、分组、筛选 */
# 案例1：查询女神名和对应的男神名 select name,boyName from boys,beauty where beauty.boyfriend_id = boys.id; # 案例2：查询员工名和对应的部门名 select last_name,department_name from employees,departments where employees.department_id = departments.department_id; # 为表起别名 /* 提高语句的简洁度，区分多个重名的字段 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定 */ # 案例1：查询员工名、工种号、工种名 select last_name,emloyees.job_id,job_title from employees,jobs where employees.job_id = jobs.job_id; select last_name,emloyees.job_id,jobs.job_title from employees as e,jobs as j where e.job_id = j.job_id; # 添加筛选 # 案例：查询有奖金的员工名、部门名 select last_name,department_name from employees as e,departments as d where e.department_id = d.department_id and e.commsisson_pct is not null; # 案例2：查询城市名中第二个字符为o的部门名和城市名 select department_name,city from departments d,location l where d.location_id = l.location_id and city like '_o%'; # 添加分组 # 案例1：查询每个城市的部门个数 select count(*) 个数,city from departments d,locations l where d.location_id = l.location_id group by city; # 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资 select department_name,d.manager_id,min(salary) from departments d,employees e where d.department_id = e.department_id and commission_pct is not null group by department_name,d.manager_id; # 添加排序 # 案例1：查询每个工种的工种名和员工的个数，并且按员工个数降序 select job_title,count(*) from employees as e,jobs as j where e.job_id = j.job_id group by job_title order by count(*) desc; # 3表连接 # 案例：查询员工名、部门名和所在的城市 select last_name,department_name,city from employees e,departments d,locations l where e.deparment_id = d.department_id and d.location_id = l.location_id; # 非等值连接 # 案例：查询员工的工资和工资级别 select salary,grade_level from employees e,job_grades g where salary between g.lowest_sal and g.highest_sal; # 自连接(将一个表分割成多个表) # 案例：查询员工名和上级的名称 select e.employee_id,e.last_name,m.employee_id,m.last_name from employees e,employees m where e.employee_id = m.employee_id
# 2 - sql199语法 /* 语法： select 查询列表 from 表1 别名 [连接类型] join 表2 别名 on 连接条件 [where 筛选条件] [group by 分组] [having 筛选条件] [order by 排序列表] 内连接：inner 外连接：outer 左外：left [outer] 右外：right [outer] 全外：full [outer] 交叉连接：cross */ # 内连接 /* select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件; */ # 等值连接 /* 特点： 1 - 添加排序、分组、筛选 2 - inner 可以省略 3 - 筛选条件放在where后面，连接条件 4 - inner join连接和sql192语法中的等值连接效果是一样的，都是查询多表的交集 */ # 案例1：查询员工名、部门名 select last_name,department_name from employees e inner join departments d on e.department_id = d.department_id; #案例2：查询名字中包含e的员工名和工种名(添加筛选) select last_name,job_title from employees e inner join jobs j on e.job_id = j.job_id where e.last_name like '%e%'; # 案例3：查询部门个数 > 3 的城市名和部门个数(添加分组+筛选) select count(*) 部门个数,city from departments e inner join locations l on e.location_id = l.location_id group by city having count(*) > 3; # 案例4：查询那个部门的部门员工个数 > 3 的部门名和员工，并按个数降序(添加排序) select count(*) 员工个数,department_name from employees e inner join department d on e.department_id = d.department_id group by department_name having count(*) > 3 order by count(*) desc; # 案例5：查询员工、部门名、工种名，按部门名降序 select last_name,department_name,job_title from employees e inner join departments d on e.department_id = d.department_id inner join jobs j on e.job_id = j.job_id group by department_name desc # 非等值连接 # 查询员工的工资级别 select salary,grade_level from employees e join job_grades g on e.salary between g.lowest_sal and g.highest_sal; # 查询工资级别的个数 > 2 的个数，并且按工资级别降序 select count(*),grade_level from employees e join job_grades g on e.salary between g.lowest_sal and g.highest_sal group by grade_level having count(*) > 20 order by grade_level desc; # 自连接 # 查询姓名中包含字符k的员工的名字、上级的名字 select e.last_name,m.last_name from employees e join employees m on e.manager_id = m.employee_id where e.last_name like '%k%'; # 外连接 /* 应用场景：用于查询一个表中有，另一个表没有的记录 特点： 1.外连接的查询结果为主表中的所有记录 如果从表中有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示null值 外连接查询结果=内连接结果+主表中有而从表没有的记录 2. 左外连接，left join左边的是主表 右外连接，right join右边的是主表 3. 左外和右外交换两个表的顺序，可以实现同样的效果 4. 全外连接 = 内连接的结果 + 表1中有但表2没有的 + 表2中有但表1没有的 */ # 案例1：查询哪个部门没有员工 use myemployees; # 左外 select d.*,e.employee_id from departments d left outer join employees e on d.department_id = e.department_id where e.employee_id is null; # 右外 select d.*,e.employee_id from employee e right outer join departments d on d.department_id = e.department_id where e.employee_id is null; # 交叉连接 use beauty; select b.*,bo.* from beauty b cross join boys bo; -- 笛卡尔积
8 - 子查询
/* 一、含义 嵌套在其他语句内部的select语句称为子查询或内查询， 外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多 外面如果为select语句，则此语句称为外查询或主查询 二、分类 1、按出现位置 select后面： 仅仅支持标量子查询 from后面： 表子查询 where或having后面： 标量子查询 列子查询 行子查询 exists后面： 标量子查询 列子查询 行子查询 表子查询 2、按结果集的行列 标量子查询（单行子查询）：结果集为一行一列 列子查询（多行子查询）：结果集为多行一列 行子查询：结果集为多行多列 表子查询：结果集为多行多列 三、示例 where或having后面 1、标量子查询 案例：查询最低工资的员工姓名和工资 ①最低工资 select min(salary) from employees ②查询员工的姓名和工资，要求工资=① select last_name,salary from employees where salary=( select min(salary) from employees ); 2、列子查询 案例：查询所有是领导的员工姓名 ①查询所有员工的 manager_id select manager_id from employees ②查询姓名，employee_id属于①列表的一个 select last_name from employees where employee_id in( select manager_id from employees ); */
# 进阶7：子查询 /* 含义： 出现在其他语句中的select语句，称之为子查询或内查询 外部的查询语句，称之为主查询或外查询 分类： 按子查询出现的位置： select后面 -> 仅仅支持标量子查询 from后面 -> 支持表子查询 where或having后面 -> 标量子查询、列子查询、行子查询 exists后面(相关子查询) -> 表子查询 按结果集的行列数不同： 标量子查询(结果集只有一行一列) 列子查询(结果集只有一列多行) 行子查询(结果集有一行多列) 表子查询(结果集一般为多行多列) */ # 1 - where或having后面 /* ①标量子查询(单行子查询) ②列子查询(多行子查询) ③行子查询(多列多行) 特点： 1、子查询放在小括号内 2、子查询一般放在条件的右侧 3、标量子查询，一般搭配着单行操作符使用 > < >= <= = <> 4、列子查询，一般搭配着多行操作符使用 in any some all 5、子查询的执行优先于主查询 */ # 标量子查询 # 案例1：谁的工资比 Abel 高 ? select * from employees where salary > ( select salary from employees where last_name = 'Abel' ); # 案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资 select last_name,job_id,salary from employees where job_id = ( select job_id from employees where job_id = 141 ) and salary > ( select salary from employees where job_id = 143 ); # 案例3：返回公司工资最少的员工的last_name,job_id和salary select last_name,job_id,salary from employees where salary = ( select min(salary) from employees ); # 案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资 select min(salary),department_id from employees group by department_id having min(salary) > ( select min(salary) from employees where department_id = 50 ); # 列子查询(多行子查询) /* 操作符介绍： in/not in 等于列表中的任意一个 any | some 和子查询返回的某一个值比较 all 和子查询返回的所有值比较 */ # 案例1：返回location_id是1400或1700的部门中的所有员工姓名 select last_name from employees where department_id in ( select distinct department_id from departmentts where location_id in (1400,1700) ); # 案例2：返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id 以及salary select last_name,employee_id,job_id,salary from employees where salary < any( select distinct salary from employees where job_id = 'IT_PROG' ) and job_id <> 'IT_PROG'; # 行子查询(结果集一行多列或多行多列) 不常用 # 2 - select后面 # 案例1：查询每个部门的员工个数 select d.*,( select count(*) from employees e where e.department_id = d.department_id ) 个数 from departments d; # 案例2：查询员工号 = 102的部门名 select ( select department_name from departments d inner join employees e on d.department_id = e.department_id where e.employee_id = 102 ); # 3 - from后面 /* */ # 案例：查询每个部门的平均工资的工资等级 select ag_dep.*,g.grade_level from ( select avg(salary) ag,department_id from employees group by department_id ) ag_dep inner join job_grades g on ag_dep.ag between lowest_sal and highest_sal; # 4 - exists后面(相关子查询) /* exists(完整的查询语句) 结果：1或0 */ # 案例1：查询员工名和部门名 select department_name from departments d where exists( select * from employees e where d.department_id = e.department_id ); # 案例2：查询没有女朋友的男生信息 # in select bo.* from boys bo where bo.id not in ( select boyfriend_id from beauty );
9 - 分页查询
/* 一、应用场景 当要查询的条目数太多，一页显示不全 二、语法 select 查询列表 from 表 limit 【offset，】size; 注意： offset代表的是起始的条目索引，默认从0卡死 size代表的是显示的条目数 公式： 假如要显示的页数为page，每一页条目数为size select 查询列表 from 表 limit (page-1)*size,size; */
# 分页查询 /* 应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求 语法： select [查询列表] from 表1 [join type] join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段 limit offset,size offset要显示条目的起始索引(起始索引从0开始) size 要显示的条目个数 特点： ① limit语句放在查询语句的最后 ② 公式 要显示的页数 page，每页的条目数sizi select 查询列表 from 表 limit (page - 1)*size,size; */ # 案例1：查询前五条员工信息 select * from employees limit 0,5; select * from employees limit 5; # 案例2：查询第11条到第25条员工信息 select * from employees limit 10,15; # 案例3：有奖金的员工信息，并且工资较高的前10名显示出来 select * from employees where commision_oct is not null order by salary desc limit 10;
10 - 联合查询
# 联合查询 /* union 联合 合并：将多条查询语句的结果合并成一个结果 语法： 查询语句1 union 查询语句2 union ... 应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但是查询的信息一致时 特点： 1.要求多条查询语句的查询列数是一致的！ 2.要求多条查询语句的查询的每一列的类型和顺序最好一致 3.union关键字默认去重，如果使用union all 可以包含重复项 */ # 引入的案例：查询部门编号 > 90或邮箱包含a的员工信息 select * from employees where email like '%a%' or department_id > 90; select * from employees where email like '%a%' union select * from employees where deparment_id > 90;
7 - DML (Data Manipulation Language) 操作语言
表中数据的操作
/* 一、方式一 语法： insert into 表名(字段名,...) values(值,...); 特点： 1、要求值的类型和字段的类型要一致或兼容 2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致 但必须保证值和字段一一对应 3、假如表中有可以为null的字段，注意可以通过以下两种方式插入null值 ①字段和值都省略 ②字段写上，值使用null 4、字段和值的个数必须一致 5、字段名可以省略，默认所有列 二、方式二 语法： insert into 表名 set 字段=值,字段=值,...; 两种方式 的区别： 1.方式一支持一次插入多行，语法如下： insert into 表名【(字段名,..)】 values(值，..),(值，...),...; 2.方式一支持子查询，语法如下： insert into 表名 查询语句; */
/* 一、修改单表的记录 ★ 语法：update 表名 set 字段=值,字段=值 【where 筛选条件】; 二、修改多表的记录【补充】 语法： update 表1 别名 left|right|inner join 表2 别名 on 连接条件 set 字段=值,字段=值 【where 筛选条件】; */
/* 方式一：使用delete 一、删除单表的记录★ 语法：delete from 表名 【where 筛选条件】【limit 条目数】 二、级联删除[补充] 语法： delete 别名1,别名2 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 【where 筛选条件】 方式二：使用truncate 语法：truncate table 表名 */
# DML语言 /* 数据操作语言： 插入：insert 修改：update 删除：delete */ # 1 - 插入语句 /* 语法1：insert into 表明(列名,...) values(值1,...),(值1,...)...; 支持子查询 */ # 1.插入的值的类型要与字段的类型一致或兼容 use test; desc user; insert into user(id,age,name) values(1,22,'alex'); # 2.插入可以为null的记录，不可以为null的字段必须插入值 insert into user(id,age,name,phone) values(2,21,"李白",null); # 3.只要记录与字段相匹配，插入顺序是可以调换的 # 4.插入记录的时候可以省略字段名 insert into user values(2,21,"李白",null); # 5.可以同时插入多条记录，记录之间用逗号隔开即可 insert into user(id,age,name) values(2,22,'韩信'),(3,22,'李白'); /* 语法2：insert into 表名 set 字段=记录值,字段=记录,...; 不支持子查询 */ insert into user set id = 5,age = 30,name = '吕布'; select * from user; # 2 - 修改语句 /* 1. 修改单表的记录 语法：update 表名 set 字段 = 新记录,字段 = 新记录,... where 帅选条件; 2. 修改多表的记录[补充] sql92语法：update 表1 别名,表2 别名 set 字段 = 新记录,... where 连接条件 and 筛选条件; sql99语法：update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 字段 = 新记录,... where 筛选条件 */ # 1.修改单表的记录 # 案例1：修改user表中李白的年龄为60 update user set age = 60 where name = "李白"; select * from user where name = '李白'; # 2. 修改多表记录 # 案例1：修改张无忌的女朋友的手机号 为114 update boys bo inner join beauty b on bo.id = b.boyfriend_id set b.phone = '114' where bo.boyName = '张无忌'; # 3 - 删除语句 /* 语法1：delete from 表名 where 筛选条件; 注：执行delete语句后删除整行数据 语法2：truncate table 表名; 注：清空表，不能加筛选条件 */ # 案例1：用delete删除 # 删除手机号为9结尾的员工信息 delete from employees where phone like '%9'; # 案例2：用truncate删除 # 删除年龄 > 25 的用户信息 truncate table user;
面试题：delete和truncate删除方式有何区别？
/* 1.truncate删除后，如果再插入，标识列从1开始delete删除后，如果再插入，标识列从断点开始 2.delete可以添加筛选条件 truncate不可以添加筛选条件 3.truncate效率较高 4.truncate没有返回值delete可以返回受影响的行数 5.truncate不可以回滚delete可以回滚 */
8 - DDL (Data Definition Language) 定义语言
1 - 库与表的管理
/* 一、创建库 create database 【if not exists】 库名【 character set 字符集名】; 二、修改库 alter database 库名 character set 字符集名; 三、删除库 drop database 【if exists】 库名; */
/* 一、创建表 ★ create table 【if not exists】 表名( 字段名 字段类型 【约束】, 字段名 字段类型 【约束】, 。。。 字段名 字段类型 【约束】 ) 二、修改表 1.添加列 alter table 表名 add column 列名 类型 【first|after 字段名】; 2.修改列的类型或约束 alter table 表名 modify column 列名 新类型 【新约束】; 3.修改列名 alter table 表名 change column 旧列名 新列名 类型; 4 .删除列 alter table 表名 drop column 列名; 5.修改表名 alter table 表名 rename 【to】 新表名; 三、删除表 drop table【if exists】 表名; 四、复制表 1、复制表的结构 create table 表名 like 旧表; 2、复制表的结构+数据 create table 表名 select 查询列表 from 旧表【where 筛选】; */
# DDL语言 /* 数据定义语言 库和表的管理 1 - 库的管理 创建、修改、删除 2 - 表的管理 创建、修改、删除 创建：create 修改：alter 删除： drop */ # 库的管理 # 1.库的创建 /* 语法：create database 库名; */ # 案例：创建Books数据库 create database if not exists Books; # 2.库的修改(会导致数据混乱一般不修改) # rename database Books to 新库名; # 一般修改库的字符集 alter database Books character set gbk; #3.库的删除 drop database if exists Books; # 表的管理 # 1.表的创建 /* 语法： create table 表名( 字段名 字段类型 [(长度) 约束], 字段名 字段类型 [(长度) 约束], ... 字段名 字段类型 [(长度) 约束], ); */ # 案例1：创建表book use books; create table book( id int, # 书的编号 name varchar(20), # 书名 price double, # 价格 author_id int, # 作者编号 publishDate datetime # 出版日期 ); # 案例2：创建表author create table author( id int, name varchar(20), nation varchar(20) ); desc author; # 2. 表的修改 /* alter table 表名 add | drop | modify | change column; */ # 1 修改字段名 alter table book change column publishDate publish_date datetime; desc book; # 2 修改字段的类型或约束 alter table book modify column publish_date timestamp; # 3 添加新字段 alter table author add column annual double; desc author; # 4 删除列 alter table author drop column annual; desc author; # 5 修改表名 alter table author rename to author_book; desc author_book; # 3. 表的删除 drop table author_book; -- 容错性处理：drop table if exists author_book; show tables; # 4. 表的复制 insert into author_book(id,name,nation) values(1,'李白','chian'),(2,'韩信','china'); # 1 仅仅复制表的结构 create table coye like author_book; # 2 复制表的结构 + 数据 create table copy2 select * from author_book;
2 - 基本数据类型
/* 一、数值型 1、整型 tinyint、smallint、mediumint、int/integer、bigint 1 2 3 4 8 特点： ①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号 ②如果超出了范围，会报out or range异常，插入临界值 ③长度可以不指定，默认会有一个长度 长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型 2、浮点型 定点数：decimal(M,D) 浮点数: float(M,D) 4 double(M,D) 8 特点： ①M代表整数部位+小数部位的个数，D代表小数部位 ②如果超出范围，则报out or range异常，并且插入临界值 ③M和D都可以省略，但对于定点数，M默认为10，D默认为0 ④如果精度要求较高，则优先考虑使用定点数 二、字符型 char、varchar、binary、varbinary、enum、set、text、blob char：固定长度的字符，写法为char(M)，最大长度不能超过M，其中M可以省略，默认为1 varchar：可变长度的字符，写法为varchar(M)，最大长度不能超过M，其中M不可以省略 三、日期型 year年 date日期 time时间 datetime 日期+时间 8 timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间 */
# 常见的数据类型 /* 数值型： 整型：TinyInt[1byte]、Smallint[2byte]、Mediumint[3byte]、Int/integer[4byte]、Bigint[8byte] 小数： 定点数 浮点数 字符型： 较短的文本：char、varchar 较长的文本：text、blob(较长的二进制数据) 日期型：date[4byte] datetime[8byte] 时间戳-timestamp[4byte] time[3byte] year[1byte] */ # 1 - 整型 /* 特点： 1 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字 2 如果插入的数值超出； 整型的范围，会报out of range异常，并且插入临界值 3 如果不设置长度，会有默认的长度。 */ # 1. 设置无符号和有符号 use test; drop table if exists table_int; create table table_int(t1 int,t2 int unsigned); insert into table_int(t1,t2) values(-1211,12); # 2 - 小数 /* 1.浮点型 float(M,D) double(M.D) 2.定点型 dec(M,D)/decimal(M,D) 特点： 1 - M 代表整数部位 + 小数部位 D 代表小数部位，如果超出范围，则插入临界值 2 - M 和 D 都可以省略，如果是decimal，则M默认为10，D默认为0 如果是float和double，则会根据插入的数值的精度来决定精度 3 - 定点型的精度较高，如果要求插入数值的精度较高如货币运算则考虑使用定点型 小数数据类型使用原则： 所选择的类型越简单越好，能保存数值的类型越小越好 */ create table table_float( f1 float(5,2), f2 float(5,2), f3 double(5,2) ); insert into table_float values(123.45,123.45,123.45); # 3 - 字符型 /* 较短的文本： 固定长度：char(M) 可变长度：varchar(M) M代表字符数 a 或 一个汉字都是一个字符 特点： char(M) 比较耗费空间 效率较高 M的值可以省略，默认为1 varchar(M) 比较节省空间 效率偏低 M的值不可以省略 其他： binary和varbinary用于保存较短的二进制 enum 用于保存枚举 set 用于保存集合 较长的文本： text blob(保存较大的二进制数据) */ create table table_char( c1 enum('a','b','c') # 枚举类型 ); create table table_set( s1 set('a','b','c','d') # 集合类型 ); # 4 - 日期和时间类型 /* 分类： date 只保存日期 time 只保存时间 year 只保存年 datetime 保存日期 + 时间 timestamp 保存日期 + 时间 特点： 字节 范围 时区等的影响 datetime 8 1000-9999 不受 timestamp 4 1970-2038 受 */ create table table_date( t1 datetime, t2 timestamp ); insert into table_date values(now(),now()); show variables like 'time_zone';
3 - 常见的约束
/* 一、常见的约束 NOT NULL：非空，该字段的值必填 UNIQUE：唯一，该字段的值不可重复 DEFAULT：默认，该字段的值不用手动插入有默认值 CHECK：检查，mysql不支持 PRIMARY KEY：主键，该字段的值不可重复并且非空 unique+not null FOREIGN KEY：外键，该字段的值引用了另外的表的字段 主键和唯一 1、区别： ①、一个表至多有一个主键，但可以有多个唯一 ②、主键不允许为空，唯一可以为空 2、相同点 都具有唯一性 都支持组合键，但不推荐 外键： 1、用于限制两个表的关系，从表的字段值引用了主表的某字段值 2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求 3、主表的被引用列要求是一个key（一般就是主键） 4、插入数据，先插入主表 删除数据，先删除从表 可以通过以下两种方式来删除主表的记录 #方式一：级联删除 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE; #方式二：级联置空 ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL; 二、创建表时添加约束 create table 表名( 字段名 字段类型 not null,#非空 字段名 字段类型 primary key,#主键 字段名 字段类型 unique,#唯一 字段名 字段类型 default 值,#默认 constraint 约束名 foreign key(字段名) references 主表（被引用列） ) 注意： 支持类型 可以起约束名 列级约束 除了外键 不可以 表级约束 除了非空和默认 可以，但对主键无效 列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求 三、修改表时添加或删除约束 1、非空 添加非空 alter table 表名 modify column 字段名 字段类型 not null; 删除非空 alter table 表名 modify column 字段名 字段类型 ; 2、默认 添加默认 alter table 表名 modify column 字段名 字段类型 default 值; 删除默认 alter table 表名 modify column 字段名 字段类型 ; 3、主键 添加主键 alter table 表名 add【 constraint 约束名】 primary key(字段名); 删除主键 alter table 表名 drop primary key; 4、唯一 添加唯一 alter table 表名 add【 constraint 约束名】 unique(字段名); 删除唯一 alter table 表名 drop index 索引名; 5、外键 添加外键 alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）; 删除外键 alter table 表名 drop foreign key 约束名; */
# 常见约束 /* create table 表名 ( 字段名 字段类型 约束 ); 含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性 分类：6大约束 not null：非空，用于保证该字段的值不能为空，比如姓名、学号等 default：默认，用于保证该字段有默认值，比如性别 primary key：主键，用于保证该字段的值具有唯一性，并且非空比如学号、员工编号等 unique：唯一，用于保证该字段的值具有唯一性，也可以为空，比如座位号 check：检查约束[mysql中不支持]，比如年龄、性别 foreign key：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联字段的值 在从表添加外键约束，用于引用主表中某字段的值，比如学生表的专业编号，员工表的部门编号，员工表的 主键和唯一：二者都保证唯一性且允许多个字段组合设置唯一，主键不允许为空而唯一可以为空，主键在一张表中只能有一个，而唯一可以有多个 外键： 1.要求在从表设置外键关系 2.从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求 3.主表的关联列必须是一个key(主键、唯一) 4.插入数据时，先插入主表，再插入从表 添加约束的时机： 1. 创建表时 2. 修改表时 约束的添加分类： 列级约束 6大约束语法都支持，但外键约束没有效果 表级约束 除了非空、默认，其他的都支持 create table 表名( 字段名 字段类型 列级约束, 字段名 字段类型， 表级约束 ); */ # 1 - 创建表时添加约束 # 1. 添加列级约束 use students; create table stuinfo( id int primary key, # 主键 stuName varchar(20) not null, # 非空 gender char(1) check(gender = '男' or gender = '女'), # 检查约束 seat int unique, # 唯一 age int default 18, # 默认约束18 majorId int references majorId # 外键 ); create table major( id int primary key, majorName varchar(20) ); # 查看表中的所有索引，包括主键、外键、唯一 desc stuinfo; show index from stuinfo; # 2. 添加表级约束 /* constraint 约束名 约束类型(字段名) */ create table user_info( id int, username varchar(20), email varchar(30), age int, majorid int, constraint pk primary key(id), # 主键 constraint uq unique(email), # 唯一键 constraint ck check(gender = '男' or gender = '女'), # 检查 constraint fk_user_info_major foreign key(majorid) references major(id) # 外键 ); # 通用的写法 create table if not exists student_info( id int primary key, student_name varchar(20) not null, sex char(1), age int default 18, seat int unique, major_id int, constraint fk_student_info_major foreign key(major_id) references major(id) ); # 修改表时添加约束 /* 1 - 添加列级约束 alter table 表名 modify column 字段名 字段类型 想约束; 2 - 添加表级约束 alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用]; */ # 1. 添加非空约束 alter table student_info modify column student_name varchar(20) not null; # 2. 添加默认约束 alter table student_info modify column age int default 18; # 3. 添加主键 alter table student_info modify column id int primary key; -- 列级约束 alter table student_info add primary key(id); -- 表级约束 # 4. 添加外键 alter table student_info add constraint fk_student_info_major foreign key(major_id) references major(id); # 修改表时删除约束 # 1. 删除非空约束 alter table student_info modify column student_name varchar(20) null; # 2. 删除默认约束 alter table student_int modify column age int; # 3. 删除主键 alter table student_info drop primary key; alter table student_info drop index seat;
4 - 自增长列
/* 四、自增长列 特点： 1、不用手动插入值，可以自动提供序列值，默认从1开始，步长为1 auto_increment_increment 如果要更改起始值：手动插入值 如果要更改步长：更改系统变量 set auto_increment_increment=值; 2、一个表至多有一个自增长列 3、自增长列只能支持数值型 4、自增长列必须为一个key 一、创建表时设置自增长列 create table 表( 字段名 字段类型 约束 auto_increment ) 二、修改表时设置自增长列 alter table 表 modify column 字段名 字段类型 约束 auto_increment 三、删除自增长列 alter table 表 modify column 字段名 字段类型 约束 */
# 标识列 /* 又称为自增长列 含义：可以不用手动的插入值，系统提供默认的序列值 特点： 1.标识列必须和主键搭配吗？不一定，但是要求是一个key 2.一个表只可以有一个标识列 3.标识列的类型只能是数值型 4.标识列可以通过 set auto_increment_increment = 3;设置步长，也可以通过手动插入值，设置起始值 */ # 创建表时设置标识列 create table tab_identity( id int primary key auto_increment, -- 自动增长 name varchar(20) ); insert into tab_identity values('alex'); # 修改表时设置标识列 alter table tab_identity modify column id int primary key auto_increment; # 修改表时删除标识列 alter table tab_identity modify column id int primary key;
9 - DCL (Data ControlLanguage) 控制语言
1 - 事务：事务由单独单元的一个或多个SQL语句组成，在这 个单元中，每个MySQL语句是相互依赖的。而整个单独单 元作为一个不可分割的整体，如果单元中某条SQL语句一 旦执行失败或产生错误，整个单元将会回滚。所有受到影 响的数据将返回到事物开始以前的状态；如果单元中的所 有SQL语句均执行成功，则事物被顺利执行
2 - 事务的ACID(acid)属性
1. 原子性（Atomicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么 都发生，要么都不发生。
2. 一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态 。
3. 隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个 事务内部的操作及使用的数据对并发的其他事务是隔离的，并发 执行的各个事务之间不能互相干扰。
4. 持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的，接下来的其他操作和数据库故障不应该对其有任何影 响
4 - 事务的隔离性
/* 当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性， 下面看看四种隔离界别 1、READ UNCOMMITTED(未提交读/脏读) （1）、该隔离级别的事务，在数据修改过程中，即使没有提交，其他事务对于这些数据也是可读的。事务可读到未提交的数据也叫 脏读（Dirty Read），由于脏读在实际应用中会导致很多问题，一般这类隔离级别应用很少。 2、READ COMMITTED(不可重复读/提交后读) （1）、不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔， 被另一个事务修改并提交了。例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次 读取该数据就得到了不同的结果，发送了不可重复读。不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交 的脏数据，而不可重复读则是读取了前一事务提交的数据。 （2）、（一般数据库默认事务级别）只有当前事务执行完，把数据提交之后，其他事务才可对这些数据进行读取。也叫不可重 复读，因为其他事务执行2次查询可能前后会得到2个不同结果（事务执行前读一次，执行后读一次）。 3、REPEATABLE READ(可重读/幻读) （1）、幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作， 这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查 看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。幻读和 不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对 的是一批数据整体（比如数据的个数）。 （2）、（Mysql的默认隔离级别）解决了脏读的问题，该级别保证了在同一事务中，多次读取的结果是一致的。但仍旧无法解决幻 读问题。幻读：事务A在读取一定范围内数据时，事务B有对该范围数据进行插入等更新操作，事务A再次读取该范围记录时，会产生 幻读。 4、SERIALIZABLE(序列化/串行化) （1）、在读取每一行数据的时候都对该行数据加锁，强制事务串行执行，在事务提交的时候会释放锁，这时其他事务才能获取相关 数据的锁进行处理这样避免了幻读问题，隔离性达到了最高但是这种执行方式效率 低，锁竞争激烈容易出现超时问题。 只有在对数据一致性要求很强的时候和没有并发或并发量很小的时候使用！ 5、事务隔离级别的查看和修改 查看：select @@tx_isolation; 修改：set global transaction isolation level 设置的隔离级别； 6、mysql中支持的四种隔离级别 （1）、Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。 （2）、Repeatable read (可重复读)：可避免脏读、不可重复读的发生。 （3）、Read committed (读已提交)：可避免脏读的发生。 （4）、Read uncommitted (读未提交)：最低级别，任何情况都无法保证。 */
5 - 事务总结
/* 一、含义 事务：一条或多条sql语句组成一个执行单位，一组sql语句要么都执行要么都不执行 二、特点（ACID） A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行 C 一致性：一个事务可以使数据从一个一致状态切换到另外一个一致的状态 I 隔离性：一个事务不受其他事务的干扰，多个事务互相隔离的 D 持久性：一个事务一旦提交了，则永久的持久化到本地 三、事务的使用步骤 ★ 了解： 隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete 显式事务：具有明显的开启和结束 使用显式事务： ①开启事务 set autocommit=0; start transaction;#可以省略 ②编写一组逻辑sql语句 注意：sql语句支持的是insert、update、delete 设置回滚点： savepoint 回滚点名; ③结束事务 提交：commit; 回滚：rollback; 回滚到指定的地方：rollback to 回滚点名; 四、并发事务 1、事务的并发问题是如何发生的？ 多个事务 同时 操作 同一个数据库的相同数据时 2、并发问题都有哪些？ 脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据 不可重复读：一个事务多次读取，结果不一样 幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据 3、如何解决并发问题 通过设置隔离级别来解决并发问题 4、隔离级别 脏读 不可重复读 幻读 read uncommitted:读未提交 × × × read committed：读已提交 √ × × repeatable read：可重复读 √ √ × serializable：串行化 √ √ √ */
# 控制语言 /* 事务：一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全都部执行 案例：转账问题 事务的特性：ACID 原子性：一个事务不可再分割，要么都执行要么都不执行 一致性：一个事务执行会使数据从一个一致状态切换到另一个一致状态 隔离性：一个事务的执行不受其他事务的干扰 持久性：一个事务一旦提交，则会永久的改变数据库的数据 事务的创建 隐式事务：事务没有明显的开始和结束的标记。比如 insert update delete语句 delete from 表 where id = 1; 显示事务：事务具有明显的开始和结束的标记 前提：必须先设置自动提交功能为禁用 set autocommit = 0; 步骤1：开启事务 set autocommit = 0; start transaction; [可选的] 步骤2：编写事务中的sql语句(select insert update delete) 语句1; 语句2; ... 步骤3：结束事务 commit; 提交事务 rollback; 回滚事务 事务的隔离级别： read uncommitted： read committed(oracle默认): repeatable read(mysql默认): serializable */ # 演示事务的使用步骤 # 1 开启事务 set autocommit = 0; start transaction; # 2 编写一组事务的语句 update accounts set balance = 500 where ussername = '张无忌'; update accounts set balance = 1500 where username = '赵敏'; # 3 结束事务 commit; /* 查看隔离级别 show variables like '%isolation%'; 设置隔离级别 set session|global transaction level 隔离级别; */ # 演示savepoint的使用 set autocommit = 0; start transaction; delete from account where id = 25; savepoint a; -- 设置保存点 delete from account where id = 28; rollback to a; -- 回滚到保存点
1 - 什么是视图
视图：MySQL从5.0.1版本开始提供视图功能。一种虚拟 存在的表，行和列的数据来自定义视图的查询中使用的表 ，并且是在使用视图时动态生成的，只保存了sql逻辑，不 保存查询结果
应用场景：
① 多个地方用到同样的查询结果
② 该查询结果使用的sql语句较复杂
CREATE VIEW my_v1 AS SELECT studentname,majorname FROM student s INNER JOIN major m ON s.majorid=m.majorid WHERE s.majorid=1;
2 - 视图的好处
① 重用sql语句
② 简化复杂的sql操作，不必知道它的查询细节
③ 保护数据，提高安全性
3 - 视图的创建与修改
/* 一、含义 mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。 好处： 1、简化sql语句 2、提高了sql的重用性 3、保护基表的数据，提高了安全性 二、创建 create view 视图名 as 查询语句; 三、修改 方式一： create or replace view 视图名 as 查询语句; 方式二： alter view 视图名 as 查询语句 四、删除 drop view 视图1，视图2,...; 五、查看 desc 视图名; show create view 视图名; 六、使用 1.插入 insert 2.修改 update 3.删除 delete 4.查看 select 注意：视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新 ①包含分组函数、group by、distinct、having、union、 ②join ③常量视图 ④where后的子查询用到了from中的表 ⑤用到了不可更新的视图 七、视图和表的对比 关键字 是否占用物理空间 使用 视图 view 占用较小，只保存sql逻辑 一般用于查询 表 table 保存实际的数据 增删改查 */
# 视图 /* 含义：虚拟表，和普通表一样使用 mysql5.1版本出现的新特性，是通过表动态生成的数据 例如：舞蹈班和普通班的对比 创建视图语法： create view 视图名 as 查询语句; */ # 案例：查询部门的平均工资级别 # ①创建视图查看每个部门的平均工资 create view my_view as select avg(salary) ag,department_id from employees group by department_id; # ②使用 select my_view.ag,g.grade_level from my_view inner join job_grades g on my_view.ag between g.lowest and g.highest_sal; # 视图的修改 /* 语法1： create or replace view 视图名 as 查询语句; 示例： select * from my_view; create or replace view new_my_view as select avg(salary),job_id from employees group by job_id; 语法2： alter view 视图名 as 查询语句; 示例： alter view my_view as select * from employees; */ # 删除视图 /* 语法： drop view 视图名,视图名,...; 示例： drop view my_view; */ # 查看视图 /* desc my_view; show create view my_view; */ # 视图的更新 /* create or replace view my_view as select last_name,email,salary*12*(1+ifnull(commission_pct,0)) "annual salary" from employees; select * from my_view; 1 插入数据 insert into my_view values('alex','zd@qq.com',10000); 2 修改数据 update my_view set name = 'alex' where name = 'egon'; 3 删除 delete from my_view where name = 'egon'; 具备以下特点的视图不润徐更新 • 包含以下关键字的sql语句：分组函数、distinct、group by 、having、union或者union all • 常量视图 • Select中包含子查询 • join • from一个不能更新的视图 • where子句的子查询引用了from子句中的表 */ /* 创建语法的关键字 是否占用物理内存空间 视图 create view 否(只是保存了sql逻辑) 表 create table 是(保存了数据) */
10 - MySQL 变量的使用
/* 分类 一、系统变量 说明：变量由系统提供的，不用自定义 语法： ①查看系统变量 show 【global|session 】variables like ''; 如果没有显式声明global还是session，则默认是session ②查看指定的系统变量的值 select @@【global|session】.变量名; 如果没有显式声明global还是session，则默认是session ③为系统变量赋值 方式一： set 【global|session 】 变量名=值; 如果没有显式声明global还是session，则默认是session 方式二： set @@global.变量名=值; set @@变量名=值； 1、全局变量 服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效 2、会话变量 服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话） 二、自定义变量 说明： 1、用户变量 作用域：针对于当前连接（会话）生效 位置：begin end里面，也可以放在外面 使用： ①声明并赋值： set @变量名=值;或 set @变量名:=值;或 select @变量名:=值; ②更新值 方式一： set @变量名=值;或 set @变量名:=值;或 select @变量名:=值; 方式二： select xx into @变量名 from 表; ③使用 select @变量名; 2、局部变量 作用域：仅仅在定义它的begin end中有效 位置：只能放在begin end中，而且只能放在第一句 使用： ①声明 declare 变量名 类型 【default 值】; ②赋值或更新 方式一： set 变量名=值;或 set 变量名:=值;或 select @变量名:=值; 方式二： select xx into 变量名 from 表; ③使用 select 变量名; */
# 变量 /* 系统变量： 全局变量 会话变量 说明：变量由系统提供，不是用户定义，属于服务器层面 使用的语法： 1 - 查看所有的系统变量 show gloabal | session variables; 2 - 查看满足条件的部分系统变量 show global | [session] variables like '%char%'; 3 - 查看指定的某个系统变量的值 select @@global | [session].系统变量名 4 - 为某个系统变量赋值 set global | [session] 系统变量名 = 值; set @@global | [session].系统变量 = 值; 注意：如果是全局级别。则需要加global，如果是会话级别，则需要加session，如果不写，默认是session 使用示例： 全局变量的使用 1 - 查看所有的全局变量 show global variables; 2 - 查看部分的全局变量 show global variables like '%char%'; 3 - 查看指定的全局变量 select @@global.autocommit; 4 - 为某个指定的全局变量赋值 set @@global.autocommit = 0; 全局变量作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话(连接)有效，但是不能跨重启 会话变量作用域：仅仅针对于当前会话(连接)有效 1 - 查看所有的会话变量 show session variables; 2 - 查看部分的会话变量 show session variables like '%char%'; 3 - 查看指定的某个会话变量 select session @@tx_isolation; 4 - 为某个会话变量赋值 set @@session.tx_isolation = 'read-uncommitted'; */ /* 自定义变量： 用户变量 局部变量 说明：变量是用户自定义的，不是由系统提供的 使用步骤： 声明 赋值 使用(查看、比较、运算) 用户变量作用域：针对于当前会话(连接)有效，同于会话变量的作用域 1 - 声明并初始化(赋值操作符 = 或 :=) set @用户变量名 = 值; set @用户变量名 := 值; (推荐使用) select @用户变量名 := 值; 2 - 赋值(更新用户变量的值) set @用户变量名 = 值; set @用户变量名 := 值; (推荐使用) select @用户变量名 := 值; 案例： set @name = 'alex'; set @name = 100; select 字段 into @变量名 from 表; 案例： set @count select count(*) into @count from employees; -- 将表中的行数赋值给变量@count 3 - 查看用户变量的值 select @用户变量名; 自定义局部变量作用域：仅仅在定义它的begin end 中有效 应用在 begin end 中的第一句话 使用步骤： 1 - 声明 declare 变量名 类型; declare 变量名 类型 default 值; 2 - 赋值 set @用户变量名 = 值; set @用户变量名 := 值; (推荐使用) select @用户变量名 := 值; 或 set @count select count(*) into @count from employees; 3 - 使用 select 局部变量名; 用户变量和局部变量的对比 作用域 定义和使用的位置 语法 用户变量 当前会话 会话中的任何地方 必须加@符号，不用限定类型 局部变量 begin end中 只能在 begin end 中，且为第一句话 一般不用加@符号，需要限定类型 案例：声明两个变量并服初始值，求和，并打印 1 - 用户变量 set @m = 1; set @n = 2; set @sum = @m + @n; select @sum; */
11 - MySQL 存储过程
# 存储过程和函数 /* 存储过程和函数：类似于Java中的方法 存储过程： 含义：一组预先编译号的SQL语句的集合，理解成批处理语句 好处： 1.提高代码的重用性 2.简化操作 3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 1 - 创建语法； create procedure 存储过程名(参数列表) begin 存储过程体(一组合法的sql语句) end 注意： 1.参数列表包含3部分：参数模式 参数名 参数类型 举例：in student_name varchar(20) 参数模式： in ；该参数可以作为输入，也就是该参数需要调用方传入值 out ：该参数可以作为输出，也就是该参数可以作为返回值 inout ；该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值 2.如果存储过程体仅仅只有一句话，begin end 可以省略，存储过程体中的每条SQL语句的结尾要求必须加分号； 3.存储过程的结尾可以使用 delimiter 重新设置 语法： delimiter 结束标记 delimiter $; -- 案例 2 - 调用方法； call 存储过程名(实参列表); 存储过程使用示例： 1.空参列表(无参的存储过程) 案例：插入到admin表中2条记录 select * from admin; -- id自增 # 创建示例 delimiter $ create procedure myp() begin insert into admin(username,password) values('alex1','123'),('alex2','456'); end $ # 调用示例 call myp()$ -- 注意是无参存储过程 2.带in参数的存储过程 案例1：创建存储过程实现根据女神名。查询对应的男神信息 delimiter $ # ①创建存储过程 create procedure myp(in beautyName varcher(20)) begin select bo.* from boys bo right join beauty b on bo.id = b.boyfriend_id where b.name = beautyName; end $ # ②调用创建的存储过程 call myp('赵敏')$ -- 注意这里带参调用存储过程 案例2：创建存储 过程实现，用户是否登录成功 delimiter $ create procedure myp(in username varchar(20),in password varchar(20)) begin declare result int default 0; -- 声明并初始化 select count(*) into result -- 赋值 from admin where admin.username = username and admin.password = password; select if(result > 0,'成功','失败'); -- 使用 end $ call myp('alex','123')$ 3.创建带out模式的存储过程 案例1：根据女神名，返回对应的男神名 create procedure myp(in brautyName varchar(20),out boyName varchar(20)) begin select bo.boyName into boyName from boys bo inner join beauty b on bo.id = b.boyfriend_id where b.name = beautyName; end$ call myp('赵敏',@bName) $ select @bName; 案例2：根据女神名，返回对应的男神名和男神魅力值 create procedure myp(in beautyName varchar(20),out boyName varchar(20),out userCP int) begin select bo.boyName,bo.userCP into boyName,userCP from boys bo inner join beauty b on bo.id = b.boyfriend_id where b.name = beautyName; end$ # 调用 call myp('赵敏',@boyName,@userCP)$ select @boyName,@cp$ 4.创建带inout模式参数的存储 案例1：传入a和b两个值，最终a和b都翻倍并返回 create procedure myp(inout a int,inout b int) begin set a = a * 2; set b = b * 2; end $ # 调用 set @a = 10; set @b = 20; call myp(@a,@b)$ select @a,@b 存储过程练习： 1 - 创建存储过程实现传入用户名和密码，插入到admin表中 create procedure test_prol(in username varchar(20),in loginpwd varcahr(20)) begin insert into admin(admin.username,password) values(username,loginpwd); end $ # 调用 call test_prol('alex','123')$ select * from admin$ 2 - 创建存储过程实现传入女神编号，返回女神名称和女神电话 create procedure test_pro(in id int,out beautyName varchar(20),out beautyPhone varchar(20)) begin select b.name,b.phone into beautyName,beautyPhone from beauty b where b.id = id; end $ 3 - 创建存储过程或函数实现传入两个女神的生日，返回大小 create procedure test_pro(in beautyBirth1 datetime,in beautyBirth2 datetime,out result int ) begin select datediff(beautyBirth1,beauty2) into result; end $ # 调用 call test_pro('1999-1-9',now(),@result)$ select @result $ 4 - 创建存储过程或函数实现传入一个日期，格式化成xx年xx月并返回 create procedure test_pro4(in mydate datetime,out strDate varchar(20)) begin select date_format(mydate,'%y年%m月%d日') into strDate; end $ # 调用 call test_pro4(now(),@result)$ select @result $ 5 - 创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录 create procedure test_pro5(in size int,in startIndex int) begin select * from beauty limit startIndex,size; end $ # 调用 call test_pro5(3,5)$ 删除存储过程： 语法：drop procedure 存储过程名 drop procedure myp; -- 示例 查看存储过程的信息 语法：show create procedure 存储过程名; */
12 - MySQL 函数
# 函数 /* 含义：一组预先编译好的SQL语句的集合，理解成批处理语句 1.提高代码的重用性 2.简化操作 3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 区别： 存储过程：可以有0个返回，也可以有多个返回，适合批量插入，批量更新 函数：有且仅有一个返回，适合做处理数据后返回一个结果 函数的创建语法： create function 函数名(参数列表) returns 返回类型 begin 函数体 end 注意： ① 参数列表 包含两部分，分别是 参数名 参数类型 ② 函数体一定要有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不报错，但不建议 ③函数体中仅有一句话，则可以省略 begin end ④使用 delimiter 语句设置结束标记 函数的调用语法： select 函数名(参数列表) 案例演示： 1.无参有返回 案例：返回员工的员工个数 create function my_func() returns int begin declare c int default 0; -- 定义变量 select count(*) into c -- 赋值 from employees return c; end $ select my_func()$ -- 调用函数 2.有参有返回 案例1：根据员工名，返回它的工资 create function my_func(empName varchar(20)) returns double begin set @sal = 0; -- 定义用户变量 select salary into @sal -- 赋值 from employees where last_name = empName; return @sal; end$ select my_func('alex') $ -- 调用函数 案例2：根据部门名，返回该部门的平均工资 create function my_func(depName varchar(20)) returns double begin declare sal double; select avg(salary) into sal from employees e inner join departments d on e.department_id = d.department_id where d.department_name = depName; return sal; end $ select my_func('IT')$ -- 调用函数 查看函数结构语法： show create function my_func; 删除函数语法： drop function my_func; 案例：创建函数，实现传入两个float，返回二者之和 create function test_func(num1 float,num2 float) returns float begin declare sum float default 0; set sum = num1 + num2; return sum; end $ select test_func(1.0,2.0)$ -- 调用函数 */
13 - MySQL 流程控制
/* 特点： 1、if函数 功能：实现简单双分支 语法： if(条件，值1，值2) 位置： 可以作为表达式放在任何位置 2、case结构 功能：实现多分支 语法1： case 表达式或字段 when 值1 then 语句1; when 值2 then 语句2； .. else 语句n; end [case]; 语法2： case when 条件1 then 语句1; when 条件2 then 语句2； .. else 语句n; end [case]; 位置： 可以放在任何位置， 如果放在begin end 外面，作为表达式结合着其他语句使用 如果放在begin end 里面，一般作为独立的语句使用 3、if结构 功能：实现多分支 语法： if 条件1 then 语句1; elseif 条件2 then 语句2; ... else 语句n; end if; 位置： 只能放在begin end中 */
/* 位置： 只能放在begin end中 特点：都能实现循环结构 对比： ①这三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave或iterate）则必须添加名称 ② loop 一般用于实现简单的死循环 while 先判断后执行 repeat 先执行后判断，无条件至少执行一次 1、while 语法： 【名称:】while 循环条件 do 循环体 end while 【名称】; 2、loop 语法： 【名称：】loop 循环体 end loop 【名称】; 3、repeat 语法： 【名称:】repeat 循环体 until 结束条件 end repeat 【名称】; 二、循环控制语句 leave：类似于break，用于跳出所在的循环 iterate：类似于continue，用于结束本次循环，继续下一次 */
# mysql 流程控制结构 /* 顺序结构：程序从上往下依次执行 分支结构：程序从两条或多条路径中选择一条去执行 循环结构：程序在满足一定条件的基础上，重复执行一段代码 */ /* 分支结构： 1 - if函数 功能：实现简单的双分支 语法： if(表达式1,表达式2,表达式3) 执行顺序: 如果表达式1成立,则if函数返回表达式2的值,否则返回表达式3的值; 应用：任何地方 2 - case结构 情况1：类似于Java中的switch语句，一般用于实现等值判断 语法： case 变量|表达式|字段 when 要判断的值 then 返回的值1; when 要判断的值 then 返回的值2; ... else 要返回的值n; end case; 情况2：类似于Java中的多重if语句，一般用于实现区间判断 语法： case when 要判断的条件1 then 返回的值1; when 要判断的条件2 then 返回的值2; ... else 要返回的值n; end case; 特点： 1.可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end 中或 begin end 的外面 2.可以作为独立的语句去使用，只能放在 begin end 中 3.如果when中的值满足或条件成立，则执行对应的then后面的语句，并且结束case，如果都不满足，则执行else中的语句或值 4.else可以省略，如果else省略了，并且所有when条件都不满足，则返回null 案例：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100 显示A;80-90 显示B;60-80 显示C 否则显示D create procedure test_case(in score int) begin case when score >= 90 and score <= 100 then select 'A'; when score >= 80 then select 'B'; when sore >= 60 then select 'C'; else select 'D'; end case; end $ call test_case(82)$ -- 调用存储过程 3 - if结构 功能：实现多重分支 语法： if 条件1 then 语句1; elseif 条件2 then 语句2; ... else 语句n; 应用在begin end 中 案例1：创建存储过程，根据传入的成绩，来显示等级，比如传入的成绩：90-100 返回A;80-90 返回B;60-80 返回C 否则返回D create function test_if(score int) returns char begin if score >= 90 and score <= 100 then return 'A'; elseif score >= 80 then return 'B'; elseif score >= 60 then retrun 'C'; else return 'D'; end if; end $ select test_if(20)$ -- 调用函数 */ /* 循环结构 分类： while loop repeat 循环控制： iterate 类似于 continue 继续、结束本次循环、继续下一次 leave 类似于 break 跳出 结束当前所在的循环 while 循环语法： [标签:] while 循环条件 do 循环体; end while [标签]; 案例：批量插入，根据次数插入到admin表中多条记录 create procedure pro_while(in insertCount int) begin declare i int default 1; while i <= insertCount do insert into admin(username,password) values(concat('alex',i),'123'); set i = i + 1; end while; end $ 添加leave语句 案例：批量插入，根据次数插入到admin表中多条记录，如果次数 > 20 则停止 truncate table admin$ -- 删除所有行记录 drop procedure test_while$ -- 删除存储过程test_while create procedure test_while(in insertCount int) declare i int default 1; a:while i <= inertCount do insert into admin(username,password) values(concat('alex',i)); if i > 20 then leave a; end if; set i = i + 1; end while a; begin $ call test_while(100) $ 联想： while(循环条件){ 循环体; } loop 循环语法： [标签:] loop 循环体; end loop [标签]; 可以用来模拟简单的死循环 repeat 循环语法： [标签:] repeat 循环体; until 结束循环的条件 end repeat [标签]; */ /* 已知表 stringcontent，其中字段：id 自增长，content varchar(20) 向该表插入指定个数的，随机的字符串 drop if exists stringcountent; create table stringcountent( id int primary key auto_increment, content varchar(20) ); delimiter $ create procedure test_insert(in insertCount int) begin declare i int default 1; -- 定义以循环变量，表示插入次数 declare str varchar(26) default 'abcdefghijklmnopqrstuvwxyz'; declare startindex int deault 1; -- 代表起始索引 declare len int default 1; -- 代表截取的字符的长度 while i <= insertCount do set len = floor(rand()*(20 - startIndex +1) + 1); -- 产生一个随机的整数，代表截取长度 set startIndex = floor(rand()*26 +1); -- 产生一个随机的整数，代表起始索引1-26 insert into stringcountent(content) values(substr(str,startIndex,len)); set i = i + 1; -- 循环变量更新 end while; end $ */
学习没有捷径，需要日积月累的积淀及对技术的热爱。
