MYSQL_01

数据库相关概念

• DB：数据库 (存储数据的文件)
• DBMS：数据库管理系统 (类似mysql这种操作数据库的软件)
• SQL： 结构化查询语言 (和数据库通信的语言)

MYSQL软件

1. mysql服务的启动与停止

   • 启动 net start mysql
   • 停止 net stop mysql

2. mysql服务端的登入与登出

   • 登录 mysql -u用户名 -p[密码]
   • 登出 ctrl+c或输入 exit

3. 操作数据库的常见命令

   //语法不区分大小写。建议关键字大写，表名、列名小写
   //注释  #或-- 单行注释   /*  */多行注释
   mysql --version                 //cmd界面未登陆查看mysql版本
   select version();				//sql编辑器内查看版本
   
   show databases; 				//查看数据库
   create database 数据库名; 		//新建数据库
   use 数据库名;					//选择一个数据库
   
   show tables;					//查看当前库中的所有表
   show tables from 数据库名;		//查看指定库的所有表
   select database();			  //查看当前库
   
   desc 表名;					//查看表结构
   drop table 表名;				//删除表
   

SQL语言

• DQL（Data Query Language）：数据查询语言，用于检索数据库中的数据，主要是SELECT语句；
• DML（Data Manipulation Language)：数据操纵语言，用于改变数据库中的数据，主要包括INSERT、UPDATE和DELETE语句；
• DDL（Data Definition Language)：数据定义语言，用于库和表的创建、修改、删除。主要包括CREATE、DROP、ALTER语句；
• DCL（Data Control Language)：数据控制语言，用于定义用户的访问权限和安全级别。主要包括GRANT和REVOKE语句；
• TCL（Transaction Control Language)：事务控制语言，用于维护数据的一致性，包括COMMIT、ROLLBACK和SAVEPOINT语句。

DQL(查询)

基础查询

select 查询列表 from 表名;

//查询列表可以是：表中的字段、常量值、表达式、函数;*代表所有字段
//查询的结果可以是一个虚拟表格;

/*
1、 别名  as或空格
2、 去重 select distinct 字段名 from 表名;
3、 +号  只能作为运算符
	会把字符型的数字转为数字型，非数字的字符当做0处理。 如果有null值，结果为null


*/

条件查询

select 查询列表 from 表名 where 筛选条件;
/*
筛选条件
1、条件运算符
	> 、 < 、 = 、 >= 、 <= 
	!= 、 <> 不等于
	<=>安全等于 可判断null值
2、逻辑表达式
	&& 、 ||  、 !
	and 、 or 、 not
3、模糊查询
	like  搭配通配符使用  % 任意多个字符 _ 任意单个字符
4、范围条件
	between
	in
5、判断null值
	is null
	is not null

*/

排序查询

order by 
//asc代表的是升序，desc代表降序，不写默认为升序;
//order by子句中可以支持单个字段、多个字段、表达式、函数、别名;
//order by子句一般是放在查询语句的最后面,limit子句除外;

函数

字符函数

length(str); 				// 获取参数的字节数
concat(str1,str2,...);		 //拼接字符串
upper(str);					//变大写
lower(str);					//变小写
substr(str,index);           //截取从索引到末尾的字符串   索引从1开始
instr(str1,str2);        	//返回str2第一次出现的索引，没有返回0
trim(str);					//去掉前后空格
lpad(str1,长度,str2);       //使用str2左填充到指定长度
rpad(str1,长度,str2);    		//右填充
replace(s1,s2,s3);			//用s3替换s1中所有的s2			   

数学函数

round(num);					//四舍五入
ceil(num);					//向上取整
floor(num);					//向下取整
truncate(1.65,1);			//保留1位小数
mod(10,3);					//取余
rand();						//获取0-1之间的随机数

日期函数

now();										//获取当前日期+时间
curdate();									//获取当前日期
curtime();									//获取当前时间
year(date);									//获取年
month(date); mouthname(date);				//获取月
day(date);									//获取日
datediff(date,date);						//获取两个日期相差天数
str_to_date('2020-5-13','%Y-%c-%d');		//字符串通过指定格式转化为日期
date_format(date,'%Y年%m月%d日');			  //日期转化为字符串

其他函数

ifnull(字段或表达式,指定的值);				//如果为null返回指定值，不为null返回原本的值
isnull(字段或表达式);     				  //如果为null
md5(参数);								//获取参数的md5加密形式

流程控制函数

if函数

if(表达式,值1,值2);
//表达式成立，返回值1；不成立，返回值2

case函数

• 用法一(类似 switch case)

