mysql

一、MySQL学习笔记

1.服务的启动和停止

方式一 ： 计算机 右键 服务

方式二：通过右键管理员身份运行

net start 服务名 （启动服务）

net stop 服务名 （停止服务）
（或者可使用PHPStuday开启数据库驱动）

2.登录

方式一：使用mysql自带的客户端

方式二：使用windows命令行

通用：mysql -h主机名 -P端口号 -uroot -p密码

本机：mysql -uroot -p密码

3.数据库的好处

①持久化数据（将数据保存起来）

②可以实现结构化查询，方便管理

4.数据库的常见概念

-DB :数据库，存储数据的容器，里面保存了一系列有规则有组织的数据

-DBMS:数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB

-RDBMS:关系型数据库管理系统

-DBA:数据库管理员

-DBS:数据库系统（包含了所有）

-SQL:结构化查询语言，用于和数据库通信的语言，不是某种数据库特有的，而是几乎所有主流数据库通用的语言（有的数据库有自己的方言）

5.常见的数据库

mysql、oracle、db2、sqlserver

6.数据库存储数据的特点

①将数据存放在表中，表再放到库中

②一个数据中可以有多个表，每个表都有自己的名字，用来标识自己、表名不允许重复

③表是由列组成的，我们也称为字段，所有表都是由一个或多个字段的组成的

④表中的数据是按行存储的，每一行代表着表中的一个具体的对象。

注：一列叫字段 一行叫一条记录 一个单元格叫字段值

7.常见的命令

命令语句	说明
show databases;	查看所有的数据库
use 数据库名	打开指定数据库
show tables;	查看当前所用数据库中的所有表
show tables from book;	查看book数据库中的所有表
desc 表名；	查看当前数据库中指定的表的结构
select version();	查看服务版本
mysql version();	在cmd命令中查看服务版本

8.MySQL的语法规范

①不区分大小写，但建议关键字大写、表名、列名小写、多个之间使用_ 如stu_name

②每条命令最好使用分号结尾

③每条命令根据需要，可以进行缩进或换行

④注释（三种）单行注释：#或-- (有一空格)；多行注释:/**/

9.SQL语言的分类

-DQL:(Data Query Language)数据查询语言，用来查询数据的语言，select

-DML:(Data Manipulate Language)数据操作语言，用来管理数据的语言，insert update delete

-DDL:(Data Define Language)数据定义语言，用来定义数据存储的结构的语言，create drop alter

-DCL:(Date Control Language)数据控制语言，用来控制用户、权限等的语言 grent

-TCL:(Transaction Control Language)事务控制语言，用来控制事务的语言，

事务（ Transaction）由一次或者多次基本操作构成，或者说，事务由一条或者多条 SQL 语句构成。

基本操作：一条sql语句

提交commit ，回滚rollback

10. DQL 数据查询语言

1.基础查询

语法：

select 查询列表 form 表名；

理解：打印出内容 特点：查询的列表就是要查的内容，查询的列表可以是：-表中的字段，-常量值，-表达式，-函数。下面以本人数据库hxzy中的stus表为例进行MySQL查询操作示范:

说明	SQL语句
查询某个字段	select 字段名 from stus;
查询多个字段	select 字段名1,字段名2from stus;
查询所有字段	select * from stus;/或者是/ select 字段名1,字段名2,字段名3,字段名4… from stus;
查询常量值	select 999; select “张三”；
查询表达式	select 100%98; select 5 * 100;/使用运算符连接的式子/
查询函数	select now();/此函数显示当前时间/
起别名： 使用as 或空格	select 100%98 as 结果为； select “张三” 姓名为； /若别名中有特殊符号，如冒号之类的，别名用双引号引起来/ select “张三” “姓名为：”;
注意：	起别名的好处——1.便于理解，2. 以后对于查询字段有重名的，可用别名区分
去重	select distinct 字段 from 表名；/使用distinct关键字进行去重/

1. +号在数据库中的使用

2. mysql中的+号只有相加功能。

3. 如果2个操作数都是数值型，那么做加法运算。

4. 如果2个操作数，一个是数值型，一个是字符型，只要一方为字符，那么mysql尝试将字符转为数值型，

   4.1. 若转换成功，做加法运算。
   4.2. 若转换不成功，将字符转为0，再运算。

5. 如果两边操作数都是字符，那么进行0+0运算。

6. null表示空值，’ '表示空字符。

7. 拼接字符串

使用拼接函数concat(),如select concat(‘a’,‘b’,‘c’) as 结果;

运行结果：结果

​ abc

2 条件查询

语法：

select 查询的列表
​            from 查询的表名
​            where  筛选条件；

条件的分类：

