数据库基础笔记2

聚合函数与分组


select  gender from T_student group by gender


group by 里面有的，select里面才能有。（聚合函数除外）


聚合函数针对null不受理




having 语句
查询完之后在进行一次筛选




联合结果集：
在两个select语句之间加   union  会删除重复的数据
如果用union all  保留重复的数据


联合的时候要注意类型
类型装换函数
cast(1234 as varchar(10))


convert(varchar(10),1234)




查询年龄大于25岁的男的
select * from T_student where age>25 and gender=1




查询年龄最大的女的
select top(1)* from T_student where gender=0  order by age desc




联合例子：


select '最高分' as '等级' ,max(scdatabase)as 分数 from score
union
select '最低分' as '等级' ,min(scdatabase)as 分数 from score
union
select '平均分' as '等级' ,avg(scdatabase)as 分数 from score




集合特征：
 无序 确定 唯一
 
 
 ASCII('abc') 把字符变为ASCii码对应的数字，只转换最左边的字符即a
 
 UNICODE('我')  把字符变为unicode码对应的数字 f返回25105
 
 char(97) 把ASCii码对应的数字转换为字符 返回a 
 
 nchar(25105)unicode码对应的数字转换为字符 返回'我'
 


 函数：
 len()  字符数
 datalength() 字节数
lower() 转小写
upper() 转大写
ltrim() 去掉左边空格
rtrim() 去掉右边空格
去掉两边空格需要结合ltrim()和rtrim()


日期函数
getdate()  获取系统当前日期


year()   获得年份
month()  获得月份
day()    获得天


如year('2012-2-12')   结果2012
如month('2012-2-12')   结果2
如day('2012-2-12')   结果12


一般根据出生日期计算出年龄
year(getdate())-year(birthday) as age




例子：得到今天30天后的日期
dateadd(day,30,getdate())
第一个参数为单位。




时间求差：
datediff(second,'2012-7-11 11:47:15','2012-7-11 11:48:23')
第一个参数为单位，第二个参数为开始时间，第二个参数为结束时间




datepart(单位,getdate()) 与  单位(getdate()) 一样的






convert(nvarchar(10),getdate(),111)  把当前日期转化为111格式字符串(详见帮助)










ADO.Net  就是一组类库




***Connection
***Command


SQLConnection 用来连接数据库
SQLCommand   用来执行SQL语句
DataReader 只读只进的结果集，一条一条读取数据
DataAdapter  一个封装了上面3个对象的对象




我的连接字符串：string sqlConn = @"server=.\sqlexpress;database=MyDataBase;Integrated security=true";

ADO.net就是类库


ADO.Net命名空间
System.Data.SqlClient
System.Data


常见类库
SqlConnection
SqlCommand




连接字符串
                                      ;integrated security=true;
server=机器名\实例名;database=数据库名;uid=用户名;pwd=密码;




图和启用SQL Server
开启混合验证模式
启用sa并设置密码


处理数据库，使用那些方法？
ExcuteNonQuery()  返回受影响的行数 （如果用他执行查询语句将返回-1）
ExcuteScalar()     返回第一行，第一列的数据




ExecuteReader()


用法：
SqlDataReader reader=cmd.ExecuteReader();
if(reader.HasRows)
{
reader.read();
int id=reader.GetInt32(列);


}




读取多行数据可用下面用法:
SqlDataReader reader=cmd.ExecuteReader();
using(reader)
{
if(reader.HasRows)
{
while(reader.read())
{
int id=reader.GetInt32(列);
...
...
...
}


}
}








连接字符串使用配置文件


添加配置文件
添加connectStrings节点（name,connectionString）
添加引用Configuration         using System.Configuration;
使用静态类ConfigurationManager


ConfigurationManager.ConnectionStrings["名字"].ConnectionString  返回自己写的连接字符串


例如
：
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings >
    <add name ="MyDataBase" connectionString ="server=.\sqlexpress;database=mydatabase;integrated security=true;"/>
  </connectionStrings>
</configuration>






string connstr=ConfigurationManager.ConnectionStrings["MyDataBase"].ConnectionString ;


cmd.Parameters.Clear();


cmd.Parameters.Add(new SqlParameter("@name",txtUid.Text.Trim()));
或
cmd.Parameters.AddWithValue("@name",txtUid.Text.Trim());










存储过程目前可以看做是：
SQL Server中的函数
















ExecuteNonQuery 执行没有查询的语句
ExecuteScalor

ExecuteReader



DataSet:(不常用)


DataSet ds =new DataSet("MyData1");
//就是一个数据库
//里面可以存表
DataTable dt=new DataTable("MyTable1")
//创建列
DataColumn dc1=new DataColumn("id", typeof(int));
dc1.AutoIncrement=true;
dc1.AutoIncrementSeed=1;
dc1.AutoIncrementStep=1;


DataColumn dc2=new DataColumn("name", typeof(string));


//将列加到table中
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);


ds.Tables.Add(dt);


//加一行数据
Data r1=dt.NewRow();
Data r2=dt.NewRow();
Data r3=dt.NewRow();
r1["name"]="张三";
r2["name"]="李四";
r3["name"]="王五";


dt.Rows.Add(r1);
dt.Rows.Add(r2);
dt.Rows.Add(r3);


