欢迎来到unity学习、unity培训、unity企业培训教育专区,这里有很多U3D资源、U3D培训视频U3D教程、U3D常见问题、U3D项目源码,我们致力于打造业内unity3d培训、学习第一品牌。
今天讲了模糊查询,左、右外连接,以及在VS中多表连接
废话不多说,先上SQL语句
模糊查询
//查询姓名字段中有qq关键字的项
select name from users where name like '%qq%'
//查询表中姓名和年龄字段里面年龄为空的项
select name,age from users where age is null
//查询表中姓名和年龄字段里面年龄不为空的项
select name,age from users where age is not null
//查询表中姓名和年龄字段里面年龄在21到24之间的项
select name,age from users where age between 21 and 24
select name,age from users where age>=21 and age<=24
//查询表中姓名和年龄里面年龄是21,22,24的项
select name,age from users where age in(21,22,24)
//查询表中姓名和年龄里面年龄不是21,22,24的项
select name,age from users where age not in(21,22,24)
//求总年龄,平均年龄,最大年龄,最小年龄以及总人数
select sum(age) 总年龄 from users
select avg(age) 平均年龄 from users
select max(age) 最大年龄,min(age) 最小年龄 from users
select count(*) 总人数 from users
select id 编号,avg(age) 平均年龄 from users group by id
select name,avg(age) 平均年龄 from users group by name
select id,name,avg(age) 平均年龄 from users group by id, name
//左外连接
select u.name,c.amount,c.price from users as u left join cart as c on u.id=c.uid
//右外连接
select u.name,c.amount,c.price from users as u right join cart as c on u.id=c.uid
下面上代码~
- public int insertCart(int amount, decimal price,int uid,int eid)
- {
- SqlConnection con = DB.Connect();
- con.Open();
- string sql = "insert into cart values("+amount+","+price+","+uid+","+eid+")";
- SqlCommand com = new SqlCommand(sql, con);
- int i = com.ExecuteNonQuery();
- return i;
- }
- public ArrayList select() {
- SqlConnection con = DB.Connect();
- con.Open();
- string sql = "select u.name,e.name,c.amount,c.price from cart c "+
- "inner join users u on u.id=c.uid "+
- "inner join equip e on e.id=c.eid";
- SqlCommand com = new SqlCommand(sql, con);
- SqlDataReader reader = com.ExecuteReader();
- while(reader.Read()){
- Users u = new Users();
- u.Name = (string)reader.GetValue(0);
- Equip e = new Equip();
- e.Name = (string)reader.GetValue(1);
- Cart c = new Cart();
- c.Amount =(int) reader.GetValue(2);
- c.Price =(decimal) reader.GetValue(3);
- list.Add(u);
- list.Add(e);
- list.Add(c);
- }
- return list;
- }
- }