mysql 基础复习笔记

发表于 | 分类于

SQL

一.什么是SQL?

Structured Query Language: 结构化查询语音，其实就是定义了操作所有关系型数据库的规则。每一种数据操作的方式存在不一样的地方，称为“方言”。

二.SQL通用语法

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

三.SQL分类

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

DDL 操作数据库、表

一.操作数据库：CRUE

  1. C(Create)：创建
    • 创建数据库：create database 数据库名称
    • 创建数据库，判断存不存，再创建：create database if not exists 数据库名称
    • 创建数据库，并指定字符集：create database 数据库名称 character set 字符集名
  2. R(Retrieve)：查询
    • 查询所有数据库名称：show databases
    • 查询某个数据库的字符集：查询某个数据库的创建语句：show create database 数据库名称
  3. U(Update): 修改
    • 修改数据库的字符集：alter database 数据库名称 character set 字符集名称
  4. D(Delete)：删除
    • 删除数据库：drop database 数据库名称
    • 判断数据库存在，存在在删除：drop database if exists 数据库名称
  5. 使用数据库
    • 查询当前正在舒勇的数据库名称：select database()
    • 使用数据库：use 数据库名称

二.操作表

  1. C(Create)：创建

    • 语法：
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      create table 表名(
      	列名1 数据类型1,
      	列名2 数据类型2,
      	......
      	列名n 数据类型n
      );
      * 注意：最后一列，不需要加逗号","
      * 一些常用数据库类型：
      	1. int 整数类型
      	2. double 小数类型
      	3. date 日期 只包含年月日 yyyy-MM-dd
      	4. dataTime 日期 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
      	5. timeTamp 时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss，注意:如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间来自动赋值
      	6. varchar 字符串

  2. R(Retrieve)：查询

    • 查询某个数据库中所有表名称：show tables
    • 查询表结构：desc 表名
  3. U(Update): 修改
    • 修改表名
      • alter table 表名 rename to 新的表名
    • 修改表的字符集
      • alter table 表名 character set 字符集名称
    • 添加一列
      • alter table 表名 add 列名 数据类型
    • 修改列的名称 类型
      • alter table 表名 change 列名 修改后的新列名 新的数据类型
      • alter table 表名 modify 列名 新的数据类型
    • 删除列
      • alter table 表名 drop 列名
  4. D(Delete)：删除
    • 删除表：drop table 表名称
    • 判断表存在，存在在删除：drop table if exists 表名称

DML 增删改表中数据

  1. 添加数据：

    • 语法：

      1
      insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

    • 注意：

      • 列名要和值一一对应
      • 如果表名后，部定义列名，则默认给所有列添加值: insert into 表名 values(值1,值2,…值n);
      • 除了数字类型，其它类型需要使用引号(单双引号都可以)，引起来
  2. 删除数据
    • 语法：deleter from 表名 [where 条件]
    • 注意：
      • 如果不加条件，则删除表中所有记录
      • 如果要删除所有记录
        1. delete from 表名; – 不推荐使用，因为5有多少记录就会执行多少次删除操作
        2. TRUNCATE Table 表名; – 推荐使用，效率高，先删除表，然后再创建一张一样的表。
  3. 修改数据
    • 语法：update 表名 set 列名1 = 值1,列名2 = 值2,... [where 条件]
    • 注意：
      • 如果不加任何条件，则会将表中所有的记录全部修改

DQL 查询表中的记录

一.语法

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

二.基础查询

  1. 多个字段查询
    • 语法：select 字段名1,字段名2... from 表名;
    • 注意：
      • 如果查询所有字段，则可以使用*来替代字段列表
  2. 去除重复
    • 语法关键字： distinct
  3. 计算列
    • 一般可以使用四则运算来计算一些列的值。（一般只会进行数值型的计算）
    • ifnull(表达式1,表达式2):null 参与的运算，计算结果都为null
      • 表达式1：哪个字段需要判断是否为null
      • 如果该字段为null后的替换值
  4. 起别名
    • 语法关键字: as,as也可以省略

