黑马程序员技术交流社区

标题: 【广州校区】【原创】MySQL常见问题精华总结 [打印本页]

---

作者: 帅气de路人甲    时间: 2017-12-12 16:10
标题: 【广州校区】【原创】MySQL常见问题精华总结
【001】MySQL是什么数据库？
答：
MySQL是一个关系型数据库管理系统，目前属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在 web 应用方面，MySQL是最好的RDBMS应用软件（Relational Database Management System，关系数据库管理系统）。
关系数据库将数据保存在不同的表中，而不是将所有的数据放在一个大仓库内，这样就增加了速度并提高了灵活性

---

【002】写出SQL的分类？
答：
结构化查询语言（Structured Query Language）简称SQL，是关系型数据库管理系统都需要遵循的规范，SQL分类如下：
①  数据定义语言：简称DDL（Data Definition Language），用来定义数据库对象中的数据库、表、列等；关键字：create、alter、drop等
②  数据操作语言：简称DML（Data Manipulation Language），用来对数据库中的表记录进行更新；关键字：insert、delete、update等
③  数据控制语言：简称DCL（Data Control Language），用来定义数据库的访问权限、安全级别和创建用户
④  数据查询语言：简称DQL（Data Query Language），用来查询数据库中表的记录；关键字：select、from、where等
注意：
SQL语句可以单行或多行书写，以分号结尾；
MySQL数据库中的SQL语句不区分大小写，关键字建议使用

---

【003】SQL常用操作有哪些？
答：
①  数据库操作（DDL）

• 创建数据库
  
  • create database 数据库名;
    
    • 创建数据库、使用默认编码：utf8
      
  • create database 数据库名 character set 字符集；
    
    • 创建数据库、指定编码
      
• 查看数据库
  
  • show databases;
    
    • 查看所有数据库
      
  • show create database 数据库名;
    
    • 查看某个数据库定义的信息
      
• 删除数据库
  
  • drop database 数据库名;
    
• 使用数据库
  
  • select database();
    
    • 查看正在使用的数据库
      
  • use 数据库名;
    
    • 切换使用数据库
      

②  表结构操作（DDL）

• 创建表
  
  • create table 表名 ( 字段名 类型(长度) [约束], 字段名 类型(长度) [约束], ... );
  • 注意：primary key主键约束修饰的数据不能重复、且不能为null
    
• 查看表
  
  • show tables;
    
    • 查看所有表
      
  • desc 表名;
    
    • 查看表结构
      
• 删除表
  
  • drop table 表名;
    
• 修改表
  
  • alter table 表名 add 列名 类型(长度) [约束];
    
    • 添加列
      
  • alter table 表名 modify 列名 类型(长度) [约束];
    
    • 修改列的类型长度、约束
      
  • alter table 表名 change 旧列名 新列名 类型(长度) [约束];
    
    • 修改列名
      
  • alter table 表名 drop 列名;
    
    • 删除列
      
  • rename table 表名 to 新表名;
    
    • 修改表名
      
  • alter table 表名 character set 字符集;
    
    • 修改表的字符集
      

③  数据操作语言（DML）

• 插入表记录：insert
  
  • insert into 表 (字段1, 字段2, 字段3...) values (值1, 值2, 值3...);
    
    • 向表中插入某些字段
      
  • insert into 表 values(值1, 值2, 值3...);
    
    • 向表中插入所有字段，字段的顺序为创建表时的顺序
      

注意：
1、值与字段必须对应：个数相同、类型相同
2、值的数据大小必须在字段的长度范围内
3、除了数值类型外，其它的字段类型的值必须使用引号（建议单引号）
4、如果要插入空值，可以不写字段，或插入null

• 更新表记录：update
  
  • update 表名 set 字段名=值, 字段名=值, ...;
    
    • 更新所有记录的指定字段
      
  • update 表名 set 字段名=值, 字段名=值, ... where 条件;
    
    • 更新符合条件记录的指定字段
      

注意：
1、列名的类型与修改的值要一致
2、修改值的时候不能超过最大长度
3、除了数值类型外，其它的字段类型的值必须使用引号引起

• 删除表记录
  
  • delete from 表名 [where 条件];
  • truncate table 表名;
    

④  简单、条件查询（DQL）

• select [distinct] * | 列名, 列名 from 表 [as] 别名 where 条件
• 条件查询：
  
  • 比较运算符：
    
    • >、<、<=、>=、=、<>
    • between...and...
    • in()
    • like        :        % 代表0个或多个任意字符，_代表一个字符
    • is null、is not null
      
  • 逻辑运算符：
    
    • and
    • or
    • not
      
