MySQL数据库知识

数据库

大纲
一.数据库的基本知识
二.Mysql的安装与基本操作
三.SQL语句(重点)
四.视图
五.变量
六.存储过程
七.函数与流程控制
注:[]指的是可有可无

数据库的基本知识

数据库的概念

数据库是“按照数据结构来组织、存储和管理数据的仓库”.
是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合.

关系型数据库

关系型数据库,存储的格式可以直观地反映实体间的关系。
关系型数据库和常见的表格比较相似，关系型数据库中表与表之间是有很多复杂的关联关系的.
常见的关系型数据库有Mysql，SqlServer等.在轻量或者小型的应用中，使用不同的关系型数据库对系统的性能影响不大；
但是在构建大型应用时，则需要根据应用的业务需求和性能需求，选择合适的关系型数据库.

数据库管理系统

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件；
用于建立、使用和维护数据库，简称DBMS。它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。
用户通过DBMS访问数据库中的数据，数据库管理员也通过DBMS进行数据库的维护工作。
它可以支持多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据库。
大部分DBMS提供数据定义语言DDL（Data Definition Language）和数据操作语言DML（Data Manipulation Language）;
供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作.

常见的数据库管理软件有:
Oracle(甲骨文)公司的产品,是大型的数据库管理系统.收费.
MySQL,是开源的数据库,被Oracle收购了,是中小型数据库,5.x版本是免费,从6.x开始收费.
SQLServer,微软的产品,收费的中型数据库.
DB2:IBM公司的产品,收费的大型数据库.

MySQL的安装与基本操作

MySQL的安装步骤

安装前的准备

如果计算机已经安装过MySQL而需要重新安装时,要把原来的MySQL卸载干净.
首先找到MySQL的安装路径,打开文件my.ini,该文件是mysql的配置文件;
在该文件中找到如下路径:datadir="D:/IDE/MySQLData/Data/",该路径是MySQL存储数据的路径.
然后通过控制面板卸载MySQL,并根据上面的路径,将相关文件彻底删除.

安装

根据安装步骤的文件安装即可.需要注意:

• 1.路径不能为中文
• 2.端口号为3306不要修改
• 3.字符集设置为utf8.

MySQL的环境变量配置

默认情况下,在安装后会自动配置MySQL的环境变量,但有时需要手动配置环境变量.在path中配置到MySQL的安装目录的bin层级即可.比如:

D:IDEMySQLin;

MySQL的基本操作

MySQL的启动与停止

默认情况下,MySQL的服务是自动启动的,如果调整成了手动启动,需要进行启动操作,使用如下指令启动和停止

启动指令: net start mysql的服务名称
停止指令: net stop mysql的服务名称

MySQL的登录与退出

在登录前,首先要保证MySQL的服务处于开启状态.使用如下指令登录和退出.
登录指令: mysql -h ip地址 -P 3306 -u 用户名 -p密码
注意:

-h代表要访问哪个mysql服务器,如果是本机,可以省略
-P字母大写,代表端口号,如果是3306也可以不写
-p代表后面输入登录密码,p字母和密码没有空格

退出指令:

ctrl + c 或者 exit

SQL语言(一)

SQL语句的介绍

SQL语言的概念

SQL(Structured Query Language),即结构化查询语句.它是一种数据库查询和修改的程序设计语言,用来对数据库的数据进行操作和管理.
按照功能分SQL语言分为如下几个部分:

• 1.DDL(数据定义语言)
  提供了创建数据库,创建表,修改库,修改表等功能,关键字有:create, drop, alter等.
• 2.DML(数据操作语言)
  提供了添加数据,修改数据,删除数据等功能,关键字有:insert, update, delete等.
• 3.DQL(数据查询语言)
  提供了数据的查询,关键字:select
• 4.DCL(数据控制语言)
  用来设置数据库的访问权限等功能
• 5.TCL(事务控制语言)
  提供了对事务的操作等功能,关键字:commit, rollback等.