case 要判断的变量或表达式
when 常量 then 值或语句
when 常量 then 值或语句
    ...
else 值或语句
end  

• 用法二(类似 if else)

case
when 条件表达式 then 值或语句
when 常量 then 值或语句
    ...
else 值或语句
end  

聚合函数

sum、avg、max、min、count;
//sun和avg 只处理数值型 转换varchar类型为数值型，不能转换的为0;
//max、min 只要能排序的字段就能处理
//如果参数为null，则忽略
//可以和distinct搭配使用，给字段加上distinct 如 count(distinct type)
//conut(*) 效率比count(字段)高，推荐使用

分组查询group by

select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选条件】
【order by 排序列表】;
//group by 子句支持单个字段、多个字段（用逗号隔开，没有先后顺序）、表达式或函数
//如果聚合函数作为条件一定放在having后面
//如果分组前后都可以判断条件，where效率高

连接查询

SQL92标准

select 查询列表 from 表名1,表名2... where 连接条件 and 筛选条件;
//等值连接
//可以三表连接，可以加条件(筛选)、排序、别名、分组
//连接条件至少为表的个数减1
//非等值连接
//自连接(一张表看作为两张表，起不一样的别名)

SQL99标椎join

适用性好，有外连接

• 语法

select 查询列表
from 表1 别名
【连接类型】join 表2 别名 
on 连接条件
 //【连接类型】join 表2 别名 on 连接条件  ---三表连接
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

• 分类

内连接（★）：inner (可省略)
外连接
	左外(★):left 【outer】  //左边为主表，输出所有值
	右外(★)：right 【outer】
	全外：full【outer】
交叉连接：cross //迪卡尔乘积

子查询

/*
嵌套在其他语句内部的select语句
分类
标量子查询：查询结果为一行一列，出现在 select、where或having后
列子查询：查询结果为一列多行，出现在where或having后，和in、any、all搭配
行子查询：一行多列 ，出现在where或having后
表子查询：多行多列，出现在from后
另：相关子查询 exists(参数)  参数可以为各种子查询

*/
tips:
//标量子查询可以给当前表增加没有的字段结果
//行子查询 用的限制比较大
/*例 查询员工编号最小并且工资最高的员工信息
        SELECT * FROM employees
        WHERE (employee_id,salary)=(
            SELECT MIN(employee_id),MAX(salary)
            FROM employees
        );
*/

分页查询limit

select 查询列表
	from 表
	【join type join 表2
	on 连接条件
	where 筛选条件
	group by 分组字段
	having 分组后的筛选
	order by 排序的字段】
	limit 【offset,】size;
//offset要显示条目的起始索引（起始索引从0开始）
//size 要显示的条目个数
//其他数据库软件可能有差别

联合查询union

select 查询列表 from 表1 
union
select 查询列表 from 表2 ;
//查询多个表且查询列表一致
//union 默认去重，union all可以输出重复项

DML语言（表数据操作）

插入insert

• 方式一

insert into 表名(字段名,…) values(值,…);
//字段类型和值类型一致或兼容
//字段个数与值个数一致，一一对应
//字段名可以省略，省略表示所有字段
//可以用null来作为值，没有提及的字段默认赋值null
//支持多行插入 values (值...),(值...),...
//values(值,…) 可以使用子查询代替 select 对应字段...

• 方式二

insert into 表名 set 列名=值,列名=值,…;
//不支持多行和子查询

修改语句

update 表名 set 列=新值,列=新值,… where 筛选条件;

补充：可以进行连表修改和多表修改

• sql92语法：update 表1 别名,表2 别名 set 列=值,… where 连接条件 and 筛选条件;
• sql99语法：update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,… where 筛选条件;

删除语句

delete

1、单表的删除【★】
delete from 表名 where 筛选条件 //筛选条件必须加，否则删全表

2、多表的删除【补充】

sql92语法：
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

sql99语法：
delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

truncate

truncate table 表名;  //清空表

delete和truncate区别

1.delete 可以加where 条件，truncate不能加
2.truncate删除，效率高一点
3.假如要删除的表中有自增长列，
  如果用delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚.

DDL语言（库、表）

库的管理

• 创建

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】 表名;

• 复制表

//只复制结构，不复制数据
create table 表名 like 旧表;
//全复制
create table 表名 
select 查询列表 from 旧表【where 筛选】;

数据类型

整型

//分类
tinyint  smallint  mediumint  int/integer  bigint
    1		2			3		4			  8    字节;
