MySQL登录

• mysql -u root -p 密码
• mysql -h ip -u root -p 连接目标的密码
• mysql --host=op --user=root --password=连接目标的密码

MYSQL退出

• exit
• quit

MySQL目录结构

MySQL安装目录

• 配置文件 mysql.ini

MySQL数据目录

几个概念

• 数据库：文件夹
• 表：文件
• 数据：数据

什么是SQL

• Structured Query Language：结构化语言
• 定义了操作所有关系型数据的规则。每一种数据库的操作存在不一样的地方，称为“方言”。

SQL通用语法

• SQL 语句可以单行或者多行书写，以分号结尾。
• 可使用空格和缩进来增强语句的可读性。
• MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
• 3种注释
  • 单行注释：-- 注释内容 //两个横杠一个空格之后加注释内容
  • #注释内容
  • 多行注释：/注释/

SQL的分类

• DDL(Data Definition Language)：数据定义语言，用来定义数据库对象：数据库，表，列等。关键字：create, drop, alter 等。
• DML(Data Manipulation Language)：数据操作语言，用来对数据库种表的数据进行增删改。 关键字：insert, delete, update 等。
• DQL(Data Query Language)：数据查询语言，用来查询数据库中的表的记录（数据）。关键字：select where 等。
• DCL(Data Control Language)：数据控制语言（了解），用来定义数据库的访问权限和安全级别，以及创建用户。关键字：GRANT,REVOKE等。

DDL

操作数据库：CRUD

• C(Create)：创建
  • 创建数据库：
    • create database 数据库名称；
  • 创建数据库，判断不存在，在创建：
    • create database if not exists 数据库名称；
  • 创建数据库，并指定字符集：
    • create database 数据库名称 character set 字符集名称
  • 联系：创建db4数据库，判断是否存在，并指定字符集为gbk
    • create database if not exists db4 character set gbk;
• R(Retrieve)：查询
  1. 查询所有数据库的名称：
     show datebases;
  2. 查看某个数据库的字符集：
     show create database 数据库名；
• U(Update)：修改
  • 修改数据库的字符集
    • alter database 数据库名称 character set 字符集名称；
• D(Delete：)删除
  • 删除数据库
    • drop database 数据库名称；
  • 判断数据库存在，存在再删除
    • drop database if exists 数据库名称；
• 使用数据库
  • use 数据库名称；

操作表

C(Create):创建

• 创建
  create table 表名(
  列名1 数据类型1，
  列名2 数据类型2，
  …
  列名n 数据类型n

  ); //最后一列不加逗号

• 数据库类型

  1. int:整数类型
     age int,
  2. double:小数类型“
     score double(5,2),
  3. date:日期，只包含年月日，yyyy-MM-dd
  4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:dd
  5. timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
  6. varchar:字符串类型
     name varchar(20),姓名最大20个字符

• 复制表

  • create table 表名 like 被复制的表名

R(Retrieve):查询

• 查询数据库中所有的表的名称
  show tables
• 查询表结构
  desc 表名

U(Update):修改

• 修改表名
  alter table 表名 rename to 新的表名;
• 修改表的字符集
  alter table 表名 character set 字符集名称；
• 修改列名称 类型
  alter table 表名 change 列名 新的列名 新的数据类型；
  alter table 表名 modify 列名 数据类型
• 删除列
  alter table 表名 drop 列名;

D(Delete):删除

• 删除表
  • drop table 表名；
  • drop table if exists 表名；

DML:增删改表中数据

添加数据：

• 语法：
  • insert into 表名(列名1，列名2，列名3，…，列名n) values(值1，值2，值3，…，值n);
• 注意
  • 列名和值要一一对应
  • 如果表名后，不定义列名，则默认给所有列添加值
    insert into 表名 values(值1，值2，…，值n)

删除数据

• 语法：
  • delete from 表名[where 条件]
• 注意：
  • 如果不加条件，则删除表中所有的记录。
  • 如果要删除所有记录
    • delete from 表名；//推荐使用，有多少条记录就会执行多少次删除操作
    • TRUNCATE TABLE 表名； //推荐使用，效率更高，先删除表，然后创建一张一模一样的表

修改数据

• 语法：
  • update 表名 set 列名1 = 值1，…，列名2 = 值2[where 条件]；
• 注意：
  • 如果不加任何条件 ，则会将表中所有记录全部修改

DQL:查询表中的数据

语法

select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定

基础查询

