mysql基础学习笔记

mysql 安装 与 启动

安装

1、官网下载对应版本mysql 并 安装
2、配置对应环境变量
3、ps -ef | grep mysqld 查看mysql是否启动

启动与使用

1、启动服务：sudo mysql.server start
2、停止服务：sudo mysql.server stop
3、重启服务：sudo mysql.server restart
4、客户端连接：sudo mysql -u root -p
5、退出：exit

数据模型

使用mysql语言通过 客户端，控制 数据库管理系统，创建数据库（数据表）
客户端->DBMS->数据库->数据表

SQL 通用语法 & SQL 分类

通用语法

• sql语句可以单行或多行书写，以分号;分隔
• sql语句可以使用空格和缩进来增加语句的可读性
• Mysql数据库的语句不区分大小写，但关键字建议大写
• 注释：
  • 单行注释：-- 或 #（mysql独有)
  • 多行注释：/* 注释内容 */

SQL 分类

DDL：数据定义语言，用来定义数据库对象（数据库，表，字段）

1. 数据库操作
   • 查询所有数据库：SHOW DATABASES;
   • 查询当前数据库：SELECT DATABASE();
   • 创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名称 [DEFAULT CHARSET 字符集] [CALLATE 排序规则];
   • 删除数据库：DROP DATABASE [IF NOT EXISTS] 数据库名称;
   • 使用数据库：USE 数据库名;
2. 表操作——创建&查询
   • 查询当前数据库所有表：SHOW TABLES;
   • 查询表结构：DESC 表名;
   • 查询指定表的建表语句：SHOW CREATE TABLE 表名;
   • 创建表：
     CREATE TABLE 表名(
     字段1 字段1类型 [COMMENT 注释],
     字段2 字段2类型 [COMMENT 注释],
     …
     字段n 字段n类型 [COMMENT 注释]
     )[COMMENT 表注释];
3. mysql的数据类型

• 数值类型

MySQL数据类型	含义（有符号）
tinyint(m)	1个字节 范围(-128~127)
smallint(m)	2个字节 范围(-32768~32767)
mediumint(m)	3个字节 范围(-8388608~8388607)
int(m)	4个字节 范围(-2147483648~2147483647)
bigint(m)	8个字节 范围(±9.22*10的18次方)
float(m,d)	单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d)	双精度浮点型 16位精度(8字节) m总个数，d小数位
decimal(m,d)	参数m<65 是总个数，d<30且 d<m 是小数位。

• 字符串类型

MySQL数据类型	含义
char(n)	定长字符串，最多255个字符 （n是多少就占用多少字节的空间，性能好）
varchar(n)	变长字符串，最多65535个字符 （存储1个字符，定义n为10，占用一个字符的空间，性能较差）
tinytext	可变长度，最多255个字符
text	可变长度，最多65535个字符
mediumtext	可变长度，最多2的24次方-1个字符
longtext	可变长度，最多2的32次方-1个字符
tinyblob	不超过255个字符的二进制数据
blob	二进制形式的长文本数据

• 日期时间类型

MySQL数据类型	含义
date	日期 ‘2008-12-2’
time	时间 ‘12:25:36’
year	1901-2155 年份值
datetime	日期时间 ‘2008-12-2 22:06:44’
timestamp	自动存储记录修改时间

4. 表操作——修改&删除
   • 修改-添加字段：alter table 表名 add 字段 字段类型 [comment 注释][约束]
   • 修改-修改数据类型：alter table 表名 modify 字段 新数据类型;
   • 修改-修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 数据类型[comment 注释][约束]
   • 删除-删除字段：alter table 表名 drop 字段名;
   • 修改表名：alter table 表名 rename to 新表名;
   • 删除表：drop table [if exists] 表名;
   • 删除表并重新创建该表：truncate table 表名;

DML：数据操作语言，用来对数据表中的数据进行 增、删、改

1. 添加数据 insert
   • 给指定字段添加数据：insert into 表名 (字段名1,字段名2,…) values (值1，值2，…);
   • 给所有字段添加数据：insert into 表名 values (值1，值2，…);
   • 批量添加数据：
     -insert into 表名 （字段名1，字段名2，…） values （值1，值2，…），（值1，值2，…）;
     -insert into 表名 values （值1，值2，…），（值1，值2，…）;
2. 删除数据 delete
   • delete from 表名 [where 条件];
3. 修改数据 update
   • update 表名 set 字段名1=值1,字段名2=值2[where 条件];

DQL：数据查询语言，用来查询数据库中表的数据

语法：

   select 字段列表 from 表名列表
   where 条件列表
   group by 分组字段列表
   having 分组后条件列表
   order by 排序字段列表
   limit 分页参数

1. 基础查询
   • 查询多个字段：select 字段1,字段2... from 表名; select * from 表名;
   • 设置别名： select 字段1 [as 别名1], ... from 表名;
   • 去除重复项： select distinct 字段列表 from 表名;
