MySQL学习笔记

目录

 一.数据库简介

 二.SQL语法

 1 DDL语句

数据库操作

 表操作

 2 DML

3 DQL

4.DCL

 三.函数

四 约束

五 多表联查

1.内连接

2.外连接

六 事务

事务四大特性

并发事务问题

事务隔离级别

---

 一.数据库简介

1.数据库：数据存储的仓库

2.数据库管理系统：操纵和管理数据库的大型软件

3.SQL：操作关系型数据库的编程语言，是一套标准

关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

通用语法：

1.SQL语句可以单行或多行书写，以分号结尾

2.SQL语句可以使用空格或缩进来增强可读性

3.SQL语句不缺分大小写，关键字建议大写

4.单行注释：-- 或 #

   多行注释：/*  */

 二.SQL语法

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据库定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data Manipulation Language	数据库操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据库查询语言，用来查询数据库表中的记录
DCL	Data Control Language	数据库控制语言，用来创建数据库用户、控制数据库访问权限

 1 DDL语句

数据库操作

查询

查询所有数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASES;

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE排序规则];

删除

DROP DATABASE [IF EXISTS] 数据库名;

使用数据库

USE 数据库名;

 表操作

 创建表

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],  #例如：COMMENT '注释1'
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ......
    字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释]

查询当前数据库所有表

SHOW TABLES;

查询表结构

DESC 表名

查询指定表的建表语句

SHOW CREATE TABLE 表名;

表操作-修改

#添加字段
alter table 表名 add 字段名 类型（长度） [comment 注释] [约束];

#修改数据类型
alter table 表名 modify 字段名 新数据类型（长度）;

#修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束];

#删除字段
alter table 表名 drop 字段名;

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

表操作-删除

#删除表
drop table [if exists] 表名;

#删除指定表，并重新创建该表
truncate table 表名;

 2 DML

 添加数据

#给指定字段添加数据
insert into 表名 （字段名1，字段名2，......） value (值1，值2，......);

#给全部字段添加数据
insert into 表名 values (值1，值2，....);

#批量添加数据
insert into 表名 （字段名1，字段名2，...） values (值1，值2，...)(值1，值2，...)，...;
insert into 表名  values (值1，值2，...)(值1，值2，...)，...;

 修改数据

#修改数据
update 表名 set 字段名1=值1，字段名2=值2，...[where 条件];

 删除数据

#删除数据
delete from 表名 [where 条件]

3 DQL

distinct  可以去除重复记录  示例：select distinct 字段列表 from 表名;

查询

#执行顺序
select  #4
        字段列表 
from    #1
        表名列表 
where   #2
        条件列表 
group by  #3
        分组字段列表 
having   
        分组后条件列表 
order by  #5
        排序字段列表
limit    #6
        分页参数

条件查询

select 字段列表 from 表名 where 条件列表;

 聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：select 聚合函数（字段列表） from 表名;

分组查询

select 字段名 from 表名 [where 查询条件] group by 分组字段 [having 分组后过滤条件]

where和having区别：

执行时机：where是查询时过滤的条件，having是分组后进行过滤的条件

判断条件：where不能对聚合函数进行判断，而having可以

注：分组之后查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

排序查询

#降序desc 升序asc
select 字段名 from 表名 where 条件 order by 字段名1 排序方式1，字段名2，排序方式2;

分页查询

#起始索引：每页第一条数据的索引，索引从0开始
select 字段名 from 表名 limit 起始索引，每页数据条数;

4.DCL

用来管理数据库用户、控制数据库的访问权限。

管理用户

#1.查询用户
USE mysql;
select * from user;

#2.创建用户
create user '用户名'@'主机名' identififd by '密码';

#3.修改用户密码
alter user '用户名'@'主机名' identififd with mysql_native_password by '密码';

#4.删除用户
drop user '用户名'@'主机名';

权限控制

权限	说明
all，all privileges	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	常见数据库/表

#查询权限
show grants for '用户名'@'主机名';

#授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

#撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

 三.函数

字符串函数

函数	功能
concat(S1,S2,...Sn)	字符串拼接
lower(str)	将字符串全部小写
upper(str)	将字符串全部大写
lpad(str,n,pad)	左填充将pad填充到str左边，达到n字符
rpad(str,n,pad)	右填充将pad填充到str右边，达到n字符
trim(str)	去除字符串头部和尾部的空格
substring(str,start,len)	截取字符串

select 函数（参数）；

数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand()	返回0-1随机数
round(x,y)	求x四舍五入值，保留y位小数

 日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定的date月份
day(date)	获取指定的date日期
date_add(date,interval exper type) 例：date_add(now(),interval 70 day)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date1和结束时间date2之间的天数

流程函数

函数	功能
if(value,t,f)	如果value为true，则返回t，否则返回f
ifnull(value1,value2)	如果value部位空，返回value1，否则返回value2
case when [cal1] then [res1] ...else [default] end	如果val1为true，返回res1， ...否则返回default默认值
case [expr] when [val1] then [res1] ...else [default] end	如果expr的值等于value2，返回res1，...否则返回default默认值

四 约束

约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

约束	描述	关键字
非空约束	限制该字段的数据不能为null	not null
唯一约束	保证该字段的所有数据都是唯一，不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	default
检查约束	保证字段满足某一个条件	check
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	foreign key

五 多表联查

多表联查：内连接、外连接、自连接

        内连接：显示内连接、隐式内连接

        外连接：左外连接、右外连接

        自连接：自己与自己别名连接

1.内连接

只查询两表交集数据

隐式内连接：select 字段名 from 表1，表2 where 表1.id=表2.id；

显示内连接：select 字段名 from 表1 join 表2 on 条件

2.外连接

左外连接：左外连接和左连接是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。

        语法：select 字段名 from 表1 left join 表2 on 条件    //多张表时写多个left jion

右外连接：右外连接和右连接是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。

        语法：语法：select 字段名 from 表1 right join 表2 on 条件

六 事务

事务的特性：原子性、一致性、隔离性、持久性

        原子性：要么所有操作全部成功，要么操作失败，事务全部回滚

        一致性：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，例：银行转账，两个账户无论怎么转账，总金额不变

        隔离性：隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一 个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

        持久性：事务一旦被提交了，那么对数据库中的数据的改变就是永久性的

查看事务提交方式：

select @@autocommit;

set @@autocommit = 0;

提交事务：

commit；

回滚事务：

rollback；

开启事务：

start transaction 或 begin；

事务四大特性

原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败

一致性：事务完成时，必须使所有的数据都保持一致状态

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影

注：并发事务问题，事务A执行过程中，事务B对同一个表的数据进行操作造成的

事务隔离级别

用来解决事务并发问题

隔离级别	脏读	不可重复读	幻读
Read uncommitted读未提交	会发生	会发生	会发生
Read committed读已提交	不会发生	会发生	会发生
Repeatable Read(默认)重复读	不会发生	不会发生	会发生
Serializable串行化	不会发生	不会发生	不会发生

 Read uncommitted数据安全性最差，但性能最好
Serializable数据安全性最好，但性能最差

--查看事务隔离级别
SELECT @@TRANSACTION_IOSLATION

--设置书屋隔离级别
SET [SESSION|GLOBAL] TRANSACTION IOSLATION LEVEL {READUNCOMMITTED|READCOMMITTED|...}
--SESSION 当前设置窗口有效  
--GLOBAL 全局有效