MySql 复习

时间:2022-06-21 06:28:16

MySql

服务

管理员身份打开cmd

net start mysql //启动mysql服务
net stop mysql  //关闭mysql服务

登录

//用户名为root,密码为1234
mysql -uroot -p1234
mysql -uroot -p //回车再密文输入密码更安全
//远程登录
mysql -h127.0.0.1 -uroot -p
mysql --host=127.0.0.1 --user=root --password=1234  //完整写法
//退出
exit
quit
//备份
mysqldump -uroot -p1234 数据库名 > 保存路径
//还原, 登录并进入对应数据库
source 文件路径

SQL

Structured Query Language

结构化查询语言,定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为”言“,mysql中只有登录后输入的才被视为sql语句

SQL分类

  1. DDL: Data Definition Language
  2. DML: Data Manipulation Language
  3. DQL: Data Query Language
  4. DCL: Data Control Language

SQL通用语法

  1. SQL语句可以单行或多行书写,以分号结尾
  2. 可以使用空格或缩进增强语句的可读性
  3. MySql数据库的SQL语句不区分大小写,关键字建议大写
  4. 注释:单行注释 -- 注释内容 或 # 注释内容;多行注释 /* 注释内容 */

操作数据库DDL

  • Create创建
    • 创建数据库(已存在会报错)
      • create database 数据库名称;
    • 如果不存在同名数据库则创建
      • create database if not exists dbName;
    • 创建时指定字符集
      • create database dbName character set gbk;
    • 创建时既判断又指定字符集
      • create database if not exists dbName character set gbk;
  • Retrieve查询
    • 查询所有数据库的名称
      • show databases;
    • 查询某个数据库的字符集(查询某个数据库的创建语句)
      • show create database 数据库名称;
  • Update修改
    • 修改数据库字符集(不能写成uft-8)
      • alter database dbName character set utf8;
  • Delete删除
    • 删除数据库(不存在会报错)
      • drop database dbName;
    • 如果存在则删除数据库
      • drop database if exists dbName;
  • 使用数据库
    • 查询当前正在使用的数据库名称
      • select database();
    • 使用某个数据库
      • use dbName;

操作表DDL

  • Create

    • 创建表(最后一列不能加逗号)

      • table tableName(

        ? 列名1 数据类型1,

        ? ...

        ? 列名n 数据类型n

        );

    • 常见数据类型

      • int, 整型
      • doublem, double(5, 2)表示一共5位,小数点后保留2位,最大值999.99
      • date, 只包含年月日,yyyy-MM-dd
      • datetime, 包含年月日时分秒,yyyy-MM-dd HH:mm:ss
      • timestamp, 时间戳,如不赋值哐赋null,则自动存入当前系统时间
      • varchar, varchar(20)表示最大20个字符
      • 一般不把大文件放入数据库,只放文件的磁盘路径
    • 复制表

      • create table newName like tableName;
  • Retrieve

    • 查询当前数据库中所有表的名称
      • show tables;
    • 查询表结构
      • desc tableName;
  • Update

    • 修改表名
      • alter table tableName rename to newName;
    • 修改表的字符集
      • alter table tableName character set 字符集名称
    • 添加一列
      • alter table tableName add 列名 数据类型
    • 修改列名称 类型
      • alter table 表名 change 列名 新列名 新数据类型
      • alter table 表名 modify 列名 新数据类型
    • 删除列
      • alter table 表名 drop 列名
  • Delete

    • 删除表
      • drop table tableName;
      • drop table if exists tableName;

操作记录DML

  • 添加数据
    • insert into 表名(列名1, 列名2, ... , 列名n) values(值1, 值2, ... , 值n);
      • 列名和值要一一对应
      • 如果表名后不定义列名则默认给所有列添加值
      • 除了数字类型,其他类型需要使用引号(单双均可)引起来
      • 没有值的时候可以传null
  • 删除数据
    • delete from 表名 [where 条件]
      • 如果不加条件,则删除表中所有记录,执行次数太多建议使用truncate table t
    • truncate table 表名
      • 删除表中所有记录,删除表,然后创建一个一模一样的空表
  • 更改数据
    • update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where 条件];
      • 如果不加条件,则修改表中所有数据

