mysql学习笔记-基础篇

1 mysql登录

直接登录

直接使用mysql自动的方式登录，但是智能登录root用户。

命令行登录

1. 将“C:\Program Files\MySQL\MySQL Server 5.7\bin”放置在系统变量中path下的第一个，否则dos窗口无法识别mysql命令。
2. 命令行输入 msql -h [主机地址] -P [端口号] -u [用户名] -p回车后提示输入密码。

退出

属于exit命令即可

2 基础学习

2.1 基础查询

select [查询列表] from [表名]
查询列表可以是表中的字段，常量值，表达式，函数。查询结果是一个虚拟的表格。

select *from [表名] 查询表中所有数据

select 10*10; 查询表达式

select version() 查询函数

select [字段名] as [别名] from [表名]  为查询字段起别名

select distinct [字段] from[表名] 去重

select concat([字段名1],[字段名2],,) from [表名] 字段合并

2.2 条件查询

select [查询列表] from [表名] where [删选条件]
按条件表达式筛选： > < = != <> >= <= 
按逻辑表达式筛选： && || !  (and or not)
模糊查询 like， between ，in ， is null

2.3 模糊查询

select [字段名] from [表名] where [字段名] like '关键字'
# like 通常与通配符结合使用
# %： 任意多个字符
# _: 一个字符
# 使用\ 作为转义符， 或者使用escape进行转义
select last_name from students where last_name like '_$_%' escape '$' #此时$被当作转义符

select [字段名] from [表名] where [字段名] between [阈值1] and [阈值2]
#包含阈值，阈值前后位置对结果有影响

select [字段名] from [表名] where [字段名] in (值1，值2，，，)
#列表中值必须同类型或者等价，且不支持通配符。

select [字段名] from [表名] where [字段名] is null(is not null)
#不可以使用‘=’判断是否为null,可以用安全等于 <=>

2.4 排序查询

select [字段名] from [表名] order by [字段名1] [asc|desc] ，[字段名2] [asc|desc] ，，，，，
#asc 是升序，desc 是降序。先排字段名1再排字段名2

SELECT [表达式]  AS [别名] FROM [表名] ORDER BY [别名] DESC

2.5 分组查询

select 
	[分组函数]，列(要求出现再group by后面)
from 
	[表] 
where [筛选条件]
group by [分组列表] 
order by [条件]

2.6 分页查询

select 查询列表-----------------7
from 表------------------------1
[type join 表  ----------------2
on 连接条件---------------------3
where 筛选条件------------------4
group by 分组字段---------------5
having 分组后筛选----------------6
order by 排序的字段]-------------8
limit offset,size--------------9
#offset 要显示起始条目的起始索引（起始从0开始）
#size 要显示的条目个数
# 要显示第page 页，每页条目数是size
select * from xxx limit (page-1)*size,size

2.7 联合查询

查询语句1
union [all 不去重]
查询语句2
，，，，
#要求多条语句的查询列数是一致的
#要求多条查询语句的查询的每一列的类型和顺论最好一致

3 常见函数

select [函数体(参数)] from [表]

3.1 单行函数

字符函数

LENGTH(str) # 字节长度

CONCAT(str1,str2,...); #字符拼接

UPPER(str) #转换大小写
LOWER(str)

SUBSTR(str,pos) #截取pos 后所有字符
SUBSTR(str,pos,len) #截取pos后len个字符
#索引从1开始

INSTR(str,substr) #返回字串的第一个索引，没有则返回0

TRIM([remstr FROM] str)# 去除前后remstr字符

LPAD(str,len,padstr) #用指定字符进行左填充到指定长度，原字符过长则截取。
RPAD(str,len,padstr) #用指定字符进行右填充到指定长度，原字符过长则截取。

REPLACE(str,from_str,to_str) #字符替换

数学函数

ROUND(X,D)  #四舍五入

CEIL(X) #向上取整

FLOOR(X) #向下取整

TRUNCATE(X,D) #截断，保留小数点后面D位 X-X/D*D

日期函数

NOW() #返回系统日期+时间

CURDATE() #返回当前日期

CURTIME() #返回当前时间

STR_TO_DATE(str,format) #将字符转为日期

DATE_FORMAT(date,format) #日期转为字符

流程控制函数

IF(expr1,expr2,expr3) #expr1为判断条件，expr2为真返回值，expr3为假返回值


