SQL

1.数据库基本概念

1.数据

定义:描述事物的符号序列, 数据(Data)是数据库中存储的基本对象。

数据的种类:数字、文字、图形、图像声音及其他特殊符号。

数据举例:学生记录(李明,男,1994,湖南,计算机学院)

​ 计算机中数据分为两部分:临时性数据、持久性数据

​ 数据有型(Type)与值(Value)之分:

​ 型:数据表示的类型,如整型、字符型等

值:给出了符合给定型的

2.数据库

  • 数据库——Database,简称DB。

    保存有组织的数据的容器(一个或者一组文件)。

    表的集合,具有统一的结构形式并存放于统一的存储介质内,是多种应用数据的集成,并可被各个应用程序所共享。按数据所提供的数据模式存放的。

  • 模式
    (schema),关于数据库和表的布局及特征的信息

  • 数据库的发展:层次数据库 网络式数据 关系型数据库 非关系型数据库(Redis)

  • 关系型数据库

    以由多张能互相联接的二维行列表格组成的数据库。在数据准备时,我们通常要建立表关联来分析。

    • 常用的关系型数据库有 SQL Server、MySQL、Oracle、 DB2 等,这个视企业使用为准
      • Oracle: 是目前市场占有率最大的数据库, Oracle, 安装起来很繁琐, 而且居然程序文件有 3G 之多… 用起来非常方便, 对于我这样的初学者, 有很简单的配置, 对于要求 很高的企业级应用, 也有很复杂的配置和管理方法, 有很强大的数据字典, 可以说是最实用的数据库 了, 但是查了一下, 价格不菲…
      • MySQL: MySQL 是一个很好的关系型数据库, 免费, 而且功能很全, 程序又小, 安装简单, 现在很多 网站都用 MYSQL, 在字段约束上做的差了点儿, 其他的都不错
  1. 数据库系统DBS

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t23w8VUU-1614984588385)(/Users/wu/Library/Application Support/typora-user-images/image-20210117103019204.png)]

    • 定义

      替你访问数据库。实现有组织地、动态地存储大量相关数据,提供数据处理和资源共享服务。

    • 构成:

      • 数据库(数据)
      • 数据库管理系统(软件)
      • 数据库管理员(人员)
      • 硬件平台:计算机和网络
      • 软件平台:操作系统、数据库系统开发工具、接口软件
  2. 应用系统构成:

    Database Application System,简称DBAS

    组成:DBS+应用软件+应用界面

  3. 关系数据库管理系统

    RDBMS(Relational Database Management System)关系数据库管理系统,是一种基于E.F.Codd提出的关系模型的数据库管理系统。RDBMS是SQL的基础,也是所有现代数据库系统(如MS SQL Server、IBMDB2、Oracle、MySQL和MicrosoftAccess)的基础。

  4. SQL

    专门用来和数据库通信的语言 。结构化查询语言(Structured Query Language)简称

    一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。至于 ORACLE、DB2、Sybase、SQLServer、MySQL、MS Access 等都是数据库,虽然语法上有 差异,但是基本上都是大同小异。

  5. RDBMS中的数据存储在表中,表是最常见和最简单的数据存储形式。这个表基本上是一个相关数据条目的集合,它由许多列和行组成。

    • 字段

      每个表都被分解成更小的实体,称为字段。所谓的列

    • 记录

      记录也称为数据行,即表中存在的每个单独的条目

  6. 表间关系

    注意:虽然建立的表与表之间的关系,但是这个关系并不会被数据库维护.需要通过外键约束来通知数据库帮助我们维护表间关系

    1. 1 对1
    2. 1对多
    3. 多对多
  7. 可移植性
    portable,能运行在多个系统上的代码成为可移植的

mysq和 redis区别

redis:内存型非关系数据库,数据保存在内存中,速度快
mysql:关系型数据库,数据保存在磁盘中,检索的话,会有一定的lo操作,访问速度相
对慢

2. 其他

1.事务

  1. 事务

    一个事情可以分为多个步骤,这些步骤要么全部成功,要么全部失败,这件事情就可以称之为一个事务。一个sql语句,就是一个事务。四个特性:(ACID:ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。)

    1. 原子性:要么全部完成,要么全部不完成
    2. 一致性:事务执行前后,数据在完整性上仍然保持一致
    3. 隔离性:避免脏数据,两个事务相互隔离。通过串行化或序列化请求,保证系统在同一时间仅有一个请求用于同一数据
    4. 持久性:不回滚,一旦提交,对数据库所作的更改便持久的保存在数据库之中,并不会被回滚
  2. 管理事务处理

    transaction processing
    可以用来维护数据库的完整性,它保证成批的mysql操作要么完全执行,要么完全不执行;

    ​ 案例
    ​ 给系统添加订单的过程
    ​ 本质是一种机制(管理方案),保证一组操作不糊中途听通知,它们或者作为整体执行,或者不执行。如果没有错误发生,整组语句提交给数据库表;如果发生错误,则进行回退以恢复数据库到某个已知且安全的状态。
    ​ 事务
    ​ transaction,指的是一组SQL语句
    ​ 回退
    ​ rollback,指的是撤销指定SQL语句的过程
    ​ 提交
    ​ commit,指的是将未存储的SQL语句结果写入数据库表
    ​ 保留点
    ​ savepoint,指的是事务处理中设置的临时占位符(placeholder)你可以对它发布回退
    ​ 回退案例
    ​ select * from text; --查看表内容
    start transaction; --开启事务
    delete from text; --删除有一个表中所有数据
    select * from text; --确认已经执行
    rollback; --回退
    select * from text; --确认回退成功
    ​ 回退的范围
    ​ 包括:insert、update、delete
    ​ 不包括:select、create、drop
    ​ 隐含提交
    ​ implicit commit,即提交动作是自动进行的(在事务处理中则需要明确提交)
    ​ 提交
    ​ start transaction;
    delete from text where vend_id = 1001;
    delete from text where prod_price=50;
    commit;
    ​ 如果第一条delete起作用而第二条delete失败,则两条delete都不会提交;
    ​ 隐含事务关闭
    ​ 当commit或者rollback语句执行后,事务会自动关闭(将来的更改会隐含提交)
    ​ 保留点
    ​ select * from text;
    start transaction;
    delete from text where vend_id = 1002;
    savepoint delete1; --设置保留点
    delete from text where vend_id = 1003;
    rollback to delete1; --回撤到保留点
    select * from text;
    ​ 保留点越多越好
    ​ 释放保留点
    ​ 保留点在事务处理完成后自定释放(也可以用release savepoint)明确释放
    ​ 更改默认的提交行为
    ​ 默认状态下,mysql行为是自动提交所有更改;
    可指示mysql不自动提交:set autocommit=0;
    全球化和本地化
    ​ 字符集和校对顺序
    ​ 字符集
    ​ 字母和符号的集合
    ​ 编码
    ​ 某个字符集成员的的内部表示
    ​ 校对
    ​ 规定字符如何比较的指令
    ​ 大小写,不同语言的字符集
    ​ 使用字符集和校对顺序
    ​ show character set;
    ​ show variables like ‘collation%’;
    ​ 作用范围
    ​ 字符集很少是服务器范围的设置,不同的表甚至不同的列都有可能需要不同的字符集,而且两者都可以在建表的时候指定。
    ​ 表
    ​ create table mytable
    ​ (column1 INT,
    ​ column2 varchar(10)
    )DEFAULT CHARACTER SET hebrew
    ​ COLLATE hebrew_general_ci;
    ​ 对整个表指定给了字符集和校对
    ​ 列
    ​ create table mytable
    ​ (column1 INT,
    ​ column2 varchar(10)
    ​ column3 varchar(10) character set latin1 collate latin1_general_ci
    )DEFAULT CHARACTER SET hebrew
    ​ COLLATE hebrew_general_ci;
    ​ 对整个表和特定的列指定了字符集和校对
    ​ 在select语句中设定
    ​ select * from customers
    order by
    cust_city,cust_name collate latin1_general_cs;
    ​ 临时指定,区分大小写
    ​ 串的字符集转换
    ​ 如果绝对需要,串可以以在字符集之间进行转换,可以用cast()或者convert函数

2. 使用存储过程

  1. 本质: 存储过程本质是函数;

  2. 调用CALL存储过程的执行称为调用
    CALL productpricing(@pricelow,@pricehigh,@priceaverage) #计算并返回产品最低、最高和平均价格

  3. 不带参数的存储过程

    1. 创建

      CREATE PROCEDURE productpricing()
      BEGIN
      select avg(prod_price) as priceaverage
      from products;
      END;

      1. 优化
        解决分隔符的执行

         	DELIMITER //  #重新定义分隔符
        

      CREATE PROCEDURE productpricing()
      BEGIN
      select avg(prod_price) as priceaverage
      from products;
      END // #创建过程(保证遇到;不执行)
      DELIMITER ; #重新定义分隔符

      1. 调用
        CALL productpricing();
        就和调用函数一样

      2. 删除
        drop procedure productpricing;

  4. 带参数的存储过程
    带变量的过程
    delimiter //

    1. CREATE PROCEDURE productpricing(
      OUT pl Decimal(8,2),
      OUT ph Decimal(8,2),
      OUT pa Decimal(8,2)
      )
      BEGIN
      SELECT Min(prod_price)
      INTO pl
      From products;
      SELECT Max(prod_price)
      INTO ph
      From products;
      SELECT Avg(prod_price)
      Into pa
      From products;
      END;//
      delimiter ;
      CALL productpricing(@pricelow,@pricehigh,@priceaverage);
      没有任何返回值
      select @pricaverage;
      select @pricelow;
      select @pricehigh;
      调用带变量的过程2
      CREATE PROCEDURE ordertotal(
      IN onumber INT,
      OUT ototal Decimal(8,2))
      BEGIN
      SELECT Sum(item_price*quantity)
      FROM orderitems
      where order_num = onumber
      INTO ototal;
      END;
      CALL ordertotal(20005,@total);
      SELECT @total;
      CALL ordertotal(20009,@total)
      SELECT @total;
  5. 建立智能存储过程

  6. 检查存储过程
    SHOW CREATE PROCEDURE ordertotal;
    SHOW PROCEDURE STATUS;
    SHOW PROCEDURE STATUS LIKE ‘ordertotal;

3.使用游标

​ 暂时卡在open ordernumbers;P188

4.使用触发器