SQL语言的语法规范

• 1.不区分大小写,但是建议关键字大写,表名和列名小写.
• 2.每条语句最好用分号结尾
• 3.每条语句根据需要,可以进行缩进或换行
• 4.注释
  单行注释: #注释内容
  多行注释:/**
  注释内容
  **/

SQL语言的常用数据类型

• 1.字符串类型
  声明关键字为:varchar或char两种,varchar长度可变,char长度不变.字符串一般情况下要用单引号或者双引号引起来.
• 2.数值类型
  声明关键字:tinyint(1个字节), smallint(2个字节), int(4个字节), bigint(8个字节),float(单精度), double(双精度)
• 3.日期类型
  - date:保存的日期信息,只有年月日,格式为: yyyy-MM-dd
  - time:保存的日期信息,只有时分秒,格式为: HH:mm:ss
  - datetime:保存的日期信息为年月日时分秒,格式为: yyyy-MM-dd HH:mm:ss
  - timestamp:不需要保存日期信息,系统会自动保存,保存的时间为当前系统时间,格式和datetime一样的.范围为:
  1970-2038.
• 4.文本类型

text:保存大字符串,最大可以达到4GB.

DDL语言

对库的操作

• 1.创建数据库
  语句:
  create database 数据库的名称;
  例:创建一个名称为study的数据库
  create database study;
• 2.查询数据库
  语句1:
  show databases; 查询所有数据库
  语句2:
  show create database 数据库名称; 查询指定数据库
• 3.删除数据库
  语句:
  drop database 数据库名称
  例:删除study数据库
  drop database study;
• 4.查询当前使用的数据库
  语句:
  select database();
• 5.选择要使用的数据库
  语句:
  use 数据库名称;
  例:使用study数据库
  use study;

对表的操作

创建表

语句:
create table 表名称(
字段1 数据类型(长度) [约束],
字段2 数据类型(长度) [约束],
...
字段n 数据类型(长度) [约束]
可能还有其他的语句,比如声明主键或外键
) ;
例:创建一张员工表emp,该表有员工id(e_id), 员工姓名(e_name), 员工薪水(e_salary),员工职位(e_job),员工部门id(e_did),员工的入职时间(hiretime)
create table emp(
e_id int,
e_name varchar(20),
e_salary double(8, 2),
e_job varchar(20),
e_did int,
hiretime datetime
)

修改表

• 1.添加新字段
  语句:
  alter table 表名 add 新字段名称 数据类型(长度);
  例:在emp表中添加一个新字段e_gender(varchar),表示性别
  alter table emp add e_gender varchar(10);
• 2.修改字段名
  语句:
  alter table 表名 change 旧字段名称 新字段名称 数据类型(长度)
  例:将emp表中的e_gender字段修改为e_sex
  alter table emp change e_gender e_sex varchar(10);
• 3.修改字段的数据类型和长度
  语句:
  alter table 表名 modify 字段名称 数据类型(长度)
  例:将emp表中的e_name的长度修改为50
  alter table emp modify e_name varchar(50);
• 4.删除字段
  语句:
  alter table 表名 drop 字段名称;
  例:在emp表中,删除e_sex字段
  alter table emp drop e_sex;
• 5.修改表名称
  语句:
  alter table 旧表名 rename to 新表名;
  例:将emp表改成employee
  alter table emp rename to employee;

删除表

• 语句:
  drop table 表名;

查看表

• 语句1:
  show tables; 查看所有表
• 语句2:
  show create table 表名; 查看指定表
• 语句3:
  desc 表名; 查看表结构.

DML语言

插入记录

(1)插入记录

• 语句:
  insert into 表名 (字段名称1, 字段名称2...) values(值1, 值2...)
• 注意:
  - 1.字段类型和值类型一致,而且一一对应.
  - 2.字段的数量和值的数量一致.
  - 3.字段名称也可以不写,则默认为所有字段赋值
  - 4.也可以批量插入.
