[大数据]数据库（SQL语言基础）

数据库（SQL语言基础）

主参考材料：菜鸟教程

SQL是用于管理关系数据库管理系统的一门结构化查询语言。可以对数据库中的数据进行CRUD，同时也可以对数据库模式进行创建和修改，以及数据访问控制。虽然SQL语言已经是一种标准，但是仍有很多不同版本的SQL语言。

关于SQL语言的语法主要有insert、delete、 update、select、create、alter、drop、grant、revoke等构成。SQL语句默认不区分大小写。

基础

select 语句：查询语句

select distinct column_name1, column_name2
from table_name1
where condition1 and condition2
group by condition3
having condition4
order by column_name1, column_name2 ASC;

select表示的就是查询记录；distinct就代表查询的结果中不会出现重复的元组，可以省略；后面是列的名字，可以是任意个，此外可以用 * 代替，表示所有的列名；

from后是表的名字，代表着数据是来自于哪个表的，可以是多个表；

where表示的就是条件语句，用来提取满足条件的记录；条件语句中的运算符包括=（等于）、<>（不等于，有些版本可写称!=）、>（大于）、>=（大于等于）、between……and……（在某个范围内）、like（搜索某种模式，用于字符串的模糊查询）、in（指定这对某个列的多个可能值）、is null（是空值）；多个条件子句之间可以用and、or、not连接；

group by则表示按照某种条件进行分组，一般是配合分组函数使用；

having不能单独使用，需要结合group by，用于解决where不能实现的条件判断；

order by则是排序，按照后面列名排序；结尾可以加asc（升序）和desc（降序）排列，如果不加入这两个关键字，则默认会是升序；

insert 语句：插入语句

insert into table_name1 (column_name1, column_name2)
values (value1, value2);

insert into表示的是在table_name1插入值，插入的值value和列名column_name要对应，如果后面没有列名则会按照表中列名的顺序进行赋值插入

update 语句：更新语句

update table_name1
set column_name1 = value1, column_name2 = value2
where condition1 or condition2;

update则表示更新表table_name1中记录，某一列中的值进行赋值操作，如果没有where语句进行提取的话，则会将某一列是数据都进行更新。

delete 语句：删除语句

delete from table_name1
where condition1;

表示删除table_name1中满足条件的记录，如果没有where，则会删除表中的所有记录，该语句只能删除表中的内容，并不能删除表。

高级

1. TOP语句：当面对记录条数过多时，我们则可用以下方式进行对返回条数的规定

   • MySQL语法：

     select column_name1
     from table_name1
     limit num; # 用于返回前num条记录
     

   • SQL Server/MS Access语法

     select TOP num|precent column_name1 # 表示返回前num条或百分之几的记录
     from table_name1;
     

   • Oracle语法

     select column_name
     from table_name1
     where rownum <= num; # 表示前num条记录
     

2. 模糊查询：like语法

   select column_name1
   from table_name1
   where column_name like pattern; # pattern是使用通配符表示的字符串
   

   • % 表示替代0个或多个字符
   • _ 表示替代一个字符
   • [charlist] 表示字符串中的任意单一字符
   • [^charlist]或[!charlist] 表示不在字符列中的任何单一字符

3. in语法

   select column_name1
   from table_name1
   where column_name1 in (value1, value2); # 若column_name1的值在括号内中则为真，反之为假
   

4. between语法：

   select column_name1
   from table_name1
   where colmun_name between value1 and value2; # 表示column_name的值是否在[value1,value2]区间内
   

5. SQL别名

   select column_name as alias_name # 用alias_name代替column_name，as可以省略
   from table_name;
   

6. join：用于把两个或多个表的行结合起来

   select * 
   from table_name1
   join table_name2 # 可以使用不同类型的join
   on condition1; # 连接的条件，即按照什么规则连接
   

   • inner join：如果表中有一个匹配，则返回行
   • left join：即使右表中没有匹配，也从左表返回所有的行
   • right join：即使左表中没有匹配，也可以从右表返回所有的行
   • full join：只要其中一个表中存在匹配，则返回行（但MySQL目前不支持）

7. union：操作符

   select * from table_name1
   union all # 主要用于合并两个或多个select语句的结果集，去掉all表示不允许重复的值
   select * from table_name2;
   

8. select into语句（Mysql不支持，但支持insert into……select）

   select *
   into table_name1
   from table_name2;
   

   把表table_name1中的记录插入到table_name2中，可以完成复制操作，也可以用以下语法完成复制

   create table new_table
   as
   select * from old_table;
   

9. insert into select 语句

   insert into table1 (column_names)
   select (colmun_names) from table2;
   # 将table2中column_names插入到table1中的column_names
   

10. create database语句

    create database database_name; # 创建一个名为database_name的数据库
    

