MySql基础笔记

1.mysql入门

1.1 mysql的常见命令

1.查看当前所有的数据库
	show databases;
2.打开指定的库
	use 库名
3.查看当前库的所有表
	show tables;
4.查看其他库的所有表
	show table from 库名
5.创建表
    create table 表名（
        列名   列类型
		...）；
6.查看表结果
	desc 表名；
7.服务器版本
	select version();

1.2 DQL语言(Data Query Language)

1.2.1 基础查询

1.基础语法
	select 查询列表 from 表名；
2.查询表中的单个字段
	select 字段 from 表；
3.查询表中的多个字段
	select 字段1,字段2... from 表；
4.查询所有字段
	select * from 表；
注意：查询之前先选中库
5.查询常量池
	select 100；
6.查询表达式
	select 10*10；
7.查询函数
	select version();
8.起别名(AS或空格)
    select 100*100 AS result;
    select 10*10 AS res1,100*100 AS res2
    select 10*10   res1,100*100   res2
9.去重(distinct)
	select distinct deparement_id from employees;
10.+号的作用
	mysql中+仅能用作运算符
	select '124'+90;会尝试将字符转换为数字
	mysql中拼接用concat()

1.2.2 条件查询

1.基本语法
	select 查询列表 from 表名 where 条件;
	条件语法分类：
	（1）按条件表达式筛选 < > = != <>
	（2）按逻辑表达式  &&  ||  ！
					and or not
	（3）模糊查询 like、 beteen and、 in、is null 
		  通配符 % :任意个字符，包含0个字符
				_ :表示任意一个字符（\_表示转义）
				ESCAPE '$',将$定义为专业字符
		  安全等于 <=>,可以判断NULL
		  IFNULL(a,b):如果a为空，返回b

1.2.3 排序查询

1.基本语法
ORDER By 表名 DESC(ASC) 降序（默认升序）
	BY 可以按函数排序、可以起别名、可以按表达式
2.按多个字段排序
	ORDER BY 字段a DESC(ASC),字段bDESC(ASC),...
3.注意事项
	语法顺序
	SELECT 查询列表 FROM 表 【WHERE 条件】 ORDER BY 表 LIMIT
	LIMIT 表示限制现实几行

1.2.4 常见函数

1.调用函数
	SELECT 函数名(实参列表) from 表
2.分类
	（1）单行函数
		如concat,length,ifnull等
	（2）分组函数
		用于统计、聚合
3.字符函数
	length(str): 返回字符长度
	concat(): 拼接
	upper: 转大写
	lower: 转小写
	substr(str,b,c): 截取b索引后c个字符
	instr(str1,str2): 返回str2在str1中的起始索引，找不到返回0
	trim(a from str): 从str中去掉前后的a，如果只有str一个参数默认去空格
	lpad(str,len,'*'): 用*左填充，使长度为len,如果len小于str长度会截断
	rpad(str,len,a): 用a右填充
	replace(str,str1,str2): 在str中的所有str1替换为str2
4.数学函数
	round(a,b): 四舍五入小数点后b位
	ceil(a): 向上取整
	floor(a): 向下取整
	truncate(a,b): 截断，小数点后保留b位
	mod(a,b): 取余《===》a%b
5.日期函数
	now(): 返回当前日期与时间
	curdate(): 只返回日期
	curtime(): 只返回时间
	year(date): 返回date的年份,同理有month、monthname月份英文、day...
	str_to_date('9-13-2020','%m-%d-&Y'): 字符转日期
	date_format(date,'%y年-%m月-%d日'): 日期转字符
	datediff(a,b): a日期减b日期
6.其他函数
	version(): 版本
	database(): 数据库
	User(): 用户
7.流程控制函数
	(1) IF(条件,a,b): 条件成立返回a，反正b
	(2) 在begin end中if还可以
		if 条件1 then 语句1;
		elseif 条件2 then 语句2;
		...
		end if;
	(3) case 多条件分支
	case
		when 条件1 then 结果1
		when 条件2 then 结果2
		...
		ELSE 结果n
		END
	(4)循环结构
		仅能在begin end中
		分类while、loop、repeat
		循环控制 iterate 类似于 continue
				leave 类似于 break
		while 循环
            [标签:] while 循环条件 do 循环体;
            end while [标签] ;
		loop 循环
            [标签:] loop 循环体;
            end loop [标签] ;
		repeat 循环
			[标签:] repeat  循环体;
            until 结束循环条件 end repeat [标签];
8.分组函数(聚会函数)
	sum: 求和
	avg: 平均折
	max: 最大值
	min: 最小值
	count: 计数
	以上函数忽略null值

1.2.5 分组查询

1.基本语法
	SELECT column,func
	FROM table
	[WHERE condition]
	[GROUP BY columns]
	[ORDER BY columns]
