数据库（MySQL）核心知识点（持续更新）

数据库（MySQL）核心知识点

1.表的创建（注释的写法）

create table stu1
(
    id int primary key,   -- 学号
    name varchar(20),   -- 姓名
    grade float(4,1)       -- 成绩
);
注意：使用--号作为注释符时，--号后面一定要带一个空格

create table stu2
(
    id int primary key,   #学号
    name varchar(20),   #姓名
    grade float(4,1)       #成绩
);
注意：使用#号作为注释符时，#号后面可加可不加空格

create table stu3
(
    id int primary key comment  '学号',
    name varchar(20) comment  '姓名',
    grade float(4,1)  comment  '成绩',
);
注意：使用comment作为注释符时，comment后面的注释内容要加引号，
          且此注释作为创建表的一部分。

2 数据库与表的创建与管理

一、数据库操作

1.创建数据库
    create database [if not exists]  数据库名
    [charset 字符集名]  [collate  校对规则名];

2.选择数据库
    use 数据库名;

3.查看当前选择了哪个数据库
    select  database();

4.查看创建数据库语句
    show create database 数据库名;

5.查看服务器上有哪些数据库
    show databases;

6.修改数据库的字符集和校对规则
    alter database 数据库名
    [charset 字符集名]  [collate  校对规则名];

7.删除数据库
    drop database [if exists]  数据库名;

2.数据表操作

1.创建表
    CREATE TABLE  [ if not exists ]  表名 (
        字段名1  数据类型  [  约束条件  ],
        字段名2  数据类型  [  约束条件  ],
        ……
        字段名n  数据类型  [  约束条件  ]
    );

2.复制已有表
    CREATE  TABLE [IF NOT EXISTS] table_name
    [ LIKE old_table_name ]     # 复制表结构
    | [AS (SELECT_statement)];    # 复制整张表（包括结构和数据）

3.查看表的基本结构
    DESC 表名

4.查看建表语句（查看表的详细结构）
    SHOW  CREATE  TABLE  表名;   

5.查看当前数据库中有哪些表
    SHOW  tables;

6.删除表
    DROP TABLE  [ IF EXISTS ]  表名1,表名2,……,表名n;

三、修改表结构
    修改表名：             alter table 旧表名 rename [to]  新表名; 
    修改字段名：          alter table 表名    change 旧字段名  新字段名 数据类型 [约束条件]; 
    修改字段数据类型：alter table 表名     modify 字段名     数据类型 [约束条件];
    修改字段排列顺序：alter table 表名     modify 字段名1   数据类型 [ first | after 字段名2 ]; 
    添加字段：             alter table 表名     add      新字段名 数据类型 [约束条件] [first | after 字段名2];
    删除指定字段：       alter table 表名    drop  字段名;

3.完整性约束

一、主键约束（PRIMARY KEY）
1.创建表时创建主键约束
    列级约束：字段名  数据类型   PRIMARY KEY
    表级约束： PRIMARY KEY（字段名[，字段名2，字段名3…]）
2. 删除主键约束
    ALTER TABLE <表名>  DROP PRIMARY KEY;
3. 修改表时创建主键约束
    ALTER TABLE  表名   ADD  PRIMARY KEY(主键列名)

二、唯一约束（UNIQUE）
1.创建表时创建唯一约束
    列级约束：字段名  数据类型   UNIQUE
    表级约束：[constraint 约束名]   UNIQUE（字段名[，字段名2，字段名3…]）
2. 删除主键约束
    ALTER TABLE  表名   DROP INDEX 约束名;
3. 修改表时创建主键约束
    ALTER TABLE  表名   ADD  [CONSTRAINT  约束名 ] UNIQUE(唯一约束列名)