1. 按条件表达式进行筛选

用法：列名 运算符 值

例1 中的表达式即为salary > 12000

#例1 查询工资大于12000的员工信息
select * from employees where salary > 12000;
#例2 查询部门编号不等于90的员工名和部门编号
select last_name,department_id 
from employees
where department_id != 90;

2. 按逻辑表达式进行筛选

其中运算符有and or not,用于连接条件表达式

1. and 和:两条件都要为true。
2. or 或：只要有一个为true。
3. not 不是/非：如果连接条件为false，结果为true，否则false。

#例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 not(department_id>=90 and department_id<=110) or salary >1500;

3. 模糊查询

1. like 像…
   一般与通配符搭配使用，

   通配符：
   %——任意多个字符，可含0个字符

   ​ _ ——任意单个字符

   注意：如下方例3 中，因为查询第二字符“_”与通配符一致，会被识别为通配符，这时使用转义，使用“/”。

   #例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 '_/_%' escape '/';
   

2. between and ； between x and y 表示值在x-y之间

   使用between and 可以简化and的写法，其中包含起点，终点临界值，不可调换顺序。

3. in 判断某个字段的值是否是in列表中的一项

4. is null 表示值是空的，与之相反 is not null表示值不为空。

   注意：!= 和<>不能用于判断null值，只有is null和 is not null可以判断

   #例1 between and 用法查询员工编号在100-120之间的员工信息
   select * from employees
   where employee_id between 100 and 120;
   
   #例2 查询员工的工种编号是AD_PRES、 IT_PROG 、FI_ACCOUNT中任意一个的员工信息
   select * from  employees
   where job_id = 'AD_PRES' 
   or job_id = 'IT_PROG' 
   or job_id = 'FI_ACCOUNT';
   --  in 用法查-----------------------------------
   select * from  employees
   where job_id  in ('AD_PRES','IT_PROG','FI_ACCOUNT');
   
   #例2 查询大理 丽江  玉溪的学生的信息
   select * from stu
   where stuAdd in ('大理','丽江','玉溪');
   
   #例4 查询员工编号是110或 109 或 103 的员工姓名和工资
   select last_name,salary from employees
   where employee_id in (110,109,103);
   
   #例5 查询没有奖金的员工名和奖金率
   select last_name,commission_pct 
   from employees
   where commission_pct is null;
   
   #例6 查询有奖金的员工名和奖金率
   select last_name,commission_pct 
   from employees
   where commission_pct is not null;
   
   

总结：= 只能和值搭配不能与null联合使用
is只能和null或not null搭配不能和值联合使用
<=>既可以搭配值又可以搭配null 不推荐使用

3.排序查询

语法：

select 查询列表 
from 表名
[where 筛选条件]
order by 排序字段/表达式 关键字；

其中order by 可以支持一个字段的排序，关键字有：asc,升序，默认，可省略；desc,降序。

常见用法：

用法说明	语法
1.按单个字段排序	select 查询列表 from 表名 order by 排序字段 关键字；
2.添加筛选条件后再排序	select 查询列表 from 表名 where 筛选条件 order by 排序字段 关键字；
3.按表达式排序	例：年薪=工资 * 12 *（1+奖金率） select 查询列表 from 表名 order by salary * 12 * (1+commission_pct) 关键字；
4.使用别名排序	select 查询列表 as 别名 from 表名 order by 别名 关键字；
5.按函数排序	select 查询列表 from 表名 order by 函数 关键字；
6.按多个字段排序	select 查询列表 from 表名 order by 排序字段 关键字，排序字段 关键字； 注意：先进行第一个字段和关键字进行排序，当第一字段值相同时进行第二字段的排序

4. 常用函数

1.字符函数

函数	用法说明
1.length()	用于获取参数值的字节个数
2.concat（）	用于拼接查询的参数值
3.upper()	upper()可将参数值由小写转为大写,lower()可将参数值由大写转为小写
4. substr()	截取，2个参数：在第一个参数中从第二个参数位置截取；3个参数：在第一个参数中从第二个参数位置截取第三个参数的个数。注意：所有数据库数数从1开始，Java中从0开始
5.instr()	第一个参数值是字符串，第二参数是要返回位置的字符串返回子字符串第一次出现的索引位置，找不到返回0
6.trim()	去除空格，只可去除两端字符。例：trim(‘aa’ from ‘aaaaa张商aaaaa’ )
7.lpad(),rpad()	左填充，右填充
8.replace()	替换函数，replace(‘原字符串’,‘被替换字符串’,‘替换字符串’)

2.数学函数

