网站推广联盟,企业公司建站平台,部队网站建设报告,网站开发最重要的技巧#Author 流云 #Version 1.0 一、引言
1.1 现有的数据存储方式有哪些#xff1f; Java程序存储数据#xff08;变量、对象、数组、集合#xff09;#xff0c;数据保存在内存中#xff0c;属于瞬时状态存储。 文件#xff08;File#xff09;存储数据#xff0c;保存… #Author 流云 #Version 1.0 一、引言
1.1 现有的数据存储方式有哪些 Java程序存储数据变量、对象、数组、集合数据保存在内存中属于瞬时状态存储。 文件File存储数据保存在硬盘上属于持久状态存储。 1.2 以上存储方式存在哪些缺点 没有数据类型的区分。 存储数据量级较小。 没有访问安全限制。 没有备份、恢复机制。 没有快速查找特定文件内容的手段。 1.3 没有使用数据库你将会变成这样 1.4 使用数据库你会变成这样 二、数据库 2.1 概念 数据库是“按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合。 2.2 数据库的分类 网状结构数据库美国通用电气公司IDSIntegrated Data Store以节点形式存储和访问。 层次结构数据库IBM公司IMSInformation Management System定向有序的树状结构实现存储和访问。 关系结构数据库Oracle、DB2、MySQL、SQL Server以表格Table存储多表间建立关联关系通过分类、合并、连接、选取等运算实现访问。 非关系型数据库ElastecSearch、MongoDB、Redis多数使用哈希表表中以键值key-value的方式实现特定的键和一个指针指向的特定数据。 三、数据库管理系统 3.1 概念 数据库管理系统DataBase Management SystemDBMS指一种操作和管理数据库的大型软件用于建立、使用和维护数据库对数据库进行统一管理和控制以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中的数据。 3.2 常见数据库管理系统 Oracle被认为是业界目前比较成功的关系型数据库管理系统。Oracle数据库可以运行在UNIX、Windows等主流操作系统平台完全支持所有的工业标准并获得最高级别的ISO标准安全性认证。 DB2IBM公司的产品DB2数据库系统采用多进程多线索体系结构其功能足以满足大中公司的需要并可灵活地服务于中小型电子商务解决方案。 SQL ServerMicrosoft 公司推出的关系型数据库管理系统。具有使用方便可伸缩性好与相关软件集成程度高等优点。 SQL Server C# SQLLite应用在手机端的数据库。 3.3 数据库的排名 https://db-engines.com/en/ranking 四、MySQL 4.1 简介 MySQL是一个关系型数据库管理系统由瑞典MySQL AB 公司开发属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一在 WEB 应用方面MySQL是最好的RDBMS(Relational Database Management System关系数据库管理系统) 应用软件之一。 mysql5开源 , mysql8社区版。 1. MySQL Community Server 社区版本开源免费但不提供官方技术支持。 2. MySQL Enterprise Edition 企业版本需付费可以试用30天。 3. MySQL Cluster 集群版开源免费。可将几个MySQL Server封装成一个Server。 4. MySQL Cluster CGE 高级集群版需付费。 4.2 访问与下载 官方网站https://www.mysql.com/ 下载地址https://dev.mysql.com/downloads/mysql/ 4.3 安装 运行mysql-installer-community-5.7.28.0.msi进入安装步骤 4.4 卸载 控制台卸载。 找到mysql的安装目录进行删除。 programdata 删除mysql 注意如果卸载后如有未删除的MySQL服务可采用手动删除。 以管理员身份打开命令行输入 sc delete MySQL57 回车。 4.5 配置环境变量 Windows 创建MYSQL_HOMEC:\Program Files\MySQL\MySQL Server 5.7 追加PATH%MYSQL_HOME%\bin; MacOS / Linux 终端中输入cd ~ 进入目录并检查.bash_profile是否存在有则追加无则创建 创建文件 touch .bash_profile 打开文件 open .bash_profile 输入export PATH${PATH}:/usr/local/mysql/bin 保存并退出终端。 4.6 MySQL目录结构 核心文件介绍 4.7 MySQL配置文件 在MySQL安装目录中找到my.ini文件并打开my.ini文件查看几个常用配置参数。 五、SQL语言 5.1 概念 SQLStructured Query Language结构化查询语言用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。 经验通常执行对数据库的“增删改查”简称CCreateRReadUUpdate DDelete。 5.2 MySQL应用 对于数据库的操作需要在进入MySQL环境下进行指令输入并在一句指令的末尾使用 ; 结束。 5.3 基本命令 查看MySQL中所有数据库。 mysql SHOW DATABASES; #显示当前MySQL中包含的所有数据库 创建自定义数据库 mysql CREATE DATABASE mydb1; #创建mydb数据库
mysql CREATE DATABASE mydb2 CHARACTER SET gbk; #创建数据库并设置编码格式为gbk
mysql CREATE DATABASE IF NOT EXISTS mydb4; #如果mydb4数据库不存在则创建如果存在则不创建。 查看数据库创建信息 mysql SHOW CREATE DATABASE mydb2; #查看创建数据库时的基本信息 修改数据库 mysql ALTER DATABASE mydb2 CHARACTER SET gbk; #查看创建数据库时的基本信息 删除数据库 mysql DROP DATABASE mydb1; #删除数据库mydb1 查看当前所使用的数据库 mysql select database(); #查看当前使用的数据库 使用数据库 mysql USE mydb1; #使用mydb1数据库 六、客户端工具 6.1 Navicate Navicat是一套快速、可靠并价格相宜的数据库管理工具专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉化的图形用户界面而建的让你可以以安全并且简单的方式创建、组织、访问并共用信息。 http://www.navicat.com.cn/ 6.2 SQLyog MySQL可能是世界上最流行的开源数据库引擎但是使用基于文本的工具和配置文件可能很难进行管理。SQLyog提供了完整的图形界面即使初学者也可以轻松使用MySQL的强大功能。其拥有广泛的预定义工具和查询、友好的视觉界面、类似 Excel 的查询结果编辑界面等优点。 https://www.webyog.com/product/sqlyog 6.3 使用客户端工具 1创建一个数据库;选择编码为utf-8 2创建数据表 3往数据表里面存储数据 七、 DML 操作【重点 】 7.1 新增INSERT INSERT INTO 表名(列 1列 2列 3....) VALUES(值 1值 2值 3......); 7.1.1 添加一条信息 #添加一条工作岗位信息
INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES(JAVA_Le,JAVA_Lecturer,2500,9000); #添加一条员工信息
INSERT INTO t_employees
EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_IDVALUES(194,Samuel,McCain,SMCCAIN, 650.501.3876, 1998-07-01, SH_CLERK,3200, NULL, 123, 50); 注意表名后的列名和 VALUES 里的值要一一对应个数、顺序、类型。 7.2 修改UPDATE UPDATE 表名 SET 列 1新值 1 ,列 2 新值 2,.....WHERE 条件; 7.2.1 修改一条信息 #修改编号为100 的员工的工资为 25000
UPDATE t_employees SET SALARY 25000 WHERE EMPLOYEE_ID 100; #修改编号为135 的员工信息岗位编号为 ST_MAN工资为3500
UPDATE t_employees SET JOB_IDST_MAN,SALARY 3500 WHERE EMPLOYEE_ID 135; 注意SET 后多个列名值绝大多数情况下都要加 WHERE 条件指定修改否则为整表更新。 7.3 删除DELETE DELETE FROM 表名 WHERE 条件 7.3.1 删除一条信息 #删除编号为135 的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID135; #删除姓Peter并且名为 Hall 的员工
DELETE FROM t_employees WHERE FIRST_NAME Peter AND LAST_NAMEHall; 注意删除时如若不加 WHERE条件删除的是整张表的数据。 7.4 清空整表数据TRUNCATE TRUNCATE TABLE 表名; 该语法不属于DML语句。 7.4.1 清空整张表 #清空t_countries整张表
TRUNCATE TABLE t_countries; 注意与 DELETE 不加 WHERE 删除整表数据不同TRUNCATE 是把表销毁再按照原表的格式创建一张新表。 八、数据查询【重点 】 8.1 数据库表的基本结构 关系结构数据库是以表格Table进行数据存储表格由“行”和“列”组成。 经验执行查询语句返回的结果集是一张虚拟表。 -- 员工表 employee
employee_id --编号
first_name --名字
email --邮箱
salary --月薪
commission_pct --提成
manager_id --所属经理编号
department_id --部门id
job_id --工作编号-- 部门表 department
department_id --部门编号
department_name --部门名称-- 工作表 job
job_id --工作id
job_name --工作名称
job_desc --工作内容--经理表 manager
manager_id --经理编号
manager_name --经理名字 8.2 基本查询 语法SELECT 列名 FROM 表名。 8.2.1 查询部分列 #查询员工表中所有员工的编号、名字、邮箱
SELECT employee_id,first_name,email FROM t_employees; 8.2.2 查询所有列 #查询员工表中所有员工的所有信息所有列
SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees; 注意生产环境下优先使用列名查询。*的方式需转换成全列名效率低可读性差。 8.2.3 对列中的数据进行运算 #查询员工表中所有员工的编号、名字、年薪
SELECT employee_id , first_name , salary*12 FROM t_employees; 注意%是占位符而非模运算符。 8.2.4 列的别名 列 as 列名 #查询员工表中所有员工的编号、名字、年薪列名均为中文
SELECT employee_id as 编号 , first_name as 名字 , salary*12 as 年薪 FROM t_employees; 8.2.5 查询结果去重 DISTINCT 列名 #查询员工表中所有经理的ID。
SELECT DISTINCT manager_id FROM t_employees; 8.3排序查询 语法 SELECT 列名 FROM 表名 ORDER BY 排序列 [排序规则] 8.3.1 依据单列排序 #查询员工的编号名字薪资。按照工资高低进行降序排序。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC; 8.3.2 依据多列排序 #查询员工的编号名字薪资。按照工资高低进行升序排序薪资相同时按照编号进行升序排序。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC; 8.4 条件查询 语法SELECT 列名 FROM 表名 WHERE 条件。 8.4.1 等值判断 #查询薪资是11000的员工信息编号、名字、薪资
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary 11000; 注意与 java 不同mysql 中等值判断使用 。 8.4.2 逻辑判断and、or、not #查询薪资是11000并且提成是0.30的员工信息编号、名字、薪资
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary 11000 AND commission_pct 0.30; 8.4.3 不等值判断 、 、 、 、! 、 #查询员工的薪资在6000~10000之间的员工信息编号名字薪资
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary 6000 AND salary 10000; 8.4.4 区间判断between and #查询员工的薪资在6000~10000之间的员工信息编号名字薪资
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000; #闭区间包含区间边界的两个值 注在区间判断语法中小值在前大值在后反之得不到正确结果。 8.4.5 NULL 值判断IS NULL、IS NOT NULL IS NULL 列名 IS NULL IS NOT NULL 列名 IS NOT NULL #查询没有提成的员工信息编号名字薪资 , 提成
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL; 8.4.6 枚举查询 IN (值 1值 2值 3 ) #查询部门编号为70、80、90的员工信息编号名字薪资 , 部门编号
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN(70,80,90);
注in的查询效率较低可通过多条件拼接。 8.4.7 模糊查询 LIKE _ 单个任意字符 列名 LIKE 张_ LIKE %任意长度的任意字符 列名 LIKE 张% 注意模糊查询只能和 LIKE 关键字结合使用。 #查询名字以L开头的员工信息编号名字薪资 , 部门编号
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE L%;#查询名字以L开头并且长度为4的员工信息编号名字薪资 , 部门编号
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE L___; 8.4.8 分支结构查询 CASEWHEN 条件1 THEN 结果1WHEN 条件2 THEN 结果2WHEN 条件3 THEN 结果3ELSE 结果
END 注意通过使用CASE END进行条件判断每条数据对应生成一个值。 经验类似 Java 中的switch。 #查询员工信息编号名字薪资 , 薪资级别对应条件表达式生成
SELECT employee_id , first_name , salary , department_id ,CASEWHEN salary10000 THEN AWHEN salary8000 AND salary10000 THEN BWHEN salary6000 AND salary8000 THEN CWHEN salary4000 AND salary6000 THEN DELSE EEND as LEVEL
FROM t_employees; 8.5 时间查询 语法SELECT 时间函数([参数列表]) 经验执行时间函数查询会自动生成一张虚表一行一列 8.5.1 获得当前系统时间 #查询当前时间
SELECT SYSDATE(); #查询当前时间
SELECT NOW(); #获取当前日期
SELECT CURDATE(); #获取当前时间
SELECT CURTIME(); 8.6 字符串查询 语法 SELECT 字符串函数 ([参数列表]) 8.6.1 字符串应用 #拼接内容
SELECT CONCAT(My,S,QL); #字符串替换
SELECT INSERT(这是一个数据库,3,2,MySql);#结果为这是 MySql 数据库 #指定内容转换为小写
SELECT LOWER(MYSQL);#mysql #指定内容转换为大写
SELECT UPPER(mysql);#MYSQL #指定内容截取
SELECT SUBSTRING(JavaMySQLOracle,5,5);#MySQL 8.7 聚合函数 语法SELECT 聚合函数(列名) FROM 表名; 经验对多条数据的单列进行统计返回统计后的一行结果。 8.7.1 单列总和 #统计所有员工每月的工资总和
SELECT sum(salary)
FROM t_employees; 8.7.2 单列平均值 #统计所有员工每月的平均工资
SELECT AVG(salary)
FROM t_employees; 8.7.3 单列最大值 #统计所有员工中月薪最高的工资
SELECT MAX(salary)
FROM t_employees; 8.7.4 单列最小值 #统计所有员工中月薪最低的工资
SELECT MIN(salary)
FROM t_employees; 8.7.5 总行数 #统计员工总数
SELECT COUNT(*)
FROM t_employees; #统计有提成的员工人数
SELECT COUNT(commission_pct)
FROM t_employees; 注意聚合函数自动忽略null值不进行统计。 8.8 分组查询 语法SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据列; 8.8.1 查询各部门的总人数 #思路
#1.按照部门编号进行分组分组依据是 department_id
#2.再针对各部门的人数进行统计count
SELECT department_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id; 8.8.2 查询各部门的平均工资 #思路
#1.按照部门编号进行分组分组依据department_id。
#2.针对每个部门进行平均工资统计avg。
SELECT department_id , AVG(salary)
FROM t_employees
GROUP BY department_id 8.8.3 查询各个部门、各个岗位的人数 #思路
#1.按照部门编号进行分组分组依据 department_id。
#2.按照岗位名称进行分组分组依据 job_id。
#3.针对每个部门中的各个岗位进行人数统计count。
SELECT department_id , job_id , COUNT(employee_id)
FROM t_employees
GROUP BY department_id , job_id; 8.8.4 常见问题 #查询各个部门id、总人数、first_name
SELECT department_id , COUNT(*) , first_name
FROM t_employees
GROUP BY department_id; #error 注分组查询中select显示的列只能是分组依据列或者聚合函数列不能出现其他列。 8.9 分组过滤查询 语法SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则。 8.9.1 统计部门的最高工资 #统计60、70、90号部门的最高工资
#思路
#1). 确定分组依据department_id
#2). 对分组后的数据过滤出部门编号是60、70、90信息
#3). max()函数处理
SELECT department_id , MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id in (60,70,90)
# group确定分组依据department_id
#having过滤出60 70 90部门
#select查看部门编号和max函数。 8.10 限定查询 SELECT 列名 FROM 表名 LIMIT 起始行查询行数。 8.10.1 查询前 5 行记录 #查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5; 注意起始行是从 0 开始代表了第一行。第二个参数代表的是从指定行开始查询几行。 8.10.2 查询范围记录 #查询表中从第四条开始查询 10 行
SELECT * FROM t_employees LIMIT 3,10; 8.10.3 LIMIT典型应用 分页查询一页显示 10 条一共查询三页 #思路第一页是从 0开始显示 10 条
SELECT * FROM LIMIT 0,10;#第二页是从第 10 条开始显示 10 条
SELECT * FROM LIMIT 10,10;#第三页是从 20 条开始显示 10 条
SELECT * FROM LIMIT 20,10; 经验在分页应用场景中起始行是变化的但是一页显示的条数是不变的。 8.11 查询总结 8.11.1 SQL 语句编写顺序 SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列asc|descLIMIT 起始行总条数。 8.11.2 SQL 语句执行顺序 1.FROM 指定数据来源表
2.WHERE : 对查询数据做第一次过滤
3.GROUP BY 分组
4.HAVING : 对分组后的数据第二次过滤
5.SELECT : 查询各字段的值
6.ORDER BY : 排序
7.LIMIT : 限定查询结果 8.12 子查询作为条件判断 SELECT 列名 FROM 表名 Where 条件 (子查询结果) 8.12.1 查询工资大于Bruce 的员工信息 #1.先查询到 Bruce 的工资一行一列
SELECT SALARY FROM t_employees WHERE FIRST_NAME Bruce;#工资是 6000
#2.查询工资大于 Bruce 的员工信息
SELECT * FROM t_employees WHERE SALARY 6000;
#3.将 1、2 两条语句整合
SELECT * FROM t_employees WHERE SALARY SELECT SALARY FROM t_employees WHERE FIRST_NAME Bruce ; 注意将子查询 ”一行一列“的结果作为外部查询的条件做第二次查询。 子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断。 8.13 子查询作为枚举查询条件 SELECT 列名 FROM 表名 Where 列名 in(子查询结果); 8.13.1 查询与名为King同一部门的员工信息 #思路
#1. 先查询 King 所在的部门编号(多行单列)
SELECT department_id
FROM t_employees
WHERE last_name King; //部门编号80、90#2. 再查询80、90号部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (80,90);#3.SQL合并
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name King); #N行一列 将子查询 ”多行一列“的结果作为外部查询的枚举查询条件做第二次查询。 8.13.2 工资高于60部门所有人的信息 #1.查询 60 部门所有人的工资多行多列
SELECT SALARY from t_employees WHERE DEPARTMENT_ID60;#2.查询高于 60 部门所有人的工资的员工信息高于所有
select * from t_employees where SALARY ALL(select SALARY from t_employees WHERE DEPARTMENT_ID60);#。查询高于 60 部门的工资的员工信息高于部分
select * from t_employees where SALARY ANY(select SALARY from t_employees WHERE DEPARTMENT_ID60); 注意当子查询结果集形式为多行单列时可以使用 ANY 或 ALL 关键字。 8.14 子查询作为一张表 SELECT 列名 FROM子查询的结果集WHERE 条件 8.14.1 查询员工表中工资排名前 5 名的员工信息 #思路
#1. 先对所有员工的薪资进行排序排序后的临时表
select employee_id , first_name , salary
from t_employees
order by salary desc#2. 再查询临时表中前5行员工信息
select employee_id , first_name , salary
from (临时表)
limit 0,5;#SQL合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary
desc) as temp
limit 0,5; 将子查询 ”多行多列“的结果作为外部查询的一张表做第二次查询。 注意子查询作为临时表为其赋予一个临时表名。 8.15 合并查询了解 SELECT * FROM 表名 1 UNION SELECT * FROM 表名 2 SELECT * FROM 表名 1 UNION ALL SELECT * FROM 表名 2 8.15.1 合并两张表的结果去除重复记录 #合并两张表的结果去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2; 注意合并结果的两张表列数必须相同列的数据类型可以不同。 8.15.2 合并两张表的结果保留重复记录 #合并两张表的结果不去除重复记录显示所有
SELECT * FROM t1 UNION ALL SELECT * FROM t2; 经验使用 UNION 合并结果集会去除掉两张表中重复的数据。 8.16 表连接查询 SELECT 列名 FROM 表 1 连接方式 表 2 ON 连接条件。 8.16.1 内连接查询INNER JOIN ON #1.查询所有有部门的员工信息不包括没有部门的员工 SQL 标准
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID t_jobs.JOB_ID#2.查询所有有部门的员工信息不包括没有部门的员工 MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID t_jobs.JOB_ID 经验在 MySql 中第二种方式也可以作为内连接查询但是不符合 SQL 标准。 而第一种属于 SQL 标准与其他关系型数据库通用。 8.16.2 三表连接查询 #查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT * FROM t_employees e
INNER JOIN t_departments d
on e.department_id d.department_id
INNER JOIN t_locations l
ON d.location_id l.location_id 8.16.3 左外连接LEFT JOIN ON #查询所有员工信息以及所对应的部门名称没有部门的员工也在查询结果中,部门名称以NULL 填充
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM
t_employees e
LEFT JOIN t_departments d
ON e.department_id d.department_id; 注意左外连接是以左表为主表依次向右匹配匹配到返回结果。 匹配不到则返回 NULL 值填充。 8.16.4 右外连接RIGHT JOIN ON #查询所有部门信息以及此部门中的所有员工信息没有员工的部门也在查询结果中员工信息以NULL填充
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM
t_employees e
RIGHT JOIN t_departments d
ON e.department_id d.department_id; 注意右外连接是以右表为主表依次向左匹配匹配到返回结果。 匹配不到则返回 NULL 值填充。 九、数据库高级 9.1 存储过程 MySQL 5.0 版本开始支持存储过程。存储过程思想上很简单就是数据库 SQL 语言层面的代码封装与重用。 存储过程Stored Procedure是一种在数据库中存储复杂程序以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集经编译创建并保存在数据库中用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。 DELIMITER $$ 或 DELIMITER // 这个代表当前sql语句的结尾符号不是 ; 而是变成了指定的 $$ 或者 // 因为在存储过程的创建过程中,我们会有;代表一行语句的结尾 9.1.1 创建存储过程 CREATE[DEFINER { user | CURRENT_USER }]PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typecharacteristic:COMMENT string| LANGUAGE SQL| [NOT] DETERMINISTIC| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }routine_body:Valid SQL routine statement[begin_label:] BEGIN[statement_list]……
END [end_label] DELIMITER $$
CREATE PROCEDURE pro_test1()BEGININSERT INTO t2(tname) VALUES(haha);UPDATE t1 SET tnamejack1 WHERE tid1;SELECT * FROM t1;END $$
call pro_test1() 9.1.2 存储过程参数 IN | OUT | INOUT 类型 名称 类型 in参数的使用 CREATE PROCEDURE pro_test2(IN t2_name VARCHAR(20) , IN t1_id INT)BEGININSERT INTO t2(tname) VALUES(t2_name);UPDATE t1 SET tnamejack1 WHERE tidt1_id;SELECT * FROM t1;END $$
CALL pro_test2(hehe,2) out参数的使用 DELIMITER $$
CREATE PROCEDURE pro_test3(OUT tname VARCHAR(20))BEGINSET tname xixi;END $$
CALL pro_test3(tname);
SELECT tname; inout参数的使用尽量分开使用in和out不使用inout DELIMITER $$
CREATE PROCEDURE pro_test4(INOUT tname VARCHAR(20))BEGINSELECT tname;SELECT CONCAT(tname,hello) INTO tname;END $$
SET tnamejack;
CALL pro_test4(tname);
SELECT tname; 9.1.3 存储过程变量 局部变量 DECLARE var_name[, var_name] ... type [DEFAULT value]; set和默认值 declare num2 int default 100;
select num2;
DELIMITER $$
CREATE PROCEDURE pro_test5()BEGINDECLARE num INT DEFAULT 100;SELECT num;set num 200;select num;END $$
CALL pro_test5 使用 into 进行赋值 DELIMITER $$
CREATE PROCEDURE pro_test6()BEGINDECLARE num INT DEFAULT 100;SELECT num;SELECT tid INTO num FROM t1 WHERE tnamejack;SELECT num;END $$
CALL pro_test6 用户变量 变量名 不需要申明可直接使用;类似于Java全局变量。 DELIMITER $$
CREATE PROCEDURE pro_test7()BEGINSET param_t1 300;SELECT param_t1;SELECT tid INTO param_t1 FROM t1 WHERE tnamejack;SELECT param_t1;END $$
CALL pro_test7 系统变量 变量名 根据系统变量的作用域分为全局变量与会话变量两个符号全局变量(global.)在MySQL启动的时候由服务器自动将全局变量初始化为默认值全局变量的默认值可以通过更改MySQL配置文件(my.ini、my.cnf)来更改。会话变量(session.)在每次建立一个新的连接的时候由MySQL来初始化
MYSQL会将当前所有全局变量的值复制一份来做为会话变量也就是说如果在建立会话以后没有手动更改过会话变量与全局变量的值那所有这些变量的值都是一样的。
全局变量与会话变量的区别对全局变量的修改会影响到整个服务器但是对会话变量的修改只会影响到当前的会话。 9.1.4 条件语句 if-then-else 语句 IF expression THENstatements;
END IF;
----------------------------------------
IF expression THENstatements;
ELSEelse-statements;
END IF;
----------------------------------------
IF expression THENstatements;
ELSEIF elseif-expression THENelseif-statements;
...
ELSEelse-statements;
END IF; DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)BEGINIF tid 1 THENINSERT INTO t1(tname) VALUES(tom);END IF;
END $$
CALL pro_test8(1);
-----------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)BEGINIF tid 1 THENINSERT INTO t1(tname) VALUES(tom);ELSESELECT CONCAT(tid,xixi);END IF;END $$
CALL pro_test8(2);
---------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE pro_test8(IN tid INT)BEGINIF tid 1 THENINSERT INTO t1(tname) VALUES(tom);ELSEIF tid 2 THENSELECT CONCAT(tid,xixi);ELSEIF tid 3 THENSELECT CONCAT(tid,xxxxxx);END IF;END $$
CALL pro_test8(3); 9.1.5 循环语句 WHILE……DO……END WHILE REPEAT……UNTIL END REPEAT LOOP……END LOOP GOTO 不建议用 WHILE……DO……END WHILE DELIMITER $$
CREATE PROCEDURE pro_test9(IN number INT)BEGIN-- 满足什么条件继续循环WHILE number 0 DOSELECT number;SET numbernumber-1;END WHILE;END $$
CALL pro_test9(6); REPEAT……UNTIL END REPEAT DELIMITER $$
CREATE PROCEDURE pro_test10(IN number INT)BEGINREPEATINSERT INTO t1(tname) VALUES(CONCAT(aa,number));SET numbernumber-1;UNTIL number 0 -- 这里不需要加分号 这个是满足什么条件退出循环END REPEAT;END $$
CALL pro_test10(6); LOOP…LEAVE…END LOOP DELIMITER $$
CREATE PROCEDURE pro_test11()BEGINBEGINDECLARE i INT DEFAULT 0;loop_x : LOOPINSERT INTO t1(tname) VALUES(CONCAT(bb,i));SET ii1;IF i 5 THENLEAVE loop_x;END IF;END LOOP;END;END $$
CALL pro_test11 9.1.6 分支语句 DELIMITER $$
CREATE PROCEDURE pro_test12(IN var INT)BEGINCASE varWHEN 1 THENSELECT a;WHEN 2 THENSELECT b;ELSESELECT c;END CASE;END $$
CALL pro_test12(2); 9.2 函数了解 自定义函数,和concat等内置函数意义一致。 CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type
[characteristic ...]
BEGINroutine_body
END;1func_name 存储函数的名称。
2param_name type可选项指定存储函数的参数。
3RETURNS type指定返回值的类型。
4characteristic可选项指定存储函数的特性。
5routine_bodySQL代码内容。调用函数
SELECT func_name([parameter[,…]]); DELIMITER //
CREATE FUNCTION fc_test(id INT)
RETURNS VARCHAR(20)
BEGINSELECT tname INTO name FROM t1 WHERE tidid;RETURN name;
END //
SELECT fc_test(2); 9.3 触发器Trigger了解 9.3.1 主外键级联操作 Cascade 在主表上update/delete记录时同步update/delete掉子表的匹配记录。 No Action 如果子表中有匹配的记录则不允许对父表对应候选键进行update/delete操作。 Restrict 同no action, 都是立即检查外键约束。 Set null 在主表上update/delete记录时将子表上匹配记录的列设为null。 注trigger不会受外键cascade行为的影响即不会触发trigger。 NULL、RESTRICT、NO ACTION
删除从表记录不存在时主表才可以删除。删除从表主表不变
更新从表记录不存在时主表才可以更新。更新从表主表不变CASCADE
删除删除主表时自动删除从表。删除从表主表不变
更新更新主表时自动更新从表。更新从表主表不变SET NULL
删除删除主表时自动更新从表值为NULL。删除从表主表不变
更新更新主表时自动更新从表值为NULL。更新从表主表不变 9.3.2 Trigger 触发器是与表有关的数据库对象在满足定义条件时触发并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。 CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW
trigger_stmt
trigger_name触发器的名称
tirgger_time触发时机为BEFORE或者AFTER
trigger_event触发事件为INSERT、DELETE或者UPDATE
tb_name表示建立触发器的表明就是在哪张表上建立触发器
trigger_stmt触发器的程序体可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATEBEFORE和AFTER参数指定了触发执行的时间在事件之前或是之后
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN执行语句列表
END DELIMITER $$
CREATE TRIGGER tri_test1 AFTER DELETE
ON t1 FOR EACH ROWBEGININSERT INTO t2(tname) VALUES(ssss);
END$$SELECT * FROM t1;
SELECT * FROM t2;UPDATE t1 SET tname aaa WHERE tid 1;
DELETE FROM t1 WHERE tid1; 9.4 视图了解 视图是一个虚拟表是sql的查询结果其内容由查询定义。同真实的表一样视图包含一系列带有名称的列和行数据在使用视图时动态生成。视图的数据变化会影响到基表基表的数据变化也会影响到视图 1简单使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件对用户来说已经是过滤好的复合条件的结果集。 2安全使用视图的用户只能访问他们被允许查询的结果集对表的权限管理并不能限制到某个行某个列但是通过视图就可以简单的实现。 3数据独立一旦视图的结构确定了可以屏蔽表结构变化对用户的影响源表增加列对视图没有影响源表修改列名则可以通过修改视图来解决不会造成对访问者的影响。 总而言之使用视图的大部分情况是为了保障数据安全性提高查询效率。 CREATE [OR REPLACE] [ALGORITHM {UNDEFINED | MERGE | TEMPTABLE}]VIEW view_name [(column_list)]AS select_statement[WITH [CASCADED | LOCAL] CHECK OPTION] CREATE VIEW view_test1
AS
SELECT * FROM t1;CREATE VIEW view_test2
AS
SELECT employee_id,first_name,manager_name FROM employee LEFT JOIN manager ON
employee.manager_id manager.manager_id; 9.5 索引和约束 9.5.1 约束 作用是为了保证数据的完整性而实现的摘自一套机制它具体的根据各个不同的数据库的实现而有不同的工具约束 1、非空约束not null; 指示某列不能存储 NULL 值。
2、唯一约束unique(); unique约束的字段要求必须是唯一的但null除外。
3、主键约束primary key(); 主键约束not null unique确保某列或两个列多个列的结合有唯一标识有助于更容易更快速地找到表中的一个特定的记录。
4、外键约束foreign key ;保证一个表中的数据匹配另一个表中的值的参照完整性。
5、自增约束auto_increment。
6、默认约束default 给定默认的值。
7、检查性约束check 保证列中的值符合指定的条件。 9.5.2 索引 作用: 快速定位特定数据提高查询效率确保数据的唯一性快速定位特定数据可以加速表和表之间的连接实现表与表之间的参照完整性使用分组和排序语句进行数据检索时可以显著减少分组和排序的时间全文检索字段进行搜索优化 1、主键索引primary key;
2、唯一索引unique;
3、常规索引index;
4、全文索引full text;
全文索引是MyISAM的一个特殊索引类型它查找的是文本中的关键词主要用于全文检索。
MySQL InnoDB从5.6开始已经支持全文索引但InnoDB内部并不支持中文、日文等因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。SHOW INDEX FROM table_name;索引字段尽量使用数字型简单的数据类型
尽量不要让字段的默认值为NULL
使用唯一索引
使用组合索引代替多个列索引
注意重复/冗余的索引、不使用的索引 不使用索引 1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求。
2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率。
3.定义为text和image和bit数据类型的列不应该增加索引。
4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系。