2.筛选分组后的结果
	group by ...
	[having condition]

1.2.6 多表查询(mysql 92)

1.笛卡尔乘积现象
	无连接条件
	select a,b from 表1，表2
2.等值连接
	两边连接
	select a,b from 表1，表2
	where 表1.col = 表2.col
	多表连接
	select a,b from 表1，表2，表3
	where 表1.col = 表2.col
	and 表2.col = 表3.col
	...
3.非等值连接
	select a,b,... from 表1，表2...
	where condition
4.自连接
	同表内的关联数据
	select a.col1,b.col2,... 
	from 表1 as a，表1 as b...
	where a.col = b.col

1.2.7 连接查询(mysql 99)

1.基本语法
	select 查询列表
	from 表1 别名 【连接类型 inner, left, outer】
	join 表2 别名
	on 连接条件
	【where, group by ,order by...】
2.内连接
	select 查询列表
	from 表1 别名 
	inner join 表2 别名
	on 连接条件
3.多表内连接
	select 查询列表
	from 表1 别名 
	inner join 表2 别名 on 连接条件
	inner join 表3 别名 on 连接条件
	...
4.外连接
	select 查询列表
	from 表1 别名 
	left join 表2 别名 on 连接条件
	主表为左表（表1），可换为right
5.全外连接
	select 查询列表
	from 表1 别名 
	full join 表2 别名 on 连接条件
6.交叉连接
	select 查询列表
	from 表1 别名 
	cross join 表2 别名 
	等价于笛卡尔乘积的形式

1.2.8 子查询

出现在其他语句中的select语句都称为子查询
1.基本语法
	select first_name from XX 
	where xx in (
		select xx from xx
		whert xx
		)
	子查询的位置：
		select 后
		from 后
		where、having 后
		exists后
	any与all的使用
        a>any(1,2): 比1、2中任意一个大的a
        a>all(1,2): 比1、2都大的a
	exist(子查询): 子查询有值返回1，没有返回0

1.2.9 分页查询

1.基本语法
	select xx
	from xx
	where xx
	limit offset,size;
	其中，offset: 要显示的条目的起始索引（以0开始）
		 size: 要显示的条目个数

1.2.10 联合查询

将多个查询结果合并为一个结果
1.基本语法
	select xx
	union (all)
	select xx
	...
注意联合查询的列数要一样，查询顺序要对应 ，不加all会自动去重

1.3 DML语言(Data Manipulation Language)

1.3.1 基本内容

插入：insert
修改：update
删除：delete

1.3.2 插入

1.方式1
	insert into 表(列名)
	values(插入值1,...),
		  (插入值2)...
注意事项：
	1.插入的值要兼容
	2.列名要与插入值一一对应
	3.可以省略列名，默认所有列
2.方式2
	insert into 表名
	set 列名 = value,列名=value,...

1.3.3 修改

1.修改单表
	update 表名
	set 列1 = 新值, 列2 = 新值...
2.修改多表
	update 表1 别名
	inner 表2 别名
	on xx
	set 列1 = 新值, 列2 = 新值...
	where xx

1.3.4 删除

1.单表删除
	删除多行
	delete from 表名 where condition
	或者
	truncate table 表
	truncate 不能加条件，表示全部删除
2.多表删除
	delete 表1 ,表2
	from 表1 ,表2
	where xx
	或者
	delete 表1 ,表2
	from 表1
	join 表2 on conditon

1.4 DDL语言(Data Definition Language)

1.4.1 库的管理

1.库的创建
	create database [if not exists]库名;
2.库的修改
	基本不修改，可以修改库默认字符集
    alter database books character set gbk;
	默认字符集为utf-8
3.库的删除
	drop database [if exists] 库名；

1.4.2 表的管理

1.表的创建
	create table  表名(
		列名  列类型(长度 约束),
		列名  列类型(长度 约束),
		....,
		表级约束
		constraint 约束名 约束
		)
2.表的修改
	(1)修改列名
		alter table 表名 change column 原列名 新列名 列类型；
	(2)修改列的类型和约束条件
		alter table 表名 modify column 列名 新类型 约束;
	(3)添加新列
		alter table 表名 add column 新列名 类型;
		ADD还可以添加列级约束
	(4)删除列
		alter table 表名 drop column 列名;
	(5)修改表名
		alter table 表名 rename to 新名;
3.表的删除
	drop table if exists 表名;
4.表的复制
	仅仅复制表的结构
		 create table 新表名 like xx
	完整复制
		create table 新表名
		select * from 原表 