• 分页查询：limit
  
  • 格式：limit 起始行号, 查询行数（起始行数：从0开始）
    

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

• 级联更新：on update cascade
• 级联删除：on delete cascade
  

---

【004】使用drop、delete、truncate删除有何异同点？
答：
相同点：
1、truncate和不带where子句的delete，以及drop都会删除表内的数据
2、drop、truncate都是DDL语句（数据定义语言），执行后会自动提交
不同点：
1、truncate和delete只删除数据，不删除表的结构
     drop语句将删除表的结构被依赖的约束（constain）、触发器（trigger）、
     索引（index）；依赖于该表的存储过程 / 函数将保留，但是变成invalid状态
2、delete是DML，操作事务提交后才生效，如有相应的trigger，执行时会被触发
     truncate、drop是DDL，操作立即生效，不能回滚，操作不触发trigger
     即：delete是DML语句，不会自动提交；drop / truncate是DDL语句，自动提交
3、效率：drop > truncate > delete
4、delete删除表中数据，但不重置auto_increment记录数
     truncate删除表数据，重置auto_increment记录数（其实是先删除表再创建表）

---

【005】SQL常用单表查询有哪些？
答：
①  排序（order by）
通过order by将查询的结果进行排序，放置在select语句的最后

• select * from 表名 order by 排序字段(别名) asc | desc;
  
  • asc 升序（默认）、desc 降序
    

②  聚合
聚合函数查询是纵向查询，是对一列的值进行计算，然后返回一个单一的值；
聚合函数会忽略空值，常用的5个聚合函数如下：

• count：统计指定列不为null的记录行数
• sum        ：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
• max        ：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
• min        ：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
• avg        ：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0
  

③  分组（group by）
分组中的having子句，是用于在分组后对数据进行过滤的，类似于where条件

• select 字段1, 字段2... from 表名 group by 分组字段 having 分组条件;
  

注意：

• having一定要与 group by 连用
  

   有 having 就一定有group by，但是有 group by 不一定有 having

• group by 有一个原则，即出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中（select中的字段不可以单独出现，必须出现在group语句或组函数中）
  

就是select后面的所有列中，如果没有使用聚合函数，就必须出现在group by 后面

---

【006】having 与 where 的区别？
答：
having        ：筛选分组，在分组后进行过滤，可使用分组函数
where        ：筛选数据，在分组前进行过滤，不可用分组函数

---

【007】SQL有哪些常用约束（constraint）？
答：
①  主键约束：primary key

• primary key 约束唯一标识数据库表中的每条记录，必须唯一、且不能为null
• 每个表都应该有且只有一个主键
• 添加 主键约束的3种方式：
  
  • 1、创建表时，在字段描述处，声明指定字段为主键
    
    • 格式：字段名 字段类型 primary key;
      
  • 2、创建表时，在constraint约束区域，声明指定字段为主键
    
    • 格式：[constraint 名称] primary key (字段列表);
      
      • 关键字constraint可以省略（如需为主键命名则不能省略）
      • 字段列表需用小括号，多个字段之间用逗号分隔
      • 声明两个以上字段为主键，称为联合主键
        
  • 3、创建表时，创建表后，通过修改表结构，声明指定字段为主键
    
    • alter table 表名 add [constraint 名称] primary key (字段列表);
      
• 删除 主键约束
  
  • alter table 表名 drop primary key;
    
• 设置 自增长：auto_increment
  
  • 格式：字段名 字段类型 primary key auto_increment;
  • 自增长类型必须是整型，且必须为键（一般是主键）
  • 自增长类型可以不为主键设置值，也可以设置为null，数据库会自动维护主键值
  • 默认auto_increment开始值是1，可用下列SQL语法修改：
    
    • alter table 表名 auto_increment=100;
      

②  非空约束：not null

• not null 约束强制列不接受null值
• 添加 非空约束的方式：
  
  • 创建表时，在字段描述处，声明指定字段非空
    
    • 格式：字段名 字段类型 primary key;
      

③  唯一约束：unique