2. 条件查询
   • 语法： select 字段列表 from 表名 where 条件列表;
   • 条件：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如NULL=NULL的结果NULL
<=>	等于，NULL安全，NULL<=>NULL的结果是true
<> 或者 !=	不等于
between…and…	匹配范围，如果范围是相等的则返回true
in（a1，a2，a3…）	其中任何一个是成立的，都返回true
is NULL	是NULL
is not NULL	不是NULL
LIKE	模糊匹配，%表示任意多个任意字符，_表示任意一个字符
AND 或 &&	多个条件必须都为true，结果才是true
OR 或 ||	任意一个条件为true，结果就是true
NOT 或 !	条件为TRUE，结果为FALSE

3. 聚合函数

将一列数据作为一个整体，进行纵向计算

• 常见的聚合函数：

AVG():求平均值
SUM()求总和
MAX() ：求最大值
MIN()：求最小值
COUNT()：求个数\

4. 分组查询 （group by）

   select 字段列表 from 表名列表
   where 条件列表
   group by 分组字段列表
   having 分组后条件列表
   order by 排序字段列表
   limit 分页参数

where与having的区别：

• 执行时机不同，where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤；
• 判断条件不同，where不能对聚合函数进行判断，having可以

5. 排序查询（order by）

   select 字段列表 from 表名列表
   where 条件列表
   group by 分组字段列表
   having 分组后条件列表
   order by 排序字段列表-字段1 排序方式1，字段2 排序方式2
   limit 分页参数

排序方式：

• ASC：升序（默认值）
• DESC：降序

6. 分页查询 （limit）

   select 字段列表 from 表名列表
   where 条件列表
   group by 分组字段列表
   having 分组后条件列表
   order by 排序字段列表
   limit 分页参数-起始索引，查询记录数

注意：

• 起始索引从0开始，起始索引=（查询页面-1）*每页记录数
• 分页查询是数据库的方言，不同数据库有不同实现，mysql是limit
• 如果查询的是第一页的数据，起始索引可以省略，简写为limit 10；

7. DQL语句的执行顺序

from -> where -> group by -> having -> select -> order by -> limit

DCL：数据控制语言，用来创建数据库用户，控制数据库的访问权限

1. 用户管理

   • 查询用户：use mysql; select * from user;
   • 创建用户: create user ‘用户名’@‘主机名’ identified by ‘密码’;
   • 修改用户密码：alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’;
   • 删除用户: drop user ‘用户名’@‘主机名’;
   • 任意主机可以用%

2. 权限控制

   • 查询权限：show grants for ‘用户名’@‘主机名’;
   • 授予权限：grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
   • 撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;

函数

字符串函数

Mysql中内置了很多字符串函数，常用的有：

函数	格式	功能
CONCAT	concat(字符串1,字符串2,…)	字符串拼接，将指定的所有字符串拼接成一个字符串
LOWER	lower(字符串)	将字符串全部转换成小写
UPPER	upper(字符串)	将字符串全部转换为大写
LPAD	lpad(字符串,字符串总长度,要填充的内容)	左填充，意思就是在字符串的左边填充内容，首先计算出现有字符串的长度，然后根据指定的字符串总长度进行比较，如果小于总长度那么就在字符串的左侧进行填充，具体填充什么就要看第三部分指定的填充内容，可以是字符串也可以是数字、特殊符号等。
RPAD	rpad(字符串,字符串总长度,要填充的内容)	右填充，意思就是在字符串的右边填充内容，首先计算出现有字符串的长度，然后根据指定的字符串总长度进行比较，如果小于总长度那么就在字符串的右侧进行填充，具体填充什么就要看第三部分指定的填充内容，可以是字符串也可以是数字、特殊符号等。
TRIM	trim(字符串)	去除字符串头部和尾部包含的空格
SUBSTRING	substring(字符串,起始位,截取的长度)	用于截取字符串的长度，首先指定要使用的字符串，然后指定起始位，这个起始位就是从第几个字符串处开始截取，第一个起始位是1，例如我们指定的字符串是jiangxl，要从第三个字符串处开始截取，截取两位，就可以这么写substring(jiangxl,3,2)，最终输出的结果就是an

数值函数

常见的数值函数：

函数名称	作 用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值

日期函数

常见的日期函数：

函数名称	作 用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

流程函数

函数名称	作用
if(value,t,f)	判断，流程控制:如果value为true，则返回true，否则返回false
ifnull(value1,value2)	判断是否为空:如果value1不为null，返回value1，否则返回value2
case when value1 then res1… else default end	搜索语句：如果value1为true，则返回res1；否则返回default
case expr when value1 then res1… else default end	搜索语句：如果expr等于value1，则返回res1；否则返回default

约束

概述

概念：约束是作用在表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中数据的正确性、有效性和完整性
分类：