/**
相当于
switch(){
	case xx:  {}
	case xx:  {}
	,,,,,
}
*/
case [要判断的字符或者表达式]
when [常量1] then [要显示的值或者表达式]
when [常量1] then [要显示的值或者表达式]
when [常量1] then [要显示的值或者表达式]
......
else [要显示的值或者表达式]
end
#如果作为表达式，嵌套在其他语句中使用，可以放在任何部分。
#如果作为独立的语句使用，只能放在begin end中

/**
相当于
if(){
}else if(){

}else{
}
*/
case 
when [常量1] then [要显示的值或者表达式]
when [常量1] then [要显示的值或者表达式]
when [常量1] then [要显示的值或者表达式]
......
else [要显示的值或者表达式]
end

3.2 分组函数

sum() #求和

avg() #求平均

max() #求最大值

min() #求最小值

count() #计算个数

#可以和distinct 搭配使用，计算去重。
sum(distinct,[])

count(*) #统计行号
count(1)

4 连接查询

4.1 内连接

等值连接

select [列明] from [表名1 as 别名，表名2] where [别名.列=表名2.列]
#为了简化语句，可以为表起别名。但是起过别名后，就不能再使用原来的表名。

非等值连接

select [列明] from [表名1 as 别名，表名1] where [BETWEEN xxx AND xx]

自连接

#将一张表当成两张表或者多张表，连接的等值条件就是本表中
select [列明] from [表名1 as 别名，表名1] where [别名.列=表名1.列]

4.2 sql99

语法

select 查询列表
from 表1 别名 连接类型
join 表2 别名
on 连接条件
[where 筛选条件
group by xxx
 having xxx
 order by xxx]
 # 内连接 inner 
 #外连接 左外：left [outer] 右外 right[outer] 全外 full[outer]
 #交叉 closs

外连接

#用于查询一个表中，而另一个表中没有。
#外连接分主从表，主表的信息都会显示出来。故外连接分为两部分，一部分为相交的，另一部分为主表有而从表无的部分
#左外连接 left join 左边是主表
#右外连接 right join 右边是主表


5 子查询

出现在其他语句中的select语句成为子查询

分类

select 后面：仅仅支持标量子查询

from 后面：支持表子查询

where 或者 having 后面： 支持标量子查询，列子查询，行子查询

exists 后面: 表子查询.

特点

• 子查询一般放在小括号内
• 子查询一般放在条件的右侧
• 标量子查询，一般搭配着单行操作符使用:< ,>,<>,=
• 列子查询一般搭配着多行操作符使用： in,any/some ,all
• 子查询的执行优先于主查询

5.1 标量子查询

select xxx
from xxx
where xxx>(select bb 
          from bbb
          where bbb)
          
#括号中的子查询结果是一个值

5.2 列子查询1

select xxx
from xxx
where xxx in (select bb 
          	  from bbb
          	  where bbb)
#括号中的子查询为一组结果

5.3 select 后

#子查询被当作一个查询字段
SELECT xxx,(SELECT COUNT(*)
			FROM dd 
			WHERE dd) dd
FROM xxx

5.4 放在from 后

#子查询被当作一个表
select xxx
from (select xxx from xxx )

5.5 放在exists后

select exists(select xxx from xxx)
#判断子查询结果是否存在，存在：1 不存在：0

6 增删改

6.1 插入语句

insert into 表名（列名，，，，，） values(值，，，，，)
/**
1. 插入的值的类型与列的类型一致
2. 不可以为null的值必须有值
3. 列的顺序可以调换
4. 列的个数和值的个数必须匹配
5. 省略列名时，默认为所有列，且列的顺序与表的顺序一致
6. 支持多行插入
7. 支持子查询语句
*/


insert into 表明
set 列名=值，列名=值，，，，，,

6.2 修改语句

#修改单表记录
update 表名 set 列=值，列=值，，， where xxxx

update 表 
inner|left|right join 表
on xxx
set 列=值 ，，，

6.3 删除语句

delete from 表明 where xxx

delete 表
from 表1 别名
inner|left|right join 表2 别名
where xxxx
#要删除哪个表中的数据，delete后就跟随其表名

truncate table 表名 
#删除整个表的数据

/**
假如要删除的表中有自增长列，用deletes删除后，再插入数据，自增长的值从断点开始，而truncate删除后，自增列从1开始

truncate 没有返回值，而delete有返回值
truncate 删除后没有回滚，而delete删除后可以回滚
*/