函数	使用说明
①round()	四舍五入函数，（负数四舍五入时，先四舍五入，再添加函数）
②abs()	绝对值函数
③ceil()	向上取整函数，小数位再小也向上取
④floor	向下取正函数，（向左）
⑥truncate()	截断函数，例：truncate(1.9999998,5),指定小数位，直接截断
⑦mod()	模函数

3.日期函数

函数	使用说明
①now()	返回当前系统的日期与时间
②curdate()	返回当前系统的日期
③curtime()	返回当前系统的时间
④year()	获取指定的年份
⑤month()	获取月份
⑥monthname()	获取月份英文名

4.其他函数

函数	使用说明
version()	获取当前系统版本号
database()	获取当前使用的数据库名
user()	获取当前用户名
if（）	if(条件，真值，假值)，若满足条件，为真，否则为假

5. 分组函数

功能：用于统计使用，又叫聚合函数，统计函数，组函数。支持去重操作。

分类：
①sum()，求和函数；
②avg(),求平均值函数；
③max()，求最大值函数；
④min()，求最小值函数；
⑤count()，计算个函数

特点：可忽略null值计算

​ sum(),avg()一般用于处理数值

​ max(),min(),count()可处理任何类型

注意：myISAM存储引擎下，count(*)效率高

​ INNODB存储引擎下，count(*)，count(1)差不多，比count(字段)高一点

5. 分组查询

语法：

select 查询列表
from   表名
[where 筛选条件]
group by 分组字段
having 聚合条件

其中筛选分两类

	针对表	位置	关键字
分组前	原始表	group by前	where
分组后	聚合后的表	group by后	having

6. 连接查询

1. **含义：**又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询。

2. **注意：**在未用连接查询时直接查询两个表中字段，会出现笛卡尔积现象，即表1有m行数据 表2有n行数据，结果=m * n行

​ **发生原因：**没有有效的两个表连接条件

​ **如何避免:**添加有效的连接条件

3. 连接查询的分类，在sql92、sql99标准下，有内连接，外连接两种。

​ 其中内连接又可分为等值连接，非等值连接，自连接

​ 外连接 可分为左外连接，右外连接

4. 在sql92标准下的内连接

   ①等值连接

   语法：

   select 查询列表，查询列表  -- 若查询列表来自不同表，可用 ‘表名（别名）.查询列表’进行区分
   from 表1 别名1,表2 别名2,...
   where 有效的连接条件-- 表1（别名）.连接字段 = 表2（别名）.连接字段
   [and 筛选条件]
   [group by 分组字段]
   [order by 排序字段]
   

   特点:
   a.多表等值连接的结果为多表的交集部分
   ​ b.一般需要为表起别名方便连接的时候使用
   ​ c.对表的顺序没有要求
   ​ d可以搭配前面学习的排序 分组 筛选等一起使用

   例题

   #实现三表相连
   #查询员工名和他对应的部门名和部门所在的城市名
   select last_name,department_name,city
   from employees e,departments d,locations l
   where e.department_id = d.department_id 
   and d.location_id=l.location_id
   and city like 's%'
   order by department_name desc;
   

   ②非等值连接

   例题

    #查询员工的工资和工资等级
    select salary,grade_level
    from employees e,job_grades g
    where salary between g.lowest_sal and g.highest_sal
    and g.grade_level = 'A';
   

   ③自连接

   例题

    #自连接
    #查询员工名和上级领导的名称
    select e.last_name,e.employee_id,m.last_name,m.employee_id
    from employees e,employees m
    where e.manager_id = m.employee_id;
   

5. 在sql99标准下，使用inner join来替换from后面的逗号，使用on来表示连接条件

语法：

 select 查询的列表
 from 表1
 inner join 表2
 on 表1.关系列 = 表2.关系列
 inner join 表3
 on 表2.关系列 = 表3.关系列
 where 筛选条件
 group by 分组列
 having 聚合条件
 order by 排序列；

例题

#在sql99标准下，查询员工名和他对应的部门名和部门所在的城市名
select last_name,department_name,city
from employees e
inner join departments d
on e.department_id = d.department_id 
inner join locations l
on d.location_id=l.location_id
and city like 's%'
order by department_name desc;

6. 外连接

   ​ 与内连接的区别：一个表和一个表做外连接，有一个是主表，一个是从表。主表里面全部字段显示去匹配从表，匹配成功则显示，不成功则显示为null。左外连接 ——左边是主表 ；右外连接 ——右边是主表

   语法：

   #左外连接
   select 查询列表，查询列表，..
   from  主表 别名 left join 从表 别名
   on 连接条件
   [where 筛选条件]
   [group by 分组字段]
   [order by 排序字段]
   
   #右外连接
   select 查询列表，查询列表，..
   from 从表 别名 right join 主表 别名
   on 连接条件
   [where 筛选条件]
   [group by 分组字段]
   [order by 排序字段]
   

