MySQL分组排名查询

--按某一字段分组取最大(小)值所在行的数据
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go
--一、按name分组取val最大的值所在行的数据。
--方法1：
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo              
---------- ----------- --------------------
a          3           a3:a的第三个值
b          5           b5b5b5b5b5
*/
--二、按name分组取val最小的值所在行的数据。
--方法1：
select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
--方法3：
select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--方法4：
select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
/*
name       val         memo              
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值
*/
--三、按name分组取第一次出现的行所在的数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
/*
name       val         memo              
---------- ----------- --------------------
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
*/
--四、按name分组随机取一条数据。
select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
/*
name       val         memo              
---------- ----------- --------------------
a          1           a1--a的第一个值
b          5           b5b5b5b5b5
*/
--五、按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
/*
name       val         memo              
---------- ----------- --------------------
a          1           a1--a的第一个值
a          2           a2(a的第二个值)
b          1           b1--b的第一个值
b          2           b2b2b2b2
*/
--六、按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
/*
name       val         memo              
---------- ----------- --------------------
a          2           a2(a的第二个值)
a          3           a3:a的第三个值
b          4           b4b4
b          5           b5b5b5b5b5
*/
--七，假如整行数据有重复，所有的列都相同。
/*
数据如下：
name val memo
a    2   a2(a的第二个值)
a    1   a1--a的第一个值
a    1   a1--a的第一个值
a    3   a3:a的第三个值
a    3   a3:a的第三个值
b    1   b1--b的第一个值
b    3   b3:b的第三个值
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go
select * , px = identity(int,1,1) into tmp from tb
select m.name,m.val,m.memo from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) m where px = (select min(px) from
(
select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
) n where n.name = m.name)
drop table tb,tmp
/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值
(2 行受影响)
*/
--在sql server 2005中可以使用row_number函数，不需要使用临时表。
--创建表并插入数据：
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(a的第二个值)')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    1,   'a1--a的第一个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('a',    3,   'a3:a的第三个值')
insert into tb values('b',    1,   'b1--b的第一个值')
insert into tb values('b',    3,   'b3:b的第三个值')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go
select m.name,m.val,m.memo from
(
select * , px = row_number() over(order by name , val) from tb
) m where px = (select min(px) from
(
select * , px = row_number() over(order by name , val) from tb
) n where n.name = m.name)
drop table tb
/*
name       val         memo
---------- ----------- --------------------
a          1           a1--a的第一个值
b          1           b1--b的第一个值
(2 行受影响)
*/

Mysql Install by compiling the source code

tar zxvf mysql-5.1.45.tar.gz
cd mysql-5.1.45
./configure --prefix=/var/lib/mysql --without-debug --with-charset=utf8 --with-extra-charsets=all  --with-plugins=all
make
make install
cp support-files/my-medium.cnf /etc/my.cnf
cd /var/lib/mysql/bin
./mysql_install_db --user=mysql
chown -R root .
cd ..
chown -R mysql /var/
chgrp -R mysql .
cd ../mysql/bin/
./mysqld_safe --user=mysql &  ----查看err.log
cd /var/lib/mysql/share/mysql/
cp mysql.server /etc/init.d/mysql
./mysql.server start
./mysqld_safe --defaults-file =/etc/my.cnf
/etc/init.d/mysql restart
cd /var/lib/mysql/bin/
cp mysql /usr/bin/




2.8.1. 源码安装概述
你必须执行的安装MySQL源码分发版的基本命令是：


shell> groupadd mysql
shell> useradd -g mysql mysql
shell> gunzip < mysql-VERSION.tar.gz | tar -xvf -
shell> cd mysql-VERSION
shell> ./configure --prefix=/usr/local/mysql
shell> make
shell> make install
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> cd /usr/local/mysql
shell> bin/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql var
shell> chgrp -R mysql .
shell> bin/mysqld_safe --user=mysql &
如果从一个源码RPM开始，那么执行如下命令：


shell> rpmbuild --rebuild --clean MySQL-VERSION.src.rpm
这样你制作一个可以安装的二进制RPM。

MySQL ini 配置文件生成

