Example.QueryBuilder.Select

Examples

Basic

    using Izayoi.Data.Query;

    static void Main()
    {
        var queryOption = new QueryOption();

        var queryBuilder = new QueryBuilder(queryOption);

        var select = new Select()
            .SetFrom("users")
            .AddField("*");

        queryBuilder.Build(select);

        string query = queryBuilder.GetQuery();

        var parameters = queryBuilder.GetParameters();

        // query:
        //   SELECT *
        //   FROM users
        // parameters:
        //   (Empty)
    }

Select

    // SELECT
    {
        var select1 = new Select()
            .SetFrom("users")
            .AddField("id")
            .AddField("name")
            .AddField("age");

        // query:
        //   SELECT id, name, age
        //   FROM users
    }

    // SELECT DISTINCT
    {
        var select2 = new Select()
            .SetType(SType.DISTINCT)
            .SetFrom("users")
            .AddField("*");

        // query:
        //   SELECT DISTINCT *
        //   FROM users
    }

    // WHERE: AND
    {
        var select3 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("age", ">=", 13)
            .AddWhere("age", "<=", 19);        

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE age >= @w_0
        //     AND age <= @w_1
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.Int32
        //     Value: 13
        //   [1]:
        //     ParameterName: @w_1
        //     DbType: DbType.Int32
        //     Value: 19
    }

    // WHERE: BETWEEN
    {
        var select4 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("age", OpType.BETWEEN, new int[] { 13, 19 });

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE BETWEEN age @w_0_0 AND @w_0_1
        // parameters:
        //   [0]:
        //     ParameterName: @w_0_0
        //     DbType: DbType.Int32
        //     Value: 13
        //   [1]:
        //     ParameterName: @w_0_1
        //     DbType: DbType.Int32
        //     Value: 19
    }

    // WHERE: IN
    {
        var select5 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("age", OpType.IN, new int[] { 20, 30, 40 });

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE age IN (@w_0_0, @w_0_1, @w_0_2)
        // parameters:
        //   [0]:
        //     ParameterName: @w_0_0
        //     DbType: DbType.Int32
        //     Value: 20
        //   [1]:
        //     ParameterName: @w_0_1
        //     DbType: DbType.Int32
        //     Value: 30
        //   [2]:
        //     ParameterName: @w_0_2
        //     DbType: DbType.Int32
        //     Value: 40
    }

    // WHERE: IN
    {
        var select6 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("age", OpType.IN, new List<int> { 20, 30, 40 });

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE age IN (@w_0_0, @w_0_1, @w_0_2)
        // parameters:
        //   [0]:
        //     ParameterName: @w_0_0
        //     DbType: DbType.Int32
        //     Value: 20
        //   [1]:
        //     ParameterName: @w_0_1
        //     DbType: DbType.Int32
        //     Value: 30
        //   [2]:
        //     ParameterName: @w_0_2
        //     DbType: DbType.Int32
        //     Value: 40
    }

    // WHERE: OR
    {
        var select7 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere('(', "age", "=", 20)
            .AddWhere(CType.OR, "age", "=", 30)
            .AddWhere(CType.OR, "age", "=", 40)
            .AddWhere(CType.OR, "age", "=", 50, ')'); 

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE (age = @w_0
        //       OR age = @w_1
        //       OR age = @w_2
        //       OR age = @w_3)
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.Int32
        //     Value: 20
        //   [1]:
        //     ParameterName: @w_1
        //     DbType: DbType.Int32
        //     Value: 30
        //   [2]:
        //     ParameterName: @w_2
        //     DbType: DbType.Int32
        //     Value: 40
        //   [3]:
        //     ParameterName: @w_3
        //     DbType: DbType.Int32
        //     Value: 50
    }

    // WHERE: IS NULL / IS NOT NULL
    {
        var select8 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("name", OpType.IS_NULL);

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE name IS NULL
        // parameters:
        //   (Empty)
    }

    // WHERE: IS NULL
    {
        var select9 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("name", "is", null);

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE name IS NULL
        // parameters:
        //   (Empty)
    }

    // WHERE: IS NOT NULL
    {
        var select10 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("name", "is not", null);

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE name IS NOT NULL
        // parameters:
        //   (Empty)
    }

    // WHERE: LIKE / NOT LIKE
    {
        var select11 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("name", OpType.LIKE, "J%");

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE name LIKE @w_0
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.String
        //     Value: "J%"
    }

    // WHERE
    {
        var select12 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddWhere("age", ">=", 20)
            .AddWhere(Type.AND, "name", OpType.LIKE, "J%")
            .AddWhere(Type.AND, "enabled", "=", true);

        // query:
        //   SELECT *
        //   FROM users
        //   WHERE age >= @w_0
        //     AND name LIKE @w_1
        //     AND enabled = @w_2
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.Int32
        //     Value: 20
        //   [1]:
        //     ParameterName: @w_1
        //     DbType: DbType.String
        //     Value: "J%"
        //   [2]:
        //     ParameterName: @w_2
        //     DbType: DbType.Boolean
        //     Value: true
    }

    // JOIN
    {
        var select13 = new Select()
            .SetFrom("posts")
            .AddJoin(JType.LEFT_JOIN, "users", "users.id = posts.user_id")
            .AddField("posts.id")
            .AddField("posts.comment")
            .AddField("posts.user_id")
            .AddField("users.name", "user_name")
            .AddWhere("users.age", "<", 18);

        // query:
        //   SELECT posts.id, posts.comment, posts.user_id, users.name AS user_name
        //   FROM posts
        //   LEFT JOIN users ON (users.id = posts.user_id)
        //   WHERE users.age < @w_0
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.Int32
        //     Value: 18
    }

    // JOIN: Table Alias
    {
        var select14 = new Select()
            .SetFrom("posts", "p")
            .AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
            .AddField("p.id")
            .AddField("p.comment")
            .AddField("p.user_id")
            .AddField("u.name", "user_name")
            .AddWhere("u.age", "<", 18);

        // query:
        //   SELECT p.id, p.comment, p.user_id, u.name AS user_name
        //   FROM posts AS p
        //   LEFT JOIN users AS u ON (u.id = p.user_id)
        //   WHERE u.age < @w_0
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.Int32
        //     Value: 18
    }

    // JOIN: Schema
    {
        var select15 = new Select()
            .SetFrom("dbo", "posts", "p")
            .AddJoin(JType.LEFT_JOIN, "dbo", "users", "u", "u.id = p.user_id")
            .AddField("p.id")
            .AddField("p.comment")
            .AddField("p.user_id")
            .AddField("u.name", "user_name")
            .AddWhere("u.age", "<", 18);

        // query:
        //   SELECT p.id, p.comment, p.user_id, u.name AS user_name
        //   FROM dbo.posts AS p
        //   LEFT JOIN dbo.users AS u ON (u.id = p.user_id)
        //   WHERE u.age < @w_0
        // parameters:
        //   [0]:
        //     ParameterName: @w_0
        //     DbType: DbType.Int32
        //     Value: 18
    }

    // GROUP BY
    {
        var select16 = new Select()
            .SetFrom("posts")
            .AddJoin(JType.LEFT_JOIN, "users", "users.id = posts.user_id")
            .AddField("user_id")
            .AddField("users.name", "user_name")
            .AddField("COUNT(comment)", "post_count")
            .AddGroup("user_id")
            .AddGroup("user_name");

        // query:
        //   SELECT user_id, users.name AS user_name, COUNT(comment) AS post_count
        //   FROM posts
        //   LEFT JOIN users ON (users.id = posts.user_id)
        //   GROUP BY user_id, user_name
    }

    // HAVING
    {
        var select17 = new Select()
            .SetFrom("posts")
            .AddField("user_id")
            .AddField("COUNT(comment)", "post_count")
            .AddGroup("user_id")
            .AddHaving("post_count", ">=", 2)
            .AddHaving("post_count", "<=", 4);

        // query:
        //   SELECT user_id, COUNT(comment) AS post_count
        //   FROM posts
        //   LEFT JOIN users ON (users.id = posts.user_id)
        //   GROUP BY user_id
        //   HAVING post_count >= @h_0 AND post_count <= @h_1
        // parameters:
        //   [0]:
        //     ParameterName: @h_0
        //     DbType: DbType.Int32
        //     Value: 2
        //   [1]:
        //     ParameterName: @h_1
        //     DbType: DbType.Int32
        //     Value: 4
    }

    // LIMIT and OFFET
    {
        var select18 = new Select()
            .SetFrom("users")
            .AddField("*")
            .AddOrder("id", OType.ASC)
            .SetLimit(5)
            .SetOffset(10);

        // query:
        //   SELECT *
        //   FROM users
        //   ORDER BY id ASC
        //   LIMIT 5
        //   OFFSET 10

        // queryOption
        //   RdbKind.SqlServer
        // query:
        //   SELECT *
        //   FROM users
        //   ORDER BY id ASC
        //   OFFSET 10 ROWS
        //   FETCH NEXT 5 ROWS ONLY
    }