7. 子查询

定义：出现在其他语句中的select语句，称为子查询，又叫内查询。外部查询语句叫主查询或外查询。

子查询可出现的位置：select 后，from后，where后，having 后。

例题

#1. 谁的工资比Abel高
SELECT last_name FROM employees
WHERE salary >(
  SELECT salary 
  FROM employees  
  WHERE last_name = 'Abel');
 
#2. 查询最低工资大于50号部门最低工资的部门id和最低工资
SELECT department_id,MIN(salary) 
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
  SELECT MIN(salary) 
  FROM employees 
  WHERE department_id = 50);

#3. 查询location_id是1400或1700的部门的所有员工姓名
select last_name
from employees
where department_id in(
  select department_id
  from departments
  where location_id in (1400,1700));
  
#4. 查询其他部门中比job_id是“IT_PROG”部门所有工资都低的员工名，job_id和工资  
#补充：关键字 all,any in ,其中本题使用到的all关键字，可配合表达式符号 > < <> = + 等使用，all表示所有
#值都为true
###
SELECT last_name,job_id,salary
FROM employees
WHERE salary < ALL(
  SELECT salary 
  FROM employees 
  where job_id = 'IT_PROG');  
#5. 查询每个部门的平均工资和工资等级  
#补充
SELECT avg,grade_level
FROM(
  SELECT AVG( salary) avg, department_id
  FROM employees
  GROUP BY department_id) avg_dep
INNER JOIN job_grades g
ON avg_dep.avg between lowest_sal AND highest_sal
ORDER BY grade_level;  

8. 分页查询

应用场景：当要显示的数据在一页中显示不全，需要分页提交sql请求

语法：

select 查询列表
from 表1
[xxx join 表2]
on 连接条件
where 筛选条件
group by 分组字段
having 聚合筛选条件
order by 排序字段
limit [offset],size; -- 分页提交，[offset]表示开始条数，从0开始，size表示页面大小，
                     -- 有公式：begin=(page-1)*10

例题

#查询有奖金的员工信息，并且列出较高的前10位
select *
from employees
order by salary desc
limit 0,10;

联合查询： union,联合合并，将多条查询语句的结果合并成员工结果显示

语法：

select 查询语句1
union
select 查询语句2
union
select 查询语句3

union ——不包含多个重复语句的值（去重）

union all ——包含多个重复语句的值

应用场景：当要查询的结果来自于多个表，并且表之间没有连接关系，但要查询的信息基本一致。

要求：多条查询语句的查询列表列是要一致，每一列的顺序基本兼容。

11. DDL 数据定义语言

表和库的管理都涉及3个关键字：

create 创建，alter 修改，drop 删除

1. 库的管理

1. 创建数据库，语法——create database [if not exists] 库名；
2. 修改数据库（早期可修改，后期不行了。）语法——rename database 旧库名 to 新库名；
3. 删除数据库，语法——drop database [is exists] 库名

2.表的管理

1. 表的创建，语法如下：

   create table 库名(
       列名1 数据类型(长度) 约束，
       列名2  数据类型(长度) 约束，
       列名3  数据类型(长度) 约束，
       列名4  数据类型(长度) 约束，
       ......
   );
   

2. 表的修改，语法如下：

   alter table 表名  add|drop|modify|change column 列名[类型 约束];
   	-- change 修改列名
   	-- modify 修改约束 数据类型
   	-- add  添加
   	-- drop 删除
   #一些具体使用
   -- ①修改列名  将publishdate修改为pubDate
   alter table book change column publishdate pubDate datetime;
   
   -- ②修改列的类型或约束
   alter table book modify column pubdate timestamp; 
   
   -- ③添加列
   alter table author add column age int;
   desc authors;
   
   -- ④删除列
   alter table author drop column age;
   
   -- ⑤ 修改表名
   alter table author rename to authors;
   

12. 常见的数据类型

​ 几乎所有的编程语言中，都会涉及到数据的存储，数据类型其实就是确定存储数据的类型，通常来讲可确定数据的大小和类型。

​ 在mysql中常见的有数值型、字符型和日期型三种。

1. 数值型

1.1. 整数型

整型数据有

数据类型	字节长度
tinyint	1个字节（8位）
smallint	2个字节（16位）
mediumint	3个字节（24位）
int/integer	4个字节（32位）
bigint	8个字节（64位）

特点：

1. 若数据类型没有设置有无符号，默认为有符号，符号占1位
2. 科学使用关键字unsigned 来设置数据类型无符号
3. 如果不设置数据长度，会有默认的长度

注意：