• 例:向emp表中插入若干条记录

insert  into  emp(e_name, e_salary)  values(‘jack’, 10000);
insert  into  emp  values(2, ‘rose’,  9000,  ‘staff’,  1,  ‘1999-1-1 10:10:10’);
insert  into  emp(e_name, e_salary)  values(‘tom’,  20000), (‘jerry’,  10000);	#批量插入

修改记录

• 语句:
  update 表名 set 字段名称1 = 新值, 字段名称2 = 新值... [where子句]; where子句用来过滤条件
• 例:修改emp表中2号员工的姓名为bush,薪水为:9001.
  update 表名 set e_name = ‘bush’, e_salary = 9001 where e_id = 2;

删除记录

• 语句:
  delete from 表名[where子句];
• 例:将emp表中的2号员工删除.
  delete from emp where e_id = 2;
• 注意:如果没有where语句,那么是清空表格.

SQL语言(二)

DQL(一)

基础查询

• 语句:
  select 查询目标 [from 表];
• 注意:
  • 1.查询目标可以是字段,也可以是常量,表达式,变量, 函数等
  • 2.可以通过 as 关键字(as也可以省略)来对查询目标起别名.
• 例1:查询常量值和表达式

select 100;
select ‘100’ + 10;
select ‘China’ + ‘10’
select 100 + null;

• 注意:
  在mysql中,”+”号只有一个功能,即加法运算,并且遵循如下规则:

  • 1.两个操作数都为数值型,就做加法运算.
  • 2.如果两个操作数其中有一个为字符串,会试图将该字符串转化数值,如果转化成功就继续执行加法运算,反之将该字符串视为0
  • 3.只要有一个操作数为null,其结果为null.

• 例2:查询函数
  select version();
  

• 例3:查询表中数据

  • 1.查询emp表中的所有员工姓名和工资
    select e_name, e_salary from emp;
  • 2.查询所有员工姓名和其部门编号,并且部门编号起别名:dept_id
    select e_name, e_did as dept_id from emp;
  • 3.查询emp表中,所有员工工资提高20%后的工资
    select e_name, e_salary * 1.2 as ‘提高20%后的工资’ from emp;
  • 4.查询emp表中所有记录
    select * from emp;

去重查询

• 语句:
  select distinct 字段名称 from 表名;
• 例:查询emp表中涉及到的所有部门编号
  select e_did from emp;

条件查询(过滤查询)

• 条件查询:根据条件过滤原始表中的记录,查询到满足条件的数据.
  语句:
  select 字段名称 from 表名称 where 条件表达式;
  条件表达式支持很多条件运算符和一些关键字,如下:
• 1.常见的运算符

, < , >=, <=, =, !=

  - 例1:查询工资是20000的员工

select * from emp where e_salary = 20000;
- 例2:查询工资小于10000的员工姓名
select e_name from emp where e_salary < 10000;
- 例3:查询职位不是staff的员工姓名
select e_name from emp where e_job != ‘staff’;

• 2.逻辑运算关键字
  and: 与运算
  or: 或运算
  not: 非运算
  - 例1:查询工资大于等于8000,小于20000的员工姓名
  select e_name from emp where e_salary >= 8000 and e_salary < 20000;
  - 例2:查询工资大于等于8000或小于20000的员工姓名
  select e_name from emp where e_salary >= 8000 or e_salary < 20000;
  - 例3:查询职位不是staff的员工姓名
  select e_name from emp where not e_job = ‘staff’;

• 3.between ... and...
  指定范围过滤,即:大于等于....小于等于...
  - 例1:查询emp表中,工资在8000到10000(包含8000和10000)的员工姓名
  select e_name from emp where e_salary between 8000 and 10000;

• 4.in
  指定值过滤,只要有这个值就满足条件
  - 例1:查询emp表中,工资为7000或8000或9000的员工.
  select e_name from emp where e_salary in(7000, 8000, 9000);