​ 你想要某条语句在事件发生时自动自行
​ MySQL响应特定语句而自动执行的一条MySQL语句(或位于BEGIN和END之间的一组语句,
包括DELETE,INSERT,UPDATE,其他语句不支持触发器
​ 只有表支持触发器(视图、临时表不支持),每个表最多支持6个触发器(每条INSERT、UPDATE、DELETE的之前和之后),单一触发器不能与多个事件、或多个表关联
​ 创建
​ CREATE TRIGGER
​ CREATE TRIGGER newproduct
AFTER INSERT ON products
FOR EACH ROW
SELECT ‘product added’;
​ 每插入一行都显示 ‘product added’
​ 删除
​ DROP TRIGGER newproduct;

5.安全管理

  1. 访问控制
    不要使用root
    通常防止的是无意的错误
    不同的需求不同的账号权限
  2. 管理用户
    创建用户账号
    use mysql; --一个默认数据库
    select user from user;–user用来存放用户信息
    create user ben identified by ‘passwd123’;–使用grant或者insert grant也可以创建用户,但是create user是最简单清晰的
    rename user ben to max;–重命名一个用户账号
    删除用户账号
    drop user max;–删除用户账号即想干权限
    设置访问权限
    show grants for max; --显示账号max 的权限
    grant select on crashcourse.* to max; --为账号max赋予指定数据库的访问权限(只读)
    show grants for max;–确认这个更改
    revoke select on crashcourse.* from max;
  3. –grant 授予
  4. –revoke 废除,取消,撤回
    grant和revoke 在几个层次上控制访问权限
    权限列表
  5. 更改口令
    set password for max = password(“password”) --必须传递到函数中
    set password = password(‘newpasswd123’)–重设自己的密码

6. 数据库维护

  1. 备份数据
    刷新未写数据
    flush tables;–保证所有数据都被写到磁盘(包括索引数据)
    方案1
    mysqldump,转储所有数据库内容到某个外部文件
    方案2
    mysqlhotcopy从一个数据库复制所有数据
    方案3
    backup table或者select into outfile转储所有数据到某个外部文件
  2. 进行数据库维护
    analyze table --用来检查表键是否正确
    check table --用来针对许多问题对表进行检查
    repair table --修复有问题的表
    optimize table --删除大量数据后收回所用的空间
  3. 诊断启动问题
    mysql服务器自身通过在命令行上执行mysqld启动
    重要命令选项
    –help 显示帮助
    –safe-mode 装载减去某些最佳配置的服务器
    –verbose显示全文本消息
    –version显示版本信息
    查看日志文件
    错误日志
    包含启动和关闭问题以及任意关键错误的细节
    通常名为hostname.err位于data目录中
    查询日志
    记录所有mysql活动,在 诊断问题时非常有用,
    通常名为hostname.log位于data目录中
    二进制日志
    记录更新过数据的所有语句,通常名为hostname-bin位于data目录内
    缓慢查询日志
    记录执行缓慢的任何查询,往往用来协助优化数据库。通常名为hostname-slow.log,位于data目录中
    备注
    使用flush logs语句刷新和重新开始所有日志文件
    改善性能

7.变量

variable,内存中一个特定的位置,用来临时存储数据
所有mysql的变量都要以 @开始

Hive

Hadoop的一个数仓工具。提供SQL查询的语音:Hive SQL。Hadoop解决大数据储存和大数据分析,但系统启动慢,计算量小时响应速度相对于MySQL慢。目前通用的方式是:通过 Hive SQL对原数据集中处理,将处理好的数据导入MySQL中,这样前端能快速调用。

4. MYSQL

1.安装

环境:mac

  1. 安装教程

    1. 安装包:第一个

    2. 环境变量配置

  2. 错误处理

    1. 报错 ERROR 1064 (42000) 语法错误
    2. 报错 ERROR 1045 (28000) 解决:密码重置
    3. 报错: 1175错误–mysql无法执行update或者delete命令
  3. 数据库操作

    1. 连接 mysql -u root -p

      环境变量配置好了,在终端输入mysql -u root -p可以登录mysql,不然必须切换到/usr/local/mysql/bin目录下

    2. 创建 create database数据库名

    3. 删除 drop database数据库名

    4. 查看 show database

      1. 使用命令show global variables like ‘port’;查看端口号

      2. 查看mysql基本信息:status;

    5. 选择use数据库名- show tables

    6. 退出:quit或exit

  4. 学习网站:

    1. W3C
  5. mySQL技能体系

    1. 创建/连接
    2. 增删查改
    3. 排序分组
    4. 索引
    5. 函数运算

SQL

1.语法规则

  • SQL语句总是以关键字开始,如SELECT、INSERT、UPDATE、DELETE、DROP、CREATE。

  • SQL语句以分号结尾。

    分号是分离数据库系统中每个SQL语句的标准方法,这样您就可以在对服务器的同一请求中执行多个SQL语句。

  • SQL在文本值周围使用单引号(大多数数据库系统也接受双引号)

  • 由于数据库类型以及数据储存方式的不同,对应的SQL语法会有区别,但结构基本相同。

1.编程规范

  1. 大小写

    不区分;建议 关键字使用大小写,表名列名使用小写,如下

    SELECT col_1, col_2, col_3,
        COUNT(*)
      FROM tbl_A
     WHERE col_1 = 'a'
       AND col_2 = ( SELECT MAX(col_2)
                       FROM tbl_B
                      WHERE col_3 = 100 )
     GROUP BY col_1, col_2, col_3
    
  2. 命名

    • 数据库名称、表名称、字段名,不要用保留字,不能以数字开头必须使用小写字母,并采用下划线分割

      如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。建立两个名字一样但大小写不一样的字段。

    • 引用表、列名用英文单引号

    • 多个列用逗号隔开:

    • 标准 SQL 中规定表名的第一个字符应该是字母

    • 库名以 d 开头,表名以 t 开头,字段名以 f_ 开头

      比如表 t_crm_relation,中间的 crm 代表业务模块名

    • 视图以view_开头,事件以event_开头,触发器以trig_开头,存储过程以proc_开头,函数以func_开头_

    • 普通索引以idx_col1_col2命名,唯一索引以uk_col1_col2命名(可去掉f_公共部分)。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)

    • 表名要有意义,且名、表名、字段名禁止超过32个字符

      库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符

    • 临时库、表名须以tmp加日期为后缀

      如 t_crm_relation_tmp0425。备份表也类似,形如 _bak20160425 。

  3. 注释,有单行注释和多行注释,如下

    -- 单行注释
    -- 从SomeTable中查询col_1 
    # 从SomeTable中查询col_1 
    SELECT col_1
      FROM SomeTable;
    
    /*
    多行注释
    从 SomeTable 中查询 col_1 
    */
    SELECT col_1
      FROM SomeTable;
    

    多行注释很多人不知道,这种写法不仅可以用来添加真正的注释,也可以用来注释代码,非常方便

  4. 缩进

    增强可读性:灵活使用空格和缩进来增强可读性——两大法宝空白隔道与垂直间距

    1. 利用空格保持关键字对齐
    2. 在等号前后(=)在逗号后(,)单引号前后(’)加上空格
    3. 子查询缩进并对齐
    select name,id,sex
    from (select *
    from school_score
    where class_cd=110)
    where sex = 'man'
    and exam_dt = '2016-06-01';
    
    --空白隔道+垂直间距+大小写+缩进
    SELECT name, id, sex
      FROM (SELECT *
              FROM school_score
             WHERE class_cd = 110)
     WHERE sex = 'man'
       AND exam_dt = '2016-06-01';
    
  5. 空格

    代码中应该适当留有一些空格,如果一点不留,代码都凑到一起, 逻辑单元不明确,阅读的人也会产生额外的压力,以下分别是是好的与坏的示例

    -- 好的示例
    SELECT col_1
      FROM tbl_A A, tbl_B B
     WHERE ( A.col_1 >= 100 OR A.col_2 IN ( 'a', 'b' ) )
       AND A.col_3 = B.col_3;
    
    -- 坏的示例
    SELECT col_1
      FROM tbl_A A,tbl_B B
     WHERE (A.col_1>=100 OR A.col_2 IN ('a','b'))
       AND A.col_3=B.col_3;
    
  6. 客户端配置

    1. 客户端字符串:utf8

2. 语言分类

1. DML

数据操作语言(DML:Data Manipulation Language):其语句包括动词INSERT,UPDATE和DELETE。它们分别用于添加,修改和删除表中的行。也称为动作查询语言。

  1. 插入 INSERT

    INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

    有自增时不可以用

  2. 修改 UPDATE

    UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

  3. 查看 SELECT

    SELECT 列名称 FROM 表名称

    SELECT *FROM ‘wu’

    Select now() 时间

    Select Version() 版本

    Select user() 用户

  4. 删除 DELETE

    DELETE FROM 表名称 WHERE 列名称

2. DDL