​ 长度代表可以显示的最大宽度，如果说输入数据长度不够会自动用0填充，但是必须使用zerofill关键字才 会显示出填充的0

1.2. 小数

小数分为浮点数和定点数，

1. 浮点数 ：有float（M,D）,double(M,D)

2. 定点数：有dec(M,D)，decimal(M,D)

3. 其中 M：表示整数位+小数位

   ​ D：表示小数所占位数

   在使用小数类型时，一般不设置长度，由值决定，M和D值在设置时可省略，其中decimal的M值默认为10，D值默认为0，float和double根据输入的值进行精度确认。

   注意：所选择的数据类型只要能保存数据，越小越好，越简单越好。

2.字符型

比较短的文本可用char ,varchar；较长文本可使用text，blob；其他还有binary，varbinary,用于保存较短的二进制；enum用于枚举，set用于保存集合。

字符的长度表示存储了多少个字。

2.1.char

表示固定长度字符，最大字符数可以省略，默认值时1，有耗费空间的特点

2.2. varchar

表示可变长度字符，最大字符数不可以省略，其特点说较节省空间。

2.3. enum

枚举，只能在列中插入指定的值

2.4. set

集合，值只能从集合中随意排列

3.时间型

3.1. datetime,保存日期+时间，8字节，范围是：1000-9999，不受时区影响

3.2. timestamp,时间戳，保存日期和时间，4个字节，范围是：1970-2038，受时区的影响

3. date,只保存日期
4. time,只保存时间
5. year,只保存年

13.标识列

又称：自增长列，可以不用手动的插入值，系统默认提供有序值。

特点：

1. 标识列必须和主键搭配吗？——不一定，但要求字符类型是数字。

2. 一个表中可以有几个标识列——最多1个。

3. 自增长关键字——auto_increment

4. 如果想从某个起始值开始，手动输入起始值

5. 主键：关键字primary key。

14. 约束

含义：一种限制，用于限制表中数据，为了保证数据的正确性和准确性，称为完整性约束。

约束的分类：六大约束

1. not null 非空约束，为了保证字段的值不能为空，比如：学号，姓名

2. default 默认约束，用于保证该字段没有插入值时的默认值，比如：性别

3. primary key 主键约束，用于保证字段的值具有唯一性，并且非空。

4. unique 唯一约束，用于保证该字段的值具有唯一性，比如工号，座位号，身份证号

5. check 检查约束，约束字段的值在某个区间只能选择某值，mysql不支持。

6. foreign key 外键约束，

   -什么是 外键：一个表的主键字段被放到另外一个表中做为一个字段

   -用于限制两个表的关系，保证该字段的值必须来自主表的关联列 的值

   -在从表中添加外键约束用于引用出表中的某列的值。

如何添加约束：

1. 添加约束-在创建表时

比较传统简单

操作例子，创建学生信息表，其中有外键gradeid：

create table stuinfo(
  id int peimary key auto_increment comment'学号'，-- 主键
  stuName varchaer(20) not null comment'姓名'
  gender char(1) check(gender = '男' or gender = '女')，
  age int default 18,-- 默认约束
  seat int unique， -- 唯一约束
  gradeId int,-- 外键列
   #外键约束的语法 
  constraint fk_stu_grade foreign key(gradeId) 
  references geade(gradeId )-- fk后是两表的简写
);

#通用的语法
drop table if exists 表名；
create table 表名(
  列1 数据类型 primary key auto_increment comment'注释'，
  列2 数据类型 not null ,
  列3 数据类型 unique，
  列4 数据类型 default 值，
  外键列5 数据类型，
  constraint f_从表_主表 foreign key（外键列5）references 主表(被引用列)
  ) comment'表名';

2.添加约束-在修改表时

​ 2.1. 添加非空约束

alter table stuinfo modify column stuName varchar(20) not null;

​ 2.2. 添加默认约束

alter table stuinfo modify age int default 18;

​ 2.3. 添加主键约束

alter table stuinfo modify column id it primary key ;

​ 2.4.添加唯一约束

alter table stuinfo modify column sear int unique;
alter table stuinfo add unique(seat);

​ 2.5.添加外键约束

#5.添加外键
    alter table stuinfo add constraint fk_stu_grade foreign key(gradeId)
references grade(gradeId);

3. 删除约束

3.1 删除非空约束，去掉not

alter table stuinfo modify column stuName varchar(20)  null;

3.2 删除默认约束,去掉默认值

alter table stuinfo modify age int;

3.3 删除主键，将modify改为drop

alter table stuinfo drop primary key;

3.4 删除唯一约束，同上

alter table stuinfo drop index seat;

3.5 删除外键约束，使用drop，与外键名删除。

