MySQL笔记

DDL

1.操作数据库 : CRUD

C(create) : 创建的操作

• create database 名称;
• 防止重复创建( create database if not exists 数据库名称 )
• 指定字符集( create database 数据库名称 character set 字符集名称 )

R(retrieve) : 查询

• 查询所有数据库的名称( show databases; )
• 查看如何创建表( show create database mysql/数据库名称; )

U(update) : 修改

• 设置字符集( alter database 数据库名称 character set 字符集名称 )

D(dalete) : 删除

• 删除数据库( drop database 数据库名称; )
• 判断数据库是否存在并删除( drop database if exists 数据库名称 )

使用数据库

• 查询当前正在使用的数据库名称( select database (); )
• 使用数据库( use 数据库名称 )

2.操作表

C(创建)

• 创建表( create table 表名(

  ​ 列名1 数据类型1( age int, )，

  ​ 列名2 数据类型2( score double(5,2) )

  ​ ( data yyyy-MM-dd / datatime yyyy-MM-dd HH:mm:ss )

  ​ ( timestamp:yy-MM-dd 时间错类型,默认使用当前的系统时间自动赋值 )

  ​ ( varchar : 字符串类型 name varchar(20) )

  ​ ) ；)

  例子: create table student(

  ​ id int,

  ​ name varchar(32),

  ​ age int,

  ​ score double(4,1),

  ​ insert_time timestamp

  ​ );

  • 创建类似表 (复制表): create table 新表名 like 旧表名

R(查询)

• 查询每个数据库里所有表的名称( show tables; )
• 查询表结构( desc 表名; )

U(修改)

• 修改表名( alter table 表名 rename to 新表名; )
• 修改表的字符集为utf8( alter table 表名 character set utf8; )
• 添加一列( alter table 表名 add 列名 数据类型 ; )
• 修改列名( alter table 表名 change 原列名 新列名 类型; )
• 修改类型( alter table 表名 modify 列名 类型 )

D(删除)

• 删除表( drop table (if exists) 表名; )
• 删除列( alter table 表名 drop 列名; )

DML:增删改表表的数据

• 添加数据
  • 语法: insert into 表名(列名1,…列名n) values(值1,… 值n);
    • 列名和值要一一对应
    • 如果表名后,不定义列名,则默认给所有的列添加值（insert into 表名 values(值…);）
    • 除了数字类型，其他类型需要使用引号。
• 删除数据
  • delete from 表名 where 名称=…;
  • truncate table stu; --删除表后再创建一个一模一样的空表。
• 修改数据
  • update 表名 set 列名1 = 值1，列名2 = 值2… where 条件;
    • 不加条件会把表里的条件全部修改

DQL:查询表中的记录

• select * from 表名;

• select [字段列表]

  • from [表名列表]
    • where[条件列表]
      • group by[分组字段]
        • having [分组后的条件]
          • order by[排序] limit[分页]；

• 基础查询

  • 多个字段的查询
    • select 字段1，字段2 from 表名;
  • 去除重复
    • distinct
  • 计算列
    • 字段后面加数学表达式( ifnull(原，替换值) );
  • 起别名
    • 关键字AS AS 别名 或者 别名

• 条件查询

  • where子句后加条件

  • 运算符

    • “> ,<,<=,>=,=,<>(不等于)”

      between…and

      like:模糊查询> _:单个任意字符 %:多个任意字符

      is (not) null

      and 或 &&

      or 或 ||

      not 或 !

• 查询语句的使用

  • 排序查询
    • order by 排序字段1 排序方式1,排序字段2 排序方式2;
    • 默认升序排列asc desc降序排列
    • 如果有多个排序条件,则当前边的条件值一样时,才会判断第二条件。
  • 聚合函数
    • 将一列数据作为整体，进行纵向的计算。（求平均分）
    • 聚合函数会排除null;
    • count : 计算个数
      • 一般使用非空的列
    • max/min : 最大/小值
    • sum : 计算和
    • avg : 计算平均值
  • 分组查询
    • group by 分组字段;
    • 分组后查询：分组字段，聚合函数。
    • 限定分组条件 : group by 前面加 where 条件
    • 最后面加 having 可以进一步(分组之后)限定
  • 分页查询( 方言，不同数据库不一样 )
    • 语法 ：limit 开始的索引，每页查询的条数
    • 每页开始的索引 = （当前的页码-1）× 每页显示的条数

