sqlexiststable
DROP
TEMPORARY
TABLE
IF
EXISTS
TABLE_1;
CREATE
TEMPORARY
TABLE
TABLE_1
(
SELECT
*
FROM
(
SELECT
'100YUAN'
vMoney,'5REN'
vNum,'2015-09-15'
vDate
UNION
ALL
SELECT
'200YUAN','6REN','2015-09-21'
)a
);
DROP
TEMPORARY
TABLE
IF
EXISTS
TABLE_2;
CREATE
TEMPORARY
TABLE
TABLE_2
(
SELECT
*
FROM
(
SELECT
'2015-09-15'
vDate
UNION
ALL
SELECT
'2015-09-16'
UNION
ALL
SELECT
'2015-09-17'
UNION
ALL
SELECT
'2015-09-18'
UNION
ALL
SELECT
'2015-09-19'
UNION
ALL
SELECT
'2015-09-20'
UNION
ALL
SELECT
'2015-09-21'
UNION
ALL
SELECT
'2015-09-22'
UNION
ALL
SELECT
'2015-09-23'
UNION
ALL
SELECT
'2015-09-24'
)
a
);
SELECT
a.vDate,IFNULL(b.vMoney,0)
vMoney,IFNULL(b.vNum,0)
vNum
FROM
TABLE_2
a
LEFT
JOIN
TABLE_1
b
ON
a.vDate
=
b.vDate
ORDER
BY
a.vDate
Ⅱ c#利用mysql connector net怎麼連接mysql資料庫
(1)首先需要下載C#訪問MySQL資料庫的ADO.NET驅動程序
mysql-connector-net-6.3.8.msi
(2)安裝mysql-connector-net
然後直接在Windows操作系統安裝 mysql-connector-net-6.3.8.msi
(3)封裝資料庫訪問組件DbConnectionMySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
/// <summary>
/// MySQL資料庫
/// 版本 mysql-connector-net-6.3.8.msi
/// </summary>
[Serializable]
public class DbConnectionMySQL : DbConnectionWrapper
{
public DbConnectionMySQL(string pConnectionString)
: base(pConnectionString)
{
this.m_dbconn = new MySqlConnection(pConnectionString);
this.m_DbConnState = DbConnState.Free;
}
//--
public override DbDataAdapter GetDbDataAdapter()
{
return new MySqlDataAdapter();
}
public override DbDataAdapter GetDbDataAdapter(DbCommand dbCommand)
{
return new MySqlDataAdapter(dbCommand as MySqlCommand);
}
public override DbCommand GetDbCommand()
{
return new MySqlCommand();
}
public override DbConnection GetDbConnection()
{
return new MySqlConnection();
}
public override DbCommandBuilder GetDbCommandBuilder()
{
return new MySqlCommandBuilder();
}
public override DataProviderType GetCurrentDataProviderType()
{
return DataProviderType.Sql;
}
public override bool IsExistsTable(string TableName, string UserName)
{
#region information
bool rbc = false; //TABLES表中去查詢 table_name
string dSql = "select * from TABLES where table_name='" + TableName + "'";
DataSet ds = this.ExecuteDataSet(dSql);
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
rbc = true;
}
else
{
rbc = false;
}
}
else
{
rbc = false;
}
return rbc;
#endregion
}
public override bool IsExistsField(string FieldName, string TableName)
{
#region information
bool rbc = false;
string dSql = "";
dSql = "select * from " + TableName + " where 1<>1";
DataSet ds = this.ExecuteDataSet(dSql);
if (ds != null)
{
DataTable dt = ds.Tables[0];
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dt.Columns[j].ColumnName.ToString().ToUpper() == FieldName.ToString().ToUpper())
{
rbc = true;
goto Return_End;
}
}
dt.Dispose();
dt = null;
}
ds.Dispose();
ds = null;
Return_End:
return rbc;
#endregion
}
public override char ParameterChar
{
get
{
return ':'; //SQLite的參數符號為:
}
}
public override DbParameter CreateParameter(string name, object value)
{
return new MySqlParameter(name, value);
}
public override DbParameter CreateParameter(string name)
{
DbParameter dbp = new MySqlParameter();
dbp.ParameterName = name;
return dbp;
}
public override DbParameter CreateParameter(string name, DbType dbtype, object value)
{
DbParameter dbp = new MySqlParameter();
dbp.ParameterName = name;
dbp.Value = value;
dbp.DbType = dbtype;
return dbp;
}
public override DbParameter CreateParameter(string name, DbType dbtype, int size, object value)
{
DbParameter dbp = new MySqlParameter();
dbp.ParameterName = name;
dbp.Value = value;
dbp.DbType = dbtype;
dbp.Size = size;
return dbp;
}
}
（4)客戶端開發實例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
public void TestCShape_MySQL()
{
string constr = "server=localhost;User Id=root;password=root;Database=xp_users";
DbConnectionWrapper dbw = new DbConnectionMySQL(constr);
bool rbc=dbw.TestConnection();
this.Context.Response.Write(rbc);
string x = "";
//刪除語句
x = "delete from xp_users";
if (dbw.ExecuteQuery(x) > 0)
{
this.Context.Response.Write("刪除語句成功!下面是<a href="https://www..com/s?wd=SQL%E8%AF%AD%E5%8F%A5&tn=44039180_cpr&fenlei=-bIi4WUvYETgN-" target="_blank" class="-highlight">SQL語句</a>" + x);
}
//插入語句
x = "insert into xp_users(gid,uid,uname,sex,email,pwd) values('";
x += "1','hsg77','何XXX',1,'[email protected]','1')";
if (dbw.ExecuteQuery(x) > 0)
{
this.Context.Response.Write("插入語句成功!下面是<a href="https://www..com/s?wd=SQL%E8%AF%AD%E5%8F%A5&tn=44039180_cpr&fenlei=-bIi4WUvYETgN-" target="_blank" class="-highlight">SQL語句</a>"+x);
}
//查詢語句
DataTable dt = dbw.ExecuteDataTable("select * from xp_users");
if (dt != null && dt.Rows.Count > 0)
{
this.Context.Response.Write("用戶數:"+dt.Rows.Count);
}
if (dt != null)
{
dt.Dispose();
dt = null;
}
dbw.Dispose();
dbw = null;
}
Ⅲ sql in 執行效率低 如何優化
你這樣不好，in,exists都是子查詢
你應該考慮連接查詢，這樣速度快很多。
以樓上兄弟為例子：
SELECT sum(*)
FROM pas_guiyyw
WHERE exists (SELECT 1
FROM subTable
WHERE subTable.rule='ruleContext'
AND pas_guiyyw.id= subTable.id )
應該修改為：
SELECT sum(*)
FROM pas_guiyyw a,
subTable b WHERE
a.id= b.id
and b.rule='ruleContext'
這樣效率高很多。
Ⅳ 如何在.net中訪問mysql資料庫
(1)首先需要下載C#訪問MySQL資料庫的ADO.NET驅動程序
mysql-connector-net-6.3.8.msi
(2)安裝mysql-connector-net
然後直接在Windows操作系統安裝mysql-connector-net-6.3.8.msi
(3)封裝資料庫訪問組件DbConnectionMySQL
///<summary>
///MySQL資料庫
///版本mysql-connector-net-6.3.8.msi
///</summary>
[Serializable]
publicclassDbConnectionMySQL:DbConnectionWrapper
{
publicDbConnectionMySQL(stringpConnectionString)
:base(pConnectionString)
{
this.m_dbconn=newMySqlConnection(pConnectionString);
this.m_DbConnState=DbConnState.Free;
}
//--
()
{
returnnewMySqlDataAdapter();
}
(DbCommanddbCommand)
{
returnnewMySqlDataAdapter(dbCommandasMySqlCommand);
}
()
{
returnnewMySqlCommand();
}
()
{
returnnewMySqlConnection();
}
()
{
returnnewMySqlCommandBuilder();
}
()
{
returnDataProviderType.Sql;
}
(stringTableName,stringUserName)
{
#regioninformation
boolrbc=false;//TABLES表中去查詢table_name
stringdSql="select*fromTABLESwheretable_name='"+TableName+"'";
DataSetds=this.ExecuteDataSet(dSql);
if(ds!=null)
{
if(ds.Tables[0].Rows.Count>0)
{
rbc=true;
}
else
{
rbc=false;
}
}
else
{
rbc=false;
}
returnrbc;
#endregion
}
(stringFieldName,stringTableName)
{
#regioninformation
boolrbc=false;
stringdSql="";
dSql="select*from"+TableName+"where1<>1";
DataSetds=this.ExecuteDataSet(dSql);
if(ds!=null)
{
DataTabledt=ds.Tables[0];
for(intj=0;j<dt.Columns.Count;j++)
{
if(dt.Columns[j].ColumnName.ToString().ToUpper()==FieldName.ToString().ToUpper())
{
rbc=true;
gotoReturn_End;
}
}
dt.Dispose();
dt=null;
}
ds.Dispose();
ds=null;
Return_End:
returnrbc;
#endregion
}
{
get
{
return':';//SQLite的參數符號為:
}
}
(stringname,objectvalue)
{
returnnewMySqlParameter(name,value);
}
(stringname)
{
DbParameterdbp=newMySqlParameter();
dbp.ParameterName=name;
returndbp;
}
(stringname,DbTypedbtype,objectvalue)
{
DbParameterdbp=newMySqlParameter();
dbp.ParameterName=name;
dbp.Value=value;
dbp.DbType=dbtype;
returndbp;
}
(stringname,DbTypedbtype,intsize,objectvalue)
{
DbParameterdbp=newMySqlParameter();
dbp.ParameterName=name;
dbp.Value=value;
dbp.DbType=dbtype;
dbp.Size=size;
returndbp;
}
}
（4)客戶端開發實例
publicvoidTestCShape_MySQL()
{
stringconstr="server=localhost;UserId=root;password=root;Database=xp_users";
DbConnectionWrapperdbw=newDbConnectionMySQL(constr);
boolrbc=dbw.TestConnection();
this.Context.Response.Write(rbc);
stringx="";
//刪除語句
x="deletefromxp_users";
if(dbw.ExecuteQuery(x)>0)
{
this.Context.Response.Write("刪除語句成功!下面是SQL語句<br>"+x);
}
//插入語句
x="insertintoxp_users(gid,uid,uname,sex,email,pwd)values('";
x+="1','hsg77','何XXX',1,'[email protected]','1')";
if(dbw.ExecuteQuery(x)>0)
{
this.Context.Response.Write("插入語句成功!下面是SQL語句<br>"+x);
}
//查詢語句
DataTabledt=dbw.ExecuteDataTable("select*fromxp_users");
if(dt!=null&&dt.Rows.Count>0)
{
this.Context.Response.Write("<br>用戶數:"+dt.Rows.Count);
}
if(dt!=null)
{
dt.Dispose();
dt=null;
}
dbw.Dispose();
dbw=null;
}
文中實例來自一篇博客，由於網路知道對 url 地址封殺，不能直接給出鏈接！