alter talbe stuinfo drop foreign key 外键名;
alter talbe stuinfo drop foreign key fk_stu_grade;

3.6 查看当前表的索引，show index from 表名，#主键也是索引，键就是索引——主键，外键，唯一键

show index from stuinfo;

15.DML 数据操作语言

数据的增、删、改

1. insert，插入

1. 经典插入语法：insert into 表名（列名1，列名2，…） values(值1，值2，…);

2. 可省略列名，默认表示所有的列而且顺序与表结构一致。insert into 表名 values(值1，值2，…);

   注：可以为null的值，要值直接写值，必要写null

3. 可为某几个列插入值，insert into 表名（指定列1，指定列2 ，…）values(值1，值2，…);

4. 列的顺序是否可以调换——未省略列名时，可以调换，省略列名时，不可调换

5. 如何插入多行——values后用逗号隔开多个插入值括号，或者执行多条插入语句。

2. update，修改

1. 语法：update 表名 set 列名 = 更新值，列名2=更新值 where 更新条件；

3. delete ，删除

1. 方式一：delete from 表名 where 条件；

   ​ delete from 表名；-- 删除了整个表的数据，出现插入id接着以前数据。

2. 方式二：只能删除全部——truncate table 表名；重新插入，id从1开始

3. delete可以添加where条件，truncate不可以

   假如要删除自增长列

   -delete删除后再插入数据会继续从标识列增长

   -truncate删除后标识列从1 开始

4. truncate删除后没有返回值（OK），delete删除后返回受影响的行数

5. truncate删除后不能回滚。delete删除后可以回滚。

16. TCL-事务控制语言

1. 事务定义

​ 一个或一组sql语句看成一个执行单元，此单元要么全部执行，要么都不执行

经典面试题——事务的特性：（ACID），即

atomicity 原子性,最小单元，一个事务不可再分，要么执行，要么不执行。

Consistency，一致性，一个事务执行会使数据从一个一致状态切换到另外一个一致状态。

Isolation，隔离性，一个事务的执行不受其他事务的干扰。

Durability，持久性，一个事务一旦提交，则会永久改变数据库的数据。

2. 事务的创建

1. 隐式事务——事务并没有明显的开启或结束的标记，如：insert,update,delete语句

2. 显式事务——事务有明显的开启或结束的标记。

   开启前提：必须先设置自动提交功能为禁用——set autocommit = 0;

3. 手动提交事务的步骤：

   ①开启事务

   set autocommit = 0;

   start transaction ;

   ②编写事务中的sql语句（insert,update,delete语句）

   ③结束事务

   commit;提交事务

   rollback;-- 回滚事务,

3. 案列——张三-李四转账

1. 建表

   drop  table if exists account;
   create table account(
   	id int primary key auto_increment,
   	username varchar(20) not null,
   	balance float not null
   );
   insert into account 
   values(null,'张三',1000),
   (null,'赵李四',1000);
   select * from account;
   

2. 开始事务

   #开启事务
   set autocommit=0;
   start transaction;
   

3. #编写事务的一组sql语句

   
   update account set balance=500 where username='张三'; 
   update account set balance=1500 where username='李四'; 
   

4. #结束事务

   #commit;-- 提交事务
   
   rollback;-- 回滚事务,
   

5. 保存点savepiont的使用

17. 视图

1. 含义：虚拟的表，和普通表一样使用。

mysql5.1出现的特性，通过原有的表来动态的生成数据

2. 视图与表的区别

	创建	实际保存的物理空间	使用
视图	create view	只保存了sql逻辑	查询，通常不做增删改
表	create table	保存了数据	增删改查

3. 创建视图

   语法：

   create view 视图名
   as
   查询语句；
   

4. 使用视图

5. 视图的修改

   ①方式一:

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

   ②方式二：

   alter view 视图名 as 查询语句；

6. 删除视图

   语法： drop view 视图名，视图名；

7. 查看视图

   ①查看视图结构信息 （表）

   desc 视图名；

   ②查看视图信息

   show create view 视图名；

18. 变量

计算内存中的一块存储区域。

在常见的编程语言中，变量就是内存中开辟的一小块存储空间，叫变量名

数据类型用来决定变量的大小和存储的数据类型

在数据库当中，变量其实也是存储了一些数据，只不过数据库中的变量比较多样。

1. 系统变量：

说明：系统变量是由系统定义的，不是用户定义的，属于服务器层面的，又分为全局变量，会话变量

注意：全局变量在使用过程中需要添加关键字global,会话变量添加关键字session，如果什么都没写，默认时会话变量。

1. 查看系统变量

   show variables;

   show global variables;#查看全局变量；

   show session variables；#查看会话变量