@echo off
echo "This is a demo script for auto installation of noninstall version of MySQL on Windows.  "
echo "Copyright: iihero@CSDN, when you distribute it, please copy this section above the head."
echo "================================iiihero@hotmail.com====================================="
set MYSQL_HOME=%~dp0
echo MYSQL_HOME=%MYSQL_HOME%
del /F my.ini
echo [client] >> my.ini
echo port = 3306 >> my.ini
echo default_character_set=gbk >> my.ini
echo [mysqld] >> my.ini
echo default_character_set=utf8 >> my.ini
echo default_storage_engine=InnoDB >> my.ini
echo basedir=%MYSQL_HOME%>>my.ini
echo datadir=%MYSQL_HOME%data>> my.ini
echo innodb_data_file=ibdata1:50M;ibdata2:10M:autoextend >> my.ini
echo transaction-isolation=READ-COMMITTED >> my.ini
echo port=3306 >> my.ini
echo max_allowed_packet = 64M >> my.ini
echo "my.ini in %MYSQL_HOME% created."
set PATH=%MYSQL_HOME%\bin;%PATH%
if exist "%MYSQL_HOME%\bin\mysqld-nt.exe" call mysqld-nt --install-manual mysql55 --defaults-file="%MYSQL_HOME%\my.ini"
if not exist "%MYSQL_HOME%\bin\mysqld-nt.exe" call mysqld --install-manual mysql55 --defaults-file="%MYSQL_HOME%\my.ini"

c++和c#访问mysql的简单代码示例

贴一份示例代码。

1) C#访问mysql

using System;
using System.Collections.Generic;
using System.Text;

using MySql.Data.MySqlClient;
using System.Data;
using System.Data.Common;

namespace SybaseUtilTest
{
    class Program
    {
        // http://bugs.mysql.com/47422
        static void testDataAdapter()
        {
            try
            {
                MySqlClientFactory factory = MySqlClientFactory.Instance;
                DbConnection conn = factory.CreateConnection();
                conn.ConnectionString = string.Format("server={0};user id={1}; password={2}; database={3}; port={4}; pooling=false",
                            "localhost", "root", "passwd", "test", 3306);
                conn.Open();

                DbDataAdapter da = factory.CreateDataAdapter();

                da.SelectCommand = conn.CreateCommand();
                da.SelectCommand.CommandText = "select * from t12345";


                da.DeleteCommand = conn.CreateCommand();
                da.DeleteCommand.CommandText = "delete from t12345 where id = @id";

                DbParameter param = factory.CreateParameter();
                param.ParameterName = "@id";
                param.DbType = DbType.Int32;
                param.SourceColumn = "id";
                param.SourceVersion = DataRowVersion.Current;

                da.DeleteCommand.Parameters.Add(param);
                da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

                DataTable dt = new DataTable("t12345");
                da.Fill(dt);

                int index = 0;
                foreach ( DataRow o in dt.Rows )
                {
                    if (o["id"].Equals(4))
                    {
                        Console.WriteLine(String.Format("index={0}, to delete id = 4, col2 = {1}" , index, o["col2"]));
                        break;
                    }
                    index++;
                }
                dt.Rows[index].Delete();
                da.Update(dt);
                dt.AcceptChanges();

                da.Dispose();
                conn.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Source + " "
                    + ex.Message + " "
                    + ex.StackTrace);
            }
            
        }
        
        static void Main(string[] args)
        {
            testDataAdapter();
        }
    }
}

  

2) C++访问：