约束

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

  • 分类

    • 非空约束:not null
    • 唯一约束:unique (不限定null)
    • 主键约束:primary key
      • 非空且唯一,一张表只能有一个主键,是表中记录的唯一标识
    • 外键约束:foreign key
      • 让表与表之间建立关系
    • 自动增长:auto_increment
      • 完成值的自动增长,传null的时候生效,可以手动赋值,自动增长只看上一条记录的值加一
  • 操作

    • 创建表时添加约束,跟在数据类型后

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

      create table stu(id int, . . . , constraint (外键名) foreign key 外键列名 references 主表名(主表列名称)); #添加外键

    • 删除约束,唯一和主键约束删除特殊

      alter table stu modify name varchar(20);

      alter table stu drop index name; #删除唯一约束

      alter table stu drop primary key; #删除主键

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

    • 创建表后添加约束

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

      alter table stu add constraint (外键名) foreign key 外键列名 references 主表名(主表列名称)); #添加外键

    • 级联操作

      • 在外键约束后加 on update cascade / on delete cascade
      • 删除或更新数据时会同时操作所有与其相关联的数据

查询记录(单表)DQL

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

  • 列表用逗号分开,可以用*代指查询所有

  • 基础查询

    • 结果去重:在字段列表前加distinct,必须所有选中字段一样
    • 结果计算:可计算类型的名字加四则运算符
      • select name, math, english, math english from stu;
    • null判断:ifnull(字段名,替换值)
      • 有null参与的计算结果为null,可以用ifnull()解决
      • select name, math, english, math ifnull(english, 0) from stu;
    • 起别名:字段名 as 别名,as可以用空格代替
      • select name, math, english, math ifnull(english, 0) as sum from stu;
      • select name, math, english, math ifnull(english, 0) sum from stu;
  • 条件查询

    • where子句后跟条件

    • 运算符

      • > 、<、<=、>=、=、<>(不等于符号,也可以用 != )

      • between...and... (也可以用and实现)

      • in(集合) (也可以用or实现)

        select * from stu where age in (15, 25, 35);

      • is null,is not null (null值不能用基本运算符判断)

      • and &&,or ||,not ! (推荐使用字母符号)

      • like (模糊查询)

        占位符:_ 表示单个任意字符;* 表示多个任意字符;

        select * from stu where name like ‘__%‘; #查询姓名至少有两个字的人

  • 排序查询

    • order by 排序字段1 排序方式1, 排序字段2 排序方式2 . . .
    • 先按字段1排序,如果字段1一样,则按字段2排序 . . .
    • 排序方式有升序ASC和降序DESC,默认按升序
  • 聚合函数

    • 把一列数据作为一个整体进行计算,结果为单行单列,如count(math)

    • count、max、 min、 sum、 avg

    • 自动排除null值,count时不方便可以用ifnull解决或选择非空的列或count(*)

      select count( ifnull( english, 0 ) ) from stu;

  • 分组查询

    • group by 分组字段;
    • 分组之后查询的字段要么是分组字段,要么是聚合函数,其他字段没有意义
    • 可以在分组之前用where进行一些筛选工作,如果不满足条件则不参与分组
    • 可以在分组之后用having进行限定,不满足条件则不会被最终查询出来
    • where后不能跟聚合函数,having后可以跟聚合函数的判断
  • 分页查询

    • limit 开始的索引 每页查询的条数
    • 公式:开始的索引 = (当前页码 - 1) * 每页显示的条数
    • limit关键字是mysql的方言

多表查询

select * from a, b; 结果是ab两张表的笛卡尔积,两个集合所有组成情况

  • 内连接查询

    查询两张表交集部分

    1. 隐式内连接:select a.name from a, b where a.bid = b.id; bid和id可以加引号

      一般在from后面的表后加别名

    2. 显式内连接:

      select * from table1 inner join table2 on 条件;

      select * from table1 join table2 on 条件;

    • 要确定查询的表,条件,字段
  • 外连接查询

    1. 左外连接(常用,可以代替右外连接)

      select * from table1 left [outer] join table2 on 条件;

      查询左表所有数据以及其与右表交集部分

    2. 右外连接

      select * from table1 right [outer] join table2 on 条件;

      查询右表所有数据以及其与左表交集部分

  • 子查询

    查询中嵌套查询,子查询加括号

    1. 子查询的结果是单行单列的
      • 作为一个值参与父查询的条件判断
    2. 子查询的结果是多行单列的
      • 作为一个集合参与父查询的条件判断
    3. 子查询的结果是多行多列的
      • 作为一张表参与父查询的from参数,需要取别名,可以用内连接代替