• 5.like
  和通配符匹配进行模糊查询,通配符有%和_. %代表多个任意字符, 代表一个任意字符.
  - 例1:查询emp表中张姓员工的信息
  select * from emp where e_name like ‘张%’;
  - 例2:查询emp表中姓名为三个字的员工
  select * from emp where e_name like ‘ _ _’;

• 6.is not null/is null
  - 例1:查询没有部门的员工.
  select * from emp where e_did is null;

排序查询

• 语句:
  select 字段名称 from 表名 [where子句] order by 字段名称 [asc|desc]
  注意:默认的排序规则为升序, 如果想使用降序,需要使用desc.
  • 例1:查询emp表中所有员工的姓名和工资,并且按照工资降序排序
    select e_name, e_salary from emp order by e_salary desc;
  • 例2:查询emp表中的姓名和部门编号和工资,按照工资降序排序,如果工资相同按照部门升序.
    select e_name, e_salary, e_did from emp order by e_salary desc, e_did asc;

分组函数

MySQL提供了一些函数,其中有一些函数叫做分组函数(也叫聚集函数或聚合函数),通常和分组查询一起使用,用来对数据进行统计.有如下函数:

• 1.avg():用来统计平均值
  • 例:求emp表中所有员工的平均工资
    select avg(e_salary) from emp;
• 2.sum():用来统计和
  • 例:求emp表中工资之和
    select sum(e_salary) from emp;
• 3.max():用来获取最大值
  • 例:求最高工资
    select max(e_salary) from emp;
• 4.min():用来获取最小值
• 5.count():计数函数,用来统计有效记录条数
  • 例1:查询工资高于8000的员工个数
    select count(*) from emp where e_salary > 8000;
  • 例2:查询已经分配了部门的员工的个数
    select count(e_did) from emp;
    注意:分组函数都不会统计值为null的数据.

分组查询

分组查询主要用在统计数据时,需要分组对数据进行统计.所以分组查询往往和分组函数一起使用.

• 语句:
  select 查询目标 from 表名 [where子句] group by 分组字段 [having子句]
  • 例1:查询emp表中,每个部门的最低工资.
    select e_did, min(e_salary) from emp group by e_did;
  • 例2:查询emp表中,每个部门的人数
    select count(*) from emp group by e_did;
  • 例3:查询emp表中平均工资高于8200的部门
    select e_did from emp group by e_did having avg(e_salary) > 8200;
• 注意:
  • 1.select后面出现的字段,必须在group by后面出现.
  • 2.where子句是在分组前进行行过滤,而having是分组后过滤.
  • 3.where子句中不能出现分组函数,而having子句可以出现分组函数.
  • 4.注意:group by 后面的字段名称可以在select后面出现,
    但是select后面的字段名称必须要在group by后面出现,除了集合函数.

limit子句.
limit子句用来限制结果集的起始行,和总行数.

• 例:查询stu表中前5行数据

select * from stu limit 0, 5;

总结:
查询语句的书写顺序

select -- from -- where -- group by -- having -- order by -- limit;

查询语句的执行顺序

from -- where -- group by-- 聚合函数 -- having子句 -- select -- order by -- limit

常用函数

类似java中方法,MySQL将一组逻辑语句封装在方法体中,对外暴露方法名.
基本用法:
select 函数名称(实参) [from 表]

• 1.字符串处理函数

  - 1.length():获取字符串字节个数
  

例1:获取字符串长度

select length(‘中国NO.1’);

  - 2.concat():拼接字符串

例1:字符串拼接显示

select concat(‘haha’, ‘heihei’) as ‘句子’;

例2:拼接字段信息

select concat(e_name, ‘的工资是’, e_salary) from emp;

  - 3.substr():截取字符串

例1:

select substr(“hello”, 1, 3);

  - 4.replace():字符串替换

例1:将”我昨天读了红楼梦”中的”红楼梦”替换”三国演义”