#include <iostream>
#include <windows.h>
#include <mysql.h>
#include <string>
static const char host[32] = "localhost";
static const char user[32] = "test";
static const char passwd[32] = "passwd";
static const char db[32] = "test";
/**
mysql> select * from t;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
mysql> delimiter //
mysql> create procedure get_t(in t1 int)
    -> begin
    -> select id from t where id=t1;
    -> end
    -> //
Query OK, 0 rows affected (0.05 sec)
mysql> call get_t(1);
    -> //
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
*/
void test_more_results(MYSQL* h)
{
    char str[512] = "insert into test_num values(101);insert into test_num values(122);commit;";
    int r = mysql_real_query(h, str, strlen(str));
    if (r)
    {
        const char * error = mysql_error(h);
        std::cout<<"*** Connection Error " << error << std::endl;
    }
    do
    {
        MYSQL_RES* res = mysql_store_result(h);
        mysql_free_result(res);
    }
    while ( (0 == mysql_next_result(h)) );
    
}
void test_proc_stmt(MYSQL* h)
{
    MYSQL* mysql_ = h;
    MYSQL_BIND          bind;
    MYSQL_BIND          obind[1];
    // test_more_results(mysql_);
    MYSQL_STMT *hStmt = mysql_stmt_init(mysql_);
    my_bool true_value= 1;
    mysql_stmt_attr_set(hStmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &true_value);    
    char sql[] = "call get_t(?)";
    //char sql[] = "select id from t where id=?";
    if (mysql_stmt_prepare(hStmt, sql, strlen(sql)))
    {
        std::cout<<__LINE__<<": stmt prepare error:  "<< (mysql_stmt_error(hStmt))<<std::endl;
        mysql_stmt_reset(hStmt);
        if (mysql_stmt_prepare(hStmt, sql, strlen(sql)))
        {
            std::cout<<__LINE__<<": stmt prepare error:  "<< (mysql_stmt_error(hStmt))<<std::endl;
            mysql_close(mysql_);
            exit( -1);
        }
    }
    int id = 1;
    unsigned long id_len = 0;
    memset(&bind, 0, sizeof(bind));
    bind.buffer_type = FIELD_TYPE_LONG;
    bind.buffer = (void*)&id;
    bind.is_unsigned = true;
    bind.length = &id_len;
    // bind[0].buffer_length = sizeof(id);
    // bind[0].is_null = 0;
    
    if (mysql_stmt_bind_param(hStmt,(MYSQL_BIND*)(&bind)) != 0)
    {
        std::cout<<__LINE__<<": stmt prepare error:  "<< (mysql_stmt_error(hStmt))<<std::endl;
        mysql_close(mysql_);
        exit( -1);
    }
    if (mysql_stmt_execute(hStmt) != 0)
    {
        std::cout<<__LINE__<<": stmt prepare error:  "<< (mysql_stmt_error(hStmt))<<std::endl;
        mysql_close(mysql_);
        exit( -1);
    }
    int t2;
    memset(obind, 0, sizeof(obind));
    obind[0].buffer_type= MYSQL_TYPE_LONG;
    obind[0].buffer= (char *)&t2;
    obind[0].buffer_length = sizeof(t2);
    
    if (mysql_stmt_bind_result(hStmt, (MYSQL_BIND*)&obind[0]) != 0)
    {
        std::cout<<__LINE__<<": stmt prepare error:  "<< (mysql_stmt_error(hStmt))<<std::endl;
        mysql_close(mysql_);
        exit( -1);
    }
    if ( mysql_stmt_store_result(hStmt) != 0 )
    {
        std::cout<<__LINE__<<": stmt prepare error:  "<< (mysql_stmt_error(hStmt))<<std::endl;
        mysql_close(mysql_);
        exit( -1);
    }
    
    
    int rows = mysql_stmt_num_rows(hStmt);
    for (int i=0; i<rows; i++)
    {
        if (mysql_stmt_fetch(hStmt) == 0)
        {
            std::cout<<"id = "<<t2<<std::endl;
        }
    }
    mysql_stmt_free_result(hStmt);
    mysql_stmt_close(hStmt);
}
//
// Just for demo only.
// 
int main()
{
    MYSQL*              mysql_ = NULL;
    MYSQL_RES*          result_ = NULL;
    MYSQL_ROW           row_;
    mysql_ = mysql_init(mysql_);
    // if (mysql_real_connect(mysql_, host, user, passwd, db, 3306, NULL, CLIENT_MULTI_STATEMENTS) == NULL)
    if (mysql_real_connect(mysql_, host, user, passwd, db, 3306, NULL, CLIENT_MULTI_STATEMENTS) == NULL)
    {
        const char * error = mysql_error(mysql_);
        std::cout<<"*** Connection Error " << error << std::endl;
        return -1;
    }
    mysql_autocommit(mysql_, false);
    std::string encodeStr = "set names 'gbk'";
    mysql_real_query(mysql_, encodeStr.c_str(), encodeStr.size());
    
    /*
    const char* tmpTableName = "t";  // assume you are querying the table 't'
    char str[512];
    int cnt = 0;
    sprintf(str,"select count(*) as cnt from %s", tmpTableName);
    mysql_real_query(mysql_, str, strlen(str));
    result_ = mysql_store_result(mysql_);
    while (row_ = mysql_fetch_row(result_))
    {
        // get the field value
        if (row_[0])
        {
            std::cout<<"count = "<<row_[0]<<std::endl;
            // convert it into int
            cnt = atoi(row_[0]);
            std::cout<<"cnt value = "<<row_[0]<<std::endl;
        }
    }
    mysql_free_result(result_);
    test_more_results();
    */
    test_proc_stmt(mysql_);
    
    do
    {
        MYSQL_RES* res = mysql_store_result(mysql_);
        mysql_free_result(res);
    }
    while ( (0 == mysql_next_result(mysql_)) );
    
    test_proc_stmt(mysql_);
    mysql_close(mysql_);
    return 0;
}