//默认为有符号，设置无符号 类型后加 unsigned
//长度为0填充时的最小显示宽度，搭配zerofill使用 （不建议使用，零填充可以用 lpad（）函数）
//存储的数字的位数与长度无关，只于范围有关。所以整型的长度没有作用  

浮点型

• 浮点数

float(M,D) double(M,D) 
    4          8         字节;
//M代表整数部位+小数部位的个数，D代表小数部位
// m和d可省略，如果写上的话，一定按要求来

• 定点数

decimal(M,D)
// m默认为10，d默认为0
//精度较高

字符型

• char、varchar

			M的意思							 特点		  空间的耗费	   效率
char(M)		最大的字符数，可以省略，默认为1	  固定长度的字符	 比较耗费	  高

varchar(M)	最大的字符数，不可以省略			可变长度的字符	   比较节省		低

• enum

CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);

INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');
INSERT INTO tab_char VALUES('A');

• set

CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');

• binary和varbinary用于保存较短的二进制,blob(较大的二进制)。长文本 text

日期型

• date 只保存日期；

• time 只保存时间；

• year 只保存年；

• datetime 保存日期+时间；

• timestamp 保存日期+时间；占用字节数小，范围小，随时区变化，用的较多

约束

not null //非空 
default //默认
primary key //主键
unique //唯一  （允许多个null）
check  //检查 （mysql不支持）
foreign key //外键 写在列级约束上无效果
// not null  和 default 不能写在表级约束上
//主键和唯一的区别
    
    	保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
主键		  √			×			至多有1个           √，但不推荐
唯一		  √			√			可以有多个          √，但不推荐

    
//建表语法
CREATE TABLE 表名{
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束
};

建表约束

• 添加列级约束

CREATE TABLE stuinfo(
	id INT PRIMARY KEY,//主键
	stuName VARCHAR(20) NOT NULL UNIQUE,//非空 唯一
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),//检查
	seat INT UNIQUE,//唯一
	age INT DEFAULT  18,//默认约束
	majorId INT REFERENCES major(id)#外键
);

• 添加表级约束

//语法：在各个字段的最下面 【constraint 约束名】 约束类型(字段名)
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	
	CONSTRAINT pk PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键	
);

• 通用模板

create table 表名(
字段名 字段类型 not null,#非空
字段名 字段类型 primary key,#主键
字段名 字段类型 unique,#唯一
字段名 字段类型 default 值,#默认
constraint 约束名 foreign key(字段名) references 主表（被引用列）
);

alter操作约束

• 添加

//修改列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

//添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

• 删除主键、外键、唯一

//删除主键
alter table 表名 drop primary key;
//删除唯一
alter table 表名 drop index 索引名;
//删除外键
alter table 表名 drop foreign key 约束名;

自增长（标识列）

auto_increment

1.不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
  auto_increment
  如果要更改起始值：手动插入值
  如果要更改步长：更改系统变量
  set auto_increment_increment=值;
2.一个表至多有一个自增长列
3.自增长列只能支持数值型
4.自增长列必须为一个key--主键、外键、唯一

TCL语言（事务）

• 特性 ACID

  //原子性：一个事务不可再分割，要么都执行要么都不执行
  
  //一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
  
  //隔离性：一个事务的执行不受其他事务的干扰
  
  //持久性：一个事务一旦提交，则会永久的改变数据库的数据.
  

• 了解

  • 隐式（自动）事务：没有明显的开启和结束，本身就是一条事务可以自动提交，比如insert、update、delete
  • 显式事务：事务具有明显的开启和结束的标记；前提：必须先设置自动提交功能为禁用

• 事务操作步骤

//步骤1：开启事务
set autocommit=0;
start transaction;可选的

//步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

//步骤3：结束事务
commit;提交事务
rollback;回滚事务

并发事务

• 多个事务并发出现的问题

  • 脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据；

  • 不可重复读：一个事务多次读取，结果不一样；

  • 幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据。

    一个事务读取了其他事务提交后插入的数据，比起提交前，当前事务两次读取的条数不同。(修改时可见条数不同)。一个事务中，读取的数据应该一致。

• 隔离级别

  可以通过设置隔离级别来解决并发问题

  							脏读			不可重复读		  幻读
  read uncommitted: 读未提交     出现           出现            出现       
  read committed：  读已提交                    出现            出现
  repeatable read： 可重复读                                    出现
  serializable：     串行化                                       
  
  

  Mysql 默认的事务隔离级别为: REPEATABLE READ 可重复读

保存点

#3.演示savepoint 的使用
    
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点

其他

视图

虚拟表，保存sql逻辑以动态的获取真实表里的数据

• 视图操作