数据库定义语言(DDL):(data definition language)其语句包括动词CREATE和DROP。在数据库中创建新表或删除表(CREAT TABLE 或 DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

  1. 创建 CREAT

    Create database database1

    CREATE TABLE 表名称

    (列名称1 数据类型,

    列名称2 数据类型,

    … )顺序:列名、数据类型、长度、默认、主键、非空、unsigned、自增、zerofil

  2. 删除 DROP

    DROP TABLE 表名称

    DROP DATABASE 数据库名称

  3. 修改字段 ALTER

    ALTER TABLE table_name COMMAND_NAME field_name

    1. 表重命名 RENAME

      ALTER TABLE 【表名字】 RENAME 【表新名字】

    2. 添加字段 ADD

      alter table MyClass add passtest int(4) default '0’;

    3. 删除字段 DROP

      ALTER TABLE yanDROP sex2 ;

    4. 修改原字段名称及类型 CHANGE

      ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

    5. 添加注释 modify column

      ALTER TABLE yan MODIFY COLUMN sex2INT COMMENT ‘boy=1,girl=0’;

    6. 修改约束 add constraint

      1. 添加主键约 primary key

        alter table 表名 add constraint 主键 (形如:PK_表名) primary key 表名(主键字段);

      2. 添加外键约束 foreign key

        alter table 从表 add constraint 外键(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);

    7. 删除

      1. 主键约束

        alter table 表名 drop primary key;

      2. 删除外键约束

        alter table 表名 drop foreign key 外键(区分大小写);

    8. 视图 VIEW

3. DCL

数据控制语言(DCL):用来授予或回收访问数据库的某种特权,并控制 数据库操纵事务发生的时间及。它的语句通过GRANT或REVOKE获得许可,确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

  • GRANT

  • REVOKE

  • COMMIT

3.数据类型

  • SQL 中通用的数据类型

    数据类型描述
    CHARACTER(n)字符/字符串。固定长度 n。
    VARCHAR(n) 或 CHARACTER VARYING(n)字符/字符串。可变长度。最大长度 n。
    BINARY(n)二进制串。固定长度 n。
    BOOLEAN存储 TRUE 或 FALSE 值
    VARBINARY(n) 或 BINARY VARYING(n)二进制串。可变长度。最大长度 n。
    INTEGER§整数值(没有小数点)。精度 p。
    SMALLINT整数值(没有小数点)。精度 5。
    INTEGER整数值(没有小数点)。精度 10。
    BIGINT整数值(没有小数点)。精度 19。
    DECIMAL(p,s)精确数值,精度 p,小数点后位数 s。例如:decimal(5,2) 是一个小数点前有 3 位数小数点后有 2 位数的数字。
    NUMERIC(p,s)精确数值,精度 p,小数点后位数 s。(与 DECIMAL 相同)
    FLOAT§近似数值,尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。
    REAL近似数值,尾数精度 7。
    FLOAT近似数值,尾数精度 16。
    DOUBLE近似数值,尾数精度 16。
    DATE存储年、月、日的值。
    TIME存储小时、分、秒的值。
    TIMESTAMP存储年、月、日、小时、分、秒的值。 自动存储记录修改时间
    INTERVAL由一些整数字段组成,代表一段时间,取决于区间的类型。
    ARRAY元素的固定长度的有序集合
    MULTISET元素的可变长度的无序集合
    XML存储 XML 数据
    • 同数据库平台上某些数据类型的通用名称

      数据类型AccessSQLServerOracleMySQLPostgreSQL
      booleanYes/NoBitByteN/ABoolean
      integerNumber (integer)IntNumberInt IntegerInt Integer
      floatNumber (single)Float RealNumberFloatNumeric
      currencyCurrencyMoneyN/AN/AMoney
      string (fixed)N/ACharCharCharChar
      string (variable)Text (<256) Memo (65k+)VarcharVarchar Varchar2VarcharVarchar
      binary objectOLE Object MemoBinary (fixed up to 8K) Varbinary (<8K) Image (<2GB)Long RawBlob TextBinary Varbinary

4. 运算

1.运算符

运算符是保留字或主要用于SQL语句的WHERE子句中的字符,用于执行操作

  1. 算术运算符

    运算符描述例子 a 的值是:10,变量 b 的值是:20
    +加法,执行加法运算。a + b 得到 30
    -减法,执行减法运算。a - b 得到 -10
    *乘法,执行乘法运算。a * b 得到 200
    /用左操作数除右操作数。b / a 得到 2
    %用左操作数除右操作数并返回余数。b % a 得到 0
  2. 比较运算符

    运算符描述
    =检查两个操作数的值是否相等,如果是,则条件为真(true)。
    !=检查两个操作数的值是否相等,如果值不相等则条件为真(true)。
    <>检查两个操作数的值是否相等,如果值不相等则条件为真(true)。
    >检查左操作数的值是否大于右操作数的值,如果是,则条件为真(true)。
    <检查左操作数的值是否小于右操作数的值,如果是,则条件为真(true)。
    >=检查左操作数的值是否大于或等于右操作数的值,如果是,则条件为真(true)。
    <=检查左操作数的值是否小于或等于右操作数的值,如果是,则条件为真(true)。
    !<检查左操作数的值是否不小于右操作数的值,如果是,则条件变为真(true)。
    !>检查左操作数的值是否不大于右操作数的值,如果是,则条件变为真(true)。
  3. 逻辑运算符

    运算符描述
    ALLALL运算符用于将值与另一个值集中的所有值进行比较。
    ANDAND运算符允许在SQL语句的WHERE子句中指定多个条件。
    ANYANY运算符用于根据条件将值与列表中的任何适用值进行比较。
    BETWEENBETWEEN运算符用于搜索在给定最小值和最大值内的值。
    EXISTSEXISTS运算符用于搜索指定表中是否存在满足特定条件的行。
    ININ运算符用于将值与已指定的文字值列表进行比较。
    LIKELIKE运算符用于使用通配符运算符将值与类似值进行比较。
    NOTNOT运算符反转使用它的逻辑运算符的含义。 例如:NOT EXISTS, NOT BETWEEN, NOT IN等等,这是一个否定运算符。
    OROR运算符用于组合SQL语句的WHERE子句中的多个条件。
    IS NULLIS NULL运算符用于将值与NULL值进行比较。
    UNIQUE集合并运算 UNIQUE运算符搜索指定表的每一行的唯一性(无重复项)。
    EXPECT集合差运算
    INTERSECT集合交运算
  4. 否定条件运算符

2. 函数

  1. 除法

    SELECT TRUNCATE(t/100,2) as g from test

2.Abs() Cos() Mod() Exp() Pi() Rand() Sin() Squrt() Tan()

5.Date函数

注意:

1. 当我们处理日期时,最困难的任务可能是确保插入日期的格式与数据库中日期列中的格式相匹配。
2. 日期加 单引号
  1. 案例:提取日期字段的天数 并且构造新的日期 提取周数 并且比较
SELECT 
	a.`’s_id‘`
	,a.`’s_name‘`
	,a.`’s_birth‘`
FROM Student a
WHERE WEEK(MAKEDATE(2020,DAYOFYEAR(a.`’s_birth‘`))) = WEEK(now())
  1. MySQL 中最重要的内置日期函数:

    函数描述格式
    NOW()返回当前的日期和时间
    CURDATE()返回当前的日期
    CURTIME()返回当前的时间
    DATE()提取日期或日期/时间表达式的日期部分YYYY-MM-DD
    DATETIMEYYYY-MM-DD HH:MM:SS
    TIMESTAMPYYYY-MM-DD HH:MM:SS
    YEARYYYY 或 YY
    EXTRACT()返回日期/时间的单独部分
    DATE_ADD()向日期添加指定的时间间隔
    DATE_SUB()从日期减去指定的时间间隔
    DATEDIFF()返回两个日期之间的天数
    DATE_FORMAT用不同的格式显示日期/时间
    datepart缩写
    yy, yyyy
    季度qq, q
    mm, m
    年中的日dy, y
    dd, d
    wk, ww
    星期dw, w
    小时hh
    分钟mi, n
    ss, s
    毫秒ms
    微妙mcs
    纳秒ns

1.日期比较大小

dayofyear

week

  1. 查询某天是本年第几周]
  2. SELECT WEEK(date_add(‘2012-12-31’,interval 6 day),2);

2. 日期加减天数

  1. datediff(date1,date2):两个日期相减,得到相减(date1减date2)之后的“天数”

6.约束 CONSTRAINTS

  1. 说明

    SQL约束用于指定表中数据的规则。作用于数据表中列上的规则,用于限制表中数据的类型。约束的存在保证了数据库中数据的精确性和可靠性。

    • 约束有列级和表级之分,列级约束作用于单一的列,而表级约束作用于整张数据表。
    • 约束可以在创建表时规定(通过 CREATE TABLE 语句),或者在表创建之后规定(通过 ALTER TABLE 语句)。
  2. 完整性约束

    完整性约束用于保证关系型数据库中数据的精确性和一致性。对于关系型数据库来说,数据完整性由参照完整性(referential integrity,RI)来保证。

    有很多种约束可以起到参照完整性的作用,这些约束包括主键约束(Primary Key)、外键约束(Foreign Key)、唯一性约束(Unique Constraint)以及上面提到的其他约束。

    如果没有为约束提供一个名字,那么ORACLE会分配一个系统生成的唯一名字,以SYS_开头,你可以使用关键字CONSTRAINTS后面跟随相关的约束名字来为约束指定名字.

  3. 语法

    
    1.主键约束
    添加:alter table  table_name add primary key (字段)
    删除:alter table table_name drop primary key
    2.非空约束
    添加:ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;
    注意:添加非空约束的这一列的值不能已经存在空值
    删除:ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
    3.唯一约束
    添加:alter table table_name add unique 约束名(字段)
    删除:alter table table_name drop key 约束名
    4.自动增长
    添加:alter table table_name  modify 列名 int  auto_increment
    删除:alter table table_name modify 列名 int  
    5.外键约束
    添加:alter table table_name add constraint 约束名 foreign key(外键列) 
    references 主键表(主键列)
    删除:
    第一步:删除外键
    alter table table_name drop foreign key 约束名
    第二步:删除索引
    alter  table table_name drop  index 索引名
    [^1]: 
    约束名和索引名一样
    6.默认值
    添加:alter table table_name alter 列名  set default '值'
    删除:alter table table_name alter 列名  drop default
    
    
    

7. 常用操作归纳

数据库

create、show、use、alter、drop、select

数据表

create、show|desc、alter、drop

表数据

insert、delete|truncate、updat、select、

排序

针对相同的表进行的连接被称为“自连接”(self join),这个技巧常常被人们忽视,其实是有挺多妙用的

在 db 中,我们经常需要按分数,人数,销售额等进行排名,有 Oracle, DB2 中可以使用 RANK 函数进行排名,不过在 MySQL 中 RANK 函数未实现,这种情况我们可以使用自连接来实现,如对以下 Products 表按价格高低进行排名

图片

使用自连接可以这么写:

-- 排序从 1 开始。如果已出现相同位次,则跳过之后的位次 
SELECT P1.name,
       P1.price,
       (SELECT COUNT(P2.price)
          FROM Products P2
         WHERE P2.price > P1.price) + 1 AS rank_1
  FROM Products P1 
  ORDER BY rank_1;

结果如下:

name price rank 
----- ------ ------ 
橘子    100     1 
西瓜     80     2 
苹果     50     3 
葡萄     50     3 
香蕉     50     3 
柠檬     30     6

排名

  1. row_number

    row_number() OVER(order by e.salary desc)

  2. rank

  3. dense_rank

  4. ntile

