SQL上门2

SQL高级教程学习

• MySQL的字符匹配和其他数据库不同，一下语句查找（第一个字符不是h，第三个字符是m）不能用“!”

  select * from country
  where countryname rlike '^[^h].[m]';

  ^是开始的语法；[[^abc]表示非abc字符；. 号代表一个字符；[a-f]表示a到f任意字符

• IN 操作符，在集合种匹配，以下语句查找（“peoplecount”是可以为括号选项）

  select * from country
  where peoplecount in(1,3,5,7,10,13);

• BETWEEN - AND -，（MySQL）在闭区间查找
• inner join：内链接，连接两个表，按照组合的方式输出（on关键子则表示条件 从组合种筛选出符合条件的返回）

• select country.countryname,country.location,island.belong
  from country 
  inner join island
  on country.location=island.countrylocation;

  3种join   inner/left/right   +   join   分别表示两个表的交集/左表/右表 （ MySQL不支持full out join）
  这就是说，即使另外一个表没有匹配项，也要把这个表（左右所述的表）的行输出，不匹配的列则显示null

• union (all) 关键子，用于合并多个select查询的结果，all关键字允许重复的出现结果；
  注意：union内部的select结果必须具有相同的 列数、列的类型和列顺序
  
  
  
  

• SQL约束
• not null：非空，表示该列的值不能为空，否则无法插入数据；下表种 “myname” 列不能为 “null”

  create table tests
  (
      id int primary key auto_increment,
      myname char(255) not null,
      age int
  );

• unique：唯一，表示该项必须是唯一的，null可以重复；下表种 “学号” 列必须是唯一的

  create table tests
  (
      id int primary key auto_increment,
      myname char(255) not null,
      xuehao int,
      age int,
      unique(xuehao)
  );

  撤销约束：下面的SQL，将撤销tests表种xuehao列的约束

  alter table tests
  drop index xuehao;

• primary key：主键约束：表种必须只有一个主键，但是主键可以是多个列的组合；下面的SQL语句规定 id 和 xuehao 的组合为主键
• 第二句SQL用于删除主键约束，注意，MySQL删除主键之前必须先删除自增约束（使用modify改变类型或者change改名同时改掉类型）
  

  create table tests
  (
      id int auto_increment,
      myname char(255) not null,
      xuehao int,
      constraint prke primary key (id,xuehao)
  );
  
  alter table tests
  drop primary key;

• • foreign key ：外键，与另外数据表的主键关联；第二SQL语句规定了amount和tests表中的id关联；关联之后列的数据必须存在于被关联的表中
    

    create table tests(
    id int primary key auto_increment,
    myname char(50) not null,
    score int
    );
    
    create table tonji(
    id int primary key auto_increment,
    amount int,
    foreign key(amount) references tests(id)
    );

    如果尝试插入被关联表中不存在的主键：

    Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`test`.`tonji`, CONSTRAINT `tonji_ibfk_1` FOREIGN KEY (`amount`) REFERENCES `tests` (`id`))

  • check：MySQL中check不能起到强制的作用，对有限的量可以采用枚举的方式（enum）；下句SQL规定score只能选枚举出来的这几个选项否则无法插入数
    

    create table stu(
    id int primary key auto_increment,
    stuname char(50),
    score enum('A','B','C','D')
    );

    下句SQL试图插入意外的字符

    insert into stu
    values(null, 'tbhrv', 'X');

    报错：Error Code: 1265. Data truncated for column 'score' at row 1

  • default：设置默认值；下面这句SQL规定了分数的默认值为‘D’

    create table stu(
    id int primary key auto_increment,
    stuname char(50),
    score enum('A','B','C','D') default 'D'
    );

  • 索引：索引用来增加搜索的速度，但是自身也需要消耗时间，所以用在经常查询的表中；下句SQL为score列增加（唯一）索引

    create index stuindex on stu(score);