2. 查看条件的部分系统变量

   show global variables like ‘%char%’;

3. 查看指定的系统变量的值

   select @@global.autocommit;#查看全局变量的值

   select @@session.autocommit;#查看会话变量的值

4. 全局变量与会话变量的区别

   全局变量针对的是当前所有会话（连接有效） 重启无效

   会话变量 针对的是

2.自定义变量：

说明：自定义变量是由用户自定义的而不是系统提供的。

步骤： 1. 声明变量——>2. 赋值——>3. 使用（查看、比较、运算等）

1. 用户变量：针对当前会话有用，作用域等同于系统的会话变量

   ①声明变量并初始化

   方式一：

   set @变量名 = 值；# ‘=’，‘：=’是赋值运算符

   set @变量名：=值；

   select @变量名：=值；

   方式二：

   select 字段 @变量名 from 变量名；

   ②赋值（更新变量的值）

   ③如何使用变量

   select @变量名；

   #用户变量的使用

   set @m=1;

   set @n=1;

   set @sum=@m+@n;

   select @sum;

2. 局部变量：仅仅在定义它的begin end 块中有效，应用在begin end 中的第一句话。

   ①局部变量的声明

   declare 变量名 类型；

   declare 变量名 类型 [default 值]；

   ②给变量赋值

   方式一：

   set 局部变量名 = 值；

   set 局部变量名 ：= 值‘

   select 局部变量名 ：= 值；

   方式二：

   select 字段 into 局部变量名 from 表名；

   ③使用（打印）

   select 局部变量名；

3. 用户变量和局部变量的对比

   	作用域	位置	语法
   用户变量	当前会话	会话中的任何位置	@变量名
   局部变量	定义的begin end 中	begin end 中的第一句话	变量名

19. 存储过程

一组预先编译好的sql语句，需要时直接执行得到想要的结果，可理解为批处理语句。

理解：存储过程和函数都是先写好一些功能，用的时候传入需要的条件就能活动想要的结果。

类似于java中的方法

1. 创建存储过程 的语法

   create procedure 存储过程名(参数列表)

   begin

   存储过程体，是一组合法 的sql语句

   end

注意事项：

参数部分包含三部分——参数模式，参数名，参数类型

举例： instuname varchar(20)

①参数模式

1. in:该参数可以作为输入，也就是该参数需要调用人传入值

2. out :该参数可以作为输出，也就是可作为返回值

3. inout :该参数既可以作为输入，也可以作为输出，也就是该参数可以是传入值，也可以是返回值

调用存储过程

call 存储过程名

1.in 模式的例子：

多个in用逗号隔开

delimiter $

create procedure myp2(in beautyName varchar(20))

begin

select bo.* from boys bo

right join beauty b

on bo.id = b.boyfriend_id

where b.name = beautyName

end$

call myp2(‘赵敏’);#调用

例3：创建存储过程实现是否登录成功

use girls;
delimiter $
create procedure myp3(in loname varchar(20),in lopass VARCHAR(20))
begin
  declare result int default 0; #声明初始化
  SELECT COUNT(*) into result#赋值
  FROM admin a
  WHERE  a.username = loname and a.password = lopass;
  SELECT if(result >0 ,'登录成功','登录失败') as 结果;
end $

CALL myp3('zs01','1111');

3.out 模式

例子：

#3.根据输入女神的名字，返回对应的男生名和魅力值

delimiter $
create procedure myp4(in beautyName varchar(20),out boyname varchar(20),out usercp int)
begin
 select bo.boyName,bo.userCP into boyname,usercp
 from boys bo
 right join beauty b
 on bo.id = b.boyfriend_id
 where b.`name`=beautyName;
end $

#调用
call myp4('小昭',@namees,@cp)
select @namees,@cp

4.inout 模式

例子：

#4.出入两个值a和b  最终将a和b翻两倍返回
delimiter $
create procedure myp5(inout a int,inout b int)
begin
	#计算翻两倍
	set a=a*2;
	set b=b*2;
end $

#调用
set @m=2;
set @n=5;
call myp5(@m,@n);
select @m,@n;

5.存储过程的删除

删除存储过程时，不可以进行多删除

drop procedure 存储过程名 ；

6. 存储过程的查看

show create procedure 存储过程名；

20. 函数

1. 函数的创建

create function函数名（参数名 类型）

return 返回值类型

begin

end

21. 流程控制语句

顺序结构：代码从上往下执行

分支结构：代码由选择的走有条路

循环语句：代码有条件的重复执行，

1. if函数

语法：

if(条件，条件成立的值，条件不成立的值)
功能：实现如果满足条件后不满足条件得到某值
应用在：bengin end 外面或里面

2. case结构