7库和表的管理

7.1 库的管理

#新建库
create database 库名

#通用写法
drop database if exists 库名
create database 库名

#修改字符集
alter database 库名 character set 字符集

#库的删除
drop database book

7.2 表管理

#新建表
creat table 表名（
 列名 类型 [长度，约束]，
 ，，，，，
）

#通用写法
drop table if exists 表名
create table 表名（，，，，）


#修改列名
alter table 表名 change column 旧名 新名

#修改类的类型
alter table 表名 modify column 列名 

#增加新列
alter table 表名 add column 列名 double

#删除列
alter table 表名 drop column 列名

#修改表名
alter table 表名 rename to 新表名

#复制表
create table 表名 like 表名 #仅仅复制表的结构
create table 表名 select * from 表 #复制表的结构和数据
create table 表名 select xxx from 表 where xxx #复制其中其中一部分

7.3 常见约束

一种限制，用于限制表中的数据，为了确保表中数据的准确性和可靠性

NOT NULL

非空约束，用于保证该字段不能为空

DEFAULT

默认约束，用于保证该字段有默认值

PRIMARY KEY

主键约束，用于保证该字段值具有唯一性，并且非空

UNIQUE

唯一，用于保证该字段唯一

CHECK (mysql 不支持)

检查限制

FOREIGN KEY

外键：用于限制两个表直接的关系

auto_increment

标识列，系统提供默认自增长序列值

• 标识列必须与key搭配
• 一个表最多只有一个标识列
• 标识列必须是数值型

8 事务控制

一个或者一组sql语句组成一个执行单元，这个执行单元要么全部执行要么全部不执行。如果单元中某条sql语句一旦执行失败或者产生错误，整个单元将会回滚。

8.1 事务的属性

原子性

指事物是一个不可再分的工作单位

一致性

事务必须使数据库从一个一致性状态变到另一个一致性状态

隔离性

指十五的执行不能被其他事务干扰，并发执行的各个事务直接不能相互干扰。

持久性

指事务一旦提交，它对数据库的数据改变是永久的。

8.2 事务使用

隐式事务

事务没有明显的开启和结束的标记。如delete，update，insert。

显示事务

事务具有明显的开启和结束标记。

#前提：必须设置自动提交功能禁用,只针对当前的会话有效。
set3 autocommit=0

#开启步事务
start transaction
编辑sql语句（select,insert,update,delete）

#结束事务
commit #提交事务
rollback #回滚事务

8.3 事务并发

并发问题

• 脏读
• 不可重复读
• 幻读

隔离级别

• read uncommitted(读未提交数据)： 允许事务读取未被其他事务提交的变更。脏读，不可重复读，幻读的问题都会出现
• read commited(读已提交的数据): 只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读，幻读问题仍然可能出现。
• repeatable read(可重复读)：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新，可避免脏读和不可重复读。
• serializable(串行化)：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作。所有并发问题都可以避免，但是性能十分低下。

设置隔离级别

#查看隔离级别
SELECT @@tx_isolation

#设置当前隔离级别 
set session transaction isolation level xxxx

#设置系统全局隔离级别
set global transaction isolation level xxxx

设置节点

savepoint 节点名  #设置节点，设置保存点

rollback to 节点名 #回滚到保存点

9 视图

视图是一个虚拟的表，但是和普通的表一样使用。行和列的数据来自自定义视图的查询中使用的表，并且是在使用视图时自动生成的，只保存了sql逻辑，不保存查询的结果。

应用

• 应用于多个地方用到同样的查询结果。
• 使用的sql语句比较复杂。
• 保护数据，提高安全性。

查看视图

desc 视图名

show create view 视图名

视图的创建

creat viem 视图名 
as
select,,,,,,,,

视图的修改

#方式1
create or replace 视图名
as
select ,,,,,

#方式2
alter view 视图名
as
select ,,,,,,

删除视图

drop view 视图名,视图名，，，，

10 存储和函数

10.1 变量

系统变量

由系统提供，不是用户自定义，属于服务器层次。

#查看所有系统变量
show global|session variables

#查看满足条件的部分系统变量
show global | session variables like '%sss%'

#查看某个指定的系统变量的值
select @@global|session.系统变量名