8.INDEX 索引

  1. 介绍

    索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时。索引能够轻易将查询性能提高好几个数量级。

    • 原理

      通过数据结构不断地缩小想要获取数据的范围。相当于字典:先定位到章,然后定位到该章下的一个小节,然后找到页数。

    • 索引的数据结构

      • hash类型的索引:查询单条快,范围查询慢
      • btree类型的索引:[b+树](https://onedrive.live.com/view.aspx?resid=36F967217A52BE25%21151&id=documents&wd=target%28SQL1.one%7CD7A9AF92-E95A-4822-B82D-020BC698D968%2F%29
        onenote:https://d.docs.live.net/36f967217a52be25/文档/数据分析/SQL1.one#24.索引&section-id={D7A9AF92-E95A-4822-B82D-020BC698D968}&page-id={AEB9F2E9-38DC-4D29-B5A3-CA60E5C03A2C}&object-id={37CD1929-746A-4A37-9395-F92D3B0F6F5F}&5A),层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)
    • 不同的存储引擎支持的索引类型也不一样

  2. 索引分类

    1. 普通索引index :加速查找
    2. 唯一索引
      主键索引:primary key :加速查找+约束(不为空且唯一)
      唯一索引:unique:加速查找+约束 (唯一)
    3. 联合索引
      -primary key(id,name):联合主键索引
      -unique(id,name):联合唯一索引
      -index(id,name):联合普通索引
    4. 全文索引fulltext :用于搜索很长一篇文章的时候,效果最好。
    5. 空间索引spatial :了解就好,几乎不
  3. 什么时候应当避免使用索引?

    • 小的数据表不应当使用索引;
    • 需要频繁进行大批量的更新或者插入操作的表;
    • 如果列中包含大数或者 NULL 值,不宜创建索引;
    • 频繁操作的列不宜创建索引。
  4. 注意

    1. 一定是为搜索条件的字段创建索引

    创建单列索引还是聚簇索引,要看每次查询中,哪些列在作为过滤条件的 WHERE 子句中最常出现。

    如果只需要一列,那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列,那么聚簇索引就是最好的选择。

    1. 在表中已经有大量数据的情况下,建索引会很慢,且占用硬盘空间,

    2. 索引能够提高 SELECT 查询和 WHERE 子句的速度,但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。插入删除更新都很慢,只有查询快

    3. 索引的创建与删除不会对表中的数据产生影响

    4. 同 UNIQUE 约束一样,索引可以是唯一的。这种情况下,索引会阻止列中(或者列的组合,其中某些列有索引)出现重复的条目

  5. 创建

    1. 使用  CREATE INDEX index_name ON table_name; 语句,该语句允许对索引命名,指定要创建索引的表以及对哪些列进行索引,还可以指定索引按照升序或者降序排列。

    2. 单列索引

      单列索引基于单一的字段创建,其基本语法如下所示:

      CREATE INDEX index_name ON table_name (column_name);

    3. 唯一索引

      唯一索引不止用于提升查询性能,还用于保证数据完整性。唯一索引不允许向表中插入任何重复值。其基本语法如下所示:

      CREATE UNIQUE INDEX index_name on table_name (column_name);

    4. 聚簇索引:

      聚簇索引在表中两个或更多的列的基础上建立。其基本语法如下所示:

      CREATE INDEX index_name on table_name (column1, column2);

    5. 隐式索引:

      隐式索引由数据库服务器在创建某些对象的时候自动生成。例如,对于主键约束和唯一约束,数据库服务器就会自动创建索引。

  6. 添加索引

    1. ALTER table tablename ADD INDEX indexname(columname)
  7. 删除

    1. DROP INDEX

      DROP INDEX table_name.index_name;

9. 性能优化

参考1

参考2

10. 表基本操作

创建表

CREATE

  1. CREATE DATABASE

    1. 用于创建数据库,数据库名称始终应该是唯一的;在创建任何数据库之前,请确保拥有管理权限

    2. 语法 CREATE DATABASE dbname;

  2. CREATE TABLE

    1. 用于创建数据库中的表,每个表都必须有一个表名

    2. 建表语法

      1. CREATE TABLE table_name ( column_name1 data_type(size),
        column_name2 data_type(size), column_name3 data_type(size), … ); (column_name参数指定表中列的名称;data_type参数指定列的数据类型(例如varchar,integer,decimal,date等等 ;Size参数规定表中列的最大长度。

      2. create table as select 建立一摸一样的表

        1. create table table1 as select * from table2 where 1=2;创建一个表结构与table2一模一样的表,只复制结构不复制数据;注意:不能将原表中的default value一同迁移过来
        2. create table table1 as select * from table2 创建一个表结构与table2一模一样的表,复制结构同时也复制数据;
        3. create table table1(columns1,columns2) as select columns1,columns2 from table2; 创建一个表结构与table2一模一样的表,复制结构同时也复制数据,但是指定新表的列名

删除表

DROP

  1. DROP DATABASE语句已用于删除数据库、表
  2. 语法:
    1. DROP DATABASE database_name
    2. drop table table_name;

修改表

ALTER

  1. 说明:用于在现有表中添加,删除或修改列。也可以修改数据库(一般不)

  2. 语法

    1. 修改表名: alter table旧表名 rename to 新表名

    2. 修改表类型: alter table表名 modify 列名 数据类型

      ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度 新默认值 新注释;

      -- COLUMN关键字可以省略不写
      alter  table table1 modify  column column1  decimal(10,1) DEFAULT NULL COMMENT '注释';
      -- 能修改字段类型、类型长度、默认值、注释
      
    3. 增加字段: alter table表名 add[column] column definition[ first/ after某一列名]

    4. 删除字段: alter table表名 drop[column列名

    5. 字段改名: ALTER TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型;(first; after某一列名

      alter  table table1 change column1 column1 varchar(100) DEFAULT 1.2 COMMENT '注释'; -- 正常,此时字段名称没有改变,能修改字段类型、类型长度、默认值、注释
      alter  table table1 change column1 column2 decimal(10,1) DEFAULT NULL COMMENT '注释' -- 正常,能修改字段名、字段类型、类型长度、默认值、注释
      alter  table table1 change column2 column1 decimal(10,1) DEFAULT NULL COMMENT '注释' -- 正常,能修改字段名、字段类型、类型长度、默认值、注释
      alter  table table1 change column1 column2; -- 报错 
      
    6. 修改字段排序:[ first/ after某一列名

    7. 修改表的字符集为gbk

      alter table user character set gbk;

    8. 更改字段的数据类型,语法:

      1. SQL Server / MS Access:

        ALTER TABLE table_name ALTER COLUMN column_name datatype

      2. MYSQL / Oracle:

        ALTER TABLE table_name MODIFY COLUMN column_name datatype

    9. 修改数据库字符集:alter database database_name character set gbk;一般情况下不会

插入行

INSERT INTO

在表中插入数据

  1. 语法

    单行:INSERT INTO 表名称 VALUES (值1, 值2,…);也可以指定所要插入数据的列:INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)

    多行:不指定列时要确保值的顺序与表中的列顺序相同INSERT INTO table_name VALUES (1行, 2行,3行, …);

  2. insert ignore into

    当插入数据时,如出现错误时,如重复数据,将不返回错误,只以警告形式返回。所以使用ignore请确保语句本身没有问题,否则也会被忽略掉

  3. insert … select … where not exist
    根据select的条件判断是否插入,可以不光通过primary 和unique来判断,也可通过其它条件

  4. Insert into select 使用另一个表填充一个表

    1. 查询的字段与本表要插入数据的字段是一一对应的。
    2. INSERT INTO first_table_name [(column1, column2, … columnN)] SELECT column1, column2, …columnN FROM second_table_name [WHERE condition];

删除行

DELETE

  1. 作用:删除表中现有记录。

  2. 语法:

    DELETE FROM table_name WHERE condition;

    DELETE * FROM table_name;

  3. 注意

    1. SQL DELETE 语句中的 WHERE 子句! WHERE子句指定需要删除哪些记录。如果省略了WHERE子句,表中所有记录都将被删除!

    2. 处理效率:drop>trustcate>delete

    3. drop删除整个表;trustcate删除全部记录,但不删除表;delete删除部分记录

      删除表中的所有行,而不需要删除该表。这意味着表的结构、属性和索引将保持不变

    4. delete不影响所用extent,高水线保持原位置不动;trustcate会将高水线复位。

更新行

UPDATE

  1. 作用:

    UPDATE 语句用于更新表中已存在的记录。还可以使用AND或OR运算符组合多个条件。

  2. 语法

    UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;

  3. 注意

  4. WHERE子句指定哪些记录需要更新。如果省略WHERE子句,所有记录都将更新

  5. 实例

    UPDATE Customers SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
    WHERE CustomerID = 1;

数据查询

执行顺序 from\ where\ group by\ select\having\order by

SELECT

  1. 用于从数据库中选择数据。 返回的数据存储在结果表中,称为结果集。

  2. 语法如下所示:
    SELECT column name column name FROM table name[WHERE Clause][LIMIT N] [OFFSET M]

  3. 指定字段,用逗号分隔,但最后一列名称之后不能添加逗号

    SELECT column1, column2, … FROM table_name;

  4. 多个表之间用逗号分隔,用 WHERE设定条件

  5. 使用Limit属性来设定返回的记录数

  6. 通过 OFFSET制定 SELECT语句开始查询的数据偏移量

  7. 所有字段,使用 * :
    SELECT * FROM table_name;

  8. 使用AS子句来应用一个新名称。

  9. 执行顺序是先找from表,然后再where,然后Group by这些,最后Select后的语句。

  10. WHERE子句

  11. 句法:SELECT [*] FROM [TableName] WHERE [condition1]

  12. SELECT语句与WHERE和/或子句,AND, OR and NOT

  13. 如果使用AND运算符组合N个条件。所有由AND分隔的条件都必须为TRUE。

  14. 如果使用OR运算符组合N个条件。OR分隔的任何一个条件都必须为TRUE。

  15. 还可以组合AND和OR(使用括号来组成成复杂的表达式)

  16. SELECT * FROM Customers WHERE Country=‘Germany’ AND (City=‘Berlin’ OR City=‘München’);

  17. 查看当前所选的数据库:select database();

  18. ORDER BY

句法:SELECT column_name() FROM table_name ORDER BY column_name() ASC or DESC

  1. DISTINCT(区分)子句:于仅返回不同的值。

句法:SELECT DISTINCT column1, column2…columnN FROM table_name;

  1. IN子句

句法:SELECT column1, column2…columnN FROM table_name WHERE column_name IN (val-1, val-2,…val-N);

  1. LIKE (类)子句

句法:SELECT column1, column2…columnN FROM table_name WHERE column_name LIKE { PATTERN };

  1. 表达式:有不同类型的sql表达式,如下所示:

  2. 布尔型

    SELECT * FROM CUSTOMERS WHERE SALARY = 10000;

  3. 数值型

    SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ;内置函数,如avg()、sum()、count()等,用于对表或特定表列执行所谓的聚合数据计算。

    案例:

    SELECT (15 + 6) AS ADDITION

    SELECT COUNT(*) AS “RECORDS” FROM CUSTOMERS;

  4. 日期

    日期表达式返回当前系统日期和时间值:SELECT CURRENT_TIMESTAMP;
    另一个日期表达式如下所示: SELECT GETDATE();

  5. HAVING子句

句法:SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name HAVING (arithematic function condition);

  1. NULL
  • 具有NULL值的字段是没有值的字段,数据表中的 NULL 值表示该值所处的字段为空。

  • 理解NULL值与零值或包含空格的字段不同是非常重要的。具有NULL值的字段是在记录创建期间留空的字段!

  • NULL 值会给选取数据带来麻烦。不过,因为 NULL 和其他任何值作比较,其结果总是未知的,所以含有 NULL 的记录不会包含在最终结果里面。

    语法:SELECT column_names FROM table_name WHERE column_name IS NULL/ IS NOT NULL;

  1. TOP 子句
  • SELECT TOP 子句用于指定要返回的记录数量。在包含数千条记录的大型表上很有用。返回大量记录会影响性能。

  • 注:并不是所有的数据库系统都支持SELECT TOP子句。MySQL支持LIMIT子句来选择有限数量的记录,而Oracle使用ROWNUM。

  1. into
  • 定义:从一个表中复制数据,然后将数据插入到另一个新表中。用IN子句来复制表到另一个数据库中:

  • 把所有的列都复制到新表中:

    SELECT * INTO newtable [IN externaldb] FROM table1;

  • 只复制希望的的列插入到新表中:

    SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;

WHERE

  1. Where之后不能以函数作为条件,比如 count

  2. WHERE 子句 用于过滤记录提取满足指定标准的记录。WHERE子句不仅用于- -

    • SELECT语法

      句法:SELECT [*] FROM [TableName] WHERE [condition1] [AND [OR]] [condition2]…

    • UPDATE

      UPDATE “table_name” SET “column_1” = [new value] WHERE “condition”;

    • DELETE

      DELETE FROM “table_name” WHERE “condition”;

ALL\ANY\SOME

All:对所有数据都满足条件,整个条件才成立;
Any:只要有一条数据满足条件,整个条件成立,表示有任何一个满足就返回true,all表示全部都满足才返回true ;
Some的作用和Any一样 .

1.运动会比赛信息的数据库,有如下三个表:
运动员ATHLETE(运动员编号 Ano,姓名Aname,性别Asex,所属系名 Adep), 项目 ITEM (项目编

BETWEEN

  1. 用于

    选取介于两个值之间的数据范围内的值。

    选择给定范围内的值。值可以是数字,文本或日期。

    包含性的:包括开始和结束值,且开始值需小于结束值。

  2. 语法

    SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;要否定BETWEEN运算符的结果,可以添加NOT运算符: NOT BETWEEN

  3. 实例

    • 带有日期值的 BETWEEN

      SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

ORDER BY

  1. SELECT field1field2, …fieldN table_name1 table_name2,…ORDER BY field1, [field2… ASC|DESC

  2. 语法

    1. 用于对结果集按照一个列或者多个字段进行排序。

      oder by 多个不需要括号

    2. 默认按照升序对记录进行排序。如果需要按照降序对记录进行排序,您可以使用 DESC 关键字。

      关键字ASC=Ascending 升序 ; 关键字DESC=Descending 降序

    3. Order By +Limit 找出一列中最高/ 最低值
      Select prod_price from products ORDER BY prod_price DESC LIMIT 1;

    4. **位置靠后:**可以使用 where like等 但不能放在 where 前,通常是所有关键字中最后一个执行的关键字。

    5. 慎用,因为任何查询使用这个关键字后,查询效率都会降低。

    6. 可以用字段的位置 来代替 字段名 ORDER BY 2 DESC 代替 ORDER BY avg_score DESC

  3. 语法

    SELECT column_name,column_nam FROM table_name ORDER BY column_name,column_name ASC|DESC;

GROUP BY

  1. 说明:

    1. GROUP BY语句通常与Aggregate集合函数(COUNT,MAX,MIN,SUM,AVG)一起使用,根据一个或多个列对结果集进行分组。没有聚合函数 默认为出现的第一项
    2. GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。
    3. GROUP BY X, Y: 意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里*。*
  2. 语法

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);

  1. 对于distinct与group by的使用:

    1、当对系统的性能高并数据量大时使用group by 2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用group by

  2. groupby 在连列表中 选择相同字段 不同表时结果可能会不同 题目 33

group_concat

  1. 分 组 合 并 , 同 时 排 序

    SELECT 
    	a.s_id
    	,GROUP_CONCAT(a.c_id ORDER BY a.c_id SEPARATOR '%' )
    FROM Score a
    GROUP BY a.s_id
    
    
    image-20210121125409339

    需要进行排序的原因是防止出现这种情况:01 修的课程顺序是:01,02,03;如果有同学修课的顺序是 02,03, 01,虽然顺序不同,但是本质上他们修的课程是相同的 使用排序后都会变成:01,02,03,保证结果相同

  2. group_concat([DISTINCT] 字 段 [Order BY ASC/DESC 排 序 字 段 ] [Separator ’ 分 隔 符 '])

HAVING

  1. 一般 HAVING 是与 GROUP BY 结合使用的,having 是对 group by后的数据进行筛选过滤,必须要有group by才能用having。

  2. 但其实它是可以独立使用的,假设有如下表,第一列 seq 叫连续编号,但其实有些编号是缺失的,怎么知道编号是否缺失呢,

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kNurpgiD-1614984588388)(/Users/wu/Library/Application Support/typora-user-images/image-20210119145338396.png)]

    SELECT '存在缺失的编号' AS gap
      FROM SeqTbl
    HAVING COUNT(*) <> MAX(seq);
    

聚集函数

SUM

  1. select sum(math) from table_name where …

AVG

  1. select avg(math) from table_name where…;

MIN

MAX()

  1. MAX() 函数返回所选列的最大值

  2. 语法

    SELECT MAX(column_name) FROM table_name WHERE condition;

COUNT()

  1. 函数返回符合指定条件的行数

  2. 语法:

    1. 函数返回指定列的值的数目

      SELECT COUNT(column_name) FROM table_name WHERE condition;

    2. 表中的记录数

      SELECT COUNT(*) FROM table_name;

    3. COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目:

      SELECT COUNT(DISTINCT column_name) FROM table_name;

  3. count(*),count(1),count(列名)

    1. count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL
    2. count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL。
    3. count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空
  4. 分类统计

COUNT( CASE WHEN a.s_score > 85 THEN 1 END

stddeev 方差

varance 标准差

percentile 百分位数

IN

  1. 说明

    IN运算符允许在WHERE子句中指定多个值。

    IN运算符是多个OR条件的简写。

  2. 语法

    SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, …)/ IN (SELECT STATEMENT);

    • 不在范围内则 写not in
  3. 参数是子查询时,使用 EXISTS 代替 IN

    如果 IN 的参数是(1,2,3)是值列表时,没啥问题,但如果IN的参数是子查询时回的结果是一样,但是用 EXISTS 的 SQL 会更快:

    -- 慢
    SELECT * 
      FROM Class_A
    WHERE id IN (SELECT id 
                   FROM  CLASS_B);
    
    -- 快
    SELECT *
      FROM Class_A A 
     WHERE EXISTS
    (SELECT * 
       FROM Class_B  B
      WHERE A.id = B.id);
    

    为啥使用 EXISTS 的 SQL 运行更快呢,有两个原因

    1. 可以用到索引,如果连接列 (id) 上建立了索引,那么查询 Class_B 时不用查实际的表,只需查索引就可以了。
    2. 如果使用 EXISTS,那么只要查到一行数据满足条件就会终止查询, 不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样

    另外如果 IN 后面如果跟着的是子查询,由于 SQL 会先执行 IN 后面的子查询,会将子查询的结果保存在一张临时的工作表里(内联视图),然后扫描整个视图,显然扫描整个视图这个工作很多时候是非常耗时的,而用 EXISTS 不会生成临时表。

    当然了,如果 IN 的参数是子查询时,也可以用连接来代替,如下:

    -- 使用连接代替 IN SELECT A.id, A.name
    FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;
    

    用到了 「id」列上的索引,而且由于没有子查询,也不会生成临时表

LIKE

  1. 作用:在WHERE子句中使用LIKE运算符来搜索列中的指定模式。

  2. 通配符

    % :百分号表示零个,一个或多个字符

    _ :下划线表示单个字符

  3. 注意:

    • MS Access使用问号(?)而不是下划线(_)
    • 百分号和下划线也可以组合使用!
    • 还可以使用AND或OR运算符组合任意数量的条件。
  4. 语法:

    SELECT column1, column2, … FROM table_nameWHERE columnN LIKE pattern;

    • 使用’%‘和’_'通配符显示不同LIKE运算符的例子:

      LIKE 运算符**描述 **
      WHERE CustomerName LIKE ‘a%’查找以“a”开头的任何值
      WHERE CustomerName LIKE ‘%a’查找以“a”结尾的任何值
      WHERE CustomerName LIKE ‘%or%’在任何位置查找任何具有“or”的值
      WHERE CustomerName LIKE ‘_r%’在第二个位置查找任何具有“r”的值
      WHERE CustomerName LIKE ‘a_%_%’查找以“a”开头且长度至少为3个字符的值
      WHERE ContactName LIKE ‘a%o’找到以"a"开头,以"o"结尾的值
      WHERE City LIKE ‘[a-c]%’;以“a”、“b”或“c”开头的所有客户
      WHERE City LIKE ‘[bsp]%’;以"b"、"s"或"p"开头
      WHERE City LIKE ‘[!bsp]%’;
      WHERE City NOT LIKE ‘[bsp]%’;
      不以“b”,“s”或“p”开头:

?正则表达式进行搜索

搜索模式
(search pattern),由字面值、通配符或者两者组合构成的搜索条件

​ 9.1正则表达式介绍
​ 9.2使用正则表达式
​ 9.2.1基本字符匹配
​ 9.2.2进行OR匹配
​ 9.2.3匹配几个字符之一
​ 9.2.4匹配范围
​ 9.2.5匹配特殊字符
​ 9.2.6匹配字符类
​ 9.2.7匹配多个实例
​ 9.2.8定位符
​ 9.3小结
​ 如果我想匹配某个固定的模式,就需要正则表达式来实现

TRUNCATE

  1. 作用:

    删除全部记录,但不删除表。实际是:将直接删除原来的表, 并重新创建一个表, 因此执行速度比delete快.

  2. 注意:

    1. 在使用truncate时一定要注意,虽然可以恢复,但为了减少麻烦,还是要慎重。
    2. 如果想删除部分数据用delete,注意带上where子句,回滚段要足够大;
    3. 如果想保留表而将所有数据删除,如果和事务无关,用truncate即可;
    4. 如果和事务有关,或者想触发trigger,还是用delete;
    5. 如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。
  3. 语句

    TRUNCATE TABLE table_name

USE

  1. 选择数据库

    当SQL Schema中有多个数据库时,在开始操作之前,需要选择一个执行所有操作的数据库。

  2. 句法

    USE DatabaseName;

  3. 注意

    USE语句的基本语法如下所示,数据库名称在RDBMS中必须是唯一的:

  4. 案例:

    • 查看可用的数据库:SHOW DATABASES;
      ±-------------------+
      | Database |
      ±-------------------+
      | information_schema |
      | AMROOD |
      | TUTORIALSPOINT |
      | mysql |
      | orig |
      | test |
      ±-------------------+
      6 rows in set (0.00 sec)
    • 选择数据库:USE AMROOD;

JOIN 字段拼接

  1. 说明:

    1. 用于把来自两个或多个表的行结合起来。基于这些表之间的共同字段。
    2. 简单地说,就是先确定一个主表作为结果集,然后,把其他表的行有选择性地“连接”在主表结果集上。
    3. 联结的过程
      联结两个表,过程是第一个表的每一行和第二个表的每一行逐条匹配
  2. 最常见的 JOIN 类型:

    1. INNER JOIN:如果表中有至少一个匹配,则返回行

    2. LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行

    3. RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行

    4. FULL JOIN:只要其中一个表中存在匹配,则返回行

    5. SELF JOIN:用于将表连接到自己,就好像该表是两个表一样,临时重命名了SQL语句中的至少一个表

    6. CARTESIAN JOIN:从两个或多个连接表返回记录集的笛卡儿积

    7. 任意连接查询 (查询结果并列显示)

      SELECT * FROM
      (SELECT COUNT() AS geo_null FROM new_taobao_user WHERE ISNULL(user_geohash))T1,
      (SELECT COUNT(
      ) AS go FROM new_taobao_user)T2;

  3. 三个表 join 连接 group by 会出错 :报错 1055 原因

    1. 处理:其中两个链接后重命名

    2. 解决问题了。

      如下:

      select version(), @@sql_mode;

      SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));

      第一行是查看sql_mode。

      第二行是修改。

      执行完就ok了。

INNER JOIN

  1. 内连接inner join或join 等值联结

    select * from a inner join b on a.id = b.a_id;
    select * from a join b on a.id = b.a_id;
    select * from a, b where a.id = b.a_id;
    

    结果如下:

    img

  2. 说明:内连接可以理解为,两个表中同时满足某条件的数据记录组合。也就是表A和表B中满足条件a.id = b.a_id的所有记录。

    当表A中的一条记录对应表B中的多条记录时,会以重复的方式对应多条表B记录出现在结果集中。

    当表B中的一条记录对应表A中的多条记录时,会以重复的方式对应多条表A记录出现在结果集中。 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WHx854go-1614984588390)(/Users/wu/Library/Application Support/typora-user-images/image-20210118101547041.png)]

LEFT JOIN

  1. 说明

    1. [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8UsPKQP4-1614984588391)(/Users/wu/Library/Application Support/typora-user-images/image-20210118102213036.png)]
  2. 语法

    SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;

  3. 案例:

    (1) 左外连接

    select * from a left join b on a.id = b.a_id;
    select * from a left outer join b on a.id = b.a_id;
    
    

    img

    左外连接,会以左边的表A为主表,返回所有行,即使右表B中没有匹配的行。

    如果左边的表A在右表B中找不到一条记录,则返回表A所有记录并且表B相应的字段设为null。

    如果左边的表A在右表B中找到多条记录,则以相同表A记录和不同表B记录多条显示在结果集中。

    这种情况下,其实是把表A中所有记录都查询出来了,包括不满足条件的A记录。

    如果我们只想查出表A中满足条件的,或是不满足条件的,该怎么查?

    select` `* ``from` `a ``left` `join` `b ``on` `a.id = b.a_id ``where` `b.a_id ``is` `not` `null``;``select` `* ``from` `a ``left` `outer` `join` `b ``on` `a.id = b.a_id ``where` `b.a_id ``is` `not` `null``;
    

    img

    上面的语句查询的,就是表A中满足条件的。

    select` `* ``from` `a ``left` `join` `b ``on` `a.id = b.a_id ``where` `b.a_id ``is` `null``;``select` `* ``from` `a ``left` `outer` `join` `b ``on` `a.id = b.a_id ``where` `b.a_id ``is` `null``;
    

    img

    上面的语句查询的,就是表A中不满足条件的。

RIGHT JOIN

  1. 说明:[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1V4kfYbY-1614984588395)(/Users/wu/Library/Application Support/typora-user-images/image-20210118102423408.png)]

  2. 语法:

    SELECT column_name(s) FROM table RIGHT JOIN table2 ON table1.column_name = table2.column_name;