• 非空约束：NOT NULL - 指示某列不能存储 NULL 值。
• 唯一约束：UNIQUE - 保证某列的每行必须有唯一的值。
• 默认约束：DEFAULT - 规定没有给列赋值时的默认值。
• 主键约束：PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• 外键约束：FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• 检查约束（8.0.16版本之后）：CHECK - 保证字段值满足某一个条件

练习：

create table test3(
    id int primary key auto_increment,
    name varchar(20) not null unique ,
    age tinyint check ( age>=0 and age<=120 ),
    status char(1) default '1',
    gender char(1)
)comment 'test3:约束关键字使用';
insert into test3 values(1, '王华', 24, '1', '女'),(2, '王贤', 24, '1', '女');
insert into test3 (name, age, gender) values('李三', 24, '女'),('王思', 24, '女');

外键约束

概念：外键用来让两张表的数据建立连接，从而保证数据的一致性和完整性
语法：

# 创建的时候添加外键：
create table 表名 (
    字段名 数据类型,
    ...
    [constraint 外键名称] foreign key (外键字段名) reference 主表(主表列名)
);

# 添加数据的时候添加外键：
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references  主表(主表列名);

# 删除外键：
alter table 表名 drop foreign key 外键名称;

外键约束的删除和更新行为

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on delete set null on update cascade;

关键字	描述
ON UPDATE CASCADE	当主表中的外键更新时，从表中的相应字段也会被更新。
ON UPDATE SET NULL	当主表中的外键更新时，从表中的相应字段将被设置为NULL。
ON UPDATE NO ACTION	当主表中的外键更新时，从表中的相应字段不做任何更改。
ON DELETE CASCADE	当主表中的外键被删除时，从表中的相应字段也会被删除。
ON DELETE SET NULL	当主表中的外键被删除时，从表中的相应字段将被设置为NULL。

多表查询

多表关系

分为一对多、多对多和一对一三种关系；\

• 一对多（多对一）：例如员工信息表，跟部门信息表，员工只属于一个部门，一个部门有很多员工；
• 多对多：例如课程信息表和学生信息表，一个课程可以有很多学生选，一个学生可以选很多课程；
• 一对一：信息过于庞杂的表为了获取信息的效率和方便，进行拆分，并通过id建立联系；

多表查询概述

• 概述：指从多张表中查询数据
• 笛卡尔积：是指在数学中，两个集合A、B的所有组合情况（在多表查询中，需要消除无效的笛卡尔积）
• 多表查询分类：
  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
    • 左外连接：查询左表所有数据，以及交集部分数据
    • 右外连接：查询右表所有数据，以及交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名
• 子查询

内连接

• 隐式内连接：select 字段列表 from 表1,表2 where ...
• 显示内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件

外连接

• 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 连接条件
• 右外连接：select 字段列表 from 表1 right [outer] join 表2 on 连接条件

自连接

• 语法：select 字段列表 from 表1 a join 表1 b on 连接条件

联合查询

union，union all
对于union 查询，就是把多次查询的结果合并起来，形成一个新的查询结果集；

select 字段列表 from 表1 
union [all] 
select 字段列表 from 表2;

• 去重：去掉 all

子查询

• 定义：sql语句中嵌套select 语句，称为嵌套查询，又称为子查询
  select * from t1 where column1=(select column1 from t2);
  子查询外部的语句可以是 insert、delete、select、update\
• 根据子查询的结果不同，分为：
  • 标量子查询：子查询的结果为单个值
  • 列子查询：子查询的结果为一列
  • 行子查询：子查询的结果为一行
  • 表子查询：子查询的结果为多行多列
• 根据子查询的位置，可以分为：where之后、select之后 和 from之后的子查询

标量子查询

列子查询

行子查询

表子查询

事务

事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败

操作演示

方式一：设置事务为手动提交，执行操作，如果没错误，就commit，否则回滚 rollback

• 查看设置事务提交方式
  * 查看 select @@autocommit;
  * 设置 set @@autocommit=0; # 设置手动提交事务
• 提交事务：commit;
• 回滚事务：rollback;

方式二：开启事务，执行操作，如果成功，提交commit，否则回滚 rollback

• 开启事务：start transaction ; 或者 begin;
• 提交事务: commit;
• 回滚事务: rollback;

事务的四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性（Consistency）：事务完成时，必须使所有数据保持一致状态
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性（Durability）：事务一旦提交或回滚，他对数据库的改变就是永久的

并发事务问题

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

事务的隔离级别

• 从上到下，隔离级别递增；但是性能递减；

隔离级别	脏读	不可重复度	幻读
read uncommited	✅	✅	✅
read commited	❎	✅	✅
repeated read(默认）	❎	❎	✅
Serializable	❎	❎	❎

• 查看事务的隔离级别：select @@transaction_isolation;
• 设置事务的隔离级别:set [global|session] transaction isolation level [repeatable read] ;