SQL Server 基础

最新推荐文章于 2022-03-30 12:40:21 发布

JayDa214

最新推荐文章于 2022-03-30 12:40:21 发布

阅读量395

点赞数

分类专栏：数据库文章标签： sql server 数据库 insert delete email join

本文链接：https://blog.csdn.net/JAVA_M16/article/details/6594495

版权

数据库专栏收录该内容

21 篇文章 0 订阅

订阅专栏

返回非空值的计数：Count(*) 用于数字或字符类型的列
例：select Count(*) as 及格人数 from Student where Score>=60;——》查询返回的是：分数60分以上的所有同学的人数
SQL Server 基础
一．基础
1.SQL Server 使用的是SQL（Structured Query Language,结构化的查询语言）语言，他只是数据库能识别的指令，T-SQL（Transact-SQL ）是标准的SQL加强版，由：数据库的定义语言（DDL），数据库的操作语言和数据库的控制语言组成；

      注:SQL中的下标从1开始；SQL中不分大小写

   2.主数据库文件：*.mdf
     次数据库文件：*.ndf   可多个，如：可分别在三个驱动盘上建立3个次数据库文件(1.ndf,2.ndf和3.ndf)
     日志文件    ：*.ldf

   3.注释：
    (1).单行注释：--
    (2).多行注释：

   4.快捷键：
    F5       :执行语句
    Ctrl F5 :检查语法

   5.登陆及权限的管理：
     (1).添加登陆账户：
         1).添加Windows账户：exec sp_grantlogin '账户名\密码'
        例：exec sp_grantlogin 'wzy\123123'

         2).添加SQL账户：exec sp_addlogin '账户名','，密码'
        例：exec sp_addlogin 'wzy','12313'

            删除登陆账户：exec sp_droplogin '账户名'
        例：exec sp_droplogin 'wzy'

     (2).添加数据库用户：
     exec sp_grantdbaccess '数据库账户'，'密码'
     例：exec sp_grantdbaccess 'wzydb','123'

         删除数据库用户：
     exec sp_revokedbaccess '数据库账户'
     例：exec sp_revokedbaccess 'wzydb'

     (3).管理数据库用户的权限：
     为数据库的用户分配权限：
     grant 权限，权限   on 表名 to 数据库账户
     例：grant insert,delete,check on Studnet to wzy
        ——赋予wzy数据库用户对Studnet表的添加，删除，查找的权限

     删除数据库的用户的权限：
     revoke 权限，权限   on 表名 from 数据库账户
     例：revoke insert,delete on Student from wzy
        ——删除wzy数据库用户对Studnet表的添加和删除的权限
二．
   1.SQL中的表达式：
    = 等于；>大于;< 小于；>= 大于或等于；<=小于或等于；<> 不等于；! 非

   2.通配符：
       ‘_’ :表示一个字符；例：A Like ‘C_’;
     %   :表示任意长度的字符串；例：A Like ‘C_%’;
        [ ] :括号中所指定范围内的任意一个字符；例：A Like ‘C_WO[1-2]’;
    [^ ] :不在括号中所指定范围内的任意一个字符；例：A Like ‘C_WO[^1-2]’;

   3.逻辑表达式：
     AND，OR，NOT （先是Not，再是AND，最后是Ro）
     例：Not(付款方式='信用卡')OR(信用卡 IN ('牡丹卡','龙卡','阳光卡'))？？？？？？？？？？？？？？？？
     注：IN是用来限制范围的；

三.函数
   1.字符串函数:
     1).字符串的长度：len
    例：select len(name) from Student where id=1;——》返回id为1的学生姓名的字符串长度：

     2).大小写转换：lower，upper
    例：select lower(name) from Student where id=1;——》返回id为1的学生姓名的字符串的小写
    例：select upper(name) from Student where id=1;——》返回id为1的学生姓名的字符串的大写

3).寻找一个字符串在另一个字符串中的起始下标：charindex
例: select charindex('Accp','My Accp',1);——》4

4).清楚左边的空格：ltrim
例：select ltrim(' 哈哈');——》查询返回的是:哈哈(前面没有空格)

5).清楚右边的空格：rtrim
例：select ltrim('哈哈 ');——》查询返回的是:哈哈(后面没有空格)

6).从字符串右边数返回指定数目的字符：right
例：select right('哈市咔哒收视费',3) ；——》查询返回的是：收视费