#为某个系统变量赋值
set global|session 系统变量名=值
set @@global|session.系统变量名=值
###加上session 为会话变量

自定义变量

/**
变量是用户自定义的，不是由系统定义的。可以应用在任何地方
声明--》赋值---》使用

作用域：针对当前会话（连接）有效
*/
#  声明并初始化
set @用户变量名=值 或者：=
select @用户变量名：=值

#赋值 更新
set @用户变量名=值 或者：=
select @用户变量名：=值
select 字段 into 变量名 from 表

#查看
select @用户变量名

自定义变量--局部变量

/**
只用应用于begin end 之中，且只能使用begin end 第一句。
*/

#声明
declare 变量名 类型
declare 变量名 类型 default 值

#赋值
set @用户变量名=值 或者：=
select @用户变量名：=值
select 字段 into 变量名 from 表

#使用
select 局部变量名

10.2 存储过程

一组预先编译好的sql语句集合。

提高代码重用性，简化操作，减少了编译此时并且减少了和数据库服务器连接的次数，提高了效率。适合做批量的插入批量的更新。

#创建语法
create procedure 存储过程名（参数列表）
begin
	存储过程体
end
/**
参数类型包含三部分：
参数模式 参数名 参数类型
参数模式：
IN:该参数可以作为输入，也就是该参数需要调用放传入值
OUT： 该参数可以作为输出，也就是该参数可以作为返回值
INOUT： 该参数既可以作为输入也可以作为输出

如果存储过程体仅仅只有一句话，begin end 可以省略
存储过程体之中每条sql语句的结尾要求必须加分号，
存储过程的结尾可以使用delimiter 重新设置

*/ 

#调用
call 存储过程名（参数列表）

#删除
drop procedure 存储过程名

#查看
SHOW CREATE PROCEDURE 存储过程名

10.3 函数

函数有且仅有一个返回值，适合做数据处理并且返回一个结果。

#创建语法
create function 函数名（参数列表） returns 返回类型
begin
	函数体
end
/**
参数列表
参数名 参数类型

函数体
必须有返回参数
使用 delimiter 符合 作为结尾标记
*/

#查看函数
show create function 函数名

#删除函数
drop function 函数名

10.4 流程控制

分支结构

if 函数
select if(表达式1，表达式2，表达式3)
如果表达式1为真，则返回表达式2，否则返回表达式3

case 结构
见 3.1

if 结构
#实现多重分支
if 条件1 then 语句1
elseif 条件2 then 语句2
[else 语句]
end if;

循环结构

/**
循环控制：
iterate :结束本次循环，开始下一次循环
leave：结束循环
*/

#1、while
标签:while 循环条件 do
		循环体
	end while 标签
	

#2、loop
标签：loop
		循环体
	end loop 标签
	
#3、repeat
标签：repeat
 		循环体
 	until 结束循环的条件
 	end repeat 标签

11 完结撒花

本文是学习mysql 基础的学习笔记，观看尚硅谷网的mysql数据库教程。如有错误或者不足之处，希望指正。。

/***
 *                        d*##$.
 *  zP"""""$e.           $"    $o
 * 4$       '$          $"      $
 * '$        '$        J$       $F
 *  'b        $k       $>       $
 *   $k        $r     J$       d$
 *   '$         $     $"       $~
 *    '$        "$   '$E       $
 *     $         $L   $"      $F ...
 *      $.       4B   $      $$$*"""*b
 *      '$        $.  $$     $$      $F
 *       "$       R$  $F     $"      $
 *        $k      ?$ u*     dF      .$
 *        ^$.      $$"     z$      u$$$$e
 *         #$b             $E.dW@e$"    ?$
 *          #$           .o$$# d$$$$c    ?F
 *           $      .d$$#" . zo$>   #$r .uF
 *           $L .u$*"      $&$$$k   .$$d$$F
 *            $$"            ""^"$$$P"$P9$
 *           JP              .o$$$$u:$P $$
 *           $          ..ue$"      ""  $"
 *          d$          $F              $
 *          $$     ....udE             4B
 *           #$    """"` $r            @$
 *            ^$L        '$            $F
 *              RN        4N           $
 *               *$b                  d$
 *                $$k                 $F
 *                 $$b                $F
 *                  $""               $F
 *                  '$                $
 *                   $L               $
 *                   '$               $
 *                    $               $
 */