SQL语句（二）

[PyxYuYu]

A well-spent day brings happy sleep.

0x01 SQL

---

• 查询
  • select * from 表名 where 列名 is null
    • 查找该列为空的N行的数据
  • select * from 表名 where 列名 is not null
    • 查找该列不为空的N行的数据
  • 联表查询
  • select * from 表1，表2 where 表1.列 = 表2.列
    • 数据库中的表可通过键将彼此联系起来，主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的，在表中，每个主键的值都是唯一的，这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起
  • select * from 表1 inner join 表2 on 表1.列 = 表2.列
    • 同上面的语句，使用关键词 join 来从两个表中查询数据
    • join：如果表中有至少一个匹配，则返回行
      • inner join 和 join 相同
    • left join：即使右表中没有匹配，也从左表返回所有的行
      • select * from 表1 left join 表2 on 表1.列 = 表2.列
      • 先返回匹配的行，然后即使没有匹配，左表的未匹配行也会返回
      • 有的数据库中，也写作 left outer join
    • right join：即使左表中没有匹配，也从右表返回所有的行
      • 同上，类似，有的数据库中，也写作 right outer join
    • full join：只要其中一个表存在匹配，就返回行，即使左表没有匹配右表，右表没有匹配左表，这些未匹配的都会返回，先返回匹配，再返回未匹配的左表，接着未匹配右表
      • 有的数据库，也写作 full outer join
  • select 列名 from 表1 union select 列名 from 表2
    • union 操作符用于合并两个或多个 select 语句的结果集
    • 注意：union 内部的 select 语句必须拥有相同数量的列，列也必须拥有相似的数据类型，同时，每条 select 语句中的列的顺序必须相同
    • 默认，union 操作符选取不同的值，如果允许重复的值，使用 union all
    • union 结果集中的列名总是等于 union 中第一个 select 语句中的列名
  • select * into 新表 from 表
    • 从一个表中选取数据，将其插入另一个表中，select into 常用于创建表的备份复件或者用于对数据进行存档
    • 结合 in 子句可用于向另一个数据库中拷贝表
      • select * into 新表 in 数据库 from 表