三.条件查询

  1. where 子句后跟条件
  2. 运算符

    • ,<,<=,>=,=,<>

    • BETWEEN…AND
    • IN(集合)
    • LIKE: 模糊查询
      • 占位符：”_”单个任意字符，”%”多个任意字符
    • IS NULL
    • and 或 &&
    • or 或 ||
    • not 或 ！

四.查询语句

  1. 排序查询
    • 语法：order by 子句，order by 排序字段1 排序方式1 , ...排序字段n 排序方式n
    • 排序方式：
      • ASC 升序，默认的
      • DESC 降序
    • 注意：
      • 如果又多个排序条件，则当前边的条件值一样时，才会去判断第二条件

  2. 聚合函数：将一列数据作为一个整体，进行纵向的计算

    1. count：计算个数，一般选择非空列（主键）,或者 count(*) 有一个不为null则计数
      • 语法举例：SELECT COUNT(id) FROM student
    2. max：计算最大值
    3. min：计算最小值
    4. sum：计算和
    5. avg：计算平均值
    • 注意：聚合函数的计算，排除null值
    • 解决方案：
      1. 选择不包含空的列进行计算，比如主健
      2. IFNULL 函数SELECT COUNT(IFNULL(english,0)) FROM student

  3. 分组查询

    • 语法: group by 分组字段
    • 注意
      1. 分组之后查询的字段只能是：分组字段、聚合函数
      2. 语法举例，按照性别分组，分别查询男女同学的数学平均分,人数SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
      3. where 和 having 的区别
        1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having 在分组之后进行限定，如不满足结果，则不会被查询出来
        2. where 后不可以跟聚合函数，having 可以进行聚合函数的判断
  4. 分页查询
    1. 语法： limit 开始索引,每页查询的条数
    2. 公式： 开始的索引=（当前页码-1）*每页显示的条数

约束

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

分类：

  1. 主键约束：primary key
  2. 非空约束：not null
  3. 唯一约束：unique
  4. 外键约束：foreign key

一.非空约束: not null

  1. 创建表时添加约束

    1
    2
    3
    4
    CREATE TABLE stu(
    	id INT,
    	NAME VARCHAR(20) NOT NULL -- name为非空
    );

  2. 创建表完后，添加非空约束

    1
    ALTER TABLE stu MONDIFY NAME VARCHAR(20) NOT NULL;

  3. 删除 name 的非空约束

    1
    ALTER TABLE stu MODIFY NAME VARCHAR(20);

二. 唯一约束：unique,值不能重复

  1. 注意：唯一约束可以有NULL值，但是只能由一条记录为null

  2. 在创建表时，添加唯一约束：

    1
    2
    3
    4
    CREATE TABLE stu(
    	id INT,
    	phone_null VARCHAR(20) UNIQUE -- 手机号
    );

  3. 删除唯一约束

    1
    ALTER TABLE stu DROP INDEX phone_number;

  4. 在表创建完后，添加唯一约束

    1
    ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

三.主键约束：primary key

  1. 注意：
    1. 含义：非空且唯一
    2. 一张表只能由一个字段为主键
    3. 主键就是表中记录的唯一标识

  2. 在创建表时，添加主键约束

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

  3. 删除主键约束

    1
    2
    -- 错误 alter table stu modify id int; --
    ALTER TABLE stu MODIFY id INT PRIMARY KEY;

  4. 创建完表后，添加主键

    1
    ALTER TABLE stu MODIFY id INT PRIMARY KEY;

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

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

      1
      2
      3
      4
      create table stu(
      	in int primary key auto_increment, -- 给id添加主键约束
      	name varchar(20)
      );

    • 删除自动增长ALTER TABLE stu MODIFY id INT

    • 添加自动增长ALTER TABLE stu MODIFY id INT AUTO_INCREMENT

四.外键约束：foreign key

  1. 在创建表时，可以添加外键

    • 语法：
      1
      2
      3
      4
      5
      create table 表名(
      	...
      	外键列
      	constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称)
      );

  2. 删除外键

    1
    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

  3. 创建表之后，添加外键

    1
    ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

