数据库知识点汇总

1.什么是数据库

存储数据的仓库，其本质是一个文件系统，数据库按照特定格式将数据存储起来，用户可以对数据库中的数据进行增加、删除、修改、查询操作。

2.常见数据库

MYSQL/Oracle/DB2/SQLServer/SyBase/SqLite

3.什么是SQL

结构化查询语言（Structured Query Language）简称SQL，SQL语句就是对数据进行操作的一种语言。

4.DDL操作数据库

1. 直接创建数据库

   CREATE DATABASE 数据库名;

2. 判断是否存在并创建数据库

   CREATE DATABASE IF NOT EXISTS 数据库名;

3. 创建数据库并指定字符集(编码表)

   CREATE DATABASE 数据库名 CHARACTER SET 字符集;

4. 查看所有的数据库

   SHOW databases;

5. 查看某个数据库的定义信息

   SHOW CREATE DATABASE 数据库名;

6. 修改数据库字符集格式

   ALTER DATABASE 数据库名 CHARACTER SET 字符集;

7. 删除数据库

   DROP DATABASE 数据库名;

8. 查看正在使用的数据库

   SELECT DATABASE();

9. 使用/切换数据库

   USE 数据库名;

5.DDL操作表

• 1.创建表

  CREATE TABLE 表名 (
  字段名1 字段类型1,
  字段名2 字段类型2
  );
  CREATE TABLE student (
  id INT,
  name VARCHAR(20),
  birthday DATE
  );

• 2.查看某个数据库中的所有表

   SHOW TABLES;
  

• 3.查看表结构

   DESC 表名;
  

• 4.查看创建表的SQL语句(掌握)

   SHOW CREATE TABLE 表名;
  

• 5.快速创建一个表结构相同的表

   CREATE TABLE 新表名 LIKE 旧表名;
  

• 6.直接删除表

   DROP TABLE 表名;
  

• 7.判断表是否存在并删除表

   DROP TABLE IF EXISTS 表名;
  

• 8.添加表列

   ALTER TABLE 表名 ADD 列名 类型;
   ALTER TABLE student ADD remark VARCHAR(20);
  

• 9.修改列类型

   ALTER TABLE 表名 MODIFY列名 新的类型;
   ALTER TABLE student MODIFY remark VARCHAR(100);
  

• 10.修改列名

   ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
   ALTER TABLE student CHANGE remark intro varchar(30);
  

• 11.删除列

   ALTER TABLE 表名 DROP 列名;
   ALTER TABLE student DROP intro;
  

• 12.修改表名

   RENAME TABLE 表名 TO 新表名;
   RENAME TABLE student TO student2;
  

• 13.修改字符集

   ALTER TABLE 表名 character set 字符集;
   ALTER TABLE student2 character set gbk;
  

6.DML语句

• 1.插入全部字段：insert into 表名（字段名1，字段名2…）values（值1，值2…）；（字段名可省略）

• 2.插入部分数据：insert into 表名（字段名1…）values （值1…）；（ 字段名不可省略，没有添加数据的字段会使用NULL）

• 3.注意

  值与字段必须对应，个数相同，类型相同

  值的数据大小必须在字段的长度范围内

  除了数值类型外，其它的字段类型的值必须使用引号引起。（建议单引号）

  如果要插入空值，可以不写字段，或者插入null

• 4.蠕虫复制

   什么是蠕虫复制：在已有的数据基础之上，将原来的数据进行复制，插入到对应的表中；
  
   语法格式：INSERT INTO 表名1 SELECT * FROM 表名2;
  
   作用:将表名2中的数据复制到表名1中。
  
   注意：如果只想复制student表中name,age字段数据到student2表中
  
   使用如下格式INSERT INTO student2(NAME, age) SELECT NAME, age FROM student;
  

• 5.更新表记录

  1.不带条件修改数据：update 表名 set 字段名 = 值；

  2.带条件修改数据：update 表名 set 字段名 = 值 where 字段名 = 值；

• 6.删除表记录

   1.不带条件删除数据：delete from 表名；
  
   2.带条件删除数据：delete from 表名 where 字段名 = 值；
  
   3.truncate删除表记录：truncate table 表名；
  
   4.truncate和delete的区别：
  
       delete是将表中的数据一条一条删除
       truncate是将整个表摧毁，重新创建一个新的表,新的表结构和原来表结构一模一样
  

7.DQL语句

• 1.简单查询

   1.使用*表示所有列：select * from 表名；
  
   2.写出查询每列的名称：select 字段名1, 字段名2, 字段名3, ... from 表名；
  

• 2.别名查询

   1.查询时给列、表指定别名需要使用AS关键字（as可省略）
  
   2.使用别名的好处是方便观看和处理查询到的数据
  
   SELECT 字段名1 AS 别名, 字段名2 AS 别名... FROM 表名 AS 表别名;
  

• 3.去重查询

   1.查询指定列并且结果不出现重复数据：select distinct 字段名 from 表名；
  

• 4.查询结果参与运算（原表不改变）

   1.某列数据和固定值（或其他列）运算：select 列名1+固定值 from 表名；
  

