public static void TestNULL()
{
MySqlTemplate.DROP("Test1");
CreateTableElement ct = new CreateTableElement("Test1");
ct.Add("ID", DataType.INT, true);
ct.Add("Name", DataType.VARCHAR40, true);
ct.Add("Age", DataType.INT);
ct.Create();
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 100, "张三", 16 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 101, "王五", null });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 102, "李四", null });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 103, "赵六", 55 });
//string cmd = "SELECT * FROM Test1 WHERE Age IS NULL;";
string cmd = "SELECT * FROM Test1 WHERE Age IS NOT NULL;";
DataSet obj = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, cmd, null);
List <Test2> list = MySqlTemplate.IList <Test2>(obj);
foreach (var item in list)
{
Console.WriteLine("ID:{0} Name:{1} Age:{2}", item.ID, item.Name, item.Age);
}
}
public static void CreateUser()
{
MySqlTemplate.DROP("user");
CreateTableElement ct = new CreateTableElement("user");
ct.Add("userId", DataType.INT, true, true);
ct.Add("accountNumber", DataType.VARCHAR40, true, true);
ct.Add("password", DataType.VARCHAR40);
ct.Add("roleId", DataType.INT);
ct.Create();
}
public static void CreateSetting()
{
MySqlTemplate.DROP("setting");
CreateTableElement ct = new CreateTableElement("setting");
ct.Add("nextUserId", DataType.INT, true);
ct.Add("nextRoleId", DataType.INT, true);
ct.Add("levelUpAddAttributePoint", DataType.INT, true);
ct.Create();
MySqlTemplate.INSERT("setting", new string[] { "nextUserId", "nextRoleId", "levelUpAddAttributePoint" }, new object[] { 1000, 1000, 5 });
}
public static void TestALTER()
{
MySqlTemplate.DROP("Test1");
CreateTableElement ct = new CreateTableElement("Test1");
ct.Add("ID", DataType.INT, true);
ct.Add("Name", DataType.CHAR10, true);
ct.Add("Age", DataType.INT);
ct.Create();
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 100, "张三", 16 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 101, "王五", 17 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 102, "李四", 18 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 103, "赵六", 55 });
MySqlTemplate.ALTER("Test1", "Age", AlterType.DROP);
string cmd = "SELECT * FROM Test1";
DataSet obj = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, cmd, null);
List <Test2> list = MySqlTemplate.IList <Test2>(obj);
foreach (var item in list)
{
Console.WriteLine("ID:{0} Name:{1} Age:{2}", item.ID, item.Name, item.Age);
}
Console.WriteLine();
MySqlTemplate.ALTER("Test1", "Gold", AlterType.ADD, DataType.INT, "ID");
MySqlTemplate.UPDATE("Test1", new string[] { "Gold" }, new object[] { 2000 });
cmd = "SELECT * FROM Test1";
obj = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, cmd, null);
list = MySqlTemplate.IList <Test2>(obj);
foreach (var item in list)
{
Console.WriteLine("ID:{0} Name:{1} Gold:{2}", item.ID, item.Name, item.Gold);
}
Console.WriteLine();
MySqlTemplate.ALTER("Test1", "Name", AlterType.MODIFY, DataType.VARCHAR40);
MySqlTemplate.ALTER("Test1", "Name", AlterType.CHANGE, DataType.VARCHAR40, "Nickname");
MySqlTemplate.ALTER("Test1", "MyTest", AlterType.RENAME);
}
public static void TestJOIN()
{
MySqlTemplate.DROP("Test1");
CreateTableElement ct = new CreateTableElement("Test1");
ct.Add("ID", DataType.INT, true);
ct.Add("Name", DataType.VARCHAR40, true);
ct.Add("Age", DataType.INT);
ct.Create();
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 100, "张三", 16 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 101, "王五", 26 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 102, "李四", 18 });
MySqlTemplate.INSERT("Test1", new string[] { "ID", "Name", "Age" }, new object[] { 103, "赵六", 55 });
MySqlTemplate.DROP("Test2");
ct.Clear();
ct = new CreateTableElement("Test2");
ct.Add("Name", DataType.VARCHAR40, true);
ct.Add("Level", DataType.INT);
ct.Create();
MySqlTemplate.INSERT("Test2", new string[] { "Name", "Level" }, new object[] { "张三", 6 });
MySqlTemplate.INSERT("Test2", new string[] { "Name", "Level" }, new object[] { "王五", 7 });
MySqlTemplate.INSERT("Test2", new string[] { "Name", "Level" }, new object[] { "李四", 12 });
MySqlTemplate.INSERT("Test2", new string[] { "Name", "Level" }, new object[] { "xx", 99 });
// 普通 join
//string cmd = "SELECT a.Name, a.Age, b.Level FROM Test1 a INNER JOIN Test2 b ON a.Name = b.Name";
// LEFT join
//string cmd = "SELECT a.Name, a.Age, b.Level FROM Test1 a LEFT JOIN Test2 b ON a.Name = b.Name";
// RIGHT join
string cmd = "SELECT a.Name, a.Age, b.Level FROM Test1 a RIGHT JOIN Test2 b ON a.Name = b.Name";
DataSet obj = MySqlHelper.GetDataSet(MySqlHelper.Conn, CommandType.Text, cmd, null);
List <Test2> list = MySqlTemplate.IList <Test2>(obj);
foreach (var item in list)
{
Console.WriteLine("Name:{0} Age:{1} Level:{2}", item.Name, item.Age, item.Level);
}
}
public static void CreateRole()
{
MySqlTemplate.DROP("role");
CreateTableElement ct = new CreateTableElement("role");
ct.Add("roleId", DataType.INT, true, true);
ct.Add("roleName", DataType.VARCHAR40);
ct.Add("level", DataType.INT);
ct.Add("exp", DataType.LONG);
ct.Add("fixedSTR", DataType.FLOAT);
ct.Add("fixedDEX", DataType.FLOAT);
ct.Add("fixedMAG", DataType.FLOAT);
ct.Add("fixedCON", DataType.FLOAT);
ct.Add("potentialSTR", DataType.FLOAT);
ct.Add("potentialDEX", DataType.FLOAT);
ct.Add("potentialMAG", DataType.FLOAT);
ct.Add("potentialCON", DataType.FLOAT);
ct.Create();
}