FULL JOIN

  1. 全连接full join ;mysql并不支持全连接,不过有相应的替代方案,就是left join union right join 来代替。
  2. 全连接会从表A和表B中返回所有的行,如果表A中的行在表B中没有匹配,或是表B中的行在表A中没有匹配,这些行都会显示,不存在的字段以null补充。

CARTESIAN JOIN

笛卡尔积查询结果数据量=左边表的数量*右边表的数量

select * from a cross join b;
select * from a, b;

自连接

  1. 自联接是一种常规联接,但表本身是连接的
  2. 语法SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
  3. 作用:查找重复行、排序

外部联结

​ 联结包含没有关联行的那些行

集合运算符 (记录 连接)

UNION

  1. 说明:

    1. UNION运算符用于组合两个或更多SELECT语句的结果集,而不返回任何重复的行
    2. UNION中的每个SELECT语句必须具有相同的列数
    3. 这些列也必须具有相似的数据类型
    4. 每个SELECT语句中的列也必须以相同的顺序排列
    5. 但是每个SELECT语句的长度不必相同
    6. 在最小情况下,UNION运算符选择一个不同的值。如果允许重复值,请使用UNION ALL。
  2. 语法

    1. SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

    2. +where : SELECT column_name(s) FROM table1 [WHERE condition] UNION SELECT column_name(s) FROM table2 [WHERE condition];

    3. union all:SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