• 多个字段的查询

  ​ select 字段名1，字段名2，…from 表名;

• 去除重复

  ​ select distinct 字段名1，字段名2，…from 表名;

  • 重复是只的所有字段全部相同

• 计算列

  • 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的运算）
  • ifnull（表达式1，表达式2）：null参与的运算，计算结果都为null
    • 表达式1：哪个字段需要判断是否为null
    • 如果该字段为null后的替换值。

• 起别名

  • as：as可以用空格代替

条件查询

• where子句后面跟条件
  • <=、>=、>、<、=、<>
    • select 列名 from 表名 where 属性名 符号 属性值;
    • select name from student where age > 18;
  • between…and
    • select 列名 from 表名 where 属性名 between 属性值 and 属性值;
    • select name from student where student.age BETWEEN 10 AND 30;
    • 包含端点
  • in(集合)
    • select 列名 from 表名 where 属性名 in (集合);
    • select * from student where age in (21,30);
  • like：模糊查询
    • 占位符
      • ‘_’： 任意单个字符
      • ‘%’：任意多个字符
      • select name from student where name like ‘张%’;
  • is null
    • select 列名 from 表名 where 属性名 is null;
    • select name from student where score is null;
  • and 或 &&
  • or 或 ||
  • not 或 ！

排序查询

• 语法：order by 语句;
  • order by 排序字段1 排序方法1，排序字段2 排序方法2…;
• 排序方式
  • ASC:升序 默认的
  • DESC:降序
• 注意：
  • 如果有多个排序条件，则当第一个条件相同时，才会判断第二个条件。

聚合函数

• 定义
  • 将一列数据作为整体，进行纵向的计算。
• count：计算个数
  • 一般选择不包含非空的列：主键
  • count(*)
• max：计算最大值
• min：计算最小值
• sum：计算和
• avg：计算平均值
• 注意
  • 聚合函数的计算，排除null值
  • 解决方案
    • 选择不包含非空的列
    • IFNULL函数

分组查询

• 语法：group by 分组字段；

• 注意：

  • 分组后查询的字段：分组字段，聚合函数
  • where 和 having 的区别
    • where 在分组前进行限定，如果不满足条件，则不参与分组。havaing 在分组后进行限定，如果不满足条件，则不会被查询出来
    • where 后不可以跟聚合函数，having可以进行聚合函数的判断

• 代码示例：

  select sex, AVG(math) from student group by sex;select sex AVG(math), COUNT(id) from student where math > 70 group by sex ;select sex AVG(math), COUNT(id) from student where math > 70 group by sex having COUNT(id) > 2;
  

分页查询

• 语法
  • limit 开始的索引，每页查询的条数；
• 公式
  • 开始的索引= （当前页码-1）*每页显示的条数
• 代码示例
  • select * from student LIMIT 0, 3;
• 注意：不同的数据库实现分页的操作不一样

约束

概念

​ 对表中的数据进行限定，保证数据的正确性，有效性和完整性。

分类

• 主键约束：primary key 值不能为空

  • 注意

    • 含义：非空且唯一
    • 一张表只能有一个字段为主键
    • 主键就是表中记录的唯一标识

  • 在船舰表时添加主键约束

    create table stu(id int primary key, -- 给id添加主键约束name varchar(20)
    );
    

  • 删除主键

    • alter table stu drop primary key;

  • 创建完表后，添加主键

    • alter table stu modify id int primary key;

  • 自动增长

    • 概念：如果某一列是数字类型的，使用 auto_increment 可以来完成完成值的自动增长

    • 创建表时，添加主键约束，并且自动增长

      create table stu(id int primary key AUTO_INCREMENT, -- 给id添加主键约束name varchar(20)
      );
      

    • 删除自动增长：alter table stu modify id int;

    • 添加自动增长：alter talble stu modify id int auto_increment;

• 非空约束：not null

  • 创建表的时候添加

    create table stu(id int,name varchar(20) not null -- name非空);
    

  • 创建表后，添加非空约束

    alter table stu modify name varchar(20) not null;
    

  • 删name非空约束

    alter table stu modify name varchar(20);
    

• 唯一约束：unique

  • 创建表时，添加唯一约束

    create table stu(id int,name varchar(20) UNIQUE -- name唯一);
    

  • 在创建表后，添加唯一约束

    • alter table stu modify name varchar(20) UNIQUE;

  • 注意：mysql中，唯一约束限定的列的值可以有多个null

  • 删除唯一约束

    • alter table stu drop index name;