• 创建
  • create database 库名
    • 创建数据库
  • create table 表名(列名1 数据类型 约束条件, ...)
    • 创建表
    • 约束条件
      • not null
        • 不能为空，不接受 null 值，意味着，如果不向字段添加值，就无法插入新数据或更新数据
      • unique
        • 唯一标识，为列或列集合提供了唯一性的保证
        • 注意：每个表可以有多个 unique 约束
        • MsSQL / Oracle

           create table Persons
           (
           Id_P int not null unique
           )
        

        • MySQL

           create table Persons
           (
           Id_P int not null,
           unique (Id_P)
           )
        

        • 如果表已被创建，如果需要为列添加 unique 约束，使用下面语法

           alter table 表名
           add unique (列名)
        

        • 如果需要为 unique 约束命名，并定义多个列的 unique 约束

           alter table 表名
           add constraint 约束名 unique (列1, 列2)
        

        • 取消 unique 约束（结合约束名）

           //MySQL
           alter table 表名
           drop index 约束名
        

           //MsSQL/Oracle
           alter table 表名
           drop constraint 约束名
        

      • primary key
        • 唯一标识，主键，必须包含唯一的值，不能包含 null 值，每个表都应该有一个主键，只能有一个主键
        • MsSQL / Oracle

           create table Persons
           (
           Id_P int not null primary key
           )
        

        • MySQL

           create table Persons
           (
           Id_P int not null,
           primary key (Id_P)
           )
        

        • 如果需要为 primary key 约束命名，以及多个列定义，使用下面的语法

           create table Persons
           (
           ...
           constraint 约束名 primary key (列1, 列2)
           )
        

        • 如果已经存在表的情况下，添加 primary key 主键

           alter table 表名
           add primary key (列名)
        

        • 如果存在表的情况下，为 primary key 约束命名，方法类似上面的 unique
        • 注意：在添加主键的时候，必须这一列不包含 null 值
        • 取消 primary key 约束

           //MySQL
           alter table 表名
           drop primary key
        

           //MsSQL/Oracle
           alter table 表名
           drop constraint 约束名
        

      • foreign key
        • 外键，一个表中的 foreign key 指向另一个表中的 primary key
        • foreign key 约束用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是指向那个表中的值之一
        • MySQL

           creat table 表2
           (
           ...
           foreign key (列名) references 表1(主键列)
           )
        

        • MsSQL / Oracle

           create table 表2
           (
           ...
           列名 int foreign key references 表1(主键列)
           )
        

        • 为 foreign key 约束命名

           create table 表2
           (
           ...
           constraint 约束名 foreign key (列名)
           references 表1(主键列)
           )
        

        • 已创建表，添加外键，取消外键都类似上面几种约束的情况
      • check
        • 用于限制列中的值的范围
        • MySQL

           create table 表名
           (
           列名 int not null,
           ...
           check (列名>0)
           )
        

        • MsSQL / Oracle

           create table 表名
           (
           列名 int not null check (列名>0)
           )
        

      • default
        • 用于向列中插入默认值

           create table 表名
           (
           列名 varchar(255) default 'aaa'
           )
        

        • 也可以通过函数来设置默认值

           create table 表名
           (
           列名 data default GetDate()
           )
        

  • create index 索引名 on 表名 (列名)
    • 创建索引，在不读取整个表的情况下，利用索引可以更快的查找数据
    • 用户无法看到索引，它们只能被用来加速搜索/查询
    • 注：更新一个包含索引的表比更新一个没有索引的表需要更多的时间，这是由于索引本身也需要更新，因此理想的做法是仅仅在经常被搜索的列（以及表）上面创建索引
    • 在表上创建一个简单的索引，允许使用重复的值

       create index 索引名 on 表名 (列名)
    

    • 在表上创建一个唯一的索引，索引的唯一就意味着两个行不能拥有相同的索引

      create unique index 索引名 on 表名 (列名)
    

    • 删除索引

       //MsSQL
       drop index 表名.索引名
    

       //MySQL
       alter table 表名 drop index 索引名
    

       //Oracle
       drop index 索引名
    

• alter 增删改
  • alter table 表名 add 列名 数据类型
    • 在表中，添加新列
  • alter table 表名 modify 列名 数据类型
    • 修改列的属性
  • alter table 表名 rename column 当前列名 to 新列名
    • 修改列名
  • alter table 表名 drop column 列名
    • 删除列名
  • alter table 当前表名 rename to 新表名
    • 修改表名
• 函数
  • select avg(列) from 表名
    • 求某一列的平均值
  • select count(*) from 表名
    • 返回指定列的数目
    • count(列) 表示列中有效的数据个数，null 不包括
  • select first 列名 from 表名
    • 列的第一个值
  • select last 列名 from 表名
    • 列的最后一个值
  • select max(列) from 表名
    • 列中的最大值
  • select min(列) from 表名
    • 列中的最小值
  • select sum(列) from 表名
    • 列中所有数组的总和
  • select 列名 from 表名 group by 列名
    • group by 语句用于结合合计函数，根据一个或多个列对结果集进行分组
  • select 列名 from 表名 group by 列名 having 判断条件
    • having 子句用在 group by 之后，对分组后的数据进行条件筛选
  • select ucase(列名) from 表名
    • 将列(字段)的值转换为大写
  • select lcase(列名) from 表名
    • 将列(字段)的值转换成小写
  • select mid(列, 起始位置[, 字符数量]) from 表名
    • 起始位置是1，从列中提取数据，省略则返回剩余的全部
  • select len(列名) from 表名
    • 返回列中值的长度
  • select round(列名, 保留的小数位数) from 表名
    • 将列中数值舍入指定的小数位数
  • select now() from 表名
    • 返回当前日期和时间，MsSQL 中用 getdate() 来获取
  • select format(列名, 格式) from 表名
    • 对列中数值显示进行格式化（比如：YYYY-MM-DD）