目录

一、SQL分类

二、命名规范

三、导入数据

3.1、一次创建多条数据

四、查询的一些常用语句

4.1、函数

4.1.1基本函数

4.1.2三角函数

4.1.3指数与对数

4.1.4进制间的转换

4.1.5字符串函数

4.1.6获取日期、时间

4.1.7 聚合函数

4.2、分组 group by使用

4.3、过滤数据 having 的使用

4.4、select 语法的使用总结

4.5、sql语句执行过程

4.6、子查询

五、数据类型的选择

六、约束

七、视图

---

一、SQL分类

• DDL（数据定义语言）：对数据库、表、视图、索引等对象的增删改操作，关键字：CREAT、DROP、ALTER、RENAME、TRUNCATE等，执行之后不可以回滚；
• DML（数据操作语言）：对数据库记录的增删改查操作，关键字：INSERT、DELETE、UPDATE、SELECT等，默认情况下执行之后不可以回滚，可以修改参数autocommit =false则可以回滚。
• DCL（数据控制语言）：对数据库、表、字段、用户的访问权限、安全级别的定义，关键字：GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT
• DQL（数据查询语言）：因为查询操作频繁，所以有很多人把查询单独拎出来一类，SELECT；
• TCL（事务控制语言）：关键字：COMMIT、ROLLBACK。

二、命名规范

• 数据库名、表名、表别名、字段名、字段别名等都是小写；
• SQL关键字、函数名、绑定变量名等都大写

三、导入数据

​​​​​​​source 文件存储地址

3.1、一次创建多条数据

• 创建表
• 设置参数 log_bin_trust_function_creators

  #不加global只对当前窗口有效
  set global log_bin_trust_function_creators=1; 
  

• 创建函数 随机产生字符串

  DELIMITER //
  CREATE FUNCTION rand_string(n INT)
  		RETURNS VARCHAR(255) #该函数会返回一个字符串
  BEGIN
  

四、查询的一些常用语句

• 去除重复行

  select distinct 字段 from 表;
  select distinct 字段,字段 from 表;
  

• IFNULL（字段，字段值，默认值），如果是空取字段值，否则取默认值
• 显示表结构

  describe 表名;
  desc 表名;
  

• 安全等于与等于的区别在于，NULL=NULL为NULL，NULL<=>NULL为1；
• least()、greatest()对字段查询最小值、最大值；

  select least(字段1,字段2) from 表名;
  select greatest(字段1,字段2) from 表名;
  

• between ... and 查询x-y的信息，包含边界值；

  select * from table_name where id between 10 and 20;
  

• in在某些范围当中;

  select * from table_name where id in(1,2,3);
  

• ESCA或者\：转义字符；
• 正则表达式：regexp、rlike；^以...开始，$以...结尾，.匹配任何一个字符
• 逻辑运算符：or 、&&、！、xor。and优先级高于or
• 在一定范围内，向左移动一位相当于乘以2，向右移动一位相当于除以2
• 没有使用排序操作默认情况下查询返回的数据是按照添加数据的顺序；
• order by对查询数据进行排序默认从低到高，DESC降序，ASC升序；

  select * 
  from table_name 
  order by 字段；
   
  select * 
  from table_name
  order by 字段 DESC；
   
  select * 
  from table_name 
  order by 字段 ASC；
   
  select * 
  from table_name 
  where id in(1,2,3) 
  order by 字段 ASC；
  

• limit分页，8.0版本中limit x offset y；oracle不支持

  select * 
  from table_name
  limit 0,20
  

• 多表查询

        分类：等值链接、非等值链接；自连接、非自连接；内连接、外连接

        union:满外连接，去除重复部分；

        union all:满外连接，不去除重复部分；

4.1、函数

从实现角度分为：数值函数、字符函数、加密解密函数、时间日期函数、流程控制函数、聚合函数等；

内置函数再分类：单行函数、聚合函数（或分组函数）。

4.1.1基本函数

4.1.2三角函数

4.1.3指数与对数

4.1.4进制间的转换

4.1.5字符串函数

4.1.6获取日期、时间

4.1.7 聚合函数

SELECT AVG(字段名) FROM TABLE_NAME;
SELECT SUM(字段名) FROM TABLE_NAME; 
SELECT MAX(字段名) FROM TABLE_NAME;
SELECT MIN(字段名) FROM TABLE_NAME;
SELECT COUNT(1) FROM TABLE_NAME;
SELECT COUNT(*) FROM TABLE_NAME;
#计算指定字段出现个数时是并不计算NULL值的
SELECT COUNT(字段名) FROM TABLE_NAME;