INTERSECT

  1. 说明:组合两个SELECT语句,但只能返回与第二个SELECT语句中的一个相同的第一个SELECT语句中的行。

EXCEPT

  1. 说明: 组合两个SELECT语句,并返回第一个SELECT语句中没有第二个SELECT语句返回的行。

RENAME

  1. 语法:
    1. SQLserve:alter table table_name rename to tables_nem_name
    2. MySQL:rename table 表名 to 新表名;

NULL

  1. 说明:
    1. NULL 代表丢失的未知数据。
    2. 它与字段包含0、空字符串或者仅仅包含空格不同
    3. 默认情况下,表列可以保存 NULL 值。如果表中的列是可选的,那么我们可以插入一个新记录或更新一个现有记录,而无需向列添加一个值。这意味着该字段将存储为 NULL 。
    4. 不能使用比较操作符测试 NULL 值,例如=、<或<>。必须使用 IS NULL 和 IS NOT NULL 操作符。例如:SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL

NOT NULL 约束

  1. 保证列中数据不能有 NULL 值
  2. CREATE TABLE Persons (P_Id int NOT NULL,LastName varchar(255) NOT NULL)

DEFAULT 约束

  1. 提供该列数据未指定时所采用的默认值

  2. 语法:

    CREATE TABLE Persons ( City varchar(255) DEFAULT ‘Sandnes’ )

    1. 用类似 GETDATE() 这样的函数:CREATE TABLE Orders (OrderDate date DEFAULT GETDATE() )
    2. 当表已被创建时:
      1. **MySQL:**ALTER TABLE Persons ALTER City SET DEFAULT ‘SANDNES’
      2. **SQL Server / MS Access:**ALTER TABLE Persons ADD CONSTRAINT DF_Persons_City DEFAULT(‘SANDNES’) FOR City注:DF_Persons_City 为我们创建的默认约束的名称 约束名称一般为:约束类型简称_表名_列名
    3. 撤销
      1. **MySQL:**ALTER TABLE Persons ALTER City DROP DEFAULT
      2. **SQL Server / Oracle / MS Access:**ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT

UNIQUE 约束

  1. 保证列中的所有数据各不相同

  2. 语法:CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE )

