【数据库】数据库基础1

如何调整字体：

安装成功！数据库之旅开启！

---

数据库操作

• 查看数据库

  show databases;
  

• 创建数据库

  create database [if not exists] db_name [character set xxx]
  

  • [if not exists]如果数据库已经存在，就不再用创建；否则，就创建。加上这一选项，即使出现同名，也不会报错

• 删除数据库

  drop database [if exists] db_name;

• 选中数据库

  use database [if exists] db_name;

数据类型

• 正常情况下，不要使用无符号，无符号类型容易溢出
• 单精度浮点型和双精度浮点型中（M,D)分别表示：有效数字的长度，小数点后的位数
• 单精度浮点型和双精度浮点型并不能完全准确表示小数

数据表操作

• 查看数据表

  desc tab_name;

• 创建数据表

  create table [if not exists] db_name [character set xxx] (

  ​ field1 type[完整性约束条件],
  ​ field2 type, ​ ... ​ fieldn type

  );

  • [if not exists]如果数据库已经存在，就不再用创建；否则，就创建。加上这一选项，即使出现同名，也不会报错

• 删除数据表

  drop table [if exists] db_name;

• 选中数据表

  use table [if exists] db_name;

增删查改

增加数据

insert into student values(1,'abe','1021634732@qq.com');

• 里面的数据要求和表的列一一对应

• 指定列插入（没有指定的为NULL）：

  insert into student (id,qq_mail) values (2,'1234@qq.com');
  

• 插入多行数据：

  insert into student values(3,'张三','11111'),(4,'李四','22222');
  

查看数据

• 全列查询（谨慎使用！）

  select * from exam_result;

  

• 指定列查询

  select[列名],[列名]from[表名];

  根据列的名字，来决定查哪些列。我们可以使用这个方法来查询。

  

  select操作得到的结果不会保存到磁盘上，它得到的只是一个临时表。

• 查询字段为表达式

  select name,chinese+math+english from exam_result;

  通过表达式来对查询结果进行一些计算。
  

  要注意的是：相加得到的结果，不受原来的列的数据类型的限制。

  • 通过表达式字段，我们可以对表起一个别名：(as可以省略)

    select name,chinese+math+english as total from exam_result;

    

• 去重查询

  select distinct math from exam_result;

指定多个列的时候，要求这些列都相同才是一个重复纪录

• 排序order by

  对于数据库来说，归并排序是最适合的。

  按升序排序：asc

  按降序排序：desc

• 条件查询：WHERE

  

  • 查询英语不及格的人：
    

  执行select的时候，会先尝试取遍历表中的每一条记录，遍历纪录的时候，会针对当前这个记录带入到比较条件中，来查看条件是否成立，成立的结果就保留下来。放到临时表里面，不成立的直接PASS

• 模糊查询
  

修改数据

• update 表名 set 列名 = 值 where条件

  

删除数据

• delete

  

• delete from
  

  一条一条删除。

其他操作

• limit

  SELECT * from emp limit 1;

  

  SELECT * from emp limit 2,5;
  

  ​

​

增删改查（进阶）

约束关系

• unique：当列有unique约束的时候，进行插入之前，就会先查找，如果当前值存在，此时就会插入失败。

  

• default：指定列的默认值

  在指定列插入的时候，如果这个列没有被指定，就会被设置为默认值，默认的默认值就是NULL

通过指定列插入方式忽略的列，就会被默认填为NULL

• primary key：表示了一个记录的身份标识（主键约束）相当于唯一&&not null

  

  一张表里面只能有一个主键

  • 自增主键：

    

    值就交给自增主键来自动生成，并不是最终存的就是null

    

• foreign key...references：外键约束（外键约束能够把两个表绑定在一起）

  当前表里的内容必须包含在references指向的表里面。

  下面的例子就不能插入成功：（原因：6没有包含在班级表里面）
  

  班级表：
  

外键约束会导致父表中的记录不能删除，但是有的时候又确实需要删除，我们应该如何操作呢？

引入一个新的字段，通过这个字段表示这个记录是有效还是无效，默认这个字段为1，表示有效，如果需要删除，就直接把这个字段该为0，此时就表示这个商品无效了。

• 这种删除没有直接删除数据库中的记录，同时能够标记出数据的无效状态，这就是“逻辑删除”。

设计模式

• 一对一关系

• 一对多关系

  • 第一种设计方式：

  学生表：

  学号	姓名
  1	张三
  2	李四
  3	王五

  班级表：

  班级ID	班级名称	学生ID
  1	c++	1,2
  2	java	3

  • 第二种设计方式

    学生表：

    学号	姓名	班级ID
    1	张三	1
    2	李四	1
    3	王五	2

    班级表：

    班级ID	班级名称
    1	c++
    2	java

    第一种设计方式依赖了“数组”，MySQL不支持这种数组类型

    我们更多使用第二种设计方式。

• 多对多关系

  需要引入一个中间表。

  • 学生表：

    学号	姓名
    1	张三
    2	李四
    3	王五

    课程表：

    课程ID	课程名字
    1	数学
    2	语文
    3	英语

    学生/课程表（中间表）：

    学号	课程ID
    1	1
    1	2
    2	2
    2	3
    3	1

查询操作

• 将查询结果和新增操作结合起来

  此处需要保证查询结果得到的列，和前面待插入的表的列要对应。

• 聚合查询

  函数	说明
  COUNT(列名)	统计行的个数(NULL不计入结果)
  SUM(列名)	统计满足条件的行的内容和
  AVG(列名)	统计平均值
  Max	统计最大值
  Min	统计最小值

  

  语法错误会出现警告warnings

  查看警告：show warnings

  • group by：分组函数

    注意,按分组条件分组后每一组只会显示第一条记录

    

    • 与聚合函数结合在一起：

      练习：计算每个职位的平均薪资：

      

      筛选条件：

      分组前：where

      

      分组后：having

      练习：计算每个职位的平均薪资，显示大于10000元的：

      

      区别：

      分组前，使用where，需要写到group by 前面

      分组后，使用having，写到group by 后面

      同时使用where和having：
      

      其执行过程：

      1. 遍历表，将所有name!='张三’的结果筛选出来
      2. 按照role分组，分为四个组
      3. 再分别计算每个组的avg
      4. 再根据having的条件，对最终结果再来一次遍历筛选

• 联合查询（多表查询）- 基于笛卡尔积展开

  练习：通过一系列操作使得许仙每门课的成绩显示出来。

联合查询的本质就是“先做加法，再做减法”

注意：筛选条件要使两个表之间有一个带关联关系的ID