• unique约束强制列唯一，primary key自动定义了unique约束
• 表中可以有多个unique约束，但只能有一个primary key约束
• 添加 唯一约束的3种方式：
  
  • 1、创建表时，在字段描述处，声明指定字段为唯一
    
    • 格式：字段名 字段类型 unique;
      
  • 2、创建表时，在constraint约束区域，声明指定字段唯一
    
    • 格式：[constraint 名称] unique (字段);
      
  • 3、创建表后，修改表结构，声明指定字段唯一
    
    • 格式：alter table 表名 add [constraint 名称] unique (字段);
      
• 删除 唯一约束
  
  • alter table 表名 drop index 名称
    
    • 如果添加唯一约束时，没有设置约束名称，默认是当前字段的字段名
      

④  外键约束：foreign key

• 外键约束时保证一个或两个表之间的参照完整性
• 当主表的记录被从表参照时，主表的记录不允许删除，如要删除需先删除从表中依赖该记录的数据，才能删除主表数据（也可以设置级联删除从表数据：on delete cascade）
• 外键约束的参照列，在主表中只能引用主键或唯一键约束的列
• 外键约束的从表外键是对主表主键的引用，且必须与主表主键类型一致
• 外键约束的目的：保证数据完整性
• 添加 外键约束的2种方式：
  
  • 1、创建表时，在constraint约束区域，声明外键约束
    
    • 格式：[constraint 名称] foreign key(从表外键字段) references 主表(主表主键);
      
      • [constraint 名称]中的外键名称建议以 "_FK" 结尾
        
  • 2、创建表后，修改表结构，声明外键约束
    
    • 格式：alter table 表名 add foreign key(从表外键) references 主表(主表主键);
      
• 删除 外键约束
  
  • alter table 从表 drop foreign key 从表外键名称;
    

---

【008】SQL表与表之间有什么关系？
答：
表与表之间主要有3种关系：1对多、多对1、1对1；
①  一对多关系

• 建表原则：在从表（多方）创建一个字段，该字段作为外键指向主表（一方）的主键
  

②  多对多关系

• 建表原则：创建第3张表（中间表），该表至少需要2个字段，这2个字段作为外键分别指向各自一方的主键
  

③  多对一关系

• 建表原则：主表主键和从表外键形成主外键关系，外键唯一（unique）
• 在实际开发应用中不多，因为一对一可以创建成1张表
  

---

【009】SQL常用多表查询有哪些？
答：
①  交叉查询（笛卡尔积）

• 交叉连接查询得到的是两个表的乘积
• 交叉连接查询基本不会使用、因为它是所有查询的最大集，基本没有实际意义
• 格式：
  
  • select * from 表A, 表B;
    

②  内连接查询

• 内连接查询：inner join...on...（inner可以省略）
• 格式：
  
  • 隐式内连接：select * from A, B where 条件;
  • 显式内连接：select * from A inner join B on 条件 where 条件;
    

③  外连接查询

• 外连接查询：outer join...on...（outer可以省略）
• 格式：
  
  • 左外连接：left outer join...on...
    
    • select * from A left outer join B on 条件 where 条件;
    • 查询左表（A表）所有数据，关联的右表（B表）数据是否显示，取决于条件是否成立，如果成立显示数据，不成立显示null
      
  • 右外连接：right outer join...on...
    
    • select * from A right outer join B on 条件 where 条件;
    • 查询右表（B表）所有数据，关联的左表（A表）数据是否显示，取决于条件是否成立，如果成立显示数据，不成立显示null
      

④  子查询

• 一条select语句结果作为另一条select语法中的一部分（查询条件、查询结果、表等）
• 格式：
  
  • select 查询字段 from 表 where 条件;
    

---

【010】关系数据库有几种范式？
答：
有6种范式：第1范式（1NF）、第2范式（2NF）、第3范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）、第5范式（5NF，又称完美范式）
一般来说，数据库只需满足第3范式（3NF）即可
①  第一范式：
符合1NF的关系中每个属性都不可再分，1NF是所有关系型数据库的最基本要求
②  第二范式：
2NF是在1NF的基础上，一张表只描述一件事情，即2NF属性完全依赖于主键
③  第三范式：
3NF是在2NF的基础上，表中的每一列都直接依赖主键，而不通过其它的列来间接依赖主键
即在满足2NF前提后，3NF的属性不依赖其它非主键属性，任何非主属性不得传递依赖主键

---

作者: java愤怒小鸟    时间: 2017-12-22 09:36
精辟,感谢

---

作者: FrankLinda    时间: 2017-12-23 00:38
完整，收录

---

作者: 纯情小飞飞    时间: 2017-12-23 18:36
不错不错，写的很好。

---

欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2