select replace(‘我昨天读了红楼梦’, ‘红楼梦’, ‘三国演义’);

例2:查询emp表中所有员工姓名+”先生”,

select replace(e_name, e_name, concat(e_name, ‘先生’)) from emp;

• 2.数学函数

1.round()
2.floor()
3.ceil()
4.mod() 取余函数

• 3.日期函数
  1.now():当前时间函数:年月日时分秒
  例:获取当前时间

select now() as ‘当前时间’;

2.year()/month()/day():获取年/月/日函数
例:获取当前时间的年份

select year(now()) as ‘年’;

3.curdate():当前系统年月日
例:获取当前的年月日

select curdate();

4.curtime():获取当前系统时分秒
例:获取当前的时分秒

select curtime();

5.str_to_date():将字符串转化成日期
例:将”2003-1-1”转化成日期

select str_to_date(“2003-1-1”, ‘%Y-%m-%d’);

6.date_format():将日期转换成字符串
例:将当前时间转化成字符串

select date_format(now(), ‘%Y-%m-%d %H:%i:%s’);

7.datediff():返回两个日期天数之差
例:计算2000-1-1到2020-1-1的天数之差

select datediff(‘2000-1-1’, ‘2020-1-1’);

• 4.其他常用函数

version() 查询当前数据库版本
database() 查看当前使用数据库
ifnull() 如果为null可以设置默认值

例:查询员工姓名和部门编号,如果没有安排部门统统视为1号部门.

select e_name, ifnull(e_did, 1) from emp;

• 5.if()函数:类似三目运算符,可以完成二选一
  例1:10和1比较,实现二选一

select if(10 > 1, ‘10大于1’, ‘10小于1’);

例2:显示emp表中的员工姓名和工资情况,如果工资大于8000,就显示工资挺高,否则显示工资一般.

select e_name, if(e_salary > 8000, ‘工资挺高’, ‘工资一般’) from emp;

概念模型

概念模型概述

当我们要完成一个软件系统时,需要把系统中的实体抽取出来,形成概念模型,比如部门和员工都是系统中的实体.概念模型中的实体最终会成为java中的类和数据库中的表.实体之间还存在着关系,关系大致分为三种:

• 1.一对多:比如每个员工都从属一个部门,而一个部门可以有多个员工,其中员工是多方,部门是一方.
• 2.一对一:比如配偶之间就是一对一的关系
• 3.多对多:例如老师和学生的关系,一个老师可以有多个学生,一个学生可以有多个老师.
  在创建java类或者数据库中的表时也要体现出来实体关系.

对象模型

概念模型在java中形成实体类(javaBean),类通过使用成员变量来描述关系,通常是双向关联.

• 例如:部门和员工之间的关系可以用如下类描述:
  

关系模型

概念模型在数据库中形成表,根据不同的关系,表一般如下设计:

• 1.一对一关系时,可以合并表(不是必须的,根据需求设计).
• 2.一对多关系时,通过主外键关联来描述其关系.
• 3.多对多关系时,通过中间表包含双方的外键来描述.

数据完整性

数据完整性的作用

保证用户输入的数据保存到数据库中是正确的。包含有实体完整性、域完整型和引用完整性三种，通过在表中添加约束来实现。

实体完整性

表中的每一行(每一条记录)就代表一个实体,实体完整性就是保证每一行数据不重复,通过主键约束、唯一约束、自动增长列来维护.

• 1.主键约束
  描述：主键是表中的一个或多个字段,它的值用于唯一地标识表中的某一条记录,每个表都要有一个主键且唯一.
  其特点是数据唯一,不能为null,并且能被引用.创建方式如下:

  - 方式一：在创建字段时声明约束
  

  - 方式二:此种方式可以更方便的创建联合主键

  创建联合主键

  - 方式三：修改表方式声明主键

例:将emp表中的e_id设置为主键

alter table emp add primary key(e_id);

