网站日志分析教程/企业网页设计制作

MYSQL数据库学习

mysql的启动

net start mysql
net stop mysql

MYSQL客户端的连接

mysql -h 127.0.0.1 -p 3306 -u root -p

SQL

SQL通用语法

1. SQL语句可以单行或者多行书写，以分号结尾
2. SQL语句可以使用空格/缩进来增强语句的可读性
3. Mysql数据库的SQL语句不区分大小写，关键字建议使用大写
4. 注释：
   ○ 单行注释：—注释内容 或 #注释内容
   ○ 多行注释：/注释内容/

SQL分类

SQL语句，其功能主要分为四类：DDL，DML，DQL，DCL

1. DDL ：数据定义语言，用来定义数据对象
2. DML：数据操作语言，用来对数据库表中的数据进行增删改
3. DQL：数据查询语言，用来查询数据库中表的记录
4. DCL：数据控制语句，用来创建数据库用户，控制数据库的访问权限

DDL

数据库操作

查询所有数据库

show database;

查询当前数据库

select database() ;

创建数据库

create database 数据库名;

删除数据库

drop database [if exists] 数据库名;

切换数据库

use 数据库名 ;

表操作

表操作-查询创建

查询当前数据库所有表

show tables;

查看指定表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

创建表结构

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
…
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;

create table tb_user(
id int comment ‘编号’,
name varchar(50) comment ‘姓名’,
age int comment ‘年龄’,
gender varchar(1) comment ‘性别’
) comment ‘用户表’;

表操作-修改

1. 添加字段
   [ COMMENT 注释] [约束];ALTER TABLE 表名 ADD 字段名 类型（长度） [ COMMENT 注释] [约束];

2. 修改数据类型
   ALTER TABLE 表名 MODIFY 字段名 新数据类型;

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

4. 删除字段
   alter table 表名 drop 字段名

5. 修改表名
   alter table 表名 rename to 新表名

表操作-删除

1. 删除表
   drop table [if exists ] 表名;

2. 删除指定表，并重新创建表
   truncate table 表名;

DML

● 添加数据
● 修改数据
● 删除数据

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

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

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

insert into 表名 values (值1，值2，…),(值1，值2，…)，(值1，值2，…);

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

e. 删除数据
delete from 表名 [where 条件];

注意事项
■ delete 语句的条件可以没有，也可以有，如果没有条件，将删除整张表
■ delete语句不能删除某一个字段的值可以使用update ，将该字段值设置为NULL即可
■ 当进行删除全部数据操作时，datagrip会提示我们，询问是否确认删除

DQL

数据库查询语言

查询关键字 select
在一个正常的业务系统中，查询操作的频次是远高于增删改的，我们去访问企业官网，电商网站，在这些网站中我们所能看到的数据，实际都是需要从数据库中查询并展示的，而且在查询的过程中，可能还会涉及条件，排序，分页等操作
基本语法
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数

● 基本查询（不带条件）
● 条件查询（where）
● 聚合函数(count,max,min,avg,xum)
● 分组查询（group by）
● 排序查询（order by）
● 分页查询(limit)
基础查询

1. 查询多个字段
   select 字段1，字段2，字段3… from 表名;
   select * from 表名；//查询所有字段，能不适用就少用（不直观，影响效率）
2. 字段设置别名
   select 字段1 [AS 别名1],字段2 [AS 别名2] … from 表名；、
   select 字段1 [别名1]，字段2 [别名2] …from 表名;
3. 去除重复记录
   select distinct 字段列表 from 表名;
   条件查询
   语法
   seelect 字段列表 from 表名 where 条件列表;
   条件between and
   in()
   like 占位符
   is null
   and &&
   or ||
   NOt !
   聚合函数
   将一列数据作为一个整体进行纵向计算
   常见的聚合函数：
   count 统计数量
   max 最大值
   min 最小值
   avg 平均值
   sum 求和
   语法：
   select 聚合函数（字段列表） from 表名;
   null值是不参与所有聚合函数运算的
   分组查询
   select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
   where 与 having的区别：
   ● 执行时机不同：where是分组之前进行过滤，不满足where 条件，不参与分组，而having是分组之后对结果进行过滤
   ● 判断条件不同：where 不能对聚合函数进行判断，而having可以
   注意事项
   ● 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
   ● 执行顺序：where > 聚合函数 > having
   ● 支持多字段分组，具体语法为：group by columnA,columnB
   排序查询（升序降序）
   select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；
   排序方式
   ● ASC:升序（默认值）
   ● desc:降序
   分页查询
   在网站中各种各样的分页条，后台都需要借助数据库的分页操作
   语法：
   select 字段列表 from 表名 limit 起始索引，查询记录数;
   执行顺序
   DQL语句在执行时的执行顺序，也就是先执行哪部分后执行哪部分
   from 先执行，然后 where 再执行select
   from - where - group by – having – select – order by – limit–
   DCL
   data control language 数据控制语言 ,用来管理数据库用户，控制数据库访问权限
   管理用户