7).替换字符串中的字符：replace
例：select replace('师傅师傅是爽肤水爱仕达阿达','师','爽')；——》查询返回的是：爽傅爽傅是爽肤水爱仕达阿达

     8).在字符串中删除指定长度的字符串字符，用另一个字符串代替：stuff
    例：select stuff('ABCDEFGHRJ',2,6,'我的地盘我做主');——》查询返回的是：A我的地盘我做主HRJ
    注：返回字符串中从2开始的6个字符的被替换后的字符串；SQL中的下标从1开始；

   2.日期函数：(yy:年；mm:月；dd:日 dw:星期(从1开始，表示周日)；)
     1).取得当前的系统的日期：getDate()
    例：select getDate();——》查询返回的是:今天的日期：2009-03-25 15:39:24.870

2).推迟指定日期的：DateAdd()
例：select DateAdd(mm,4,'01/03/2009');——》查询返回的日期是：2009-05-03 00:00:00.000

3).两个日期之间指定日期部分的差值(前-后)：DateDiff()
例：select DateDiff(mm,'01/03/2009','07/12/2008');——》查询返回的日期是：-6

4).返回日期中指定部分的字符串形式：DateName()
例：select DateName(dw,'01/01/2000');——》查询返回的日期是：星期六

     5).返回指定日期部分的整数形式：DatePart()
    例：select DatePart(day,'07/12/2008');——》查询返回的日期是:12
        Select DatePart(dw,getDate());——》查询返回的日期是：6（今天是星期5）

   3.数学函数：
     1).取绝对值：Abs()
    例：select Abs(-23);——》查询返回的是：23
     2).大于或等于指定数值或表达式的最小整数：Ceiling()
    例：select Ceiling(23.5);——》查询返回的是：24

     3).小于或等于指定数值或表达式的最大整数：Floor()
    例：select Floor(23.5);——》查询返回的是：23

     4).取数值表达式的幂：Power()
    例：select Power(2,4);——》查询返回的是：16

5).将数值表达式精确到指定的位数：Round()
例：select Round(23.5329323,3);——》查询返回的是：23.5330000

     6)判断正负(正数的返回 1；负数的返回 1；0返回0):Sign()
    例：select Sign(-234);——》查询返回的是：-1

     7).取平方根：Sqrt()
    例：select Sqrt(9);——》查询返回的是：3.0

4.系统函数：
     1).转换数据类型：Convert
    例：select Convert(Varchar(5),12345)——》查询返回的是："12345";(把12345转换成字符串)
    例：select Convert(int,'12345')——》查询返回的是:12345;(把字符串12345转换成整形数字)

三.插入
    1.插入一条语句：
          insert into 表名（列名）values（值列表）
    例：Insert into Students(name,address,age)values('老李','北京',23);
    注：1).（列名）和（值列表）要完全对应，实现实体（行）的完整性

2).如果允许为空或者有默认值时，且（列名）中没写该字段，（值列表）中对应的项均可以不填

3).如果允许为空或者有默认值时，且（列名）中写该字段，（值列表）中对应的项必填，（列名）中默认值对应（值列表）中用 default，（列名）中允许为的对应（值列表）中用null(表示表中该列对应的字段没有应用对象)或' '(表示表中该列对应的字段类型的默认值)代替

4).要完全符合检查约束：（列名）中的字段有约束时，（值列表）中对应的项必须符合约束，否则添加失败

     2.一次插入多条语句：
    1).插入的新表必须预先存在：
      例：insert into User (name,address,Email)
          select name,address,Email from Student
      注：User表必须预先存在，查询到的数据的个数，顺序，数据类型等，必须与插入的项保持一致

    2).插入的新表预先不存在：
      例：select Student.name,Studnet.Address,Student.Emial
          into User
          from Student
      注：User表必须预先不存在，语句运行的过程中自己创建的

          向新表插入数据的时候需要创建标示例：
          例：select identity（int,1(标示的种子),1(增长量)as Student.Id,Student.name,Studnet.Address,Student.Emial
          into User
                from Student

四.修改
    update 表名 set 列名='',列名='',... where 列名=''
    例：update Student set name='老王'，address='南京' where Student.Id=1;
    update Scores set scores=scores 5 where scores<=90;

    注：1).如果不用where 限制，整个表所有的数据都被更新
    2).update 可以更新一条数据，也可以更新多条数据，还可以更新整张表

五.删除
   (1).删除表中的数据
       delete from 表名 where 列名=''
       例; delete from Scores where scores<=60;

注：如果不用where 限制，整个表所有的数据都被删除

   (2).删除表中的所有数据，表存在
       truncate table 表名: 删除没有主外建约束的表
       delete 表名: 可删除有主外建约束的表

(3).删除表中的所有数据，表也被删掉
drop table 表名: 完全删除表的所有

六.查询
1.简单的查询：
(1).查询表中的所有数据：select * from 表名；
例:select * from Students

