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 地址封杀，不能直接给出链接！