所需软件： unity vs mysqlworkbench 同类软件即可
首先安装MySQL 官网：https://www.mysql.com/ 下载地址 ：https://dev.mysql.com/downloads/mysql/
下载安装即可。mysqlworkbench下载地址：https://dev.mysql.com/downloads/workbench/
此处用Mac电脑为例：
点击MySQL开启即可 。由于MySQL会消耗性能 开启关闭自己看着办
数据库软件我用的这个 选择比较多 大同小异用哪个都行
下面是一些操作数据库常用的语句 自己可以多敲敲
-- 创建数据库
/*
asdasdaa
*/
-- create database db4;
-- create database db5 character set utf8;
-- 查询
-- show databases;
-- 修改
-- alter database db5 character set gbk;
-- 删除
-- drop database db5;
-- 选择数据库
-- use db4;
-- 查看当前选择哪个数据库
-- select database();
-- create database mydb1;
-- use mydb1;
-- create table mytable2(
-- id int,
-- gender varchar(10)
-- );
-- 查看所有表
-- show tables
-- 查看表中字段
-- desc mytable22;
-- 添加字段
-- alter table mytable2 add image blob;
-- 修改字段
-- alter table mytable2 modify gender varchar(12);
-- 删除表字段
-- alter table mytable22 drop image;
-- 修改表名
-- rename table mytable2 to mytable22;
-- 查看表的创建细节
-- show create table mytable22;
-- 修改表中字段名
-- alter table mytable22 change gender ggeennddeerr varchar(10) ;
-- 修改字符集
-- alter table mytable22 character set gbk;
-- DML
set sql_safe_updates=0;
use mydb1;
-- 查询表中数据 *是全排列的意思
select * from mytable1;
-- 指定列查询
select id from mytable1;
-- 插入数据
insert into mytable1(id,name) values(1,'张三');
insert into mytable1(id,name) values(2,'赵四');
insert into mytable1(id,name) values(3,'王五');
-- 这两者是不一样的
insert into mytable1(id) values(4);
insert into mytable1(id,name) values(5,'');
-- 修改数据
update mytable1 set name='李四' where name = '赵四';
update mytable1 set name='李四' where id = '2';
-- 删除数据
delete from mytable1;
delete from mytable1 where id=2;
-- truncate 与delete的区别看有道云笔记
create database DB1;
use DB1;
create table stu(
sid char(6),
sname varchar(50),
age int,
gender varchar(10)
);
select * from stu;
insert into stu values('S_1001','liuYi',35,'male');
insert into stu values('S_1002','chenEr',15,'female');
insert into stu values('S_1003','ZhangSan',95,'male');
insert into stu values('S_1004','liSi',65,'female');
insert into stu values('S_1005','WangWu',55,'male');
insert into stu values('S_1006','zhaoLiu',75,'female');
insert into stu values('S_1007','sunQi',28,'male');
insert into stu values('S_1008','zhouBa',325,'female');
insert into stu values('S_1009','wuJiu',35,'male');
insert into stu values('S_1010','zhengShi',39,'female');
insert into stu values('S_1011','xxx',12,'male');
select * from emp;
create table emp(
empno int,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
-- 工资
sal decimal(7,2),
-- 奖金
comm decimal(7,2),
deptno int
);
insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-27',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-02-27',1250,500,30);
insert into emp values(7566,'JONES','SALESMAN',7839,'1981-02-27',2975,null,20);
insert into emp values(7654,'JONES','SALESMAN',7698,'1981-02-27',1250,1400,30);
insert into emp values(7698,'JONES','SALESMAN',7839,'1981-02-27',2850,null,30);
insert into emp values(7782,'JONES','SALESMAN',7839,'1981-02-27',2450,null,10);
insert into emp values(7788,'JONES','SALESMAN',7566,'1981-02-27',3000,null,20);
insert into emp values(7839,'KING','SALESMAN',null,'1981-02-27',5000,null,10);
insert into emp values(7844,'JONES','SALESMAN',7698,'1981-02-27',1500,0,30);
insert into emp values(7876,'JONES','SALESMAN',7788,'1981-02-27',1100,null,20);
insert into emp values(7900,'JONES','SALESMAN',7698,'1981-02-27',950,null,30);
insert into emp values(7902,'JONES','SALESMAN',7566,'1981-02-27',3000,null,20);
insert into emp values(7934,'JONES','SALESMAN',7782,'1981-02-27',1300,null,10);
select * from dept;
create table dept(
deptno int,
dname varchar(14),
ioc varchar(13)
);
insert into dept values(10,'ACCOUNTING','NEW YORK');
insert into dept values(20,'RESEARCH','DALLAS');
insert into dept values(30,'SALES','CHICAGO');
insert into dept values(40,'OPERATIONS','BOSTON');
select * from stu
where gender='female' and age=75;
select * from stu
where sid='S_1001'or sname='liSi';
select * from stu
where sid in('S_1001','S_1002');
select * from stu
where sid not in('S_1001','S_1002');
select * from stu
where age is null;
select * from stu
where age>=20 and age<=40;
select * from stu
where age>=20 and age<=40;
select * from stu
where gender !='male';
-- 不等于
select * from stu
where gender <>'male';
select * from stu
where not gender = 'male';
select * from stu
where sname is not null;
select * from stu
where not sname is null;
-- 模糊查询
-- 查询姓名由5个字母构成的学生记录
select * from stu
where sname like '_____';
select * from stu
where sname like '____i';
-- 查询姓名由z开头的
select * from stu
where sname like 'z%';
-- 包含a
select * from stu
where sname like '%a%';
select * from emp;
select deptno from emp;
-- 字段控制查询
-- 去除重复记录
select distinct deptno from emp;
-- 查看雇员的月薪与佣金之和
-- 任何值+ null=null
select sal,comm,sal+comm from emp;
select sal,comm,sal+ifnull(comm,0) from emp;
-- 给列其别名
select sal,comm,sal+ifnull(comm,0) as 总金额 from emp;
select sal as 月薪,comm as 佣金,sal+ifnull(comm,0) as 总金额 from emp;
-- 排序查询
-- asc 升序 desc 降序
select * from stu
order by age asc;
select * from stu
order by age desc;
-- 查询所有雇员 按月薪降序 月薪相同 按编号降序排列
select * from emp
order by sal desc,empno desc;
-- 聚合函数
-- 统计个数
select * from emp;
select count(comm) as 总数 from emp;
-- 查询emp表中月薪大于2500的人数
select count(*) as 月薪大于250的人数总数 from emp
where sal>=2500;
-- 查询月薪与佣金之和大于2500
select count(*) as 月薪与佣金之和大于250的人数总数 from emp
where sal+ifnull(comm,0)>=2500;
-- 有佣金有领导的人
select count(comm) , count(mgr) as 有佣金有领导的人 from emp;
-- 查询所有雇员月薪和
select sum(sal) from emp;
-- 查询所有雇员月薪和雇员佣金和
select sum(sal) ,sum(comm) from emp;
-- 查询所有雇员月薪+佣金和
select sum(sal+ifnull(comm,0)) from emp;
-- 统计所有雇员的平均工资
select avg(sal) as 平均工资 from emp;
-- 查询最高 最低工资
select max(sal),min(sal) from emp;
-- 分组查询
-- 错误
-- select gender,count(*) from stu;
-- 查询学生表里男生和女生的个数
select count(*) from stu where gender='male';
select count(*) from stu where gender='female';
select gender, count(*) from stu group by gender;
-- 查询每个部门的部门编号和每个部门的工资和
select deptno from emp group by deptno;
select deptno ,sum(sal) from emp group by deptno;
-- 查询 每个部门的部门编号 以及每个部门的人数
select deptno ,count(*) from emp group by deptno;
-- 查询每个部门的部门编号以及每个部门 工资大于1500的人数
select deptno ,count(*) from emp where sal>1500 group by deptno;
-- 查询工资总和大于9000的部门编号 以及 工资和
select deptno,sum(sal) from emp group by deptno having sum(sal)>9000 order by sum(sal) desc;
-- 限定查询
select * from emp limit 0,3; -- 从第0行开始查3行
-- 分页查询
-- select * from emp limit (currPage-1)*pageSize,pageSize;
-- 主键约束 特点： 数据唯一 且不能为null
create table table2(
id int primary key,
t_name varchar(50)
);
create table table3(
id int ,
t_name varchar(50),
primary key(id)
);
drop table table4;
create table table3(
id int ,
t_name varchar(50),
primary key(id,t_name) -- 联合主键 两个不是都相同即可
);
create table table4(
id int
);
alter table table4 add constraint pk_table3_id primary key(id);
-- 唯一约束
create table table5(
id int primary key,
t_name varchar(11) unique
);
-- 自动增长列
create table table6(
id int primary key auto_increment,
t_name varchar(11)
);
-- 非空约束
create table table7(
id int primary key not null auto_increment,
t_name varchar(11) not null
);
insert into table7(t_name) values('2');
-- 默认值约束
create table table8(
id int primary key not null auto_increment,
t_name varchar(11) default 'las'
);
insert into table8(id) values(2);
insert into table8(id,t_name) values(3,default);
create table student(
stuid varchar(10) primary key,
stuname varchar(50)
);
-- 外健约束
create table score(
stuid varchar(10) ,
score int,
courseid int,
constraint fk_stu_sco foreign key(stuid) references student(stuid)
);
alter table score1 add constraint fk_stu_sco foreign key(stuid) references student(stuid);
drop table score;
select * from student;
insert into student values('1001','a');
insert into student values('1002','b');
insert into student values('1003','c');
insert into student values('1004','d');
select * from score;
insert into score values('1001',98,1);
insert into score values('1002',95,1);
insert into score values('1002',67,2);
insert into score values('1003',83,2);
insert into score values('1003',57,3);
use game;
select * from user;
insert into user(username,password) values('1001','a');
接下来开始用vs编写服务器
写一个connhelper类： 用于连接数据库
using System;
using MySql.Data.MySqlClient;
namespace GameServer.Tool
{
public class ConnHelper
{
public const string CONNRCTIONSTR = "database=game ; datasource= 127.0.0.1; port=3306; User Id=root; Password=12345678;";
public static MySqlConnection Connect()
{
MySqlConnection conn = new MySqlConnection(CONNRCTIONSTR);
try
{
conn.Open();
Console.WriteLine("数据库连接成功");
return conn;
}
catch (Exception ex)
{
Console.WriteLine(ex);
return null;
}
}
public static void CloseConnect(MySqlConnection conn)
{
if (conn != null)
{
conn.Close();
}
else
{
Console.WriteLine(conn + "不存在");
}
}
}
}
下面两个类是操作数据库 比如写入用户 密码 查询用户是否存在 修改密码 通过用户得到用户信息
using System;
using GameServer.Model;
using MySql.Data.MySqlClient;
namespace GameServer.DAO
{
public class UserDto
{
public User VerifyUser(MySqlConnection connection, string username, string password)
{
MySqlDataReader dataReader = null;
try
{
MySqlCommand command = new MySqlCommand("select * from user where username=@username and password=@pwd", connection);
command.Parameters.AddWithValue("username", username);
command.Parameters.AddWithValue("pwd", password);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
int id = dataReader.GetInt32("id");
return new User(id, username, password);
}
else
{
return null;
}
}
catch (Exception ex)
{
Console.WriteLine("VerifyUser" + ex);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
}
return null;
}
public bool GetUserByUsername(MySqlConnection connection, string username)
{
MySqlDataReader dataReader = null;
try
{
MySqlCommand command = new MySqlCommand("select * from user where username = @username", connection);
command.Parameters.AddWithValue("username", username);
dataReader = command.ExecuteReader();
if (dataReader.HasRows)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
Console.WriteLine("GetUserByUsername" + ex);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
}
return false;
}
public void AddUser(MySqlConnection connection, string username, string password)
{
try
{
Console.WriteLine(username + "||||||" + password);
MySqlCommand command = new MySqlCommand("insert into user set username = @use , password = @pwd", connection);
command.Parameters.AddWithValue("use", username);
command.Parameters.AddWithValue("pwd", password);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("AddUser" + ex);
}
}
}
}
using System;
using GameServer.Model;
using MySql.Data.MySqlClient;
namespace GameServer.DAO
{
public class ResultDAO
{
public Result GetResultByUserid(MySqlConnection connection, int userid)
{
MySqlDataReader dataReader = null;
try
{
MySqlCommand command = new MySqlCommand("select * from result where userid=@userid ", connection);
command.Parameters.AddWithValue("userid", userid);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
int id = dataReader.GetInt32("id");
int totalCount = dataReader.GetInt32("totalcount");
int winCount = dataReader.GetInt32("wincount");
return new Result(id, userid, totalCount, winCount);
}
else
{
return new Result(-1, userid, 0, 0);
}
}
catch (Exception ex)
{
Console.WriteLine("GetResultByUserid" + ex);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
}
return null;
}
public void UpdateOrAddResult(MySqlConnection connection, Result result)
{
MySqlCommand cmd = null;
try
{
if (result.id <= -1)
{
cmd = new MySqlCommand("insert into result set totalcount= @totalcount, wincount=@wincount, userid=@userid", connection);
}
else
{
cmd = new MySqlCommand("update result set totalcount= @totalcount, wincount=@wincount where userid=@userid", connection);
}
cmd.Parameters.AddWithValue("totalcount", result.TotalCount);
cmd.Parameters.AddWithValue("wincount", result.WinCount);
cmd.Parameters.AddWithValue("userid", result.Userid);
cmd.ExecuteNonQuery();
if (result.id <= -1)
{
Result tempRes = GetResultByUserid(connection, result.Userid);
result.id = tempRes.id;
}
}
catch (Exception ex)
{
Console.WriteLine("UpdateOrAddResult" + ex);
}
}
}
}
下载是我学习mwsql做的笔记
http://note.youdao.com/noteshare?id=90a406fa8d57c102fa12b4f1d492c719
3853
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