//创建视图
create view 视图名 as 查询语句;
//修改视图
//方式一：
create or replace view 视图名 as 查询语句;
//方式二：
alter view 视图名 as 查询语句;

//删除(需要权限)
drop view 视图名,视图名,...;
//查看
desc 视图名;
show create view 视图名;

• 视图一般为只读，修改数据会影响到真实表内的数据

     关键字		  是否占用物理空间		  	  使用
  

  视图 view 占用较小，只保存sql逻辑 一般用于查询
  表 table 保存实际的数据 增删改查

变量

系统变量

系统变量分为全局变量和会话变量

操作全局变量需要加global关键字，会话变量添加session关键字或不加

show global|【session】variables;      //查看所有变量
show global|【session】 variables like '%char%';  //查看符合条件的变量
select @@global|【session】系统变量名;         //查看指定变量
//给系统变量赋值
方式一：
set global|【session】系统变量名=值;
方式二：
set @@global.|【session】系统变量名=值;

自定义变量

• 用户变量（作用域当前连接有效，与会话变量相似）

//声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

//赋值（更新变量的值）
#方式一：
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
#方式二：
	SELECT 字段 INTO @变量名
	FROM 表;
//③使用（查看变量的值）
SELECT @变量名;

• 局部变量

  作用域：只在begin - end中有效，定义放在begin - end 中的第一句话

//声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;


//赋值（更新变量的值）

#方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
#方式二：
	SELECT 字段 INTO 局部变量名
	FROM 表;
//使用（查看变量的值）
SELECT 局部变量名;

• 对比

    		作用域						定义位置					语法
    	用户变量	当前会话				会话的任何地方				加@符号，不用指定类型
    	局部变量	定义它的BEGIN END中 		BEGIN END的第一句话	 一般不用加@,需要指定类型	
  

存储过程和函数

存储过程

• 创建语法

  CREATE PROCEDURE 存储过程名(参数列表)
  BEGIN
  	存储过程体（一组合法的SQL语句）
  END
  
  注意：
  1、参数列表包含三部分
  参数模式  参数名  参数类型
  举例：
  in name varchar(20)
  
  参数模式：
  in：该参数可以作为输入，也就是该参数需要调用方传入值
  out：该参数可以作为输出，也就是该参数可以作为返回值
  inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
  
  2、如果存储过程体仅仅只有一句话，begin end可以省略
  存储过程体中的每条sql语句的结尾要求必须加分号。
  存储过程的结尾可以使用 delimiter 重新设置
  语法：
  delimiter 结束标记
  

• 调用

CALL 存储过程名(实参列表);

举例：
调用in模式的参数：call sp1（‘值’）;
调用out模式的参数：set @name; call sp1(@name);select @name;
调用inout模式的参数：set @name=值; call sp1(@name); select @name;

• 查看

show create procedure 存储过程名;

• 删除

drop procedure 存储过程名;

函数

• 区别：

  • 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新；
  • 函数：有且仅有1 个返回，适合做处理数据后返回一个结果。

• 创建

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
END

/*
注意：
1.参数列表 包含两部分：
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记
*/

DELIMITER $

• 调用

SELECT 函数名(参数列表);

• 查看

show create function 函数名;

• 删除

drop function 函数名;

流程控制结构

分支结构

• if函数

if(条件,值1，值2); //类似三目 可以放在任何位置

• case结构

//类似于switch，一般用于实现等值判断。

case 变量或表达式
when 值1 then 值/语句1;
when 值2 then 值/语句2;
...
else 语句n; //else可省略
end 

//类似于多重if语句，一般用于实现区间判断。
case 
when 条件1 then 值/语句1;
when 条件2 then 值/语句2;
...
else 语句n;//else可省略
end 

可以放在任何位置，begin-end外整体当作值来用

作为独立的语句，只能放在begin-end中；

• if结构

//类似于多重if；只能应用在begin end 中
if 条件1 then 语句1;
elseif 条件2 then 语句2;
....
else 语句n;
end if;

循环结构

只能用在begin-end中

• while

【名称:】while 循环条件 do
		循环体
end while 【名称】;

• loop

//不加循环控制语句就是死循环
【名称：】loop
		循环体
end loop 【名称】;

• repeat

//类似dowhile  先执行一次
【名称:】repeat
		循环体
until 结束条件 
end repeat 【名称】;

• 循环控制语句

  leave 类似于break，用于跳出所在的循环

  iterate 类似于continue，用于结束本次循环，继续下一次

循环控制语句后加循环的名称，所以要使用循环控制语句必须给循环加上名称