三、检查约束（CHECK）
1.创建表时创建检查约束
    列级约束：字段名  数据类型   CHECK(条件表达式)
    表级约束：[constraint 约束名]   CHECK(条件表达式）
2. 删除检查约束
    ALTER TABLE <表名>  DROP CHECK  约束名 ;
3. 修改表时创建检查约束
    ALTER TABLE  表名   ADD  [CONSTRAINT 约束名]   CHECK(条件表达式)

四、外键约束
1.创建表时创建外键约束
    表级约束：CONSTRAINT  外键约束名  FOREIGN KEY( 从表列名 )   REFERENCES 主表名 (主表主键)
2. 删除外键约束
    ALTER TABLE <表名>  DROP FOREIGN KEY 约束名 ;
3. 修改表时创建检查约束
    ALTER TABLE  表名   ADD  [CONSTRAINT 约束名]   FOREIGN KEY(从表列名)  REFERENCES 主表名（主表主键）

五、非空约束
    列级约束：字段名  数据类型    NOT  NULL

六、自增约束（AUTO_INCREMENT)
    列级约束：字段名  数据类型   AUTO_INCREMENT
    注意：自增约束一般与PRIMARY KEY一起使用

七、默认约束
    列级约束：字段名  数据类型   DEFAULT  默认值

4.数据操纵

一、插入数据－INSERT
    
      1. 向表中所有字段插入数据
        1）指定字段及其值
                   INSERT INTO 表名( 列名1, 列名2, ……, 列名n )   VALUES( 值1, 值2,……, 值n);
        2）不指定字段只列出字段值
                    INSERT INTO  表名  VALUES( 值1, 值2,……, 值n);

        注意：
               -- 如果插入值的顺序和表定义的列的顺序不同，则在对所有列插入数据时不能省略列名。
               -- 当插入值的数据类型为字符串或日期时，需要将值包含在英文格式的引号中。
               -- 指定所有字段及其对应的值时，字段可以不按照表中字段的顺序排列，但字段要和值一一对应。
               -- 不指定字段只列出字段值时，值的顺序要与表中字段的顺序相同，当表中的字段顺序发生改变时，值的插入顺序也要随之改变。

      2. 向表中指定字段插入数据
           INSERT INTO  表名 ( 列名1, 列名2, …… ) 
           VALUES ( 值1, 值2, …… );

         注意：
              向表中指定字段插入数据时，对于没有指定值的字段，系统会根据其自身情况设置不同的值：
              -- 当字段没有设置约束时，系统会将字段值设置为NULL。
              -- 当字段设置自增约束时，系统会将字段值设置为自增后的序列值。
              -- 当字段设置默认约束时，系统会将字段值设置为默认值。

      3. 同时向表中插入多条记录
              INSERT INTO  表名 ( 列名1, 列名2, …… ) 
              VALUES ( 值1, 值2, …… ), …… ,( 值1, 值2, …… );

             注意：插入多条记录时，在插入语句中，只需指定多个插入值列表，插入值列表之间用逗号隔开。
      
      4. 将其他表中的记录插入到表中
              INSERT  INTO  表名1 ( 表1中的列 ) 
              SELECT  表2中的列  FROM  表2;
 
          注意：
                -- 两张表的字段名可以不一样，但数据类型必须一样。
                -- 插入数据时，SQL语句中的字段顺序可以与表中的字段顺序不同，但两张表中的字段顺序必须一一对应。

其他（非主流）插入方法：
      
       1. REPLACE  INTO  表名 ( 列名1, 列名2, …… ) 
           VALUES ( 值1, 值2, …… );
           
          作用：REPLACE语句的语法格式与INSERT语句基本相同，当存在相同的记录时，REPLACE语句可以在插入数据之前将与新记录冲突的旧记录删除，使新记录能够正常插入。                             

       2. INSERT INTO   表名  SET  列名1=值1,列名2=值2,……;
          作用：该语句只给指定的列赋指定的值。