embedded_mysql:成功实验程序实现Embedded MySQL Server启动(C/C++)

今天，碰巧有人问，说写的代码server_init，老是失败，我不信，试了一番，大费周折，总算成功。

有兴趣的，不妨自己动手一试，蛮有意思的。

我机器上原本有一个解压缩版的MySQL5.0.9，位于D:/program/mysql-5.0.9-beta-win32，默认存储引擎是InnoDB

1. 为便于测试，首先建立一个测试表，并插入几条记录，引擎定为MyISAM

create table t2(id int primary key, col2 varchar(32)) engine=MyISAM;

2. 创建Embed　Server的配置文件，

D:/program/mysql-5.0.9-beta-win32/Embedded/my.ini，值得一提的是，差点把我害惨了，就是Server那一项的名字必须与你的Server程序的名字保持一致。这里，EmbedMySQLServer就是我后边的exe程序的名，否则server永远也启不来。

内容如下：

[EmbedMySQLServer]
basedir = D:/program/mysql-5.0.9-beta-win32
datadir = D:/program/mysql-5.0.9-beta-win32/data
language = D:/program/mysql-5.0.9-beta-win32/share/english
skip-innodb
port=3306

[libmysqld_client]
language = D:/program/mysql-5.0.9-beta-win32/share/english
port=3306

3. 开始写自己的代码了

create EmbedMySQLServer.exe, source code like:

1. // #define _WIN32_WINNT 0x0400
2. #include <windows.h>
3. #include <stdio.h>
4. #include <stdlib.h>
5. #include <stdarg.h>
6. #include "mysql.h"
8. MYSQL *mysql;
9. MYSQL_RES *results;
10. MYSQL_ROW record;
12. #pragma comment(lib, "D://program//mysql-5.0.9-beta-win32//Embedded//DLL//debug//libmysqld.lib")
14. static char *server_options[] = { "mysql_test", "--defaults-file=D:/program/mysql-5.0.9-beta-win32/Embedded/my.ini" };
15. int num_elements = sizeof(server_options)/ sizeof(char *);
17. static char *server_groups[] = { "EmbedMySQLServer", "libmysqld_client" };
19. int main(void)
20. {
21.    int ret = mysql_server_init(num_elements, server_options, server_groups);
22.    printf("return %ld/n", ret);
23.    mysql = mysql_init(NULL);
24.    mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "libmysqld_client");
25.    mysql_options(mysql, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);
27.    MYSQL* t = mysql_real_connect(mysql, NULL,"test","test", "test", 0,NULL,0);
28.    
30.    mysql_query(mysql, "SELECT id, col2 FROM t2");
32.    results = mysql_store_result(mysql);
34.    while((record = mysql_fetch_row(results))) {
35.       printf("%s - %s /n", record[0], record[1]);
36.    }
38.    mysql_free_result(results);
39.    mysql_close(mysql);
40.    mysql_server_end();
42.    return 0;
43. }

4. 最终运行结果：

return 0
1 - test
2 - test
3 - test
4 - test
5 - fdas
Press any key to continue

像这类东东，MySQL Online Doc都没好好说，它都推荐买它的商业license，看来自己多动动手，也蛮有意思的。