主键约束

  1. 唯一标识数据表中的行/记录
  2. PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
  3. CREATE
    • **MySQL:**CREATE TABLE Persons ( P_Id int NOT NULL,PRIMARY KEY (P_Id) )
    • SQL Server / Oracle / MS Access: CREAT TABLE Persons ( P_Id int NOT NULL PRIMARY KEY)
    • 定义多个列的 PRIMARY KEY 约束:
      • CREATE TABLE Persons (P_Id int NOT NULL,
        LastName varchar(255) NOT NULL, CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) )
  4. ALTER (MySQL / SQL Server / Oracle / MS Access)
    1. ALTER TABLE Persons ADD PRIMARY KEY (P_Id)
    2. ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) **注释:**如果使用 ALTER TABLE 语句添加主键,必须把主键列声明为不包含 NULL 值(在表首次创建时
  5. Drop
    • **MySQL:**ALTER TABLE Persons DROP PRIMARY KEY
    • SQL Server / Oracle / MS Access::ALTER TABLE Persons
      DROP CONSTRAINT pk_PersonID

外键约束

  1. 唯一标识其他表中的一条行/记录

  2. 作用:

    • 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
    • FOREIGN KEY 约束用于预防破坏表之间连接的行为。
    • FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一
  3. CREATE

    • create table audit( EMP_no INT NOT NULL, foreign key(EMP_no) references employees_test(ID));
  4. ALTER (MySQL / SQL Server / Oracle / MS Access:)

    • ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
    • ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
  5. Drop:

    • MySQL:

      ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders

    • SQL Server / Oracle / MS Access:

      ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

CHECK 约束:此约束保证列中的所有值满足某一条件

  1. 作用:

    • CHECK 约束用于限制列中的值的范围。如果对单个列定义 CHECK 约束,那么该列只允许特定的值。
    • ?如果对一个表定义 CHECK 约束,那么此约束会基于行中其他列的值在特定的列中对值进行限制。
  2. creat

    • **MySQL:**CREATE TABLE Persons ( P_Id int NOT NULL,CHECK (P_Id>0))
    • SQL Server / Oracle / MS Access::CREATE TABLE Persons (P_Id int NOT NULL CHECK (P_Id>0)
    • 定义多个列的 CHECK 约束(MySQL / SQL Server / Oracle / MS Access:):CREATE TABLE Persons (P_Id int NOT NULL,City varchar(255),CONSTRAINT chk_Person CHECK (P_Id>0 AND City=‘Sandnes’) )
  3. ALTER:MySQL / SQL Server / Oracle / MS Access:

    • ALTER TABLE Persons ADD CHECK (P_Id>0)
    • ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City=‘Sandnes’)
  4. DROP:

    • SQL Server / Oracle / MS Access:

      ALTER TABLE Persons DROP CONSTRAINT chk_Person

    • MySQL:

      ALTER TABLE Persons DROP CHECK chk_Person

索引约束:用于在数据库中快速创建或检索数据

  1. DROP INDEX语句 已用于删除表中的索引:

    1. Access:

    DROP INDEX index_name ON table_name

    1. SQL Server:

      DROP INDEX table_name.index_name

    2. DB2 / Oracle:

      DROP INDEX index_name

    3. MySQL的DROP INDEX语法:

      ALTER TABLE table_name DROP INDEX index_name

AUTO INCREMENT约束

  1. 说明:

    在每次插入新记录时自动创建主键字段的值。Auto-increment 会在新记录插入表中时生成一个唯一的数字。 默认情况下,AUTO_INREMENT的起始值为1,每个新记录增加1。新记录,我们不需要为"ID"栏指定值(自动添加唯一值)

  2. 语法:

    1. 若要以其他值开始AUTO_INREMENT序列,请使用以下SQL语法:

      ALTER TABLE Persons AUTO_INCREMENT=100

    2. Access

      CREATE TABLE Persons ( ID Integer PRIMARY KEY AUTOINCREMENT ) 默认情况下,AUTOINCREMEN的起始值为1,每个新记录递增 1。指定“ID”栏以10开头,并递增5( AUTOINCREMENT(10,5))。

    3. Oracle

      使用序列( sequence )对象(该对象生成数字序列)创建自动增量( auto-increment )字段。

      CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10

      上面的代码创建了一个名为seq_pean的序列( sequence) 对象,它以1开头,以1递增。此对象缓存10个值以提高性能。缓存选项指定要存储多少序列值以提高访问速度。

      要在"Persons" 表中插入新记录,我们必须使用nextval函数,该函数从seq_hor序列检索下一个值:

      INSERT INTO Persons (ID,FirstName,LastName) VALUES (seq_person.nextval,‘Lars’,‘Monsen’)

      上面的SQL语句在 “Persons” 表中插入一个新记录。“ID” 列从 seq_person 序列中分配下一个数字。“FirstName"栏设置为"Lars”,“LastName"栏设置为"Monsen”

  3. 实例

    CREATE TABLE Persons ( ID int NOT NULL AUTO_INCREMENT)

Views视图

  1. 说明:

    1. 视图是可视化的虚拟表,它不包含数据,只包含使用时动态检索数据的查询;
      提供了一种MySQL的SELECT语句层次的封装,可用来简化数据处理及重新格式化基础数据或保护基础数据;。视图是基于 SQL 语句的结果集的可视化表。包含行和列,就像真正的表一样。视图中的字段是一个或多个数据库中真实表中的字段。
  2. 视图总是显示最新数据!每当用户查询视图时,数据库引擎就使用视图的 SQL 语句重新构建数据。
    3. 利用视图简化,复杂的联结,将格式转换的语句封装为视图,之后使用的时候可以简单调用视图即可
    4. 每次使用都会进行对应的计算,因此会影响到性能

  3. CREATE VIEW 语句

    CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

  4. 更新视图CREATE OR REPLACE VIEW

    CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

  5. 撤销视图:DROP VIEW

    DROP VIEW view_name

  6. 使用视图与计算字段
    select prod_id,quantity,item_price,quantity*item_price AS expanded_price
    from orderitems
    where order_num =20005;

  7. 实例

    “Current Product List”(当前产品列表)视图从"Products"表中列出了所有正在使用的产品(未停产的产品)。

    CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No

    查询上面视图:

    SELECT * FROM [Category Sales For 1997] WHERE CategoryName=‘Beverages’

AS

  1. 简介:

    1. (Alias)可以为表名称或列名称指定别名。

    2. 别名用于为表或表中的列提供临时名称,数据库中的实际表名不会更改

    3. 别名通常用于使列名更具可读性。

      1. 在下列情况下使用别名是有用的:
        • 查询涉及多个表
        • 用于查询函数
        • 需要把两个或更多的列放在一起
        • 列名长或可读性差
    4. 一个别名只存在于查询期间。

    5. 由于执行顺序的原因 select 中的As 不可以在 where \ group by 中出现

      1. 用as别名 做where条件,报错未知的列 1054

      需求:SELECT a AS b WHRER b=1; //这样使用会报错,说b不存在。

      因为mysql底层跑SQL语句时:where 后的筛选条件在先, as B的别名在后。所以机器看到where 后的别名是不认的,所以会报说B不存在。

      这个b只是字段a查询结果的一个别名

    如果非要用B做筛选条件的话:解决方案:外边再嵌套一层。
    select * from(

     select A as B from table
    

    ) t

    where t.B = XXX – 任意的筛选条件

    如果不嵌套,只能用A做筛选条件了

    1. as 中文 不需要引号
  2. 语法

    1. SELECT column_name AS alias_name FROM table_name as WHERE [condition];
    2. 如果别名包含空格,则需要双引号或方括号:SELECT CustomerName AS Customer, ContactName AS [Contact Person]
      FROM Customers;

?拼接字段

​ concat()函数
​ 多数数据库管理系统用+或者||,mysql用concat()函数
​ select concat(vend_name,’(’,vend_country,’)’) from vendors order by vend_name;

文本处理函数

mysql索引从1开始

一、mysql截取字符串函数

Left ()

left(str,index) 从左边第index开始截取

Left ()

right(str,index)从右边第index开始截取

substring()

substring(str,index)当index>0从左边开始截取直到结束 当index<0从右边开始截取直到结束 当index=0返回空

select SUBSTRING('MySQL SUBSTRING' ,10);
select SUBSTRING('MySQL SUBSTRING' FROM -10);

substring(str,index,len) 截取str,从index开始,截取len长度

SUBSTRING_INDEX(x,y,z)

分列x,根据y,并返回第z个字段

SUBSTRING_INDEX(data_1,’ ',1) ;可以根据空格分列data列,提取第一字段substring_index(str,delim,count),str是要截取的字符串,delim是截取的字段 count是从哪里开始截取(为0则是左边第0个开始,1位左边开始第一个选取左边的,-1从右边第一个开始选取右边的

subdate

6、subdate(date,day)截取时间,时间减去后面的day

subtime

7、subtime(expr1,expr2) 时分秒expr1-expr2

Upper

​ select vend_name,Upper(vend_name) AS vend_name_upcase from vendors order by vend_name;

Lengh()

Locate()

​ select locate(“ball”,“football”); #返回值5

Lower

Ltrim()

Rtrim()

soundex()

​ 根据近似的读音去匹配
​ select cust_name,cust_contact from customers where cust_contact =‘Y.Lie’; 返回 empty
​ select cust_name,cust_contact from customers where soundex(cust_contact) =soundex(‘Y.Lie’); 返回Y Lee

split

  1. 在mysql中并没有split函数,需要自己写:https://blog.csdn.net/pestd/article/details/6973772?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&dist_request_id=3d725bc6-9b16-466d-80ba-b6a89228ba7d&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.control
  2. hive sql 支持 split

SHOW

  1. 查看数据的创建,获取一条指定了原表的结构、索引等信息的

  2. 语法:

    1. 查数据库创建语句

      SHOW CREATE TABLE

    2. 显示数据库

      SHOW DATABASES

    3. 有哪些数据:

      show tables

    4. 表的创建信息

      show create table tabName;

    5. 这张数据表里都有哪些字段(列)
      show colums from customers;

    6. 描述一下这张数据表

      describe customers;

      show status;

    7. 数据库的创建信息

      show create database crashcourse;

    8. 安全权限;show grants;

    9. show errors;

    10. show warnings;

DESC 查看表结构

以student(sid,sname,birthday,sex)的查看为例。

【方式一】:desc student;

​ 语法:desc 表名;---------------------用于查看表整体结构

【方式二】:describe student;

​ 语法:describe 表名;---------------------用于查看表整体结构;

【方式三】:show columns from student;

​ 语法:show columns from 表名;--------------------------用于查看表整体结构;

【方式四】:show create table student;

​ 语法:show create table 表名;--------------------------用于查看表整体结构;

【方式五】:show full fields from student;

​ 语法:show full fields from 表名;--------------------------------- 用于查看表整体结构;

【方式六】:show fields from student;

​ 语法:show fields from 表名;----------------------------用于查看表整体结构;

【方式七】:desc student sname;

​ 语法:desc 表名 成员名;--------------------------------用于查询表中的一部分;

【方式八】:show index from student;

​ 语法:show index from 表名;------------------------------------用于查看表局部结构;这种显示不是很直观,也不是可以完全显示所有信息。

?PROCEDURE

  1. 创建储存过程

  2. 案例:

    #1. 准备表
    create table s1(
    id int,
    name varchar(20),
    gender char(6),
    email varchar(50)
    );
    \#2. 创建存储过程,实现批量插入记录
     delimiter $$ #声明存储过程的结束符号为$$
     create procedure auto_insert1()
     BEGIN
       declare i int default 1;
       while(i<3000000)do
         insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));
         set i=i+1;
       end while;
     END$$ #$$结束
     delimiter ; #重新声明分号为结束符号
    
    \#3. 查看存储过程
     show create procedure auto_insert1\G 
    
    \#4. 调用存储过程
     call auto_insert1();
    

DISTINCT

  1. 去重

    1. select 后直接使用,对所有记录去重
      1. select distinct name1 from chengji
    2. 在聚合函数中使用
  2. 案例:

CONCAT

CONCAT 函数用于将两个字符串连接为一个字符串。

不同数据库连接字符串的方法不完全相同,MySQL、SQL Server、Oracle等数据库支持CONCAT方法,而SQLite数据库只支持用**连接符号"||"**来连接字符串

REPLACE

  1. 字符串替换函数
  2. 语法
    1. 方法一 全字段更新替换 Replace into 字段 values ()
    2. Update set replce(X,Y,z) 其中X是要处理的字符串,Y是X中将要被替换的字符串,Z是用来替换Y的字符串,最终返回替换后的字符串。

LENGTH