• 外键约束：foreign key，让表与表产生关系，从而保证数据的正确性

  • 在创建表的时候，可以添加外键

  • 语法
    create table 表名{

    …

    外键列，

    constraint 外键名称 foreign key (外键名称) references 主表名称(主表列名称)

    };

  • 删除外键
    alter table drop foreign key 外键名称；

  • 创建表之后，添加外键
    alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

  • 级联操作

    • 添加级联操作
      1. 代码示例
         alter table employee add constraint emp_dept_fk foreign key (dep_id) references department(id) on update cascade on delete cascade;
      2. 语法
         alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;
      3. 分类：
      4. 级联更新：on update cascade
      5. 级联删除：on delete cascade
      6. 注意：在实际开发中慎用级联操作

数据库设计

多表之间的关系

• 一对一（了解）：
  • 如：人和身份证
  • 分析：一个人只有一个身份证，一个身份证只能对应一个人
• 一对多（多对一）：
  • 如：部门和员工
  • 分析：一个部门有多个员工，一个员工对应多个部门
• 多对多：
  • 如：学生和课程
  • 分析：一个学生可以选择很多们课程，一个课程也可以被很多学生选择

实现多表之间的关系

• 一对多（多对一）：
  • 如：部门和员工
  • 实现方式：在多的一方建立外键，指向一的一方主键

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qUZD9nmq-1591261337506)(Image\表的一对多关系.png)]

• 多对多：
  • 如：学生和课程
  • 实现方式：创建一个中间表，进行两个表的对应

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7HuuaAVz-1591261337514)(Image\表的多对多关系.png)]

• 一对一（了解）：
  • 如：人和身份证
  • 实现方式：在任意一方添加唯一外键指向另一方的主键

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fLHlBZhD-1591261337521)(Image\表的一对一关系.png)]

数据库设计的范式

概念

​ 设计数据库时，需要遵守的一些规范。要遵循后面的范式要求，必须先遵循前面所有范式要求

​ 设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

分类

1. 第一范式：每一列都是不可分割的原子数据项
2. 第二范式：在1NF的基础上，非码属性必须完全依赖于码（在1NF的基础上消除非主属性对主码的部分函数依赖）
   • 几个概念
     1. 函数依赖：
        1. A—>B，如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖与A
        2. 例如：学号—>姓名。 （学号，课程名称）---->分数
     2. 完全函数依赖：
        1. 如果A是一个属性组，则B属性值确定只需要依赖于A属性组中所有属性的值。
        2. 例如：（学号，课程名称 ）-- >分数
     3. 部分函数依赖：
        1. 如果A是一个属性组，则B属性值确定只需要依赖于A属性组中的某一些值即可。
        2. 例如：（学号，课程名称 ）-- >姓名
     4. 传递函数依赖
        1. A–>B, B–>C, 如果通过A属性（属性组）的值，可以唯一确定B属性的值，通过B属性（属性组）的值，可以唯一确定C属性的值。
        2. 例如：学号–>系名 系名–>系主任
     5. 码：
        1. 如果在一张表中，一个属性或属性组，被其他所有属性完全依赖，则称这个属性（属性组）为该表的码。
        2. 例如：该表中码为：（学号，课程名称）
           主属性：码属性组中的所有属性
           非主属性：除码属性组的属性
3. 第三范式：在2NF基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）
4. 巴斯-科德范式：
5. 第四范式：
6. 第五范式：

数据库的备份与还原

1. 语法
   1. 备份：mysqldump -u用户名 -p密码 >保存的路径
   2. 还原：
      1. 登录数据库
      2. 创建数据库
      3. 使用数据库
      4. 执行

多表查询

查询语法

select

​ 列名列表

from

​ 表名列表

where…

多表查询的分类

1. 概念：

   1. 笛卡尔积：有两个集合A,B去这两个集合所有的组成情况

2. 内连接查询：

   1. 隐式内连接：使用where条件来消除无用的数据
   2. 显示内连接：select 字段列表 from 表名1 [inner] join 表名2 on 条件
   3. 例如：
      1. select * from emp inner jion dept on emp.‘dept_id’ = dept.‘id’;
      2. select * from emp jion dept on emp.‘dept_id’ = dept.‘id’;

3. 外连接查询：

   1. 左外连接：
      1. 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
      2. 查询的是左表的所有数据机器交集部分
   2. 右外连接
      1. 语法：select 字段列表 from 表1 右 [outer] join 表2 on 条件；
      2. 查询的是右表所有数据及其交际部分