(2).带条件的查询：select * from 表名 where 列名='';
例:select * from Students where name='老王';

(3).指定列名的查询：select 列名1,列名2,... from 表名 where 列名='';
例:select name,address from Students where id=1;

(4).查询空行：select 列名1,列名2,... from 表名 where 列名 is null; (或者is Not null)
例:select name,address from Students where Email is null; ——》查询Email为空的实体

    (5).查询后显示标题的为汉字，查询后多出一列(常量列)：
    例：select 姓名=name，地址=address，学校名称='北大青鸟' from Students
            或：select name as 姓名，address as 地址，'北大青鸟' as 学校名称 from Students

    (6).查询显示限制行数：
    例：select Top 5 姓名=name,地址=address，'北大青鸟' as 学校名称 from Students where Email is Not null;
        或：select Top 20 percent 姓名=name,地址=address，'北大青鸟' as 学校名称 from Students where Email is Not null;

    注：percent ：表示百分之几；TOP ：表示从上往下
        Top 20 percent：所有查询的数据的前20%条
    (7).合并列名的查询：
    例：select FirstName '.' LastName as 姓名 from User

    (8).排序查询结果：
    1).使用order by语句；并不是where的条件
           例：select StudentId as 学员编号，(Scores*0.9 9) as 学员成绩 from Student order by Scores

    2).ASC为升序；DESC为降序；默认为ASC升序排列；
       例：select StudentId as 学员编号，(Scores*0.9 9) as 学员成绩 from Student where (Scores*0.9 5)<60 order by Scores
       注： order by Scores——》查出的结果按成绩的升序排序

    3).也可以按多个字段进行排序显示查询结果，注意排序是按字段的先后顺序的：
       例：select StudentId as 学员编号，Scores as 学员成绩 from Student where Scores>60 order by Scores,StudentId
       注：order by Scores,StudentId——》查出的结果先按按成绩的升序排序，再按学员编号的升序排序

2.模糊查询：
    (1).使用Like:只用于字符串，仅与Char和Varchar数据的类型联合使用
    例：select * from Studnet where name Like '张%'；——》查询返回的是：姓张的所有学生
    例：select * from Card where id Like '%[^8]%[A,C]%';——》查询返回的是：第三位不是8的A卡或C卡

    (2).使用Between...and...或Not Between...and...进行范围查询：
    例：select * from Scores where Score Between 60 and 80; ——》查询返回的是：分数是60到80的成绩
        例：select * from Student where birthday Between '1993-2-3' and '2009-3-4';——》查询返回的是:出生在那时间段你的学生

(3).使用in或not in进行范围查询：
例：select * from Student where address in ('北京','上海','天津')； ——》查询返回的是：住址是这三个地的所有学生

3.聚合函数查询：
    (1).数字的汇总：Sum ,不能汇总字符，日期等其他函数
    例：select Sum(Score) from Student where name like '张%'； ——》查询返回的是：姓张的所有同学的分数总和
     注：查询只能返回一个数字，不能与返回多行的数据一起查询，如加上返回查询address，姓张的同学可能有与不同的地址（多行）

    (2).查询数字的平均值：Avg ,不能汇总字符，日期等其他函数
    例：select Avg(Score) from Student where name like '张%'； ——》查询返回的是：姓张的所有同学的分数平均值
    例：select avg(Score) from Student where Score>=60;——》查询返回的是：分数60分以上的所有同学的平均值
     注：查询只能返回一个数字，不能与返回多行的数据一起查询，如加上返回查询address，姓张的同学可能有与不同的地址（多行）

    (3).查询最大值或最小值：Max,Min; 能用于数字，字符，日期等其他函数
    例：select Age(Score) as 平均分,Max(Score) 最高分,Min(Score) as 最低分 from Student where Score>=60
         ——》查询返回的是：分数大于60分的同学的平均分，最高分，最低分
    注：查询只能返回一个数字，不能与返回多行的数据一起查询，如加上返回查询address，姓张的同学可能有与不同的地址（多行）

    (4).返回非空值的计数：Count(*) 用于数字或字符类型的列
    例：select Count(*) as 及格人数 from Student where Score>=60;——》查询返回的是：分数60分以上的所有同学的人数