5.常见的数据类型
	数组型
		整型 tinyint,smallint,mediumint,int,bigint
		无符号设定 unsigned
		小数 
			浮点型
				float(M,D),double(M,D)
			定点型(精确度较高)
				dec(M,D)
				decimal(M,D)
			M表示数总位数,D表示小数位数,都可以省略
	字符型
		短文本: 固定长度字符char(M)
			   不定长度字符varchart(M)
			   M表示字符最大长度
			
		长文本：text,blob
	日期型
		date 仅日期
		datetime 日期和时间
		timestamp  时间戳
		time  时间
		year 年份
	枚举
		enum(a,b,c,...) 
	集合
		set(a,b,c,....)
	二进制
		binary,varbinary
6.常见的约束
	(1)非空约束not null
	(2)默认约束default
	(3)主键约束primary key,唯一且不能为空
	(4)唯一约束unique
	(5)检查约束check(mysql不支持)
	(6)外键约束foreign key reference,限制两个表的关系
7.标识列
	create table xx (
		 列名 类型  约束 auto_increment,
	)
	该列会自动增长，默认每次增长1
	show variables like '%auto_increment%' ->显示自增长参数
	set auto_increment_increment = n;  -> 修改默认步长
	标识列必须要和key列搭配，而且一表只能有一个，标识列只能为数值型

1.5 TCL语言(Transaction Control Language)

事务(transaction):一个或一组sql语句组成一个执行单元，要么全部执行要么全部不执行

1.5.1显事务的创建

默认自动提交功能开启，即任意一条语句都会创建事务，
因此创建显示事务时要关闭自动提交功能
SHOW VARIABLES like '%autocommit%'; #查看自动提交是否开启
set autocommit = 0; #关闭自动提交，并开启事务
start transaction; #开启事务（可选）
...
savepoint a; #设置保存点a
commit; #提交事务
rollback; #回滚事务
rollback a; #回滚到a

1.5.2事务并发问题

多个事务同时访问同个数据会出现一些问题
1.查看隔离级别 select @@tx_isolatin;
2.设置隔离级别 set session transaction isolation level XX
3.隔离级别从低到高有: 1.read uncommitted
				   2.read commited
				   3.repeatable read
				   4.serializable
4.设置数据库全局隔离级别 set global transaction isolation level XX

1.5.3视图

将一个查询封装起来，方便下次调用
1.创建方法：
	create view a AS
	select xx from xx ...
2.调用(还可以在其基础上筛选分组等)：
	select * from a (where xx)
3.修改：
	create or replace view 视图名 as
	查询语句; ## 如果有该视图就修改，没有就创建
	或
	alter view 视图名 as
	查询语句;
4.删除：
	drop view 视图名1，视图名2,...
5.查看视图：
	desc 视图；
	show create view 视图1;
6.视图的更新(视图数据更新)：
	简单视图可以增删改
	包含以下关键词的视图无法修改：
        (1)分组函数
        (2)distinct
        (3)groub by
        (4)union
        (5)having
	常量视图、包含子查询的视图、有连接的视图无法更新

1.6 变量

1.6.1系统变量

1.查看
查看所有系统变量 show global(session) variables;
global为全局级别，session为会话级别
查看满足条件的系统变量 show global(session) variables like 'xx';
查看指定系统变量 select @@global(session.)系统变量名;
2.赋值
	set global|session 系统变量名 = 值;
	每次服务器重启系统全局变量会恢复初始值

1.6.2 自定义变量

1.声明并初始化
	(1)  set @用户变量名= 值;
	(2)  set @用户变量名:= 值;
	(3)  select @用户变量名:= 值;
2.赋值
	可以用声明的方式重新赋值也可以
	select 字段 into 变量名
	from 表;
3.使用
	select @用户变量名;
4.局部变量
	仅在begin end中有效，在begin end中第一句话使用
	声明 declare 变量名 类型 (default 值);

1.7 存储过程和函数

1.7.1 存储过程

1.存储过程创建
	create procedure 存储过程名(参数列表)
	begin
		一组sql语句
	end
	注意：
		(1)参数列表包含三部分
		参数模式	参数名    参数类型
		参数模式:
			in:该参数作为输入
			out:该参数作为输出
			inout:该参数可输入可输出
		(2)如果存储过程仅一句话，begin end 可以省略，
		    存储过程中每条sql语句结尾必须加;
		    delimiter $ 可以定义结束符为$  
2.存储过程调用
	call 存储过程名(实参列表)
3.删除存储过程
	drop procedure 存储过程名
4.查看存储过程
	show create procedure 存储过程名
5.函数与存储过程的区别
	函数有且仅有一个返回

1.7.2 函数

1.函数的创建语法
	create function 函数名（参数列表） returns 返回类型
	begin
		函数体
		(return xx)
	end
2.函数的调用
	select 函数名（参数列表）
3.查看函数
	show create function xx ;
4.删除函数
	drop function xx;