11. create table语句

    create table table_name (
    	column_name1 data_type(size)
        column_name2 data_type(size)
    ); # 创建一个名为table_name的表，其中有column_name1和column_name2属性，其中后面data_type是数据类型，size是列的最大长度
    

12. SQL约束：用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止，约束可以在表创建之时规定，也可以在表创建之后。

    • not null 则该列不能为null

      创建表的时候添加

      create table table_name1 (
      	column_name1 datatype(size) not null
      );
      

      创建表之后添加not null

      alter table table_name1
      modify column_name1 datatype(size) not null
      

      删除not null约束

      alter table table_name1
      modify column_name1 datatype(size) null
      

    • unique约束：该列是一个候选键

      MySQL：

      create table table_name1(
      	column_name1 datatype(size) not null, 
          column_name2 datatype(size),
          unique(column_name1)
      );
      

      SQL Server/Oracle/MS Access

      create table table_name1 (
      	column_name1 datatype not null unique
      );
      

      MySQL/SQL Server/Oracle/MS Access：多个元素

      create table table_name1 (
      	column_name1 datatype not null,
          column_name2 datatype not null,
          column_name3 datatype,
          constraint constraint_name unique (column_name1, column_name2)
      );
      # 也可以使用alter进行修改
      alter table table_name1
      add unique(column_name1); # 一个属性
      alter table table_name1
      add constraint constraint_name unique(column_name1，column_name2); # 多个属性
      

      撤销unique约束

      MySQL：

      alter table table_name1
      drop index constraint_name;
      

      SQL Server/Oracle/MS Access:

      alter table table_name1
      drop constraint constraint_name;
      

    • primary key约束：表示该列为主键；语法与候选键约束相类似，将unique换成primary key，index 约束名换成primary key

    • foreign key约束：表示是指向另一个表的外键，语法与候选键约束相类似，将unique换成foreign key (父表列名) references 子表名(子表列名)，撤销语法index 约束名换成foreign key约束名

    • check约束：用于限制列中的范围，语法与候选键约束相类似，将unique换成check condition，撤销语法index 约束名换成constraint 约束名，要将constraint 约束名 改为 check 约束名。

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

      MySQL/SQL Server/Oracle/MS Access：

      create table table_name1 (
      	column_name1 datatype(size) default default_value
      );
      # MySQL
      alter table table_name1
      alter column_name set default default_value;
      # SQL Server/MS Access
      alter table table_name1
      add constraint constraint_name default default_value for column_name;
      # Oracle
      alter table table_name1
      modify column_name default default_value;
      

      撤销约束

      # MySQL
      alter table table_name1
      alter column_name drop default;
      #SQL Server/Oracle/MS Access
      alter table table_name1
      alter column column_name drop default;
      

13. create index语句：为表创建索引，可以让查找的效率提高

    create unique index index_name # unique表示不允许使用重复的值，去掉则表示允许使用重复的值
    on table_name(column_name);
    

14. drop语句

    # 删除索引
    drop index index_name on table_name; # MS Access
    drop index table_name1.index_name1;	# SQL Server
    drop index index_name; # DB2/Oracle
    alter table table_name1 drop index index_name; # MySQL
    # 删除表
    drop table table_name1;
    # 删除数据库
    drop database database_name;
    # 仅删除表内的数据，但不删除表本身
    truncate table table_name1;
    

15. alter table 语句

    # 增加列
    alter table table_name1
    add column_name datatype(size);
    # 删除列
    alter table table_name1
    drop column column_name;
    # 修改列
    alter table table_name1
    alter column column_name datatype(size); # SQL Server/MS Assecc
    alter table table_name1
    modify column column_name datatype(size); # MySQL/Oracle
    

16. auto increment 字段

    # MySQL
    create table table_name1 (
    	column_name datatype(size) auto_increment
    );
    alter table table_name1 auto_increment = 100; # 默认是从1开始，递增1，可以用这种方式更改
    #SQL Server
    create table table_name1 (
    	column_name datatype(size) identity(num, i) # 从num开始，递增i
    );
    # Assecc
    create table table_name1 (
    	column_name datatype(size) autoincrement # 默认地，AUTOINCREMENT 的开始值是 1，每条新记录递增 1。如果有其他需求可以改为AUTOINCREMENT(num,i)。
    );
    # Oracle
    create sequence name
    minvalue num1
    start with num2
    increment by num3
    cache num4;
    insert into table_name(increment_column, column_name)
    value (name.nextval, column_value);
    

17. view 视图，就是基于SQL语句的结果的可视化的表

    # 创建视图
    create view view_name as
    select column_names
    from table_name1;
    # 更新视图
    create or replace view view_name as
    select column_names
    from table_name1;
    # 撤销视图
    drop view view_name;