MySQL --- SQL基础知识

内连接与左（右）外连接的区别

内连接关键字：inner join on
语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;  
# 组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
左（外）连接关键字：left join on / left outer join on
语句：SELECT  * FROM a_table a left join b_table b ON a.a_id = b.b_id; 
#  left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
右（外）连接关键字：right join on / right outer join on
语句：SELECT  * FROM a_table a right outer join b_table b on a.a_id = b.b_id;  
#right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
# 注意：书写sql语句时连接join关键字的左右两边是将要连接的表，on后边跟着关联条件。

总结：

• 内连接：查询左右表都有的数据，不要左/右中NULL的那一部分
• 左连接：即以左表为基准，到右表找匹配的数据，找不到匹配的用NULL补齐。
• 左右连接可以相互转换：A left join B --->  B right join A 是同样的。 #A 站在 B的左边   --->  B 站在 A的右边

具体见：https://blog.csdn.net/zjt980452483/article/details/82945663

where、having、group by、order by、limit的区别和使用顺序

• where：通过在SELECT语句的WHERE子句中指定条件进行查询，WHERE子句必须紧跟在FROM子句之后。如：从员工表里查询员工id为h0001的员工的工资

select 工资 from 工资表 where id='h0001';

• having：一般与group by组合来使用，表示在得到分类汇总记录的基础之上，进一步筛选记录。如：从部门表里查部门内员工薪水总和大于100000的部门的编号。

select 部门编号,sum(薪水) from 部门表 group by 部门编号 having sum(薪水)>100000;

ps：相同点：where和having都可以加条件

区别：1.where在分组之前加条件，having在分组之后加条件. 2.where的效率要远远高于having. 分组本身消耗资源非常大.

• GROUP BY：当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部门来分组。

select 部门编号,sum(薪水) from 部门表 group by 部门编号;

• 
  ORDER BY：order by 用来指定数据的排序方式。有升序和降序两种。desc表示降序，asc为升序，默认为升序，asc可省略。ps：order by 要写在where之后，limit之前。

select * from stu_info order by id asc;// 按照id升序排序，其中asc可省略。
select * from stu_info order by id desc; //按照id降序

• LIMITE：LIMIT用来限定查询结果的起始行，以及总行数。如：查询10行记录，起始行从3开始

select * from emp limit 3,10;

综合运用时的使用顺序：

从employee表中查询salary列的值>0且prize字段值>0的记录，结果以id字段分组且降序排列,起始行从0开始，显示5行：

select * from emploee where salary>0 group by id having prize>0 order by id desc limit 0, 5;

结果：查询到的记录条数小于5行，所以只能显示查询到的总行数。

总结顺序： where(条件) - group by(分组) - having(条件) - order by(排序) - limit(限定查询结果)

增删改查命令

#练习： 创建db4数据库，判断是否存在，并制定字符集为gbk
create database if not exists db4 character set gbk
#查询所有数据库的名称:
show databases;
#修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
#判断数据库存在，存在再删除
drop database if exists 数据库名称;
#查询当前正在使用的数据库名称
select database();
#使用数据库
use 数据库名称;
#复制表，注意位置
create table 表名 like 被复制的表名;
#查询表结构
desc 表名;
#删除表中指定列
alter table 表名 drop 列名;
#操作表，列名 + 数据类型，最后一行没有逗号
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);

mysql常见函数与数据类型

常用函数：

• 字符函数 （自动进行数字和字符串的转化）
• 数值计算函数
• 比较运算符函数
• 日期时间函数

具体见：https://www.cnblogs.com/duhuo/p/5650876.html

数据类型：

• MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
• MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

count(1),count(*),count(列)区别是啥？

主要是当数据量达到一定级别，考察基本调优思路。

在Mysql中的不同的存储引擎对count函数有不同的实现方式。

• MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count()的时候会直接返回这个数，效率很高（没有where查询条件，如果有 where 条件，那么即使是 MyISAM 也必须累积计数的。
• InnoDB引擎并没有直接将总数存在磁盘上，在执行count()函数的时候需要一行一行的将数据读出，然来后累计总数。

注：下面的讨论和结论是基于 InnoDB 引擎的。

count 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

所以，count( * )、count(1)和count(主键 id) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

• count(可空字段)：扫描全表，读到server层，判断字段可空，拿出该字段所有值，判断每一个值是否为空，不为空则累加（ps：字段非主键的情况最好不要出现，因为不走索引）
• count(非空字段)与count(主键 id)：扫描全表，读到server层，判断字段不可空，按行累加。
• count(1)：扫描全表，但不取值，server层收到的每一行都是1，判断不可能是null，按值累加。注意：count(1)执行速度比count(主键 id)快的原因：从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作，ps：两者都只扫描主键。
• count( * )：MySQL 执行count( * )在优化器做了专门优化。因为count( * )返回的行一定不是空。扫描全表，但是不取值，按行累加。
  ps：InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。因此，普通索引树比主键索引树小很多。
  对于 count ( * ) 来说，遍历哪个索引树得到的结果逻辑上都是一样的。MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

性能对比：

• count(可空字段) < count(非空字段) = count(主键 id) < （count(1) ≈ count(*)，都扫描全表，不取值，按行累加）

至于分析性能差别的时候，记住这么几个原则：

• server 层要什么就给什么；
• InnoDB 只给必要的值；
• 现在的优化器只优化了 count(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。
• ps：Redis 存储计数会出现的问题，把计数值也放在 MySQL 中，利用事务的原子性和隔离性，就可以解决一致性的问题。数据量不大，我们尽量用 count ( * ) 实现计数；数据量很大的情况考虑新建 MySQL 表存储计数，用事务的原子性和隔离性解决。

mysql常用命令

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

通用写法：

• SQL 语句可以单行或多行书写，以分号结尾。
• 可使用空格和缩进来增强语句的可读性。
• MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。

111

什么是sql注入，如何防止sql注入?

所谓SQL注入，黑客对数据库进行攻击的常用手段之一。一部分程序员在编写代码的时候，没有对用户输入数据的合法性进行判断，注入者可以在表单中输入一段数据库查询代码并提交，程序将提交的信息拼凑生成一个完整sql语句，服务器被欺骗而执行该条恶意的SQL命令。注入者根据程序返回的结果，成功获取一些敏感数据，甚至控制整个服务器，这就是SQL注入。

SQL注入攻击的总体思路:

（1）寻找到SQL注入的位置

（2）判断服务器类型和后台数据库类型

（3）针对不同的服务器和数据库特点进行SQL注入攻击

如何防御SQL注入？

• 加强用户输入内容的验证
• 严格区分权限（普通用户和管理用户）
• 使用参数化语句（？代表此处为一个参数，需要后期通过set设置）
• 使用专业软件对SQL漏洞进行扫描

数据库设计的三个范式？

• 第一范式（1NF）：强调的是列的原子性（属性不可分割，即每个字段都不可能拆分），即列不能够再分成其他几列，比如字段name，中国名符合，外国名不符合。
• 第二范式（2NF）：一是表必须有一个主键；二是其他字段必须完全依赖于主键，理解为主键约束就好了，而不能只依赖于主键的一部分，比如学生表中的学号，其他字段都可以通过学号获取信息。
• 第三范式（3NF）：每一列数据都和主键直接相关，而不能间接相关。表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。
  比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。