count使用时效率问题：

如果存储引擎使用MYISAM，三者效率一致；

如果使用InnoDB存储引擎：count(*)=count(1)>count(字段)，数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描。

4.2、分组 group by使用

注意事项：

• 查询中的字段一定要写在group by中，反之不一定，在oracle中会报错。
• group by声明在from、where后面，order by、limit、having前面。
• group by中使用with rollup会计算整体平均，要慎重使用order by。

4.3、过滤数据 having 的使用

注意事项：

• 如果过滤条件中使用聚合函数，则必须使用having来替代where。否则，报错。
• having必须声明group by后面。
• 开发中使用having的前提是使用了group by。
• 当非聚合函数过滤条件出现时，where比having效率高。通过使用where在进行分组的时候可以过滤一些不必要的数据，提高效率。

4.4、select 语法的使用总结

• sql92语法

  SELECT ...,...,...(存在聚合函数)
  FROM ...,...,...
  WHERE 多表的链接条件 AND 不包含聚合函数的过滤条件
  GROUP BY ...,...
  HAVING 包含聚合函数的过滤条件
  ORDER BY ...,...(ASC / DESC)
  LIMIT ...,...
  

• sql99语法​​​​​​​

  SELECT ...,...,...(存在聚合函数)
  FROM ...,...,... (LEEF / RIGHT) JOIN ... ON 多表的链接条件
  (RIGHT / LEEF) JOIN ... ON 
  JOIN ... ON ...
  WHERE 多表的链接条件 
  GROUP BY ...,...(ASC / DESC)
  HAVING 包含聚合函数的过滤条件
  ORDER BY ...,...(ASC / DESC)
  LIMIT ...,...
  

4.5、sql语句执行过程

根据FROM查找具体表 -> 根据ON过滤连接条件 ->是否为左外右外链接 -> 行过滤：where过滤数据 -> group by 分组 -> HAVING 过滤 -> 过滤列：SELECT 过滤字段 -> DISTINCT去除 -> ORDER BY -> LIMIT

4.6、子查询

select中可以使用

case 字段名 when 条件 then 取值1 else 取值2 end 显示字段名

用来设置某个值在满足条件语句时返回不同情况字段名。

• 多行子查询：IN ANY ALL SOME；
• 相关子查询：内查询需要使用外查询的条件来进行查询，例如：查找公司员工中大于本部门平均工资的员工的last_name,salary,department_id；
• EXISTS的使用：将外查询送进子查询

  select  employee_id,last_name,job_id,department_id
  from employees el
  where exists(select * 
    			    from employees e2
    				where el.employee_id = e2.manager_id
  			;
  

五、数据类型的选择

• 无符号数

• 选择建议

整数用int；小数用decimal(m,d)；时间用datetime；

如果存储范围超过decimal范围，将数据拆成整数和小数并分开存储；

如果存储的字符串长度几乎相等，使用char定长字符串类型；

varchar是可变长字符串，不预先分配存储空间，长度不要超过5000。如果存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响其他字段索引效率。

六、约束

约束的作用：

• not null(非空约束)
• unique(唯一约束)

  create table table_name(
  字段1；
  字段2；
  字段3 unique；
  字段4 unique key;
  constraint 约束名称 unique(字段1，字段2)
  );
   
  alter table 表名称 add unique key(字段列表);
  alter table 表名称 drop index 索引名称;
  

• primary key(主键约束）

create table table_name(
字段1；
字段2；
字段3 primary key；
constraint 约束名称 primary key(字段1，字段2)
);
 
alter table 表名称 add primary key(字段列表);
alter table 表名称 drop primary key;

• foreign key(外键约束）
• check(检查约束）8.0版本支持

  create table table_name(
  字段1；
  字段2；
  字段3 check('男'or'女')；
  constraint 约束名称 primary key(字段1，字段2)
  );
  

• default(默认值约束）

查看表约束：

select * from information_schema.table_constraints where table_name='表名';

七、视图

• 创建视图

  create view 视图名称 as 查询语句
  

• 利用视图对数据进行格式化

  create view 视图名称
  as
  select concat(e.last_name,'(',d.department_name,')') emp_info
  from emps e join depts d
  on e.department_id = d.department_id;