4. 子查询：

   1. 概念：查询中嵌套查询，成嵌套查询为子查询

   2. 子查询的不同情况

      1. 子查询的结果是单行单列的：

         1. 子查询可以作为条件，使用运算符去判断
         2. 示例：
            1. select * from emp where emp.salary < (select avg(salary) from emp);

      2. 子查询的结果是多行单列的：

         1. 子查询可以作为条件，使用运算符in来判断
         2. 示例：
            1. 一般查询：
               1. select id from dept where name = ‘财务部’ or name = ‘市场部’;
                  select * from emp where dept_id =3 or dept_id = 2;
            2. 子查询：
               1. select * from emp where dept_id in (select id from dept where name in (‘财务部’ ,‘市场部’));

      3. 子查询的结果是多行多列的：

         1. 概念：子查询可以作为一张虚拟表参与查询

         2. 示例：查询员工入职日期是2011-11-11日之后的员工信息和部门信息

            1. 子查询
               select * from dept t1, (select * from emp where emp.‘join_date’ > ‘2011-11-11’) t2 where t1.id = t2.dept_id;

            2. 普通内连接
               select *from emp t1, dept t2 where t1.‘dept_id’ = t2.‘id’ and t1.‘join_date’ > ‘2011-11-11’;

事务

事务的基本介绍

1. 概念：
   如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
2. 操作：
   1. 开启事务：start transaction;
   2. 回滚：rollback;
   3. 提交：commit;
   4. MySQL数据库中事务默认自动提交
      1. 事务提交的两种方式：
         1. 自动提交
            1. mysql就是自动提交的
            2. 一条DML（增删改）语句会自动提交一次事务
         2. 手动提交
            1. Oracle数据库默认是手动提交事务
            2. 需要先开启事务在提交
         3. 修改事务的默认提交方式：
            1. 查看事务的默认提交方式：SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
            2. 修改默认提交方式：set @@autocommit = 0;
3. 事务的四大特征：
   1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
   2. 持久性：当事务提交或者回滚后，数据库会持久化的保存数据
   3. 隔离性：多个事务之间。相互独立。
   4. 事务操作前后，数据总量不变
4. 事物的隔离级别（了解）：
   1. 概念：多个事务之间隔离的，相互独立。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题
   2. 存在问题：
      1. 脏读：一个事务读取到另一个事务中没有提交的数据
      2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
      3. 幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
   3. 隔离级别：
      1. read uncommitted:读未提交
         1. 产生问题：脏读，不可重复读，幻读
      2. read committed：读已提交
         1. 产生问题：不可重复读，幻读
      3. repeatable read: 可重复读（MySQL默认）
         1. 产生问题：幻读
      4. serializable:串行化
         1. 可以解决所有的问题
      5. 注意：
         1. 隔离级别从小到大安全性越来越高，但是效率越来越低
         2. 数据库查询隔离级别
            1. select @@tx_isolation;
         3. 数据库设置隔离级别
            1. set global transaction isolation level 级别字符串;

DCL:管理用户，授权

管理用户

1. 添加用户：
   1. 语法：create user ‘用户名’@‘主机名’ identified by ‘密码’;
2. 删除用户：
   1. 语法：drop user ‘用户名’@‘主机名’;
3. 修改用户密码：
   1. 语法：upadate user set password = password(‘新密码’) where user = ‘用户名’;
   2. set password for ‘用户名’@‘主机名’ = password(‘新密码’);
   3. mysql中忘记root用户密码
      1. net stop mysql 停止mysql服务 //需要管理员权限运行cmd
      2. 使用五验证方式启动mysql服务
         mysqld --skip-grant-tables
      3. 打开新的cmd窗口直接输入mysql命令，敲回车，直接进入mysql
      4. use mysql
      5. upadate user set password = password(‘新密码’) where user = ‘用户名’;
      6. 在任务管理器手动关闭mysqld
      7. 启动mysql服务使用新密码登录
4. 查询用户：
   1. 切换到mysql数据库
      use mysql；
   2. 查询user表
      select * from user;
   3. 通配符：%可以表示在任意主机使用用户登录数据库

权限管理

1. 查询权限：
   1. 查询权限
      show grants for ‘用户名’@‘主机名’;
2. 授予权限：
   1. grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
      给用户授予所有库的所有权限
      grant all on “* . *”(没有引号，不用空格) to ‘用户名’@‘主机名’;
3. 撤销权限：
   1. revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;