当值等于XXX时执行某个操作，类似于switch case

情况一：判断变量或表达式执行不同语句

case 变量或表达式

when 值1 then 语句1；

when 值2 then 语句2；

22. 循环

1. while

标签 while 循环条件 do

​ 循环体

end while 标签；

#批量插入admin表，如果此时》20则停止

delimiter $
ceate procedure test_while( in insertCount int)
begin 
  declare i int default 1;
  a:while i < insertCount do insert into admin(username, `password`)
   values(concat('小王',i)，‘0000’)；
  if  i >=20 then  leave a;
  end if;
  set i=i+1;
  end while a;
end $

2. loop循环

loop
[标签：] loop
   循环体
end loop 标签；

二. 数据库设计

1.软件研发步骤

需求分析：产品经理（比较牛的存在，负责人）- 设计（架构师，包含数据库、接口、过程设计）- 编码（开发工程师）- 测试（测试工程师）- 安装部署（运维，实施）

（产品原型——>软件架构设计——>

2.数据库设计:

根据需求或业务系统对实际数据库进行完整的实际，选型表的结构 表和表之间的关系，字段

3.为什么需要设计数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RRTPxkYO-1675650309886)(E:\春哥成长日记\md笔记\img\1660789682500.png)]

4.数据库设计步骤

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LaTsk9Kd-1675650309888)(E:\网页设计\md笔记\img\1660789746884-1660789851906.png)]

①发现实体，通过于相关人员的交流探讨，发现需要制作系统的相关存储对此，称为实体

②发现实体的属性，

③发现实体与实体间的关系（每个存储对象之间是什么关系，方便后续处理主外键）

④画e-r图，实体关系图

⑤根据e-r图绘制关系模式图

5.为什么使用e-r图

矩形，椭圆，关系

6.数据模型图（关系模型）

7.数据库模型图

8.范式

①第一范式：原子性，每个列都是不可再分的单元

②第二范式：要求每个表只描述一件事情（每个表的列必须与主键直接相关）

③第三范式：如果一个关系满足2NF，并且除了主键以外的其他列不传递依赖与主键列不相关（传递依赖）

9.完成前7步设计后，重新根据三大范式检测数据库设计，重新绘制e-r图，模型图，建库建表

结构：代码从上往下执行

分支结构：代码由选择的走有条路

循环语句：代码有条件的重复执行，

1. if函数

语法：

if(条件，条件成立的值，条件不成立的值)
功能：实现如果满足条件后不满足条件得到某值
应用在：bengin end 外面或里面

2. case结构

当值等于XXX时执行某个操作，类似于switch case

情况一：判断变量或表达式执行不同语句

case 变量或表达式

when 值1 then 语句1；

when 值2 then 语句2；

22. 循环

1. while

标签 while 循环条件 do

​ 循环体

end while 标签；

#批量插入admin表，如果此时》20则停止

delimiter $
ceate procedure test_while( in insertCount int)
begin 
  declare i int default 1;
  a:while i < insertCount do insert into admin(username, `password`)
   values(concat('小王',i)，‘0000’)；
  if  i >=20 then  leave a;
  end if;
  set i=i+1;
  end while a;
end $

2. loop循环

loop
[标签：] loop
   循环体
end loop 标签；

二. 数据库设计

1.软件研发步骤

需求分析：产品经理（比较牛的存在，负责人）- 设计（架构师，包含数据库、接口、过程设计）- 编码（开发工程师）- 测试（测试工程师）- 安装部署（运维，实施）

（产品原型——>软件架构设计——>

2.数据库设计:

根据需求或业务系统对实际数据库进行完整的实际，选型表的结构 表和表之间的关系，字段

3.为什么需要设计数据库

[外链图片转存中…(img-RRTPxkYO-1675650309886)]

4.数据库设计步骤

[外链图片转存中…(img-LaTsk9Kd-1675650309888)]

①发现实体，通过于相关人员的交流探讨，发现需要制作系统的相关存储对此，称为实体

②发现实体的属性，

③发现实体与实体间的关系（每个存储对象之间是什么关系，方便后续处理主外键）

④画e-r图，实体关系图

⑤根据e-r图绘制关系模式图

5.为什么使用e-r图

矩形，椭圆，关系

6.数据模型图（关系模型）

7.数据库模型图

8.范式

①第一范式：原子性，每个列都是不可再分的单元

②第二范式：要求每个表只描述一件事情（每个表的列必须与主键直接相关）

③第三范式：如果一个关系满足2NF，并且除了主键以外的其他列不传递依赖与主键列不相关（传递依赖）

9.完成前7步设计后，重新根据三大范式检测数据库设计，重新绘制e-r图，模型图，建库建表