SQL进阶

简单表的拆分思路

如图

描述

  有一张表 cars,有字段id,name,catename三个。
  现要将其拆分成新cars表和cates俩表，
  其中新cars表包含id,name和cates的主键cateid。

操作

  1.创建cates表
  
  DROP TABLE IF EXISTS `cates`;
  CREATE TABLE `cates` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

  2.将cars中的cateName插入到cates中

  insert into cates(name)
  select cateName from cars;

  3.创建表cars_tmp存放cars表的id,name和cates的id

  DROP TABLE IF EXISTS `cars_tmp`;
  CREATE TABLE `cars_tmp` (
    `id` int(11) DEFAULT NULL,
    `name` varchar(100) DEFAULT NULL,
    `cateid` int(11) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  4.将对应数据插入到cars_tmp中
  
  insert into cars_tmp
  select c.id,c.name,cs.id
  from cars c join cates cs on c.cateName = cs.name;
  /inner join和join相同

  5.删除老cars表，重命名cars_tmp表为cars

  drop table cars;
  alter table cars_tmp rename to cars;

case转换

查询结果中字段性别 1和0 转换为男/女

  SELECT id, case sex WHEN '1' THEN '男' WHEN '0' THEN '女' END AS sex FROM person;

  表结构：
  -- ----------------------------
  DROP TABLE IF EXISTS `person`;
  CREATE TABLE `person` (
    `id` int(11) NOT NULL,
    `sex` varchar(1) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

临时表

创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字即可。

创建临时表

  CREATE TEMPORARY TABLE tmp_table (
   name VARCHAR(10) NOT NULL
  )

向临时表中插数据

  insert into tmp_table(name)
  select name from cars_old;

删除临时表
临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。
```
  DROP TABLE tmp_table
```

查看表结构

  desc table_name
  describe table_name

order by

order by 1/2/3的含义
```
  SELECT * FROM table_name ORDER BY 1;
  SELECT id,name,sex FROM table_name ORDER BY 3;
```
ORDER BY 1代表以所查询字段的第1个字段排序；
ORDER BY 3代表以所查询字段的第3个字段排序，如果查询总字段的个数小于3个字段，则会报错。

数据库视图

什么是视图
视图，并不是真实存在的数据表（但是其对应的数据表是真实存在的），而是数据表字段的组合或者筛选，可以与存储过程对比理解，也可以把视图暂且理解为一个或多个数据表特定字段的组合。

如何创建视图

比如，现在存在数据表student

  DROP TABLE IF EXISTS `student`;
  CREATE TABLE `student` (
    `id` int(11) NOT NULL,
    `sid` varchar(255) DEFAULT NULL,
    `sname` varchar(255) DEFAULT NULL,
    `ssex` varchar(255) DEFAULT NULL,
    `sage` varchar(255) DEFAULT NULL,
    `saddress` varchar(255) DEFAULT NULL,
    `sclass` varchar(255) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- ----------------------------
  -- Records of student
  -- ----------------------------
  INSERT INTO `student` VALUES ('1', '2017001', '张三', '男', '21', '上海', '001');
  INSERT INTO `student` VALUES ('2', '2017002', '李四', '女', '22', '北京', '002');

该表包含id,学号，姓名，性别，年龄，地址这几个字段，如果此刻只想暴露学号，姓名，性别这三个字段信息供外界查询，那么可以建立视图，用以隐藏其余不想暴露的字段。

  create view v_student AS
  SELECT id,sid,sname,ssex FROM student;

上面的SQL创建了名为v_student的视图，该视图针对student数据表而存在，展示学号，姓名，性别三个字段。

视图的调用

视图的操作与普通数据表的操作一样

查询：

  select*from v_student;

插入：

  insert into v_student values('3','2017003','王五','男');

修改：

  UPDATE v_student set ssex='女' WHERE sid = '2017003';

删除：

   DELETE FROM v_student where sid='2017003';

视图的优劣
优点：
使用视图，可以定制用户数据，聚焦特定的数据。
使用视图，可以简化数据操作
数据隐藏
以合并分离的数据，创建分区视图
缺点：
性能差
修改限制
参考文章：
http://blog.csdn.net/javajxz008/article/details/50720936

外连接与内链接

LEFT JOIN
左表为主表，查询结果含有左表所有满足条件的。
RIGHT JOIN
右表为主表，查询结果含有右表所有满足条件的。
INNER JOIN
只有两表同时满足条件的记录，才会被查询出来。

以info1和info2表中主键(id/sid)相同为条件，查询info1中的id和info2的sname为例：

表1：

  mysql> select*from info1;
  +----+-------+
  | id | name  |
  +----+-------+
  |  4 | 张三4 |
  | 11 | 张三1 |
  | 22 | 张三2 |
  | 33 | 张三3 |
  +----+-------+

表2：

  mysql> select*from info2;
  +-----+-------+
  | sid | sname |
  +-----+-------+
  |   1 | 李四1 |
  |  22 | 李四2 |
  |  33 | 李四3 |
  |  44 | 李四4 |
  +-----+-------+
  4 rows in set

左连接，以左表为主，其中id为11，4，和55的在info2表中没有对应的id,所以sname为NULL

  mysql> select id,sname from info1 as a left join info2 as b on a.id = b.sid order by name;
  +----+-------+
  | id | sname |
  +----+-------+
  | 11 | NULL  |
  | 22 | 李四2 |
  | 33 | 李四3 |
  |  4 | NULL  |
  | 55 | NULL  |
  +----+-------+
  5 rows in set

右连接，以右表为主，因此左表中的第五列没有被查询出来

  mysql> select id,sname from info1 as a right join info2 as b on a.id = b.sid order by name;
  +------+-------+
  | id   | sname |
  +------+-------+
  | NULL | 李四1 |
  | NULL | 李四4 |
  |   22 | 李四2 |
  |   33 | 李四3 |
  +------+-------+
  4 rows in set

内连接，只查询a.id和b.sid一致的记录

  mysql> select id,sname from info1 as a inner join info2 as b on a.id = b.sid order by name;
  +----+-------+
  | id | sname |
  +----+-------+
  | 22 | 李四2 |
  | 33 | 李四3 |
  +----+-------+
  2 rows in set

给table起别名(DB2)

LC.B_SH_DlvSeqInfo起别名为TC.B_SH_DLVSEQINFO

  CREATE ALIAS TC.B_SH_DLVSEQINFO FOR LC.B_SH_DlvSeqInfo;

索引

索引的创建与查看

UNION与UNION ALL

示例表结构

  DROP TABLE IF EXISTS `cars`;
  CREATE TABLE `cars` (
    `id` int(11) DEFAULT NULL,
    `name` varchar(100) DEFAULT NULL,
    `cateid` int(11) DEFAULT NULL,
    `new` int(255) DEFAULT NULL,
    KEY `myid` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  -- ----------------------------
  -- Records of cars
  -- ----------------------------
  INSERT INTO `cars` VALUES ('1001', 'car1', '1', null);
  INSERT INTO `cars` VALUES ('2', 'car2', '2', null);
  INSERT INTO `cars` VALUES ('3', 'car3', '3', null);
  INSERT INTO `cars` VALUES ('4', 'car4', '4', null);
  INSERT INTO `cars` VALUES ('5', 'car4', '5', null);

union查询与union all查询对比

UNION:

  mysql> select "信息名" from cars
      -> union
      -> select name from cars;
  
  +--------+
  | 信息名 |
  +--------+
  | 信息名 |
  | car1   |
  | car2   |
  | car3   |
  | car4   |
  +--------+

UNION ALL:

  mysql> select "信息名" from cars
      -> union all
      -> select name from cars;

  +--------+
  | 信息名 |
  +--------+
  | 信息名 |
  | 信息名 |
  | 信息名 |
  | 信息名 |
  | 信息名 |
  | car1   |
  | car2   |
  | car3   |
  | car4   |
  | car4   |
  +--------+

对比结果：

UNION会将查询的结果去重复，UNION ALL不会进行重复过滤。

但是，如果在UNION ALL中条件唯一（查询结果只有一条），一样可以做到和UNION一样的结果。

如下：

  mysql> select sum(id),"信息" from cars
      -> union
      -> select id,name from cars;
  +---------+------+
  | sum(id) | 信息 |
  +---------+------+
  | 1015    | 信息 |
  | 1001    | car1 |
  | 2       | car2 |
  | 3       | car3 |
  | 4       | car4 |
  | 5       | car4 |
  +---------+------+
  
  mysql> select sum(id),"信息" from cars
      -> union all
      -> select id,name from cars;
  +---------+------+
  | sum(id) | 信息 |
  +---------+------+
  | 1015    | 信息 |
  | 1001    | car1 |
  | 2       | car2 |
  | 3       | car3 |
  | 4       | car4 |
  | 5       | car4 |
  +---------+------+

**因为sum（id）的结果只有一条，所以出现UNION ALL和UNION一样的结果。 **

IFNULL/ISNULL/NVL

IFNULL/ISNULL/NVL 可以做数据表两个字段的查询，如字段1，字段2，若字段1为null，则用字段2代替

MySQL语法如下：

  SELECT IFNULL(new,name) from cars;
  
  +------------------+
  | IFNULL(new,name) |
  +------------------+
  | 33               |
  | car2             |
  | car3             |
  | car4             |
  | car4             |
  +------------------+

Oracle语法：
```
  SELECT NVL(new,name) from cars;
  
```

DB2

  SELECT COLUMN FROM TABLE FETCH FIRST N ROWS ONLY

CASE的另一种用法

  需求：如果我想更新id=1的status为1，id不为1的status为0  ，且id有外键
  
  update AccountStatus a set a.statusSource=(case when a.statusSource =1 then 2 else  1 end )
  
  --这样可以替换掉id为1的数据为0，id为0的数据为1

普通用法

  将成绩划分等级：
  select grade,case 
              when grade>=90 then '优秀'
              when grade>=80 then '良好'
              when grade>=70 then '中等'
              when grade>=60 then '及格'
              when grade is null then '缺考'
              else '不及格'
              end
  from sc

Mysql的Limit

limit

  select * from tbname LIMIT 10;--检索前10行数据，显示1-10条数据
  select * from tbname LIMIT 1,10;--检索从第2行开始，累加10条id记录，共显示id为2....11
  select * from tbname limit 5,10;--检索从第6行开始向前加10条数据，共显示id为6,7....15
  select * from tbname limit 6,10;--检索从第7行开始向前加10条记录，显示id为7,8...16