• 2.唯一约束(unique)
  描述:保证数据唯一,不能重复,但可以为null。
  创建方法：
  

• 3.自动增长列(auto_increment)
  描述:通常和主键一起使用，给主键添加自动增长的数值，列只能是整数类型
  创建方法：
  

域完整性

域代表单元格,域完整性就是保证每个单元格的数据合理.通过默认值约束和非空约束保证域的完整性.

• 1.默认值约束(default)
  描述:给域赋予默认值
  创建方法：
  

• 2.非空约束(not null)
  描述:保证数据必须非null.
  创建方法：
  

参照完整性(引用完整性)

参照的完整性要求关系（多表）中不允许引用不存在的实体。通过外键约束来保证其参照完整性.

• 1.外键约束(foreign key)
  描述:表的外键是另一表的主键, 并且前表为称之为从表，而后一张表称之为主表，从表在关系上依赖于主表.同时,通过外键约束描述了表与表(实体与实体)之间的关系.
  创建方法如下:
• 方式一:修改表结构方式,设置外键
  创建一张部门表,该表为主表:
  

将员工表的e_did设置为外键:

alter table emp add constraint fk_emp_dept foreign key(e_did) references dept(d_id);

• 方式二:在创建表时直接声明外键.
  

DQL(二:多表查询)

连接查询

笛卡尔积

因为在现实中,表与表之间往往是有一定关系的(一对多,多对多等),所以在执行查询时,往往将多张表连接在一起查询.比如:

select * from dept, emp;

通过观察查询结果,我们可以发现,很多条没有意义的数据.原因是对于上面的连接查询,其结果是笛卡尔积.
笛卡尔积的概念:假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。将表看成一个集合,那么表中一条的记录就是一个元素.
我们可以通过where e_did = d_id这个条件(主外键关联),使连接查询有意义.

内连接

上面的sql语句就是内连接查询,但是不满足sql99标准.标准版写法如下:
select 字段名称... from 表1 [inner] join 表2 on 连接条件 [inner] join 表3 on 连接条件...
where 过滤条件.
注意:有时不同的表,字段可能相同,那么可以使用表名.字段名的方式去区分,而且还可以起别名.
例:查询所有员工的姓名以及员工所对应的部门名称

select dept.d_name '部门名称', emp.e_name '员工名称' from dept inner join emp on e_did = d_id;

外连接

外连接分为:左外连接和右外连接
左/右外连接:以左/右表为基础,不仅返回连接表中符合连接条件及过滤条件的数据行,也返回左/右表中仅符合过滤条件但不符合连接条件的数据行.
语句:

左外: select 字段... from 表1 left [outer] join表2 on连接条件 where 过滤条件;
右外: select 字段... from 表1 right [outer] join表2 on连接条件 where 过滤条件;

例:查询所有员工信息和对应的部门信息,包括没有分配部门的员工信息.

select * from dept right[outer] join emp on e_did = d_id;

自连接

有时候在查询时,需要通过起别名的方式,自己连接自己.
例:查询员工(非经理)的姓名和员工所在的部门经理姓名

select e1.e_name, e2.e_name from emp e1, emp e2 where e1.e_did = e2.e_did and e1.e_job = 'staff' and e2.e_job = 'manager';

子查询

子查询:一个select语句中包含了另一个完整的select语句.子查询的特点如下:

• 1.子查询的语句放在小括号内部.
• 2.子查询语句可以放在from后面,select后面和where后面,以及having.
• 3.子查询语句优先于主查询执行,主查询使用的是子查询的查询结果.

子查询根据查询结果的行数分为如下两种:

• 1.单行子查询
  也就是子查询的结果集只有一行,一般搭配这些操作符: > , <, = , !=, >= , <=
• 2.多行子查询
  也就是子查询的结果集有多行,一般搭配的操作符:any, all, in, not in

例1:查询工资高于高于张飞工资的员工姓名

select e_name from emp where e_salary >
(select e_salary from emp where e_name = ‘张飞’);