管理用户及授权 DCL

  • 用户和授权信息存在自带的mysql数据库中,先use mysql切换到mysql数据库,主机名为%时表示可以在任意主机上登录

  • 管理用户

    • 添加用户:create user ‘用户名‘@’主机名‘ identified by ‘密码‘;

    • 删除用户:drop user ‘用户名‘@’主机名‘;

    • 更改用户:update user set password = password(‘新密码‘) where user = ‘用户名‘;

      ? set password for ‘用户名‘@’主机名‘ = password(‘新密码‘);

    • 查询用户:select * from user;

    • 忘记管理员密码

      1. 停止mysql服务:cmd --> net stop mysql
      2. 使用无验证方式启动mysql服务:mysqld --skip-grant-tables
      3. 打开新的cmd窗口,直接输入mysql回车,就可以登录成功
      4. 修改root用户密码
      5. 关闭两个窗口,手动停止服务
      6. 启动服务,用新密码登录
  • 管理权限

    • 查询权限:show grants for ‘用户名‘@’主机名‘;

    • 授予权限:grant 权限列表 on 数据库名.表名 to ‘用户名‘@’主机名‘;

      ? 权限包括:select, delete, update

      ? grant all on 星号.星号 to ‘用户名‘@’主机名‘; (通配符)

    • 撤销权限:revoke 权限列表 on 数据库名.表名 to ‘用户名‘@’主机名‘;

事务

  • 概念:如果多个步骤被事务管理,那么这些操作要么同时成功,要么同时失败
    • 操作:
      • 开启事务:start transaction;
      • 回滚:rollback;
      • 提交:commit;
    • mysql中单条增删改事务默认自动提交手动提交需要先开户事务再提交,oracle默认手动提交
    • 查看当前提交方式 select @@autocommit; #1代表自动提交,0代表手动提交
  • 事务的四大特征
    1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
    2. 持久性:当事务提交或回滚后,数据库会持久化地保存数据
    3. 隔离性:多个事务之间相互独立
    4. 一致性: 事务的执行使数据从一个状态转换为另一个状态,但是对于整个数据的完整性保持稳定
  • 事务的隔离级别
    • 概念:多个事务同时操作同一批数据时会引发一些问题,设置不同的隔离级另就可以解决这些问题
    • 存在问题:
      • 脏读:一个事务读取到另一个事务没有提交的数据
      • 不可重复读:在同一个事务中,两次读取到的数据不一样
      • 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
    • 隔离级别(等级越高效率越低)
      • read uncommitted:读未提交,问题:脏读,不可重复读,幻读
      • read committed:读已提交,问题:不可重复读,幻读 (oracle默认)
      • repeatable read:可重复读,问题:幻读 (mysql默认)
      • serializable:串行化,问题:无
    • 设置隔离级别(需重新连接数据库)
      • set global transaction isolation level 隔离级别

数据库设计

  • 多表之间的关系

    • 一对一(少见)
      • 在任意一方建立指向另一方的主键,并使外键唯一,一般直接合成一张表
    • 一对多
      • 在多的一方建立外键,指向一的一方的主键
    • 多对多
      • 使用中间表,最少两个字段,作为外键分别指向两边的主键,中间表使用联合主键
  • 范式

    设计数据库时,需要遵循的一些规范

    • 概念

      • 函数依赖:如果通过A能确定B,则称B依赖于A,A可以是多个属性组成的属性组

      • 完全函数依赖:通过属性组A中所有的属性才能确定B

      • 部分函数依赖:cefp属性组A中部分的属性就能确定B

      • 传递函数依赖:A确定B,B确定C,则C传递依赖于A

      • 码:一张表中,一个属性或属性组被其他所有属性完全依赖,则其为该表的码

        主属性:码中所有属性

        非主属性:其他属性

    • 第一范式1NF

      每一列都是不可分割的原子项

      • 问题:1. 存在严重的数据冗余;2. 添加和删除存在问题
    • 第二范式2NF

      1NF基础上,非码属性必须完全依赖于候选码(消除部分依赖)

      • 只能解决数据冗余
    • 第三范式3NF

      2NF基础上,任何非主属性不依赖于其它非主属性(消除传递依赖)