• 约束

  • 概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性
    • 值的自动增长
      • 概念，如果某一列是数值类型的，使用 auto_increment 可以完成自动增长
      • 在创建表时，添加主键约束，并且完成主键自增长
        • create table stu(
        • ​ id int primary key auto_increment
        • );
      • 自动增长只和上一条有关
      • alter table 表名 modify 字段名 字段类型;
  • 约束：
    • 主键约束：primary key
      • 含义：非空且唯一
      • 一张表只能有一个字段为主键
      • 主键就是表中记录的唯一标示
      • 在创建表时使用 primary key ;–添加主键约束
      • 创建完表后添加主键：alter table 表名 modify 字段名 字段类型 primary key;
      • 删除主键约束：alter table 表名 drop primary key;( 主键的唯一性 )
    • 非空约束：not null 这一列的值不能为空
      • 创建表时在表后加 not null 关键字
      • 表创建后添加约束 alter table 表名 modify 列名 类型 not null;( 不加not null去除唯一约束 )
    • 唯一约束：unique 值不能重复
      • 使用 unique 修饰添加唯一约束
      • alter table 表名 modify 列名 类型 unique;（ 不加unique可以去除唯一约束 ）
      • ---------可以为多个null
    • 外键约束：foreign key 让表与表产生关系，从而保证数据的正确性。
      • 在创建表时添加外键约束：
      • constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
      • 删除外键约束： alter table 表名 drop foreign key 外键名称;
      • 添加外键约束：alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)；
    • 级联操作：
      • alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on update cascade on delete casecade；
      • on update cascade：级联更新
      • on delete cascade ：级联删除

• 数据库的设计

  • 多表之间的关系
    • 1对1：人和身份证：在任意一方添加唯一外键指向另一方的主键。或者直接一张表。
    • 1对多/多对1：部门和员工:在多的一方建立外键指向1的地方的主键 1 o-o / o-o 1
    • 多对多：学生和课程：一个学生可以选多门课程，一个课程可以被多个学生选择 1 [o-o o-o] 1
      • 需要中间表:中间表至少包含俩个字段，这俩个字段作为第三张表的外键，分别指向俩张表的主键。
  • 范式
    • 在设计数据库时需要遵循的一些规范。
    • 1NF：每一列都是不可分割的原子数据项
      • 问题：1.数据冗余严重，2.数据添加存在严重问题，3.数据删除存在严重问题。
    • 2NF：在1NF的基础上，非码属性必须依赖于候选码（在1NF的基础上消除非主属性对主码的部分函数依赖）
      • 属性的确定关系
      • 主属性：码属性组中的所有属性
      • 非主属性：除过码属性组的属性
      • 码：如果一个属性或属性组被其他属性所完全依赖，则称这个属性组为该表的码
      • 仅仅解决了第一范式
    • 3NF：在2NF的基础上，任何非主属性不依赖与其他非主属性（在2NF基础上消除传递依赖）

• 数据库的备份与还原

  • 命令行：
    • mysqldump -u用户名 -p密码 要备份的数据库 > 保存的路径;( 备份语法 )
    • 还原
      • 登录数据库
      • 创建数据库
      • 使用数据库
      • 执行文件。source 文件路径;
  • 图形化工具

• 多表查询

  • 有俩个集合A,B,取这俩个集合的所有组成情况
  • 要完成多表查询，需要消除无用的数据
  • 内连接查询{ 哪些表？哪些条件？哪些字段？}
    • 隐式内连接：使用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 表名 where 数据 = （select 聚合函数 from 表名）;
    • 结果：
      • 单行单列：
      • 多行单列：使用运算符in判断
      • 多行多列：作为虚拟表参与查询

• 事务

  • 事物的基本介绍（银行转账）
    • 概念：如果一个包含多个步骤的业务操作，被事物管理，那么这些操作要么同时成功，要么同时失败。
    • 操作：开启事物：start transaction
    • 操作：回滚：rollback
    • 操作：提交：commit
  • 事物的四大特征
    • 原子性：是不可分隔的最小操作单位，要么同时成功，要么同时失败
    • 持久性：当事物提交或回滚后，数据库会持久化的保存数据
    • 隔离性：多个事物之间，相互独立
    • 一致性：事物操作之后，数据总量不变
  • 事物的隔离级别（了解）

• DCL( 控制权限，管理用户 )（DBA数据库管理员）

  • 管理用户：use myql
    • 添加用户：create user “用户名”@“主机名” identified by “密码”;
    • 删除用户：drop user “用户名”@“主机名”;
    • 修改用户密码：update use set password = password(“新密码”) where user = “用户名”;
      • ​ set password for “用户名”@“主机名” = password(“新密码”);
    • 查询用户：
  • 授权：
    • 查询权限：show crants for “用户名”@“主机名”;
    • 授予权限：grant 权限列表 on 数据库名.表名 to “用户名”@“主机名”;
    • 撤销权限：revoke 权限列表 on 数据库名.表名 from “用户名”@“主机名”;