例2:查询和刘备同一个部门的员工

select e_name from emp where e_did = (select e_did from emp where e_name = ‘刘备’);

例3:查询工资高于2号部门所有人的员工姓名

select e_name from emp where e_salary >
(select max(e_salary) from emp where e_did = 2);

例4:查询部门和工资与赵云完全相同的员工姓名

select e_name from emp where e_did = (select e_did from emp where e_name = '赵云')
empempand e_salary = (select e_salary from emp where e_name = '赵云');

或者

select e_name from emp where (e_did, e_salary) in (select e_did a, e_salary from emp where e_name = '赵云');

例5:查询有4个以上下属的员工信息

select e_name from emp where e_did =
(select e_did from emp where e_job = 'staff' group by e_did having count(e_did) > 4) and e_job != 'staff';

合并查询

把两个select语句的查询结果合并.关键字:union 和union all
例:

注意:在使用合并查询之前,要注意每个select语句的结果集的列数和列的数据类型要相同.和列的名称名称没有关系.

SQL语言(三)

事务

事务的概念

事务是构成一个完整的逻辑单元的一条或者是多条sql语句.在所谓的完整的逻辑单元中,每条sql语句是相互依赖的.而整个逻辑单元做为一个不可分割的整体.如果单元中的某条sql语句一旦执行失败或者产生错误,那么整个单元的sql语句都将失败(回滚).如果单元中的某条sql语句执行成功,那么整个单元的sql语句都要执行成功.
例:a向b转账的业务,需要如下sql语句:

update account set money=money-100 where name = ‘a’;
update account set money=money+100 where name=’b’;

上面这两条sql语句可以理解为一个整体,即一个完整的工作逻辑单元.因为少了哪句,该业务都会出现问题.

事务的特性(ACID)

原子性:指一个事务是一个不可分割的工作单元,事务中的操作要么都成功,要么都失败.
一致性:事务必须使数据库中的数据从一个一致性状态变换到另一个一致性状态.
隔离性:事务的隔离性是多个用户并发访问数据库时,数据库会为每一个用户开启事务,不能被其他用户的事务的操作所干扰,多个并发事务要相互隔离.
持久性:一个事务一旦提交,它对数据库中的数据的改变是永久的.

事务的隔离级别

多个事务同时操作一个数据库的数据时,就会产生事务并发,会产生如下问题:

• 1.脏读:一个事务读取了另一个事务未提交的数据.
• 2.不可重复读:在同一个事务中,多次读取的数据不一致.(读取到了其他事务执行的update操作)
• 3.幻读(虚读):指的是一个事务读取到了另外一个事务在执行的insert语句.
  数据库通过设置隔离级别来避免事务的并发问题,MySQL有四种隔离级别:
• 1.read uncommitted:未提交读,是指脏读,不可重复读,幻读都有可能发生.
• 2.read committed:已提交读.可以避免脏读现象.但是不能避免不可重复读和幻读.
• 3.repeatable read:可重复读,可以避免脏读和不可重复读,但是不能避免幻读(mysql默认的隔离级别)
• 4.serializable:可串行化.脏读,不可重复读和幻读都可以避免.
  注意:隔离级别越高,执行效率越低.数据越安全.

TCL

查看事务的隔离级别

语句:

select @@tx_isolation;

修改事务的隔离级别

语句:

set session transaction isolation level 隔离级别;

事务的操作

在默认情况下,mysql是自动提交事务的,也就是说每条sql语句都是一条独立的事务.如果一个事务包含多条sql语句,需要手动开启事务,语句如下:

set autocommit = 0;

或:

begin;

如果在事务执行的过程中,出现了异常.需要对事务进行回滚.语句如下:

rollback;

如果事务没有异常发生,需要对事务进行提交,语句如下:

commit;

视图

视图的概述

视图的概念

视图就是从一张表或几张表中导出的虚拟的表.
目的是为了提高sql语句的重用性和效率,并且通过视图实现了和表分离,提高了安全性.