• 5.条件查询

   in 关键字：
  
       in里面的每个数据都会作为一次条件，只要满足条件的就会显示；语法格式：SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2...);
  
   范围：
  
       BETWEEN 值1 AND 值2；例：SELECT * FROM student3 WHERE english>=75 AND english<=90;
  
   like：
  
       表示模糊查询；select * from 表名 where like '通配符字符串'；
  
       MySQL通配符有两个：%: 表示0个或多个字符(任意个字符)_: 表示一个字符
  
   排序：
  
       1.单列排序
  
           order by；例：select 字段名 from 表名 where 字段 = 值 order by 字段名 asc;(asc升序；desc降序)
  
       2.组合排序
  
           select 字段名 from 表名 where 字段 = 值 order by 字段名1 [asc|desc], 字段名2 [asc}desc];
  

8.聚合函数

count： 统计指定列记录数，记录为NULL的不统计

sum： 计算指定列的数值和，如果不是数值类型，那么计算结果为0

max： 计算指定列的最大值

min： 计算指定列的最小值

avg： 计算指定列的平均值，如果不是数值类型，那么计算结果为0

9.分组

语法句式：SELECT 字段1,字段2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

例：select * from student group by sex；

注意：分组一般和聚合函数一起使用；where 条件在group by前；having 条件在group by之后。

having与where的区别：

    having是在分组后对数据进行过滤.
    where是在分组前对数据进行过滤
    having后面可以使用聚合函数
    where后面不可以使用聚合函数

10.limit语句

语法句式：SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];

分页查询：limit offset，length；例：limit a,b；

a = (page-1)*b

offset是指偏移量，可以认为是跳过的记录数量，默认为0；length是指需要显示的总记录数；

11.数据库约束

1.主键

    1.特点：非空，唯一；

    2.在创建表的时候给字段添加主键：字段名 字段类型 PRIMARY KEY

    3.在已有表中添加主键：alter table 表名 add primary key (字段名)；

    4.删除主键：alter table 表名 drop primary key；

2.外键     

    1.外键约束：一张表中的某个字段引用另一个表的主键；

        主表： 约束别人；

        副表/从表： 使用别人的数据，被别人约束；

        例：department表(主表)中主键id,employee表（从表/副表）中的dep_id（外键）；

    2.新建表时增加外键：[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

    3.已有表增加外键：ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

    4.删除外键：alter table 表名 drop foreign key 外键名称；

        例：ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;

        在employee表情存在况下添加外键：

        ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);

    5.外键的级联：在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作

12.表关系

1.一对一

    身份证--人名

2.一对多

    班级--学生,部门--员工,客户--订单

    一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

3.多对多
    老师--学生,学生--课程

    多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

13.多表查询（重点）

1.笛卡尔积
    1.笛卡尔积现象：多表查询时左表的每条数据和右表的每条数据组合，这种效果成为笛卡尔积(数学中的组合)；

    2.消除：通过条件

        例：SELECT * FROM dept, emp WHERE emp.dept_id=dept.id;

2.内连接

    1.隐式内连接：看不到JOIN关键字，条件使用WHERE指定SELECT 字段名 FROM 左表, 右表 WHERE 条件；

    2.显示内连接：使用INNER JOIN ... ON语句, 可以省略INNERSELECT 字段名 FROM 左表 INNER JOIN 右表 ON 条件；

3.外链接

    1.左外连接：使用LEFT OUTER JOIN ... ON，OUTER可以省略SELECT 字段名 FROM 左表 LEFT OUTER JOIN 右表 ON 条件；

        可以理解为：在内连接的基础上保证左表的数据全部显示，不符合条件显示null

    2.右外连接：使用RIGHT OUTER JOIN ... ON，OUTER可以省略SELECT 字段名 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件；

        可以理解为：在内连接的基础上保证右表的数据全部显示,不符合条件显示null

4.子查询

    一条SELECT语句结果作为另一条SELECT语法一部分（查询条件，查询结果，表）

    例：SELECT 查询字段 FROM 表 WHERE 查询条件;SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

    说明：

        1.子查询结果只要是单列，肯定在WHERE后面作为条件

            SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

        2.子查询结果只要是多列，肯定在FROM后面作为表

            SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

14.三范式

1.第一范式：每一列不能再拆分

2.第二范式：

    1.一张表只描述一件事情

    2.表中的每一个字段都依赖于主键

3.第三范式：从表的外键必须使用主表的主键

15.操作事务

1.手动提交事务

    第1种情况：开启事务 -> 执行SQL语句 -> 成功 -> 提交事务 

    第2种情况：开启事务 -> 执行SQL语句 -> 失败 -> 回滚事务


    总结:
    如果事务中SQL语句没有问题，commit提交事务，会对数据库数据的数据进行改变。
    如果事务中SQL语句有问题，rollback回滚事务，会回退到开启事务时的状态。

2.自动提交事务

16.事务四大特性

17.事务隔离级别

• 事务在操作时的理想状态：多个事务之间互不影响，如果隔离级别设置不当就可能引发并发访问问题。