foreach(DataRow r in dt.Rows)
{
Console.WriteLine("{0},{1}",r["id"],r["name"]);
}






SqlDataAdapter与DataSet的配合
 static void Main(string[] args)
        {
            SqlDataAdapter adapt = new SqlDataAdapter("select * from t_test", @"server=.\sqlexpress;database=mydatabase;integrated security=true;");


            DataSet dt = new DataSet();
            adapt.Fill(dt);


            dt.WriteXml(@"d:\desktop\xxx.xml");
        }








using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Text.RegularExpressions;


namespace MySQLHelper
{
   public class HELPME_QSL
    {
        static string sqlstr = ConfigurationManager.ConnectionStrings["mydatabase"].ConnectionString;
        public static int ExecuteNonQuery(string commandText, params SqlParameter[] p)
        {
            using (SqlConnection conn = new SqlConnection(sqlstr))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {


                    if (p != null && p.Length != 0)
                    {
                        cmd.Parameters.AddRange(p);
                    }




                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }


                    return cmd.ExecuteNonQuery();
                }
            }


        }
        public static object ExecuteScalar(string commandText, params SqlParameter[] p)
        {
            using (SqlConnection conn = new SqlConnection(sqlstr))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {


                    if (p != null && p.Length != 0)
                    {
                        cmd.Parameters.AddRange(p);
                    }




                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }


                    return cmd.ExecuteScalar();
                }
            }
        }
        public static SqlDataReader ExecuteReader(string commandText, params SqlParameter[] p)
        {
            SqlConnection conn = new SqlConnection(sqlstr);


            using (SqlCommand cmd = new SqlCommand(commandText, conn))
            {


                if (p != null && p.Length != 0)
                {
                    cmd.Parameters.AddRange(p);
                }




                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }


                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }


        }
        public static DataSet SqlDataAdapter(string commandText, params SqlParameter[] p)
        {


            SqlDataAdapter adapt = new SqlDataAdapter(commandText, sqlstr);


            DataSet dt = new DataSet();
            using (adapt)
            {
                if (p != null)
                {
                    adapt.SelectCommand.Parameters.AddRange(p);
                }
                adapt.Fill(dt);
            }


            return dt;


        }


        //第二个参数为object
        public static int ExecuteNonQuery(string commandText, params object[] o)
        {
            List<string> list = new List<string>();
            using (SqlConnection conn = new SqlConnection(sqlstr))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {


                    MatchCollection ms = Regex.Matches(commandText, @"@\w+");
                    foreach (Match m in ms)
                    {
                        list.Add(m.Value);
                    }
                    if (list.Count != o.Length)
                    {
                        throw new Exception("参数个数与SQL语句不符");
                    }


                    if (o != null && o.Length != 0)
                    {
                        for (int i = 0; i < list.Count; i++)
                        {
                            cmd.Parameters.AddWithValue(list[i], o[i]);
                        }
                    }


                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }


                    return cmd.ExecuteNonQuery();




                }
            }


        }


        public static object ExecuteScalar(string commandText, params object[] o)
        {
            List<string> list = new List<string>();
            using (SqlConnection conn = new SqlConnection(sqlstr))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, conn))
                {


                    MatchCollection ms = Regex.Matches(commandText, @"@\w+");
                    foreach (Match m in ms)
                    {
                        list.Add(m.Value);
                    }
                    if (list.Count != o.Length)
                    {
                        throw new Exception("参数个数与SQL语句不符");
                    }


                    if (o != null && o.Length != 0)
                    {
                        for (int i = 0; i < list.Count; i++)
                        {
                            cmd.Parameters.AddWithValue(list[i], o[i]);
                        }
                    }


                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }


                    return cmd.ExecuteScalar();




                }
            }


        }


        public static SqlDataReader ExecuteReader(string commandText, params object[] o)
        {
            List<string> list = new List<string>();
            SqlConnection conn = new SqlConnection(sqlstr);


            using (SqlCommand cmd = new SqlCommand(commandText, conn))
            {


                MatchCollection ms = Regex.Matches(commandText, @"@\w+");
                foreach (Match m in ms)
                {
                    list.Add(m.Value);
                }
                if (list.Count != o.Length)
                {
                    throw new Exception("参数个数与SQL语句不符");
                }


                if (o != null && o.Length != 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        cmd.Parameters.AddWithValue(list[i], o[i]);
                    }
                }


                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }


                return cmd.ExecuteReader(CommandBehavior.CloseConnection);


            }



        }


        public static DataSet SqlDataAdapter(string commandText, params object[] o)
        {
            SqlDataAdapter adapt = new SqlDataAdapter(commandText, sqlstr);


            DataSet dt = new DataSet();
            List<string> list = new List<string>();


            MatchCollection ms = Regex.Matches(commandText, @"@\w+");
            foreach (Match m in ms)
            {
                list.Add(m.Value);
            }
            if (list.Count != o.Length)
            {
                throw new Exception("参数个数与SQL语句不符");
            }
            using (adapt)
            {


                if (o != null && o.Length != 0)
                {
                    for (int i = 0; i < list.Count; i++)
                    {
                        adapt.SelectCommand.Parameters.AddWithValue(list[i], o[i]);
                    }
                }
                adapt.Fill(dt);
            }




            return dt;




        }






    }
}