视图和表的区别

表里面存储的是记录,也就是数据,那么表是占用物理空间的.而视图并不占用物理空间(基本不占用,因为还需要保存sql的逻辑).

视图的操作

视图的创建和使用

创建视图的语句如下:

create view 视图名称 as 查询语句;

例1:对emp表和dept表满足连接查询的查询结果集创建视图

create view emp_dept as select * from dept, emp where dept.d_id = emp.e_did;

例2:利用上面的视图,查询emp表中张姓员工的姓名和其部门名称

select e_name, d_name from emp_dept where e_name like ‘张%’;

视图的修改

视图的修改有两种方式:
方式1:

create or replace view 视图名称 as 新查询语句;

方式2:

alter view 视图名称 as 新查询语句;

视图的删除

语句:

drop view 视图名称;

视图的写操作对原始视图的影响

因为视图是一张虚拟的表,那么对于视图中的数据的增删改,是有可能影响原始数据.我们以如下视图来说明:

create view myView as select * from emp;

插入/修改/删除数据

例:向myView视图中插入一条记录

insert into myView(e_id, e_name) values(17, ‘张三’);

通过观察后,发现修改了myView也修改了emp表.

关于视图操作的注意事项

视图中的sql语句如果包含了如下内容,即使修改了视图也不会修改表内容:

• 1.分组函数(聚合函数)
• 2.distinct
• 3.group by having
• 4.union 或 union all
• 5.join(连接查询)
• 6.常量(比如sql语句为:select 1)
• 7.子查询

变量

变量的分类

在Mysql中,变量分为两大类型,一个是系统变量,一个是自定义变量.而系统变量又有全局变量和会话变量两种.
自定义变量又有局部变量和用户变量两种.如下:

系统变量

系统变量概述

变量由系统定义,不是用户定义的,属于服务器层面的变量.又分为全局变量(global)和会话变量(session).
全局变量的作用域为所有连接.但是不能跨重启.会话变量的作用域为一次连接.

系统变量的基本操作

• 1.查看所有系统变量
  语句:

show global/session variables; 注意:如果不写global和session,默认为session

例:查询以”tx”开头的系统变量

show global/session variables like “tx%”

• 2.查询单个指定系统变量
  语句:

select @@global/session.系统变量名称, 注意:同上

例:查询”port”变量

select @@port;

• 3.修改系统变量的值
  语句:

set @@global/session.系统变量名称 = 值; 注意:同上

自定义变量

自定义变量概述

自定义变量就是mysql使用者定义的变量.不是系统定义的.自定义变量又分为局部变量和用户变量.
用户变量的作用域是一次连接.局部变量的作用域仅仅在定义它的begin...end语句块中(该语句块见存储过程和函数那篇随笔).

用户变量

• 1.用户变量的声明
  语句:

set @变量名 = 初始值;

因为”=”在数据库中也作为比较运算符,所以为了避免歧义,还有如下声明方式:

set @变量名 := 初始值;

或

select @用户变量名 := 初始值;

• 2.用户变量的查询
  语句:

select @变量名;

• 3.修改用户变量

set @用户变量名 = 新的值

或

select 字段 into @用户变量名 from 表名;

例1:定义两个用户变量,m = 1, n = 2,并将这两个变量的和保存在 plus的用户变量中;然后查询plus变量的值

set @m = 1;
set @n = 2;
set @plus = @m + @n;
select @plus;

例2:查询emp表中所有记录的条数,并将结果保存在名为emp_counts的变量中

set @emp_counts = null;
select count(*) into @emp_counts from emp;

局部变量

• 1.局部变量的声明
  语句:

declare 局部变量名称 类型;
或
declare 局部变量名称 类型 default 值或表达式;

注意:声明局部变量的语句只能出现在begin...end语句块中.

• 2.修改局部变量的值
  语句:

set 局部变量名称 = 值;