4.分组查询：
    (1).使用Group By 进行分组查询：先分组在进行查询，并不是where的条件
    例：select CourseId as 课程id ，Avg(Score) as 课程的平均成绩 from Score Group By CourseId
        ——》查询返回的是：按课程的id进行分组查询，返回每门课程的平均成绩
    例：select StudnetId as 学生id，CourseId as 课程id，Avg(Score) as 课程的平均成绩 from Score
             Group by CourseId,Studentid
        ——》查询返回的是：先按课程的id进行分组，再按学生的id进行分组，然后查询返回每个学生每门课程的平均成绩
                    (还是每个学生每门课程的成绩，因为select语句中只能出现为每个分组返回一个值得表达式)
    注：分组查询中：select语句中只能出现：(1)被分组的列(2)为每个分组返回一个值得表达式（如聚合函数）

    (2).使用Having进行分组筛选：
    例：select * from StudnetId as 学生id，CourseId as 课程id，Avg(Score) as 课程的平均成绩 from Score
             Group by CourseId,Studentid Having Count(Score)>1
         ——》查询返回的是：先按课程的id进行分组，再按学生的id进行分组，并且分组后出现1次以上的列
                                    然后查询返回每个学生每门课程的平均成绩
                    (还是每个学生每门课程的成绩，因为select语句中只能出现为每个分组返回一个值得表达式)

    注：查询语句中：筛选顺序是：where，Group By ,Having
    where是从数据源中去掉不符合搜索条件的语句
    Group By从剩下的符合条件的列中，进行分组，表达式(函数)为各个组进行计算统计
    Having对分组进行筛选，去掉不符合组搜索条件的各组中的数据行

5.多表查询：
    (1).内联查询：表之间存在主外键关系,表之间的地位平等
    1).在where语句中制定查询条件：
       例：select Studnet.name as 学生姓名,Course.name as 课程名称,Scores.Score as 课程成绩 from Studnet,Scores,Course
        where Student.id = Scores.StudentId，Course.Id=Scores.CourseId
           ==》select S.name as 学生姓名,C.name as 课程名称,SC.Score as 课程成绩
                     from Studnet as S
             inner Join Scores as SC on (S.id = SC.StudentId)
                     inner Join Course as C on (C.Id=SC.CourseId)
        ——》查询返回的是：查询学生表和成绩表，返回每个学生的每门课程的成绩

    2).通过(inner)Join...on...语句中制定查询条件：
       例：select student.name,Scores.CourseId,Scores.Score from Studnet,Scores
        where Student.id = Scores.StudentId
        ==>select S.name as 学生姓名,C.CourseId as 课程id,C.Score as 课程成绩 from Studnet as S inner join Scores as C
        on S.id = C.StudentId

     (2)外联查询：表之间存在主外键关系,表之间的地位有主次之分
    1).左外链接查询：包含左表中所有匹配的行，如果右表中有的项左表中没有对应的项，以空填充
       例：select S.name as 学生姓名,C.CourseId as 课程id,C.Score as 课程成绩
               from Studnet as S
           left outer Join Scores as C
               on S.id=C.StudentId
         ——》查询返回的是：以学员信息表为主表，成绩表为次表，查询符合条件的列中的某些学员没有对应的科目和成绩，以空填充

    2).右外链接查询：包含右表中所有匹配的行，如果右表中有的项左表中没有对应的项，以空填充
       例：select S.name as 学生姓名,C.CourseId as 课程id,C.Score as 课程成绩
               from Studnet as S
           right outer Join Scores as C
               on S.id=C.StudentId
         ——》查询返回的是：以成绩表为主表，学员信息表为次表，查询符合条件的列中的某些成绩没有对应的学员和课程，以空填充

七.变量：
     (1).声明变量：局部变量用'@',系统(全局)变量用'@@',不能赋值
    DeClare @变量名变量的类型
    例：deClare @name varchar(10)
        set @name='马文才'
        select * from Student where name= @name
        ——查找马文才的信息

     (2).为变量赋值：
     1). set 专用的赋值常量，一次只能为一个变量赋值
         例：deClare @name varchar(10)
                 set @name='马文才'

     2).select 给从表中查询的结果赋值，可以给多个变量赋值，用逗号隔开
         例：deClare @name varchar(10)
         set @name='马文才'
         deClare @seat int

                 select @seat=Seat from Student where StuName=@name ——》查找马文才的座位号并赋值给变量@seat
                 select * from Student where (Seat=@seat 1) or (Seat=@seat 1) ——》查找马文才的左右同桌
     (3).最常用的全局变量：@@error ， @@identity
         1).@@error ：判断最近一条增删改的语句是否正确；成功：0；不成功：非0
        例：print '当前的错误号：' Convert(varchar(5),@@error)
         ——》输出错误号，大于0，表示上一条语句执行错误

         2).@@identity：最新的标识列号
         例：insert into Studnet (id,names)values(1,'wzy')
                 print '当前的错误号：' Convert(varchar(5),@@identity)
         ——》输出最后一条添加语句的id