标签归档：Mysql

CentOS7 升级 Mysql 到 5.7.32

发表评论

升级mysql服务端

yum update mysql-server

然后升级数据

mysql_upgrade -uroot -p

顺便把客户端版本也升级了

yum update mysql

最后用 SELECT @@version; 查看版本。

注意：我这里是小版本升级，没备份直接升了。如果跨主版本建议先做数据备份再升级。

批处理执行Mysql脚本

发表评论 
@echo off

%cd%\mysql\bin\mysql.exe -u root -h 127.0.0.1 -p123456 -e"use cqms;select 1;select 2;"

echo 数据库升级完成
pause

通过文件执行

@echo off

%cd%\mysql\bin\mysql.exe -u root -h 127.0.0.1 -p123456 < c:\test\aaa.txt

echo 数据库升级完成
pause

SQL SERVER按字母数字的组合排序

发表评论 
SELECT Price, Contact,Number,  GameType, ServerName, Account,Password, Rank, RentDate,  Legends, Detail, IsRent, Id
FROM dbo.AccountInfo
ORDER BY UPPER(LEFT(Number, patindex('%[^a-z]%',Number)-1)) ASC, Convert(int, RIGHT(Number,LEN(Number)- patindex('%[^a-z]%',Number)+1)) ASC, Number ASC

Mysql版本

SELECT  Number
FROM    accountinfo
ORDER   BY         
        CASE WHEN Number REGEXP '^[A-Z]{2}'
            THEN 1
            ELSE 0
        END ASC,
        CASE WHEN Number REGEXP '^[A-Z]{2}'
            THEN LEFT(Number, 2)
            ELSE LEFT(Number, 1)
        END ASC,
        CASE WHEN Number REGEXP '^[A-Z]{2}'
            THEN CAST(RIGHT(Number, LENGTH(Number) - 2) AS SIGNED)
            ELSE CAST(RIGHT(Number, LENGTH(Number) - 1) AS SIGNED)
        END ASC

postgresql版本

SELECT *, (regexp_matches(Number, '[A-Z]+' ))[1], (regexp_matches(Number, '[0-9]+' ))[1] from aaa
    ORDER BY (regexp_matches(Number, '[A-Z]+' ))[1] ASC ,
                  to_number((regexp_matches(Number, '[0-9]+' ))[1], '999999') ASC

DBConn mysql

发表评论 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace VideoSpider
{
    public class DBConn
    {
#if DEBUG
        private static   string _connectionString = @"server=61.164.149.180;User ID=sql_zuqiu;Password=FULFQcvuFsqnbQSL;database=zuqiu_bak;";
#else
        private static string _connectionString = @"server=localhost;User=11xs;Password=HPU3aMJVzECQfVXR;database=11xs";
#endif
        public static MySqlConnection OpenConnection( string connstr = "" )
        {

            if (connstr != "" )
            {
                _connectionString = connstr;
            }
            var conn = new MySqlConnection(_connectionString);
            conn.Open();
            return conn;
        }
    }
}