二、修改（更新）数据－UPDATE

        1. 修改指定数据（带WHERE条件的修改）
            UPDATE   表名                     
            SET  列名1=值1, 列名2=值2, ……, 列名n=值n
            WHERE   条件;
       
            作用：对符合条件的记录（行）修改相应的列

        2. 修改所有数据（不带WHERE条件的修改）
            UPDATE  表名
            SET  列名1=值1,列名2=值2 ……,列名n=值n;
            
            作用：修改所有记录行）相应的列

三、删除数据－DELETE

        1. 删除所有数据（行、记录）（不带条件删除）
           DELETE  FROM  表名;
          
           作用：删除表中所有行，而不删除表的定义。

        2. 删除指定数据（行、记录）（带条件删除）
           DELETE  FROM  表名  WHERE  条件;

           作用：删除原来的表并重新创建一个表，执行速度比DELETE语句快。

5.单表查询

SELECT语句的完整格式：

     SELECT   [DISTINCT]  * |  字段列表    FROM 表名  
     [WHERE  条件表达式]                                                                 #  WHERE子句
     [GROUP BY 字段名  [ HAVING  条件表达式 ] ]                             #  GROUP子句
     [ORDER BY 字段名1  [ASC | DESC ] [,字段名2  [ASC | DESC ] …]   #  ORDER BY子句
     [LIMIT  [索引值,]  记录数 ];                                                         #  LIMIT子句

1. 无条件查询 
   格式：select [distinct] *|字段名列表  from 表名;
    （1）查询所有字段
        select  *  from 表名;
        select 字段名1,字段名2,...,字段名n from 表名;
    （2）查询指定字段
        select 字段名1,字段名2,... from 表名;

2. 条件查询（带where子句）
   格式：select  [distinct] *|字段名列表  from 表名 where 条件表达式;
   
   where子句经常用到的运算符：
    （1）算术运算符
    +、-、*、/(DIV)、%(MOD)             求余  5/2=2.5  5%2=1
    （2）比较运算符（常用）
    >、>=、<、<=、=、<>(!=) :大于、大于等于、小于、小于等于、不等于
    between...and...   : 判断某一值是否在某一闭区间内 score between 80 and 90
    in(值1,值2,...)          : 判断某一值是否在列表中
    not in(值1,值2,...)  : 判断某一值是否不在列表中
    is null                       : 判断某一值是否为空
    is not null              ：判断某一值是否不为空
    like ‘模式串’：模糊查询  通配符%和_，%: 表示0个或多个字符     _ :表示一个 
    （3）逻辑运算符（真：1，假：0）
    AND（&&） : 逻辑与， 有0则0，全1为1 
    OR  （||）    : 逻辑或， 有1则1，全0为0
    NOT（!）     : 逻辑非， 0则1，1则0
    XOR            : 逻辑异或，相同为0，不同为1：
3. 排序查询
     SELECT   [DISTINCT]  * |  字段列表    FROM 表名  
     [WHERE  条件表达式]                                                                 #  WHERE子句
     [GROUP BY 字段名  [ HAVING  条件表达式 ] ]                             #  GROUP子句
     ORDER BY 字段名1  [ASC | DESC ] [,字段名2  [ASC | DESC ] …     #  ORDER BY子句
     [LIMIT  [索引值,]  记录数 ];                                                         #  LIMIT子句
 
注意：
（1）where子句中不允许出现别名,也不能出现聚合函数。
（2）默认情况下，是按照排序字段进行升序（ASC）排序，
    ASC:升序排序（默认，可以省略不写）   DESC:降序排序
（3）如果同时有多个排序字段，则先按字段名1的值进行升序或降序排序，
    若字段名1的值是相同的，则按照字段名2的值进行升序或降序排序
（4）order by后的字段名可以是列名，表达式，别名，聚合函数（使用了group by后）    

4. 限制查询结果的数量（分页查询）
     SELECT   [DISTINCT]  * |  字段列表    FROM 表名  
     [WHERE  条件表达式]                                                                 #  WHERE子句
     [GROUP BY 字段名  [ HAVING  条件表达式 ] ]                             #  GROUP子句
     [ORDER BY 字段名1  [ASC | DESC ] [,字段名2  [ASC | DESC ] …]   #  ORDER BY子句
     LIMIT  [索引值,]  记录数;                                                            #  LIMIT子句
注意：
    （1）索引值：第1条记录的索引值是0，第2条记录的索引是1，依此类推。
            索引值是可选项，若省略，表示从第一条记录开始显示，即索引值=0
    （2）limit m,n  :表示从第m+1条记录开始显示n条记录
            limit n    :  表示显示前n条记录
            limit m,n  :  m=(页数-1)*每页的记录数，n=每页的记录数
5. 聚合函数
     count(*|列名):  用来统计记录的个数
    count(*):用来统计所有记录的个数，不管某字段是否包含null值
    count(列名)：统计字段列中非null数据的个数
     sum(列名)：求该列数据之和，忽略null值
     avg(列名)：求该列数据的平均值，忽略null值
     max(列名)：求该列数据的最大值，忽略null值
     min(列名)：求该列数据的最小值，忽略null值
注意：
      在简单查询（条件和无条件查询）中，select 后使用了聚合函数，
      则不能有其它未使用聚合函数的字段出现，否则它是无意义的或是错误的。

6.分组查询

     SELECT   [DISTINCT]  * |  字段列表    FROM 表名  
     [WHERE  条件表达式]                                                                 #  WHERE子句
     GROUP BY 字段名  [ HAVING  条件表达式 ]                                #  GROUP子句
     [ORDER BY 字段名1  [ASC | DESC ] [,字段名2  [ASC | DESC ] …]   #  ORDER BY子句
     [LIMIT  [索引值,]  记录数 ];                                                         #  LIMIT子句
         
注意：
       （1）当使用group by进行分组进行统计时，select后的字段列表只能出现分组字段和使用了聚合函数的字段或表达式，
  不能出现非聚合函数的字段或表达式，否则会出现错误或不合理的数据。
       （2）HAVING子句用于对分组按指定条件进一步进行筛选，过滤出满足指定条件的分组。

        当WHERE子句、GROUP BY子句、HAVING子句、ORDER BY子句在一个SELECT语句中时，执行顺序如下：
      （1）执行WHERE子句，在表中选择行。
      （2）执行GROUP BY子句，对选取行进行分组。
      （3）执行聚合函数。
      （4）执行HAVING子句，筛选满足条件的分组。
      （5）执行ORDER BY子句，进行排序。

7.where和having的区别：
      （1）where在分组前对条件进行筛选；
              having在分组后对条件进行筛选。
      （2）where可以单独使用；
              having只能和group by一起。
      （3）where后的条件表达式中不能出现聚合函数和别名；
              having后的条件表达式中可以出现聚合函数和别名。

6.

SELECT语句的完整格式：

     SELECT   [DISTINCT]  * |  字段列表    FROM 表名  
     [WHERE  条件表达式]                                            #  WHERE子句
     [GROUP BY 字段名  [ HAVING  条件表达式 ] ]                     #  GROUP子句
     [ORDER BY 字段名1  [ASC | DESC ] [,字段名2  [ASC | DESC ] …]   #  ORDER BY子句
     [LIMIT  [索引值,]  记录数 ];                                   #  LIMIT子句

下列案例用到的表：
        
create table dept(
   did int primary key,   -- 部门编号
   dname varchar(20)      -- 部门名称
);
create table emp(
    id int primary key auto_increment,    -- 员工编号
    ename varchar(20),                    -- 员工姓名
    age int,                            -- 年龄
    did int                              -- 部门编号
);

insert into dept(did,dname)
values(10,'网络部'),(20,'媒体部'),(30,'研发部'),(40,'人事部');

insert into emp
values(101,'王红',20,10),(102,'张三',19,10),(103,'李四',20,20),
      (104,'王五',18,30),(105,'张小娟',null,null);
      
一、连接查询

1.交叉连接
    交叉连接(CROSS JOIN)又称笛卡尔积，由第一个表的每一行与第二个表的每一行连接起来后形成的表。
    语法格式如下：
        SELECT * FROM 表1 CROSS JOIN 表2;
    或         
        SELECT * FROM 表1, 表2;
    补充：
        （1）若未指定查询字段（即为*）,则默认表1中的字段在前，表2中的字段在后；
        （2）若两表记录数相同，则用表1中的所有记录与表2中的每一条记录连接；
        （3）若两表记录数不同，则用记录数较少的表中的所有记录与记录数较多的表中的每一条记录连接；
        （4）若表1记录数为m，表2记录数为n，则交叉连接后的表中的记录数为m*n。

    【例1】对员工表emp和部门表dept进行交叉连接，查询两表所有可能的组合。
        select * from emp cross join dept;
        或
        select * from emp,dept
    
2.内连接
        内连接使用比较运算符进行表间某些字段值的比较操作，并将与连接条件相匹配的数据行组成新记录，
    以消除交叉连接中没有意义的数据行。
    内连接有两种连接方式：
    第一种：使用INNER JOIN定义连接条件的显式语法结构，语法格式如下：
        SELECT 字段列表
        FROM  表1  [INNOR]  JOIN  表2 ON 连接条件
        [WHERE 过滤条件]
    第二种：使用WHERE子句定义连接条件的隐式语法结构，语法格式如下：
        SELECT  字段列表
        FROM  表1, 表2 
        WHERE  连接条件  [AND 过滤条件]
                    
（1）等值连接与非等值连接。
    表之间通过比较运算符“=”连接起来，这被称为等值连接，而使用其他比较运算符的则被称为非等值连接。
    【例2】对员工表emp和部门表dept进行等值连接。
        SELECT e.*,d.*
        FROM emp e,dept d 
        WHERE e.did=d.did;        
    或
        SELECT e.*, d.*
        FROM emp e JOIN dept d  
        ON e.did=d.did;

（2）自然连接
    自然连接在FROM子句中使用关键字NATURAL JOIN，在目标列中去除相同的字段名。
    【例3】对员工表emp和部门表dept进行自然连接查询。
        SELECT *  FROM emp NATURAL JOIN dept;

    注意：
    -- 通过MySql自己的判断完成连接过程，不需要指定连接条件;MySql会使用表内的，相同的字段，作为连接条件。
    -- 如果两个表有多个相同字段，或者没有相同字段，则自然连接返回为空。        

（3）自连接
        将某个表与自身进行连接，称为自表连接或自身连接，简称自连接，使用自连接需要为表指定多个别名，
    且对所有查询字段的引用必须使用表别名限定。
    【例4】使用自连接查询”王红”所在的部门有哪些员工。
         select e1.name from emp e1 join emp e2 
        on e1.did=e2.did 
        where e2.name='王红';
            
3.外连接
    在内连接的结果表，只有满足连接条件的行才能作为结果输出。外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。
    外连接有以下2种：
    （1）左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行，当左表有记录而在右表中没有匹配记录时，右表对应列被设置为空值NULL。
    （2）右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行，当右表有记录而在左表中没有匹配记录时，左表对应列被设置为空值NULL。
    
    【例5】对员工表emp和部门表dept进行左外连接。
        select * from emp e left join dept d
        on d.did=e.did;
    【例6】对员工表emp和部门表dept进行右外连接。
        select * from emp e right join dept d
        on d.did=e.did;    

二、子查询
    子查询：指一个查询语句嵌套在另一个查询语句内部的查询。外层查询称为主查询，内层查询称为子查询或者嵌套查询。该类查询可以基于一个表或多个表。在此类查询中，系统会先执行子查询，将子查询的结果作为主查询的过滤条件。
    子查询可以应用在SELECT，UPDATE和DELETE语句中，并且大多数子查询会包含在FROM子句或WHERE子句中，在WHERE子句中通常与IN，ANY，ALL和EXISTS关键字搭配使用，也可以使用条件判断符（比较运算符）。

    1.IN子查询
    在IN子查询中，首先执行括号内的子查询，再执行父查询，子查询的结果作为父查询的查询条件。
    格式：
        SELECT {*|字段列表} FROM  表1 
        WHERE  列名1 IN (SELECT  列名2  FROM  表2  [WHERE 条件表达式] );
    【例7】查询部门员工中有年龄为20岁的员工的部门名称。
        select dname from dept
        where did in(select did from emp where age=20);
        
    2.比较子查询
    比较子查询是指父查询与子查询之间用比较运算符进行关联。
    语法格式如下：
        <表达式> { < | <= | = | > | >= | != | <> } { ALL | SOME | ANY } (<子查询>)
    说明如下：
    关键字ALL、SOME和ANY用于对比较运算的限制，ALL指定表达式要与子查询结果集中每个值都进行比较，当表达式与子查询结果集中每个值都满足比较关系时，才返回TRUE，否则返回FALSE；SOME和ANY指定表达式要只要与子查询结果集中某个值满足比较关系时，就返回TRUE，否则返回FALSE。
    【例8】查询”王红”所在的部门有哪些员工。
        SELECT ename FROM emp
        WHERE did=(SELECT did FROM emp where ename='王红');

    【例9】查询比10号部门所有员工年龄都小的部门及部门名称。
        SELECT d.* from dept d join emp e
        ON e.did=d.did
        WHERE age<ALL(SELECT age FROM emp WHERE did=10);

    3.EXISTS子查询
    EXISTS子查询只用于测试子查询是否返回行，它不产生任何数据，只返回TRUE或FALSE，当返回TRUE时，外层查询才会执行。
    语法格式如下:
        [ NOT ] EXISTS ( <子查询> )
        
    【例10】查询网络部的员工姓名。
        SELECT ename FROM emp
        WHERE exists(SELECT * FROM dept WHERE dept.did=emp.did AND dname='网络部')；
        
    【例11】查询网络部的员工姓名。（等值子查询）(一般使用这种方法)
        SELECT ename FROM emp
        WHERE did=(SELECT did FROM dept WHERE dname='网络部');

三、联合查询
    联合查询将两个或多个SQL语句的查询结果集合并起来，利用联合进行查询处理以完成特定的任务，使用UNION关键字，将两个或多个SQL查询语句结合成一个单独SQL查询语句。
    联合查询的语法格式如下:
        <SELECT查询语句1>
        {UNION | UNION ALL }
        <SELECT查询语句2>
    说明：
    -- UNION语句将第一个查询中的所有行与第二个查询的所有行相加。
    -- 不使用关键字ALL,消除重复行，所有返回行都是唯一的。
    -- 使用关键字ALL，不去掉重复记录，也不对结果自动排序。 
    
    在联合查询中，需要遵循的规则如下：
        ● 在构成联合查询的各个单独的查询中，列数和列的顺序必须匹配，数据类型必须兼容。
        ● 若有ORDER BY子句和LIMIT子句，则必须置于最后一条SELECT语句之后。
    
    【例12】查询年龄大于18岁的和已分配部门的员工姓名，年龄和部门编号。
        SELECT ename,age,did
        FROM emp
        WHERE age>18
        UNION
        SELECT ename,age,did
        FROM emp
        WHERE did is not null;

        注意与下列语句的区分：
        SELECT ename,age,did
        FROM emp
        WHERE age>18
        UNION ALL   -- 增加ALL，与上例没有ALL进行结果对比
        SELECT ename,age,did
        FROM emp
        WHERE did is not null;