4.级联操作

1. 添加级联操作
    - 语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
2. 分类:
    - 级联更新：ON UPDATE CASCADE
    - 级联删除：ON DELETE CASCAAD

数据库的设计

多表之间的关系

一. 分类

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

二. 实现关系：

  1. 一对多（多对一）：

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

  2. 多对多：

    • 如：学生和课程
    • 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三者表的外键，分别指向两张表的主键

  3. 一对一（少见，一般合为一张表）：

    • 如：人和身份证
    • 实现方式：可以在任意一方添加外键指向另一方的主键，并且需要对外键进行唯一约束（unique）

数据库设计的范式

概念：在设计数据库时，需要遵循一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。

分类：

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

数据库的备份和还原

  1. 命令行：
    • 备份语法：mysqldump -u用户名 -p密码 > 保存的路径
    • 还原方式：登录数据库，创建数据库，使用数据库，执行文件：source 文件路径
  2. 图形化工具：备份/导出

多表查询

  • 笛卡尔积：

    • 有两个集合A、B,取这两个集合的所有组成情况
    • 要完成多表查询，需要消除无用的数据

  • 多表查询的分类：

    • 内连接查询

      1. 隐式内连接：使用 where 条件消除无用数据

        1
        2
        3
        4
        5
        6
        7
        8
        9
        SELECT
        	t1.name,
        	t1.gender,
        	t2.name
        FROM
        	emp t1,
        	dept t2
        WHERE
        	t1.'dept_id' = t2.'id'

      2. 显示内连接

        • 语法： select 字段列表 from 表名1 inner join 表名2 on 条件
          1
          SELECT * FROM emp [INNER] JOIN dept ON emp.'dept_id' = dept.'id' -- INNER可省略

      3. 内链接查询：

        1. 从哪些表中查询数据
        2. 条件是什么
        3. 查询哪些字段
    • 外连接查询
      1. 左外连接
        • 语法： select 字段列表 from 表1 left [out] join 表2 on 条件;
        • 查询的是左表所有数据以及其交集部分
      2. 右外连接
        • 语法： select 字段列表 from 表1 right [out] join 表2 on 条件;
        • 查询的是右表所有数据以及其交集部分
    • 子查询：查询中嵌套查询，称嵌套查询为子查询
      • 案例：查询工资最高的员工信息
        • 查询最高工资是多少（9000）：SELECT MAX(salary) FROM emp;
        • 查询员工信息，并且工资等于9000的：SELECT * FROM emp WHERE emp.’salary’ = 9000
        • 利用子查询，一条 sql 完成：SELECT * FROM emp WHERE emp.’salary’ = (SELECT MAX(salary) FROM emp);
      • 子查询不同情况
        1. 子查询的结果是单行单列的：
          • 子查询可以作为条件，使用运算符去判断。运算符：> >= < <= =
          • 查询员工工资小于平均工资的人
          • SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
        2. 子查询结果是多行单列的：
          • 子查询可以作为条件，使用运算符 in 来判断
          • 查询’财务部’和’市场部’所有员工的信息
          • SELECT id FROM emp WHERE NAME = ‘财务部’ OR NAME = ‘市场部’;
          • SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
          • 子查询：SELECT * FROM emp WHERE dept_id IN (SELECT id FROM emp WHERE NAME = ‘财务部’ OR NAME = ‘市场部’);
        3. 子查询是的结果是多行多列的：
          • 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
          • 子查询的结果集可以作为一张虚拟表，来进行一个表的查询
          • SELECT FROM dept t1 , (SELECT FROM emp WHERE emp.’join_date’ > ‘2011-11-11’) t2 WHERE t1.id = t2.dept_id;
          • 普通内连接也能实现： SELECT * FROM emp t1,dept t2 WHERE t1.’dept_id’ = t2.’id’ AND t1.’join_date’ > ‘2011-11-11’;