TRIGGER触发器

  1. 什么是触发器

    当我执行一个事件时,同时也去执行另外一个事件。触发器是一个特殊的存储过程,不同的是存储过程要用CALL来调用,而触发器不需要使用CALL,也不需要手工启动,只要当一个预定义的事件发生的时候,就会被MYSQL自动调用。

  2. 触发器语法

    CREATE TRIGGER <触发器名称>–触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
    { BEFORE | AFTER }–触发器有执行的时间设置:可以设置为事件发生前或后。
    { INSERT | UPDATE | DELETE }–同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。
    ON <表名称>–触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
    FOR EACH ROW–触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。
    <触发器SQL语句>–触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句, 包括复合语句,但是这里的语句受的限制和函数的一样。
    类型
    SQL Server 触发器
    SQLite 触发器(Trigger)BEGIN与END() 触发器执行的内容写出 BEGIN与END 之间,begin和end间的代码块的语句要写分号,否则不通过。

LIMIT

  • limit [offset], [rows]

  • LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

  • 语法:

    用在 SELECT 语句 末尾

    • 一个参数

      select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

    • 两个参数

      select tid from a limit 50000,200

CASE WHEN

可以用在 select 、group by (不可以重命名)、聚合函数(聚合函数+distinc + case when 基本可以完成所有的 sql 分组)、

  1. 统计:

    1. 仅仅 case when

    图片

    ​ 使用 CASE WHEN 如下

    SELECT CASE city
          WHEN '长沙' THEN '湖南' 
          WHEN '衡阳' THEN '湖南'
          WHEN '海口' THEN '海南' 
          WHEN '三亚' THEN '海南'
        ELSE '其他' END AS district,
        SUM(population) 
    FROM PopTbl
    GROUP BY district;
    
    1. 与 in 连用

      case  when  city in ( '长沙','衡阳') then ’湖南‘,
      	when  city in ('南京') then ’江苏‘
      
  2. SET 数据更新

    图片

    奇葩的规定

    1. 对当前工资为 1 万以上的员工,降薪 10%。
    2. 对当前工资低于 1 万的员工,加薪 20%。如果分开执行则会出现计算两次的现象
    UPDATE Salaries
    SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9
    WHEN salary < 10000 THEN salary * 1.2
    ELSE salary END;
    

over函数的用法

?COALESCE 函数 非空表达式

此函数作用返回参数中的第一个非空表达式,假设有如下商品,我们重新格式化一样,如果 city 为 null,代表商品不在此城市发行,但我们在展示结果的时候不想展示 null,而想展示 ‘N/A’, 可以这么做:

SELECT 
    COALESCE(city, 'N/A')
  FROM
    customers;

图片

查询空值

// An highlighted block
mysql 查询出某字段的值不为空的语句
// 1.不为空
select * from table where id <> "";
select * from table where id != "";
select * from table where is not null;
// 2.为空
select * from table where id ="";
select * from table where isNull(id);
select * from table where id is null;


查询结果导出

窗口函数

与聚类函数类似,对记录分组后进行聚合计算,但是不会为每一个组只返回一个值,而是可以返回多个值,为分组中的每条记录都返回特定值。只能用在select 语句中,使用后不能再使用 group by 。

  1. 基本结构 :

    <窗口函数> over
    (partition by <用于分组的列名>
    order by <用于排序的列名>)

  2. <窗口函数>一般有以下两种:

    1. 排序函数,包括rank,dense_rank,row_number等。
    2. 聚合函数,如sum,avg,count,max,min等。
  3. 常用函数

    1. row_number() over() 记录在同一分组内的排序
    2. percent_rank() over() 记录在同一分组内排序的分位数
    3. sum(col ) over() 记录同一分组内所有记录col 值的和 ,同一分组内记录的返回值 相同
    4. avg(col) over() 记录同一分组内所有记录col 值的平均值,同一分组内记录的返回值 相同
    5. max(col) /min(col) over() 记录同一分组内所有记录col 值的最大 、最小 ,同一分组内记录的返回值 相同
  4. topN相关

    select *
    from (
      select *,row_number() over (partition by 要分组的列名 order by 要排序的列名 desc)
      as 列别名 from 原表名
    ) as 表别名 
    where ranking <= N;
    
    
    
  5. 组内比较相关

    作为子查询 嵌套

  6. 窗口函数的移动平均

    <窗口函数>  over
    (partition by <用于分组的列名>
    order by <用于排序的列名>
    rows <N preceding/N following/current row>)
    
    

动态更新

  1. mysql 支持 update ,insert
  2. Hive SQL 建立动态分区

面试

基础

  1. 数据库事务的四个特性及含义

  2. Mysql的技术特点是什么?

    答:Mysql数据库软件是一个客户端或服务器系统,其中包括:支持各种客户端程序和库的多线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理 工具。

  3. 无法登录MySQL会有哪写原因导致?答

    1. 服务未启用
    2. 远程连接权限被关或一直为开放。
    3. 登录mysql时的用户没有登录权限
    4. 客户端与服务端的端口不一致
    5. 网络原因导致的通信故障,或服务端防火墙未开放数据库端口。
  4. CHAR和VARCHAR的区别?

    答: CHAR和VARCHAR类型在存储和检索方面有所不同 。CHAR列长度固定为创建表时声明的长度,长度值范围是1到255当CHAR值被存储时,它们被用空格填充到特定长度,检索CHAR值时需删除尾随空格。VARCHAR和CHAR的使用方面在于设计表之初的设计,两者各有千秋,VARCHAR比CHAR更省空间,但效率上略微的低于CHAR。

  5. drop,delete与truncate的区别

    答:

    • 方式

      drop直接删掉表 truncate删除表中数据,再插入时自增长id又从1开始 delete删除表中数据,可以加where字句。**

    • 恢复

      DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作

      TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

    • 内存

      表和索引所占空间。当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

    • 效率

      一般而言,drop > truncate > delete

    • 应用范围。

      TRUNCATE 只能对TABLE;DELETE可以是table和view

      TRUNCATE 和DELETE只删除数据,而DROP则删除整个表(结构和数据)。

  6. 公司的数据库现不支持emoji表情,如何解决?

    如果是utf8字符集的话,需要升级至utf8_mb4即可。

  7. 描述下过往工作经验中mysql数据库的开发规范。

  8. 如何通过Unix shell 登录 MySQL

    1. 答:/bin/mysql -h hostname | host_ip -u {userName} -p port
  9. LIKE声明中的%和_是什么意思?

    答:%对应于0个或更多字符,_只是LIKE语句中的一个字符。

  10. 如何在Unix和Mysql时间戳之间进行转换?

    答: UNIX_TIMESTAMP是从Mysql时间戳转换为Unix时间戳的

    FROM_UNIXTIME是从Unix时间戳转换为Mysql时间戳的命令

  11. 列对比运算符是什么?

    答:在SELECT语句的列比较中使用=,<>,<=,<,> =,>,<<,>>,<=>,AND,OR或LIKE运算符。

  12. MySQL查询语句中是否区分大小写?

    答:要根据操作系统而定。windows中的MySQL查询语句无需区分大小写。但在Linux系统中需要区分大小写。

    在Linxu系统中的规则如下:

    1、数据库名与表名是严格区分大小写的;

    2、表的别名是严格区分大小写的;

    3、列名与列的别名在所有的情况下均是忽略大小写的;

    4、变量名也是严格区分大小写的;

  13. 什么样的对象可以使用CREATE语句创建?

    答:DATABASE、EVENT、FUNCTION、INDEX、PROCEDURE

    TABLE、TRIGGER、USER 、VIEW

  14. MYSQL支持事务吗?

    答:在缺省模式下,MYSQL是autocommit模式的,所有的数据库更新操作都会即时提交,所以在缺省情况下,mysql是不支持事务的。

    但是如果你的MYSQL表类型是使用InnoDB Tables 或 BDB tables的话,你的MYSQL就可以使用事务处理,使用SET AUTOCOMMIT=0就可以使MYSQL 允许在非autocommit模式,在非autocommit模式下,你必须使用COMMIT来提交你的更改,或者用ROLLBACK来回滚你的更改。

  15. MYSQL数据表在什么情况下容易损坏?

    答:服务器突然断电导致数据文件损坏。 强制关机,没有先关闭mysql 服务等。

  16. 超键、候选键、主键、外键

    超键:在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选 键和主键。

    候选键:是最小超键,即没有冗余元素的超键。

    主键:数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值 (Null)。

    外键:在一个表中存在的另一个表的主键称此表的外键。

  17. 什么是事务?什么是锁?

    事务:就是被绑定在一起作为一个逻辑工作单元的SQL 语句分组,如果任何一个语句作失败那么整个操作就被失败,以后操作就会回滚到操作前状态, 或者是上有个节点。为了确保要么执行,要么不执行,就可以使用事务。要将有组语句作为事务考虑,就需要通过ACID 测试,即原子性,一致性,隔离 性和持久性。

    锁:在所以的 DBMS 中,锁是实现事务的关键,锁可以保证事务的完整性和并发性。与现实生活中锁一样,它可以使某些数据的拥有者,在某段时间内不 能使用某些数据或数据结构。当然锁还分级别的。

  18. 出现Lock wait timeout exceeded; try restarting transaction是什么原因?

    是锁等待超时。是当前事务在等待其它事务释放锁资源造成的。通过select * from information_schema.innodb_trx;可以查看到有哪些事务未释放资源。通过kill+线程号可以解决。例如kill+79干掉对应的线程。

  19. Nonrepeatable Read是什么意思?与数据库、MySQL有什么关系?

    Nonrepeatable Read – 不可重复读,是一种事务线程安全的问题。在MySQL中仅是一种隔离级别,MySQL的默认隔离级别是 – 可重复读 – Repeatable read

语法

limit

  1. 一个6亿的表a,一个3亿的表b,通过外键tid关联,你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

    解析:此题可以的关键是一个limit。

    答:

    1、如果A表TID是自增长,并且是连续的,B表的ID为索引

    答:select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

    2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。

    答:select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

Not in \Distinct

  1. 用一条SQL 语句 查询出每门课都大于80 分的学生姓名

    namekechengfenshu
    张三语文81
    张三数学75
    李四语文76
    李四数学90
    王五语文81
    王五数学100
    王五英语90

    建表语句:

    CREATE TABLE chengji(

    name varchar(20),

    kecheng varchar(20),

    fenshu int

    );

    INSERT INTO chengji VALUES(‘张三’,‘语文’,81),(‘张三’,‘数学’,75),(‘李四’,‘语文’,76),(‘李四’,‘数学’,90),(‘王五’,‘语文’,81),(‘王五’,‘数学’,100),(‘王五’,‘英语’,90);

    SELECT DISTINCT NAME FROM chengji WHERE NAME NOT IN (SELECT DISTINCT NAME FROM chengji WHERE fenshu <=80);

待解决:

  1. 一行变多行
  2. 调优 数据倾斜
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页