SQL多表连接查询以及mysql数据库、sqlserver数据库常见不同点

2022-09-30 22:00:53

mysql数据库表及数据准备语句：

USE test;

DROP TABLE
IF EXISTS `teacher_table`;

DROP TABLE
IF EXISTS `student_table`;

CREATE TABLE `teacher_table` (
    `id` ) NOT NULL AUTO_INCREMENT,
    `tName` ) NOT NULL DEFAULT '老师名字',
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

CREATE TABLE `student_table` (
    `id` ) NOT NULL AUTO_INCREMENT,
    `sName` ) NOT NULL DEFAULT '学生名字',
    `teacherId` ) ',
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `teacher_table`
VALUES
    (, '鲁迅'),
    (, '三毛'),
    (, '莫言'),
    (, '易中天'),
    (, '罗永浩');

INSERT INTO `student_table`
VALUES
    (, ),
    (, ),
    (, ),
    (, );

以上在mysql数据库中创建了两个表teacher_table和student_table，并准备好了数据。老师表有五条记录，前四条记录分别对应学生表的一条记录，最后一条记录在学生表中没有对应记录。

左连接查询语句如下：

SELECT
    *
FROM
    teacher_table t
LEFT JOIN student_table s ON t.id = s.teacherId;

在数据库连接软件中可以看到查询结果如下：

id	tName	id1	sName	teacherId
1	鲁迅	3	小赵	1
2	三毛	1	小丽	2
3	莫言	2	小王	3
4	易中天	4	小张	4
5	罗永浩	Null	Null	Null

由查询结果可以分析出：

左连接查询的结果是：两个表满足连接条件的记录以及左表的剩余记录，其中左表的剩余记录中属于左表的字段的值为实际的值，属于右表的字段的值为null，注意不是对应字段的默认值。这个在用自己封装的ResultSetUitls工具类处理成List<Map<String,Object>>对象时也有验证。list对象其中一些map元素的值是null，而不是数据库对应字段的默认值；

此外，在项目中更推荐使用内连接inner join查询，内连接查询只会查询出连接表符合连接条件的记录。

以上是mysql数据库建表及插入数据的语句，sqlserver数据库有些不同：

IF (
    SELECT
        )
    FROM
        sysobjects
    WHERE
        name = 'teacher_table'
)  DROP TABLE teacher_table ;
IF (
    SELECT
        )
    FROM
        sysobjects
    WHERE
        name = 'student_table'
)  DROP TABLE student_table ; CREATE TABLE teacher_table (
    id INT NOT NULL IDENTITY,
    tName ) NOT NULL DEFAULT '老师名字',
    PRIMARY KEY (id)
);

CREATE TABLE student_table (
    id INT NOT NULL IDENTITY,
    sName ) NOT NULL DEFAULT '学生名字',
    teacherId ,
    PRIMARY KEY (id)
);

INSERT INTO teacher_table
VALUES
    ('鲁迅'),
    ('三毛'),
    ('莫言'),
    ('易中天'),
    ('罗永浩');

INSERT INTO student_table
VALUES
    (),
    (),
    (),
    ();

不同点：

1.判断表是否存在，如果存在则删除的方法不同；

2.创建表时自增长的标识字段不同，mysql中用AUTO_INCREMENT，sqlserver中用IDENTITY；

3.mysql数据库在创建表时可以给 int 类型指定列宽，如int(11)，也可以不指定。而sqlserver数据库在创建表时则不能对 int 类型指定列宽；

4.自增长字段在插入数据时的sql语句不一样。mysql在插入数据时，可以插入自增长字段的值，只要不是重复的值就不会报错，而且不必要比现在最大值大；如果插入null，则对应记录的对应自增字段的值就是当前记录此自增字段的最大值加上步长(一般为1)。而sqlserver数据库在插入语句中默认是不能加自增字段的，如果非要在插入语句中指定自增字段的值，则需要将IDENTITY_INSERT设值为0(仅当使用了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表中的标识列指定显式值)。

5.分页查询不一样。

mysql分页用关键字limit，如每页3条，查询第2页的语句是：

,;

limit后面跟两个数，用逗号隔开，不用括号。第一个数是要查询的记录的起始索引，是算出来的，(page-1)*pageSize。第二个数就是每页的条数，是固定值。

sqlserver分页用top关键字，一般配合排名函数row_number() over()使用：

row_number() over([partition by xx] order by xx) as rowNumber ，按照特定顺序给每行记录添加一个行号。分组可选，排序必需，不管是正序还是逆序，行号永远从1开始。如下

SELECT
    row_number () OVER (ORDER BY id DESC) AS rowNumber,
    *
FROM
    teacher_table;

查询结果是：

rowNumber	id	tName
1	5	罗永浩
2	4	易中天
3	3	莫言
4	2	三毛
5	1	鲁迅

可以看到这一sql语句把全部记录数按照我们的要求排序后给又给每一行都标上了行号，行号为1的就代表是第一条记录。

我们只需在当前记录数中用where截断记录然后去头多少行就好了。例如，还是每页3条，查询第2页，则就是where rowNumber>3，然后select top 3 *，其实就是在要查询的字段前加上top N。分页sql语句如下：

SELECT
     *
FROM
    (
        SELECT
            row_number () OVER (ORDER BY id DESC) AS rowNumber,
            *
        FROM
            teacher_table
    ) R
WHERE
    rowNumber ;

查询结果如下：

rowNumber	id	tName
4	2	三毛
5	1	鲁迅

总的来看，其实是用了子查询，先给所有的记录排序并标上行号，然后取从某个行号开始取固定长度的记录数，这就是sqlserver分页的原理。

码农公寓

相关文章