4. 查询用户
   select * from mysql.user;
5. 创建用户
   create user ‘用户名’@‘主机名’ identified by ‘密码’
6. 修改用户密码
   alter user ‘用户名’ identified with mysql_native_password by ‘新密码’;
7. 删除用户
   drop user ‘用户名’@‘主机名’;
   权限控制
   all ，allprivileges 所有权限
   select 查询数据
   insert 插入数据
   update 修改数据
   delete 删除数据
   alter 修改表
   drop 删除数据库表视图
   create 创建数据库/表
   查询权限
   show grants for ‘用户名’@‘主机名’;
   授予权限
   grant 权限列表 on 数据库名，表名 to ‘用户名’@‘主机名’;
   撤销权限
   revoke 权限列表 on 数据库名，表名 from ‘用户名’@‘主机名’;
   注意事项：
   ● 多个权限之间，使用逗号分隔
   ● 授权时，数据库名和表名可以使用*进行通配，代表所有
   函数
   字符串函数，数值函数，日期函数，流程函数
   字符串函数
   concat() 字符串拼接
   lower ()将字段穿str全部转为小写
   upper（）将字符串str全部转为大写
   lpad(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
   rpad(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
   trim() 去掉字符串头部和尾部的空格
   substring(str,start,len)返回从字符串str从start位置起的len个长度的字符串
   数值函数
   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,exprtype)返回一个日期/时间值加上一个时间间隔expr后的时间值
   datediff(date1,date2)返回起始时间date1和结束时间date2之间的天数
   select curdate();
   select curtime();
   select now();
   select year(now());
   select month(now());
   select day(now());
   select date_add(now(),interval 70 year);
   select datediff(‘2021-10-01’,‘2021-12-01’);
   流程函数
   可以在SQL语句中实现条件筛选，从而提高语句的效率。
   if（value,t,f）如果value为true,则返回t,否则返回f
   ifnull(value1,value2)如果value1不为空，返回value1,否则返回value2
   case when [vall] then [res1] … else [default] end 如果vall为true，返回res1,否则返回default默值
   case [expr] when [vall] then [res1] … else [default] end 如果expr的值等于vall，返回res1,…否则返回default默认值

约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据
保证数据库中数据的正确，有效性和完整性
分类
● 非空约束 限制该字段数据不能为null
● 唯一约束 保证该字段的所有数据都是唯一，不重复的
● 主键约束 主键是一行数据的唯一标识，要求非空且唯一
● 默认约束 保存数据时，如果未指定该字段的值，则采用默认值
● 检查约束 保证字段值满足某一个条件
● 外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性
约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束
约束演示
需求如下：

外键约束
用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
我们来看一个例子：

左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID，姓名，年龄，职位，薪资，入职日期，上级主管ID，部门ID,在员工的信息存储的是部门ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键，关联的是另一张表的主键
注意：目前上述两张表，只是在逻辑上存在这样一层关系。在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的
语法：

1. 添加外键
   create table 表名(
   字段名 数据类型，
   …
   [constrantnt] [外键名称] foreing key (外键字段名) references 主表(主表列名)
   );
   alter table 表名 add constraint 外键名称 foreing key(外键字段名)
   references 主表（主表列名）;
   删除外键
   alter table 表名 drop foreing key 外键名称;
   删除更新行为
   添加了外键后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为：
   ● no action
   ● 当在父表中删除/更新对应记录时，首先检查该记录是否有对应的外键，如果有则不允许删除/更新
   ● restrict
   ● 当在父表中删除/更新对应记录时，首先先检查该记录是否有对应外键，如果有则不允许更新./删除与 no action 一致
   ● cascade
   ● 当在父表中删除更新对应记录时，首先检查该记录是否有对应的外键，如果有，则也删除/更新外键在子表中的记录
   ● set null
   ● 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null
   ● set default
   ● 父表有变更时，子表将外键列设置成一个默认的值（innodb不支持）
   alter table 表名 add constraint 外键名称 foreing key (外键字段) references 主表名（主表字段名） on update cascade on delete cascade;
   多表查询
   多表关系
   ● 一对多（多对一）
   ● 多对多
   ● 一对一
2. 一对多
   ● 案例：部门与员工的关系
   ● 关系：一个部门对应多个员工，一个员工对应一个部门
   ● 实现：在多的一方建立外键，指向一的一方的主键
3. 多对多
   a. 案例：学生与课程的关系
   b. 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
   c. 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
4. 一对一
   a. 案例：用户与用户详细的关系
   b. 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详细字段放在里一张表中，以提升操作效率
   c. 实现:在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的unique

多表查询概述
多表查询就是指从多张表中查询数据
分类
● 连接查询
○ 内连接，相当于查询A，B交集部分数据
○ 外连接
■ 左外连接：查询左表所有数据，以及两张表交集部分数据
■ 右外连接： 查询右表所有数据，以及两张表交集部分数据
■ 自动连接：当前表与自身的连接查询，自连接必须使用表别名
● 子查询
● 内连接
○ 内连接查询的是两张表交集部分的数据
○ 内连接的语法分为两种：隐式内连接，显式内连接
○ 隐式内连接：
select 字段列表 from 表1，表2 where 条件…；
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件 …；
外连接
● 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件…;
左外连接相当于查询表1（左表）的所有数据，当然也包含表1和表交集部分的数据。

● 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件…;
右外连接相当于查询表2 （右表）的所有数据，当然也包含表1和表2交集部分的数据。
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了，而我们在日常开发使用时，更偏向于左外连接
自连接
一张表连接查询多次
对于自连接可以是内连接也可以是外连接
select 字段列表 from 表A 别名A join 表A 别名B on 条件 …，
在自连接查询中，必须要为表起别名，要不然，我们不清楚所指的条件，返回的字段，到底是哪一张表的字段

联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集
select 字段列表 from 表A…
union all
select 字段列表 from 表B…
● 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
● union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重
如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all 联合查询时，将会报错
子查询
sql语句中嵌套select语句，称为嵌套查询，又称子查询
select * from t1 where volum1 = (select column1 from t2);
子查询外部的语句可以是insert / update /delete /select中的任何一个

分类
根据子查询结果不同，分为：
A.标量子查询(子查询结果为单个值)
B.列子查询(子查询结果为一列)
C.行子查询(子查询结果为一行)
D.表子查询(子查询结结构为多行多列)
根据子查询位置，分为：
where之后
from之后
select之后

标量子查询‘
子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式，这种子查询称为标量子查询
= <> > >= < <=
列查询：
子查询返回的结果是一列（可以是多行）,这种子查询称为列子查询
常用的操作符：IN，NOT IN ， ANY ，SOME，ALL
in 在制定的集合范围之内，多选一
not in 不在指定的集合范围之内
any 子查询返回列表中，有任意一个满足即可
some 与any等同，使用some的地方都可以使用any
all 子查询返回列表的所有值都必须满足
行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询
常用的操作符 = <> IN NOT IN
表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询
常用的操作符：IN

事务
事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
业务逻辑之前开启事务，执行完毕后提交事务，如果执行过程报错，则回滚事务，把数据恢复到事务开始之前的状态
默认mysql的事物是自动提交的，也就是说，当执行完一条DML语句时，Mysql会立即稳式的提交事务。
事务操作
控制事物一

1. 查看/设置事物提交方式
   select @@autocommit;
   set @@autocommit = 0 ;
2. 提交事务
   commit;
3. 回滚事物
   rollback;
   上述的这种方式，我们是修改了事物的自动提交行为，把默认的自动提交改为手动提交，此时我们执行的DML语句不会提交，需要手动的执行commit进行提交
   控制事物二
   开启事务
   start transaction 或 begin;
   提交事务
   commit;
   回滚事务
   rollback;
   转账案例：
   start transaction

select * from account where name=‘张三’;
update account set money = money +1000 where name = ‘李四’;
commit;
rolloback;
事务四大特性
● 原子性：事务是不可分割的 最小操作单元，要么全部成功，要么全部失败。
● 一致性：事务完成时，必须使所有的数据都保持一致状态
● 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行‘
● 持久性： 事务一旦提交或者回滚，它对数据库中的数据的改变就是永久的
事务四大特性ACID
并发事务问题
脏读：一个事务读到另一个事务还没有提交的数据
不可重复度：一个事务先后读取同一记录，但两次读取的数据不用，称之为不可重复读。
幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，出现幻读。
事务的隔离级别：
read uncommitted 脏读 不可重复读 幻读
read committed 不可重复读 幻读
repeatable read 幻读
serializable
查看事务隔离级别
select @@transacttion_isolation;
设置事物的隔离级别
set [session | global] transaction isolation level {
read uncommitted | read committed | repeatable read | serializable}
事务的隔离级别越高，数据越安全，但是性能越低。